Subversion Repositories oidplus

Rev

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

  1. -- We have the same problem like in Oracle in re upper/lower-case:
  2. -- config means CONFIG,
  3. -- "config" means config
  4. -- Since we use config in the code, we must use "CONFIG" here
  5.  
  6.  
  7. -- You can import this file using the iSQL Tool command:
  8. -- "C:\Program Files\Firebird\Firebird_3_0\isql.exe" -z -i "d:\SVN\OIDplus\trunk\setup\sql\struct_firebird.sql"
  9. -- (Change the file paths)
  10.  
  11.  
  12. -- Connect to the database
  13. -- PLEASE CHANGE THE PATH HERE, BEFORE PASSING THE FILE TO iSQL Tool
  14. CONNECT 'D:\SVN\OIDplus\trunk\userdata\database\oidplus.fdb' USER 'SYSDBA' password 'masterkey';
  15.  
  16.  
  17. recreate TABLE "CONFIG" (
  18.   "NAME" VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
  19.   "VALUE" BLOB SUB_TYPE TEXT NOT NULL,
  20.   "DESCRIPTION" VARCHAR(255) CHARACTER SET UNICODE_FSS,
  21.   "PROTECTED" CHAR(1) DEFAULT '0' NOT NULL,
  22.   "VISIBLE" CHAR(1) DEFAULT '0' NOT NULL,
  23.   CONSTRAINT "PK_CONFIG" PRIMARY KEY ("NAME")
  24. );
  25.  
  26. /* -------------------------------------------------- */
  27.  
  28. recreate TABLE "ASN1ID" (
  29.   "LFD" INTEGER generated BY DEFAULT AS IDENTITY,
  30.   "OID" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
  31.   "NAME" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
  32.   "STANDARDIZED" CHAR(1) DEFAULT '0' NOT NULL,
  33.   "WELL_KNOWN" CHAR(1) DEFAULT '0' NOT NULL,
  34.   CONSTRAINT "PK_ASN1ID" PRIMARY KEY ("LFD"),
  35.   CONSTRAINT "UK_ASN1ID_1" UNIQUE ("OID","NAME")
  36. );
  37.  
  38. /* -------------------------------------------------- */
  39.  
  40. recreate TABLE "IRI" (
  41.   "LFD" INTEGER generated BY DEFAULT AS IDENTITY,
  42.   "OID" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
  43.   "NAME" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
  44.   "LONGARC" CHAR(1) DEFAULT '0' NOT NULL,
  45.   "WELL_KNOWN" CHAR(1) DEFAULT '0' NOT NULL,
  46.   CONSTRAINT "PK_IRI" PRIMARY KEY ("LFD"),
  47.   CONSTRAINT "UK_IRI_1" UNIQUE ("OID","NAME")
  48. );
  49.  
  50. /* -------------------------------------------------- */
  51.  
  52. recreate TABLE "OBJECTS" (
  53.   "ID" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
  54.   "PARENT" VARCHAR(255) CHARACTER SET UNICODE_FSS,
  55.   "TITLE" VARCHAR(255) CHARACTER SET UNICODE_FSS,
  56.   "DESCRIPTION" BLOB SUB_TYPE TEXT,
  57.   "RA_EMAIL" VARCHAR(100) CHARACTER SET UNICODE_FSS,
  58.   "CONFIDENTIAL" CHAR(1) NOT NULL,
  59.   "CREATED" TIMESTAMP,
  60.   "UPDATED" TIMESTAMP,
  61.   "COMMENT" VARCHAR(255) CHARACTER SET UNICODE_FSS,
  62.   CONSTRAINT "PK_OBJECTS" PRIMARY KEY ("ID")
  63. );
  64.  
  65. /* -------------------------------------------------- */
  66.  
  67. recreate TABLE "RA" (
  68.   "RA_ID" INTEGER generated BY DEFAULT AS IDENTITY,
  69.   "EMAIL" VARCHAR(100) CHARACTER SET UNICODE_FSS NOT NULL,
  70.   "RA_NAME" VARCHAR(100) CHARACTER SET UNICODE_FSS,
  71.   "PERSONAL_NAME" VARCHAR(100) CHARACTER SET UNICODE_FSS,
  72.   "ORGANIZATION" VARCHAR(100) CHARACTER SET UNICODE_FSS,
  73.   "OFFICE" VARCHAR(100) CHARACTER SET UNICODE_FSS,
  74.   "STREET" VARCHAR(100) CHARACTER SET UNICODE_FSS,
  75.   "ZIP_TOWN" VARCHAR(100) CHARACTER SET UNICODE_FSS,
  76.   "COUNTRY" VARCHAR(100) CHARACTER SET UNICODE_FSS,
  77.   "PHONE" VARCHAR(100) CHARACTER SET UNICODE_FSS,
  78.   "MOBILE" VARCHAR(100) CHARACTER SET UNICODE_FSS,
  79.   "FAX" VARCHAR(100) CHARACTER SET UNICODE_FSS,
  80.   "PRIVACY" CHAR(1) DEFAULT '0' NOT NULL,
  81.   "AUTHKEY" VARCHAR(250) CHARACTER SET UNICODE_FSS,
  82.   "REGISTERED" TIMESTAMP,
  83.   "UPDATED" TIMESTAMP,
  84.   "LAST_LOGIN" TIMESTAMP,
  85.   CONSTRAINT "PK_RA" PRIMARY KEY ("RA_ID"),
  86.   CONSTRAINT "UK_RA_1" UNIQUE ("EMAIL")
  87. );
  88.  
  89. /* -------------------------------------------------- */
  90.  
  91. recreate TABLE "LOG" (
  92.   "ID" INTEGER generated BY DEFAULT AS IDENTITY,
  93.   "UNIX_TS" BIGINT NOT NULL,
  94.   "ADDR" VARCHAR(45) CHARACTER SET UNICODE_FSS NOT NULL,
  95.   "EVENT" BLOB SUB_TYPE TEXT NOT NULL,
  96.   CONSTRAINT "PK_LOG" PRIMARY KEY ("ID")
  97. );
  98.  
  99. /* -------------------------------------------------- */
  100.  
  101. recreate TABLE "LOG_USER" (
  102.   "ID" INTEGER generated BY DEFAULT AS IDENTITY,
  103.   "LOG_ID" INTEGER NOT NULL,
  104.   "USERNAME" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
  105.   "SEVERITY" INTEGER NOT NULL,
  106.   CONSTRAINT "PK_LOG_USER" PRIMARY KEY ("ID"),
  107.   CONSTRAINT "UK_LOG_USER_1" UNIQUE ("LOG_ID","USERNAME")
  108. );
  109.  
  110. /* -------------------------------------------------- */
  111.  
  112. recreate TABLE "LOG_OBJECT" (
  113.   "ID" INTEGER generated BY DEFAULT AS IDENTITY,
  114.   "LOG_ID" INTEGER NOT NULL,
  115.   "OBJECT" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
  116.   "SEVERITY" INTEGER NOT NULL,
  117.   CONSTRAINT "PK_LOG_OBJECT" PRIMARY KEY ("ID"),
  118.   CONSTRAINT "UK_LOG_OBJECT_1" UNIQUE ("LOG_ID","OBJECT")
  119. );
  120.  
  121. /* -------------------------------------------------- */
  122.  
  123. INSERT INTO "CONFIG" ("NAME", "DESCRIPTION", "VALUE", "PROTECTED", "VISIBLE") VALUES ('database_version', 'Version of the database tables', '1002', '1', '0');
  124.  
  125.  
  126. -- Quit = exit and rollback changes!
  127. -- Exit = and commit changes
  128. exit;
  129.