Rev 442 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed
| Rev | Author | Line No. | Line |
|---|---|---|---|
| 239 | daniel-mar | 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 | |||
| 272 | daniel-mar | 10 | ------------------------------------------------------- |
| 11 | |||
| 239 | daniel-mar | 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 | |||
| 272 | daniel-mar | 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 | |||
| 239 | daniel-mar | 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 | |||
| 272 | daniel-mar | 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 | |||
| 239 | daniel-mar | 42 | DROP TABLE IF EXISTS "objects"; |
| 43 | CREATE TABLE "objects" ( |
||
| 44 | "id" VARCHAR(255) PRIMARY KEY, |
||
| 45 | "parent" VARCHAR(255) DEFAULT NULL, |
||
| 441 | daniel-mar | 46 | "title" VARCHAR(255) NULL, |
| 47 | "description" text NULL, |
||
| 239 | daniel-mar | 48 | "ra_email" VARCHAR(100) NULL, |
| 49 | "confidential" BOOLEAN NOT NULL, |
||
| 50 | "created" TIMESTAMP, |
||
| 51 | "updated" TIMESTAMP, |
||
| 52 | "comment" VARCHAR(255) NULL |
||
| 53 | ); |
||
| 54 | |||
| 272 | daniel-mar | 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 | |||
| 239 | daniel-mar | 63 | DROP TABLE IF EXISTS "ra"; |
| 64 | CREATE TABLE "ra" ( |
||
| 65 | "ra_id" serial PRIMARY KEY, |
||
| 66 | "email" VARCHAR(100) NOT NULL, |
||
| 441 | daniel-mar | 67 | "ra_name" VARCHAR(100) NULL, |
| 68 | "personal_name" VARCHAR(100) NULL, |
||
| 69 | "organization" VARCHAR(100) NULL, |
||
| 70 | "office" VARCHAR(100) NULL, |
||
| 71 | "street" VARCHAR(100) NULL, |
||
| 72 | "zip_town" VARCHAR(100) NULL, |
||
| 73 | "country" VARCHAR(100) NULL, |
||
| 74 | "phone" VARCHAR(100) NULL, |
||
| 75 | "mobile" VARCHAR(100) NULL, |
||
| 76 | "fax" VARCHAR(100) NULL, |
||
| 239 | daniel-mar | 77 | "privacy" BOOLEAN NOT NULL DEFAULT FALSE, |
| 441 | daniel-mar | 78 | "salt" VARCHAR(100) NULL, |
| 79 | "authkey" VARCHAR(100) NULL, |
||
| 239 | daniel-mar | 80 | "registered" TIMESTAMP, |
| 81 | "updated" TIMESTAMP, |
||
| 82 | "last_login" TIMESTAMP |
||
| 83 | ); |
||
| 84 | |||
| 272 | daniel-mar | 85 | DROP INDEX IF EXISTS "index_ra_uq_email"; |
| 86 | |||
| 87 | CREATE UNIQUE INDEX "index_ra_uq_email" ON "ra"("email"); |
||
| 88 | |||
| 89 | ------------------------------------------------------- |
||
| 90 | |||
| 239 | daniel-mar | 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 | |||
| 272 | daniel-mar | 99 | ------------------------------------------------------- |
| 100 | |||
| 239 | daniel-mar | 101 | DROP TABLE IF EXISTS "log_user"; |
| 102 | CREATE TABLE "log_user" ( |
||
| 103 | "id" serial PRIMARY KEY, |
||
| 104 | "log_id" INTEGER NOT NULL, |
||
| 288 | daniel-mar | 105 | "username" VARCHAR(255) NOT NULL, |
| 106 | "severity" INTEGER NOT NULL |
||
| 239 | daniel-mar | 107 | ); |
| 108 | |||
| 272 | daniel-mar | 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 | |||
| 239 | daniel-mar | 119 | DROP TABLE IF EXISTS "log_object"; |
| 120 | CREATE TABLE "log_object" ( |
||
| 121 | "id" serial PRIMARY KEY, |
||
| 122 | "log_id" INTEGER NOT NULL, |
||
| 288 | daniel-mar | 123 | "object" VARCHAR(255) NOT NULL, |
| 124 | "severity" INTEGER NOT NULL |
||
| 239 | daniel-mar | 125 | ); |
| 126 | |||
| 273 | daniel-mar | 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"; |
||
| 239 | daniel-mar | 130 | |
| 272 | daniel-mar | 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"); |
||
| 239 | daniel-mar | 134 | |
| 272 | daniel-mar | 135 | ------------------------------------------------------- |
| 136 | |||
| 830 | daniel-mar | 137 | INSERT INTO "config" ("name", "description", "value", "protected", "visible") VALUES ('database_version', 'Version of the database tables', '1000', TRUE, FALSE); |