case_table_1.sql 6.5 KB

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