From 6f486197c5aae3246e8175b828fe3e4c8de3906d Mon Sep 17 00:00:00 2001 From: James Moger Date: Mon, 3 Nov 2014 11:05:52 -0500 Subject: [PATCH] Add support for SQLite 3.8.7 --- .classpath | 1 + README.markdown | 6 +- build.moxie | 2 + src/main/java/com/iciql/Db.java | 1 + src/main/java/com/iciql/IciqlException.java | 20 ++- src/main/java/com/iciql/SQLDialectSQLite.java | 115 ++++++++++++++++++ src/site/index.mkd | 4 +- src/site/jaqu_comparison.mkd | 2 +- src/test/java/com/iciql/test/IciqlSuite.java | 31 ++++- .../java/com/iciql/test/UpgradesTest.java | 12 +- 10 files changed, 182 insertions(+), 12 deletions(-) create mode 100644 src/main/java/com/iciql/SQLDialectSQLite.java diff --git a/.classpath b/.classpath index 7e0f9a7..4431ee5 100644 --- a/.classpath +++ b/.classpath @@ -8,6 +8,7 @@ + diff --git a/README.markdown b/README.markdown index 4c2b2c7..63ec22a 100644 --- a/README.markdown +++ b/README.markdown @@ -12,7 +12,7 @@ iciql **is**... iciql **is not**... - a complete alternative to JDBC -- designed to compete with more powerful database query tools like [jOOQ](http://jooq.sourceforge.net) or [Querydsl](http://source.mysema.com/display/querydsl/Querydsl) +- designed to compete with more powerful database query tools like [jOOQ](http://jooq.sourceforge.net) or [QueryDSL](http://source.mysema.com/display/querydsl/Querydsl) - designed to compete with enterprise ORM tools like [Hibernate](http://www.hibernate.org) or [mybatis](http://www.mybatis.org) Supported Databases (Unit-Tested) @@ -22,6 +22,7 @@ Supported Databases (Unit-Tested) - [Derby](http://db.apache.org/derby) 10.11 - [MySQL](http://mysql.com) 5.6 - [PostgreSQL](http://postgresql.org) 9.3 +- [SQLite](http://www.sqlite.org) 3.8 Support for others is possible and may only require creating a simple "dialect" class. @@ -37,7 +38,8 @@ iciql requires a Java 6 Runtime Environment (JRE) or a Java 6 Development Kit (J Getting help ------- Read the online documentation available at the [iciql website](http://iciql.com)
-Issues, binaries, & sources @ [Google Code](http://code.google.com/p/iciql) +Issues & sources @ [GitHub](http://github.com/gitblit/iciql) +Binaries @ [Iciql Maven Repository](http://gitblit.github.io/iciql/maven/) Building iciql ---------------- diff --git a/build.moxie b/build.moxie index 2656e5d..51034cc 100644 --- a/build.moxie +++ b/build.moxie @@ -87,6 +87,7 @@ properties: { derby.version : 10.11.1.1 mysql.version : 5.6 postgresql.version : 9.3 + sqlite.version : 3.8.7 } dependencies: @@ -96,6 +97,7 @@ dependencies: - provided 'org.apache.derby:derby:${derby.version}' - provided 'mysql:mysql-connector-java:5.1.33' - provided 'org.postgresql:postgresql:9.3-1102-jdbc4' +- provided 'org.xerial:sqlite-jdbc:${sqlite.version}' - provided 'org.slf4j:slf4j-api:1.6.1' - provided 'commons-pool:commons-pool:1.5.6' - provided 'commons-dbcp:commons-dbcp:1.4' diff --git a/src/main/java/com/iciql/Db.java b/src/main/java/com/iciql/Db.java index 67e73b0..23001d8 100644 --- a/src/main/java/com/iciql/Db.java +++ b/src/main/java/com/iciql/Db.java @@ -85,6 +85,7 @@ public class Db implements AutoCloseable { DIALECTS.put("MySQL", SQLDialectMySQL.class); DIALECTS.put("PostgreSQL", SQLDialectPostgreSQL.class); DIALECTS.put("Microsoft SQL Server", SQLDialectMSSQL.class); + DIALECTS.put("SQLite", SQLDialectSQLite.class); } private Db(Connection conn) { diff --git a/src/main/java/com/iciql/IciqlException.java b/src/main/java/com/iciql/IciqlException.java index 3f27b73..3db62cf 100644 --- a/src/main/java/com/iciql/IciqlException.java +++ b/src/main/java/com/iciql/IciqlException.java @@ -101,7 +101,7 @@ public class IciqlException extends RuntimeException { // MySQL duplicate primary key on insert iciqlCode = CODE_DUPLICATE_KEY; if (s.getErrorCode() == 1217) { - iciqlCode = CODE_CONSTRAINT_VIOLATION; + iciqlCode = CODE_CONSTRAINT_VIOLATION; } } else if ("23505".equals(state)) { // Derby duplicate primary key on insert @@ -154,6 +154,24 @@ public class IciqlException extends RuntimeException { } else if ("X0Y25".equals(state)) { // Derby constraint violation iciqlCode = CODE_CONSTRAINT_VIOLATION; + } else if (s.getMessage().startsWith("[SQLITE")) { + // SQLite error codes + final String msg = s.getMessage(); + switch (s.getErrorCode()) { + case 1: + iciqlCode = CODE_OBJECT_NOT_FOUND; + break; + case 19: + if (msg.contains("UNIQUE")) { + iciqlCode = CODE_DUPLICATE_KEY; + } else { + iciqlCode = CODE_CONSTRAINT_VIOLATION; + } + break; + default: + iciqlCode = s.getErrorCode(); + break; + } } else { // uncharacterized SQL code, we can always rely on iciqlCode != 0 in IciqlException iciqlCode = s.getErrorCode() == 0 ? CODE_UNCHARACTERIZED : s.getErrorCode(); diff --git a/src/main/java/com/iciql/SQLDialectSQLite.java b/src/main/java/com/iciql/SQLDialectSQLite.java new file mode 100644 index 0000000..c9bf8ec --- /dev/null +++ b/src/main/java/com/iciql/SQLDialectSQLite.java @@ -0,0 +1,115 @@ +/* + * Copyright 2014 James Moger. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package com.iciql; + +import com.iciql.TableDefinition.FieldDefinition; +import com.iciql.TableDefinition.IndexDefinition; +import com.iciql.util.IciqlLogger; +import com.iciql.util.StatementBuilder; + + +/** + * SQLite database dialect. + */ +public class SQLDialectSQLite extends SQLDialectDefault { + + @Override + public boolean supportsSavePoints() { + return false; + } + + @Override + public void configureDialect(Db db) { + super.configureDialect(db); + // enable foreign key constraint enforcement + db.executeUpdate("PRAGMA foreign_keys = ON;"); + } + + @Override + protected String prepareCreateTable(TableDefinition def) { + return "CREATE TABLE IF NOT EXISTS"; + } + + @Override + protected String prepareCreateView(TableDefinition def) { + return "CREATE VIEW IF NOT EXISTS"; + } + + @Override + public void prepareDropView(SQLStatement stat, TableDefinition def) { + StatementBuilder buff = new StatementBuilder("DROP VIEW IF EXISTS " + + prepareTableName(def.schemaName, def.tableName)); + stat.setSQL(buff.toString()); + return; + } + + @Override + public void prepareCreateIndex(SQLStatement stat, String schemaName, String tableName, + IndexDefinition index) { + StatementBuilder buff = new StatementBuilder(); + buff.append("CREATE "); + switch (index.type) { + case UNIQUE: + buff.append("UNIQUE "); + break; + case UNIQUE_HASH: + buff.append("UNIQUE "); + break; + default: + IciqlLogger.warn("{0} does not support hash indexes", getClass().getSimpleName()); + } + buff.append("INDEX IF NOT EXISTS "); + buff.append(index.indexName); + buff.append(" ON "); + // FIXME maybe we can use schemaName ? + // buff.append(prepareTableName(schemaName, tableName)); + buff.append(tableName); + buff.append("("); + for (String col : index.columnNames) { + buff.appendExceptFirst(", "); + buff.append(prepareColumnName(col)); + } + buff.append(") "); + + stat.setSQL(buff.toString().trim()); + } + + @Override + public void prepareMerge(SQLStatement stat, String schemaName, String tableName, + TableDefinition def, Object obj) { + StatementBuilder buff = new StatementBuilder("INSERT OR REPLACE INTO "); + buff.append(prepareTableName(schemaName, tableName)).append(" ("); + buff.resetCount(); + for (FieldDefinition field : def.fields) { + buff.appendExceptFirst(", "); + buff.append(field.columnName); + } + buff.append(") "); + buff.resetCount(); + buff.append("VALUES ("); + for (FieldDefinition field : def.fields) { + buff.appendExceptFirst(", "); + buff.append('?'); + Object value = def.getValue(obj, field); + Object parameter = serialize(value, field.typeAdapter); + stat.addParameter(parameter); + } + buff.append(')'); + stat.setSQL(buff.toString()); + } + +} \ No newline at end of file diff --git a/src/site/index.mkd b/src/site/index.mkd index 7e489b3..bddebd7 100644 --- a/src/site/index.mkd +++ b/src/site/index.mkd @@ -12,7 +12,7 @@ iciql **is**... iciql **is not**... - a complete alternative to JDBC -- designed to compete with more powerful database query tools like [jOOQ][jooq] or [Querydsl][querydsl] +- designed to compete with more powerful database query tools like [jOOQ][jooq] or [QueryDSL][querydsl] - designed to compete with enterprise [ORM][orm] tools like [Hibernate][hibernate] or [mybatis][mybatis] ### Example Usage @@ -42,6 +42,7 @@ select * from products - [Derby](http://db.apache.org/derby) ${derby.version} - [MySQL](http://mysql.com) ${mysql.version} - [PostgreSQL](http://postgresql.org) ${postgresql.version} +- [SQLite](http://www.sqlite.org) ${sqlite.version} Support for others is possible and may only require creating a simple "dialect" class. @@ -59,5 +60,4 @@ iciql is distributed under the terms of the [Apache Software Foundation license, [hibernate]: http://www.hibernate.org "Hibernate" [mybatis]: http://www.mybatis.org "mybatis" [github]: http://github.com/gitblit/iciql "iciql git repository" -[googlecode]: http://code.google.com/p/iciql "iciql project management" [apachelicense]: http://www.apache.org/licenses/LICENSE-2.0 "Apache License, Version 2.0" \ No newline at end of file diff --git a/src/site/jaqu_comparison.mkd b/src/site/jaqu_comparison.mkd index 3a00e6d..e7afbf8 100644 --- a/src/site/jaqu_comparison.mkd +++ b/src/site/jaqu_comparison.mkd @@ -7,7 +7,7 @@ This is an overview of the fundamental differences between the original JaQu pro IciqlJaQu core deploymentsmall, discrete librarydepends on H2 database jar file -databasesH2, HSQL, Derby, MySQL, and PostreSQLH2 only +databasesH2, HSQL, Derby, MySQL, PostreSQL, and SQLiteH2 only loggingconsole, SLF4J, or custom loggingconsole logging exceptionsalways includes generated statement in exception, when available-- column mappingswildcard queries index result sets by column nameall result sets built by field index
this can fail for wildcard queries diff --git a/src/test/java/com/iciql/test/IciqlSuite.java b/src/test/java/com/iciql/test/IciqlSuite.java index c418938..4800b9a 100644 --- a/src/test/java/com/iciql/test/IciqlSuite.java +++ b/src/test/java/com/iciql/test/IciqlSuite.java @@ -110,7 +110,11 @@ public class IciqlSuite { new TestDb("Derby", true, true, "jdbc:derby:memory:iciql;create=true"), new TestDb("Derby", true, false, "jdbc:derby:directory:testdbs/derby/iciql;create=true"), new TestDb("MySQL", false, false, "jdbc:mysql://localhost:3306/iciql", "sa", "sa"), - new TestDb("PostgreSQL", false, false, "jdbc:postgresql://localhost:5432/iciql", "sa", "sa") }; + new TestDb("PostgreSQL", false, false, "jdbc:postgresql://localhost:5432/iciql", "sa", "sa"), + new TestDb("SQLite", true, true, "jdbc:sqlite:file:iciql?mode=memory&cache=shared"), + new TestDb("SQLite", true, false, "jdbc:sqlite:" + + new File(baseFolder, "/sqlite/iciql.db").getAbsolutePath()) + }; private static final TestDb DEFAULT_TEST_DB = TEST_DBS[0]; @@ -255,6 +259,16 @@ public class IciqlSuite { return IciqlSuite.getDatabaseEngineName(db).equals("MySQL"); } + /** + * Returns true if the underlying database engine is SQLite. + * + * @param db + * @return true if underlying database engine is SQLite + */ + public static boolean isSQLite(Db db) { + return IciqlSuite.getDatabaseEngineName(db).equals("SQLite"); + } + /** * Gets the default schema of the underlying database engine. * @@ -296,6 +310,7 @@ public class IciqlSuite { } deleteRecursively(baseFolder); + new File(baseFolder, "/sqlite").mkdirs(); // Start the HSQL and H2 servers in-process org.hsqldb.Server hsql = startHSQL(); @@ -412,6 +427,20 @@ public class IciqlSuite { out.println(dividerMajor); out.println(MessageFormat.format("{0} {1} ({2}) test suite performance results", Constants.NAME, Constants.VERSION, Constants.VERSION_DATE)); + + StringBuilder compressedSystem = new StringBuilder(); + compressedSystem.append(" on "); + compressedSystem.append(System.getProperty("java.vendor")); + compressedSystem.append(' '); + compressedSystem.append(System.getProperty("java.runtime.version")); + compressedSystem.append(", "); + compressedSystem.append(System.getProperty("os.name")); + compressedSystem.append(' '); + compressedSystem.append(System.getProperty("os.version")); + compressedSystem.append(", "); + compressedSystem.append(System.getProperty("os.arch")); + out.println(compressedSystem.toString()); + out.println(dividerMajor); List dbs = Arrays.asList(TEST_DBS); Collections.sort(dbs); diff --git a/src/test/java/com/iciql/test/UpgradesTest.java b/src/test/java/com/iciql/test/UpgradesTest.java index 7de691f..4ab1ff1 100644 --- a/src/test/java/com/iciql/test/UpgradesTest.java +++ b/src/test/java/com/iciql/test/UpgradesTest.java @@ -32,7 +32,7 @@ import com.iciql.test.models.SupportedTypes.SupportedTypes2; /** * Tests the database and table upgrade functions. - * + * */ public class UpgradesTest { @@ -54,7 +54,7 @@ public class UpgradesTest { // open a second connection to the database // and then apply the v2 upgrade. - // For H2 its important to keep the first connection + // For an in-memory db its important to keep the first connection // alive so that the database is not destroyed. Db db2 = IciqlSuite.openCurrentDb(); @@ -72,7 +72,7 @@ public class UpgradesTest { db.close(); db2.close(); } - + @Test public void testDatabaseInheritedUpgrade() { Db db = IciqlSuite.openNewDb(); @@ -91,7 +91,7 @@ public class UpgradesTest { // open a second connection to the database // and then apply the v2 upgrade. - // For H2 its important to keep the first connection + // For an in-memory db its important to keep the first connection // alive so that the database is not destroyed. Db db2 = IciqlSuite.openCurrentDb(); @@ -139,6 +139,7 @@ public class UpgradesTest { final AtomicInteger oldVersion = new AtomicInteger(0); final AtomicInteger newVersion = new AtomicInteger(0); + @Override public boolean upgradeTable(Db db, String schema, String table, int fromVersion, int toVersion) { // just claims success on upgrade request oldVersion.set(fromVersion); @@ -146,6 +147,7 @@ public class UpgradesTest { return true; } + @Override public boolean upgradeDatabase(Db db, int fromVersion, int toVersion) { // just claims success on upgrade request oldVersion.set(fromVersion); @@ -168,7 +170,7 @@ public class UpgradesTest { class V2DbUpgrader extends BaseDbUpgrader { } - + /** * A sample V2 database upgrader class which inherits its * version from the parent class. -- 2.39.5