Subversion Repositories oidplus

Rev

Rev 442 | Go to most recent revision | 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.   `salt` TEXT NULL,
  61.   `authkey` TEXT NULL,
  62.   `registered` TEXT, -- DateTime
  63.   `updated` TEXT, -- DateTime
  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,
  78.   `log_id` INTEGER NOT NULL REFERENCES `log`(`id`),
  79.   `username` TEXT NOT NULL,
  80.   `severity` INTEGER NOT NULL,
  81.   UNIQUE (`log_id`,`username`)
  82. );
  83.  
  84. DROP TABLE IF EXISTS `log_object`;
  85. CREATE TABLE `log_object` (
  86.   `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  87.   `log_id` INTEGER NOT NULL REFERENCES `log`(`id`),
  88.   `object` TEXT NOT NULL,
  89.   `severity` INTEGER NOT NULL,
  90.   UNIQUE (`log_id`,`object`)
  91. );
  92.  
  93. INSERT INTO `config` (name, description, VALUE, protected, visible) VALUES ('database_version', 'Version of the database tables', '1000', '1', '0');
  94.