utils/db_create.sqlite.php

   1 <?php
   2 /* $Id$ */
   3
   4 function make_sql_stm() {
   5     $db = array();
   6
   7     array_push($db, "drop table if exists roles");
   8     array_push($db, "CREATE TABLE roles (
   9     id integer primary key autoincrement,
  10     name text not null
  11     )");
  12     array_push($db, "INSERT INTO roles VALUES(0,'administrator')");
  13     array_push($db, "INSERT INTO roles VALUES(1,'power user')");
  14     array_push($db, "INSERT INTO roles VALUES(2,'user')");
  15
  16     array_push($db, "drop table if exists user");
  17     array_push($db, "CREATE TABLE user (
  18     id integer primary key autoincrement,
  19     uid text not null,
  20     pwd text not null,
  21     userrole integer not null,
  22     timeout integer default 3600,
  23     userview text default 'week',
  24     weekstart integer default 0,
  25     daystart real default 8.00,
  26     dayend real default 17.00,
  27     timezone text default 'Etc/UTC',
  28     seckey text default '',
  29     pubkey text default '',
  30     constraint uid_index unique (uid)
  31     )");
  32
  33     array_push($db, "drop table if exists calendar");
  34     array_push($db, "CREATE TABLE calendar (
  35     id integer default -1,
  36     uid integer not null,
  37     name text not null,
  38     color text default 'navy',
  39     config text default '',
  40     constraint calendar_pk primary key (id, uid)
  41     )");
  42     array_push($db, "CREATE TRIGGER compute_id after insert on calendar
  43     begin
  44         update calendar set id = (select max(id) + 1 from calendar) where id = -1;
  45     end;");
  46     array_push($db, "CREATE TRIGGER genfkey2_insert_referencing BEFORE INSERT ON calendar WHEN
  47         new.uid IS NOT NULL AND NOT EXISTS (SELECT 1 FROM user WHERE new.uid == id)
  48     BEGIN
  49       SELECT RAISE(ABORT, 'constraint failed');
  50     END");
  51     array_push($db, "CREATE TRIGGER genfkey2_update_referencing BEFORE
  52         UPDATE OF uid ON calendar WHEN
  53         new.uid IS NOT NULL AND
  54         NOT EXISTS (SELECT 1 FROM user WHERE new.uid == id)
  55     BEGIN
  56       SELECT RAISE(ABORT, 'constraint failed');
  57     END");
  58     array_push($db, "CREATE TRIGGER genfkey2_delete_referenced BEFORE DELETE ON
  59     user WHEN EXISTS (SELECT 1 FROM calendar WHERE old.id == uid)
  60     BEGIN
  61       DELETE FROM calendar WHERE uid = old.id;
  62     END");
  63     array_push($db, "CREATE TRIGGER genfkey2_update_referenced AFTER
  64         UPDATE OF id ON user WHEN
  65         EXISTS (SELECT 1 FROM calendar WHERE old.id == uid)
  66     BEGIN
  67       UPDATE calendar SET uid = new.id WHERE uid = old.id;
  68     END");
  69     array_push($db, "CREATE TRIGGER genfkey1_insert_referencing BEFORE INSERT ON
  70     user WHEN new.userrole IS NOT NULL AND NOT EXISTS (SELECT 1 FROM roles WHERE
  71     new.userrole == id)
  72     BEGIN
  73       SELECT RAISE(ABORT, 'constraint failed');
  74     END");
  75     array_push($db, "CREATE TRIGGER genfkey1_update_referencing BEFORE
  76         UPDATE OF userrole ON user WHEN new.userrole IS NOT NULL AND
  77         NOT EXISTS (SELECT 1 FROM roles WHERE new.userrole == id)
  78     BEGIN
  79       SELECT RAISE(ABORT, 'constraint failed');
  80     END");
  81     array_push($db, "CREATE TRIGGER genfkey1_delete_referenced BEFORE DELETE ON
  82     roles WHEN EXISTS (SELECT 1 FROM user WHERE old.id == userrole)
  83     BEGIN
  84       SELECT RAISE(ABORT, 'constraint failed');
  85     END");
  86     array_push($db, "CREATE TRIGGER genfkey1_update_referenced AFTER
  87         UPDATE OF id ON roles WHEN
  88         EXISTS (SELECT 1 FROM user WHERE old.id == userrole)
  89     BEGIN
  90       UPDATE user SET userrole = new.id WHERE userrole = old.id;
  91     END");
  92     array_push($db, "drop table if exists about");
  93     array_push($db, "create table about (
  94     id integer default 1,
  95     version text default '')");
  96     array_push($db, "drop table if exists ldap");
  97     array_push($db, "create table ldap (
  98     enable integer default 0,
  99     dns text default '',
 100     tls integer default 0,
 101     base_dn text default '',
 102     user_attr text default 'uid')");
 103
 104     return $db;
 105 }