def forward():
c = db.get_cursor()
c.execute("""
CREATE TABLE "im_conversation_state" (
"im_type" varchar(255) NOT NULL,
"im_id" varchar(255) NOT NULL,
"state" integer NOT NULL,
"extra" text NOT NULL DEFAULT '',
PRIMARY KEY ("im_type", "im_id", "state"))
""")
# Migrate old data.
c.execute("""
SELECT "question_id", "im_type", "im_id" FROM "question_session"
""")
psycopg2.extras.execute_values(
c,
"""
INSERT INTO "im_conversation_state" (
"im_type", "im_id", "state", "extra") VALUES %s
""",
[make_question_state_row(*row) for row in c],
)
c.execute("""
DROP TABLE IF EXISTS "question_session"
""")
def wrapped(*args, **kwargs):
target_name = f(*args, **kwargs)
c = db.get_cursor()
c.execute(
"""
UPDATE "migration"
SET "version" = %(version)s
WHERE "app" = 'quizzler'
""", {'version': int(target_name)})
def get_current_name():
c = db.get_cursor()
c.execute("""
SELECT "version" FROM "migration"
WHERE "app" = 'quizzler'
LIMIT 1
""")
version, = c.fetchone()
if version < 1:
return None
return f'{version:04}'
def forward():
c = db.get_cursor()
c.execute("""
ALTER TABLE "user" ADD PRIMARY KEY ("serial")
""")
c.execute("""
CREATE TABLE "answer_history" (
"user_serial" varchar(255) NOT NULL REFERENCES "user" ("serial"),
"question_id" varchar(255) NOT NULL,
"correctness" bool NOT NULL,
"created_at" date NOT NULL DEFAULT CURRENT_TIMESTAMP)
""")
def init_system():
logger.info('Init.')
c = db.get_cursor()
c.execute("""
SELECT EXISTS(
SELECT * FROM "information_schema"."tables"
WHERE "table_name" = 'migration'
)
""")
has_table, = c.fetchone()
if not has_table:
c.execute("""
CREATE TABLE "migration" (
"app" varchar(255) NOT NULL UNIQUE,
"version" integer NOT NULL DEFAULT 0)
""")
c.execute("""
INSERT INTO "migration" ("app", "version")
VALUES ('quizzler', 0)
""")
logger.info('Migration table created.')
def forward():
c = db.get_cursor()
c.execute("""
ALTER TABLE "answer_history" ALTER COLUMN "created_at" TYPE timestamp
""")
def forward():
c = db.get_cursor()
c.execute("""
ALTER TABLE "user" ADD COLUMN "score" integer NOT NULL DEFAULT 0
""")
def forward():
c = db.get_cursor()
c.execute("""
CREATE INDEX "user_score_leader_index" ON "user" ("score" DESC)
""")
def forward():
c = db.get_cursor()
c.execute("""CREATE TABLE "user" ("serial" varchar(255) NOT NULL)""")