New database tables and data imports: user_group, user_in_group, language, problem_group, problem.

1 files changed, 52 insertions, 6 deletions

diff --git a/db/create.sql b/db/create.sql
index 5b3fc8c..39eab20 100644
--- a/db/create.sql
+++ b/db/create.sql
@@ -6,22 +6,30 @@
    \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;
+   grant all privileges on all sequences in schema public to codeq;
  */
 
+create sequence user_group_id_seq;
 create sequence codeq_user_id_seq;
+create sequence language_id_seq;
+create sequence problem_group_id_seq;
+create sequence problem_id_seq;
 create sequence solution_id_seq;
 
+create table user_group (
+  id integer not null default nextval('user_group_id_seq'),
+  name varchar(80) not null,
+  constraint user_group_pk primary key (id),
+  constraint user_group_uq1 unique (name)
+);
+
 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,
+  name varchar(200),
   email varchar(254) not null,
-  is_superuser bool not null,
-  is_staff bool not null,
+  is_admin bool not null,
   is_active bool not null,
   date_joined timestamp not null,
   last_login timestamp not null,
@@ -29,6 +37,44 @@ create table codeq_user (
   constraint codeq_user_uq1 unique (username)
 );
 
+create table user_in_group (
+  user_group_id integer not null,
+  codeq_user_id integer not null,
+  constraint user_in_group_pk primary key (user_group_id, codeq_user_id)
+);
+
+create table language (
+  id integer not null default nextval('language_id_seq'),
+  name varchar(30) not null,
+  identifier varchar(30) not null,
+  constraint language_pk primary key (id),
+  constraint language_uq1 unique (name),
+  constraint language_uq2 unique (identifier)
+);
+
+create table problem_group (
+  id integer not null default nextval('problem_group_id_seq'),
+  name varchar(100) not null,
+  identifier varchar(100) not null,
+  constraint group_pk primary key (id),
+  constraint group_uq1 unique (name),
+  constraint group_uq2 unique (identifier)
+);
+
+create table problem (
+  id integer not null default nextval('problem_id_seq'),
+  language_id integer not null,
+  problem_group_id integer not null,
+  name varchar(100) not null,
+  identifier varchar(100) not null,
+  is_visible bool not null default true,
+  constraint problem_pk primary key (id),
+  constraint problem_fk1 foreign key (language_id) references language (id) on delete no action on update cascade,
+  constraint problem_fk2 foreign key (problem_group_id) references problem_group (id) on delete no action on update cascade,
+  constraint problem_uq1 unique (language_id, problem_group_id, name),
+  constraint problem_uq2 unique (identifier)
+);
+
 create table solution (
   id integer not null default nextval('solution_id_seq'),
   done bool not null,