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 votes (
  24     id INTEGER primary key autoincrement,
  25     title TEXT not null,
  26     description TEXT,
  27     category TEXT,
  28     date_begin INTEGER default CURRENT_TIMESTAMP not null,
  29     date_end INTEGER not null,
  30     is_transparent INTEGER default 1 not null,
  31     is_public INTEGER default 1 not null,
  32     is_multiplechoice INTEGER default 1 not null,
  33     is_weighted INTEGER default 0 not null,
  34     is_open INTEGER default 0 not null,
  35     id_author INTEGER, -- :COMMENT:maethor:120528: not null ?
  36     id_role INTEGER default 1 not null,
  37     FOREIGN KEY(id_author) REFERENCES users(id)
  38     FOREIGN KEY(id_role) REFERENCES roles(id)
  39 );
  40
  41 create table attachments (
  42     url TEXT not null,
  43     id_vote INTEGER not null,
  44     FOREIGN KEY(id_vote) REFERENCES vote(id),
  45     PRIMARY KEY(url, id_vote)
  46 );
  47
  48 create table choices (
  49     id INTEGER primary key autoincrement,
  50     name TEXT not null,
  51     id_vote INTEGER not null,
  52     FOREIGN KEY(id_vote) REFERENCES vote(id)
  53 );
  54
  55 -- Test data
  56
  57 insert into users (email, password, name, organization, is_admin, key) values ("admin@admin.fr", "admin", "Toto (admin) Tata", "World corp", 1, "test");
  58 insert into roles (id, name, system) values (1, "Tous", 1);
  59 insert into roles (name) values ("CA");
  60 insert into roles (name) values ("Members");
  61