Subversion Repositories oidplus

Rev

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

  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.  
  139. INSERT INTO "CONFIG" ("NAME", "DESCRIPTION", "VALUE", "PROTECTED", "VISIBLE") VALUES ('database_version', 'Version of the database tables', '1000', '1', '0');
  140.