1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
| package com.emergencyrepair.bootstrap;
|
| import lombok.RequiredArgsConstructor;
| import org.springframework.boot.CommandLineRunner;
| import org.springframework.core.annotation.Order;
| import org.springframework.jdbc.core.JdbcTemplate;
| import org.springframework.stereotype.Component;
|
| import java.util.LinkedHashMap;
| import java.util.Map;
|
| @Component
| @Order(1)
| @RequiredArgsConstructor
| public class DatabaseBootstrap implements CommandLineRunner {
| private final JdbcTemplate jdbcTemplate;
|
| @Override
| public void run(String... args) {
| ensureSchema();
| for (Map.Entry<String, String> entry : tableDdl().entrySet()) {
| if (!tableExists(entry.getKey())) {
| jdbcTemplate.execute(entry.getValue());
| }
| }
| renameColumnIfNeeded("T_FAULT", "FAULT_DESCRIPTION", "ENGINEERING_NAME_DESC", "ALTER TABLE LX.T_FAULT RENAME COLUMN FAULT_DESCRIPTION TO ENGINEERING_NAME_DESC");
| ensureColumn("T_FAULT", "EQUIPMENT_IDS", "ALTER TABLE LX.T_FAULT ADD EQUIPMENT_IDS VARCHAR(2000)");
| ensureColumn("T_COMPLETION_INFO", "CONSUMABLE_COUNT", "ALTER TABLE LX.T_COMPLETION_INFO ADD CONSUMABLE_COUNT INT");
| ensureColumn("T_COMPLETION_INFO", "SOLUTIONS_JSON", "ALTER TABLE LX.T_COMPLETION_INFO ADD SOLUTIONS_JSON CLOB");
| ensureIndex("UX_T_FAULT_FAULT_CODE", "CREATE UNIQUE INDEX LX.UX_T_FAULT_FAULT_CODE ON LX.T_FAULT(FAULT_CODE)");
| ensureIndex("UX_T_WORK_ORDER_NO", "CREATE UNIQUE INDEX LX.UX_T_WORK_ORDER_NO ON LX.T_WORK_ORDER(WORK_ORDER_NO)");
| ensureIndex("IX_T_WORK_ORDER_FAULT_ID", "CREATE INDEX LX.IX_T_WORK_ORDER_FAULT_ID ON LX.T_WORK_ORDER(FAULT_ID)");
| ensureIndex("IX_T_COMPLETION_WORK_ORDER", "CREATE INDEX LX.IX_T_COMPLETION_WORK_ORDER ON LX.T_COMPLETION_INFO(WORK_ORDER_ID)");
| }
|
| private void ensureSchema() {
| Integer count = jdbcTemplate.queryForObject(
| "SELECT COUNT(*) FROM ALL_USERS WHERE USERNAME = ?", Integer.class, "LX");
| if (count == null || count == 0) {
| jdbcTemplate.execute("CREATE SCHEMA LX AUTHORIZATION SYSDBA");
| }
| try {
| jdbcTemplate.execute("CREATE OR REPLACE SYNONYM LX.SYS_USER FOR CSICZB_ZB.SYS_USER");
| jdbcTemplate.execute("CREATE OR REPLACE SYNONYM LX.SYS_DEPT FOR CSICZB_ZB.SYS_DEPT");
| } catch (Exception ignored) {
| // Synonyms are optional for the first version. Biz tables remain fully usable.
| }
| }
|
| private boolean tableExists(String tableName) {
| Integer count = jdbcTemplate.queryForObject(
| "SELECT COUNT(*) FROM ALL_TABLES WHERE OWNER = 'LX' AND TABLE_NAME = ?",
| Integer.class, tableName);
| return count != null && count > 0;
| }
|
| private void ensureColumn(String tableName, String columnName, String ddl) {
| if (!columnExists(tableName, columnName)) {
| jdbcTemplate.execute(ddl);
| }
| }
|
| private void renameColumnIfNeeded(String tableName, String oldColumnName, String newColumnName, String ddl) {
| if (!columnExists(tableName, newColumnName) && columnExists(tableName, oldColumnName)) {
| jdbcTemplate.execute(ddl);
| }
| }
|
| private boolean columnExists(String tableName, String columnName) {
| Integer count = jdbcTemplate.queryForObject(
| "SELECT COUNT(*) FROM ALL_TAB_COLUMNS WHERE OWNER = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?",
| Integer.class, "LX", tableName, columnName);
| return count != null && count > 0;
| }
|
| private void ensureIndex(String indexName, String ddl) {
| Integer count = jdbcTemplate.queryForObject(
| "SELECT COUNT(*) FROM ALL_INDEXES WHERE OWNER = 'LX' AND INDEX_NAME = ?",
| Integer.class, indexName);
| if (count == null || count == 0) {
| jdbcTemplate.execute(ddl);
| }
| }
|
| private Map<String, String> tableDdl() {
| Map<String, String> ddl = new LinkedHashMap<>();
| ddl.put("T_FAULT", "CREATE TABLE LX.T_FAULT (" +
| "ID BIGINT NOT NULL PRIMARY KEY, FAULT_CODE VARCHAR(80), X_NO VARCHAR(40), EQUIPMENT_ID BIGINT, EQUIPMENT_IDS VARCHAR(2000), EQUIPMENT_NAME VARCHAR(200), PARENT_SYSTEM VARCHAR(200), ENGINEERING_MAJOR VARCHAR(100), TEAM_MAJOR VARCHAR(100), REPORT_UNIT VARCHAR(200), REPORT_DATE DATE, OWNER VARCHAR(80), FAULT_IMPACT VARCHAR(100), FAULT_NATURE VARCHAR(100), MAJOR_FAULT_DESC VARCHAR(2000), REPAIR_REQUIREMENT VARCHAR(2000), ENGINEERING_NAME_DESC VARCHAR(2000), IS_SELF_REPAIR INT, IS_H_RELATED INT, IS_MONITOR_ITEM INT, MONITOR_ITEM VARCHAR(200), MONITOR_VALUE VARCHAR(200), REQUESTED_FINISH_DATE DATE, REMARK VARCHAR(2000), TASK_STATUS VARCHAR(40), CONTRACTOR_IDS VARCHAR(1000), CONTRACTOR_NAMES VARCHAR(2000), TRACKING_TEAM VARCHAR(100), ENGINEERING_NO VARCHAR(120), XCB_REVIEW_TIME TIMESTAMP, CMB_OPINION VARCHAR(2000), CMB_RESULT VARCHAR(20), CMB_TIME TIMESTAMP, JCC_ASSISTANT_OPINION VARCHAR(2000), JCC_ASSISTANT_RESULT VARCHAR(20), JCC_ASSISTANT_TIME TIMESTAMP, JCC_DIRECTOR_OPINION VARCHAR(2000), JCC_DIRECTOR_RESULT VARCHAR(20), JCC_DIRECTOR_TIME TIMESTAMP, DISPATCH_TIME TIMESTAMP, CANCEL_REASON VARCHAR(1000), CANCELLED_BY VARCHAR(80), CANCEL_TIME TIMESTAMP, CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
| ddl.put("T_ENGINEERING_MAJOR", "CREATE TABLE LX.T_ENGINEERING_MAJOR (ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR(200), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
| ddl.put("T_TEAM_MAJOR", "CREATE TABLE LX.T_TEAM_MAJOR (ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR(200), CATEGORY VARCHAR(10), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
| ddl.put("T_CONTRACTOR", "CREATE TABLE LX.T_CONTRACTOR (ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR(200), SHORT_NAME VARCHAR(80), NATURE VARCHAR(100), SORT_NO INT, CONTACT VARCHAR(120), DISHONEST INT, REMARK VARCHAR(1000), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
| ddl.put("T_BASE_EQUIPMENT", "CREATE TABLE LX.T_BASE_EQUIPMENT (ID BIGINT NOT NULL PRIMARY KEY, PARENT_ID BIGINT, NODE_LEVEL INT, NAME VARCHAR(200), CODE VARCHAR(80), SORT_NO INT, REMARK VARCHAR(1000), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
| ddl.put("T_PRODUCT_TREE", "CREATE TABLE LX.T_PRODUCT_TREE (ID BIGINT NOT NULL PRIMARY KEY, PARENT_ID BIGINT, NODE_LEVEL INT, NODE_TYPE VARCHAR(40), NAME VARCHAR(200), CODE VARCHAR(80), BASE_EQUIPMENT_ID BIGINT, SORT_NO INT, REMARK VARCHAR(1000), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
| ddl.put("T_WORK_ORDER", "CREATE TABLE LX.T_WORK_ORDER (ID BIGINT NOT NULL PRIMARY KEY, WORK_ORDER_NO VARCHAR(120), FAULT_ID BIGINT, FAULT_CODE VARCHAR(80), CONTRACTOR_ID BIGINT, CONTRACTOR_NAME VARCHAR(200), X_NO VARCHAR(40), EQUIPMENT_NAME VARCHAR(200), ENGINEERING_MAJOR VARCHAR(100), TEAM_MAJOR VARCHAR(100), PARENT_SYSTEM VARCHAR(200), REPORT_UNIT VARCHAR(200), REPORT_DATE DATE, REVIEW_TIME TIMESTAMP, DISPATCH_TIME TIMESTAMP, OWNER VARCHAR(80), REQUESTED_FINISH_DATE DATE, ENGINEERING_NAME_DESC VARCHAR(2000), REPAIR_REQUIREMENT VARCHAR(2000), FAULT_IMPACT VARCHAR(100), FAULT_NATURE VARCHAR(100), IS_SELF_REPAIR INT, IS_H_RELATED INT, IS_MONITOR_ITEM INT, MONITOR_ITEM VARCHAR(200), MONITOR_VALUE VARCHAR(200), REMARK VARCHAR(2000), COMPLETION_STATUS VARCHAR(40), SCORE INT, QUALITY_OPINION VARCHAR(2000), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
| ddl.put("T_COMPLETION_INFO", "CREATE TABLE LX.T_COMPLETION_INFO (ID BIGINT NOT NULL PRIMARY KEY, WORK_ORDER_ID BIGINT, WORK_ORDER_NO VARCHAR(120), X_NO VARCHAR(40), CONTRACTOR_NAME VARCHAR(200), ENGINEERING_NO VARCHAR(120), START_DATE DATE, FINISH_DATE DATE, CONTRACTOR_PERSON VARCHAR(100), T_INTERNAL_CONSTRUCTION VARCHAR(200), RELATED_PROJECT VARCHAR(500), DURATION_DAYS INT, PERSON_COUNT INT, ACCEPTANCE_MODE VARCHAR(100), QUALITY_INFO VARCHAR(2000), REASON_ANALYSIS VARCHAR(2000), FAULT_DESCRIPTION VARCHAR(2000), ITEM_NAME VARCHAR(500), SOLUTION VARCHAR(2000), CONSUMABLES_JSON CLOB, CONSUMABLE_COUNT INT, SOLUTIONS_JSON CLOB, SUBMIT_TIME TIMESTAMP, CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
| ddl.put("T_ACCEPTANCE", "CREATE TABLE LX.T_ACCEPTANCE (ID BIGINT NOT NULL PRIMARY KEY, WORK_ORDER_ID BIGINT, WORK_ORDER_NO VARCHAR(120), X_NO VARCHAR(40), CONTRACTOR_NAME VARCHAR(200), STAGE VARCHAR(60), STATUS VARCHAR(60), APPLY_TIME TIMESTAMP, APPLY_REMARK VARCHAR(2000), INSPECTOR_OPINION VARCHAR(2000), INSPECTOR_RESULT VARCHAR(20), INSPECTOR_TIME TIMESTAMP, LEADER_OPINION VARCHAR(2000), LEADER_RESULT VARCHAR(20), LEADER_TIME TIMESTAMP, PRINT_TIME TIMESTAMP, RATING VARCHAR(20), RATING_REMARK VARCHAR(2000), RATING_TIME TIMESTAMP, CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
| ddl.put("T_SYS_MENU", "CREATE TABLE LX.T_SYS_MENU (ID BIGINT NOT NULL PRIMARY KEY, PID BIGINT, NAME VARCHAR(100), URL VARCHAR(200), ICON VARCHAR(80), MENU_TYPE INT, PERMISSIONS VARCHAR(200), SORT_NO INT, VISIBLE INT, CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
| ddl.put("T_SYS_ROLE", "CREATE TABLE LX.T_SYS_ROLE (ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR(100), CODE VARCHAR(80), ROLE_TYPE VARCHAR(40), SORT_NO INT, REMARK VARCHAR(1000), CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
| ddl.put("T_SYS_ROLE_MENU", "CREATE TABLE LX.T_SYS_ROLE_MENU (ID BIGINT NOT NULL PRIMARY KEY, ROLE_ID BIGINT, MENU_ID BIGINT, CREATE_TIME TIMESTAMP)");
| ddl.put("T_SYS_TENANT_MENU", "CREATE TABLE LX.T_SYS_TENANT_MENU (ID BIGINT NOT NULL PRIMARY KEY, TENANT_ID BIGINT, MENU_ID BIGINT, CREATE_TIME TIMESTAMP)");
| ddl.put("T_SYS_ROLE_USER", "CREATE TABLE LX.T_SYS_ROLE_USER (ID BIGINT NOT NULL PRIMARY KEY, ROLE_ID BIGINT, USER_ID BIGINT, CREATE_TIME TIMESTAMP)");
| return ddl;
| }
| }
|
|
