1 package net.sourceforge.phpeclipse.wiki.sql;
3 import java.io.BufferedReader;
4 import java.io.FileNotFoundException;
5 import java.io.FileReader;
6 import java.io.IOException;
7 import java.sql.Connection;
8 import java.sql.DriverManager;
9 import java.sql.PreparedStatement;
10 import java.sql.ResultSet;
11 import java.sql.ResultSetMetaData;
12 import java.sql.SQLException;
13 import java.sql.Statement;
14 import java.util.ArrayList;
15 import java.util.List;
17 import net.sourceforge.phpeclipse.wiki.editor.LineTokenizer;
18 import net.sourceforge.phpeclipse.wiki.editor.WikiEditorPlugin;
19 import net.sourceforge.phpeclipse.wiki.internal.ConfigurationManager;
20 import net.sourceforge.phpeclipse.wiki.internal.IConfiguration;
22 public class WikipediaDB {
24 public static void dump(ResultSet rs) throws SQLException {
26 // the order of the rows in a cursor
27 // are implementation dependent unless you use the SQL ORDER statement
28 ResultSetMetaData meta = rs.getMetaData();
29 int colmax = meta.getColumnCount();
33 // the result set is a cursor into the data. You can only
34 // point to one row at a time
35 // assume we are pointing to BEFORE the first row
36 // rs.next() points to next row and returns true
37 // or false if there is no next row, which breaks the loop
39 for (i = 0; i < colmax; ++i) {
40 o = rs.getObject(i + 1); // Is SQL the first column is indexed
42 System.out.print(o.toString() + " ");
45 System.out.println(" ");
49 public static ArrayList getResultAsString(ResultSet rs) throws SQLException {
50 ArrayList list = new ArrayList();
51 int maxProposals = 500;
52 // the order of the rows in a cursor
53 // are implementation dependent unless you use the SQL ORDER statement
54 ResultSetMetaData meta = rs.getMetaData();
55 int colmax = meta.getColumnCount();
59 // the result set is a cursor into the data. You can only
60 // point to one row at a time
61 // assume we are pointing to BEFORE the first row
62 // rs.next() points to next row and returns true
63 // or false if there is no next row, which breaks the loop
65 for (i = 0; i < colmax; ++i) {
66 o = rs.getObject(i + 1); // Is SQL the first column is indexed
68 list.add(o.toString());
70 if (maxProposals <= 0) {
78 public static void main(String[] args) {
79 WikipediaDB db = null;
82 db = new WikipediaDB();
83 } catch (Exception ex1) {
84 ex1.printStackTrace(); // could not start db
90 ArrayList list = db.queryPrefix("Programming:PHP");
91 // db.query("SELECT * FROM cur WHERE cur_title like 'Programming:PHP%'"); // WHERE num_col < 250");
92 for (int i = 0; i < list.size(); i++) {
93 System.out.println(list.get(i).toString());
97 } catch (SQLException ex3) {
98 ex3.printStackTrace();
102 // private static void readFile(WikipediaDB db, String filename) {
103 // FileReader fileReader;
105 // BufferedReader bufferedReader = new BufferedReader(new FileReader(filename));
107 // LineTokenizer lineTokenizer = new LineTokenizer();
108 // StringBuffer line = new StringBuffer(1024);
109 // while (lineTokenizer.getToken(line, bufferedReader)) {
110 // if (line.length() == 0) {
111 // // this should not happen
114 // // db.update("INSERT INTO wp_titles(title) VALUES('" + line + "')");
115 // System.out.println(line);
116 // line.delete(0, line.length());
118 // // } catch (SQLException ex3) {
119 // //// ex3.printStackTrace();
123 // bufferedReader.close();
124 // } catch (FileNotFoundException e) {
126 // // TODO DialogBox which asks the user if she/he likes to build new index?
127 // } catch (IOException e) {
128 // // TODO Auto-generated catch block
129 // e.printStackTrace();
135 PreparedStatement fGetPrefixTitles;
137 public WikipediaDB() throws Exception // note more general exception
140 // Load the Database Engine JDBC driver
141 // mysql-connector.jar should be in the class path or made part of the current jar
142 Class.forName("com.mysql.jdbc.Driver");
144 // determine the foirst SQL configuration
145 List allConfigsList = ConfigurationManager.getInstance().getConfigurations();
146 ArrayList configsList = new ArrayList();
147 IConfiguration configuration = null;
148 for (int i = 0; i < allConfigsList.size(); i++) {
149 configuration = (IConfiguration) allConfigsList.get(i);
150 if (configuration.getType().equals(WikiEditorPlugin.WIKIPEDIA_SQL)) {
153 configuration = null;
156 // connect to the database. This will load the db files and start the
157 // database if it is not alread running.
158 // db_file_name_prefix is used to open or create files that hold the state
160 // It can contain directory names relative to the
161 // current working directory
162 if (configuration != null) {
163 conn = DriverManager.getConnection(configuration.getURL(), configuration.getUser(), configuration.getPassword());
165 // default configuration for XAMPP distribution
166 conn = DriverManager.getConnection("jdbc:mysql://localhost/wikidb", // filenames
170 fGetPrefixTitles = conn.prepareStatement("SELECT cur_title FROM cur WHERE LOWER( cur_title ) like ?");
173 //use for SQL commands CREATE and SELECT
174 public synchronized void query(String expression) throws SQLException {
179 st = conn.createStatement(); // statement objects can be reused with
180 // repeated calls to execute but we
181 // choose to make a new one each time
182 rs = st.executeQuery(expression); // run the query
184 // do something with the result set.
186 st.close(); // NOTE!! if you close a statement the associated ResultSet is
188 // so you should copy the contents to some other object.
189 // the result set is invalidated also if you recycle an Statement
190 // and try to execute some other query before the result set has been
191 // completely examined.
194 public synchronized ArrayList queryPrefix(String prefix) throws SQLException {
195 fGetPrefixTitles.setString(1, prefix.toLowerCase() + '%');
197 rs = fGetPrefixTitles.executeQuery(); // run the query
198 // do something with the result set.
199 return getResultAsString(rs);
200 // st.close(); // NOTE!! if you close a statement the associated ResultSet is
203 public void shutdown() throws SQLException {
205 conn.close(); // if there are no other open connection
206 // db writes out to files and shuts down
207 // this happens anyway at garbage collection
211 //use for SQL commands DROP and INSERT and UPDATE
212 public synchronized void update(String expression) throws SQLException {
216 st = conn.createStatement(); // statements
218 int i = st.executeUpdate(expression); // run the query
221 System.out.println("db error : " + expression);