Coverage for opt/mealie/lib/python3.12/site-packages/mealie/alembic/versions/2024-03-18-02.28.15_d7c6efd2de42_migrate_favorites_and_ratings_to_user_.py: 48%

1"""migrate favorites and ratings to user_ratings 

2 

3Revision ID: d7c6efd2de42 

4Revises: 09aba125b57a 

5Create Date: 2024-03-18 02:28:15.896959 

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 sqlalchemy import orm 1 ctx1a

17 

18import mealie.db.migration_types 1 ctx1a

19 

20# revision identifiers, used by Alembic. 

21revision = "d7c6efd2de42" 1 ctx1a

22down_revision = "09aba125b57a" 1 ctx1a

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

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

25 

26 

27def is_postgres(): 1 ctx1a

28 return op.get_context().dialect.name == "postgresql" 1 ctx1a

29 

30 

31def new_user_rating(user_id: Any, recipe_id: Any, rating: float | None = None, is_favorite: bool = False): 1 ctx1a

32 if is_postgres(): 

33 id = str(uuid4()) 

34 else: 

35 id = "%.32x" % uuid4().int # noqa: UP031 

36 

37 now = datetime.now(UTC).isoformat() 

38 return { 

39 "id": id, 

40 "user_id": user_id, 

41 "recipe_id": recipe_id, 

42 "rating": rating, 

43 "is_favorite": is_favorite, 

44 "created_at": now, 

45 "update_at": now, 

46 } 

47 

48 

49def migrate_user_favorites_to_user_ratings(): 1 ctx1a

50 bind = op.get_bind() 1 ctx1a

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

52 

53 with session: 1 ctx1a

54 user_ids_and_recipe_ids = session.execute(sa.text("SELECT user_id, recipe_id FROM users_to_favorites")).all() 1 ctx1a

55 rows = [ 1 ctx1a

56 new_user_rating(user_id, recipe_id, is_favorite=True) 

57 for user_id, recipe_id in user_ids_and_recipe_ids 

58 if user_id and recipe_id 

59 ] 

60 

61 if is_postgres(): 61 ↛ 62line 61 didn't jump to line 62 because the condition on line 61 was never true1 ctx1a

62 query = dedent( 

63 """ 

64 INSERT INTO users_to_recipes (id, user_id, recipe_id, rating, is_favorite, created_at, update_at) 

65 VALUES (:id, :user_id, :recipe_id, :rating, :is_favorite, :created_at, :update_at) 

66 ON CONFLICT DO NOTHING 

67 """ 

68 ) 

69 else: 

70 query = dedent( 1 ctx1a

71 """ 

72 INSERT OR IGNORE INTO users_to_recipes 

73 (id, user_id, recipe_id, rating, is_favorite, created_at, update_at) 

74 VALUES (:id, :user_id, :recipe_id, :rating, :is_favorite, :created_at, :update_at) 

75 """ 

76 ) 

77 

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

79 session.execute(sa.text(query), row) 

80 

81 

82def migrate_group_to_user_ratings(group_id: Any): 1 ctx1a

83 bind = op.get_bind() 

84 session = orm.Session(bind=bind) 

85 

86 with session: 

87 user_ids = ( 

88 session.execute(sa.text("SELECT id FROM users WHERE group_id=:group_id").bindparams(group_id=group_id)) 

89 .scalars() 

90 .all() 

91 ) 

92 

93 recipe_ids_ratings = session.execute( 

94 sa.text( 

95 "SELECT id, rating FROM recipes WHERE group_id=:group_id AND rating > 0 AND rating IS NOT NULL" 

96 ).bindparams(group_id=group_id) 

97 ).all() 

98 

99 # Convert recipe ratings to user ratings. Since we don't know who 

100 # rated the recipe initially, we copy the rating to all users. 

101 rows: list[dict] = [] 

102 for recipe_id, rating in recipe_ids_ratings: 

103 for user_id in user_ids: 

104 rows.append(new_user_rating(user_id, recipe_id, rating, is_favorite=False)) 

105 

106 if is_postgres(): 

107 insert_query = dedent( 

108 """ 

109 INSERT INTO users_to_recipes (id, user_id, recipe_id, rating, is_favorite, created_at, update_at) 

110 VALUES (:id, :user_id, :recipe_id, :rating, :is_favorite, :created_at, :update_at) 

111 ON CONFLICT (user_id, recipe_id) DO NOTHING; 

112 """ 

113 ) 

114 else: 

115 insert_query = dedent( 

116 """ 

117 INSERT OR IGNORE INTO users_to_recipes 

118 (id, user_id, recipe_id, rating, is_favorite, created_at, update_at) 

119 VALUES (:id, :user_id, :recipe_id, :rating, :is_favorite, :created_at, :update_at); 

120 """ 

121 ) 

122 

123 update_query = dedent( 

124 """ 

125 UPDATE users_to_recipes 

126 SET rating = :rating, update_at = :update_at 

127 WHERE user_id = :user_id AND recipe_id = :recipe_id; 

128 """ 

129 ) 

130 

131 # Create new user ratings with is_favorite set to False 

132 for row in rows: 

133 session.execute(sa.text(insert_query), row) 

134 

135 # Update existing user ratings with the correct rating 

136 for row in rows: 

137 session.execute(sa.text(update_query), row) 

138 

139 

140def migrate_to_user_ratings(): 1 ctx1a

141 migrate_user_favorites_to_user_ratings() 1 ctx1a

142 

143 bind = op.get_bind() 1 ctx1a

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

145 

146 with session: 1 ctx1a

147 group_ids = session.execute(sa.text("SELECT id FROM groups")).scalars().all() 1 ctx1a

148 

149 for group_id in group_ids: 149 ↛ 150line 149 didn't jump to line 150 because the loop on line 149 never started1 ctx1a

150 migrate_group_to_user_ratings(group_id) 

151 

152 

153def upgrade(): 1 ctx1a

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

155 op.create_table( 1 ctx1a

156 "users_to_recipes", 

157 sa.Column("user_id", mealie.db.migration_types.GUID(), nullable=False), 

158 sa.Column("recipe_id", mealie.db.migration_types.GUID(), nullable=False), 

159 sa.Column("rating", sa.Float(), nullable=True), 

160 sa.Column("is_favorite", sa.Boolean(), nullable=False), 

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

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

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

164 sa.ForeignKeyConstraint( 

165 ["recipe_id"], 

166 ["recipes.id"], 

167 ), 

168 sa.ForeignKeyConstraint( 

169 ["user_id"], 

170 ["users.id"], 

171 ), 

172 sa.PrimaryKeyConstraint("user_id", "recipe_id", "id"), 

173 sa.UniqueConstraint("user_id", "recipe_id", name="user_id_recipe_id_rating_key"), 

174 ) 

175 op.create_index(op.f("ix_users_to_recipes_created_at"), "users_to_recipes", ["created_at"], unique=False) 1 ctx1a

176 op.create_index(op.f("ix_users_to_recipes_is_favorite"), "users_to_recipes", ["is_favorite"], unique=False) 1 ctx1a

177 op.create_index(op.f("ix_users_to_recipes_rating"), "users_to_recipes", ["rating"], unique=False) 1 ctx1a

178 op.create_index(op.f("ix_users_to_recipes_recipe_id"), "users_to_recipes", ["recipe_id"], unique=False) 1 ctx1a

179 op.create_index(op.f("ix_users_to_recipes_user_id"), "users_to_recipes", ["user_id"], unique=False) 1 ctx1a

180 

181 migrate_to_user_ratings() 1 ctx1a

182 

183 if is_postgres(): 183 ↛ 184line 183 didn't jump to line 184 because the condition on line 183 was never true1 ctx1a

184 op.drop_index("ix_users_to_favorites_recipe_id", table_name="users_to_favorites") 

185 op.drop_index("ix_users_to_favorites_user_id", table_name="users_to_favorites") 

186 op.alter_column("recipes", "rating", existing_type=sa.INTEGER(), type_=sa.Float(), existing_nullable=True) 

187 else: 

188 op.execute("DROP INDEX IF EXISTS ix_users_to_favorites_recipe_id") 1 ctx1a

189 op.execute("DROP INDEX IF EXISTS ix_users_to_favorites_user_id") 1 ctx1a

190 with op.batch_alter_table("recipes") as batch_op: 1 ctx1a

191 batch_op.alter_column("rating", existing_type=sa.INTEGER(), type_=sa.Float(), existing_nullable=True) 1 ctx1a

192 

193 op.drop_table("users_to_favorites") 1 ctx1a

194 op.create_index(op.f("ix_recipes_rating"), "recipes", ["rating"], unique=False) 1 ctx1a

195 # ### end Alembic commands ### 

196 

197 

198def downgrade(): 1 ctx1a

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

200 op.alter_column( 

201 "recipes_ingredients", "quantity", existing_type=sa.Float(), type_=sa.INTEGER(), existing_nullable=True 

202 ) 

203 op.drop_index(op.f("ix_recipes_rating"), table_name="recipes") 

204 op.alter_column("recipes", "rating", existing_type=sa.Float(), type_=sa.INTEGER(), existing_nullable=True) 

205 op.create_table( 

206 "users_to_favorites", 

207 sa.Column("user_id", sa.CHAR(length=32), nullable=True), 

208 sa.Column("recipe_id", sa.CHAR(length=32), nullable=True), 

209 sa.ForeignKeyConstraint( 

210 ["recipe_id"], 

211 ["recipes.id"], 

212 ), 

213 sa.ForeignKeyConstraint( 

214 ["user_id"], 

215 ["users.id"], 

216 ), 

217 sa.UniqueConstraint("user_id", "recipe_id", name="user_id_recipe_id_key"), 

218 ) 

219 op.create_index("ix_users_to_favorites_user_id", "users_to_favorites", ["user_id"], unique=False) 

220 op.create_index("ix_users_to_favorites_recipe_id", "users_to_favorites", ["recipe_id"], unique=False) 

221 op.drop_index(op.f("ix_users_to_recipes_user_id"), table_name="users_to_recipes") 

222 op.drop_index(op.f("ix_users_to_recipes_recipe_id"), table_name="users_to_recipes") 

223 op.drop_index(op.f("ix_users_to_recipes_rating"), table_name="users_to_recipes") 

224 op.drop_index(op.f("ix_users_to_recipes_is_favorite"), table_name="users_to_recipes") 

225 op.drop_index(op.f("ix_users_to_recipes_created_at"), table_name="users_to_recipes") 

226 op.drop_table("users_to_recipes") 

227 # ### end Alembic commands ###