commit d7dfd8c04aa45af8646c1f7983950ed26aba6bde
parent 1ffc6ae3c31aaedbd27317cbede06b1b505ff301
Author: dankert <devnull@localhost>
Date: Fri, 12 Feb 2010 00:56:13 +0100
Ãberarbeitete DDL-Skripte für 4 DB-Systeme: Mysql, Postgrs, SQLite und Oracle. Oracle wird offiziell nicht unterstützt.
Diffstat:
4 files changed, 1491 insertions(+), 889 deletions(-)
diff --git a/doc/database/sql/mysql/create.sql b/doc/database/sql/mysql/create.sql
@@ -1,313 +1,393 @@
--- $Id$
---
--- OpenRat Content Management System
--- SQL-Dump MySql 3.x, 4.x, 5.x
---
--- Licensed under the GNU General Public Licence
+-- DDL-Script for mysql
+-- Table project
+CREATE TABLE or_project(
+ id INT NOT NULL
+ ,name VARCHAR(128) NOT NULL
+ ,target_dir VARCHAR(255) NOT NULL
+ ,ftp_url VARCHAR(255) NOT NULL
+ ,ftp_passive TINYINT(1) NOT NULL DEFAULT 0
+ ,cmd_after_publish VARCHAR(255) NOT NULL
+ ,content_negotiation TINYINT(1) NOT NULL DEFAULT 0
+ ,cut_index TINYINT(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+) ENGINE InnoDB;
+CREATE UNIQUE INDEX or_uidx_project_name
+ ON or_project (name);
-CREATE TABLE or_project (
- id INT NOT NULL,
- name VARCHAR(128) NOT NULL
- , target_dir VARCHAR(255) NOT NULL
- , ftp_url VARCHAR(255) NOT NULL
- , ftp_passive tinyint(1) DEFAULT 0 NOT NULL
- , cmd_after_publish VARCHAR(255) NOT NULL
- , content_negotiation tinyint(1) DEFAULT 0 NOT NULL
- , cut_index tinyint(1) DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
-);
-CREATE UNIQUE INDEX idx_project_uk ON or_project (name);
+-- Table user
+CREATE TABLE or_user(
+ id INT NOT NULL
+ ,name VARCHAR(128) NOT NULL
+ ,password VARCHAR(50) NOT NULL
+ ,ldap_dn VARCHAR(255) NOT NULL
+ ,fullname VARCHAR(128) NOT NULL
+ ,tel VARCHAR(128) NOT NULL
+ ,mail VARCHAR(255) NOT NULL
+ ,descr VARCHAR(255) NOT NULL
+ ,style VARCHAR(64) NOT NULL
+ ,is_admin TINYINT(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+) ENGINE InnoDB;
+CREATE UNIQUE INDEX or_uidx_user_name
+ ON or_user (name);
-CREATE TABLE or_user (
- id INT NOT NULL
- , name VARCHAR(128) NOT NULL
- , password VARCHAR(50) NOT NULL
- , ldap_dn VARCHAR(255) NOT NULL
- , fullname VARCHAR(128) NOT NULL
- , tel VARCHAR(128) NOT NULL
- , mail VARCHAR(255) NOT NULL
- , descr VARCHAR(255) NOT NULL
- , style VARCHAR(64) NOT NULL
- , is_admin INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
-);
-CREATE UNIQUE INDEX idx_user_uk ON or_user (name);
+-- Table group
+CREATE TABLE or_group(
+ id INT NOT NULL
+ ,name VARCHAR(100) NOT NULL
+ ,PRIMARY KEY (id)
+) ENGINE InnoDB;
+CREATE UNIQUE INDEX or_uidx_group_name
+ ON or_group (name);
-CREATE TABLE or_group (
- id INT NOT NULL
- , name VARCHAR(100) NOT NULL
- , PRIMARY KEY (id)
-);
-CREATE UNIQUE INDEX idx_group_uk ON or_group (name);
+-- Table object
+CREATE TABLE or_object(
+ id INT NOT NULL
+ ,parentid INT NULL
+ ,projectid INT NOT NULL DEFAULT 0
+ ,filename VARCHAR(255) NOT NULL
+ ,orderid INT NOT NULL DEFAULT 0
+ ,create_date INT NOT NULL DEFAULT 0
+ ,create_userid INT NULL DEFAULT 0
+ ,lastchange_date INT NOT NULL DEFAULT 0
+ ,lastchange_userid INT NULL DEFAULT 0
+ ,is_folder TINYINT(1) NOT NULL
+ ,is_file TINYINT(1) NOT NULL
+ ,is_page TINYINT(1) NOT NULL
+ ,is_link TINYINT(1) NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_object_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_object_lastchange_userid
+ FOREIGN KEY (lastchange_userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_object_create_userid
+ FOREIGN KEY (create_userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE INDEX or_idx_object_parentid
+ ON or_object (parentid);
+CREATE INDEX or_idx_object_projectid
+ ON or_object (projectid);
+CREATE INDEX or_idx_object_is_folder
+ ON or_object (is_folder);
+CREATE INDEX or_idx_object_is_file
+ ON or_object (is_file);
+CREATE INDEX or_idx_object_is_page
+ ON or_object (is_page);
+CREATE INDEX or_idx_object_is_link
+ ON or_object (is_link);
+CREATE INDEX or_idx_object_orderid
+ ON or_object (orderid);
+CREATE INDEX or_idx_object_create_userid
+ ON or_object (create_userid);
+CREATE INDEX or_idx_object_lastchange_userid
+ ON or_object (lastchange_userid);
+CREATE UNIQUE INDEX or_uidx_object_parentid_filename
+ ON or_object (parentid,filename);
-CREATE TABLE or_object (
- id INT NOT NULL
- , parentid INT
- , projectid INT DEFAULT 0 NOT NULL
- , filename VARCHAR(255) NOT NULL
- , orderid INT DEFAULT 0 NOT NULL
- , create_date INT DEFAULT 0 NOT NULL
- , create_userid INT DEFAULT 0
- , lastchange_date INT DEFAULT 0 NOT NULL
- , lastchange_userid INT DEFAULT 0
- , is_folder INT DEFAULT 0 NOT NULL
- , is_file INT DEFAULT 0 NOT NULL
- , is_page INT DEFAULT 0 NOT NULL
- , is_link INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_object_01 FOREIGN KEY (projectid)
- REFERENCES or_project (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_object_02 FOREIGN KEY (lastchange_userid)
- REFERENCES or_user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_object_03 FOREIGN KEY (create_userid)
- REFERENCES or_user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE INDEX idx_object_01 ON or_object (parentid);
-CREATE INDEX idx_object_02 ON or_object (projectid);
-CREATE INDEX idx_object_03 ON or_object (is_folder);
-CREATE INDEX idx_object_04 ON or_object (is_file);
-CREATE INDEX idx_object_05 ON or_object (is_page);
-CREATE INDEX idx_object_06 ON or_object (is_link);
-CREATE INDEX idx_object_07 ON or_object (orderid);
-CREATE INDEX idx_object_08 ON or_object (create_userid);
-CREATE INDEX idx_object_09 ON or_object (lastchange_userid);
-CREATE UNIQUE INDEX idx_object_uk ON or_object (parentid, filename);
+-- Table template
+CREATE TABLE or_template(
+ id INT NOT NULL
+ ,projectid INT NOT NULL
+ ,name VARCHAR(50) NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_template_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE INDEX or_idx_template_projectid
+ ON or_template (projectid);
+CREATE INDEX or_idx_template_name
+ ON or_template (name);
+CREATE UNIQUE INDEX or_uidx_template_projectid_name
+ ON or_template (projectid,name);
-CREATE TABLE or_template (
- id INT NOT NULL
- , projectid INT NOT NULL
- , name VARCHAR(50) NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_template_01 FOREIGN KEY (projectid)
- REFERENCES or_project (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE INDEX idx_template_01 ON or_template (projectid);
-CREATE INDEX idx_template_02 ON or_template (name);
-CREATE UNIQUE INDEX idx_template_uk ON or_template (projectid, name);
+-- Table language
+CREATE TABLE or_language(
+ id INT NOT NULL
+ ,projectid INT NOT NULL DEFAULT 0
+ ,isocode VARCHAR(10) NOT NULL
+ ,name VARCHAR(50) NOT NULL
+ ,is_default TINYINT(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_language_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE UNIQUE INDEX or_uidx_language_projectid_isocode
+ ON or_language (projectid,isocode);
-CREATE TABLE or_language (
- id INT NOT NULL
- , projectid INT DEFAULT 0 NOT NULL
- , isocode VARCHAR(10) NOT NULL
- , name VARCHAR(50) NOT NULL
- , is_default INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_language_01 FOREIGN KEY (projectid)
- REFERENCES or_project (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE UNIQUE INDEX idx_language_uk ON or_language (projectid, isocode);
+-- Table page
+CREATE TABLE or_page(
+ id INT NOT NULL
+ ,objectid INT NOT NULL DEFAULT 0
+ ,templateid INT NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_page_templateid
+ FOREIGN KEY (templateid) REFERENCES or_template (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_page_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE UNIQUE INDEX or_uidx_page_objectid
+ ON or_page (objectid);
+CREATE INDEX or_idx_page_templateid
+ ON or_page (templateid);
-CREATE TABLE or_page (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , templateid INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_page_01 FOREIGN KEY (templateid)
- REFERENCES or_template (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_page_02 FOREIGN KEY (objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE UNIQUE INDEX idx_page_uk ON or_page (objectid);
-CREATE INDEX idx_page_01 ON or_page (templateid);
+-- Table projectmodel
+CREATE TABLE or_projectmodel(
+ id INT NOT NULL
+ ,projectid INT NOT NULL DEFAULT 0
+ ,name VARCHAR(50) NOT NULL
+ ,extension VARCHAR(10) NULL
+ ,is_default TINYINT(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_projectmodel_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE INDEX or_idx_projectmodel_projectid
+ ON or_projectmodel (projectid);
+CREATE UNIQUE INDEX or_uidx_projectmodel_projectid_name
+ ON or_projectmodel (projectid,name);
-CREATE TABLE or_projectmodel (
- id INT NOT NULL
- , projectid INT DEFAULT 0 NOT NULL
- , name VARCHAR(50) NOT NULL
- , extension VARCHAR(10)
- , is_default CHAR(10) DEFAULT '0' NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_projectmodel_01 FOREIGN KEY (projectid)
- REFERENCES or_project (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE INDEX idx_projectmodel_01 ON or_projectmodel (projectid);
-CREATE UNIQUE INDEX idx_projectmodel_uk ON or_projectmodel (projectid, name);
+-- Table element
+CREATE TABLE or_element(
+ id INT NOT NULL
+ ,templateid INT NOT NULL DEFAULT 0
+ ,name VARCHAR(50) NOT NULL
+ ,descr VARCHAR(255) NOT NULL
+ ,type VARCHAR(20) NOT NULL
+ ,subtype VARCHAR(20) NULL
+ ,with_icon TINYINT(1) NOT NULL DEFAULT 0
+ ,dateformat VARCHAR(100) NULL
+ ,wiki TINYINT(1) NULL DEFAULT 0
+ ,html TINYINT(1) NULL DEFAULT 0
+ ,all_languages TINYINT(1) NOT NULL DEFAULT 0
+ ,writable TINYINT(1) NOT NULL DEFAULT 0
+ ,decimals INT NULL DEFAULT 0
+ ,dec_point VARCHAR(5) NULL
+ ,thousand_sep VARCHAR(1) NULL
+ ,code MEDIUMTEXT NULL
+ ,default_text MEDIUMTEXT NULL
+ ,folderobjectid INT NULL
+ ,default_objectid INT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_element_default_objectid
+ FOREIGN KEY (default_objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_element_folderobjectid
+ FOREIGN KEY (folderobjectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_element_templateid
+ FOREIGN KEY (templateid) REFERENCES or_template (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE INDEX or_idx_element_templateid
+ ON or_element (templateid);
+CREATE INDEX or_idx_element_name
+ ON or_element (name);
+CREATE UNIQUE INDEX or_uidx_element_templateid_name
+ ON or_element (templateid,name);
-CREATE TABLE or_element (
- id INT NOT NULL
- , templateid INT DEFAULT 0 NOT NULL
- , name VARCHAR(50) NOT NULL
- , descr VARCHAR(255) NOT NULL
- , type VARCHAR(20) NOT NULL
- , subtype VARCHAR(20)
- , with_icon CHAR(1) DEFAULT '0' NOT NULL
- , dateformat VARCHAR(100)
- , wiki CHAR(1) DEFAULT '0'
- , html CHAR(1) DEFAULT '0'
- , all_languages CHAR(1) DEFAULT '0' NOT NULL
- , writable CHAR(1) DEFAULT '0' NOT NULL
- , decimals INT DEFAULT 0
- , dec_point VARCHAR(5)
- , thousand_sep CHAR(1)
- , code TEXT
- , default_text TEXT
- , folderobjectid INT
- , default_objectid INT
- , PRIMARY KEY (id)
- , CONSTRAINT fk_element_01 FOREIGN KEY (default_objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_element_02 FOREIGN KEY (folderobjectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_element_03 FOREIGN KEY (templateid)
- REFERENCES or_template (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE INDEX idx_element_01 ON or_element (templateid);
-CREATE INDEX idx_element_02 ON or_element (name);
-CREATE UNIQUE INDEX idx_element_uk ON or_element (templateid, name);
+-- Table file
+CREATE TABLE or_file(
+ id INT NOT NULL
+ ,objectid INT NOT NULL DEFAULT 0
+ ,extension VARCHAR(10) NOT NULL
+ ,size INT NOT NULL DEFAULT 0
+ ,value MEDIUMBLOB NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_file_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE UNIQUE INDEX or_uidx_file_objectid
+ ON or_file (objectid);
-CREATE TABLE or_file (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , extension VARCHAR(10) NOT NULL
- , size INT DEFAULT 0 NOT NULL
- , value MEDIUMBLOB NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_file_01 FOREIGN KEY (objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE UNIQUE INDEX idx_file_01 ON or_file (objectid);
+-- Table folder
+CREATE TABLE or_folder(
+ id INT NOT NULL
+ ,objectid INT NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_folder_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE UNIQUE INDEX or_uidx_folder_objectid
+ ON or_folder (objectid);
-CREATE TABLE or_folder (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_folder_01 FOREIGN KEY (objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE UNIQUE INDEX idx_folder_01 ON or_folder (objectid);
+-- Table link
+CREATE TABLE or_link(
+ id INT NOT NULL
+ ,objectid INT NOT NULL DEFAULT 0
+ ,link_objectid INT NULL
+ ,url VARCHAR(255) NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_link_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_link_link_objectid
+ FOREIGN KEY (link_objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE UNIQUE INDEX or_uidx_link_objectid
+ ON or_link (objectid);
+CREATE INDEX or_idx_link_link_objectid
+ ON or_link (link_objectid);
-CREATE TABLE or_link (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , link_objectid INT
- , url VARCHAR(255)
- , PRIMARY KEY (id)
- , CONSTRAINT fk_link_01 FOREIGN KEY (objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_link_02 FOREIGN KEY (link_objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE UNIQUE INDEX idx_link_01 ON or_link (objectid);
-CREATE INDEX idx_link_02 ON or_link (link_objectid);
+-- Table name
+CREATE TABLE or_name(
+ id INT NOT NULL
+ ,objectid INT NOT NULL DEFAULT 0
+ ,name VARCHAR(255) NOT NULL
+ ,descr VARCHAR(255) NOT NULL
+ ,languageid INT NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_name_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_name_languageid
+ FOREIGN KEY (languageid) REFERENCES or_language (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE INDEX or_idx_name_objectid
+ ON or_name (objectid);
+CREATE INDEX or_idx_name_languageid
+ ON or_name (languageid);
+CREATE UNIQUE INDEX or_uidx_name_objectid_languageid
+ ON or_name (objectid,languageid);
-CREATE TABLE or_name (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , name VARCHAR(255) NOT NULL
- , descr VARCHAR(255) NOT NULL
- , languageid INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_name_01 FOREIGN KEY (objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_name_02 FOREIGN KEY (languageid)
- REFERENCES or_language (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE INDEX idx_name_01 ON or_name (objectid);
-CREATE INDEX idx_name_02 ON or_name (languageid);
-CREATE UNIQUE INDEX idx_name_uk ON or_name (objectid, languageid);
+-- Table templatemodel
+CREATE TABLE or_templatemodel(
+ id INT NOT NULL
+ ,templateid INT NOT NULL DEFAULT 0
+ ,projectmodelid INT NOT NULL DEFAULT 0
+ ,extension VARCHAR(10) NULL
+ ,text MEDIUMTEXT NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_templatemodel_templateid
+ FOREIGN KEY (templateid) REFERENCES or_template (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_templatemodel_projectmodelid
+ FOREIGN KEY (projectmodelid) REFERENCES or_projectmodel (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE INDEX or_idx_templatemodel_templateid
+ ON or_templatemodel (templateid);
+CREATE UNIQUE INDEX or_uidx_templatemodel_templateid_extension
+ ON or_templatemodel (templateid,extension);
+CREATE UNIQUE INDEX or_uidx_templatemodel_templateid_projectmodelid
+ ON or_templatemodel (templateid,projectmodelid);
-CREATE TABLE or_templatemodel (
- id INT NOT NULL
- , templateid INT DEFAULT 0 NOT NULL
- , projectmodelid INT DEFAULT 0 NOT NULL
- , extension VARCHAR(10)
- , text TEXT NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT UQ_or_templatemodel_1 UNIQUE (templateid, extension)
- , CONSTRAINT fk_templatemodel_01 FOREIGN KEY (templateid)
- REFERENCES or_template (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_templatemodel_02 FOREIGN KEY (projectmodelid)
- REFERENCES or_projectmodel (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE INDEX idx_templatemodel_01 ON or_templatemodel (templateid);
-CREATE UNIQUE INDEX idx_templatemodel_uk_01 ON or_templatemodel (templateid, projectmodelid);
+-- Table usergroup
+CREATE TABLE or_usergroup(
+ id INT NOT NULL
+ ,userid INT NOT NULL
+ ,groupid INT NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_usergroup_groupid
+ FOREIGN KEY (groupid) REFERENCES or_group (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_usergroup_userid
+ FOREIGN KEY (userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE INDEX or_idx_usergroup_groupid
+ ON or_usergroup (groupid);
+CREATE INDEX or_idx_usergroup_userid
+ ON or_usergroup (userid);
+CREATE UNIQUE INDEX or_uidx_usergroup_userid_groupid
+ ON or_usergroup (userid,groupid);
-CREATE TABLE or_usergroup (
- id INT NOT NULL
- , userid INT DEFAULT 0 NOT NULL
- , groupid INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_usergroup_01 FOREIGN KEY (groupid)
- REFERENCES or_group (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_usergroup_02 FOREIGN KEY (userid)
- REFERENCES or_user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE INDEX idx_usergroup_01 ON or_usergroup (groupid);
-CREATE INDEX idx_usergroup_02 ON or_usergroup (userid);
-CREATE UNIQUE INDEX idx_usergroup_uk ON or_usergroup (userid, groupid);
+-- Table value
+CREATE TABLE or_value(
+ id INT NOT NULL
+ ,pageid INT NOT NULL DEFAULT 0
+ ,languageid INT NOT NULL
+ ,elementid INT NOT NULL DEFAULT 0
+ ,linkobjectid INT NULL
+ ,text MEDIUMTEXT NULL
+ ,number INT NULL
+ ,date INT NULL
+ ,active INT NOT NULL DEFAULT 0
+ ,publish INT NOT NULL
+ ,lastchange_date INT NOT NULL DEFAULT 0
+ ,lastchange_userid INT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_value_pageid
+ FOREIGN KEY (pageid) REFERENCES or_page (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_value_elementid
+ FOREIGN KEY (elementid) REFERENCES or_element (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_value_languageid
+ FOREIGN KEY (languageid) REFERENCES or_language (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_value_lastchange_userid
+ FOREIGN KEY (lastchange_userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_value_linkobjectid
+ FOREIGN KEY (linkobjectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE INDEX or_idx_value_pageid
+ ON or_value (pageid);
+CREATE INDEX or_idx_value_languageid
+ ON or_value (languageid);
+CREATE INDEX or_idx_value_elementid
+ ON or_value (elementid);
+CREATE INDEX or_idx_value_active
+ ON or_value (active);
+CREATE INDEX or_idx_value_lastchange_date
+ ON or_value (lastchange_date);
+CREATE INDEX or_idx_value_publish
+ ON or_value (publish);
-CREATE TABLE or_value (
- id INT NOT NULL
- , pageid INT DEFAULT 0 NOT NULL
- , languageid INT NOT NULL
- , elementid INT DEFAULT 0 NOT NULL
- , linkobjectid INT
- , text TEXT
- , number INT
- , date INT
- , active INT DEFAULT 0 NOT NULL
- , publish INT NOT NULL
- , lastchange_date INT DEFAULT 0 NOT NULL
- , lastchange_userid INT DEFAULT 0
- , PRIMARY KEY (id)
- , CONSTRAINT fk_value_01 FOREIGN KEY (pageid)
- REFERENCES or_page (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_value_02 FOREIGN KEY (elementid)
- REFERENCES or_element (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_value_03 FOREIGN KEY (languageid)
- REFERENCES or_language (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_value_04 FOREIGN KEY (lastchange_userid)
- REFERENCES or_user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_value_05 FOREIGN KEY (linkobjectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE INDEX idx_value_01 ON or_value (pageid);
-CREATE INDEX idx_value_02 ON or_value (languageid);
-CREATE INDEX idx_value_03 ON or_value (elementid);
-CREATE INDEX idx_value_04 ON or_value (active);
-CREATE INDEX idx_value_05 ON or_value (lastchange_date);
-CREATE INDEX idx_value_06 ON or_value (elementid);
-CREATE INDEX idx_value_07 ON or_value (publish);
-
-CREATE TABLE or_acl (
- id INT NOT NULL
- , userid INT
- , groupid INT
- , objectid INT DEFAULT 0 NOT NULL
- , languageid INT DEFAULT 0
- , is_write INT DEFAULT 0 NOT NULL
- , is_prop INT DEFAULT 0 NOT NULL
- , is_create_folder INT DEFAULT 0 NOT NULL
- , is_create_file INT DEFAULT 0 NOT NULL
- , is_create_link INT DEFAULT 0 NOT NULL
- , is_create_page INT DEFAULT 0 NOT NULL
- , is_delete INT DEFAULT 0 NOT NULL
- , is_release INT NOT NULL
- , is_publish INT DEFAULT 0 NOT NULL
- , is_grant INT DEFAULT 0 NOT NULL
- , is_transmit CHAR(10) NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_acl_01 FOREIGN KEY (groupid)
- REFERENCES or_group (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_acl_02 FOREIGN KEY (userid)
- REFERENCES or_user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_acl_03 FOREIGN KEY (objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_acl_04 FOREIGN KEY (languageid)
- REFERENCES or_language (id) ON DELETE RESTRICT ON UPDATE RESTRICT
-);
-CREATE INDEX idx_acl_01 ON or_acl (userid);
-CREATE INDEX idx_acl_02 ON or_acl (groupid);
-CREATE INDEX idx_acl_03 ON or_acl (languageid);
-CREATE INDEX idx_acl_04 ON or_acl (objectid);
-CREATE INDEX idx_acl_05 ON or_acl (is_transmit);
-
-
--- Insert 1 Admin-User
-
-INSERT INTO or_user
- (id,name,password,ldap_dn,fullname,tel,mail,descr,style,is_admin)
- VALUES( 1,'admin','admin','','Administrator','','','Admin user','default',1 );
-
-
-\ No newline at end of file
+-- Table acl
+CREATE TABLE or_acl(
+ id INT NOT NULL
+ ,userid INT NULL
+ ,groupid INT NULL
+ ,objectid INT NOT NULL
+ ,languageid INT NULL DEFAULT 0
+ ,is_write TINYINT(1) NOT NULL DEFAULT 0
+ ,is_prop TINYINT(1) NOT NULL DEFAULT 0
+ ,is_create_folder TINYINT(1) NOT NULL DEFAULT 0
+ ,is_create_file TINYINT(1) NOT NULL DEFAULT 0
+ ,is_create_link TINYINT(1) NOT NULL DEFAULT 0
+ ,is_create_page TINYINT(1) NOT NULL DEFAULT 0
+ ,is_delete TINYINT(1) NOT NULL DEFAULT 0
+ ,is_release TINYINT(1) NOT NULL DEFAULT 0
+ ,is_publish TINYINT(1) NOT NULL DEFAULT 0
+ ,is_grant TINYINT(1) NOT NULL DEFAULT 0
+ ,is_transmit TINYINT(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_acl_groupid
+ FOREIGN KEY (groupid) REFERENCES or_group (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_acl_userid
+ FOREIGN KEY (userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_acl_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_acl_languageid
+ FOREIGN KEY (languageid) REFERENCES or_language (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+) ENGINE InnoDB;
+CREATE INDEX or_idx_acl_userid
+ ON or_acl (userid);
+CREATE INDEX or_idx_acl_groupid
+ ON or_acl (groupid);
+CREATE INDEX or_idx_acl_languageid
+ ON or_acl (languageid);
+CREATE INDEX or_idx_acl_objectid
+ ON or_acl (objectid);
+CREATE INDEX or_idx_acl_is_transmit
+ ON or_acl (is_transmit);
+INSERT INTO or_user (id,name,password,ldap_dn,fullname,tel,mail,descr,style,is_admin) VALUES(1,'admin','admin','','Administrator','','','Admin user','default',1)
diff --git a/doc/database/sql/oracle/create.sql b/doc/database/sql/oracle/create.sql
@@ -1,313 +1,363 @@
--- OpenRat DDL for Oracle RDBMS
---
--- Special Oracle behavour need the following differences:
--- using CLOBs for text an file values
--- keywords number, size, date in '"'
--- removed: "on delete restrict on update restrict"
--- ' ' instead of '' because of Oracles NULL/''-Bug
-
-CREATE TABLE or_project (
- id INT NOT NULL,
- name VARCHAR(128) NOT NULL
- , target_dir VARCHAR(255) NOT NULL
- , ftp_url VARCHAR(255) NOT NULL
- , ftp_passive INT DEFAULT 0 NOT NULL
- , cmd_after_publish VARCHAR(255) NOT NULL
- , content_negotiation INT DEFAULT 0 NOT NULL
- , cut_index INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
-);
-CREATE UNIQUE INDEX idx_project_uk ON or_project (name);
-
-CREATE TABLE or_user (
- id INT NOT NULL
- , name VARCHAR(128) NOT NULL
- , password VARCHAR(50) NOT NULL
- , ldap_dn VARCHAR(255) NOT NULL
- , fullname VARCHAR(128) NOT NULL
- , tel VARCHAR(128) NOT NULL
- , mail VARCHAR(255) NOT NULL
- , descr VARCHAR(255) NOT NULL
- , style VARCHAR(64) NOT NULL
- , is_admin INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
-);
-CREATE UNIQUE INDEX idx_user_uk ON or_user (name);
-
-CREATE TABLE or_group (
- id INT NOT NULL
- , name VARCHAR(100) NOT NULL
- , PRIMARY KEY (id)
-);
-CREATE UNIQUE INDEX idx_group_uk ON or_group (name);
-
-CREATE TABLE or_object (
- id INT NOT NULL
- , parentid INT
- , projectid INT DEFAULT 0 NOT NULL
- , filename VARCHAR(255) NOT NULL
- , orderid INT DEFAULT 0 NOT NULL
- , create_date INT DEFAULT 0 NOT NULL
- , create_userid INT DEFAULT 0
- , lastchange_date INT DEFAULT 0 NOT NULL
- , lastchange_userid INT DEFAULT 0
- , is_folder INT DEFAULT 0 NOT NULL
- , is_file INT DEFAULT 0 NOT NULL
- , is_page INT DEFAULT 0 NOT NULL
- , is_link INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_object_01 FOREIGN KEY (projectid)
- REFERENCES or_project (id)
- , CONSTRAINT fk_object_02 FOREIGN KEY (lastchange_userid)
- REFERENCES or_user (id)
- , CONSTRAINT fk_object_03 FOREIGN KEY (create_userid)
- REFERENCES or_user (id)
-);
-CREATE INDEX idx_object_01 ON or_object (parentid);
-CREATE INDEX idx_object_02 ON or_object (projectid);
-CREATE INDEX idx_object_03 ON or_object (is_folder);
-CREATE INDEX idx_object_04 ON or_object (is_file);
-CREATE INDEX idx_object_05 ON or_object (is_page);
-CREATE INDEX idx_object_06 ON or_object (is_link);
-CREATE INDEX idx_object_07 ON or_object (orderid);
-CREATE INDEX idx_object_08 ON or_object (create_userid);
-CREATE INDEX idx_object_09 ON or_object (lastchange_userid);
-CREATE UNIQUE INDEX idx_object_uk ON or_object (parentid, filename);
-
-CREATE TABLE or_template (
- id INT NOT NULL
- , projectid INT NOT NULL
- , name VARCHAR(50) NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_template_01 FOREIGN KEY (projectid)
- REFERENCES or_project (id)
-);
-CREATE INDEX idx_template_01 ON or_template (projectid);
-CREATE INDEX idx_template_02 ON or_template (name);
-CREATE UNIQUE INDEX idx_template_uk ON or_template (projectid, name);
-
-CREATE TABLE or_language (
- id INT NOT NULL
- , projectid INT DEFAULT 0 NOT NULL
- , isocode VARCHAR(10) NOT NULL
- , name VARCHAR(50) NOT NULL
- , is_default INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_language_01 FOREIGN KEY (projectid)
- REFERENCES or_project (id)
-);
-CREATE UNIQUE INDEX idx_language_uk ON or_language (projectid, isocode);
-
-CREATE TABLE or_page (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , templateid INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_page_01 FOREIGN KEY (templateid)
- REFERENCES or_template (id)
- , CONSTRAINT fk_page_02 FOREIGN KEY (objectid)
- REFERENCES or_object (id)
-);
-CREATE UNIQUE INDEX idx_page_uk ON or_page (objectid);
-CREATE INDEX idx_page_01 ON or_page (templateid);
-
-CREATE TABLE or_projectmodel (
- id INT NOT NULL
- , projectid INT DEFAULT 0 NOT NULL
- , name VARCHAR(50) NOT NULL
- , extension VARCHAR(10)
- , is_default CHAR(10) DEFAULT '0' NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_projectmodel_01 FOREIGN KEY (projectid)
- REFERENCES or_project (id)
-);
-CREATE INDEX idx_projectmodel_01 ON or_projectmodel (projectid);
-CREATE UNIQUE INDEX idx_projectmodel_uk ON or_projectmodel (projectid, name);
-
-CREATE TABLE or_element (
- id INT NOT NULL
- , templateid INT DEFAULT 0 NOT NULL
- , name VARCHAR(50) NOT NULL
- , descr VARCHAR(255) NOT NULL
- , type VARCHAR(20) NOT NULL
- , subtype VARCHAR(20)
- , with_icon CHAR(1) DEFAULT '0' NOT NULL
- , dateformat VARCHAR(100)
- , wiki CHAR(1) DEFAULT '0'
- , html CHAR(1) DEFAULT '0'
- , all_languages CHAR(1) DEFAULT '0' NOT NULL
- , writable CHAR(1) DEFAULT '0' NOT NULL
- , decimals INT DEFAULT 0
- , dec_point VARCHAR(5)
- , thousand_sep CHAR(1)
- , code CLOB
- , default_text CLOB
- , folderobjectid INT
- , default_objectid INT
- , PRIMARY KEY (id)
- , CONSTRAINT fk_element_01 FOREIGN KEY (default_objectid)
- REFERENCES or_object (id)
- , CONSTRAINT fk_element_02 FOREIGN KEY (folderobjectid)
- REFERENCES or_object (id)
- , CONSTRAINT fk_element_03 FOREIGN KEY (templateid)
- REFERENCES or_template (id)
-);
-CREATE INDEX idx_element_01 ON or_element (templateid);
-CREATE INDEX idx_element_02 ON or_element (name);
-CREATE UNIQUE INDEX idx_element_uk ON or_element (templateid, name);
-
-CREATE TABLE or_file (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , extension VARCHAR(10) NOT NULL
- , "SIZE" INT DEFAULT 0 NOT NULL
- , value CLOB NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_file_01 FOREIGN KEY (objectid)
- REFERENCES or_object (id)
-);
-CREATE UNIQUE INDEX idx_file_01 ON or_file (objectid);
-
-CREATE TABLE or_folder (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_folder_01 FOREIGN KEY (objectid)
- REFERENCES or_object (id)
-);
-CREATE UNIQUE INDEX idx_folder_01 ON or_folder (objectid);
-
-CREATE TABLE or_link (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , link_objectid INT
- , url VARCHAR(255)
- , PRIMARY KEY (id)
- , CONSTRAINT fk_link_01 FOREIGN KEY (objectid)
- REFERENCES or_object (id)
- , CONSTRAINT fk_link_02 FOREIGN KEY (link_objectid)
- REFERENCES or_object (id)
-);
-CREATE UNIQUE INDEX idx_link_01 ON or_link (objectid);
-CREATE INDEX idx_link_02 ON or_link (link_objectid);
-
-CREATE TABLE or_name (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , name VARCHAR(255) NOT NULL
- , descr VARCHAR(255) NOT NULL
- , languageid INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_name_01 FOREIGN KEY (objectid)
- REFERENCES or_object (id)
- , CONSTRAINT fk_name_02 FOREIGN KEY (languageid)
- REFERENCES or_language (id)
-);
-CREATE INDEX idx_name_01 ON or_name (objectid);
-CREATE INDEX idx_name_02 ON or_name (languageid);
-CREATE UNIQUE INDEX idx_name_uk ON or_name (objectid, languageid);
-
-CREATE TABLE or_templatemodel (
- id INT NOT NULL
- , templateid INT DEFAULT 0 NOT NULL
- , projectmodelid INT DEFAULT 0 NOT NULL
- , extension VARCHAR(10)
- , text CLOB NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT UQ_or_templatemodel_1 UNIQUE (templateid, extension)
- , CONSTRAINT fk_templatemodel_01 FOREIGN KEY (templateid)
- REFERENCES or_template (id)
- , CONSTRAINT fk_templatemodel_02 FOREIGN KEY (projectmodelid)
- REFERENCES or_projectmodel (id)
-);
-CREATE INDEX idx_templatemodel_01 ON or_templatemodel (templateid);
-CREATE UNIQUE INDEX idx_templatemodel_uk_01 ON or_templatemodel (templateid, projectmodelid);
-
-CREATE TABLE or_usergroup (
- id INT NOT NULL
- , userid INT DEFAULT 0 NOT NULL
- , groupid INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_usergroup_01 FOREIGN KEY (groupid)
- REFERENCES or_group (id)
- , CONSTRAINT fk_usergroup_02 FOREIGN KEY (userid)
- REFERENCES or_user (id)
-);
-CREATE INDEX idx_usergroup_01 ON or_usergroup (groupid);
-CREATE INDEX idx_usergroup_02 ON or_usergroup (userid);
-CREATE UNIQUE INDEX idx_usergroup_uk ON or_usergroup (userid, groupid);
-
-CREATE TABLE or_value (
- id INT NOT NULL
- , pageid INT DEFAULT 0 NOT NULL
- , languageid INT NOT NULL
- , elementid INT DEFAULT 0 NOT NULL
- , linkobjectid INT
- , text CLOB
- , "NUMBER" INT
- , "DATE" INT
- , active INT DEFAULT 0 NOT NULL
- , publish INT NOT NULL
- , lastchange_date INT DEFAULT 0 NOT NULL
- , lastchange_userid INT DEFAULT 0
- , PRIMARY KEY (id)
- , CONSTRAINT fk_value_01 FOREIGN KEY (pageid)
- REFERENCES or_page (id)
- , CONSTRAINT fk_value_02 FOREIGN KEY (elementid)
- REFERENCES or_element (id)
- , CONSTRAINT fk_value_03 FOREIGN KEY (languageid)
- REFERENCES or_language (id)
- , CONSTRAINT fk_value_04 FOREIGN KEY (lastchange_userid)
- REFERENCES or_user (id)
- , CONSTRAINT fk_value_05 FOREIGN KEY (linkobjectid)
- REFERENCES or_object (id)
-);
-CREATE INDEX idx_value_01 ON or_value (pageid);
-CREATE INDEX idx_value_02 ON or_value (languageid);
-CREATE INDEX idx_value_03 ON or_value (elementid);
-CREATE INDEX idx_value_04 ON or_value (active);
-CREATE INDEX idx_value_05 ON or_value (lastchange_date);
--- CREATE INDEX idx_value_06 ON or_value (elementid);
-CREATE INDEX idx_value_07 ON or_value (publish);
-
-CREATE TABLE or_acl (
- id INT NOT NULL
- , userid INT
- , groupid INT
- , objectid INT DEFAULT 0 NOT NULL
- , languageid INT DEFAULT 0
- , is_write INT DEFAULT 0 NOT NULL
- , is_prop INT DEFAULT 0 NOT NULL
- , is_create_folder INT DEFAULT 0 NOT NULL
- , is_create_file INT DEFAULT 0 NOT NULL
- , is_create_link INT DEFAULT 0 NOT NULL
- , is_create_page INT DEFAULT 0 NOT NULL
- , is_delete INT DEFAULT 0 NOT NULL
- , is_release INT NOT NULL
- , is_publish INT DEFAULT 0 NOT NULL
- , is_grant INT DEFAULT 0 NOT NULL
- , is_transmit CHAR(10) NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_acl_01 FOREIGN KEY (groupid)
- REFERENCES or_group (id)
- , CONSTRAINT fk_acl_02 FOREIGN KEY (userid)
- REFERENCES or_user (id)
- , CONSTRAINT fk_acl_03 FOREIGN KEY (objectid)
- REFERENCES or_object (id)
- , CONSTRAINT fk_acl_04 FOREIGN KEY (languageid)
- REFERENCES or_language (id)
-);
-CREATE INDEX idx_acl_01 ON or_acl (userid);
-CREATE INDEX idx_acl_02 ON or_acl (groupid);
-CREATE INDEX idx_acl_03 ON or_acl (languageid);
-CREATE INDEX idx_acl_04 ON or_acl (objectid);
-CREATE INDEX idx_acl_05 ON or_acl (is_transmit);
-
-
--- Insert 1 Admin-User
-
-INSERT INTO or_user
- (id,name,password,ldap_dn,fullname,tel,mail,descr,style,is_admin)
- VALUES( 1,'admin','admin',' ','Administrator',' ',' ','Admin user','default',1 );
-
-
+-- DDL-Script for oracle
+
+-- Table project
+CREATE TABLE or_project(
+ "ID" NUMBER NOT NULL
+ ,"NAME" VARCHAR(128) NOT NULL
+ ,"TARGET_DIR" VARCHAR(255) NOT NULL
+ ,"FTP_URL" VARCHAR(255) NOT NULL
+ ,"FTP_PASSIVE" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"CMD_AFTER_PUBLISH" VARCHAR(255) NOT NULL
+ ,"CONTENT_NEGOTIATION" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"CUT_INDEX" NUMBER(1) DEFAULT 0 NOT NULL
+ ,PRIMARY KEY (id)
+);
+CREATE UNIQUE INDEX or_uidx_project_name
+ ON or_project (name);
+
+-- Table user
+CREATE TABLE or_user(
+ "ID" NUMBER NOT NULL
+ ,"NAME" VARCHAR(128) NOT NULL
+ ,"PASSWORD" VARCHAR(50) NOT NULL
+ ,"LDAP_DN" VARCHAR(255) NOT NULL
+ ,"FULLNAME" VARCHAR(128) NOT NULL
+ ,"TEL" VARCHAR(128) NOT NULL
+ ,"MAIL" VARCHAR(255) NOT NULL
+ ,"DESCR" VARCHAR(255) NOT NULL
+ ,"STYLE" VARCHAR(64) NOT NULL
+ ,"IS_ADMIN" NUMBER(1) DEFAULT 0 NOT NULL
+ ,PRIMARY KEY (id)
+);
+CREATE UNIQUE INDEX or_uidx_user_name
+ ON or_user (name);
+
+-- Table group
+CREATE TABLE or_group(
+ "ID" NUMBER NOT NULL
+ ,"NAME" VARCHAR(100) NOT NULL
+ ,PRIMARY KEY (id)
+);
+CREATE UNIQUE INDEX or_uidx_group_name
+ ON or_group (name);
+
+-- Table object
+CREATE TABLE or_object(
+ "ID" NUMBER NOT NULL
+ ,"PARENTID" NUMBER NULL
+ ,"PROJECTID" NUMBER DEFAULT 0 NOT NULL
+ ,"FILENAME" VARCHAR(255) NOT NULL
+ ,"ORDERID" NUMBER DEFAULT 0 NOT NULL
+ ,"CREATE_DATE" NUMBER DEFAULT 0 NOT NULL
+ ,"CREATE_USERID" NUMBER DEFAULT 0 NULL
+ ,"LASTCHANGE_DATE" NUMBER DEFAULT 0 NOT NULL
+ ,"LASTCHANGE_USERID" NUMBER DEFAULT 0 NULL
+ ,"IS_FOLDER" NUMBER(1) NOT NULL
+ ,"IS_FILE" NUMBER(1) NOT NULL
+ ,"IS_PAGE" NUMBER(1) NOT NULL
+ ,"IS_LINK" NUMBER(1) NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_object_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+ ,CONSTRAINT or_fk_object_lastchange_userid
+ FOREIGN KEY (lastchange_userid) REFERENCES or_user (id)
+ ,CONSTRAINT or_fk_object_create_userid
+ FOREIGN KEY (create_userid) REFERENCES or_user (id)
+);
+CREATE INDEX or_idx_object_parentid
+ ON or_object (parentid);
+CREATE INDEX or_idx_object_projectid
+ ON or_object (projectid);
+CREATE INDEX or_idx_object_is_folder
+ ON or_object (is_folder);
+CREATE INDEX or_idx_object_is_file
+ ON or_object (is_file);
+CREATE INDEX or_idx_object_is_page
+ ON or_object (is_page);
+CREATE INDEX or_idx_object_is_link
+ ON or_object (is_link);
+CREATE INDEX or_idx_object_orderid
+ ON or_object (orderid);
+CREATE INDEX or_idx_object_create_userid
+ ON or_object (create_userid);
+CREATE INDEX or_idx_object_lastchange_userid
+ ON or_object (lastchange_userid);
+CREATE UNIQUE INDEX or_uidx_object_parentid_filename
+ ON or_object (parentid,filename);
+
+-- Table template
+CREATE TABLE or_template(
+ "ID" NUMBER NOT NULL
+ ,"PROJECTID" NUMBER NOT NULL
+ ,"NAME" VARCHAR(50) NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_template_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+);
+CREATE INDEX or_idx_template_projectid
+ ON or_template (projectid);
+CREATE INDEX or_idx_template_name
+ ON or_template (name);
+CREATE UNIQUE INDEX or_uidx_template_projectid_name
+ ON or_template (projectid,name);
+
+-- Table language
+CREATE TABLE or_language(
+ "ID" NUMBER NOT NULL
+ ,"PROJECTID" NUMBER DEFAULT 0 NOT NULL
+ ,"ISOCODE" VARCHAR(10) NOT NULL
+ ,"NAME" VARCHAR(50) NOT NULL
+ ,"IS_DEFAULT" NUMBER(1) DEFAULT 0 NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_language_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+);
+CREATE UNIQUE INDEX or_uidx_language_projectid_isocode
+ ON or_language (projectid,isocode);
+
+-- Table page
+CREATE TABLE or_page(
+ "ID" NUMBER NOT NULL
+ ,"OBJECTID" NUMBER DEFAULT 0 NOT NULL
+ ,"TEMPLATEID" NUMBER DEFAULT 0 NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_page_templateid
+ FOREIGN KEY (templateid) REFERENCES or_template (id)
+ ,CONSTRAINT or_fk_page_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+);
+CREATE UNIQUE INDEX or_uidx_page_objectid
+ ON or_page (objectid);
+CREATE INDEX or_idx_page_templateid
+ ON or_page (templateid);
+
+-- Table projectmodel
+CREATE TABLE or_projectmodel(
+ "ID" NUMBER NOT NULL
+ ,"PROJECTID" NUMBER DEFAULT 0 NOT NULL
+ ,"NAME" VARCHAR(50) NOT NULL
+ ,"EXTENSION" VARCHAR(10) NULL
+ ,"IS_DEFAULT" NUMBER(1) DEFAULT 0 NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_projectmodel_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+);
+CREATE INDEX or_idx_projectmodel_projectid
+ ON or_projectmodel (projectid);
+CREATE UNIQUE INDEX or_uidx_projectmodel_projectid_name
+ ON or_projectmodel (projectid,name);
+
+-- Table element
+CREATE TABLE or_element(
+ "ID" NUMBER NOT NULL
+ ,"TEMPLATEID" NUMBER DEFAULT 0 NOT NULL
+ ,"NAME" VARCHAR(50) NOT NULL
+ ,"DESCR" VARCHAR(255) NOT NULL
+ ,"TYPE" VARCHAR(20) NOT NULL
+ ,"SUBTYPE" VARCHAR(20) NULL
+ ,"WITH_ICON" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"DATEFORMAT" VARCHAR(100) NULL
+ ,"WIKI" NUMBER(1) DEFAULT 0 NULL
+ ,"HTML" NUMBER(1) DEFAULT 0 NULL
+ ,"ALL_LANGUAGES" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"WRITABLE" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"DECIMALS" NUMBER DEFAULT 0 NULL
+ ,"DEC_POINT" VARCHAR(5) NULL
+ ,"THOUSAND_SEP" VARCHAR(1) NULL
+ ,"CODE" CLOB NULL
+ ,"DEFAULT_TEXT" CLOB NULL
+ ,"FOLDEROBJECTID" NUMBER NULL
+ ,"DEFAULT_OBJECTID" NUMBER NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_element_default_objectid
+ FOREIGN KEY (default_objectid) REFERENCES or_object (id)
+ ,CONSTRAINT or_fk_element_folderobjectid
+ FOREIGN KEY (folderobjectid) REFERENCES or_object (id)
+ ,CONSTRAINT or_fk_element_templateid
+ FOREIGN KEY (templateid) REFERENCES or_template (id)
+);
+CREATE INDEX or_idx_element_templateid
+ ON or_element (templateid);
+CREATE INDEX or_idx_element_name
+ ON or_element (name);
+CREATE UNIQUE INDEX or_uidx_element_templateid_name
+ ON or_element (templateid,name);
+
+-- Table file
+CREATE TABLE or_file(
+ "ID" NUMBER NOT NULL
+ ,"OBJECTID" NUMBER DEFAULT 0 NOT NULL
+ ,"EXTENSION" VARCHAR(10) NOT NULL
+ ,"SIZE" NUMBER DEFAULT 0 NOT NULL
+ ,"VALUE" TEXT NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_file_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+);
+CREATE UNIQUE INDEX or_uidx_file_objectid
+ ON or_file (objectid);
+
+-- Table folder
+CREATE TABLE or_folder(
+ "ID" NUMBER NOT NULL
+ ,"OBJECTID" NUMBER DEFAULT 0 NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_folder_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+);
+CREATE UNIQUE INDEX or_uidx_folder_objectid
+ ON or_folder (objectid);
+
+-- Table link
+CREATE TABLE or_link(
+ "ID" NUMBER NOT NULL
+ ,"OBJECTID" NUMBER DEFAULT 0 NOT NULL
+ ,"LINK_OBJECTID" NUMBER NULL
+ ,"URL" VARCHAR(255) NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_link_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ,CONSTRAINT or_fk_link_link_objectid
+ FOREIGN KEY (link_objectid) REFERENCES or_object (id)
+);
+CREATE UNIQUE INDEX or_uidx_link_objectid
+ ON or_link (objectid);
+CREATE INDEX or_idx_link_link_objectid
+ ON or_link (link_objectid);
+
+-- Table name
+CREATE TABLE or_name(
+ "ID" NUMBER NOT NULL
+ ,"OBJECTID" NUMBER DEFAULT 0 NOT NULL
+ ,"NAME" VARCHAR(255) NOT NULL
+ ,"DESCR" VARCHAR(255) NOT NULL
+ ,"LANGUAGEID" NUMBER DEFAULT 0 NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_name_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ,CONSTRAINT or_fk_name_languageid
+ FOREIGN KEY (languageid) REFERENCES or_language (id)
+);
+CREATE INDEX or_idx_name_objectid
+ ON or_name (objectid);
+CREATE INDEX or_idx_name_languageid
+ ON or_name (languageid);
+CREATE UNIQUE INDEX or_uidx_name_objectid_languageid
+ ON or_name (objectid,languageid);
+
+-- Table templatemodel
+CREATE TABLE or_templatemodel(
+ "ID" NUMBER NOT NULL
+ ,"TEMPLATEID" NUMBER DEFAULT 0 NOT NULL
+ ,"PROJECTMODELID" NUMBER DEFAULT 0 NOT NULL
+ ,"EXTENSION" VARCHAR(10) NULL
+ ,"TEXT" CLOB NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_templatemodel_templateid
+ FOREIGN KEY (templateid) REFERENCES or_template (id)
+ ,CONSTRAINT or_fk_templatemodel_projectmodelid
+ FOREIGN KEY (projectmodelid) REFERENCES or_projectmodel (id)
+);
+CREATE INDEX or_idx_templatemodel_templateid
+ ON or_templatemodel (templateid);
+CREATE UNIQUE INDEX or_uidx_templatemodel_templateid_extension
+ ON or_templatemodel (templateid,extension);
+CREATE UNIQUE INDEX or_uidx_templatemodel_templateid_projectmodelid
+ ON or_templatemodel (templateid,projectmodelid);
+
+-- Table usergroup
+CREATE TABLE or_usergroup(
+ "ID" NUMBER NOT NULL
+ ,"USERID" NUMBER NOT NULL
+ ,"GROUPID" NUMBER NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_usergroup_groupid
+ FOREIGN KEY (groupid) REFERENCES or_group (id)
+ ,CONSTRAINT or_fk_usergroup_userid
+ FOREIGN KEY (userid) REFERENCES or_user (id)
+);
+CREATE INDEX or_idx_usergroup_groupid
+ ON or_usergroup (groupid);
+CREATE INDEX or_idx_usergroup_userid
+ ON or_usergroup (userid);
+CREATE UNIQUE INDEX or_uidx_usergroup_userid_groupid
+ ON or_usergroup (userid,groupid);
+
+-- Table value
+CREATE TABLE or_value(
+ "ID" NUMBER NOT NULL
+ ,"PAGEID" NUMBER DEFAULT 0 NOT NULL
+ ,"LANGUAGEID" NUMBER NOT NULL
+ ,"ELEMENTID" NUMBER DEFAULT 0 NOT NULL
+ ,"LINKOBJECTID" NUMBER NULL
+ ,"TEXT" CLOB NULL
+ ,"NUMBER" NUMBER NULL
+ ,"DATE" NUMBER NULL
+ ,"ACTIVE" NUMBER DEFAULT 0 NOT NULL
+ ,"PUBLISH" NUMBER NOT NULL
+ ,"LASTCHANGE_DATE" NUMBER DEFAULT 0 NOT NULL
+ ,"LASTCHANGE_USERID" NUMBER NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_value_pageid
+ FOREIGN KEY (pageid) REFERENCES or_page (id)
+ ,CONSTRAINT or_fk_value_elementid
+ FOREIGN KEY (elementid) REFERENCES or_element (id)
+ ,CONSTRAINT or_fk_value_languageid
+ FOREIGN KEY (languageid) REFERENCES or_language (id)
+ ,CONSTRAINT or_fk_value_lastchange_userid
+ FOREIGN KEY (lastchange_userid) REFERENCES or_user (id)
+ ,CONSTRAINT or_fk_value_linkobjectid
+ FOREIGN KEY (linkobjectid) REFERENCES or_object (id)
+);
+CREATE INDEX or_idx_value_pageid
+ ON or_value (pageid);
+CREATE INDEX or_idx_value_languageid
+ ON or_value (languageid);
+CREATE INDEX or_idx_value_elementid
+ ON or_value (elementid);
+CREATE INDEX or_idx_value_active
+ ON or_value (active);
+CREATE INDEX or_idx_value_lastchange_date
+ ON or_value (lastchange_date);
+CREATE INDEX or_idx_value_publish
+ ON or_value (publish);
+
+-- Table acl
+CREATE TABLE or_acl(
+ "ID" NUMBER NOT NULL
+ ,"USERID" NUMBER NULL
+ ,"GROUPID" NUMBER NULL
+ ,"OBJECTID" NUMBER NOT NULL
+ ,"LANGUAGEID" NUMBER DEFAULT 0 NULL
+ ,"IS_WRITE" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"IS_PROP" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"IS_CREATE_FOLDER" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"IS_CREATE_FILE" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"IS_CREATE_LINK" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"IS_CREATE_PAGE" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"IS_DELETE" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"IS_RELEASE" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"IS_PUBLISH" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"IS_GRANT" NUMBER(1) DEFAULT 0 NOT NULL
+ ,"IS_TRANSMIT" NUMBER(1) DEFAULT 0 NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_acl_groupid
+ FOREIGN KEY (groupid) REFERENCES or_group (id)
+ ,CONSTRAINT or_fk_acl_userid
+ FOREIGN KEY (userid) REFERENCES or_user (id)
+ ,CONSTRAINT or_fk_acl_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ,CONSTRAINT or_fk_acl_languageid
+ FOREIGN KEY (languageid) REFERENCES or_language (id)
+);
+CREATE INDEX or_idx_acl_userid
+ ON or_acl (userid);
+CREATE INDEX or_idx_acl_groupid
+ ON or_acl (groupid);
+CREATE INDEX or_idx_acl_languageid
+ ON or_acl (languageid);
+CREATE INDEX or_idx_acl_objectid
+ ON or_acl (objectid);
+CREATE INDEX or_idx_acl_is_transmit
+ ON or_acl (is_transmit);
+INSERT INTO or_user (id,name,password,ldap_dn,fullname,tel,mail,descr,style,is_admin) VALUES(1,'admin','admin','','Administrator','','','Admin user','default',1)
diff --git a/doc/database/sql/postgresql/create.sql b/doc/database/sql/postgresql/create.sql
@@ -1,312 +1,393 @@
--- OpenRat Content Management System
--- SQL-Dump
---
--- (c) Jan Dankert
--- Licensed under the GNU General Public Licence
+-- DDL-Script for postgresql
-
-CREATE TABLE or_project (
- id INT NOT NULL
- , name VARCHAR(128) NOT NULL
- , target_dir VARCHAR(255) NOT NULL
- , ftp_url VARCHAR(255) NOT NULL
- , ftp_passive CHAR(1) DEFAULT '0' NOT NULL
- , cmd_after_publish VARCHAR(255) NOT NULL
- , content_negotiation CHAR(1) DEFAULT '0' NOT NULL
- , cut_index CHAR(1) DEFAULT '0' NOT NULL
- , PRIMARY KEY (id)
+-- Table project
+CREATE TABLE or_project(
+ id INTEGER NOT NULL
+ ,name VARCHAR(128) NOT NULL
+ ,target_dir VARCHAR(255) NOT NULL
+ ,ftp_url VARCHAR(255) NOT NULL
+ ,ftp_passive INTEGER(1) NOT NULL DEFAULT 0
+ ,cmd_after_publish VARCHAR(255) NOT NULL
+ ,content_negotiation INTEGER(1) NOT NULL DEFAULT 0
+ ,cut_index INTEGER(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
);
-CREATE UNIQUE INDEX idx_project_uk ON or_project (name);
+CREATE UNIQUE INDEX or_uidx_project_name
+ ON or_project (name);
-CREATE TABLE or_user (
- id INT NOT NULL
- , name VARCHAR(128) NOT NULL
- , password VARCHAR(50) NOT NULL
- , ldap_dn VARCHAR(255) NOT NULL
- , fullname VARCHAR(128) NOT NULL
- , tel VARCHAR(128) NOT NULL
- , mail VARCHAR(255) NOT NULL
- , descr VARCHAR(255) NOT NULL
- , style VARCHAR(64) NOT NULL
- , is_admin INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
+-- Table user
+CREATE TABLE or_user(
+ id INTEGER NOT NULL
+ ,name VARCHAR(128) NOT NULL
+ ,password VARCHAR(50) NOT NULL
+ ,ldap_dn VARCHAR(255) NOT NULL
+ ,fullname VARCHAR(128) NOT NULL
+ ,tel VARCHAR(128) NOT NULL
+ ,mail VARCHAR(255) NOT NULL
+ ,descr VARCHAR(255) NOT NULL
+ ,style VARCHAR(64) NOT NULL
+ ,is_admin INTEGER(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
);
-CREATE UNIQUE INDEX idx_user_uk ON or_user (name);
+CREATE UNIQUE INDEX or_uidx_user_name
+ ON or_user (name);
-CREATE TABLE or_group (
- id INT NOT NULL
- , name VARCHAR(100) NOT NULL
- , PRIMARY KEY (id)
+-- Table group
+CREATE TABLE or_group(
+ id INTEGER NOT NULL
+ ,name VARCHAR(100) NOT NULL
+ ,PRIMARY KEY (id)
);
-CREATE UNIQUE INDEX idx_group_uk ON or_group (name);
+CREATE UNIQUE INDEX or_uidx_group_name
+ ON or_group (name);
-CREATE TABLE or_object (
- id INT NOT NULL
- , parentid INT
- , projectid INT DEFAULT 0 NOT NULL
- , filename VARCHAR(255) NOT NULL
- , orderid INT DEFAULT 0 NOT NULL
- , create_date INT DEFAULT 0 NOT NULL
- , create_userid INT DEFAULT 0
- , lastchange_date INT DEFAULT 0 NOT NULL
- , lastchange_userid INT DEFAULT 0
- , is_folder INT DEFAULT 0 NOT NULL
- , is_file INT DEFAULT 0 NOT NULL
- , is_page INT DEFAULT 0 NOT NULL
- , is_link INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_object_01 FOREIGN KEY (projectid)
- REFERENCES or_project (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_object_02 FOREIGN KEY (lastchange_userid)
- REFERENCES or_user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_object_03 FOREIGN KEY (create_userid)
- REFERENCES or_user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table object
+CREATE TABLE or_object(
+ id INTEGER NOT NULL
+ ,parentid INTEGER NULL
+ ,projectid INTEGER NOT NULL DEFAULT 0
+ ,filename VARCHAR(255) NOT NULL
+ ,orderid INTEGER NOT NULL DEFAULT 0
+ ,create_date INTEGER NOT NULL DEFAULT 0
+ ,create_userid INTEGER NULL DEFAULT 0
+ ,lastchange_date INTEGER NOT NULL DEFAULT 0
+ ,lastchange_userid INTEGER NULL DEFAULT 0
+ ,is_folder INTEGER(1) NOT NULL
+ ,is_file INTEGER(1) NOT NULL
+ ,is_page INTEGER(1) NOT NULL
+ ,is_link INTEGER(1) NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_object_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_object_lastchange_userid
+ FOREIGN KEY (lastchange_userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_object_create_userid
+ FOREIGN KEY (create_userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE INDEX idx_object_01 ON or_object (parentid);
-CREATE INDEX idx_object_02 ON or_object (projectid);
-CREATE INDEX idx_object_03 ON or_object (is_folder);
-CREATE INDEX idx_object_04 ON or_object (is_file);
-CREATE INDEX idx_object_05 ON or_object (is_page);
-CREATE INDEX idx_object_06 ON or_object (is_link);
-CREATE INDEX idx_object_07 ON or_object (orderid);
-CREATE INDEX idx_object_08 ON or_object (create_userid);
-CREATE INDEX idx_object_09 ON or_object (lastchange_userid);
-CREATE UNIQUE INDEX idx_object_uk ON or_object (parentid, filename);
+CREATE INDEX or_idx_object_parentid
+ ON or_object (parentid);
+CREATE INDEX or_idx_object_projectid
+ ON or_object (projectid);
+CREATE INDEX or_idx_object_is_folder
+ ON or_object (is_folder);
+CREATE INDEX or_idx_object_is_file
+ ON or_object (is_file);
+CREATE INDEX or_idx_object_is_page
+ ON or_object (is_page);
+CREATE INDEX or_idx_object_is_link
+ ON or_object (is_link);
+CREATE INDEX or_idx_object_orderid
+ ON or_object (orderid);
+CREATE INDEX or_idx_object_create_userid
+ ON or_object (create_userid);
+CREATE INDEX or_idx_object_lastchange_userid
+ ON or_object (lastchange_userid);
+CREATE UNIQUE INDEX or_uidx_object_parentid_filename
+ ON or_object (parentid,filename);
-CREATE TABLE or_template (
- id INT NOT NULL
- , projectid INT NOT NULL
- , name VARCHAR(50) NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_template_01 FOREIGN KEY (projectid)
- REFERENCES or_project (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table template
+CREATE TABLE or_template(
+ id INTEGER NOT NULL
+ ,projectid INTEGER NOT NULL
+ ,name VARCHAR(50) NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_template_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE INDEX idx_template_01 ON or_template (projectid);
-CREATE INDEX idx_template_02 ON or_template (name);
-CREATE UNIQUE INDEX idx_template_uk ON or_template (projectid, name);
+CREATE INDEX or_idx_template_projectid
+ ON or_template (projectid);
+CREATE INDEX or_idx_template_name
+ ON or_template (name);
+CREATE UNIQUE INDEX or_uidx_template_projectid_name
+ ON or_template (projectid,name);
-CREATE TABLE or_language (
- id INT NOT NULL
- , projectid INT DEFAULT 0 NOT NULL
- , isocode VARCHAR(10) NOT NULL
- , name VARCHAR(50) NOT NULL
- , is_default INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_language_01 FOREIGN KEY (projectid)
- REFERENCES or_project (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table language
+CREATE TABLE or_language(
+ id INTEGER NOT NULL
+ ,projectid INTEGER NOT NULL DEFAULT 0
+ ,isocode VARCHAR(10) NOT NULL
+ ,name VARCHAR(50) NOT NULL
+ ,is_default INTEGER(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_language_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE UNIQUE INDEX idx_language_uk ON or_language (projectid, isocode);
+CREATE UNIQUE INDEX or_uidx_language_projectid_isocode
+ ON or_language (projectid,isocode);
-CREATE TABLE or_page (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , templateid INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_page_01 FOREIGN KEY (templateid)
- REFERENCES or_template (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_page_02 FOREIGN KEY (objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table page
+CREATE TABLE or_page(
+ id INTEGER NOT NULL
+ ,objectid INTEGER NOT NULL DEFAULT 0
+ ,templateid INTEGER NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_page_templateid
+ FOREIGN KEY (templateid) REFERENCES or_template (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_page_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE UNIQUE INDEX idx_page_uk ON or_page (objectid);
-CREATE INDEX idx_page_01 ON or_page (templateid);
+CREATE UNIQUE INDEX or_uidx_page_objectid
+ ON or_page (objectid);
+CREATE INDEX or_idx_page_templateid
+ ON or_page (templateid);
-CREATE TABLE or_projectmodel (
- id INT NOT NULL
- , projectid INT DEFAULT 0 NOT NULL
- , name VARCHAR(50) NOT NULL
- , extension VARCHAR(10)
- , is_default CHAR(10) DEFAULT '0' NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_projectmodel_01 FOREIGN KEY (projectid)
- REFERENCES or_project (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table projectmodel
+CREATE TABLE or_projectmodel(
+ id INTEGER NOT NULL
+ ,projectid INTEGER NOT NULL DEFAULT 0
+ ,name VARCHAR(50) NOT NULL
+ ,extension VARCHAR(10) NULL
+ ,is_default INTEGER(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_projectmodel_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE INDEX idx_projectmodel_01 ON or_projectmodel (projectid);
-CREATE UNIQUE INDEX idx_projectmodel_uk ON or_projectmodel (projectid, name);
+CREATE INDEX or_idx_projectmodel_projectid
+ ON or_projectmodel (projectid);
+CREATE UNIQUE INDEX or_uidx_projectmodel_projectid_name
+ ON or_projectmodel (projectid,name);
-CREATE TABLE or_element (
- id INT NOT NULL
- , templateid INT DEFAULT 0 NOT NULL
- , name VARCHAR(50) NOT NULL
- , descr VARCHAR(255) NOT NULL
- , type VARCHAR(20) NOT NULL
- , subtype VARCHAR(20)
- , with_icon CHAR(1) DEFAULT '0' NOT NULL
- , dateformat VARCHAR(100)
- , wiki CHAR(1) DEFAULT '0'
- , html CHAR(1) DEFAULT '0'
- , all_languages CHAR(1) DEFAULT '0' NOT NULL
- , writable CHAR(1) DEFAULT '0' NOT NULL
- , decimals INT DEFAULT 0
- , dec_point VARCHAR(5)
- , thousand_sep CHAR(1)
- , code TEXT
- , default_text TEXT
- , folderobjectid INT
- , default_objectid INT
- , PRIMARY KEY (id)
- , CONSTRAINT fk_element_01 FOREIGN KEY (default_objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_element_02 FOREIGN KEY (folderobjectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_element_03 FOREIGN KEY (templateid)
- REFERENCES or_template (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table element
+CREATE TABLE or_element(
+ id INTEGER NOT NULL
+ ,templateid INTEGER NOT NULL DEFAULT 0
+ ,name VARCHAR(50) NOT NULL
+ ,descr VARCHAR(255) NOT NULL
+ ,type VARCHAR(20) NOT NULL
+ ,subtype VARCHAR(20) NULL
+ ,with_icon INTEGER(1) NOT NULL DEFAULT 0
+ ,dateformat VARCHAR(100) NULL
+ ,wiki INTEGER(1) NULL DEFAULT 0
+ ,html INTEGER(1) NULL DEFAULT 0
+ ,all_languages INTEGER(1) NOT NULL DEFAULT 0
+ ,writable INTEGER(1) NOT NULL DEFAULT 0
+ ,decimals INTEGER NULL DEFAULT 0
+ ,dec_point VARCHAR(5) NULL
+ ,thousand_sep VARCHAR(1) NULL
+ ,code TEXT NULL
+ ,default_text TEXT NULL
+ ,folderobjectid INTEGER NULL
+ ,default_objectid INTEGER NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_element_default_objectid
+ FOREIGN KEY (default_objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_element_folderobjectid
+ FOREIGN KEY (folderobjectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_element_templateid
+ FOREIGN KEY (templateid) REFERENCES or_template (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE INDEX idx_element_01 ON or_element (templateid);
-CREATE INDEX idx_element_02 ON or_element (name);
-CREATE UNIQUE INDEX idx_element_uk ON or_element (templateid, name);
+CREATE INDEX or_idx_element_templateid
+ ON or_element (templateid);
+CREATE INDEX or_idx_element_name
+ ON or_element (name);
+CREATE UNIQUE INDEX or_uidx_element_templateid_name
+ ON or_element (templateid,name);
-CREATE TABLE or_file (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , extension VARCHAR(10) NOT NULL
- , size INT DEFAULT 0 NOT NULL
- , value TEXT NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_file_01 FOREIGN KEY (objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table file
+CREATE TABLE or_file(
+ id INTEGER NOT NULL
+ ,objectid INTEGER NOT NULL DEFAULT 0
+ ,extension VARCHAR(10) NOT NULL
+ ,size INTEGER NOT NULL DEFAULT 0
+ ,value TEXT NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_file_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE UNIQUE INDEX idx_file_01 ON or_file (objectid);
+CREATE UNIQUE INDEX or_uidx_file_objectid
+ ON or_file (objectid);
-CREATE TABLE or_folder (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_folder_01 FOREIGN KEY (objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table folder
+CREATE TABLE or_folder(
+ id INTEGER NOT NULL
+ ,objectid INTEGER NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_folder_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE UNIQUE INDEX idx_folder_01 ON or_folder (objectid);
+CREATE UNIQUE INDEX or_uidx_folder_objectid
+ ON or_folder (objectid);
-CREATE TABLE or_link (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , link_objectid INT
- , url VARCHAR(255)
- , PRIMARY KEY (id)
- , CONSTRAINT fk_link_01 FOREIGN KEY (objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_link_02 FOREIGN KEY (link_objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table link
+CREATE TABLE or_link(
+ id INTEGER NOT NULL
+ ,objectid INTEGER NOT NULL DEFAULT 0
+ ,link_objectid INTEGER NULL
+ ,url VARCHAR(255) NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_link_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_link_link_objectid
+ FOREIGN KEY (link_objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE UNIQUE INDEX idx_link_01 ON or_link (objectid);
-CREATE INDEX idx_link_02 ON or_link (link_objectid);
+CREATE UNIQUE INDEX or_uidx_link_objectid
+ ON or_link (objectid);
+CREATE INDEX or_idx_link_link_objectid
+ ON or_link (link_objectid);
-CREATE TABLE or_name (
- id INT NOT NULL
- , objectid INT DEFAULT 0 NOT NULL
- , name VARCHAR(255) NOT NULL
- , descr VARCHAR(255) NOT NULL
- , languageid INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_name_01 FOREIGN KEY (objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_name_02 FOREIGN KEY (languageid)
- REFERENCES or_language (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table name
+CREATE TABLE or_name(
+ id INTEGER NOT NULL
+ ,objectid INTEGER NOT NULL DEFAULT 0
+ ,name VARCHAR(255) NOT NULL
+ ,descr VARCHAR(255) NOT NULL
+ ,languageid INTEGER NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_name_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_name_languageid
+ FOREIGN KEY (languageid) REFERENCES or_language (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE INDEX idx_name_01 ON or_name (objectid);
-CREATE INDEX idx_name_02 ON or_name (languageid);
-CREATE UNIQUE INDEX idx_name_uk ON or_name (objectid, languageid);
+CREATE INDEX or_idx_name_objectid
+ ON or_name (objectid);
+CREATE INDEX or_idx_name_languageid
+ ON or_name (languageid);
+CREATE UNIQUE INDEX or_uidx_name_objectid_languageid
+ ON or_name (objectid,languageid);
-CREATE TABLE or_templatemodel (
- id INT NOT NULL
- , templateid INT DEFAULT 0 NOT NULL
- , projectmodelid INT DEFAULT 0 NOT NULL
- , extension VARCHAR(10)
- , text TEXT NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT UQ_or_templatemodel_1 UNIQUE (templateid, extension)
- , CONSTRAINT fk_templatemodel_01 FOREIGN KEY (templateid)
- REFERENCES or_template (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_templatemodel_02 FOREIGN KEY (projectmodelid)
- REFERENCES or_projectmodel (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table templatemodel
+CREATE TABLE or_templatemodel(
+ id INTEGER NOT NULL
+ ,templateid INTEGER NOT NULL DEFAULT 0
+ ,projectmodelid INTEGER NOT NULL DEFAULT 0
+ ,extension VARCHAR(10) NULL
+ ,text TEXT NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_templatemodel_templateid
+ FOREIGN KEY (templateid) REFERENCES or_template (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_templatemodel_projectmodelid
+ FOREIGN KEY (projectmodelid) REFERENCES or_projectmodel (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE INDEX idx_templatemodel_01 ON or_templatemodel (templateid);
-CREATE UNIQUE INDEX idx_templatemodel_uk_01 ON or_templatemodel (templateid, projectmodelid);
+CREATE INDEX or_idx_templatemodel_templateid
+ ON or_templatemodel (templateid);
+CREATE UNIQUE INDEX or_uidx_templatemodel_templateid_extension
+ ON or_templatemodel (templateid,extension);
+CREATE UNIQUE INDEX or_uidx_templatemodel_templateid_projectmodelid
+ ON or_templatemodel (templateid,projectmodelid);
-CREATE TABLE or_usergroup (
- id INT NOT NULL
- , userid INT DEFAULT 0 NOT NULL
- , groupid INT DEFAULT 0 NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_usergroup_01 FOREIGN KEY (groupid)
- REFERENCES or_group (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_usergroup_02 FOREIGN KEY (userid)
- REFERENCES or_user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table usergroup
+CREATE TABLE or_usergroup(
+ id INTEGER NOT NULL
+ ,userid INTEGER NOT NULL
+ ,groupid INTEGER NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_usergroup_groupid
+ FOREIGN KEY (groupid) REFERENCES or_group (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_usergroup_userid
+ FOREIGN KEY (userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE INDEX idx_usergroup_01 ON or_usergroup (groupid);
-CREATE INDEX idx_usergroup_02 ON or_usergroup (userid);
-CREATE UNIQUE INDEX idx_usergroup_uk ON or_usergroup (userid, groupid);
+CREATE INDEX or_idx_usergroup_groupid
+ ON or_usergroup (groupid);
+CREATE INDEX or_idx_usergroup_userid
+ ON or_usergroup (userid);
+CREATE UNIQUE INDEX or_uidx_usergroup_userid_groupid
+ ON or_usergroup (userid,groupid);
-CREATE TABLE or_value (
- id INT NOT NULL
- , pageid INT DEFAULT 0 NOT NULL
- , languageid INT NOT NULL
- , elementid INT DEFAULT 0 NOT NULL
- , linkobjectid INT
- , text TEXT
- , number INT
- , date INT
- , active INT DEFAULT 0 NOT NULL
- , publish INT NOT NULL
- , lastchange_date INT DEFAULT 0 NOT NULL
- , lastchange_userid INT DEFAULT 0
- , PRIMARY KEY (id)
- , CONSTRAINT fk_value_01 FOREIGN KEY (pageid)
- REFERENCES or_page (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_value_02 FOREIGN KEY (elementid)
- REFERENCES or_element (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_value_03 FOREIGN KEY (languageid)
- REFERENCES or_language (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_value_04 FOREIGN KEY (lastchange_userid)
- REFERENCES or_user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_value_05 FOREIGN KEY (linkobjectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table value
+CREATE TABLE or_value(
+ id INTEGER NOT NULL
+ ,pageid INTEGER NOT NULL DEFAULT 0
+ ,languageid INTEGER NOT NULL
+ ,elementid INTEGER NOT NULL DEFAULT 0
+ ,linkobjectid INTEGER NULL
+ ,text TEXT NULL
+ ,number INTEGER NULL
+ ,date INTEGER NULL
+ ,active INTEGER NOT NULL DEFAULT 0
+ ,publish INTEGER NOT NULL
+ ,lastchange_date INTEGER NOT NULL DEFAULT 0
+ ,lastchange_userid INTEGER NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_value_pageid
+ FOREIGN KEY (pageid) REFERENCES or_page (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_value_elementid
+ FOREIGN KEY (elementid) REFERENCES or_element (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_value_languageid
+ FOREIGN KEY (languageid) REFERENCES or_language (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_value_lastchange_userid
+ FOREIGN KEY (lastchange_userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_value_linkobjectid
+ FOREIGN KEY (linkobjectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE INDEX idx_value_01 ON or_value (pageid);
-CREATE INDEX idx_value_02 ON or_value (languageid);
-CREATE INDEX idx_value_03 ON or_value (elementid);
-CREATE INDEX idx_value_04 ON or_value (active);
-CREATE INDEX idx_value_05 ON or_value (lastchange_date);
-CREATE INDEX idx_value_06 ON or_value (elementid);
-CREATE INDEX idx_value_07 ON or_value (publish);
+CREATE INDEX or_idx_value_pageid
+ ON or_value (pageid);
+CREATE INDEX or_idx_value_languageid
+ ON or_value (languageid);
+CREATE INDEX or_idx_value_elementid
+ ON or_value (elementid);
+CREATE INDEX or_idx_value_active
+ ON or_value (active);
+CREATE INDEX or_idx_value_lastchange_date
+ ON or_value (lastchange_date);
+CREATE INDEX or_idx_value_publish
+ ON or_value (publish);
-CREATE TABLE or_acl (
- id INT NOT NULL
- , userid INT
- , groupid INT
- , objectid INT DEFAULT 0 NOT NULL
- , languageid INT DEFAULT 0
- , is_write INT DEFAULT 0 NOT NULL
- , is_prop INT DEFAULT 0 NOT NULL
- , is_create_folder INT DEFAULT 0 NOT NULL
- , is_create_file INT DEFAULT 0 NOT NULL
- , is_create_link INT DEFAULT 0 NOT NULL
- , is_create_page INT DEFAULT 0 NOT NULL
- , is_delete INT DEFAULT 0 NOT NULL
- , is_release INT NOT NULL
- , is_publish INT DEFAULT 0 NOT NULL
- , is_grant INT DEFAULT 0 NOT NULL
- , is_transmit CHAR(10) NOT NULL
- , PRIMARY KEY (id)
- , CONSTRAINT fk_acl_01 FOREIGN KEY (groupid)
- REFERENCES or_group (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_acl_02 FOREIGN KEY (userid)
- REFERENCES or_user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_acl_03 FOREIGN KEY (objectid)
- REFERENCES or_object (id) ON DELETE RESTRICT ON UPDATE RESTRICT
- , CONSTRAINT fk_acl_04 FOREIGN KEY (languageid)
- REFERENCES or_language (id) ON DELETE RESTRICT ON UPDATE RESTRICT
+-- Table acl
+CREATE TABLE or_acl(
+ id INTEGER NOT NULL
+ ,userid INTEGER NULL
+ ,groupid INTEGER NULL
+ ,objectid INTEGER NOT NULL
+ ,languageid INTEGER NULL DEFAULT 0
+ ,is_write INTEGER(1) NOT NULL DEFAULT 0
+ ,is_prop INTEGER(1) NOT NULL DEFAULT 0
+ ,is_create_folder INTEGER(1) NOT NULL DEFAULT 0
+ ,is_create_file INTEGER(1) NOT NULL DEFAULT 0
+ ,is_create_link INTEGER(1) NOT NULL DEFAULT 0
+ ,is_create_page INTEGER(1) NOT NULL DEFAULT 0
+ ,is_delete INTEGER(1) NOT NULL DEFAULT 0
+ ,is_release INTEGER(1) NOT NULL DEFAULT 0
+ ,is_publish INTEGER(1) NOT NULL DEFAULT 0
+ ,is_grant INTEGER(1) NOT NULL DEFAULT 0
+ ,is_transmit INTEGER(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_acl_groupid
+ FOREIGN KEY (groupid) REFERENCES or_group (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_acl_userid
+ FOREIGN KEY (userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_acl_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_acl_languageid
+ FOREIGN KEY (languageid) REFERENCES or_language (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE INDEX idx_acl_01 ON or_acl (userid);
-CREATE INDEX idx_acl_02 ON or_acl (groupid);
-CREATE INDEX idx_acl_03 ON or_acl (languageid);
-CREATE INDEX idx_acl_04 ON or_acl (objectid);
-CREATE INDEX idx_acl_05 ON or_acl (is_transmit);
-
-
--- Insert 1 Admin-User
-
-INSERT INTO or_user
- (id,name,password,ldap_dn,fullname,tel,mail,descr,style,is_admin)
- VALUES( 1,'admin','admin','','Administrator','','','The Admin User','default',1 );
-
-
-\ No newline at end of file
+CREATE INDEX or_idx_acl_userid
+ ON or_acl (userid);
+CREATE INDEX or_idx_acl_groupid
+ ON or_acl (groupid);
+CREATE INDEX or_idx_acl_languageid
+ ON or_acl (languageid);
+CREATE INDEX or_idx_acl_objectid
+ ON or_acl (objectid);
+CREATE INDEX or_idx_acl_is_transmit
+ ON or_acl (is_transmit);
+INSERT INTO or_user (id,name,password,ldap_dn,fullname,tel,mail,descr,style,is_admin) VALUES(1,'admin','admin','','Administrator','','','Admin user','default',1)
diff --git a/doc/database/sql/sqlite/create.sql b/doc/database/sql/sqlite/create.sql
@@ -0,0 +1,393 @@
+-- DDL-Script for sqlite
+
+-- Table project
+CREATE TABLE or_project(
+ id INTEGER NOT NULL
+ ,name VARCHAR(128) NOT NULL
+ ,target_dir VARCHAR(255) NOT NULL
+ ,ftp_url VARCHAR(255) NOT NULL
+ ,ftp_passive INTEGER(1) NOT NULL DEFAULT 0
+ ,cmd_after_publish VARCHAR(255) NOT NULL
+ ,content_negotiation INTEGER(1) NOT NULL DEFAULT 0
+ ,cut_index INTEGER(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+);
+CREATE UNIQUE INDEX or_uidx_project_name
+ ON or_project (name);
+
+-- Table user
+CREATE TABLE or_user(
+ id INTEGER NOT NULL
+ ,name VARCHAR(128) NOT NULL
+ ,password VARCHAR(50) NOT NULL
+ ,ldap_dn VARCHAR(255) NOT NULL
+ ,fullname VARCHAR(128) NOT NULL
+ ,tel VARCHAR(128) NOT NULL
+ ,mail VARCHAR(255) NOT NULL
+ ,descr VARCHAR(255) NOT NULL
+ ,style VARCHAR(64) NOT NULL
+ ,is_admin INTEGER(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+);
+CREATE UNIQUE INDEX or_uidx_user_name
+ ON or_user (name);
+
+-- Table group
+CREATE TABLE or_group(
+ id INTEGER NOT NULL
+ ,name VARCHAR(100) NOT NULL
+ ,PRIMARY KEY (id)
+);
+CREATE UNIQUE INDEX or_uidx_group_name
+ ON or_group (name);
+
+-- Table object
+CREATE TABLE or_object(
+ id INTEGER NOT NULL
+ ,parentid INTEGER NULL
+ ,projectid INTEGER NOT NULL DEFAULT 0
+ ,filename VARCHAR(255) NOT NULL
+ ,orderid INTEGER NOT NULL DEFAULT 0
+ ,create_date INTEGER NOT NULL DEFAULT 0
+ ,create_userid INTEGER NULL DEFAULT 0
+ ,lastchange_date INTEGER NOT NULL DEFAULT 0
+ ,lastchange_userid INTEGER NULL DEFAULT 0
+ ,is_folder INTEGER(1) NOT NULL
+ ,is_file INTEGER(1) NOT NULL
+ ,is_page INTEGER(1) NOT NULL
+ ,is_link INTEGER(1) NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_object_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_object_lastchange_userid
+ FOREIGN KEY (lastchange_userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_object_create_userid
+ FOREIGN KEY (create_userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE INDEX or_idx_object_parentid
+ ON or_object (parentid);
+CREATE INDEX or_idx_object_projectid
+ ON or_object (projectid);
+CREATE INDEX or_idx_object_is_folder
+ ON or_object (is_folder);
+CREATE INDEX or_idx_object_is_file
+ ON or_object (is_file);
+CREATE INDEX or_idx_object_is_page
+ ON or_object (is_page);
+CREATE INDEX or_idx_object_is_link
+ ON or_object (is_link);
+CREATE INDEX or_idx_object_orderid
+ ON or_object (orderid);
+CREATE INDEX or_idx_object_create_userid
+ ON or_object (create_userid);
+CREATE INDEX or_idx_object_lastchange_userid
+ ON or_object (lastchange_userid);
+CREATE UNIQUE INDEX or_uidx_object_parentid_filename
+ ON or_object (parentid,filename);
+
+-- Table template
+CREATE TABLE or_template(
+ id INTEGER NOT NULL
+ ,projectid INTEGER NOT NULL
+ ,name VARCHAR(50) NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_template_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE INDEX or_idx_template_projectid
+ ON or_template (projectid);
+CREATE INDEX or_idx_template_name
+ ON or_template (name);
+CREATE UNIQUE INDEX or_uidx_template_projectid_name
+ ON or_template (projectid,name);
+
+-- Table language
+CREATE TABLE or_language(
+ id INTEGER NOT NULL
+ ,projectid INTEGER NOT NULL DEFAULT 0
+ ,isocode VARCHAR(10) NOT NULL
+ ,name VARCHAR(50) NOT NULL
+ ,is_default INTEGER(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_language_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE UNIQUE INDEX or_uidx_language_projectid_isocode
+ ON or_language (projectid,isocode);
+
+-- Table page
+CREATE TABLE or_page(
+ id INTEGER NOT NULL
+ ,objectid INTEGER NOT NULL DEFAULT 0
+ ,templateid INTEGER NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_page_templateid
+ FOREIGN KEY (templateid) REFERENCES or_template (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_page_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE UNIQUE INDEX or_uidx_page_objectid
+ ON or_page (objectid);
+CREATE INDEX or_idx_page_templateid
+ ON or_page (templateid);
+
+-- Table projectmodel
+CREATE TABLE or_projectmodel(
+ id INTEGER NOT NULL
+ ,projectid INTEGER NOT NULL DEFAULT 0
+ ,name VARCHAR(50) NOT NULL
+ ,extension VARCHAR(10) NULL
+ ,is_default INTEGER(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_projectmodel_projectid
+ FOREIGN KEY (projectid) REFERENCES or_project (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE INDEX or_idx_projectmodel_projectid
+ ON or_projectmodel (projectid);
+CREATE UNIQUE INDEX or_uidx_projectmodel_projectid_name
+ ON or_projectmodel (projectid,name);
+
+-- Table element
+CREATE TABLE or_element(
+ id INTEGER NOT NULL
+ ,templateid INTEGER NOT NULL DEFAULT 0
+ ,name VARCHAR(50) NOT NULL
+ ,descr VARCHAR(255) NOT NULL
+ ,type VARCHAR(20) NOT NULL
+ ,subtype VARCHAR(20) NULL
+ ,with_icon INTEGER(1) NOT NULL DEFAULT 0
+ ,dateformat VARCHAR(100) NULL
+ ,wiki INTEGER(1) NULL DEFAULT 0
+ ,html INTEGER(1) NULL DEFAULT 0
+ ,all_languages INTEGER(1) NOT NULL DEFAULT 0
+ ,writable INTEGER(1) NOT NULL DEFAULT 0
+ ,decimals INTEGER NULL DEFAULT 0
+ ,dec_point VARCHAR(5) NULL
+ ,thousand_sep VARCHAR(1) NULL
+ ,code TEXT NULL
+ ,default_text TEXT NULL
+ ,folderobjectid INTEGER NULL
+ ,default_objectid INTEGER NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_element_default_objectid
+ FOREIGN KEY (default_objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_element_folderobjectid
+ FOREIGN KEY (folderobjectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_element_templateid
+ FOREIGN KEY (templateid) REFERENCES or_template (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE INDEX or_idx_element_templateid
+ ON or_element (templateid);
+CREATE INDEX or_idx_element_name
+ ON or_element (name);
+CREATE UNIQUE INDEX or_uidx_element_templateid_name
+ ON or_element (templateid,name);
+
+-- Table file
+CREATE TABLE or_file(
+ id INTEGER NOT NULL
+ ,objectid INTEGER NOT NULL DEFAULT 0
+ ,extension VARCHAR(10) NOT NULL
+ ,size INTEGER NOT NULL DEFAULT 0
+ ,value TEXT NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_file_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE UNIQUE INDEX or_uidx_file_objectid
+ ON or_file (objectid);
+
+-- Table folder
+CREATE TABLE or_folder(
+ id INTEGER NOT NULL
+ ,objectid INTEGER NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_folder_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE UNIQUE INDEX or_uidx_folder_objectid
+ ON or_folder (objectid);
+
+-- Table link
+CREATE TABLE or_link(
+ id INTEGER NOT NULL
+ ,objectid INTEGER NOT NULL DEFAULT 0
+ ,link_objectid INTEGER NULL
+ ,url VARCHAR(255) NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_link_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_link_link_objectid
+ FOREIGN KEY (link_objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE UNIQUE INDEX or_uidx_link_objectid
+ ON or_link (objectid);
+CREATE INDEX or_idx_link_link_objectid
+ ON or_link (link_objectid);
+
+-- Table name
+CREATE TABLE or_name(
+ id INTEGER NOT NULL
+ ,objectid INTEGER NOT NULL DEFAULT 0
+ ,name VARCHAR(255) NOT NULL
+ ,descr VARCHAR(255) NOT NULL
+ ,languageid INTEGER NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_name_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_name_languageid
+ FOREIGN KEY (languageid) REFERENCES or_language (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE INDEX or_idx_name_objectid
+ ON or_name (objectid);
+CREATE INDEX or_idx_name_languageid
+ ON or_name (languageid);
+CREATE UNIQUE INDEX or_uidx_name_objectid_languageid
+ ON or_name (objectid,languageid);
+
+-- Table templatemodel
+CREATE TABLE or_templatemodel(
+ id INTEGER NOT NULL
+ ,templateid INTEGER NOT NULL DEFAULT 0
+ ,projectmodelid INTEGER NOT NULL DEFAULT 0
+ ,extension VARCHAR(10) NULL
+ ,text TEXT NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_templatemodel_templateid
+ FOREIGN KEY (templateid) REFERENCES or_template (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_templatemodel_projectmodelid
+ FOREIGN KEY (projectmodelid) REFERENCES or_projectmodel (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE INDEX or_idx_templatemodel_templateid
+ ON or_templatemodel (templateid);
+CREATE UNIQUE INDEX or_uidx_templatemodel_templateid_extension
+ ON or_templatemodel (templateid,extension);
+CREATE UNIQUE INDEX or_uidx_templatemodel_templateid_projectmodelid
+ ON or_templatemodel (templateid,projectmodelid);
+
+-- Table usergroup
+CREATE TABLE or_usergroup(
+ id INTEGER NOT NULL
+ ,userid INTEGER NOT NULL
+ ,groupid INTEGER NOT NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_usergroup_groupid
+ FOREIGN KEY (groupid) REFERENCES or_group (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_usergroup_userid
+ FOREIGN KEY (userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE INDEX or_idx_usergroup_groupid
+ ON or_usergroup (groupid);
+CREATE INDEX or_idx_usergroup_userid
+ ON or_usergroup (userid);
+CREATE UNIQUE INDEX or_uidx_usergroup_userid_groupid
+ ON or_usergroup (userid,groupid);
+
+-- Table value
+CREATE TABLE or_value(
+ id INTEGER NOT NULL
+ ,pageid INTEGER NOT NULL DEFAULT 0
+ ,languageid INTEGER NOT NULL
+ ,elementid INTEGER NOT NULL DEFAULT 0
+ ,linkobjectid INTEGER NULL
+ ,text TEXT NULL
+ ,number INTEGER NULL
+ ,date INTEGER NULL
+ ,active INTEGER NOT NULL DEFAULT 0
+ ,publish INTEGER NOT NULL
+ ,lastchange_date INTEGER NOT NULL DEFAULT 0
+ ,lastchange_userid INTEGER NULL
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_value_pageid
+ FOREIGN KEY (pageid) REFERENCES or_page (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_value_elementid
+ FOREIGN KEY (elementid) REFERENCES or_element (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_value_languageid
+ FOREIGN KEY (languageid) REFERENCES or_language (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_value_lastchange_userid
+ FOREIGN KEY (lastchange_userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_value_linkobjectid
+ FOREIGN KEY (linkobjectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE INDEX or_idx_value_pageid
+ ON or_value (pageid);
+CREATE INDEX or_idx_value_languageid
+ ON or_value (languageid);
+CREATE INDEX or_idx_value_elementid
+ ON or_value (elementid);
+CREATE INDEX or_idx_value_active
+ ON or_value (active);
+CREATE INDEX or_idx_value_lastchange_date
+ ON or_value (lastchange_date);
+CREATE INDEX or_idx_value_publish
+ ON or_value (publish);
+
+-- Table acl
+CREATE TABLE or_acl(
+ id INTEGER NOT NULL
+ ,userid INTEGER NULL
+ ,groupid INTEGER NULL
+ ,objectid INTEGER NOT NULL
+ ,languageid INTEGER NULL DEFAULT 0
+ ,is_write INTEGER(1) NOT NULL DEFAULT 0
+ ,is_prop INTEGER(1) NOT NULL DEFAULT 0
+ ,is_create_folder INTEGER(1) NOT NULL DEFAULT 0
+ ,is_create_file INTEGER(1) NOT NULL DEFAULT 0
+ ,is_create_link INTEGER(1) NOT NULL DEFAULT 0
+ ,is_create_page INTEGER(1) NOT NULL DEFAULT 0
+ ,is_delete INTEGER(1) NOT NULL DEFAULT 0
+ ,is_release INTEGER(1) NOT NULL DEFAULT 0
+ ,is_publish INTEGER(1) NOT NULL DEFAULT 0
+ ,is_grant INTEGER(1) NOT NULL DEFAULT 0
+ ,is_transmit INTEGER(1) NOT NULL DEFAULT 0
+ ,PRIMARY KEY (id)
+ ,CONSTRAINT or_fk_acl_groupid
+ FOREIGN KEY (groupid) REFERENCES or_group (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_acl_userid
+ FOREIGN KEY (userid) REFERENCES or_user (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_acl_objectid
+ FOREIGN KEY (objectid) REFERENCES or_object (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT or_fk_acl_languageid
+ FOREIGN KEY (languageid) REFERENCES or_language (id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+);
+CREATE INDEX or_idx_acl_userid
+ ON or_acl (userid);
+CREATE INDEX or_idx_acl_groupid
+ ON or_acl (groupid);
+CREATE INDEX or_idx_acl_languageid
+ ON or_acl (languageid);
+CREATE INDEX or_idx_acl_objectid
+ ON or_acl (objectid);
+CREATE INDEX or_idx_acl_is_transmit
+ ON or_acl (is_transmit);
+INSERT INTO or_user (id,name,password,ldap_dn,fullname,tel,mail,descr,style,is_admin) VALUES(1,'admin','admin','','Administrator','','','Admin user','default',1)