EMMA Coverage Report (generated Sat Aug 20 11:00:51 CDT 2011)
[all classes][tuffy.db]

COVERAGE SUMMARY FOR SOURCE FILE [RDB.java]

nameclass, %method, %block, %line, %
RDB.java100% (2/2)69%  (36/52)50%  (867/1730)49%  (212.7/435)

COVERAGE BREAKDOWN BY CLASS AND METHOD

nameclass, %method, %block, %line, %
     
class RDB$1100% (1/1)100% (2/2)8%   (9/114)12%  (3/24)
run (): void 100% (1/1)3%   (3/108)5%   (1/22)
RDB$1 (RDB): void 100% (1/1)100% (6/6)100% (2/2)
     
class RDB100% (1/1)68%  (34/50)53%  (858/1616)51%  (210.7/412)
access$0 (RDB): Statement 0%   (0/1)0%   (0/3)0%   (0/1)
access$1 (RDB, Statement): void 0%   (0/1)0%   (0/4)0%   (0/1)
callFunctionDouble (String, String): Double 0%   (0/1)0%   (0/44)0%   (0/11)
callProcedure (String): void 0%   (0/1)0%   (0/25)0%   (0/7)
copyTable (String, String): void 0%   (0/1)0%   (0/25)0%   (0/7)
createSetTable (String, HashSet): void 0%   (0/1)0%   (0/57)0%   (0/14)
dumpSQL (String): void 0%   (0/1)0%   (0/22)0%   (0/4)
dumpTableToFile (Predicate, String): void 0%   (0/1)0%   (0/121)0%   (0/21)
estimateQuery (String, boolean): void 0%   (0/1)0%   (0/58)0%   (0/11)
executeWhatever (String): void 0%   (0/1)0%   (0/16)0%   (0/6)
getSequenceCurValue (String): int 0%   (0/1)0%   (0/30)0%   (0/8)
insertConstantTable (Map): void 0%   (0/1)0%   (0/91)0%   (0/19)
isTableExists (String, String): boolean 0%   (0/1)0%   (0/32)0%   (0/8)
loadSymbolIdMapFromTable (): HashMap 0%   (0/1)0%   (0/44)0%   (0/13)
resetSequence (String): void 0%   (0/1)0%   (0/14)0%   (0/4)
updateBatch (ArrayList): boolean 0%   (0/1)0%   (0/36)0%   (0/11)
setAutoCommit (boolean): void 100% (1/1)30%  (6/20)40%  (2/5)
explain (String): String 100% (1/1)51%  (35/68)47%  (8/17)
commit (): void 100% (1/1)62%  (5/8)50%  (2/4)
query (String): ResultSet 100% (1/1)68%  (25/37)64%  (7/11)
restoreAutoCommitState (): void 100% (1/1)70%  (7/10)50%  (2/4)
isSchemaExists (String): boolean 100% (1/1)72%  (18/25)43%  (3/7)
execute (String): void 100% (1/1)73%  (22/30)64%  (7/11)
disableAutoCommitForNow (): void 100% (1/1)79%  (11/14)60%  (3/5)
countTuples (String): long 100% (1/1)81%  (29/36)72%  (7.9/11)
dropIndex (String): void 100% (1/1)81%  (13/16)60%  (3/5)
close (): void 100% (1/1)82%  (14/17)60%  (3/5)
getPrepareStatement (String): PreparedStatement 100% (1/1)83%  (15/18)67%  (4/6)
dropStuff (String, String): boolean 100% (1/1)85%  (34/40)67%  (6/9)
RDB (String, String, String): void 100% (1/1)87%  (58/67)80%  (16/20)
estimateCost (String): String 100% (1/1)88%  (61/69)79%  (11/14)
loadIdSymbolMapFromTable (): HashMap 100% (1/1)93%  (39/42)83%  (10/12)
createTempTableIntList (String, Collection): void 100% (1/1)95%  (54/57)85%  (11/13)
createConstantTable (Map): void 100% (1/1)95%  (118/124)85%  (23/27)
resetSchema (String): void 100% (1/1)98%  (80/82)99%  (16.9/17)
<static initializer> 100% (1/1)100% (11/11)100% (4/4)
analyze (String): void 100% (1/1)100% (13/13)100% (3/3)
dropSchema (String): boolean 100% (1/1)100% (10/10)100% (1/1)
dropSequence (String): void 100% (1/1)100% (6/6)100% (2/2)
dropTable (String): void 100% (1/1)100% (6/6)100% (2/2)
dropView (String): void 100% (1/1)100% (6/6)100% (2/2)
getConnection (): Connection 100% (1/1)100% (3/3)100% (1/1)
getLastUpdateRowCount (): int 100% (1/1)100% (3/3)100% (1/1)
getRDBbyConfig (): RDB 100% (1/1)100% (22/22)100% (7/7)
getRDBbyConfig (String): RDB 100% (1/1)100% (34/34)100% (9/9)
regExplainProc (String): void 100% (1/1)100% (27/27)100% (10/10)
registerDrivers (): void 100% (1/1)100% (1/1)100% (1/1)
update (String): int 100% (1/1)100% (37/37)100% (11/11)
updateRaw (String): void 100% (1/1)100% (22/22)100% (8/8)
vacuum (String): void 100% (1/1)100% (13/13)100% (3/3)

1package tuffy.db;
2import java.io.BufferedWriter;
3import java.io.File;
4import java.io.FileInputStream;
5import java.io.FileOutputStream;
6import java.io.OutputStreamWriter;
7import java.io.PipedInputStream;
8import java.io.PipedOutputStream;
9import java.sql.CallableStatement;
10import java.sql.Connection;
11import java.sql.DriverManager;
12import java.sql.PreparedStatement;
13import java.sql.ResultSet;
14import java.sql.SQLException;
15import java.sql.Statement;
16import java.util.ArrayList;
17import java.util.Collection;
18import java.util.HashMap;
19import java.util.HashSet;
20import java.util.Map;
21 
22 
23import org.postgresql.PGConnection;
24 
25import tuffy.mln.Predicate;
26import tuffy.util.Config;
27import tuffy.util.ExceptionMan;
28import tuffy.util.FileMan;
29import tuffy.util.StringMan;
30import tuffy.util.Timer;
31import tuffy.util.UIMan;
32 
33/**
34 * Interface with the RDBMS. Currently only supports PostgreSQL (8.4 or later).
35 */
36public 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}

[all classes][tuffy.db]
EMMA 2.0.5312 EclEmma Fix 2 (C) Vladimir Roubtsov