Start support for Sqlite

diff --git a/prod/net/jaekl/cfb/db/driver/DbDriver.java b/prod/net/jaekl/cfb/db/driver/DbDriver.java
index bbe38f8cc0d9f23c4b3a24430bd747c1027a1354..b8d1ee6bf5f67935a882d62d57667a82cd81a3b6 100644 (file)
--- a/prod/net/jaekl/cfb/db/driver/DbDriver.java
+++ b/prod/net/jaekl/cfb/db/driver/DbDriver.java
@@ -31,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);
@@ -183,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;
@@ -383,8 +369,6 @@ 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;
@@ -401,4 +385,15 @@ public abstract class DbDriver {
                
                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);
+               }
+       }
 }

diff --git a/prod/net/jaekl/cfb/db/driver/PostgresqlDriver.java b/prod/net/jaekl/cfb/db/driver/PostgresqlDriver.java
index fbb328503add803379a1cab500a959bdefc0c9f0..a828d7fc651a84f3d034b6aa8e6452ab3f17cbfc 100644 (file)
--- a/prod/net/jaekl/cfb/db/driver/PostgresqlDriver.java
+++ b/prod/net/jaekl/cfb/db/driver/PostgresqlDriver.java
@@ -4,6 +4,8 @@ package net.jaekl.cfb.db.driver;
 
 import java.sql.Connection;
 import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.Properties;
 
@@ -31,11 +33,23 @@ public class PostgresqlDriver extends DbDriver {
        }
        
        @Override 
-       public String nextValSql(Sequence seq) 
+       public long nextVal(Connection con, Sequence seq) throws SQLException
        {
-               return " SELECT NEXTVAL('" + seq.getName() + "') ";
+               String sql = " SELECT NEXTVAL('" + seq.getName() + "') ";
+               
+               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);
        }
        
+       
        @Override
        protected String typeName(Type type) {
                // Special case:  TIMESTAMPTZ stored as INTEGER (milliseconds since the epoch)

diff --git a/prod/net/jaekl/cfb/db/driver/SqliteDriver.java b/prod/net/jaekl/cfb/db/driver/SqliteDriver.java
new file mode 100644 (file)
index 0000000..1c336f5
--- /dev/null
+++ b/prod/net/jaekl/cfb/db/driver/SqliteDriver.java
@@ -0,0 +1,80 @@
+package net.jaekl.cfb.db.driver;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+import net.jaekl.cfb.db.Sequence;
+
+public class SqliteDriver extends DbDriver {
+       static final long SEQ_NO_VALUE = (-1);
+       static final long SEQ_INIT_VALUE = 0;
+
+       @Override
+       public void load() throws ClassNotFoundException {
+               Class.forName("org.sqlite.JDBC");
+       }
+
+       @Override
+       public Connection connect(String host, int port, String dbName, String user, String pass) 
+               throws SQLException 
+       {
+               String url = "jdbc:sqlite:" + dbName;
+               return DriverManager.getConnection(url);
+       }
+
+       @Override
+       public long nextVal(Connection con, Sequence seq) throws SQLException 
+       {
+               final String sqlRead = "SELECT value FROM " + seq.getName();
+               final String sqlIncr = "UPDATE " + seq.getName() + " SET value=? WHERE value=?";
+               long value = SEQ_NO_VALUE;
+               
+               boolean auto = con.getAutoCommit();
+               try (
+                               PreparedStatement psRead = con.prepareStatement(sqlRead);
+                               PreparedStatement psIncr = con.prepareStatement(sqlIncr);
+                       ) 
+               {
+                       con.setAutoCommit(false);
+                       
+                       for (int retryCount = 10; retryCount > 0; retryCount--) {
+                               try ( ResultSet rs = psRead.executeQuery() ) 
+                               {
+                                       if (rs.next()) {
+                                               value = rs.getLong(1);
+                                       }
+                               }
+                               
+                               if (value >= SEQ_INIT_VALUE) {
+                                       psIncr.setLong(1, value + 1);
+                                       psIncr.setLong(2, value);
+                                       int count = psIncr.executeUpdate();
+                                       if (1 == count) {
+                                               return (value + 1);
+                                       }
+                               }
+                       }
+               }
+               finally {
+                       con.setAutoCommit(auto);
+               }
+               
+               throw new SQLException("Unable to get next value for sequence " + seq.getName() + ".  Retry count exceeded.");
+       }
+       
+       @Override
+       public boolean createSequence(Connection con, Sequence seq)
+               throws SQLException
+       {
+               String sqlCreate = "CREATE TABLE " + seq.getName() + " ( value INTEGER )";
+               String sqlInsert = "INSERT INTO " + seq.getName() + " VALUES ( 0 )";
+
+               executeUpdate(con, sqlCreate);
+               executeUpdate(con, sqlInsert);
+               
+               return true;
+       }
+}