Mercurial > hg > LGServices

comparison src/main/java/de/mpiwg/gazetteer/scripts/SectionsIndex.java @ 0:3e62083dbcbf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
First commit. This project comes from LGServer. We removed the framework icefaces. Now, LGServices uses just JSP and jquery.
author "jurzua <jurzua@mpiwg-berlin.mpg.de>"
date Thu, 23 Apr 2015 15:46:01 +0200
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:3e62083dbcbf
1 package de.mpiwg.gazetteer.scripts;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.PreparedStatement;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.sql.Statement;
9 import java.util.HashMap;
10 import java.util.Map;
11
12
13
14 /**
15 *
16 * mvn exec:java -Dexec.mainClass="de.mpiwg.gazetteer.scripts.SectionsIndex" -Dexec.args="Gazetteer root admin"
17 *
18 * @author jurzua
19 *
20 */
21 public class SectionsIndex {
22
23 private static String TABLE_NAME = "sections_index";
24
25 private static String DROP_TABLE =
26 "DROP TABLE IF EXISTS `"+ TABLE_NAME +"`;";
27
28 private static String CREATE_TABLE =
29 "CREATE TABLE `" + TABLE_NAME + "` (`id` int(10) NOT NULL AUTO_INCREMENT, "
30 + "`name` varchar(255) NOT NULL, "
31 + "`books_id` varchar(5) NOT NULL, "
32 + "`section_after` varchar(255) NOT NULL, "
33 + "`start_page` int(5) NOT NULL, `end_page` int(5) NOT NULL, "
34 + "`level` int(5) NOT NULL, "
35 + "`split_from` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) "
36 + "ENGINE=MyISAM DEFAULT CHARSET=utf8;";
37
38 private static String SELECT_SECTIONS =
39 "SELECT * FROM sections";
40
41 private static String SECTIONS_COUNT =
42 "SELECT count(*) FROM sections";
43
44 private static String SELECT_SECTIONS_VERSIONS =
45 "SELECT * from sections_versions";
46
47
48 private static void execute(String dbName, String dbUser, String dbPwd){
49
50 Connection conn = null;
51
52
53 try {
54 //Class.forName("com.mysql.jdbc.Driver").newInstance();
55 String dbURL = "jdbc:mysql://localhost/"+ dbName +"?characterEncoding=UTF-8";
56 conn = DriverManager.getConnection(dbURL, dbUser, dbPwd);
57
58 createTable(conn);
59 copyFromSectionsTable(conn);
60 replaceFromSectionsRevisions(conn);
61
62 } catch (Exception e) {
63 e.printStackTrace();
64 }finally{
65 try {
66 conn.close();
67 } catch (SQLException e) {
68 e.printStackTrace();
69 }
70 }
71 }
72
73 private static void replaceFromSectionsRevisions(Connection conn){
74 Statement stmt = null;
75 ResultSet rs = null;
76 try {
77
78 stmt = conn.createStatement();
79 rs = stmt.executeQuery(SELECT_SECTIONS_VERSIONS);
80
81 Map<String, SectionVersion> mapVersions = new HashMap<String, SectionsIndex.SectionVersion>();
82
83 while(rs.next()){
84 SectionVersion version = new SectionVersion(rs);
85 if(!mapVersions.containsKey(version.books_id)){
86 mapVersions.put(version.books_id, version);
87 }else if(version.version > mapVersions.get(version.books_id).version){
88 mapVersions.put(version.books_id, version);
89 }
90 }
91
92 for(SectionVersion sectionVersion : mapVersions.values()){
93
94 String query = "SELECT * FROM sections_revisions WHERE "
95 + "books_id = '"+ sectionVersion.books_id +"' AND "
96 + "versions_id = " + sectionVersion.id + " AND "
97 + "deleted = 0";
98 stmt = conn.createStatement();
99 rs = stmt.executeQuery(query);
100
101 while(rs.next()){
102 Section section = new Section(rs, false);
103
104 if(section.deleted == 0){
105 if(section.id == -1){
106
107 PreparedStatement stmt0 = section.getInsertStatementWithoutId(conn);
108 stmt0.executeUpdate();
109 ResultSet rs0 = stmt0.getGeneratedKeys();
110 rs0.next();
111 int newSectionId = rs0.getInt(1);
112
113 //Updating sections_revisions (from -1 to new id)
114 String sqlUpdateRevision = "UPDATE sections_revisions SET sections_id = ? WHERE id = ?";
115 PreparedStatement stmt1 = conn.prepareStatement(sqlUpdateRevision);
116 stmt1.setInt(1, newSectionId);
117 stmt1.setInt(2, section.sectionsRevisionsId);
118 int rowsUpdated = stmt1.executeUpdate();
119
120 System.out.println("Changing revision section with id: " + section.sectionsRevisionsId + " from -1 to " + newSectionId + ". Rows updated: " + rowsUpdated);
121 //System.out.print("#");
122 }else{
123 PreparedStatement stm = section.getUpdateStatement(conn);
124 stm.execute();
125 }
126 }
127 }
128 }
129
130 System.out.println();
131
132 } catch (Exception e) {
133 e.printStackTrace();
134 }
135 }
136
137 private static void copyFromSectionsTable(Connection conn){
138 Statement stmt = null;
139 ResultSet rs = null;
140 try {
141 /*
142 stmt = conn.createStatement();
143 rs = stmt.executeQuery(SECTIONS_COUNT);
144 rs.next();
145 int count = rs.getInt(1);
146 */
147 stmt = conn.createStatement();
148 rs = stmt.executeQuery(SELECT_SECTIONS);
149
150 int index = 0;
151 while(rs.next()){
152 Section section = new Section(rs, true);
153
154 //stmt = conn.createStatement();
155 //stmt.executeUpdate(section.getInsertStatement());
156 PreparedStatement stm = section.getInsertStatementWithId(conn);
157 stm.execute();
158
159 if(index % 100 == 0){
160 System.out.print("*");
161 }
162 index++;
163 }
164 System.out.println();
165
166 } catch (Exception e) {
167 e.printStackTrace();
168 }
169 }
170
171
172 private static void createTable(Connection conn){
173 Statement stmt = null;
174
175 try {
176 stmt = conn.createStatement();
177 int rows = stmt.executeUpdate(DROP_TABLE);
178 System.out.println("DROP table: " + rows);
179
180 } catch (Exception e) {
181 System.err.println("\n" + DROP_TABLE);
182 e.printStackTrace();
183 }
184
185 try {
186 stmt = conn.createStatement();
187 int rows = stmt.executeUpdate(CREATE_TABLE);
188 System.out.println("CREATE table: " + rows);
189 } catch (Exception e) {
190 System.err.println("\n" + CREATE_TABLE);
191 e.printStackTrace();
192 }
193 }
194
195
196 private static void printSetting(){
197
198 int mb = 1024*1024;
199
200 //Getting the runtime reference from system
201 Runtime runtime = Runtime.getRuntime();
202
203 System.out.println("##### Heap utilization statistics [MB] #####");
204
205 //Print used memory
206 System.out.println("Used Memory:"
207 + (runtime.totalMemory() - runtime.freeMemory()) / mb);
208
209 //Print free memory
210 System.out.println("Free Memory:"
211 + runtime.freeMemory() / mb);
212
213 //Print total available memory
214 System.out.println("Total Memory:" + runtime.totalMemory() / mb);
215
216 //Print Maximum available memory
217 System.out.println("Max Memory:" + runtime.maxMemory() / mb);
218 }
219
220 public static void main(String[] args){
221
222 printSetting();
223
224 String dbName = args[0];
225 String dbUser = args[1];
226 String dbPwd = args[2];
227
228 SectionsIndex.execute(dbName, dbUser, dbPwd);
229
230 System.exit(0);
231 }
232 //table: sections_revisions
233 private static class SectionVersion{
234
235 private int id;
236 private int version;
237 private String editor;
238 private String books_id;
239
240 public SectionVersion(ResultSet rs) throws SQLException{
241 this.id = rs.getInt("id");
242 this.version = rs.getInt("version");
243 this.editor = rs.getString("editor");
244 this.books_id = rs.getString("books_id");
245 }
246
247 public int getVersion(){
248 return this.version;
249 }
250 }
251
252 private static class Section{
253 private int id;
254 private String name;
255 private String books_id;
256 private String section_after;
257 private int start_page;
258 private int end_page;
259 private int level;
260 private int split_from;
261 //private int sections_id;
262
263 //tables for the revisions_sections
264 private int sectionsRevisionsId;
265 private int deleted;
266
267 public Section(ResultSet rs, boolean isSectionTable) throws SQLException{
268
269 this.name = rs.getString("name");
270 this.books_id = rs.getString("books_id");
271 this.section_after = rs.getString("section_after");
272 this.start_page = rs.getInt("start_page");
273 this.end_page = rs.getInt("end_page");
274 this.level = rs.getInt("level");
275 this.split_from = rs.getInt("split_from");
276 if(isSectionTable){
277 this.id = rs.getInt("id");
278 }else{
279 this.id = rs.getInt("sections_id");
280 this.sectionsRevisionsId = rs.getInt("id");
281 this.deleted = rs.getInt("deleted");
282 }
283 }
284
285 public PreparedStatement getInsertStatementWithId(Connection conn) throws SQLException{
286
287 String sql = "INSERT INTO " + TABLE_NAME + " " +
288 "(id, name, books_id, section_after, start_page, end_page, level, split_from) VALUES" +
289 "(?,?,?,?,?,?,?,?)";
290 PreparedStatement stm = conn.prepareStatement(sql);
291
292 stm.setInt(1, id);
293 stm.setString(2, name);
294 stm.setString(3, books_id);
295 stm.setString(4, section_after);
296 stm.setInt(5, start_page);
297 stm.setInt(6, end_page);
298 stm.setInt(7, level);
299 stm.setInt(8, split_from);
300
301 return stm;
302 }
303
304 public PreparedStatement getInsertStatementWithoutId(Connection conn) throws SQLException{
305
306 String sql = "INSERT INTO " + TABLE_NAME + " " +
307 "(name, books_id, section_after, start_page, end_page, level, split_from) VALUES" +
308 "(?,?,?,?,?,?,?)";
309 PreparedStatement stm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
310
311 stm.setString(1, name);
312 stm.setString(2, books_id);
313 stm.setString(3, section_after);
314 stm.setInt(4, start_page);
315 stm.setInt(5, end_page);
316 stm.setInt(6, level);
317 stm.setInt(7, split_from);
318
319 return stm;
320 }
321
322 public PreparedStatement getUpdateStatement(Connection conn) throws SQLException{
323 String s = "UPDATE " + TABLE_NAME + " " +
324 "SET id = ?, name = ?, books_id = ?, section_after = ?, start_page = ?, end_page = ?, level = ?, split_from = ? " +
325 "WHERE id = ?";
326 PreparedStatement stm = conn.prepareStatement(s);
327
328 stm.setInt(1, id);
329 stm.setString(2, name);
330 stm.setString(3, books_id);
331 stm.setString(4, section_after);
332 stm.setInt(5, start_page);
333 stm.setInt(6, end_page);
334 stm.setInt(7, level);
335 stm.setInt(8, split_from);
336 stm.setInt(9, id);
337
338 return stm;
339 }
340 /*
341 public String getUpdateStatement(){
342
343 String statement = "UPDATE " + TABLE_NAME + " ";
344 statement += "WHERE id = " + sections_id + " ";
345 //statement += " (id, name, books_id, section_after, start_page, end_page, level, split_from) ";
346 statement += " VALUES (" +
347 sections_id + ", '" +
348 name + "', '"+
349 books_id +"', '"+
350 section_after+"', "+
351 start_page +", " +
352 end_page + ", "+
353 level +", " +
354 split_from + ")";
355
356 return statement;
357 }*/
358
359 }
360 }