schema.sql

   1 drop table if exists choices;
   2 drop table if exists attachments;
   3 drop table if exists votes;
   4 drop table if exists roles;
   5 drop table if exists users;
   6
   7 create table users (
   8     id INTEGER primary key autoincrement,
   9     email TEXT unique not null,
  10     password TEXT not null,
  11     name TEXT unique,
  12     organization TEXT,
  13     is_admin INTEGER default 0 not null,
  14     key TEXT
  15 );
  16
  17 create table roles (
  18     id INTEGER primary key autoincrement,
  19     name TEXT,
  20     system INTEGER default 0 not null
  21 );
  22
  23 create table user_role (
  24     id_user INTEGER,
  25     id_role INTEGER,
  26     FOREIGN KEY(id_user) REFERENCES users(id),
  27     FOREIGN KEY(id_role) REFERENCES roles(id),
  28     PRIMARY KEY(id_user, id_role)
  29 );
  30
  31 create table votes (
  32     id INTEGER primary key autoincrement,
  33     title TEXT not null,
  34     description TEXT,
  35     category TEXT,
  36     date_begin INTEGER default CURRENT_TIMESTAMP not null,
  37     date_end INTEGER not null,
  38     is_transparent INTEGER default 1 not null,
  39     is_public INTEGER default 1 not null,
  40     is_multiplechoice INTEGER default 1 not null,
  41     is_weighted INTEGER default 0 not null,
  42     is_open INTEGER default 0 not null,
  43     id_author INTEGER, -- :COMMENT:maethor:120528: not null ?
  44     id_role INTEGER default 1 not null,
  45     FOREIGN KEY(id_author) REFERENCES users(id)
  46     FOREIGN KEY(id_role) REFERENCES roles(id)
  47 );
  48
  49 create table attachments (
  50     url TEXT not null,
  51     id_vote INTEGER not null,
  52     FOREIGN KEY(id_vote) REFERENCES vote(id),
  53     PRIMARY KEY(url, id_vote)
  54 );
  55
  56 create table choices (
  57     id INTEGER primary key autoincrement,
  58     name TEXT not null,
  59     id_vote INTEGER not null,
  60     FOREIGN KEY(id_vote) REFERENCES vote(id)
  61 );
  62
  63 -- Test data
  64
  65 insert into users (email, password, name, organization, is_admin, key) values ("admin@admin.fr", "admin", "Toto (admin) Tata", "World corp", 1, "test");
  66 insert into roles (id, name, system) values (1, "Tous", 1);
  67 insert into roles (name) values ("CA");
  68 insert into roles (name) values ("Members");
  69