createendusertables.sql

ODAC 6 最新版的﹐網上找了好久才找到﹐不太好找啊﹐大家一起共享

DROP TABLE RB_FOLDER;
DROP SEQUENCE FOLDER_ID_SEQ;
DROP TABLE RB_ITEM;
DROP SEQUENCE ITEM_ID_SEQ;
DROP TABLE RB_TABLE;
DROP TABLE RB_FIELD;
DROP TABLE RB_JOIN;


CREATE TABLE RB_FOLDER (
  FOLDER_ID NUMBER(6, 0) DEFAULT 0,
  FOLDER_NAME CHAR(60),
  PARENT_ID NUMBER(6, 0) DEFAULT 0 );

CREATE INDEX FOLDER_IDX ON RB_FOLDER (FOLDER_ID);
CREATE INDEX PARENT_IDX ON RB_FOLDER (PARENT_ID);


CREATE SEQUENCE FOLDER_ID_SEQ
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 999999
  MINVALUE 1;

CREATE TRIGGER SET_FOLDER_ID AFTER INSERT ON RB_FOLDER
BEGIN
  UPDATE RB_FOLDER
    SET FOLDER_ID = FOLDER_ID_SEQ.NEXTVAL
    WHERE FOLDER_ID = 0;
END;
/


CREATE TABLE RB_ITEM (
  ITEM_ID NUMBER(6, 0) DEFAULT 0 ,
  FOLDER_ID NUMBER(6, 0) DEFAULT 0,
  ITEM_NAME CHAR(60),
  ITEM_SIZE NUMBER(6, 0) DEFAULT 0,
  ITEM_TYPE NUMBER(6, 0) DEFAULT 0,
  MODIFIED NUMBER,
  DELETED NUMBER,
  TEMPLATE LONG);

CREATE INDEX ITEM_IDX ON RB_ITEM (ITEM_ID);
CREATE INDEX FOLDER_ITEM_NAME_IDX ON RB_ITEM (FOLDER_ID, ITEM_TYPE, ITEM_NAME);

CREATE SEQUENCE ITEM_ID_SEQ
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 999999
  MINVALUE 1;

CREATE TRIGGER SET_ITEM_ID AFTER INSERT ON RB_ITEM
BEGIN
  UPDATE RB_ITEM
    SET ITEM_ID = ITEM_ID_SEQ.NEXTVAL
    WHERE ITEM_ID = 0;
END;
/


CREATE TABLE RB_TABLE (
  TABLE_NAME VARCHAR2(60) NOT NULL,
  TABLE_ALIAS VARCHAR2(60)) ;

CREATE INDEX RB_TABLE_NAME_IDX ON RB_TABLE (TABLE_NAME);


CREATE TABLE RB_FIELD (
  TABLE_NAME VARCHAR2(60) NOT NULL,
  FIELD_NAME VARCHAR2(60) NOT NULL,
  FIELD_ALIAS VARCHAR2(60),
  DATATYPE VARCHAR2(60),
  SELECTABLE VARCHAR2(1),
  SEARCHABLE VARCHAR2(1),
  SORTABLE VARCHAR2(1),
  AUTOSEARCH VARCHAR2(1),
  MANDATORY VARCHAR2(1));

CREATE INDEX RB_TABLE_FIELD_NAME_IDX ON RB_FIELD (TABLE_NAME, FIELD_NAME);


CREATE TABLE RB_JOIN (
  TABLE_NAME1 VARCHAR2(60) NOT NULL,
  TABLE_NAME2 VARCHAR2(60) NOT NULL,
  JOIN_TYPE VARCHAR2(60),
  FIELD_NAMES1 VARCHAR2(255),
  OPERATORS VARCHAR2(60),
  FIELD_NAMES2 VARCHAR2(255));

CREATE INDEX RB_TABLE1_TABLE2_IDX ON rb_join (TABLE_NAME1, TABLE_NAME2);
DROP SEQUENCE FOLDER_ID_SEQ;
DROP TABLE RB_ITEM;
DROP SEQUENCE ITEM_ID_SEQ;
DROP TABLE RB_TABLE;
DROP TABLE RB_FIELD;
DROP TABLE RB_JOIN;


CREATE TABLE RB_FOLDER (
  FOLDER_ID NUMBER(6, 0) DEFAULT 0,
  FOLDER_NAME CHAR(60),
  PARENT_ID NUMBER(6, 0) DEFAULT 0 );

CREATE INDEX FOLDER_IDX ON RB_FOLDER (FOLDER_ID);
CREATE INDEX PARENT_IDX ON RB_FOLDER (PARENT_ID);


CREATE SEQUENCE FOLDER_ID_SEQ
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 999999
  MINVALUE 1;

CREATE TRIGGER SET_FOLDER_ID AFTER INSERT ON RB_FOLDER
BEGIN
  UPDATE RB_FOLDER
    SET FOLDER_ID = FOLDER_ID_SEQ.NEXTVAL
    WHERE FOLDER_ID = 0;
END;
/


CREATE TABLE RB_ITEM (
  ITEM_ID NUMBER(6, 0) DEFAULT 0 ,
  FOLDER_ID NUMBER(6, 0) DEFAULT 0,
  ITEM_NAME CHAR(60),
  ITEM_SIZE NUMBER(6, 0) DEFAULT 0,
  ITEM_TYPE NUMBER(6, 0) DEFAULT 0,
  MODIFIED NUMBER,
  DELETED NUMBER,
  TEMPLATE LONG);

CREATE INDEX ITEM_IDX ON RB_ITEM (ITEM_ID);
CREATE INDEX FOLDER_ITEM_NAME_IDX ON RB_ITEM (FOLDER_ID, ITEM_TYPE, ITEM_NAME);

CREATE SEQUENCE ITEM_ID_SEQ
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 999999
  MINVALUE 1;

CREATE TRIGGER SET_ITEM_ID AFTER INSERT ON RB_ITEM
BEGIN
  UPDATE RB_ITEM
    SET ITEM_ID = ITEM_ID_SEQ.NEXTVAL
    WHERE ITEM_ID = 0;
END;
/


CREATE TABLE RB_TABLE (
  TABLE_NAME VARCHAR2(60) NOT NULL,
  TABLE_ALIAS VARCHAR2(60)) ;

CREATE INDEX RB_TABLE_NAME_IDX ON RB_TABLE (TABLE_NAME);


CREATE TABLE RB_FIELD (
  TABLE_NAME VARCHAR2(60) NOT NULL,
  FIELD_NAME VARCHAR2(60) NOT NULL,
  FIELD_ALIAS VARCHAR2(60),
  DATATYPE VARCHAR2(60),
  SELECTABLE VARCHAR2(1),
  SEARCHABLE VARCHAR2(1),
  SORTABLE VARCHAR2(1),
  AUTOSEARCH VARCHAR2(1),
  MANDATORY VARCHAR2(1));

CREATE INDEX RB_TABLE_FIELD_NAME_IDX ON RB_FIELD (TABLE_NAME, FIELD_NAME);


CREATE TABLE RB_JOIN (
  TABLE_NAME1 VARCHAR2(60) NOT NULL,
  TABLE_NAME2 VARCHAR2(60) NOT NULL,
  JOIN_TYPE VARCHAR2(60),
  FIELD_NAMES1 VARCHAR2(255),
  OPERATORS VARCHAR2(60),
  FIELD_NAMES2 VARCHAR2(255));

CREATE INDEX RB_TABLE1_TABLE2_IDX ON rb_join (TABLE_NAME1, TABLE_NAME2);