# HG changeset patch # User "jurzua " # Date 1426695825 -3600 # Node ID 964fc53abeec693fa9f55e767c1246ac56832a39 # Parent 10b6e9d96d59340cf12f45300e46be48d0b0713f Script to create table sections_index and to solve problems of inconsistency. diff -r 10b6e9d96d59 -r 964fc53abeec src/main/java/de/mpiwg/gazetteer/scripts/SectionsIndex.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/main/java/de/mpiwg/gazetteer/scripts/SectionsIndex.java Wed Mar 18 17:23:45 2015 +0100 @@ -0,0 +1,330 @@ +package de.mpiwg.gazetteer.scripts; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.HashMap; +import java.util.Map; + + + +/** + * + * mvn exec:java -Dexec.mainClass="de.mpiwg.gazetteer.scripts.SectionsIndex" -Dexec.args="Gazetteer root admin" + * + * @author jurzua + * + */ +public class SectionsIndex { + + private static String TABLE_NAME = "sections_index"; + + private static String DROP_TABLE = + "DROP TABLE IF EXISTS `"+ TABLE_NAME +"`;"; + + private static String CREATE_TABLE = + "CREATE TABLE `" + TABLE_NAME + "` (`id` int(10) NOT NULL AUTO_INCREMENT, " + + "`name` varchar(255) NOT NULL, " + + "`books_id` varchar(5) NOT NULL, " + + "`section_after` varchar(255) NOT NULL, " + + "`start_page` int(5) NOT NULL, `end_page` int(5) NOT NULL, " + + "`level` int(5) NOT NULL, " + + "`split_from` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) " + + "ENGINE=MyISAM DEFAULT CHARSET=utf8;"; + + private static String SELECT_SECTIONS = + "SELECT * FROM sections"; + + private static String SECTIONS_COUNT = + "SELECT count(*) FROM sections"; + + private static String SELECT_SECTIONS_VERSIONS = + "SELECT * from sections_versions"; + + + private static void execute(String dbName, String dbUser, String dbPwd){ + + Connection conn = null; + + + try { + //Class.forName("com.mysql.jdbc.Driver").newInstance(); + String dbURL = "jdbc:mysql://localhost/"+ dbName +"?characterEncoding=UTF-8"; + conn = DriverManager.getConnection(dbURL, dbUser, dbPwd); + + createTable(conn); + copyFromSectionsTable(conn); + replaceFromSectionsRevisions(conn); + + } catch (Exception e) { + e.printStackTrace(); + } + } + + private static void replaceFromSectionsRevisions(Connection conn){ + Statement stmt = null; + ResultSet rs = null; + try { + + stmt = conn.createStatement(); + rs = stmt.executeQuery(SELECT_SECTIONS_VERSIONS); + + Map mapVersions = new HashMap(); + + while(rs.next()){ + SectionVersion version = new SectionVersion(rs); + if(!mapVersions.containsKey(version.books_id)){ + mapVersions.put(version.books_id, version); + }else if(version.version > mapVersions.get(version.books_id).version){ + mapVersions.put(version.books_id, version); + } + } + + for(SectionVersion sectionVersion : mapVersions.values()){ + + String query = "SELECT * FROM sections_revisions WHERE " + + "books_id = '"+ sectionVersion.books_id +"' AND " + + "versions_id = " + sectionVersion.id + " AND " + + "deleted = 0"; + stmt = conn.createStatement(); + rs = stmt.executeQuery(query); + + while(rs.next()){ + Section section = new Section(rs, false); + + if(section.deleted == 0){ + if(section.id == -1){ + + PreparedStatement stmt0 = section.getInsertStatementWithoutId(conn); + stmt0.executeUpdate(); + ResultSet rs0 = stmt0.getGeneratedKeys(); + rs0.next(); + int newSectionId = rs0.getInt(1); + + //Updating sections_revisions (from -1 to new id) + String sqlUpdateRevision = "UPDATE sections_revisions SET sections_id = ? WHERE id = ?"; + PreparedStatement stmt1 = conn.prepareStatement(sqlUpdateRevision); + stmt1.setInt(1, newSectionId); + stmt1.setInt(2, section.sectionsRevisionsId); + int rowsUpdated = stmt1.executeUpdate(); + + System.out.println("Changing revision section with id: " + section.sectionsRevisionsId + " from -1 to " + newSectionId + ". Rows updated: " + rowsUpdated); + //System.out.print("#"); + }else{ + PreparedStatement stm = section.getUpdateStatement(conn); + stm.execute(); + } + } + } + } + + System.out.println(); + + + + } catch (Exception e) { + e.printStackTrace(); + } + } + + private static void copyFromSectionsTable(Connection conn){ + Statement stmt = null; + ResultSet rs = null; + try { + /* + stmt = conn.createStatement(); + rs = stmt.executeQuery(SECTIONS_COUNT); + rs.next(); + int count = rs.getInt(1); + */ + stmt = conn.createStatement(); + rs = stmt.executeQuery(SELECT_SECTIONS); + + int index = 0; + while(rs.next()){ + Section section = new Section(rs, true); + + //stmt = conn.createStatement(); + //stmt.executeUpdate(section.getInsertStatement()); + PreparedStatement stm = section.getInsertStatementWithId(conn); + stm.execute(); + + if(index % 100 == 0){ + System.out.print("*"); + } + index++; + } + System.out.println(); + + } catch (Exception e) { + e.printStackTrace(); + } + } + + + private static void createTable(Connection conn){ + Statement stmt = null; + + try { + stmt = conn.createStatement(); + int rows = stmt.executeUpdate(DROP_TABLE); + System.out.println("DROP table: " + rows); + + } catch (Exception e) { + System.err.println("\n" + DROP_TABLE); + e.printStackTrace(); + } + + try { + stmt = conn.createStatement(); + int rows = stmt.executeUpdate(CREATE_TABLE); + System.out.println("CREATE table: " + rows); + } catch (Exception e) { + System.err.println("\n" + CREATE_TABLE); + e.printStackTrace(); + } + } + + + public static void main(String[] args){ + + String dbName = args[0]; + String dbUser = args[1]; + String dbPwd = args[2]; + + SectionsIndex.execute(dbName, dbUser, dbPwd); + + System.exit(0); + } + //table: sections_revisions + private static class SectionVersion{ + + private int id; + private int version; + private String editor; + private String books_id; + + public SectionVersion(ResultSet rs) throws SQLException{ + this.id = rs.getInt("id"); + this.version = rs.getInt("version"); + this.editor = rs.getString("editor"); + this.books_id = rs.getString("books_id"); + } + + public int getVersion(){ + return this.version; + } + } + + private static class Section{ + private int id; + private String name; + private String books_id; + private String section_after; + private int start_page; + private int end_page; + private int level; + private int split_from; + //private int sections_id; + + //tables for the revisions_sections + private int sectionsRevisionsId; + private int deleted; + + public Section(ResultSet rs, boolean isSectionTable) throws SQLException{ + + this.name = rs.getString("name"); + this.books_id = rs.getString("books_id"); + this.section_after = rs.getString("section_after"); + this.start_page = rs.getInt("start_page"); + this.end_page = rs.getInt("end_page"); + this.level = rs.getInt("level"); + this.split_from = rs.getInt("split_from"); + if(isSectionTable){ + this.id = rs.getInt("id"); + }else{ + this.id = rs.getInt("sections_id"); + this.sectionsRevisionsId = rs.getInt("id"); + this.deleted = rs.getInt("deleted"); + } + } + + public PreparedStatement getInsertStatementWithId(Connection conn) throws SQLException{ + + String sql = "INSERT INTO " + TABLE_NAME + " " + + "(id, name, books_id, section_after, start_page, end_page, level, split_from) VALUES" + + "(?,?,?,?,?,?,?,?)"; + PreparedStatement stm = conn.prepareStatement(sql); + + stm.setInt(1, id); + stm.setString(2, name); + stm.setString(3, books_id); + stm.setString(4, section_after); + stm.setInt(5, start_page); + stm.setInt(6, end_page); + stm.setInt(7, level); + stm.setInt(8, split_from); + + return stm; + } + + public PreparedStatement getInsertStatementWithoutId(Connection conn) throws SQLException{ + + String sql = "INSERT INTO " + TABLE_NAME + " " + + "(name, books_id, section_after, start_page, end_page, level, split_from) VALUES" + + "(?,?,?,?,?,?,?)"; + PreparedStatement stm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); + + stm.setString(1, name); + stm.setString(2, books_id); + stm.setString(3, section_after); + stm.setInt(4, start_page); + stm.setInt(5, end_page); + stm.setInt(6, level); + stm.setInt(7, split_from); + + return stm; + } + + public PreparedStatement getUpdateStatement(Connection conn) throws SQLException{ + String s = "UPDATE " + TABLE_NAME + " " + + "SET id = ?, name = ?, books_id = ?, section_after = ?, start_page = ?, end_page = ?, level = ?, split_from = ? " + + "WHERE id = ?"; + PreparedStatement stm = conn.prepareStatement(s); + + stm.setInt(1, id); + stm.setString(2, name); + stm.setString(3, books_id); + stm.setString(4, section_after); + stm.setInt(5, start_page); + stm.setInt(6, end_page); + stm.setInt(7, level); + stm.setInt(8, split_from); + stm.setInt(9, id); + + return stm; + } + /* + public String getUpdateStatement(){ + + String statement = "UPDATE " + TABLE_NAME + " "; + statement += "WHERE id = " + sections_id + " "; + //statement += " (id, name, books_id, section_after, start_page, end_page, level, split_from) "; + statement += " VALUES (" + + sections_id + ", '" + + name + "', '"+ + books_id +"', '"+ + section_after+"', "+ + start_page +", " + + end_page + ", "+ + level +", " + + split_from + ")"; + + return statement; + }*/ + + } +} diff -r 10b6e9d96d59 -r 964fc53abeec src/main/resources/config.properties --- a/src/main/resources/config.properties Tue Mar 17 10:51:19 2015 +0100 +++ b/src/main/resources/config.properties Wed Mar 18 17:23:45 2015 +0100 @@ -6,5 +6,5 @@ extraction_interface=http://localgazetteers-dev/extraction-interface dvn_server=http://localgazetteers-dev/dvn #root_server=http://localgazetteers.mpiwg-berlin.mpg.de:8080/gazetteer-server -#root_server=http://localhost:8080/LGServer -root_server=http://localgazetteers-dev/LGServer +root_server=http://localhost:8080/LGServer +#root_server=http://localgazetteers-dev/LGServer