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