Coverage for app \ services \ sqlite_store.py: 83%

1import sqlite3 

2from pathlib import Path 

3 

4from app.settings import SQLITE_PATH 

5 #Opens a Database connection 

6def get_conn() -> sqlite3.Connection: 

7 # Ensure the DB directory exists before opening the SQLite file. 

8 db_path = Path(SQLITE_PATH) 

9 db_path.parent.mkdir(parents=True, exist_ok=True) 

10 

11 # row_factory lets us access columns by name (row["expert_id"]) 

12 conn = sqlite3.connect(db_path, check_same_thread=False) 

13 conn.row_factory = sqlite3.Row 

14 conn.execute("PRAGMA foreign_keys = ON;") 

15 return conn 

16 

17 

18def _children_needs_unlink_migration(conn: sqlite3.Connection) -> bool: 

19 rows = conn.execute("PRAGMA table_info(children)").fetchall() 

20 if not rows: 

21 return False 

22 for row in rows: 

23 if row["name"] == "expert_id": 

24 # old schema had NOT NULL expert_id, new schema allows NULL for unlink 

25 return bool(row["notnull"]) 

26 return False 

27 

28 

29def _migrate_drop_unique_name_index(conn: sqlite3.Connection) -> None: 

30 indexes = {row["name"] for row in conn.execute("PRAGMA index_list(children)").fetchall()} 

31 if "idx_children_unique_profile_per_expert" in indexes: 

32 conn.execute("DROP INDEX idx_children_unique_profile_per_expert") 

33 conn.commit() 

34 

35 

36def _migrate_add_interaction_mode(conn: sqlite3.Connection) -> None: 

37 cols = {row["name"] for row in conn.execute("PRAGMA table_info(children)").fetchall()} 

38 if "interaction_mode" not in cols: 

39 conn.execute( 

40 "ALTER TABLE children ADD COLUMN interaction_mode TEXT NOT NULL DEFAULT 'flexible'" 

41 ) 

42 conn.commit() 

43 

44def _migrate_add_login_code_plain(conn: sqlite3.Connection) -> None: 

45 cols = {row["name"] for row in conn.execute("PRAGMA table_info(parents)").fetchall()} 

46 if "login_code" not in cols: 

47 conn.execute("ALTER TABLE parents ADD COLUMN login_code TEXT NULL") 

48 conn.commit() 

49 

50 

51def _migrate_add_parent_id(conn: sqlite3.Connection) -> None: 

52 cols = {row["name"] for row in conn.execute("PRAGMA table_info(children)").fetchall()} 

53 if "parent_id" not in cols: 

54 conn.execute( 

55 "ALTER TABLE children ADD COLUMN parent_id TEXT NULL REFERENCES parents(parent_id) ON UPDATE CASCADE ON DELETE SET NULL" 

56 ) 

57 conn.commit() 

58 

59 

60def _migrate_children_for_unlink(conn: sqlite3.Connection) -> None: 

61 if not _children_needs_unlink_migration(conn): 

62 return 

63 

64 conn.execute("PRAGMA foreign_keys = OFF;") 

65 conn.executescript( 

66 """ 

67 CREATE TABLE IF NOT EXISTS children_new( 

68 child_id TEXT PRIMARY KEY 

69 CHECK( 

70 length(child_id) = 6 

71 AND child_id GLOB '[0-9][0-9][0-9][0-9][0-9][0-9]' 

72 ), 

73 expert_id TEXT NULL, 

74 first_name TEXT NOT NULL, 

75 last_name TEXT NOT NULL, 

76 icon_key TEXT NOT NULL, 

77 is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0,1)), 

78 created_at TEXT NOT NULL, 

79 updated_at TEXT NOT NULL, 

80 FOREIGN KEY(expert_id) REFERENCES experts(expert_id) 

81 ON UPDATE CASCADE 

82 ON DELETE SET NULL 

83 ); 

84 

85 INSERT INTO children_new 

86 (child_id, expert_id, first_name, last_name, icon_key, is_active, created_at, updated_at) 

87 SELECT 

88 child_id, expert_id, first_name, last_name, icon_key, is_active, created_at, updated_at 

89 FROM children; 

90 

91 DROP TABLE children; 

92 ALTER TABLE children_new RENAME TO children; 

93 

94 CREATE INDEX IF NOT EXISTS idx_children_expert_id 

95 ON children (expert_id); 

96 

97 CREATE INDEX IF NOT EXISTS idx_children_active_expert 

98 ON children (expert_id, is_active); 

99 

100 """ 

101 ) 

102 conn.execute("PRAGMA foreign_keys = ON;") 

103 

104 

105def init_db() -> None: 

106 #creates table on start up  

107 with get_conn() as conn: 

108 # Idempotent bootstrap: safe to run at every app startup. from documentation 

109 conn.executescript( 

110 """ 

111 CREATE TABLE IF NOT EXISTS experts ( 

112 expert_id TEXT PRIMARY KEY, 

113 display_name TEXT NOT NULL, 

114 password_hash TEXT NOT NULL, 

115 is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)), 

116 created_at TEXT NOT NULL, 

117 updated_at TEXT NOT NULL 

118 ); 

119 

120 CREATE TABLE IF NOT EXISTS videos( 

121 id TEXT PRIMARY KEY, 

122 title TEXT NOT NULL, 

123 thumbnail TEXT, 

124 duration FLOAT DEFAULT 0 

125 ); 

126 

127 CREATE TABLE IF NOT EXISTS video_assignments ( 

128 video_id TEXT PRIMARY KEY, 

129 expert_id TEXT NULL, 

130 assignment_source TEXT NOT NULL CHECK (assignment_source IN ('admin', 'expert_claim', 'unassigned')), 

131 assigned_at TEXT NULL, 

132 updated_at TEXT NOT NULL, 

133 FOREIGN KEY (expert_id) REFERENCES experts(expert_id) 

134 ON UPDATE CASCADE 

135 ON DELETE SET NULL 

136 ); 

137 

138 CREATE INDEX IF NOT EXISTS idx_video_assignments_expert_id 

139 ON video_assignments (expert_id); 

140 

141 CREATE TABLE IF NOT EXISTS video_expert_assignments( 

142  

143 video_id TEXT NOT NULL, 

144 expert_id TEXT NOT NULL, 

145 assignment_source TEXT NOT NULL CHECK(assignment_source IN ('admin', 'expert_claim', 'unassigned')), 

146 assigned_at TEXT NOT NULL, 

147 updated_at TEXT NOT NULL, 

148 PRIMARY KEY (video_id, expert_id), 

149 FOREIGN KEY (expert_id) REFERENCES experts(expert_id) 

150 ON UPDATE CASCADE 

151 ON DELETE CASCADE  

152 ); 

153 

154 CREATE INDEX IF NOT EXISTS idx_video_expert_assignments_video_id 

155 ON video_expert_assignments (video_id); 

156 

157 CREATE INDEX IF NOT EXISTS idx_video_expert_assignments_expert_id 

158 ON video_expert_assignments (expert_id); 

159 

160 

161 INSERT OR IGNORE INTO video_expert_assignments 

162 (video_id,expert_id,assignment_source,assigned_at,updated_at) 

163 SELECT 

164 video_id, 

165 expert_id, 

166 COALESCE(NULLIF(assignment_source, ''), 'admin'), 

167 COALESCE(assigned_at, updated_at), 

168 updated_at 

169 FROM video_assignments 

170 WHERE expert_id IS NOT NULL; 

171 

172 CREATE TABLE IF NOT EXISTS children( 

173 child_id TEXT PRIMARY KEY 

174 CHECK( 

175 length(child_id) = 6 

176 AND child_id GLOB '[0-9][0-9][0-9][0-9][0-9][0-9]' 

177 ), 

178 expert_id TEXT NULL, 

179 first_name TEXT NOT NULL, 

180 last_name TEXT NOT NULL, 

181 icon_key TEXT NOT NULL, 

182 interaction_mode TEXT NOT NULL DEFAULT 'flexible', 

183 is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0,1)), 

184 created_at TEXT NOT NULL, 

185 updated_at TEXT NOT NULL, 

186 FOREIGN KEY(expert_id) REFERENCES experts(expert_id) 

187 ON UPDATE CASCADE 

188 ON DELETE SET NULL 

189 ); 

190  

191 CREATE INDEX IF NOT EXISTS idx_children_expert_id 

192 ON children (expert_id); 

193  

194 CREATE INDEX IF NOT EXISTS idx_children_active_expert 

195 ON children (expert_id, is_active);  

196  

197 CREATE TABLE IF NOT EXISTS parents( 

198 parent_id TEXT PRIMARY KEY, 

199 display_name TEXT NOT NULL, 

200 login_code_hash TEXT NOT NULL, 

201 is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN(0,1)), 

202 created_at TEXT NOT NULL, 

203 updated_at TEXT NOT NULL 

204 ); 

205  

206 CREATE INDEX IF NOT EXISTS idx_parents_active 

207 ON parents (is_active); 

208 

209 CREATE TABLE IF NOT EXISTS quiz_attempts( 

210 

211 attempt_id INTEGER PRIMARY KEY AUTOINCREMENT, 

212 video_id TEXT NOT NULL, 

213 child_id TEXT NOT NULL, 

214 interaction_mode TEXT, 

215 timestamp TEXT, 

216 total_questions INT, 

217 correct INT, 

218 incorrect INT, 

219 percentage FLOAT, 

220 FOREIGN KEY (video_id) REFERENCES video_assignments(video_id), 

221 FOREIGN KEY (child_id) REFERENCES children(child_id) 

222 ); 

223 

224 """ 

225 

226 

227 

228 ) 

229 _migrate_children_for_unlink(conn) 

230 _migrate_add_interaction_mode(conn) 

231 _migrate_drop_unique_name_index(conn) 

232 _migrate_add_parent_id(conn) 

233 _migrate_add_login_code_plain(conn) 

234 conn.commit()