Subversion Repositories oidplus

Rev

Rev 442 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
264 daniel-mar 1 
DROP TABLE IF EXISTS `config`;
2 
CREATE TABLE `config` (
3 
  `name` TEXT NOT NULL,
4 
  `value` TEXT NOT NULL,
5 
  `description` TEXT,
6 
  `protected` INTEGER NOT NULL DEFAULT 0,
7 
  `visible` INTEGER NOT NULL DEFAULT 0,
8 
  PRIMARY KEY (`name`)
9 
);
10 
 
11 
DROP TABLE IF EXISTS `asn1id`;
12 
CREATE TABLE `asn1id` (
13 
  `lfd` INTEGER PRIMARY KEY AUTOINCREMENT,
265 daniel-mar 14 
  `oid` TEXT NOT NULL REFERENCES `objects`(`id`),
264 daniel-mar 15 
  `name` TEXT NOT NULL,
16 
  `standardized` INTEGER NOT NULL DEFAULT 0,
17 
  `well_known` INTEGER NOT NULL DEFAULT 0,
18 
  UNIQUE (`oid`,`name`)
19 
);
20 
 
21 
DROP TABLE IF EXISTS `iri`;
22 
CREATE TABLE `iri` (
23 
  `lfd` INTEGER PRIMARY KEY AUTOINCREMENT,
265 daniel-mar 24 
  `oid` TEXT NOT NULL REFERENCES `objects`(`id`),
264 daniel-mar 25 
  `name` TEXT NOT NULL,
26 
  `longarc` INTEGER NOT NULL DEFAULT 0,
27 
  `well_known` INTEGER NOT NULL DEFAULT 0,
28 
  UNIQUE (`oid`,`name`)
29 
);
30 
 
31 
DROP TABLE IF EXISTS `objects`;
32 
CREATE TABLE `objects` (
33 
  `id` TEXT NOT NULL,
265 daniel-mar 34 
  `parent` TEXT DEFAULT NULL REFERENCES `objects`(`id`),
441 daniel-mar 35 
  `title` TEXT NULL,
36 
  `description` TEXT NULL,
265 daniel-mar 37 
  `ra_email` TEXT NULL REFERENCES `ra`(`email`),
264 daniel-mar 38 
  `confidential` BOOLEAN NOT NULL,
265 daniel-mar 39 
  `created` TEXT, -- DateTime
40 
  `updated` TEXT, -- DateTime
264 daniel-mar 41 
  `comment` TEXT NULL,
42 
  PRIMARY KEY (`id`)
43 
);
44 
 
45 
DROP TABLE IF EXISTS `ra`;
46 
CREATE TABLE `ra` (
47 
  `ra_id` INTEGER PRIMARY KEY AUTOINCREMENT,
48 
  `email` TEXT NOT NULL UNIQUE,
441 daniel-mar 49 
  `ra_name` TEXT NULL,
50 
  `personal_name` TEXT NULL,
51 
  `organization` TEXT NULL,
52 
  `office` TEXT NULL,
53 
  `street` TEXT NULL,
54 
  `zip_town` TEXT NULL,
55 
  `country` TEXT NULL,
56 
  `phone` TEXT NULL,
57 
  `mobile` TEXT NULL,
58 
  `fax` TEXT NULL,
264 daniel-mar 59 
  `privacy` INTEGER NOT NULL DEFAULT 0,
441 daniel-mar 60 
  `salt` TEXT NULL,
61 
  `authkey` TEXT NULL,
265 daniel-mar 62 
  `registered` TEXT, -- DateTime
63 
  `updated` TEXT, -- DateTime
264 daniel-mar 64 
  `last_login` datetime
65 
);
66 
 
67 
DROP TABLE IF EXISTS `log`;
68 
CREATE TABLE `log` (
69 
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
70 
  `unix_ts` INTEGER NOT NULL,
71 
  `addr` TEXT NOT NULL,
72 
  `event` TEXT NOT NULL
73 
);
74 
 
75 
DROP TABLE IF EXISTS `log_user`;
76 
CREATE TABLE `log_user` (
77 
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
265 daniel-mar 78 
  `log_id` INTEGER NOT NULL REFERENCES `log`(`id`),
272 daniel-mar 79 
  `username` TEXT NOT NULL,
288 daniel-mar 80 
  `severity` INTEGER NOT NULL,
272 daniel-mar 81 
  UNIQUE (`log_id`,`username`)
264 daniel-mar 82 
);
83 
 
84 
DROP TABLE IF EXISTS `log_object`;
85 
CREATE TABLE `log_object` (
86 
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
265 daniel-mar 87 
  `log_id` INTEGER NOT NULL REFERENCES `log`(`id`),
272 daniel-mar 88 
  `object` TEXT NOT NULL,
288 daniel-mar 89 
  `severity` INTEGER NOT NULL,
272 daniel-mar 90 
  UNIQUE (`log_id`,`object`)
264 daniel-mar 91 
);
92 
 
830 daniel-mar 93 
INSERT INTO `config` (name, description, VALUE, protected, visible) VALUES ('database_version', 'Version of the database tables', '1000', '1', '0');