1 package fr.ifremer.tutti.ui.swing.content.actions;
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 import java.sql.Connection;
29 import java.sql.DriverManager;
30 import java.sql.PreparedStatement;
31 import java.sql.ResultSet;
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47 public class FixesBatchRankOrder {
48
49 protected boolean commit;
50
51 static public void main(String... args) throws Exception {
52 if (args.length <= 0 || ("-nocommit".equals(args[0]) && args.length <= 1)) {
53 System.err.println("usage: FixesBatchRankOrder -nocommit <db file path> [login] [password]");
54 } else {
55 int i=0;
56 boolean commit = true;
57 if ("-nocommit".equals(args[0])) {
58 commit = false;
59 i++;
60 }
61 String dbPath = args[i++];
62 String login = args.length > i ? args[i++] : "SA";
63 String password = args.length > i ? args[i++] : "";
64 new FixesBatchRankOrder(commit).doAction(dbPath, login, password);
65 }
66 }
67
68 public FixesBatchRankOrder() {
69 this(true);
70 }
71
72
73
74
75 public FixesBatchRankOrder(boolean commit) {
76 this.commit = commit;
77 }
78
79
80
81
82
83
84
85
86
87
88
89 public void doAction(String dbPath, String login, String password) throws Exception {
90 System.out.println("FixesRankOrder for db: " + dbPath);
91
92 Class.forName("org.hsqldb.jdbcDriver" );
93 try (Connection c = DriverManager.getConnection(
94 String.format("jdbc:hsqldb:file:%s;ifexists=true;shutdown=true", dbPath),
95 login,
96 password)) {
97 doAction(c);
98 }
99 }
100
101
102
103
104
105
106
107 public void doAction(Connection c) throws Exception {
108 boolean autoCommit = c.getAutoCommit();
109 try {
110 c.setAutoCommit(false);
111
112
113 PreparedStatement catchWithError = c.prepareStatement(
114 "SELECT b1.parent_batch_fk as pid, count(b1.id) as conflict"
115 + " FROM BATCH b1, BATCH b2"
116 + " WHERE b1.parent_batch_fk = b2.parent_batch_fk and b1.raNK_ORDER=b2.rank_order and b1.id != b2.id group by b1.parent_batch_fk"
117 + " ORDER BY b1.parent_batch_fk");
118 ResultSet rs = catchWithError.executeQuery();
119 while (rs.next()) {
120 int id = rs.getInt("pid");
121 int conflict = rs.getInt("conflict");
122 fixesRankOrder(c, id, conflict);
123 }
124 if (commit) {
125 c.commit();
126 System.out.println("Modification commited");
127 } else {
128 c.rollback();
129 System.out.println("Modification reverted");
130 }
131 } finally {
132 c.setAutoCommit(autoCommit);
133 }
134 }
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149 protected int fixesRankOrder(Connection c, int id, int conflict) throws Exception {
150 System.out.println(String.format("FixesRankOrder catch id: %s with %s conflicts ", id, conflict));
151
152 String query = "update batch set rank_order = (" +
153 "select count(id) from batch b2 where batch.parent_batch_fk = b2.parent_batch_fk and convert(concat(concat(b2.rank_order, '.'), b2.id), DECIMAL) < convert(concat(concat(batch.rank_order, '.'), batch.id), DECIMAL) ) +1" +
154 "where PARENT_BATCH_FK = ?";
155
156
157 PreparedStatement update = c.prepareStatement(query);
158 update.setInt(1, id);
159 int result = update.executeUpdate();
160
161 System.out.println(String.format("..... %s children reordered", result));
162 return result;
163 }
164 }