Coverage for src/copytrading_engine/backup/databases.py: 76%
130 statements
« prev ^ index » next coverage.py v7.16.2, created at 2026-11-16 16:55 +0000
« prev ^ index » next coverage.py v7.16.2, created at 2026-11-16 16:55 +0000
1"""SQLite snapshots and the schema checks every backed-up database must pass."""
3from __future__ import annotations
5import os
6import sqlite3
7from pathlib import Path
9from copytrading_engine.backup.files import require_regular_file
10from copytrading_engine.backup.manifest import (
11 ACCOUNT_DATABASE,
12 APPLICATION_SCHEMA_PREFIX,
13 BackupManifestError,
14 account_schema_version,
15 json_object,
16)
17from copytrading_engine.backup.ports import AccountSnapshotSchemaProvider, SchemaCatalogProvider
18from copytrading_engine.backup.schema_catalog import (
19 APPLICATION_COMPONENTS,
20 APPLICATION_DATABASE_VERSION,
21)
23_SUPPORTED_APPLICATION_COMPONENTS = {
24 component.name: component.revision for component in APPLICATION_COMPONENTS
25}
28def snapshot_database(source: Path, destination: Path) -> None:
29 require_regular_file(source, "operational database is invalid")
30 destination.parent.mkdir(mode=0o700, parents=True, exist_ok=True)
31 source_db = sqlite3.connect(source, timeout=5)
32 target_db = sqlite3.connect(destination, timeout=5)
33 try:
34 source_db.backup(target_db, pages=256, sleep=0.01)
35 finally:
36 target_db.close()
37 source_db.close()
38 os.chmod(destination, 0o600)
41def sqlite_schema_version(
42 path: Path,
43 member_path: str,
44 schema_catalog: SchemaCatalogProvider,
45 snapshot_schema: AccountSnapshotSchemaProvider,
46) -> str:
47 connection = sqlite3.connect(path)
48 try:
49 if connection.execute("PRAGMA integrity_check").fetchone() != ("ok",): 49 ↛ 50line 49 didn't jump to line 50 because the condition on line 49 was never true
50 raise BackupManifestError(f"operational SQLite database is corrupt: {member_path}")
51 if member_path == "application.db":
52 user_version = connection.execute("PRAGMA user_version").fetchone()[0]
53 if user_version != APPLICATION_DATABASE_VERSION: 53 ↛ 54line 53 didn't jump to line 54 because the condition on line 53 was never true
54 raise BackupManifestError("operational database schema is unsupported")
55 rows = _component_revisions(connection)
56 if set(rows) != set(_SUPPORTED_APPLICATION_COMPONENTS):
57 raise BackupManifestError("operational component schema is incomplete")
58 if rows != _SUPPORTED_APPLICATION_COMPONENTS: 58 ↛ 59line 58 didn't jump to line 59 because the condition on line 58 was never true
59 raise BackupManifestError("operational component schema is unsupported")
60 if _schema_catalog(connection) != schema_catalog(member_path):
61 raise BackupManifestError("operational database schema structure is incompatible")
62 components = ",".join(f"{name}:{revision}" for name, revision in sorted(rows.items()))
63 return f"{APPLICATION_SCHEMA_PREFIX}{components}"
64 if ACCOUNT_DATABASE.fullmatch(member_path): 64 ↛ 106line 64 didn't jump to line 106 because the condition on line 64 was always true
65 rows = _component_revisions(connection)
66 execution_revision = snapshot_schema.execution_schema_revision
67 if type(execution_revision) is not int or execution_revision <= 0: 67 ↛ 68line 67 didn't jump to line 68 because the condition on line 67 was never true
68 raise BackupManifestError("account execution schema is unsupported")
69 if rows != {"execution": execution_revision}: 69 ↛ 70line 69 didn't jump to line 70 because the condition on line 69 was never true
70 raise BackupManifestError("account execution schema is unsupported")
71 if _schema_catalog(connection) != schema_catalog(member_path):
72 raise BackupManifestError(
73 "account execution database schema structure is incompatible"
74 )
75 snapshot_row = connection.execute(
76 "SELECT data FROM snapshot WHERE singleton=1"
77 ).fetchone()
78 identity_row = connection.execute(
79 "SELECT environment,account_id FROM identity WHERE singleton=1"
80 ).fetchone()
81 if snapshot_row is not None:
82 serialized_snapshot = snapshot_row[0]
83 if not isinstance(serialized_snapshot, str): 83 ↛ 84line 83 didn't jump to line 84 because the condition on line 83 was never true
84 raise BackupManifestError("account ledger snapshot is invalid")
85 try:
86 snapshot_environment, snapshot_account_id = snapshot_schema.validate_snapshot(
87 serialized_snapshot
88 )
89 except (BackupManifestError, UnicodeError, TypeError, ValueError) as exc:
90 raise BackupManifestError(
91 "account ledger snapshot is invalid or unsupported"
92 ) from exc
93 if identity_row is None or ( 93 ↛ 97line 93 didn't jump to line 97 because the condition on line 93 was never true
94 snapshot_environment,
95 snapshot_account_id,
96 ) != (identity_row[0], identity_row[1]):
97 raise BackupManifestError(
98 "account ledger snapshot identity does not match its database"
99 )
100 if identity_row is not None and identity_row[0] not in {"paper", "live"}: 100 ↛ 101line 100 didn't jump to line 101 because the condition on line 100 was never true
101 raise BackupManifestError("account environment identity is invalid")
102 return account_schema_version(snapshot_schema)
103 except sqlite3.Error as exc:
104 raise BackupManifestError(f"SQLite database is invalid: {member_path}") from exc
105 finally:
106 connection.close()
107 raise BackupManifestError("unsupported SQLite member")
110def _schema_catalog(
111 connection: sqlite3.Connection,
112) -> tuple[tuple[str, str, str, str], ...]:
113 objects = connection.execute(
114 "SELECT type,name,tbl_name,sql FROM sqlite_master "
115 "WHERE name NOT LIKE 'sqlite_%' ORDER BY type,name"
116 ).fetchall()
117 return tuple(
118 (
119 kind,
120 name,
121 table_name,
122 statement.strip(),
123 )
124 for kind, name, table_name, statement in objects
125 if isinstance(statement, str)
126 )
129def _component_revisions(connection: sqlite3.Connection) -> dict[str, int]:
130 exists = connection.execute(
131 "SELECT 1 FROM sqlite_master WHERE type='table' "
132 "AND name='copytrading_engine_schema_revisions'"
133 ).fetchone()
134 if exists is None:
135 return {}
136 rows = connection.execute(
137 "SELECT component,revision FROM copytrading_engine_schema_revisions ORDER BY component"
138 ).fetchall()
139 if any(not isinstance(name, str) or type(revision) is not int for name, revision in rows): 139 ↛ 140line 139 didn't jump to line 140 because the condition on line 139 was never true
140 raise BackupManifestError("SQLite component schema table is invalid")
141 return {name: revision for name, revision in rows}
144def account_environment(path: Path) -> str:
145 """The ledger's environment. Its broker account number is checked against the broker, never
146 against the account's folder, which holds the app's own name for the account."""
147 connection = sqlite3.connect(path)
148 try:
149 row = connection.execute(
150 "SELECT environment,account_id FROM identity WHERE singleton=1"
151 ).fetchone()
152 except sqlite3.Error as exc:
153 raise BackupManifestError("account identity is unavailable") from exc
154 finally:
155 connection.close()
156 if row is None or row[0] not in {"paper", "live"} or not isinstance(row[1], str): 156 ↛ 157line 156 didn't jump to line 157 because the condition on line 156 was never true
157 raise BackupManifestError("account identity is invalid")
158 return row[0]
161def validate_sqlite(
162 path: Path,
163 member_path: str,
164 installation_id: str,
165 schema_catalog: SchemaCatalogProvider,
166 snapshot_schema: AccountSnapshotSchemaProvider,
167) -> str:
168 schema_version = sqlite_schema_version(path, member_path, schema_catalog, snapshot_schema)
169 if member_path == "application.db":
170 connection = sqlite3.connect(path)
171 try:
172 row = connection.execute(
173 "SELECT value FROM engine_metadata WHERE key='instance_id'"
174 ).fetchone()
175 except sqlite3.Error as exc:
176 raise BackupManifestError("operational installation identity is unavailable") from exc
177 finally:
178 connection.close()
179 if row is None or row[0] != installation_id: 179 ↛ 180line 179 didn't jump to line 180 because the condition on line 179 was never true
180 raise BackupManifestError("operational database belongs to a different installation")
181 elif ACCOUNT_DATABASE.fullmatch(member_path): 181 ↛ 194line 181 didn't jump to line 194 because the condition on line 181 was always true
182 account_environment(path)
183 connection = sqlite3.connect(path)
184 try:
185 snapshot_row = connection.execute(
186 "SELECT data FROM snapshot WHERE singleton=1"
187 ).fetchone()
188 except sqlite3.Error as exc:
189 raise BackupManifestError("account ledger state is unavailable") from exc
190 finally:
191 connection.close()
192 if snapshot_row is None: 192 ↛ 193line 192 didn't jump to line 193 because the condition on line 192 was never true
193 raise BackupManifestError("account ledger state is incomplete")
194 return schema_version
197def require_manual_disabled_account(path: Path) -> None:
198 connection = sqlite3.connect(path)
199 try:
200 row = connection.execute("SELECT data FROM snapshot WHERE singleton=1").fetchone()
201 if row is None or not isinstance(row[0], str): 201 ↛ 202line 201 didn't jump to line 202 because the condition on line 201 was never true
202 raise BackupManifestError("restored account state is unavailable")
203 snapshot = json_object(row[0].encode("utf-8"))
204 control = snapshot.get("control")
205 if not isinstance(control, dict) or ( 205 ↛ 209line 205 didn't jump to line 209 because the condition on line 205 was never true
206 control.get("entry_permission"),
207 control.get("recovery_preference"),
208 ) != ("disabled", "manual"):
209 raise BackupManifestError("restored account is not durably manual-disabled")
210 commands = control.get("commands")
211 manual_command = (
212 next(
213 (
214 result
215 for result in commands.values()
216 if isinstance(result, dict)
217 and isinstance(result.get("command"), dict)
218 and result["command"].get("action") == "restore_manual"
219 and result.get("entry_permission") == "disabled"
220 and result.get("recovery_preference") == "manual"
221 ),
222 None,
223 )
224 if isinstance(commands, dict)
225 else None
226 )
227 journal_row = connection.execute(
228 "SELECT event FROM journal ORDER BY id DESC LIMIT 1"
229 ).fetchone()
230 if manual_command is None or journal_row is None: 230 ↛ 231line 230 didn't jump to line 231 because the condition on line 230 was never true
231 raise BackupManifestError("restored account manual state lacks its audit event")
232 event = json_object(journal_row[0].encode("utf-8"))
233 payload = event.get("payload")
234 if ( 234 ↛ 239line 234 didn't jump to line 239 because the condition on line 234 was never true
235 not isinstance(payload, dict)
236 or payload.get("kind") != "account_control_changed"
237 or payload.get("result") != manual_command
238 ):
239 raise BackupManifestError("restored account manual state lacks its audit event")
240 except sqlite3.Error as exc:
241 raise BackupManifestError("restored account state is unavailable") from exc
242 finally:
243 connection.close()