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 _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()
def prepareSqlite(out,featureClass,fileType,includeGeometry):
[shp,shpType]=getShp(featureClass)
if shpType == "point":
gType = 1
elif shpType == "multipoint":
gType = 4
elif shpType == "polyline":
gType = 5
elif shpType == "polygon":
gType = 6
fields=listFields(featureClass)
fieldNames = []
fieldNames.append("OGC_FID INTEGER PRIMARY KEY")
if includeGeometry:
fieldNames.append("GEOMETRY blob")
for field in fields:
if (fields[field] != u'OID') and field.lower() !=shp.lower():
fieldNames.append(parseFieldType(field,fields[field]))
conn=Connection(out)
c=conn.cursor()
name = splitext(split(out)[1])[0]
c.execute("""CREATE TABLE geometry_columns ( f_table_name VARCHAR, f_geometry_column VARCHAR, geometry_type INTEGER, coord_dimension INTEGER, srid INTEGER, geometry_format VARCHAR )""")
c.execute("""insert into geometry_columns( f_table_name, f_geometry_column, geometry_type, coord_dimension, srid, geometry_format) values(?,?,?,?,?,?)""",(name,"GEOMETRY",gType,2,4326,"WKB"))
c.execute("""CREATE TABLE spatial_ref_sys ( srid INTEGER UNIQUE, auth_name TEXT, auth_srid TEXT, srtext TEXT)""")
c.execute("insert into spatial_ref_sys(srid ,auth_name ,auth_srid ,srtext) values(?,?,?,?)",(4326, u'EPSG', 4326, u'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]'))
c.execute("create table {0}({1})".format(name,", ".join(fieldNames)))
return [name,c,conn]
def __init__(self, ladder: str, dbh: sqlite3.Connection) -> None:
self.ladder = ladder
self.dbh = dbh
self.cursor = dbh.cursor()
self.players: Dict[str, Player] = {}
self.tsh: trueskill.TrueSkill = None
self.last_ranking = 0
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 import_catalog_into_db(catalog: Dict[str, CatalogEntry], connection: sqlite3.Connection):
cursor = connection.cursor()
for catalog_entry in catalog.values():
cursor.execute(
'insert into catalog (item_key, category_name) values (?, ?);',
[catalog_entry.item_key, catalog_entry.category_name]
)
def import_sales_into_db(sales, connection: sqlite3.Connection):
cursor = connection.cursor()
for sale in sales:
cursor.execute(
'insert into sales (item_key, country, city_name, sale_timestamp, price) values (?, ?, ?, ?, ?);',
[sale.item_key, sale.country, sale.city_name, sale.timestamp.isoformat(), str(sale.price)]
)
def load_max_id(db: sqlite3.Connection):
cur = db.cursor()
cur.execute('select min(id) from fav_tweets')
r = cur.fetchone()
db.rollback()
cur.close()
if r:
return r[0]
pass
def refresh_disable_proxies(conn: Connection):
"""
刷新失效的代理，失效一天以上就可删除
:param conn: 数据连接
:return:
"""
delete_sql = '''
DELETE FROM disable_proxies
WHERE CAST(strftime('%s', datetime('now', 'localtime')) - strftime('%s', insert_date) as INTEGER) > ?
'''
cur = conn.cursor()
cur.execute(delete_sql, (day_of_seconds,))
def insert_disable_proxy(conn: Connection, proxy: ()):
"""
写入失效的代理
:param conn: 数据连接
:param proxy: (代理)
:return:
"""
insert_sql = '''
INSERT OR IGNORE INTO disable_proxies (proxy, insert_date)
VALUES (?, datetime('now', 'localtime'))
'''
cur = conn.cursor()
cur.execute(insert_sql, proxy)
def sql_dict_insert(
data: Dict[str, Any], table: str, sql_connection: sqlite3.Connection
) -> None:
"""
Insert a dictionary into a sqlite table
:param data: Dict to insert
:param table: Table to insert to
:param sql_connection: SQL connection
"""
cursor = sql_connection.cursor()
columns = ", ".join(data.keys())
placeholders = ":" + ", :".join(data.keys())
query = "INSERT INTO " + table + " (%s) VALUES (%s)" % (columns, placeholders)
cursor.execute(query, data)
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 update_proxy_error(conn: Connection, proxy_list: []) -> []:
"""
批量更新代理信息表中代理错误次数
:param conn: 数据连接
:proxy_list: 代理列表[(代理１), (代理１), ... , (代理n)]
:return:
"""
update_sql = '''
UPDATE OR IGNORE proxy_list
SET error_count = error_count + 1
WHERE proxy = ?
'''
cur = conn.cursor()
cur.executemany(update_sql, proxy_list)
def update_tac_status(conn: Connection, tac_data: ()):
"""
更新tac的状态
:param conn: 数据连接
:param tac_data: (状态，tac）
状态：（2:不存在，3:查询错）
:return:
"""
update_sql = '''
UPDATE OR IGNORE tac_info
SET status = ? ,
query_count = query_count + 1 ,
update_date = datetime('now', 'localtime')
WHERE tac_id = ?
'''
cur = conn.cursor()
cur.execute(update_sql, tac_data)
def reload_activate_proxies(conn: Connection, data_list: []):
"""
载入活跃代理，载入前先清空数据
:param conn: 数据连接
:param data_list: 代理列表，格式为：[(proxy1, response_time1, test_time1, test_xp1),...,(proxyn, response_timen, test_timen, test_xpn)]
:return:
"""
delete_sql = '''
DELETE FROM activate_proxies
'''
insert_sql = '''
INSERT INTO activate_proxies (proxy, response_time, test_time, test_xp, insert_date)
VALUES (?, ?, ?, ?, datetime('now', 'localtime'))
'''
cur = conn.cursor()
cur.execute(delete_sql)
cur.executemany(insert_sql, data_list)
def add_indexes_station_mast(conn: sqlite3.Connection):
"""
Add index for station table
:param conn: DB Connection
:return: None
"""
cur = conn.cursor()
cmd = '''
CREATE INDEX station_idx
ON station
(
station
)
'''
cur.execute(cmd)
conn.commit()
def update_friendly(connection: sqlite3.Connection, player_id: int,
time: datetime.datetime = datetime.datetime.utcnow()) -> bool:
logger.debug(f'update_friendly: {player_id}, {time}')
query = "UPDATE friendly SET last_used = ? WHERE id = ?"
query_param = [time, player_id]
try:
cur = connection.cursor()
cur.execute(query, query_param)
connection.commit()
logger.debug(replace_char_list(query, query_param))
logger.debug(f'lastrowid: {cur.lastrowid}')
return True
except Error as e:
logger.error(f'There was an error updating last_used in friendly: {e}')
return False
def update_match_accepted(connection: sqlite3.Connection, match_id: int, accepted: int = 1) -> bool:
logger.debug(f'update_match_accepted: {match_id}, {accepted}')
query = "UPDATE matches SET accepted = ? WHERE id = ?"
query_param = [accepted, match_id]
try:
cur = connection.cursor()
cur.execute(query, query_param)
connection.commit()
logger.debug(replace_char_list(query, query_param))
logger.debug(f'lastrowid: {cur.lastrowid}')
return True
except Error as e:
logger.error(f'There was an error updating accepted in matches: {e}')
return False
def save(self, sqlite_con: Connection):
""" Save settings to the provided sqlite db. """
table_create = """
create table settings (
id text, created_at integer, rnn text, rnn_layers integer, char_rnn integer,
bidirectional integer, classes text[], vocab_size integer, msg_len integer,
context_dim integer, embed_dim integer, learn_rnn_init integer, context_mode text,
no_lowercase integer, filter_chars text, batch_size integer, epochs integer,
cuda integer, learning_rate real, optimizer text, loss_fn text, embed_dropout real,
context_dropout real, token_regex text, class_weights real[], gradient_clip real,
learn_class_weights integer
);
"""
crs = sqlite_con.cursor()
crs.execute('DROP TABLE IF EXISTS settings;')
crs.execute(table_create)
crs.execute(*self.to_sql_insert())
sqlite_con.commit()
def update_marble_count(connection: sqlite3.Connection, player_id: int, marbles: int) -> bool:
logger.debug(f'update_marble_count: {player_id}, {marbles}')
query = "UPDATE users SET marbles = ? WHERE id = ?"
query_param = [marbles, player_id]
try:
cur = connection.cursor()
cur.execute(query, query_param)
connection.commit()
logger.debug(replace_char_list(query, query_param))
logger.debug(f'lastrowid: {cur.lastrowid}')
return True
except Error as e:
logger.error(f'There was an error updating marbles in user: {e}')
return False
def update_tac_info(conn: Connection, tac_data: ()):
"""
更新tac的品牌，机型和状态
:param conn: 数据连接
:param tac_data: (品牌，机型，状态，tac）
状态：（0:正常）
:return:
"""
update_sql = '''
UPDATE OR IGNORE tac_info
SET brand = ? ,
model = ? ,
status = ? ,
query_count = query_count + 1 ,
update_date = datetime('now', 'localtime')
WHERE tac_id = ?
'''
cur = conn.cursor()
cur.execute(update_sql, tac_data)
def part_one(db: sqlite3.Connection) -> int:
"""
Example:
2-9 c: ccccccccc
"""
sql = """
WITH parsed AS (
SELECT
cast(substr(
password,
0,
instr(password, '-')
) as int) as low,
cast(substr(
password,
instr(password, '-') + 1,
instr(password, ' ') - instr(password, '-')
) as int) as high,
substr(
password,
instr(password, ':') -1,
1
) as letter,
trim(substr(
password,
instr(password, ':') + 1
)) as password
FROM passwords
),
occurences AS (
SELECT
(LENGTH(password) - LENGTH(REPLACE(password, letter, ''))) as counter,
*
FROM parsed
)
SELECT count(*)
FROM occurences
WHERE counter >= low AND counter <= high
"""
cursor = db.cursor()
cursor.execute(sql)
return cursor.fetchone()[0]
def export_database_to_csv(sql_connection: sqlite3.Connection,
column_names: List[str], output_directory: str):
"""Export the SQL database to a csv file.
Args:
sql_connection: The connection to the in-memory database.
column_names: The name of the columns to hold.
output_directory: The directory where to store the output files.
"""
# Fetch all the rows of the database (add the webGLJs canvas row to get the
# extensions)
columns = column_names + [RAW_CANVAS_COLUMNS[1]]
fetch_all_query = f'SELECT {", ".join(columns)} FROM {DB_NAME};'
logger.debug(f'Executing "{fetch_all_query}".')
fetch_all_cursor = sql_connection.cursor()
fetch_all_result = fetch_all_cursor.execute(fetch_all_query)
# Open the resulting csv file where to write each row
fp_csv_path = path.join(output_directory, FINGERPRINT_CSV_FILE)
logger.info(f'Saving the fingerprint database to {fp_csv_path}.')
with open(fp_csv_path, 'w+') as fp_csv_file:
fp_csv_writer = csv.writer(fp_csv_file)
# Replace some columns and write the headers
headers = copy(column_names)
for header_to_replace, replacement_header in REPLACE_COLUMN:
column_id = column_names.index(header_to_replace)
headers[column_id] = replacement_header
logger.debug(f'Replaced the header {header_to_replace} with '
f'{replacement_header}.')
fp_csv_writer.writerow(headers)
logger.debug(f'Headers: {headers}.')
# For each row of the database
for row in fetch_all_result:
# Export the list of webGL extensions
webgl_extensions = '§'.join(row[-1].split('§')[1:])
row = row[:-1] + (webgl_extensions, )
fp_csv_writer.writerow(row)
fetch_all_cursor.close()
def _ensure_schema(db: sqlite3.Connection) -> None:
cur = db.cursor()
ok = True
found = set()
cur.execute("SELECT type, name, sql from sqlite_master")
for type, name, sql in cur:
if type not in ("table", "index"):
continue
if name in SCHEMA:
if SCHEMA[name] != sql:
ok = False
break
else:
found.add(name)
if not ok or found < SCHEMA.keys():
for sql in reversed(list(DROP.values())):
cur.execute(sql)
for sql in SCHEMA.values():
cur.execute(sql)
def delete_database_table(connection:sqlite3.Connection,
table_name:str) -> None:
""" Функция удаляет таблицу из базе данных
аргументы:
connection -- соединение с базой данных
table_name -- название таблицы
"""
try:
if connection is None:
raise WrongDataBaseNameException('Неправильное название базы данных. Введите существующую')
if type(table_name) is not str:
raise TypeError('Название таблицы может быть только строковое')
cursor = connection.cursor()
cursor.execute(f"""DROP TABLE IF EXISTS {table_name.lower()}""")
connection.commit()
except sqlite3.Error as e:
print(e)
finally:
connection.close()
def select_col_names(connection: Connection, table_name):
"""Return column names for chosen table name
:param connection: SQLite Connection object
:param table_name: Name of table
:return: list
"""
if not isinstance(connection, Connection):
raise TypeError(
"Parameter 'connection' must be SQLite Connection object")
cursor = connection.cursor()
try:
cursor.execute(f"PRAGMA table_info({table_name});")
except Error as e:
raise e
fetch_cols = cursor.fetchall()
if not fetch_cols:
raise OperationalError(f"no such table: {table_name}")
else:
return [x[1] for x in fetch_cols]
def select_user_by_credentials(conn: sqlite3.Connection, username: str,
password: str) -> list:
"""Return user by user credentials
Args:
conn: connection object
username: user username
password: user password
Returns:
list: user
"""
cur = conn.cursor()
cur.execute("SELECT * FROM users WHERE username=? and password=?",
(username, password))
row = cur.fetchall()
print("login user is:")
print(type(row))
print(row)
return row
def create_or_update_table_and_cols_(conn: Connection, table_name: str, cols: List[str], print_time: bool = PRINT_TIME):
try:
if print_time:
print(f"{get_timestamp()}: SQL start writing table cols to {table_name}")
c = conn.cursor()
c.execute(f"create table if not exists {table_name} ({','.join(cols)})")
for col in cols:
try:
c.execute(f"alter table {table_name} add column {col}")
except sqlite3.Error as e:
if print_time:
print(f"{get_timestamp()}: {e}")
conn.commit()
c.close()
if print_time:
print(f"{get_timestamp()}: SQL end writing table cols to {table_name}")
except sqlite3.Error as e:
if print_time:
print(f"{get_timestamp()}: {e}")
raise e
def exist_time_in(conn: sqlite3.Connection, user_id: int, date: str) -> bool:
"""Checks if entry already exist in database
Args:
conn: connection object
user_id: user id
date: date of arrival
Returns:
bool: True if entry already exist otherwise False
"""
cur = conn.cursor()
cur.execute("SELECT count(*) FROM presence WHERE userId=? AND date=?", (
user_id,
date,
))
row = cur.fetchall()
if row[0][0] == 0:
return False
return True
def write_dict_(conn: Connection, table_name: str, d: dict, print_time: bool = PRINT_TIME):
try:
# Convert tensors to values we can write to SQL
for k, v in d.items():
if hasattr(v, 'dtype'):
d[k] = v.item()
if print_time:
print(f"{get_timestamp()}: SQL start writing rows to {table_name}")
c = conn.cursor()
c.execute(f"insert into {table_name} ({','.join(d.keys())}) values ({':' +', :'.join(d.keys())})", d)
conn.commit()
c.close()
if print_time:
print(f"{get_timestamp()}: SQL end writing rows to {table_name}")
return c.lastrowid # this is guaranteed to be the ID generated by the previous operation
except sqlite3.Error as e:
if print_time:
print(f"{get_timestamp()}: {e}")
raise e
def get_current_sail_config(conn: sqlite3.Connection) -> dict:
c = conn.cursor()
sql = """SELECT value
FROM telemetry
where variable_name = 'sailConfig'
AND timestamp = (
SELECT timestamp
FROM telemetry
WHERE variable_name = 'sailConfig'
order by timestamp desc
limit 1)"""
tmp = c.execute(sql).fetchone()
if tmp is None:
tmp = {'main_sail': None, 'head_sail': None, 'flying_sail': None}
else:
tmp = json.loads(tmp[0])
return tmp
def _query_items(db: sqlite3.Connection) -> pa.Table:
"""Return a table; raise sqlite3.ProgrammingError if queries fail."""
db.create_function("comment_yaml_to_text", 1, comment_yaml_to_text)
with contextlib.closing(db.cursor()) as cursor:
cursor.execute(ITEMS_SQL)
table = _cursor_to_table(cursor)
status_id_to_label = _query_team_status_labels_lookup(db)
# dictionary_encode() makes the pylist take less RAM because strings aren't
# duplicated. (Each duplicated Python string costs 50 bytes overhead.)
status_ids = table["item_status"].dictionary_encode().to_pylist()
status_labels = pa.array(
[(status_id_to_label.get(id, id) if id is not None else None)
for id in status_ids],
pa.utf8(),
).dictionary_encode()
table = table.set_column(table.column_names.index("item_status"),
"item_status", status_labels)
return table
def write_box_office(df: pd.DataFrame, conn: sqlite3.Connection):
buffer = []
for _, row in df.iterrows():
buffer.append((
sha1_hex(row["name"] + row["publisher"] + row["week"].strftime("%Y-%m-%d"), truncate=12),
sha1_hex(row["name"] + row["publisher"]),
row["week"].strftime("%Y-%m-%d"),
row["theaters"],
row["revenue"],
row["tickets"],
row["total_revenue"],
row["total_tickets"]
))
cur = conn.cursor()
cur.executemany(
'INSERT INTO weekly_box_office VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
buffer
)
conn.commit()
return len(buffer)
def add_history(db: sqlite3.Connection, user_id: int, request: dict,
response: dict):
"""
Adds request/response session to history to DB.
Args:
db: Database sqlite3.Connection used
user_id: Id of user - requester
request: CATP-compatible dictionary with request by user
response: CATP-compatible dictionary with response by server
Returns:
Nothing
"""
cursor = db.cursor()
if 'password' in request:
request['password'] = request['password'].decode('ascii')
cursor.execute(
'insert into History(Request, Response, UserId) values (?, ?, ?);',
(json.dumps(request), json.dumps(response), user_id))
def show_all_created_tables_name(connection:sqlite3.Connection) -> None:
""" Функция выводит список имен всех созданных таблиц (без содержания)
аргумент:
connection -- соединение с базой данных
"""
try:
if connection is None:
raise WrongDataBaseNameException('Неправильное название базы данных. Введите существующую')
cursor = connection.cursor()
cursor.execute("""SELECT name FROM sqlite_master
WHERE type='table'""")
print("Список всех созданных таблиц:")
for e, table in enumerate(cursor.fetchall()):
print(e+1, ' -- ', table[0])
connection.commit()
except sqlite3.Error as e:
print(e)
finally:
connection.close()
def upsert_proxy(conn: Connection, proxy_list: []):
"""
往代理列表中放代理，存在则修改更新时间，不存在则新插入
:param conn: 数据连接
:param proxy_list: 代理列表，格式为：[(proxy1),(proxy2),...(proxyn)]
:return:
"""
update_sql = '''
UPDATE OR IGNORE proxy_list
SET update_date = datetime('now', 'localtime')
WHERE proxy = ?
'''
insert_sql = '''
INSERT OR IGNORE INTO proxy_list (proxy, insert_date, update_date)
VALUES (?, datetime('now', 'localtime'), datetime('now', 'localtime'))
'''
cur = conn.cursor()
cur.executemany(update_sql, proxy_list)
cur.executemany(insert_sql, proxy_list)
def insert_course(conn: Connection, course: Course, moodle_id: int):
"""
Adds a new course the the DB
:param conn: DB connection
:param course: Course information
:param moodle_id: Moodle identifier in the DB
"""
c = conn.cursor()
c.execute(
"""INSERT INTO courses(id, moodle_id, shortname, download, downloaded, hash)
VALUES(?, ?, ?, ?, 0, ?);""",
(course.id, moodle_id, course.name, course.download, hash(course)))
conn.commit()
c.close()
# Add the sections
for section in course.sections:
insert_section(conn, section, course.id, moodle_id)
def add_items(conn: sqlite3.Connection, items: SystembolagetSortiment):
cur = conn.cursor()
insertion_list = []
for item in items.items:
row = []
for p in PROPERTY_TYPES:
if p.identifier in item:
row.append(item[p.identifier])
else:
row.append(None)
insertion_list.append(row)
cur.executemany('INSERT INTO sortiment({}) values ({})'
.format(', '.join([p.identifier for p in PROPERTY_TYPES]),
', '.join('?' * len(PROPERTY_TYPES))),
insertion_list)
conn.commit()
def xml_writer(filepath, db: sqlite3.Connection):
cursor = db.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = cursor.fetchall()
tree = ET.ElementTree()
i = 0
xml_root = ET.Element('data')
for idx, table in enumerate(tables):
cursor.execute("SELECT * FROM " + table[0] + ";")
column_names = [description[0] for description in cursor.description]
data = cursor.fetchall()
table_data = ET.SubElement(xml_root, table[0])
for row in data:
row_data = ET.SubElement(table_data, 'row')
for idx, value in enumerate(row):
value_data = ET.SubElement(row_data, column_names[idx])
value_data.text = str(value)
tree._setroot(xml_root)
tree.write(filepath)
def get_values(conn: sqlite3.Connection, table: str, columns: list,
conditions: list) -> list:
"""
Returns values from the DB based on the given parameters
:param conn: Connection to the DB
:param table: Table name
:param columns: Specific columns to be returned (SELECT clause parameters)
:param conditions: WHERE clause conditions in the form of [ "a = b" , ... ] -> WHERE a = b AND ...
:return: list
"""
# Format the fill query
sql_str = "SELECT {0} FROM {1} WHERE {2};".format(",".join(columns), table,
" AND ".join(conditions))
# Execute and retrieve rows
cursor = conn.cursor()
cursor.execute(sql_str)
rows = cursor.fetchall()
cursor.close()
return rows
def delete_bet(connection: sqlite3.Connection, bet_id: int) -> bool:
logger.debug(f'delete_bet: {bet_id}')
query = "DELETE FROM bets WHERE id=?"
query_param = [bet_id]
try:
cur = connection.cursor()
cur.execute(query, query_param)
connection.commit()
logger.debug(replace_char_list(query, query_param))
logger.debug(f'lastrowid: {cur.lastrowid}')
logger.debug(f'bet deleted')
return True
except Error as e:
logger.error(f'There was an error deleting bet from bets: {e}')
return False
def ping_connection(dbapi_connection: Connection, connection_record,
connection_proxy) -> None:
"""Ensure connections are valid.
From: `http://docs.sqlalchemy.org/en/rel_0_8/core/pooling.html`
In case db has been restarted pool may return invalid connections.
"""
cursor = dbapi_connection.cursor()
try:
cursor.execute("SELECT 1")
except Exception:
# optional - dispose the whole pool
# instead of invalidating one at a time
# connection_proxy._pool.dispose()
# raise DisconnectionError - pool will try
# connecting again up to three times before raising.
raise sa.exc.DisconnectionError()
cursor.close()
def __execute_parameterized_query(p_connection: sqlite3.Connection,
p_query: str,
p_parameters: tuple) -> list:
try:
l_cursor: sqlite3.Cursor = p_connection.cursor()
l_cursor.execute(p_query, p_parameters)
l_rows: list = l_cursor.fetchall()
p_connection.commit()
Printer.print("Executed SQLite query: {}".format(p_query),
Level.DEBUG)
SQLite.__print_rows_affected(l_cursor, p_query)
return l_rows
except sqlite3.ProgrammingError as l_error:
Printer.print(
"Programming Error: executing SQLite query: {}".format(
l_error), Level.ERROR)
except sqlite3.OperationalError as l_error:
Printer.print(
"Operational Error executing SQLite query: {}".format(l_error),
Level.ERROR)
def insert_observation(conn: sqlite3.Connection, observation_val: str,
timestamp_id: int) -> int:
"""
Insert a new row in observations table, linking to a timestamp.
Pass the observation value as observation_val and the timestamp row id as
timestamp_id, returns id of new row in observations.
"""
row_statement = str('INSERT INTO observations(observationVal, timestampID)'
+ 'VALUES ("' + observation_val + '",'
+ str(timestamp_id) + ')')
try:
cur = conn.cursor()
cur.execute(row_statement)
conn.commit()
except Error as err:
print(err)
return cur.lastrowid
def create_users_table(connection: sqlite3.Connection) -> NoReturn:
""" Функция создания таблицы в базе данных
аргументы:
connection -- соединение с базой данных
"""
try:
if connection is None:
raise WrongDataBaseNameException(
'Неправильное название базы данных. Введите существующую')
cursor = connection.cursor()
cursor.execute(f"""CREATE TABLE IF NOT EXISTS "users"(
"username" TEXT NOT NULL UNIQUE,
"password" TEXT NOT NULL UNIQUE,
PRIMARY KEY("password"));""")
connection.commit()
except sqlite3.Error as e:
print(e)
finally:
connection.close()
def create_project(conn: sqlite3.Connection, name: str, reference: str):
"""Create the table "projects" and insert project name and reference genome
Args:
conn (sqlite3.Connection): Sqlite3 Connection
name (str): Project name
reference (str): Genom project
Todo:
* Rename to create_table_project
"""
cursor = conn.cursor()
cursor.execute(
"CREATE TABLE projects (id INTEGER PRIMARY KEY, name TEXT, reference TEXT)"
)
cursor.execute(
"INSERT INTO projects (name, reference) VALUES (?, ?)", (name, reference)
)
conn.commit()
def get_configuration_items(conn: sql.Connection, sc_map: Dict[str, List[str]],
ci_map: Dict[str, List[List[str]]]):
cur = conn.cursor()
cur.execute("""
SELECT DISTINCT SC, CI, CI_Type, CI_Subtype
FROM (
SELECT Service_Component_WBS_aff as SC, CI_Name_aff as CI, CI_Type_aff as CI_Type, CI_Subtype_aff as CI_Subtype
FROM Change
UNION
SELECT Service_Comp_WBS_aff as SC, CI_Name_aff as CI, CI_Type_aff as CI_Type, CI_Subtype_aff as CI_Subtype
FROM Interaction
UNION
SELECT Service_Component_WBS_aff as SC, CI_Name_aff as CI, CI_Type_aff as CI_Type, CI_Subtype_aff as CI_Subtype
FROM Incident
UNION
SELECT ServiceComp_WBS_CBy as SC, CI_Name_CBy as CI, CI_Type_aff as CI_Type, CI_Subtype_CBy as CI_Subtype
FROM Incident
)
""")
return cur.fetchall()
def create_tables(connection: sqlite3.Connection):
cursor = connection.cursor()
cursor.execute('''
create table if not exists catalog (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_key varchar(200) NOT NULL,
category_name varchar(200)
);
''')
cursor.execute('''
create table if not exists sales (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_key varchar(200) NOT NULL,
country varchar(3),
city_name varchar(60),
sale_timestamp TEXT,
price NUMERIC
);
''')
def print_occurrences(connection: sqlite3.Connection, city_to_search: str):
cursor = connection.cursor()
cursor.execute('select * from sales where city_name=? order by price asc;', [city_to_search])
occurrences = cursor.fetchall()
if occurrences:
print('\nПродажби в град {}:\n'.format(city_to_search))
max_padding = max(len(item) for item in occurrences)
for row_index, row in enumerate(occurrences, start=1):
item_key = row[1]
sale_timestamp = row[4]
price = row[5]
print('Артикул #{:<{}}{}\t\tдата/час: {}\t\tсума: {}€'.format(
row_index, max_padding, item_key, sale_timestamp, price
))
else:
print('Няма данни за продажби в град {}.'.format(city_to_search))
def exec_select(conn: Connection, sql: str, params: () = None) -> []:
"""
根据sql查询数据并返回结果
:param conn: 数据连接
:param sql: 查询语句
:param params: 查询参数
:return: List([])
"""
data_list = []
cur = conn.cursor()
if params is None:
cur.execute(sql)
else:
cur.execute(sql, params)
rows = cur.fetchall()
for row in rows:
data = []
for value in row:
data.append(value)
data_list.append(data)
return data_list
from importlib import import_module
from time import time
from sqlite3 import Connection
from .constants import *
from .commands import *
# Stuff cwd into python path
sys.path += os.getcwd(),
__all__ = ["Commands", "Handler"]
LOGGER = logging.getLogger(__name__)
MERCDB = Connection("merc.db", isolation_level=None)
MERCDB.cursor().execute("CREATE TABLE IF NOT EXISTS merc (ts INT PRIMARY KEY, msg TEXT)")
MERCDB.cursor().execute("CREATE TABLE IF NOT EXISTS red (ts INT PRIMARY KEY, msg TEXT)")
class Commands(list):
def __init__(self, additional):
super().__init__()
for cmd in additional:
try:
mod = import_module(cmd)
for cand in mod.__dict__.values():
if not self.valid(cand):
continue
self += cand,
except ImportError:
LOGGER.exception("Failed to import custom command")
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 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 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()
