pyfpdb/SQL.py

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