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

1"""SQLite snapshots and the schema checks every backed-up database must pass.""" 

2 

3from __future__ import annotations 

4 

5import os 

6import sqlite3 

7from pathlib import Path 

8 

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) 

22 

23_SUPPORTED_APPLICATION_COMPONENTS = { 

24 component.name: component.revision for component in APPLICATION_COMPONENTS 

25} 

26 

27 

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) 

39 

40 

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") 

108 

109 

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 ) 

127 

128 

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} 

142 

143 

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] 

159 

160 

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 

195 

196 

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()