def _purge_old_search_metadata_communities(self):
"""
Cleans up all SearchCommunity and MetadataCommunity stuff in dispersy database.
"""
db_path = os.path.join(self.session.get_state_dir(), u"sqlite", u"dispersy.db")
if not os.path.isfile(db_path):
return
communities_to_delete = (u"SearchCommunity", u"MetadataCommunity", u"TunnelCommunity")
connection = Connection(db_path)
cursor = connection.cursor()
for community in communities_to_delete:
try:
result = list(cursor.execute(u"SELECT id FROM community WHERE classification == ?;", (community,)))
for community_id, in result:
cursor.execute(u"DELETE FROM community WHERE id == ?;", (community_id,))
cursor.execute(u"DELETE FROM meta_message WHERE community == ?;", (community_id,))
cursor.execute(u"DELETE FROM sync WHERE community == ?;", (community_id,))
except StopIteration:
continue
cursor.close()
connection.commit()
connection.close()
def parse_and_import_cards(
input_file: pathlib.Path, sql_connection: sqlite3.Connection
) -> None:
"""
Parse the JSON cards and input them into the database
:param input_file: AllSets.json file
:param sql_connection: Database connection
"""
LOGGER.info("Loading JSON into memory")
json_data = json.load(input_file.open("r"))
LOGGER.info("Building sets")
for set_code, set_data in json_data.items():
# Handle set insertion
LOGGER.info("Inserting set row for {}".format(set_code))
set_insert_values = handle_set_row_insertion(set_data)
sql_dict_insert(set_insert_values, "sets", sql_connection)
for card in set_data.get("cards"):
LOGGER.debug("Inserting card row for {}".format(card.get("name")))
card_attr: Dict[str, Any] = handle_card_row_insertion(card, set_code)
sql_insert_all_card_fields(card_attr, sql_connection)
for token in set_data.get("tokens"):
LOGGER.debug("Inserting token row for {}".format(token.get("name")))
token_attr = handle_token_row_insertion(token, set_code)
sql_dict_insert(token_attr, "tokens", sql_connection)
sql_connection.commit()
def _update_dispersy(self):
"""
Cleans up all SearchCommunity and MetadataCommunity stuff in dispersy database.
"""
db_path = os.path.join(self.session.get_state_dir(), u"sqlite", u"dispersy.db")
if not os.path.isfile(db_path):
return
communities_to_delete = (u"SearchCommunity", u"MetadataCommunity")
connection = Connection(db_path)
cursor = connection.cursor()
data_updated = False
for community in communities_to_delete:
try:
result = list(cursor.execute(u"SELECT id FROM community WHERE classification == ?", (community,)))
for community_id, in result:
self._logger.info(u"deleting all data for community %s...", community_id)
cursor.execute(u"DELETE FROM community WHERE id == ?", (community_id,))
cursor.execute(u"DELETE FROM meta_message WHERE community == ?", (community_id,))
cursor.execute(u"DELETE FROM sync WHERE community == ?", (community_id,))
data_updated = True
except StopIteration:
continue
if data_updated:
connection.commit()
cursor.close()
connection.close()
class Graph(object):
"""
Initializes a new Graph object.
:param uri: The URI of the SQLite db.
:param graphs: Graphs to create.
"""
def __init__(self, uri, graphs=()):
self.uri = uri
self.db = Connection(database=uri)
self.setup_sql(graphs)
def setup_sql(self, graphs):
"""
Sets up the SQL tables for the graph object,
and creates indexes as well.
:param graphs: The graphs to create.
"""
with closing(self.db.cursor()) as cursor:
for table in graphs:
cursor.execute(SQL.CREATE_TABLE % (table))
for index in SQL.INDEXES:
cursor.execute(index % (table))
self.db.commit()
def close(self):
"""
Close the SQLite connection.
"""
self.db.close()
__del__ = close
def __contains__(self, edge):
"""
Checks if an edge exists within the database
with the given source and destination nodes.
:param edge: The edge to query.
"""
with closing(self.db.cursor()) as cursor:
cursor.execute(*SQL.select_one(edge.src, edge.rel, edge.dst))
return bool(cursor.fetchall())
def find(self, edge_query):
"""
Returns a Query object that acts on the graph.
"""
return Query(self.db)(edge_query)
def transaction(self):
"""
Returns a Transaction object. All modifying
operations, i.e. ``store``, ``delete`` must
then be performed on the transaction object.
"""
return Transaction(self.db)
def insert(db: sqlite3.Connection, t):
id = int(t['id_str'])
created_at = int(timestamp_from_id(id))
cur = db.cursor()
cur.execute(
"""
INSERT INTO fav_tweets (`id`, `tweet`, `created_at`)
VALUES (?, ?, ?)
ON CONFLICT (`id`) DO NOTHING
""",
(id, json.dumps(t), created_at)
)
db.commit()
cur.close()
def delete_friend(ds_connection: sqlite3.Connection, id: str) -> dict:
"""
Delete a given entry from the friends table in a given SQLite connection.
Args:
ds_connection (sqllite3.Connection): An active connection to a
sqllite datastore containing a friends table.
id (str): An `id` value which will be used to find a specific
datastore row to delete.
"""
cursor = ds_connection.execute("DELETE " "from friends where lower(id) = ?", [id.lower()])
if not cursor.rowcount:
raise ValueError()
ds_connection.commit()
def add_friend(ds_connection: sqlite3.Connection, entry_data: dict):
"""
Create a new row in the friends table.
Args:
ds_connection (sqllite3.Connection): An active connection to a
sqllite datastore containing a friends table.
entry_data (dict): The data needed to created a new entry.
"""
ds_connection.execute(
"insert into friends (id, first_name, last_name, telephone, email, notes) "
"values (?, ?, ?, ?, ?, ?)",
[entry_data['id'],
entry_data['firstName'],
entry_data['lastName'],
entry_data['telephone'],
entry_data['email'],
entry_data['notes']])
ds_connection.commit()
def fully_update_friend(ds_connection: sqlite3.Connection, entry_data: dict):
"""
Update all aspects of given row in the friends table.
Args:
ds_connection (sqllite3.Connection): An active connection to a
sqllite datastore containing a friends table.
entry_data (dict): The data needed to update a given entry. The
`id` value of this dictionary is used to identify the entry
to update.
"""
ds_connection.execute(
"UPDATE friends "
"SET id=?, first_name=?, last_name=?, telephone=?, email=?, notes=? "
"WHERE lower(id) = ?",
[entry_data['id'],
entry_data['firstName'],
entry_data['lastName'],
entry_data['telephone'],
entry_data['email'],
entry_data['notes'],
entry_data['id'].lower()])
ds_connection.commit()
def addBooks(
conn: sqlite3.Connection,
books: List[dict],
series: dict,
genres: dict,
authors: dict,
keywords: dict
):
def chunks(iterable, size=10):
# http://stackoverflow.com/a/24527424
iterator = iter(iterable)
for first in iterator:
yield chain([first], islice(iterator, size - 1))
chunkSize = 250000
print('Adding series...')
serNum = 0
serTotal = len(series)
seriesIter = ((num['serie_id'], name) for name, num in series.items())
for i in chunks(seriesIter, chunkSize):
startTime = time.time()
conn.executemany('INSERT INTO series_temp VALUES (?, ?)', i)
conn.commit()
endTime = time.time()
serNum += chunkSize
print('\t{n}/{t} ({s}) series added...'.format(
n=serNum,
t=serTotal,
s=(endTime - startTime) / chunkSize
))
print('Done.\n')
print('Adding genres...')
genreNum = 0
genreTotal = len(genres)
genresIter = ((num, name) for name, num in genres.items())
for i in chunks(genresIter, chunkSize):
startTime = time.time()
conn.executemany('INSERT INTO genres_temp VALUES (?, ?)', i)
conn.commit()
endTime = time.time()
genreNum += chunkSize
print('\t{n}/{t} ({s}) genres added...'.format(
n=genreNum,
t=genreTotal,
s=(endTime - startTime) / chunkSize
))
print('Done.\n')
print('Adding authors...')
authorNum = 0
authorTotal = len(authors)
authorsIter = ((num, *name) for name, num in authors.items())
for i in chunks(authorsIter, chunkSize):
startTime = time.time()
conn.executemany('INSERT INTO authors_temp(author_id, first_name, middle_name, last_name, nickname) VALUES (?, ?, ?, ?, ?)', i)
conn.commit()
endTime = time.time()
authorNum += chunkSize
print('\t{n}/{t} ({s}) authors added...'.format(
n=authorNum,
t=authorTotal,
s=(endTime - startTime) / chunkSize
))
print('Done.\n')
print('Adding keywords...')
keywordsNum = 0
keywordsTotal = len(keywords)
keywordsIter = ((num, name) for name, num in keywords.items())
for i in chunks(keywordsIter, chunkSize):
startTime = time.time()
conn.executemany('INSERT INTO keywords_temp VALUES (?, ?)', i)
conn.commit()
endTime = time.time()
keywordsNum += chunkSize
print('\t{n}/{t} ({s}) keywords added...'.format(
n=keywordsNum,
t=keywordsTotal,
s=(endTime - startTime) / chunkSize
))
print('Done.\n')
print('Adding books...')
bookNum = 0
bookTotal = len(books)
for i in chunks(books, chunkSize):
startTime = time.time()
conn.executemany('''
INSERT INTO books_temp VALUES (
:book_id,
:title,
:title_alt,
:size,
:file,
:add_date,
:lang,
:rate,
:deleted,
:joined_into
)
''', i)
conn.commit()
endTime = time.time()
bookNum += chunkSize
print('\t{n}/{t} ({s}) books added...'.format(
n=bookNum,
t=bookTotal,
s=(endTime - startTime) / chunkSize
))
print('Done.\n')
print('Filling author2book...')
a2bNum = 0
def authorToBookIter():
for b in books:
for a in b['authors']:
yield (b['book_id'], authors[a])
for i in chunks(authorToBookIter(), chunkSize):
startTime = time.time()
conn.executemany('INSERT INTO author_to_book_temp(book_id, author_id) VALUES (?, ?)', i)
conn.commit()
endTime = time.time()
a2bNum += chunkSize
print('\t{n} ({s}) added...'.format(n=a2bNum, s=(endTime - startTime) / chunkSize))
print('Done.\n')
print('Filling genre2book...')
g2bNum = 0
def genreToBookIter():
for b in books:
for g in b['genres']:
yield (b['book_id'], genres[g])
for i in chunks(genreToBookIter(), chunkSize):
startTime = time.time()
conn.executemany('INSERT INTO genre_to_book_temp(book_id, genre_id) VALUES (?, ?)', i)
conn.commit()
endTime = time.time()
g2bNum += chunkSize
print('\t{n} ({s}) added...'.format(n=g2bNum, s=(endTime - startTime) / chunkSize))
print('Done.\n')
print('Filling serie2book')
s2bnum = 0
def serieToBookIter():
for b in books:
for sername, serno in b['series'].items():
s = series[sername]
if s['zero'] and serno is not None and serno != -1:
serno += 1
yield (b['book_id'], s['serie_id'], serno)
for i in chunks(serieToBookIter(), chunkSize):
startTime = time.time()
conn.executemany('INSERT INTO serie_to_book_temp(book_id, serie_id, serno) VALUES (?, ?, ?)', i)
conn.commit()
endTime = time.time()
s2bnum += chunkSize
print('\t{n} ({s}) added...'.format(n=s2bnum, s=(endTime - startTime) / chunkSize))
print('Filling keyword2book...')
kw2bNum = 0
def keywordToBookIter():
for b in books:
for kw in b['keywords']:
yield (b['book_id'], keywords[kw])
for i in chunks(keywordToBookIter(), chunkSize):
startTime = time.time()
conn.executemany('INSERT INTO keyword_to_book_temp(book_id, keyword_id) VALUES (?, ?)', i)
conn.commit()
endTime = time.time()
kw2bNum += chunkSize
print('\t{n} ({s}) added...'.format(n=kw2bNum, s=(endTime - startTime) / chunkSize))
print('Done.\n')
print('Done all!')
def offer(self, connection: Connection) -> None:
connection.commit()
connection.close()
self.connection = None
pass
def initDb(conn: sqlite3.Connection):
conn.executescript('''
CREATE TABLE series (
serie_id INTEGER PRIMARY KEY,
name TEXT UNIQUE
);
CREATE TABLE serie_to_book (
book_id INTEGER,
serie_id INTEGER,
serno INTEGER,
PRIMARY KEY(book_id, serie_id),
FOREIGN KEY(book_id) REFERENCES books(book_id) ON DELETE CASCADE,
FOREIGN KEY(serie_id) REFERENCES series(serie_id) ON DELETE CASCADE
);
CREATE TABLE books (
book_id INTEGER PRIMARY KEY,
title TEXT,
title_alt TEXT,
size INTEGER,
file TEXT,
add_date DATE,
lang TEXT,
rate INTEGER,
keywords TEXT,
deleted INTEGER,
joined_into INTEGER,
authors TEXT,
series TEXT,
genres TEXT,
FOREIGN KEY(joined_into) REFERENCES books(book_id)
);
CREATE TABLE authors (
author_id INTEGER PRIMARY KEY,
first_name TEXT,
middle_name TEXT,
last_name TEXT,
nickname TEXT,
name TEXT
);
CREATE TABLE author_to_book (
book_id INTEGER,
author_id INTEGER,
PRIMARY KEY(book_id, author_id),
FOREIGN KEY(book_id) REFERENCES books(book_id) ON DELETE CASCADE,
FOREIGN KEY(author_id) REFERENCES authors(author_id) ON DELETE CASCADE
);
CREATE TABLE genres (
genre_id INTEGER PRIMARY KEY,
name TEXT UNIQUE
);
CREATE TABLE genre_to_book (
book_id INTEGER,
genre_id INTEGER,
PRIMARY KEY(book_id, genre_id),
FOREIGN KEY(book_id) REFERENCES books(book_id) ON DELETE CASCADE,
FOREIGN KEY(genre_id) REFERENCES genres(genre_id) ON DELETE CASCADE
);
CREATE TABLE keywords (
keyword_id INTEGER PRIMARY KEY,
name TEXT UNIQUE
);
CREATE TABLE keyword_to_book (
book_id INTEGER,
keyword_id INTEGER,
PRIMARY KEY(book_id, keyword_id),
FOREIGN KEY(book_id) REFERENCES books(book_id) ON DELETE CASCADE,
FOREIGN KEY(keyword_id) REFERENCES keywords(keyword_id) ON DELETE CASCADE
);
CREATE TABLE series_temp (
serie_id INTEGER,
name TEXT
);
CREATE TABLE serie_to_book_temp (
book_id INTEGER,
serie_id INTEGER,
serno INTEGER
);
CREATE TABLE books_temp (
book_id INTEGER,
title TEXT,
title_alt TEXT,
size INTEGER,
file TEXT,
add_date DATE,
lang TEXT,
rate INTEGER,
deleted INTEGER,
joined_into INTEGER
);
CREATE TABLE authors_temp (
author_id INTEGER,
first_name TEXT,
middle_name TEXT,
last_name TEXT,
nickname TEXT
);
CREATE TABLE author_to_book_temp (
book_id INTEGER,
author_id INTEGER
);
CREATE TABLE genres_temp (
genre_id INTEGER,
name TEXT
);
CREATE TABLE genre_to_book_temp (
book_id INTEGER,
genre_id INTEGER
);
CREATE TABLE keywords_temp (
keyword_id INTEGER PRIMARY KEY,
name TEXT UNIQUE
);
CREATE TABLE keyword_to_book_temp (
book_id INTEGER,
keyword_id INTEGER
);
''')
conn.commit()
def delete_pictures_hikes(connection: sqlite3.Connection,
cursor: sqlite3.Connection.cursor):
cursor.execute("DELETE FROM camera.pictures")
connection.commit()
cursor.execute("DELETE FROM camera.hikes")
connection.commit()
def _perform_update(
self,
connection: sqlite3.Connection,
conflicts: Optional[Dict[Asset, Literal['remote', 'local']]],
local_schema_version: int,
infojson: Dict[str, Any],
up_to_version: Optional[int],
) -> None:
version = self.local_assets_version + 1
target_version = min(
up_to_version, self.last_remote_checked_version
) if up_to_version else self.last_remote_checked_version # type: ignore # noqa: E501
# type ignore since due to check_for_updates we know last_remote_checked_version exists
cursor = connection.cursor()
while version <= target_version:
try:
min_schema_version = infojson['updates'][str(
version)]['min_schema_version']
max_schema_version = infojson['updates'][str(
version)]['max_schema_version']
if local_schema_version < min_schema_version or local_schema_version > max_schema_version: # noqa: E501
self.msg_aggregator.add_warning(
f'Skipping assets update {version} since it requires a min '
f'schema of {min_schema_version} and max schema of {max_schema_version} '
f'while the local DB schema version is {local_schema_version}. '
f'You will have to follow an alternative method to '
f'obtain the assets of this update.', )
cursor.execute(
'INSERT OR REPLACE INTO settings(name, value) VALUES(?, ?)',
(ASSETS_VERSION_KEY, str(version)),
)
version += 1
continue
except KeyError as e:
log.error(
f'Remote info.json for version {version} did not contain '
f'key "{str(e)}". Skipping update.', )
version += 1
continue
try:
url = f'https://raw.githubusercontent.com/rotki/assets/{self.branch}/updates/{version}/updates.sql' # noqa: E501
response = requests.get(url)
except requests.exceptions.RequestException as e:
connection.rollback()
raise RemoteError(
f'Failed to query Github for {url} during assets update: {str(e)}'
) from e # noqa: E501
if response.status_code != 200:
connection.rollback()
raise RemoteError(
f'Github query for {url} failed with status code '
f'{response.status_code} and text: {response.text}', )
self._apply_single_version_update(
cursor=cursor,
version=version,
text=response.text,
conflicts=conflicts,
)
version += 1
if self.conflicts == []:
connection.commit()
return
# In this case we have conflicts. Everything should also be rolled back
connection.rollback()
def filter_node_transformation(nodeName: str, conn: sqlite3.Connection,
verbose: bool, dottyFile: str):
"""Filter out all node transformation that is related to the given node.
Args:
nodeName: str. The node name that is passed to this script.
conn: sqlite3.Connection. A sqlite3 database connection.
verbose: bool. Verbosity of the output.
dottyFile: str. Dotty file name.
"""
cursor = conn.cursor()
cursor.execute(
"""
SELECT trans_id
FROM Log_Transformation
WHERE node_name = ?
GROUP BY trans_id
""", (nodeName, ))
rows = cursor.fetchall()
directTransIDs = [str(r[0]) for r in rows]
transIDs = find_all_related_transformation(cursor, directTransIDs)
for tid in transIDs:
cursor.execute(
"""
SELECT *
FROM Log_Transformation
WHERE trans_id = ?
""", (tid, ))
rows = cursor.fetchall()
if len(rows):
tran = Transformation(tid)
if tid in directTransIDs:
tran.isDirectTrans_ = True
TRANS_LIST.append(tran)
tran.scopeName_ = rows[0][4].replace("glow::",
"").replace("->", r" --\> ")
for r in rows:
opr_type, name, kind = r[1:4]
if opr_type == 'ADD_OPERAND':
nodeKindAndName = kind + r" \l" + name
tran.appendAddedNode(nodeKindAndName)
NODES_ADDING_MAP[nodeKindAndName] = tran
elif opr_type == 'REMOVE_OPERAND':
nodeKindAndName = kind + r" \l" + name
tran.appendRemovedNode(nodeKindAndName)
if nodeKindAndName in NODES_ADDING_MAP:
tran.addAncestor(NODES_ADDING_MAP[nodeKindAndName])
elif opr_type == 'OPERATOR_BASE':
nodeKindAndName = kind + r" \l" + name
tran.setBase(nodeKindAndName)
def processOutDottyName(dottyStyleName):
return dottyStyleName.split(r"\l")[1]
def checkNodeInIt(tran, nodeName):
if nodeName == processOutDottyName(tran.baseNode_):
return True
for rn in tran.removedNodes_:
if nodeName == processOutDottyName(rn):
return True
for an in tran.addedNodes_:
if nodeName == processOutDottyName(an):
return True
return False
for tran in TRANS_LIST:
if not verbose:
if not checkNodeInIt(tran, nodeName):
continue
print(
f"\n===============Transformation ID: {tran.transID_} ================"
)
print("Scope: " + tran.scopeName_.replace(r"\>", ">"))
if nodeName == processOutDottyName(tran.baseNode_):
print("USER NODE: \n(*)" + tran.baseNode_.replace(r"\l", " "))
else:
print("USER NODE: \n" + tran.baseNode_.replace(r"\l", " "))
print("------ Previous operands set:")
for rn in tran.removedNodes_:
if nodeName == processOutDottyName(rn):
print("\t(*)" + rn.replace(r"\l", " "))
else:
print("\t" + rn.replace(r"\l", " "))
print("------ New operands set:")
for an in tran.addedNodes_:
if nodeName == processOutDottyName(an):
print("\t(*)" + an.replace(r"\l", " "))
else:
print("\t" + an.replace(r"\l", " "))
dump_dotty_DAG(dottyFile)
conn.commit()
def approve_all(connection: Connection):
connection.execute('''UPDATE MOVIES
SET approved = 1
WHERE approved = 0''')
connection.commit()
def add_user(con: sqlite3.Connection, username: str, password: str,
admin: bool):
pass_hash = gen_password_hash(password)
con.execute("INSERT INTO users VALUES (?,?,?)",
(username, pass_hash, admin))
con.commit()
def remove_user(con: sqlite3.Connection, username: str):
con.execute("DELETE FROM users WHERE username=?", (username, ))
con.commit()
def check_sql_database(conn: sqlite3.Connection):
"""Initializes and/or updates the database to the current version"""
# Database file is automatically created with connect, now we have to check if it has tables
log.info("Checking sqlite database version...")
c = conn.cursor()
try:
c.execute("SELECT COUNT(*) as count FROM sqlite_master WHERE type = 'table'")
result = c.fetchone()
# Database is empty
if result[0] == 0:
log.warning("\tDatabase is empty.")
return False
c.execute("SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND name LIKE 'db_info'")
result = c.fetchone()
# If there's no version value, version 1 is assumed
if result is None:
c.execute("""CREATE TABLE db_info (
key TEXT,
value TEXT
)""")
c.execute("INSERT INTO db_info(key,value) VALUES('version','1')")
db_version = 1
log.warning("\tNo version found, version 1 assumed")
else:
c.execute("SELECT value FROM db_info WHERE key LIKE 'version'")
db_version = int(c.fetchone()[0])
log.info("\tVersion {0}".format(db_version))
if db_version == SQL_DB_LASTVERSION:
log.info("\tDatabase is up to date.")
return True
# Code to patch database changes
if db_version == 1:
# Added 'vocation' column to chars table, to display vocations when /check'ing users among other things.
# Changed how the last_level flagging system works a little, a character of unknown level is now flagged as
# level 0 instead of -1, negative levels are now used to flag of characters never seen online before.
c.execute("ALTER TABLE chars ADD vocation TEXT")
c.execute("UPDATE chars SET last_level = 0 WHERE last_level = -1")
db_version += 1
if db_version == 2:
# Added 'events' table
c.execute("""CREATE TABLE events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
creator INTEGER,
name TEXT,
start INTEGER,
duration INTEGER,
active INTEGER DEFAULT 1
)""")
db_version += 1
if db_version == 3:
# Added 'char_deaths' table
# Added 'status column' to events (for event announces)
c.execute("""CREATE TABLE char_deaths (
char_id INTEGER,
level INTEGER,
killer TEXT,
date INTEGER,
byplayer BOOLEAN
)""")
c.execute("ALTER TABLE events ADD COLUMN status INTEGER DEFAULT 4")
db_version += 1
if db_version == 4:
# Added 'name' column to 'discord_users' table to save their names for external use
c.execute("ALTER TABLE discord_users ADD name TEXT")
db_version += 1
if db_version == 5:
# Added 'world' column to 'chars', renamed 'discord_users' to 'users', created table 'user_servers'
c.execute("ALTER TABLE chars ADD world TEXT")
c.execute("ALTER TABLE discord_users RENAME TO users")
c.execute("""CREATE TABLE user_servers (
id INTEGER,
server INTEGER,
PRIMARY KEY(id)
);""")
db_version += 1
if db_version == 6:
# Added 'description', 'server' column to 'events', created table 'events_subscribers'
c.execute("ALTER TABLE events ADD description TEXT")
c.execute("ALTER TABLE events ADD server INTEGER")
c.execute("""CREATE TABLE event_subscribers (
event_id INTEGER,
user_id INTEGER
);""")
db_version += 1
if db_version == 7:
# Created 'server_properties' table
c.execute("""CREATE TABLE server_properties (
server_id INTEGER,
name TEXT,
value TEXT
);""")
db_version += 1
if db_version == 8:
# Added 'achievements', 'axe', 'club', 'distance', 'fishing', 'fist', 'loyalty', 'magic', 'shielding',
# 'sword', 'achievements_rank', 'axe_rank', 'club_rank', 'distance_rank', 'fishing_rank', 'fist_rank',
# 'loyalty_rank', 'magic_rank', 'shielding_rank', 'sword_rank', columns to 'chars'
c.execute("ALTER TABLE chars ADD achievements INTEGER")
c.execute("ALTER TABLE chars ADD axe INTEGER")
c.execute("ALTER TABLE chars ADD club INTEGER")
c.execute("ALTER TABLE chars ADD distance INTEGER")
c.execute("ALTER TABLE chars ADD fishing INTEGER")
c.execute("ALTER TABLE chars ADD fist INTEGER")
c.execute("ALTER TABLE chars ADD loyalty INTEGER")
c.execute("ALTER TABLE chars ADD magic INTEGER")
c.execute("ALTER TABLE chars ADD shielding INTEGER")
c.execute("ALTER TABLE chars ADD sword INTEGER")
c.execute("ALTER TABLE chars ADD achievements_rank INTEGER")
c.execute("ALTER TABLE chars ADD axe_rank INTEGER")
c.execute("ALTER TABLE chars ADD club_rank INTEGER")
c.execute("ALTER TABLE chars ADD distance_rank INTEGER")
c.execute("ALTER TABLE chars ADD fishing_rank INTEGER")
c.execute("ALTER TABLE chars ADD fist_rank INTEGER")
c.execute("ALTER TABLE chars ADD loyalty_rank INTEGER")
c.execute("ALTER TABLE chars ADD magic_rank INTEGER")
c.execute("ALTER TABLE chars ADD shielding_rank INTEGER")
c.execute("ALTER TABLE chars ADD sword_rank INTEGER")
db_version += 1
if db_version == 9:
# Added 'magic_ek', 'magic_rp', 'magic_ek_rank', 'magic_rp_rank' columns to 'chars'
c.execute("ALTER TABLE chars ADD magic_ek INTEGER")
c.execute("ALTER TABLE chars ADD magic_rp INTEGER")
c.execute("ALTER TABLE chars ADD magic_ek_rank INTEGER")
c.execute("ALTER TABLE chars ADD magic_rp_rank INTEGER")
db_version += 1
if db_version == 10:
# Added 'guild' column to 'chars'
c.execute("ALTER TABLE chars ADD guild TEXT")
db_version += 1
if db_version == 11:
# Added 'deleted' column to 'chars'
c.execute("ALTER TABLE chars ADD deleted INTEGER DEFAULT 0")
db_version += 1
if db_version == 12:
# Added 'hunted' table
c.execute("""CREATE TABLE hunted_list (
name TEXT,
is_guild BOOLEAN DEFAULT 0,
server_id INTEGER
);""")
db_version += 1
if db_version == 13:
# Renamed table hunted_list to watched_list and related server properties
c.execute("ALTER TABLE hunted_list RENAME TO watched_list")
c.execute("UPDATE server_properties SET name = 'watched_channel' WHERE name LIKE 'hunted_channel'")
c.execute("UPDATE server_properties SET name = 'watched_message' WHERE name LIKE 'hunted_message'")
db_version += 1
if db_version == 14:
c.execute("""CREATE TABLE ignored_channels (
server_id INTEGER,
channel_id INTEGER
);""")
db_version += 1
if db_version == 15:
c.execute("""CREATE TABLE highscores (
rank INTEGER,
category TEXT,
world TEXT,
name TEXT,
vocation TEXT,
value INTEGER
);""")
c.execute("""CREATE TABLE highscores_times (
world TEXT,
last_scan INTEGER
);""")
db_version += 1
if db_version == 16:
c.execute("ALTER table highscores_times ADD category TEXT")
db_version += 1
if db_version == 17:
# Cleaning up unused columns and renaming columns
c.execute("""CREATE TABLE chars_temp(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
name TEXT,
level INTEGER DEFAULT -1,
vocation TEXT,
world TEXT,
guild TEXT
);""")
c.execute("INSERT INTO chars_temp SELECT id, user_id, name, last_level, vocation, world, guild FROM chars")
c.execute("DROP TABLE chars")
c.execute("ALTER table chars_temp RENAME TO chars")
c.execute("DROP TABLE IF EXISTS user_servers")
c.execute("""CREATE TABLE users_temp(
id INTEGER NOT NULL,
name TEXT,
PRIMARY KEY(id)
);""")
c.execute("INSERT INTO users_temp SELECT id, name FROM users")
c.execute("DROP TABLE users")
c.execute("ALTER table users_temp RENAME TO users")
db_version += 1
if db_version == 18:
# Adding event participants
c.execute("ALTER TABLE events ADD joinable INTEGER DEFAULT 1")
c.execute("ALTER TABLE events ADD slots INTEGER DEFAULT 0")
c.execute("""CREATE TABLE event_participants(
event_id INTEGER NOT NULL,
char_id INTEGER NOT NULL
);""")
db_version += 1
if db_version == 19:
# Adding reason and author to watched-list
c.execute("ALTER TABLE watched_list ADD reason TEXT")
c.execute("ALTER TABLE watched_list ADD author INTEGER")
c.execute("ALTER TABLE watched_list ADD added INTEGER")
db_version += 1
if db_version == 20:
# Joinable ranks
c.execute("""CREATE TABLE joinable_roles(
server_id INTEGER NOT NULL,
role_id INTEGER NOT NULL
);""")
db_version += 1
if db_version == 21:
# Autoroles
c.execute("""CREATE TABLE auto_roles(
server_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
guild TEXT NOT NULL
);""")
db_version += 1
log.info("\tUpdated database to version {0}".format(db_version))
c.execute("UPDATE db_info SET value = ? WHERE key LIKE 'version'", (db_version,))
return True
except Exception as e:
log.error(f"\tError reading sqlite database: {e}")
return False
finally:
c.close()
conn.commit()
def save_result(con: sqlite3.Connection, user: str, subject: str, result: int):
con.execute(
"INSERT INTO results (username, subject, result) VALUES (?,?,?)",
(user, subject, result))
con.commit()
def create_tables(con: sqlite3.Connection) -> None:
cur = con.cursor()
cur.execute("""
CREATE TABLE MetaData (
Key TEXT NOT NULL UNIQUE,
Value TEXT NOT NULL,
PRIMARY KEY(Key)
);
""")
cur.execute(
"""
INSERT INTO MetaData (Key, Value) VALUES
("Version", "12"),
("GeneratedTime", ?);
""", (datetime.utcnow().strftime("%Y%m%d%H%M%S"), ))
cur.execute("""
CREATE TABLE Rarities (
Name TEXT NOT NULL UNIQUE,
ObjectName TEXT NOT NULL UNIQUE,
PRIMARY KEY(Name),
UNIQUE(Name, ObjectName)
);
""")
for name, obj_name in RARITIES:
cur.execute("INSERT INTO Rarities (Name, ObjectName) VALUES (?, ?);",
(name, obj_name))
cur.execute("""
CREATE TABLE GearCategories (
Name TEXT NOT NULL UNIQUE,
ObjectName TEXT NOT NULL UNIQUE,
PRIMARY KEY(Name),
UNIQUE(Name, ObjectName)
);
""")
for name, obj_name in GEAR_CATEGORIES:
cur.execute(
"INSERT INTO GearCategories (Name, ObjectName) VALUES (?, ?);",
(name, obj_name))
cur.execute("""
CREATE TABLE ItemGroups (
Name TEXT NOT NULL UNIQUE,
PRIMARY KEY(Name)
);
""")
for name in ITEM_GROUPS:
cur.execute("INSERT INTO ItemGroups (Name) VALUES (?);", (name, ))
cur.execute("""
CREATE TABLE Manufacturers (
Name TEXT NOT NULL UNIQUE,
ObjectName TEXT NOT NULL UNIQUE,
PRIMARY KEY(Name),
UNIQUE(Name, ObjectName)
);
""")
for name, obj_name in MANUFACTURERS:
cur.execute(
"INSERT INTO Manufacturers (Name, ObjectName) VALUES (?, ?);",
(name, obj_name))
cur.execute("""
CREATE TABLE SourceTypes (
Name TEXT NOT NULL UNIQUE,
PRIMARY KEY(Name)
);
""")
for name in SOURCE_TYPES:
cur.execute("INSERT INTO SourceTypes (Name) VALUES (?);", (name, ))
cur.execute("""
CREATE TABLE Maps (
Name TEXT NOT NULL UNIQUE,
ObjectName TEXT NOT NULL UNIQUE,
PRIMARY KEY(Name),
UNIQUE(Name, ObjectName)
);
""")
for name, obj_name in MAPS:
cur.execute("INSERT INTO Maps (Name, ObjectName) VALUES (?, ?);",
(name, obj_name))
cur.execute("""
CREATE TABLE PlayerClass (
Name TEXT NOT NULL UNIQUE,
ObjectName TEXT NOT NULL UNIQUE,
PRIMARY KEY(Name),
UNIQUE(Name, ObjectName)
);
""")
for name, obj_name in PLAYER_CLASSES:
cur.execute(
"INSERT INTO PlayerClass (Name, ObjectName) VALUES (?, ?);",
(name, obj_name))
cur.execute("""
CREATE TABLE Items (
ID INTEGER NOT NULL UNIQUE,
Rarity TEXT NOT NULL,
GearCategory TEXT NOT NULL,
ItemGroup TEXT NOT NULL,
RequiredClass TEXT,
Name TEXT NOT NULL UNIQUE,
ObjectName TEXT NOT NULL UNIQUE,
PRIMARY KEY(ID AUTOINCREMENT),
FOREIGN KEY(Rarity) REFERENCES Rarities(Name),
FOREIGN KEY(GearCategory) REFERENCES GearCategories(Name),
FOREIGN KEY(ItemGroup) REFERENCES ItemGroups(Name),
FOREIGN KEY(RequiredClass) REFERENCES PlayerClass(Name)
);
""")
cur.execute("""
CREATE TABLE ManufacturedBy (
ID INTEGER NOT NULL UNIQUE,
ItemID INTEGER NOT NULL,
Manufacturer TEXT NOT NULL,
PRIMARY KEY(ID AUTOINCREMENT),
FOREIGN KEY(ItemID) REFERENCES Items(ID),
FOREIGN KEY(Manufacturer) REFERENCES Manufacturers(Name),
UNIQUE(ItemID, Manufacturer)
);
""")
cur.execute("""
CREATE TABLE Variants (
ID INTEGER NOT NULL UNIQUE,
ItemID INTEGER NOT NULL,
VariantName TEXT NOT NULL UNIQUE,
PRIMARY KEY(ID AUTOINCREMENT),
FOREIGN KEY(ItemID) REFERENCES Items(ID)
);
""")
cur.execute("""
CREATE TABLE Sources (
ID INTEGER NOT NULL UNIQUE,
SourceType TEXT NOT NULL,
Map TEXT,
Description TEXT NOT NULL UNIQUE,
ObjectName TEXT,
PRIMARY KEY(ID AUTOINCREMENT),
FOREIGN KEY(SourceType) REFERENCES SourceTypes(Name),
FOREIGN KEY(Map) REFERENCES Maps(Name)
);
""")
cur.execute("""
CREATE TABLE ObtainedFrom (
ID INTEGER NOT NULL UNIQUE,
ItemID INTEGER NOT NULL,
SourceID INTEGER NOT NULL,
PRIMARY KEY(ID AUTOINCREMENT),
FOREIGN KEY(ItemID) REFERENCES Items(ID),
FOREIGN KEY(SourceID) REFERENCES Sources(ID),
UNIQUE(ItemID, SourceID)
);
""")
con.commit()
def update_name(db: Connection,
g_id: int,
new: str):
db.cursor().execute("UPDATE groups SET groupName = ? WHERE groupId = ?", (new, g_id))
db.commit()
def add_petition(conn: sqlite3.Connection, user_id: int, first_name: str, petition: str):
c = conn.cursor()
c.execute('INSERT INTO user_petition (user_id, first_name , petition , petition_date) VALUES(?,?, ?, ?)',
(user_id, first_name, petition, ctime()))
conn.commit()
def build_sql_schema(sql_connection: sqlite3.Connection) -> None:
"""
Create the SQLite DB schema
:param sql_connection: Connection to the database
"""
LOGGER.info("Building SQLite Schema")
cursor = sql_connection.cursor()
# Build Set table
cursor.execute(
"CREATE TABLE `sets` ("
"baseSetSize INTEGER,"
"block TEXT,"
"boosterV3 TEXT,"
"code TEXT,"
"codeV3 TEXT,"
"isFoilOnly INTEGER NOT NULL DEFAULT 0,"
"isOnlineOnly INTEGER NOT NULL DEFAULT 0,"
"keyruneCode TEXT,"
"mcmName TEXT,"
"mcmId INTEGER,"
"meta TEXT,"
"mtgoCode TEXT,"
"name TEXT,"
"parentCode TEXT,"
"releaseDate TEXT,"
"tcgplayerGroupId INTEGER,"
"totalSetSize INTEGER,"
"type TEXT"
")"
)
# Translations for set names
cursor.execute(
"CREATE TABLE `set_translations` ("
"language TEXT,"
"translation TEXT,"
"setCode TEXT"
")"
)
# Build foreignData table
cursor.execute(
"CREATE TABLE `foreignData` ("
"uuid TEXT,"
"flavorText TEXT,"
"language TEXT,"
"multiverseId INTEGER,"
"name TEXT,"
"text TEXT,"
"type TEXT"
")"
)
# Build legalities table
cursor.execute(
"CREATE TABLE `legalities` (" "uuid TEXT," "format TEXT," "status TEXT" ")"
)
# Build ruling table
cursor.execute("CREATE TABLE `rulings` (" "uuid TEXT," "date TEXT," "text TEXT" ")")
# Build cards table
cursor.execute(
"CREATE TABLE `cards` ("
"artist TEXT,"
"borderColor TEXT,"
"colorIdentity TEXT,"
"colorIndicator TEXT,"
"colors TEXT,"
"convertedManaCost FLOAT,"
"duelDeck TEXT,"
"faceConvertedManaCost FLOAT,"
"flavorText TEXT,"
"frameEffect TEXT,"
"frameVersion TEXT,"
"hand TEXT,"
"hasFoil INTEGER NOT NULL DEFAULT 0,"
"hasNonFoil INTEGER NOT NULL DEFAULT 0,"
"isAlternative INTEGER NOT NULL DEFAULT 0,"
"isOnlineOnly INTEGER NOT NULL DEFAULT 0,"
"isOversized INTEGER NOT NULL DEFAULT 0,"
"isReserved INTEGER NOT NULL DEFAULT 0,"
"isStarter INTEGER NOT NULL DEFAULT 0,"
"isTimeshifted INTEGER NOT NULL DEFAULT 0,"
"layout TEXT,"
"life TEXT,"
"loyalty TEXT,"
"manaCost TEXT,"
"mcmName TEXT DEFAULT NULL,"
"mcmId INTEGER DEFAULT 0,"
"mcmMetaId INTEGER DEFAULT 0,"
"multiverseId INTEGER,"
"name TEXT,"
"names TEXT,"
"number TEXT,"
"originalText TEXT,"
"originalType TEXT,"
"printings TEXT,"
"power TEXT,"
"purchaseUrls TEXT,"
"rarity TEXT,"
"scryfallId TEXT,"
"scryfallOracleId TEXT,"
"scryfallIllustrationId TEXT,"
"setCode TEXT,"
"side TEXT,"
"subtypes TEXT,"
"supertypes TEXT,"
"tcgplayerProductId INTEGER,"
"tcgplayerPurchaseUrl TEXT,"
"text TEXT,"
"toughness TEXT,"
"type TEXT,"
"types TEXT,"
"uuid TEXT(36) PRIMARY KEY,"
"uuidV421 TEXT,"
"variations TEXT,"
"watermark TEXT"
")"
)
# Build tokens table
cursor.execute(
"CREATE TABLE `tokens` ("
"artist TEXT,"
"borderColor TEXT,"
"colorIdentity TEXT,"
"colorIndicator TEXT,"
"colors TEXT,"
"isOnlineOnly INTEGER NOT NULL DEFAULT 0,"
"layout TEXT,"
"loyalty TEXT,"
"name TEXT,"
"number TEXT,"
"power TEXT,"
"reverseRelated TEXT,"
"scryfallId TEXT,"
"scryfallOracleId TEXT,"
"scryfallIllustrationId TEXT,"
"setCode TEXT,"
"side TEXT,"
"text TEXT,"
"toughness TEXT,"
"type TEXT,"
"uuid TEXT,"
"uuidV421 TEXT,"
"watermark TEXT"
")"
)
# Execute the commands
sql_connection.commit()
def add_subject(con: sqlite3.Connection, sub_name: str, fpath: str):
con.execute(
"INSERT INTO subjects (name, questions_file_path) VALUES (?,?)",
(sub_name, fpath))
con.commit()
def parse(items_game: str, db: sqlite3.Connection, merge_allclass = True):
"""
Parses items_game.txt into a database format usable by TF2IDB.
:param items_game: Path to the items_game.txt file from TF2.
:param db: An SQLite3 connection.
:param merge_allclass: Whether or not items designated as usable by every class should use
the 'all' keyword. Defaults to True. Set to false if using a different branch of TF2IDB.
"""
data = None
with open(items_game) as f:
data = vdf.parse(f)
data = data['items_game']
dbc = db.cursor()
created_tables = {}
def init_table(name: str, columns: list, primary_key = None):
c = ', '.join(('"{}" {}'.format(k, v) for k, v in columns))
if primary_key:
column_names = (column for column, *_ in columns)
if not all(key in column_names for key in primary_key):
raise ValueError("Primary key not a valid column in table '{}'".format(name))
c += ', PRIMARY KEY ({})'.format(', '.join( ('"{}"'.format(k)) for k in primary_key))
query = 'CREATE TABLE "new_{}" ({})'.format(name, c)
dbc.execute('DROP TABLE IF EXISTS new_{}'.format(name))
dbc.execute(query)
created_tables[name] = [ column for column, *_ in columns ]
def insert_dict(name: str, item: dict, prop_remap: dict = {}):
if not name in created_tables:
raise ValueError("Table '{}' does not exist")
dbc.execute('INSERT INTO new_{name} ({cols}) VALUES ({args})'.format(
name = name,
cols = ','.join(created_tables[name]),
args = ','.join(':' + prop_remap.get(col, col) for col in created_tables[name])),
item)
init_table('tf2idb_class', [
('id', 'INTEGER NOT NULL'), ('class', 'TEXT NOT NULL'), ('slot', 'TEXT')
], primary_key = ('id', 'class'))
init_table('tf2idb_item_attributes', [
('id', 'INTEGER NOT NULL'), ('attribute', 'INTEGER NOT NULL'), ('value', 'TEXT NOT NULL'),
('static', 'INTEGER')
], primary_key = ('id', 'attribute'))
init_table('tf2idb_item', [
('id', 'INTEGER PRIMARY KEY NOT NULL'),
('name', 'TEXT NOT NULL'),
('item_name', 'TEXT'),
('class', 'TEXT NOT NULL'),
('slot', 'TEXT'),
('quality', 'TEXT NOT NULL'),
('tool_type', 'TEXT'),
('min_ilevel', 'INTEGER'),
('max_ilevel', 'INTEGER'),
('baseitem', 'INTEGER'),
('holiday_restriction', 'TEXT'),
('has_string_attribute', 'INTEGER'),
('propername', 'INTEGER')
])
init_table('tf2idb_particles', [
('id', 'INTEGER PRIMARY KEY NOT NULL'), ('name', 'TEXT NOT NULL'),
('type', 'TEXT NOT NULL')
])
init_table('tf2idb_equip_conflicts', [
('name', 'TEXT NOT NULL'), ('region', 'TEXT NOT NULL'),
], primary_key = ('name', 'region'))
init_table('tf2idb_equip_regions', [
('id', 'INTEGER NOT NULL'), ('region', 'TEXT NOT NULL')
], primary_key = ('id', 'region'))
init_table('tf2idb_capabilities', [
('id', 'INTEGER NOT NULL'), ('capability', 'TEXT NOT NULL')
], primary_key = ('id', 'capability'))
init_table('tf2idb_attributes', [
('id', 'INTEGER PRIMARY KEY NOT NULL'),
('name', 'TEXT NOT NULL'),
('attribute_class', 'TEXT'),
('attribute_type', 'TEXT'),
('description_string', 'TEXT'),
('description_format', 'TEXT'),
('effect_type', 'TEXT'),
('hidden', 'INTEGER'),
('stored_as_integer', 'INTEGER'),
('armory_desc', 'TEXT'),
('is_set_bonus', 'INTEGER'),
('is_user_generated', 'INTEGER'),
('can_affect_recipe_component_name', 'INTEGER'),
('apply_tag_to_item_definition', 'TEXT')
])
init_table('tf2idb_qualities', [
('name', 'TEXT PRIMARY KEY NOT NULL'),
('value', 'INTEGER NOT NULL')
])
init_table('tf2idb_rarities', [
('name', 'TEXT PRIMARY KEY NOT NULL'),
('value', 'INTEGER NOT NULL')
])
init_table('tf2idb_item_rarities', [
('id', 'INTEGER PRIMARY KEY NOT NULL'),
('rarity', 'INTEGER'),
('collection', 'TEXT')
])
nonce = int(time.time())
dbc.execute('CREATE INDEX "tf2idb_item_attributes_%i" ON "new_tf2idb_item_attributes" ("attribute" ASC)' % nonce)
dbc.execute('CREATE INDEX "tf2idb_class_%i" ON "new_tf2idb_class" ("class" ASC)' % nonce)
dbc.execute('CREATE INDEX "tf2idb_item_%i" ON "new_tf2idb_item" ("slot" ASC)' % nonce)
# qualities
dbc.executemany('INSERT INTO new_tf2idb_qualities (name, value) VALUES (?,?)',
((qname, qdata['value']) for qname, qdata in data['qualities'].items()))
# particles
for particle_type, particle_list in data['attribute_controlled_attached_particles'].items():
dbc.executemany('INSERT INTO new_tf2idb_particles (id, name, type) VALUES (?,?,?)',
((id, property['system'], particle_type) for id, property in particle_list.items()))
# attributes
attribute_type = {}
for k,v in data['attributes'].items():
atype = v.get('attribute_type', 'integer' if v.get('stored_as_integer') else 'float')
attribute_type[v['name'].lower()] = (k, atype)
insert_dict('tf2idb_attributes', defaultdict(lambda: None, { **{ 'id': k }, **v }))
# conflicts
for k,v in data['equip_conflicts'].items():
dbc.executemany('INSERT INTO new_tf2idb_equip_conflicts (name,region) VALUES (?,?)',
((k, region) for region in v.keys()))
# rarities
db.executemany('INSERT INTO new_tf2idb_rarities (name, value) VALUES (?, ?)',
((rname, rdata['value']) for rname, rdata in data['rarities'].items()))
# item / rarity mapping
item_rarity = {}
for collection, collection_desc in data['item_collections'].items():
for rarity, itemlist in collection_desc['items'].items():
if rarity in data['rarities']:
for item in itemlist:
item_rarity[item] = (collection, int(data['rarities'][rarity]['value']))
# items
item_defaults = {'propername': 0, 'item_quality': ''}
for id,v in data['items'].items():
if id == 'default':
continue
i, prefabs_used = resolve_prefabs(v, data['prefabs'])
baseitem = 'baseitem' in i
try:
has_string_attribute = False
for name,value in i.get('static_attrs', {}).items():
aid,atype = attribute_type[name.lower()]
if atype == 'string':
has_string_attribute = True
dbc.execute('INSERT INTO new_tf2idb_item_attributes (id,attribute,value,static) VALUES (?,?,?,?)', (id,aid,value,1))
for name,info in i.get('attributes', {}).items():
aid,atype = attribute_type[name.lower()]
if atype == 'string':
has_string_attribute = True
dbc.execute('INSERT INTO new_tf2idb_item_attributes (id,attribute,value,static) VALUES (?,?,?,?)', (id,aid,info['value'],0))
tool = i.get('tool', {}).get('type')
item_insert_values = {
'id': id, 'tool_type': tool, 'baseitem': baseitem, 'has_string_attribute': has_string_attribute
}
insert_dict('tf2idb_item',
defaultdict(lambda: None, { **item_defaults, **item_insert_values, **i }),
prop_remap = {'class': 'item_class', 'slot': 'item_slot', 'quality': 'item_quality'})
default_slot = i.get('item_slot', None)
used_classes = i.get('used_by_classes', {})
if merge_allclass and all(c in used_classes for c in CLASSES_USABLE):
# insert the 'all' keyword into tf2idb_class instead of a row for each class
dbc.execute('INSERT INTO new_tf2idb_class (id, class, slot) VALUES (?, ?, ?)',
(id, 'all', default_slot))
else:
dbc.executemany('INSERT INTO new_tf2idb_class (id,class,slot) VALUES (?,?,?)',
((id, prof.lower(), val if val != '1' else default_slot)
for prof, val in used_classes.items()))
region_field = i.get('equip_region') or i.get('equip_regions')
if region_field:
if type(region_field) is str:
region_field = {region_field: 1}
dbc.executemany('INSERT INTO new_tf2idb_equip_regions (id,region) VALUES (?,?)',
((id, region) for region in region_field.keys()))
# capabilties
dbc.executemany('INSERT INTO new_tf2idb_capabilities (id,capability) VALUES (?,?)',
((id, (capability if val != '0' else '!'+capability))
for capability, val in i.get('capabilities', {}).items()))
# custom extended capabilities
if item_has_australium_support(int(id), i):
dbc.execute('INSERT INTO new_tf2idb_capabilities (id, capability) VALUES (?, ?)',
(id, 'supports_australium'))
if item_has_paintkit_support(int(id), i):
dbc.execute('INSERT INTO new_tf2idb_capabilities (id, capability) VALUES (?, ?)',
(id, 'can_apply_paintkit'))
# item rarity
if i['name'] in item_rarity:
dbc.execute('INSERT INTO new_tf2idb_item_rarities (id, collection, rarity) VALUES (?, ?, ?)',
(id,) + item_rarity[ i['name'] ])
except Exception as e:
raise ItemParseError(id) from e
# finalize tables
for table in created_tables.keys():
dbc.execute('DROP TABLE IF EXISTS %s' % table)
dbc.execute('ALTER TABLE new_%s RENAME TO %s' % (table, table))
db.commit()
dbc.execute('VACUUM')
def update_videos(conn: sqlite3.Connection,
api_auth,
update_age_cutoff=86400,
verbosity=1):
verbosity_level_1 = verbosity >= 1
verbosity_level_2 = verbosity >= 2
verbosity_level_3 = verbosity >= 3
records_passed, updated, newly_inactive, newly_active, deleted = [0] * 5
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute(
"""SELECT id, last_updated FROM videos
WHERE title NOT IN (?, ?) AND NOT status = ?
ORDER BY last_updated;""",
('unknown', 'YouTube Music', 'deleted'))
records = {k: v for k, v in cur.fetchall()}
cur.execute("""SELECT * FROM channels WHERE title is not NULL;""")
channels = {k: v for k, v in cur.fetchall()}
cur.execute("""SELECT * FROM tags;""")
existing_tags = {v: k for k, v in cur.fetchall()}
cur.execute("""SELECT * FROM videos_tags""")
existing_videos_tags = {}
for video_tag_entry in cur.fetchall():
existing_videos_tags.setdefault(video_tag_entry[0], [])
existing_videos_tags[video_tag_entry[0]].append(video_tag_entry[1])
cur.execute("""SELECT * FROM videos_topics""")
existing_topics_tags = {}
for video_topic_entry in cur.fetchall():
existing_topics_tags.setdefault(video_topic_entry[0], [])
existing_topics_tags[video_topic_entry[0]].append(video_topic_entry[1])
cur.close()
now = datetime.utcnow() # for determining if the record is old enough
dt_strp = datetime.strptime
dt_format = '%Y-%m-%d %H:%M:%S'
records_filtered_by_age = [
k for k, v in records.items()
if (now - dt_strp(v, dt_format)).total_seconds() > update_age_cutoff
]
sub_percent, sub_percent_int = calculate_subpercentage(
len(records_filtered_by_age))
commit_interval = calculate_commit_interval(sub_percent_int)
commit_interval_counter = 0
del records
if verbosity_level_1:
logger.info(f'\nStarting records\' updating...\n' + '-' * 100)
for record in records_filtered_by_age:
records_passed += 1
if records_passed % sub_percent_int == 0:
yield ((records_passed // sub_percent) / 10, records_passed,
updated, newly_inactive, newly_active, deleted)
record = execute_query(conn, 'SELECT * FROM videos WHERE id = ?',
(record, ))
record = dict(record[0])
video_id = record['id']
for attempt in range(1, 6):
api_response = youtube.get_video_info(video_id, api_auth)
time.sleep(0.01 * attempt**attempt)
if api_response:
if api_response['items']:
api_video_data = wrangle_video_record(
api_response['items'])
if len(api_video_data) >= 7:
# a record must have at least 7 fields after
# going through wrangle_video_record, otherwise it's a
# record of a deleted video with no valid data
api_video_data.pop('published_at', None)
if 'channel_title' not in api_video_data:
# the video is somehow available through API
# (though some data is missing), but not on YouTube
pass
else:
if record['status'] == 'inactive':
record['status'] = 'active'
newly_active += 1
if verbosity_level_1:
logger.info(
f'{get_record_id_and_title(record)}, '
f'is now active')
record.update(api_video_data)
else:
record['status'] = 'deleted'
deleted += 1
if verbosity_level_1:
logger.info(f'{get_record_id_and_title(record)}, '
f'is now deleted from YT')
else:
if record['status'] == 'active':
record['status'] = 'inactive'
newly_inactive += 1
if verbosity_level_1:
logger.info(f'{get_record_id_and_title(record)}, '
f'is now inactive')
record['last_updated'] = datetime.utcnow().replace(
microsecond=0)
break
else:
continue
if 'tags' in record:
tags = record.pop('tags')
add_tags_to_table_and_videos(conn, tags, video_id, existing_tags,
existing_videos_tags,
verbosity_level_3)
# perhaps, the record should also be checked for tags that have
# been removed from the updated version and have them removed from
# the DB as well. However, keeping a fuller record, despite what
# the video's uploader/author might think about its accuracy,
# seems like a better option
channel_id = record['channel_id']
if 'channel_title' in record:
channel_title = record.pop('channel_title')
try:
if channel_title != channels[channel_id]:
update_channel(conn, channel_id, channel_title,
channels[channel_id], verbosity_level_1)
channels[channel_id] = channel_title
except KeyError:
"""The channel now has a different ID... it's a thing.
One possible reason for this is large channels, belonging to
large media companies, getting split off into smaller
channels. That's what it looked like when I came across it.
Only encountered this once in ~19k of my own records."""
add_channel(conn, channel_id, channel_title, verbosity_level_2)
else:
# Less than a handful of videos were not available on YouTube, but
# were available through API, with channel id, but no channel title.
# In Takeout, these had no channel title or id, but had regular
# title/id. Very strange.
if channel_id not in channels:
add_channel(conn, channel_id)
if 'relevant_topic_ids' in record:
topics_list = record.pop('relevant_topic_ids')
if existing_topics_tags.get(video_id):
for topic in topics_list:
if topic not in existing_topics_tags[video_id]:
add_topic_to_video(conn, topic, video_id,
verbosity_level_2)
if update_video(conn, record, verbosity_level_3):
updated += 1
commit_interval_counter += 1
if commit_interval_counter == commit_interval:
conn.commit()
commit_interval_counter = 0
conn.commit()
execute_query(conn, 'VACUUM')
conn.row_factory = None
results = {
'records_processed': records_passed,
'records_updated': updated,
'newly_inactive': newly_inactive,
'newly_active': newly_active,
'deleted_from_youtube': deleted
}
if verbosity_level_1:
logger.info(json.dumps(results, indent=4))
logger.info('\n' + '-' * 100 + f'\nUpdating finished')
class Database(object):
__metaclass__ = ABCMeta
def __init__(self, file_path):
"""
Initialize a new Database instance.
@param file_path: the path to the database file.
@type file_path: unicode
"""
assert isinstance(file_path, unicode)
logger.debug("loading database [%s]", file_path)
self._file_path = file_path
# _CONNECTION, _CURSOR, AND _DATABASE_VERSION are set during open(...)
self._connection = None
self._cursor = None
self._database_version = 0
# _commit_callbacks contains a list with functions that are called on each database commit
self._commit_callbacks = []
# Database.commit() is enabled when _pending_commits == 0. Database.commit() is disabled
# when _pending_commits > 0. A commit is required when _pending_commits > 1.
self._pending_commits = 0
if __debug__:
self._debug_thread_ident = 0
def open(self, initial_statements=True, prepare_visioning=True):
assert self._cursor is None, "Database.open() has already been called"
assert self._connection is None, "Database.open() has already been called"
if __debug__:
self._debug_thread_ident = thread.get_ident()
logger.debug("open database [%s]", self._file_path)
self._connect()
if initial_statements:
self._initial_statements()
if prepare_visioning:
self._prepare_version()
return True
def close(self, commit=True):
assert self._cursor is not None, "Database.close() has been called or Database.open() has not been called"
assert self._connection is not None, "Database.close() has been called or Database.open() has not been called"
if commit:
self.commit(exiting=True)
logger.debug("close database [%s]", self._file_path)
self._cursor.close()
self._cursor = None
self._connection.close()
self._connection = None
return True
def _connect(self):
self._connection = Connection(self._file_path)
self._cursor = self._connection.cursor()
def _initial_statements(self):
assert self._cursor is not None, "Database.close() has been called or Database.open() has not been called"
assert self._connection is not None, "Database.close() has been called or Database.open() has not been called"
# collect current database configuration
page_size = int(next(self._cursor.execute(u"PRAGMA page_size"))[0])
journal_mode = unicode(next(self._cursor.execute(u"PRAGMA journal_mode"))[0]).upper()
synchronous = unicode(next(self._cursor.execute(u"PRAGMA synchronous"))[0]).upper()
#
# PRAGMA page_size = bytes;
# http://www.sqlite.org/pragma.html#pragma_page_size
# Note that changing page_size has no effect unless performed on a new database or followed
# directly by VACUUM. Since we do not want the cost of VACUUM every time we load a
# database, existing databases must be upgraded.
#
if page_size < 8192:
logger.debug("PRAGMA page_size = 8192 (previously: %s) [%s]", page_size, self._file_path)
# it is not possible to change page_size when WAL is enabled
if journal_mode == u"WAL":
self._cursor.executescript(u"PRAGMA journal_mode = DELETE")
journal_mode = u"DELETE"
self._cursor.execute(u"PRAGMA page_size = 8192")
self._cursor.execute(u"VACUUM")
page_size = 8192
else:
logger.debug("PRAGMA page_size = %s (no change) [%s]", page_size, self._file_path)
#
# PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF
# http://www.sqlite.org/pragma.html#pragma_page_size
#
if not (journal_mode == u"WAL" or self._file_path == u":memory:"):
logger.debug("PRAGMA journal_mode = WAL (previously: %s) [%s]", journal_mode, self._file_path)
self._cursor.execute(u"PRAGMA journal_mode = WAL")
else:
logger.debug("PRAGMA journal_mode = %s (no change) [%s]", journal_mode, self._file_path)
#
# PRAGMA synchronous = 0 | OFF | 1 | NORMAL | 2 | FULL;
# http://www.sqlite.org/pragma.html#pragma_synchronous
#
if not synchronous in (u"NORMAL", u"1"):
logger.debug("PRAGMA synchronous = NORMAL (previously: %s) [%s]", synchronous, self._file_path)
self._cursor.execute(u"PRAGMA synchronous = NORMAL")
else:
logger.debug("PRAGMA synchronous = %s (no change) [%s]", synchronous, self._file_path)
def _prepare_version(self):
assert self._cursor is not None, "Database.close() has been called or Database.open() has not been called"
assert self._connection is not None, "Database.close() has been called or Database.open() has not been called"
# check is the database contains an 'option' table
try:
count, = next(self.execute(u"SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = 'option'"))
except StopIteration:
raise RuntimeError()
if count:
# get version from required 'option' table
try:
version, = next(self.execute(u"SELECT value FROM option WHERE key == 'database_version' LIMIT 1"))
except StopIteration:
# the 'database_version' key was not found
version = u"0"
else:
# the 'option' table probably hasn't been created yet
version = u"0"
self._database_version = self.check_database(version)
assert isinstance(self._database_version, (int, long)), type(self._database_version)
@property
def database_version(self):
return self._database_version
@property
def file_path(self):
"""
The database filename including path.
"""
return self._file_path
def __enter__(self):
"""
Enters a no-commit state. The commit will be performed by __exit__.
@return: The method self.execute
"""
assert self._cursor is not None, "Database.close() has been called or Database.open() has not been called"
assert self._connection is not None, "Database.close() has been called or Database.open() has not been called"
assert self._debug_thread_ident != 0, "please call database.open() first"
assert self._debug_thread_ident == thread.get_ident(), "Calling Database.execute on the wrong thread"
logger.debug("disabling commit [%s]", self._file_path)
self._pending_commits = max(1, self._pending_commits)
return self
def __exit__(self, exc_type, exc_value, traceback):
"""
Leaves a no-commit state. A commit will be performed if Database.commit() was called while
in the no-commit state.
"""
assert self._cursor is not None, "Database.close() has been called or Database.open() has not been called"
assert self._connection is not None, "Database.close() has been called or Database.open() has not been called"
assert self._debug_thread_ident != 0, "please call database.open() first"
assert self._debug_thread_ident == thread.get_ident(), "Calling Database.execute on the wrong thread"
self._pending_commits, pending_commits = 0, self._pending_commits
if exc_type is None:
logger.debug("enabling commit [%s]", self._file_path)
if pending_commits > 1:
logger.debug("performing %d pending commits [%s]", pending_commits - 1, self._file_path)
self.commit()
return True
elif isinstance(exc_value, IgnoreCommits):
logger.debug("enabling commit without committing now [%s]", self._file_path)
return True
else:
# Niels 23-01-2013, an exception happened from within the with database block
# returning False to let Python reraise the exception.
return False
@property
def last_insert_rowid(self):
"""
The row id of the most recent insert query.
@rtype: int or long
"""
assert self._cursor is not None, "Database.close() has been called or Database.open() has not been called"
assert self._connection is not None, "Database.close() has been called or Database.open() has not been called"
assert self._debug_thread_ident != 0, "please call database.open() first"
assert self._debug_thread_ident == thread.get_ident(), "Calling Database.execute on the wrong thread"
assert not self._cursor.lastrowid is None, "The last statement was NOT an insert query"
return self._cursor.lastrowid
@property
def changes(self):
"""
The number of changes that resulted from the most recent query.
@rtype: int or long
"""
assert self._cursor is not None, "Database.close() has been called or Database.open() has not been called"
assert self._connection is not None, "Database.close() has been called or Database.open() has not been called"
assert self._debug_thread_ident != 0, "please call database.open() first"
assert self._debug_thread_ident == thread.get_ident(), "Calling Database.execute on the wrong thread"
return self._cursor.rowcount
@attach_explain_query_plan
@attach_runtime_statistics("{0.__class__.__name__}.{function_name} {1} [{0.file_path}]")
def execute(self, statement, bindings=()):
"""
Execute one SQL statement.
A SQL query must be presented in unicode format. This is to ensure that no unicode
exeptions occur when the bindings are merged into the statement.
Furthermore, the bindings may not contain any strings either. For a 'string' the unicode
type must be used. For a binary string the buffer(...) type must be used.
The SQL query may contain placeholder entries defined with a '?'. Each of these
placeholders will be used to store one value from bindings. The placeholders are filled by
sqlite and all proper escaping is done, making this the preferred way of adding variables to
the SQL query.
@param statement: the SQL statement that is to be executed.
@type statement: unicode
@param bindings: the values that must be set to the placeholders in statement.
@type bindings: list, tuple, dict, or set
@returns: unknown
@raise sqlite.Error: unknown
"""
if __debug__:
assert self._cursor is not None, "Database.close() has been called or Database.open() has not been called"
assert (
self._connection is not None
), "Database.close() has been called or Database.open() has not been called"
assert self._debug_thread_ident != 0, "please call database.open() first"
assert self._debug_thread_ident == thread.get_ident(), "Calling Database.execute on the wrong thread"
assert isinstance(statement, unicode), "The SQL statement must be given in unicode"
assert isinstance(
bindings, (tuple, list, dict, set)
), "The bindings must be a tuple, list, dictionary, or set"
# bindings may not be strings, text must be given as unicode strings while binary data,
# i.e. blobs, must be given as a buffer(...)
if isinstance(bindings, dict):
tests = (not isinstance(binding, str) for binding in bindings.itervalues())
else:
tests = (not isinstance(binding, str) for binding in bindings)
assert all(tests), "Bindings may not be strings. Provide unicode for TEXT and buffer(...) for BLOB\n%s" % (
statement,
)
logger.log(logging.NOTSET, "%s <-- %s [%s]", statement, bindings, self._file_path)
return self._cursor.execute(statement, bindings)
@attach_runtime_statistics("{0.__class__.__name__}.{function_name} {1} [{0.file_path}]")
def executescript(self, statements):
assert self._cursor is not None, "Database.close() has been called or Database.open() has not been called"
assert self._connection is not None, "Database.close() has been called or Database.open() has not been called"
assert self._debug_thread_ident != 0, "please call database.open() first"
assert self._debug_thread_ident == thread.get_ident(), "Calling Database.execute on the wrong thread"
assert isinstance(statements, unicode), "The SQL statement must be given in unicode"
logger.log(logging.NOTSET, "%s [%s]", statements, self._file_path)
return self._cursor.executescript(statements)
@attach_explain_query_plan
@attach_runtime_statistics("{0.__class__.__name__}.{function_name} {1} [{0.file_path}]")
def executemany(self, statement, sequenceofbindings):
"""
Execute one SQL statement several times.
All SQL queries must be presented in unicode format. This is to ensure that no unicode
exeptions occur when the bindings are merged into the statement.
Furthermore, the bindings may not contain any strings either. For a 'string' the unicode
type must be used. For a binary string the buffer(...) type must be used.
The SQL query may contain placeholder entries defined with a '?'. Each of these
placeholders will be used to store one value from bindings. The placeholders are filled by
sqlite and all proper escaping is done, making this the preferred way of adding variables to
the SQL query.
@param statement: the SQL statement that is to be executed.
@type statement: unicode
@param sequenceofbindings: a list, tuple, set, or generator of bindings, where every binding
contains the values that must be set to the placeholders in
statement.
@type sequenceofbindings: list, tuple, set or generator
@returns: unknown
@raise sqlite.Error: unknown
"""
assert self._cursor is not None, "Database.close() has been called or Database.open() has not been called"
assert self._connection is not None, "Database.close() has been called or Database.open() has not been called"
assert self._debug_thread_ident != 0, "please call database.open() first"
assert self._debug_thread_ident == thread.get_ident(), "Calling Database.execute on the wrong thread"
if __debug__:
# we allow GeneratorType but must convert it to a list in __debug__ mode since a
# generator can only iterate once
from types import GeneratorType
is_iterator = isinstance(sequenceofbindings, GeneratorType)
if is_iterator:
sequenceofbindings = list(sequenceofbindings)
assert isinstance(statement, unicode), "The SQL statement must be given in unicode"
assert isinstance(
sequenceofbindings, (tuple, list, set)
), "The sequenceofbindings must be a tuple, list, or set"
assert all(
isinstance(x, (tuple, list, dict, set)) for x in sequenceofbindings
), "The sequenceofbindings must be a list with tuples, lists, dictionaries, or sets"
for bindings in sequenceofbindings:
# bindings may not be strings, text must be given as unicode strings while binary data,
# i.e. blobs, must be given as a buffer(...)
if isinstance(bindings, dict):
tests = (not isinstance(binding, str) for binding in bindings.itervalues())
else:
tests = (not isinstance(binding, str) for binding in bindings)
assert all(
tests
), "Bindings may not be strings. Provide unicode for TEXT and buffer(...) for BLOB\n%s" % (statement,)
if is_iterator:
sequenceofbindings = iter(sequenceofbindings)
logger.log(logging.NOTSET, "%s [%s]", statement, self._file_path)
return self._cursor.executemany(statement, sequenceofbindings)
@attach_runtime_statistics("{0.__class__.__name__}.{function_name} [{0.file_path}]")
def commit(self, exiting=False):
assert self._cursor is not None, "Database.close() has been called or Database.open() has not been called"
assert self._connection is not None, "Database.close() has been called or Database.open() has not been called"
assert self._debug_thread_ident != 0, "please call database.open() first"
assert self._debug_thread_ident == thread.get_ident(), "Calling Database.commit on the wrong thread"
assert not (exiting and self._pending_commits), "No pending commits should be present when exiting"
if self._pending_commits:
logger.debug("defer commit [%s]", self._file_path)
self._pending_commits += 1
return False
else:
logger.debug("commit [%s]", self._file_path)
for callback in self._commit_callbacks:
try:
callback(exiting=exiting)
except Exception as exception:
logger.exception("%s [%s]", exception, self._file_path)
return self._connection.commit()
@abstractmethod
def check_database(self, database_version):
"""
Check the database and upgrade if required.
This method is called once for each Database instance to ensure that the database structure
and version is correct. Each Database must contain one table of the structure below where
the database_version is stored. This value is used to keep track of the current database
version.
>>> CREATE TABLE option(key TEXT PRIMARY KEY, value BLOB);
>>> INSERT INTO option(key, value) VALUES('database_version', '1');
@param database_version: the current database_version value from the option table. This
value reverts to u'0' when the table could not be accessed.
@type database_version: unicode
"""
pass
def attach_commit_callback(self, func):
assert not func in self._commit_callbacks
self._commit_callbacks.append(func)
def detach_commit_callback(self, func):
assert func in self._commit_callbacks
self._commit_callbacks.remove(func)
def dbdropall(conn: sqlite3.Connection) -> None:
logger.info("尝试删除全部")
drop_sql = 'update `illustJsonMsg` set `time`=-1 where `time`!=-1;'
conn.execute(drop_sql)
conn.commit()
logger.info("删除全部成功")
def delete_movie(connection: Connection, movie: Movie):
connection.execute(
'''UPDATE MOVIES
SET deleted = 1
WHERE movie_id = ?''', movie.movie_id)
connection.commit()
def close_db(connection: sqlite3.Connection):
connection.commit()
connection.close()
def insert_pictures(connection: sqlite3.Connection,
cursor: sqlite3.Connection.cursor):
# Get number of pictures
cursor.execute("SELECT COUNT(*) FROM camera.pictures")
all_rows = cursor.fetchall()
num_pics = all_rows[0][0]
# Insert all pictures
cursor.execute("SELECT * FROM camera.pictures")
all_rows = cursor.fetchall()
picture_count = 0
count17 = 0
count18 = 0
count19 = 0
count20 = 0
old_id = 0
new_id = 0
for row in all_rows:
# print(row)
if row[2] == 17:
old_id = 17
new_id = 30
count17 += 1
elif row[2] == 18:
old_id = 18
new_id = 31
count18 += 1
elif row[2] == 19:
old_id = 19
new_id = 32
count19 += 1
elif row[2] == 20:
old_id = 20
new_id = 33
count20 += 1
path1 = change_cam_location(row[4], old_id, new_id)
path2 = change_cam_location(row[5], old_id, new_id)
path3 = change_cam_location(row[6], old_id, new_id)
statement = "INSERT INTO pictures (time, altitude, hike, index_in_hike, camera1, camera2, camera3, created_date_time, updated_date_time) VALUES ({t}, {a}, {h}, {i}, '{c1}', '{c2}', '{c3}', '{t1}', '{t2}')".format(
t=row[0],
a=row[1],
h=new_id,
i=row[3],
c1=path1,
c2=path2,
c3=path3,
t1=row[7],
t2=row[8])
cursor.execute(statement)
connection.commit()
picture_count += 1
print('Finished inserting {i} of {n} pictures from camera'.format(
i=picture_count, n=num_pics))
print(
'HIKE17 : {c17} | HIKE18 : {c18} | HIKE19 : {c19} | HIKE20 : {c20}'
.format(c17=count17, c18=count18, c19=count19, c20=count20))
def commit(conn: sqlite3.Connection):
conn.commit()
def close_db(connection: sqlite3.Connection):
connection.commit() # make sure any changes get saved
connection.close()
def serialise_locus(stranded_loci: [Superlocus],
conn: sqlite3.Connection,
counter,
print_subloci=True,
print_cds=True,
print_monosubloci=True):
loci = []
subloci = []
monoloci = []
for stranded_locus in sorted(stranded_loci):
loci.append([
json.dumps(locus.as_dict(), default=default_for_serialisation)
for locus in stranded_locus.loci.values()
])
if print_subloci is True:
batch = []
batch.append(
stranded_locus.__str__(level="subloci", print_cds=print_cds))
sub_metrics_rows = [
_ for _ in stranded_locus.print_subloci_metrics()
if _ != {} and "tid" in _
]
sub_scores_rows = [
_ for _ in stranded_locus.print_subloci_scores()
if _ != {} and "tid" in _
]
batch.append(sub_metrics_rows)
batch.append(sub_scores_rows)
subloci.append(batch)
if print_monosubloci is True:
batch = []
batch.append(
stranded_locus.__str__(level="monosubloci",
print_cds=print_cds))
mono_metrics_rows = [
_ for _ in stranded_locus.print_monoholder_metrics()
if _ is not None and _ != {} and "tid" in _
]
mono_scores_rows = [
_ for _ in stranded_locus.print_monoholder_scores()
if _ is not None and _ != {} and "tid" in _
]
batch.append(mono_metrics_rows)
batch.append(mono_scores_rows)
monoloci.append(batch)
loci = msgpack.dumps(loci)
try:
subloci = msgpack.dumps(json.dumps(subloci,
number_mode=json.NM_NATIVE))
monoloci = msgpack.dumps(
json.dumps(monoloci, number_mode=json.NM_NATIVE))
except ValueError:
subloci = msgpack.dumps(subloci)
monoloci = msgpack.dumps(monoloci)
if not stranded_loci:
chrom = ""
num_genes = 0
else:
chrom = stranded_loci[0].chrom
num_genes = sum(len(slid.loci) for slid in stranded_loci)
conn.execute("INSERT INTO loci VALUES (?, ?, ?, ?)",
(counter, chrom, num_genes, loci))
conn.execute("INSERT INTO subloci VALUES (?, ?, ?)",
(counter, chrom, subloci))
conn.execute("INSERT INTO monoloci VALUES (?, ?, ?)",
(counter, chrom, monoloci))
conn.commit()
return
def offer(self, connection: Connection) -> None:
connection.commit()
self.connection_pool.put_nowait(connection)
def __init__(self, db: sqlite3.Connection):
self.db: sqlite3.Connection = db
self._create_tables()
db.commit()
def doneDb(conn: sqlite3.Connection):
print('Creating indexes and FTS...')
sql = '''
-- Removing duplicates
CREATE INDEX idx_xxx_id ON books_temp(book_id);
DELETE FROM books_temp
WHERE rowid NOT IN(SELECT MAX(rowid)
FROM books_temp
GROUP BY book_id);
CREATE INDEX idx_xx2_id ON genre_to_book_temp(book_id, genre_id);
DELETE FROM genre_to_book_temp
WHERE rowid NOT IN(SELECT MAX(rowid)
FROM genre_to_book_temp
GROUP BY book_id, genre_id);
CREATE INDEX idx_xx3_id ON author_to_book_temp(book_id, author_id);
DELETE FROM author_to_book_temp
WHERE rowid NOT IN(SELECT MAX(rowid)
FROM author_to_book_temp
GROUP BY book_id, author_id);
CREATE INDEX idx_xx4_id ON serie_to_book_temp(book_id, serie_id);
DELETE FROM serie_to_book_temp
WHERE rowid NOT IN(SELECT MAX(rowid)
FROM serie_to_book_temp
GROUP BY book_id, serie_id);
CREATE INDEX idx_xx5_id ON keyword_to_book_temp(book_id, keyword_id);
DELETE FROM keyword_to_book_temp
WHERE rowid NOT IN(SELECT MAX(rowid)
FROM keyword_to_book_temp
GROUP BY book_id, keyword_id);
INSERT INTO series SELECT * FROM series_temp;
INSERT INTO genres SELECT * FROM genres_temp;
INSERT INTO authors
SELECT
a.*,
(a.first_name || ' ' || a.middle_name || ' ' || a.last_name || ' ' || a.nickname) as name
FROM authors_temp a;
INSERT INTO keywords SELECT * FROM keywords_temp;
INSERT INTO genre_to_book SELECT * FROM genre_to_book_temp;
INSERT INTO author_to_book SELECT * FROM author_to_book_temp;
INSERT INTO serie_to_book SELECT * FROM serie_to_book_temp;
INSERT INTO keyword_to_book SELECT * FROM keyword_to_book_temp;
PRAGMA foreign_keys = ON;
DELETE FROM authors WHERE name = '' OR name = ' ';
DELETE FROM genres WHERE name = '';
DELETE FROM series WHERE name = '';
DELETE FROM keywords WHERE name = '';
CREATE INDEX serieBook_book_idx ON serie_to_book(book_id);
CREATE INDEX serieBook_serie_idx ON serie_to_book(serie_id);
CREATE VIRTUAL TABLE series_fts USING fts4(name, tokenize=unicode61);
INSERT INTO series_fts SELECT name FROM series;
CREATE INDEX authorBook_book_idx ON author_to_book(book_id);
CREATE INDEX authorBook_author_idx ON author_to_book(author_id);
CREATE VIRTUAL TABLE authors_fts USING fts4(name, tokenize=unicode61);
INSERT INTO authors_fts SELECT name FROM authors;
CREATE INDEX genreBook_book_idx ON genre_to_book(book_id);
CREATE INDEX genreBook_genre_idx ON genre_to_book(genre_id);
CREATE INDEX keywordBook_book_idx ON keyword_to_book(book_id);
CREATE INDEX keywordBook_keyword_idx ON keyword_to_book(keyword_id);
CREATE VIRTUAL TABLE keywords_fts USING fts4(name, tokenize=unicode61);
INSERT INTO keywords_fts SELECT name FROM keywords;
INSERT INTO books
SELECT b.*,
NULL as authors,
NULL as series,
NULL AS genres,
NULL as keywords
FROM books_temp b;
UPDATE books
SET authors = (
SELECT group_concat(a.author_id || '###' || a.name, '$$$')
FROM authors a,
author_to_book ab
WHERE a.author_id = ab.author_id
AND books.book_id = ab.book_id
);
UPDATE books
SET series = (
SELECT group_concat(s.serie_id || '###' || s.name || '###' || sb.serno, '$$$')
FROM series s,
serie_to_book sb
WHERE s.serie_id = sb.serie_id
AND books.book_id = sb.book_id
);
UPDATE books
SET genres = (
SELECT group_concat(g.genre_id || '###' || g.name, '$$$')
FROM genres g,
genre_to_book gb
WHERE g.genre_id = gb.genre_id
AND books.book_id = gb.book_id
);
UPDATE books
SET keywords = (
SELECT group_concat(kw.keyword_id || '###' || kw.name, '$$$')
FROM keywords kw,
keyword_to_book kwb
WHERE kw.keyword_id = kwb.keyword_id
AND books.book_id = kwb.book_id
);
CREATE INDEX books_title_idx ON books(title);
CREATE INDEX books_title_alt_idx ON books(title_alt);
CREATE VIRTUAL TABLE titles_fts USING fts4(title, tokenize=unicode61);
INSERT INTO titles_fts SELECT title FROM books;
CREATE VIRTUAL TABLE titles_alt_fts USING fts4(title, tokenize=unicode61);
INSERT INTO titles_alt_fts SELECT title_alt FROM books;
--DROP TABLE author_to_book_temp;
--DROP TABLE books_temp;
--DROP TABLE genre_to_book_temp;
--DROP TABLE genres_temp;
--DROP TABLE series_temp;
--DROP TABLE authors_temp;
--DROP TABLE serie_to_book_temp;
--DROP TABLE keywords_temp;
--DROP TABLE keyword_to_book_temp;
--VACUUM;
'''
# conn.executescript(sql)
for part in sql.split(';'):
print(part)
conn.executescript(part + ';')
conn.commit()
print('Done!')
def close_db(conn: sqlite3.Connection):
conn.commit() # Save changes
conn.close()
class SimpleBot(Client):
def on_connected(self):
self.init_db()
self.authorized_users = list()
self.command_prefix = "*"
for channel in self.config['channels']:
self.join(channel)
self.aliases = {
"telka": ["телка", "телочка"]
}
def on_disconnected(self):
if self.db:
self.db.close()
def init_db(self):
self.db = Connection(self.config['database'])
self.db.execute("""
CREATE TABLE IF NOT EXISTS
message_log (
id INTEGER PRIMARY KEY ASC,
channel TEXT,
nick TEXT,
ident TEXT,
host TEXT,
message TEXT,
date INTEGER
)
""")
self.db.execute("""
CREATE TABLE IF NOT EXISTS
social_telki (
id INTEGER PRIMARY KEY ASC,
rating INTEGER,
displayed_times INNTEGER,
url TEXT,
who_added TEXT,
date_added INTEGER
)
""")
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
self.db.row_factory = dict_factory
def get_unix_timestamp(self):
return int(time.mktime(datetime.datetime.now().timetuple()))
def log_message(self, nick_name, ident, host_name, message, channel):
self.db.execute("""
INSERT INTO message_log (channel, nick, ident, host, message, date)
VALUES (?,?,?,?,?,?)
""", (channel, nick_name, ident, host_name, message, self.get_unix_timestamp()))
self.db.commit()
def on_private(self, nick, ident, host, message):
print u"pm from:%s: %s" % (nick, message)
def on_channel(self, nick, ident, host, message, channel):
print u"on %s from %s: %s" % (channel, nick, message)
def on_privmsg(self, nick, ident, host, params, trailing):
channel = None
message = trailing
if params == self.config['nick']:
self.on_private(nick, ident, host, message)
else:
channel = params.decode('utf-8')
self.on_channel(nick, ident, host, message, channel)
self.log_message(nick.decode('utf-8'), ident.decode('utf-8'), host.decode('utf-8'), message.decode('utf-8'), channel)
if message.startswith(self.command_prefix):
self.handle_command(nick, ident, host, message[len(self.command_prefix):], channel)
def on_nick(self, old_nick, ident, host, params, new_nick):
if old_nick == self.nick_name:
self.print_debug("Yay! My name changed to: %s" % new_nick)
self.nick_name = new_nick
def is_authorized(self, nick, ident, host):
for login_data in self.authorized_users:
if login_data[0] == nick and login_data[1] == ident and login_data[2] == host:
return True
return False
def authorize(self, nick, ident, host, password):
for bot_oper in self.config['allowed_ops']:
if bot_oper['nick'] == nick and bot_oper['password'] == password:
self.authorized_users.append((nick, ident, host))
return True
def handle_command(self, nick, ident, host, command, channel):
args = None
if " " in command:
command, args = command.split(" ", 1)
command_name = "cmd_%s" % command.lower()
try:
meth = getattr(self, command_name, None)
if meth is None:
for cmdname, aliases in self.aliases.iteritems():
for alias in aliases:
if command == alias:
command_name = "cmd_%s" % cmdname.lower()
meth = getattr(self, command_name)
break
if meth:
meth(nick, ident, host, channel, args)
ameth = getattr(self, "a%s" % command_name, None)
if ameth is not None:
if self.is_authorized(nick, ident, host):
ameth(nick, ident, host, channel, args)
return
else:
self.say_to(nick, "Nope!")
except Exception as e:
print(e.message)
self.say_to(nick, "Some error occurred while your command being executed :[")
def cmd_auth(self, nick, ident, host, channel, args):
if channel:
self.say_to(nick, 'Noob :D', channel)
if not self.is_authorized(nick, ident, host):
if self.authorize(nick, ident, host, args):
self.say_to(nick, "Auth successed!")
else:
self.say_to(nick, "Auth failed!")
else:
self.say_to(nick, "You're already authorized!")
def acmd_die(self, nick, ident, host, channel, args):
self.say_to(nick, "Quitting....")
self._shutdown_pending = True
def acmd_join(self, nick, ident, host, channel, args):
self.join(args)
def acmd_part(self, nick, ident, host, channel, args):
self.part(args)
def cmd_lastlog(self, nick, ident, host, channel, args):
known_args = ["nick", "channel", "message", "date", "limit"]
if not args:
self.say_to(nick, "Known args are: %s" % ", ".join(known_args), channel)
return
arg_dict = dict()
if args:
arg_pairs = args.split(" ")
wrong_attrs = list()
for p in arg_pairs:
if "=" in p:
k,v = p.split("=")
if k in known_args:
arg_dict[k] = v
else:
wrong_attrs.append(k)
else:
wrong_attrs.append(p)
if wrong_attrs:
self.say_to(nick, "Wrong or unknown attributes: %s" % ",".join(wrong_attrs), channel)
if not arg_dict:
return
has_limit = 30
if "limit" in arg_dict.keys():
has_limit = arg_dict.pop("limit")
query = [
"SELECT * FROM ("
"SELECT id, channel, nick, ident, host, message, date FROM message_log"
]
if arg_dict:
query.append("WHERE")
query_conditions = list()
placeholders = list()
parsed = parse_data(arg_dict)
#print parsed
for attr, value in parsed:
query_conditions.append("%s %s"% (attr, value[0]))
placeholders.append(value[1])
query.append(" AND ".join(query_conditions))
query.append("LIMIT ?")
placeholders.append(has_limit)
query.append(") ORDER BY id ASC")
self.print_debug("querying: %s" % " ".join(query))
cur = self.db.execute(" ".join(query), placeholders)
message = ["chan\tnick\tmessage"]
for row in cur.fetchall():
datime = datetime.datetime.fromtimestamp(row['date'])
row['date'] = datime.strftime("%Y.%m.%d %H:%M:%S")
message.append("%(date)s\t%(channel)s\t%(nick)s\t%(message)s" % row)
if not message:
self.say_to(nick, "Nothing found :[")
return
resp = requests.post("https://clbin.com", dict(clbin="\n".join(message)))
if resp.status_code == 200:
self.say_to(nick, resp.text, channel)
else:
self.say_to(nick, "Post failed. Code %d" % resp.status_code,channel)
def acmd_nick(self, nick, ident, host, channel, args):
self.sendMessage("NICK :%s" % args)
def acmd_telki_loadf(self, nick, ident, host, channel, args):
self.print_debug("Loading telki from plain file %s" % args)
duplicated_rows = 0
added_rows = 0
processed_rows = 0
incorrect_urls = 0
if os.path.exists(args):
args = open(args)
elif "," in args:
args = args.split(",")
elif args.startswith("http"):
args = [args]
for line in args:
clean_url = line.strip()
if not clean_url.startswith("http"):
incorrect_urls +=1
continue
cur = self.db.execute("SELECT COUNT(*) count FROM social_telki WHERE URL=?", (clean_url, ))
count = cur.fetchone()
if int(count['count']) <=0:
self.db.execute("""INSERT INTO social_telki (
rating,
displayed_times,
url,
who_added,
date_added
)
VALUES
(?,?,?,?,?)""", (0, 0, clean_url, nick, self.get_unix_timestamp()))
added_rows +=1
else:
duplicated_rows += 1
processed_rows+=1
self.db.commit()
self.say_to(nick, "File loaded; total rows: %d; added: %s; doubles ignored: %s; incorrect urls: %d" % (processed_rows, added_rows, duplicated_rows, incorrect_urls), channel)
def cmd_telka(self, nick, ident, host, channel, args):
cur = self.db.execute("""
SELECT id, url FROM social_telki
WHERE displayed_times<= (SELECT MIN(displayed_times) FROM social_telki)
ORDER BY RANDOM()
LIMIT 1
""")
row = cur.fetchone()
if row:
cur.execute("UPDATE social_telki SET displayed_times=displayed_times+1 WHERE id=?", (row['id'], ))
url = row['url']
resp = requests.get(url)
if resp.status_code == 200:
self.say_to(args or nick, row['url'], channel)
self.db.commit()
else:
self.say_to(nick, "Еще разок попробуй, ссыль битая :(", channel)
self.db.rollback()
return
self.say_to(nick, "Nothing found O_o", channel)
def check_sql_database(conn: sqlite3.Connection):
"""Initializes and/or updates the database to the current version"""
# Database file is automatically created with connect, now we have to check if it has tables
log.info("Checking sqlite database version...")
c = conn.cursor()
try:
c.execute("SELECT COUNT(*) as count FROM sqlite_master WHERE type = 'table'")
result = c.fetchone()
# Database is empty
if result[0] == 0:
log.warning("\tDatabase is empty.")
return False
c.execute("SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND name LIKE 'db_info'")
result = c.fetchone()
# If there's no version value, version 1 is assumed
if result is None:
c.execute("""CREATE TABLE db_info (
key TEXT,
value TEXT
)""")
c.execute("INSERT INTO db_info(key,value) VALUES('version','1')")
db_version = 1
log.warning("\tNo version found, version 1 assumed")
else:
c.execute("SELECT value FROM db_info WHERE key LIKE 'version'")
db_version = int(c.fetchone()[0])
log.info("\tVersion {0}".format(db_version))
if db_version == SQL_DB_LASTVERSION:
log.info("\tDatabase is up to date.")
return True
# Code to patch database changes
if db_version == 1:
# Added 'vocation' column to chars table, to display vocations when /check'ing users among other things.
# Changed how the last_level flagging system works a little, a character of unknown level is now flagged as
# level 0 instead of -1, negative levels are now used to flag of characters never seen online before.
c.execute("ALTER TABLE chars ADD vocation TEXT")
c.execute("UPDATE chars SET last_level = 0 WHERE last_level = -1")
db_version += 1
if db_version == 2:
# Added 'events' table
c.execute("""CREATE TABLE events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
creator INTEGER,
name TEXT,
start INTEGER,
duration INTEGER,
active INTEGER DEFAULT 1
)""")
db_version += 1
if db_version == 3:
# Added 'char_deaths' table
# Added 'status column' to events (for event announces)
c.execute("""CREATE TABLE char_deaths (
char_id INTEGER,
level INTEGER,
killer TEXT,
date INTEGER,
byplayer BOOLEAN
)""")
c.execute("ALTER TABLE events ADD COLUMN status INTEGER DEFAULT 4")
db_version += 1
if db_version == 4:
# Added 'name' column to 'discord_users' table to save their names for external use
c.execute("ALTER TABLE discord_users ADD name TEXT")
db_version += 1
if db_version == 5:
# Added 'world' column to 'chars', renamed 'discord_users' to 'users', created table 'user_servers'
c.execute("ALTER TABLE chars ADD world TEXT")
c.execute("ALTER TABLE discord_users RENAME TO users")
c.execute("""CREATE TABLE user_servers (
id INTEGER,
server INTEGER,
PRIMARY KEY(id)
);""")
db_version += 1
if db_version == 6:
# Added 'description', 'server' column to 'events', created table 'events_subscribers'
c.execute("ALTER TABLE events ADD description TEXT")
c.execute("ALTER TABLE events ADD server INTEGER")
c.execute("""CREATE TABLE event_subscribers (
event_id INTEGER,
user_id INTEGER
);""")
db_version += 1
if db_version == 7:
# Created 'server_properties' table
c.execute("""CREATE TABLE server_properties (
server_id INTEGER,
name TEXT,
value TEXT
);""")
db_version += 1
if db_version == 8:
# Added 'achievements', 'axe', 'club', 'distance', 'fishing', 'fist', 'loyalty', 'magic', 'shielding',
# 'sword', 'achievements_rank', 'axe_rank', 'club_rank', 'distance_rank', 'fishing_rank', 'fist_rank',
# 'loyalty_rank', 'magic_rank', 'shielding_rank', 'sword_rank', columns to 'chars'
c.execute("ALTER TABLE chars ADD achievements INTEGER")
c.execute("ALTER TABLE chars ADD axe INTEGER")
c.execute("ALTER TABLE chars ADD club INTEGER")
c.execute("ALTER TABLE chars ADD distance INTEGER")
c.execute("ALTER TABLE chars ADD fishing INTEGER")
c.execute("ALTER TABLE chars ADD fist INTEGER")
c.execute("ALTER TABLE chars ADD loyalty INTEGER")
c.execute("ALTER TABLE chars ADD magic INTEGER")
c.execute("ALTER TABLE chars ADD shielding INTEGER")
c.execute("ALTER TABLE chars ADD sword INTEGER")
c.execute("ALTER TABLE chars ADD achievements_rank INTEGER")
c.execute("ALTER TABLE chars ADD axe_rank INTEGER")
c.execute("ALTER TABLE chars ADD club_rank INTEGER")
c.execute("ALTER TABLE chars ADD distance_rank INTEGER")
c.execute("ALTER TABLE chars ADD fishing_rank INTEGER")
c.execute("ALTER TABLE chars ADD fist_rank INTEGER")
c.execute("ALTER TABLE chars ADD loyalty_rank INTEGER")
c.execute("ALTER TABLE chars ADD magic_rank INTEGER")
c.execute("ALTER TABLE chars ADD shielding_rank INTEGER")
c.execute("ALTER TABLE chars ADD sword_rank INTEGER")
db_version += 1
if db_version == 9:
# Added 'magic_ek', 'magic_rp', 'magic_ek_rank', 'magic_rp_rank' columns to 'chars'
c.execute("ALTER TABLE chars ADD magic_ek INTEGER")
c.execute("ALTER TABLE chars ADD magic_rp INTEGER")
c.execute("ALTER TABLE chars ADD magic_ek_rank INTEGER")
c.execute("ALTER TABLE chars ADD magic_rp_rank INTEGER")
db_version += 1
if db_version == 10:
# Added 'guild' column to 'chars'
c.execute("ALTER TABLE chars ADD guild TEXT")
db_version += 1
if db_version == 11:
# Added 'deleted' column to 'chars'
c.execute("ALTER TABLE chars ADD deleted INTEGER DEFAULT 0")
db_version += 1
if db_version == 12:
# Added 'hunted' table
c.execute("""CREATE TABLE hunted_list (
name TEXT,
is_guild BOOLEAN DEFAULT 0,
server_id INTEGER
);""")
db_version += 1
if db_version == 13:
# Renamed table hunted_list to watched_list and related server properties
c.execute("ALTER TABLE hunted_list RENAME TO watched_list")
c.execute("UPDATE server_properties SET name = 'watched_channel' WHERE name LIKE 'hunted_channel'")
c.execute("UPDATE server_properties SET name = 'watched_message' WHERE name LIKE 'hunted_message'")
db_version += 1
if db_version == 14:
c.execute("""CREATE TABLE ignored_channels (
server_id INTEGER,
channel_id INTEGER
);""")
db_version += 1
if db_version == 15:
c.execute("""CREATE TABLE highscores (
rank INTEGER,
category TEXT,
world TEXT,
name TEXT,
vocation TEXT,
value INTEGER
);""")
c.execute("""CREATE TABLE highscores_times (
world TEXT,
last_scan INTEGER
);""")
db_version += 1
if db_version == 16:
c.execute("ALTER table highscores_times ADD category TEXT")
db_version += 1
if db_version == 17:
# Cleaning up unused columns and renaming columns
c.execute("""CREATE TABLE chars_temp(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
name TEXT,
level INTEGER DEFAULT -1,
vocation TEXT,
world TEXT,
guild TEXT
);""")
c.execute("INSERT INTO chars_temp SELECT id, user_id, name, last_level, vocation, world, guild FROM chars")
c.execute("DROP TABLE chars")
c.execute("ALTER table chars_temp RENAME TO chars")
c.execute("DROP TABLE IF EXISTS user_servers")
c.execute("""CREATE TABLE users_temp(
id INTEGER NOT NULL,
name TEXT,
PRIMARY KEY(id)
);""")
c.execute("INSERT INTO users_temp SELECT id, name FROM users")
c.execute("DROP TABLE users")
c.execute("ALTER table users_temp RENAME TO users")
db_version += 1
if db_version == 18:
# Adding event participants
c.execute("ALTER TABLE events ADD joinable INTEGER DEFAULT 1")
c.execute("ALTER TABLE events ADD slots INTEGER DEFAULT 0")
c.execute("""CREATE TABLE event_participants(
event_id INTEGER NOT NULL,
char_id INTEGER NOT NULL
);""")
db_version += 1
if db_version == 19:
# Adding reason and author to watched-list
c.execute("ALTER TABLE watched_list ADD reason TEXT")
c.execute("ALTER TABLE watched_list ADD author INTEGER")
c.execute("ALTER TABLE watched_list ADD added INTEGER")
db_version += 1
if db_version == 20:
# Joinable ranks
c.execute("""CREATE TABLE joinable_roles(
server_id INTEGER NOT NULL,
role_id INTEGER NOT NULL
);""")
db_version += 1
if db_version == 21:
# Autoroles
c.execute("""CREATE TABLE auto_roles(
server_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
guild TEXT NOT NULL
);""")
db_version += 1
log.info("\tUpdated database to version {0}".format(db_version))
c.execute("UPDATE db_info SET value = ? WHERE key LIKE 'version'", (db_version,))
return True
except Exception as e:
log.error(f"\tError reading sqlite database: {e}")
return False
finally:
c.close()
conn.commit()
def write_big_table_data(data: json, db: Connection):
insert = f"INSERT INTO big_table VALUES ({ ('?,' * len(big_table_fmt))[:-1]})"
db.execute(insert, data)
db.commit()
def close_conn(conn: sql.Connection):
"""
Closes the given database connection.
"""
conn.commit()
conn.close()
