/* $Id: create_tables.oracle,v 1.13 2008-04-25 16:58:02 mike Exp $ */
/*
* See comments in "create_tables.mysql"
*
* In Oracle, column types are a bit different than in MySQL:
*
* CHAR to CHAR
* VARCHAR to VARCHAR2
* various Large Object types to RAW or BLOB
* DATE, DATETIME, and TIME to DATE
* TIMESTAMP and YEAR to NUMBER
* INT datatypes to NUMBER(16, 0), or shorter?
* FLOAT, DOUBLE, REAL, and DECIMAL to FLOAT
* TEXT (max 65.535 chars) to VARCHAR2(4096)
* BOOL to NUMBER(1, 0)
* KEY to INDEX
*
* LEVEL is an Oracle reserved word, and therefore TABLE identity
* fails to create unless the column name "level" is quoted. SESSION
* and USER are reserved words as well, therefore creation of TABLE
* session and TABLE user fail. The simplest approach is to quote
* _all_ field and column names. For a list of reserved words, see:
*
* Oracle does not like blank lines in the middle of commands. Avoid!
*/
CREATE TABLE "genre" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"tag" VARCHAR2(4000),
"name" VARCHAR2(4000)
);
CREATE TABLE "mformat" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"genre_id" NUMBER(16, 0),
"name" VARCHAR2(4000),
"uri" VARCHAR2(4000),
FOREIGN KEY("genre_id") REFERENCES "genre"("id")
);
CREATE TABLE "service_type" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"tag" VARCHAR2(4000),
"name" VARCHAR2(4000),
"plugin" VARCHAR2(4000),
"priority" NUMBER(16, 0)
);
CREATE TABLE "genre_service_type" (
"genre_id" NUMBER(16, 0),
"service_type_id" NUMBER(16, 0),
FOREIGN KEY("genre_id") REFERENCES "genre"("id"),
FOREIGN KEY("service_type_id") REFERENCES "service_type"("id")
);
CREATE TABLE "provider" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"name" VARCHAR2(4000),
"priority" NUMBER(16, 0),
"contact" VARCHAR2(4000)
);
CREATE TABLE "service" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"service_type_id" NUMBER(16, 0),
"provider_id" NUMBER(16, 0),
"tag" VARCHAR2(4000),
"name" VARCHAR2(4000),
"priority" NUMBER(16, 0),
"url_recipe" VARCHAR2(4000),
"need_auth" NUMBER(1,0),
"auth_recipe" VARCHAR2(4000),
"disabled" NUMBER(1,0),
FOREIGN KEY("service_type_id") REFERENCES "service_type"("id"),
FOREIGN KEY("provider_id") REFERENCES "provider"("id")
);
CREATE TABLE "serial" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"name" VARCHAR2(4000),
"issn" VARCHAR2(4000)
);
CREATE TABLE "serial_alias" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"serial_id" NUMBER(16, 0),
"alias" VARCHAR2(4000),
FOREIGN KEY("serial_id") REFERENCES "serial"("id")
);
CREATE TABLE "service_serial" (
"service_id" NUMBER(16, 0),
"serial_id" NUMBER(16, 0),
FOREIGN KEY("service_id") REFERENCES "service"("id"),
FOREIGN KEY("serial_id") REFERENCES "serial"("id")
);
CREATE TABLE "identity" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"name" VARCHAR2(4000),
"level" VARCHAR2(4000),
"parent_id" NUMBER(16, 0),
FOREIGN KEY("parent_id") REFERENCES "identity"("id")
);
CREATE TABLE "credentials" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"service_id" NUMBER(16, 0),
"identity_id" NUMBER(16, 0),
CONSTRAINT credentials_service FOREIGN KEY("service_id") REFERENCES "service"("id"),
CONSTRAINT credentials_identity FOREIGN KEY("identity_id") REFERENCES "identity"("id")
);
CREATE TABLE "credentials_details" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"credentials_id" NUMBER(16, 0),
"name" VARCHAR2(4000),
"value" VARCHAR2(4000),
FOREIGN KEY("credentials_id") REFERENCES "credentials"("id")
);
CREATE TABLE "sid" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"tag" VARCHAR2(4000),
"recipe" VARCHAR2(4000)
);
CREATE TABLE "source" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"sid_id" NUMBER(16, 0),
"name" VARCHAR2(4000),
"url" VARCHAR2(4000),
"encoding" VARCHAR2(4000),
FOREIGN KEY("sid_id") REFERENCES "sid"("id")
);
CREATE TABLE "config" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"name" VARCHAR2(64) NOT NULL,
"value" VARCHAR2(4000) NOT NULL
);
CREATE TABLE "domain" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"domain" VARCHAR2(4000),
"status" NUMBER(16, 0)
);
CREATE TABLE "service_type_rule" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"fieldname" VARCHAR2(255),
"value" VARCHAR2(255),
"deny" NUMBER(1,0),
"tags" VARCHAR2(4000),
UNIQUE("fieldname", "value")
);
CREATE TABLE "service_rule" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"fieldname" VARCHAR2(255),
"value" VARCHAR2(255),
"deny" NUMBER(1,0),
"tags" VARCHAR2(4000),
UNIQUE("fieldname", "value")
);
/*
* Pathetically, "not null" on non-PK columns in MySQL doesn't mean
* "don't allow rows to be created where this field has no value", but
* "make up some crappy default value if none is specified", at least
* as far as release 4.0.18. That's why all the NOT NULL constrains
* were commented out in create_tables.mysql. Happily, Oracle does
* better: it's impossible to add a row which violates the constraint.
*/
CREATE TABLE "site" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"tag" VARCHAR2(255) NOT NULL,
"name" VARCHAR2(4000) NOT NULL,
"bg_colour" VARCHAR2(4000),
"email_address" VARCHAR2(255),
UNIQUE("tag")
);
CREATE TABLE "session" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"site_id" NUMBER(16, 0) NOT NULL,
"cookie" VARCHAR2(255) NOT NULL,
"user_id" NUMBER(16, 0),
"dest" VARCHAR2(4000),
"query" VARCHAR2(4000),
UNIQUE("cookie")
);
CREATE TABLE "user" (
"id" NUMBER(16, 0) NOT NULL PRIMARY KEY,
"site_id" NUMBER(16, 0) NOT NULL,
"admin" NUMBER(16, 0) DEFAULT 0 NOT NULL,
"name" VARCHAR2(4000) NOT NULL,
"email_address" VARCHAR2(255) NOT NULL,
"password" VARCHAR2(255) NOT NULL,
FOREIGN KEY("site_id") REFERENCES "site"("id"),
UNIQUE("site_id", "email_address")
);
QUIT