Subversion Repositories oidplus

Rev

Blame | Last modification | View Log | RSS feed

  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,
  14.   `oid` TEXT NOT NULL, -- REFERENCES `objects`(`id`)
  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,
  24.   `oid` TEXT NOT NULL, -- REFERENCES `objects`(`id`)
  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,
  34.   `parent` TEXT DEFAULT NULL, -- REFERENCES `objects`(`id`)
  35.   `title` TEXT NULL,
  36.   `description` TEXT NULL,
  37.   `ra_email` TEXT NULL, -- REFERENCES `ra`(`email`)
  38.   `confidential` BOOLEAN NOT NULL,
  39.   `created` TEXT, -- DateTime
  40.   `updated` TEXT, -- DateTime
  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,
  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,
  59.   `privacy` INTEGER NOT NULL DEFAULT 0,
  60.   `authkey` TEXT NULL,
  61.   `registered` TEXT, -- DateTime
  62.   `updated` TEXT, -- DateTime
  63.   `last_login` datetime
  64. );
  65.  
  66. DROP TABLE IF EXISTS `log`;
  67. CREATE TABLE `log` (
  68.   `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  69.   `unix_ts` INTEGER NOT NULL,
  70.   `addr` TEXT NOT NULL,
  71.   `event` TEXT NOT NULL
  72. );
  73.  
  74. DROP TABLE IF EXISTS `log_user`;
  75. CREATE TABLE `log_user` (
  76.   `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  77.   `log_id` INTEGER NOT NULL, -- REFERENCES `log`(`id`)
  78.   `username` TEXT NOT NULL,
  79.   `severity` INTEGER NOT NULL,
  80.   UNIQUE (`log_id`,`username`)
  81. );
  82.  
  83. DROP TABLE IF EXISTS `log_object`;
  84. CREATE TABLE `log_object` (
  85.   `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  86.   `log_id` INTEGER NOT NULL, -- REFERENCES `log`(`id`)
  87.   `object` TEXT NOT NULL,
  88.   `severity` INTEGER NOT NULL,
  89.   UNIQUE (`log_id`,`object`)
  90. );
  91.  
  92. INSERT INTO `config` (name, description, VALUE, protected, visible) VALUES ('database_version', 'Version of the database tables', '1002', '1', '0');
  93.