Start support for Sqlite

[cfb.git] / prod / net / jaekl / cfb / db / driver / DbDriver.java

diff --git a/prod/net/jaekl/cfb/db/driver/DbDriver.java b/prod/net/jaekl/cfb/db/driver/DbDriver.java
index eea3e22c44c8d89f8fa79d21a52089b87e05555b..b8d1ee6bf5f67935a882d62d57667a82cd81a3b6 100644 (file)
--- a/prod/net/jaekl/cfb/db/driver/DbDriver.java
+++ b/prod/net/jaekl/cfb/db/driver/DbDriver.java
@@ -9,7 +9,6 @@ import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.ArrayList;
-import java.util.Date;
 import java.util.List;
 
 import net.jaekl.cfb.db.Column;
@@ -32,6 +31,8 @@ public abstract class DbDriver {
        
        public abstract Connection connect(String host, int port, String dbName, String user, String pass) throws SQLException;
        
+       public abstract long nextVal(Connection con, Sequence seq) throws SQLException;
+       
        public boolean createTable(Connection con, Table table) throws SQLException 
        {
                String sql = createTableSql(table);
@@ -80,6 +81,22 @@ public abstract class DbDriver {
                }
        }
        
+       public Row selectExactlyOne(Connection con, Column[] columns, Table[] tables, Condition[] conditions) 
+               throws SQLException
+       {
+               Sort[] sorts = new Sort[0];
+               int limit = 2;
+               List<Row> rows = select(con, columns, tables, conditions, sorts, limit);
+               if (rows.size() < 1) {
+                       throw new SQLException("Expected one result, but found none:  ", selectSql(columns, tables, conditions, sorts, limit));
+               }
+               if (rows.size() > 1) {
+                       throw new SQLException("Expected one result, but found more than one:  " + selectSql(columns, tables, conditions, sorts, limit));
+               }
+               
+               return rows.get(0);
+       }
+       
        public List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions)
                throws SQLException
        {
@@ -99,8 +116,9 @@ public abstract class DbDriver {
                        int index = 0;
                        for (Condition condition : conditions) {
                                if (condition.getOperation().hasParam()) {
+                                       Column column = condition.getColumn();
                                        index++;
-                                       ps.setObject(index, condition.getValue());
+                                       column.setObject(ps, index, condition.getValue());
                                }
                        }
                        
@@ -121,6 +139,9 @@ public abstract class DbDriver {
                                }
                        }
                }
+               catch (SQLException se) {
+                       throw new SQLException("Error with SQL:  " + sql, se);
+               }
                
                return result;
        }
@@ -131,6 +152,8 @@ public abstract class DbDriver {
                int count = 0;
                int pendingValues = 0;
                
+               assert( isValidInsert(table, values));
+               
                String sql = insertSql(table);
                
                try (PreparedStatement ps = con.prepareStatement(sql))
@@ -141,19 +164,10 @@ public abstract class DbDriver {
                                assert(null != data);
                                assert(data.length == table.getNumColumns());
                                
-                               for (int col = 0; col < data.length; ++col) {
-                                       Object obj = data[col];
-                                       Column column = table.getColumn(col);
-                                       if (column.getType().equals(Type.TIMESTAMPTZ)) {
-                                               // Special case:  because there's no good way to read a TIMESTAMPTZ from 
-                                               // the database using JDBC, we store it as an integer (milliseconds since
-                                               // the epoch, 01.01.1970 00:00:00.000 UTC).
-                                               Date date = (Date)obj;
-                                               ps.setLong(col + 1, date.getTime());
-                                       }
-                                       else {
-                                               ps.setObject(col + 1, data[col]);
-                                       }
+                               for (int idx = 0; idx < data.length; ++idx) {
+                                       Object obj = data[idx];
+                                       Column column = table.getColumn(idx);
+                                       column.setObject(ps, idx + 1, obj);
                                        pendingValues++;
                                }
                                ps.addBatch();
@@ -171,22 +185,6 @@ public abstract class DbDriver {
                return count;
        }
        
-       public long nextVal(Connection con, Sequence seq) throws SQLException
-       {
-               String sql = nextValSql(seq);
-               
-               try (PreparedStatement ps = con.prepareStatement(sql)) 
-               {
-                       try (ResultSet rs = ps.executeQuery()) {
-                               if (rs.next()) {
-                                       return rs.getLong(1);
-                               }
-                       }
-               }
-               
-               throw new SQLException("No value returned for sequence:  " + sql);
-       }
-       
        int checkFlushBatch(PreparedStatement ps, int pendingValues, boolean forceFlush) throws SQLException
        {
                int count = 0;
@@ -282,10 +280,10 @@ public abstract class DbDriver {
                                sb.append(sort.getColumn().getName());
                                
                                if (sort.getDirection().equals(Sort.Direction.ASCENDING)) {
-                                       sb.append(" ASCENDING ");
+                                       sb.append(" ASC ");
                                }
                                else {
-                                       sb.append(" DESCENDING ");
+                                       sb.append(" DESC ");
                                }
                        }
                }
@@ -371,5 +369,31 @@ public abstract class DbDriver {
                return "DROP SEQUENCE " + seq.getName();
        }
        
-       abstract protected String nextValSql(Sequence seq);
+       boolean isValidInsert(Table table, Object[][] values)
+       {
+               if (null == table) return false;
+               if (null == values) return false;
+               
+               for (Object[] rowValues : values) {
+                       if (rowValues.length != table.getNumColumns()) {
+                               return false;
+                       }
+                       for (int idx = 0; idx < rowValues.length; ++idx) {
+                               
+                       }
+               }
+               
+               return true;
+       }
+       
+       int executeUpdate(Connection con, String sql) throws SQLException
+       {
+               try (PreparedStatement ps = con.prepareStatement(sql))
+               {
+                       return ps.executeUpdate();
+               }
+               catch (SQLException exc) {
+                       throw new SQLException("Failed to executeUpdate:  " + sql, exc);
+               }
+       }
 }