DROP TABLE IF EXISTS "config";
CREATE TABLE "config" (
"name" VARCHAR(50) PRIMARY KEY,
"value" text NOT NULL,
"description" VARCHAR(255),
"protected" BOOLEAN NOT NULL DEFAULT FALSE,
"visible" BOOLEAN NOT NULL DEFAULT FALSE
);
-------------------------------------------------------
DROP TABLE IF EXISTS "asn1id";
CREATE TABLE "asn1id" (
"lfd" serial PRIMARY KEY,
"oid" VARCHAR(255) NOT NULL,
"name" VARCHAR(255) NOT NULL,
"standardized" BOOLEAN NOT NULL DEFAULT FALSE,
"well_known" BOOLEAN NOT NULL DEFAULT FALSE
);
DROP INDEX IF EXISTS "index_asn1id_uq_oid_name";
CREATE UNIQUE INDEX "index_asn1id_uq_oid_name" ON "asn1id"("oid","name");
-------------------------------------------------------
DROP TABLE IF EXISTS "iri";
CREATE TABLE "iri" (
"lfd" serial PRIMARY KEY,
"oid" VARCHAR(255) NOT NULL,
"name" VARCHAR(255) NOT NULL,
"longarc" BOOLEAN NOT NULL DEFAULT FALSE,
"well_known" BOOLEAN NOT NULL DEFAULT FALSE
);
DROP INDEX IF EXISTS "index_iri_uq_oid_name";
CREATE UNIQUE INDEX "index_iri_uq_oid_name" ON "iri"("oid","name");
-------------------------------------------------------
DROP TABLE IF EXISTS "objects";
CREATE TABLE "objects" (
"id" VARCHAR(255) PRIMARY KEY,
"parent" VARCHAR(255) DEFAULT NULL,
"title" VARCHAR(255) NOT NULL,
"description" text NOT NULL,
"ra_email" VARCHAR(100) NULL,
"confidential" BOOLEAN NOT NULL,
"created" TIMESTAMP,
"updated" TIMESTAMP,
"comment" VARCHAR(255) NULL
);
DROP INDEX IF EXISTS "index_objects_fk_parent";
DROP INDEX IF EXISTS "index_objects_ra_email";
CREATE INDEX "index_objects_fk_parent" ON "objects"("parent");
CREATE INDEX "index_objects_ra_email" ON "objects"("ra_email");
-------------------------------------------------------
DROP TABLE IF EXISTS "ra";
CREATE TABLE "ra" (
"ra_id" serial PRIMARY KEY,
"email" VARCHAR(100) NOT NULL,
"ra_name" VARCHAR(100) NOT NULL,
"personal_name" VARCHAR(100) NOT NULL,
"organization" VARCHAR(100) NOT NULL,
"office" VARCHAR(100) NOT NULL,
"street" VARCHAR(100) NOT NULL,
"zip_town" VARCHAR(100) NOT NULL,
"country" VARCHAR(100) NOT NULL,
"phone" VARCHAR(100) NOT NULL,
"mobile" VARCHAR(100) NOT NULL,
"fax" VARCHAR(100) NOT NULL,
"privacy" BOOLEAN NOT NULL DEFAULT FALSE,
"salt" VARCHAR(100) NOT NULL,
"authkey" VARCHAR(100) NOT NULL,
"registered" TIMESTAMP,
"updated" TIMESTAMP,
"last_login" TIMESTAMP
);
DROP INDEX IF EXISTS "index_ra_uq_email";
CREATE UNIQUE INDEX "index_ra_uq_email" ON "ra"("email");
-------------------------------------------------------
DROP TABLE IF EXISTS "log";
CREATE TABLE "log" (
"id" serial PRIMARY KEY,
"unix_ts" BIGINT NOT NULL,
"addr" VARCHAR(45) NOT NULL,
"event" text NOT NULL
);
-------------------------------------------------------
DROP TABLE IF EXISTS "log_user";
CREATE TABLE "log_user" (
"id" serial PRIMARY KEY,
"log_id" INTEGER NOT NULL,
"username" VARCHAR(255) NOT NULL,
"severity" INTEGER NOT NULL
);
DROP INDEX IF EXISTS "index_log_user_fk_log_id";
DROP INDEX IF EXISTS "index_log_user_fk_username";
DROP INDEX IF EXISTS "index_log_user_uq_log_id_username";
CREATE INDEX "index_log_user_fk_log_id" ON "log_user"("log_id");
CREATE INDEX "index_log_user_fk_username" ON "log_user"("username");
CREATE UNIQUE INDEX "index_log_user_uq_log_id_username" ON "log_user"("log_id","username");
-------------------------------------------------------
DROP TABLE IF EXISTS "log_object";
CREATE TABLE "log_object" (
"id" serial PRIMARY KEY,
"log_id" INTEGER NOT NULL,
"object" VARCHAR(255) NOT NULL,
"severity" INTEGER NOT NULL
);
DROP INDEX IF EXISTS "index_log_object_fk_log_id";
DROP INDEX IF EXISTS "index_log_object_fk_object";
DROP INDEX IF EXISTS "index_log_object_uq_log_id_object";
CREATE INDEX "index_log_object_fk_log_id" ON "log_object"("log_id");
CREATE INDEX "index_log_object_fk_object" ON "log_object"("object");
CREATE UNIQUE INDEX "index_log_object_uq_log_id_object" ON "log_object"("log_id","object");
-------------------------------------------------------
INSERT INTO "config" ("name", "description", "value", "protected", "visible") VALUES ('database_version', 'Version of the database tables', '204', TRUE, FALSE);