From 01f0bef1b13c6b9f5b370372d36ad4d6149ff2c3 Mon Sep 17 00:00:00 2001 From: Chris Jaekl Date: Fri, 11 Dec 2015 23:34:48 +0900 Subject: [PATCH] Start support for Sqlite --- prod/net/jaekl/cfb/db/driver/DbDriver.java | 31 +++---- .../jaekl/cfb/db/driver/PostgresqlDriver.java | 18 ++++- .../net/jaekl/cfb/db/driver/SqliteDriver.java | 80 +++++++++++++++++++ 3 files changed, 109 insertions(+), 20 deletions(-) create mode 100644 prod/net/jaekl/cfb/db/driver/SqliteDriver.java diff --git a/prod/net/jaekl/cfb/db/driver/DbDriver.java b/prod/net/jaekl/cfb/db/driver/DbDriver.java index bbe38f8..b8d1ee6 100644 --- 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 fbb3285..a828d7f 100644 --- 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 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; + } +} -- 2.39.2