ORACLE.sql 6.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205
  1. create sequence SEQ_LOG
  2. minvalue 1
  3. maxvalue 999999999999999999999999999
  4. start with 1
  5. increment by 1
  6. nocache;
  7. /*
  8. Navicat Oracle Data Transfer
  9. Oracle Client Version : 11.2.0.4.0
  10. Source Server : 36xtdsp
  11. Source Server Version : 110200
  12. Source Host : 192.168.57.36:1521
  13. Source Schema : XTDSP
  14. Target Server Type : ORACLE
  15. Target Server Version : 110200
  16. File Encoding : 65001
  17. Date: 2016-08-29 09:56:52
  18. */
  19. -- ----------------------------
  20. -- Table structure for DSP_DATASOURCE
  21. -- ----------------------------
  22. DROP TABLE "XTDSP"."DSP_DATASOURCE";
  23. CREATE TABLE "XTDSP"."DSP_DATASOURCE" (
  24. "ID" VARCHAR2(40 BYTE) NOT NULL ,
  25. "URL" VARCHAR2(255 BYTE) NULL ,
  26. "DRIVERCLASSNAME" VARCHAR2(255 BYTE) NULL ,
  27. "USERNAME" VARCHAR2(50 BYTE) NULL ,
  28. "PASSWORD" VARCHAR2(50 BYTE) NULL ,
  29. "INITIALSIZE" VARCHAR2(10 BYTE) NULL ,
  30. "MINIDLE" VARCHAR2(10 BYTE) NULL ,
  31. "MAXACTIVE" VARCHAR2(10 BYTE) NULL ,
  32. "TIMEBETWEENEVICTIONRUNSMILLIS" VARCHAR2(10 BYTE) NULL ,
  33. "MINEVICTABLEIDLETIMEMILLIS" VARCHAR2(10 BYTE) NULL ,
  34. "TESTONRETURN" VARCHAR2(10 BYTE) NULL ,
  35. "POOLPREPAREDSTATEMENTS" VARCHAR2(10 BYTE) NULL ,
  36. "MAXPOOLPREPAREDSTATEMENTPERCON" VARCHAR2(10 BYTE) NULL ,
  37. "VALIDATIONQUERY" VARCHAR2(255 BYTE) NULL
  38. )
  39. LOGGING
  40. NOCOMPRESS
  41. NOCACHE
  42. ;
  43. -- ----------------------------
  44. -- Table structure for DSP_JOB
  45. -- ----------------------------
  46. DROP TABLE "XTDSP"."DSP_JOB";
  47. CREATE TABLE "XTDSP"."DSP_JOB" (
  48. "ID" VARCHAR2(40 BYTE) NOT NULL ,
  49. "NAME" VARCHAR2(100 BYTE) NULL ,
  50. "CREATE_TIME" TIMESTAMP(7) DEFAULT current_timestamp NULL ,
  51. "START_TIME" TIMESTAMP(7) NULL ,
  52. "END_TIME" TIMESTAMP(7) NULL ,
  53. "CIRCLE" CHAR(1 BYTE) NULL ,
  54. "RESULT" VARCHAR2(100 BYTE) NULL ,
  55. "INTERVAL" NUMBER NULL ,
  56. "STATUS" VARCHAR2(100 BYTE) NULL ,
  57. "PARAM" VARCHAR2(100 BYTE) NULL ,
  58. "CRON_EXPRESSION" VARCHAR2(100 BYTE) NULL ,
  59. "CODE" VARCHAR2(100 BYTE) NOT NULL
  60. )
  61. LOGGING
  62. NOCOMPRESS
  63. NOCACHE
  64. ;
  65. COMMENT ON TABLE "XTDSP"."DSP_JOB" IS '任务信息表';
  66. COMMENT ON COLUMN "XTDSP"."DSP_JOB"."ID" IS '唯一标识';
  67. COMMENT ON COLUMN "XTDSP"."DSP_JOB"."NAME" IS '任务名称';
  68. COMMENT ON COLUMN "XTDSP"."DSP_JOB"."CREATE_TIME" IS '任务创建时间';
  69. COMMENT ON COLUMN "XTDSP"."DSP_JOB"."START_TIME" IS '任务启动时间';
  70. COMMENT ON COLUMN "XTDSP"."DSP_JOB"."END_TIME" IS '任务结束时间';
  71. COMMENT ON COLUMN "XTDSP"."DSP_JOB"."CIRCLE" IS '是否循环';
  72. COMMENT ON COLUMN "XTDSP"."DSP_JOB"."RESULT" IS '上次执行结果';
  73. COMMENT ON COLUMN "XTDSP"."DSP_JOB"."INTERVAL" IS '间隔时间';
  74. COMMENT ON COLUMN "XTDSP"."DSP_JOB"."STATUS" IS '任务状态';
  75. COMMENT ON COLUMN "XTDSP"."DSP_JOB"."PARAM" IS '执行参数';
  76. -- ----------------------------
  77. -- Table structure for DSP_TASK
  78. -- ----------------------------
  79. DROP TABLE "XTDSP"."DSP_TASK";
  80. CREATE TABLE "XTDSP"."DSP_TASK" (
  81. "ID" VARCHAR2(40 BYTE) NOT NULL ,
  82. "NAME" VARCHAR2(100 BYTE) NULL ,
  83. "CODE" VARCHAR2(100 BYTE) NULL ,
  84. "JOB_CODE" VARCHAR2(40 BYTE) NULL ,
  85. "TYPE" CHAR(1 BYTE) NULL ,
  86. "CACHE_FOLDER" VARCHAR2(255 BYTE) NULL ,
  87. "CACHE_USE" CHAR(1 BYTE) DEFAULT 0 NULL
  88. )
  89. LOGGING
  90. NOCOMPRESS
  91. NOCACHE
  92. ;
  93. COMMENT ON COLUMN "XTDSP"."DSP_TASK"."NAME" IS '任务名称';
  94. COMMENT ON COLUMN "XTDSP"."DSP_TASK"."CODE" IS '任务代码';
  95. COMMENT ON COLUMN "XTDSP"."DSP_TASK"."JOB_CODE" IS '所在工作';
  96. COMMENT ON COLUMN "XTDSP"."DSP_TASK"."TYPE" IS '任务类型(sql同步任务:1)';
  97. COMMENT ON COLUMN "XTDSP"."DSP_TASK"."CACHE_FOLDER" IS '缓存目录';
  98. COMMENT ON COLUMN "XTDSP"."DSP_TASK"."CACHE_USE" IS '是否存在缓存';
  99. -- ----------------------------
  100. -- Table structure for DSP_TASK_SQL
  101. -- ----------------------------
  102. DROP TABLE "XTDSP"."DSP_TASK_SQL";
  103. CREATE TABLE "XTDSP"."DSP_TASK_SQL" (
  104. "ID" VARCHAR2(40 BYTE) NOT NULL ,
  105. "TASK_CODE" VARCHAR2(40 BYTE) NULL ,
  106. "SRC_CONN" VARCHAR2(20 BYTE) NULL ,
  107. "TARGET_CONN" VARCHAR2(20 BYTE) NULL ,
  108. "QUERY_SQL" VARCHAR2(2000 BYTE) NULL ,
  109. "TARGET_TABLE" VARCHAR2(40 BYTE) NULL ,
  110. "UPDATE_SQL" VARCHAR2(2000 BYTE) NULL ,
  111. "INSERT_SQL" VARCHAR2(2000 BYTE) NULL ,
  112. "REFRESH_SQL" CHAR(1 BYTE) NULL ,
  113. "COLUMNS" VARCHAR2(2000 BYTE) NULL ,
  114. "SYN_MODE" CHAR(1 BYTE) NULL
  115. )
  116. LOGGING
  117. NOCOMPRESS
  118. NOCACHE
  119. ;
  120. COMMENT ON TABLE "XTDSP"."DSP_TASK_SQL" IS 'sql查询与同步任务';
  121. COMMENT ON COLUMN "XTDSP"."DSP_TASK_SQL"."UPDATE_SQL" IS '更新sql';
  122. COMMENT ON COLUMN "XTDSP"."DSP_TASK_SQL"."INSERT_SQL" IS '插入sql';
  123. COMMENT ON COLUMN "XTDSP"."DSP_TASK_SQL"."REFRESH_SQL" IS '执是否行前更新sql';
  124. COMMENT ON COLUMN "XTDSP"."DSP_TASK_SQL"."COLUMNS" IS '同步列';
  125. COMMENT ON COLUMN "XTDSP"."DSP_TASK_SQL"."SYN_MODE" IS '同步模式:逐条1、全表2';
  126. -- ----------------------------
  127. -- Indexes structure for table DSP_DATASOURCE
  128. -- ----------------------------
  129. -- ----------------------------
  130. -- Checks structure for table DSP_DATASOURCE
  131. -- ----------------------------
  132. ALTER TABLE "XTDSP"."DSP_DATASOURCE" ADD CHECK ("ID" IS NOT NULL);
  133. ALTER TABLE "XTDSP"."DSP_DATASOURCE" ADD CHECK ("ID" IS NOT NULL);
  134. -- ----------------------------
  135. -- Primary Key structure for table DSP_DATASOURCE
  136. -- ----------------------------
  137. ALTER TABLE "XTDSP"."DSP_DATASOURCE" ADD PRIMARY KEY ("ID");
  138. -- ----------------------------
  139. -- Indexes structure for table DSP_JOB
  140. -- ----------------------------
  141. CREATE UNIQUE INDEX "XTDSP"."I_CODE"
  142. ON "XTDSP"."DSP_JOB" ("CODE" ASC)
  143. LOGGING
  144. VISIBLE;
  145. -- ----------------------------
  146. -- Uniques structure for table DSP_JOB
  147. -- ----------------------------
  148. ALTER TABLE "XTDSP"."DSP_JOB" ADD UNIQUE ("CODE");
  149. -- ----------------------------
  150. -- Checks structure for table DSP_JOB
  151. -- ----------------------------
  152. ALTER TABLE "XTDSP"."DSP_JOB" ADD CHECK ("ID" IS NOT NULL);
  153. ALTER TABLE "XTDSP"."DSP_JOB" ADD CHECK ("CODE" IS NOT NULL);
  154. -- ----------------------------
  155. -- Primary Key structure for table DSP_JOB
  156. -- ----------------------------
  157. ALTER TABLE "XTDSP"."DSP_JOB" ADD PRIMARY KEY ("ID");
  158. -- ----------------------------
  159. -- Indexes structure for table DSP_TASK
  160. -- ----------------------------
  161. -- ----------------------------
  162. -- Checks structure for table DSP_TASK
  163. -- ----------------------------
  164. ALTER TABLE "XTDSP"."DSP_TASK" ADD CHECK ("ID" IS NOT NULL);
  165. -- ----------------------------
  166. -- Primary Key structure for table DSP_TASK
  167. -- ----------------------------
  168. ALTER TABLE "XTDSP"."DSP_TASK" ADD PRIMARY KEY ("ID");
  169. -- ----------------------------
  170. -- Indexes structure for table DSP_TASK_SQL
  171. -- ----------------------------
  172. -- ----------------------------
  173. -- Checks structure for table DSP_TASK_SQL
  174. -- ----------------------------
  175. ALTER TABLE "XTDSP"."DSP_TASK_SQL" ADD CHECK ("ID" IS NOT NULL);
  176. -- ----------------------------
  177. -- Primary Key structure for table DSP_TASK_SQL
  178. -- ----------------------------
  179. ALTER TABLE "XTDSP"."DSP_TASK_SQL" ADD PRIMARY KEY ("ID");