pyfpdb/SQL.py

   1 #!/usr/bin/env python
   2 # -*- coding: utf-8 -*-
   3 """Returns a dict of SQL statements used in fpdb.
   4 """
   5 #    Copyright 2008-2011, Ray E. Barker
   6 #
   7 #    This program is free software; you can redistribute it and/or modify
   8 #    it under the terms of the GNU General Public License as published by
   9 #    the Free Software Foundation; either version 2 of the License, or
  10 #    (at your option) any later version.
  11 #
  12 #    This program is distributed in the hope that it will be useful,
  13 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  15 #    GNU General Public License for more details.
  16 #
  17 #    You should have received a copy of the GNU General Public License
  18 #    along with this program; if not, write to the Free Software
  19 #    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
  20
  21 #    NOTES:  The sql statements use the placeholder %s for bind variables
  22 #            which is then replaced by ? for sqlite. Comments can be included
  23 #            within sql statements using C style /* ... */ comments, BUT
  24 #            THE COMMENTS MUST NOT INCLUDE %s OR ?.
  25
  26 ########################################################################
  27
  28 #    Standard Library modules
  29 import re
  30
  31 #    pyGTK modules
  32
  33 #    FreePokerTools modules
  34
  35 class Sql:
  36
  37     def __init__(self, game = 'holdem', db_server = 'mysql'):
  38         self.query = {}
  39 ###############################################################################3
  40 #    Support for the Free Poker DataBase = fpdb   http://fpdb.sourceforge.net/
  41 #
  42
  43         ################################
  44         # List tables
  45         ################################
  46         if db_server == 'mysql':
  47             self.query['list_tables'] = """SHOW TABLES"""
  48         elif db_server == 'postgresql':
  49             self.query['list_tables'] = """SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'"""
  50         elif db_server == 'sqlite':
  51             self.query['list_tables'] = """SELECT name FROM sqlite_master
  52             WHERE type='table'
  53             ORDER BY name;"""
  54
  55         ################################
  56         # List indexes
  57         ################################
  58         if db_server == 'mysql':
  59             self.query['list_indexes'] = """SHOW INDEXES"""
  60         elif db_server == 'postgresql':
  61             self.query['list_indexes'] = """SELECT tablename, indexname FROM PG_INDEXES"""
  62         elif db_server == 'sqlite':
  63             self.query['list_indexes'] = """SELECT name FROM sqlite_master
  64                                             WHERE type='index'
  65                                             ORDER BY name;"""
  66
  67         ##################################################################
  68         # Drop Tables - MySQL, PostgreSQL and SQLite all share same syntax
  69         ##################################################################
  70
  71         self.query['drop_table'] = """DROP TABLE IF EXISTS """
  72
  73
  74         ##################################################################
  75         # Set transaction isolation level
  76         ##################################################################
  77
  78         if db_server == 'mysql' or db_server == 'postgresql':
  79             self.query['set tx level'] = """SET SESSION TRANSACTION
  80             ISOLATION LEVEL READ COMMITTED"""
  81         elif db_server == 'sqlite':
  82             self.query['set tx level'] = """ """
  83
  84
  85         ################################
  86         # Select basic info
  87         ################################
  88
  89         self.query['getSiteId'] = """SELECT id from Sites where name = %s"""
  90
  91         self.query['getGames'] = """SELECT DISTINCT category from Gametypes"""
  92
  93         self.query['getCurrencies'] = """SELECT DISTINCT currency from Gametypes ORDER BY currency"""
  94
  95         self.query['getLimits'] = """SELECT DISTINCT bigBlind from Gametypes ORDER by bigBlind DESC"""
  96
  97         self.query['getTourneyTypesIds'] = "SELECT id FROM TourneyTypes"
  98
  99         ################################
 100         # Create Settings
 101         ################################
 102         if db_server == 'mysql':
 103             self.query['createSettingsTable'] = """CREATE TABLE Settings (
 104                                         version SMALLINT NOT NULL)
 105                                 ENGINE=INNODB"""
 106         elif db_server == 'postgresql':
 107             self.query['createSettingsTable'] =  """CREATE TABLE Settings (version SMALLINT NOT NULL)"""
 108
 109         elif db_server == 'sqlite':
 110             self.query['createSettingsTable'] = """CREATE TABLE Settings
 111             (version INTEGER NOT NULL) """
 112
 113         ################################
 114         # Create InsertLock
 115         ################################
 116         if db_server == 'mysql':
 117             self.query['createLockTable'] = """CREATE TABLE InsertLock (
 118                         id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 119                         locked BOOLEAN NOT NULL DEFAULT FALSE)
 120                         ENGINE=INNODB"""
 121
 122         ################################
 123         # Create RawHands (this table is all but identical with RawTourneys)
 124         ################################
 125         if db_server == 'mysql':
 126             self.query['createRawHands'] = """CREATE TABLE RawHands (
 127                         id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 128                         handId BIGINT NOT NULL,
 129                         rawHand TEXT NOT NULL,
 130                         complain BOOLEAN NOT NULL DEFAULT FALSE)
 131                         ENGINE=INNODB"""
 132         elif db_server == 'postgresql':
 133             self.query['createRawHands'] =  """CREATE TABLE RawHands (
 134                         id BIGSERIAL, PRIMARY KEY (id),
 135                         handId BIGINT NOT NULL,
 136                         rawHand TEXT NOT NULL,
 137                         complain BOOLEAN NOT NULL DEFAULT FALSE)"""
 138         elif db_server == 'sqlite':
 139             self.query['createRawHands'] = """CREATE TABLE RawHands (
 140                         id INTEGER PRIMARY KEY,
 141                         handId BIGINT NOT NULL,
 142                         rawHand TEXT NOT NULL,
 143                         complain BOOLEAN NOT NULL DEFAULT FALSE)"""
 144
 145         ################################
 146         # Create RawTourneys (this table is all but identical with RawHands)
 147         ################################
 148         if db_server == 'mysql':
 149             self.query['createRawTourneys'] = """CREATE TABLE RawTourneys (
 150                         id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 151                         tourneyId BIGINT NOT NULL,
 152                         rawTourney TEXT NOT NULL,
 153                         complain BOOLEAN NOT NULL DEFAULT FALSE)
 154                         ENGINE=INNODB"""
 155         elif db_server == 'postgresql':
 156             self.query['createRawTourneys'] =  """CREATE TABLE RawTourneys (
 157                         id BIGSERIAL, PRIMARY KEY (id),
 158                         tourneyId BIGINT NOT NULL,
 159                         rawTourney TEXT NOT NULL,
 160                         complain BOOLEAN NOT NULL DEFAULT FALSE)"""
 161         elif db_server == 'sqlite':
 162             self.query['createRawTourneys'] = """CREATE TABLE RawTourneys (
 163                         id INTEGER PRIMARY KEY,
 164                         tourneyId BIGINT NOT NULL,
 165                         rawTourney TEXT NOT NULL,
 166                         complain BOOLEAN NOT NULL DEFAULT FALSE)"""
 167
 168         ################################
 169         # Create Actions
 170         ################################
 171
 172         if db_server == 'mysql':
 173             self.query['createActionsTable'] = """CREATE TABLE Actions (
 174                         id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 175                         name varchar(32) NOT NULL,
 176                         code char(4) NOT NULL)
 177                         ENGINE=INNODB"""
 178         elif db_server == 'postgresql':
 179             self.query['createActionsTable'] = """CREATE TABLE Actions (
 180                         id SERIAL, PRIMARY KEY (id),
 181                         name varchar(32),
 182                         code char(4))"""
 183         elif db_server == 'sqlite':
 184             self.query['createActionsTable'] = """CREATE TABLE Actions (
 185                         id INTEGER PRIMARY KEY,
 186                         name TEXT NOT NULL,
 187                         code TEXT NOT NULL)"""
 188
 189         ################################
 190         # Create Sites
 191         ################################
 192
 193         if db_server == 'mysql':
 194             self.query['createSitesTable'] = """CREATE TABLE Sites (
 195                         id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 196                         name varchar(32) NOT NULL,
 197                         code char(2) NOT NULL)
 198                         ENGINE=INNODB"""
 199         elif db_server == 'postgresql':
 200             self.query['createSitesTable'] = """CREATE TABLE Sites (
 201                         id SERIAL, PRIMARY KEY (id),
 202                         name varchar(32),
 203                         code char(2))"""
 204         elif db_server == 'sqlite':
 205             self.query['createSitesTable'] = """CREATE TABLE Sites (
 206                         id INTEGER PRIMARY KEY,
 207                         name TEXT NOT NULL,
 208                         code TEXT NOT NULL)"""
 209
 210         ################################
 211         # Create Backings
 212         ################################
 213
 214         if db_server == 'mysql':
 215             self.query['createBackingsTable'] = """CREATE TABLE Backings (
 216                         id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 217                         tourneysPlayersId BIGINT UNSIGNED NOT NULL, FOREIGN KEY (tourneysPlayersId) REFERENCES TourneysPlayers(id),
 218                         playerId INT UNSIGNED NOT NULL, FOREIGN KEY (playerId) REFERENCES Players(id),
 219                         buyInPercentage FLOAT UNSIGNED NOT NULL,
 220                         payOffPercentage FLOAT UNSIGNED NOT NULL) ENGINE=INNODB"""
 221         elif db_server == 'postgresql':
 222             self.query['createBackingsTable'] = """CREATE TABLE Backings (
 223                         id BIGSERIAL, PRIMARY KEY (id),
 224                         tourneysPlayersId INT NOT NULL, FOREIGN KEY (tourneysPlayersId) REFERENCES TourneysPlayers(id),
 225                         playerId INT NOT NULL, FOREIGN KEY (playerId) REFERENCES Players(id),
 226                         buyInPercentage FLOAT NOT NULL,
 227                         payOffPercentage FLOAT NOT NULL)"""
 228         elif db_server == 'sqlite':
 229             self.query['createBackingsTable'] = """CREATE TABLE Backings (
 230                         id INTEGER PRIMARY KEY,
 231                         tourneysPlayersId INT NOT NULL,
 232                         playerId INT NOT NULL,
 233                         buyInPercentage REAL UNSIGNED NOT NULL,
 234                         payOffPercentage REAL UNSIGNED NOT NULL)"""
 235
 236         ################################
 237         # Create Gametypes
 238         ################################
 239
 240         if db_server == 'mysql':
 241             self.query['createGametypesTable'] = """CREATE TABLE Gametypes (
 242                         id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 243                         siteId SMALLINT UNSIGNED NOT NULL, FOREIGN KEY (siteId) REFERENCES Sites(id),
 244                         currency varchar(4) NOT NULL,
 245                         type char(4) NOT NULL,
 246                         base char(4) NOT NULL,
 247                         category varchar(9) NOT NULL,
 248                         limitType char(2) NOT NULL,
 249                         hiLo char(1) NOT NULL,
 250                         mix varchar(9) NOT NULL,
 251                         smallBlind int,
 252                         bigBlind int,
 253                         smallBet int NOT NULL,
 254                         bigBet int NOT NULL,
 255                         maxSeats TINYINT NOT NULL,
 256                         ante INT NOT NULL)
 257                         ENGINE=INNODB"""
 258         elif db_server == 'postgresql':
 259             self.query['createGametypesTable'] = """CREATE TABLE Gametypes (
 260                         id SERIAL NOT NULL, PRIMARY KEY (id),
 261                         siteId INTEGER NOT NULL, FOREIGN KEY (siteId) REFERENCES Sites(id),
 262                         currency varchar(4) NOT NULL,
 263                         type char(4) NOT NULL,
 264                         base char(4) NOT NULL,
 265                         category varchar(9) NOT NULL,
 266                         limitType char(2) NOT NULL,
 267                         hiLo char(1) NOT NULL,
 268                         mix char(9) NOT NULL,
 269                         smallBlind int,
 270                         bigBlind int,
 271                         smallBet int NOT NULL,
 272                         bigBet int NOT NULL,
 273                         maxSeats SMALLINT NOT NULL,
 274                         ante INT NOT NULL)"""
 275         elif db_server == 'sqlite':
 276             self.query['createGametypesTable'] = """CREATE TABLE Gametypes (
 277                         id INTEGER PRIMARY KEY NOT NULL,
 278                         siteId INTEGER NOT NULL,
 279                         currency TEXT NOT NULL,
 280                         type TEXT NOT NULL,
 281                         base TEXT NOT NULL,
 282                         category TEXT NOT NULL,
 283                         limitType TEXT NOT NULL,
 284                         hiLo TEXT NOT NULL,
 285                         mix TEXT NOT NULL,
 286                         smallBlind INTEGER,
 287                         bigBlind INTEGER,
 288                         smallBet INTEGER NOT NULL,
 289                         bigBet INTEGER NOT NULL,
 290                         maxSeats INT NOT NULL,
 291                         ante INT NOT NULL,
 292                         FOREIGN KEY(siteId) REFERENCES Sites(id) ON DELETE CASCADE)"""
 293
 294
 295         ################################
 296         # Create Players
 297         ################################
 298
 299         if db_server == 'mysql':
 300             self.query['createPlayersTable'] = """CREATE TABLE Players (
 301                         id INT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 302                         name VARCHAR(32) NOT NULL,
 303                         siteId SMALLINT UNSIGNED NOT NULL, FOREIGN KEY (siteId) REFERENCES Sites(id),
 304                         comment text,
 305                         commentTs DATETIME)
 306                         ENGINE=INNODB"""
 307         elif db_server == 'postgresql':
 308             self.query['createPlayersTable'] = """CREATE TABLE Players (
 309                         id SERIAL, PRIMARY KEY (id),
 310                         name VARCHAR(32),
 311                         siteId INTEGER, FOREIGN KEY (siteId) REFERENCES Sites(id),
 312                         comment text,
 313                         commentTs timestamp without time zone)"""
 314         elif db_server == 'sqlite':
 315             self.query['createPlayersTable'] = """CREATE TABLE Players (
 316                         id INTEGER PRIMARY KEY,
 317                         name TEXT,
 318                         siteId INTEGER,
 319                         comment TEXT,
 320                         commentTs REAL,
 321                         FOREIGN KEY(siteId) REFERENCES Sites(id) ON DELETE CASCADE)"""
 322
 323
 324         ################################
 325         # Create Autorates
 326         ################################
 327
 328         if db_server == 'mysql':
 329             self.query['createAutoratesTable'] = """CREATE TABLE Autorates (
 330                             id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 331                             playerId INT UNSIGNED NOT NULL, FOREIGN KEY (playerId) REFERENCES Players(id),
 332                             gametypeId SMALLINT UNSIGNED NOT NULL, FOREIGN KEY (gametypeId) REFERENCES Gametypes(id),
 333                             description varchar(50) NOT NULL,
 334                             shortDesc char(8) NOT NULL,
 335                             ratingTime DATETIME NOT NULL,
 336                             handCount int NOT NULL)
 337                         ENGINE=INNODB"""
 338         elif db_server == 'postgresql':
 339             self.query['createAutoratesTable'] = """CREATE TABLE Autorates (
 340                             id BIGSERIAL, PRIMARY KEY (id),
 341                             playerId INT, FOREIGN KEY (playerId) REFERENCES Players(id),
 342                             gametypeId INT, FOREIGN KEY (gametypeId) REFERENCES Gametypes(id),
 343                             description varchar(50),
 344                             shortDesc char(8),
 345                             ratingTime timestamp without time zone,
 346                             handCount int)"""
 347         elif db_server == 'sqlite':
 348             self.query['createAutoratesTable'] = """CREATE TABLE Autorates (
 349                             id INTEGER PRIMARY KEY,
 350                             playerId INT,
 351                             gametypeId INT,
 352                             description TEXT,
 353                             shortDesc TEXT,
 354                             ratingTime REAL,
 355                             handCount int)"""
 356
 357
 358         ################################
 359         # Create Hands
 360         ################################
 361
 362         if db_server == 'mysql':
 363             self.query['createHandsTable'] = """CREATE TABLE Hands (
 364                             id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 365                             tableName VARCHAR(50) NOT NULL,
 366                             siteHandNo BIGINT NOT NULL,
 367                             tourneyId INT UNSIGNED,
 368                             gametypeId SMALLINT UNSIGNED NOT NULL, FOREIGN KEY (gametypeId) REFERENCES Gametypes(id),
 369                             sessionId INT UNSIGNED,
 370                             gameSessionId INT UNSIGNED,
 371                             fileId INT(10) UNSIGNED NOT NULL, FOREIGN KEY (fileId) REFERENCES Files(id),
 372                             startTime DATETIME NOT NULL,
 373                             importTime DATETIME NOT NULL,
 374                             seats TINYINT NOT NULL,
 375                             rush BOOLEAN,
 376                             boardcard1 smallint,  /* 0=none, 1-13=2-Ah 14-26=2-Ad 27-39=2-Ac 40-52=2-As */
 377                             boardcard2 smallint,
 378                             boardcard3 smallint,
 379                             boardcard4 smallint,
 380                             boardcard5 smallint,
 381                             texture smallint,
 382                             runItTwice BOOLEAN,
 383                             playersVpi SMALLINT NOT NULL,         /* num of players vpi */
 384                             playersAtStreet1 SMALLINT NOT NULL,   /* num of players seeing flop/street4 */
 385                             playersAtStreet2 SMALLINT NOT NULL,
 386                             playersAtStreet3 SMALLINT NOT NULL,
 387                             playersAtStreet4 SMALLINT NOT NULL,
 388                             playersAtShowdown SMALLINT NOT NULL,
 389                             street0Raises TINYINT NOT NULL, /* num small bets paid to see flop/street4, including blind */
 390                             street1Raises TINYINT NOT NULL, /* num small bets paid to see turn/street5 */
 391                             street2Raises TINYINT NOT NULL, /* num big bets paid to see river/street6 */
 392                             street3Raises TINYINT NOT NULL, /* num big bets paid to see sd/street7 */
 393                             street4Raises TINYINT NOT NULL, /* num big bets paid to see showdown */
 394                             street1Pot INT,                  /* pot size at flop/street4 */
 395                             street2Pot INT,                  /* pot size at turn/street5 */
 396                             street3Pot INT,                  /* pot size at river/street6 */
 397                             street4Pot INT,                  /* pot size at sd/street7 */
 398                             showdownPot INT,                 /* pot size at sd/street7 */
 399                             comment TEXT,
 400                             commentTs DATETIME)
 401                         ENGINE=INNODB"""
 402         elif db_server == 'postgresql':
 403             self.query['createHandsTable'] = """CREATE TABLE Hands (
 404                             id BIGSERIAL, PRIMARY KEY (id),
 405                             tableName VARCHAR(50) NOT NULL,
 406                             siteHandNo BIGINT NOT NULL,
 407                             tourneyId INT,
 408                             gametypeId INT NOT NULL, FOREIGN KEY (gametypeId) REFERENCES Gametypes(id),
 409                             sessionId INT,
 410                             gameSessionId INT,
 411                             fileId BIGINT NOT NULL, FOREIGN KEY (fileId) REFERENCES Files(id),
 412                             startTime timestamp without time zone NOT NULL,
 413                             importTime timestamp without time zone NOT NULL,
 414                             seats SMALLINT NOT NULL,
 415                             rush BOOLEAN,
 416                             boardcard1 smallint,  /* 0=none, 1-13=2-Ah 14-26=2-Ad 27-39=2-Ac 40-52=2-As */
 417                             boardcard2 smallint,
 418                             boardcard3 smallint,
 419                             boardcard4 smallint,
 420                             boardcard5 smallint,
 421                             texture smallint,
 422                             runItTwice BOOLEAN,
 423                             playersVpi SMALLINT NOT NULL,         /* num of players vpi */
 424                             playersAtStreet1 SMALLINT NOT NULL,   /* num of players seeing flop/street4 */
 425                             playersAtStreet2 SMALLINT NOT NULL,
 426                             playersAtStreet3 SMALLINT NOT NULL,
 427                             playersAtStreet4 SMALLINT NOT NULL,
 428                             playersAtShowdown SMALLINT NOT NULL,
 429                             street0Raises SMALLINT NOT NULL, /* num small bets paid to see flop/street4, including blind */
 430                             street1Raises SMALLINT NOT NULL, /* num small bets paid to see turn/street5 */
 431                             street2Raises SMALLINT NOT NULL, /* num big bets paid to see river/street6 */
 432                             street3Raises SMALLINT NOT NULL, /* num big bets paid to see sd/street7 */
 433                             street4Raises SMALLINT NOT NULL, /* num big bets paid to see showdown */
 434                             street1Pot INT,                 /* pot size at flop/street4 */
 435                             street2Pot INT,                 /* pot size at turn/street5 */
 436                             street3Pot INT,                 /* pot size at river/street6 */
 437                             street4Pot INT,                 /* pot size at sd/street7 */
 438                             showdownPot INT,                /* pot size at sd/street7 */
 439                             comment TEXT,
 440                             commentTs timestamp without time zone)"""
 441         elif db_server == 'sqlite':
 442             self.query['createHandsTable'] = """CREATE TABLE Hands (
 443                             id INTEGER PRIMARY KEY,
 444                             tableName TEXT(50) NOT NULL,
 445                             siteHandNo INT NOT NULL,
 446                             tourneyId INT,
 447                             gametypeId INT NOT NULL,
 448                             sessionId INT,
 449                             gameSessionId INT,
 450                             fileId INT NOT NULL,
 451                             startTime REAL NOT NULL,
 452                             importTime REAL NOT NULL,
 453                             seats INT NOT NULL,
 454                             rush BOOLEAN,
 455                             boardcard1 INT,  /* 0=none, 1-13=2-Ah 14-26=2-Ad 27-39=2-Ac 40-52=2-As */
 456                             boardcard2 INT,
 457                             boardcard3 INT,
 458                             boardcard4 INT,
 459                             boardcard5 INT,
 460                             texture INT,
 461                             runItTwice BOOLEAN,
 462                             playersVpi INT NOT NULL,         /* num of players vpi */
 463                             playersAtStreet1 INT NOT NULL,   /* num of players seeing flop/street4 */
 464                             playersAtStreet2 INT NOT NULL,
 465                             playersAtStreet3 INT NOT NULL,
 466                             playersAtStreet4 INT NOT NULL,
 467                             playersAtShowdown INT NOT NULL,
 468                             street0Raises INT NOT NULL, /* num small bets paid to see flop/street4, including blind */
 469                             street1Raises INT NOT NULL, /* num small bets paid to see turn/street5 */
 470                             street2Raises INT NOT NULL, /* num big bets paid to see river/street6 */
 471                             street3Raises INT NOT NULL, /* num big bets paid to see sd/street7 */
 472                             street4Raises INT NOT NULL, /* num big bets paid to see showdown */
 473                             street1Pot INT,                 /* pot size at flop/street4 */
 474                             street2Pot INT,                 /* pot size at turn/street5 */
 475                             street3Pot INT,                 /* pot size at river/street6 */
 476                             street4Pot INT,                 /* pot size at sd/street7 */
 477                             showdownPot INT,                /* pot size at sd/street7 */
 478                             comment TEXT,
 479                             commentTs REAL)"""
 480
 481         ################################
 482         # Create Hands
 483         ################################
 484
 485         if db_server == 'mysql':
 486             self.query['createBoardsTable'] = """CREATE TABLE Boards (
 487                             id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 488                             handId BIGINT UNSIGNED NOT NULL, FOREIGN KEY (handId) REFERENCES Hands(id),
 489                             boardId smallint,
 490                             boardcard1 smallint,  /* 0=none, 1-13=2-Ah 14-26=2-Ad 27-39=2-Ac 40-52=2-As */
 491                             boardcard2 smallint,
 492                             boardcard3 smallint,
 493                             boardcard4 smallint,
 494                             boardcard5 smallint)
 495                         ENGINE=INNODB"""
 496         elif db_server == 'postgresql':
 497             self.query['createBoardsTable'] = """CREATE TABLE Boards (
 498                             id BIGSERIAL, PRIMARY KEY (id),
 499                             handId BIGINT NOT NULL, FOREIGN KEY (handId) REFERENCES Hands(id),
 500                             boardId smallint,
 501                             boardcard1 smallint,  /* 0=none, 1-13=2-Ah 14-26=2-Ad 27-39=2-Ac 40-52=2-As */
 502                             boardcard2 smallint,
 503                             boardcard3 smallint,
 504                             boardcard4 smallint,
 505                             boardcard5 smallint)"""
 506         elif db_server == 'sqlite':
 507             self.query['createBoardsTable'] = """CREATE TABLE Boards (
 508                             id INTEGER PRIMARY KEY,
 509                             handId INT NOT NULL,
 510                             boardId INT,
 511                             boardcard1 INT,  /* 0=none, 1-13=2-Ah 14-26=2-Ad 27-39=2-Ac 40-52=2-As */
 512                             boardcard2 INT,
 513                             boardcard3 INT,
 514                             boardcard4 INT,
 515                             boardcard5 INT)"""
 516
 517
 518         ################################
 519         # Create TourneyTypes
 520         ################################
 521
 522         if db_server == 'mysql':
 523             self.query['createTourneyTypesTable'] = """CREATE TABLE TourneyTypes (
 524                         id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 525                         siteId SMALLINT UNSIGNED NOT NULL, FOREIGN KEY (siteId) REFERENCES Sites(id),
 526                         currency varchar(4),
 527                         buyIn INT,
 528                         fee INT,
 529                         category varchar(9) NOT NULL,
 530                         limitType char(2) NOT NULL,
 531                         buyInChips INT,
 532                         maxSeats INT,
 533                         rebuy BOOLEAN,
 534                         rebuyCost INT,
 535                         rebuyFee INT,
 536                         rebuyChips INT,
 537                         addOn BOOLEAN,
 538                         addOnCost INT,
 539                         addOnFee INT,
 540                         addOnChips INT,
 541                         knockout BOOLEAN,
 542                         koBounty INT,
 543                         speed varchar(10),
 544                         shootout BOOLEAN,
 545                         matrix BOOLEAN,
 546                         sng BOOLEAN,
 547                         satellite BOOLEAN,
 548                         doubleOrNothing BOOLEAN,
 549                         guarantee INT,
 550                         added INT,
 551                         addedCurrency VARCHAR(4))
 552                         ENGINE=INNODB"""
 553         elif db_server == 'postgresql':
 554             self.query['createTourneyTypesTable'] = """CREATE TABLE TourneyTypes (
 555                         id SERIAL, PRIMARY KEY (id),
 556                         siteId INT NOT NULL, FOREIGN KEY (siteId) REFERENCES Sites(id),
 557                         currency varchar(4),
 558                         buyin INT,
 559                         fee INT,
 560                         category varchar(9),
 561                         limitType char(2),
 562                         buyInChips INT,
 563                         maxSeats INT,
 564                         rebuy BOOLEAN,
 565                         rebuyCost INT,
 566                         rebuyFee INT,
 567                         rebuyChips INT,
 568                         addOn BOOLEAN,
 569                         addOnCost INT,
 570                         addOnFee INT,
 571                         addOnChips INT,
 572                         knockout BOOLEAN,
 573                         koBounty INT,
 574                         speed varchar(10),
 575                         shootout BOOLEAN,
 576                         matrix BOOLEAN,
 577                         sng BOOLEAN,
 578                         satellite BOOLEAN,
 579                         doubleOrNothing BOOLEAN,
 580                         guarantee INT,
 581                         added INT,
 582                         addedCurrency VARCHAR(4))"""
 583         elif db_server == 'sqlite':
 584             self.query['createTourneyTypesTable'] = """CREATE TABLE TourneyTypes (
 585                         id INTEGER PRIMARY KEY,
 586                         siteId INT NOT NULL,
 587                         currency VARCHAR(4),
 588                         buyin INT,
 589                         fee INT,
 590                         category TEXT,
 591                         limitType TEXT,
 592                         buyInChips INT,
 593                         maxSeats INT,
 594                         rebuy BOOLEAN,
 595                         rebuyCost INT,
 596                         rebuyFee INT,
 597                         rebuyChips INT,
 598                         addOn BOOLEAN,
 599                         addOnCost INT,
 600                         addOnFee INT,
 601                         addOnChips INT,
 602                         knockout BOOLEAN,
 603                         koBounty INT,
 604                         speed TEXT,
 605                         shootout BOOLEAN,
 606                         matrix BOOLEAN,
 607                         sng BOOLEAN,
 608                         satellite BOOLEAN,
 609                         doubleOrNothing BOOLEAN,
 610                         guarantee INT,
 611                         added INT,
 612                         addedCurrency VARCHAR(4))"""
 613
 614         ################################
 615         # Create Tourneys
 616         ################################
 617
 618         if db_server == 'mysql':
 619             self.query['createTourneysTable'] = """CREATE TABLE Tourneys (
 620                         id INT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 621                         tourneyTypeId SMALLINT UNSIGNED NOT NULL, FOREIGN KEY (tourneyTypeId) REFERENCES TourneyTypes(id),
 622                         siteTourneyNo BIGINT NOT NULL,
 623                         entries INT,
 624                         prizepool INT,
 625                         startTime DATETIME NOT NULL,
 626                         endTime DATETIME,
 627                         tourneyName varchar(40),
 628                         matrixIdProcessed TINYINT UNSIGNED DEFAULT 0,    /* Mask use : 1=Positionnal Winnings|2=Match1|4=Match2|...|pow(2,n)=Matchn */
 629                         totalRebuyCount INT,
 630                         totalAddOnCount INT,
 631                         comment TEXT,
 632                         commentTs DATETIME)
 633                         ENGINE=INNODB"""
 634         elif db_server == 'postgresql':
 635             self.query['createTourneysTable'] = """CREATE TABLE Tourneys (
 636                         id SERIAL, PRIMARY KEY (id),
 637                         tourneyTypeId INT, FOREIGN KEY (tourneyTypeId) REFERENCES TourneyTypes(id),
 638                         siteTourneyNo BIGINT,
 639                         entries INT,
 640                         prizepool INT,
 641                         startTime timestamp without time zone,
 642                         endTime timestamp without time zone,
 643                         tourneyName varchar(40),
 644                         matrixIdProcessed SMALLINT DEFAULT 0,    /* Mask use : 1=Positionnal Winnings|2=Match1|4=Match2|...|pow(2,n)=Matchn */
 645                         totalRebuyCount INT,
 646                         totalAddOnCount INT,
 647                         comment TEXT,
 648                         commentTs timestamp without time zone)"""
 649         elif db_server == 'sqlite':
 650             self.query['createTourneysTable'] = """CREATE TABLE Tourneys (
 651                         id INTEGER PRIMARY KEY,
 652                         tourneyTypeId INT,
 653                         siteTourneyNo INT,
 654                         entries INT,
 655                         prizepool INT,
 656                         startTime REAL,
 657                         endTime REAL,
 658                         tourneyName TEXT,
 659                         matrixIdProcessed INT UNSIGNED DEFAULT 0,    /* Mask use : 1=Positionnal Winnings|2=Match1|4=Match2|...|pow(2,n)=Matchn */
 660                         totalRebuyCount INT,
 661                         totalAddOnCount INT,
 662                         comment TEXT,
 663                         commentTs REAL)"""
 664         ################################
 665         # Create HandsPlayers
 666         ################################
 667
 668         if db_server == 'mysql':
 669             self.query['createHandsPlayersTable'] = """CREATE TABLE HandsPlayers (
 670                         id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
 671                         handId BIGINT UNSIGNED NOT NULL, FOREIGN KEY (handId) REFERENCES Hands(id),
 672                         playerId INT UNSIGNED NOT NULL, FOREIGN KEY (playerId) REFERENCES Players(id),
 673                         startCash INT NOT NULL,
 674                         position CHAR(1),
 675                         seatNo SMALLINT NOT NULL,
 676                         sitout BOOLEAN NOT NULL,
 677                         wentAllInOnStreet SMALLINT,
 678
 679                         card1 smallint NOT NULL,  /* 0=none, 1-13=2-Ah 14-26=2-Ad 27-39=2-Ac 40-52=2-As */
 680                         card2 smallint NOT NULL,
 681                         card3 smallint,
 682                         card4 smallint,
 683                         card5 smallint,
 684                         card6 smallint,
 685                         card7 smallint,
 686                         card8 smallint,  /* cards 8-20 for draw hands */
 687                         card9 smallint,
 688                         card10 smallint,
 689                         card11 smallint,
 690                         card12 smallint,
 691                         card13 smallint,
 692                         card14 smallint,
 693                         card15 smallint,
 694                         card16 smallint,
 695                         card17 smallint,
 696                         card18 smallint,
 697                         card19 smallint,
 698                         card20 smallint,
 699                         startCards smallint,
 700
 701                         ante INT,
 702                         winnings int NOT NULL,
 703                         rake int NOT NULL,
 704                         totalProfit INT,
 705                         comment text,
 706                         commentTs DATETIME,
 707                         tourneysPlayersId BIGINT UNSIGNED, FOREIGN KEY (tourneysPlayersId) REFERENCES TourneysPlayers(id),
 708
 709                         wonWhenSeenStreet1 FLOAT,
 710                         wonWhenSeenStreet2 FLOAT,
 711                         wonWhenSeenStreet3 FLOAT,
 712                         wonWhenSeenStreet4 FLOAT,
 713                         wonAtSD FLOAT,
 714
 715                         street0VPI BOOLEAN,
 716                         street0Aggr BOOLEAN,
 717                         street0_3BChance BOOLEAN,
 718                         street0_3BDone BOOLEAN,
 719                         street0_4BChance BOOLEAN,
 720                         street0_C4BChance BOOLEAN,
 721                         street0_4BDone BOOLEAN,
 722                         street0_C4BDone BOOLEAN,
 723                         street0_FoldTo3BChance BOOLEAN,
 724                         street0_FoldTo3BDone BOOLEAN,
 725                         street0_FoldTo4BChance BOOLEAN,
 726                         street0_FoldTo4BDone BOOLEAN,
 727                         street0_SqueezeChance BOOLEAN,
 728                         street0_SqueezeDone BOOLEAN,
 729
 730                         raiseToStealChance BOOLEAN,
 731                         raiseToStealDone BOOLEAN,
 732                         success_Steal BOOLEAN,
 733
 734                         street1Seen BOOLEAN,
 735                         street2Seen BOOLEAN,
 736                         street3Seen BOOLEAN,
 737                         street4Seen BOOLEAN,
 738                         sawShowdown BOOLEAN,
 739                         showed      BOOLEAN,
 740
 741                         street1Aggr BOOLEAN,
 742                         street2Aggr BOOLEAN,
 743                         street3Aggr BOOLEAN,
 744                         street4Aggr BOOLEAN,
 745
 746                         otherRaisedStreet0 BOOLEAN,
 747                         otherRaisedStreet1 BOOLEAN,
 748                         otherRaisedStreet2 BOOLEAN,
 749                         otherRaisedStreet3 BOOLEAN,
 750                         otherRaisedStreet4 BOOLEAN,
 751                         foldToOtherRaisedStreet0 BOOLEAN,
 752                         foldToOtherRaisedStreet1 BOOLEAN,
 753                         foldToOtherRaisedStreet2 BOOLEAN,
 754                         foldToOtherRaisedStreet3 BOOLEAN,
 755                         foldToOtherRaisedStreet4 BOOLEAN,
 756
 757                         raiseFirstInChance BOOLEAN,
 758                         raisedFirstIn BOOLEAN,
 759                         foldBbToStealChance BOOLEAN,
 760                         foldedBbToSteal BOOLEAN,
 761                         foldSbToStealChance BOOLEAN,
 762                         foldedSbToSteal BOOLEAN,
 763
 764                         street1CBChance BOOLEAN,
 765                         street1CBDone BOOLEAN,
 766                         street2CBChance BOOLEAN,
 767                         street2CBDone BOOLEAN,
 768                         street3CBChance BOOLEAN,
 769                         street3CBDone BOOLEAN,
 770                         street4CBChance BOOLEAN,
 771                         street4CBDone BOOLEAN,
 772
 773                         foldToStreet1CBChance BOOLEAN,
 774                         foldToStreet1CBDone BOOLEAN,
 775                         foldToStreet2CBChance BOOLEAN,
 776                         foldToStreet2CBDone BOOLEAN,
 777                         foldToStreet3CBChance BOOLEAN,
 778                         foldToStreet3CBDone BOOLEAN,
 779                         foldToStreet4CBChance BOOLEAN,
 780                         foldToStreet4CBDone BOOLEAN,
 781
 782                         street1CheckCallRaiseChance BOOLEAN,
 783                         street1CheckCallRaiseDone BOOLEAN,
 784                         street2CheckCallRaiseChance BOOLEAN,
 785                         street2CheckCallRaiseDone BOOLEAN,
 786                         street3CheckCallRaiseChance BOOLEAN,
 787                         street3CheckCallRaiseDone BOOLEAN,
 788                         street4CheckCallRaiseChance BOOLEAN,
 789                         street4CheckCallRaiseDone BOOLEAN,
 790
 791                         street0Calls TINYINT,
 792                         street1Calls TINYINT,
 793                         street2Calls TINYINT,
 794                         street3Calls TINYINT,
 795                         street4Calls TINYINT,
 796                         street0Bets TINYINT,
 797                         street1Bets TINYINT,
 798                         street2Bets TINYINT,
 799                         street3Bets TINYINT,
 800                         street4Bets TINYINT,
 801                         street0Raises TINYINT,
 802                         street1Raises TINYINT,
 803                         street2Raises TINYINT,
 804                         street3Raises TINYINT,
 805                         street4Raises TINYINT,
 806
 807                         actionString VARCHAR(15))
 808                         ENGINE=INNODB"""
 809         elif db_server == 'postgresql':
 810             self.query['createHandsPlayersTable'] = """CREATE TABLE HandsPlayers (
 811                         id BIGSERIAL, PRIMARY KEY (id),
 812                         handId BIGINT NOT NULL, FOREIGN KEY (handId) REFERENCES Hands(id),
 813                         playerId INT NOT NULL, FOREIGN KEY (playerId) REFERENCES Players(id),
 814                         startCash INT NOT NULL,
 815                         position CHAR(1),
 816                         seatNo SMALLINT NOT NULL,
 817                         sitout BOOLEAN NOT NULL,
 818                         wentAllInOnStreet SMALLINT,
 819
 820                         card1 smallint NOT NULL,  /* 0=none, 1-13=2-Ah 14-26=2-Ad 27-39=2-Ac 40-52=2-As */
 821                         card2 smallint NOT NULL,
 822                         card3 smallint,
 823                         card4 smallint,
 824                         card5 smallint,
 825                         card6 smallint,
 826                         card7 smallint,
 827                         card8 smallint,  /* cards 8-20 for draw hands */
 828                         card9 smallint,
 829                         card10 smallint,
 830                         card11 smallint,
 831                         card12 smallint,
 832                         card13 smallint,
 833                         card14 smallint,
 834                         card15 smallint,
 835                         card16 smallint,
 836                         card17 smallint,
 837                         card18 smallint,
 838                         card19 smallint,
 839                         card20 smallint,
 840                         startCards smallint,
 841
 842                         ante INT,
 843                         winnings int NOT NULL,
 844                         rake int NOT NULL,
 845                         totalProfit INT,
 846                         comment text,
 847                         commentTs timestamp without time zone,
 848                         tourneysPlayersId BIGINT, FOREIGN KEY (tourneysPlayersId) REFERENCES TourneysPlayers(id),
 849
 850                         wonWhenSeenStreet1 FLOAT,
 851                         wonWhenSeenStreet2 FLOAT,
 852                         wonWhenSeenStreet3 FLOAT,
 853                         wonWhenSeenStreet4 FLOAT,
 854                         wonAtSD FLOAT,
 855
 856                         street0VPI BOOLEAN,
 857                         street0Aggr BOOLEAN,
 858                         street0_3BChance BOOLEAN,
 859                         street0_3BDone BOOLEAN,
 860                         street0_4BChance BOOLEAN,
 861                         street0_4BDone BOOLEAN,
 862                         street0_C4BChance BOOLEAN,
 863                         street0_C4BDone BOOLEAN,
 864                         street0_FoldTo3BChance BOOLEAN,
 865                         street0_FoldTo3BDone BOOLEAN,
 866                         street0_FoldTo4BChance BOOLEAN,
 867                         street0_FoldTo4BDone BOOLEAN,
 868                         street0_SqueezeChance BOOLEAN,
 869                         street0_SqueezeDone BOOLEAN,
 870
 871                         raiseToStealChance BOOLEAN,
 872                         raiseToStealDone BOOLEAN,
 873                         success_Steal BOOLEAN,
 874
 875                         street1Seen BOOLEAN,
 876                         street2Seen BOOLEAN,
 877                         street3Seen BOOLEAN,
 878                         street4Seen BOOLEAN,
 879                         sawShowdown BOOLEAN,
 880                         showed      BOOLEAN,
 881
 882                         street1Aggr BOOLEAN,
 883                         street2Aggr BOOLEAN,
 884                         street3Aggr BOOLEAN,
 885                         street4Aggr BOOLEAN,
 886
 887                         otherRaisedStreet0 BOOLEAN,
 888                         otherRaisedStreet1 BOOLEAN,
 889                         otherRaisedStreet2 BOOLEAN,
 890                         otherRaisedStreet3 BOOLEAN,
 891                         otherRaisedStreet4 BOOLEAN,
 892                         foldToOtherRaisedStreet0 BOOLEAN,
 893                         foldToOtherRaisedStreet1 BOOLEAN,
 894                         foldToOtherRaisedStreet2 BOOLEAN,
 895                         foldToOtherRaisedStreet3 BOOLEAN,
 896                         foldToOtherRaisedStreet4 BOOLEAN,
 897
 898                         raiseFirstInChance BOOLEAN,
 899                         raisedFirstIn BOOLEAN,
 900                         foldBbToStealChance BOOLEAN,
 901                         foldedBbToSteal BOOLEAN,
 902                         foldSbToStealChance BOOLEAN,
 903                         foldedSbToSteal BOOLEAN,
 904
 905                         street1CBChance BOOLEAN,
 906                         street1CBDone BOOLEAN,
 907                         street2CBChance BOOLEAN,
 908                         street2CBDone BOOLEAN,
 909                         street3CBChance BOOLEAN,
 910                         street3CBDone BOOLEAN,
 911                         street4CBChance BOOLEAN,
 912                         street4CBDone BOOLEAN,
 913
 914                         foldToStreet1CBChance BOOLEAN,
 915                         foldToStreet1CBDone BOOLEAN,
 916                         foldToStreet2CBChance BOOLEAN,
 917                         foldToStreet2CBDone BOOLEAN,
 918                         foldToStreet3CBChance BOOLEAN,
 919                         foldToStreet3CBDone BOOLEAN,
 920                         foldToStreet4CBChance BOOLEAN,
 921                         foldToStreet4CBDone BOOLEAN,
 922
 923                         street1CheckCallRaiseChance BOOLEAN,
 924                         street1CheckCallRaiseDone BOOLEAN,
 925                         street2CheckCallRaiseChance BOOLEAN,
 926                         street2CheckCallRaiseDone BOOLEAN,
 927                         street3CheckCallRaiseChance BOOLEAN,
 928                         street3CheckCallRaiseDone BOOLEAN,
 929                         street4CheckCallRaiseChance BOOLEAN,
 930                         street4CheckCallRaiseDone BOOLEAN,
 931
 932                         street0Calls SMALLINT,
 933                         street1Calls SMALLINT,
 934                         street2Calls SMALLINT,
 935                         street3Calls SMALLINT,
 936                         street4Calls SMALLINT,
 937                         street0Bets SMALLINT,
 938                         street1Bets SMALLINT,
 939                         street2Bets SMALLINT,
 940                         street3Bets SMALLINT,
 941                         street4Bets SMALLINT,
 942                         street0Raises SMALLINT,
 943                         street1Raises SMALLINT,
 944                         street2Raises SMALLINT,
 945                         street3Raises SMALLINT,
 946                         street4Raises SMALLINT,
 947
 948                         actionString VARCHAR(15))"""
 949         elif db_server == 'sqlite':
 950             self.query['createHandsPlayersTable'] = """CREATE TABLE HandsPlayers (
 951                         id INTEGER PRIMARY KEY,
 952                         handId INT NOT NULL,
 953                         playerId INT NOT NULL,
 954                         startCash INT NOT NULL,
 955                         position TEXT,
 956                         seatNo INT NOT NULL,
 957                         sitout BOOLEAN NOT NULL,
 958                         wentAllInOnStreet INT,
 959
 960                         card1 INT NOT NULL,  /* 0=none, 1-13=2-Ah 14-26=2-Ad 27-39=2-Ac 40-52=2-As */
 961                         card2 INT NOT NULL,
 962                         card3 INT,
 963                         card4 INT,
 964                         card5 INT,
 965                         card6 INT,
 966                         card7 INT,
 967                         card8 INT,  /* cards 8-20 for draw hands */
 968                         card9 INT,
 969                         card10 INT,
 970                         card11 INT,
 971                         card12 INT,
 972                         card13 INT,
 973                         card14 INT,
 974                         card15 INT,
 975                         card16 INT,
 976                         card17 INT,
 977                         card18 INT,
 978                         card19 INT,
 979                         card20 INT,
 980                         startCards INT,
 981
 982                         ante INT,
 983                         winnings INT NOT NULL,
 984                         rake INT NOT NULL,
 985                         totalProfit INT,
 986                         comment TEXT,
 987                         commentTs REAL,
 988                         tourneysPlayersId INT,
 989
 990                         wonWhenSeenStreet1 REAL,
 991                         wonWhenSeenStreet2 REAL,
 992                         wonWhenSeenStreet3 REAL,
 993                         wonWhenSeenStreet4 REAL,
 994                         wonAtSD REAL,
 995
 996                         street0VPI INT,
 997                         street0Aggr INT,
 998                         street0_3BChance INT,
 999                         street0_3BDone INT,
1000                         street0_4BChance INT,
1001                         street0_4BDone INT,
1002                         street0_C4BChance INT,
1003                         street0_C4BDone INT,
1004                         street0_FoldTo3BChance INT,
1005                         street0_FoldTo3BDone INT,
1006                         street0_FoldTo4BChance INT,
1007                         street0_FoldTo4BDone INT,
1008                         street0_SqueezeChance INT,
1009                         street0_SqueezeDone INT,
1010
1011                         raiseToStealChance INT,
1012                         raiseToStealDone INT,
1013                         success_Steal INT,
1014
1015                         street1Seen INT,
1016                         street2Seen INT,
1017                         street3Seen INT,
1018                         street4Seen INT,
1019                         sawShowdown INT,
1020                         showed      INT,
1021
1022                         street1Aggr INT,
1023                         street2Aggr INT,
1024                         street3Aggr INT,
1025                         street4Aggr INT,
1026
1027                         otherRaisedStreet0 INT,
1028                         otherRaisedStreet1 INT,
1029                         otherRaisedStreet2 INT,
1030                         otherRaisedStreet3 INT,
1031                         otherRaisedStreet4 INT,
1032                         foldToOtherRaisedStreet0 INT,
1033                         foldToOtherRaisedStreet1 INT,
1034                         foldToOtherRaisedStreet2 INT,
1035                         foldToOtherRaisedStreet3 INT,
1036                         foldToOtherRaisedStreet4 INT,
1037
1038                         raiseFirstInChance INT,
1039                         raisedFirstIn INT,
1040                         foldBbToStealChance INT,
1041                         foldedBbToSteal INT,
1042                         foldSbToStealChance INT,
1043                         foldedSbToSteal INT,
1044
1045                         street1CBChance INT,
1046                         street1CBDone INT,
1047                         street2CBChance INT,
1048                         street2CBDone INT,
1049                         street3CBChance INT,
1050                         street3CBDone INT,
1051                         street4CBChance INT,
1052                         street4CBDone INT,
1053
1054                         foldToStreet1CBChance INT,
1055                         foldToStreet1CBDone INT,
1056                         foldToStreet2CBChance INT,
1057                         foldToStreet2CBDone INT,
1058                         foldToStreet3CBChance INT,
1059                         foldToStreet3CBDone INT,
1060                         foldToStreet4CBChance INT,
1061                         foldToStreet4CBDone INT,
1062
1063                         street1CheckCallRaiseChance INT,
1064                         street1CheckCallRaiseDone INT,
1065                         street2CheckCallRaiseChance INT,
1066                         street2CheckCallRaiseDone INT,
1067                         street3CheckCallRaiseChance INT,
1068                         street3CheckCallRaiseDone INT,
1069                         street4CheckCallRaiseChance INT,
1070                         street4CheckCallRaiseDone INT,
1071
1072                         street0Calls INT,
1073                         street1Calls INT,
1074                         street2Calls INT,
1075                         street3Calls INT,
1076                         street4Calls INT,
1077                         street0Bets INT,
1078                         street1Bets INT,
1079                         street2Bets INT,
1080                         street3Bets INT,
1081                         street4Bets INT,
1082                         street0Raises INT,
1083                         street1Raises INT,
1084                         street2Raises INT,
1085                         street3Raises INT,
1086                         street4Raises INT,
1087                         actionString VARCHAR(15))
1088                         """
1089
1090
1091         ################################
1092         # Create TourneysPlayers
1093         ################################
1094
1095         if db_server == 'mysql':
1096             self.query['createTourneysPlayersTable'] = """CREATE TABLE TourneysPlayers (
1097                         id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
1098                         tourneyId INT UNSIGNED NOT NULL, FOREIGN KEY (tourneyId) REFERENCES Tourneys(id),
1099                         playerId INT UNSIGNED NOT NULL, FOREIGN KEY (playerId) REFERENCES Players(id),
1100                         rank INT,
1101                         winnings INT,
1102                         winningsCurrency VARCHAR(4),
1103                         rebuyCount INT,
1104                         addOnCount INT,
1105                         koCount INT,
1106                         comment TEXT,
1107                         commentTs DATETIME)
1108                         ENGINE=INNODB"""
1109         elif db_server == 'postgresql':
1110             self.query['createTourneysPlayersTable'] = """CREATE TABLE TourneysPlayers (
1111                         id BIGSERIAL, PRIMARY KEY (id),
1112                         tourneyId INT, FOREIGN KEY (tourneyId) REFERENCES Tourneys(id),
1113                         playerId INT, FOREIGN KEY (playerId) REFERENCES Players(id),
1114                         rank INT,
1115                         winnings INT,
1116                         winningsCurrency VARCHAR(4),
1117                         rebuyCount INT,
1118                         addOnCount INT,
1119                         koCount INT,
1120                         comment TEXT,
1121                         commentTs timestamp without time zone)"""
1122         elif db_server == 'sqlite':
1123             self.query['createTourneysPlayersTable'] = """CREATE TABLE TourneysPlayers (
1124                         id INTEGER PRIMARY KEY,
1125                         tourneyId INT,
1126                         playerId INT,
1127                         rank INT,
1128                         winnings INT,
1129                         winningsCurrency VARCHAR(4),
1130                         rebuyCount INT,
1131                         addOnCount INT,
1132                         koCount INT,
1133                         comment TEXT,
1134                         commentTs timestamp without time zone,
1135                         FOREIGN KEY (tourneyId) REFERENCES Tourneys(id),
1136                         FOREIGN KEY (playerId) REFERENCES Players(id)
1137                         )"""
1138
1139
1140         ################################
1141         # Create HandsActions
1142         ################################
1143
1144         if db_server == 'mysql':
1145             self.query['createHandsActionsTable'] = """CREATE TABLE HandsActions (
1146                         id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
1147                         handId BIGINT UNSIGNED NOT NULL, FOREIGN KEY (handId) REFERENCES Hands(id),
1148                         playerId INT UNSIGNED NOT NULL, FOREIGN KEY (playerId) REFERENCES Players(id),
1149                         street SMALLINT NOT NULL,
1150                         actionNo SMALLINT NOT NULL,
1151                         streetActionNo SMALLINT NOT NULL,
1152                         actionId SMALLINT UNSIGNED NOT NULL, FOREIGN KEY (actionId) REFERENCES Actions(id),
1153                         amount INT NOT NULL,
1154                         raiseTo INT NOT NULL,
1155                         amountCalled INT NOT NULL,
1156                         numDiscarded SMALLINT NOT NULL,
1157                         cardsDiscarded varchar(14),
1158                         allIn BOOLEAN NOT NULL)
1159                         ENGINE=INNODB"""
1160         elif db_server == 'postgresql':
1161             self.query['createHandsActionsTable'] = """CREATE TABLE HandsActions (
1162                         id BIGSERIAL, PRIMARY KEY (id),
1163                         handId BIGINT NOT NULL, FOREIGN KEY (handId) REFERENCES Hands(id),
1164                         playerId INT NOT NULL, FOREIGN KEY (playerId) REFERENCES Players(id),
1165                         street SMALLINT,
1166                         actionNo SMALLINT,
1167                         streetActionNo SMALLINT,
1168                         actionId SMALLINT, FOREIGN KEY (actionId) REFERENCES Actions(id),
1169                         amount INT,
1170                         raiseTo INT,
1171                         amountCalled INT,
1172                         numDiscarded SMALLINT,
1173                         cardsDiscarded varchar(14),
1174                         allIn BOOLEAN)"""
1175         elif db_server == 'sqlite':
1176             self.query['createHandsActionsTable'] = """CREATE TABLE HandsActions (
1177                         id INTEGER PRIMARY KEY,
1178                         handId INT NOT NULL,
1179                         playerId INT NOT NULL,
1180                         street SMALLINT,
1181                         actionNo SMALLINT,
1182                         streetActionNo SMALLINT,
1183                         actionId SMALLINT,
1184                         amount INT,
1185                         raiseTo INT,
1186                         amountCalled INT,
1187                         numDiscarded SMALLINT,
1188                         cardsDiscarded TEXT,
1189                         allIn BOOLEAN
1190                         )"""
1191
1192         ################################
1193         # Create Files
1194         ################################
1195
1196         if db_server == 'mysql':
1197             self.query['createFilesTable'] = """CREATE TABLE Files (
1198                         id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
1199                         file text NOT NULL,
1200                         site VARCHAR(32),
1201                         type VARCHAR(7),
1202                         startTime DATETIME NOT NULL,
1203                         lastUpdate DATETIME NOT NULL,
1204                         endTime DATETIME,
1205                         hands INT,
1206                         stored INT,
1207                         dups INT,
1208                         partial INT,
1209                         errs INT,
1210                         ttime100 INT,
1211                         finished BOOLEAN)
1212                         ENGINE=INNODB"""
1213         elif db_server == 'postgresql':
1214             self.query['createFilesTable'] = """CREATE TABLE Files (
1215                         id BIGSERIAL, PRIMARY KEY (id),
1216                         file TEXT NOT NULL,
1217                         site VARCHAR(32),
1218                         type VARCHAR(7),
1219                         startTime timestamp without time zone NOT NULL,
1220                         lastUpdate timestamp without time zone NOT NULL,
1221                         endTime timestamp without time zone,
1222                         hands INT,
1223                         stored INT,
1224                         dups INT,
1225                         partial INT,
1226                         errs INT,
1227                         ttime100 INT,
1228                         finished BOOLEAN)"""
1229         elif db_server == 'sqlite':
1230             self.query['createFilesTable'] = """CREATE TABLE Files (
1231                         id INTEGER PRIMARY KEY,
1232                         file TEXT NOT NULL,
1233                         site VARCHAR(32),
1234                         type VARCHAR(7),
1235                         startTime timestamp NOT NULL,
1236                         lastUpdate timestamp NOT NULL,
1237                         endTime timestamp,
1238                         hands INT,
1239                         stored INT,
1240                         dups INT,
1241                         partial INT,
1242                         errs INT,
1243                         ttime100 INT,
1244                         finished BOOLEAN
1245                         )"""
1246
1247         ################################
1248         # Create HudCache
1249         ################################
1250
1251         if db_server == 'mysql':
1252             self.query['createHudCacheTable'] = """CREATE TABLE HudCache (
1253                         id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
1254                         gametypeId SMALLINT UNSIGNED NOT NULL, FOREIGN KEY (gametypeId) REFERENCES Gametypes(id),
1255                         playerId INT UNSIGNED NOT NULL, FOREIGN KEY (playerId) REFERENCES Players(id),
1256                         activeSeats SMALLINT NOT NULL,
1257                         position CHAR(1),
1258                         tourneyTypeId SMALLINT UNSIGNED, FOREIGN KEY (tourneyTypeId) REFERENCES TourneyTypes(id),
1259                         styleKey CHAR(7) NOT NULL,  /* 1st char is style (A/T/H/S), other 6 are the key */
1260                         HDs INT NOT NULL,
1261
1262                         wonWhenSeenStreet1 FLOAT,
1263                         wonWhenSeenStreet2 FLOAT,
1264                         wonWhenSeenStreet3 FLOAT,
1265                         wonWhenSeenStreet4 FLOAT,
1266                         wonAtSD FLOAT,
1267
1268                         street0VPI INT,
1269                         street0Aggr INT,
1270                         street0_3BChance INT,
1271                         street0_3BDone INT,
1272                         street0_4BChance INT,
1273                         street0_4BDone INT,
1274                         street0_C4BChance INT,
1275                         street0_C4BDone INT,
1276                         street0_FoldTo3BChance INT,
1277                         street0_FoldTo3BDone INT,
1278                         street0_FoldTo4BChance INT,
1279                         street0_FoldTo4BDone INT,
1280                         street0_SqueezeChance INT,
1281                         street0_SqueezeDone INT,
1282
1283                         raiseToStealChance INT,
1284                         raiseToStealDone INT,
1285                         success_Steal INT,
1286
1287
1288                         street1Seen INT,
1289                         street2Seen INT,
1290                         street3Seen INT,
1291                         street4Seen INT,
1292                         sawShowdown INT,
1293
1294                         street1Aggr INT,
1295                         street2Aggr INT,
1296                         street3Aggr INT,
1297                         street4Aggr INT,
1298
1299                         otherRaisedStreet0 INT,
1300                         otherRaisedStreet1 INT,
1301                         otherRaisedStreet2 INT,
1302                         otherRaisedStreet3 INT,
1303                         otherRaisedStreet4 INT,
1304                         foldToOtherRaisedStreet0 INT,
1305                         foldToOtherRaisedStreet1 INT,
1306                         foldToOtherRaisedStreet2 INT,
1307                         foldToOtherRaisedStreet3 INT,
1308                         foldToOtherRaisedStreet4 INT,
1309
1310                         raiseFirstInChance INT,
1311                         raisedFirstIn INT,
1312                         foldBbToStealChance INT,
1313                         foldedBbToSteal INT,
1314                         foldSbToStealChance INT,
1315                         foldedSbToSteal INT,
1316
1317                         street1CBChance INT,
1318                         street1CBDone INT,
1319                         street2CBChance INT,
1320                         street2CBDone INT,
1321                         street3CBChance INT,
1322                         street3CBDone INT,
1323                         street4CBChance INT,
1324                         street4CBDone INT,
1325
1326                         foldToStreet1CBChance INT,
1327                         foldToStreet1CBDone INT,
1328                         foldToStreet2CBChance INT,
1329                         foldToStreet2CBDone INT,
1330                         foldToStreet3CBChance INT,
1331                         foldToStreet3CBDone INT,
1332                         foldToStreet4CBChance INT,
1333                         foldToStreet4CBDone INT,
1334
1335                         totalProfit INT,
1336
1337                         street1CheckCallRaiseChance INT,
1338                         street1CheckCallRaiseDone INT,
1339                         street2CheckCallRaiseChance INT,
1340                         street2CheckCallRaiseDone INT,
1341                         street3CheckCallRaiseChance INT,
1342                         street3CheckCallRaiseDone INT,
1343                         street4CheckCallRaiseChance INT,
1344                         street4CheckCallRaiseDone INT,
1345
1346                         street0Calls INT,
1347                         street1Calls INT,
1348                         street2Calls INT,
1349                         street3Calls INT,
1350                         street4Calls INT,
1351                         street0Bets INT,
1352                         street1Bets INT,
1353                         street2Bets INT,
1354                         street3Bets INT,
1355                         street4Bets INT,
1356                         street0Raises INT,
1357                         street1Raises INT,
1358                         street2Raises INT,
1359                         street3Raises INT,
1360                         street4Raises INT)
1361
1362                         ENGINE=INNODB"""
1363         elif db_server == 'postgresql':
1364             self.query['createHudCacheTable'] = """CREATE TABLE HudCache (
1365                         id BIGSERIAL, PRIMARY KEY (id),
1366                         gametypeId INT, FOREIGN KEY (gametypeId) REFERENCES Gametypes(id),
1367                         playerId INT, FOREIGN KEY (playerId) REFERENCES Players(id),
1368                         activeSeats SMALLINT,
1369                         position CHAR(1),
1370                         tourneyTypeId INT, FOREIGN KEY (tourneyTypeId) REFERENCES TourneyTypes(id),
1371                         styleKey CHAR(7) NOT NULL,  /* 1st char is style (A/T/H/S), other 6 are the key */
1372                         HDs INT,
1373
1374                         wonWhenSeenStreet1 FLOAT,
1375                         wonWhenSeenStreet2 FLOAT,
1376                         wonWhenSeenStreet3 FLOAT,
1377                         wonWhenSeenStreet4 FLOAT,
1378                         wonAtSD FLOAT,
1379
1380                         street0VPI INT,
1381                         street0Aggr INT,
1382                         street0_3BChance INT,
1383                         street0_3BDone INT,
1384                         street0_4BChance INT,
1385                         street0_4BDone INT,
1386                         street0_C4BChance INT,
1387                         street0_C4BDone INT,
1388                         street0_FoldTo3BChance INT,
1389                         street0_FoldTo3BDone INT,
1390                         street0_FoldTo4BChance INT,
1391                         street0_FoldTo4BDone INT,
1392                         street0_SqueezeChance INT,
1393                         street0_SqueezeDone INT,
1394
1395                         raiseToStealChance INT,
1396                         raiseToStealDone INT,
1397                         success_Steal INT,
1398
1399                         street1Seen INT,
1400                         street2Seen INT,
1401                         street3Seen INT,
1402                         street4Seen INT,
1403                         sawShowdown INT,
1404                         street1Aggr INT,
1405                         street2Aggr INT,
1406                         street3Aggr INT,
1407                         street4Aggr INT,
1408
1409                         otherRaisedStreet0 INT,
1410                         otherRaisedStreet1 INT,
1411                         otherRaisedStreet2 INT,
1412                         otherRaisedStreet3 INT,
1413                         otherRaisedStreet4 INT,
1414                         foldToOtherRaisedStreet0 INT,
1415                         foldToOtherRaisedStreet1 INT,
1416                         foldToOtherRaisedStreet2 INT,
1417                         foldToOtherRaisedStreet3 INT,
1418                         foldToOtherRaisedStreet4 INT,
1419
1420                         raiseFirstInChance INT,
1421                         raisedFirstIn INT,
1422                         foldBbToStealChance INT,
1423                         foldedBbToSteal INT,
1424                         foldSbToStealChance INT,
1425                         foldedSbToSteal INT,
1426
1427                         street1CBChance INT,
1428                         street1CBDone INT,
1429                         street2CBChance INT,
1430                         street2CBDone INT,
1431                         street3CBChance INT,
1432                         street3CBDone INT,
1433                         street4CBChance INT,
1434                         street4CBDone INT,
1435
1436                         foldToStreet1CBChance INT,
1437                         foldToStreet1CBDone INT,
1438                         foldToStreet2CBChance INT,
1439                         foldToStreet2CBDone INT,
1440                         foldToStreet3CBChance INT,
1441                         foldToStreet3CBDone INT,
1442                         foldToStreet4CBChance INT,
1443                         foldToStreet4CBDone INT,
1444
1445                         totalProfit INT,
1446
1447                         street1CheckCallRaiseChance INT,
1448                         street1CheckCallRaiseDone INT,
1449                         street2CheckCallRaiseChance INT,
1450                         street2CheckCallRaiseDone INT,
1451                         street3CheckCallRaiseChance INT,
1452                         street3CheckCallRaiseDone INT,
1453                         street4CheckCallRaiseChance INT,
1454                         street4CheckCallRaiseDone INT,
1455
1456                         street0Calls INT,
1457                         street1Calls INT,
1458                         street2Calls INT,
1459                         street3Calls INT,
1460                         street4Calls INT,
1461                         street0Bets INT,
1462                         street1Bets INT,
1463                         street2Bets INT,
1464                         street3Bets INT,
1465                         street4Bets INT,
1466                         street0Raises INT,
1467                         street1Raises INT,
1468                         street2Raises INT,
1469                         street3Raises INT,
1470                         street4Raises INT)
1471                         """
1472         elif db_server == 'sqlite':
1473             self.query['createHudCacheTable'] = """CREATE TABLE HudCache (
1474                         id INTEGER PRIMARY KEY,
1475                         gametypeId INT,
1476                         playerId INT,
1477                         activeSeats INT,
1478                         position TEXT,
1479                         tourneyTypeId INT,
1480                         styleKey TEXT NOT NULL,  /* 1st char is style (A/T/H/S), other 6 are the key */
1481                         HDs INT,
1482
1483                         wonWhenSeenStreet1 REAL,
1484                         wonWhenSeenStreet2 REAL,
1485                         wonWhenSeenStreet3 REAL,
1486                         wonWhenSeenStreet4 REAL,
1487                         wonAtSD REAL,
1488
1489                         street0VPI INT,
1490                         street0Aggr INT,
1491                         street0_3BChance INT,
1492                         street0_3BDone INT,
1493                         street0_4BChance INT,
1494                         street0_4BDone INT,
1495                         street0_C4BChance INT,
1496                         street0_C4BDone INT,
1497                         street0_FoldTo3BChance INT,
1498                         street0_FoldTo3BDone INT,
1499                         street0_FoldTo4BChance INT,
1500                         street0_FoldTo4BDone INT,
1501                         street0_SqueezeChance INT,
1502                         street0_SqueezeDone INT,
1503
1504                         raiseToStealChance INT,
1505                         raiseToStealDone INT,
1506                         success_Steal INT,
1507
1508                         street1Seen INT,
1509                         street2Seen INT,
1510                         street3Seen INT,
1511                         street4Seen INT,
1512                         sawShowdown INT,
1513                         street1Aggr INT,
1514                         street2Aggr INT,
1515                         street3Aggr INT,
1516                         street4Aggr INT,
1517
1518                         otherRaisedStreet0 INT,
1519                         otherRaisedStreet1 INT,
1520                         otherRaisedStreet2 INT,
1521                         otherRaisedStreet3 INT,
1522                         otherRaisedStreet4 INT,
1523                         foldToOtherRaisedStreet0 INT,
1524                         foldToOtherRaisedStreet1 INT,
1525                         foldToOtherRaisedStreet2 INT,
1526                         foldToOtherRaisedStreet3 INT,
1527                         foldToOtherRaisedStreet4 INT,
1528
1529                         raiseFirstInChance INT,
1530                         raisedFirstIn INT,
1531                         foldBbToStealChance INT,
1532                         foldedBbToSteal INT,
1533                         foldSbToStealChance INT,
1534                         foldedSbToSteal INT,
1535
1536                         street1CBChance INT,
1537                         street1CBDone INT,
1538                         street2CBChance INT,
1539                         street2CBDone INT,
1540                         street3CBChance INT,
1541                         street3CBDone INT,
1542                         street4CBChance INT,
1543                         street4CBDone INT,
1544
1545                         foldToStreet1CBChance INT,
1546                         foldToStreet1CBDone INT,
1547                         foldToStreet2CBChance INT,
1548                         foldToStreet2CBDone INT,
1549                         foldToStreet3CBChance INT,
1550                         foldToStreet3CBDone INT,
1551                         foldToStreet4CBChance INT,
1552                         foldToStreet4CBDone INT,
1553
1554                         totalProfit INT,
1555
1556                         street1CheckCallRaiseChance INT,
1557                         street1CheckCallRaiseDone INT,
1558                         street2CheckCallRaiseChance INT,
1559                         street2CheckCallRaiseDone INT,
1560                         street3CheckCallRaiseChance INT,
1561                         street3CheckCallRaiseDone INT,
1562                         street4CheckCallRaiseChance INT,
1563                         street4CheckCallRaiseDone INT,
1564
1565                         street0Calls INT,
1566                         street1Calls INT,
1567                         street2Calls INT,
1568                         street3Calls INT,
1569                         street4Calls INT,
1570                         street0Bets INT,
1571                         street1Bets INT,
1572                         street2Bets INT,
1573                         street3Bets INT,
1574                         street4Bets INT,
1575                         street0Raises INT,
1576                         street1Raises INT,
1577                         street2Raises INT,
1578                         street3Raises INT,
1579                         street4Raises INT)
1580                         """
1581
1582         ################################
1583         # Create SessionsCache
1584         ################################
1585
1586         if db_server == 'mysql':
1587             self.query['createSessionsCacheTable'] = """CREATE TABLE SessionsCache (
1588                         id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY (id),
1589                         sessionStart DATETIME NOT NULL,
1590                         sessionEnd DATETIME NOT NULL,
1591                         gameStart DATETIME NOT NULL,
1592                         gameEnd DATETIME NOT NULL,
1593                         sessionId BIGINT,
1594                         date CHAR(7) NOT NULL,  /* 1st char is style (A/T/H/S), other 6 are the key */
1595                         type char(7) NOT NULL,
1596                         gametypeId SMALLINT UNSIGNED, FOREIGN KEY (gametypeId) REFERENCES Gametypes(id),
1597                         tourneyTypeId SMALLINT UNSIGNED, FOREIGN KEY (tourneyTypeId) REFERENCES TourneyTypes(id),
1598                         playerId INT UNSIGNED NOT NULL, FOREIGN KEY (playerId) REFERENCES Players(id),
1599                         played BOOLEAN,
1600                         hands INT NOT NULL,
1601                         tourneys INT NOT NULL,
1602                         totalProfit INT)
1603                         ENGINE=INNODB
1604                         """
1605
1606         elif db_server == 'postgresql':
1607             self.query['createSessionsCacheTable'] = """CREATE TABLE SessionsCache (
1608                         id BIGSERIAL, PRIMARY KEY (id),
1609                         sessionStart timestamp without time zone NOT NULL,
1610                         sessionEnd timestamp without time zone NOT NULL,
1611                         gameStart timestamp without time zone NOT NULL,
1612                         gameEnd timestamp without time zone NOT NULL,
1613                         sessionId INT,
1614                         date CHAR(7) NOT NULL, /* 1st char is style (A/T/H/S), other 6 are the key */
1615                         type char(7),
1616                         gametypeId INT, FOREIGN KEY (gametypeId) REFERENCES Gametypes(id),
1617                         tourneyTypeId INT, FOREIGN KEY (tourneyTypeId) REFERENCES TourneyTypes(id),
1618                         playerId INT, FOREIGN KEY (playerId) REFERENCES Players(id),
1619                         played BOOLEAN,
1620                         hands INT,
1621                         tourneys INT,
1622                         totalProfit INT)
1623                         """
1624
1625         elif db_server == 'sqlite':
1626             self.query['createSessionsCacheTable'] = """CREATE TABLE SessionsCache (
1627                         id INTEGER PRIMARY KEY,
1628                         sessionStart timestamp NOT NULL,
1629                         sessionEnd timestamp NOT NULL,
1630                         gameStart timestamp NOT NULL,
1631                         gameEnd timestamp NOT NULL,
1632                         sessionId INT,
1633                         date TEXT NOT NULL, /* 1st char is style (A/T/H/S), other 6 are the key */
1634                         type TEXT,
1635                         gametypeId INT,
1636                         tourneyTypeId INT,
1637                         playerId INT,
1638                         played INT,
1639                         hands INT,
1640                         tourneys INT,
1641                         totalProfit INT)
1642                         """
1643
1644         self.query['addSessionIdIndex'] = """CREATE INDEX index_SessionId ON SessionsCache (sessionId)"""
1645
1646         self.query['addHandsSessionIdIndex'] = """CREATE INDEX index_handsSessionId ON Hands (sessionId)"""
1647
1648         self.query['addHandsGameSessionIdIndex'] = """CREATE INDEX index_handsGameSessionId ON Hands (gameSessionId)"""
1649
1650         if db_server == 'mysql':
1651             self.query['addTourneyIndex'] = """ALTER TABLE Tourneys ADD UNIQUE INDEX siteTourneyNo(siteTourneyNo, tourneyTypeId)"""
1652         elif db_server == 'postgresql':
1653             self.query['addTourneyIndex'] = """CREATE UNIQUE INDEX siteTourneyNo ON Tourneys (siteTourneyNo, tourneyTypeId)"""
1654         elif db_server == 'sqlite':
1655             self.query['addTourneyIndex'] = """CREATE UNIQUE INDEX siteTourneyNo ON Tourneys (siteTourneyNo, tourneyTypeId)"""
1656
1657         if db_server == 'mysql':
1658             self.query['addHandsIndex'] = """ALTER TABLE Hands ADD UNIQUE INDEX siteHandNo(siteHandNo, gametypeId)"""
1659         elif db_server == 'postgresql':
1660             self.query['addHandsIndex'] = """CREATE UNIQUE INDEX siteHandNo ON Hands (siteHandNo, gametypeId)"""
1661         elif db_server == 'sqlite':
1662             self.query['addHandsIndex'] = """CREATE UNIQUE INDEX siteHandNo ON Hands (siteHandNo, gametypeId)"""
1663
1664         if db_server == 'mysql':
1665             self.query['addPlayersIndex'] = """ALTER TABLE Players ADD UNIQUE INDEX name(name, siteId)"""
1666         elif db_server == 'postgresql':
1667             self.query['addPlayersIndex'] = """CREATE UNIQUE INDEX name ON Players (name, siteId)"""
1668         elif db_server == 'sqlite':
1669             self.query['addPlayersIndex'] = """CREATE UNIQUE INDEX name ON Players (name, siteId)"""
1670
1671         if db_server == 'mysql':
1672             self.query['addTPlayersIndex'] = """ALTER TABLE TourneysPlayers ADD UNIQUE INDEX _tourneyId(tourneyId, playerId)"""
1673         elif db_server == 'postgresql':
1674             self.query['addTPlayersIndex'] = """CREATE UNIQUE INDEX tourneyId ON TourneysPlayers (tourneyId, playerId)"""
1675         elif db_server == 'sqlite':
1676             self.query['addTPlayersIndex'] = """CREATE UNIQUE INDEX tourneyId ON TourneysPlayers (tourneyId, playerId)"""
1677
1678         if db_server == 'mysql':
1679             self.query['addTTypesIndex'] = """ALTER TABLE TourneyTypes ADD UNIQUE INDEX tourneytypes_all(siteId, buyin, fee
1680                                              , maxSeats, knockout, rebuy, addOn, speed, shootout, matrix, sng)"""
1681         elif db_server == 'postgresql':
1682             self.query['addTTypesIndex'] = """CREATE UNIQUE INDEX tourneyTypes_all ON TourneyTypes (siteId, buyin, fee
1683                                              , maxSeats, knockout, rebuy, addOn, speed, shootout, matrix, sng)"""
1684         elif db_server == 'sqlite':
1685             self.query['addTTypesIndex'] = """CREATE UNIQUE INDEX tourneyTypes_all ON TourneyTypes (siteId, buyin, fee
1686                                              , maxSeats, knockout, rebuy, addOn, speed, shootout, matrix, sng)"""
1687
1688         self.query['get_last_hand'] = "select max(id) from Hands"
1689
1690         self.query['get_last_date'] = "SELECT MAX(startTime) FROM Hands"
1691
1692         self.query['get_first_date'] = "SELECT MIN(startTime) FROM Hands"
1693
1694         self.query['get_player_id'] = """
1695                 select Players.id AS player_id
1696                 from Players, Sites
1697                 where Players.name = %s
1698                 and Sites.name = %s
1699                 and Players.siteId = Sites.id
1700             """
1701
1702         self.query['get_player_names'] = """
1703                 select p.name
1704                 from Players p
1705                 where lower(p.name) like lower(%s)
1706                 and   (p.siteId = %s or %s = -1)
1707             """
1708
1709         self.query['get_gameinfo_from_hid'] = """
1710                 SELECT
1711                         s.name,
1712                         g.category,
1713                         g.base,
1714                         g.type,
1715                         g.limitType,
1716                         g.hilo,
1717                         round(g.smallBlind / 100.0,2),
1718                         round(g.bigBlind / 100.0,2),
1719                         round(g.smallBet / 100.0,2),
1720                         round(g.bigBet / 100.0,2),
1721                         g.currency
1722                     FROM
1723                         Hands as h,
1724                         Sites as s,
1725                         Gametypes as g,
1726                         HandsPlayers as hp,
1727                         Players as p
1728                     WHERE
1729                         h.id = %s
1730                     and g.id = h.gametypeid
1731                     and hp.handid = h.id
1732                     and p.id = hp.playerid
1733                     and s.id = p.siteid
1734                     limit 1
1735             """
1736
1737         self.query['get_stats_from_hand'] = """
1738                 SELECT hc.playerId                      AS player_id,
1739                     hp.seatNo                           AS seat,
1740                     p.name                              AS screen_name,
1741                     sum(hc.HDs)                         AS n,
1742                     sum(hc.street0VPI)                  AS vpip,
1743                     sum(hc.street0Aggr)                 AS pfr,
1744                     sum(hc.street0_3BChance)            AS TB_opp_0,
1745                     sum(hc.street0_3BDone)              AS TB_0,
1746                     sum(hc.street0_4BChance)            AS FB_opp_0,
1747                     sum(hc.street0_4BDone)              AS FB_0,
1748                     sum(hc.street0_C4BChance)           AS CFB_opp_0,
1749                     sum(hc.street0_C4BDone)             AS CFB_0,
1750                     sum(hc.street0_FoldTo3BChance)      AS F3B_opp_0,
1751                     sum(hc.street0_FoldTo3BDone)        AS F3B_0,
1752                     sum(hc.street0_FoldTo4BChance)      AS F4B_opp_0,
1753                     sum(hc.street0_FoldTo4BDone)        AS F4B_0,
1754                     sum(hc.street0_SqueezeChance)       AS SQZ_opp_0,
1755                     sum(hc.street0_SqueezeDone)         AS SQZ_0,
1756                     sum(hc.raiseToStealChance)          AS RTS_opp,
1757                     sum(hc.raiseToStealDone)            AS RTS,
1758                     sum(hc.success_Steal)               AS SUC_ST,
1759                     sum(hc.street1Seen)                 AS saw_f,
1760                     sum(hc.street1Seen)                 AS saw_1,
1761                     sum(hc.street2Seen)                 AS saw_2,
1762                     sum(hc.street3Seen)                 AS saw_3,
1763                     sum(hc.street4Seen)                 AS saw_4,
1764                     sum(hc.sawShowdown)                 AS sd,
1765                     sum(hc.street1Aggr)                 AS aggr_1,
1766                     sum(hc.street2Aggr)                 AS aggr_2,
1767                     sum(hc.street3Aggr)                 AS aggr_3,
1768                     sum(hc.street4Aggr)                 AS aggr_4,
1769                     sum(hc.otherRaisedStreet1)          AS was_raised_1,
1770                     sum(hc.otherRaisedStreet2)          AS was_raised_2,
1771                     sum(hc.otherRaisedStreet3)          AS was_raised_3,
1772                     sum(hc.otherRaisedStreet4)          AS was_raised_4,
1773                     sum(hc.foldToOtherRaisedStreet1)    AS f_freq_1,
1774                     sum(hc.foldToOtherRaisedStreet2)    AS f_freq_2,
1775                     sum(hc.foldToOtherRaisedStreet3)    AS f_freq_3,
1776                     sum(hc.foldToOtherRaisedStreet4)    AS f_freq_4,
1777                     sum(hc.wonWhenSeenStreet1)          AS w_w_s_1,
1778                     sum(hc.wonAtSD)                     AS wmsd,
1779                     sum(case hc.position
1780                         when 'S' then hc.raiseFirstInChance
1781                         when '0' then hc.raiseFirstInChance
1782                         when '1' then hc.raiseFirstInChance
1783                         else 0
1784                        )                                AS steal_opp,
1785                     sum(case hc.position
1786                         when 'S' then hc.raisedFirstIn
1787                         when '0' then hc.raisedFirstIn
1788                         when '1' then hc.raisedFirstIn
1789                         else 0
1790                        )                                AS steal,
1791                     sum(hc.foldSbToStealChance)         AS SBstolen,
1792                     sum(hc.foldedSbToSteal)             AS SBnotDef,
1793                     sum(hc.foldBbToStealChance)         AS BBstolen,
1794                     sum(hc.foldedBbToSteal)             AS BBnotDef,
1795                     sum(hc.street1CBChance)             AS CB_opp_1,
1796                     sum(hc.street1CBDone)               AS CB_1,
1797                     sum(hc.street2CBChance)             AS CB_opp_2,
1798                     sum(hc.street2CBDone)               AS CB_2,
1799                     sum(hc.street3CBChance)             AS CB_opp_3,
1800                     sum(hc.street3CBDone)               AS CB_3,
1801                     sum(hc.street4CBChance)             AS CB_opp_4,
1802                     sum(hc.street4CBDone)               AS CB_4,
1803                     sum(hc.foldToStreet1CBChance)       AS f_cb_opp_1,
1804                     sum(hc.foldToStreet1CBDone)         AS f_cb_1,
1805                     sum(hc.foldToStreet2CBChance)       AS f_cb_opp_2,
1806                     sum(hc.foldToStreet2CBDone)         AS f_cb_2,
1807                     sum(hc.foldToStreet3CBChance)       AS f_cb_opp_3,
1808                     sum(hc.foldToStreet3CBDone)         AS f_cb_3,
1809                     sum(hc.foldToStreet4CBChance)       AS f_cb_opp_4,
1810                     sum(hc.foldToStreet4CBDone)         AS f_cb_4,
1811                     sum(hc.totalProfit)                 AS net,
1812                     sum(gt.bigblind)                    AS bigblind,
1813                     sum(hc.street1CheckCallRaiseChance) AS ccr_opp_1,
1814                     sum(hc.street1CheckCallRaiseDone)   AS ccr_1,
1815                     sum(hc.street2CheckCallRaiseChance) AS ccr_opp_2,
1816                     sum(hc.street2CheckCallRaiseDone)   AS ccr_2,
1817                     sum(hc.street3CheckCallRaiseChance) AS ccr_opp_3,
1818                     sum(hc.street3CheckCallRaiseDone)   AS ccr_3,
1819                     sum(hc.street4CheckCallRaiseChance) AS ccr_opp_4,
1820                     sum(hc.street4CheckCallRaiseDone)   AS ccr_4
1821                     sum(hc.street0Calls)                AS call_0,
1822                     sum(hc.street1Calls)                AS call_1,
1823                     sum(hc.street2Calls)                AS call_2,
1824                     sum(hc.street3Calls)                AS call_3,
1825                     sum(hc.street4Calls)                AS call_4,
1826                     sum(hc.street0Bets)                 AS bet_0,
1827                     sum(hc.street1Bets)                 AS bet_1,
1828                     sum(hc.street2Bets)                 AS bet_2,
1829                     sum(hc.street3Bets)                 AS bet_3,
1830                     sum(hc.street4Bets)                 AS bet_4,
1831                     sum(hc.street0Raises)               AS raise_0,
1832                     sum(hc.street1Raises)               AS raise_1,
1833                     sum(hc.street2Raises)               AS raise_2,
1834                     sum(hc.street3Raises)               AS raise_3,
1835                     sum(hc.street4Raises)               AS raise_4
1836                 FROM Hands h
1837                      INNER JOIN HandsPlayers hp ON (hp.handId = h.id)
1838                      INNER JOIN HudCache hc ON (    hc.PlayerId = hp.PlayerId+0
1839                                                 AND hc.gametypeId+0 = h.gametypeId+0)
1840                      INNER JOIN Players p ON (p.id = hp.PlayerId+0)
1841                      INNER JOIN Gametypes gt ON (gt.id = hc.gametypeId)
1842                 WHERE h.id = %s
1843                 AND   hc.styleKey > %s
1844                       /* styleKey is currently 'd' (for date) followed by a yyyymmdd
1845                          date key. Set it to 0000000 or similar to get all records  */
1846                 /* also check activeseats here even if only 3 groups eg 2-3/4-6/7+
1847                    e.g. could use a multiplier:
1848                    AND   h.seats > X / 1.25  and  hp.seats < X * 1.25
1849                    where X is the number of active players at the current table (and
1850                    1.25 would be a config value so user could change it)
1851                 */
1852                 GROUP BY hc.PlayerId, hp.seatNo, p.name
1853                 ORDER BY hc.PlayerId, hp.seatNo, p.name
1854             """
1855
1856 #    same as above except stats are aggregated for all blind/limit levels
1857         self.query['get_stats_from_hand_aggregated'] = """
1858                 /* explain query plan */
1859                 SELECT hc.playerId                         AS player_id,
1860                        max(case when hc.gametypeId = h.gametypeId
1861                                 then hp.seatNo
1862                                 else -1
1863                            end)                            AS seat,
1864                        p.name                              AS screen_name,
1865                        sum(hc.HDs)                         AS n,
1866                        sum(hc.street0VPI)                  AS vpip,
1867                        sum(hc.street0Aggr)                 AS pfr,
1868                        sum(hc.street0_3BChance)            AS TB_opp_0,
1869                        sum(hc.street0_3BDone)              AS TB_0,
1870                        sum(hc.street0_4BChance)            AS FB_opp_0,
1871                        sum(hc.street0_4BDone)              AS FB_0,
1872                        sum(hc.street0_C4BChance)           AS CFB_opp_0,
1873                        sum(hc.street0_C4BDone)             AS CFB_0,
1874                        sum(hc.street0_FoldTo3BChance)      AS F3B_opp_0,
1875                        sum(hc.street0_FoldTo3BDone)        AS F3B_0,
1876                        sum(hc.street0_FoldTo4BChance)      AS F4B_opp_0,
1877                        sum(hc.street0_FoldTo4BDone)        AS F4B_0,
1878                        sum(hc.street0_SqueezeChance)       AS SQZ_opp_0,
1879                        sum(hc.street0_SqueezeDone)         AS SQZ_0,
1880                        sum(hc.raiseToStealChance)          AS RTS_opp,
1881                        sum(hc.raiseToStealDone)            AS RTS,
1882                        sum(hc.success_Steal)               AS SUC_ST,
1883                        sum(hc.street1Seen)                 AS saw_f,
1884                        sum(hc.street1Seen)                 AS saw_1,
1885                        sum(hc.street2Seen)                 AS saw_2,
1886                        sum(hc.street3Seen)                 AS saw_3,
1887                        sum(hc.street4Seen)                 AS saw_4,
1888                        sum(hc.sawShowdown)                 AS sd,
1889                        sum(hc.street1Aggr)                 AS aggr_1,
1890                        sum(hc.street2Aggr)                 AS aggr_2,
1891                        sum(hc.street3Aggr)                 AS aggr_3,
1892                        sum(hc.street4Aggr)                 AS aggr_4,
1893                        sum(hc.otherRaisedStreet1)          AS was_raised_1,
1894                        sum(hc.otherRaisedStreet2)          AS was_raised_2,
1895                        sum(hc.otherRaisedStreet3)          AS was_raised_3,
1896                        sum(hc.otherRaisedStreet4)          AS was_raised_4,
1897                        sum(hc.foldToOtherRaisedStreet1)    AS f_freq_1,
1898                        sum(hc.foldToOtherRaisedStreet2)    AS f_freq_2,
1899                        sum(hc.foldToOtherRaisedStreet3)    AS f_freq_3,
1900                        sum(hc.foldToOtherRaisedStreet4)    AS f_freq_4,
1901                        sum(hc.wonWhenSeenStreet1)          AS w_w_s_1,
1902                        sum(hc.wonAtSD)                     AS wmsd,
1903                        sum(hc.raiseFirstInChance)          AS steal_opp,
1904                        sum(hc.raisedFirstIn)               AS steal,
1905                        sum(hc.foldSbToStealChance)         AS SBstolen,
1906                        sum(hc.foldedSbToSteal)             AS SBnotDef,
1907                        sum(hc.foldBbToStealChance)         AS BBstolen,
1908                        sum(hc.foldedBbToSteal)             AS BBnotDef,
1909                        sum(hc.street1CBChance)             AS CB_opp_1,
1910                        sum(hc.street1CBDone)               AS CB_1,
1911                        sum(hc.street2CBChance)             AS CB_opp_2,
1912                        sum(hc.street2CBDone)               AS CB_2,
1913                        sum(hc.street3CBChance)             AS CB_opp_3,
1914                        sum(hc.street3CBDone)               AS CB_3,
1915                        sum(hc.street4CBChance)             AS CB_opp_4,
1916                        sum(hc.street4CBDone)               AS CB_4,
1917                        sum(hc.foldToStreet1CBChance)       AS f_cb_opp_1,
1918                        sum(hc.foldToStreet1CBDone)         AS f_cb_1,
1919                        sum(hc.foldToStreet2CBChance)       AS f_cb_opp_2,
1920                        sum(hc.foldToStreet2CBDone)         AS f_cb_2,
1921                        sum(hc.foldToStreet3CBChance)       AS f_cb_opp_3,
1922                        sum(hc.foldToStreet3CBDone)         AS f_cb_3,
1923                        sum(hc.foldToStreet4CBChance)       AS f_cb_opp_4,
1924                        sum(hc.foldToStreet4CBDone)         AS f_cb_4,
1925                        sum(hc.totalProfit)                 AS net,
1926                        sum(gt.bigblind)                    AS bigblind,
1927                        sum(hc.street1CheckCallRaiseChance) AS ccr_opp_1,
1928                        sum(hc.street1CheckCallRaiseDone)   AS ccr_1,
1929                        sum(hc.street2CheckCallRaiseChance) AS ccr_opp_2,
1930                        sum(hc.street2CheckCallRaiseDone)   AS ccr_2,
1931                        sum(hc.street3CheckCallRaiseChance) AS ccr_opp_3,
1932                        sum(hc.street3CheckCallRaiseDone)   AS ccr_3,
1933                        sum(hc.street4CheckCallRaiseChance) AS ccr_opp_4,
1934                        sum(hc.street4CheckCallRaiseDone)   AS ccr_4,
1935                        sum(hc.street0Calls)                AS call_0,
1936                        sum(hc.street1Calls)                AS call_1,
1937                        sum(hc.street2Calls)                AS call_2,
1938                        sum(hc.street3Calls)                AS call_3,
1939                        sum(hc.street4Calls)                AS call_4,
1940                        sum(hc.street0Bets)                 AS bet_0,
1941                        sum(hc.street1Bets)                 AS bet_1,
1942                        sum(hc.street2Bets)                 AS bet_2,
1943                        sum(hc.street3Bets)                 AS bet_3,
1944                        sum(hc.street4Bets)                 AS bet_4,
1945                        sum(hc.street0Raises)               AS raise_0,
1946                        sum(hc.street1Raises)               AS raise_1,
1947                        sum(hc.street2Raises)               AS raise_2,
1948                        sum(hc.street3Raises)               AS raise_3,
1949                        sum(hc.street4Raises)               AS raise_4
1950                 FROM Hands h
1951                      INNER JOIN HandsPlayers hp ON (hp.handId = h.id)
1952                      INNER JOIN HudCache hc     ON (hc.playerId = hp.playerId)
1953                      INNER JOIN Players p       ON (p.id = hc.playerId)
1954                      INNER JOIN Gametypes gt    ON (gt.id = hc.gametypeId)
1955                 WHERE h.id = %s
1956                 AND   (   /* 2 separate parts for hero and opponents */
1957                           (    hp.playerId != %s
1958                            AND hc.styleKey > %s
1959                            AND hc.gametypeId+0 in
1960                                  (SELECT gt1.id from Gametypes gt1, Gametypes gt2
1961                                   WHERE  gt1.siteid = gt2.siteid  /* find gametypes where these match: */
1962                                   AND    gt1.type = gt2.type               /* ring/tourney */
1963                                   AND    gt1.category = gt2.category       /* holdem/stud*/
1964                                   AND    gt1.limittype = gt2.limittype     /* fl/nl */
1965                                   AND    gt1.bigblind <= gt2.bigblind * %s  /* bigblind similar size */
1966                                   AND    gt1.bigblind >= gt2.bigblind / %s
1967                                   AND    gt2.id = h.gametypeId)
1968                            AND hc.activeSeats between %s and %s
1969                           )
1970                        OR
1971                           (    hp.playerId = %s
1972                            AND hc.styleKey > %s
1973                            AND hc.gametypeId+0 in
1974                                  (SELECT gt1.id from Gametypes gt1, Gametypes gt2
1975                                   WHERE  gt1.siteid = gt2.siteid  /* find gametypes where these match: */
1976                                   AND    gt1.type = gt2.type               /* ring/tourney */
1977                                   AND    gt1.category = gt2.category       /* holdem/stud*/
1978                                   AND    gt1.limittype = gt2.limittype     /* fl/nl */
1979                                   AND    gt1.bigblind <= gt2.bigblind * %s  /* bigblind similar size */
1980                                   AND    gt1.bigblind >= gt2.bigblind / %s
1981                                   AND    gt2.id = h.gametypeId)
1982                            AND hc.activeSeats between %s and %s
1983                           )
1984                       )
1985                 GROUP BY hc.PlayerId, p.name
1986                 ORDER BY hc.PlayerId, p.name
1987             """
1988                 #  NOTES on above cursor:
1989                 #  - Do NOT include %s inside query in a comment - the db api thinks
1990                 #  they are actual arguments.
1991                 #  - styleKey is currently 'd' (for date) followed by a yyyymmdd
1992                 #  date key. Set it to 0000000 or similar to get all records
1993                 #  Could also check activeseats here even if only 3 groups eg 2-3/4-6/7+
1994                 #  e.g. could use a multiplier:
1995                 #  AND   h.seats > %s / 1.25  and  hp.seats < %s * 1.25
1996                 #  where %s is the number of active players at the current table (and
1997                 #  1.25 would be a config value so user could change it)
1998
1999         if db_server == 'mysql':
2000             self.query['get_stats_from_hand_session'] = """
2001                     SELECT hp.playerId                                              AS player_id, /* playerId and seats must */
2002                            h.seats                                                  AS seats,     /* be first and second field */
2003                            hp.handId                                                AS hand_id,
2004                            hp.seatNo                                                AS seat,
2005                            p.name                                                   AS screen_name,
2006                            1                                                        AS n,
2007                            cast(hp2.street0VPI as <signed>integer)                  AS vpip,
2008                            cast(hp2.street0Aggr as <signed>integer)                 AS pfr,
2009                            cast(hp2.street0_3BChance as <signed>integer)            AS TB_opp_0,
2010                            cast(hp2.street0_3BDone as <signed>integer)              AS TB_0,
2011                            cast(hp2.street0_4BChance as <signed>integer)            AS FB_opp_0,
2012                            cast(hp2.street0_4BDone as <signed>integer)              AS FB_0,
2013                            cast(hp2.street0_C4BChance as <signed>integer)           AS CFB_opp_0,
2014                            cast(hp2.street0_C4BDone as <signed>integer)             AS CFB_0,
2015                            cast(hp2.street0_FoldTo3BChance as <signed>integer)      AS F3B_opp_0,
2016                            cast(hp2.street0_FoldTo3BDone as <signed>integer)        AS F3B_0,
2017                            cast(hp2.street0_FoldTo4BChance as <signed>integer)      AS F4B_opp_0,
2018                            cast(hp2.street0_FoldTo4BDone as <signed>integer)        AS F4B_0,
2019                            cast(hp2.street0_SqueezeChance as <signed>integer)       AS SQZ_opp_0,
2020                            cast(hp2.street0_SqueezeDone as <signed>integer)         AS SQZ_0,
2021                            cast(hp2.raiseToStealChance as <signed>integer)          AS RTS_opp,
2022                            cast(hp2.raiseToStealDone as <signed>integer)            AS RTS,
2023                            cast(hp2.success_Steal as <signed>integer)               AS SUC_ST,
2024                            cast(hp2.street1Seen as <signed>integer)                 AS saw_f,
2025                            cast(hp2.street1Seen as <signed>integer)                 AS saw_1,
2026                            cast(hp2.street2Seen as <signed>integer)                 AS saw_2,
2027                            cast(hp2.street3Seen as <signed>integer)                 AS saw_3,
2028                            cast(hp2.street4Seen as <signed>integer)                 AS saw_4,
2029                            cast(hp2.sawShowdown as <signed>integer)                 AS sd,
2030                            cast(hp2.street1Aggr as <signed>integer)                 AS aggr_1,
2031                            cast(hp2.street2Aggr as <signed>integer)                 AS aggr_2,
2032                            cast(hp2.street3Aggr as <signed>integer)                 AS aggr_3,
2033                            cast(hp2.street4Aggr as <signed>integer)                 AS aggr_4,
2034                            cast(hp2.otherRaisedStreet1 as <signed>integer)          AS was_raised_1,
2035                            cast(hp2.otherRaisedStreet2 as <signed>integer)          AS was_raised_2,
2036                            cast(hp2.otherRaisedStreet3 as <signed>integer)          AS was_raised_3,
2037                            cast(hp2.otherRaisedStreet4 as <signed>integer)          AS was_raised_4,
2038                            cast(hp2.foldToOtherRaisedStreet1 as <signed>integer)    AS f_freq_1,
2039                            cast(hp2.foldToOtherRaisedStreet2 as <signed>integer)    AS f_freq_2,
2040                            cast(hp2.foldToOtherRaisedStreet3 as <signed>integer)    AS f_freq_3,
2041                            cast(hp2.foldToOtherRaisedStreet4 as <signed>integer)    AS f_freq_4,
2042                            cast(hp2.wonWhenSeenStreet1 as <signed>integer)          AS w_w_s_1,
2043                            cast(hp2.wonAtSD as <signed>integer)                     AS wmsd,
2044                            cast(hp2.raiseFirstInChance as <signed>integer)          AS steal_opp,
2045                            cast(hp2.raisedFirstIn as <signed>integer)               AS steal,
2046                            cast(hp2.foldSbToStealChance as <signed>integer)         AS SBstolen,
2047                            cast(hp2.foldedSbToSteal as <signed>integer)             AS SBnotDef,
2048                            cast(hp2.foldBbToStealChance as <signed>integer)         AS BBstolen,
2049                            cast(hp2.foldedBbToSteal as <signed>integer)             AS BBnotDef,
2050                            cast(hp2.street1CBChance as <signed>integer)             AS CB_opp_1,
2051                            cast(hp2.street1CBDone as <signed>integer)               AS CB_1,
2052                            cast(hp2.street2CBChance as <signed>integer)             AS CB_opp_2,
2053                            cast(hp2.street2CBDone as <signed>integer)               AS CB_2,
2054                            cast(hp2.street3CBChance as <signed>integer)             AS CB_opp_3,
2055                            cast(hp2.street3CBDone as <signed>integer)               AS CB_3,
2056                            cast(hp2.street4CBChance as <signed>integer)             AS CB_opp_4,
2057                            cast(hp2.street4CBDone as <signed>integer)               AS CB_4,
2058                            cast(hp2.foldToStreet1CBChance as <signed>integer)       AS f_cb_opp_1,
2059                            cast(hp2.foldToStreet1CBDone as <signed>integer)         AS f_cb_1,
2060                            cast(hp2.foldToStreet2CBChance as <signed>integer)       AS f_cb_opp_2,
2061                            cast(hp2.foldToStreet2CBDone as <signed>integer)         AS f_cb_2,
2062                            cast(hp2.foldToStreet3CBChance as <signed>integer)       AS f_cb_opp_3,
2063                            cast(hp2.foldToStreet3CBDone as <signed>integer)         AS f_cb_3,
2064                            cast(hp2.foldToStreet4CBChance as <signed>integer)       AS f_cb_opp_4,
2065                            cast(hp2.foldToStreet4CBDone as <signed>integer)         AS f_cb_4,
2066                            cast(hp2.totalProfit as <signed>integer)                 AS net,
2067                            cast(gt.bigblind as <signed>integer)                     AS bigblind,
2068                            cast(hp2.street1CheckCallRaiseChance as <signed>integer) AS ccr_opp_1,
2069                            cast(hp2.street1CheckCallRaiseDone as <signed>integer)   AS ccr_1,
2070                            cast(hp2.street2CheckCallRaiseChance as <signed>integer) AS ccr_opp_2,
2071                            cast(hp2.street2CheckCallRaiseDone as <signed>integer)   AS ccr_2,
2072                            cast(hp2.street3CheckCallRaiseChance as <signed>integer) AS ccr_opp_3,
2073                            cast(hp2.street3CheckCallRaiseDone as <signed>integer)   AS ccr_3,
2074                            cast(hp2.street4CheckCallRaiseChance as <signed>integer) AS ccr_opp_4,
2075                            cast(hp2.street4CheckCallRaiseDone as <signed>integer)   AS ccr_4,
2076                            cast(hp2.street0Calls as <signed>integer)                AS call_0,
2077                            cast(hp2.street1Calls as <signed>integer)                AS call_1,
2078                            cast(hp2.street2Calls as <signed>integer)                AS call_2,
2079                            cast(hp2.street3Calls as <signed>integer)                AS call_3,
2080                            cast(hp2.street4Calls as <signed>integer)                AS call_4,
2081                            cast(hp2.street0Bets as <signed>integer)                 AS bet_0,
2082                            cast(hp2.street1Bets as <signed>integer)                 AS bet_1,
2083                            cast(hp2.street2Bets as <signed>integer)                 AS bet_2,
2084                            cast(hp2.street3Bets as <signed>integer)                 AS bet_3,
2085                            cast(hp2.street4Bets as <signed>integer)                 AS bet_4,
2086                            cast(hp2.street0Raises as <signed>integer)               AS raise_0,
2087                            cast(hp2.street1Raises as <signed>integer)               AS raise_1,
2088                            cast(hp2.street2Raises as <signed>integer)               AS raise_2,
2089                            cast(hp2.street3Raises as <signed>integer)               AS raise_3,
2090                            cast(hp2.street4Raises as <signed>integer)               AS raise_4
2091                     FROM
2092                          Hands h
2093                          INNER JOIN Hands h2         ON (h2.id >= %s AND   h2.tableName = h.tableName)
2094                          INNER JOIN HandsPlayers hp  ON (h.id = hp.handId)         /* players in this hand */
2095                          INNER JOIN HandsPlayers hp2 ON (hp2.playerId+0 = hp.playerId+0 AND (hp2.handId = h2.id+0))  /* other hands by these players */
2096                          INNER JOIN Players p        ON (p.id = hp2.PlayerId+0)
2097                          INNER JOIN Gametypes gt     ON (gt.id = h2.gametypeId)
2098                     WHERE hp.handId = %s
2099                     /* check activeseats once this data returned (don't want to do that here as it might
2100                        assume a session ended just because the number of seats dipped for a few hands)
2101                     */
2102                     AND   (   /* 2 separate parts for hero and opponents */
2103                               (    hp2.playerId != %s
2104                                AND h2.seats between %s and %s
2105                               )
2106                            OR
2107                               (    hp2.playerId = %s
2108                                AND h2.seats between %s and %s
2109                               )
2110                           )
2111                     ORDER BY h.startTime desc, hp2.PlayerId
2112                     /* order rows by handstart descending so that we can stop reading rows when
2113                        there's a gap over X minutes between hands (ie. when we get back to start of
2114                        the session */
2115                 """
2116         elif db_server == 'postgresql':
2117             self.query['get_stats_from_hand_session'] = """
2118                     SELECT hp.playerId                                              AS player_id,
2119                            hp.handId                                                AS hand_id,
2120                            hp.seatNo                                                AS seat,
2121                            p.name                                                   AS screen_name,
2122                            h.seats                                                  AS seats,
2123                            1                                                        AS n,
2124                            cast(hp2.street0VPI as <signed>integer)                  AS vpip,
2125                            cast(hp2.street0Aggr as <signed>integer)                 AS pfr,
2126                            cast(hp2.street0_3BChance as <signed>integer)            AS TB_opp_0,
2127                            cast(hp2.street0_3BDone as <signed>integer)              AS TB_0,
2128                            cast(hp2.street0_4BChance as <signed>integer)            AS FB_opp_0,
2129                            cast(hp2.street0_4BDone as <signed>integer)              AS FB_0,
2130                            cast(hp2.street0_C4BChance as <signed>integer)           AS CFB_opp_0,
2131                            cast(hp2.street0_C4BDone as <signed>integer)             AS CFB_0,
2132                            cast(hp2.street0_FoldTo3BChance as <signed>integer)      AS F3B_opp_0,
2133                            cast(hp2.street0_FoldTo3BDone as <signed>integer)        AS F3B_0,
2134                            cast(hp2.street0_FoldTo4BChance as <signed>integer)      AS F4B_opp_0,
2135                            cast(hp2.street0_FoldTo4BDone as <signed>integer)        AS F4B_0,
2136                            cast(hp2.street0_SqueezeChance as <signed>integer)       AS SQZ_opp_0,
2137                            cast(hp2.street0_SqueezeDone as <signed>integer)         AS SQZ_0,
2138                            cast(hp2.raiseToStealChance as <signed>integer)          AS RTS_opp,
2139                            cast(hp2.raiseToStealDone as <signed>integer)            AS RTS,
2140                            cast(hp2.success_Steal as <signed>integer)               AS SUC_ST,
2141                            cast(hp2.street1Seen as <signed>integer)                 AS saw_f,
2142                            cast(hp2.street1Seen as <signed>integer)                 AS saw_1,
2143                            cast(hp2.street2Seen as <signed>integer)                 AS saw_2,
2144                            cast(hp2.street3Seen as <signed>integer)                 AS saw_3,
2145                            cast(hp2.street4Seen as <signed>integer)                 AS saw_4,
2146                            cast(hp2.sawShowdown as <signed>integer)                 AS sd,
2147                            cast(hp2.street1Aggr as <signed>integer)                 AS aggr_1,
2148                            cast(hp2.street2Aggr as <signed>integer)                 AS aggr_2,
2149                            cast(hp2.street3Aggr as <signed>integer)                 AS aggr_3,
2150                            cast(hp2.street4Aggr as <signed>integer)                 AS aggr_4,
2151                            cast(hp2.otherRaisedStreet1 as <signed>integer)          AS was_raised_1,
2152                            cast(hp2.otherRaisedStreet2 as <signed>integer)          AS was_raised_2,
2153                            cast(hp2.otherRaisedStreet3 as <signed>integer)          AS was_raised_3,
2154                            cast(hp2.otherRaisedStreet4 as <signed>integer)          AS was_raised_4,
2155                            cast(hp2.foldToOtherRaisedStreet1 as <signed>integer)    AS f_freq_1,
2156                            cast(hp2.foldToOtherRaisedStreet2 as <signed>integer)    AS f_freq_2,
2157                            cast(hp2.foldToOtherRaisedStreet3 as <signed>integer)    AS f_freq_3,
2158                            cast(hp2.foldToOtherRaisedStreet4 as <signed>integer)    AS f_freq_4,
2159                            cast(hp2.wonWhenSeenStreet1 as <signed>integer)          AS w_w_s_1,
2160                            cast(hp2.wonAtSD as <signed>integer)                     AS wmsd,
2161                            cast(hp2.raiseFirstInChance as <signed>integer)          AS steal_opp,
2162                            cast(hp2.raisedFirstIn as <signed>integer)               AS steal,
2163                            cast(hp2.foldSbToStealChance as <signed>integer)         AS SBstolen,
2164                            cast(hp2.foldedSbToSteal as <signed>integer)             AS SBnotDef,
2165                            cast(hp2.foldBbToStealChance as <signed>integer)         AS BBstolen,
2166                            cast(hp2.foldedBbToSteal as <signed>integer)             AS BBnotDef,
2167                            cast(hp2.street1CBChance as <signed>integer)             AS CB_opp_1,
2168                            cast(hp2.street1CBDone as <signed>integer)               AS CB_1,
2169                            cast(hp2.street2CBChance as <signed>integer)             AS CB_opp_2,
2170                            cast(hp2.street2CBDone as <signed>integer)               AS CB_2,
2171                            cast(hp2.street3CBChance as <signed>integer)             AS CB_opp_3,
2172                            cast(hp2.street3CBDone as <signed>integer)               AS CB_3,
2173                            cast(hp2.street4CBChance as <signed>integer)             AS CB_opp_4,
2174                            cast(hp2.street4CBDone as <signed>integer)               AS CB_4,
2175                            cast(hp2.foldToStreet1CBChance as <signed>integer)       AS f_cb_opp_1,
2176                            cast(hp2.foldToStreet1CBDone as <signed>integer)         AS f_cb_1,
2177                            cast(hp2.foldToStreet2CBChance as <signed>integer)       AS f_cb_opp_2,
2178                            cast(hp2.foldToStreet2CBDone as <signed>integer)         AS f_cb_2,
2179                            cast(hp2.foldToStreet3CBChance as <signed>integer)       AS f_cb_opp_3,
2180                            cast(hp2.foldToStreet3CBDone as <signed>integer)         AS f_cb_3,
2181                            cast(hp2.foldToStreet4CBChance as <signed>integer)       AS f_cb_opp_4,
2182                            cast(hp2.foldToStreet4CBDone as <signed>integer)         AS f_cb_4,
2183                            cast(hp2.totalProfit as <signed>integer)                 AS net,
2184                            cast(gt.bigblind as <signed>integer)                     AS bigblind,
2185                            cast(hp2.street1CheckCallRaiseChance as <signed>integer) AS ccr_opp_1,
2186                            cast(hp2.street1CheckCallRaiseDone as <signed>integer)   AS ccr_1,
2187                            cast(hp2.street2CheckCallRaiseChance as <signed>integer) AS ccr_opp_2,
2188                            cast(hp2.street2CheckCallRaiseDone as <signed>integer)   AS ccr_2,
2189                            cast(hp2.street3CheckCallRaiseChance as <signed>integer) AS ccr_opp_3,
2190                            cast(hp2.street3CheckCallRaiseDone as <signed>integer)   AS ccr_3,
2191                            cast(hp2.street4CheckCallRaiseChance as <signed>integer) AS ccr_opp_4,
2192                            cast(hp2.street4CheckCallRaiseDone as <signed>integer)   AS ccr_4,
2193                            cast(hp2.street0Calls as <signed>integer)                AS call_0,
2194                            cast(hp2.street1Calls as <signed>integer)                AS call_1,
2195                            cast(hp2.street2Calls as <signed>integer)                AS call_2,
2196                            cast(hp2.street3Calls as <signed>integer)                AS call_3,
2197                            cast(hp2.street4Calls as <signed>integer)                AS call_4,
2198                            cast(hp2.street0Bets as <signed>integer)                 AS bet_0,
2199                            cast(hp2.street1Bets as <signed>integer)                 AS bet_1,
2200                            cast(hp2.street2Bets as <signed>integer)                 AS bet_2,
2201                            cast(hp2.street3Bets as <signed>integer)                 AS bet_3,
2202                            cast(hp2.street4Bets as <signed>integer)                 AS bet_4,
2203                            cast(hp2.street0Raises as <signed>integer)               AS raise_0,
2204                            cast(hp2.street1Raises as <signed>integer)               AS raise_1,
2205                            cast(hp2.street2Raises as <signed>integer)               AS raise_2,
2206                            cast(hp2.street3Raises as <signed>integer)               AS raise_3,
2207                            cast(hp2.street4Raises as <signed>integer)               AS raise_4
2208                          FROM Hands h                                                  /* this hand */
2209                          INNER JOIN Hands h2         ON (    h2.id >= %s           /* other hands */
2210                                                          AND h2.tableName = h.tableName)
2211                          INNER JOIN HandsPlayers hp  ON (h.id = hp.handId)        /* players in this hand */
2212                          INNER JOIN HandsPlayers hp2 ON (    hp2.playerId+0 = hp.playerId+0
2213                                                          AND hp2.handId = h2.id)  /* other hands by these players */
2214                          INNER JOIN Players p        ON (p.id = hp2.PlayerId+0)
2215                          INNER JOIN Gametypes gt     ON (gt.id = h2.gametypeId)
2216                     WHERE h.id = %s
2217                     /* check activeseats once this data returned (don't want to do that here as it might
2218                        assume a session ended just because the number of seats dipped for a few hands)
2219                     */
2220                     AND   (   /* 2 separate parts for hero and opponents */
2221                               (    hp2.playerId != %s
2222                                AND h2.seats between %s and %s
2223                               )
2224                            OR
2225                               (    hp2.playerId = %s
2226                                AND h2.seats between %s and %s
2227                               )
2228                           )
2229                     ORDER BY h.startTime desc, hp2.PlayerId
2230                     /* order rows by handstart descending so that we can stop reading rows when
2231                        there's a gap over X minutes between hands (ie. when we get back to start of
2232                        the session */
2233                 """
2234         elif db_server == 'sqlite':
2235             self.query['get_stats_from_hand_session'] = """
2236                     SELECT hp.playerId                                              AS player_id,
2237                            hp.handId                                                AS hand_id,
2238                            hp.seatNo                                                AS seat,
2239                            p.name                                                   AS screen_name,
2240                            h.seats                                                  AS seats,
2241                            1                                                        AS n,
2242                            cast(hp2.street0VPI as <signed>integer)                  AS vpip,
2243                            cast(hp2.street0Aggr as <signed>integer)                 AS pfr,
2244                            cast(hp2.street0_3BChance as <signed>integer)            AS TB_opp_0,
2245                            cast(hp2.street0_3BDone as <signed>integer)              AS TB_0,
2246                            cast(hp2.street0_4BChance as <signed>integer)            AS FB_opp_0,
2247                            cast(hp2.street0_4BDone as <signed>integer)              AS FB_0,
2248                            cast(hp2.street0_C4BChance as <signed>integer)           AS CFB_opp_0,
2249                            cast(hp2.street0_C4BDone as <signed>integer)             AS CFB_0,
2250                            cast(hp2.street0_FoldTo3BChance as <signed>integer)      AS F3B_opp_0,
2251                            cast(hp2.street0_FoldTo3BDone as <signed>integer)        AS F3B_0,
2252                            cast(hp2.street0_FoldTo4BChance as <signed>integer)      AS F4B_opp_0,
2253                            cast(hp2.street0_FoldTo4BDone as <signed>integer)        AS F4B_0,
2254                            cast(hp2.street0_SqueezeChance as <signed>integer)       AS SQZ_opp_0,
2255                            cast(hp2.street0_SqueezeDone as <signed>integer)         AS SQZ_0,
2256                            cast(hp2.raiseToStealChance as <signed>integer)          AS RTS_opp,
2257                            cast(hp2.raiseToStealDone as <signed>integer)            AS RTS,
2258                            cast(hp2.success_Steal as <signed>integer)               AS SUC_ST,
2259                            cast(hp2.street1Seen as <signed>integer)                 AS saw_f,
2260                            cast(hp2.street1Seen as <signed>integer)                 AS saw_1,
2261                            cast(hp2.street2Seen as <signed>integer)                 AS saw_2,
2262                            cast(hp2.street3Seen as <signed>integer)                 AS saw_3,
2263                            cast(hp2.street4Seen as <signed>integer)                 AS saw_4,
2264                            cast(hp2.sawShowdown as <signed>integer)                 AS sd,
2265                            cast(hp2.street1Aggr as <signed>integer)                 AS aggr_1,
2266                            cast(hp2.street2Aggr as <signed>integer)                 AS aggr_2,
2267                            cast(hp2.street3Aggr as <signed>integer)                 AS aggr_3,
2268                            cast(hp2.street4Aggr as <signed>integer)                 AS aggr_4,
2269                            cast(hp2.otherRaisedStreet1 as <signed>integer)          AS was_raised_1,
2270                            cast(hp2.otherRaisedStreet2 as <signed>integer)          AS was_raised_2,
2271                            cast(hp2.otherRaisedStreet3 as <signed>integer)          AS was_raised_3,
2272                            cast(hp2.otherRaisedStreet4 as <signed>integer)          AS was_raised_4,
2273                            cast(hp2.foldToOtherRaisedStreet1 as <signed>integer)    AS f_freq_1,
2274                            cast(hp2.foldToOtherRaisedStreet2 as <signed>integer)    AS f_freq_2,
2275                            cast(hp2.foldToOtherRaisedStreet3 as <signed>integer)    AS f_freq_3,
2276                            cast(hp2.foldToOtherRaisedStreet4 as <signed>integer)    AS f_freq_4,
2277                            cast(hp2.wonWhenSeenStreet1 as <signed>integer)          AS w_w_s_1,
2278                            cast(hp2.wonAtSD as <signed>integer)                     AS wmsd,
2279                            cast(hp2.raiseFirstInChance as <signed>integer)          AS steal_opp,
2280                            cast(hp2.raisedFirstIn as <signed>integer)               AS steal,
2281                            cast(hp2.foldSbToStealChance as <signed>integer)         AS SBstolen,
2282                            cast(hp2.foldedSbToSteal as <signed>integer)             AS SBnotDef,
2283                            cast(hp2.foldBbToStealChance as <signed>integer)         AS BBstolen,
2284                            cast(hp2.foldedBbToSteal as <signed>integer)             AS BBnotDef,
2285                            cast(hp2.street1CBChance as <signed>integer)             AS CB_opp_1,
2286                            cast(hp2.street1CBDone as <signed>integer)               AS CB_1,
2287                            cast(hp2.street2CBChance as <signed>integer)             AS CB_opp_2,
2288                            cast(hp2.street2CBDone as <signed>integer)               AS CB_2,
2289                            cast(hp2.street3CBChance as <signed>integer)             AS CB_opp_3,
2290                            cast(hp2.street3CBDone as <signed>integer)               AS CB_3,
2291                            cast(hp2.street4CBChance as <signed>integer)             AS CB_opp_4,
2292                            cast(hp2.street4CBDone as <signed>integer)               AS CB_4,
2293                            cast(hp2.foldToStreet1CBChance as <signed>integer)       AS f_cb_opp_1,
2294                            cast(hp2.foldToStreet1CBDone as <signed>integer)         AS f_cb_1,
2295                            cast(hp2.foldToStreet2CBChance as <signed>integer)       AS f_cb_opp_2,
2296                            cast(hp2.foldToStreet2CBDone as <signed>integer)         AS f_cb_2,
2297                            cast(hp2.foldToStreet3CBChance as <signed>integer)       AS f_cb_opp_3,
2298                            cast(hp2.foldToStreet3CBDone as <signed>integer)         AS f_cb_3,
2299                            cast(hp2.foldToStreet4CBChance as <signed>integer)       AS f_cb_opp_4,
2300                            cast(hp2.foldToStreet4CBDone as <signed>integer)         AS f_cb_4,
2301                            cast(hp2.totalProfit as <signed>integer)                 AS net,
2302                            cast(gt.bigblind as <signed>integer)                     AS bigblind,
2303                            cast(hp2.street1CheckCallRaiseChance as <signed>integer) AS ccr_opp_1,
2304                            cast(hp2.street1CheckCallRaiseDone as <signed>integer)   AS ccr_1,
2305                            cast(hp2.street2CheckCallRaiseChance as <signed>integer) AS ccr_opp_2,
2306                            cast(hp2.street2CheckCallRaiseDone as <signed>integer)   AS ccr_2,
2307                            cast(hp2.street3CheckCallRaiseChance as <signed>integer) AS ccr_opp_3,
2308                            cast(hp2.street3CheckCallRaiseDone as <signed>integer)   AS ccr_3,
2309                            cast(hp2.street4CheckCallRaiseChance as <signed>integer) AS ccr_opp_4,
2310                            cast(hp2.street4CheckCallRaiseDone as <signed>integer)   AS ccr_4,
2311                            cast(hp2.street0Calls as <signed>integer)                AS call_0,
2312                            cast(hp2.street1Calls as <signed>integer)                AS call_1,
2313                            cast(hp2.street2Calls as <signed>integer)                AS call_2,
2314                            cast(hp2.street3Calls as <signed>integer)                AS call_3,
2315                            cast(hp2.street4Calls as <signed>integer)                AS call_4,
2316                            cast(hp2.street0Bets as <signed>integer)                 AS bet_0,
2317                            cast(hp2.street1Bets as <signed>integer)                 AS bet_1,
2318                            cast(hp2.street2Bets as <signed>integer)                 AS bet_2,
2319                            cast(hp2.street3Bets as <signed>integer)                 AS bet_3,
2320                            cast(hp2.street4Bets as <signed>integer)                 AS bet_4,
2321                            cast(hp2.street0Raises as <signed>integer)               AS raise_0,
2322                            cast(hp2.street1Raises as <signed>integer)               AS raise_1,
2323                            cast(hp2.street2Raises as <signed>integer)               AS raise_2,
2324                            cast(hp2.street3Raises as <signed>integer)               AS raise_3,
2325                            cast(hp2.street4Raises as <signed>integer)               AS raise_4
2326                          FROM Hands h                                                  /* this hand */
2327                          INNER JOIN Hands h2         ON (    h2.id >= %s           /* other hands */
2328                                                          AND h2.tableName = h.tableName)
2329                          INNER JOIN HandsPlayers hp  ON (h.id = hp.handId)        /* players in this hand */
2330                          INNER JOIN HandsPlayers hp2 ON (    hp2.playerId+0 = hp.playerId+0
2331                                                          AND hp2.handId = h2.id)  /* other hands by these players */
2332                          INNER JOIN Players p        ON (p.id = hp2.PlayerId+0)
2333                          INNER JOIN Gametypes gt     ON (gt.id = h2.gametypeId)
2334                     WHERE h.id = %s
2335                     /* check activeseats once this data returned (don't want to do that here as it might
2336                        assume a session ended just because the number of seats dipped for a few hands)
2337                     */
2338                     AND   (   /* 2 separate parts for hero and opponents */
2339                               (    hp2.playerId != %s
2340                                AND h2.seats between %s and %s
2341                               )
2342                            OR
2343                               (    hp2.playerId = %s
2344                                AND h2.seats between %s and %s
2345                               )
2346                           )
2347                     ORDER BY h.startTime desc, hp2.PlayerId
2348                     /* order rows by handstart descending so that we can stop reading rows when
2349                        there's a gap over X minutes between hands (ie. when we get back to start of
2350                        the session */
2351                 """
2352
2353         self.query['get_players_from_hand'] = """
2354                 SELECT HandsPlayers.playerId, seatNo, name
2355                 FROM  HandsPlayers INNER JOIN Players ON (HandsPlayers.playerId = Players.id)
2356                 WHERE handId = %s
2357             """
2358 #                    WHERE handId = %s AND Players.id LIKE %s
2359
2360         self.query['get_winners_from_hand'] = """
2361                 SELECT name, winnings
2362                 FROM HandsPlayers, Players
2363                 WHERE winnings > 0
2364                     AND Players.id = HandsPlayers.playerId
2365                     AND handId = %s;
2366             """
2367
2368         self.query['get_table_name'] = """
2369                 SELECT h.tableName, gt.maxSeats, gt.category, gt.type, s.id, s.name
2370                      , count(1) as numseats
2371                 FROM Hands h, Gametypes gt, Sites s, HandsPlayers hp
2372                 WHERE h.id = %s
2373                     AND   gt.id = h.gametypeId
2374                     AND   s.id = gt.siteID
2375                     AND   hp.handId = h.id
2376                 GROUP BY h.tableName, gt.maxSeats, gt.category, gt.type, s.id, s.name
2377             """
2378
2379         self.query['get_actual_seat'] = """
2380                 select seatNo
2381                 from HandsPlayers
2382                 where HandsPlayers.handId = %s
2383                 and   HandsPlayers.playerId  = (select Players.id from Players
2384                                                 where Players.name = %s)
2385             """
2386
2387         self.query['get_cards'] = """
2388                 select
2389                     seatNo     AS seat_number,
2390                     card1, /*card1Value, card1Suit, */
2391                     card2, /*card2Value, card2Suit, */
2392                     card3, /*card3Value, card3Suit, */
2393                     card4, /*card4Value, card4Suit, */
2394                     card5, /*card5Value, card5Suit, */
2395                     card6, /*card6Value, card6Suit, */
2396                     card7  /*card7Value, card7Suit */
2397                 from HandsPlayers, Players
2398                 where handID = %s and HandsPlayers.playerId = Players.id
2399                 order by seatNo
2400             """
2401
2402         self.query['get_common_cards'] = """
2403                 select
2404                 boardcard1,
2405                 boardcard2,
2406                 boardcard3,
2407                 boardcard4,
2408                 boardcard5
2409                 from Hands
2410                 where Id = %s
2411             """
2412
2413         if db_server == 'mysql':
2414             self.query['get_hand_1day_ago'] = """
2415                 select coalesce(max(id),0)
2416                 from Hands
2417                 where startTime < date_sub(utc_timestamp(), interval '1' day)"""
2418         elif db_server == 'postgresql':
2419             self.query['get_hand_1day_ago'] = """
2420                 select coalesce(max(id),0)
2421                 from Hands
2422                 where startTime < now() at time zone 'UTC' - interval '1 day'"""
2423         elif db_server == 'sqlite':
2424             self.query['get_hand_1day_ago'] = """
2425                 select coalesce(max(id),0)
2426                 from Hands
2427                 where startTime < datetime(strftime('%J', 'now') - 1)"""
2428
2429         # not used yet ...
2430         # gets a date, would need to use handsplayers (not hudcache) to get exact hand Id
2431         if db_server == 'mysql':
2432             self.query['get_date_nhands_ago'] = """
2433                 select concat( 'd', date_format(max(h.startTime), '%Y%m%d') )
2434                 from (select hp.playerId
2435                             ,coalesce(greatest(max(hp.handId)-%s,1),1) as maxminusx
2436                       from HandsPlayers hp
2437                       where hp.playerId = %s
2438                       group by hp.playerId) hp2
2439                 inner join HandsPlayers hp3 on (    hp3.handId <= hp2.maxminusx
2440                                                 and hp3.playerId = hp2.playerId)
2441                 inner join Hands h          on (h.id = hp3.handId)
2442                 """
2443         elif db_server == 'postgresql':
2444             self.query['get_date_nhands_ago'] = """
2445                 select 'd' || to_char(max(h3.startTime), 'YYMMDD')
2446                 from (select hp.playerId
2447                             ,coalesce(greatest(max(hp.handId)-%s,1),1) as maxminusx
2448                       from HandsPlayers hp
2449                       where hp.playerId = %s
2450                       group by hp.playerId) hp2
2451                 inner join HandsPlayers hp3 on (    hp3.handId <= hp2.maxminusx
2452                                                 and hp3.playerId = hp2.playerId)
2453                 inner join Hands h          on (h.id = hp3.handId)
2454                 """
2455         elif db_server == 'sqlite': # untested guess at query:
2456             self.query['get_date_nhands_ago'] = """
2457                 select 'd' || strftime(max(h3.startTime), 'YYMMDD')
2458                 from (select hp.playerId
2459                             ,coalesce(greatest(max(hp.handId)-%s,1),1) as maxminusx
2460                       from HandsPlayers hp
2461                       where hp.playerId = %s
2462                       group by hp.playerId) hp2
2463                 inner join HandsPlayers hp3 on (    hp3.handId <= hp2.maxminusx
2464                                                 and hp3.playerId = hp2.playerId)
2465                 inner join Hands h          on (h.id = hp3.handId)
2466                 """
2467
2468         # Used in *Filters:
2469         #self.query['getLimits'] = already defined further up
2470         self.query['getLimits2'] = """SELECT DISTINCT type, limitType, bigBlind
2471                                       from Gametypes
2472                                       ORDER by type, limitType DESC, bigBlind DESC"""
2473         self.query['getLimits3'] = """select DISTINCT type
2474                                            , gt.limitType
2475                                            , case type
2476                                                  when 'ring' then bigBlind
2477 -                                                else buyin
2478 -                                            end as bb_or_buyin
2479                                       from Gametypes gt
2480                                       cross join TourneyTypes tt
2481                                       order by type, gt.limitType DESC, bb_or_buyin DESC"""
2482         self.query['getCashLimits'] = """select DISTINCT type
2483                                            , limitType
2484                                            , bigBlind as bb_or_buyin
2485                                       from Gametypes gt
2486                                       WHERE type = 'ring'
2487                                       order by type, limitType DESC, bb_or_buyin DESC"""
2488         #FIXME: Some stats not added to DetailedStats (miss raise to steal)
2489         if db_server == 'mysql':
2490             self.query['playerDetailedStats'] = """
2491                      select  <hgametypeId>                                                          AS hgametypeid
2492                             ,<playerName>                                                           AS pname
2493                             ,gt.base
2494                             ,gt.category
2495                             ,upper(gt.limitType)                                                    AS limittype
2496                             ,s.name
2497                             ,min(gt.bigBlind)                                                       AS minbigblind
2498                             ,max(gt.bigBlind)                                                       AS maxbigblind
2499                             /*,<hcgametypeId>                                                         AS gtid*/
2500                             ,<position>                                                             AS plposition
2501                             ,count(1)                                                               AS n
2502                             ,100.0*sum(cast(hp.street0VPI as <signed>integer))/count(1)             AS vpip
2503                             ,100.0*sum(cast(hp.street0Aggr as <signed>integer))/count(1)            AS pfr
2504                             ,case when sum(cast(hp.street0_3Bchance as <signed>integer)) = 0 then -999
2505                                   else 100.0*sum(cast(hp.street0_3Bdone as <signed>integer))/sum(cast(hp.street0_3Bchance as <signed>integer))
2506                              end                                                                    AS pf3
2507                             ,case when sum(cast(hp.street0_4Bchance as <signed>integer)) = 0 then -999
2508                                   else 100.0*sum(cast(hp.street0_4Bdone as <signed>integer))/sum(cast(hp.street0_4Bchance as <signed>integer))
2509                              end                                                                    AS pf4
2510                             ,case when sum(cast(hp.street0_FoldTo3Bchance as <signed>integer)) = 0 then -999
2511                                   else 100.0*sum(cast(hp.street0_FoldTo3Bdone as <signed>integer))/sum(cast(hp.street0_FoldTo3Bchance as <signed>integer))
2512                              end                                                                    AS pff3
2513                             ,case when sum(cast(hp.street0_FoldTo4Bchance as <signed>integer)) = 0 then -999
2514                                   else 100.0*sum(cast(hp.street0_FoldTo4Bdone as <signed>integer))/sum(cast(hp.street0_FoldTo4Bchance as <signed>integer))
2515                              end                                                                    AS pff4
2516
2517                             ,case when sum(cast(hp.raiseFirstInChance as <signed>integer)) = 0 then -999
2518                                   else 100.0 * sum(cast(hp.raisedFirstIn as <signed>integer)) /
2519                                        sum(cast(hp.raiseFirstInChance as <signed>integer))
2520                              end                                                                    AS rfi
2521                             ,case when sum(case hp.position
2522                                            when 'S' then cast(hp.raiseFirstInChance as <signed>integer)
2523                                            when '0' then cast(hp.raiseFirstInChance as <signed>integer)
2524                                            when '1' then cast(hp.raiseFirstInChance as <signed>integer)
2525                                            else 0
2526                                            end
2527                                           ) = 0 then -999
2528                                   else 100.0 *
2529                                        sum(case hp.position
2530                                            when 'S' then cast(hp.raisedFirstIn as <signed>integer)
2531                                            when '0' then cast(hp.raisedFirstIn as <signed>integer)
2532                                            when '1' then cast(hp.raisedFirstIn as <signed>integer)
2533                                            else 0
2534                                            end
2535                                           ) /
2536                                        sum(case hp.position
2537                                            when 'S' then cast(hp.raiseFirstInChance as <signed>integer)
2538                                            when '0' then cast(hp.raiseFirstInChance as <signed>integer)
2539                                            when '1' then cast(hp.raiseFirstInChance as <signed>integer)
2540                                            else 0
2541                                            end
2542                                           )
2543                              end                                                                    AS steals
2544                             ,case when sum(cast(hp.success_Steal as <signed>integer)) = 0 then -999
2545                                   else 100.0 *
2546                                        sum(cast(hp.success_Steal as <signed>integer))
2547                                            /
2548                                        sum(case hp.position
2549                                            when 'S' then cast(hp.raisedFirstIn as <signed>integer)
2550                                            when '0' then cast(hp.raisedFirstIn as <signed>integer)
2551                                            when '1' then cast(hp.raisedFirstIn as <signed>integer)
2552                                            else 0
2553                                            end
2554                                           )
2555                              end                                                                    AS suc_steal
2556                             ,100.0*sum(cast(hp.street1Seen as <signed>integer))/count(1)            AS saw_f
2557                             ,100.0*sum(cast(hp.sawShowdown as <signed>integer))/count(1)            AS sawsd
2558                             ,case when sum(cast(hp.street1Seen as <signed>integer)) = 0 then -999
2559                                   else 100.0*sum(cast(hp.sawShowdown as <signed>integer))/sum(cast(hp.street1Seen as <signed>integer))
2560                              end                                                                    AS wtsdwsf
2561                             ,case when sum(cast(hp.sawShowdown as <signed>integer)) = 0 then -999
2562                                   else 100.0*sum(cast(hp.wonAtSD as <signed>integer))/sum(cast(hp.sawShowdown as <signed>integer))
2563                              end                                                                    AS wmsd
2564                             ,case when sum(cast(hp.street1Seen as <signed>integer)) = 0 then -999
2565                                   else 100.0*sum(cast(hp.street1Aggr as <signed>integer))/sum(cast(hp.street1Seen as <signed>integer))
2566                              end                                                                    AS flafq
2567                             ,case when sum(cast(hp.street2Seen as <signed>integer)) = 0 then -999
2568                                   else 100.0*sum(cast(hp.street2Aggr as <signed>integer))/sum(cast(hp.street2Seen as <signed>integer))
2569                              end                                                                    AS tuafq
2570                             ,case when sum(cast(hp.street3Seen as <signed>integer)) = 0 then -999
2571                                  else 100.0*sum(cast(hp.street3Aggr as <signed>integer))/sum(cast(hp.street3Seen as <signed>integer))
2572                              end                                                                    AS rvafq
2573                             ,case when sum(cast(hp.street1Seen as <signed>integer))+sum(cast(hp.street2Seen as <signed>integer))+sum(cast(hp.street3Seen as <signed>integer)) = 0 then -999
2574                                  else 100.0*(sum(cast(hp.street1Aggr as <signed>integer))+sum(cast(hp.street2Aggr as <signed>integer))+sum(cast(hp.street3Aggr as <signed>integer)))
2575                                           /(sum(cast(hp.street1Seen as <signed>integer))+sum(cast(hp.street2Seen as <signed>integer))+sum(cast(hp.street3Seen as <signed>integer)))
2576                              end                                                                    AS pofafq
2577                             ,case when sum(cast(hp.street1Calls as <signed>integer))+ sum(cast(hp.street2Calls as <signed>integer))+ sum(cast(hp.street3Calls as <signed>integer))+ sum(cast(hp.street4Calls as <signed>integer)) = 0 then -999
2578                                  else (sum(cast(hp.street1Aggr as <signed>integer)) + sum(cast(hp.street2Aggr as <signed>integer)) + sum(cast(hp.street3Aggr as <signed>integer)) + sum(cast(hp.street4Aggr as <signed>integer)))
2579                                      /(0.0+sum(cast(hp.street1Calls as <signed>integer))+ sum(cast(hp.street2Calls as <signed>integer))+ sum(cast(hp.street3Calls as <signed>integer))+ sum(cast(hp.street4Calls as <signed>integer)))
2580                              end                                                                    AS aggfac
2581                             ,100.0*(sum(cast(hp.street1Aggr as <signed>integer)) + sum(cast(hp.street2Aggr as <signed>integer)) + sum(cast(hp.street3Aggr as <signed>integer)) + sum(cast(hp.street4Aggr as <signed>integer)))
2582                                        / ((sum(cast(hp.foldToOtherRaisedStreet1 as <signed>integer))+ sum(cast(hp.foldToOtherRaisedStreet2 as <signed>integer))+ sum(cast(hp.foldToOtherRaisedStreet3 as <signed>integer))+ sum(cast(hp.foldToOtherRaisedStreet4 as <signed>integer))) +
2583                                        (sum(cast(hp.street1Calls as <signed>integer))+ sum(cast(hp.street2Calls as <signed>integer))+ sum(cast(hp.street3Calls as <signed>integer))+ sum(cast(hp.street4Calls as <signed>integer))) +
2584                                        (sum(cast(hp.street1Aggr as <signed>integer)) + sum(cast(hp.street2Aggr as <signed>integer)) + sum(cast(hp.street3Aggr as <signed>integer)) + sum(cast(hp.street4Aggr as <signed>integer))) )
2585                                                                                                     AS aggfrq
2586                             ,100.0*(sum(cast(hp.street1CBDone as <signed>integer)) + sum(cast(hp.street2CBDone as <signed>integer)) + sum(cast(hp.street3CBDone as <signed>integer)) + sum(cast(hp.street4CBDone as <signed>integer)))
2587                                        / (sum(cast(hp.street1CBChance as <signed>integer))+ sum(cast(hp.street2CBChance as <signed>integer))+ sum(cast(hp.street3CBChance as <signed>integer))+ sum(cast(hp.street4CBChance as <signed>integer)))
2588                                                                                                     AS conbet
2589                             ,sum(hp.totalProfit)/100.0                                              AS net
2590                             ,sum(hp.rake)/100.0                                                     AS rake
2591                             ,100.0*avg(hp.totalProfit/(gt.bigBlind+0.0))                            AS bbper100
2592                             ,avg(hp.totalProfit)/100.0                                              AS profitperhand
2593                             ,100.0*avg((hp.totalProfit+hp.rake)/(gt.bigBlind+0.0))                  AS bb100xr
2594                             ,avg((hp.totalProfit+hp.rake)/100.0)                                    AS profhndxr
2595                             ,avg(h.seats+0.0)                                                       AS avgseats
2596                             ,variance(hp.totalProfit/100.0)                                         AS variance
2597                       from HandsPlayers hp
2598                            inner join Hands h       on  (h.id = hp.handId)
2599                            inner join Gametypes gt  on  (gt.Id = h.gametypeId)
2600                            inner join Sites s       on  (s.Id = gt.siteId)
2601                            inner join Players p     on  (p.Id = hp.playerId)
2602                       where hp.playerId in <player_test>
2603                       <game_test>
2604                       <site_test>
2605                       <currency_test>
2606                       /*and   hp.tourneysPlayersId IS NULL*/
2607                       and   h.seats <seats_test>
2608                       <flagtest>
2609                       <cardstest>
2610                       <gtbigBlind_test>
2611                       and   date_format(h.startTime, '%Y-%m-%d %T') <datestest>
2612                       group by hgametypeId
2613                               ,pname
2614                               ,gt.base
2615                               ,gt.category
2616                               <groupbyseats>
2617                               ,plposition
2618                               ,upper(gt.limitType)
2619                               ,s.name
2620                       having 1 = 1 <havingclause>
2621                       order by pname
2622                               ,gt.base
2623                               ,gt.category
2624                               <orderbyseats>
2625                               ,case <position> when 'B' then 'B'
2626                                                when 'S' then 'S'
2627                                                else concat('Z', <position>)
2628                                end
2629                               <orderbyhgametypeId>
2630                               ,upper(gt.limitType) desc
2631                               ,maxbigblind desc
2632                               ,s.name
2633                       """
2634         elif db_server == 'postgresql':
2635             self.query['playerDetailedStats'] = """
2636                      select  <hgametypeId>                                                          AS hgametypeid
2637                             ,<playerName>                                                           AS pname
2638                             ,gt.base
2639                             ,gt.category
2640                             ,upper(gt.limitType)                                                    AS limittype
2641                             ,s.name
2642                             ,min(gt.bigBlind)                                                       AS minbigblind
2643                             ,max(gt.bigBlind)                                                       AS maxbigblind
2644                             /*,<hcgametypeId>                                                       AS gtid*/
2645                             ,<position>                                                             AS plposition
2646                             ,count(1)                                                               AS n
2647                             ,100.0*sum(cast(hp.street0VPI as <signed>integer))/count(1)             AS vpip
2648                             ,100.0*sum(cast(hp.street0Aggr as <signed>integer))/count(1)            AS pfr
2649                             ,case when sum(cast(hp.street0_3Bchance as <signed>integer)) = 0 then -999
2650                                   else 100.0*sum(cast(hp.street0_3Bdone as <signed>integer))/sum(cast(hp.street0_3Bchance as <signed>integer))
2651                              end                                                                    AS pf3
2652                             ,case when sum(cast(hp.street0_4Bchance as <signed>integer)) = 0 then -999
2653                                   else 100.0*sum(cast(hp.street0_4Bdone as <signed>integer))/sum(cast(hp.street0_4Bchance as <signed>integer))
2654                              end                                                                    AS pf4
2655                             ,case when sum(cast(hp.street0_FoldTo3Bchance as <signed>integer)) = 0 then -999
2656                                   else 100.0*sum(cast(hp.street0_FoldTo3Bdone as <signed>integer))/sum(cast(hp.street0_FoldTo3Bchance as <signed>integer))
2657                              end                                                                    AS pff3
2658                             ,case when sum(cast(hp.street0_FoldTo4Bchance as <signed>integer)) = 0 then -999
2659                                   else 100.0*sum(cast(hp.street0_FoldTo4Bdone as <signed>integer))/sum(cast(hp.street0_FoldTo4Bchance as <signed>integer))
2660                              end                                                                    AS pff4
2661                             ,case when sum(cast(hp.raiseFirstInChance as <signed>integer)) = 0 then -999
2662                                   else 100.0 * sum(cast(hp.raisedFirstIn as <signed>integer)) /
2663                                        sum(cast(hp.raiseFirstInChance as <signed>integer))
2664                              end                                                                    AS rfi
2665                             ,case when sum(case hp.position
2666                                            when 'S' then cast(hp.raiseFirstInChance as <signed>integer)
2667                                            when '0' then cast(hp.raiseFirstInChance as <signed>integer)
2668                                            when '1' then cast(hp.raiseFirstInChance as <signed>integer)
2669                                            else 0
2670                                            end
2671                                           ) = 0 then -999
2672                                   else 100.0 *
2673                                        sum(case hp.position
2674                                            when 'S' then cast(hp.raisedFirstIn as <signed>integer)
2675                                            when '0' then cast(hp.raisedFirstIn as <signed>integer)
2676                                            when '1' then cast(hp.raisedFirstIn as <signed>integer)
2677                                            else 0
2678                                            end
2679                                           ) /
2680                                        sum(case hp.position
2681                                            when 'S' then cast(hp.raiseFirstInChance as <signed>integer)
2682                                            when '0' then cast(hp.raiseFirstInChance as <signed>integer)
2683                                            when '1' then cast(hp.raiseFirstInChance as <signed>integer)
2684                                            else 0
2685                                            end
2686                                           )
2687                              end                                                                    AS steals
2688                             ,case when sum(cast(hp.success_Steal as <signed>integer)) = 0 then -999
2689                                   else 100.0 *
2690                                        sum(cast(hp.success_Steal as <signed>integer))
2691                                            /
2692                                        sum(case hp.position
2693                                            when 'S' then cast(hp.raisedFirstIn as <signed>integer)
2694                                            when '0' then cast(hp.raisedFirstIn as <signed>integer)
2695                                            when '1' then cast(hp.raisedFirstIn as <signed>integer)
2696                                            else 0
2697                                            end
2698                                           )
2699                              end                                                                    AS suc_steal
2700                             ,100.0*sum(cast(hp.street1Seen as <signed>integer))/count(1)            AS saw_f
2701                             ,100.0*sum(cast(hp.sawShowdown as <signed>integer))/count(1)            AS sawsd
2702                             ,case when sum(cast(hp.street1Seen as <signed>integer)) = 0 then -999
2703                                   else 100.0*sum(cast(hp.sawShowdown as <signed>integer))/sum(cast(hp.street1Seen as <signed>integer))
2704                              end                                                                    AS wtsdwsf
2705                             ,case when sum(cast(hp.sawShowdown as <signed>integer)) = 0 then -999
2706                                   else 100.0*sum(cast(hp.wonAtSD as <signed>integer))/sum(cast(hp.sawShowdown as <signed>integer))
2707                              end                                                                    AS wmsd
2708                             ,case when sum(cast(hp.street1Seen as <signed>integer)) = 0 then -999
2709                                   else 100.0*sum(cast(hp.street1Aggr as <signed>integer))/sum(cast(hp.street1Seen as <signed>integer))
2710                              end                                                                    AS flafq
2711                             ,case when sum(cast(hp.street2Seen as <signed>integer)) = 0 then -999
2712                                   else 100.0*sum(cast(hp.street2Aggr as <signed>integer))/sum(cast(hp.street2Seen as <signed>integer))
2713                              end                                                                    AS tuafq
2714                             ,case when sum(cast(hp.street3Seen as <signed>integer)) = 0 then -999
2715                                  else 100.0*sum(cast(hp.street3Aggr as <signed>integer))/sum(cast(hp.street3Seen as <signed>integer))
2716                              end                                                                    AS rvafq
2717                             ,case when sum(cast(hp.street1Seen as <signed>integer))+sum(cast(hp.street2Seen as <signed>integer))+sum(cast(hp.street3Seen as <signed>integer)) = 0 then -999
2718                                  else 100.0*(sum(cast(hp.street1Aggr as <signed>integer))+sum(cast(hp.street2Aggr as <signed>integer))+sum(cast(hp.street3Aggr as <signed>integer)))
2719                                           /(sum(cast(hp.street1Seen as <signed>integer))+sum(cast(hp.street2Seen as <signed>integer))+sum(cast(hp.street3Seen as <signed>integer)))
2720                              end                                                                    AS pofafq
2721                             ,case when sum(cast(hp.street1Calls as <signed>integer))+ sum(cast(hp.street2Calls as <signed>integer))+ sum(cast(hp.street3Calls as <signed>integer))+ sum(cast(hp.street4Calls as <signed>integer)) = 0 then -999
2722                                  else (sum(cast(hp.street1Aggr as <signed>integer)) + sum(cast(hp.street2Aggr as <signed>integer)) + sum(cast(hp.street3Aggr as <signed>integer)) + sum(cast(hp.street4Aggr as <signed>integer)))
2723                                      /(0.0+sum(cast(hp.street1Calls as <signed>integer))+ sum(cast(hp.street2Calls as <signed>integer))+ sum(cast(hp.street3Calls as <signed>integer))+ sum(cast(hp.street4Calls as <signed>integer)))
2724                              end                                                                    AS aggfac
2725                             ,case when
2726                                 sum(cast(hp.foldToOtherRaisedStreet1 as <signed>integer))+ sum(cast(hp.foldToOtherRaisedStreet2 as <signed>integer))+ sum(cast(hp.foldToOtherRaisedStreet3 as <signed>integer))+ sum(cast(hp.foldToOtherRaisedStreet4 as <signed>integer))+
2727                                 sum(cast(hp.street1Calls as <signed>integer))+ sum(cast(hp.street2Calls as <signed>integer))+ sum(cast(hp.street3Calls as <signed>integer))+ sum(cast(hp.street4Calls as <signed>integer))+
2728                                 sum(cast(hp.street1Aggr as <signed>integer))+ sum(cast(hp.street2Aggr as <signed>integer))+ sum(cast(hp.street3Aggr as <signed>integer))+ sum(cast(hp.street4Aggr as <signed>integer))
2729                                 = 0 then -999
2730                             else
2731                             100.0*(sum(cast(hp.street1Aggr as <signed>integer)) + sum(cast(hp.street2Aggr as <signed>integer)) + sum(cast(hp.street3Aggr as <signed>integer)) + sum(cast(hp.street4Aggr as <signed>integer)))
2732                                        / ((sum(cast(hp.foldToOtherRaisedStreet1 as <signed>integer))+ sum(cast(hp.foldToOtherRaisedStreet2 as <signed>integer))+ sum(cast(hp.foldToOtherRaisedStreet3 as <signed>integer))+ sum(cast(hp.foldToOtherRaisedStreet4 as <signed>integer))) +
2733                                        (sum(cast(hp.street1Calls as <signed>integer))+ sum(cast(hp.street2Calls as <signed>integer))+ sum(cast(hp.street3Calls as <signed>integer))+ sum(cast(hp.street4Calls as <signed>integer))) +
2734                                        (sum(cast(hp.street1Aggr as <signed>integer)) + sum(cast(hp.street2Aggr as <signed>integer)) + sum(cast(hp.street3Aggr as <signed>integer)) + sum(cast(hp.street4Aggr as <signed>integer))) )
2735                               end                                                                   AS aggfrq
2736                             ,case when
2737                                 sum(cast(hp.street1CBChance as <signed>integer))+
2738                                 sum(cast(hp.street2CBChance as <signed>integer))+
2739                                 sum(cast(hp.street3CBChance as <signed>integer))+
2740                                 sum(cast(hp.street4CBChance as <signed>integer)) = 0 then -999
2741                             else
2742                              100.0*(sum(cast(hp.street1CBDone as <signed>integer)) + sum(cast(hp.street2CBDone as <signed>integer)) + sum(cast(hp.street3CBDone as <signed>integer)) + sum(cast(hp.street4CBDone as <signed>integer)))
2743                                        / (sum(cast(hp.street1CBChance as <signed>integer))+ sum(cast(hp.street2CBChance as <signed>integer))+ sum(cast(hp.street3CBChance as <signed>integer))+ sum(cast(hp.street4CBChance as <signed>integer)))
2744                             end                                                                     AS conbet
2745                             ,sum(hp.totalProfit)/100.0                                              AS net
2746                             ,sum(hp.rake)/100.0                                                     AS rake
2747                             ,100.0*avg(hp.totalProfit/(gt.bigBlind+0.0))                            AS bbper100
2748                             ,avg(hp.totalProfit)/100.0                                              AS profitperhand
2749                             ,100.0*avg((hp.totalProfit+hp.rake)/(gt.bigBlind+0.0))                  AS bb100xr
2750                             ,avg((hp.totalProfit+hp.rake)/100.0)                                    AS profhndxr
2751                             ,avg(h.seats+0.0)                                                       AS avgseats
2752                             ,variance(hp.totalProfit/100.0)                                         AS variance
2753                       from HandsPlayers hp
2754                            inner join Hands h       on  (h.id = hp.handId)
2755                            inner join Gametypes gt  on  (gt.Id = h.gametypeId)
2756                            inner join Sites s       on  (s.Id = gt.siteId)
2757                            inner join Players p     on  (p.Id = hp.playerId)
2758                       where hp.playerId in <player_test>
2759                       <game_test>
2760                       <site_test>
2761                       <currency_test>
2762                       /*and   hp.tourneysPlayersId IS NULL*/
2763                       and   h.seats <seats_test>
2764                       <flagtest>
2765                       <cardstest>
2766                       <gtbigBlind_test>
2767                       and   to_char(h.startTime, 'YYYY-MM-DD HH24:MI:SS') <datestest>
2768                       group by hgametypeId
2769                               ,pname
2770                               ,gt.base
2771                               ,gt.category
2772                               <groupbyseats>
2773                               ,plposition
2774                               ,upper(gt.limitType)
2775                               ,s.name
2776                       having 1 = 1 <havingclause>
2777                       order by pname
2778                               ,gt.base
2779                               ,gt.category
2780                               <orderbyseats>
2781                               ,case <position> when 'B' then 'B'
2782                                                when 'S' then 'S'
2783                                                when '0' then 'Y'
2784                                                else 'Z'||<position>
2785                                end
2786                               <orderbyhgametypeId>
2787                               ,upper(gt.limitType) desc
2788                               ,maxbigblind desc
2789                               ,s.name
2790                       """
2791         elif db_server == 'sqlite':
2792             self.query['playerDetailedStats'] = """
2793                      select  <hgametypeId>                                                          AS hgametypeid
2794                             ,<playerName>                                                           AS pname
2795                             ,gt.base
2796                             ,gt.category                                                            AS category
2797                             ,upper(gt.limitType)                                                    AS limittype
2798                             ,s.name                                                                 AS name
2799                             ,min(gt.bigBlind)                                                       AS minbigblind
2800                             ,max(gt.bigBlind)                                                       AS maxbigblind
2801                             /*,<hcgametypeId>                                                       AS gtid*/
2802                             ,<position>                                                             AS plposition
2803                             ,count(1)                                                               AS n
2804                             ,100.0*sum(cast(hp.street0VPI as <signed>integer))/count(1)             AS vpip
2805                             ,100.0*sum(cast(hp.street0Aggr as <signed>integer))/count(1)            AS pfr
2806                             ,case when sum(cast(hp.street0_3Bchance as <signed>integer)) = 0 then -999
2807                                   else 100.0*sum(cast(hp.street0_3Bdone as <signed>integer))/sum(cast(hp.street0_3Bchance as <signed>integer))
2808                              end                                                                    AS pf3
2809                             ,case when sum(cast(hp.street0_4Bchance as <signed>integer)) = 0 then -999
2810                                   else 100.0*sum(cast(hp.street0_4Bdone as <signed>integer))/sum(cast(hp.street0_4Bchance as <signed>integer))
2811                              end                                                                    AS pf4
2812                             ,case when sum(cast(hp.street0_FoldTo3Bchance as <signed>integer)) = 0 then -999
2813                                   else 100.0*sum(cast(hp.street0_FoldTo3Bdone as <signed>integer))/sum(cast(hp.street0_FoldTo3Bchance as <signed>integer))
2814                              end                                                                    AS pff3
2815                             ,case when sum(cast(hp.street0_FoldTo4Bchance as <signed>integer)) = 0 then -999
2816                                   else 100.0*sum(cast(hp.street0_FoldTo4Bdone as <signed>integer))/sum(cast(hp.street0_FoldTo4Bchance as <signed>integer))
2817                              end                                                                    AS pff4
2818                             ,case when sum(cast(hp.raiseFirstInChance as <signed>integer)) = 0 then -999
2819                                   else 100.0 * sum(cast(hp.raisedFirstIn as <signed>integer)) /
2820                                        sum(cast(hp.raiseFirstInChance as <signed>integer))
2821                              end                                                                    AS rfi
2822                             ,case when sum(case hp.position
2823                                            when 'S' then cast(hp.raiseFirstInChance as <signed>integer)
2824                                            when '0' then cast(hp.raiseFirstInChance as <signed>integer)
2825                                            when '1' then cast(hp.raiseFirstInChance as <signed>integer)
2826                                            else 0
2827                                            end
2828                                           ) = 0 then -999
2829                                   else 100.0 *
2830                                        sum(case hp.position
2831                                            when 'S' then cast(hp.raisedFirstIn as <signed>integer)
2832                                            when '0' then cast(hp.raisedFirstIn as <signed>integer)
2833                                            when '1' then cast(hp.raisedFirstIn as <signed>integer)
2834                                            else 0
2835                                            end
2836                                           ) /
2837                                        sum(case hp.position
2838                                            when 'S' then cast(hp.raiseFirstInChance as <signed>integer)
2839                                            when '0' then cast(hp.raiseFirstInChance as <signed>integer)
2840                                            when '1' then cast(hp.raiseFirstInChance as <signed>integer)
2841                                            else 0
2842                                            end
2843                                           )
2844                              end                                                                    AS steals
2845                             ,case when sum(cast(hp.success_Steal as <signed>integer)) = 0 then -999
2846                                   else 100.0 *
2847                                        sum(cast(hp.success_Steal as <signed>integer))
2848                                            /
2849                                        sum(case hp.position
2850                                            when 'S' then cast(hp.raisedFirstIn as <signed>integer)
2851                                            when '0' then cast(hp.raisedFirstIn as <signed>integer)
2852                                            when '1' then cast(hp.raisedFirstIn as <signed>integer)
2853                                            else 0
2854                                            end
2855                                           )
2856                              end                                                                    AS suc_steal
2857                             ,100.0*sum(cast(hp.street1Seen as <signed>integer))/count(1)            AS saw_f
2858                             ,100.0*sum(cast(hp.sawShowdown as <signed>integer))/count(1)            AS sawsd
2859                             ,case when sum(cast(hp.street1Seen as <signed>integer)) = 0 then -999
2860                                   else 100.0*sum(cast(hp.sawShowdown as <signed>integer))/sum(cast(hp.street1Seen as <signed>integer))
2861                              end                                                                    AS wtsdwsf
2862                             ,case when sum(cast(hp.sawShowdown as <signed>integer)) = 0 then -999
2863                                   else 100.0*sum(cast(hp.wonAtSD as <signed>integer))/sum(cast(hp.sawShowdown as <signed>integer))
2864                              end                                                                    AS wmsd
2865                             ,case when sum(cast(hp.street1Seen as <signed>integer)) = 0 then -999
2866                                   else 100.0*sum(cast(hp.street1Aggr as <signed>integer))/sum(cast(hp.street1Seen as <signed>integer))
2867                              end                                                                    AS flafq
2868                             ,case when sum(cast(hp.street2Seen as <signed>integer)) = 0 then -999
2869                                   else 100.0*sum(cast(hp.street2Aggr as <signed>integer))/sum(cast(hp.street2Seen as <signed>integer))
2870                              end                                                                    AS tuafq
2871                             ,case when sum(cast(hp.street3Seen as <signed>integer)) = 0 then -999
2872                                  else 100.0*sum(cast(hp.street3Aggr as <signed>integer))/sum(cast(hp.street3Seen as <signed>integer))
2873                              end                                                                    AS rvafq
2874                             ,case when sum(cast(hp.street1Seen as <signed>integer))+sum(cast(hp.street2Seen as <signed>integer))+sum(cast(hp.street3Seen as <signed>integer)) = 0 then -999
2875                                  else 100.0*(sum(cast(hp.street1Aggr as <signed>integer))+sum(cast(hp.street2Aggr as <signed>integer))+sum(cast(hp.street3Aggr as <signed>integer)))
2876                                           /(sum(cast(hp.street1Seen as <signed>integer))+sum(cast(hp.street2Seen as <signed>integer))+sum(cast(hp.street3Seen as <signed>integer)))
2877                              end                                                                    AS pofafq
2878                             ,case when sum(cast(hp.street1Calls as <signed>integer))+ sum(cast(hp.street2Calls as <signed>integer))+ sum(cast(hp.street3Calls as <signed>integer))+ sum(cast(hp.street4Calls as <signed>integer)) = 0 then -999
2879                                  else (sum(cast(hp.street1Aggr as <signed>integer)) + sum(cast(hp.street2Aggr as <signed>integer)) + sum(cast(hp.street3Aggr as <signed>integer)) + sum(cast(hp.street4Aggr as <signed>integer)))
2880                                      /(0.0+sum(cast(hp.street1Calls as <signed>integer))+ sum(cast(hp.street2Calls as <signed>integer))+ sum(cast(hp.street3Calls as <signed>integer))+ sum(cast(hp.street4Calls as <signed>integer)))
2881                              end                                                                    AS aggfac
2882                             ,100.0*(sum(cast(hp.street1Aggr as <signed>integer)) + sum(cast(hp.street2Aggr as <signed>integer)) + sum(cast(hp.street3Aggr as <signed>integer)) + sum(cast(hp.street4Aggr as <signed>integer)))
2883                                        / ((sum(cast(hp.foldToOtherRaisedStreet1 as <signed>integer))+ sum(cast(hp.foldToOtherRaisedStreet2 as <signed>integer))+ sum(cast(hp.foldToOtherRaisedStreet3 as <signed>integer))+ sum(cast(hp.foldToOtherRaisedStreet4 as <signed>integer))) +
2884                                        (sum(cast(hp.street1Calls as <signed>integer))+ sum(cast(hp.street2Calls as <signed>integer))+ sum(cast(hp.street3Calls as <signed>integer))+ sum(cast(hp.street4Calls as <signed>integer))) +
2885                                        (sum(cast(hp.street1Aggr as <signed>integer)) + sum(cast(hp.street2Aggr as <signed>integer)) + sum(cast(hp.street3Aggr as <signed>integer)) + sum(cast(hp.street4Aggr as <signed>integer))) )
2886                                                                                                     AS aggfrq
2887                             ,100.0*(sum(cast(hp.street1CBDone as <signed>integer)) + sum(cast(hp.street2CBDone as <signed>integer)) + sum(cast(hp.street3CBDone as <signed>integer)) + sum(cast(hp.street4CBDone as <signed>integer)))
2888                                        / (sum(cast(hp.street1CBChance as <signed>integer))+ sum(cast(hp.street2CBChance as <signed>integer))+ sum(cast(hp.street3CBChance as <signed>integer))+ sum(cast(hp.street4CBChance as <signed>integer)))
2889                                                                                                     AS conbet
2890                             ,sum(hp.totalProfit)/100.0                                              AS net
2891                             ,sum(hp.rake)/100.0                                                     AS rake
2892                             ,100.0*avg(hp.totalProfit/(gt.bigBlind+0.0))                            AS bbper100
2893                             ,avg(hp.totalProfit)/100.0                                              AS profitperhand
2894                             ,100.0*avg((hp.totalProfit+hp.rake)/(gt.bigBlind+0.0))                  AS bb100xr
2895                             ,avg((hp.totalProfit+hp.rake)/100.0)                                    AS profhndxr
2896                             ,avg(h.seats+0.0)                                                       AS avgseats
2897                             ,variance(hp.totalProfit/100.0)                                         AS variance
2898                       from HandsPlayers hp
2899                            inner join Hands h       on  (h.id = hp.handId)
2900                            inner join Gametypes gt  on  (gt.Id = h.gametypeId)
2901                            inner join Sites s       on  (s.Id = gt.siteId)
2902                            inner join Players p     on  (p.Id = hp.playerId)
2903                       where hp.playerId in <player_test>
2904                       <game_test>
2905                       <site_test>
2906                       <currency_test>
2907                       /*and   hp.tourneysPlayersId IS NULL*/
2908                       and   h.seats <seats_test>
2909                       <flagtest>
2910                       <cardstest>
2911                       <gtbigBlind_test>
2912                       and   datetime(h.startTime) <datestest>
2913                       group by hgametypeId
2914                               ,hp.playerId
2915                               ,gt.base
2916                               ,gt.category
2917                               <groupbyseats>
2918                               ,plposition
2919                               ,upper(gt.limitType)
2920                               ,s.name
2921                       having 1 = 1 <havingclause>
2922                       order by hp.playerId
2923                               ,gt.base
2924                               ,gt.category
2925                               <orderbyseats>
2926                               ,case <position> when 'B' then 'B'
2927                                                when 'S' then 'S'
2928                                                when '0' then 'Y'
2929                                                else 'Z'||<position>
2930                                end
2931                               <orderbyhgametypeId>
2932                               ,upper(gt.limitType) desc
2933                               ,max(gt.bigBlind) desc
2934                               ,s.name
2935                       """
2936
2937         #FIXME: 3/4bet and foldTo don't added four tournaments yet
2938         if db_server == 'mysql':
2939             self.query['tourneyPlayerDetailedStats'] = """
2940                       select s.name                                                                 AS siteName
2941                             ,t.tourneyTypeId                                                        AS tourneyTypeId
2942                             ,tt.currency                                                            AS currency
2943                             ,(CASE
2944                                 WHEN tt.currency = 'USD' THEN tt.buyIn/100.0
2945                                 WHEN tt.currency = 'EUR' THEN tt.buyIn/100.0
2946                                 ELSE tt.buyIn
2947                               END)                                                                  AS buyIn
2948                             ,tt.fee/100.0                                                           AS fee
2949                             ,tt.category                                                            AS category
2950                             ,tt.limitType                                                           AS limitType
2951                             ,p.name                                                                 AS playerName
2952                             ,COUNT(1)                                                               AS tourneyCount
2953                             ,SUM(CASE WHEN tp.rank > 0 THEN 0 ELSE 1 END)                           AS unknownRank
2954                             ,SUM(CASE WHEN winnings > 0 THEN 1 ELSE 0 END)/(COUNT(1) - SUM(CASE WHEN tp.rank > 0 THEN 0 ELSE 1 END)) AS itm
2955                             ,SUM(CASE WHEN rank = 1 THEN 1 ELSE 0 END)                              AS _1st
2956                             ,SUM(CASE WHEN rank = 2 THEN 1 ELSE 0 END)                              AS _2nd
2957                             ,SUM(CASE WHEN rank = 3 THEN 1 ELSE 0 END)                              AS _3rd
2958                             ,SUM(tp.winnings)/100.0                                                 AS won
2959                             ,SUM(CASE WHEN tt.currency = 'USD' THEN (tt.buyIn+tt.fee)/100.0 WHEN tt.currency = 'EUR' THEN (tt.buyIn+tt.fee)/100.0 ELSE tt.buyIn END) AS spent
2960                             ,SUM(tp.winnings)/SUM(tt.buyin+tt.fee)*100.0-100                        AS roi
2961                             ,SUM(tp.winnings-(tt.buyin+tt.fee))/100.0/(COUNT(1)-SUM(CASE WHEN tp.rank > 0 THEN 0 ELSE 1 END)) AS profitPerTourney
2962                       from TourneysPlayers tp
2963                            inner join Tourneys t        on  (t.id = tp.tourneyId)
2964                            inner join TourneyTypes tt   on  (tt.Id = t.tourneyTypeId)
2965                            inner join Sites s           on  (s.Id = tt.siteId)
2966                            inner join Players p         on  (p.Id = tp.playerId)
2967                       where tp.playerId in <nametest> <sitetest>
2968                       and   date_format(t.startTime, '%Y-%m-%d %T') <datestest>
2969                       group by tourneyTypeId, playerName
2970                       order by tourneyTypeId
2971                               ,playerName
2972                               ,siteName"""
2973         elif db_server == 'postgresql':
2974             # sc: itm and profitPerTourney changed to "ELSE 0" to avoid divide by zero error as temp fix
2975             # proper fix should use coalesce() or case ... when ... to work in all circumstances
2976             self.query['tourneyPlayerDetailedStats'] = """
2977                       select s.name                                                                 AS "siteName"
2978                             ,t.tourneyTypeId                                                        AS "tourneyTypeId"
2979                             ,tt.currency                                                            AS "currency"
2980                             ,(CASE
2981                                 WHEN tt.currency = 'USD' THEN tt.buyIn/100.0
2982                                 WHEN tt.currency = 'EUR' THEN tt.buyIn/100.0
2983                                 ELSE tt.buyIn
2984                               END)                                                                  AS "buyIn"
2985                             ,tt.fee/100.0                                                           AS "fee"
2986                             ,tt.category                                                            AS "category"
2987                             ,tt.limitType                                                           AS "limitType"
2988                             ,p.name                                                                 AS "playerName"
2989                             ,COUNT(1)                                                               AS "tourneyCount"
2990                             ,SUM(CASE WHEN tp.rank > 0 THEN 0 ELSE 1 END)                           AS "unknownRank"
2991                             ,SUM(CASE WHEN winnings > 0 THEN 1 ELSE 0 END)
2992                              /(COUNT(1) - SUM(CASE WHEN tp.rank > 0 THEN 0 ELSE 0 END))             AS "itm"
2993                             ,SUM(CASE WHEN rank = 1 THEN 1 ELSE 0 END)                              AS "_1st"
2994                             ,SUM(CASE WHEN rank = 2 THEN 1 ELSE 0 END)                              AS "_2nd"
2995                             ,SUM(CASE WHEN rank = 3 THEN 1 ELSE 0 END)                              AS "_3rd"
2996                             ,SUM(tp.winnings)/100.0                                                 AS "won"
2997                             ,SUM(CASE WHEN tt.currency = 'USD' THEN (tt.buyIn+tt.fee)/100.0 ELSE tt.buyIn END) AS "spent"
2998                             ,SUM(tp.winnings)/SUM(tt.buyin+tt.fee)*100.0-100                        AS "roi"
2999                             ,SUM(tp.winnings-(tt.buyin+tt.fee))/100.0
3000                              /(COUNT(1)-SUM(CASE WHEN tp.rank > 0 THEN 0 ELSE 0 END))               AS "profitPerTourney"
3001                       from TourneysPlayers tp
3002                            inner join Tourneys t        on  (t.id = tp.tourneyId)
3003                            inner join TourneyTypes tt   on  (tt.Id = t.tourneyTypeId)
3004                            inner join Sites s           on  (s.Id = tt.siteId)
3005                            inner join Players p         on  (p.Id = tp.playerId)
3006                       where tp.playerId in <nametest> <sitetest>
3007                       and   to_char(t.startTime, 'YYYY-MM-DD HH24:MI:SS') <datestest>
3008                       group by t.tourneyTypeId, s.name, p.name, tt.currency, tt.buyin, tt.fee
3009                              , tt.category, tt.limitType
3010                       order by t.tourneyTypeId
3011                               ,p.name
3012                               ,s.name"""
3013         elif db_server == 'sqlite':
3014             self.query['tourneyPlayerDetailedStats'] = """
3015                       select s.name                                                                 AS siteName
3016                             ,t.tourneyTypeId                                                        AS tourneyTypeId
3017                             ,tt.currency                                                            AS currency
3018                             ,(CASE
3019                                 WHEN tt.currency = 'USD' THEN tt.buyIn/100.0
3020                                 WHEN tt.currency = 'EUR' THEN tt.buyIn/100.0
3021                                 ELSE tt.buyIn
3022                               END)                                                                  AS buyIn
3023                             ,tt.fee/100.0                                                           AS fee
3024                             ,tt.category                                                            AS category
3025                             ,tt.limitType                                                           AS limitType
3026                             ,p.name                                                                 AS playerName
3027                             ,COUNT(1)                                                               AS tourneyCount
3028                             ,SUM(CASE WHEN tp.rank > 0 THEN 0 ELSE 1 END)                           AS unknownRank
3029                             ,SUM(CASE WHEN winnings > 0 THEN 1 ELSE 0 END)/(COUNT(1) - SUM(CASE WHEN tp.rank > 0 THEN 0 ELSE 1 END)) AS itm
3030                             ,SUM(CASE WHEN rank = 1 THEN 1 ELSE 0 END)                              AS _1st
3031                             ,SUM(CASE WHEN rank = 2 THEN 1 ELSE 0 END)                              AS _2nd
3032                             ,SUM(CASE WHEN rank = 3 THEN 1 ELSE 0 END)                              AS _3rd
3033                             ,SUM(tp.winnings)/100.0                                                 AS won
3034                             ,SUM(CASE WHEN tt.currency = 'USD' THEN (tt.buyIn+tt.fee)/100.0 ELSE tt.buyIn END) AS spent
3035                             ,SUM(tp.winnings)/SUM(tt.buyin+tt.fee)*100.0-100                        AS roi
3036                             ,SUM(tp.winnings-(tt.buyin+tt.fee))/100.0/(COUNT(1)-SUM(CASE WHEN tp.rank > 0 THEN 0 ELSE 1 END)) AS profitPerTourney
3037                       from TourneysPlayers tp
3038                            inner join Tourneys t        on  (t.id = tp.tourneyId)
3039                            inner join TourneyTypes tt   on  (tt.Id = t.tourneyTypeId)
3040                            inner join Sites s           on  (s.Id = tt.siteId)
3041                            inner join Players p         on  (p.Id = tp.playerId)
3042                       where tp.playerId in <nametest> <sitetest>
3043                       and   datetime(t.startTime) <datestest>
3044                       group by tourneyTypeId, playerName
3045                       order by tourneyTypeId
3046                               ,playerName
3047                               ,siteName"""
3048
3049         if db_server == 'mysql':
3050             self.query['playerStats'] = """
3051                 SELECT
3052                       concat(upper(stats.limitType), ' '
3053                             ,concat(upper(substring(stats.category,1,1)),substring(stats.category,2) ), ' '
3054                             ,stats.name, ' '
3055                             ,cast(stats.bigBlindDesc as char)
3056                             )                                                      AS Game
3057                      ,stats.n
3058                      ,stats.vpip
3059                      ,stats.pfr
3060                      ,stats.pf3
3061                      ,stats.pf4
3062                      ,stats.pff3
3063                      ,stats.pff4
3064                      ,stats.steals
3065                      ,stats.saw_f
3066                      ,stats.sawsd
3067                      ,stats.wtsdwsf
3068                      ,stats.wmsd
3069                      ,stats.FlAFq
3070                      ,stats.TuAFq
3071                      ,stats.RvAFq
3072                      ,stats.PoFAFq
3073                      ,stats.Net
3074                      ,stats.BBper100
3075                      ,stats.Profitperhand
3076                      ,case when hprof2.variance = -999 then '-'
3077                            else format(hprof2.variance, 2)
3078                       end                                                          AS Variance
3079                      ,stats.AvgSeats
3080                 FROM
3081                     (select /* stats from hudcache */
3082                             gt.base
3083                            ,gt.category
3084                            ,upper(gt.limitType) as limitType
3085                            ,s.name
3086                            ,<selectgt.bigBlind>                                             AS bigBlindDesc
3087                            ,<hcgametypeId>                                                  AS gtId
3088                            ,sum(HDs)                                                        AS n
3089                            ,format(100.0*sum(street0VPI)/sum(HDs),1)                        AS vpip
3090                            ,format(100.0*sum(street0Aggr)/sum(HDs),1)                       AS pfr
3091                            ,case when sum(street0_3Bchance) = 0 then '0'
3092                                  else format(100.0*sum(street0_3Bdone)/sum(street0_3Bchance),1)
3093                             end                                                             AS pf3
3094                            ,case when sum(street0_4Bchance) = 0 then '0'
3095                                  else format(100.0*sum(street0_4Bdone)/sum(street0_4Bchance),1)
3096                             end                                                             AS pf4
3097                            ,case when sum(street0_FoldTo3Bchance) = 0 then '0'
3098                                  else format(100.0*sum(street0_FoldTo3Bdone)/sum(street0_FoldTo3Bchance),1)
3099                             end                                                             AS pff3
3100                            ,case when sum(street0_FoldTo4Bchance) = 0 then '0'
3101                                  else format(100.0*sum(street0_FoldTo4Bdone)/sum(street0_FoldTo4Bchance),1)
3102                             end                                                             AS pff4
3103                            ,case when sum(raiseFirstInChance) = 0 then '-'
3104                                  else format(100.0*sum(raisedFirstIn)/sum(raiseFirstInChance),1)
3105                             end                                                             AS steals
3106                            ,format(100.0*sum(street1Seen)/sum(HDs),1)                       AS saw_f
3107                            ,format(100.0*sum(sawShowdown)/sum(HDs),1)                       AS sawsd
3108                            ,case when sum(street1Seen) = 0 then '-'
3109                                  else format(100.0*sum(sawShowdown)/sum(street1Seen),1)
3110                             end                                                             AS wtsdwsf
3111                            ,case when sum(sawShowdown) = 0 then '-'
3112                                  else format(100.0*sum(wonAtSD)/sum(sawShowdown),1)
3113                             end                                                             AS wmsd
3114                            ,case when sum(street1Seen) = 0 then '-'
3115                                  else format(100.0*sum(street1Aggr)/sum(street1Seen),1)
3116                             end                                                             AS FlAFq
3117                            ,case when sum(street2Seen) = 0 then '-'
3118                                  else format(100.0*sum(street2Aggr)/sum(street2Seen),1)
3119                             end                                                             AS TuAFq
3120                            ,case when sum(street3Seen) = 0 then '-'
3121                                 else format(100.0*sum(street3Aggr)/sum(street3Seen),1)
3122                             end                                                             AS RvAFq
3123                            ,case when sum(street1Seen)+sum(street2Seen)+sum(street3Seen) = 0 then '-'
3124                                 else format(100.0*(sum(street1Aggr)+sum(street2Aggr)+sum(street3Aggr))
3125                                          /(sum(street1Seen)+sum(street2Seen)+sum(street3Seen)),1)
3126                             end                                                             AS PoFAFq
3127                            ,format(sum(totalProfit)/100.0,2)                                AS Net
3128                            ,format((sum(totalProfit/(gt.bigBlind+0.0))) / (sum(HDs)/100.0),2)
3129                                                                                             AS BBper100
3130                            ,format( (sum(totalProfit)/100.0) / sum(HDs), 4)                 AS Profitperhand
3131                            ,format( sum(activeSeats*HDs)/(sum(HDs)+0.0), 2)                 AS AvgSeats
3132                      from Gametypes gt
3133                           inner join Sites s on s.Id = gt.siteId
3134                           inner join HudCache hc on hc.gametypeId = gt.Id
3135                      where hc.playerId in <player_test>
3136                      and   <gtbigBlind_test>
3137                      and   hc.activeSeats <seats_test>
3138                      and   concat( '20', substring(hc.styleKey,2,2), '-', substring(hc.styleKey,4,2), '-'
3139                                  , substring(hc.styleKey,6,2) ) <datestest>
3140                      group by gt.base
3141                           ,gt.category
3142                           ,upper(gt.limitType)
3143                           ,s.name
3144                           <groupbygt.bigBlind>
3145                           ,gtId
3146                     ) stats
3147                 inner join
3148                     ( select # profit from handsplayers/handsactions
3149                              hprof.gtId, sum(hprof.profit) sum_profit,
3150                              avg(hprof.profit/100.0) profitperhand,
3151                              case when hprof.gtId = -1 then -999
3152                                   else variance(hprof.profit/100.0)
3153                              end as variance
3154                       from
3155                           (select hp.handId, <hgametypeId> as gtId, hp.totalProfit as profit
3156                            from HandsPlayers hp
3157                            inner join Hands h        ON h.id            = hp.handId
3158                            where hp.playerId in <player_test>
3159                            and   hp.tourneysPlayersId IS NULL
3160                            and   date_format(h.startTime, '%Y-%m-%d') <datestest>
3161                            group by hp.handId, gtId, hp.totalProfit
3162                           ) hprof
3163                       group by hprof.gtId
3164                      ) hprof2
3165                     on hprof2.gtId = stats.gtId
3166                 order by stats.category, stats.limittype, stats.bigBlindDesc desc <orderbyseats>"""
3167         elif db_server == 'sqlite':
3168             self.query['playerStats'] = """
3169                 SELECT
3170                       upper(substr(stats.category,1,1)) || substr(stats.category,2) || ' ' ||
3171                       stats.name || ' ' ||
3172                       cast(stats.bigBlindDesc as char) || ' ' || stats.maxSeats || ' seat'  AS Game
3173                      ,stats.n,stats.vpip,stats.pfr,stats.pf3,stats.pf4,stats.pff3,stats.pff4
3174                      ,stats.steals,stats.saw_f,stats.sawsd,stats.wtsdwsf,stats.wmsd,stats.FlAFq
3175                      ,stats.TuAFq,stats.RvAFq,stats.PoFAFq,stats.Net,stats.BBper100,stats.Profitperhand
3176                      ,case when hprof2.variance = -999 then '-' else round(hprof2.variance, 2)
3177                       end                                                                   AS Variance
3178                      ,stats.AvgSeats
3179                 FROM
3180                     (select /* stats from hudcache */
3181                             gt.base
3182                            ,gt.category,maxSeats,gt.bigBlind,gt.currency
3183                            ,upper(gt.limitType)                                             AS limitType
3184                            ,s.name
3185                            ,<selectgt.bigBlind>                                             AS bigBlindDesc
3186                            ,<hcgametypeId>                                                  AS gtId
3187                            ,sum(HDs)                                                        AS n
3188                            ,round(100.0*sum(street0VPI)/sum(HDs),1)                         AS vpip
3189                            ,round(100.0*sum(street0Aggr)/sum(HDs),1)                        AS pfr
3190                            ,case when sum(street0_3Bchance) = 0 then '0'
3191                                  else round(100.0*sum(street0_3Bdone)/sum(street0_3Bchance),1)
3192                             end                                                             AS pf3
3193                            ,case when sum(street0_4Bchance) = 0 then '0'
3194                                  else round(100.0*sum(street0_4Bdone)/sum(street0_4Bchance),1)
3195                             end                                                             AS pf4
3196                            ,case when sum(street0_FoldTo3Bchance) = 0 then '0'
3197                                  else round(100.0*sum(street0_FoldTo3Bdone)/sum(street0_FoldTo3Bchance),1)
3198                             end                                                             AS pff3
3199                            ,case when sum(street0_FoldTo4Bchance) = 0 then '0'
3200                                  else round(100.0*sum(street0_FoldTo4Bdone)/sum(street0_FoldTo4Bchance),1)
3201                             end                                                             AS pff4
3202                            ,case when sum(raiseFirstInChance) = 0 then '-'
3203                                  else round(100.0*sum(raisedFirstIn)/sum(raiseFirstInChance),1)
3204                             end                                                             AS steals
3205                            ,round(100.0*sum(street1Seen)/sum(HDs),1)                        AS saw_f
3206                            ,round(100.0*sum(sawShowdown)/sum(HDs),1)                        AS sawsd
3207                            ,case when sum(street1Seen) = 0 then '-'
3208                                  else round(100.0*sum(sawShowdown)/sum(street1Seen),1)
3209                             end                                                             AS wtsdwsf
3210                            ,case when sum(sawShowdown) = 0 then '-'
3211                                  else round(100.0*sum(wonAtSD)/sum(sawShowdown),1)
3212                             end                                                             AS wmsd
3213                            ,case when sum(street1Seen) = 0 then '-'
3214                                  else round(100.0*sum(street1Aggr)/sum(street1Seen),1)
3215                             end                                                             AS FlAFq
3216                            ,case when sum(street2Seen) = 0 then '-'
3217                                  else round(100.0*sum(street2Aggr)/sum(street2Seen),1)
3218                             end                                                             AS TuAFq
3219                            ,case when sum(street3Seen) = 0 then '-'
3220                                 else round(100.0*sum(street3Aggr)/sum(street3Seen),1)
3221                             end                                                             AS RvAFq
3222                            ,case when sum(street1Seen)+sum(street2Seen)+sum(street3Seen) = 0 then '-'
3223                                 else round(100.0*(sum(street1Aggr)+sum(street2Aggr)+sum(street3Aggr))
3224                                          /(sum(street1Seen)+sum(street2Seen)+sum(street3Seen)),1)
3225                             end                                                             AS PoFAFq
3226                            ,round(sum(totalProfit)/100.0,2)                                 AS Net
3227                            ,round((sum(totalProfit/(gt.bigBlind+0.0))) / (sum(HDs)/100.0),2)
3228                                                                                             AS BBper100
3229                            ,round( (sum(totalProfit)/100.0) / sum(HDs), 4)                  AS Profitperhand
3230                            ,round( sum(activeSeats*HDs)/(sum(HDs)+0.0), 2)                  AS AvgSeats
3231                      from Gametypes gt
3232                           inner join Sites s on s.Id = gt.siteId
3233                           inner join HudCache hc on hc.gametypeId = gt.Id
3234                      where hc.playerId in <player_test>
3235                      and   <gtbigBlind_test>
3236                      and   hc.activeSeats <seats_test>
3237                      and   '20' || substr(hc.styleKey,2,2) || '-' || substr(hc.styleKey,4,2) || '-' ||
3238                                    substr(hc.styleKey,6,2) <datestest>
3239                      group by gt.base,gt.category,upper(gt.limitType),s.name <groupbygt.bigBlind>,gtId
3240                     ) stats
3241                 inner join
3242                     ( select /* profit from handsplayers/handsactions */
3243                              hprof.gtId, sum(hprof.profit) sum_profit,
3244                              avg(hprof.profit/100.0) profitperhand,
3245                              case when hprof.gtId = -1 then -999
3246                                   else variance(hprof.profit/100.0)
3247                              end as variance
3248                       from
3249                           (select hp.handId, <hgametypeId> as gtId, hp.totalProfit as profit
3250                            from HandsPlayers hp
3251                            inner join Hands h        ON h.id            = hp.handId
3252                            where hp.playerId in <player_test>
3253                            and   hp.tourneysPlayersId IS NULL
3254                            and   datetime(h.startTime) <datestest>
3255                            group by hp.handId, gtId, hp.totalProfit
3256                           ) hprof
3257                       group by hprof.gtId
3258                      ) hprof2
3259                     on hprof2.gtId = stats.gtId
3260                 order by stats.category, stats.bigBlind, stats.limittype, stats.currency, stats.maxSeats <orderbyseats>"""
3261         else:  # assume postgres
3262             self.query['playerStats'] = """
3263                 SELECT upper(stats.limitType) || ' '
3264                        || initcap(stats.category) || ' '
3265                        || stats.name || ' '
3266                        || stats.bigBlindDesc                                          AS Game
3267                       ,stats.n
3268                       ,stats.vpip
3269                       ,stats.pfr
3270                       ,stats.pf3
3271                       ,stats.pf4
3272                       ,stats.pff3
3273                       ,stats.pff4
3274                       ,stats.steals
3275                       ,stats.saw_f
3276                       ,stats.sawsd
3277                       ,stats.wtsdwsf
3278                       ,stats.wmsd
3279                       ,stats.FlAFq
3280                       ,stats.TuAFq
3281                       ,stats.RvAFq
3282                       ,stats.PoFAFq
3283                       ,stats.Net
3284                       ,stats.BBper100
3285                       ,stats.Profitperhand
3286                       ,case when hprof2.variance = -999 then '-'
3287                             else to_char(hprof2.variance, '0D00')
3288                        end                                                          AS Variance
3289                       ,AvgSeats
3290                 FROM
3291                     (select gt.base
3292                            ,gt.category
3293                            ,upper(gt.limitType)                                             AS limitType
3294                            ,s.name
3295                            ,<selectgt.bigBlind>                                             AS bigBlindDesc
3296                            ,<hcgametypeId>                                                  AS gtId
3297                            ,sum(HDs) as n
3298                            ,to_char(100.0*sum(street0VPI)/sum(HDs),'990D0')                 AS vpip
3299                            ,to_char(100.0*sum(street0Aggr)/sum(HDs),'90D0')                 AS pfr
3300                            ,case when sum(street0_3Bchance) = 0 then '0'
3301                                  else to_char(100.0*sum(street0_3Bdone)/sum(street0_3Bchance),'90D0')
3302                             end                                                             AS pf3
3303                            ,case when sum(raiseFirstInChance) = 0 then '-'
3304                                  else to_char(100.0*sum(raisedFirstIn)/sum(raiseFirstInChance),'90D0')
3305                             end                                                             AS steals
3306                            ,to_char(100.0*sum(street1Seen)/sum(HDs),'90D0')                 AS saw_f
3307                            ,to_char(100.0*sum(sawShowdown)/sum(HDs),'90D0')                 AS sawsd
3308                            ,case when sum(street1Seen) = 0 then '-'
3309                                  else to_char(100.0*sum(sawShowdown)/sum(street1Seen),'90D0')
3310                             end                                                             AS wtsdwsf
3311                            ,case when sum(sawShowdown) = 0 then '-'
3312                                  else to_char(100.0*sum(wonAtSD)/sum(sawShowdown),'90D0')
3313                             end                                                             AS wmsd
3314                            ,case when sum(street1Seen) = 0 then '-'
3315                                  else to_char(100.0*sum(street1Aggr)/sum(street1Seen),'90D0')
3316                             end                                                             AS FlAFq
3317                            ,case when sum(street2Seen) = 0 then '-'
3318                                  else to_char(100.0*sum(street2Aggr)/sum(street2Seen),'90D0')
3319                             end                                                             AS TuAFq
3320                            ,case when sum(street3Seen) = 0 then '-'
3321                                 else to_char(100.0*sum(street3Aggr)/sum(street3Seen),'90D0')
3322                             end                                                             AS RvAFq
3323                            ,case when sum(street1Seen)+sum(street2Seen)+sum(street3Seen) = 0 then '-'
3324                                 else to_char(100.0*(sum(street1Aggr)+sum(street2Aggr)+sum(street3Aggr))
3325                                          /(sum(street1Seen)+sum(street2Seen)+sum(street3Seen)),'90D0')
3326                             end                                                             AS PoFAFq
3327                            ,round(sum(totalProfit)/100.0,2)                                 AS Net
3328                            ,to_char((sum(totalProfit/(gt.bigBlind+0.0))) / (sum(HDs)/100.0), '990D00')
3329                                                                                             AS BBper100
3330                            ,to_char(sum(totalProfit/100.0) / (sum(HDs)+0.0), '990D0000')    AS Profitperhand
3331                            ,to_char(sum(activeSeats*HDs)/(sum(HDs)+0.0),'90D00')            AS AvgSeats
3332                      from Gametypes gt
3333                           inner join Sites s on s.Id = gt.siteId
3334                           inner join HudCache hc on hc.gametypeId = gt.Id
3335                      where hc.playerId in <player_test>
3336                      and   <gtbigBlind_test>
3337                      and   hc.activeSeats <seats_test>
3338                      and   '20' || SUBSTR(hc.styleKey,2,2) || '-' || SUBSTR(hc.styleKey,4,2) || '-'
3339                            || SUBSTR(hc.styleKey,6,2) <datestest>
3340                      group by gt.base
3341                           ,gt.category
3342                           ,upper(gt.limitType)
3343                           ,s.name
3344                           <groupbygt.bigBlind>
3345                           ,gtId
3346                     ) stats
3347                 inner join
3348                     ( select
3349                              hprof.gtId, sum(hprof.profit) AS sum_profit,
3350                              avg(hprof.profit/100.0) AS profitperhand,
3351                              case when hprof.gtId = -1 then -999
3352                                   else variance(hprof.profit/100.0)
3353                              end as variance
3354                       from
3355                           (select hp.handId, <hgametypeId> as gtId, hp.totalProfit as profit
3356                            from HandsPlayers hp
3357                            inner join Hands h   ON (h.id = hp.handId)
3358                            where hp.playerId in <player_test>
3359                            and   hp.tourneysPlayersId IS NULL
3360                            and   to_char(h.startTime, 'YYYY-MM-DD') <datestest>
3361                            group by hp.handId, gtId, hp.totalProfit
3362                           ) hprof
3363                       group by hprof.gtId
3364                      ) hprof2
3365                     on hprof2.gtId = stats.gtId
3366                 order by stats.base, stats.limittype, stats.bigBlindDesc desc <orderbyseats>"""
3367
3368         if db_server == 'mysql':
3369             self.query['playerStatsByPosition'] = """
3370                 SELECT
3371                       concat(upper(stats.limitType), ' '
3372                             ,concat(upper(substring(stats.category,1,1)),substring(stats.category,2) ), ' '
3373                             ,stats.name, ' '
3374                             ,cast(stats.bigBlindDesc as char)
3375                             )                                                      AS Game
3376                      ,case when stats.PlPosition = -2 then 'BB'
3377                            when stats.PlPosition = -1 then 'SB'
3378                            when stats.PlPosition =  0 then 'Btn'
3379                            when stats.PlPosition =  1 then 'CO'
3380                            when stats.PlPosition =  2 then 'MP'
3381                            when stats.PlPosition =  5 then 'EP'
3382                            else 'xx'
3383                       end                                                          AS PlPosition
3384                      ,stats.n
3385                      ,stats.vpip
3386                      ,stats.pfr
3387                      ,stats.pf3
3388                      ,stats.pf4
3389                      ,stats.pff3
3390                      ,stats.pff4
3391                      ,stats.steals
3392                      ,stats.saw_f
3393                      ,stats.sawsd
3394                      ,stats.wtsdwsf
3395                      ,stats.wmsd
3396                      ,stats.FlAFq
3397                      ,stats.TuAFq
3398                      ,stats.RvAFq
3399                      ,stats.PoFAFq
3400                      ,stats.Net
3401                      ,stats.BBper100
3402                      ,stats.Profitperhand
3403                      ,case when hprof2.variance = -999 then '-'
3404                            else format(hprof2.variance, 2)
3405                       end                                                          AS Variance
3406                      ,stats.AvgSeats
3407                 FROM
3408                     (select /* stats from hudcache */
3409                             gt.base
3410                            ,gt.category
3411                            ,upper(gt.limitType)                                             AS limitType
3412                            ,s.name
3413                            ,<selectgt.bigBlind>                                             AS bigBlindDesc
3414                            ,<hcgametypeId>                                                  AS gtId
3415                            ,case when hc.position = 'B' then -2
3416                                  when hc.position = 'S' then -1
3417                                  when hc.position = 'D' then  0
3418                                  when hc.position = 'C' then  1
3419                                  when hc.position = 'M' then  2
3420                                  when hc.position = 'E' then  5
3421                                  else 9
3422                             end                                                             as PlPosition
3423                            ,sum(HDs)                                                        AS n
3424                            ,format(100.0*sum(street0VPI)/sum(HDs),1)                        AS vpip
3425                            ,format(100.0*sum(street0Aggr)/sum(HDs),1)                       AS pfr
3426                            ,case when sum(street0_3Bchance) = 0 then '0'
3427                                  else format(100.0*sum(street0_3Bdone)/sum(street0_3Bchance),1)
3428                             end                                                             AS pf3
3429                            ,case when sum(street0_4Bchance) = 0 then '0'
3430                                  else format(100.0*sum(street0_4Bdone)/sum(street0_4Bchance),1)
3431                             end                                                             AS pf4
3432                            ,case when sum(street0_FoldTo3Bchance) = 0 then '0'
3433                                  else format(100.0*sum(street0_FoldTo3Bdone)/sum(street0_FoldTo3Bchance),1)
3434                             end                                                             AS pff3
3435                            ,case when sum(street0_FoldTo4Bchance) = 0 then '0'
3436                                  else format(100.0*sum(street0_FoldTo4Bdone)/sum(street0_FoldTo4Bchance),1)
3437                             end                                                             AS pff4
3438                            ,case when sum(raiseFirstInChance) = 0 then '-'
3439                                  else format(100.0*sum(raisedFirstIn)/sum(raiseFirstInChance),1)
3440                             end                                                             AS steals
3441                            ,format(100.0*sum(street1Seen)/sum(HDs),1)                       AS saw_f
3442                            ,format(100.0*sum(sawShowdown)/sum(HDs),1)                       AS sawsd
3443                            ,case when sum(street1Seen) = 0 then '-'
3444                                  else format(100.0*sum(sawShowdown)/sum(street1Seen),1)
3445                             end                                                             AS wtsdwsf
3446                            ,case when sum(sawShowdown) = 0 then '-'
3447                                  else format(100.0*sum(wonAtSD)/sum(sawShowdown),1)
3448                             end                                                             AS wmsd
3449                            ,case when sum(street1Seen) = 0 then '-'
3450                                  else format(100.0*sum(street1Aggr)/sum(street1Seen),1)
3451                             end                                                             AS FlAFq
3452                            ,case when sum(street2Seen) = 0 then '-'
3453                                  else format(100.0*sum(street2Aggr)/sum(street2Seen),1)
3454                             end                                                             AS TuAFq
3455                            ,case when sum(street3Seen) = 0 then '-'
3456                                 else format(100.0*sum(street3Aggr)/sum(street3Seen),1)
3457                             end                                                             AS RvAFq
3458                            ,case when sum(street1Seen)+sum(street2Seen)+sum(street3Seen) = 0 then '-'
3459                                 else format(100.0*(sum(street1Aggr)+sum(street2Aggr)+sum(street3Aggr))
3460                                          /(sum(street1Seen)+sum(street2Seen)+sum(street3Seen)),1)
3461                             end                                                             AS PoFAFq
3462                            ,format(sum(totalProfit)/100.0,2)                                AS Net
3463                            ,format((sum(totalProfit/(gt.bigBlind+0.0))) / (sum(HDs)/100.0),2)
3464                                                                                             AS BBper100
3465                            ,format( (sum(totalProfit)/100.0) / sum(HDs), 4)                 AS Profitperhand
3466                            ,format( sum(activeSeats*HDs)/(sum(HDs)+0.0), 2)                 AS AvgSeats
3467                      from Gametypes gt
3468                           inner join Sites s on s.Id = gt.siteId
3469                           inner join HudCache hc on hc.gametypeId = gt.Id
3470                      where hc.playerId in <player_test>
3471                      and   <gtbigBlind_test>
3472                      and   hc.activeSeats <seats_test>
3473                      and   concat( '20', substring(hc.styleKey,2,2), '-', substring(hc.styleKey,4,2), '-'
3474                                  , substring(hc.styleKey,6,2) ) <datestest>
3475                      group by gt.base
3476                           ,gt.category
3477                           ,upper(gt.limitType)
3478                           ,s.name
3479                           <groupbygt.bigBlind>
3480                           ,gtId
3481                           <groupbyseats>
3482                           ,PlPosition
3483                     ) stats
3484                 inner join
3485                     ( select # profit from handsplayers/handsactions
3486                              hprof.gtId,
3487                              case when hprof.position = 'B' then -2
3488                                   when hprof.position = 'S' then -1
3489                                   when hprof.position in ('3','4') then 2
3490                                   when hprof.position in ('6','7') then 5
3491                                   else hprof.position
3492                              end                                      as PlPosition,
3493                              sum(hprof.profit) as sum_profit,
3494                              avg(hprof.profit/100.0) as profitperhand,
3495                              case when hprof.gtId = -1 then -999
3496                                   else variance(hprof.profit/100.0)
3497                              end as variance
3498                       from
3499                           (select hp.handId, <hgametypeId> as gtId, hp.position
3500                                 , hp.totalProfit as profit
3501                            from HandsPlayers hp
3502                            inner join Hands h  ON  (h.id = hp.handId)
3503                            where hp.playerId in <player_test>
3504                            and   hp.tourneysPlayersId IS NULL
3505                            and   date_format(h.startTime, '%Y-%m-%d') <datestest>
3506                            group by hp.handId, gtId, hp.position, hp.totalProfit
3507                           ) hprof
3508                       group by hprof.gtId, PlPosition
3509                      ) hprof2
3510                     on (    hprof2.gtId = stats.gtId
3511                         and hprof2.PlPosition = stats.PlPosition)
3512                 order by stats.category, stats.limitType, stats.bigBlindDesc desc
3513                          <orderbyseats>, cast(stats.PlPosition as signed)
3514                 """
3515         elif db_server == 'sqlite':
3516             self.query['playerStatsByPosition'] = """
3517                 SELECT
3518                       upper(substr(stats.category,1,1)) || substr(stats.category,2) || ' ' ||
3519                       stats.name || ' ' ||
3520                       cast(stats.bigBlindDesc as char) || ' ' || stats.maxSeats || ' seat'  AS Game
3521                      ,case when stats.PlPosition = -2 then 'BB'
3522                            when stats.PlPosition = -1 then 'SB'
3523                            when stats.PlPosition =  0 then 'Btn'
3524                            when stats.PlPosition =  1 then 'CO'
3525                            when stats.PlPosition =  2 then 'MP'
3526                            when stats.PlPosition =  5 then 'EP'
3527                            else 'xx'
3528                       end                                                                   AS PlPosition
3529                      ,stats.n,stats.vpip,stats.pfr,stats.pf3,stats.pf4,stats.pff3,stats.pff4
3530                      ,stats.steals,stats.saw_f,stats.sawsd,stats.wtsdwsf,stats.wmsd,stats.FlAFq
3531                      ,stats.TuAFq,stats.RvAFq,stats.PoFAFq,stats.Net,stats.BBper100,stats.Profitperhand
3532                      ,case when hprof2.variance = -999 then '-'
3533                            else round(hprof2.variance, 2)
3534                       end                                                                   AS Variance
3535                      ,stats.AvgSeats
3536                 FROM
3537                     (select /* stats from hudcache */
3538                             gt.base
3539                            ,gt.category,maxSeats,gt.bigBlind,gt.currency
3540                            ,upper(gt.limitType)                                             AS limitType
3541                            ,s.name
3542                            ,<selectgt.bigBlind>                                             AS bigBlindDesc
3543                            ,<hcgametypeId>                                                  AS gtId
3544                            ,case when hc.position = 'B' then -2
3545                                  when hc.position = 'S' then -1
3546                                  when hc.position = 'D' then  0
3547                                  when hc.position = 'C' then  1
3548                                  when hc.position = 'M' then  2
3549                                  when hc.position = 'E' then  5
3550                                  else 9
3551                             end                                                             AS PlPosition
3552                            ,sum(HDs)                                                        AS n
3553                            ,round(100.0*sum(street0VPI)/sum(HDs),1)                         AS vpip
3554                            ,round(100.0*sum(street0Aggr)/sum(HDs),1)                        AS pfr
3555                            ,case when sum(street0_3Bchance) = 0 then '0'
3556                                  else round(100.0*sum(street0_3Bdone)/sum(street0_3Bchance),1)
3557                             end                                                             AS pf3
3558                            ,case when sum(street0_4Bchance) = 0 then '0'
3559                                  else round(100.0*sum(street0_4Bdone)/sum(street0_4Bchance),1)
3560                             end                                                             AS pf4
3561                            ,case when sum(street0_FoldTo3Bchance) = 0 then '0'
3562                                  else round(100.0*sum(street0_FoldTo3Bdone)/sum(street0_FoldTo3Bchance),1)
3563                             end                                                             AS pff3
3564                            ,case when sum(street0_FoldTo4Bchance) = 0 then '0'
3565                                  else round(100.0*sum(street0_FoldTo4Bdone)/sum(street0_FoldTo4Bchance),1)
3566                             end                                                             AS pff4
3567                            ,case when sum(raiseFirstInChance) = 0 then '-'
3568                                  else round(100.0*sum(raisedFirstIn)/sum(raiseFirstInChance),1)
3569                             end                                                             AS steals
3570                            ,round(100.0*sum(street1Seen)/sum(HDs),1)                        AS saw_f
3571                            ,round(100.0*sum(sawShowdown)/sum(HDs),1)                        AS sawsd
3572                            ,case when sum(street1Seen) = 0 then '-'
3573                                  else round(100.0*sum(sawShowdown)/sum(street1Seen),1)
3574                             end                                                             AS wtsdwsf
3575                            ,case when sum(sawShowdown) = 0 then '-'
3576                                  else round(100.0*sum(wonAtSD)/sum(sawShowdown),1)
3577                             end                                                             AS wmsd
3578                            ,case when sum(street1Seen) = 0 then '-'
3579                                  else round(100.0*sum(street1Aggr)/sum(street1Seen),1)
3580                             end                                                             AS FlAFq
3581                            ,case when sum(street2Seen) = 0 then '-'
3582                                  else round(100.0*sum(street2Aggr)/sum(street2Seen),1)
3583                             end                                                             AS TuAFq
3584                            ,case when sum(street3Seen) = 0 then '-'
3585                                 else round(100.0*sum(street3Aggr)/sum(street3Seen),1)
3586                             end                                                             AS RvAFq
3587                            ,case when sum(street1Seen)+sum(street2Seen)+sum(street3Seen) = 0 then '-'
3588                                 else round(100.0*(sum(street1Aggr)+sum(street2Aggr)+sum(street3Aggr))
3589                                          /(sum(street1Seen)+sum(street2Seen)+sum(street3Seen)),1)
3590                             end                                                             AS PoFAFq
3591                            ,round(sum(totalProfit)/100.0,2)                                 AS Net
3592                            ,round((sum(totalProfit/(gt.bigBlind+0.0))) / (sum(HDs)/100.0),2)
3593                                                                                             AS BBper100
3594                            ,round( (sum(totalProfit)/100.0) / sum(HDs), 4)                  AS Profitperhand
3595                            ,round( sum(activeSeats*HDs)/(sum(HDs)+0.0), 2)                  AS AvgSeats
3596                      from Gametypes gt
3597                           inner join Sites s on s.Id = gt.siteId
3598                           inner join HudCache hc on hc.gametypeId = gt.Id
3599                      where hc.playerId in <player_test>
3600                      and   <gtbigBlind_test>
3601                      and   hc.activeSeats <seats_test>
3602                      and   '20' || substr(hc.styleKey,2,2) || '-' || substr(hc.styleKey,4,2) || '-' ||
3603                                    substr(hc.styleKey,6,2) <datestest>
3604                      group by gt.base,gt.category,upper(gt.limitType),s.name
3605                               <groupbygt.bigBlind>,gtId<groupbyseats>,PlPosition
3606                     ) stats
3607                 inner join
3608                     ( select /* profit from handsplayers/handsactions */
3609                              hprof.gtId,
3610                              cast(case when hprof.position = 'B' then -2
3611                                   when hprof.position = 'S' then -1
3612                                   when hprof.position in ('3','4') then 2
3613                                   when hprof.position in ('6','7') then 5
3614                                   else hprof.position
3615                              end as signed)                           as PlPosition,
3616                              sum(hprof.profit) as sum_profit,
3617                              avg(hprof.profit/100.0) as profitperhand,
3618                              case when hprof.gtId = -1 then -999
3619                                   else variance(hprof.profit/100.0)
3620                              end as variance
3621                       from
3622                           (select hp.handId, <hgametypeId> as gtId, hp.position
3623                                 , hp.totalProfit as profit
3624                            from HandsPlayers hp
3625                            inner join Hands h  ON  (h.id = hp.handId)
3626                            where hp.playerId in <player_test>
3627                            and   hp.tourneysPlayersId IS NULL
3628                            and   datetime(h.startTime) <datestest>
3629                            group by hp.handId, gtId, hp.position, hp.totalProfit
3630                           ) hprof
3631                       group by hprof.gtId, PlPosition
3632                      ) hprof2
3633                     on (    hprof2.gtId = stats.gtId
3634                         and hprof2.PlPosition = stats.PlPosition)
3635                 order by stats.category, stats.bigBlind, stats.limitType, stats.currency, stats.maxSeats <orderbyseats>
3636                         ,cast(stats.PlPosition as signed)
3637                 """
3638         else:  # assume postgresql
3639             self.query['playerStatsByPosition'] = """
3640                 select /* stats from hudcache */
3641                        upper(stats.limitType) || ' '
3642                        || upper(substr(stats.category,1,1)) || substr(stats.category,2) || ' '
3643                        || stats.name || ' '
3644                        || stats.bigBlindDesc                                        AS Game
3645                       ,case when stats.PlPosition = -2 then 'BB'
3646                             when stats.PlPosition = -1 then 'SB'
3647                             when stats.PlPosition =  0 then 'Btn'
3648                             when stats.PlPosition =  1 then 'CO'
3649                             when stats.PlPosition =  2 then 'MP'
3650                             when stats.PlPosition =  5 then 'EP'
3651                             else 'xx'
3652                        end                                                          AS PlPosition
3653                       ,stats.n
3654                       ,stats.vpip
3655                       ,stats.pfr
3656                       ,stats.pf3
3657                       ,stats.pf4
3658                       ,stats.pff3
3659                       ,stats.pff4
3660                       ,stats.steals
3661                       ,stats.saw_f
3662                       ,stats.sawsd
3663                       ,stats.wtsdwsf
3664                       ,stats.wmsd
3665                       ,stats.FlAFq
3666                       ,stats.TuAFq
3667                       ,stats.RvAFq
3668                       ,stats.PoFAFq
3669                       ,stats.Net
3670                       ,stats.BBper100
3671                       ,stats.Profitperhand
3672                       ,case when hprof2.variance = -999 then '-'
3673                             else to_char(hprof2.variance, '0D00')
3674                        end                                                          AS Variance
3675                       ,stats.AvgSeats
3676                 FROM
3677                     (select /* stats from hudcache */
3678                             gt.base
3679                            ,gt.category
3680                            ,upper(gt.limitType)                                             AS limitType
3681                            ,s.name
3682                            ,<selectgt.bigBlind>                                             AS bigBlindDesc
3683                            ,<hcgametypeId>                                                  AS gtId
3684                            ,case when hc.position = 'B' then -2
3685                                  when hc.position = 'S' then -1
3686                                  when hc.position = 'D' then  0
3687                                  when hc.position = 'C' then  1
3688                                  when hc.position = 'M' then  2
3689                                  when hc.position = 'E' then  5
3690                                  else 9
3691                             end                                                             AS PlPosition
3692                            ,sum(HDs)                                                        AS n
3693                            ,to_char(round(100.0*sum(street0VPI)/sum(HDs)),'990D0')          AS vpip
3694                            ,to_char(round(100.0*sum(street0Aggr)/sum(HDs)),'90D0')          AS pfr
3695                            ,case when sum(street0_3Bchance) = 0 then '0'
3696                                  else to_char(100.0*sum(street0_3Bdone)/sum(street0_3Bchance),'90D0')
3697                             end                                                             AS pf3
3698                            ,case when sum(street0_4Bchance) = 0 then '0'
3699                                  else to_char(100.0*sum(street0_4Bdone)/sum(street0_4Bchance),'90D0')
3700                             end                                                             AS pf4
3701                            ,case when sum(street0_FoldTo3Bchance) = 0 then '0'
3702                                  else to_char(100.0*sum(street0_FoldTo3Bdone)/sum(street0_FoldTo3Bchance),'90D0')
3703                             end                                                             AS pff3
3704                            ,case when sum(street0_FoldTo4Bchance) = 0 then '0'
3705                                  else to_char(100.0*sum(street0_FoldTo4Bdone)/sum(street0_FoldTo4Bchance),'90D0')
3706                             end                                                             AS pff4
3707                            ,case when sum(raiseFirstInChance) = 0 then '-'
3708                                  else to_char(100.0*sum(raisedFirstIn)/sum(raiseFirstInChance),'90D0')
3709                             end                                                             AS steals
3710                            ,to_char(round(100.0*sum(street1Seen)/sum(HDs)),'90D0')          AS saw_f
3711                            ,to_char(round(100.0*sum(sawShowdown)/sum(HDs)),'90D0')          AS sawsd
3712                            ,case when sum(street1Seen) = 0 then '-'
3713                                  else to_char(round(100.0*sum(sawShowdown)/sum(street1Seen)),'90D0')
3714                             end                                                             AS wtsdwsf
3715                            ,case when sum(sawShowdown) = 0 then '-'
3716                                  else to_char(round(100.0*sum(wonAtSD)/sum(sawShowdown)),'90D0')
3717                             end                                                             AS wmsd
3718                            ,case when sum(street1Seen) = 0 then '-'
3719                                  else to_char(round(100.0*sum(street1Aggr)/sum(street1Seen)),'90D0')
3720                             end                                                             AS FlAFq
3721                            ,case when sum(street2Seen) = 0 then '-'
3722                                  else to_char(round(100.0*sum(street2Aggr)/sum(street2Seen)),'90D0')
3723                             end                                                             AS TuAFq
3724                            ,case when sum(street3Seen) = 0 then '-'
3725                                 else to_char(round(100.0*sum(street3Aggr)/sum(street3Seen)),'90D0')
3726                             end                                                             AS RvAFq
3727                            ,case when sum(street1Seen)+sum(street2Seen)+sum(street3Seen) = 0 then '-'
3728                                 else to_char(round(100.0*(sum(street1Aggr)+sum(street2Aggr)+sum(street3Aggr))
3729                                          /(sum(street1Seen)+sum(street2Seen)+sum(street3Seen))),'90D0')
3730                             end                                                             AS PoFAFq
3731                            ,to_char(sum(totalProfit)/100.0,'9G999G990D00')                  AS Net
3732                            ,case when sum(HDs) = 0 then '0'
3733                                  else to_char(sum(totalProfit/(gt.bigBlind+0.0)) / (sum(HDs)/100.0), '990D00')
3734                             end                                                             AS BBper100
3735                            ,case when sum(HDs) = 0 then '0'
3736                                  else to_char( (sum(totalProfit)/100.0) / sum(HDs), '90D0000')
3737                             end                                                             AS Profitperhand
3738                            ,to_char(sum(activeSeats*HDs)/(sum(HDs)+0.0),'90D00')            AS AvgSeats
3739                      from Gametypes gt
3740                           inner join Sites s     on (s.Id = gt.siteId)
3741                           inner join HudCache hc on (hc.gametypeId = gt.Id)
3742                      where hc.playerId in <player_test>
3743                      and   <gtbigBlind_test>
3744                      and   hc.activeSeats <seats_test>
3745                      and   '20' || SUBSTR(hc.styleKey,2,2) || '-' || SUBSTR(hc.styleKey,4,2) || '-'
3746                            || SUBSTR(hc.styleKey,6,2) <datestest>
3747                      group by gt.base
3748                           ,gt.category
3749                           ,upper(gt.limitType)
3750                           ,s.name
3751                           <groupbygt.bigBlind>
3752                           ,gtId
3753                           <groupbyseats>
3754                           ,PlPosition
3755                     ) stats
3756                 inner join
3757                     ( select /* profit from handsplayers/handsactions */
3758                              hprof.gtId,
3759                              case when hprof.position = 'B' then -2
3760                                   when hprof.position = 'S' then -1
3761                                   when hprof.position in ('3','4') then 2
3762                                   when hprof.position in ('6','7') then 5
3763                                   else cast(hprof.position as smallint)
3764                              end                                      as PlPosition,
3765                              sum(hprof.profit) as sum_profit,
3766                              avg(hprof.profit/100.0) as profitperhand,
3767                              case when hprof.gtId = -1 then -999
3768                                   else variance(hprof.profit/100.0)
3769                              end as variance
3770                       from
3771                           (select hp.handId, <hgametypeId> as gtId, hp.position
3772                                 , hp.totalProfit as profit
3773                            from HandsPlayers hp
3774                            inner join Hands h  ON  (h.id = hp.handId)
3775                            where hp.playerId in <player_test>
3776                            and   hp.tourneysPlayersId IS NULL
3777                            and   to_char(h.startTime, 'YYYY-MM-DD') <datestest>
3778                            group by hp.handId, gametypeId, hp.position, hp.totalProfit
3779                           ) hprof
3780                       group by hprof.gtId, PlPosition
3781                     ) hprof2
3782                     on (    hprof2.gtId = stats.gtId
3783                         and hprof2.PlPosition = stats.PlPosition)
3784                 order by stats.category, stats.limitType, stats.bigBlindDesc desc
3785                          <orderbyseats>, cast(stats.PlPosition as smallint)
3786                 """
3787
3788         ####################################
3789         # Cash Game Graph query
3790         ####################################
3791         self.query['getRingProfitAllHandsPlayerIdSite'] = """
3792             SELECT hp.handId, hp.totalProfit, hp.sawShowdown
3793             FROM HandsPlayers hp
3794             INNER JOIN Players pl      ON  (pl.id = hp.playerId)
3795             INNER JOIN Hands h         ON  (h.id  = hp.handId)
3796             INNER JOIN Gametypes gt    ON  (gt.id = h.gametypeId)
3797             WHERE pl.id in <player_test>
3798             AND   pl.siteId in <site_test>
3799             AND   h.startTime > '<startdate_test>'
3800             AND   h.startTime < '<enddate_test>'
3801             <limit_test>
3802             <game_test>
3803             AND   gt.type = 'ring'
3804             GROUP BY h.startTime, hp.handId, hp.sawShowdown, hp.totalProfit
3805             ORDER BY h.startTime"""
3806
3807         self.query['getRingProfitAllHandsPlayerIdSiteInBB'] = """
3808             SELECT hp.handId, ( hp.totalProfit / ( gt.bigBlind  * 2.0 ) ) * 100 , hp.sawShowdown
3809             FROM HandsPlayers hp
3810             INNER JOIN Players pl      ON  (pl.id = hp.playerId)
3811             INNER JOIN Hands h         ON  (h.id  = hp.handId)
3812             INNER JOIN Gametypes gt    ON  (gt.id = h.gametypeId)
3813             WHERE pl.id in <player_test>
3814             AND   pl.siteId in <site_test>
3815             AND   h.startTime > '<startdate_test>'
3816             AND   h.startTime < '<enddate_test>'
3817             <limit_test>
3818             <game_test>
3819             <currency_test>
3820             AND   hp.tourneysPlayersId IS NULL
3821             GROUP BY h.startTime, hp.handId, hp.sawShowdown, hp.totalProfit
3822             ORDER BY h.startTime"""
3823
3824         self.query['getRingProfitAllHandsPlayerIdSiteInDollars'] = """
3825             SELECT hp.handId, hp.totalProfit, hp.sawShowdown
3826             FROM HandsPlayers hp
3827             INNER JOIN Players pl      ON  (pl.id = hp.playerId)
3828             INNER JOIN Hands h         ON  (h.id  = hp.handId)
3829             INNER JOIN Gametypes gt    ON  (gt.id = h.gametypeId)
3830             WHERE pl.id in <player_test>
3831             AND   pl.siteId in <site_test>
3832             AND   h.startTime > '<startdate_test>'
3833             AND   h.startTime < '<enddate_test>'
3834             <limit_test>
3835             <game_test>
3836             <currency_test>
3837             AND   hp.tourneysPlayersId IS NULL
3838             GROUP BY h.startTime, hp.handId, hp.sawShowdown, hp.totalProfit
3839             ORDER BY h.startTime"""
3840
3841
3842
3843         ####################################
3844         # Tourney Results query
3845         ####################################
3846         self.query['tourneyResults'] = """
3847             SELECT tp.tourneyId, (coalesce(tp.winnings,0) - coalesce(tt.buyIn,0) - coalesce(tt.fee,0)) as profit, tp.koCount, tp.rebuyCount, tp.addOnCount, tt.buyIn, tt.fee, t.siteTourneyNo
3848             FROM TourneysPlayers tp
3849             INNER JOIN Players pl      ON  (pl.id = tp.playerId)
3850             INNER JOIN Tourneys t         ON  (t.id  = tp.tourneyId)
3851             INNER JOIN TourneyTypes tt    ON  (tt.id = t.tourneyTypeId)
3852             WHERE pl.id in <player_test>
3853             AND   pl.siteId in <site_test>
3854             AND   t.startTime > '<startdate_test>'
3855             AND   t.startTime < '<enddate_test>'
3856             GROUP BY t.startTime, tp.tourneyId, tp.winningsCurrency,
3857                      tp.winnings, tp.koCount,
3858                      tp.rebuyCount, tp.addOnCount,
3859                      tt.buyIn, tt.fee
3860             ORDER BY t.startTime"""
3861
3862             #AND   gt.type = 'ring'
3863             #<limit_test>
3864             #<game_test>
3865
3866         ####################################
3867         # Session stats query
3868         ####################################
3869         if db_server == 'mysql':
3870             self.query['sessionStats'] = """
3871                 SELECT UNIX_TIMESTAMP(h.startTime) as time, hp.totalProfit
3872                 FROM HandsPlayers hp
3873                  INNER JOIN Hands h       on  (h.id = hp.handId)
3874                  INNER JOIN Gametypes gt  on  (gt.Id = h.gametypeId)
3875                  INNER JOIN Sites s       on  (s.Id = gt.siteId)
3876                  INNER JOIN Players p     on  (p.Id = hp.playerId)
3877                 WHERE hp.playerId in <player_test>
3878                  AND  date_format(h.startTime, '%Y-%m-%d') <datestest>
3879                  AND  gt.type LIKE 'ring'
3880                  <limit_test>
3881                  <game_test>
3882                  <seats_test>
3883                  <currency_test>
3884                 ORDER by time"""
3885         elif db_server == 'postgresql':
3886             self.query['sessionStats'] = """
3887                 SELECT EXTRACT(epoch from h.startTime) as time, hp.totalProfit
3888                 FROM HandsPlayers hp
3889                  INNER JOIN Hands h       on  (h.id = hp.handId)
3890                  INNER JOIN Gametypes gt  on  (gt.Id = h.gametypeId)
3891                  INNER JOIN Sites s       on  (s.Id = gt.siteId)
3892                  INNER JOIN Players p     on  (p.Id = hp.playerId)
3893                 WHERE hp.playerId in <player_test>
3894                  AND  h.startTime <datestest>
3895                  AND  gt.type LIKE 'ring'
3896                  <limit_test>
3897                  <game_test>
3898                  <seats_test>
3899                  <currency_test>
3900                 ORDER by time"""
3901         elif db_server == 'sqlite':
3902             self.query['sessionStats'] = """
3903                 SELECT STRFTIME('<ampersand_s>', h.startTime) as time, hp.totalProfit
3904                 FROM HandsPlayers hp
3905                  INNER JOIN Hands h       on  (h.id = hp.handId)
3906                  INNER JOIN Gametypes gt  on  (gt.Id = h.gametypeId)
3907                  INNER JOIN Sites s       on  (s.Id = gt.siteId)
3908                  INNER JOIN Players p     on  (p.Id = hp.playerId)
3909                 WHERE hp.playerId in <player_test>
3910                  AND  h.startTime <datestest>
3911                  AND  gt.type is 'ring'
3912                  <limit_test>
3913                  <game_test>
3914                  <seats_test>
3915                  <currency_test>
3916                 ORDER by time"""
3917
3918
3919         ####################################
3920         # Queries to rebuild/modify hudcache
3921         ####################################
3922
3923         self.query['clearHudCache'] = """DELETE FROM HudCache"""
3924
3925         if db_server == 'mysql':
3926             self.query['rebuildHudCache'] = """
3927                 INSERT INTO HudCache
3928                 (gametypeId
3929                 ,playerId
3930                 ,activeSeats
3931                 ,position
3932                 <tourney_insert_clause>
3933                 ,styleKey
3934                 ,HDs
3935                 ,wonWhenSeenStreet1
3936                 ,wonWhenSeenStreet2
3937                 ,wonWhenSeenStreet3
3938                 ,wonWhenSeenStreet4
3939                 ,wonAtSD
3940                 ,street0VPI
3941                 ,street0Aggr
3942                 ,street0_3BChance
3943                 ,street0_3BDone
3944                 ,street0_4BChance
3945                 ,street0_4BDone
3946                 ,street0_C4BChance
3947                 ,street0_C4BDone
3948                 ,street0_FoldTo3BChance
3949                 ,street0_FoldTo3BDone
3950                 ,street0_FoldTo4BChance
3951                 ,street0_FoldTo4BDone
3952                 ,street0_SqueezeChance
3953                 ,street0_SqueezeDone
3954                 ,raiseToStealChance
3955                 ,raiseToStealDone
3956                 ,success_Steal
3957                 ,street1Seen
3958                 ,street2Seen
3959                 ,street3Seen
3960                 ,street4Seen
3961                 ,sawShowdown
3962                 ,street1Aggr
3963                 ,street2Aggr
3964                 ,street3Aggr
3965                 ,street4Aggr
3966                 ,otherRaisedStreet0
3967                 ,otherRaisedStreet1
3968                 ,otherRaisedStreet2
3969                 ,otherRaisedStreet3
3970                 ,otherRaisedStreet4
3971                 ,foldToOtherRaisedStreet0
3972                 ,foldToOtherRaisedStreet1
3973                 ,foldToOtherRaisedStreet2
3974                 ,foldToOtherRaisedStreet3
3975                 ,foldToOtherRaisedStreet4
3976                 ,raiseFirstInChance
3977                 ,raisedFirstIn
3978                 ,foldBbToStealChance
3979                 ,foldedBbToSteal
3980                 ,foldSbToStealChance
3981                 ,foldedSbToSteal
3982                 ,street1CBChance
3983                 ,street1CBDone
3984                 ,street2CBChance
3985                 ,street2CBDone
3986                 ,street3CBChance
3987                 ,street3CBDone
3988                 ,street4CBChance
3989                 ,street4CBDone
3990                 ,foldToStreet1CBChance
3991                 ,foldToStreet1CBDone
3992                 ,foldToStreet2CBChance
3993                 ,foldToStreet2CBDone
3994                 ,foldToStreet3CBChance
3995                 ,foldToStreet3CBDone
3996                 ,foldToStreet4CBChance
3997                 ,foldToStreet4CBDone
3998                 ,totalProfit
3999                 ,street1CheckCallRaiseChance
4000                 ,street1CheckCallRaiseDone
4001                 ,street2CheckCallRaiseChance
4002                 ,street2CheckCallRaiseDone
4003                 ,street3CheckCallRaiseChance
4004                 ,street3CheckCallRaiseDone
4005                 ,street4CheckCallRaiseChance
4006                 ,street4CheckCallRaiseDone
4007                 ,street0Calls
4008                 ,street1Calls
4009                 ,street2Calls
4010                 ,street3Calls
4011                 ,street4Calls
4012                 ,street0Bets
4013                 ,street1Bets
4014                 ,street2Bets
4015                 ,street3Bets
4016                 ,street4Bets
4017                 ,street0Raises
4018                 ,street1Raises
4019                 ,street2Raises
4020                 ,street3Raises
4021                 ,street4Raises
4022                 )
4023                 SELECT h.gametypeId
4024                       ,hp.playerId
4025                       ,h.seats
4026                       ,case when hp.position = 'B' then 'B'
4027                             when hp.position = 'S' then 'S'
4028                             when hp.position = '0' then 'D'
4029                             when hp.position = '1' then 'C'
4030                             when hp.position = '2' then 'M'
4031                             when hp.position = '3' then 'M'
4032                             when hp.position = '4' then 'M'
4033                             when hp.position = '5' then 'E'
4034                             when hp.position = '6' then 'E'
4035                             when hp.position = '7' then 'E'
4036                             when hp.position = '8' then 'E'
4037                             when hp.position = '9' then 'E'
4038                             else 'E'
4039                        end                                            AS hc_position
4040                       <tourney_select_clause>
4041                       ,date_format(h.startTime, 'd%y%m%d')
4042                       ,count(1)
4043                       ,sum(wonWhenSeenStreet1)
4044                       ,sum(wonWhenSeenStreet2)
4045                       ,sum(wonWhenSeenStreet3)
4046                       ,sum(wonWhenSeenStreet4)
4047                       ,sum(wonAtSD)
4048                       ,sum(street0VPI)
4049                       ,sum(street0Aggr)
4050                       ,sum(street0_3BChance)
4051                       ,sum(street0_3BDone)
4052                       ,sum(street0_4BChance)
4053                       ,sum(street0_4BDone)
4054                       ,sum(street0_C4BChance)
4055                       ,sum(street0_C4BDone)
4056                       ,sum(street0_FoldTo3BChance)
4057                       ,sum(street0_FoldTo3BDone)
4058                       ,sum(street0_FoldTo4BChance)
4059                       ,sum(street0_FoldTo4BDone)
4060                       ,sum(street0_SqueezeChance)
4061                       ,sum(street0_SqueezeDone)
4062                       ,sum(raiseToStealChance)
4063                       ,sum(raiseToStealDone)
4064                       ,sum(success_Steal)
4065                       ,sum(street1Seen)
4066                       ,sum(street2Seen)
4067                       ,sum(street3Seen)
4068                       ,sum(street4Seen)
4069                       ,sum(sawShowdown)
4070                       ,sum(street1Aggr)
4071                       ,sum(street2Aggr)
4072                       ,sum(street3Aggr)
4073                       ,sum(street4Aggr)
4074                       ,sum(otherRaisedStreet0)
4075                       ,sum(otherRaisedStreet1)
4076                       ,sum(otherRaisedStreet2)
4077                       ,sum(otherRaisedStreet3)
4078                       ,sum(otherRaisedStreet4)
4079                       ,sum(foldToOtherRaisedStreet0)
4080                       ,sum(foldToOtherRaisedStreet1)
4081                       ,sum(foldToOtherRaisedStreet2)
4082                       ,sum(foldToOtherRaisedStreet3)
4083                       ,sum(foldToOtherRaisedStreet4)
4084                       ,sum(raiseFirstInChance)
4085                       ,sum(raisedFirstIn)
4086                       ,sum(foldBbToStealChance)
4087                       ,sum(foldedBbToSteal)
4088                       ,sum(foldSbToStealChance)
4089                       ,sum(foldedSbToSteal)
4090                       ,sum(street1CBChance)
4091                       ,sum(street1CBDone)
4092                       ,sum(street2CBChance)
4093                       ,sum(street2CBDone)
4094                       ,sum(street3CBChance)
4095                       ,sum(street3CBDone)
4096                       ,sum(street4CBChance)
4097                       ,sum(street4CBDone)
4098                       ,sum(foldToStreet1CBChance)
4099                       ,sum(foldToStreet1CBDone)
4100                       ,sum(foldToStreet2CBChance)
4101                       ,sum(foldToStreet2CBDone)
4102                       ,sum(foldToStreet3CBChance)
4103                       ,sum(foldToStreet3CBDone)
4104                       ,sum(foldToStreet4CBChance)
4105                       ,sum(foldToStreet4CBDone)
4106                       ,sum(totalProfit)
4107                       ,sum(street1CheckCallRaiseChance)
4108                       ,sum(street1CheckCallRaiseDone)
4109                       ,sum(street2CheckCallRaiseChance)
4110                       ,sum(street2CheckCallRaiseDone)
4111                       ,sum(street3CheckCallRaiseChance)
4112                       ,sum(street3CheckCallRaiseDone)
4113                       ,sum(street4CheckCallRaiseChance)
4114                       ,sum(street4CheckCallRaiseDone)
4115                       ,sum(street0Calls)
4116                       ,sum(street1Calls)
4117                       ,sum(street2Calls)
4118                       ,sum(street3Calls)
4119                       ,sum(street4Calls)
4120                       ,sum(street0Bets)
4121                       ,sum(street1Bets)
4122                       ,sum(street2Bets)
4123                       ,sum(street3Bets)
4124                       ,sum(street4Bets)
4125                       ,sum(hp.street0Raises)
4126                       ,sum(hp.street1Raises)
4127                       ,sum(hp.street2Raises)
4128                       ,sum(hp.street3Raises)
4129                       ,sum(hp.street4Raises)
4130                 FROM HandsPlayers hp
4131                 INNER JOIN Hands h ON (h.id = hp.handId)
4132                 <tourney_join_clause>
4133                 <where_clause>
4134                 GROUP BY h.gametypeId
4135                         ,hp.playerId
4136                         ,h.seats
4137                         ,hc_position
4138                         <tourney_group_clause>
4139                         ,date_format(h.startTime, 'd%y%m%d')
4140 """
4141         elif db_server == 'postgresql':
4142             self.query['rebuildHudCache'] = """
4143                 INSERT INTO HudCache
4144                 (gametypeId
4145                 ,playerId
4146                 ,activeSeats
4147                 ,position
4148                 <tourney_insert_clause>
4149                 ,styleKey
4150                 ,HDs
4151                 ,wonWhenSeenStreet1
4152                 ,wonWhenSeenStreet2
4153                 ,wonWhenSeenStreet3
4154                 ,wonWhenSeenStreet4
4155                 ,wonAtSD
4156                 ,street0VPI
4157                 ,street0Aggr
4158                 ,street0_3BChance
4159                 ,street0_3BDone
4160                 ,street0_4BChance
4161                 ,street0_4BDone
4162                 ,street0_C4BChance
4163                 ,street0_C4BDone
4164                 ,street0_FoldTo3BChance
4165                 ,street0_FoldTo3BDone
4166                 ,street0_FoldTo4BChance
4167                 ,street0_FoldTo4BDone
4168                 ,street0_SqueezeChance
4169                 ,street0_SqueezeDone
4170                 ,raiseToStealChance
4171                 ,raiseToStealDone
4172                 ,success_Steal
4173                 ,street1Seen
4174                 ,street2Seen
4175                 ,street3Seen
4176                 ,street4Seen
4177                 ,sawShowdown
4178                 ,street1Aggr
4179                 ,street2Aggr
4180                 ,street3Aggr
4181                 ,street4Aggr
4182                 ,otherRaisedStreet0
4183                 ,otherRaisedStreet1
4184                 ,otherRaisedStreet2
4185                 ,otherRaisedStreet3
4186                 ,otherRaisedStreet4
4187                 ,foldToOtherRaisedStreet0
4188                 ,foldToOtherRaisedStreet1
4189                 ,foldToOtherRaisedStreet2
4190                 ,foldToOtherRaisedStreet3
4191                 ,foldToOtherRaisedStreet4
4192                 ,raiseFirstInChance
4193                 ,raisedFirstIn
4194                 ,foldBbToStealChance
4195                 ,foldedBbToSteal
4196                 ,foldSbToStealChance
4197                 ,foldedSbToSteal
4198                 ,street1CBChance
4199                 ,street1CBDone
4200                 ,street2CBChance
4201                 ,street2CBDone
4202                 ,street3CBChance
4203                 ,street3CBDone
4204                 ,street4CBChance
4205                 ,street4CBDone
4206                 ,foldToStreet1CBChance
4207                 ,foldToStreet1CBDone
4208                 ,foldToStreet2CBChance
4209                 ,foldToStreet2CBDone
4210                 ,foldToStreet3CBChance
4211                 ,foldToStreet3CBDone
4212                 ,foldToStreet4CBChance
4213                 ,foldToStreet4CBDone
4214                 ,totalProfit
4215                 ,street1CheckCallRaiseChance
4216                 ,street1CheckCallRaiseDone
4217                 ,street2CheckCallRaiseChance
4218                 ,street2CheckCallRaiseDone
4219                 ,street3CheckCallRaiseChance
4220                 ,street3CheckCallRaiseDone
4221                 ,street4CheckCallRaiseChance
4222                 ,street4CheckCallRaiseDone
4223                 ,street0Calls
4224                 ,street1Calls
4225                 ,street2Calls
4226                 ,street3Calls
4227                 ,street4Calls
4228                 ,street0Bets
4229                 ,street1Bets
4230                 ,street2Bets
4231                 ,street3Bets
4232                 ,street4Bets
4233                 ,street0Raises
4234                 ,street1Raises
4235                 ,street2Raises
4236                 ,street3Raises
4237                 ,street4Raises
4238                 )
4239                 SELECT h.gametypeId
4240                       ,hp.playerId
4241                       ,h.seats
4242                       ,case when hp.position = 'B' then 'B'
4243                             when hp.position = 'S' then 'S'
4244                             when hp.position = '0' then 'D'
4245                             when hp.position = '1' then 'C'
4246                             when hp.position = '2' then 'M'
4247                             when hp.position = '3' then 'M'
4248                             when hp.position = '4' then 'M'
4249                             when hp.position = '5' then 'E'
4250                             when hp.position = '6' then 'E'
4251                             when hp.position = '7' then 'E'
4252                             when hp.position = '8' then 'E'
4253                             when hp.position = '9' then 'E'
4254                             else 'E'
4255                        end                                            AS hc_position
4256                       <tourney_select_clause>
4257                       ,'d' || to_char(h.startTime, 'YYMMDD')
4258                       ,count(1)
4259                       ,sum(wonWhenSeenStreet1)
4260                       ,sum(wonWhenSeenStreet2)
4261                       ,sum(wonWhenSeenStreet3)
4262                       ,sum(wonWhenSeenStreet4)
4263                       ,sum(wonAtSD)
4264                       ,sum(CAST(street0VPI as integer))
4265                       ,sum(CAST(street0Aggr as integer))
4266                       ,sum(CAST(street0_3BChance as integer))
4267                       ,sum(CAST(street0_3BDone as integer))
4268                       ,sum(CAST(street0_4BChance as integer))
4269                       ,sum(CAST(street0_4BDone as integer))
4270                       ,sum(CAST(street0_C4BChance as integer))
4271                       ,sum(CAST(street0_C4BDone as integer))
4272                       ,sum(CAST(street0_FoldTo3BChance as integer))
4273                       ,sum(CAST(street0_FoldTo3BDone as integer))
4274                       ,sum(CAST(street0_FoldTo4BChance as integer))
4275                       ,sum(CAST(street0_FoldTo4BDone as integer))
4276                       ,sum(CAST(street0_SqueezeChance as integer))
4277                       ,sum(CAST(street0_SqueezeDone as integer))
4278                       ,sum(CAST(raiseToStealChance as integer))
4279                       ,sum(CAST(raiseToStealDone as integer))
4280                       ,sum(CAST(success_Steal as integer))
4281                       ,sum(CAST(street1Seen as integer))
4282                       ,sum(CAST(street2Seen as integer))
4283                       ,sum(CAST(street3Seen as integer))
4284                       ,sum(CAST(street4Seen as integer))
4285                       ,sum(CAST(sawShowdown as integer))
4286                       ,sum(CAST(street1Aggr as integer))
4287                       ,sum(CAST(street2Aggr as integer))
4288                       ,sum(CAST(street3Aggr as integer))
4289                       ,sum(CAST(street4Aggr as integer))
4290                       ,sum(CAST(otherRaisedStreet0 as integer))
4291                       ,sum(CAST(otherRaisedStreet1 as integer))
4292                       ,sum(CAST(otherRaisedStreet2 as integer))
4293                       ,sum(CAST(otherRaisedStreet3 as integer))
4294                       ,sum(CAST(otherRaisedStreet4 as integer))
4295                       ,sum(CAST(foldToOtherRaisedStreet0 as integer))
4296                       ,sum(CAST(foldToOtherRaisedStreet1 as integer))
4297                       ,sum(CAST(foldToOtherRaisedStreet2 as integer))
4298                       ,sum(CAST(foldToOtherRaisedStreet3 as integer))
4299                       ,sum(CAST(foldToOtherRaisedStreet4 as integer))
4300                       ,sum(CAST(raiseFirstInChance as integer))
4301                       ,sum(CAST(raisedFirstIn as integer))
4302                       ,sum(CAST(foldBbToStealChance as integer))
4303                       ,sum(CAST(foldedBbToSteal as integer))
4304                       ,sum(CAST(foldSbToStealChance as integer))
4305                       ,sum(CAST(foldedSbToSteal as integer))
4306                       ,sum(CAST(street1CBChance as integer))
4307                       ,sum(CAST(street1CBDone as integer))
4308                       ,sum(CAST(street2CBChance as integer))
4309                       ,sum(CAST(street2CBDone as integer))
4310                       ,sum(CAST(street3CBChance as integer))
4311                       ,sum(CAST(street3CBDone as integer))
4312                       ,sum(CAST(street4CBChance as integer))
4313                       ,sum(CAST(street4CBDone as integer))
4314                       ,sum(CAST(foldToStreet1CBChance as integer))
4315                       ,sum(CAST(foldToStreet1CBDone as integer))
4316                       ,sum(CAST(foldToStreet2CBChance as integer))
4317                       ,sum(CAST(foldToStreet2CBDone as integer))
4318                       ,sum(CAST(foldToStreet3CBChance as integer))
4319                       ,sum(CAST(foldToStreet3CBDone as integer))
4320                       ,sum(CAST(foldToStreet4CBChance as integer))
4321                       ,sum(CAST(foldToStreet4CBDone as integer))
4322                       ,sum(CAST(totalProfit as integer))
4323                       ,sum(CAST(street1CheckCallRaiseChance as integer))
4324                       ,sum(CAST(street1CheckCallRaiseDone as integer))
4325                       ,sum(CAST(street2CheckCallRaiseChance as integer))
4326                       ,sum(CAST(street2CheckCallRaiseDone as integer))
4327                       ,sum(CAST(street3CheckCallRaiseChance as integer))
4328                       ,sum(CAST(street3CheckCallRaiseDone as integer))
4329                       ,sum(CAST(street4CheckCallRaiseChance as integer))
4330                       ,sum(CAST(street4CheckCallRaiseDone as integer))
4331                       ,sum(CAST(street0Calls as integer))
4332                       ,sum(CAST(street1Calls as integer))
4333                       ,sum(CAST(street2Calls as integer))
4334                       ,sum(CAST(street3Calls as integer))
4335                       ,sum(CAST(street4Calls as integer))
4336                       ,sum(CAST(street0Bets as integer))
4337                       ,sum(CAST(street1Bets as integer))
4338                       ,sum(CAST(street2Bets as integer))
4339                       ,sum(CAST(street3Bets as integer))
4340                       ,sum(CAST(street4Bets as integer))
4341                       ,sum(CAST(hp.street0Raises as integer))
4342                       ,sum(CAST(hp.street1Raises as integer))
4343                       ,sum(CAST(hp.street2Raises as integer))
4344                       ,sum(CAST(hp.street3Raises as integer))
4345                       ,sum(CAST(hp.street4Raises as integer))
4346                 FROM HandsPlayers hp
4347                 INNER JOIN Hands h ON (h.id = hp.handId)
4348                 <tourney_join_clause>
4349                 <where_clause>
4350                 GROUP BY h.gametypeId
4351                         ,hp.playerId
4352                         ,h.seats
4353                         ,hc_position
4354                         <tourney_group_clause>
4355                         ,to_char(h.startTime, 'YYMMDD')
4356 """
4357         else:   # assume sqlite
4358             self.query['rebuildHudCache'] = """
4359                 INSERT INTO HudCache
4360                 (gametypeId
4361                 ,playerId
4362                 ,activeSeats
4363                 ,position
4364                 <tourney_insert_clause>
4365                 ,styleKey
4366                 ,HDs
4367                 ,wonWhenSeenStreet1
4368                 ,wonWhenSeenStreet2
4369                 ,wonWhenSeenStreet3
4370                 ,wonWhenSeenStreet4
4371                 ,wonAtSD
4372                 ,street0VPI
4373                 ,street0Aggr
4374                 ,street0_3BChance
4375                 ,street0_3BDone
4376                 ,street0_4BChance
4377                 ,street0_4BDone
4378                 ,street0_C4BChance
4379                 ,street0_C4BDone
4380                 ,street0_FoldTo3BChance
4381                 ,street0_FoldTo3BDone
4382                 ,street0_FoldTo4BChance
4383                 ,street0_FoldTo4BDone
4384                 ,street0_SqueezeChance
4385                 ,street0_SqueezeDone
4386                 ,raiseToStealChance
4387                 ,raiseToStealDone
4388                 ,success_Steal
4389                 ,street1Seen
4390                 ,street2Seen
4391                 ,street3Seen
4392                 ,street4Seen
4393                 ,sawShowdown
4394                 ,street1Aggr
4395                 ,street2Aggr
4396                 ,street3Aggr
4397                 ,street4Aggr
4398                 ,otherRaisedStreet0
4399                 ,otherRaisedStreet1
4400                 ,otherRaisedStreet2
4401                 ,otherRaisedStreet3
4402                 ,otherRaisedStreet4
4403                 ,foldToOtherRaisedStreet0
4404                 ,foldToOtherRaisedStreet1
4405                 ,foldToOtherRaisedStreet2
4406                 ,foldToOtherRaisedStreet3
4407                 ,foldToOtherRaisedStreet4
4408                 ,raiseFirstInChance
4409                 ,raisedFirstIn
4410                 ,foldBbToStealChance
4411                 ,foldedBbToSteal
4412                 ,foldSbToStealChance
4413                 ,foldedSbToSteal
4414                 ,street1CBChance
4415                 ,street1CBDone
4416                 ,street2CBChance
4417                 ,street2CBDone
4418                 ,street3CBChance
4419                 ,street3CBDone
4420                 ,street4CBChance
4421                 ,street4CBDone
4422                 ,foldToStreet1CBChance
4423                 ,foldToStreet1CBDone
4424                 ,foldToStreet2CBChance
4425                 ,foldToStreet2CBDone
4426                 ,foldToStreet3CBChance
4427                 ,foldToStreet3CBDone
4428                 ,foldToStreet4CBChance
4429                 ,foldToStreet4CBDone
4430                 ,totalProfit
4431                 ,street1CheckCallRaiseChance
4432                 ,street1CheckCallRaiseDone
4433                 ,street2CheckCallRaiseChance
4434                 ,street2CheckCallRaiseDone
4435                 ,street3CheckCallRaiseChance
4436                 ,street3CheckCallRaiseDone
4437                 ,street4CheckCallRaiseChance
4438                 ,street4CheckCallRaiseDone
4439                 ,street0Calls
4440                 ,street1Calls
4441                 ,street2Calls
4442                 ,street3Calls
4443                 ,street4Calls
4444                 ,street0Bets
4445                 ,street1Bets
4446                 ,street2Bets
4447                 ,street3Bets
4448                 ,street4Bets
4449                 ,street0Raises
4450                 ,street1Raises
4451                 ,street2Raises
4452                 ,street3Raises
4453                 ,street4Raises
4454                 )
4455                 SELECT h.gametypeId
4456                       ,hp.playerId
4457                       ,h.seats
4458                       ,case when hp.position = 'B' then 'B'
4459                             when hp.position = 'S' then 'S'
4460                             when hp.position = '0' then 'D'
4461                             when hp.position = '1' then 'C'
4462                             when hp.position = '2' then 'M'
4463                             when hp.position = '3' then 'M'
4464                             when hp.position = '4' then 'M'
4465                             when hp.position = '5' then 'E'
4466                             when hp.position = '6' then 'E'
4467                             when hp.position = '7' then 'E'
4468                             when hp.position = '8' then 'E'
4469                             when hp.position = '9' then 'E'
4470                             else 'E'
4471                        end                                            AS hc_position
4472                       <tourney_select_clause>
4473                       ,'d' || substr(strftime('%Y%m%d', h.startTime),3,7)
4474                       ,count(1)
4475                       ,sum(wonWhenSeenStreet1)
4476                       ,sum(wonWhenSeenStreet2)
4477                       ,sum(wonWhenSeenStreet3)
4478                       ,sum(wonWhenSeenStreet4)
4479                       ,sum(wonAtSD)
4480                       ,sum(CAST(street0VPI as integer))
4481                       ,sum(CAST(street0Aggr as integer))
4482                       ,sum(CAST(street0_3BChance as integer))
4483                       ,sum(CAST(street0_3BDone as integer))
4484                       ,sum(CAST(street0_4BChance as integer))
4485                       ,sum(CAST(street0_4BDone as integer))
4486                       ,sum(CAST(street0_C4BChance as integer))
4487                       ,sum(CAST(street0_C4BDone as integer))
4488                       ,sum(CAST(street0_FoldTo3BChance as integer))
4489                       ,sum(CAST(street0_FoldTo3BDone as integer))
4490                       ,sum(CAST(street0_FoldTo4BChance as integer))
4491                       ,sum(CAST(street0_FoldTo4BDone as integer))
4492                       ,sum(CAST(street0_SqueezeChance as integer))
4493                       ,sum(CAST(street0_SqueezeDone as integer))
4494                       ,sum(CAST(raiseToStealChance as integer))
4495                       ,sum(CAST(raiseToStealDone as integer))
4496                       ,sum(CAST(success_Steal as integer))
4497                       ,sum(CAST(street1Seen as integer))
4498                       ,sum(CAST(street2Seen as integer))
4499                       ,sum(CAST(street3Seen as integer))
4500                       ,sum(CAST(street4Seen as integer))
4501                       ,sum(CAST(sawShowdown as integer))
4502                       ,sum(CAST(street1Aggr as integer))
4503                       ,sum(CAST(street2Aggr as integer))
4504                       ,sum(CAST(street3Aggr as integer))
4505                       ,sum(CAST(street4Aggr as integer))
4506                       ,sum(CAST(otherRaisedStreet0 as integer))
4507                       ,sum(CAST(otherRaisedStreet1 as integer))
4508                       ,sum(CAST(otherRaisedStreet2 as integer))
4509                       ,sum(CAST(otherRaisedStreet3 as integer))
4510                       ,sum(CAST(otherRaisedStreet4 as integer))
4511                       ,sum(CAST(foldToOtherRaisedStreet0 as integer))
4512                       ,sum(CAST(foldToOtherRaisedStreet1 as integer))
4513                       ,sum(CAST(foldToOtherRaisedStreet2 as integer))
4514                       ,sum(CAST(foldToOtherRaisedStreet3 as integer))
4515                       ,sum(CAST(foldToOtherRaisedStreet4 as integer))
4516                       ,sum(CAST(raiseFirstInChance as integer))
4517                       ,sum(CAST(raisedFirstIn as integer))
4518                       ,sum(CAST(foldBbToStealChance as integer))
4519                       ,sum(CAST(foldedBbToSteal as integer))
4520                       ,sum(CAST(foldSbToStealChance as integer))
4521                       ,sum(CAST(foldedSbToSteal as integer))
4522                       ,sum(CAST(street1CBChance as integer))
4523                       ,sum(CAST(street1CBDone as integer))
4524                       ,sum(CAST(street2CBChance as integer))
4525                       ,sum(CAST(street2CBDone as integer))
4526                       ,sum(CAST(street3CBChance as integer))
4527                       ,sum(CAST(street3CBDone as integer))
4528                       ,sum(CAST(street4CBChance as integer))
4529                       ,sum(CAST(street4CBDone as integer))
4530                       ,sum(CAST(foldToStreet1CBChance as integer))
4531                       ,sum(CAST(foldToStreet1CBDone as integer))
4532                       ,sum(CAST(foldToStreet2CBChance as integer))
4533                       ,sum(CAST(foldToStreet2CBDone as integer))
4534                       ,sum(CAST(foldToStreet3CBChance as integer))
4535                       ,sum(CAST(foldToStreet3CBDone as integer))
4536                       ,sum(CAST(foldToStreet4CBChance as integer))
4537                       ,sum(CAST(foldToStreet4CBDone as integer))
4538                       ,sum(CAST(totalProfit as integer))
4539                       ,sum(CAST(street1CheckCallRaiseChance as integer))
4540                       ,sum(CAST(street1CheckCallRaiseDone as integer))
4541                       ,sum(CAST(street2CheckCallRaiseChance as integer))
4542                       ,sum(CAST(street2CheckCallRaiseDone as integer))
4543                       ,sum(CAST(street3CheckCallRaiseChance as integer))
4544                       ,sum(CAST(street3CheckCallRaiseDone as integer))
4545                       ,sum(CAST(street4CheckCallRaiseChance as integer))
4546                       ,sum(CAST(street4CheckCallRaiseDone as integer))
4547                       ,sum(CAST(street0Calls as integer))
4548                       ,sum(CAST(street1Calls as integer))
4549                       ,sum(CAST(street2Calls as integer))
4550                       ,sum(CAST(street3Calls as integer))
4551                       ,sum(CAST(street4Calls as integer))
4552                       ,sum(CAST(street0Bets as integer))
4553                       ,sum(CAST(street1Bets as integer))
4554                       ,sum(CAST(street2Bets as integer))
4555                       ,sum(CAST(street3Bets as integer))
4556                       ,sum(CAST(street4Bets as integer))
4557                       ,sum(CAST(hp.street0Raises as integer))
4558                       ,sum(CAST(hp.street1Raises as integer))
4559                       ,sum(CAST(hp.street2Raises as integer))
4560                       ,sum(CAST(hp.street3Raises as integer))
4561                       ,sum(CAST(hp.street4Raises as integer))
4562                 FROM HandsPlayers hp
4563                 INNER JOIN Hands h ON (h.id = hp.handId)
4564                 <tourney_join_clause>
4565                 <where_clause>
4566                 GROUP BY h.gametypeId
4567                         ,hp.playerId
4568                         ,h.seats
4569                         ,hc_position
4570                         <tourney_group_clause>
4571                         ,'d' || substr(strftime('%Y%m%d', h.startTime),3,7)
4572 """
4573
4574         self.query['insert_hudcache'] = """
4575             insert into HudCache (
4576                 gametypeId,
4577                 playerId,
4578                 activeSeats,
4579                 position,
4580                 tourneyTypeId,
4581                 styleKey,
4582                 HDs,
4583                 street0VPI,
4584                 street0Aggr,
4585                 street0_3BChance,
4586                 street0_3BDone,
4587                 street0_4BChance,
4588                 street0_4BDone,
4589                 street0_C4BChance,
4590                 street0_C4BDone,
4591                 street0_FoldTo3BChance,
4592                 street0_FoldTo3BDone,
4593                 street0_FoldTo4BChance,
4594                 street0_FoldTo4BDone,
4595                 street0_SqueezeChance,
4596                 street0_SqueezeDone,
4597                 raiseToStealChance,
4598                 raiseToStealDone,
4599                 success_Steal,
4600                 street1Seen,
4601                 street2Seen,
4602                 street3Seen,
4603                 street4Seen,
4604                 sawShowdown,
4605                 street1Aggr,
4606                 street2Aggr,
4607                 street3Aggr,
4608                 street4Aggr,
4609                 otherRaisedStreet0,
4610                 otherRaisedStreet1,
4611                 otherRaisedStreet2,
4612                 otherRaisedStreet3,
4613                 otherRaisedStreet4,
4614                 foldToOtherRaisedStreet0,
4615                 foldToOtherRaisedStreet1,
4616                 foldToOtherRaisedStreet2,
4617                 foldToOtherRaisedStreet3,
4618                 foldToOtherRaisedStreet4,
4619                 wonWhenSeenStreet1,
4620                 wonWhenSeenStreet2,
4621                 wonWhenSeenStreet3,
4622                 wonWhenSeenStreet4,
4623                 wonAtSD,
4624                 raiseFirstInChance,
4625                 raisedFirstIn,
4626                 foldBbToStealChance,
4627                 foldedBbToSteal,
4628                 foldSbToStealChance,
4629                 foldedSbToSteal,
4630                 street1CBChance,
4631                 street1CBDone,
4632                 street2CBChance,
4633                 street2CBDone,
4634                 street3CBChance,
4635                 street3CBDone,
4636                 street4CBChance,
4637                 street4CBDone,
4638                 foldToStreet1CBChance,
4639                 foldToStreet1CBDone,
4640                 foldToStreet2CBChance,
4641                 foldToStreet2CBDone,
4642                 foldToStreet3CBChance,
4643                 foldToStreet3CBDone,
4644                 foldToStreet4CBChance,
4645                 foldToStreet4CBDone,
4646                 totalProfit,
4647                 street1CheckCallRaiseChance,
4648                 street1CheckCallRaiseDone,
4649                 street2CheckCallRaiseChance,
4650                 street2CheckCallRaiseDone,
4651                 street3CheckCallRaiseChance,
4652                 street3CheckCallRaiseDone,
4653                 street4CheckCallRaiseChance,
4654                 street4CheckCallRaiseDone,
4655                 street0Calls,
4656                 street1Calls,
4657                 street2Calls,
4658                 street3Calls,
4659                 street4Calls,
4660                 street0Bets,
4661                 street1Bets,
4662                 street2Bets,
4663                 street3Bets,
4664                 street4Bets,
4665                 street0Raises,
4666                 street1Raises,
4667                 street2Raises,
4668                 street3Raises,
4669                 street4Raises)
4670             values (%s, %s, %s, %s, %s,
4671                     %s, %s, %s, %s, %s,
4672                     %s, %s, %s, %s, %s,
4673                     %s, %s, %s, %s, %s,
4674                     %s, %s, %s, %s, %s,
4675                     %s, %s, %s, %s, %s,
4676                     %s, %s, %s, %s, %s,
4677                     %s, %s, %s, %s, %s,
4678                     %s, %s, %s, %s, %s,
4679                     %s, %s, %s, %s, %s,
4680                     %s, %s, %s, %s, %s,
4681                     %s, %s, %s, %s, %s,
4682                     %s, %s, %s, %s, %s,
4683                     %s, %s, %s, %s, %s,
4684                     %s, %s, %s, %s, %s,
4685                     %s, %s, %s, %s, %s,
4686                     %s, %s, %s, %s, %s,
4687                     %s, %s, %s, %s, %s,
4688                     %s, %s, %s, %s)"""
4689
4690         self.query['update_hudcache'] = """
4691             UPDATE HudCache SET
4692             HDs=HDs+%s,
4693             street0VPI=street0VPI+%s,
4694             street0Aggr=street0Aggr+%s,
4695             street0_3BChance=street0_3BChance+%s,
4696             street0_3BDone=street0_3BDone+%s,
4697             street0_4BChance=street0_4BChance+%s,
4698             street0_4BDone=street0_4BDone+%s,
4699             street0_C4BChance=street0_C4BChance+%s,
4700             street0_C4BDone=street0_C4BDone+%s,
4701             street0_FoldTo3BChance=street0_FoldTo3BChance+%s,
4702             street0_FoldTo3BDone=street0_FoldTo3BDone+%s,
4703             street0_FoldTo4BChance=street0_FoldTo4BChance+%s,
4704             street0_FoldTo4BDone=street0_FoldTo4BDone+%s,
4705             street0_SqueezeChance=street0_SqueezeChance+%s,
4706             street0_SqueezeDone=street0_SqueezeDone+%s,
4707             raiseToStealChance=raiseToStealChance+%s,
4708             raiseToStealDone=raiseToStealDone+%s,
4709             success_Steal=success_Steal+%s,
4710             street1Seen=street1Seen+%s,
4711             street2Seen=street2Seen+%s,
4712             street3Seen=street3Seen+%s,
4713             street4Seen=street4Seen+%s,
4714             sawShowdown=sawShowdown+%s,
4715             street1Aggr=street1Aggr+%s,
4716             street2Aggr=street2Aggr+%s,
4717             street3Aggr=street3Aggr+%s,
4718             street4Aggr=street4Aggr+%s,
4719             otherRaisedStreet0=otherRaisedStreet0+%s,
4720             otherRaisedStreet1=otherRaisedStreet1+%s,
4721             otherRaisedStreet2=otherRaisedStreet2+%s,
4722             otherRaisedStreet3=otherRaisedStreet3+%s,
4723             otherRaisedStreet4=otherRaisedStreet4+%s,
4724             foldToOtherRaisedStreet0=foldToOtherRaisedStreet0+%s,
4725             foldToOtherRaisedStreet1=foldToOtherRaisedStreet1+%s,
4726             foldToOtherRaisedStreet2=foldToOtherRaisedStreet2+%s,
4727             foldToOtherRaisedStreet3=foldToOtherRaisedStreet3+%s,
4728             foldToOtherRaisedStreet4=foldToOtherRaisedStreet4+%s,
4729             wonWhenSeenStreet1=wonWhenSeenStreet1+%s,
4730             wonWhenSeenStreet2=wonWhenSeenStreet2+%s,
4731             wonWhenSeenStreet3=wonWhenSeenStreet3+%s,
4732             wonWhenSeenStreet4=wonWhenSeenStreet4+%s,
4733             wonAtSD=wonAtSD+%s,
4734             raiseFirstInChance=raiseFirstInChance+%s,
4735             raisedFirstIn=raisedFirstIn+%s,
4736             foldBbToStealChance=foldBbToStealChance+%s,
4737             foldedBbToSteal=foldedBbToSteal+%s,
4738             foldSbToStealChance=foldSbToStealChance+%s,
4739             foldedSbToSteal=foldedSbToSteal+%s,
4740             street1CBChance=street1CBChance+%s,
4741             street1CBDone=street1CBDone+%s,
4742             street2CBChance=street2CBChance+%s,
4743             street2CBDone=street2CBDone+%s,
4744             street3CBChance=street3CBChance+%s,
4745             street3CBDone=street3CBDone+%s,
4746             street4CBChance=street4CBChance+%s,
4747             street4CBDone=street4CBDone+%s,
4748             foldToStreet1CBChance=foldToStreet1CBChance+%s,
4749             foldToStreet1CBDone=foldToStreet1CBDone+%s,
4750             foldToStreet2CBChance=foldToStreet2CBChance+%s,
4751             foldToStreet2CBDone=foldToStreet2CBDone+%s,
4752             foldToStreet3CBChance=foldToStreet3CBChance+%s,
4753             foldToStreet3CBDone=foldToStreet3CBDone+%s,
4754             foldToStreet4CBChance=foldToStreet4CBChance+%s,
4755             foldToStreet4CBDone=foldToStreet4CBDone+%s,
4756             totalProfit=totalProfit+%s,
4757             street1CheckCallRaiseChance=street1CheckCallRaiseChance+%s,
4758             street1CheckCallRaiseDone=street1CheckCallRaiseDone+%s,
4759             street2CheckCallRaiseChance=street2CheckCallRaiseChance+%s,
4760             street2CheckCallRaiseDone=street2CheckCallRaiseDone+%s,
4761             street3CheckCallRaiseChance=street3CheckCallRaiseChance+%s,
4762             street3CheckCallRaiseDone=street3CheckCallRaiseDone+%s,
4763             street4CheckCallRaiseChance=street4CheckCallRaiseChance+%s,
4764             street4CheckCallRaiseDone=street4CheckCallRaiseDone+%s,
4765             street0Calls=street0Calls+%s,
4766             street1Calls=street1Calls+%s,
4767             street2Calls=street2Calls+%s,
4768             street3Calls=street3Calls+%s,
4769             street4Calls=street4Calls+%s,
4770             street0Bets=street0Bets+%s,
4771             street1Bets=street1Bets+%s,
4772             street2Bets=street2Bets+%s,
4773             street3Bets=street3Bets+%s,
4774             street4Bets=street4Bets+%s,
4775             street0Raises=street0Raises+%s,
4776             street1Raises=street1Raises+%s,
4777             street2Raises=street2Raises+%s,
4778             street3Raises=street3Raises+%s,
4779             street4Raises=street4Raises+%s
4780         WHERE gametypeId+0=%s
4781             AND   playerId=%s
4782             AND   activeSeats=%s
4783             AND   position=%s
4784             AND   (case when tourneyTypeId is NULL then 1 else
4785                    (case when tourneyTypeId+0=%s then 1 else 0 end) end)=1
4786             AND   styleKey=%s"""
4787
4788         self.query['get_hero_hudcache_start'] = """select min(hc.styleKey)
4789                                                    from HudCache hc
4790                                                    where hc.playerId in <playerid_list>
4791                                                    and   hc.styleKey like 'd%'"""
4792
4793         ####################################
4794         # Queries to rebuild/modify sessionscache
4795         ####################################
4796
4797         self.query['clearSessionsCache'] = """DELETE FROM SessionsCache"""
4798
4799         self.query['rebuildSessionsCache'] = """
4800                     SELECT Hands.id as id,
4801                     Hands.startTime as startTime,
4802                     HandsPlayers.playerId as playerId,
4803                     Hands.gametypeId as gametypeId,
4804                     Gametypes.type as game,
4805                     HandsPlayers.totalProfit as totalProfit,
4806                     Tourneys.tourneyTypeId as tourneyTypeId,
4807                     HandsPlayers.street0VPI as street0VPI,
4808                     HandsPlayers.street1Seen as street1Seen
4809                     FROM Gametypes, HandsPlayers, Hands
4810                     LEFT JOIN Tourneys ON Hands.tourneyId = Tourneys.tourneyTypeId
4811                     WHERE HandsPlayers.handId = Hands.id
4812                     AND   Hands.gametypeId = Gametypes.id
4813                     AND (case when HandsPlayers.playerId = <where_clause> then 1 else 0 end) = 1
4814                     ORDER BY Hands.startTime ASC"""
4815
4816         self.query['rebuildSessionsCacheSum'] = """
4817                     SELECT Tourneys.id as id,
4818                     Tourneys.startTime as startTime,
4819                     TourneysPlayers.playerId,
4820                     TourneyTypes.id as tourneyTypeId,
4821                     TourneysPlayers.winnings as winnings,
4822                     TourneysPlayers.winningsCurrency as winningsCurrency,
4823                     TourneyTypes.currency as buyinCurrency,
4824                     TourneyTypes.buyIn as buyIn,
4825                     TourneyTypes.fee as fee,
4826                     case when TourneyTypes.rebuy then TourneyTypes.rebuyCost else 0 end as rebuyCost,
4827                     case when TourneyTypes.rebuy then TourneyTypes.rebuyFee else 0 end as rebuyFee,
4828                     case when TourneyTypes.addOn then TourneyTypes.addOnCost else 0 end as addOnCost,
4829                     case when TourneyTypes.addOn then TourneyTypes.addOnFee else 0 end as addOnFee,
4830                     case when TourneyTypes.knockout then TourneyTypes.koBounty else 0 end as koBounty
4831                     FROM  Tourneys, TourneyTypes, TourneysPlayers
4832                     WHERE Tourneys.tourneyTypeId = TourneyTypes.id
4833                     AND   Tourneys.id = TourneysPlayers.tourneyId
4834                     AND (case when TourneysPlayers.playerId = <where_clause> then 1 else 0 end) = 1
4835                     ORDER BY Tourneys.startTime ASC"""
4836
4837         self.query['select_prepSC'] = """
4838                     SELECT sessionId as id,
4839                     sessionStart,
4840                     sessionEnd,
4841                     count(sessionId) as count
4842                     FROM SessionsCache
4843                     WHERE sessionEnd>=%s
4844                     AND sessionStart<=%s
4845                     GROUP BY sessionId, sessionStart, sessionEnd"""
4846
4847         self.query['update_prepSC'] = """
4848                     UPDATE SessionsCache SET
4849                     sessionStart=%s,
4850                     sessionEnd=%s
4851                     WHERE sessionId=%s"""
4852
4853         self.query['update_SC'] = """
4854                     UPDATE SessionsCache SET
4855                     sessionStart=%s,
4856                     sessionEnd=%s,
4857                     gameStart=%s,
4858                     gameEnd=%s,
4859                     hands=hands+%s,
4860                     tourneys=tourneys+%s,
4861                     totalProfit=totalProfit+%s
4862                     WHERE id=%s"""
4863
4864         self.query['select_SC'] = """
4865                     SELECT id,
4866                     sessionStart,
4867                     sessionEnd,
4868                     gameStart,
4869                     gameEnd,
4870                     sessionId,
4871                     date,
4872                     type,
4873                     gametypeId,
4874                     tourneyTypeId,
4875                     playerId,
4876                     played,
4877                     hands,
4878                     tourneys,
4879                     totalProfit
4880                     FROM SessionsCache
4881                     WHERE gameEnd>=%s
4882                     AND gameStart<=%s
4883                     AND date=%s
4884                     AND type=%s
4885                     AND (case when gametypeId is NULL then 1 else
4886                         (case when gametypeId=%s then 1 else 0 end) end)=1
4887                     AND (case when tourneyTypeId is NULL then 1 else
4888                         (case when tourneyTypeId=%s then 1 else 0 end) end)=1
4889                     AND playerId=%s
4890                     AND played=%s"""
4891
4892         self.query['insert_SC'] = """
4893                     insert into SessionsCache (
4894                     sessionStart,
4895                     sessionEnd,
4896                     gameStart,
4897                     gameEnd,
4898                     sessionId,
4899                     date,
4900                     type,
4901                     gametypeId,
4902                     tourneyTypeId,
4903                     playerId,
4904                     played,
4905                     hands,
4906                     tourneys,
4907                     totalProfit)
4908                     values (%s, %s, %s, %s, %s, %s, %s,
4909                             %s, %s, %s, %s, %s, %s, %s)"""
4910
4911         self.query['update_Hands_gsid'] = """
4912                     UPDATE Hands SET
4913                     gameSessionId=%s
4914                     WHERE gameSessionId=%s"""
4915
4916         self.query['update_Hands_sid'] = """
4917                     UPDATE Hands SET
4918                     sessionId=%s
4919                     WHERE sessionId=%s"""
4920
4921         self.query['update_SC_sid'] = """
4922                     UPDATE SessionsCache SET
4923                     sessionStart=%s,
4924                     sessionEnd=%s,
4925                     sessionId=%s
4926                     WHERE sessionId=%s"""
4927
4928         self.query['delete_SC'] = """
4929                     DELETE FROM SessionsCache
4930                     WHERE id=%s"""
4931
4932         ####################################
4933         # Database management queries
4934         ####################################
4935
4936         if db_server == 'mysql':
4937             self.query['analyze'] = """
4938             analyze table Autorates, GameTypes, Hands, HandsPlayers, HudCache, Players
4939                         , Settings, Sites, Tourneys, TourneysPlayers, TourneyTypes
4940             """
4941         elif db_server == 'postgresql':
4942             self.query['analyze'] = "analyze"
4943         elif db_server == 'sqlite':
4944             self.query['analyze'] = "analyze"
4945
4946         if db_server == 'mysql':
4947             self.query['selectLock'] = """
4948                         SELECT locked
4949                         FROM InsertLock
4950                         WHERE locked=True
4951                         LOCK IN SHARE MODE"""
4952
4953         if db_server == 'mysql':
4954             self.query['switchLock'] = """
4955                         UPDATE InsertLock SET
4956                         locked=%s
4957                         WHERE id=%s"""
4958
4959         if db_server == 'mysql':
4960             self.query['missedLock'] = """
4961                         UPDATE InsertLock SET
4962                         missed=missed+%s
4963                         WHERE id=%s"""
4964
4965         if db_server == 'mysql':
4966             self.query['lockForInsert'] = """
4967                 lock tables Hands write, HandsPlayers write, HandsActions write, Players write
4968                           , HudCache write, GameTypes write, Sites write, Tourneys write
4969                           , TourneysPlayers write, TourneyTypes write, Autorates write
4970                 """
4971         elif db_server == 'postgresql':
4972             self.query['lockForInsert'] = ""
4973         elif db_server == 'sqlite':
4974             self.query['lockForInsert'] = ""
4975
4976         if db_server == 'mysql':
4977             self.query['vacuum'] = """optimize table Hands, HandsPlayers, HandsActions, Players
4978                                                    , HudCache, GameTypes, Sites, Tourneys
4979                                                    , TourneysPlayers, TourneyTypes, Autorates
4980                                       """
4981         elif db_server == 'postgresql':
4982             self.query['vacuum'] = """ vacuum """
4983         elif db_server == 'sqlite':
4984             self.query['vacuum'] = """ vacuum """
4985
4986         self.query['getGametypeFL'] = """SELECT id
4987                                            FROM Gametypes
4988                                            WHERE siteId=%s
4989                                            AND   type=%s
4990                                            AND   category=%s
4991                                            AND   limitType=%s
4992                                            AND   smallBet=%s
4993                                            AND   bigBet=%s
4994                                            AND   maxSeats=%s
4995                                            AND   ante=%s
4996         """ #TODO: seems odd to have limitType variable in this query
4997
4998         self.query['getGametypeNL'] = """SELECT id
4999                                            FROM Gametypes
5000                                            WHERE siteId=%s
5001                                            AND   type=%s
5002                                            AND   category=%s
5003                                            AND   limitType=%s
5004                                            AND   currency=%s
5005                                            AND   mix=%s
5006                                            AND   smallBlind=%s
5007                                            AND   bigBlind=%s
5008                                            AND   maxSeats=%s
5009                                            AND   ante=%s
5010         """ #TODO: seems odd to have limitType variable in this query
5011
5012         self.query['insertGameTypes'] = """INSERT INTO Gametypes
5013                                               (siteId, currency, type, base, category, limitType
5014                                               ,hiLo, mix, smallBlind, bigBlind, smallBet, bigBet, maxSeats, ante)
5015                                            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
5016
5017         self.query['isAlreadyInDB'] = """SELECT id FROM Hands
5018                                          WHERE gametypeId=%s AND siteHandNo=%s
5019         """
5020
5021         self.query['getTourneyTypeIdByTourneyNo'] = """SELECT tt.id,
5022                                                               tt.buyin,
5023                                                               tt.fee,
5024                                                               tt.maxSeats,
5025                                                               tt.knockout,
5026                                                               tt.rebuy,
5027                                                               tt.addOn,
5028                                                               tt.speed,
5029                                                               tt.shootout,
5030                                                               tt.matrix
5031                                                     FROM TourneyTypes tt
5032                                                     INNER JOIN Tourneys t ON (t.tourneyTypeId = tt.id)
5033                                                     WHERE t.siteTourneyNo=%s AND tt.siteId=%s
5034         """
5035
5036         self.query['getTourneyTypeId'] = """SELECT  id
5037                                             FROM TourneyTypes
5038                                             WHERE siteId=%s
5039                                             AND currency=%s
5040                                             AND buyin=%s
5041                                             AND fee=%s
5042                                             AND category=%s
5043                                             AND limitType=%s
5044                                             AND maxSeats=%s
5045                                             AND knockout=%s
5046                                             AND rebuy=%s
5047                                             AND addOn=%s
5048                                             AND speed=%s
5049                                             AND shootout=%s
5050                                             AND matrix=%s
5051         """
5052
5053         self.query['insertTourneyType'] = """INSERT INTO TourneyTypes
5054                                                   (siteId, currency, buyin, fee, category, limitType, maxSeats, buyInChips, knockout, koBounty, rebuy,
5055                                                   addOn ,speed, shootout, matrix, added, addedCurrency)
5056                                               VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
5057         """
5058
5059         self.query['getTourneyByTourneyNo'] = """SELECT t.*
5060                                         FROM Tourneys t
5061                                         INNER JOIN TourneyTypes tt ON (t.tourneyTypeId = tt.id)
5062                                         WHERE tt.siteId=%s AND t.siteTourneyNo=%s
5063         """
5064
5065         self.query['getTourneyInfo'] = """SELECT tt.*, t.*
5066                                         FROM Tourneys t
5067                                         INNER JOIN TourneyTypes tt ON (t.tourneyTypeId = tt.id)
5068                                         INNER JOIN Sites s ON (tt.siteId = s.id)
5069                                         WHERE s.name=%s AND t.siteTourneyNo=%s
5070         """
5071
5072         self.query['getSiteTourneyNos'] = """SELECT t.siteTourneyNo
5073                                         FROM Tourneys t
5074                                         INNER JOIN TourneyTypes tt ON (t.tourneyTypeId = tt.id)
5075                                         INNER JOIN Sites s ON (tt.siteId = s.id)
5076                                         WHERE tt.siteId=%s
5077         """
5078
5079         self.query['getTourneyPlayerInfo'] = """SELECT tp.*
5080                                         FROM Tourneys t
5081                                         INNER JOIN TourneyTypes tt ON (t.tourneyTypeId = tt.id)
5082                                         INNER JOIN Sites s ON (tt.siteId = s.id)
5083                                         INNER JOIN TourneysPlayers tp ON (tp.tourneyId = t.id)
5084                                         INNER JOIN Players p ON (p.id = tp.playerId)
5085                                         WHERE s.name=%s AND t.siteTourneyNo=%s AND p.name=%s
5086         """
5087
5088         self.query['insertTourney'] = """INSERT INTO Tourneys
5089                                             (tourneyTypeId, siteTourneyNo, entries, prizepool,
5090                                              startTime, endTime, tourneyName, matrixIdProcessed,
5091                                              totalRebuyCount, totalAddOnCount)
5092                                         VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
5093         """
5094
5095         self.query['updateTourney'] = """UPDATE Tourneys
5096                                              SET entries = %s,
5097                                                  prizepool = %s,
5098                                                  startTime = %s,
5099                                                  endTime = %s,
5100                                                  tourneyName = %s,
5101                                                  matrixIdProcessed = %s,
5102                                                  totalRebuyCount = %s,
5103                                                  totalAddOnCount = %s,
5104                                                  comment = %s,
5105                                                  commentTs = %s
5106                                         WHERE id=%s
5107         """
5108
5109         self.query['getTourneysPlayersByIds'] = """SELECT *
5110                                                 FROM TourneysPlayers
5111                                                 WHERE tourneyId=%s AND playerId+0=%s
5112         """
5113
5114         self.query['updateTourneysPlayer'] = """UPDATE TourneysPlayers
5115                                                  SET rank = %s,
5116                                                      winnings = %s,
5117                                                      winningsCurrency = %s,
5118                                                      rebuyCount = %s,
5119                                                      addOnCount = %s,
5120                                                      koCount = %s
5121                                                  WHERE id=%s
5122         """
5123
5124         self.query['insertTourneysPlayer'] = """insert into TourneysPlayers
5125                                                     (tourneyId, playerId, rank, winnings, winningsCurrency, rebuyCount, addOnCount, koCount)
5126                                                 values (%s, %s, %s, %s, %s, %s, %s, %s)
5127         """
5128
5129         self.query['selectHandsPlayersWithWrongTTypeId'] = """SELECT id
5130                                                               FROM HandsPlayers
5131                                                               WHERE tourneyTypeId <> %s AND (TourneysPlayersId+0=%s)
5132         """
5133
5134 #            self.query['updateHandsPlayersForTTypeId2'] = """UPDATE HandsPlayers
5135 #                                                            SET tourneyTypeId= %s
5136 #                                                            WHERE (TourneysPlayersId+0=%s)
5137 #            """
5138
5139         self.query['updateHandsPlayersForTTypeId'] = """UPDATE HandsPlayers
5140                                                          SET tourneyTypeId= %s
5141                                                          WHERE (id=%s)
5142         """
5143
5144
5145         self.query['handsPlayersTTypeId_joiner'] = " OR TourneysPlayersId+0="
5146         self.query['handsPlayersTTypeId_joiner_id'] = " OR id="
5147
5148         self.query['store_hand'] = """insert into Hands (
5149                                             tablename,
5150                                             sitehandno,
5151                                             tourneyId,
5152                                             gametypeid,
5153                                             sessionId,
5154                                             gameSessionId,
5155                                             fileId,
5156                                             startTime,
5157                                             importtime,
5158                                             seats,
5159                                             texture,
5160                                             playersVpi,
5161                                             boardcard1,
5162                                             boardcard2,
5163                                             boardcard3,
5164                                             boardcard4,
5165                                             boardcard5,
5166                                             runItTwice,
5167                                             playersAtStreet1,
5168                                             playersAtStreet2,
5169                                             playersAtStreet3,
5170                                             playersAtStreet4,
5171                                             playersAtShowdown,
5172                                             street0Raises,
5173                                             street1Raises,
5174                                             street2Raises,
5175                                             street3Raises,
5176                                             street4Raises,
5177                                             street1Pot,
5178                                             street2Pot,
5179                                             street3Pot,
5180                                             street4Pot,
5181                                             showdownPot
5182                                              )
5183                                              values
5184                                               (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
5185                                                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
5186                                                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
5187
5188
5189         self.query['store_hands_players'] = """insert into HandsPlayers (
5190                 handId,
5191                 playerId,
5192                 startCash,
5193                 seatNo,
5194                 sitout,
5195                 card1,
5196                 card2,
5197                 card3,
5198                 card4,
5199                 card5,
5200                 card6,
5201                 card7,
5202                 card8,
5203                 card9,
5204                 card10,
5205                 card11,
5206                 card12,
5207                 card13,
5208                 card14,
5209                 card15,
5210                 card16,
5211                 card17,
5212                 card18,
5213                 card19,
5214                 card20,
5215                 winnings,
5216                 rake,
5217                 totalProfit,
5218                 street0VPI,
5219                 street1Seen,
5220                 street2Seen,
5221                 street3Seen,
5222                 street4Seen,
5223                 sawShowdown,
5224                 showed,
5225                 wonAtSD,
5226                 street0Aggr,
5227                 street1Aggr,
5228                 street2Aggr,
5229                 street3Aggr,
5230                 street4Aggr,
5231                 street1CBChance,
5232                 street2CBChance,
5233                 street3CBChance,
5234                 street4CBChance,
5235                 street1CBDone,
5236                 street2CBDone,
5237                 street3CBDone,
5238                 street4CBDone,
5239                 wonWhenSeenStreet1,
5240                 wonWhenSeenStreet2,
5241                 wonWhenSeenStreet3,
5242                 wonWhenSeenStreet4,
5243                 street0Calls,
5244                 street1Calls,
5245                 street2Calls,
5246                 street3Calls,
5247                 street4Calls,
5248                 street0Bets,
5249                 street1Bets,
5250                 street2Bets,
5251                 street3Bets,
5252                 street4Bets,
5253                 position,
5254                 tourneysPlayersId,
5255                 startCards,
5256                 street0_3BChance,
5257                 street0_3BDone,
5258                 street0_4BChance,
5259                 street0_4BDone,
5260                 street0_C4BChance,
5261                 street0_C4BDone,
5262                 street0_FoldTo3BChance,
5263                 street0_FoldTo3BDone,
5264                 street0_FoldTo4BChance,
5265                 street0_FoldTo4BDone,
5266                 street0_SqueezeChance,
5267                 street0_SqueezeDone,
5268                 raiseToStealChance,
5269                 raiseToStealDone,
5270                 success_Steal,
5271                 otherRaisedStreet0,
5272                 otherRaisedStreet1,
5273                 otherRaisedStreet2,
5274                 otherRaisedStreet3,
5275                 otherRaisedStreet4,
5276                 foldToOtherRaisedStreet0,
5277                 foldToOtherRaisedStreet1,
5278                 foldToOtherRaisedStreet2,
5279                 foldToOtherRaisedStreet3,
5280                 foldToOtherRaisedStreet4,
5281                 raiseFirstInChance,
5282                 raisedFirstIn,
5283                 foldBbToStealChance,
5284                 foldedBbToSteal,
5285                 foldSbToStealChance,
5286                 foldedSbToSteal,
5287                 foldToStreet1CBChance,
5288                 foldToStreet1CBDone,
5289                 foldToStreet2CBChance,
5290                 foldToStreet2CBDone,
5291                 foldToStreet3CBChance,
5292                 foldToStreet3CBDone,
5293                 foldToStreet4CBChance,
5294                 foldToStreet4CBDone,
5295                 street1CheckCallRaiseChance,
5296                 street1CheckCallRaiseDone,
5297                 street2CheckCallRaiseChance,
5298                 street2CheckCallRaiseDone,
5299                 street3CheckCallRaiseChance,
5300                 street3CheckCallRaiseDone,
5301                 street4CheckCallRaiseChance,
5302                 street4CheckCallRaiseDone,
5303                 street0Raises,
5304                 street1Raises,
5305                 street2Raises,
5306                 street3Raises,
5307                 street4Raises
5308                )
5309                values (
5310                     %s, %s, %s, %s, %s,
5311                     %s, %s, %s, %s, %s,
5312                     %s, %s, %s, %s, %s,
5313                     %s, %s, %s, %s, %s,
5314                     %s, %s, %s, %s, %s,
5315                     %s, %s, %s, %s, %s,
5316                     %s, %s, %s, %s, %s,
5317                     %s, %s, %s, %s, %s,
5318                     %s, %s, %s, %s, %s,
5319                     %s, %s, %s, %s, %s,
5320                     %s, %s, %s, %s, %s,
5321                     %s, %s, %s, %s, %s,
5322                     %s, %s, %s, %s, %s,
5323                     %s, %s, %s, %s, %s,
5324                     %s, %s, %s, %s, %s,
5325                     %s, %s, %s, %s, %s,
5326                     %s, %s, %s, %s, %s,
5327                     %s, %s, %s, %s, %s,
5328                     %s, %s, %s, %s, %s,
5329                     %s, %s, %s, %s, %s,
5330                     %s, %s, %s, %s, %s,
5331                     %s, %s, %s, %s, %s,
5332                     %s, %s, %s, %s, %s,
5333                     %s, %s, %s
5334                 )"""
5335
5336         self.query['store_hands_actions'] = """insert into HandsActions (
5337                         handId,
5338                         playerId,
5339                         street,
5340                         actionNo,
5341                         streetActionNo,
5342                         actionId,
5343                         amount,
5344                         raiseTo,
5345                         amountCalled,
5346                         numDiscarded,
5347                         cardsDiscarded,
5348                         allIn
5349                )
5350                values (
5351                     %s, %s, %s, %s, %s,
5352                     %s, %s, %s, %s, %s,
5353                     %s, %s
5354                 )"""
5355
5356         self.query['store_boards'] = """insert into Boards (
5357                         handId,
5358                         boardId,
5359                         boardcard1,
5360                         boardcard2,
5361                         boardcard3,
5362                         boardcard4,
5363                         boardcard5
5364                )
5365                values (
5366                     %s, %s, %s, %s, %s,
5367                     %s, %s
5368                 )"""
5369
5370         ################################
5371         # queries for Files Table
5372         ################################
5373
5374         self.query['store_file'] = """  insert into Files (
5375                         file,
5376                         site,
5377                         startTime,
5378                         lastUpdate,
5379                         hands,
5380                         stored,
5381                         dups,
5382                         partial,
5383                         errs,
5384                         ttime100,
5385                         finished)
5386                values (
5387                     %s, %s, %s, %s, %s,
5388                     %s, %s, %s, %s, %s,
5389                     %s
5390                 )"""
5391
5392         self.query['update_file'] = """
5393                     UPDATE Files SET
5394                     type=%s,
5395                     lastUpdate=%s,
5396                     endTime=%s,
5397                     hands=hands+%s,
5398                     stored=stored+%s,
5399                     dups=dups+%s,
5400                     partial=partial+%s,
5401                     errs=errs+%s,
5402                     ttime100=ttime100+%s,
5403                     finished=%s
5404                     WHERE id=%s"""
5405
5406         ################################
5407         # Counts for DB stats window
5408         ################################
5409         self.query['getHandCount'] = "SELECT COUNT(*) FROM Hands"
5410         self.query['getTourneyCount'] = "SELECT COUNT(*) FROM Tourneys"
5411         self.query['getTourneyTypeCount'] = "SELECT COUNT(*) FROM TourneyTypes"
5412
5413         ################################
5414         # queries for dumpDatabase
5415         ################################
5416         for table in (u'Autorates', u'Backings', u'Gametypes', u'Hands', u'HandsActions', u'HandsPlayers', u'HudCache', u'Players', u'RawHands', u'RawTourneys', u'Settings', u'Sites', u'TourneyTypes', u'Tourneys', u'TourneysPlayers'):
5417             self.query['get'+table] = u"SELECT * FROM "+table
5418
5419         ################################
5420         # placeholders and substitution stuff
5421         ################################
5422         if db_server == 'mysql':
5423             self.query['placeholder'] = u'%s'
5424         elif db_server == 'postgresql':
5425             self.query['placeholder'] = u'%s'
5426         elif db_server == 'sqlite':
5427             self.query['placeholder'] = u'?'
5428
5429
5430         # If using sqlite, use the ? placeholder instead of %s
5431         if db_server == 'sqlite':
5432             for k,q in self.query.iteritems():
5433                 self.query[k] = re.sub('%s','?',q)
5434
5435 if __name__== "__main__":
5436 #    just print the default queries and exit
5437     s = Sql()
5438     for key in s.query:
5439         print "For query " + key + ", sql ="
5440         print s.query[key]