def apply(self, conn: dbver.Connection) -> None:
super().apply(conn)
if not self._ordered_te_rows:
return
oldest, newest = self._ordered_te_rows[-1], self._ordered_te_rows[0]
cur = conn.cursor()
cur.execute("create temp table ids (id integer not null primary key)")
try:
cur.executemany(
"insert into temp.ids (id) values (?)",
[(row["id"], ) for row in self._te_rows],
)
if self._offset + len(self._te_rows) >= self._total:
# This result set represents the oldest torrent entries, so
# delete all older ones
cur.execute(
"update torrent_entry set deleted = 1 "
"where time <= ? and id < ? and not deleted",
(oldest["time"], oldest["id"]),
)
cur.execute(
"update torrent_entry set deleted = 1 "
"where (not deleted) and time < ? and time > ? and "
"id not in (select id from temp.ids)",
(newest["time"], oldest["time"]),
)
finally:
cur.execute("drop table temp.ids")
def migrate_2(conn: dbver.Connection, schema: str) -> None:
cur = conn.cursor()
cur.execute(
f'create table "{schema}".a2 (a int primary key, t text)')
cur.execute(
f'insert into "{schema}".a2 select * from "{schema}".a')
cur.execute(f'drop table "{schema}".a')
def get_format_unchecked(self,
conn: dbver.Connection,
schema: str = "main") -> Optional[str]:
if dbver.get_application_id(conn, schema) == 0:
return None
cur = conn.cursor()
cur.execute(f'select name from "{schema}".format')
(name, ) = cast(Tuple[str], cur.fetchone())
return name
def apply(self, conn: dbver.Connection, torrent_entry_id: int = 0) -> None:
if not self._rows:
return
assert (torrent_entry_id > 0) ^ (self._torrent_entry_id > 0)
if torrent_entry_id > 0:
for row in self._rows:
row["id"] = torrent_entry_id
cols = self._rows[0].keys()
query = "".join((
"insert into file_info (",
",".join(cols),
") values (",
",".join(f":{k}" for k in cols),
") on conflict (id, file_index) do update set ",
",".join(f"{k} = :{k}" for k in cols
if k not in ("id", "file_index")),
))
conn.cursor().executemany(query, self._rows)
def set_format(
self,
new_format: Optional[str],
conn: dbver.Connection,
schema: str = "main",
) -> None:
assert new_format is not None
super().set_format(new_format, conn, schema=schema)
cur = conn.cursor()
cur.execute(f'drop table if exists "{schema}".format')
cur.execute(f'create table "{schema}".format (name text not null)')
cur.execute(f'insert into "{schema}".format (name) values (?)',
(new_format, ))
def _update_groups(conn: dbver.Connection, *rows: _GroupRow) -> None:
if not rows:
return
cur = conn.cursor()
cols = rows[0].keys()
query = "".join((
"insert into torrent_entry_group (",
", ".join(cols),
") values (",
", ".join(f":{k}" for k in cols),
") on conflict (id) do update set ",
", ".join(f"{k} = :{k}" for k in cols if k != "id"),
" where ",
" or ".join(f"{k} is not :{k}" for k in cols if k != "id"),
))
cur.executemany(query, rows)
def migrate_1dot1(conn: dbver.Connection, schema: str) -> None:
cur = conn.cursor()
cur.execute(f'alter table "{schema}".a add column t text')
def migrate_1(conn: dbver.Connection, schema: str) -> None:
conn.cursor().execute(
f'create table "{schema}".a (a int primary key)')
def migrate_b_a(conn: dbver.Connection, schema: str = "main") -> None:
cur = conn.cursor()
cur.execute(f'create table "{schema}".a (a int primary key)')
cur.execute(f'insert into "{schema}".a select * from "{schema}".b')
cur.execute(f'drop table "{schema}".b')
def migrate_null_b(conn: dbver.Connection,
schema: str = "main") -> None:
conn.cursor().execute(
f'create table "{schema}".b (b int primary key)')
def apply(self, conn: dbver.Connection) -> None:
conn.cursor().execute(
"insert or ignore into info(id, info) values (?, ?)",
(self._torrent_entry_id, self._info),
)
def _migrate_1(conn: dbver.Connection, schema: str = "main") -> None:
assert schema == "main"
conn.cursor().execute(
"create table info (id integer primary key, info blob not null)")
def apply(self, conn: dbver.Connection) -> None:
if not self._rows:
return
conn.cursor().executemany(self._query, self._rows)
def _migrate_1(conn: dbver.Connection, schema: str = "main") -> None:
assert schema == "main"
conn.cursor().execute(
"create table snatchlist (id integer primary key, downloaded integer, "
"uploaded integer, seed_time integer, seeding tinyint, "
"snatch_time integer, hnr_removed tinyint not null default 0)")
def _migrate_1(conn: dbver.Connection, schema: str) -> None:
sql = importlib_resources.read_text("btn_cache.sql", "metadata_1.0.0.sql")
cur = conn.cursor()
for line in sql.splitlines():
line = line.format(schema=schema)
cur.execute(line)