scripts/db.sql

   1
   2  /*
   3   *   Copyright (C) 2007, Rus V. Brushkoff, All rights reserved.
   4   */
   5
   6 /* TODO: add cascade delete to all trees */
   7
   8 set sql dialect 3;
   9 set names koi8r;
  10 create database '/usr/local/sfinx/sfinx.fdb' user 'sysdba' password 'harbour' page_size 16384 default character set koi8r;
  11
  12 create table objects (
  13   id INTEGER not null,
  14   name VARCHAR(64) not null,
  15   description VARCHAR(256) not null,
  16   /* TODO: ÉÍÑ ÔÁÂÌÉÃÙ Ó ÏÐÉÓÁÎÉÅÍ ÐÏÌÅÊ É Ô.Ä. */
  17   constraint pkobjects PRIMARY KEY (id),
  18   UNIQUE (name)
  19 );
  20
  21 insert into objects (id, name, description) values (1, 'slice', 'User defined abstraction');
  22 insert into objects (id, name, description) values (2, 'file', 'Any file, containing some data');
  23 insert into objects (id, name, description) values (3, 'note', 'Note about smth.');
  24 insert into objects (id, name, description) values (4, 'event', 'Event');
  25 insert into objects (id, name, description) values (5, 'contact', 'Contact');
  26 insert into objects (id, name, description) values (6, 'task', 'Task or plan');
  27 insert into objects (id, name, description) values (7, 'message', 'Mail or IM');
  28 insert into objects (id, name, description) values (8, 'knowledge', 'Knowledge / kh [ know how ]');
  29
  30 /*  TODO: rule / intention / aim / dream */
  31
  32 create sequence slice_id;
  33 set generator slice_id to 0;
  34
  35 /* slices - ÔÁÂÌÉÃÁ ËÁÔÅÇÏÒÉÊ/ÓÒÅÚÏ×/ÁÂÓÔÒÁËÃÉÊ. Slice ÏÔÌÉÞÁÅÔÓÑ ÏÔ ÏÓÔÁÌØÎÙÈ
  36   ÏÂØÅËÔÏ× ÏÔÓÔÕÔÓÔ×ÉÅÍ ÇÌÁ×ÎÏÊ Ó×ÑÚÉ, Ô.Å. Ñ×ÌÑÅÔÓÑ ÉÚÎÁÞÁÌØÎÙÍ [ Dao ] É
  37   ÍÏÖÅÔ ÓÕÝÅÓÔ×Ï×ÁÔØ ÓÁÍ ÐÏ-ÓÅÂÅ. óÌÁÊÓÙ ÉÍÅÀÔ ÄÒÅ×Ï×ÉÄÎÕÀ ÓÔÒÕËÔÕÒÕ, ÐÏËÁ ÂÅÚ
  38   ÍÎÏÖÅÓÔ×ÅÎÎÏÇÏ ÎÁÓÌÅÄÏ×ÁÎÉÑ
  39
  40  */
  41
  42 create table slices (
  43   id BIGINT not null,
  44   parent_id BIGINT not null,
  45   name VARCHAR(64) not null,
  46   description VARCHAR(256) not null,
  47   /* ÄÉÒÅËÔÏÒÉÑ ÄÌÑ ÏÔÓÏÒÔÉÒÏ×ÁÎÎÙÈ ÆÁÊÌÏ× */
  48   directory VARCHAR(256),
  49   /* ÄÁÔÁ ÓÏÚÄÁÎÉÑ */
  50   ctime timestamp default 'now',
  51   /* ÄÁÔÁ ÉÚÍÅÎÅÎÉÑ */
  52   etime timestamp default 'now',
  53   constraint pkslices PRIMARY KEY (id),
  54   UNIQUE (parent_id, name)
  55 );
  56
  57 create index slices_etime on slices(etime);
  58 create index slices_ctime on slices(ctime);
  59
  60 set term ^ ;
  61
  62 /* ÕÎÉ×ÅÒÓÁÌØÎÙÊ ÔÒÉÇÇÅÒ: firebird >= 2.x ËÁÖÉÓØ */
  63
  64 create trigger slice_trg for slices
  65 active before insert or update position 0
  66 as
  67 begin
  68   if (new.id is null) then
  69     new.id = gen_id(slice_id, 1);
  70   select cast('now' as timestamp) from rdb$database into new.etime;
  71 end
  72 ^
  73
  74 set term ^ ;
  75
  76 insert into slices (parent_id, name, description) values (0, 'Root', 'Dao Slice');
  77 insert into slices (parent_id, name, description, directory) values (1, 'Texts', 'ôÅËÓÔÙ, äÏËÉ', 'Texts');
  78 insert into slices (parent_id, name, description, directory) values (1, 'Images', 'ëÁÒÔÉÎËÉ', 'Images');
  79 insert into slices (parent_id, name, description, directory) values (1, 'Music', 'íÕÚÙËÁ, MP3', 'Music');
  80
  81 /* module_conf - ÎÁÓÔÒÏÊËÉ ÍÏÄÕÌÅÊ */
  82
  83 create table files_module_conf (
  84   unsorted_files_path VARCHAR(256),
  85   sorted_file_tree VARCHAR(256),
  86   desc_file_name VARCHAR(256),
  87   desc_file_enable SMALLINT,
  88   detect_mime_type SMALLINT
  89 );
  90
  91 insert into files_module_conf (unsorted_files_path, sorted_file_tree, desc_file_name, desc_file_enable) values ('/Arhiv/unsorted', '/Arhiv/sorted', 'description.txt', 1);
  92
  93 create sequence file_id;
  94 set generator file_id to 0;
  95
  96 create table files (
  97
  98   id BIGINT not null,
  99   /* file name */
 100   name VARCHAR(256) not null,
 101   title VARCHAR(1024),
 102   /* Á×ÔÏÒ/ÉÚÄÁÔÅÌØÓÔ×Ï */
 103   authority VARCHAR(512),
 104   description VARCHAR(1024),
 105   comments VARCHAR(1024),
 106
 107   /* TODO : */
 108   /* is_directory SMALLINT default 0, */
 109   /* ÅÓÌÉ ÆÁÊÌ ÌÅÖÉÔ × ËÁËÏÊ-ÎÉÔØ ÄÉÒÅËÔÏÒÉÉ */
 110   /* parent_file_id INTEGER default 0, */
 111
 112   /* ÄÁÔÁ ÚÁÎÅÓÅÎÉÑ ÆÁÊÌÁ × ÂÁÚÕ */
 113   ctime timestamp default 'now',
 114   /* ÄÁÔÁ ÉÚÍÅÎÅÎÉÑ ÉÎÆÙ Ï ÆÁÊÌÅ × ÂÁÚÅ */
 115   etime timestamp default 'now',
 116
 117   /* ÔÉÐ ÇÌÁ×ÎÏÇÏ ÏÂØÅËÔÁ (main object) Ó ËÏÔÏÒÙÍ ÁÓÓÏÃÉÉÒÏ×ÁÎ ÆÁÊÌ */
 118   mtag_type INTEGER not null,
 119   /* ÅÇÏ id */
 120   mtag_id BIGINT not null,
 121   /* ÌÀÂÁÑ ÉÅÒÁÒÈÉÑ ÏÂØÅËÔÏ× ÉÍÅÅÔ Ó×ÏÊ ÇÌÁ×ÎÙÊ ÓÌÁÊÓ-ÒÏÄÉÔÅÌØ
 122      ÉÓÐÏÌØÚÕÅÔÓÑ ÄÌÑ ÂÙÓÔÒÏÊ ×ÙÂÏÒËÉ ×ÎÕÔÒÉ ÓÌÁÊÓÁ
 123      ÔÅÏÒÅÔÉÞÅÓËÉ - ÎÅ ÎÕÖÅÎ - ÍÏÖÎÏ ×ÙÞÉÓÌÑÔØ ÎÁ ÈÏÄÕ, ÐÏËÁ ÌÏÍÁÅÔ
 124    */
 125   mtag_slice_id BIGINT not null,
 126
 127   fsize BIGINT default 0,
 128   sha256 VARCHAR(64),
 129   fsize_compressed BIGINT default 0,
 130   sha256_compressed VARCHAR(64),
 131
 132   /* ×ÒÅÍÑ ÐÏÓÌÅÄÎÅÊ ÐÒÏ×ÅÒËÉ ËÏÎÔÒ. ÓÕÍÍÙ */
 133   csum_last_checked timestamp default '01.01.1900 00:00:00',
 134   csum_valid SMALLINT default 0,
 135
 136   /* ÔÉÐ ÐÒÏ×ÅÒËÉ ÆÁÊÌÁ :
 137     0 ÂÉÔ - ÒÁÚÍÅÒ
 138     1 ÂÉÔ - csum
 139   */
 140   /* check_type SMALLINT default 0, */
 141
 142   store_in_sorted_location SMALLINT default 0,
 143
 144   /* mimetype INTEGER default 0, */
 145
 146   /* mime ÔÉÐ ÄÌÑ ÈÒÁÎÅÎÉÑ × ÕÐÁËÏ×ÁÎÎÏÍ ×ÉÄÅ */
 147   /* compress_mimetype INTEGER default 0, */
 148   /* full_index SMALLINT default 0, */
 149   /* added_by_uid INTEGER not null */
 150
 151   constraint pkfiles PRIMARY KEY (id),
 152   UNIQUE (mtag_id, mtag_type, name)
 153 );
 154
 155 create index files_etime on files(etime);
 156 create index files_ctime on files(ctime);
 157 create index files_name on files(name);
 158 create index files_title on files(title);
 159 create index files_auth on files(authority);
 160 create index files_desc on files(description);
 161 create index files_comm on files(comments);
 162
 163 set term ^ ;
 164
 165 create trigger file_trg for files
 166 active before insert or update position 0
 167 as
 168 begin
 169   if (new.id is null) then
 170     new.id = gen_id(file_id, 1);
 171   select cast('now' as timestamp) from rdb$database into new.etime;
 172 end
 173 ^
 174
 175 set term ^ ;
 176
 177 /* ÄÏÐ-ÔÁÂÌÉÃÁ Ó×ÑÚÉ ÆÁÊÌÏ× É ÏÂØÅËÔÏ× */
 178
 179 create table file_tags (
 180   file_id BIGINT,
 181   obj_id BIGINT,
 182   obj_type INTEGER,
 183   constraint pkfile_slices PRIMARY KEY (file_id, obj_id, obj_type)
 184 );
 185
 186 create sequence note_id;
 187 set generator note_id to 0;
 188
 189 create table notes (
 190
 191   id BIGINT not null,
 192   name VARCHAR(256) not null,
 193   url VARCHAR(256),
 194   text VARCHAR(4096),
 195
 196   /* ÄÁÔÁ ÚÁÎÅÓÅÎÉÑ × ÂÁÚÕ */
 197   ctime timestamp default 'now',
 198   /* ÄÁÔÁ ÉÚÍÅÎÅÎÉÑ ÉÎÆÙ × ÂÁÚÅ */
 199   etime timestamp default 'now',
 200
 201   mtag_type INTEGER not null,
 202   mtag_id BIGINT not null,
 203   mtag_slice_id BIGINT not null,
 204
 205   /* text field is crypted */
 206   secured SMALLINT default 0,
 207
 208   constraint pknotes PRIMARY KEY (id),
 209   UNIQUE (mtag_slice_id, mtag_type, mtag_id, name),
 210   UNIQUE (url)
 211 );
 212
 213 create index notes_etime on notes(etime);
 214 create index notes_ctime on notes(ctime);
 215 create index notes_name on notes(name);
 216 create index notes_url on notes(url);
 217
 218 /* ÅÓÌÉ Ë ÐÒÉÍÅÒÕ text crypted - ÉÎÄÅËÓ ÎÅ ÎÕÖÅÎ: ÉÎÔÅÒÅÓÎÏ ÅÓÔØ ÌÉ ÔÁËÁÑ
 219    ÆÉÇÎÑ ËÁË ÕÓÌÏ×ÎÙÊ ÉÎÄÅËÓ - ÉÎÄÅËÓÉÒÏ×ÁÔØ ÔÏÌØËÏ ÞÁÓÔØ ÔÁÂÌÉÃÙ ÐÏ ÕÓÌÏ×ÉÀ
 220    ËÁËÉÈ-ÌÉÂÏ ÐÏÌÅÊ × ÔÅËÕÝÅÍ row ? */
 221 /* create index notes_text on notes(text); */
 222
 223 set term ^ ;
 224
 225 create trigger note_trg for notes
 226 active before insert or update position 0
 227 as
 228 begin
 229   if (new.id is null) then
 230     new.id = gen_id(note_id, 1);
 231   select cast('now' as timestamp) from rdb$database into new.etime;
 232 end
 233 ^
 234
 235 set term ^ ;
 236
 237 insert into notes (name, url, text, mtag_id, mtag_type, mtag_slice_id) values ('sample note', 'some name', 'some url', 3, 1, 3);
 238
 239 /* ÄÏÐ-ÔÁÂÌÉÃÁ note-Ó×ÑÚÅÊ */
 240
 241 create table note_tags (
 242   note_id BIGINT,
 243   obj_id BIGINT,
 244   obj_type INTEGER,
 245   constraint pknote_slices PRIMARY KEY (note_id, obj_id, obj_type)
 246 );
 247
 248 commit;
 249
 250 /*  TODO:
 251
 252  - ÔÁÂÌÉÃÁ ÀÚÅÒÏ×
 253  - ÔÁÂÌÉÃÁ ÒÅÊÔÉÎÇÏ× ÄÌÑ ÏÂØÅËÔÏ× (files/notes/events/contacts/etc.)
 254  - ÔÁÂÌÉÃÁ ÓÔÁÔÉÓÔÉËÉ ÄÌÑ ÄÅÍÏÎÁ (ËÏÌ. ËÏÎÎÅËÔÏ×/ÏÂØÅÍÙ ÉÎÆÙ/etc.)
 255
 256 */