print "Adding r4_album_faves table."
db.c.update(" \
CREATE TABLE r4_album_faves ( \
album_id INTEGER NOT NULL, \
user_id INTEGER NOT NULL, \
album_fave BOOLEAN \
)")
db.c.create_idx("r4_album_faves", "user_id", "album_id") #Should be handled by primary key.
db.c.create_idx("r4_album_faves", "album_fave")
db.c.create_delete_fk("r4_album_faves", "r4_albums", "album_id", create_idx=False)
db.c.create_delete_fk("r4_album_faves", "phpbb_users", "user_id", create_idx=False)
print "Populating album_faves table..."
for row in db.c.fetch_all("SELECT album_id, user_id FROM r4_album_ratings WHERE album_fave = TRUE"):
if not db.c.fetch_var("SELECT COUNT(*) FROM r4_album_faves WHERE album_id = %s AND user_id = %s", (row['album_id'], row['user_id'])):
db.c.update("INSERT INTO r4_album_faves (album_id, user_id, album_fave) VALUES (%s, %s, TRUE)", (row['album_id'], row['user_id']))
print "Removing old column..."
db.c.update("ALTER TABLE r4_album_ratings DROP COLUMN album_fave")
print "Recalculating ratings for testing..."
for row in db.c.fetch_all("SELECT DISTINCT album_id, sid FROM r4_album_sid"):
alb = Album.load_from_id_sid(row['album_id'], row['sid'])
alb.update_all_user_ratings()
alb.update_fave_count()
print "Done"
create_idx=False)
db.c.create_delete_fk("r4_album_faves",
"phpbb_users",
"user_id",
create_idx=False)
print "Populating album_faves table..."
for row in db.c.fetch_all(
"SELECT album_id, user_id FROM r4_album_ratings WHERE album_fave = TRUE"
):
if not db.c.fetch_var(
"SELECT COUNT(*) FROM r4_album_faves WHERE album_id = %s AND user_id = %s",
(row['album_id'], row['user_id'])):
db.c.update(
"INSERT INTO r4_album_faves (album_id, user_id, album_fave) VALUES (%s, %s, TRUE)",
(row['album_id'], row['user_id']))
print "Removing old column..."
db.c.update("ALTER TABLE r4_album_ratings DROP COLUMN album_fave")
print "Recalculating ratings for testing..."
for row in db.c.fetch_all(
"SELECT DISTINCT album_id, sid FROM r4_album_sid"):
alb = Album.load_from_id_sid(row['album_id'], row['sid'])
alb.update_all_user_ratings()
alb.update_fave_count()
print "Done"
