From c0b8e24a6fc3452b9eb3c0e6202919915bb51b54 Mon Sep 17 00:00:00 2001 From: Kees Monshouwer Date: Thu, 24 Apr 2014 18:20:34 +0200 Subject: [PATCH] cleanup goracle database schema --- .../goraclebackend/goracle-drop-schema.sql | 6 +- modules/goraclebackend/goracle-schema.sql | 122 +++++++++--------- 2 files changed, 64 insertions(+), 64 deletions(-) diff --git a/modules/goraclebackend/goracle-drop-schema.sql b/modules/goraclebackend/goracle-drop-schema.sql index cf23c81f5..f7a0370fc 100644 --- a/modules/goraclebackend/goracle-drop-schema.sql +++ b/modules/goraclebackend/goracle-drop-schema.sql @@ -1,9 +1,6 @@ DROP TABLE records; DROP SEQUENCE records_id_sequence; -DROP TABLE domains; -DROP SEQUENCE domains_id_sequence; - DROP TABLE supermasters; DROP TABLE domainmetadata; @@ -14,3 +11,6 @@ DROP SEQUENCE cryptokeys_id_sequence; DROP TABLE tsigkeys; DROP SEQUENCE tsigkeys_id_sequence; + +DROP TABLE domains; +DROP SEQUENCE domains_id_sequence; diff --git a/modules/goraclebackend/goracle-schema.sql b/modules/goraclebackend/goracle-schema.sql index 812a51524..a65e2842c 100644 --- a/modules/goraclebackend/goracle-schema.sql +++ b/modules/goraclebackend/goracle-schema.sql @@ -1,94 +1,94 @@ -create table domains ( - id NUMBER, - name VARCHAR(255) NOT NULL, - master VARCHAR(128) DEFAULT NULL, - last_check INT DEFAULT NULL, - type VARCHAR(6) NOT NULL, - notified_serial INT DEFAULT NULL, - account VARCHAR(40) DEFAULT NULL, - primary key (id) +CREATE TABLE domains ( + id INTEGER NOT NULL, + name VARCHAR2(255) NOT NULL, + master VARCHAR2(128) DEFAULT NULL, + last_check INTEGER DEFAULT NULL, + type VARCHAR2(6) NOT NULL, + notified_serial NUMBER(10,0) DEFAULT NULL, + account VARCHAR2(40) DEFAULT NULL, + PRIMARY KEY (id) ); -create sequence DOMAINS_ID_SEQUENCE; -create index DOMAINS$NAME on domains (NAME); +CREATE SEQUENCE domains_id_sequence; +CREATE INDEX domains$name ON domains (name); CREATE TABLE records ( - id number(11) not NULL, - domain_id INT DEFAULT NULL REFERENCES Domains(ID) ON DELETE CASCADE, - name VARCHAR(255) DEFAULT NULL, - type VARCHAR(10) DEFAULT NULL, + id INTEGER NOT NULL, + domain_id INTEGER DEFAULT NULL REFERENCES domains (id) ON DELETE CASCADE, + name VARCHAR2(255) DEFAULT NULL, + type VARCHAR2(10) DEFAULT NULL, content VARCHAR2(4000) DEFAULT NULL, - ttl INT DEFAULT NULL, - prio INT DEFAULT NULL, - change_date INT DEFAULT NULL, - disabled INT DEFAULT 0, - ordername VARCHAR(255) DEFAULT NULL, - auth INT DEFAULT NULL, - primary key (id) + ttl INTEGER DEFAULT NULL, + prio INTEGER DEFAULT NULL, + change_date INTEGER DEFAULT NULL, + disabled NUMBER(1,0) DEFAULT 0 NOT NULL, + ordername VARCHAR2(255) DEFAULT NULL, + auth NUMBER(1,0) DEFAULT 1 NOT NULL, + PRIMARY KEY (id) ) pctfree 40; -create index records$nametype on records (name, type); -create index records$domain_id on records (domain_id); -create index records$recordorder on records (domain_id, ordername); -create sequence records_id_sequence; +CREATE SEQUENCE records_id_sequence; +CREATE INDEX records$nametype ON records (name, type); +CREATE INDEX records$domain_id ON records (domain_id); +CREATE INDEX records$recordorder ON records (domain_id, ordername); -create table supermasters ( - ip VARCHAR(64) NOT NULL, - nameserver VARCHAR(255) NOT NULL, - account VARCHAR(40) DEFAULT NULL, - PRIMARY KEY(ip, nameserver) +CREATE TABLE supermasters ( + ip VARCHAR2(64) NOT NULL, + nameserver VARCHAR2(255) NOT NULL, + account VARCHAR2(40) DEFAULT NULL, + PRIMARY KEY (ip, nameserver) ); CREATE TABLE comments ( - id number(11) not NULL, - domain_id INT NOT NULL REFERENCES Domains(ID) ON DELETE CASCADE, - name VARCHAR(255) NOT NULL, - type VARCHAR(10) NOT NULL, - modified_at INT NOT NULL, - account VARCHAR(40) NOT NULL, + id INTEGER NOT NULL, + domain_id INTEGER NOT NULL REFERENCES domains (id) ON DELETE CASCADE, + name VARCHAR2(255) NOT NULL, + type VARCHAR2(10) NOT NULL, + modified_at INTEGER NOT NULL, + account VARCHAR2(40) NOT NULL, comment VARCHAR2(4000) NOT NULL ); +CREATE SEQUENCE comments_id_sequence; CREATE INDEX comments$nametype ON comments (name, type); CREATE INDEX comments$domain_id ON comments (domain_id); CREATE INDEX comments$order ON comments (domain_id, modified_at); -CREATE SEQUENCE comments_id_sequence; -create table domainmetadata ( - id NUMBER, - domain_id INT NOT NULL, - kind VARCHAR(16), +CREATE TABLE domainmetadata ( + id INTEGER NOT NULL, + domain_id INTEGER NOT NULL, + kind VARCHAR2(16), content VARCHAR2(4000), - primary key(id) + PRIMARY KEY (id) ); -create sequence DOMAINMETADATA_ID_SEQUENCE; -create index domainmetadata$domainid on domainmetadata(domain_id); +CREATE SEQUENCE domainmetadata_id_sequence; +CREATE INDEX domainmetadata$domain_id ON domainmetadata (domain_id); -create table cryptokeys ( - id NUMBER, - domain_id INT NOT NULL, - flags INT NOT NULL, - active INT NOT NULL, +CREATE TABLE cryptokeys ( + id INTEGER NOT NULL, + domain_id INTEGER NOT NULL, + flags INTEGER NOT NULL, + active INTEGER NOT NULL, content VARCHAR2(4000), - primary key(id) + PRIMARY KEY (id) ); -create sequence CRYPTOKEYS_ID_SEQUENCE; -create index cryptokeys$domainid on cryptokeys(domain_id); +CREATE SEQUENCE cryptokeys_id_sequence; +CREATE INDEX cryptokeys$domain_id ON cryptokeys (domain_id); -create table tsigkeys ( - id NUMBER, - name VARCHAR(255), - algorithm VARCHAR(50), - secret VARCHAR(255), - primary key(id) +CREATE TABLE tsigkeys ( + id INTEGER NOT NULL, + name VARCHAR2(255), + algorithm VARCHAR2(50), + secret VARCHAR2(255), + PRIMARY KEY (id) ); -create sequence TSIGKEYS_ID_SEQUENCE; -create unique index tsigkeys$namealgo on tsigkeys(name, algorithm); +CREATE SEQUENCE tsigkeys_id_sequence; +CREATE UNIQUE INDEX tsigkeys$namealgo ON tsigkeys (name, algorithm); -- 2.40.0