From c7820c61e376774abd65bb1c13ea12239ce87be4 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ale=C5=A1=20Smodi=C5=A1?= Date: Thu, 13 Aug 2015 16:51:14 +0200 Subject: Initial PostgreSQL data model: codeq_user and solution tables. Converted the action.py to use the new model. --- db/create.sql | 41 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 41 insertions(+) create mode 100644 db/create.sql (limited to 'db/create.sql') diff --git a/db/create.sql b/db/create.sql new file mode 100644 index 0000000..5b3fc8c --- /dev/null +++ b/db/create.sql @@ -0,0 +1,41 @@ +/* initial (server preparatory) statements: + create user codeq with password 'c0d3q'; + create user vladar with password 'vl4d4r' superuser createdb createrole; + * to create the database (logged in as vladar): + create database codeq with encoding 'UTF8' template template0; + \c codeq + \i create.sql + grant all privileges on all tables in schema public to codeq; + grant all privileges on codeq_user_id_seq to codeq; + grant all privileges on solution_id_seq to codeq; + */ + +create sequence codeq_user_id_seq; +create sequence solution_id_seq; + +create table codeq_user ( + id integer not null default nextval('codeq_user_id_seq'), + username varchar(30) not null, + password varchar(128) not null, + first_name varchar(30) not null, + last_name varchar(30) not null, + email varchar(254) not null, + is_superuser bool not null, + is_staff bool not null, + is_active bool not null, + date_joined timestamp not null, + last_login timestamp not null, + constraint codeq_user_pk primary key (id), + constraint codeq_user_uq1 unique (username) +); + +create table solution ( + id integer not null default nextval('solution_id_seq'), + done bool not null, + content text not null, + problem_id integer not null, + codeq_user_id integer not null, + trace jsonb not null default '[]'::jsonb, + constraint solution_pk primary key (id), + constraint solution_fk1 foreign key (codeq_user_id) references codeq_user (id) on delete cascade on update cascade +); -- cgit v1.2.1