File indexing completed on 2024-04-21 07:49:03
0001 # -*- coding: utf-8 -*- 0002 0003 """ 0004 Copyright (C) 2008-2016 Wolfgang Rohdewald <wolfgang@rohdewald.de> 0005 0006 partially based on C++ code from: 0007 - Copyright (C) 2006 Mauricio Piacentini <mauricio@tabuleiro.com> 0008 0009 SPDX-License-Identifier: GPL-2.0 0010 0011 """ 0012 0013 import os 0014 import traceback 0015 import time 0016 import datetime 0017 import random 0018 from collections import defaultdict 0019 import sqlite3 0020 0021 from mi18n import i18n, i18ncE 0022 from util import Duration 0023 from log import logInfo, logWarning, logException, logError, logDebug, id4 0024 from common import IntDict, Options, Internal, Debug, appdataDir 0025 0026 class QueryException(Exception): 0027 0028 """as the name says""" 0029 0030 def __init__(self, msg): 0031 Exception.__init__(self, msg) 0032 0033 0034 class DBCursor(sqlite3.Cursor): 0035 0036 """logging wrapper""" 0037 0038 # pylint: disable=no-member 0039 0040 def __init__(self, dbHandle): 0041 sqlite3.Cursor.__init__(self, dbHandle) 0042 self.statement = None 0043 self.parameters = None 0044 self.failure = None 0045 0046 def execute(self, statement, parameters=None, 0047 silent=False, failSilent=False, mayFail=False): 0048 """logging wrapper, returning all selected data""" 0049 # pylint: disable=too-many-branches 0050 self.statement = statement 0051 self.parameters = parameters 0052 if not silent: 0053 logDebug(str(self)) 0054 try: 0055 for _ in range(10): 0056 try: 0057 with Duration(statement, 60.0 if Debug.neutral else 2.0): 0058 if isinstance(parameters, list): 0059 sqlite3.Cursor.executemany( 0060 self, statement, parameters) 0061 elif parameters: 0062 sqlite3.Cursor.execute(self, statement, parameters) 0063 else: 0064 sqlite3.Cursor.execute(self, statement) 0065 break 0066 except sqlite3.OperationalError as exc: 0067 logDebug( 0068 '{} failed after {} tries:{}'.format(self, _, ' '.join(exc.args))) 0069 time.sleep(1) 0070 else: 0071 break 0072 else: 0073 raise sqlite3.OperationalError( 0074 'Failed after 10 tries:{}'.format(self)) 0075 self.failure = None 0076 except sqlite3.Error as exc: 0077 self.failure = exc 0078 msg = 'ERROR in %s: %s for %s' % ( 0079 self.connection.path, 0080 exc.message if hasattr(exc, 'message') else '', 0081 self) 0082 if mayFail: 0083 if not failSilent: 0084 logDebug(msg) 0085 else: 0086 if not failSilent: 0087 logError(msg) 0088 raise QueryException(msg) from exc 0089 return 0090 0091 def __str__(self): 0092 """the statement""" 0093 if self.parameters is not None: 0094 return "{cmd} [{args}]".format(cmd=self.statement, args=self.parameters) 0095 return self.statement 0096 0097 0098 class DBHandle(sqlite3.Connection): 0099 0100 """a handle with our preferred configuration""" 0101 0102 # pylint: disable=no-member 0103 0104 def __init__(self, path: str): 0105 assert Internal.db is None, id(self) 0106 Internal.db = self 0107 self.inTransaction = None 0108 self.path = path 0109 self.identifier = None 0110 try: 0111 sqlite3.Connection.__init__(self, self.path, timeout=10.0) 0112 except sqlite3.Error as exc: 0113 if hasattr(exc, 'message'): 0114 msg = exc.message 0115 elif hasattr(exc, 'args'): 0116 msg = ' '.join(exc.args) 0117 else: 0118 msg = '' 0119 logException('opening %s: %s' % (self.path, msg)) 0120 if self.hasTable('general'): 0121 cursor = self.cursor() 0122 cursor.execute('select ident from general') 0123 self.identifier = cursor.fetchone()[0] 0124 if Debug.sql: 0125 logDebug('Opened %s with identifier %s' % ( 0126 self.path, self.identifier)) 0127 0128 def __enter__(self): 0129 self.inTransaction = datetime.datetime.now() 0130 if Debug.sql: 0131 logDebug('starting transaction') 0132 return sqlite3.Connection.__enter__(self) 0133 0134 def __exit__(self, *args): 0135 self.inTransaction = None 0136 sqlite3.Connection.__exit__(self, *args) 0137 if Debug.sql: 0138 logDebug('finished transaction') 0139 0140 @staticmethod 0141 def dbPath(): 0142 """ 0143 The path for the data base. 0144 0145 @return: The full path for kajonggserver.db or kajongg.db. 0146 @rtype: C{str} 0147 """ 0148 name = 'kajonggserver' if Internal.isServer else 'kajongg' 0149 name += '3.db' 0150 return Options.dbPath if Options.dbPath else os.path.join(appdataDir(), name) 0151 0152 @property 0153 def name(self): 0154 """get name for log messages. Readonly.""" 0155 stack = [x[2] for x in traceback.extract_stack()] 0156 name = stack[-3] 0157 if name in ('__exit__', '__init__'): 0158 name = stack[-4] 0159 return '%s on %s (%x)' % (name, self.path, id4(self)) 0160 0161 def commit(self, silent=None): 0162 """commit and log it""" 0163 try: 0164 sqlite3.Connection.commit(self) 0165 except sqlite3.Error as exc: 0166 if not silent: 0167 logWarning( 0168 '%s cannot commit: %s :' % 0169 (self.name, exc.message)) 0170 0171 def rollback(self, silent=None): 0172 """rollback and log it""" 0173 try: 0174 sqlite3.Connection.rollback(self) 0175 if not silent and Debug.sql: 0176 logDebug('%x rollbacked transaction' % id(self)) 0177 except sqlite3.Error as exc: 0178 logWarning('%s cannot rollback: %s' % (self.name, exc.message)) 0179 0180 def close(self, silent=False): 0181 """just for logging""" 0182 if not silent and (Debug.sql or Debug.quit): 0183 if self is Internal.db: 0184 logDebug('Closing Internal.db: %s' % self.path) 0185 else: 0186 logDebug('Closing DBHandle %s: %s' % (self, self.path)) 0187 if self is Internal.db: 0188 Internal.db = None 0189 try: 0190 self.commit(silent=True) 0191 except sqlite3.Error: 0192 self.rollback() 0193 try: 0194 sqlite3.Connection.close(self) 0195 except sqlite3.Error as exc: 0196 logDebug(exc) 0197 0198 @staticmethod 0199 def hasTable(table): 0200 """does the table contain table?""" 0201 return len(Query('SELECT name FROM sqlite_master WHERE type="table" AND name="%s"' % table).records) > 0 0202 0203 def tableHasField(self, table, field): 0204 """does the table contain a column named field?""" 0205 cursor = self.cursor() 0206 cursor.execute('select * from %s' % table) 0207 return any(x[0] == field for x in cursor.description) 0208 0209 0210 class Query: 0211 0212 """a wrapper arout python sqlite3, adding logging and some exception handling. 0213 For selecting queries we fill a list with ALL records which is never much. 0214 Every record is a list of all fields. q.records[0][1] is record 0, field 1. 0215 """ 0216 0217 localServerName = i18ncE( 0218 'kajongg name for local game server', 0219 'Local Game') 0220 0221 def __init__(self, statement, args=None, 0222 silent=False, mayFail=False, failSilent=False): 0223 """we take one sql statement. 0224 Do prepared queries by passing the parameters in args. 0225 If args is a list of lists, execute the prepared query for every sublist. 0226 Use Internal.db for db access. 0227 Else if the default dbHandle (Internal.db) is defined, use it.""" 0228 # pylint: disable=too-many-branches 0229 silent |= not Debug.sql 0230 self.msg = None 0231 self.records = [] 0232 self.statement = statement 0233 self.args = args 0234 if Internal.db: 0235 self.cursor = Internal.db.cursor( 0236 DBCursor) # pylint: disable=no-member 0237 self.cursor.execute( 0238 statement, 0239 args, 0240 silent=silent, 0241 mayFail=mayFail, 0242 failSilent=failSilent) 0243 self.failure = self.cursor.failure 0244 self.records = list(self.cursor.fetchall()) 0245 if not Internal.db.inTransaction: 0246 Internal.db.commit() 0247 else: 0248 # may happen at shutdown 0249 self.cursor = None 0250 self.failure = None 0251 self.records = list() 0252 if self.records and Debug.sql: 0253 logDebug('result set:{}'.format(self.records)) 0254 0255 def __str__(self): 0256 return '{} {}'.format(self.statement, 0257 'args=' + ','.join(str(x) for x in self.args) if self.args else '') 0258 0259 def rowcount(self): 0260 """how many rows were affected?""" 0261 return self.cursor.rowcount if self.cursor else 0 0262 0263 0264 def initDb(): 0265 """open the db, create or update it if needed. 0266 sets Internal.db.""" 0267 PrepareDB(DBHandle.dbPath()) # create or upgrade 0268 DBHandle(DBHandle.dbPath()) 0269 # if not Internal.db = DBHandle.default: # had to create it. Close and reopen 0270 # Internal.db = DBHandle() 0271 # assert Internal.db = DBHandle.default 0272 # except sqlite3.Error as exc: 0273 # logException(exc) 0274 # return False 0275 return True 0276 0277 0278 class PrepareDB: 0279 0280 """create or upgrade DB if needed""" 0281 schema = {} 0282 schema['player'] = """ 0283 id INTEGER PRIMARY KEY, 0284 name TEXT unique""" 0285 schema['game'] = """ 0286 id integer primary key, 0287 seed text, 0288 autoplay integer default 0, 0289 starttime text default current_timestamp, 0290 endtime text, 0291 ruleset integer references ruleset(id), 0292 p0 integer constraint fk_p0 references player(id), 0293 p1 integer constraint fk_p1 references player(id), 0294 p2 integer constraint fk_p2 references player(id), 0295 p3 integer constraint fk_p3 references player(id)""" 0296 schema['score'] = """ 0297 game integer constraint fk_game references game(id), 0298 hand integer, 0299 data text, 0300 manualrules text, 0301 rotated integer, 0302 notrotated integer, 0303 player integer constraint fk_player references player(id), 0304 scoretime text, 0305 won integer, 0306 penalty integer default 0, 0307 prevailing text, 0308 wind text, 0309 points integer, 0310 payments integer, 0311 balance integer""" 0312 schema['ruleset'] = """ 0313 id integer primary key, 0314 name text, 0315 hash text, 0316 description text""" 0317 schema['rule'] = """ 0318 ruleset integer, 0319 list integer, 0320 position integer, 0321 name text, 0322 definition text, 0323 points text, 0324 doubles text, 0325 limits text, 0326 parameter text, 0327 primary key(ruleset,list,position)""" 0328 schema['server'] = """ 0329 url text, 0330 lastname text, 0331 lasttime text, 0332 lastruleset integer, 0333 primary key(url)""" 0334 schema['passwords'] = """ 0335 url text, 0336 player integer, 0337 password text""" 0338 schema['general'] = """ 0339 ident text, 0340 schemaversion text""" 0341 0342 def __init__(self, path): 0343 self.path = path 0344 if not os.path.exists(path): 0345 self.__create() 0346 else: 0347 self.__upgrade() 0348 0349 def __create(self): 0350 """create a brand new kajongg database""" 0351 tmpPath = '%s.new.%d' % (self.path, os.getpid()) 0352 Internal.db = DBHandle(tmpPath) 0353 try: 0354 with Internal.db: 0355 self.createTables() 0356 self.__generateDbIdent() 0357 Query( 0358 'UPDATE general SET schemaversion=?', (Internal.defaultPort,)) 0359 finally: 0360 Internal.db.close(silent=True) 0361 if os.path.exists(self.path): 0362 # somebody was faster 0363 os.remove(tmpPath) 0364 else: 0365 os.rename(tmpPath, self.path) 0366 0367 @staticmethod 0368 def __currentVersion(): 0369 """ 0370 Get current version of DB schema as a comparable string. 0371 0372 @returns: The current version from the database. 0373 @rtype: C{str} 0374 """ 0375 if Internal.db.tableHasField('general', 'schemaversion'): 0376 return Query('select schemaversion from general').records[0][0] 0377 return '1.1.1' 0378 0379 def __upgrade(self): 0380 """upgrade the structure of an existing kajongg database""" 0381 try: 0382 Internal.db = DBHandle(self.path) 0383 allVersions = list(['4.13.0', '8300', '8301']) 0384 assert allVersions[-1] == str(Internal.defaultPort), '{} != {}'.format( 0385 allVersions[-1], str(Internal.defaultPort)) 0386 # skip versions before current db versions: 0387 currentVersion = self.__currentVersion() 0388 while allVersions and allVersions[0] <= currentVersion: 0389 allVersions = allVersions[1:] 0390 for version in allVersions: 0391 currentVersion = self.__currentVersion() 0392 with Internal.db: # transaction 0393 updateMethodName = 'updateToVersion{}'.format(version.replace('.', '_')) 0394 if hasattr(self, updateMethodName): 0395 getattr(self, updateMethodName)() 0396 Query('UPDATE general SET schemaversion=?', (version,)) 0397 logInfo(i18n('Database %1 updated from schema %2 to %3', 0398 Internal.db.path, currentVersion, version), showDialog=True) 0399 except sqlite3.Error as exc: 0400 logException('opening %s: %s' % (self.path, exc.message)) 0401 finally: 0402 Internal.db.close(silent=True) 0403 0404 def updateToVersion4_13_0(self): 0405 """as the name says""" 0406 self.__upgradeFromLegacy() 0407 # add general.schemaversion 0408 Query('ALTER TABLE general add schemaversion text') 0409 Query('UPDATE general set schemaversion="001.001.001"') 0410 # this makes finding suspended games much faster in the presence 0411 # of many test games (with autoplay=1) 0412 self.createIndex('idxautoplay', 'game(autoplay)') 0413 0414 @classmethod 0415 def sqlForCreateTable(cls, table): 0416 """the SQL command for creating 'table'""" 0417 return "create table %s(%s)" % (table, cls.schema[table]) 0418 0419 @classmethod 0420 def createTable(cls, table): 0421 """create a single table using the predefined schema""" 0422 if not Internal.db.hasTable(table): 0423 Query(cls.sqlForCreateTable(table)) 0424 0425 @classmethod 0426 def createTables(cls): 0427 """creates empty tables""" 0428 for table in ['player', 'game', 'score', 'ruleset', 'rule', 'general']: 0429 cls.createTable(table) 0430 cls.createIndex('idxgame', 'score(game)') 0431 # this makes finding suspended games much faster in the presence 0432 # of many test games (with autoplay=1) 0433 cls.createIndex('idxautoplay', 'game(autoplay)') 0434 0435 if Internal.isServer: 0436 Query('ALTER TABLE player add password text') 0437 else: 0438 cls.createTable('passwords') 0439 cls.createTable('server') 0440 0441 @staticmethod 0442 def createIndex(name, cmd): 0443 """only try to create it if it does not yet exist. Do not use create if not exists because 0444 we want debug output only if we really create the index""" 0445 if not Query( 0446 "select 1 from sqlite_master where type='index' and name=?", ( 0447 name,), 0448 silent=True).records: 0449 Query("create index %s on %s" % (name, cmd)) 0450 0451 def cleanPlayerTable(self): 0452 """remove now unneeded columns host, password and make names unique""" 0453 playerCounts = IntDict() 0454 names = {} 0455 keep = {} 0456 for nameId, name in Query('select id,name from player').records: 0457 playerCounts[name] += 1 0458 names[int(nameId)] = name 0459 for name, counter in defaultdict.items(playerCounts): 0460 nameIds = [x[0] for x in names.items() if x[1] == name] 0461 keepId = nameIds[0] 0462 keep[keepId] = name 0463 if counter > 1: 0464 for nameId in nameIds[1:]: 0465 Query( 0466 'update score set player=%d where player=%d' % 0467 (keepId, nameId)) 0468 Query( 0469 'update game set p0=%d where p0=%d' % 0470 (keepId, nameId)) 0471 Query( 0472 'update game set p1=%d where p1=%d' % 0473 (keepId, nameId)) 0474 Query( 0475 'update game set p2=%d where p2=%d' % 0476 (keepId, nameId)) 0477 Query( 0478 'update game set p3=%d where p3=%d' % 0479 (keepId, nameId)) 0480 Query('delete from player where id=%d' % nameId) 0481 Query('drop table player') 0482 self.createTable('player') 0483 for nameId, name in keep.items(): 0484 Query('insert into player(id,name) values(?,?)', (nameId, name)) 0485 0486 @classmethod 0487 def removeGameServer(cls): 0488 """drops column server from table game. Sqlite3 cannot drop columns""" 0489 Query('create table gameback(%s)' % cls.schema['game']) 0490 Query('insert into gameback ' 0491 'select id,seed,autoplay,starttime,endtime,ruleset,p0,p1,p2,p3 from game') 0492 Query('drop table game') 0493 Query('create table game(%s)' % cls.schema['game']) 0494 Query('insert into game ' 0495 'select id,seed,autoplay,starttime,endtime,ruleset,p0,p1,p2,p3 from gameback') 0496 Query('drop table gameback') 0497 0498 def removeUsedRuleset(self): 0499 """eliminate usedruleset and usedrule""" 0500 if Internal.db.hasTable('usedruleset'): 0501 if Internal.db.hasTable('ruleset'): 0502 Query('UPDATE ruleset set id=-id where id>0') 0503 Query( 0504 'INSERT OR IGNORE INTO usedruleset SELECT * FROM ruleset') 0505 Query('DROP TABLE ruleset') 0506 Query('ALTER TABLE usedruleset RENAME TO ruleset') 0507 if Internal.db.hasTable('usedrule'): 0508 if Internal.db.hasTable('rule'): 0509 Query('UPDATE rule set ruleset=-ruleset where ruleset>0') 0510 Query('INSERT OR IGNORE INTO usedrule SELECT * FROM rule') 0511 Query('DROP TABLE rule') 0512 Query('ALTER TABLE usedrule RENAME TO rule') 0513 query = Query("select count(1) from sqlite_master " 0514 "where type='table' and tbl_name='ruleset' and sql like '%name text unique,%'", silent=True) 0515 if int(query.records[0][0]): 0516 # make name non-unique. Needed for used rulesets: Content may change with identical name 0517 # and we now have both ruleset templates and copies of used 0518 # rulesets in the same table 0519 for statement in list([ 0520 'create table temp(%s)' % self.schema['ruleset'], 0521 'insert into temp select id,name,hash,description from ruleset', 0522 'drop table ruleset', 0523 self.sqlForCreateTable('ruleset'), 0524 'insert into ruleset select * from temp', 0525 'drop table temp']): 0526 Query(statement) 0527 query = Query("select count(1) from sqlite_master " 0528 "where type='table' and tbl_name='rule' and sql like '%unique (ruleset,name)%'", silent=True) 0529 if int(query.records[0][0]): 0530 # make ruleset,name non-unique 0531 for statement in list([ 0532 'create table temp(%s)' % self.schema['rule'], 0533 'insert into temp select * from rule', 0534 'drop table rule', 0535 self.sqlForCreateTable('rule'), 0536 'insert into rule select * from temp', 0537 'drop table temp']): 0538 Query(statement) 0539 0540 def __upgradeFromLegacy(self): 0541 """upgrade versions prior to 4.13.0""" 0542 self.createIndex('idxgame', 'score(game)') 0543 if not Internal.db.tableHasField('game', 'autoplay'): 0544 Query('ALTER TABLE game add autoplay integer default 0') 0545 if not Internal.db.tableHasField('score', 'penalty'): 0546 Query('ALTER TABLE score add penalty integer default 0') 0547 Query("UPDATE score SET penalty=1 WHERE manualrules LIKE " 0548 "'False Naming%' OR manualrules LIKE 'False Decl%'") 0549 if Internal.db.tableHasField('player', 'host'): 0550 self.cleanPlayerTable() 0551 if Internal.isServer: 0552 if not Internal.db.tableHasField('player', 'password'): 0553 Query('ALTER TABLE player add password text') 0554 else: 0555 self.createTable('passwords') 0556 if not Internal.db.tableHasField('server', 'lastruleset'): 0557 Query('alter table server add lastruleset integer') 0558 if Internal.db.tableHasField('game', 'server'): 0559 self.removeGameServer() 0560 if not Internal.db.tableHasField('score', 'notrotated'): 0561 Query('ALTER TABLE score add notrotated integer default 0') 0562 self.removeUsedRuleset() 0563 0564 @staticmethod 0565 def __generateDbIdent(): 0566 """make sure the database has a unique ident and get it""" 0567 records = Query('select ident from general').records 0568 assert len(records) < 2 0569 if not records: 0570 dbIdent = str(random.randrange(100000000000)) 0571 Query("INSERT INTO general(ident) values(?)", (dbIdent,)) 0572 if Debug.sql: 0573 logDebug( 0574 'generated new dbIdent %s for %s' % 0575 (dbIdent, Internal.db.path))