-- We have the same problem like in Oracle in re upper/lower-case:
-- config means CONFIG,
-- "config" means config
-- Since we use config in the code, we must use "CONFIG" here
-- You can import this file using the iSQL Tool command:
-- "C:\Program Files\Firebird\Firebird_3_0\isql.exe" -z -i "d:\SVN\OIDplus\trunk\setup\sql\struct_firebird.sql"
-- (Change the file paths)
-- Connect to the database
-- PLEASE CHANGE THE PATH HERE, BEFORE PASSING THE FILE TO iSQL Tool
CONNECT 'D:\SVN\OIDplus\trunk\userdata\database\oidplus.fdb' USER 'SYSDBA' password 'masterkey';
recreate TABLE "CONFIG" (
"NAME" VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
"VALUE" BLOB SUB_TYPE TEXT NOT NULL,
"DESCRIPTION" VARCHAR(255) CHARACTER SET UNICODE_FSS,
"PROTECTED" CHAR(1) DEFAULT '0' NOT NULL,
"VISIBLE" CHAR(1) DEFAULT '0' NOT NULL,
CONSTRAINT "PK_CONFIG" PRIMARY KEY ("NAME")
);
/* -------------------------------------------------- */
recreate TABLE "ASN1ID" (
"LFD" INTEGER generated BY DEFAULT AS IDENTITY,
"OID" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
"NAME" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
"STANDARDIZED" CHAR(1) DEFAULT '0' NOT NULL,
"WELL_KNOWN" CHAR(1) DEFAULT '0' NOT NULL,
CONSTRAINT "PK_ASN1ID" PRIMARY KEY ("LFD"),
CONSTRAINT "UK_ASN1ID_1" UNIQUE ("OID","NAME")
);
/* -------------------------------------------------- */
recreate TABLE "IRI" (
"LFD" INTEGER generated BY DEFAULT AS IDENTITY,
"OID" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
"NAME" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
"LONGARC" CHAR(1) DEFAULT '0' NOT NULL,
"WELL_KNOWN" CHAR(1) DEFAULT '0' NOT NULL,
CONSTRAINT "PK_IRI" PRIMARY KEY ("LFD"),
CONSTRAINT "UK_IRI_1" UNIQUE ("OID","NAME")
);
/* -------------------------------------------------- */
recreate TABLE "OBJECTS" (
"ID" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
"PARENT" VARCHAR(255) CHARACTER SET UNICODE_FSS,
"TITLE" VARCHAR(255) CHARACTER SET UNICODE_FSS,
"DESCRIPTION" BLOB SUB_TYPE TEXT,
"RA_EMAIL" VARCHAR(100) CHARACTER SET UNICODE_FSS,
"CONFIDENTIAL" CHAR(1) NOT NULL,
"CREATED" TIMESTAMP,
"UPDATED" TIMESTAMP,
"COMMENT" VARCHAR(255) CHARACTER SET UNICODE_FSS,
CONSTRAINT "PK_OBJECTS" PRIMARY KEY ("ID")
);
/* -------------------------------------------------- */
recreate TABLE "RA" (
"RA_ID" INTEGER generated BY DEFAULT AS IDENTITY,
"EMAIL" VARCHAR(100) CHARACTER SET UNICODE_FSS NOT NULL,
"RA_NAME" VARCHAR(100) CHARACTER SET UNICODE_FSS,
"PERSONAL_NAME" VARCHAR(100) CHARACTER SET UNICODE_FSS,
"ORGANIZATION" VARCHAR(100) CHARACTER SET UNICODE_FSS,
"OFFICE" VARCHAR(100) CHARACTER SET UNICODE_FSS,
"STREET" VARCHAR(100) CHARACTER SET UNICODE_FSS,
"ZIP_TOWN" VARCHAR(100) CHARACTER SET UNICODE_FSS,
"COUNTRY" VARCHAR(100) CHARACTER SET UNICODE_FSS,
"PHONE" VARCHAR(100) CHARACTER SET UNICODE_FSS,
"MOBILE" VARCHAR(100) CHARACTER SET UNICODE_FSS,
"FAX" VARCHAR(100) CHARACTER SET UNICODE_FSS,
"PRIVACY" CHAR(1) DEFAULT '0' NOT NULL,
"AUTHKEY" VARCHAR(250) CHARACTER SET UNICODE_FSS,
"REGISTERED" TIMESTAMP,
"UPDATED" TIMESTAMP,
"LAST_LOGIN" TIMESTAMP,
CONSTRAINT "PK_RA" PRIMARY KEY ("RA_ID"),
CONSTRAINT "UK_RA_1" UNIQUE ("EMAIL")
);
/* -------------------------------------------------- */
recreate TABLE "LOG" (
"ID" INTEGER generated BY DEFAULT AS IDENTITY,
"UNIX_TS" BIGINT NOT NULL,
"ADDR" VARCHAR(45) CHARACTER SET UNICODE_FSS NOT NULL,
"EVENT" BLOB SUB_TYPE TEXT NOT NULL,
CONSTRAINT "PK_LOG" PRIMARY KEY ("ID")
);
/* -------------------------------------------------- */
recreate TABLE "LOG_USER" (
"ID" INTEGER generated BY DEFAULT AS IDENTITY,
"LOG_ID" INTEGER NOT NULL,
"USERNAME" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
"SEVERITY" INTEGER NOT NULL,
CONSTRAINT "PK_LOG_USER" PRIMARY KEY ("ID"),
CONSTRAINT "UK_LOG_USER_1" UNIQUE ("LOG_ID","USERNAME")
);
/* -------------------------------------------------- */
recreate TABLE "LOG_OBJECT" (
"ID" INTEGER generated BY DEFAULT AS IDENTITY,
"LOG_ID" INTEGER NOT NULL,
"OBJECT" VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
"SEVERITY" INTEGER NOT NULL,
CONSTRAINT "PK_LOG_OBJECT" PRIMARY KEY ("ID"),
CONSTRAINT "UK_LOG_OBJECT_1" UNIQUE ("LOG_ID","OBJECT")
);
/* -------------------------------------------------- */
INSERT INTO "CONFIG" ("NAME", "DESCRIPTION", "VALUE", "PROTECTED", "VISIBLE") VALUES ('database_version', 'Version of the database tables', '1002', '1', '0');
-- Quit = exit and rollback changes!
-- Exit = and commit changes
exit;