def update_resume_data(atp: lt.add_torrent_params, conn: apsw.Connection) -> None:
# Change OR to AND when https://github.com/arvidn/libtorrent/issues/6913 is fixed
conn.cursor().execute(
"UPDATE torrent SET resume_data = ?3 "
"WHERE (info_sha1 IS ?1) OR (info_sha256 IS ?2)",
(*_ih_bytes(info_hashes(atp)), resume_data(atp)),
)
class DatabaseTestCase(unittest.TestCase):
def setUp(self):
self.db = Connection(':memory:')
cur = self.db.cursor()
cur.execute("""
CREATE TABLE mytable (
id INTEGER PRIMARY KEY,
firstname TEXT,
lastname TEXT)""")
cur.execute("""
INSERT INTO mytable (id, firstname, lastname)
VALUES
(1, 'Mir', 'Jakuri')
""")
def test_upsert_insert(self):
utils.upsert(self.db, 'mytable', ['id'], {
'id': 2,
'firstname': 'Ion',
'lastname': 'Preciel',
})
cur = self.db.cursor()
cur.execute('SELECT * FROM mytable')
self.assertEqual((1, 'Mir', 'Jakuri'), cur.fetchone())
self.assertEqual((2, 'Ion', 'Preciel'), cur.fetchone())
def test_upsert_update(self):
utils.upsert(self.db, 'mytable', ['id'], {
'id': 1,
'lastname': 'Teiwaz',
})
cur = self.db.cursor()
cur.execute('SELECT * FROM mytable')
self.assertEqual((1, 'Mir', 'Teiwaz'), cur.fetchone())
def __init__(self, **kwargs):
self.register_event_type('on_pre_start')
super(ThreeDoListApp, self).__init__(**kwargs)
if not self.db:
connection = Connection('db.db')
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE [notebook](
page_number INTEGER NOT NULL,
page TEXT NOT NULL,
what TEXT DEFAULT '',
when_ TEXT DEFAULT '',
why INTEGER DEFAULT 0,
how TEXT DEFAULT '',
ix INTEGER,
bookmark INTEGER DEFAULT 0);
CREATE TABLE [archive](
page TEXT,
what TEXT,
when_ TEXT,
why INTEGER DEFAULT 0,
how TEXT);
CREATE TRIGGER [on_complete]
INSERT ON archive
BEGIN DELETE FROM notebook WHERE page=new.page AND ix=new.ix AND what=new.what;
END;
INSERT INTO notebook(page_number, page)
VALUES(0, 'Main List')
""")
#cursor.execute("commit")
self.db = connection
def connect(self):
try:
logger.debug2(u"Connecting to sqlite db '%s'" % self._database)
if not self._connection:
self._connection = Connection(filename=self._database,
flags=SQLITE_OPEN_READWRITE
| SQLITE_OPEN_CREATE
| SQLITE_CONFIG_MULTITHREAD,
vfs=None,
statementcachesize=100)
if not self._cursor:
def rowtrace(cursor, row):
valueSet = {}
for rowDescription, current in izip(
cursor.getdescription(), row):
valueSet[rowDescription[0]] = current
return valueSet
self._cursor = self._connection.cursor()
if not self._synchronous:
self._cursor.execute('PRAGMA synchronous=OFF')
self._cursor.execute('PRAGMA temp_store=MEMORY')
self._cursor.execute('PRAGMA cache_size=5000')
if self._databaseCharset.lower() in ('utf8', 'utf-8'):
self._cursor.execute('PRAGMA encoding="UTF-8"')
self._cursor.setrowtrace(rowtrace)
return (self._connection, self._cursor)
except Exception as connectionError:
logger.warning("Problem connecting to SQLite databse: {!r}",
connectionError)
raise connectionError
def update_info(ti: lt.torrent_info, conn: apsw.Connection) -> None:
# Change OR to AND when https://github.com/arvidn/libtorrent/issues/6913 is fixed
conn.cursor().execute(
"UPDATE torrent SET info = ?3 "
"WHERE (info_sha1 IS ?1) OR (info_sha256 IS ?2) "
"AND (info IS NULL)",
(*_ih_bytes(ti.info_hashes()), ti.info_section()),
)
def test_bad_resume_data(conn: apsw.Connection) -> None:
conn.cursor().execute(
"INSERT INTO torrent (info_sha1, info_sha256, resume_data, info) VALUES "
"(RANDOMBLOB(20), RANDOMBLOB(32), X'00', NULL)")
atps = list(resume.iter_resume_data_from_db(conn))
assert atps == []
def insert_or_ignore_resume_data(
atp: lt.add_torrent_params, conn: apsw.Connection
) -> None:
conn.cursor().execute(
"INSERT OR IGNORE INTO torrent (info_sha1, info_sha256, resume_data) "
"VALUES (?1, ?2, ?3)",
(*_ih_bytes(info_hashes(atp)), resume_data(atp)),
)
def test_upgrade_dispersy(self):
self.torrent_upgrader._update_dispersy()
db_path = os.path.join(self.sqlite_path, u"dispersy.db")
connection = Connection(db_path)
cursor = connection.cursor()
self.assertFalse(list(cursor.execute(u"SELECT * FROM community WHERE classification == 'SearchCommunity'")))
self.assertFalse(list(cursor.execute(u"SELECT * FROM community WHERE classification == 'MetadataCommunity'")))
cursor.close()
connection.close()
def test_upgrade_dispersy(self):
self.torrent_upgrader._update_dispersy()
db_path = os.path.join(self.sqlite_path, u"dispersy.db")
connection = Connection(db_path)
cursor = connection.cursor()
self.assertFalse(list(cursor.execute(u"SELECT * FROM community WHERE classification == 'SearchCommunity'")))
self.assertFalse(list(cursor.execute(u"SELECT * FROM community WHERE classification == 'MetadataCommunity'")))
cursor.close()
connection.close()
def test_insert_info_hash_sizes(conn: apsw.Connection) -> None:
with pytest.raises(apsw.ConstraintError):
conn.cursor().execute(
"INSERT INTO torrent (info_sha1, resume_data) VALUES (?, X'00')",
(b"aaa",),
)
with pytest.raises(apsw.ConstraintError):
conn.cursor().execute(
"INSERT INTO torrent (info_sha256, resume_data) VALUES (?, X'00')",
(b"bbb",),
)
def update_info_hashes(ih: lt.info_hash_t, conn: apsw.Connection) -> None:
params = _ih_bytes(ih)
info_sha1, info_sha256 = params
if info_sha1 is None or info_sha256 is None:
return
conn.cursor().execute(
"UPDATE torrent SET info_sha1 = ?1 "
"WHERE (info_sha1 IS NULL) AND (info_sha256 IS ?2)",
params,
)
conn.cursor().execute(
"UPDATE torrent SET info_sha256 = ?2 "
"WHERE (info_sha256 IS NULL) AND (info_sha1 IS ?1)",
params,
)
def _init_db(conn: apsw.Connection, schema: str) -> None:
assert schema == "main"
# NB: nulls are distinct in unique constraints. See https://sqlite.org/nulls.html
conn.cursor().execute(
"CREATE TABLE torrent ("
"info_sha1 BLOB, "
"info_sha256 BLOB, "
"resume_data BLOB NOT NULL, "
"info BLOB, "
"CHECK (info_sha1 IS NOT NULL OR info_sha256 IS NOT NULL), "
"CHECK (info_sha1 IS NULL OR LENGTH(info_sha1) = 20), "
"CHECK (info_sha256 IS NULL OR LENGTH(info_sha256) = 32), "
"UNIQUE (info_sha1), "
"UNIQUE (info_sha256))"
)
def remove_tables_from_connection(conn: apsw.Connection):
cursor = conn.cursor()
with conn:
cursor.execute(
"""DROP TABLE contrib_activitymetadata_message_metadata""")
cursor.execute(
"""DROP TABLE contrib_activitymetadata_guild_settings""")
def import_file(file: str, database: sql.Connection):
with open(file, "r", -1, "utf-8") as kanjidic_file:
xml = etree.parse(kanjidic_file)
root = xml.getroot()
kanji_list = root.findall("character")
# We would ordinarily have to do 2 passes over the data two parse references between the
# different kanji. Since this would involve parsing a lot of the tree twice we instead build
# up the references on the first run. This uses significantly more memory (most of the mapping
# references will go unused) but is much faster.
codepoint_map = {}
variant_map = {}
cursor = database.cursor()
with database:
for kanji in kanji_list:
onyomi, kunyomi, nanori = extract_readings(kanji)
if not onyomi and not kunyomi and not nanori:
# This kanji is most likely not used in the Japanese language
continue
kanji_id = import_kanji(cursor, database, kanji)
# The reading and meaning imports insert the appropriate values into the database
import_meanings(cursor, kanji_id, kanji)
import_readings(cursor, kanji_id, onyomi, kunyomi, nanori)
# Update the maps used for the deferred variant mapping
update_reference_maps(kanji_id, kanji, codepoint_map, variant_map)
import_references(cursor, codepoint_map, variant_map)
def remove_tables_from_connection(conn: apsw.Connection):
cursor = conn.cursor()
with conn:
cursor.execute("""DROP TABLE contrib_qotw_all_history""")
cursor.execute("""DROP TABLE contrib_qotw_selected_history""")
cursor.execute("""DROP TABLE contrib_qotw_members""")
cursor.execute("""DROP TABLE contrib_qotw_guild_settings""")
def set_req_all(conn: apsw.Connection, guild_id: int, role_id: int,
*req_role_ids: int):
total_role_ids = {
role_id,
*req_role_ids,
}
with contextlib.closing(conn.cursor()) as cursor, conn:
cursor.execute(
"""
INSERT INTO guild_settings (guild_id) VALUES (?)
ON CONFLICT (guild_id) DO NOTHING
""",
(guild_id, ),
)
cursor.executemany(
"""
INSERT INTO role_settings (role_id, guild_id) VALUES (?,?)
ON CONFLICT (role_id) DO NOTHING
""",
tuple((rid, guild_id) for rid in total_role_ids),
)
cursor.execute(
"""
DELETE FROM role_requires_all WHERE role_id = ?
""",
(role_id, ),
)
cursor.executemany(
"""
INSERT INTO role_requires_all (role_id, required_role_id) VALUES (?,?)
ON CONFLICT (role_id, required_role_id) DO NOTHING
""",
tuple((role_id, other_id) for other_id in req_role_ids),
)
def remove_all_on_message(conn: apsw.Connection, message_id: int):
with contextlib.closing(conn.cursor()) as cursor, conn:
cursor.execute(
"""
DELETE FROM react_role_entries WHERE message_id = ?
""",
(message_id, ),
)
def test_good_resume_data_bad_info_dict(conn: apsw.Connection,
ti: lt.torrent_info) -> None:
atp = lt.add_torrent_params()
atp.ti = ti
bdecoded = lt.write_resume_data(atp)
bdecoded.pop(b"info")
resume_data = lt.bencode(bdecoded)
conn.cursor().execute(
"INSERT INTO torrent (info_sha1, info_sha256, resume_data, info) VALUES "
"(RANDOMBLOB(20), RANDOMBLOB(32), ?, X'00')",
(resume_data, ),
)
atps = list(resume.iter_resume_data_from_db(conn))
assert len(atps) == 1
assert atps[0].ti is None
def remove_entry(conn: apsw.Connection, message_id: int,
reaction_str: str):
with contextlib.closing(conn.cursor()) as cursor, conn:
cursor.execute(
"""
DELETE FROM react_role_entries WHERE message_id = ? AND reaction_string = ?
""",
(message_id, strip_variation_selectors(reaction_str)),
)
def get_all_tasks(
con: apsw.Connection) -> Generator[ScheduledPayloadTask, None, None]:
with closing(con.cursor()) as cursor:
for (uuid, payload, schedule, tzi) in cursor.execute("""
SELECT uuid, payload, schedule, tzinfo
FROM crontab
WHERE version=1
"""):
ce = CronEntry.parse(schedule)
yield ScheduledPayloadTask(ce, payload, uuid, pytz.timezone(tzi))
def set_sticky(self, conn: apsw.Connection, val: bool):
cursor = conn.cursor()
with contextlib.closing(conn.cursor()) as cursor, conn:
cursor.execute(
"""
INSERT INTO guild_settings (guild_id) VALUES (?)
ON CONFLICT (guild_id) DO NOTHING
""",
(self.guild_id, ),
)
cursor.execute(
"""
INSERT INTO role_settings (role_id, guild_id, sticky) VALUES (?,?,?)
ON CONFLICT (role_id)
DO UPDATE SET
sticky=excluded.sticky
""",
(self.role_id, self.guild_id, val),
)
def test_resume_data_external_info(conn: apsw.Connection,
ti: lt.torrent_info) -> None:
orig = lt.add_torrent_params()
orig.ti = ti
bdecoded = lt.write_resume_data(orig)
bdecoded.pop(b"info")
resume_data = lt.bencode(bdecoded)
info = ti.info_section()
info_hashes = ti.info_hashes()
conn.cursor().execute(
"INSERT INTO torrent (info_sha1, info_sha256, resume_data, info) "
"VALUES (?, ?, ?, ?)",
(info_hashes.v1.to_bytes(), info_hashes.v2.to_bytes(), resume_data,
info),
)
atps = list(resume.iter_resume_data_from_db(conn))
assert len(atps) == 1
assert normalize(atps[0]) == normalize(orig)
def bulk_remove_exclusivity(conn: apsw.Connection, guild_id: int,
role_ids: Iterable[int]):
with contextlib.closing(conn.cursor()) as cursor, conn:
cursor.executemany(
"""
DELETE FROM role_mutual_exclusivity
WHERE role_id_1 = ?1 OR role_id_2 = ?1
""",
tuple((rid, ) for rid in set(role_ids)),
)
def unstore_task(connection: apsw.Connection, s_type: str, uuid: bytes):
if s_type == CRON:
with closing(connection.cursor()) as cursor:
cursor.execute(
"""
DELETE FROM crontab WHERE uuid=?
""",
(uuid, ),
)
else:
raise RuntimeError("Unexpected type: %s" % s_type)
def all_in_guild(cls, conn: apsw.Connection, guild_id: int):
cursor = conn.cursor()
rows = cursor.execute(
"""
SELECT guild_id, channel_id, message_id, reaction_string, role_id, react_remove_triggers_removal
FROM react_role_entries
WHERE guild_id=?
""",
(guild_id, ),
)
return [cls(*row) for row in rows]
def setUp(self):
self.db = Connection(':memory:')
cur = self.db.cursor()
cur.execute("""
CREATE TABLE mytable (
id INTEGER PRIMARY KEY,
firstname TEXT,
lastname TEXT)""")
cur.execute("""
INSERT INTO mytable (id, firstname, lastname)
VALUES
(1, 'Mir', 'Jakuri')
""")
def self_assignable_ids_in_guild(conn: apsw.Connection,
guild_id: int) -> List[int]:
cursor = conn.cursor()
return [
r[0] for r in cursor.execute(
"""
SELECT role_id FROM role_settings
WHERE guild_id = ? AND self_assignable
""",
(guild_id, ),
)
]
def setUp(self):
self.db = Connection(':memory:')
cur = self.db.cursor()
cur.execute("""
CREATE TABLE mytable (
firstname TEXT,
lastname TEXT,
age INTEGER
)""")
cur.execute("""
INSERT INTO mytable (firstname, lastname, age)
VALUES
('Mir', 'Jakuri', 10)
""")
class MultipleKeyTestCase(unittest.TestCase):
def setUp(self):
self.db = Connection(':memory:')
cur = self.db.cursor()
cur.execute("""
CREATE TABLE mytable (
firstname TEXT,
lastname TEXT,
age INTEGER
)""")
cur.execute("""
INSERT INTO mytable (firstname, lastname, age)
VALUES
('Mir', 'Jakuri', 10)
""")
def test_upsert_multiple_key_insert(self):
utils.upsert(self.db, 'mytable', ['firstname', 'lastname'], {
'firstname': 'Mir',
'lastname': 'Teiwaz',
'age': 350,
})
cur = self.db.cursor()
cur.execute('SELECT * FROM mytable')
self.assertEqual(('Mir', 'Jakuri', 10), cur.fetchone())
self.assertEqual(('Mir', 'Teiwaz', 350), cur.fetchone())
def test_upsert_multiple_key_update(self):
utils.upsert(self.db, 'mytable', ['firstname', 'lastname'], {
'firstname': 'Mir',
'lastname': 'Jakuri',
'age': 350,
})
cur = self.db.cursor()
cur.execute('SELECT * FROM mytable')
self.assertEqual(('Mir', 'Jakuri', 350), cur.fetchone())
def __init__(self, config):
# TODO: concurrency issue resolved?
if config['apsw']:
from apsw import Connection
self.store = Connection(config['sql'])
self.store.cursor().execute('PRAGMA foreign_keys = ON')
else:
from sqlite3 import connect
self.store = connect(config['sql'], check_same_thread=False)
self.store.execute('PRAGMA foreign_keys = ON')
self._loadAdminConf(config['admin'])
self.dropbox = config['dropbox']
# TODO: Awkward, but this'll need to be the hour difference between Pacific
# and wherever the server is. Shouldn't be an issue if we use GU.
self.timezone = 1 * 60 * 60
def store_task(connection: apsw.Connection, task: ScheduledPayloadTask):
with closing(connection.cursor()) as cursor:
zone_str = task.zone.zone # type: ignore
s_type, s_ver, s_parse = task.schedule.to_store()
if s_type == CRON:
cursor.execute(
"""
INSERT INTO crontab(uuid, payload, schedule, tzinfo, version)
VALUES(?,?,?,?,?)
""",
(task.uuid, task.payload, s_parse, zone_str, s_ver),
)
else:
# If this happens, I forgot to add logic for the others after adding them
raise RuntimeError("Unexpected type: %s" % s_type)
def get_member_sticky(
conn: apsw.Connection,
guild_id: int,
member_id: int,
) -> Sequence[int]:
cursor = conn.cursor()
return tuple(row[0] for row in cursor.execute(
"""
SELECT role_id FROM roles_stuck_to_members
WHERE user_id = ? AND guild_id = ?
""",
(member_id, guild_id),
))
def update_member_sticky(
conn: apsw.Connection,
guild_id: int,
member_id: int,
added_roles: Iterable[int],
removed_roles: Iterable[int],
):
with contextlib.closing(conn.cursor()) as cursor, conn:
cursor.execute(
"""
INSERT INTO guild_settings (guild_id) VALUES (?)
ON CONFLICT (guild_id) DO NOTHING
""",
(guild_id, ),
)
cursor.execute(
"""
INSERT INTO user_settings(user_id) VALUES (?)
ON CONFLICT (user_id) DO NOTHING
""",
(member_id, ),
)
cursor.execute(
"""
INSERT INTO member_settings (user_id, guild_id) VALUES (?,?)
ON CONFLICT (user_id, guild_id) DO NOTHING
""",
(member_id, guild_id),
)
if removed_roles:
cursor.executemany(
"""
DELETE FROM roles_stuck_to_members WHERE
guild_id = ? AND user_id = ? AND role_id = ?
""",
tuple((guild_id, member_id, rid) for rid in removed_roles),
)
if added_roles:
cursor.executemany(
"""
INSERT INTO roles_stuck_to_members (guild_id, user_id, role_id)
SELECT ?1, ?2, ?3
WHERE EXISTS(SELECT 1 FROM role_settings WHERE role_id = ?3 AND sticky)
ON CONFLICT (guild_id, user_id, role_id) DO NOTHING
""",
tuple((guild_id, member_id, rid) for rid in added_roles),
)
def match_character_extracts(anime_database, character_extracts):
"""Match and merge characters using an anime database."""
next_reference_id = 1
reference_id_to_character = {}
with Connection(anime_database) as connection:
connection.createscalarfunction("lv_jaro",
jaro,
numargs=2,
deterministic=True)
cursor = connection.cursor()
cursor.execute("delete from unmatched_character")
for filename in tqdm(character_extracts):
# pylint: disable=line-too-long
with open_transcoded(filename, "r",
errors="ignore") as character_fileobj:
character_json = json.loads(
character_fileobj.read().decode("utf8"))
for character in tqdm(character_json):
reference_id = next_reference_id
reference_id_to_character[reference_id] = character
character_names = \
list(chain.from_iterable(character["names"].values()))
anime_names = [a["name"] for a in character["anime_roles"]]
for character_name, anime_name in product(
character_names, anime_names):
cursor.execute(
"insert into unmatched_character ("
"character_name, normalized_character_name,"
"anime_name, normalized_anime_name, "
"reference_id) values (?, ?, ?, ?, ?)",
(character_name,
normalize_character_name(character_name), anime_name,
normalize_anime_name(anime_name), reference_id))
next_reference_id += 1
cursor.execute("REINDEX")
for (reference_id_csv, ) in cursor.execute(MATCH_SQL):
reference_ids = list(map(int, reference_id_csv.split(",")))
characters = map(reference_id_to_character.get, reference_ids)
merged_character = reduce(merge_character_metadata, characters)
if is_sensitive_metadata(merged_character):
print(
f"Skipping character {merged_character['names']['en']} "
f"due to sensitive metadata.",
file=sys.stderr)
continue
yield merged_character
def __init__(self,name):
'''
Set up C{apsw} connection and cursor for database given filename.
@param name: database file name
@type name: C{string}
'''
self.filename = name
try:
self.db = Connection(self.filename)
except CantOpenError:
print "Can't open database %s"%(self.filename)
raise
# rowDict function used to manipulate output into dictionary form.
self.primaryCursor = self.db.cursor()
self.primaryCursor.setrowtrace(self.__rowDict)
def test_insert_info_hashes_unique(conn: apsw.Connection) -> None:
cur = conn.cursor()
info_sha1, info_sha256 = next(cur.execute("SELECT RANDOMBLOB(20), RANDOMBLOB(32)"))
cur.execute(
"INSERT INTO torrent (info_sha1, info_sha256, resume_data) "
"VALUES (?, ?, X'00')",
(info_sha1, info_sha256),
)
with pytest.raises(apsw.ConstraintError):
cur.execute(
"INSERT INTO torrent (info_sha1, resume_data) VALUES (?, X'00')",
(info_sha1,),
)
with pytest.raises(apsw.ConstraintError):
cur.execute(
"INSERT INTO torrent (info_sha256, resume_data) VALUES (?, X'00')",
(info_sha256,),
)
def from_database(cls, conn: apsw.Connection, message_id: int,
reaction_str: str):
cursor = conn.cursor()
row = cursor.execute(
"""
SELECT guild_id, channel_id, message_id, reaction_string, role_id, react_remove_triggers_removal
FROM react_role_entries
WHERE message_id = ? AND reaction_string = ?
""",
(message_id, reaction_str),
).fetchone()
if not row:
raise NoSuchRecord()
return cls(*row)
class DatabaseController(object):
def __init__(self,name):
'''
Set up C{apsw} connection and cursor for database given filename.
@param name: database file name
@type name: C{string}
'''
self.filename = name
try:
self.db = Connection(self.filename)
except CantOpenError:
print "Can't open database %s"%(self.filename)
raise
# rowDict function used to manipulate output into dictionary form.
self.primaryCursor = self.db.cursor()
self.primaryCursor.setrowtrace(self.__rowDict)
def __rowDict(self,cursor,row):
'''
Transform rows returned into dicts, {colname:value}
@param cursor: C{apsw} connection cursor, called automatically from C{rowtrace}
@type cursor: C{apsw.Connection.cursor}
@param row: a row from the database
@type row: C{tuple}
'''
desc = cursor.getdescription()
desc = [val[0] for val in desc] # extract just the names of table columns
d = dict(zip(desc,row)) # turn into a dictionary, name:value pairs
return d
def getTableNames(self):
'''
Return a list of table names in the database
@return: List of strings of table names in database
@rtype: C{list[string]}
'''
self.primaryCursor.execute("select name from sqlite_master;")
return [a['name'] for a in self.primaryCursor.fetchall()] # make a flat array of names
def iter_resume_data_from_db(conn: apsw.Connection) -> Iterator[lt.add_torrent_params]:
version = get_version(conn)
if version == 0:
return
dbver.semver_check_breaking(LATEST, version)
cur = conn.cursor().execute(
"SELECT info_sha1, info_sha256, resume_data, info FROM torrent"
)
for row in cur:
info_sha1, info_sha256, resume_data, info = cast(
tuple[Optional[bytes], Optional[bytes], bytes, Optional[bytes]], row
)
# NB: certain fields (creation date, creator, comment) live in the torrent_info
# object at runtime, but are serialized with the resume data. If the b"info"
# field is empty, the torrent_info won't be created, and these fields will be
# dropped. We want to deserialize the resume data all at once, rather than
# deserialize the torrent_info separately.
info_dict: Optional[Any] = None
if info is not None:
try:
with ltpy.translate_exceptions():
info_dict = lt.bdecode(info)
except ltpy.Error:
_LOG.exception(
"%s parsing info dict", _log_ih_bytes(info_sha1, info_sha256)
)
try:
with ltpy.translate_exceptions():
bdecoded = lt.bdecode(resume_data)
if not isinstance(bdecoded, dict):
_LOG.error(
"%s resume data not a dict",
_log_ih_bytes(info_sha1, info_sha256),
)
continue
if bdecoded.get(b"info") is None and info_dict is not None:
bdecoded[b"info"] = info_dict
yield lt.read_resume_data(lt.bencode(bdecoded))
except ltpy.Error:
_LOG.exception(
"%s parsing resume data", _log_ih_bytes(info_sha1, info_sha256)
)
def create_anime_db(database, anime_extract):
"""Create an anime database from an anime extract."""
with open_transcoded(anime_extract, "r", errors="ignore") as anime_fileobj:
anime_json = json.loads(anime_fileobj.read().decode("utf8"))
with Connection(database) as connection:
connection.createscalarfunction("lv_jaro",
jaro,
numargs=2,
deterministic=True)
cursor = connection.cursor()
cursor.execute(SCHEMA_SQL)
for anime_id, anime in enumerate(tqdm(anime_json), start=1):
cursor.execute("insert into anime values (:anime_id)",
{"anime_id": anime_id})
for name in anime["names"]:
cursor.execute(
"insert into anime_name (is_primary, anime_id, "
"anime_name, normalized_anime_name) values (:is_primary, "
":anime_id, :anime_name, :normalized_anime_name)", {
"is_primary":
name["is_primary"],
"anime_id":
anime_id,
"anime_name":
name["name"],
"normalized_anime_name":
(normalize_anime_name(name["name"]))
})
for character in anime["characters"]:
cursor.execute(
"insert into character_name (anime_id, character_name, "
"normalized_character_name) values (:anime_id, "
":character_name, :normalized_character_name)", {
"anime_id":
anime_id,
"character_name":
character["name"],
"normalized_character_name":
(normalize_character_name(character["name"]))
})
cursor.execute("REINDEX")
from json import load
from apsw import Connection
from os.path import join
from datetime import datetime
config = load(open('/home/main/manager/data/config'))
src = Connection(config['sql'])
dst_file = str(int(datetime.now().strftime("%s")))
dst = Connection(join(config['backups'], dst_file))
with dst.backup('main', src, 'main') as backup:
while not backup.done:
backup.step(100)
class SQLStore:
def __init__(self, config):
# TODO: concurrency issue resolved?
if config['apsw']:
from apsw import Connection
self.store = Connection(config['sql'])
self.store.cursor().execute('PRAGMA foreign_keys = ON')
else:
from sqlite3 import connect
self.store = connect(config['sql'], check_same_thread=False)
self.store.execute('PRAGMA foreign_keys = ON')
self._loadAdminConf(config['admin'])
self.dropbox = config['dropbox']
# TODO: Awkward, but this'll need to be the hour difference between Pacific
# and wherever the server is. Shouldn't be an issue if we use GU.
self.timezone = 1 * 60 * 60
"""
Returns true iff the argument 'username' is equal to a known admin username.
"""
def isInstructor(self, username):
return username == self.admin_name
"""
Returns true iff the argument 'username' is associcated with the given student
table primary key.
"""
def isStudent(self, username, id):
return self.usernameID(username) == id;
"""
Returns the student primary key for a user with the given username.
"""
def usernameID(self, username):
matches = self._select('students', [('username', username)])
return None if len(matches) == 0 else matches[0]['id']
"""
Returns true iff the given username matches the given password.
"""
def passwordMatches(self, username, passwordCandidate):
actual = self.password(username)
if actual == None:
return False
return pwd_context.verify(passwordCandidate, actual)
"""
Returns all offerings of the course with code 'courseCode', or None if no such
course exists.
"""
def offerings(self, courseCode):
return self._select('courses', [('code', courseCode)])
"""
Returns the most recent course offering with code 'courseCode', or None if no
such course exists.
"""
def lastOffering(self, courseCode):
offerings = self.offerings(courseCode)
if len(offerings) == 0:
return None
return sorted(offerings, compare_courses)[-1]
"""
Returns the hashed password for the user with the given login 'username'.
"""
def password(self, username):
if self.isInstructor(username):
return self.admin_pass
else:
info = self._select('students', [('username', username)])
if len(info) != 1:
return None
return info[0]['password']
"""
Gives the given 'student' the given 'password', overwriting the prior password.
"""
def editPassword(self, student, password):
self._update('students', [('password', sha256_crypt.encrypt(password))],
[('id', student)])
"""
Adds a new course with the given attributes.
If course is just an integer (like: 101), adds the prefix 'PHIL '.
The new course will inherit all assignments from the last offering of a course
with the same code.
Returns the primary key identifier for the new course.
"""
def addCourse(self, name, code, year, semester, active=False):
try:
code = 'PHIL {0}'.format(int(code))
except:
pass
lastOffering = self.lastOffering(code)
vals = (name, code, year, semester, int(active))
id = self._insert('courses', external('courses'), vals)
# Inherit all assignments from the last offering with this code.
if lastOffering != None:
for asst in self.assignments(lastOffering['id']):
del asst['id']
asst['course'] = id
self.addAssignment(**asst)
for link in self.links(lastOffering['id']):
del link['id']
link['course'] = id
self.addLink(**link)
return id
"""
Returns a list containing a course dictionary for each registered course.
"""
def courses(self):
return self._select('courses')
"""
Adds a new student with the given attributes. If the username is already
chosen, assigns the same username with an integer suffix that will make it
unique.
Returns the primary key identifier for the new student.
"""
def addStudent(self, course, section, name, email, notes, username, pseudonym, password):
# Ensure unique username.
prior = self._select('students', [('username', username)])
while username == self.admin_name or len(prior) != 0:
username += str('_')
prior = self._select('students', [('username', username)])
vals = (course, section, name, email, notes,
username, pseudonym, sha256_crypt.encrypt(password))
return self._insert('students', external('students'), vals)
"""
Returns all the students registered for the given 'course'.
If 'bySection' is true, returns a map from section name to list containing a
student dictionary for each registered student in that section of 'course'.
"""
def students(self, course=None, bySection=True):
students = self._select('students', [] if course == None else [('course', course)])
return _groupBy(students, 'section') if bySection else students
"""
Returns the course in which the student with the given ID is enrolled.
"""
def courseFor(self, student):
matches = self._select('students', [('id', student)])
return None if len(matches) == 0 else matches[0]['course']
"""
Returns the full stored name of the student.
"""
def studentName(self, student):
matching = self._select('students', [('id', student)])
return None if len(matching) == 0 else matching[0]['name']
"""
Adds a new link with the given attributes.
Returns the primary key identifier for the new link.
"""
def addLink(self, course, link, description):
vals = (course, link, description)
return self._insert('links', external('links'), vals)
"""
Returns a list of all the link dictionaries for the given 'course'.
"""
def links(self, course):
return self._select('links', [('course', course)])
"""
Adds a new assignment with the given attributes.
Returns the primary key identifier for the new assignment.
"""
def addAssignment(self, course, name, description, formal, required,
dropbox, min_weight, max_weight, default_weight):
vals = (course, name, description, int(formal), int(required),
int(dropbox), min_weight, max_weight, default_weight)
return self._insert('assignments', external('assignments'), vals)
"""
Returns the info dict for the assignment with the given 'id'.
"""
def assignmentInfo(self, id):
matching = self._select('assignments', [('id', id)])
return None if len(matching) == 0 else matching[0]
"""
Returns a list of all the assignment dictionaries for the given 'course'.
"""
def assignments(self, course=None):
return self._select('assignments', [] if course == None else [('course', course)])
"""
Returns the full stored name of the assignment.
"""
def assignmentName(self, assignment):
matching = self._select('assignments', [('id', assignment)])
return None if len(matching) == 0 else matching[0]['name']
"""
Returns the row entry in 'table' with primary key 'id', or None if no such entry
exists.
"""
def row(self, table, id):
matching = self._select(table, [('id', id)])
if len(matching) == 0:
return None
return matching[0]
"""
Returns true if 'id' corresponds is the primary key to a row in 'table'.
"""
def rowExists(self, table, id):
return self.row(table, id) != None
"""
Updates the row in 'table' with given 'primary_key' according to each (column,
new value) mapping in 'changes'.
"""
def editRowEntry(self, table, primary_key, changes):
set = []
for column in changes:
set.append((column, changes[column]))
self._update(table, set, [ ('id', primary_key) ])
"""
Deletes the row in 'table' with 'id'.
"""
def deleteRowEntry(self, table, id):
self._delete(table, [('id', id)])
"""
Removes 'student's score for 'assignment'.
"""
def removeScore(self, student, assignment):
self._delete('scores', [('student', student), ('assignment', assignment)])
"""
Removes the weight that 'student' has given to 'assignment'.
"""
def removeWeight(self, student, assignment):
self._delete('weights', [('student', student), ('assignment', assignment)])
"""
Deletes all rows from 'table' that match 'where' clause (list of k=v pairs).
"""
def _delete(self, table, where):
if table == '*':
raise ValueError('Give a specific table.')
if len(where) == 0:
raise ValueError('Narrow the WHERE down a bit, yeah?')
placeholders, values = _escape(where, 'AND ')
fmt = 'DELETE FROM {0} WHERE {1}'.format(table, placeholders)
with self.store:
cursor = self.store.cursor()
cursor.execute(fmt, values)
self.store.commit()
"""
Returns a map of maps, keyed first on student and then on assignment id. An
entry for every student, assignment pair in the 'given' course is included.
Each value is either that student's score on that assignment, or None if no
score is recorded.
"""
def scores(self, course):
return self._fullGradesheet('scores', 'score', course)
"""
Returns a map of maps (see scores() for format), but instead of score values,
includes weight values. Any weight that value that would be None (has no entry
in the weights table) is given the default value for that assignment.
"""
def weights(self, course):
assigned = self._fullGradesheet('weights', 'weight', course)
# If the 'weights' table doesn't have an entry for a given (student,
# assignment) pair, assume the default.
assignments = self._select('assignments', [('course', course)])
defaults = {}
for a in assignments:
defaults[a['id']] = a['default_weight']
for student in assigned:
for assignment in assigned[student]:
if assigned[student][assignment] == None:
assigned[student][assignment] = float(defaults[assignment])
return assigned
"""
Returns a map with an assignment id key for each assignment in the given
'course'. Each value is a dict keyed on ('min', 'max', 'allowZero')
describing basic validation constraints for that assignment. An assignment
is allowed to have a zero weight when it is not required.
"""
def weightConstraints(self, course):
res = {}
for a in self.assignments(course):
res[a['id']] = {
'min': a['min_weight'],
'max': a['max_weight'],
'allowZero': not a['required']
}
return res
"""
Returns a map of maps (see scores() for format), with values from either the
"scores" or "weights" table. 'keyColumn' is either "score" or "weight".
Raises ValueError if some other table is passed.
"""
def _fullGradesheet(self, table, keyColumn, course):
if table != 'scores' and table != 'weights':
raise ValueError('Invalid table.')
if keyColumn != 'score' and keyColumn != 'weight':
raise ValueError('Invalid keyColumn.')
with self.store:
cursor = self.store.cursor()
cursor.execute('SELECT students.id, {0}.assignment, {0}.{1} \
FROM students \
JOIN {0} ON students.id={0}.student \
WHERE students.course=?'.format(table, keyColumn), [course])
res = self._blankGradesheet(course)
rows = cursor.fetchall()
for (student, assignment, grade) in rows:
res[student][assignment] = grade
return res
"""
Returns a map of maps, keyed first on student and then on assignment id. An
entry for every student, assignment pair in the 'given' course is included,
with every value 'None'.
"""
def _blankGradesheet(self, course):
students = self.students(course, bySection=False)
assignments = self.assignments(course)
res = {}
for s in students:
if s['id'] not in res:
res[s['id']] = {}
for a in assignments:
res[s['id']][a['id']] = None
return res
"""
Sets the given student's score on the given assignment to that provided,
overwriting any prior score for that (student, assignment) pair.
"""
def setScore(self, student, assignment, score):
# TODO: Should be implemented with INSERT OR REPLACE INTO for efficiency.
self._setGradeAttribute(student, assignment, 'scores', 'score', score)
"""
Sets the given student's weight for the given assignment to that provided,
overwriting any prior weight for that (student, assignment) pair. If the
assignment is optional, the weight can be either 0 or in the required
weight range for the assignment. If the assignment is required, the weight
must be within the required weight range for the assignment.
"""
def setWeight(self, student, assignment, weight):
# Set the weight to the minimum or maximum allowed for that assignment.
a = self._select('assignments', [('id', assignment)])
if len(a) == 0:
raise ValueError('Unknown assignment.')
else:
a = a[0]
if a['required'] or weight != 0:
weight = max(a['min_weight'], weight)
weight = min(a['max_weight'], weight)
self._setGradeAttribute(student, assignment, 'weights', 'weight', weight)
"""
Returns a list of file entries in the given 'student's dropbox for the given
'assignment'. If two file submissions have the same name, only the most
recent (based on submit timestamp) is included.
"""
def studentDropbox(self, student, assignment):
where = [('student', student), ('assignment', assignment)]
revisions = _groupBy(self._select('dropbox', where), 'original')
return self.fileRevisionHeads_(self._select('dropbox', where))
"""
Returns a map from 'student' id to list of files is that student's dropbox
for the given 'assignment'.
"""
def studentDropboxes(self, assignment):
files = self._select('dropbox', [('assignment', assignment)])
files = sorted(files, key=lambda f : f['timestamp'])
byStudent = _groupBy(files, 'student')
for id in byStudent.keys():
byStudent[id] = self.fileRevisionHeads_(byStudent[id])
return byStudent;
"""
Returns files filtered to exclude a file f if any other file has the same
original name but a later timestamp.
"""
def fileRevisionHeads_(self, files):
revisionGroups = _groupBy(files, 'original').values()
revisionGroups = filter(lambda gp : len(gp) > 0, revisionGroups)
def latestSubmission(files):
return sorted(files, key=lambda f : f['timestamp'])[-1]
return map(latestSubmission, revisionGroups)
"""
Saves the file to the dropbox directory according to a safe file naming scheme,
recording the file's new dropbox path in the store. The file's original name
is recorded unless 'newName' is provided, in which case this filename is used.
Returns the file ID of the new file.
"""
def addDropboxFile(self, student, assignment, file, newName=None):
course = self.assignmentInfo(assignment)['course']
timestamp = int((datetime.now() - datetime(1970, 1, 1)).total_seconds())
timestamp += self.timezone
original_name = newName or secure_filename(file.filename)
# Add database entry to retrieve new ID.
id = self._insert('dropbox', external('dropbox'),
(assignment, student, '', original_name, timestamp))
path = self._dropboxPath(course)
path = join(path, '_'.join(map(str, (id, student, assignment, original_name))))
# Save file contents, ensuring no overwrites.
if exists(path):
raise ValueError('Attempted to overwrite previous file.')
file.save(path)
# Record path in database.
self._update('dropbox', [('path', path)], [('id', id)])
return id
"""
Retrives the stored dropbox file info dict with the given primary key 'id'.
"""
def getDropboxFile(self, id):
res = self._select('dropbox', [('id', id)])
return res[0] if len(res) > 0 else None
"""
Returns the directory for this 'course's file dropbox, creating one if it does
not already exist.
"""
def _dropboxPath(self, course):
path = '{0}/course_{1}'.format(self.dropbox, course)
if not exists(path):
makedirs(path)
return path
"""
Sets the grade attribute (either score or weight) for the student, assignment
pair. 'table' should be either 'scores' or 'weights'. 'key' should be either
'score' or 'weight', respectively.
"""
def _setGradeAttribute(self, student, assignment, table, key, value):
keys = ('student', 'assignment', key)
values = (student, assignment, value)
priorWhere = [('student', student), ('assignment', assignment)]
prior = self._select(table, priorWhere)
if len(prior) == 0:
self._insert(table, keys, values)
else:
self._update(table, [(key, value)], [('id', prior[0]['id'])])
"""
Updates all rows in 'table' matching all filters in 'where' to the values
provided in 'set':
UPDATE <table> SET <set> WHERE <where>
Both 'set' and 'where' are lists of (column name, value) pairs.
"""
def _update(self, table, set, where):
if table not in TABLES:
raise ValueError('Unknown table "{0}".'.format(table))
fmt = 'UPDATE {0} SET {1} WHERE {2}'
sets, setValues = _escape(set)
wheres, whereValues = _escape(where)
with self.store:
cursor = self.store.cursor()
cursor.execute(fmt.format(table, sets, wheres), (setValues + whereValues))
self.store.commit()
"""
Returns the fetched rows resulting from executing an SQL query of the form
SELECT * FROM <table> WHERE <where>
as a list of row dictionaries keyed on column names and valued on entry values.
"""
def _select(self, table, where=[]):
if table not in TABLES:
raise ValueError('Unknown table "{0}".'.format(table))
with self.store:
cursor = self.store.cursor()
fmt = 'SELECT * FROM {0}'.format(table)
if len(where) != 0:
placeholders, values = _escape(where, 'AND ')
fmt += ' WHERE ' + placeholders
cursor.execute(fmt, values)
else:
cursor.execute(fmt)
rows = cursor.fetchall()
keys = map(lambda k : k[0], TABLES[table])
return map(partial(toDict, keys), rows)
"""
Returns the new primary key of a newly inserted row resulting from a query of
the form:
INSERT INTO <table>(<keys>) VALUES(<values>)
"""
def _insert(self, table, keys, values):
if table not in TABLES:
raise ValueError('Unknown table "{0}".'.format(table))
fmt = 'INSERT INTO {0}({1}) VALUES({2})'
fmt_vals = table, ','.join(keys), ','.join(['?'] * len(values))
with self.store:
cursor = self.store.cursor()
cursor.execute(fmt.format(*fmt_vals), values)
self.store.commit()
return cursor.lastrowid
"""
Adds a new table for each described in db_scheme.TABLES. Assumes no such TABLES
have already been created in this store.
"""
def createTables(self):
fmt = 'CREATE TABLE {0}({1})'
with self.store:
cursor = self.store.cursor()
for name in TABLES:
columns = map(lambda c : ' '.join(c), TABLES[name])
cursor.execute(fmt.format(name, ', '.join(columns)))
self.store.commit()
"""
Loads a simple admin (user, password) configuration based on a configuration file
with the two-line format:
<username>
<sha256 hash>
Raises IOError if the file with 'filename' is improperly formatted.
"""
def _loadAdminConf(self, filename):
try:
lines = map(lambda s : s.strip(), list(open(filename)))
self.admin_name, self.admin_pass = lines
except:
raise IOError('Malformed admin configuration file.')
def __init__(self, **kwargs):
self.register_event_type('on_pre_start')
super(ThreeDoListApp, self).__init__(**kwargs)
if not self.db:
connection = Connection('db.db')
#connection = Connection(self.user_data_dir + '/db.db')
cursor = connection.cursor()
cursor.execute("""
PRAGMA user_version=1;
CREATE TABLE [table of contents](
page_number UNSIGNED INTEGER,
page TEXT PRIMARY KEY,
bookmark UNSIGNED SHORT INTEGER DEFAULT 0);
CREATE TABLE [notebook](
ix UNSIGNED INTEGER,
what TEXT DEFAULT '',
when_ TEXT DEFAULT '',
where_ TEXT DEFAULT '',
why UNSIGNED SHORT INTEGER DEFAULT 0,
how TEXT DEFAULT '',
page TEXT,
UNIQUE(page, ix),
FOREIGN KEY(page) REFERENCES [table of contents](page) ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TABLE [archive](
ix UNSIGNED INTEGER,
what TEXT DEFAULT '',
when_ TEXT DEFAULT '',
where_ TEXT DEFAULT '',
why UNSIGNED SHORT INTEGER DEFAULT 0,
how TEXT DEFAULT '',
page TEXT,
FOREIGN KEY(page) REFERENCES [table of contents](page) ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TRIGGER [on_new_page]
AFTER INSERT ON [table of contents]
BEGIN
INSERT INTO [notebook](page, ix) VALUES(NEW.page, 1);
INSERT INTO [notebook](page, ix) VALUES(NEW.page, 2);
INSERT INTO [notebook](page, ix) VALUES(NEW.page, 3);
END;
CREATE TRIGGER [soft_delete]
BEFORE DELETE ON [notebook]
WHEN OLD.ix<4 AND OLD.page=(SELECT page FROM [table of contents] WHERE bookmark=1)
BEGIN
UPDATE [notebook] SET what='', when_='', where_='', why=0, how='' WHERE ix=OLD.ix AND page=OLD.page;
SELECT RAISE(IGNORE);
END;
CREATE TRIGGER [new_action_item]
AFTER UPDATE ON [notebook]
WHEN NEW.what=''
BEGIN
DELETE FROM [notebook] WHERE ix>3 AND WHAT='' AND page=(SELECT page FROM [table of contents] WHERE bookmark=1);
END;
CREATE TRIGGER [on_complete]
AFTER INSERT ON archive
BEGIN
DELETE FROM [notebook] WHERE page=NEW.page AND ix=NEW.ix AND what=NEW.what;
END;
INSERT INTO [table of contents](page_number, page)
VALUES(1, 'Main List');
INSERT INTO [table of contents](page_number, page) VALUES(2, 'Sample List');
UPDATE [notebook] SET what='Click Me', when_='', where_='', why=0, how='Double-tap any one of us' WHERE page='Sample List' AND ix=1;
INSERT INTO [notebook](page, ix, what, how) VALUES('Sample List', 4, 'Swipe right to complete me', 'You can find me later in the "Archive Screen"');
INSERT INTO [notebook](page, ix, what, how) VALUES('Sample List', 5, 'Swipe left to delete me', 'You can also delete a list itself in the "List Screen" this way.');
INSERT INTO [notebook](page, ix, what, how) VALUES('Sample List', 6, 'Double-tap to rename me', 'You can also rename a list itself in the "List Screen" this way.');
INSERT INTO [notebook](page, ix, what, how) VALUES('Sample List', 7, 'Press and hold to Drag-N''-Drop', 'You can re-order your tasks this way.');
INSERT INTO [notebook](page, ix, what, how) VALUES('Sample List', 8, 'Drag me to an Action Item.', 'The Action Items are your top 3 tasks to focus on at a time.');
""")
#cursor.execute("commit")
self.db = connection
self.db.cursor().execute("PRAGMA foreign_keys=ON;")
