Coverage for opt/mealie/lib/python3.12/site-packages/mealie/alembic/versions/2024-07-12-16.16.29_feecc8ffb956_add_households.py: 51%

1"""add households 

2 

3Revision ID: feecc8ffb956 

4Revises: 32d69327997b 

5Create Date: 2024-07-12 16:16:29.973929 

6 

7""" 

8 

9from datetime import UTC, datetime 1 ctx1a

10from textwrap import dedent 1 ctx1a

11from typing import Any 1 ctx1a

12from uuid import uuid4 1 ctx1a

13 

14import sqlalchemy as sa 1 ctx1a

15from alembic import op 1 ctx1a

16from slugify import slugify 1 ctx1a

17from sqlalchemy import orm 1 ctx1a

18 

19import mealie.db.migration_types 1 ctx1a

20from mealie.core.config import get_app_settings 1 ctx1a

21 

22# revision identifiers, used by Alembic. 

23revision = "feecc8ffb956" 1 ctx1a

24down_revision = "32d69327997b" 1 ctx1a

25branch_labels: str | tuple[str, ...] | None = None 1 ctx1a

26depends_on: str | tuple[str, ...] | None = None 1 ctx1a

27 

28settings = get_app_settings() 1 ctx1a

29 

30 

31def is_postgres(): 1 ctx1a

32 return op.get_context().dialect.name == "postgresql" 

33 

34 

35def generate_id() -> str: 1 ctx1a

36 """See GUID.convert_value_to_guid""" 

37 val = uuid4() 

38 if is_postgres(): 

39 return str(val) 

40 else: 

41 return f"{val.int:032x}" 

42 

43 

44def dedupe_cookbook_slugs(): 1 ctx1a

45 bind = op.get_bind() 1 ctx1a

46 session = orm.Session(bind=bind) 1 ctx1a

47 with session: 1 ctx1a

48 sql = sa.text( 1 ctx1a

49 dedent( 

50 """ 

51 SELECT slug, group_id, COUNT(*) 

52 FROM cookbooks 

53 GROUP BY slug, group_id 

54 HAVING COUNT(*) > 1 

55 """ 

56 ) 

57 ) 

58 rows = session.execute(sql).fetchall() 1 ctx1a

59 

60 for slug, group_id, _ in rows: 60 ↛ 61line 60 didn't jump to line 61 because the loop on line 60 never started1 ctx1a

61 sql = sa.text( 

62 dedent( 

63 """ 

64 SELECT id 

65 FROM cookbooks 

66 WHERE slug = :slug AND group_id = :group_id 

67 ORDER BY id 

68 """ 

69 ) 

70 ) 

71 cookbook_ids = session.execute(sql, {"slug": slug, "group_id": group_id}).fetchall() 

72 

73 for i, (cookbook_id,) in enumerate(cookbook_ids): 

74 if i == 0: 

75 continue 

76 

77 sql = sa.text( 

78 dedent( 

79 """ 

80 UPDATE cookbooks 

81 SET slug = :slug || '-' || :i 

82 WHERE id = :id 

83 """ 

84 ) 

85 ) 

86 session.execute(sql, {"slug": slug, "i": i, "id": cookbook_id}) 

87 

88 

89def create_household(session: orm.Session, group_id: str) -> str: 1 ctx1a

90 # create/insert household 

91 household_id = generate_id() 

92 timestamp = datetime.now(UTC).isoformat() 

93 household_data = { 

94 "id": household_id, 

95 "name": settings.DEFAULT_HOUSEHOLD, 

96 "slug": slugify(settings.DEFAULT_HOUSEHOLD), 

97 "group_id": group_id, 

98 "created_at": timestamp, 

99 "update_at": timestamp, 

100 } 

101 columns = ", ".join(household_data.keys()) 

102 placeholders = ", ".join(f":{key}" for key in household_data.keys()) 

103 sql_statement = f"INSERT INTO households ({columns}) VALUES ({placeholders})" 

104 

105 session.execute(sa.text(sql_statement), household_data) 

106 

107 # fetch group preferences so we can copy them over to household preferences 

108 migrated_field_defaults = { 

109 "private_group": True, # this is renamed later 

110 "first_day_of_week": 0, 

111 "recipe_public": True, 

112 "recipe_show_nutrition": False, 

113 "recipe_show_assets": False, 

114 "recipe_landscape_view": False, 

115 "recipe_disable_comments": False, 

116 "recipe_disable_amount": True, 

117 } 

118 sql_statement = ( 

119 f"SELECT {', '.join(migrated_field_defaults.keys())} FROM group_preferences WHERE group_id = :group_id" 

120 ) 

121 group_preferences = session.execute(sa.text(sql_statement), {"group_id": group_id}).fetchone() 

122 

123 # build preferences data 

124 if group_preferences: 

125 preferences_data: dict[str, Any] = {} 

126 for i, (field, default_value) in enumerate(migrated_field_defaults.items()): 

127 value = group_preferences[i] 

128 preferences_data[field] = value if value is not None else default_value 

129 else: 

130 preferences_data = migrated_field_defaults 

131 

132 preferences_data["id"] = generate_id() 

133 preferences_data["household_id"] = household_id 

134 preferences_data["created_at"] = timestamp 

135 preferences_data["update_at"] = timestamp 

136 preferences_data["private_household"] = preferences_data.pop("private_group") 

137 

138 # insert preferences data 

139 columns = ", ".join(preferences_data.keys()) 

140 placeholders = ", ".join(f":{key}" for key in preferences_data.keys()) 

141 sql_statement = f"INSERT INTO household_preferences ({columns}) VALUES ({placeholders})" 

142 

143 session.execute(sa.text(sql_statement), preferences_data) 

144 

145 return household_id 

146 

147 

148def create_households_for_groups() -> dict[str, str]: 1 ctx1a

149 bind = op.get_bind() 1 ctx1a

150 session = orm.Session(bind=bind) 1 ctx1a

151 group_id_household_id_map: dict[str, str] = {} 1 ctx1a

152 with session: 1 ctx1a

153 rows = session.execute(sa.text("SELECT id FROM groups")).fetchall() 1 ctx1a

154 for row in rows: 154 ↛ 155line 154 didn't jump to line 155 because the loop on line 154 never started1 ctx1a

155 group_id = row[0] 

156 group_id_household_id_map[group_id] = create_household(session, group_id) 

157 

158 return group_id_household_id_map 1 ctx1a

159 

160 

161def _do_assignment(session: orm.Session, table: str, group_id: str, household_id: str): 1 ctx1a

162 sql = sa.text( 

163 dedent( 

164 f""" 

165 UPDATE {table} 

166 SET household_id = :household_id 

167 WHERE group_id = :group_id 

168 """, 

169 ) 

170 ) 

171 session.execute(sql, {"group_id": group_id, "household_id": household_id}) 

172 

173 

174def assign_households(group_id_household_id_map: dict[str, str]): 1 ctx1a

175 tables = [ 1 ctx1a

176 "cookbooks", 

177 "group_events_notifiers", 

178 "group_meal_plan_rules", 

179 "invite_tokens", 

180 "recipe_actions", 

181 "users", 

182 "webhook_urls", 

183 ] 

184 

185 bind = op.get_bind() 1 ctx1a

186 session = orm.Session(bind=bind) 1 ctx1a

187 with session: 1 ctx1a

188 for table in tables: 1 ctx1a

189 for group_id, household_id in group_id_household_id_map.items(): 189 ↛ 190line 189 didn't jump to line 190 because the loop on line 189 never started1 ctx1a

190 _do_assignment(session, table, group_id, household_id) 

191 

192 

193def populate_household_data(): 1 ctx1a

194 group_id_household_id_map = create_households_for_groups() 1 ctx1a

195 assign_households(group_id_household_id_map) 1 ctx1a

196 

197 

198def upgrade(): 1 ctx1a

199 dedupe_cookbook_slugs() 1 ctx1a

200 

201 # ### commands auto generated by Alembic - please adjust! ### 

202 op.create_table( 1 ctx1a

203 "households", 

204 sa.Column("id", mealie.db.migration_types.GUID(), nullable=False), 

205 sa.Column("name", sa.String(), nullable=False), 

206 sa.Column("slug", sa.String(), nullable=True), 

207 sa.Column("group_id", mealie.db.migration_types.GUID(), nullable=False), 

208 sa.Column("created_at", sa.DateTime(), nullable=True), 

209 sa.Column("update_at", sa.DateTime(), nullable=True), 

210 sa.ForeignKeyConstraint( 

211 ["group_id"], 

212 ["groups.id"], 

213 ), 

214 sa.PrimaryKeyConstraint("id"), 

215 sa.UniqueConstraint("group_id", "name", name="household_name_group_id_key"), 

216 sa.UniqueConstraint("group_id", "slug", name="household_slug_group_id_key"), 

217 ) 

218 op.create_index(op.f("ix_households_created_at"), "households", ["created_at"], unique=False) 1 ctx1a

219 op.create_index(op.f("ix_households_group_id"), "households", ["group_id"], unique=False) 1 ctx1a

220 op.create_index(op.f("ix_households_name"), "households", ["name"], unique=False) 1 ctx1a

221 op.create_index(op.f("ix_households_slug"), "households", ["slug"], unique=False) 1 ctx1a

222 op.create_table( 1 ctx1a

223 "household_preferences", 

224 sa.Column("id", mealie.db.migration_types.GUID(), nullable=False), 

225 sa.Column("household_id", mealie.db.migration_types.GUID(), nullable=False), 

226 sa.Column("private_household", sa.Boolean(), nullable=True), 

227 sa.Column("first_day_of_week", sa.Integer(), nullable=True), 

228 sa.Column("recipe_public", sa.Boolean(), nullable=True), 

229 sa.Column("recipe_show_nutrition", sa.Boolean(), nullable=True), 

230 sa.Column("recipe_show_assets", sa.Boolean(), nullable=True), 

231 sa.Column("recipe_landscape_view", sa.Boolean(), nullable=True), 

232 sa.Column("recipe_disable_comments", sa.Boolean(), nullable=True), 

233 sa.Column("recipe_disable_amount", sa.Boolean(), nullable=True), 

234 sa.Column("created_at", sa.DateTime(), nullable=True), 

235 sa.Column("update_at", sa.DateTime(), nullable=True), 

236 sa.ForeignKeyConstraint( 

237 ["household_id"], 

238 ["households.id"], 

239 ), 

240 sa.PrimaryKeyConstraint("id"), 

241 ) 

242 op.create_index(op.f("ix_household_preferences_created_at"), "household_preferences", ["created_at"], unique=False) 1 ctx1a

243 op.create_index( 1 ctx1a

244 op.f("ix_household_preferences_household_id"), "household_preferences", ["household_id"], unique=False 

245 ) 

246 

247 with op.batch_alter_table("cookbooks") as batch_op: 1 ctx1a

248 batch_op.add_column(sa.Column("household_id", mealie.db.migration_types.GUID(), nullable=True)) 1 ctx1a

249 batch_op.create_index(op.f("ix_cookbooks_household_id"), ["household_id"], unique=False) 1 ctx1a

250 batch_op.create_foreign_key("fk_cookbooks_household_id", "households", ["household_id"], ["id"]) 1 ctx1a

251 

252 # not directly related to households, but important for frontend routes 

253 batch_op.create_unique_constraint("cookbook_slug_group_id_key", ["slug", "group_id"]) 1 ctx1a

254 

255 with op.batch_alter_table("group_events_notifiers") as batch_op: 1 ctx1a

256 batch_op.add_column(sa.Column("household_id", mealie.db.migration_types.GUID(), nullable=True)) 1 ctx1a

257 batch_op.create_index(op.f("ix_group_events_notifiers_household_id"), ["household_id"], unique=False) 1 ctx1a

258 batch_op.create_foreign_key("fk_group_events_notifiers_household_id", "households", ["household_id"], ["id"]) 1 ctx1a

259 

260 with op.batch_alter_table("group_meal_plan_rules") as batch_op: 1 ctx1a

261 batch_op.add_column(sa.Column("household_id", mealie.db.migration_types.GUID(), nullable=True)) 1 ctx1a

262 batch_op.create_index(op.f("ix_group_meal_plan_rules_household_id"), ["household_id"], unique=False) 1 ctx1a

263 batch_op.create_foreign_key("fk_group_meal_plan_rules_household_id", "households", ["household_id"], ["id"]) 1 ctx1a

264 

265 with op.batch_alter_table("invite_tokens") as batch_op: 1 ctx1a

266 batch_op.add_column(sa.Column("household_id", mealie.db.migration_types.GUID(), nullable=True)) 1 ctx1a

267 batch_op.create_index(op.f("ix_invite_tokens_household_id"), ["household_id"], unique=False) 1 ctx1a

268 batch_op.create_foreign_key("fk_invite_tokens_household_id", "households", ["household_id"], ["id"]) 1 ctx1a

269 

270 with op.batch_alter_table("recipe_actions") as batch_op: 1 ctx1a

271 batch_op.add_column(sa.Column("household_id", mealie.db.migration_types.GUID(), nullable=True)) 1 ctx1a

272 batch_op.create_index(op.f("ix_recipe_actions_household_id"), ["household_id"], unique=False) 1 ctx1a

273 batch_op.create_foreign_key("fk_recipe_actions_household_id", "households", ["household_id"], ["id"]) 1 ctx1a

274 

275 with op.batch_alter_table("users") as batch_op: 1 ctx1a

276 batch_op.add_column(sa.Column("household_id", mealie.db.migration_types.GUID(), nullable=True)) 1 ctx1a

277 batch_op.create_index(op.f("ix_users_household_id"), ["household_id"], unique=False) 1 ctx1a

278 batch_op.create_foreign_key("fk_users_household_id", "households", ["household_id"], ["id"]) 1 ctx1a

279 

280 with op.batch_alter_table("webhook_urls") as batch_op: 1 ctx1a

281 batch_op.add_column(sa.Column("household_id", mealie.db.migration_types.GUID(), nullable=True)) 1 ctx1a

282 batch_op.create_index(op.f("ix_webhook_urls_household_id"), ["household_id"], unique=False) 1 ctx1a

283 batch_op.create_foreign_key("fk_webhook_urls_household_id", "households", ["household_id"], ["id"]) 1 ctx1a

284 # ### end Alembic commands ### 

285 

286 populate_household_data() 1 ctx1a

287 

288 

289def downgrade(): 1 ctx1a

290 # ### commands auto generated by Alembic - please adjust! ### 

291 op.drop_constraint(None, "webhook_urls", type_="foreignkey") 

292 op.drop_index(op.f("ix_webhook_urls_household_id"), table_name="webhook_urls") 

293 op.drop_column("webhook_urls", "household_id") 

294 op.drop_constraint(None, "users", type_="foreignkey") 

295 op.drop_index(op.f("ix_users_household_id"), table_name="users") 

296 op.drop_column("users", "household_id") 

297 op.drop_constraint(None, "recipe_actions", type_="foreignkey") 

298 op.drop_index(op.f("ix_recipe_actions_household_id"), table_name="recipe_actions") 

299 op.drop_column("recipe_actions", "household_id") 

300 op.drop_constraint(None, "invite_tokens", type_="foreignkey") 

301 op.drop_index(op.f("ix_invite_tokens_household_id"), table_name="invite_tokens") 

302 op.drop_column("invite_tokens", "household_id") 

303 op.drop_constraint(None, "group_meal_plan_rules", type_="foreignkey") 

304 op.drop_index(op.f("ix_group_meal_plan_rules_household_id"), table_name="group_meal_plan_rules") 

305 op.drop_column("group_meal_plan_rules", "household_id") 

306 op.drop_constraint(None, "group_events_notifiers", type_="foreignkey") 

307 op.drop_index(op.f("ix_group_events_notifiers_household_id"), table_name="group_events_notifiers") 

308 op.drop_column("group_events_notifiers", "household_id") 

309 op.drop_constraint(None, "cookbooks", type_="foreignkey") 

310 op.drop_index(op.f("ix_cookbooks_household_id"), table_name="cookbooks") 

311 op.drop_column("cookbooks", "household_id") 

312 op.drop_constraint("cookbook_slug_group_id_key", "cookbooks", type_="unique") 

313 op.drop_index(op.f("ix_household_preferences_household_id"), table_name="household_preferences") 

314 op.drop_index(op.f("ix_household_preferences_created_at"), table_name="household_preferences") 

315 op.drop_table("household_preferences") 

316 op.drop_index(op.f("ix_households_slug"), table_name="households") 

317 op.drop_index(op.f("ix_households_name"), table_name="households") 

318 op.drop_index(op.f("ix_households_group_id"), table_name="households") 

319 op.drop_index(op.f("ix_households_created_at"), table_name="households") 

320 op.drop_table("households") 

321 # ### end Alembic commands ###