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