1 | package tuffy.db; |
2 | import java.io.BufferedWriter; |
3 | import java.io.File; |
4 | import java.io.FileInputStream; |
5 | import java.io.FileOutputStream; |
6 | import java.io.OutputStreamWriter; |
7 | import java.io.PipedInputStream; |
8 | import java.io.PipedOutputStream; |
9 | import java.sql.CallableStatement; |
10 | import java.sql.Connection; |
11 | import java.sql.DriverManager; |
12 | import java.sql.PreparedStatement; |
13 | import java.sql.ResultSet; |
14 | import java.sql.SQLException; |
15 | import java.sql.Statement; |
16 | import java.util.ArrayList; |
17 | import java.util.Collection; |
18 | import java.util.HashMap; |
19 | import java.util.HashSet; |
20 | import java.util.Map; |
21 | |
22 | |
23 | import org.postgresql.PGConnection; |
24 | |
25 | import tuffy.mln.Predicate; |
26 | import tuffy.util.Config; |
27 | import tuffy.util.ExceptionMan; |
28 | import tuffy.util.FileMan; |
29 | import tuffy.util.StringMan; |
30 | import tuffy.util.Timer; |
31 | import tuffy.util.UIMan; |
32 | |
33 | /** |
34 | * Interface with the RDBMS. Currently only supports PostgreSQL (8.4 or later). |
35 | */ |
36 | public class RDB { |
37 | private int lastUpdateRowCount = -1; |
38 | private boolean savedAutoCommit = false; |
39 | |
40 | static ArrayList<RDB> allRDBs = new ArrayList<RDB>(); |
41 | static int currentDBCounter = 0; |
42 | |
43 | public Connection con = null; |
44 | |
45 | private Statement currentlyRunningQuery = null; |
46 | |
47 | public String db; |
48 | public String user; |
49 | public String password; |
50 | public String schema = null; |
51 | |
52 | public static HashSet<RDB> historyInstances = new HashSet<RDB>(); |
53 | |
54 | /** |
55 | * Disable auto-commit so that JDBC won't fetch all query results at once. |
56 | * Call this before retrieving data from a huge table. |
57 | * After the big query is done, call {@link RDB#restoreAutoCommitState()} to |
58 | * restore the initial auto-commit state. |
59 | * |
60 | * @see RDB#restoreAutoCommitState() |
61 | * @see <a href='http://jdbc.postgresql.org/documentation/84/query.html#query-with-cursor'> |
62 | * PostgreSQL's JDBC doc</a> |
63 | */ |
64 | public void disableAutoCommitForNow(){ |
65 | try { |
66 | savedAutoCommit = con.getAutoCommit(); |
67 | con.setAutoCommit(false); |
68 | } catch (SQLException e) { |
69 | ExceptionMan.handle(e); |
70 | } |
71 | } |
72 | |
73 | /** |
74 | * Register a stored procedure to explain SQL queries. |
75 | * @param pname name of the stored procedure |
76 | */ |
77 | public void regExplainProc(String pname){ |
78 | String sql = "create or replace function " + pname + |
79 | "(q text) returns setof text as $$\r\n" + |
80 | "declare r record;\r\n" + |
81 | "begin\r\n" + |
82 | " for r in execute 'explain ' || q loop\r\n" + |
83 | " return next r.\"QUERY PLAN\";\r\n" + |
84 | " end loop;\r\n" + |
85 | "end$$ language plpgsql"; |
86 | |
87 | update(sql); |
88 | } |
89 | |
90 | |
91 | public void estimateQuery(String sql, boolean analyze){ |
92 | RDB db = this; |
93 | db.estimateCost(sql); |
94 | UIMan.verbose(2, "ESTIMATED cost = " + db.estimatedCost + " ; rows = " + db.estimatedRows); |
95 | if(analyze){ |
96 | Timer.start("cqmat"); |
97 | db.update(sql); |
98 | double rtime = Timer.elapsedMilliSeconds("cqmat"); |
99 | UIMan.verbose(2, Timer.elapsed("cqmat")); |
100 | UIMan.verbose(2, "COST-RATIO = " + (db.estimatedCost/rtime) + " ; ROW-RATIO = " + |
101 | ((double)db.estimatedRows/db.getLastUpdateRowCount())); |
102 | } |
103 | } |
104 | |
105 | public double estimatedCost = 0; |
106 | public double estimatedRows = 0; |
107 | public String estimateCost(String sql){ |
108 | String plan = explain(sql); |
109 | if(plan == null){ |
110 | estimatedCost = Double.MAX_VALUE; |
111 | estimatedRows = Double.MAX_VALUE; |
112 | return null; |
113 | } |
114 | String rep = plan.split("\n")[0]; |
115 | String[] parts = rep.split(" "); |
116 | for(String p : parts){ |
117 | if(p.startsWith("(cost=")){ |
118 | int i = p.indexOf("..") + 2; |
119 | estimatedCost = Double.parseDouble(p.substring(i)); |
120 | }else if(p.startsWith("rows=")){ |
121 | estimatedRows = Double.parseDouble(p.substring(5)); |
122 | } |
123 | } |
124 | return rep; |
125 | } |
126 | |
127 | /** |
128 | * Explain a SQL query with an execution plan. |
129 | * @param sql |
130 | */ |
131 | public String explain(String sql){ |
132 | |
133 | try { |
134 | |
135 | //this.execute("EXPLAIN " + sql); |
136 | //this.regExplainProc("expl"); |
137 | |
138 | PreparedStatement ps = getPrepareStatement( |
139 | "SELECT * FROM expl(cast(? as text))"); |
140 | ps.setString(1, sql); |
141 | //System.out.println(sql); |
142 | ResultSet rs = ps.executeQuery(); |
143 | |
144 | StringBuilder sb = new StringBuilder(); |
145 | while(rs.next()){ |
146 | sb.append(rs.getString(1) + "\n"); |
147 | } |
148 | return sb.toString(); |
149 | } catch (SQLException e) { |
150 | // TODO Auto-generated catch block |
151 | //e.printStackTrace(); |
152 | |
153 | // dirty |
154 | |
155 | try { |
156 | this.con.close(); |
157 | this.con = DriverManager.getConnection(db, user, password); |
158 | if(this.schema != null){ |
159 | this.execute("SET SEARCH_PATH TO " + schema); |
160 | } |
161 | //return this.explain(sql); |
162 | return null; |
163 | } catch (SQLException e1) { |
164 | // TODO Auto-generated catch block |
165 | e1.printStackTrace(); |
166 | } |
167 | |
168 | return null; |
169 | } |
170 | |
171 | } |
172 | |
173 | |
174 | public void createTempTableIntList(String rel, Collection<Integer> vals){ |
175 | dropTable(rel); |
176 | String sql = "CREATE TEMPORARY TABLE " + rel + "(id INT)"; |
177 | update(sql); |
178 | try { |
179 | PreparedStatement ps = getPrepareStatement( |
180 | "INSERT INTO " + rel + " VALUES(?)"); |
181 | for(int pid : vals){ |
182 | ps.setInt(1, pid); |
183 | ps.addBatch(); |
184 | } |
185 | ps.executeBatch(); |
186 | ps.close(); |
187 | } catch (SQLException e) { |
188 | e.printStackTrace(); |
189 | } |
190 | } |
191 | |
192 | |
193 | /** |
194 | * Restore the auto-commit state saved by {@link RDB#disableAutoCommitForNow()}. |
195 | * |
196 | * @see RDB#disableAutoCommitForNow() |
197 | */ |
198 | public void restoreAutoCommitState(){ |
199 | try { |
200 | con.setAutoCommit(savedAutoCommit); |
201 | } catch (SQLException e) { |
202 | ExceptionMan.handle(e); |
203 | } |
204 | } |
205 | |
206 | /** |
207 | * Return the number of affected tuples from last update. |
208 | */ |
209 | public int getLastUpdateRowCount() { |
210 | return lastUpdateRowCount; |
211 | } |
212 | |
213 | /** |
214 | * Return the database connection. |
215 | */ |
216 | public Connection getConnection(){ |
217 | return con; |
218 | } |
219 | |
220 | |
221 | /** |
222 | * Dump a MAP world produced by MAP inference. |
223 | * |
224 | * @param fout path of output file |
225 | */ |
226 | public void dumpTableToFile(Predicate p, String fout) { |
227 | HashMap<Long,String> cmap = this.loadIdSymbolMapFromTable(); |
228 | try { |
229 | BufferedWriter bufferedWriter = null; |
230 | bufferedWriter = new BufferedWriter(new OutputStreamWriter |
231 | (new FileOutputStream(fout),"UTF8")); |
232 | String sql = "SELECT * FROM " + p.getRelName() + |
233 | " WHERE truth" + |
234 | " ORDER BY " + StringMan.commaList(p.getArgs()); |
235 | ResultSet rs = this.query(sql); |
236 | while(rs.next()) { |
237 | String line = p.getName() + "("; |
238 | ArrayList<String> cs = new ArrayList<String>(); |
239 | for(String a : p.getArgs()) { |
240 | long c = rs.getLong(a); |
241 | cs.add("\"" + StringMan.escapeJavaString(cmap.get(c)) + "\""); |
242 | } |
243 | line += StringMan.commaList(cs) + ")"; |
244 | bufferedWriter.append(line + "\n"); |
245 | } |
246 | rs.close(); |
247 | bufferedWriter.close(); |
248 | } catch (Exception e) { |
249 | ExceptionMan.handle(e); |
250 | } |
251 | } |
252 | |
253 | |
254 | |
255 | /** |
256 | * Attempt to establish the connection as specified in the |
257 | * (deault) configuration. |
258 | */ |
259 | public static RDB getRDBbyConfig() { |
260 | |
261 | //TODO: why need so large |
262 | //int nConnections = 1; |
263 | |
264 | //TODO: change nCores to # connect |
265 | //if(allRDBs.size() < nConnections){ |
266 | RDB tmp = new RDB(Config.db_url, |
267 | Config.db_username, Config.db_password); |
268 | |
269 | tmp.db = Config.db_url; |
270 | tmp.user = Config.db_username; |
271 | tmp.password = Config.db_password; |
272 | |
273 | historyInstances.add(tmp); |
274 | |
275 | |
276 | // allRDBs.add(tmp); |
277 | // currentDBCounter = allRDBs.size() - 1; |
278 | //}else{ |
279 | // currentDBCounter = (currentDBCounter+1) % nConnections; |
280 | //} |
281 | |
282 | //return allRDBs.get(currentDBCounter); |
283 | return tmp; |
284 | |
285 | } |
286 | |
287 | public static RDB getRDBbyConfig(String schema) { |
288 | |
289 | RDB tmp = new RDB(Config.db_url, |
290 | Config.db_username, Config.db_password); |
291 | |
292 | tmp.db = Config.db_url; |
293 | tmp.user = Config.db_username; |
294 | tmp.password = Config.db_password; |
295 | tmp.schema = schema; |
296 | |
297 | tmp.execute("SET search_path = " + schema); |
298 | |
299 | historyInstances.add(tmp); |
300 | |
301 | return tmp; |
302 | |
303 | } |
304 | |
305 | /** |
306 | * Register the JDBC driver. |
307 | */ |
308 | private void registerDrivers(){ |
309 | /* |
310 | try { |
311 | Class.forName("org.postgresql.Driver"); |
312 | } catch (ClassNotFoundException e) { |
313 | System.err.println(e.getMessage()); |
314 | System.err.println("Failed to load PostgreSQL JDBC driver."); |
315 | } |
316 | */ |
317 | } |
318 | |
319 | private void dumpSQL(String sql){ |
320 | UIMan.println("-----BEGIN:SQL-----"); |
321 | UIMan.println(sql); |
322 | UIMan.println("-----END:SQL-----"); |
323 | } |
324 | |
325 | /** |
326 | * Execute an update SQL statement. |
327 | * |
328 | * @return the number of tuples affected |
329 | */ |
330 | public int update(String sql){ |
331 | if(Config.exiting_mode) ExceptionMan.die(""); |
332 | try { |
333 | Statement stmt = con.createStatement(); |
334 | currentlyRunningQuery = stmt; |
335 | lastUpdateRowCount = stmt.executeUpdate(sql); |
336 | stmt.close(); |
337 | currentlyRunningQuery = null; |
338 | } catch (SQLException e) { |
339 | UIMan.error(sql); |
340 | ExceptionMan.handle(e); |
341 | return 0; |
342 | } |
343 | return lastUpdateRowCount; |
344 | } |
345 | |
346 | /** |
347 | * Execute a SQL statement (query/update). |
348 | */ |
349 | public void execute(String sql) { |
350 | if(Config.exiting_mode) ExceptionMan.die(""); |
351 | try { |
352 | Statement stmt = con.createStatement(); |
353 | currentlyRunningQuery = stmt; |
354 | stmt.execute(sql); |
355 | stmt.close(); |
356 | currentlyRunningQuery = null; |
357 | } catch (SQLException e) { |
358 | dumpSQL(sql); |
359 | e.printStackTrace(); |
360 | |
361 | ExceptionMan.handle(e); |
362 | } |
363 | } |
364 | |
365 | public void executeWhatever(String sql) { |
366 | try { |
367 | Statement stmt = con.createStatement(); |
368 | stmt.execute(sql); |
369 | stmt.close(); |
370 | } catch (SQLException e) { |
371 | dumpSQL(sql); |
372 | } |
373 | } |
374 | |
375 | private void updateRaw(String sql) throws SQLException{ |
376 | this.commit(); |
377 | this.setAutoCommit(true); |
378 | Statement stmt = con.createStatement(); |
379 | currentlyRunningQuery = stmt; |
380 | stmt.executeUpdate(sql); |
381 | stmt.close(); |
382 | currentlyRunningQuery = null; |
383 | } |
384 | |
385 | /** |
386 | * Execute a set of update SQL statements as a batch. |
387 | * |
388 | * @return true on success |
389 | */ |
390 | public boolean updateBatch(ArrayList<String> sqls) { |
391 | try { |
392 | Statement st = con.createStatement(); |
393 | currentlyRunningQuery = st; |
394 | for(String s : sqls) { |
395 | st.addBatch(s); |
396 | } |
397 | st.executeBatch(); |
398 | st.close(); |
399 | currentlyRunningQuery = null; |
400 | return true; |
401 | } catch (SQLException e) { |
402 | ExceptionMan.handle(e); |
403 | } |
404 | return false; |
405 | } |
406 | |
407 | /** |
408 | * Execute a SQL query. |
409 | * |
410 | * @param sql the SQL statement |
411 | * @return the result set. remembe to close it afterwards. |
412 | */ |
413 | public ResultSet query(String sql){ |
414 | if(Config.exiting_mode) ExceptionMan.die(""); |
415 | try { |
416 | Statement stmt = con.createStatement(ResultSet.HOLD_CURSORS_OVER_COMMIT, 1); |
417 | currentlyRunningQuery = stmt; |
418 | stmt.setFetchSize(100000); |
419 | ResultSet rs = stmt.executeQuery(sql); |
420 | currentlyRunningQuery = null; |
421 | return rs; |
422 | } catch (SQLException e) { |
423 | UIMan.error(sql); |
424 | ExceptionMan.handle(e); |
425 | return null; |
426 | } |
427 | } |
428 | |
429 | /** |
430 | * Load the symbol table into a hash table mapping |
431 | * symbols to their IDs. |
432 | * |
433 | * @see Config#relConstants |
434 | */ |
435 | public HashMap<String, Integer> loadSymbolIdMapFromTable() { |
436 | HashMap<String, Integer> map = |
437 | new HashMap<String, Integer>(); |
438 | String rel = Config.relConstants; |
439 | String sql = "SELECT * FROM " + rel; |
440 | ResultSet rs = query(sql); |
441 | try { |
442 | while(rs.next()) { |
443 | String word = rs.getString("string"); |
444 | int id = rs.getInt("id"); |
445 | map.put(word, id); |
446 | } |
447 | rs.close(); |
448 | } catch (SQLException e) { |
449 | ExceptionMan.handle(e); |
450 | } |
451 | return map; |
452 | } |
453 | |
454 | /** |
455 | * Load the symbol table into a hash table mapping |
456 | * symbol IDs to the original symbols. |
457 | * |
458 | * @see Config#relConstants |
459 | */ |
460 | public HashMap<Long,String> loadIdSymbolMapFromTable() { |
461 | HashMap<Long,String> map = |
462 | new HashMap<Long,String>(); |
463 | String sql = "SELECT * FROM " + Config.relConstants; |
464 | ResultSet rs = query(sql); |
465 | try { |
466 | while(rs.next()) { |
467 | String word = rs.getString("string"); |
468 | long id = rs.getLong("id"); |
469 | map.put(id, word); |
470 | } |
471 | rs.close(); |
472 | } catch (SQLException e) { |
473 | ExceptionMan.handle(e); |
474 | } |
475 | return map; |
476 | } |
477 | |
478 | /** |
479 | * Store the symbol-ID mapping into a symbol table. |
480 | * |
481 | * @param mapConstantID the symbol-ID mapping |
482 | * @see Config#relConstants |
483 | */ |
484 | public void createConstantTable(Map<String, Integer> mapConstantID) { |
485 | String rel = Config.relConstants; |
486 | dropTable(rel); |
487 | String sql = "CREATE TABLE " + rel + |
488 | "(id bigint PRIMARY KEY, string TEXT)"; |
489 | update(sql); |
490 | |
491 | BufferedWriter writer = null; |
492 | File loadingFile = new File(Config.getLoadingDir(), "loading_symbols_"); |
493 | try { |
494 | writer = new BufferedWriter(new OutputStreamWriter |
495 | (new FileOutputStream(loadingFile),"UTF8")); |
496 | } catch (Exception e) { |
497 | ExceptionMan.handle(e); |
498 | } |
499 | try { |
500 | for(Map.Entry<String, Integer> pair : mapConstantID.entrySet()) { |
501 | writer.append(pair.getValue().toString()); |
502 | writer.append("\t"); |
503 | writer.append(StringMan.escapeJavaString(pair.getKey())); |
504 | writer.append("\n"); |
505 | } |
506 | writer.close(); |
507 | FileInputStream in = new FileInputStream(loadingFile); |
508 | PGConnection con = (PGConnection)this.getConnection(); |
509 | sql = "COPY " + rel + " FROM STDIN "; |
510 | con.getCopyAPI().copyIn(sql, in); |
511 | in.close(); |
512 | |
513 | sql = "CREATE INDEX constants_index_id ON " + rel + "(id)"; |
514 | this.execute(sql); |
515 | |
516 | }catch(Exception e) { |
517 | ExceptionMan.handle(e); |
518 | } |
519 | |
520 | } |
521 | |
522 | public void insertConstantTable(Map<String, Integer> mapConstantID) { |
523 | String rel = Config.relConstants; |
524 | String sql; |
525 | BufferedWriter writer = null; |
526 | File loadingFile = new File(Config.getLoadingDir(), "loading_symbols_"); |
527 | try { |
528 | writer = new BufferedWriter(new OutputStreamWriter |
529 | (new FileOutputStream(loadingFile),"UTF8")); |
530 | } catch (Exception e) { |
531 | ExceptionMan.handle(e); |
532 | } |
533 | try { |
534 | for(Map.Entry<String, Integer> pair : mapConstantID.entrySet()) { |
535 | writer.append(pair.getValue() + "\t" + |
536 | StringMan.escapeJavaString(pair.getKey()) + "\n"); |
537 | } |
538 | writer.close(); |
539 | FileInputStream in = new FileInputStream(loadingFile); |
540 | PGConnection con = (PGConnection)this.getConnection(); |
541 | sql = "COPY " + rel + " FROM STDIN "; |
542 | con.getCopyAPI().copyIn(sql, in); |
543 | in.close(); |
544 | |
545 | //sql = "CREATE INDEX constants_index_id ON " + rel + "(id)"; |
546 | //this.execute(sql); |
547 | |
548 | }catch(Exception e) { |
549 | ExceptionMan.handle(e); |
550 | } |
551 | } |
552 | |
553 | /** |
554 | * Create a table to store a set of integers |
555 | * @param rel the name of the table |
556 | * @param set the set of integers |
557 | */ |
558 | public void createSetTable(String rel, HashSet<Integer> set){ |
559 | dropTable(rel); |
560 | String sql = "CREATE TEMPORARY TABLE " + rel + |
561 | "(id INT)"; |
562 | update(sql); |
563 | PreparedStatement ps = getPrepareStatement( |
564 | "INSERT INTO " + rel + " VALUES(?)"); |
565 | try { |
566 | for(int pid : set){ |
567 | ps.setInt(1, pid); |
568 | ps.addBatch(); |
569 | } |
570 | ps.executeBatch(); |
571 | ps.close(); |
572 | } catch (SQLException e) { |
573 | e.printStackTrace(); |
574 | } |
575 | } |
576 | |
577 | /** |
578 | * Try to drop a table; remain silent if the specified |
579 | * table doesn't exist. |
580 | */ |
581 | public void dropTable(String rel){ |
582 | dropStuff("TABLE", rel); |
583 | } |
584 | |
585 | /** |
586 | * Try to drop a schema; remain silent if the specified |
587 | * schema doesn't exist. |
588 | */ |
589 | public boolean dropSchema(String sch){ |
590 | return dropStuff("SCHEMA", sch + ""); |
591 | } |
592 | |
593 | /** |
594 | * Try to drop a sequence; remain silent if the specified |
595 | * sequence doesn't exist. |
596 | */ |
597 | public void dropSequence(String seq){ |
598 | dropStuff("SEQUENCE", seq); |
599 | } |
600 | |
601 | public void dropView(String view){ |
602 | dropStuff("VIEW", view); |
603 | } |
604 | |
605 | private boolean dropStuff(String type, String obj){ |
606 | String sql = "DROP " + type + " IF EXISTS " + obj + " CASCADE"; |
607 | String sql2 = "DROP " + type + " IF EXISTS " + obj + ""; |
608 | try { |
609 | updateRaw(sql); |
610 | return true; |
611 | } catch (SQLException e) { |
612 | |
613 | // the target was not found; do nothing |
614 | try{ |
615 | updateRaw(sql2); |
616 | return true; |
617 | }catch(Exception e2){ |
618 | return false; |
619 | } |
620 | |
621 | } |
622 | } |
623 | |
624 | |
625 | /** |
626 | * Return a prepared statement of the given SQL statement. |
627 | * A SQL statement with or without parameters can be pre-compiled |
628 | * and stored in a PreparedStatement object. This object can then |
629 | * be used to efficiently execute this statement multiple times. |
630 | */ |
631 | public PreparedStatement getPrepareStatement(String sql) { |
632 | PreparedStatement ps = null; |
633 | try { |
634 | ps = con.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); |
635 | ps.setFetchSize(100000); |
636 | } catch (SQLException e) { |
637 | ExceptionMan.handle(e); |
638 | } |
639 | return ps; |
640 | } |
641 | |
642 | public boolean isSchemaExists(String name){ |
643 | |
644 | ResultSet rs = this.query("SELECT * FROM information_schema.schemata WHERE schema_name = '" + name.toLowerCase() + "'"); |
645 | try { |
646 | if(rs.next()){ |
647 | return true; |
648 | }else{ |
649 | return false; |
650 | } |
651 | } catch (SQLException e) { |
652 | // TODO Auto-generated catch block |
653 | e.printStackTrace(); |
654 | } |
655 | return false; |
656 | } |
657 | |
658 | public boolean isTableExists(String schemaName, String tableName){ |
659 | |
660 | ResultSet rs = this.query("SELECT * FROM information_schema.tables WHERE table_name = '" + tableName.toLowerCase() + "'"+ |
661 | " AND table_schema='" + schemaName.toLowerCase() + "'"); |
662 | try { |
663 | if(rs.next()){ |
664 | return true; |
665 | }else{ |
666 | return false; |
667 | } |
668 | } catch (SQLException e) { |
669 | // TODO Auto-generated catch block |
670 | e.printStackTrace(); |
671 | } |
672 | return false; |
673 | } |
674 | |
675 | /** |
676 | * Reset the database schema that serves as Tuffy's workspace. |
677 | * |
678 | * @see Config#db_schema |
679 | */ |
680 | public void resetSchema(String schema) { |
681 | if(Config.evidDBSchema != null){ |
682 | |
683 | }else{ |
684 | dropSchema(schema); |
685 | String sql = "CREATE SCHEMA " + schema + " AUTHORIZATION " + Config.db_username; |
686 | update(sql); |
687 | sql = "GRANT ALL ON SCHEMA " + schema + " TO " + Config.db_username; |
688 | update(sql); |
689 | } |
690 | |
691 | execute("SET search_path = " + schema); |
692 | |
693 | execute("DROP TYPE IF EXISTS typeOfIntArray CASCADE"); |
694 | execute("CREATE TYPE typeOfIntArray AS ( a INT[] );"); |
695 | |
696 | execute(SQLMan.sqlTypeConversions); |
697 | |
698 | if(Config.evidDBSchema == null || Config.dbNeedTranslate == true){ |
699 | execute("SET search_path = " + schema); |
700 | execute(SQLMan.sqlIntArrayFuncReg); |
701 | execute(SQLMan.sqlRandomAgg); |
702 | execute(SQLMan.sqlFuncMisc); |
703 | |
704 | regExplainProc("expl"); |
705 | } |
706 | } |
707 | |
708 | /** |
709 | * Copy the tuples of a table to another. |
710 | * Can be used to check out the content of a temporary table. |
711 | * |
712 | * @param src name of the source table |
713 | * @param dest name the destination table; will be dropped if already exists |
714 | */ |
715 | public void copyTable(String src, String dest) { |
716 | dropTable(dest); |
717 | String sql = "CREATE TABLE "+dest+" AS " + |
718 | "SELECT * FROM " + src; |
719 | try { |
720 | updateRaw(sql); |
721 | } catch (SQLException e) { |
722 | ExceptionMan.handle(e); |
723 | } |
724 | } |
725 | |
726 | /** |
727 | * Commit the previous actions. |
728 | * Useless when AutoCommit is on, which is so by default. |
729 | */ |
730 | public void commit() { |
731 | try { |
732 | con.commit(); |
733 | } catch (SQLException e) { |
734 | ExceptionMan.handle(e); |
735 | } |
736 | } |
737 | |
738 | /** |
739 | * Specifies a JDBC connection. |
740 | */ |
741 | public RDB(String url, String user, String password){ |
742 | |
743 | UIMan.verbose(3, "------------------- Open a new DB " + this); |
744 | |
745 | registerDrivers(); |
746 | try { |
747 | con = DriverManager.getConnection(url, user, password); |
748 | con.setAutoCommit(true); |
749 | execute("SET work_mem = '100MB'"); |
750 | //execute("SET checkpoint_segments = 30"); |
751 | //execute("SET temp_buffers = '2000MB'"); |
752 | //execute("SET maintenance_work_mem = '100MB'"); |
753 | //execute("SET archive_mode = OFF"); |
754 | //execute("SET wal_buffers = '50MB'"); |
755 | //execute("SET shared_buffers = '500MB'"); |
756 | execute("set client_encoding='utf8'"); |
757 | /* |
758 | if(Config.forceNestedLoop) { |
759 | execute("SET enable_bitmapscan = 'off'"); |
760 | execute("SET enable_hashagg = 'off'"); |
761 | execute("SET enable_hashjoin = 'off'"); |
762 | execute("SET enable_indexscan = 'off'"); |
763 | execute("SET enable_mergejoin = 'off'"); |
764 | execute("SET enable_sort = 'off'"); |
765 | execute("SET enable_tidscan = 'off'"); |
766 | } |
767 | if(Config.forceJoinOrder) { |
768 | execute("SET join_collapse_limit = '1'"); |
769 | } |
770 | */ |
771 | Runtime.getRuntime().addShutdownHook(new Thread() { |
772 | public void run() { |
773 | if(Config.exiting_mode) return; |
774 | Config.exiting_mode = true; |
775 | UIMan.setSilent(true); |
776 | UIMan.setSilentErr(true); |
777 | System.out.println("\n!!! Shutting down Tuffy !!!"); |
778 | if (currentlyRunningQuery != null){ |
779 | try { |
780 | System.out.print("Cacelling currently running DB query..."); |
781 | currentlyRunningQuery.cancel(); |
782 | currentlyRunningQuery = null; |
783 | System.out.println("Done."); |
784 | } catch (SQLException e) { |
785 | System.out.println("Failed."); |
786 | } |
787 | } |
788 | |
789 | System.out.print("Removing temporary dir '" + Config.getWorkingDir() + "'..."); |
790 | System.out.println(FileMan.removeDirectory(new File(Config.getWorkingDir()))?"OK" : "FAILED"); |
791 | |
792 | if(!Config.keep_db_data){ |
793 | System.out.print("Removing database schema '" + Config.db_schema + "'..."); |
794 | System.out.println(dropSchema(Config.db_schema)?"OK" : "FAILED"); |
795 | }else{ |
796 | System.out.println("Data remains in schema '" + Config.db_schema + "'."); |
797 | } |
798 | try { |
799 | if(con != null && !con.isClosed()) { |
800 | con.close(); |
801 | } |
802 | } catch (SQLException e) { |
803 | } |
804 | } |
805 | }); |
806 | } catch (SQLException e) { |
807 | System.err.println("Failed to connect to PostgreSQL!"); |
808 | System.err.println(e.getMessage()); |
809 | return; |
810 | } |
811 | } |
812 | |
813 | /** |
814 | * Set auto-commit state of this connection. |
815 | */ |
816 | public void setAutoCommit(boolean v){ |
817 | try { |
818 | con.setAutoCommit(v); |
819 | } catch (SQLException e) { |
820 | System.err.println("Failed to set AutoCommit to " + v); |
821 | System.err.println(e.getMessage()); |
822 | } |
823 | } |
824 | |
825 | /** |
826 | * Read the current value of a sequence. |
827 | * |
828 | * @param seq the name of the sequence |
829 | */ |
830 | public int getSequenceCurValue(String seq) { |
831 | String s = "SELECT CURRVAL('"+seq+"')"; |
832 | ResultSet rs = query(s); |
833 | if(rs == null) return -1; |
834 | try { |
835 | if(rs.next()) { |
836 | return rs.getInt(1); |
837 | } |
838 | } catch (SQLException e) { |
839 | e.printStackTrace(); |
840 | } |
841 | return -1; |
842 | } |
843 | |
844 | /** |
845 | * Count the tuples in a table. |
846 | */ |
847 | public long countTuples(String table) { |
848 | if(Config.exiting_mode) ExceptionMan.die(""); |
849 | String s = "SELECT COUNT(*) FROM " + table; |
850 | ResultSet rs = query(s); |
851 | if(rs == null) ExceptionMan.die(""); |
852 | try { |
853 | if(rs.next()) { |
854 | long c = rs.getLong(1); |
855 | rs.close(); |
856 | return c; |
857 | } |
858 | } catch (SQLException e) { |
859 | e.printStackTrace(); |
860 | } |
861 | return -1; |
862 | } |
863 | |
864 | /** |
865 | * Close this connection. |
866 | */ |
867 | public void close() { |
868 | try { |
869 | UIMan.verbose(3, "------------------- Close a DB " + this); |
870 | con.close(); |
871 | } catch (SQLException e) { |
872 | ExceptionMan.handle(e); |
873 | } |
874 | } |
875 | |
876 | /** |
877 | * Analyze a specific table. |
878 | * |
879 | * @param rel name of the table |
880 | * |
881 | * @see <a hef='http://www.postgresql.org/docs/current/static/sql-analyze.html'> |
882 | * the PostgreSQL doc</a> |
883 | */ |
884 | public void analyze(String rel) { |
885 | String sql = "ANALYZE " + rel; |
886 | this.update(sql); |
887 | } |
888 | |
889 | /** |
890 | * Vacuum a specific table. |
891 | * |
892 | * @param rel name of the table |
893 | * |
894 | * @see <a hef='http://www.postgresql.org/docs/current/static/sql-vacuum.html'> |
895 | * the PostgreSQL doc</a> |
896 | */ |
897 | public void vacuum(String rel) { |
898 | String sql = "VACUUM " + rel; |
899 | this.update(sql); |
900 | } |
901 | |
902 | /** |
903 | * Drop an index if it exists. |
904 | * |
905 | * @param idx name of the index |
906 | */ |
907 | public void dropIndex(String idx){ |
908 | String sql = "DROP INDEX IF EXISTS " + idx; |
909 | try { |
910 | updateRaw(sql); |
911 | } catch (SQLException e) { |
912 | ExceptionMan.handle(e); |
913 | } |
914 | } |
915 | |
916 | /** |
917 | * Reset the value of a sequence to 1. |
918 | * |
919 | * @param seq name of the sequence |
920 | */ |
921 | public void resetSequence(String seq) { |
922 | String sql = "SELECT setval('" + seq + |
923 | "', 1, false)"; |
924 | execute(sql); |
925 | } |
926 | |
927 | /** |
928 | * Call a stored procedure that doesn't have any parameters. |
929 | * |
930 | * @param proc name of the stored procedure |
931 | */ |
932 | public void callProcedure(String proc) { |
933 | CallableStatement stmt = null; |
934 | try { |
935 | stmt = con.prepareCall("{call "+proc+"()}"); |
936 | stmt.execute(); |
937 | stmt.close(); |
938 | } catch (SQLException e) { |
939 | ExceptionMan.handle(e); |
940 | } |
941 | } |
942 | |
943 | /** |
944 | * Call a function that returns a double. |
945 | * |
946 | * @param func name of the function |
947 | * @param args arguments in the form of a string |
948 | * @return value returned by the function; null on error |
949 | */ |
950 | public Double callFunctionDouble(String func, String args) { |
951 | CallableStatement stmt = null; |
952 | try { |
953 | if(args == null) args = ""; |
954 | stmt = con.prepareCall("{? = call "+func+"(" +args + ")}"); |
955 | stmt.registerOutParameter(1, java.sql.Types.DOUBLE); |
956 | stmt.execute(); |
957 | double x = stmt.getDouble(1); |
958 | stmt.close(); |
959 | return x; |
960 | } catch (SQLException e) { |
961 | ExceptionMan.handle(e); |
962 | } |
963 | return null; |
964 | } |
965 | |
966 | } |