def getOrphans(self, reference, column): orphans = set() dbconfig.getCursor().execute('SELECT ' + column + ' FROM ' + self.name + ';') for record in dbconfig.getCursor(): if not record[0] in reference: orphans.add(record[0]) return orphans
def dump(self): if self.copy: output = open(os.path.join('db', self.name + '.csv'), 'w', encoding='utf8') # Order by all columns, in order orderByClause = '' for col in self.cols: if col.datatype.startswith('JSON'): continue if len(orderByClause) > 0: orderByClause += ', ' orderByClause += col.name dbconfig.getCursor().copy_expert('COPY (SELECT * FROM ' + self.name + ' ORDER BY ' + orderByClause + ') TO STDOUT;', output) output.close()
def insert(self, values, returning=""): if self.dropBeforeUpdate: self.create() if len(returning) > 0: returning = " RETURNING " + returning # Don't use len(values) for %s to make sure the number of colums is the same colsList, _ = self.getCols(False) query = 'INSERT INTO ' + self.name + '(' + colsList + ') VALUES (' + self.getPercentS() + ') ON CONFLICT ' + self.getConflit(onUpdate=False) + returning + ';' for val in values: dbconfig.getCursor().execute(query, val) if len(returning) > 0: return dbconfig.getCursor().fetchone()[0]
def update(self): # Don't update static data Tables if self.dontUpdate: return if self.dropBeforeUpdate: self.create() # Copy from an existing CSV file dbconfig.getCursor().execute('CREATE TEMP TABLE tmp_table (LIKE ' + self.name + ');') input = open(os.path.join('db', self.name + '.csv'), 'r', encoding='utf8') dbconfig.getCursor().copy_from(input, 'tmp_table') dbconfig.getCursor().execute('INSERT INTO ' + self.name + ' SELECT * FROM tmp_table ON CONFLICT ' + self.getConflit(onUpdate=True) + ';') dbconfig.getCursor().execute('DROP TABLE tmp_table;')
def main(argv): global previews_path, server if len(argv) < 1: print('One argument needed: absolute path to previews root directory', previews_path) return 1 if not os.path.abspath(argv[0]): print('Path is not absolute', previews_path) return 2 previews_path = os.path.join(argv[0], 'party') # Make sure the directory exists if not os.path.exists(previews_path) or not os.path.isdir(previews_path): print('Directory doesn\'t exist', previews_path) return 3 if len(argv) == 2 and argv[1] == '--all': dbconfig.getCursor().execute('SELECT partyid FROM party') result = dbconfig.getCursor().fetchall() print('Processing', len(result), 'parties') for party in result: processRequest(party[0]) else: # Set non-blocking, read-only, SQL connection dbconfig.setAutoCommit() # Start the processing queue in a single thread queue_thread = threading.Thread(target=processQueue) queue_thread.daemon = True queue_thread.start() # The server uses other threads server = PartyPreviewServer() server.server_forever()
def drop(self): if self.dropBeforeUpdate: # Table must exist (else, we forget to create the tables...) dbconfig.getCursor().execute('DROP TABLE ' + self.name + ';')
def create(self): colsListStr, primaryCols = self.getCols(True) constraint = "" if len(self.constraint) > 0: constraint = ", " + self.constraint q = 'CREATE TABLE IF NOT EXISTS ' + self.name + ' (' + colsListStr + ', PRIMARY KEY (' + primaryCols + ') ' + constraint + ');' dbconfig.getCursor().execute(q) # Add missing columns dbconfig.getCursor().execute('SELECT * FROM ' + self.name + ' LIMIT 0;') colNames = [desc[0] for desc in dbconfig.getCursor().description] for c in self.cols: if not c.name in colNames: dbconfig.getCursor().execute('ALTER TABLE ' + self.name + ' ADD COLUMN ' + c.name + ' ' + c.datatype + ';') print("Adding column " + c.name + " to table " + self.name) if self.constArray: # Static data, create from the array colsListStr, _ = self.getCols(False) for idx, val in enumerate(self.constArray): dbconfig.getCursor().execute('INSERT INTO ' + self.name + '(' + colsListStr + ') VALUES (%s, %s) ON CONFLICT DO NOTHING;', (idx, val)) elif self.constDict: colsListStr, _ = self.getCols(False) for val, idx in self.constDict.items(): dbconfig.getCursor().execute('INSERT INTO ' + self.name + '(' + colsListStr + ') VALUES (%s, %s) ON CONFLICT DO NOTHING;', (idx, val))
def getCount(self): dbconfig.getCursor().execute('SELECT COUNT(*) FROM ' + self.name + ';') return dbconfig.getCursor().fetchone()[0]
def removeOrphans(self, orphans, column): for orphan in orphans: dbconfig.getCursor().execute('DELETE FROM ' + self.name + ' WHERE ' + column + '= %s;', [orphan])
def updateValue(self, primaryKey, keyId, rowToUpdate, value): dbconfig.getCursor().execute('UPDATE ' + self.name + ' SET ' + rowToUpdate + '= %s WHERE ' + primaryKey + '= %s;', [value, keyId])
def fetchParty(party_id): # fetch party details dbconfig.getCursor().execute( 'SELECT partydata, EXTRACT(EPOCH FROM updated)::bigint FROM party WHERE partyid = %s', [party_id]) result = dbconfig.getCursor().fetchone() party_json = None updated = None if result != None: party_json = result[0] updated = result[1] if updated == None: updated = 0 query = 'SELECT ' values = [] # Characters if 'characters' in party_json and countItemsInCategory( party_json, 'characters') > 0: query += '(SELECT array_agg(characters.nameen) FROM ( ' sub_query = '' for character in party_json['characters']: if sub_query: sub_query += ' UNION ALL ' if character: sub_query += '(SELECT nameen FROM character WHERE characterid = %s)' values.append(character) else: sub_query += '(SELECT NULL AS nameen)' query += sub_query + ') AS characters) AS characters, ' else: query += 'NULL, ' # Summons if 'summons' in party_json and countItemsInCategory( party_json, 'summons') > 0: query += '(SELECT array_agg(summons.nameen) FROM ( ' sub_query = '' for summon in party_json['summons']: if sub_query: sub_query += ' UNION ALL ' if summon: sub_query += '(SELECT nameen FROM summon WHERE summonid = %s)' values.append(summon) else: sub_query += '(SELECT NULL AS nameen)' query += sub_query + ') AS summons) AS summons, ' else: query += 'NULL, ' # Weapons if 'weapons' in party_json and countItemsInCategory( party_json, 'weapons') > 0: query += '(SELECT array_agg(weapons.nameen) FROM ( ' sub_query = '' for weapon in party_json['weapons']: if sub_query: sub_query += ' UNION ALL ' if weapon: sub_query += '(SELECT nameen FROM weapon WHERE weaponid = %s)' values.append(weapon) else: sub_query += '(SELECT NULL AS nameen)' query += sub_query + ') AS weapons) AS weapons, ' else: query += 'NULL, ' # Class if 'classe' in party_json: query += 'class.nameen AS class FROM class WHERE class.classid = %s' values.append(party_json['classe']) else: query += 'NULL' dbconfig.getCursor().execute(query, values) result = dbconfig.getCursor().fetchone() return result, party_json, updated