schema.sql

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