case_table_1.sql 6.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264
  1. -- CREATE SEQUENCE global_seq START 1000;
  2. -- CREATE SEQUENCE time_series_id_seq START 1;
  3. -- CREATE SEQUENCE use_call_seq START 1;
  4. -- CREATE SEQUENCE call_seq START 1;
  5. DROP TABLE IF EXISTS file_info;
  6. CREATE TABLE file_info
  7. (
  8. id BIGINT PRIMARY KEY,
  9. pid BIGINT NOT NULL,
  10. fileName VARCHAR,
  11. sheetName VARCHAR,
  12. batchId BIGINT,
  13. templateId BIGINT,
  14. tableName VARCHAR,
  15. lineNo INTEGER,
  16. header VARCHAR,
  17. password VARCHAR,
  18. charSet VARCHAR,
  19. delimiter VARCHAR,
  20. fileType VARCHAR,
  21. filePath VARCHAR,
  22. fileSize BIGINT DEFAULT 0 NOT NULL,
  23. dataNum INTEGER DEFAULT 0 NOT NULL,
  24. successNum INTEGER DEFAULT 0 NOT NULL,
  25. failNum INTEGER DEFAULT 0 NOT NULL,
  26. fileStatus VARCHAR,
  27. md5 VARCHAR,
  28. -- 备注(持久化):电子数据包解析失败原因等。老案件库由开案时的 FILE_INFO_MIGRATIONS 补列
  29. remark VARCHAR,
  30. -- 电子数据包采集编号:删除数据包时按它清理 wa_mforensics_* 与 EDS 附属表
  31. collectTargetId VARCHAR,
  32. createDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  33. );
  34. -- 非表格文件(txt / doc / docx / pdf / ppt / 压缩包 …)的 AI 识别档案。
  35. -- 这些文件无法解析成表格数据,file_info 里只留一条 FILE_UNSUPPORTED_FAIL 记录;
  36. -- 本表存 Tika 抽取的文本样本 + 大模型识别出的分类/摘要/关键词,供后续数据分析工具使用。
  37. -- 注意:业务代码(CaseDataSourceRegistry#ensureAiProfileTable)会用 CREATE TABLE IF NOT EXISTS
  38. -- 做幂等补建、用 AI_PROFILE_MIGRATIONS 给老库补列,老案件库开案时自动补上,无需手工迁移。
  39. -- 改这里的列时,必须同步改 CaseDataSourceRegistry#AI_PROFILE_DDL / #AI_PROFILE_MIGRATIONS
  40. -- 与实体 FileAiProfile(FileAiProfileSchemaTest 会比对三者的列名)。
  41. DROP TABLE IF EXISTS file_ai_profile;
  42. CREATE TABLE file_ai_profile
  43. (
  44. id BIGINT PRIMARY KEY,
  45. caseId BIGINT,
  46. fileId BIGINT,
  47. batchId BIGINT,
  48. fileName VARCHAR,
  49. fileType VARCHAR,
  50. mimeType VARCHAR,
  51. category VARCHAR,
  52. summary VARCHAR,
  53. keywords VARCHAR,
  54. sampleText VARCHAR,
  55. sampleLength INTEGER,
  56. parseStatus VARCHAR,
  57. failReason VARCHAR,
  58. modelName VARCHAR,
  59. costMillis BIGINT,
  60. createDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  61. );
  62. DROP TABLE IF EXISTS person_basic_info;
  63. create table person_basic_info
  64. (
  65. personName VARCHAR PRIMARY KEY,
  66. personCertNo VARCHAR,
  67. personBirth DATE,
  68. personGender VARCHAR,
  69. personAge INTEGER,
  70. personAddress VARCHAR,
  71. photograph VARCHAR,
  72. marry INTEGER,
  73. nation VARCHAR,
  74. nativePlace VARCHAR,
  75. householdRegistered VARCHAR,
  76. orderSort VARCHAR,
  77. personTag VARCHAR,
  78. sysLabel VARCHAR
  79. );
  80. DROP TABLE IF EXISTS person_group;
  81. create table person_group
  82. (
  83. id BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
  84. groupName VARCHAR,
  85. remark VARCHAR
  86. );
  87. DROP TABLE IF EXISTS person_group_rel;
  88. create table person_group_rel
  89. (
  90. id BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
  91. groupId BIGINT,
  92. personName VARCHAR,
  93. hasMain VARCHAR,
  94. mainDesc VARCHAR
  95. );
  96. DROP TABLE IF EXISTS person_lib_no;
  97. create table person_lib_no
  98. (
  99. md5 VARCHAR PRIMARY KEY,
  100. personName VARCHAR,
  101. personNo VARCHAR,
  102. personNoCn VARCHAR,
  103. libType INTEGER,
  104. startTime TIMESTAMP,
  105. endTime TIMESTAMP,
  106. hasAuto INTEGER
  107. );
  108. DROP TABLE IF EXISTS rel_edge;
  109. create table rel_edge
  110. (
  111. id BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
  112. node1 VARCHAR,
  113. node2 VARCHAR,
  114. node1cn VARCHAR,
  115. node2cn VARCHAR,
  116. mc VARCHAR,
  117. clazz VARCHAR,
  118. num VARCHAR,
  119. sj TIMESTAMP,
  120. fx INTEGER,
  121. lx INTEGER,
  122. cs INTEGER
  123. );
  124. DROP TABLE IF EXISTS rel_node;
  125. create table rel_node
  126. (
  127. node_id VARCHAR,
  128. node_type VARCHAR
  129. );
  130. DROP TABLE IF EXISTS edge_conf;
  131. create table edge_conf
  132. (
  133. id BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
  134. clazz VARCHAR,
  135. tableName VARCHAR,
  136. node1 VARCHAR,
  137. node2 VARCHAR,
  138. mc VARCHAR,
  139. sj VARCHAR
  140. );
  141. DROP TABLE IF EXISTS rel_node_conf;
  142. create table rel_node_conf
  143. (
  144. id BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
  145. clazz VARCHAR,
  146. tableName VARCHAR,
  147. node VARCHAR,
  148. fd VARCHAR
  149. );
  150. DROP TABLE IF EXISTS special_date;
  151. create table special_date
  152. (
  153. batchId BIGINT,
  154. name VARCHAR,
  155. startDate DATE,
  156. endDate DATE
  157. );
  158. DROP TABLE IF EXISTS special_date_reord;
  159. create table special_date_reord
  160. (
  161. id BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
  162. name VARCHAR,
  163. flag INTEGER,
  164. type INTEGER,
  165. gap INTEGER,
  166. spDate VARCHAR
  167. );
  168. DROP TABLE IF EXISTS govern_tree;
  169. CREATE TABLE govern_tree
  170. (
  171. id BIGINT PRIMARY KEY,
  172. pid BIGINT,
  173. type VARCHAR,
  174. tableNameEn VARCHAR,
  175. tableNameCn VARCHAR,
  176. dataCount INTEGER
  177. );
  178. DROP TABLE IF EXISTS govern_conf;
  179. CREATE TABLE govern_conf
  180. (
  181. id BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
  182. confStr VARCHAR,
  183. confType VARCHAR
  184. );
  185. DROP TABLE IF EXISTS cash_key;
  186. CREATE TABLE cash_key
  187. (
  188. id BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
  189. cashKey VARCHAR
  190. );
  191. DROP TABLE IF EXISTS trans_financial_key;
  192. CREATE TABLE trans_financial_key
  193. (
  194. id BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
  195. financialKey VARCHAR,
  196. tag VARCHAR
  197. );
  198. DROP TABLE IF EXISTS fixed_deposit_key;
  199. CREATE TABLE fixed_deposit_key
  200. (
  201. id BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
  202. fixedDepositKey VARCHAR
  203. );
  204. DROP TABLE IF EXISTS clean_error_log;
  205. CREATE TABLE clean_error_log
  206. (
  207. id BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
  208. fileId BIGINT,
  209. batchId BIGINT,
  210. sheetId BIGINT,
  211. fileName VARCHAR,
  212. sheetName VARCHAR,
  213. content VARCHAR,
  214. createDateTime timestamp
  215. );
  216. DROP TABLE IF EXISTS intimacy;
  217. CREATE TABLE intimacy
  218. (
  219. id BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
  220. scores VARCHAR,
  221. weight VARCHAR,
  222. tags VARCHAR
  223. );
  224. DROP TABLE IF EXISTS graph_his;
  225. CREATE TABLE graph_his
  226. (
  227. id BIGINT PRIMARY KEY DEFAULT nextval('global_seq'),
  228. graphName VARCHAR,
  229. path VARCHAR
  230. );