utils/db_create.sql

   1 BEGIN TRANSACTION;
   2 DELETE FROM sqlite_sequence;
   3 INSERT INTO "sqlite_sequence" VALUES('roles',2);
   4 INSERT INTO "sqlite_sequence" VALUES('user',5);
   5 CREATE TABLE roles (
   6 id integer primary key autoincrement,
   7 name text not null
   8 );
   9 INSERT INTO "roles" VALUES(0,'administrator');
  10 INSERT INTO "roles" VALUES(1,'power user');
  11 INSERT INTO "roles" VALUES(2,'user');
  12 CREATE TABLE user (
  13 id integer primary key autoincrement,
  14 uid text not null,
  15 pwd text not null,
  16 userrole integer not null,
  17 timeout integer default 3600,
  18 userview text default 'week',
  19 weekstart integer default 0,
  20 daystart real default 8.00,
  21 dayend real default 17.00,
  22 seckey text default '',
  23 pubkey text default '',
  24 constraint uid_index unique (uid)
  25 constraint userrole_fk foreign key (userrole) references roles (id)
  26 on delete restrict
  27 on update cascade
  28 on insert cascade
  29 );
  30 INSERT INTO "user" VALUES(1,'admin','d033e22ae348aeb5660fc2140aec35850c4da997',0,1200,'week',0,8.0,17.0,'','');
  31 INSERT INTO "user" VALUES(2,'test','a94a8fe5ccb19ba61c4c0873d391e987982fbbd3',1,3600,'week',0,8.0,17.0,'da830961dc3af47fff6d1af3be3d66d6','\93¢yQ è\85§¸\90d\8e\9c±gaÙ8(
<\a\vk\9dÂy\86*\19:ð');
  32 CREATE TABLE calendar (
  33 id integer default -1,
  34 uid integer not null,
  35 name text not null,
  36 color text default 'navy',
  37 config text default '',
  38 constraint calendar_pk primary key (id, uid),
  39 constraint uid_fk foreign key (uid) references user (id)
  40 on delete cascade
  41 on update cascade
  42 on insert cascade
  43 );
  44 CREATE TRIGGER compute_id after insert on calendar
  45 begin
  46     update calendar set id = (select max(id) + 1 from calendar) where id = -1;
  47 end;
  48 CREATE TRIGGER genfkey2_insert_referencing BEFORE INSERT ON "calendar" WHEN
  49     new."uid" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM "user" WHERE new."uid" == "id")
  50 BEGIN
  51   SELECT RAISE(ABORT, 'constraint failed');
  52 END;
  53 CREATE TRIGGER genfkey2_update_referencing BEFORE
  54     UPDATE OF uid ON "calendar" WHEN
  55     new."uid" IS NOT NULL AND
  56     NOT EXISTS (SELECT 1 FROM "user" WHERE new."uid" == "id")
  57 BEGIN
  58   SELECT RAISE(ABORT, 'constraint failed');
  59 END;
  60 CREATE TRIGGER genfkey2_delete_referenced BEFORE DELETE ON "user" WHEN
  61     EXISTS (SELECT 1 FROM "calendar" WHERE old."id" == "uid")
  62 BEGIN
  63   DELETE FROM "calendar" WHERE "uid" = old."id";
  64 END;
  65 CREATE TRIGGER genfkey2_update_referenced AFTER
  66     UPDATE OF id ON "user" WHEN
  67     EXISTS (SELECT 1 FROM "calendar" WHERE old."id" == "uid")
  68 BEGIN
  69   UPDATE "calendar" SET "uid" = new."id" WHERE "uid" = old."id";
  70 END;
  71 CREATE TRIGGER genfkey1_insert_referencing BEFORE INSERT ON "user" WHEN
  72     new."userrole" IS NOT NULL AND NOT EXISTS (SELECT 1 FROM "roles" WHERE new."userrole" == "id")
  73 BEGIN
  74   SELECT RAISE(ABORT, 'constraint failed');
  75 END;
  76 CREATE TRIGGER genfkey1_update_referencing BEFORE
  77     UPDATE OF userrole ON "user" WHEN
  78     new."userrole" IS NOT NULL AND
  79     NOT EXISTS (SELECT 1 FROM "roles" WHERE new."userrole" == "id")
  80 BEGIN
  81   SELECT RAISE(ABORT, 'constraint failed');
  82 END;
  83 CREATE TRIGGER genfkey1_delete_referenced BEFORE DELETE ON "roles" WHEN
  84     EXISTS (SELECT 1 FROM "user" WHERE old."id" == "userrole")
  85 BEGIN
  86   SELECT RAISE(ABORT, 'constraint failed');
  87 END;
  88 CREATE TRIGGER genfkey1_update_referenced AFTER
  89     UPDATE OF id ON "roles" WHEN
  90     EXISTS (SELECT 1 FROM "user" WHERE old."id" == "userrole")
  91 BEGIN
  92   UPDATE "user" SET "userrole" = new."id" WHERE "userrole" = old."id";
  93 END;
  94 COMMIT;