Initial commit

11 files changed, 798 insertions, 0 deletions

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..9d4716a
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,6 @@
+.idea/
+.gradle
+build/
+*.iml
+*.sqlite
+*.swp
diff --git a/build.gradle b/build.gradle
new file mode 100644
index 0000000..d9c6a07
--- /dev/null
+++ b/build.gradle
@@ -0,0 +1,39 @@
+apply plugin: 'java'
+
+group = 'de.xenoworld'
+version = '1.0-SNAPSHOT'
+
+description = ""
+
+sourceCompatibility = 1.8
+targetCompatibility = 1.8
+
+repositories {
+    jcenter()
+    mavenCentral()
+}
+
+configurations {
+    asciidoclet
+}
+
+dependencies {
+    asciidoclet 'org.asciidoctor:asciidoclet:1.+'
+    compile group: 'org.xerial', name: 'sqlite-jdbc', version:'3.8.7'
+    compile group: 'org.apache.commons', name: 'commons-lang3', version:'3.4'
+    testCompile group: 'org.hamcrest', name: 'hamcrest-all', version:'1.3'
+    testCompile group: 'org.mockito', name: 'mockito-all', version:'1.10.19'
+    testCompile group: 'junit', name: 'junit', version:'4.12'
+}
+
+// as shown here: http://asciidoctor.org/docs/asciidoclet/
+javadoc {
+    options.docletpath = configurations.asciidoclet.files.asType(List)
+    options.doclet = 'org.asciidoctor.Asciidoclet'
+    options.overview = "src/main/java/overview.adoc"
+    options.addStringOption "-base-dir", "${projectDir}"
+    options.addStringOption "-attribute",
+            "name=${project.name}," +
+            "version=${project.version}," +
+            "title-link=http://example.com[${project.name} ${project.version}]"
+}
diff --git a/settings.gradle b/settings.gradle
new file mode 100644
index 0000000..758dd6e
--- /dev/null
+++ b/settings.gradle
@@ -0,0 +1 @@
+rootProject.name = 'Ormpaloompa'
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;
+    }
+}