Rev 783 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed
| Rev | Author | Line No. | Line |
|---|---|---|---|
| 783 | daniel-mar | 1 | |
| 2 | -- It is important that table names and column names inside quotes are uppercase |
||
| 3 | -- because if you query a name without quotes, then it will be automatically |
||
| 4 | -- converted to uppercase. Example: |
||
| 5 | -- create table test ==> uppercase name will be written to disk |
||
| 6 | -- create table "TEST" ==> uppercase name will be written to disk |
||
| 7 | -- create table "test" ==> lowercase name will be written to disk |
||
| 8 | -- select * from test ==> uppercase name will be read |
||
| 9 | -- select * from "TEST" ==> uppercase name will be read |
||
| 10 | -- select * from "test" ==> lowercase name will be read |
||
| 11 | |||
| 12 | -- The column name "COMMENT" is a reserved word. |
||
| 13 | -- In the program we MUST use 'select "COMMENT"' instead of 'select comment' |
||
| 14 | |||
| 15 | DROP TABLE "CONFIG"; |
||
| 16 | CREATE TABLE "CONFIG" ( |
||
| 17 | "NAME" VARCHAR2(50) NOT NULL, |
||
| 18 | "VALUE" VARCHAR2(4000) NOT NULL, |
||
| 19 | "DESCRIPTION" VARCHAR2(255), |
||
| 20 | "PROTECTED" NUMBER(1) DEFAULT '0' NOT NULL, |
||
| 21 | "VISIBLE" NUMBER(1) DEFAULT '0' NOT NULL, |
||
| 22 | PRIMARY KEY("NAME") |
||
| 23 | ); |
||
| 24 | |||
| 25 | /* -------------------------------------------------- */ |
||
| 26 | |||
| 27 | DROP TABLE "ASN1ID"; |
||
| 28 | CREATE TABLE "ASN1ID" ( |
||
| 29 | "LFD" NUMBER GENERATED ALWAYS AS IDENTITY, |
||
| 30 | "OID" VARCHAR2(255) NOT NULL, |
||
| 31 | "NAME" VARCHAR2(255) NOT NULL, |
||
| 32 | "STANDARDIZED" NUMBER(1) DEFAULT '0' NOT NULL, |
||
| 33 | "WELL_KNOWN" NUMBER(1) DEFAULT '0' NOT NULL, |
||
| 34 | PRIMARY KEY ("LFD"), |
||
| 35 | CONSTRAINT "UNIQ_ASN1ID" UNIQUE ("OID","NAME") |
||
| 36 | ); |
||
| 37 | |||
| 38 | /* -------------------------------------------------- */ |
||
| 39 | |||
| 40 | DROP TABLE "IRI"; |
||
| 41 | CREATE TABLE "IRI" ( |
||
| 42 | "LFD" NUMBER GENERATED ALWAYS AS IDENTITY, |
||
| 43 | "OID" VARCHAR2(255) NOT NULL, |
||
| 44 | "NAME" VARCHAR2(255) NOT NULL, |
||
| 45 | "LONGARC" NUMBER(1) DEFAULT '0' NOT NULL, |
||
| 46 | "WELL_KNOWN" NUMBER(1) DEFAULT '0' NOT NULL, |
||
| 47 | PRIMARY KEY ("LFD"), |
||
| 48 | CONSTRAINT "UNIQ_IRI" UNIQUE ("OID","NAME") |
||
| 49 | ); |
||
| 50 | |||
| 51 | /* -------------------------------------------------- */ |
||
| 52 | |||
| 53 | DROP TABLE "OBJECTS"; |
||
| 54 | CREATE TABLE "OBJECTS" ( |
||
| 55 | "ID" VARCHAR2(255) NOT NULL, |
||
| 56 | "PARENT" VARCHAR2(255) DEFAULT NULL, |
||
| 57 | "TITLE" VARCHAR2(255) NULL, |
||
| 58 | "DESCRIPTION" VARCHAR2(4000) NULL, |
||
| 59 | "RA_EMAIL" VARCHAR2(100) NULL, |
||
| 60 | "CONFIDENTIAL" NUMBER(1) NOT NULL, |
||
| 61 | "CREATED" TIMESTAMP, |
||
| 62 | "UPDATED" TIMESTAMP, |
||
| 63 | "COMMENT" VARCHAR2(255) NULL, |
||
| 64 | PRIMARY KEY ("ID") |
||
| 65 | ); |
||
| 66 | |||
| 67 | CREATE INDEX "OBJECTS_PARENT" ON "OBJECTS"("PARENT"); |
||
| 68 | CREATE INDEX "OBJECTS_RA_EMAIL" ON "OBJECTS"("RA_EMAIL"); |
||
| 69 | |||
| 70 | /* -------------------------------------------------- */ |
||
| 71 | |||
| 72 | DROP TABLE "RA"; |
||
| 73 | CREATE TABLE "RA" ( |
||
| 74 | "RA_ID" NUMBER GENERATED ALWAYS AS IDENTITY, |
||
| 75 | "EMAIL" VARCHAR2(100) NOT NULL, |
||
| 76 | "RA_NAME" VARCHAR2(100) NULL, |
||
| 77 | "PERSONAL_NAME" VARCHAR2(100) NULL, |
||
| 78 | "ORGANIZATION" VARCHAR2(100) NULL, |
||
| 79 | "OFFICE" VARCHAR2(100) NULL, |
||
| 80 | "STREET" VARCHAR2(100) NULL, |
||
| 81 | "ZIP_TOWN" VARCHAR2(100) NULL, |
||
| 82 | "COUNTRY" VARCHAR2(100) NULL, |
||
| 83 | "PHONE" VARCHAR2(100) NULL, |
||
| 84 | "MOBILE" VARCHAR2(100) NULL, |
||
| 85 | "FAX" VARCHAR2(100) NULL, |
||
| 86 | "PRIVACY" NUMBER(1) DEFAULT '0' NOT NULL, |
||
| 87 | "SALT" VARCHAR2(100) NULL, |
||
| 88 | "AUTHKEY" VARCHAR2(100) NULL, |
||
| 89 | "REGISTERED" TIMESTAMP, |
||
| 90 | "UPDATED" TIMESTAMP, |
||
| 91 | "LAST_LOGIN" TIMESTAMP, |
||
| 92 | PRIMARY KEY ("RA_ID"), |
||
| 93 | CONSTRAINT "UNIQ_RA" UNIQUE ("EMAIL") |
||
| 94 | ); |
||
| 95 | |||
| 96 | /* -------------------------------------------------- */ |
||
| 97 | |||
| 98 | DROP TABLE "LOG"; |
||
| 99 | CREATE TABLE "LOG" ( |
||
| 100 | "ID" NUMBER GENERATED ALWAYS AS IDENTITY, |
||
| 101 | "UNIX_TS" NUMBER NOT NULL, |
||
| 102 | "ADDR" VARCHAR2(45) NOT NULL, |
||
| 103 | "EVENT" VARCHAR2(4000) NOT NULL, |
||
| 104 | PRIMARY KEY ("ID") |
||
| 105 | ); |
||
| 106 | |||
| 107 | /* -------------------------------------------------- */ |
||
| 108 | |||
| 109 | DROP TABLE "LOG_USER"; |
||
| 110 | CREATE TABLE "LOG_USER" ( |
||
| 111 | "ID" NUMBER GENERATED ALWAYS AS IDENTITY, |
||
| 112 | "LOG_ID" NUMBER NOT NULL, |
||
| 113 | "USERNAME" VARCHAR2(255) NOT NULL, |
||
| 114 | "SEVERITY" NUMBER NOT NULL, |
||
| 115 | PRIMARY KEY ("ID"), |
||
| 116 | CONSTRAINT "UNIQ_LOG_USER" UNIQUE ("LOG_ID","USERNAME") |
||
| 117 | ); |
||
| 118 | |||
| 119 | CREATE INDEX "LOG_USER_LOG_ID" ON "LOG_USER"("LOG_ID"); |
||
| 120 | CREATE INDEX "LOG_USER_USERNAME" ON "LOG_USER"("USERNAME"); |
||
| 121 | |||
| 122 | /* -------------------------------------------------- */ |
||
| 123 | |||
| 124 | DROP TABLE "LOG_OBJECT"; |
||
| 125 | CREATE TABLE "LOG_OBJECT" ( |
||
| 126 | "ID" NUMBER GENERATED ALWAYS AS IDENTITY, |
||
| 127 | "LOG_ID" NUMBER NOT NULL, |
||
| 128 | "OBJECT" VARCHAR2(255) NOT NULL, |
||
| 129 | "SEVERITY" NUMBER NOT NULL, |
||
| 130 | PRIMARY KEY ("ID"), |
||
| 131 | CONSTRAINT "UNIQ_LOG_OBJECT" UNIQUE ("LOG_ID","OBJECT") |
||
| 132 | ); |
||
| 133 | |||
| 134 | CREATE INDEX "LOG_OBJECT_LOG_ID" ON "LOG_OBJECT"("LOG_ID"); |
||
| 135 | CREATE INDEX "LOG_OBJECT_OBJECT" ON "LOG_OBJECT"("OBJECT"); |
||
| 136 | |||
| 137 | /* -------------------------------------------------- */ |
||
| 138 | |||
| 830 | daniel-mar | 139 | INSERT INTO "CONFIG" ("NAME", "DESCRIPTION", "VALUE", "PROTECTED", "VISIBLE") VALUES ('database_version', 'Version of the database tables', '1000', '1', '0'); |