X-Git-Url: http://git.phpeclipse.com diff --git a/archive/net.sourceforge.phpeclipse.wiki/src/net/sourceforge/phpeclipse/wiki/sql/WikipediaDB.java b/archive/net.sourceforge.phpeclipse.wiki/src/net/sourceforge/phpeclipse/wiki/sql/WikipediaDB.java index 204d563..4ae5980 100644 --- a/archive/net.sourceforge.phpeclipse.wiki/src/net/sourceforge/phpeclipse/wiki/sql/WikipediaDB.java +++ b/archive/net.sourceforge.phpeclipse.wiki/src/net/sourceforge/phpeclipse/wiki/sql/WikipediaDB.java @@ -1,9 +1,5 @@ package net.sourceforge.phpeclipse.wiki.sql; -import java.io.BufferedReader; -import java.io.FileNotFoundException; -import java.io.FileReader; -import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; @@ -12,8 +8,12 @@ import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; +import java.util.List; -import net.sourceforge.phpeclipse.wiki.editor.LineTokenizer; +import net.sourceforge.phpeclipse.wiki.editor.WikiEditorPlugin; +import net.sourceforge.phpeclipse.wiki.internal.ConfigurationManager; +import net.sourceforge.phpeclipse.wiki.internal.IConfiguration; +import net.sourceforge.phpeclipse.wiki.preferences.Util; public class WikipediaDB { @@ -71,6 +71,29 @@ public class WikipediaDB { return list; } + public static String getFirstRow(ResultSet rs) throws SQLException { + // the order of the rows in a cursor + // are implementation dependent unless you use the SQL ORDER statement + ResultSetMetaData meta = rs.getMetaData(); + int colmax = meta.getColumnCount(); + int i; + Object o = null; + + // the result set is a cursor into the data. You can only + // point to one row at a time + // assume we are pointing to BEFORE the first row + // rs.next() points to next row and returns true + // or false if there is no next row, which breaks the loop + for (; rs.next();) { + for (i = 0; i < colmax; ++i) { + o = rs.getObject(i + 1); // Is SQL the first column is indexed + // with 1 not 0 + return o.toString(); + } + } + return null; + } + public static void main(String[] args) { WikipediaDB db = null; @@ -83,7 +106,7 @@ public class WikipediaDB { try { // do a query - ArrayList list = db.queryPrefix("Programming:PHP"); + ArrayList list = db.queryPrefixTexts("Programming:PHP"); // db.query("SELECT * FROM cur WHERE cur_title like 'Programming:PHP%'"); // WHERE num_col < 250"); for (int i = 0; i < list.size(); i++) { System.out.println(list.get(i).toString()); @@ -95,40 +118,44 @@ public class WikipediaDB { } } -// private static void readFile(WikipediaDB db, String filename) { -// FileReader fileReader; -// try { -// BufferedReader bufferedReader = new BufferedReader(new FileReader(filename)); -// // String line; -// LineTokenizer lineTokenizer = new LineTokenizer(); -// StringBuffer line = new StringBuffer(1024); -// while (lineTokenizer.getToken(line, bufferedReader)) { -// if (line.length() == 0) { -// // this should not happen -// } else { -// // try { -// // db.update("INSERT INTO wp_titles(title) VALUES('" + line + "')"); -// System.out.println(line); -// line.delete(0, line.length()); -// // addLine(line); -// // } catch (SQLException ex3) { -// //// ex3.printStackTrace(); -// // } -// } -// } -// bufferedReader.close(); -// } catch (FileNotFoundException e) { -// // ignore this -// // TODO DialogBox which asks the user if she/he likes to build new index? -// } catch (IOException e) { -// // TODO Auto-generated catch block -// e.printStackTrace(); -// } -// } - - Connection conn; - - PreparedStatement fGetPrefixTitles; + // private static void readFile(WikipediaDB db, String filename) { + // FileReader fileReader; + // try { + // BufferedReader bufferedReader = new BufferedReader(new FileReader(filename)); + // // String line; + // LineTokenizer lineTokenizer = new LineTokenizer(); + // StringBuffer line = new StringBuffer(1024); + // while (lineTokenizer.getToken(line, bufferedReader)) { + // if (line.length() == 0) { + // // this should not happen + // } else { + // // try { + // // db.update("INSERT INTO wp_titles(title) VALUES('" + line + "')"); + // System.out.println(line); + // line.delete(0, line.length()); + // // addLine(line); + // // } catch (SQLException ex3) { + // //// ex3.printStackTrace(); + // // } + // } + // } + // bufferedReader.close(); + // } catch (FileNotFoundException e) { + // // ignore this + // // TODO DialogBox which asks the user if she/he likes to build new index? + // } catch (IOException e) { + // // TODO Auto-generated catch block + // e.printStackTrace(); + // } + // } + + private final Connection conn; + + private final PreparedStatement fGetPrefixTitles; + + private final PreparedStatement fGetPrefixTexts; + + private final PreparedStatement fGetExactText; public WikipediaDB() throws Exception // note more general exception { @@ -137,16 +164,39 @@ public class WikipediaDB { // mysql-connector.jar should be in the class path or made part of the current jar Class.forName("com.mysql.jdbc.Driver"); + // determine the first SQL configuration + IConfiguration configuration = null; + try { + List allConfigsList = ConfigurationManager.getInstance().getConfigurations(); + ArrayList configsList = new ArrayList(); + for (int i = 0; i < allConfigsList.size(); i++) { + configuration = (IConfiguration) allConfigsList.get(i); + if (configuration.getType().equals(WikiEditorPlugin.WIKIPEDIA_SQL)) { + break; + } + configuration = null; + } + } catch (Throwable th) { + // + } + // connect to the database. This will load the db files and start the // database if it is not alread running. // db_file_name_prefix is used to open or create files that hold the state // of the db. // It can contain directory names relative to the // current working directory - conn = DriverManager.getConnection("jdbc:mysql://localhost/wikidb", // filenames - "root", // username - ""); // password + if (configuration != null) { + conn = DriverManager.getConnection(configuration.getURL(), configuration.getUser(), configuration.getPassword()); + } else { + // default configuration for XAMPP distribution + conn = DriverManager.getConnection("jdbc:mysql://localhost/wikidb", // filenames + "root", // username + ""); // password + } fGetPrefixTitles = conn.prepareStatement("SELECT cur_title FROM cur WHERE LOWER( cur_title ) like ?"); + fGetPrefixTexts = conn.prepareStatement("SELECT cur_text FROM cur WHERE LOWER( cur_title ) like ?"); + fGetExactText = conn.prepareStatement("SELECT cur_text FROM cur WHERE cur_title = ?"); } //use for SQL commands CREATE and SELECT @@ -170,15 +220,60 @@ public class WikipediaDB { // completely examined. } - public synchronized ArrayList queryPrefix(String prefix) throws SQLException { + public synchronized ArrayList queryPrefixTitle(String prefix) throws SQLException { fGetPrefixTitles.setString(1, prefix.toLowerCase() + '%'); ResultSet rs = null; rs = fGetPrefixTitles.executeQuery(); // run the query // do something with the result set. + ArrayList list = getResultAsString(rs); + if (list != null) { + // convert to editor format + for (int i = 0; i < list.size(); i++) { + list.set(i, Util.db2TitleLink((String) list.get(i))); + } + } + return list; + // st.close(); // NOTE!! if you close a statement the associated ResultSet is + } + + public synchronized ArrayList queryPrefixTexts(String prefix) throws SQLException { + fGetPrefixTexts.setString(1, prefix.toLowerCase() + '%'); + ResultSet rs = null; + rs = fGetPrefixTexts.executeQuery(); // run the query + // do something with the result set. return getResultAsString(rs); // st.close(); // NOTE!! if you close a statement the associated ResultSet is } + public synchronized String queryExactText(String prefix) throws SQLException { + fGetExactText.setString(1, prefix); + ResultSet rs = null; + rs = fGetExactText.executeQuery(); // run the query + // do something with the result set. + return getFirstRow(rs); + // st.close(); // NOTE!! if you close a statement the associated ResultSet is + } + + public static String getExactText(String prefix) { + WikipediaDB db = null; + + try { + db = new WikipediaDB(); + } catch (Exception ex1) { + ex1.printStackTrace(); // could not start db + return null; // bye bye + } + + try { + String text = db.queryExactText(prefix); + db.shutdown(); + return text; + } catch (SQLException ex3) { + ex3.printStackTrace(); + } + return null; + } + public void shutdown() throws SQLException { conn.close(); // if there are no other open connection