X-Git-Url: http://jaekl.net/gitweb/?a=blobdiff_plain;f=prod%2Fnet%2Fjaekl%2Fcfb%2Fdb%2Fdriver%2FDbDriver.java;h=b8d1ee6bf5f67935a882d62d57667a82cd81a3b6;hb=a1378c84c773511e4ffe99fb419da67af188aff7;hp=169cfee3553b8cc2e247bea6914b97b40b5c5b70;hpb=a683a5834138300c924274d1cda66a4a359222c5;p=cfb.git

diff --git a/prod/net/jaekl/cfb/db/driver/DbDriver.java b/prod/net/jaekl/cfb/db/driver/DbDriver.java
index 169cfee..b8d1ee6 100644
--- a/prod/net/jaekl/cfb/db/driver/DbDriver.java
+++ b/prod/net/jaekl/cfb/db/driver/DbDriver.java
@@ -1,17 +1,27 @@
 package net.jaekl.cfb.db.driver;
 
-import static net.jaekl.cfb.db.Column.Null.*;
+// Copyright (C) 2015 Christian Jaekl
+
+import static net.jaekl.cfb.db.Column.Null.NOT_NULL;
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
 
 import net.jaekl.cfb.db.Column;
 import net.jaekl.cfb.db.Column.Type;
+import net.jaekl.cfb.db.Condition;
+import net.jaekl.cfb.db.Row;
+import net.jaekl.cfb.db.Sequence;
+import net.jaekl.cfb.db.Sort;
 import net.jaekl.cfb.db.Table;
 
 public abstract class DbDriver {
+	static int PENDING_LIMIT = 1024;	// Rough limit at which point we'll start a new batch for batch updates
+	
 	DbDriver() {
 		
 	}
@@ -19,20 +29,281 @@ public abstract class DbDriver {
 	// Load the JDBC driver
 	public abstract void load() throws ClassNotFoundException;
 	
-	public abstract Connection connect(String host, int port, String user, String pass);
+	public abstract Connection connect(String host, int port, String dbName, String user, String pass) throws SQLException;
 	
-	public boolean createTable(Connection con, Table table) 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);
 		try (PreparedStatement ps = con.prepareStatement(sql)) {
 			ps.executeUpdate();
 		}
+		catch (SQLException exc) {
+			throw new SQLException("Failed to executeUpdate:  " + sql, exc);
+		}
 		
 		return true;
 	}
 	
-	public abstract ResultSet selectColumnsFromWhere(Column[] columns, Table[] tables, String where);
+	public void dropTable(Connection con, Table table) throws SQLException
+	{
+		String sql = dropTableSql(table);
+		try (PreparedStatement ps = con.prepareStatement(sql)) {
+			ps.executeUpdate();
+		}
+		catch (SQLException exc) {
+			throw new SQLException("Failed to drop table:  " + sql, exc);
+		}
+	}
+	
+	public boolean createSequence(Connection con, Sequence seq) throws SQLException 
+	{
+		String sql = createSequenceSql(seq);
+		try (PreparedStatement ps = con.prepareStatement(sql)) {
+			ps.executeUpdate();
+		}
+		catch (SQLException exc) {
+			throw new SQLException("Failed to executeUpdate:  " + sql, exc);
+		}
+		
+		return true;
+	}
+	
+	public void dropSequence(Connection con, Sequence seq) throws SQLException
+	{
+		String sql = dropSequenceSql(seq);
+		try (PreparedStatement ps = con.prepareStatement(sql)) {
+			ps.executeUpdate();
+		}
+		catch (SQLException exc) {
+			throw new SQLException("Failed to drop sequence:  " + sql, exc);
+		}
+	}
+	
+	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
+	{
+		Sort[] sorts = new Sort[0];
+		int limit = (-1);	// no limit
+		
+		return select(con, columns, tables, conditions, sorts, limit);
+	}
+	
+	public List<Row> select(Connection con, Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit)
+		throws SQLException
+	{
+		String sql = selectSql(columns, tables, conditions, sorts, limit);
+		ArrayList<Row> result = new ArrayList<Row>();
+		
+		try (PreparedStatement ps = con.prepareStatement(sql)) {
+			int index = 0;
+			for (Condition condition : conditions) {
+				if (condition.getOperation().hasParam()) {
+					Column column = condition.getColumn();
+					index++;
+					column.setObject(ps, index, condition.getValue());
+				}
+			}
+			
+			try (ResultSet rs = ps.executeQuery()) {
+				while (rs.next()) {
+					Object[] values = new Object[columns.length];
+					for (index = 0; index < columns.length; ++index) {
+						if (columns[index].getType().equals(Type.TIMESTAMPTZ)) {
+							long milliseconds = rs.getLong(index + 1);
+							values[index] = new java.util.Date(milliseconds);
+						}
+						else {
+							values[index] = rs.getObject(index + 1);
+						}
+					}
+					Row row = new Row(columns, values);
+					result.add(row);
+				}
+			}
+		}
+		catch (SQLException se) {
+			throw new SQLException("Error with SQL:  " + sql, se);
+		}
+		
+		return result;
+	}
+	
+	// Returns the number of rows inserted
+	public int insert(Connection con, Table table, Object[][] values) throws SQLException 
+	{
+		int count = 0;
+		int pendingValues = 0;
+		
+		assert( isValidInsert(table, values));
+		
+		String sql = insertSql(table);
+		
+		try (PreparedStatement ps = con.prepareStatement(sql))
+		{
+			for (int row = 0; row < values.length; ++row) {
+				Object[] data = values[row];
+				
+				assert(null != data);
+				assert(data.length == table.getNumColumns());
+				
+				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();
+				
+				int rowsFlushed = checkFlushBatch(ps, pendingValues, false);
+				if (rowsFlushed > 0) {
+					count += rowsFlushed;
+					pendingValues = 0;
+				}
+			}
+			
+			count += checkFlushBatch(ps, pendingValues, true);
+		}
+		
+		return count;
+	}
+	
+	int checkFlushBatch(PreparedStatement ps, int pendingValues, boolean forceFlush) throws SQLException
+ 	{
+		int count = 0;
+		
+		if (forceFlush || (pendingValues >= PENDING_LIMIT)) 
+		{
+			int[] updateCounts = ps.executeBatch();
+			for (int i = 0; i < updateCounts.length; ++i) {
+				if (updateCounts[i] > 0) {
+					count += updateCounts[i];
+				}
+			}
+		}
+		
+		return count;
+	}
+	
+	String insertSql(Table table) {
+		StringBuilder sb = new StringBuilder("INSERT INTO ");
+		sb.append(table.getName())
+		  .append(" VALUES (");
+		
+		for (int i = 0; i < table.getNumColumns(); ++i) {
+			if (i > 0) {
+				sb.append(",");
+			}
+			sb.append("?");
+		}
+		sb.append(")");
+		
+		return sb.toString();
+	}
+	
+	protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit) 
+	{
+		StringBuilder sb = new StringBuilder("SELECT ");
+		
+		boolean firstColumn = true;
+		for (Column column : columns) {
+			if (firstColumn) {
+				firstColumn = false;
+			}
+			else {
+				sb.append(", ");
+			}
+			sb.append(column.getName());
+		}
+		
+		sb.append(" FROM ");
+		
+		boolean firstTable = true;
+		for (Table table : tables) {
+			if (firstTable) {
+				firstTable = false;
+			}
+			else {
+				sb.append(", ");
+			}
+			sb.append(table.getName());
+		}
+		
+		if (null != conditions && conditions.length > 0) {			
+			sb.append(" WHERE ");
+			
+			boolean firstCondition = true;
+			
+			for (Condition condition : conditions) {
+				if (firstCondition) {
+					firstCondition = false;
+				}
+				else {
+					sb.append(" AND ");
+				}
+				
+				sb.append(condition.getColumn().getName())
+				  .append(condition.getOperation().getSql());
+			}
+		}
+
+		if (null != sorts && sorts.length > 0) {
+			sb.append(" ORDER BY ");
+			
+			boolean firstSort = true;
+			
+			for (Sort sort : sorts) {
+				if (firstSort) {
+					firstSort = false;
+				}
+				else {
+					sb.append(", ");
+				}
+				
+				sb.append(sort.getColumn().getName());
+				
+				if (sort.getDirection().equals(Sort.Direction.ASCENDING)) {
+					sb.append(" ASC ");
+				}
+				else {
+					sb.append(" DESC ");
+				}
+			}
+		}
+		
+		if (limit > 0) {
+			sb.append(" LIMIT " + limit + " ");
+		}
+		
+		return sb.toString();
+	}
 	
 	protected String typeName(Type type) {
+		// Special case:  TIMESTAMPTZ stored as INTEGER (milliseconds since the epoch)
+		// Reading a TIMESTAMPTZ back from the DB, and converting it to a java.util.Date,
+		// is fraught with peril.  The best way around this is to store the dates in 
+		// milliseconds-since-the-epoch (01.01.1970 00:00:00.000 UTC).
+		if (Type.TIMESTAMPTZ.equals(type)) {
+			return Type.INTEGER.toString();
+		}
+		
 		return type.toString();
 	}
 	
@@ -76,4 +347,53 @@ public abstract class DbDriver {
 		
 		return sb.toString();
 	}	
+	
+	protected String dropTableSql(Table table) {
+		assert(null != table);
+		assert(null != table.getName());
+		
+		return "DROP TABLE " + table.getName();
+	}
+	
+	protected String createSequenceSql(Sequence seq) {
+		assert(null != seq);
+		assert(null != seq.getName());
+		
+		return "CREATE SEQUENCE " + seq.getName();
+	}
+	
+	protected String dropSequenceSql(Sequence seq) {
+		assert(null != seq);
+		assert(null != seq.getName());
+		
+		return "DROP SEQUENCE " + seq.getName();
+	}
+	
+	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);
+		}
+	}
 }