sum25_upgrade_sql.sql

-- Running upgrade a62e70d564b3 -> 981662a4cab3

CREATE TABLE access_log (
    subject_id INTEGER NOT NULL, 
    route VARCHAR(255) NOT NULL, 
    method VARCHAR(255) NOT NULL, 
    ip_address VARCHAR(255) NOT NULL, 
    client_timestamp VARCHAR(255), 
    client_timezone VARCHAR(255), 
    id INTEGER NOT NULL, 
    date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    date_modified TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    PRIMARY KEY (id), 
    FOREIGN KEY(subject_id) REFERENCES "user" (id)
);

CREATE INDEX access_log_subject_index ON access_log (subject_id);

CREATE TABLE course_log (
    course_id INTEGER NOT NULL, 
    subject_id INTEGER NOT NULL, 
    event_type VARCHAR(9) NOT NULL, 
    field VARCHAR(255), 
    value TEXT, 
    client_timestamp VARCHAR(255), 
    client_timezone VARCHAR(255), 
    id INTEGER NOT NULL, 
    date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    date_modified TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    PRIMARY KEY (id), 
    FOREIGN KEY(course_id) REFERENCES course (id), 
    FOREIGN KEY(subject_id) REFERENCES "user" (id)
);

CREATE INDEX course_log_course_index ON course_log (course_id);

CREATE INDEX course_log_subject_index ON course_log (subject_id);

CREATE TABLE error_log (
    access_log_id INTEGER NOT NULL, 
    error_type VARCHAR(255) NOT NULL, 
    message TEXT NOT NULL, 
    traceback TEXT NOT NULL, 
    id INTEGER NOT NULL, 
    date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    date_modified TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    PRIMARY KEY (id), 
    FOREIGN KEY(access_log_id) REFERENCES access_log (id)
);

CREATE INDEX error_log_access_index ON error_log (access_log_id);

CREATE TABLE assignment_log (
    assignment_id INTEGER NOT NULL, 
    assignment_version INTEGER NOT NULL, 
    course_id INTEGER NOT NULL, 
    subject_id INTEGER NOT NULL, 
    event_type VARCHAR(9) NOT NULL, 
    field VARCHAR(255), 
    value TEXT, 
    client_timestamp VARCHAR(255), 
    client_timezone VARCHAR(255), 
    id INTEGER NOT NULL, 
    date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    date_modified TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    PRIMARY KEY (id), 
    FOREIGN KEY(assignment_id) REFERENCES assignment (id), 
    FOREIGN KEY(course_id) REFERENCES course (id), 
    FOREIGN KEY(subject_id) REFERENCES "user" (id)
);

CREATE INDEX assignment_log_assignment_index ON assignment_log (assignment_id);

CREATE INDEX assignment_log_subject_index ON assignment_log (subject_id);

CREATE TABLE role_log (
    role_id INTEGER NOT NULL, 
    course_id INTEGER NOT NULL, 
    subject_id INTEGER NOT NULL, 
    authorizer_id INTEGER NOT NULL, 
    event_type VARCHAR(7) NOT NULL, 
    event_value VARCHAR(255) NOT NULL, 
    client_timestamp VARCHAR(255), 
    client_timezone VARCHAR(255), 
    id INTEGER NOT NULL, 
    date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    date_modified TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    PRIMARY KEY (id), 
    FOREIGN KEY(authorizer_id) REFERENCES "user" (id),
    FOREIGN KEY(course_id) REFERENCES course (id), 
    FOREIGN KEY(role_id) REFERENCES role (id), 
    FOREIGN KEY(subject_id) REFERENCES "user" (id)
);

CREATE INDEX role_log_authorizer_index ON role_log (authorizer_id);

CREATE INDEX role_log_course_index ON role_log (course_id);

CREATE INDEX role_log_role_index ON role_log (role_id);

CREATE INDEX role_log_subject_index ON role_log (subject_id);

-- DROP INDEX log_index;

-- DROP TABLE log;

ALTER TABLE log RENAME TO log_old;

CREATE TABLE submission_log (
    submission_id INTEGER NOT NULL,
    submission_version INTEGER NOT NULL,
    assignment_id INTEGER NOT NULL,
    assignment_version INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    subject_id INTEGER NOT NULL,
    event_type VARCHAR(255) NOT NULL,
    file_path VARCHAR(255),
    category VARCHAR(255),
    label VARCHAR(255),
    message TEXT,
    client_timestamp VARCHAR(255),
    client_timezone VARCHAR(255),
    id INTEGER NOT NULL,
    date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    date_modified TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(submission_id) REFERENCES submission (id),
    FOREIGN KEY(assignment_id) REFERENCES assignment (id),
    FOREIGN KEY(course_id) REFERENCES course (id),
    FOREIGN KEY(subject_id) REFERENCES "user" (id)
);

-- ALTER TABLE submission_log ALTER COLUMN event_type TYPE VARCHAR(8);

CREATE INDEX submission_log_assignment_index ON submission_log (assignment_id);

CREATE INDEX submission_log_course_index ON submission_log (course_id);

CREATE INDEX submission_log_subject_index ON submission_log (subject_id);

CREATE INDEX submission_log_submission_index ON submission_log (submission_id);

ALTER TABLE assignment ADD COLUMN status VARCHAR(9) NOT NULL DEFAULT 'published';

ALTER TABLE assignment ALTER COLUMN status SET DEFAULT 'draft';

-- ALTER TABLE assignment ALTER COLUMN type SET NOT NULL;

-- ALTER TABLE assignment ALTER COLUMN type TYPE VARCHAR(11);

CREATE INDEX assignment_course_index ON assignment (course_id);

CREATE INDEX assignment_url_index ON assignment (url);

ALTER TABLE assignment_group ADD COLUMN category VARCHAR(9) DEFAULT 'none' NOT NULL;

CREATE INDEX assignment_group_course_index ON assignment_group (course_id);

CREATE INDEX assignment_group_url_index ON assignment_group (url);

ALTER TABLE assignment_group_membership ADD COLUMN policy VARCHAR(255);

CREATE INDEX assignment_group_membership_assignment_group_id ON assignment_group_membership (assignment_group_id);

CREATE INDEX assignment_group_membership_assignment_id ON assignment_group_membership (assignment_id);

CREATE INDEX assignment_group_membership_lookup ON assignment_group_membership (assignment_group_id, assignment_id);

ALTER TABLE authentication ADD COLUMN expires_at TIMESTAMP WITHOUT TIME ZONE;

ALTER TABLE authentication ADD COLUMN refresh_token VARCHAR(255);

-- ALTER TABLE authentication ALTER COLUMN type SET NOT NULL;

-- ALTER TABLE authentication ALTER COLUMN type TYPE VARCHAR(6);

CREATE INDEX authentication_user_index ON authentication (user_id);

ALTER TABLE course ADD COLUMN kind VARCHAR(8) NOT NULL DEFAULT 'default';

ALTER TABLE course ADD COLUMN lms_id INTEGER;

ALTER TABLE course ADD COLUMN version INTEGER NOT NULL DEFAULT 0;

--ALTER TABLE course ALTER COLUMN service SET NOT NULL;

--ALTER TABLE course ALTER COLUMN service TYPE VARCHAR(6);

--ALTER TABLE course ALTER COLUMN visibility SET NOT NULL;

--ALTER TABLE course ALTER COLUMN visibility TYPE VARCHAR(8);

CREATE INDEX course_url_index ON course (url);

ALTER TABLE invite ADD COLUMN kind VARCHAR(13) NOT NULL;

ALTER TABLE invite ADD COLUMN status VARCHAR(8) NOT NULL;

ALTER TABLE invite ADD COLUMN approver_id INTEGER;

ALTER TABLE invite ALTER COLUMN role TYPE VARCHAR(17);

CREATE INDEX invite_course_id ON invite (course_id);

CREATE INDEX invite_user_id ON invite (user_id);

CREATE INDEX invite_url_index ON invite (url);

--ALTER TABLE report ALTER COLUMN status SET NOT NULL;

--ALTER TABLE report ALTER COLUMN status TYPE VARCHAR(8);

-- ALTER TABLE report ALTER COLUMN visibility SET NOT NULL;

--ALTER TABLE report ALTER COLUMN visibility TYPE VARCHAR(8);

ALTER TABLE review ADD COLUMN comment_format VARCHAR(8) NOT NULL DEFAULT 'markdown';

ALTER TABLE review ADD COLUMN status VARCHAR(80) NOT NULL DEFAULT 'draft';

ALTER TABLE review ALTER COLUMN status SET DEFAULT 'published';

ALTER TABLE review ADD COLUMN extra_data TEXT NOT NULL DEFAULT '';

ALTER TABLE review ADD COLUMN replaces INTEGER;

ALTER TABLE review ADD COLUMN tool VARCHAR(255);

CREATE INDEX review_submission_index ON review (submission_id);

ALTER TABLE role ADD COLUMN subname VARCHAR(80) NOT NULL DEFAULT '';

ALTER TABLE role ADD COLUMN external_id INTEGER;

-- ALTER TABLE role ALTER COLUMN name SET NOT NULL;

-- ALTER TABLE role ALTER COLUMN name TYPE VARCHAR(17);

CREATE INDEX role_course_id ON role (course_id);

CREATE INDEX role_user_id ON role (user_id);

ALTER TABLE submission ADD COLUMN date_started TIMESTAMP WITHOUT TIME ZONE;

--ALTER TABLE submission ALTER COLUMN submission_status SET NOT NULL;

--ALTER TABLE submission ALTER COLUMN submission_status TYPE VARCHAR(11);

--ALTER TABLE submission ALTER COLUMN grading_status SET NOT NULL;

--ALTER TABLE submission ALTER COLUMN grading_status TYPE VARCHAR(14);

DROP INDEX submission_index;

CREATE INDEX submission_index ON submission (course_id, assignment_id, user_id);

CREATE INDEX submission_assignment_index ON submission (assignment_id);

CREATE INDEX submission_url_index ON submission (url);

CREATE INDEX submission_user_index ON submission (user_id);

UPDATE role SET name = REGEXP_REPLACE(name, '^.*/', '');

UPDATE alembic_version SET version_num='981662a4cab3' WHERE alembic_version.version_num = 'a62e70d564b3';

CREATE TABLE submission_counts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    submission_id INTEGER,
    metric VARCHAR(255) NOT NULL,
    value INTEGER NOT NULL DEFAULT 0,
    date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    date_modified TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    FOREIGN KEY (submission_id) REFERENCES submission(id)
);

CREATE UNIQUE INDEX submission_counts_unique_index ON submission_counts (submission_id, metric);


ALTER TABLE submission ADD COLUMN feedback TEXT DEFAULT '';

ALTER TABLE submission ADD COLUMN time_limit TEXT DEFAULT '';

ALTER TABLE submission ADD COLUMN attempts INTEGER DEFAULT 0;
ALTER TABLE submission ADD COLUMN events INTEGER DEFAULT 0;

ALTER TABLE "user" ADD COLUMN last_login_at TIMESTAMP WITHOUT TIME ZONE;
ALTER TABLE "user" ADD COLUMN current_login_at TIMESTAMP WITHOUT TIME ZONE;
ALTER TABLE "user" ADD COLUMN last_login_ip VARCHAR(255);
ALTER TABLE "user" ADD COLUMN current_login_ip VARCHAR(255);
ALTER TABLE "user" ADD COLUMN login_count INTEGER NOT NULL DEFAULT 0;

ALTER TABLE submission_log ADD COLUMN extended BOOLEAN DEFAULT FALSE;