Skip to content

Commit 1232549

Browse files
PkayJavaPkayJava
committed
trigger improvement
1 parent e7337c5 commit 1232549

2 files changed

Lines changed: 107 additions & 23 deletions

File tree

‎src/test/java/com/angkorteam/fintech/installation/Trigger.java‎

Lines changed: 105 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,10 @@
66
import java.sql.ResultSet;
77
import java.sql.ResultSetMetaData;
88
import java.sql.Statement;
9+
import java.util.Arrays;
10+
import java.util.HashMap;
911
import java.util.List;
12+
import java.util.Map;
1013
import java.util.Properties;
1114

1215
import javax.sql.DataSource;
@@ -16,12 +19,37 @@
1619

1720
import com.angkorteam.fintech.MifosDataSourceManager;
1821
import com.angkorteam.framework.spring.JdbcTemplate;
22+
import com.google.common.collect.Lists;
1923

2024
/**
2125
* Created by socheatkhauv on 6/21/17.
2226
*/
2327
public class Trigger {
2428

29+
private final static Map<String, List<String>> IDS;
30+
31+
static {
32+
IDS = new HashMap<>();
33+
IDS.put("c_external_service_properties", Lists.newArrayList("name", "external_service_id"));
34+
IDS.put("m_appuser_role", Lists.newArrayList("appuser_id", "role_id"));
35+
IDS.put("m_deposit_product_interest_rate_chart", Lists.newArrayList("deposit_product_id", "interest_rate_chart_id"));
36+
IDS.put("m_group_client", Lists.newArrayList("group_id", "client_id"));
37+
IDS.put("m_holiday_office", Lists.newArrayList("office_id", "holiday_id"));
38+
IDS.put("m_loan_arrears_aging", Lists.newArrayList("loan_id"));
39+
IDS.put("m_loan_paid_in_advance", Lists.newArrayList("loan_id"));
40+
IDS.put("m_product_loan_charge", Lists.newArrayList("product_loan_id", "charge_id"));
41+
IDS.put("m_role_permission", Lists.newArrayList("role_id", "permission_id"));
42+
IDS.put("m_savings_product_charge", Lists.newArrayList("savings_product_id", "charge_id"));
43+
IDS.put("m_share_product_charge", Lists.newArrayList("product_id", "charge_id"));
44+
IDS.put("m_template_m_templatemappers", Lists.newArrayList("m_template_id", "mappers_id"));
45+
// IDS.put("oauth_access_token", Lists.newArrayList("token_id",
46+
// "authentication_id", "client_id"));
47+
// IDS.put("oauth_client_details", Lists.newArrayList("client_id"));
48+
// IDS.put("oauth_refresh_token", Lists.newArrayList("token_id"));
49+
IDS.put("x_registered_table", Lists.newArrayList("registered_table_name"));
50+
IDS.put("x_table_column_code_mappings", Lists.newArrayList("column_alias_name"));
51+
}
52+
2553
public static void main(String[] args) throws Exception {
2654

2755
File fintechFile = new File(FileUtils.getUserDirectory(), ".xml/fintech.properties.xml");
@@ -43,21 +71,35 @@ public static void main(String[] args) throws Exception {
4371
dataSourceManager.setMifosUrl(mifosUrl);
4472
dataSourceManager.afterPropertiesSet();
4573

74+
boolean fileout = true;
75+
4676
String delimiter = "";
4777
String newline = " ";
4878

79+
if (fileout) {
80+
delimiter = "%%";
81+
newline = "\n";
82+
}
83+
4984
DataSource dataSource = dataSourceManager.getDataSource("default");
5085

5186
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
5287
Connection connection = dataSource.getConnection();
5388
List<String> tables = jdbcTemplate.queryForList("show tables", String.class);
89+
List<String> customTable = jdbcTemplate.queryForList("SELECT registered_table_name FROM x_registered_table", String.class);
5490
StringBuffer sql = new StringBuffer();
91+
sql.append("delimiter %%").append(newline);
5592
for (String table : tables) {
56-
System.out.println(table);
5793
if ("tbl_audit".equals(table) || "tbl_audit_value".equals(table)) {
5894
continue;
5995
}
6096

97+
if (customTable.contains(table)) {
98+
continue;
99+
}
100+
101+
System.out.println(table);
102+
61103
ResultSet resultSet = connection.createStatement().executeQuery("select * from " + table);
62104
ResultSetMetaData metaData = resultSet.getMetaData();
63105

@@ -70,13 +112,29 @@ public static void main(String[] args) throws Exception {
70112
}
71113
}
72114

115+
List<String> idFields = null;
116+
if (!hasId) {
117+
hasId = IDS.containsKey(table);
118+
idFields = IDS.get(table);
119+
if (idFields == null) {
120+
idFields = Arrays.asList("id");
121+
}
122+
} else {
123+
idFields = Lists.newArrayList("id");
124+
}
125+
126+
if (!hasId) {
127+
System.out.println(table);
128+
}
129+
73130
{
74131
StringBuffer delete = new StringBuffer();
75-
// delete.append("delimiter ").append(delimiter).append(newline);
76132
delete.append("DROP TRIGGER IF EXISTS " + table + "_d").append(delimiter).append(newline);
77-
try (Statement statement = connection.createStatement()) {
78-
statement.execute(delete.toString());
79-
delete.delete(0, delete.length());
133+
if (!fileout) {
134+
try (Statement statement = connection.createStatement()) {
135+
statement.execute(delete.toString());
136+
delete.delete(0, delete.length());
137+
}
80138
}
81139
delete.append("CREATE TRIGGER `" + table + "_d`").append(newline);
82140
delete.append("BEFORE DELETE").append(newline);
@@ -87,7 +145,14 @@ public static void main(String[] args) throws Exception {
87145
delete.append(" DECLARE _id VARCHAR(100);").append(newline);
88146
delete.append(" DECLARE _log_script TEXT;").append(newline);
89147
delete.append(" SELECT uuid() INTO _id FROM dual;").append(newline);
90-
String logScript = "CONCAT('DELETE FROM " + table + " WHERE id = ', OLD.id)";
148+
String field = idFields.get(0);
149+
String logScript = "CONCAT('DELETE FROM " + table + " WHERE " + field + " = \"', OLD." + field + ", '\"'";
150+
for (int i = 1; i < idFields.size(); i++) {
151+
String f = idFields.get(i);
152+
logScript = logScript + ", ' AND " + f + " = \"', OLD." + f + ", '\"'";
153+
}
154+
logScript = logScript + ")";
155+
91156
if (hasId) {
92157
delete.append(" SELECT " + logScript + " INTO _log_script FROM dual;").append(newline);
93158
delete.append(" INSERT INTO tbl_audit (id, log_date, log_event, log_table, log_script) VALUES (_id, now(), 'DELETE', '" + table + "', _log_script);").append(newline);
@@ -100,17 +165,21 @@ public static void main(String[] args) throws Exception {
100165
}
101166
delete.append(" END").append(delimiter).append(newline);
102167
sql.append(delete);
103-
try (Statement statement = connection.createStatement()) {
104-
statement.execute(delete.toString());
168+
if (!fileout) {
169+
try (Statement statement = connection.createStatement()) {
170+
statement.execute(delete.toString());
171+
}
105172
}
106173
}
107174

108175
{
109176
StringBuffer insert = new StringBuffer();
110177
insert.append("DROP TRIGGER IF EXISTS " + table + "_i").append(delimiter).append(newline);
111-
try (Statement statement = connection.createStatement()) {
112-
statement.execute(insert.toString());
113-
insert.delete(0, insert.length());
178+
if (!fileout) {
179+
try (Statement statement = connection.createStatement()) {
180+
statement.execute(insert.toString());
181+
insert.delete(0, insert.length());
182+
}
114183
}
115184
insert.append("CREATE TRIGGER `" + table + "_i`").append(newline);
116185
insert.append("AFTER INSERT").append(newline);
@@ -157,18 +226,21 @@ public static void main(String[] args) throws Exception {
157226
}
158227
insert.append(" END").append(delimiter).append(newline);
159228
sql.append(insert);
160-
161-
try (Statement statement = connection.createStatement()) {
162-
statement.execute(insert.toString());
229+
if (!fileout) {
230+
try (Statement statement = connection.createStatement()) {
231+
statement.execute(insert.toString());
232+
}
163233
}
164234
}
165235

166236
{
167237
StringBuffer update = new StringBuffer();
168238
update.append("DROP TRIGGER IF EXISTS " + table + "_u").append(delimiter).append(newline);
169-
try (Statement statement = connection.createStatement()) {
170-
statement.execute(update.toString());
171-
update.delete(0, update.length());
239+
if (!fileout) {
240+
try (Statement statement = connection.createStatement()) {
241+
statement.execute(update.toString());
242+
update.delete(0, update.length());
243+
}
172244
}
173245
update.append("CREATE TRIGGER `" + table + "_u`").append(newline);
174246
update.append("AFTER UPDATE").append(newline);
@@ -190,7 +262,14 @@ public static void main(String[] args) throws Exception {
190262
logScript = logScript + fieldName + " = ', " + fieldValue + ", '" + ", ";
191263
}
192264
}
193-
logScript = logScript + "WHERE id = ', NEW.id)";
265+
266+
String field = idFields.get(0);
267+
logScript = logScript + "WHERE " + field + " = \"', NEW." + field + ", '\"'";
268+
for (int i = 1; i < idFields.size(); i++) {
269+
String f = idFields.get(i);
270+
logScript = logScript + ", ' AND " + f + " = \"', OLD." + f + ", '\"'";
271+
}
272+
logScript = logScript + ")";
194273
if (hasId) {
195274
update.append(" SELECT " + logScript + " INTO _log_script FROM dual;").append(newline);
196275
update.append(" INSERT INTO tbl_audit (id, log_date, log_event, log_table, log_script) VALUES (_id, now(), 'UPDATE', '" + table + "', _log_script);").append(newline);
@@ -203,14 +282,17 @@ public static void main(String[] args) throws Exception {
203282
}
204283
update.append(" END").append(delimiter).append(newline);
205284
sql.append(update);
206-
207-
try (Statement statement = connection.createStatement()) {
208-
statement.execute(update.toString());
285+
if (!fileout) {
286+
try (Statement statement = connection.createStatement()) {
287+
statement.execute(update.toString());
288+
}
209289
}
210290
}
291+
sql.append("\n\n");
211292
resultSet.close();
212293
}
213-
214-
FileUtils.write(new File("src/test/resources/trigger.sql"), sql.toString(), "UTF-8");
294+
if (fileout) {
295+
FileUtils.write(new File("src/test/resources/trigger.sql"), sql.toString(), "UTF-8");
296+
}
215297
}
216298
}

‎src/test/resources/audit.sql‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
1+
DROP TABLE IF EXISTS `tbl_audit`;
12
CREATE TABLE `tbl_audit` (
23
`id` varchar(100) NOT NULL,
34
`log_date` datetime NOT NULL,
@@ -10,6 +11,7 @@ CREATE TABLE `tbl_audit` (
1011
KEY `tbl_audit_003` (`log_table`)
1112
);
1213

14+
DROP TABLE IF EXISTS `tbl_audit_value`;
1315
CREATE TABLE `tbl_audit_value` (
1416
`id` varchar(100) NOT NULL,
1517
`audit_id` varchar(100) DEFAULT NULL,

0 commit comments

Comments
 (0)