Subversion Repositories oidplus

Rev

Rev 273 | Go to most recent revision | Blame | Last modification | View Log | RSS feed

  1. DROP TABLE IF EXISTS "config";
  2. CREATE TABLE "config" (
  3.   "name" VARCHAR(50) PRIMARY KEY,
  4.   "value" text NOT NULL,
  5.   "description" VARCHAR(255),
  6.   "protected" BOOLEAN NOT NULL DEFAULT FALSE,
  7.   "visible" BOOLEAN NOT NULL DEFAULT FALSE
  8. );
  9.  
  10. -------------------------------------------------------
  11.  
  12. DROP TABLE IF EXISTS "asn1id";
  13. CREATE TABLE "asn1id" (
  14.   "lfd" serial PRIMARY KEY,
  15.   "oid" VARCHAR(255) NOT NULL,
  16.   "name" VARCHAR(255) NOT NULL,
  17.   "standardized" BOOLEAN NOT NULL DEFAULT FALSE,
  18.   "well_known" BOOLEAN NOT NULL DEFAULT FALSE
  19. );
  20.  
  21. DROP INDEX IF EXISTS "index_asn1id_uq_oid_name";
  22.  
  23. CREATE UNIQUE INDEX "index_asn1id_uq_oid_name" ON "asn1id"("oid","name");
  24.  
  25. -------------------------------------------------------
  26.  
  27. DROP TABLE IF EXISTS "iri";
  28. CREATE TABLE "iri" (
  29.   "lfd" serial PRIMARY KEY,
  30.   "oid" VARCHAR(255) NOT NULL,
  31.   "name" VARCHAR(255) NOT NULL,
  32.   "longarc" BOOLEAN NOT NULL DEFAULT FALSE,
  33.   "well_known" BOOLEAN NOT NULL DEFAULT FALSE
  34. );
  35.  
  36. DROP INDEX IF EXISTS "index_iri_uq_oid_name";
  37.  
  38. CREATE UNIQUE INDEX "index_iri_uq_oid_name"    ON "iri"("oid","name");
  39.  
  40. -------------------------------------------------------
  41.  
  42. DROP TABLE IF EXISTS "objects";
  43. CREATE TABLE "objects" (
  44.   "id" VARCHAR(255) PRIMARY KEY,
  45.   "parent" VARCHAR(255) DEFAULT NULL,
  46.   "title" VARCHAR(255) NOT NULL,
  47.   "description" text NOT NULL,
  48.   "ra_email" VARCHAR(100) NULL,
  49.   "confidential" BOOLEAN NOT NULL,
  50.   "created" TIMESTAMP,
  51.   "updated" TIMESTAMP,
  52.   "comment" VARCHAR(255) NULL
  53. );
  54.  
  55. DROP INDEX IF EXISTS "index_objects_fk_parent";
  56. DROP INDEX IF EXISTS "index_objects_ra_email";
  57.  
  58. CREATE        INDEX "index_objects_fk_parent"  ON "objects"("parent");
  59. CREATE        INDEX "index_objects_ra_email"   ON "objects"("ra_email");
  60.  
  61. -------------------------------------------------------
  62.  
  63. DROP TABLE IF EXISTS "ra";
  64. CREATE TABLE "ra" (
  65.   "ra_id" serial PRIMARY KEY,
  66.   "email" VARCHAR(100) NOT NULL,
  67.   "ra_name" VARCHAR(100) NOT NULL,
  68.   "personal_name" VARCHAR(100) NOT NULL,
  69.   "organization" VARCHAR(100) NOT NULL,
  70.   "office" VARCHAR(100) NOT NULL,
  71.   "street" VARCHAR(100) NOT NULL,
  72.   "zip_town" VARCHAR(100) NOT NULL,
  73.   "country" VARCHAR(100) NOT NULL,
  74.   "phone" VARCHAR(100) NOT NULL,
  75.   "mobile" VARCHAR(100) NOT NULL,
  76.   "fax" VARCHAR(100) NOT NULL,
  77.   "privacy" BOOLEAN NOT NULL DEFAULT FALSE,
  78.   "salt" VARCHAR(100) NOT NULL,
  79.   "authkey" VARCHAR(100) NOT NULL,
  80.   "registered" TIMESTAMP,
  81.   "updated" TIMESTAMP,
  82.   "last_login" TIMESTAMP
  83. );
  84.  
  85. DROP INDEX IF EXISTS "index_ra_uq_email";
  86.  
  87. CREATE UNIQUE INDEX "index_ra_uq_email" ON "ra"("email");
  88.  
  89. -------------------------------------------------------
  90.  
  91. DROP TABLE IF EXISTS "log";
  92. CREATE TABLE "log" (
  93.   "id" serial PRIMARY KEY,
  94.   "unix_ts" BIGINT NOT NULL,
  95.   "addr" VARCHAR(45) NOT NULL,
  96.   "event" text NOT NULL
  97. );
  98.  
  99. -------------------------------------------------------
  100.  
  101. DROP TABLE IF EXISTS "log_user";
  102. CREATE TABLE "log_user" (
  103.   "id" serial PRIMARY KEY,
  104.   "log_id" INTEGER NOT NULL,
  105.   "username" VARCHAR(255) NOT NULL,
  106.   "severity" INTEGER NOT NULL
  107. );
  108.  
  109. DROP INDEX IF EXISTS "index_log_user_fk_log_id";
  110. DROP INDEX IF EXISTS "index_log_user_fk_username";
  111. DROP INDEX IF EXISTS "index_log_user_uq_log_id_username";
  112.  
  113. CREATE        INDEX "index_log_user_fk_log_id"          ON "log_user"("log_id");
  114. CREATE        INDEX "index_log_user_fk_username"        ON "log_user"("username");
  115. CREATE UNIQUE INDEX "index_log_user_uq_log_id_username" ON "log_user"("log_id","username");
  116.  
  117. -------------------------------------------------------
  118.  
  119. DROP TABLE IF EXISTS "log_object";
  120. CREATE TABLE "log_object" (
  121.   "id" serial PRIMARY KEY,
  122.   "log_id" INTEGER NOT NULL,
  123.   "object" VARCHAR(255) NOT NULL,
  124.   "severity" INTEGER NOT NULL
  125. );
  126.  
  127. DROP INDEX IF EXISTS "index_log_object_fk_log_id";
  128. DROP INDEX IF EXISTS "index_log_object_fk_object";
  129. DROP INDEX IF EXISTS "index_log_object_uq_log_id_object";
  130.  
  131. CREATE         INDEX "index_log_object_fk_log_id"        ON "log_object"("log_id");
  132. CREATE         INDEX "index_log_object_fk_object"        ON "log_object"("object");
  133. CREATE UNIQUE  INDEX "index_log_object_uq_log_id_object" ON "log_object"("log_id","object");
  134.  
  135. -------------------------------------------------------
  136.  
  137. INSERT INTO "config" ("name", "description", "value", "protected", "visible") VALUES ('database_version', 'Version of the database tables', '204', TRUE, FALSE);
  138.