--- /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<String, SectionVersion> mapVersions = new HashMap<String, SectionsIndex.SectionVersion>();
+			
+			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;
+		}*/
+		
+	}
+}

--- 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