jsgkj/xtdsp/trunk/sql/ORACLE.sql

create sequence SEQ_LOG
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;

/*
Navicat Oracle Data Transfer
Oracle Client Version : 11.2.0.4.0

Source Server         : 36xtdsp
Source Server Version : 110200
Source Host           : 192.168.57.36:1521
Source Schema         : XTDSP

Target Server Type    : ORACLE
Target Server Version : 110200
File Encoding         : 65001

Date: 2016-08-29 09:56:52
*/


-- ----------------------------
-- Table structure for DSP_DATASOURCE
-- ----------------------------
DROP TABLE "XTDSP"."DSP_DATASOURCE";
CREATE TABLE "XTDSP"."DSP_DATASOURCE" (
"ID" VARCHAR2(40 BYTE) NOT NULL ,
"URL" VARCHAR2(255 BYTE) NULL ,
"DRIVERCLASSNAME" VARCHAR2(255 BYTE) NULL ,
"USERNAME" VARCHAR2(50 BYTE) NULL ,
"PASSWORD" VARCHAR2(50 BYTE) NULL ,
"INITIALSIZE" VARCHAR2(10 BYTE) NULL ,
"MINIDLE" VARCHAR2(10 BYTE) NULL ,
"MAXACTIVE" VARCHAR2(10 BYTE) NULL ,
"TIMEBETWEENEVICTIONRUNSMILLIS" VARCHAR2(10 BYTE) NULL ,
"MINEVICTABLEIDLETIMEMILLIS" VARCHAR2(10 BYTE) NULL ,
"TESTONRETURN" VARCHAR2(10 BYTE) NULL ,
"POOLPREPAREDSTATEMENTS" VARCHAR2(10 BYTE) NULL ,
"MAXPOOLPREPAREDSTATEMENTPERCON" VARCHAR2(10 BYTE) NULL ,
"VALIDATIONQUERY" VARCHAR2(255 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Table structure for DSP_JOB
-- ----------------------------
DROP TABLE "XTDSP"."DSP_JOB";
CREATE TABLE "XTDSP"."DSP_JOB" (
"ID" VARCHAR2(40 BYTE) NOT NULL ,
"NAME" VARCHAR2(100 BYTE) NULL ,
"CREATE_TIME" TIMESTAMP(7)  DEFAULT current_timestamp  NULL ,
"START_TIME" TIMESTAMP(7)  NULL ,
"END_TIME" TIMESTAMP(7)  NULL ,
"CIRCLE" CHAR(1 BYTE) NULL ,
"RESULT" VARCHAR2(100 BYTE) NULL ,
"INTERVAL" NUMBER NULL ,
"STATUS" VARCHAR2(100 BYTE) NULL ,
"PARAM" VARCHAR2(100 BYTE) NULL ,
"CRON_EXPRESSION" VARCHAR2(100 BYTE) NULL ,
"CODE" VARCHAR2(100 BYTE) NOT NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "XTDSP"."DSP_JOB" IS '任务信息表';
COMMENT ON COLUMN "XTDSP"."DSP_JOB"."ID" IS '唯一标识';
COMMENT ON COLUMN "XTDSP"."DSP_JOB"."NAME" IS '任务名称';
COMMENT ON COLUMN "XTDSP"."DSP_JOB"."CREATE_TIME" IS '任务创建时间';
COMMENT ON COLUMN "XTDSP"."DSP_JOB"."START_TIME" IS '任务启动时间';
COMMENT ON COLUMN "XTDSP"."DSP_JOB"."END_TIME" IS '任务结束时间';
COMMENT ON COLUMN "XTDSP"."DSP_JOB"."CIRCLE" IS '是否循环';
COMMENT ON COLUMN "XTDSP"."DSP_JOB"."RESULT" IS '上次执行结果';
COMMENT ON COLUMN "XTDSP"."DSP_JOB"."INTERVAL" IS '间隔时间';
COMMENT ON COLUMN "XTDSP"."DSP_JOB"."STATUS" IS '任务状态';
COMMENT ON COLUMN "XTDSP"."DSP_JOB"."PARAM" IS '执行参数';

-- ----------------------------
-- Table structure for DSP_TASK
-- ----------------------------
DROP TABLE "XTDSP"."DSP_TASK";
CREATE TABLE "XTDSP"."DSP_TASK" (
"ID" VARCHAR2(40 BYTE) NOT NULL ,
"NAME" VARCHAR2(100 BYTE) NULL ,
"CODE" VARCHAR2(100 BYTE) NULL ,
"JOB_CODE" VARCHAR2(40 BYTE) NULL ,
"TYPE" CHAR(1 BYTE) NULL ,
"CACHE_FOLDER" VARCHAR2(255 BYTE) NULL ,
"CACHE_USE" CHAR(1 BYTE) DEFAULT 0  NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON COLUMN "XTDSP"."DSP_TASK"."NAME" IS '任务名称';
COMMENT ON COLUMN "XTDSP"."DSP_TASK"."CODE" IS '任务代码';
COMMENT ON COLUMN "XTDSP"."DSP_TASK"."JOB_CODE" IS '所在工作';
COMMENT ON COLUMN "XTDSP"."DSP_TASK"."TYPE" IS '任务类型（sql同步任务：1）';
COMMENT ON COLUMN "XTDSP"."DSP_TASK"."CACHE_FOLDER" IS '缓存目录';
COMMENT ON COLUMN "XTDSP"."DSP_TASK"."CACHE_USE" IS '是否存在缓存';

-- ----------------------------
-- Table structure for DSP_TASK_SQL
-- ----------------------------
DROP TABLE "XTDSP"."DSP_TASK_SQL";
CREATE TABLE "XTDSP"."DSP_TASK_SQL" (
"ID" VARCHAR2(40 BYTE) NOT NULL ,
"TASK_CODE" VARCHAR2(40 BYTE) NULL ,
"SRC_CONN" VARCHAR2(20 BYTE) NULL ,
"TARGET_CONN" VARCHAR2(20 BYTE) NULL ,
"QUERY_SQL" VARCHAR2(2000 BYTE) NULL ,
"TARGET_TABLE" VARCHAR2(40 BYTE) NULL ,
"UPDATE_SQL" VARCHAR2(2000 BYTE) NULL ,
"INSERT_SQL" VARCHAR2(2000 BYTE) NULL ,
"REFRESH_SQL" CHAR(1 BYTE) NULL ,
"COLUMNS" VARCHAR2(2000 BYTE) NULL ,
"SYN_MODE" CHAR(1 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;
COMMENT ON TABLE "XTDSP"."DSP_TASK_SQL" IS 'sql查询与同步任务';
COMMENT ON COLUMN "XTDSP"."DSP_TASK_SQL"."UPDATE_SQL" IS '更新sql';
COMMENT ON COLUMN "XTDSP"."DSP_TASK_SQL"."INSERT_SQL" IS '插入sql';
COMMENT ON COLUMN "XTDSP"."DSP_TASK_SQL"."REFRESH_SQL" IS '执是否行前更新sql';
COMMENT ON COLUMN "XTDSP"."DSP_TASK_SQL"."COLUMNS" IS '同步列';
COMMENT ON COLUMN "XTDSP"."DSP_TASK_SQL"."SYN_MODE" IS '同步模式：逐条1、全表2';

-- ----------------------------
-- Indexes structure for table DSP_DATASOURCE
-- ----------------------------

-- ----------------------------
-- Checks structure for table DSP_DATASOURCE
-- ----------------------------
ALTER TABLE "XTDSP"."DSP_DATASOURCE" ADD CHECK ("ID" IS NOT NULL);
ALTER TABLE "XTDSP"."DSP_DATASOURCE" ADD CHECK ("ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table DSP_DATASOURCE
-- ----------------------------
ALTER TABLE "XTDSP"."DSP_DATASOURCE" ADD PRIMARY KEY ("ID");

-- ----------------------------
-- Indexes structure for table DSP_JOB
-- ----------------------------
CREATE UNIQUE INDEX "XTDSP"."I_CODE"
ON "XTDSP"."DSP_JOB" ("CODE" ASC)
LOGGING
VISIBLE;

-- ----------------------------
-- Uniques structure for table DSP_JOB
-- ----------------------------
ALTER TABLE "XTDSP"."DSP_JOB" ADD UNIQUE ("CODE");

-- ----------------------------
-- Checks structure for table DSP_JOB
-- ----------------------------
ALTER TABLE "XTDSP"."DSP_JOB" ADD CHECK ("ID" IS NOT NULL);
ALTER TABLE "XTDSP"."DSP_JOB" ADD CHECK ("CODE" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table DSP_JOB
-- ----------------------------
ALTER TABLE "XTDSP"."DSP_JOB" ADD PRIMARY KEY ("ID");

-- ----------------------------
-- Indexes structure for table DSP_TASK
-- ----------------------------

-- ----------------------------
-- Checks structure for table DSP_TASK
-- ----------------------------
ALTER TABLE "XTDSP"."DSP_TASK" ADD CHECK ("ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table DSP_TASK
-- ----------------------------
ALTER TABLE "XTDSP"."DSP_TASK" ADD PRIMARY KEY ("ID");

-- ----------------------------
-- Indexes structure for table DSP_TASK_SQL
-- ----------------------------

-- ----------------------------
-- Checks structure for table DSP_TASK_SQL
-- ----------------------------
ALTER TABLE "XTDSP"."DSP_TASK_SQL" ADD CHECK ("ID" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table DSP_TASK_SQL
-- ----------------------------
ALTER TABLE "XTDSP"."DSP_TASK_SQL" ADD PRIMARY KEY ("ID");