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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
| 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 static final String[] AUDIT_TABLES = {
| "T_FAULT", "T_ENGINEERING_MAJOR", "T_TEAM_MAJOR", "T_CONTRACTOR",
| "T_BASE_EQUIPMENT", "T_PRODUCT_TREE", "T_WORK_ORDER", "T_COMPLETION_INFO",
| "T_ACCEPTANCE", "T_SYS_MENU", "T_SYS_ROLE", "T_SYS_ROLE_MENU",
| "T_SYS_TENANT_MENU", "T_SYS_ROLE_USER", "T_SYS_LOGIN_LOG", "T_SYS_OPERATION_LOG",
| "T_SYS_ERROR_LOG", "T_SYS_DEPT_EXT", "T_SYS_USER_EXT"
| };
| 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());
| }
| }
| ensureAuditColumns();
| 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)");
| ensureIndex("IX_T_SYS_LOGIN_LOG_TIME", "CREATE INDEX LX.IX_T_SYS_LOGIN_LOG_TIME ON LX.T_SYS_LOGIN_LOG(LOGIN_TIME)");
| ensureIndex("IX_T_SYS_OPERATION_LOG_TIME", "CREATE INDEX LX.IX_T_SYS_OPERATION_LOG_TIME ON LX.T_SYS_OPERATION_LOG(CREATE_TIME)");
| ensureIndex("IX_T_SYS_ERROR_LOG_TIME", "CREATE INDEX LX.IX_T_SYS_ERROR_LOG_TIME ON LX.T_SYS_ERROR_LOG(ERROR_TIME)");
| ensureIndex("IX_T_SYS_ROLE_USER_USER", "CREATE INDEX LX.IX_T_SYS_ROLE_USER_USER ON LX.T_SYS_ROLE_USER(USER_ID)");
| }
|
| private void ensureAuditColumns() {
| for (String tableName : AUDIT_TABLES) {
| if (!tableExists(tableName)) {
| continue;
| }
| ensureColumn(tableName, "CREATE_BY", "ALTER TABLE LX." + tableName + " ADD CREATE_BY VARCHAR(120)");
| ensureColumn(tableName, "UPDATE_BY", "ALTER TABLE LX." + tableName + " ADD UPDATE_BY VARCHAR(120)");
| ensureColumn(tableName, "DEL_FLAG", "ALTER TABLE LX." + tableName + " ADD DEL_FLAG INT DEFAULT 0");
| jdbcTemplate.update("UPDATE LX." + tableName + " SET DEL_FLAG = 0 WHERE DEL_FLAG IS NULL");
| }
| }
| 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)");
| ddl.put("T_SYS_DEPT_EXT", "CREATE TABLE LX.T_SYS_DEPT_EXT (DEPT_ID BIGINT NOT NULL PRIMARY KEY, RESPONSIBLE_X_NO VARCHAR(40), CREATE_BY VARCHAR(120), UPDATE_BY VARCHAR(120), DEL_FLAG INT DEFAULT 0, CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
| ddl.put("T_SYS_USER_EXT", "CREATE TABLE LX.T_SYS_USER_EXT (USER_ID BIGINT NOT NULL PRIMARY KEY, ENGINEERING_MAJOR VARCHAR(100), TEAM_MAJOR VARCHAR(100), RESPONSIBLE_X_NO VARCHAR(40), CREATE_BY VARCHAR(120), UPDATE_BY VARCHAR(120), DEL_FLAG INT DEFAULT 0, CREATE_TIME TIMESTAMP, UPDATE_TIME TIMESTAMP)");
| ddl.put("T_SYS_LOGIN_LOG", "CREATE TABLE LX.T_SYS_LOGIN_LOG (ID BIGINT NOT NULL PRIMARY KEY, USER_ID BIGINT, USER_NAME VARCHAR(120), NICK_NAME VARCHAR(120), DEPT_NAME VARCHAR(120), LOGIN_IP VARCHAR(200), STATUS VARCHAR(20), MESSAGE VARCHAR(500), LOGIN_TIME TIMESTAMP)");
| ddl.put("T_SYS_OPERATION_LOG", "CREATE TABLE LX.T_SYS_OPERATION_LOG (ID BIGINT NOT NULL PRIMARY KEY, USER_ID BIGINT, USER_NAME VARCHAR(120), NICK_NAME VARCHAR(120), MODULE VARCHAR(100), OPERATION_TYPE VARCHAR(40), TARGET_TYPE VARCHAR(100), TARGET_ID VARCHAR(80), DESCRIPTION VARCHAR(500), REQUEST_METHOD VARCHAR(10), REQUEST_URI VARCHAR(300), RESULT VARCHAR(20), CREATE_TIME TIMESTAMP)");
| ddl.put("T_SYS_ERROR_LOG", "CREATE TABLE LX.T_SYS_ERROR_LOG (ID BIGINT NOT NULL PRIMARY KEY, USER_ID BIGINT, USER_NAME VARCHAR(120), NICK_NAME VARCHAR(120), MODULE VARCHAR(100), REQUEST_METHOD VARCHAR(10), REQUEST_URI VARCHAR(500), EXCEPTION_TYPE VARCHAR(300), MESSAGE VARCHAR(2000), STACK_TRACE CLOB, ERROR_TIME TIMESTAMP)");
| return ddl;
| }
| }
|
|
