From a683a5834138300c924274d1cda66a4a359222c5 Mon Sep 17 00:00:00 2001 From: Chris Jaekl Date: Sat, 22 Aug 2015 21:58:07 +0900 Subject: [PATCH 1/1] Initial commit. --- prod/net/jaekl/cfb/db/CfbSchema.java | 61 +++++++++++++ prod/net/jaekl/cfb/db/Column.java | 46 ++++++++++ prod/net/jaekl/cfb/db/Schema.java | 86 +++++++++++++++++++ prod/net/jaekl/cfb/db/Table.java | 40 +++++++++ prod/net/jaekl/cfb/db/driver/DbDriver.java | 79 +++++++++++++++++ .../jaekl/cfb/db/driver/PostgresqlDriver.java | 32 +++++++ .../cfb/db/driver/PostgresqlDriverTest.java | 66 ++++++++++++++ 7 files changed, 410 insertions(+) create mode 100644 prod/net/jaekl/cfb/db/CfbSchema.java create mode 100644 prod/net/jaekl/cfb/db/Column.java create mode 100644 prod/net/jaekl/cfb/db/Schema.java create mode 100644 prod/net/jaekl/cfb/db/Table.java create mode 100644 prod/net/jaekl/cfb/db/driver/DbDriver.java create mode 100644 prod/net/jaekl/cfb/db/driver/PostgresqlDriver.java create mode 100644 test/net/jaekl/cfb/db/driver/PostgresqlDriverTest.java diff --git a/prod/net/jaekl/cfb/db/CfbSchema.java b/prod/net/jaekl/cfb/db/CfbSchema.java new file mode 100644 index 0000000..b298f57 --- /dev/null +++ b/prod/net/jaekl/cfb/db/CfbSchema.java @@ -0,0 +1,61 @@ +package net.jaekl.cfb.db; + +import static net.jaekl.cfb.db.Column.Null.*; +import static net.jaekl.cfb.db.Column.Type.*; +import net.jaekl.cfb.db.driver.DbDriver; + + +public class CfbSchema extends Schema { + // Define each table as follows: + // { + // { table_name }, + // { column_name, type, width (-1 for default), null/not_null } + // } + private static final Object[][][] TABLES = { + { + { "BUGS" }, + { "BUGID", INTEGER, -1, NOT_NULL }, + { "TYPE", VARCHAR, 80, NOT_NULL }, + { "SHORTDESCR", VARCHAR, 128, NOT_NULL }, + { "LONGDESCR", VARCHAR, 128, NOT_NULL }, + { "DETAILS", VARCHAR, 4096, NOT_NULL } + }, + { + { "CATEGORIES" }, + { "CATEGORYID", INTEGER, -1, NOT_NULL }, + { "DESCRIPTION", VARCHAR, 128, NOT_NULL }, + { "ABBREVIATION", CHAR, 1, NOT_NULL }, + { "DETAILS", VARCHAR, 4096, NOT_NULL } + }, + { + { "FOUND" }, + { "FOUNDID", INTEGER, -1, NOT_NULL }, + { "BUGID", INTEGER, -1, NOT_NULL }, + { "CATEGORYID", INTEGER, -1, NOT_NULL }, + { "FIRSTLOCID", INTEGER, -1, NOT_NULL }, + { "SECONDLOCID", INTEGER, -1, NULL }, + { "THIRDLOCID", INTEGER, -1, NULL } + }, + { + { "LOCATION" }, + { "LOCID", INTEGER, -1, NOT_NULL }, + { "CLASSNAME", VARCHAR, 256, NOT_NULL }, + { "STARTLINE", INTEGER, -1, NULL }, + { "ENDLINE", INTEGER, -1, NULL } + }, + { + // Runs of FindBugs, normally one per build version + { "RUNS " }, + { "RUNID", INTEGER, -1, NOT_NULL }, + { "VERSION", VARCHAR, 32, NULL }, + { "START", TIMESTAMPTZ, -1, NOT_NULL }, + { "END", TIMESTAMPTZ, -1, NOT_NULL } + } + }; + + public CfbSchema(DbDriver driver) { + super("CFB", driver); + + addTables(TABLES); + } +} diff --git a/prod/net/jaekl/cfb/db/Column.java b/prod/net/jaekl/cfb/db/Column.java new file mode 100644 index 0000000..bd06570 --- /dev/null +++ b/prod/net/jaekl/cfb/db/Column.java @@ -0,0 +1,46 @@ +package net.jaekl.cfb.db; + +public class Column { + public enum Type { + CHAR, INTEGER, TIMESTAMP, TIMESTAMPTZ, VARCHAR + }; + public enum Null { + NOT_NULL, NULL + } + + String m_name; + Type m_type; + int m_width; + Null m_null; + + public Column(String name, Type type, int width, Null canBeNull) + { + m_name = name; + m_type = type; + m_width = width; + m_null = canBeNull; + } + + public String getName() { return m_name; } + public Type getType() { return m_type; } + public int getWidth() { return m_width; } + public Null getNull() { return m_null; } + + // Create a column based on an array of Objects + // Input format: { name, type, width, can_be_null } + public static Column construct(Object[] spec) { + assert(null != spec); + assert(4 == spec.length); + assert(spec[0] instanceof String); + assert(spec[1] instanceof Type); + assert(spec[2] instanceof Number); + assert(spec[3] instanceof Null); + + String name = (String)(spec[0]); + Type type = (Type)(spec[1]); + Number width = (Number)(spec[2]); + Null canBeNull = (Null)(spec[3]); + + return new Column(name, type, width.intValue(), canBeNull); + } +} diff --git a/prod/net/jaekl/cfb/db/Schema.java b/prod/net/jaekl/cfb/db/Schema.java new file mode 100644 index 0000000..bfcfdb8 --- /dev/null +++ b/prod/net/jaekl/cfb/db/Schema.java @@ -0,0 +1,86 @@ +package net.jaekl.cfb.db; + +import java.sql.Connection; +import java.sql.DatabaseMetaData; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.HashSet; + +import net.jaekl.cfb.db.driver.DbDriver; + +public class Schema { + String m_name; + DbDriver m_driver; + ArrayList m_tables; + + public Schema(String name, DbDriver driver) { + m_name = name; + m_driver = driver; + m_tables = new ArrayList
(); + } + + public boolean ensureDbInitialized(Connection con) throws SQLException { + assert(null != con); + boolean result = true; + + if (allTablesPresent(con)) { + return true; + } + + result = createAllTables(con); + + return result; + } + + boolean allTablesPresent(Connection con) throws SQLException + { + assert(null != con); + + DatabaseMetaData dbmd = con.getMetaData(); + HashSet extantTables = new HashSet(); + + try (ResultSet rs = dbmd.getTables(null, null, null, new String[]{"TABLE"})) { + while (rs.next()) { + extantTables.add(rs.getString(3)); + } + } + + for (Table table : m_tables) { + if ( ! extantTables.contains(table.getName()) ) { + // One or more tables missing + return false; + } + } + + // We could be more thorough here, and check that the expected columns are in place. + + return true; + } + + boolean createAllTables(Connection con) throws SQLException { + for (Table table : m_tables) { + if (!m_driver.createTable(con, table)) { + return false; + } + } + return true; + } + + void addTable(Table table) { + m_tables.add(table); + } + + // Add a list of tables. + // Define each table in the list as follows: + // { + // { table_name }, + // { column_name, type, width (-1 for default), null/not_null } + // } + void addTables(Object[][][] tables) + { + for (Object[][] table : tables) { + addTable(Table.construct(table)); + } + } +} diff --git a/prod/net/jaekl/cfb/db/Table.java b/prod/net/jaekl/cfb/db/Table.java new file mode 100644 index 0000000..64b2caa --- /dev/null +++ b/prod/net/jaekl/cfb/db/Table.java @@ -0,0 +1,40 @@ +package net.jaekl.cfb.db; + +import java.util.ArrayList; +import java.util.Arrays; + +public class Table { + String m_name; + ArrayList m_columns; + + public Table(String name, Column[] columns) { + m_name = name; + m_columns = new ArrayList(Arrays.asList(columns)); + } + + public String getName() { return m_name; } + public int getNumColumns() { return m_columns.size(); } + public Column getColumn(int idx) { return m_columns.get(idx); } + + // Construct a table from an array of objects like this: + // { + // { table_name }, + // { column_name, type, width (-1 for default), null/not_null } + // } + public static Table construct(Object[][] spec) + { + assert(null != spec); + assert(spec.length > 1); + assert(1 == spec[0].length); + assert(spec[0][0] instanceof String); + + String name = (String)(spec[0][0]); + Column[] columns = new Column[spec.length - 1]; + + for (int idx = 1; idx < spec.length; ++idx) { + columns[idx - 1] = Column.construct(spec[idx]); + } + + return new Table(name, columns); + } +} diff --git a/prod/net/jaekl/cfb/db/driver/DbDriver.java b/prod/net/jaekl/cfb/db/driver/DbDriver.java new file mode 100644 index 0000000..169cfee --- /dev/null +++ b/prod/net/jaekl/cfb/db/driver/DbDriver.java @@ -0,0 +1,79 @@ +package net.jaekl.cfb.db.driver; + +import static net.jaekl.cfb.db.Column.Null.*; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; + +import net.jaekl.cfb.db.Column; +import net.jaekl.cfb.db.Column.Type; +import net.jaekl.cfb.db.Table; + +public abstract class DbDriver { + DbDriver() { + + } + + // Load the JDBC driver + public abstract void load() throws ClassNotFoundException; + + public abstract Connection connect(String host, int port, String user, String pass); + + public boolean createTable(Connection con, Table table) throws SQLException { + String sql = createTableSql(table); + try (PreparedStatement ps = con.prepareStatement(sql)) { + ps.executeUpdate(); + } + + return true; + } + + public abstract ResultSet selectColumnsFromWhere(Column[] columns, Table[] tables, String where); + + protected String typeName(Type type) { + return type.toString(); + } + + protected String createColumnSql(Column column) + { + String result = column.getName() + " " + typeName(column.getType()); + if (column.getWidth() > 0) { + result += "(" + column.getWidth() + ")"; + } + + if (NOT_NULL == column.getNull()) { + result += " NOT NULL"; + } + else { + result += " NULL"; + } + + return result; + } + + protected String createTableSql(Table table) + { + assert(null != table); + assert(null != table.getName()); + assert(table.getNumColumns() > 0); + + StringBuilder sb = new StringBuilder(); + + sb.append("CREATE TABLE ") + .append(table.getName()) + .append("("); + + for (int idx = 0; idx < table.getNumColumns(); ++idx) { + if (idx > 0) { + sb.append(", "); + } + sb.append(createColumnSql(table.getColumn(idx))); + } + + sb.append(")"); + + return sb.toString(); + } +} diff --git a/prod/net/jaekl/cfb/db/driver/PostgresqlDriver.java b/prod/net/jaekl/cfb/db/driver/PostgresqlDriver.java new file mode 100644 index 0000000..0f6a12d --- /dev/null +++ b/prod/net/jaekl/cfb/db/driver/PostgresqlDriver.java @@ -0,0 +1,32 @@ +package net.jaekl.cfb.db.driver; + +import java.sql.Connection; +import java.sql.ResultSet; + +import net.jaekl.cfb.db.Column; +import net.jaekl.cfb.db.Table; + +public class PostgresqlDriver extends DbDriver { + + @Override + public void load() throws ClassNotFoundException { + // This should no longer be necessary, so long as we're using + // JDBC 4 (which came out with JDK 6) and an updated driver. + // But, it shouldn't hurt, either. + Class.forName("org.postgresql.Driver"); + } + + @Override + public Connection connect(String host, int port, String user, String pass) { + // TODO Auto-generated method stub + return null; + } + + @Override + public ResultSet selectColumnsFromWhere(Column[] columns, Table[] tables, + String where) { + // TODO Auto-generated method stub + return null; + } + +} diff --git a/test/net/jaekl/cfb/db/driver/PostgresqlDriverTest.java b/test/net/jaekl/cfb/db/driver/PostgresqlDriverTest.java new file mode 100644 index 0000000..e803b98 --- /dev/null +++ b/test/net/jaekl/cfb/db/driver/PostgresqlDriverTest.java @@ -0,0 +1,66 @@ +package net.jaekl.cfb.db.driver; + +import static net.jaekl.cfb.db.Column.Null.*; +import static net.jaekl.cfb.db.Column.Type.*; +import static org.junit.Assert.*; +import net.jaekl.cfb.db.Column; +import net.jaekl.cfb.db.Table; +import net.jaekl.cfb.db.driver.PostgresqlDriver; + +import org.junit.Test; + +public class PostgresqlDriverTest { + + @Test + public void testCreateColumnSql() { + final Object[][] input = { + {"FIRSTNAME", VARCHAR, 20, NOT_NULL}, + {"LASTNAME", VARCHAR, 32, NOT_NULL}, + {"COMPANYNAME", VARCHAR, 25, NULL}, + {"AGE", INTEGER, -1, NULL}, + {"GENDER", CHAR, 1, NOT_NULL} + }; + final String[] expected = { + "FIRSTNAME VARCHAR(20) NOT NULL", + "LASTNAME VARCHAR(32) NOT NULL", + "COMPANYNAME VARCHAR(25) NULL", + "AGE INTEGER NULL", + "GENDER CHAR(1) NOT NULL" + }; + + PostgresqlDriver driver = new PostgresqlDriver(); + + for (int idx = 0; idx < input.length; ++idx) { + Object[] spec = input[idx]; + Column column = Column.construct(spec); + String actual = driver.createColumnSql(column); + + assertEquals(expected[idx], actual); + } + + } + + @Test + public void testCreateTableSql() { + Object[][][] input = { + { + {"STUDENT"}, + {"ID", INTEGER, -1, NOT_NULL}, + {"NAME", VARCHAR, 64, NOT_NULL} + } + }; + + String[] expected = { + "CREATE TABLE STUDENT(ID INTEGER NOT NULL, NAME VARCHAR(64) NOT NULL)" + }; + + PostgresqlDriver driver = new PostgresqlDriver(); + + for (int idx = 0; idx < input.length; ++idx) { + Table table = Table.construct(input[idx]); + String actual = driver.createTableSql(table); + assertEquals(expected[idx], actual); + } + } + +} -- 2.39.2