diff options
Diffstat (limited to 'src')
8 files changed, 752 insertions, 0 deletions
diff --git a/src/main/java/de/xenoworld/ormpaloompa/Table.java b/src/main/java/de/xenoworld/ormpaloompa/Table.java new file mode 100644 index 0000000..96497b6 --- /dev/null +++ b/src/main/java/de/xenoworld/ormpaloompa/Table.java @@ -0,0 +1,346 @@ +package de.xenoworld.ormpaloompa; + +import de.xenoworld.ormpaloompa.annotations.Field; +import de.xenoworld.ormpaloompa.annotations.TableInfo; +import org.apache.commons.lang3.tuple.Pair; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.Optional; +import java.util.stream.Collectors; +import java.util.stream.IntStream; +import java.util.stream.Stream; + +// TODO: Filter out fields where the value is null +// TODO: Filter out the identity fields when updating +// (insert identities only when they are not null → auto IDs, but things like the term-database do +// have a string as key) +// TODO: Use more Optionals and get rid of exception throwing where possible +// Name: Ormpaloompa +/** + * A SQLite table of a POJO `T` as a proxy. + * + * This makes writing and retrieving simple things from the SQLite database + * easier. + * + * It takes a simple class with some annotations such as: + * + * [source,java] + * ---- + * class Bird { + * @Field(identity = true) + * public Integer id; + * + * @Field + * public String name; + * } + * ---- + * + * and makes it possible to insert and query for objects: + * + * [source,java] + * ---- + * Table<Bird> t = new Table<>(Bird.class, someDatabaseConnection); + * Bird newBird = new Bird(); + * newBird.name = "A new bird"; + * Object id = table.insert(newBird); + * Bird storedBird = t.getById(id).get(); + * ---- + * + * @param <T> + */ +public class Table<T> { + private final Class<T> tableClass; + private final Connection connection; + private final String tableName; + + private final TableField identity; + private ArrayList<TableField> tableFields; + + private enum Query { + COUNT("SELECT COUNT(*) FROM %s"), + GET_BY_ID("SELECT * FROM %s WHERE %s = ?"), + INSERT("INSERT INTO %s (%s) VALUES (%s)"), + WHERE("SELECT * FROM %s WHERE %s"), + UPDATE_BY_ID("UPDATE %s SET %s = ? WHERE %s = ?"),; + + final String query; + + Query(String query) { + this.query = query; + } + + @Override + public String toString() { + return this.query; + } + } + + + private class TableField { + final String name; + final String dflt; + final boolean identity; + + private TableField(String name, String dflt, boolean identity) { + this.name = name; + this.dflt = dflt; + this.identity = identity; + } + + @Override + public String toString() { + return "TableField{" + + "name='" + name + '\'' + + ", dflt='" + dflt + '\'' + + ", identity=" + identity + + '}'; + } + } + + public Table(Class<T> tableClass, Connection connection) { + this.tableClass = tableClass; + this.connection = connection; + + tableFields = new ArrayList<>(); + + Stream.of(tableClass.getFields()) + .map(field -> Pair.of(field.getName(), field.getAnnotation(Field.class))) + .forEach(p -> tableFields.add( + new TableField( + p.getRight().name().isEmpty() ? p.getLeft() : p.getRight().name(), + p.getRight().dflt(), + p.getRight().identity()))); + + this.tableName = discoverTableName(tableClass); + this.identity = discoverIdentity(tableClass); + } + + // TODO: This is kind of an idiotic contraption + private TableField discoverIdentity(Class<T> tableClass) { + return tableFields.stream() + .filter(f -> f.identity) + .findFirst() + .orElseGet(() -> tableFields.stream() + .filter(f -> f.name.toLowerCase().equals("id")) + .findFirst() + .orElseThrow(() -> new RuntimeException( + String.format("%s: No identity given, and no field 'id' present.", + tableClass.getName() + ) + ) + )); + } + + /** + * Get name for table from TableInfo annotation or append "s" to the lower + * case table name. + * + * @param tableClass table to inspect + * @return name of table in database + */ + private String discoverTableName(Class<T> tableClass) { + TableInfo tableInfo = tableClass.getAnnotation(TableInfo.class); + if (tableInfo != null && !tableInfo.tableName().isEmpty()) { + return tableInfo.tableName(); + } + + return tableClass.getSimpleName().toLowerCase() + "s"; + } + + /** + * Return the number of rows in the table, uses `COUNT(*)`. + * + * @return total number of rows + * @throws SQLException + */ + public Integer count() throws SQLException { + String query = String.format(Query.COUNT.toString(), tableName); + PreparedStatement stmt = connection.prepareStatement(query); + ResultSet result = stmt.executeQuery(); + result.next(); + return result.getInt(1); + } + + /** + * Get a `T` by Identity. + * + * @param id identity + * @return `Optional<T>`, `empty()` if no object could be found, or if an + * error occurred. + */ + public Optional<T> getById(Object id) { + String query = String.format(Query.GET_BY_ID.toString(), tableName, identity.name); + + try { + PreparedStatement stmt = connection.prepareStatement(query); + stmt.setObject(1, id); + ResultSet result = stmt.executeQuery(); + + if (!result.next()) { + return Optional.empty(); + } + + T t = fromResultSet(result); + + return Optional.of(t); + } catch (Exception e) { + e.printStackTrace(); + return Optional.empty(); + } + } + + /** + * Return matching rows. + * + * This will return a `Stream` of `T`. + * + * The optional `args` parameter an be used in situations where the + * WhereQuery needs parameters, for example: + * + * [source,java] + * ---- + * find(where("foo = ?").limit(1), "bar") + * ---- + * + * This will create a query for `… WHERE foo = bar …`. + * + * @param whereQuery a query + * @param args (optional) arguments to WHERE clause + * @return a Stream of matching `T` + */ + public Stream<T> find(WhereQuery whereQuery, Object... args) { + // TODO: Make try-mess better + try { + String query = String.format(Query.WHERE.toString(), tableName, whereQuery.toString()); + PreparedStatement stmt = connection.prepareStatement(query); + + for (int i = 0; i < args.length; i++) { + stmt.setObject(i + 1, args[i]); + } + + ResultSet resultSet = stmt.executeQuery(); + + Stream.Builder<T> tBuilder = Stream.builder(); + + while (resultSet.next()) { + tBuilder.add(fromResultSet(resultSet)); + } + return tBuilder.build(); + } catch (Exception e) { + e.printStackTrace(); + return Stream.empty(); + } + } + + private T fromResultSet(ResultSet result) throws InstantiationException, IllegalAccessException { + T t = tableClass.newInstance(); + + tableFields.forEach(f -> { + try { + t.getClass().getField(f.name).set(t, result.getObject(f.name)); + } catch (IllegalAccessException | NoSuchFieldException | SQLException e) { + e.printStackTrace(); + } + }); + return t; + } + + public Long insert(T t) throws SQLException { + String fields = tableFields + .stream() + .map(f -> f.name) + .collect(Collectors.joining(",")); + + StringBuffer placeholder = new StringBuffer("?"); + + IntStream.range(0, tableFields.size() - 1).forEach(i -> placeholder.append(", ?")); + + String query = String.format(Query.INSERT.toString(), tableName, fields, placeholder); + PreparedStatement stmt = connection.prepareStatement(query); + + fillStatementFromObject(t, stmt); + + stmt.execute(); + + return stmt.getGeneratedKeys().getLong(1); + } + + public void update(T t) { + String fields = tableFields + .stream() + .map(f -> f.name) + .collect(Collectors.joining(" = ?, ")); + + String query = String.format(Query.UPDATE_BY_ID.toString(), tableName, fields, identity.name); + + try { + PreparedStatement stmt = connection.prepareStatement(query); + int order = fillStatementFromObject(t, stmt); + stmt.setObject(order, t.getClass().getField(identity.name).get(t)); + stmt.execute(); + } catch (SQLException | NoSuchFieldException | IllegalAccessException e) { + e.printStackTrace(); + } + } + + private int fillStatementFromObject(T t, PreparedStatement stmt) { + final int[] order = {1}; + + tableFields.stream().forEach(f -> { + try { + Object o = t.getClass().getField(f.name).get(t); + stmt.setObject(order[0]++, o); + } catch (IllegalAccessException | NoSuchFieldException | SQLException e) { + e.printStackTrace(); + } + }); + + return order[0]; + } + + /** + * Take `t` and insert a new entry, or update an existing entry. + * + * @param t + * @return Optional of insert Id (a Long) + * @throws NoSuchFieldException + * @throws IllegalAccessException + * @throws SQLException + */ + public Optional<Object> updateOrInsert(T t) throws NoSuchFieldException, IllegalAccessException, SQLException { + Optional<T> existingEntry = Optional.empty(); + Optional<Object> idValue = retrieveIdValue(t); + + if (idValue.isPresent()) { + existingEntry = getById(idValue.get()); + } + + if (existingEntry.isPresent()) { + update(t); + return Optional.empty(); + } else { + return Optional.of(insert(t)); + } + } + + /** + * Retrieve the current value of the `@Identity` field. + * + * Will return an empty Optional if the value is null, or if there was + * an Exception. + * + * @param t object to retrieve identity value from + * @return value of identity, or empty on null or error + */ + public Optional<Object> retrieveIdValue(T t) { + try { + return Optional.ofNullable(t.getClass().getField(identity.name).get(t)); + } catch (NoSuchFieldException | IllegalAccessException e) { + return Optional.empty(); + } + } +} diff --git a/src/main/java/de/xenoworld/ormpaloompa/WhereQuery.java b/src/main/java/de/xenoworld/ormpaloompa/WhereQuery.java new file mode 100644 index 0000000..fcc42eb --- /dev/null +++ b/src/main/java/de/xenoworld/ormpaloompa/WhereQuery.java @@ -0,0 +1,52 @@ +package de.xenoworld.ormpaloompa; + +import java.util.Optional; + +public class WhereQuery { + private Optional<Integer> limit = Optional.empty(); + private Optional<String> orderFields = Optional.empty(); + private Order order; + private String where; + + public enum Order { + DESC, + ASC + } + + @Override + public String toString() { + StringBuffer query = new StringBuffer(); + + query.append(where); + + orderFields.ifPresent( + o -> query.append(String.format(" ORDER BY %s %s", o, order.toString())) + ); + + limit.ifPresent( + i -> query.append(String.format(" LIMIT %d", i)) + ); + + return query.toString(); + } + + public WhereQuery(String where) { + this.where = where; + } + + public WhereQuery limit(Integer limit) { + this.limit = Optional.of(limit); + return this; + } + + public WhereQuery orderBy(String fields, Order order) { + orderFields = Optional.of(fields); + this.order = order; + return this; + } + + public static WhereQuery where(String where) { + return new WhereQuery(where); + } + +} diff --git a/src/main/java/de/xenoworld/ormpaloompa/annotations/Field.java b/src/main/java/de/xenoworld/ormpaloompa/annotations/Field.java new file mode 100644 index 0000000..5cd917b --- /dev/null +++ b/src/main/java/de/xenoworld/ormpaloompa/annotations/Field.java @@ -0,0 +1,16 @@ +package de.xenoworld.ormpaloompa.annotations; + +import java.lang.annotation.Retention; +import java.lang.annotation.RetentionPolicy; + +/** + * Annotate a public field. + */ +@Retention(RetentionPolicy.RUNTIME) +public @interface Field { + String name() default ""; + + String dflt() default ""; + + boolean identity() default false; +}
\ No newline at end of file diff --git a/src/main/java/de/xenoworld/ormpaloompa/annotations/TableInfo.java b/src/main/java/de/xenoworld/ormpaloompa/annotations/TableInfo.java new file mode 100644 index 0000000..5094c5d --- /dev/null +++ b/src/main/java/de/xenoworld/ormpaloompa/annotations/TableInfo.java @@ -0,0 +1,12 @@ +package de.xenoworld.ormpaloompa.annotations; + +import java.lang.annotation.Retention; +import java.lang.annotation.RetentionPolicy; + +/** + * Optional table annotation, can be used to set the table name in the database + */ +@Retention(RetentionPolicy.RUNTIME) +public @interface TableInfo { + String tableName() default ""; +} diff --git a/src/main/java/overview.adoc b/src/main/java/overview.adoc new file mode 100644 index 0000000..90a82f6 --- /dev/null +++ b/src/main/java/overview.adoc @@ -0,0 +1,18 @@ += Eduard XVII — The loyal MUC servant + +== Continuous Integration + +We currently use the CI of Gitlab.com, but it might be warranted to use a +dedicated runner which could execute the requests much faster. This is work +for Future Homer. + +Currently everything is configured in the file `.gitlab-ci.yml`: + +[source,yaml] +---- +include::../../../.gitlab-ci.yml[] +---- + +NOTE: Replacing `ec.SunEC` is done here because there were problems in the + Docker container with OpenJDK. Apparently making a HTTPS connection + to the Gradle repositories would not work.
\ No newline at end of file diff --git a/src/test/java/de/xenoworld/ormpaloompa/TableTest.java b/src/test/java/de/xenoworld/ormpaloompa/TableTest.java new file mode 100644 index 0000000..79b3a0c --- /dev/null +++ b/src/test/java/de/xenoworld/ormpaloompa/TableTest.java @@ -0,0 +1,242 @@ +package de.xenoworld.ormpaloompa; + +import de.xenoworld.ormpaloompa.annotations.Field; +import de.xenoworld.ormpaloompa.annotations.TableInfo; +import de.xenoworld.ormpaloompa.testutils.DBRule; +import org.junit.Ignore; +import org.junit.Rule; +import org.junit.Test; + +import java.sql.SQLException; +import java.util.List; +import java.util.Optional; +import java.util.stream.Collectors; + +import static de.xenoworld.ormpaloompa.WhereQuery.where; +import static org.hamcrest.MatcherAssert.assertThat; +import static org.hamcrest.Matchers.hasSize; +import static org.hamcrest.Matchers.is; + +/** + * Test object with explicit identity + */ +class Bird { + @Field(identity = true) + public Integer id; + + @Field + public String name; + + @Field + public String description; +} + +/** + * Test object with no explicit identity + */ +class Rabbit { + @Field + public Integer id; + + @Field + public String name; +} + +/** + * Test object with two identities. The first identity should be selected. + * Also, the identity here is a string. + */ +@TableInfo(tableName = "foxes") +class Fox { + @Field(identity = true) + public String name; + + @Field(identity = true) + public Integer id; +} + +/** + * A table with no id, this should throw a RuntimeException when used. + */ +@TableInfo +class Monkey { + @Field + public String name; +} + +public class TableTest { + static String[] FIXTURES = { + "CREATE TABLE birds (id INTEGER PRIMARY KEY, name TEXT, description TEXT);", + "INSERT INTO birds " + + "(id, name) " + + "VALUES " + + "(1, 'fluffy bird')," + + "(2, 'supersonic bird');", + + "CREATE TABLE rabbits (id INTEGER PRIMARY KEY, name TEXT);", + "INSERT INTO rabbits " + + "(id, name) " + + "VALUES " + + "(1, 'tiny rabbit')," + + "(2, 'giant rabbit');", + + "CREATE TABLE foxes (id INTEGER, name TEXT PRIMARY KEY);", + "INSERT INTO foxes " + + "(id, name) " + + "VALUES " + + "(1, 'red fox')," + + "(2, 'blue fox');", + + "CREATE TABLE snails (name TEXT PRIMARY KEY);", + "INSERT INTO snails " + + "(name) " + + "VALUES " + + "('slow snail')," + + "('fast snail');" + + }; + + @Rule + public DBRule dbRule = new DBRule(FIXTURES); + + /** + * The field `id` should be annotated as `@Field`, but it should not have + * property `identity` set to `true`. The field should be recognised as + * identity by virtue of being named `id`. + */ + @Test + public void testGetById_ImplicitIdFieldSelection() { + Table<Rabbit> table = new Table<>(Rabbit.class, dbRule.getConnection()); + + Rabbit rabbit = table.getById(1L).get(); + assertThat(rabbit.name, is("tiny rabbit")); + } + + /** + * This also effectively test the TableInfo annotation, declaring the table + * to be "foxes" instead of "foxs". + */ + @Test + public void testGetById_IdIsATextField() { + Table<Fox> table = new Table<>(Fox.class, dbRule.getConnection()); + + Fox fox = table.getById("red fox").get(); + + assertThat(fox.id, is(1)); + assertThat(fox.name, is("red fox")); + } + + @Test(expected = RuntimeException.class) + public void testNewTable_TableHasNoId() { + new Table<>(Monkey.class, dbRule.getConnection()); + } + + @Test + public void testCount() throws Exception { + Table<Bird> table = new Table<>(Bird.class, dbRule.getConnection()); + + assertThat(table.count(), is(2)); + } + + @Test + public void testGetById() throws Exception { + Table<Bird> table = new Table<>(Bird.class, dbRule.getConnection()); + + Bird bird = table.getById(1L).get(); + + assertThat(bird.name, is("fluffy bird")); + } + + @Test + public void testGetById_nonexistentId() throws Exception { + Table<Bird> table = new Table<>(Bird.class, dbRule.getConnection()); + + assertThat(table.getById(0L).isPresent(), is(false)); + } + + @Test + public void testInsert() throws Exception { + Table<Bird> table = new Table<>(Bird.class, dbRule.getConnection()); + + Bird newBird = new Bird(); + newBird.name = "A new bird"; + + Object id = table.insert(newBird); + + assertThat(id, is(3L)); + + Bird storedBird = table.getById(id).get(); + + assertThat(storedBird.name, is(newBird.name)); + } + + // TODO: Future: Return Identity instead of ROWID on insert() + @Ignore + @Test + public void testInsert_withStringId() throws Exception { + Table<Fox> table = new Table<>(Fox.class, dbRule.getConnection()); + + Fox newFox = new Fox(); + newFox.name = "A new fox"; + + Object id = table.insert(newFox); + + assertThat(id, is("A new fox")); + + Fox storedFox = table.getById(id).get(); + + assertThat(storedFox.name, is(newFox.name)); + } + + @Test + public void testFind() throws IllegalAccessException, SQLException, InstantiationException { + Table<Bird> table = new Table<>(Bird.class, dbRule.getConnection()); + + List<Bird> list = table.find(where("id > ?") + .limit(1) + .orderBy("name", WhereQuery.Order.DESC), 0L) + .collect(Collectors.toList()); + + assertThat(list, hasSize(1)); + assertThat(list.get(0).name, is("supersonic bird")); + } + + @Test + public void testUpdate() throws Exception { + Table<Bird> table = new Table<>(Bird.class, dbRule.getConnection()); + + Bird someBird = table.getById(1).get(); + assertThat(someBird.name, is("fluffy bird")); + + someBird.name = "hard bird"; + table.update(someBird); + + Bird sameBird = table.getById(1L).get(); + assertThat(sameBird.name, is("hard bird")); + } + + @Test + public void testUpdateOrInsert() throws Exception { + Table<Bird> table = new Table<>(Bird.class, dbRule.getConnection()); + + Bird newBird = new Bird(); + newBird.name = "green bird"; + + Optional<Object> insertId; + + insertId = table.updateOrInsert(newBird); + assertThat("Table grew to three entries", table.count(), is(3)); + assertThat("A new entry was created", insertId.isPresent(), is(true)); + + insertId = table.updateOrInsert(newBird); + assertThat("Table grew to four entries", table.count(), is(4)); + assertThat("A second entry was created", insertId.isPresent(), is(true)); + + Bird presentBird = table.getById(insertId.get()).get(); + presentBird.description = "This is a test"; + + insertId = table.updateOrInsert(presentBird); + assertThat("Table size did not change", table.count(), is(4)); + assertThat("No new entries were added", insertId.isPresent(), is(false)); + } +}
\ No newline at end of file diff --git a/src/test/java/de/xenoworld/ormpaloompa/WhereQueryTest.java b/src/test/java/de/xenoworld/ormpaloompa/WhereQueryTest.java new file mode 100644 index 0000000..3f3f472 --- /dev/null +++ b/src/test/java/de/xenoworld/ormpaloompa/WhereQueryTest.java @@ -0,0 +1,22 @@ +package de.xenoworld.ormpaloompa; + +import org.junit.Test; + +import static org.junit.Assert.assertEquals; + +public class WhereQueryTest { + + @Test + public void testWhereQuery() { + WhereQuery q; + + q = WhereQuery.where("foo > 0 AND bar = ?").limit(5).orderBy("bar", WhereQuery.Order.ASC); + assertEquals("foo > 0 AND bar = ? ORDER BY bar ASC LIMIT 5", q.toString()); + + q = WhereQuery.where("foo > 0 AND bar = ?").orderBy("bar", WhereQuery.Order.DESC); + assertEquals("foo > 0 AND bar = ? ORDER BY bar DESC", q.toString()); + + q = WhereQuery.where("foo > 0 AND bar = ?"); + assertEquals("foo > 0 AND bar = ?", q.toString()); + } +}
\ No newline at end of file diff --git a/src/test/java/de/xenoworld/ormpaloompa/testutils/DBRule.java b/src/test/java/de/xenoworld/ormpaloompa/testutils/DBRule.java new file mode 100644 index 0000000..0bb12f7 --- /dev/null +++ b/src/test/java/de/xenoworld/ormpaloompa/testutils/DBRule.java @@ -0,0 +1,44 @@ +package de.xenoworld.ormpaloompa.testutils; + +import org.junit.rules.ExternalResource; + +import java.sql.*; +import java.util.stream.Stream; + +public class DBRule extends ExternalResource { + protected Connection connection; + protected String[] fixtures; + + public DBRule(String... fixtures) { + this.fixtures = fixtures; + } + + @Override + protected void after() { + try { + connection.close(); + } catch (SQLException ignored) {} + } + + @Override + protected void before() throws Throwable { + Class.forName("org.sqlite.JDBC"); + connection = DriverManager.getConnection("jdbc:sqlite::memory:"); + + Statement statement = connection.createStatement(); + + Stream.of(fixtures).forEach(s -> { + try { + statement.addBatch(s); + } catch (SQLException e) { + e.printStackTrace(); + } + }); + + statement.executeBatch(); + } + + public Connection getConnection() { + return connection; + } +} |