| 1 | """Tests for `storage/database.py` migration + `delete_aweme_by_ids`. |
| 2 | |
| 3 | Covers task 1.2 of the desktop-ux-overhaul spec: |
| 4 | |
| 5 | 1. Legacy DB without the `author_sec_uid` column -> `initialize()` adds it. |
| 6 | 2. Second `initialize()` is a no-op (idempotent). |
| 7 | 3. `add_aweme` persists None / non-null values for `author_sec_uid` correctly, |
| 8 | including the payload-key fallback. |
| 9 | 4. `get_aweme_history` surfaces `author_sec_uid` on each returned item. |
| 10 | 5. `delete_aweme_by_ids(["a","b"])` removes only matching rows and returns |
| 11 | the affected row count. |
| 12 | 6. Empty list is a no-op returning 0; duplicate ids don't double-count. |
| 13 | """ |
| 14 | |
| 15 | import aiosqlite |
| 16 | |
| 17 | from storage.database import Database |
| 18 | |
| 19 | # --------------------------------------------------------------------------- |
| 20 | # Legacy DDL: the `aweme` table as it existed BEFORE the `author_sec_uid` |
| 21 | # migration. Creating this directly lets us prove the migration upgrades an |
| 22 | # existing, pre-populated database in place without data loss. |
| 23 | # --------------------------------------------------------------------------- |
| 24 | _LEGACY_AWEME_DDL = """ |
| 25 | CREATE TABLE IF NOT EXISTS aweme ( |
| 26 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 27 | aweme_id TEXT UNIQUE NOT NULL, |
| 28 | aweme_type TEXT NOT NULL, |
| 29 | title TEXT, |
| 30 | author_id TEXT, |
| 31 | author_name TEXT, |
| 32 | create_time INTEGER, |
| 33 | download_time INTEGER, |
| 34 | file_path TEXT, |
| 35 | metadata TEXT |
| 36 | ) |
| 37 | """ |
| 38 | |
| 39 | |
| 40 | async def _table_columns(db_path: str, table: str): |
| 41 | """Return the set of column names for the given table via PRAGMA.""" |
| 42 | async with aiosqlite.connect(db_path) as conn: |
| 43 | cursor = await conn.execute(f"PRAGMA table_info({table})") |
| 44 | rows = await cursor.fetchall() |
| 45 | return {row[1] for row in rows} |
| 46 | |
| 47 | |
| 48 | # --------------------------------------------------------------------------- |
| 49 | # 1. Migration — column added onto a legacy DB |
| 50 | # --------------------------------------------------------------------------- |
| 51 | async def test_initialize_adds_author_sec_uid_to_legacy_db(tmp_path): |
| 52 | db_path = tmp_path / "test.db" |
| 53 | |
| 54 | # Simulate a pre-migration database: the aweme table exists WITHOUT the |
| 55 | # author_sec_uid column and contains a row. The migration must be additive. |
| 56 | async with aiosqlite.connect(str(db_path)) as raw: |
| 57 | await raw.execute(_LEGACY_AWEME_DDL) |
| 58 | await raw.execute( |
| 59 | """ |
| 60 | INSERT INTO aweme |
| 61 | (aweme_id, aweme_type, title, author_id, author_name, |
| 62 | create_time, download_time, file_path, metadata) |
| 63 | VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) |
| 64 | """, |
| 65 | ("legacy_1", "video", "t", "u", "A", 1700000000, 1700000000, "/tmp", "{}"), |
| 66 | ) |
| 67 | await raw.commit() |
| 68 | |
| 69 | pre_cols = await _table_columns(str(db_path), "aweme") |
| 70 | assert "author_sec_uid" not in pre_cols, "fixture should start pre-migration" |
| 71 | |
| 72 | db = Database(db_path=str(db_path)) |
| 73 | await db.initialize() |
| 74 | try: |
| 75 | post_cols = await _table_columns(str(db_path), "aweme") |
| 76 | assert "author_sec_uid" in post_cols |
| 77 | |
| 78 | # Legacy row must still exist and the new column defaults to NULL. |
| 79 | conn = await db._get_conn() |
| 80 | cursor = await conn.execute( |
| 81 | "SELECT aweme_id, author_sec_uid FROM aweme WHERE aweme_id = ?", |
| 82 | ("legacy_1",), |
| 83 | ) |
| 84 | row = await cursor.fetchone() |
| 85 | assert row == ("legacy_1", None) |
| 86 | finally: |
| 87 | await db.close() |
| 88 | |
| 89 | |
| 90 | # --------------------------------------------------------------------------- |
| 91 | # 2. Idempotent migration |
| 92 | # --------------------------------------------------------------------------- |
| 93 | async def test_initialize_is_idempotent_on_same_instance(tmp_path): |
| 94 | db = Database(db_path=str(tmp_path / "test.db")) |
| 95 | try: |
| 96 | await db.initialize() |
| 97 | # Second call on the same instance must not raise and must leave the |
| 98 | # schema intact. |
| 99 | await db.initialize() |
| 100 | cols = await _table_columns(db.db_path, "aweme") |
| 101 | assert "author_sec_uid" in cols |
| 102 | finally: |
| 103 | await db.close() |
| 104 | |
| 105 | |
| 106 | async def test_initialize_is_idempotent_across_instances(tmp_path): |
| 107 | path = str(tmp_path / "test.db") |
| 108 | |
| 109 | db1 = Database(db_path=path) |
| 110 | await db1.initialize() |
| 111 | await db1.close() |
| 112 | |
| 113 | # A brand-new Database instance pointing at an already-migrated file must |
| 114 | # also complete initialize() without error (no duplicate ALTER TABLE, etc.). |
| 115 | db2 = Database(db_path=path) |
| 116 | try: |
| 117 | await db2.initialize() |
| 118 | cols = await _table_columns(path, "aweme") |
| 119 | assert "author_sec_uid" in cols |
| 120 | finally: |
| 121 | await db2.close() |
| 122 | |
| 123 | |
| 124 | # --------------------------------------------------------------------------- |
| 125 | # 3. add_aweme persists author_sec_uid (kwarg, payload-key, or NULL) |
| 126 | # --------------------------------------------------------------------------- |
| 127 | def _base_payload(aweme_id: str): |
| 128 | return { |
| 129 | "aweme_id": aweme_id, |
| 130 | "aweme_type": "video", |
| 131 | "title": f"title-{aweme_id}", |
| 132 | "author_id": "u1", |
| 133 | "author_name": "Alice", |
| 134 | "create_time": 1700000000, |
| 135 | "file_path": f"/tmp/{aweme_id}", |
| 136 | "metadata": "{}", |
| 137 | } |
| 138 | |
| 139 | |
| 140 | async def _fetch_sec_uid(db: Database, aweme_id: str): |
| 141 | conn = await db._get_conn() |
| 142 | cursor = await conn.execute("SELECT author_sec_uid FROM aweme WHERE aweme_id = ?", (aweme_id,)) |
| 143 | row = await cursor.fetchone() |
| 144 | return None if row is None else row[0] |
| 145 | |
| 146 | |
| 147 | async def test_add_aweme_persists_explicit_author_sec_uid(tmp_path): |
| 148 | db = Database(db_path=str(tmp_path / "test.db")) |
| 149 | await db.initialize() |
| 150 | try: |
| 151 | await db.add_aweme(_base_payload("id1"), author_sec_uid="SEC_X") |
| 152 | assert await _fetch_sec_uid(db, "id1") == "SEC_X" |
| 153 | finally: |
| 154 | await db.close() |
| 155 | |
| 156 | |
| 157 | async def test_add_aweme_persists_null_when_nothing_provided(tmp_path): |
| 158 | db = Database(db_path=str(tmp_path / "test.db")) |
| 159 | await db.initialize() |
| 160 | try: |
| 161 | await db.add_aweme(_base_payload("id2")) # no kwarg, no payload key |
| 162 | assert await _fetch_sec_uid(db, "id2") is None |
| 163 | finally: |
| 164 | await db.close() |
| 165 | |
| 166 | |
| 167 | async def test_add_aweme_falls_back_to_payload_key(tmp_path): |
| 168 | """When no kwarg is given, the value from the payload dict is used.""" |
| 169 | db = Database(db_path=str(tmp_path / "test.db")) |
| 170 | await db.initialize() |
| 171 | try: |
| 172 | payload = _base_payload("id3") |
| 173 | payload["author_sec_uid"] = "SEC_FROM_PAYLOAD" |
| 174 | await db.add_aweme(payload) |
| 175 | assert await _fetch_sec_uid(db, "id3") == "SEC_FROM_PAYLOAD" |
| 176 | finally: |
| 177 | await db.close() |
| 178 | |
| 179 | |
| 180 | async def test_add_aweme_kwarg_wins_over_payload_key(tmp_path): |
| 181 | """When both are provided, the explicit kwarg takes precedence.""" |
| 182 | db = Database(db_path=str(tmp_path / "test.db")) |
| 183 | await db.initialize() |
| 184 | try: |
| 185 | payload = _base_payload("id4") |
| 186 | payload["author_sec_uid"] = "SEC_FROM_PAYLOAD" |
| 187 | await db.add_aweme(payload, author_sec_uid="SEC_FROM_KWARG") |
| 188 | assert await _fetch_sec_uid(db, "id4") == "SEC_FROM_KWARG" |
| 189 | finally: |
| 190 | await db.close() |
| 191 | |
| 192 | |
| 193 | # --------------------------------------------------------------------------- |
| 194 | # 4. get_aweme_history surfaces author_sec_uid |
| 195 | # --------------------------------------------------------------------------- |
| 196 | async def test_get_aweme_history_returns_author_sec_uid(tmp_path): |
| 197 | db = Database(db_path=str(tmp_path / "test.db")) |
| 198 | await db.initialize() |
| 199 | try: |
| 200 | await db.add_aweme(_base_payload("with_sec"), author_sec_uid="SEC_ABC") |
| 201 | await db.add_aweme(_base_payload("without_sec")) # NULL |
| 202 | |
| 203 | res = await db.get_aweme_history(page=1, size=10) |
| 204 | assert res["total"] == 2 |
| 205 | |
| 206 | by_id = {item["aweme_id"]: item for item in res["items"]} |
| 207 | assert "author_sec_uid" in by_id["with_sec"] |
| 208 | assert by_id["with_sec"]["author_sec_uid"] == "SEC_ABC" |
| 209 | assert by_id["without_sec"]["author_sec_uid"] is None |
| 210 | finally: |
| 211 | await db.close() |
| 212 | |
| 213 | |
| 214 | # --------------------------------------------------------------------------- |
| 215 | # 5. delete_aweme_by_ids — happy path |
| 216 | # --------------------------------------------------------------------------- |
| 217 | async def test_delete_aweme_by_ids_removes_only_matching_rows(tmp_path): |
| 218 | db = Database(db_path=str(tmp_path / "test.db")) |
| 219 | await db.initialize() |
| 220 | try: |
| 221 | for aid in ("a", "b", "c"): |
| 222 | await db.add_aweme(_base_payload(aid)) |
| 223 | |
| 224 | deleted = await db.delete_aweme_by_ids(["a", "b"]) |
| 225 | assert deleted == 2 |
| 226 | |
| 227 | assert await db.is_downloaded("a") is False |
| 228 | assert await db.is_downloaded("b") is False |
| 229 | assert await db.is_downloaded("c") is True |
| 230 | finally: |
| 231 | await db.close() |
| 232 | |
| 233 | |
| 234 | async def test_delete_aweme_by_ids_ignores_unknown_ids(tmp_path): |
| 235 | """Unknown ids simply contribute 0 to the count; known ones are removed.""" |
| 236 | db = Database(db_path=str(tmp_path / "test.db")) |
| 237 | await db.initialize() |
| 238 | try: |
| 239 | await db.add_aweme(_base_payload("a")) |
| 240 | deleted = await db.delete_aweme_by_ids(["a", "does-not-exist"]) |
| 241 | assert deleted == 1 |
| 242 | assert await db.is_downloaded("a") is False |
| 243 | finally: |
| 244 | await db.close() |
| 245 | |
| 246 | |
| 247 | # --------------------------------------------------------------------------- |
| 248 | # 6. delete_aweme_by_ids — empty list / duplicate ids |
| 249 | # --------------------------------------------------------------------------- |
| 250 | async def test_delete_aweme_by_ids_empty_list_is_noop(tmp_path): |
| 251 | db = Database(db_path=str(tmp_path / "test.db")) |
| 252 | await db.initialize() |
| 253 | try: |
| 254 | await db.add_aweme(_base_payload("a")) |
| 255 | |
| 256 | deleted = await db.delete_aweme_by_ids([]) |
| 257 | assert deleted == 0 |
| 258 | # The previously inserted row must still be present. |
| 259 | assert await db.is_downloaded("a") is True |
| 260 | finally: |
| 261 | await db.close() |
| 262 | |
| 263 | |
| 264 | async def test_delete_aweme_by_ids_dedupes_duplicate_ids(tmp_path): |
| 265 | """Passing the same id twice must not inflate the deleted count.""" |
| 266 | db = Database(db_path=str(tmp_path / "test.db")) |
| 267 | await db.initialize() |
| 268 | try: |
| 269 | await db.add_aweme(_base_payload("a")) |
| 270 | deleted = await db.delete_aweme_by_ids(["a", "a"]) |
| 271 | assert deleted == 1 |
| 272 | assert await db.is_downloaded("a") is False |
| 273 | finally: |
| 274 | await db.close() |
| 275 |