Subversion Repositories oidplus

Rev

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