def init_db(conn: sqlite3.Connection) -> None:
init_sql_path = Path(__file__).parent / "init.sql"
with open(init_sql_path) as file:
init_sql = file.read()
conn.executescript(init_sql)
def _create_config_table(self, db: sqlite3.Connection) -> None:
db.executescript(
'CREATE TABLE IF NOT EXISTS configs ('
' path TEXT NOT NULL,'
' PRIMARY KEY (path)'
');',
)
def create_raw_table(connection: sqlite3.Connection):
if not connection:
print('Database already exists')
return
sql = """
CREATE TABLE IF NOT EXISTS char_set (
pinyin CHARACTER (7),
char CHARACTER (1),
count INT
);
CREATE UNIQUE INDEX IF NOT EXISTS pinyin_char on char_set(pinyin, char);
CREATE TABLE IF NOT EXISTS relation2 (
left INT,
right INT,
count INT,
FOREIGN KEY(left) REFERENCES char_set(oid),
FOREIGN KEY(right) REFERENCES char_set(oid)
);
CREATE UNIQUE INDEX IF NOT EXISTS relation2_index on relation2(left, right);
CREATE TABLE IF NOT EXISTS relation3 (
left INT,
middle INT,
right INT,
count INT,
FOREIGN KEY(left) REFERENCES char_set(oid),
FOREIGN KEY(middle) REFERENCES char_set(oid),
FOREIGN KEY(right) REFERENCES char_set(oid)
);
CREATE UNIQUE INDEX IF NOT EXISTS relation3_index on relation3(left, middle, right);
"""
connection.executescript(sql)
print('Finished table creation')
def init_db(db_con: sqlite3.Connection):
init_script = """
CREATE TABLE Message (guild_id integer, unix_time real, user_id integer, channel_id integer, message_id integer);
CREATE TABLE GuildJoin (guild_id integer, unix_time real, user_id integer);
"""
with db_con:
db_con.executescript(init_script)
def _create_sqlite_tables(cls, db: sqlite3.Connection):
# For `repo_server.py` we need repo + path lookup, so that's the
# primary key.
#
# For repo debugging & exploration, we want global lookup on
# name-version-release -- hence the `nvrea` index. It's unimportant
# to index on arch & epoch, or not to index on repo, since the total
# number of rows for a given NVR should be low.
db.executescript('''
CREATE TABLE "rpm" ({rpm_cols}, PRIMARY KEY ("repo", "path"));
CREATE INDEX "rpm_nvrea" ON "rpm" (
"name", "version", "release", "epoch", "arch"
);
CREATE TABLE "repodata" ({repodata_cols}, PRIMARY KEY ("repo", "path"));
CREATE TABLE "repomd" ({repomd_cols}, PRIMARY KEY ("repo"));
'''.format(
**{
f'{table}_cols': ',\n'.join(f'"{k}" {v}'
for k, v in col_spec.items())
for table, col_spec in [
('rpm', cls._RPM_COLUMNS),
('repodata', cls._REPODATA_COLUMNS),
('repomd', cls._REPOMD_COLUMNS),
]
}))
def _create_config_table(self, db: sqlite3.Connection) -> None:
db.executescript(
"CREATE TABLE IF NOT EXISTS configs ("
" path TEXT NOT NULL,"
" PRIMARY KEY (path)"
");",
)
def init_db(conn: sqlite3.Connection):
conn.executescript('''
CREATE TABLE IF NOT EXISTS `gifts` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`name` TEXT NOT NULL,
`desc` TEXT NOT NULL,
`total` INTEGER NOT NULL DEFAULT 0,
`winners_count` INTEGER NOT NULL DEFAULT 0,
`src` TEXT NOT NULL,
`batch` INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS `candidates` (
`name` TEXT NOT NULL,
PRIMARY KEY(`name`)
);
CREATE TABLE IF NOT EXISTS `winners` (
`id` INTEGER NOT NULL,
`name` TEXT NOT NULL,
PRIMARY KEY(`id`,`name`)
);
''')
conn.commit()
def create_raw_table(connection: sqlite3.Connection):
sql = """
CREATE TABLE charset (
char CHARACTER (1) UNIQUE,
count INT
);
CREATE TABLE relation (
left INT,
right INT,
count INT,
FOREIGN KEY(left) REFERENCES charset(oid),
FOREIGN KEY(right) REFERENCES charset(oid)
);
CREATE UNIQUE INDEX relation_index on relation(left, right);
CREATE TABLE pinyin_set (
pinyin CHARACTER (8) UNIQUE
);
CREATE TABLE pinyin_char (
pinyin INT,
char INT,
FOREIGN KEY(pinyin) REFERENCES pinyin(oid),
FOREIGN KEY(char) REFERENCES charset(oid)
);
CREATE INDEX pinyin_charset_index on pinyin_char(pinyin);
"""
connection.executescript(sql)
print('Finished table creation')
def add_triggers(cur, db: Connection, table: str, columns: List[str]):
global upgraded
column_list_without = ",".join(f"{c}" for c in columns
if c.find(" UNINDEXED") == -1)
triggers = [
i[1] for i in cur.execute(
"select * from sqlite_master where type = 'trigger';")
]
execute_script = ""
if f"{table}_fts_insert" not in triggers:
execute_script += """
CREATE TRIGGER {table}_fts_insert AFTER INSERT ON messages
BEGIN
INSERT INTO {table}_fts (rowid, {column_list}) VALUES (new.id, {new_columns});
END;
""".format(
table=table,
column_list=column_list_without,
new_columns=",".join(f"new.{c}" for c in columns
if c.find(" UNINDEXED") == -1),
)
if f"{table}_fts_delete" not in triggers:
execute_script += """
CREATE TRIGGER {table}_fts_delete AFTER DELETE ON messages
BEGIN
INSERT INTO {table}_fts ({table}_fts, rowid, {column_list}) VALUES ('delete', old.id, {old_columns});
END;
""".format(
table=table,
column_list=column_list_without,
old_columns=",".join(f"old.{c}" for c in columns
if c.find(" UNINDEXED") == -1),
)
if f"{table}_fts_update" not in triggers:
execute_script += """
CREATE TRIGGER {table}_fts_update AFTER UPDATE ON messages
BEGIN
INSERT INTO {table}_fts ({table}_fts, rowid, {column_list}) VALUES ('delete', old.id, {old_columns});
INSERT INTO {table}_fts (rowid, {column_list}) VALUES (new.id, {new_columns});
END;
""".format(
table=table,
column_list=column_list_without,
new_columns=",".join(f"new.{c}" for c in columns
if c.find(" UNINDEXED") == -1),
old_columns=",".join(f"old.{c}" for c in columns
if c.find(" UNINDEXED") == -1),
)
if execute_script != "":
upgraded = True
acarshub_logging.log("Inserting FTS triggers",
"db_upgrade",
level=LOG_LEVEL["INFO"])
db.executescript(execute_script)
conn.executescript(
'INSERT INTO messages_fts(messages_fts) VALUES ("rebuild")')
def _init(db_dir: Path) -> Connection:
db = (db_dir / _SCHEMA).with_suffix(".sqlite3")
db.parent.mkdir(parents=True, exist_ok=True)
conn = Connection(db, isolation_level=None)
init_db(conn)
conn.executescript(sql("create", "pragma"))
conn.executescript(sql("create", "tables"))
return conn
def db_init_table(conn: sqlite3.Connection):
conn.executescript('''
CREATE TABLE IF NOT EXISTS SAMBA (
uid INTEGER UNIQUE NOT NULL,
groups VARCHAR(127) NOT NULL,
FOREIGN KEY(uid) REFERENCES USERS(uid)
);
''')
def create_schema(db: sqlite3.Connection) -> None:
"""Creates the database schema."""
schema_dir = pkg_resources.resource_filename('git_code_debt', 'schema')
schema_files = os.listdir(schema_dir)
for sql_file in schema_files:
resource_filename = os.path.join(schema_dir, sql_file)
with open(resource_filename, 'r') as resource:
db.executescript(resource.read())
def migrate(con: Connection) -> None:
"""Migrate to latest version of database.
Does not roll back on error.
"""
for version, source in schemas():
if user_version(con) < version:
con.executescript(source)
con.commit()
def wipe(conn: sqlite3.Connection):
conn.executescript("""
DROP TABLE IF EXISTS Members;
DROP TABLE IF EXISTS Contestants;
DROP TABLE IF EXISTS Responses;
DROP TABLE IF EXISTS Votes;
DROP TABLE IF EXISTS Status;
DROP TABLE IF EXISTS ResponseArchive;
""")
def init(conn: sqlite3.Connection):
conn.executescript("""
CREATE TABLE IF NOT EXISTS Members (
uid INTEGER PRIMARY KEY NOT NULL,
aggregateVoteCount INTEGER DEFAULT 0,
roundVoteCount INTEGER DEFAULT 0,
remindStart UNSIGNED BIG INT,
remindInterval UNSIGNED BIG INT
);
CREATE TABLE IF NOT EXISTS Contestants (
uid INTEGER PRIMARY KEY NOT NULL,
alive BOOLEAN DEFAULT 0,
allowedResponseCount INTEGER DEFAULT 1,
responseCount INTEGER DEFAULT 0,
prized INTEGER DEFAULT 0
);
CREATE TABLE IF NOT EXISTS Responses (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
uid INTEGER NOT NULL,
rid INTEGER NOT NULL,
response TEXT,
confirmedVoteCount INTEGER DEFAULT 0,
pendingVoteCount INTEGER DEFAULT 0
);
CREATE TABLE IF NOT EXISTS Votes (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
uid INTEGER NOT NULL,
vid INTEGER NOT NULL,
gseed TEXT UNIQUE NOT NULL,
vote TEXT
);
CREATE TABLE IF NOT EXISTS Status (
id INTEGER PRIMARY KEY,
roundNum INTEGER,
prompt TEXT,
phase TEXT,
deadline UNSIGNED BIG INT,
startTime UNSIGNED BIG INT
);
INSERT OR IGNORE INTO Status VALUES (0, 1, NULL, "none", -1, -1);
CREATE TABLE IF NOT EXISTS ResponseArchive (
roundNum INTEGER NOT NULL,
id INTEGER NOT NULL,
uid INTEGER NOT NULL,
rid INTEGER NOT NULL,
rank INTEGER NOT NULL,
response TEXT NOT NULL,
score DOUBLE NOT NULL,
skew DOUBLE NOT NULL
);
""")
def __init__(self, conn: sqlite3.Connection, logs: logging,
schema_file: TextIO):
self.conn = conn
self.logs = logs
self.schema = schema_file.read()
try:
conn.executescript(self.schema)
logs.info("Database initialized from schema.sql")
except sqlite3.Error:
logs.error("Failed creating database from schema.sql")
def _init(db_dir: Path, cwd: PurePath) -> Connection:
ncwd = normcase(cwd)
name = f"{md5(encode(ncwd)).hexdigest()}-{_SCHEMA}"
db = (db_dir / name).with_suffix(".sqlite3")
db.parent.mkdir(parents=True, exist_ok=True)
conn = Connection(str(db), isolation_level=None)
init_db(conn)
conn.executescript(sql("create", "pragma"))
conn.executescript(sql("create", "tables"))
return conn
def db_init_table(conn: sqlite3.Connection):
conn.executescript('''
CREATE TABLE IF NOT EXISTS WEB_USER_ACTION (
aid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
uid INTEGER NOT NULL,
action_type INTEGER NOT NULL,
create_time timestamp NOT NULL,
valid_until timestamp NOT NULL,
closed INTEGER NOT NULL DEFAULT 0,
parm TEXT NOT NULL DEFAULT "",
FOREIGN KEY(uid) REFERENCES USERS(uid)
);
''')
def create_schema(db: sqlite3.Connection) -> None:
db.executescript(
'CREATE TABLE repos (\n'
' name TEXT NOT NULL,\n'
' PRIMARY KEY (name)\n'
')', )
db.executescript(
'CREATE TABLE packages (\n'
' repo_name TEXT NOT NULL,\n'
' type TEXT NOT NULL,\n'
' key TEXT NOT NULL,\n'
' name TEXT NOT NULL,\n'
' PRIMARY KEY (repo_name, type, key)\n'
')', )
db.executescript(
'CREATE TABLE depends (\n'
' repo_name TEXT NOT NULL,\n'
' relationship TEXT NOT NULL,\n'
' package_type TEXT NOT NULL,\n'
' package_key TEXT NOT NULL,\n'
' spec TEXT NOT NULL\n'
')', )
db.executescript(
'CREATE TABLE errors (\n'
' repo_name TEXT NOT NULL,\n'
' trace BLOB\n'
')', )
def _initialize(conn: sqlite3.Connection) -> None:
schema = resources.read_text('wn', 'schema.sql')
with conn:
conn.executescript(schema)
# prepare lookup tables
conn.executemany('INSERT INTO parts_of_speech (pos) VALUES (?)',
((pos, ) for pos in constants.PARTS_OF_SPEECH))
conn.executemany('INSERT INTO adjpositions (position) VALUES (?)',
((adj, ) for adj in constants.ADJPOSITIONS))
conn.executemany('INSERT INTO synset_relation_types (type) VALUES (?)',
((typ, ) for typ in constants.SYNSET_RELATIONS))
conn.executemany('INSERT INTO sense_relation_types (type) VALUES (?)',
((typ, ) for typ in constants.SENSE_RELATIONS))
conn.executemany(
'INSERT INTO lexicographer_files (id, name) VALUES (?,?)',
((id, name) for name, id in constants.LEXICOGRAPHER_FILES.items()))
def migrate(connection: sqlite3.Connection, thread: gui.threads.WorkerThread):
connection.executescript(f"""
BEGIN;
CREATE TABLE version (
db_version INTEGER PRIMARY KEY,
app_version TEXT
);
ALTER TABLE images ADD COLUMN hash BLOB; -- Cannot use INTEGER as hashes are 64-bit *unsigned* integers
CREATE INDEX idx_images_hash ON images (hash); -- Speed up hash querying
ALTER TABLE tags ADD COLUMN definition TEXT;
INSERT INTO version (db_version, app_version) VALUES (1, "{constants.VERSION}");
""")
cursor = connection.execute('SELECT id, path FROM images')
rows = cursor.fetchall()
total_rows = len(rows)
for i, (ident, path) in enumerate(rows):
if thread.cancelled:
cursor.close()
connection.rollback()
break
thread.progress_signal.emit(
i / total_rows,
_t(f'popup.database_update.migration_0000.hashing_image_text',
image=path,
index=i + 1,
total=total_rows), thread.STATUS_UNKNOWN)
image_hash = utils.image.get_hash(path)
try:
connection.execute('UPDATE images SET hash = ? WHERE id = ?',
(data_access.ImageDao.encode_hash(image_hash)
if image_hash is not None else None, ident))
except sqlite3.Error as e:
cursor.close()
thread.error = str(e)
thread.cancel()
else:
thread.progress_signal.emit(
(i + 1) / total_rows,
_t(f'popup.database_update.migration_0000.hashing_image_text',
image=path,
index=i + 1,
total=total_rows), thread.STATUS_SUCCESS)
else:
cursor.close()
connection.commit()
def DBExecuteScript(DB: sqlite3.Connection, sql: str, *args):
"""
Execute many SQL script in database `DB`
:param DB: The database
:param sql: SQL script (commands separated by ';')
::
DBWikictionary = sqlite3.connect( DB_NAME )
DBExecute( DBWikictionary,
"CREATE TABLE wiktionary (LanguageCode CHAR(2)); CREATE INDEX LanguageCode ON wiktionary (LanguageCode);"
)
"""
assert isinstance(DB, sqlite3.Connection)
DB.executescript(sql)
DB.commit()
def create_database(db: sqlite3.Connection):
"""
Initializes the database.
"""
try:
with db:
db.executescript("""
CREATE TABLE messages (
timestamp INTEGER NOT NULL,
chat_id INTEGER NOT NULL,
from_user_id INTEGER NOT NULL,
text TEXT NOT NULL
);
""")
except sqlite3.OperationalError:
logging.info("Table is already created.")
else:
logging.info("Created table.")
def drop_tables(connector: sql.Connection):
"""Удаление всех таблиц в базе данных"""
try:
connector.executescript("""
pragma foreign_keys=off;
begin transaction;
drop table if exists webpages;
drop table if exists tags;
drop table if exists webpagetags;
drop table if exists search_table;
drop table if exists html_contents;
commit;
pragma foreign_keys=on;
""")
except sql.Error:
logger.exception('Exception in drop tables')
connector.rollback()
close_connection(connector)
def create_indexes(connector: sql.Connection):
"""Создает индексы в базе данных"""
try:
with connector:
connector.executescript("""
begin transaction;
CREATE UNIQUE INDEX IF NOT EXISTS html_contents_id_page_idx ON html_contents (id_page);
CREATE INDEX IF NOT EXISTS webpages_title_idx ON webpages (title COLLATE nocase);
CREATE INDEX IF NOT EXISTS webpages_time_saved_idx ON webpages (time_saved);
CREATE UNIQUE INDEX IF NOT EXISTS webpages_hash_idx ON webpages (hash);
CREATE INDEX IF NOT EXISTS tags_tag_idx ON tags (tag COLLATE nocase);
CREATE INDEX IF NOT EXISTS webpagetags_id_page_idx ON webpagetags (id_page);
CREATE INDEX IF NOT EXISTS webpagetags_id_tag_idx ON webpagetags (id_tag);
commit;
""")
except sql.Error as e:
logger.exception('Ошибка создания индексов')
connector.rollback()
raise e
def delete_indexes(connector: sql.Connection):
"""Удаляет индексы в базе данных"""
try:
with connector:
connector.executescript("""
begin transaction;
drop index if exists html_contents_id_page_idx;
drop index if exists webpages_title_idx;
drop index if exists webpages_time_saved_idx;
drop index if exists webpages_hash_idx;
drop index if exists tags_tag_idx;
drop index if exists webpagetags_id_page_idxl;
drop index if exists webpagetags_id_tag_idx;
commit;
""")
except sql.Error as e:
logger.exception('Ошибка удаления индексов')
connector.rollback()
raise e
def init_sql_schema(db: Connection):
# Run schema script
schema_file = os.path.join(os.path.dirname(__file__), "schema.sql")
with open(schema_file, "rb") as f:
db.executescript(f.read().decode("utf8"))
# Populate permissions table
permissions = [
("read", "files"),
("write", "files"),
("read", "disk_storage"),
("write", "disk_storage"),
]
for x in permissions:
db.execute(
"INSERT INTO permission (access_level, scope) VALUES (?, ?)",
[x[0], x[1]])
db.commit()
def create_tables(con: sqlite3.Connection):
SQL_SCRIPT = """
CREATE TABLE IF NOT EXISTS subjects(
sub_id int AUTO INCREMENT,
name varchar(20) NOT NULL,
questions_file_path varchar(200) NOT NULL,
PRIMARY KEY (sub_id)
);
CREATE TABLE IF NOT EXISTS users(
username varchar(20) NOT NULL,
password_hash varchar(100) NOT NULL,
is_admin bool NOT NULL
);
CREATE TABLE IF NOT EXISTS results(
rec_id int AUTO INCREMENT,
username varchar(20) NOT NULL,
subject varchar(20) NOT NULL,
result int NOT NULL,
PRIMARY KEY (rec_id)
);
"""
con.executescript(SQL_SCRIPT)
add_user(con, "admin", "admin", True)
def setup_database(conn: sqlite3.Connection):
conn.executescript("""
PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS names(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS years(
year INTEGER PRIMARY KEY,
total_males INTEGER NOT NULL,
total_females INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS name_counts(
year INTEGER NOT NULL,
name_id INTEGER NOT NULL,
male_count INTEGER NOT NULL,
female_count INTEGER NOT NULL,
male_rank INTEGER,
female_rank INTEGER,
FOREIGN KEY (name_id) REFERENCES names(id),
FOREIGN KEY (year) REFERENCES years(year)
);
""")
def execute_db_transformation_commands(conn: sqlite3.Connection) -> None:
"""
Executed commands intended to transform the database.
Commands include: Removing leftover tables, setting up useful views.
@param conn: Active SQLite3 database connection
"""
command_files = [
os.path.join(command_path, f) for f in os.listdir(command_path)
if f.endswith(".sql")
]
logger.info("Executing database setup...")
for c in sorted(command_files):
script = read_sql_script(c)
try:
with conn:
cur = conn.executescript(script)
cur.close()
except (sqlite3.IntegrityError, sqlite3.OperationalError):
logger.error(f"Failed to execute script at {c}")
logger.error(traceback.format_exc())
else:
logger.info(f"Successfully executed scriot {c}")