Add support for SQLite 3.8.7

7 files changed, 175 insertions, 10 deletions

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 <T> String prepareCreateTable(TableDefinition<T> def) {

+		return "CREATE TABLE IF NOT EXISTS";

+	}

+

+	@Override

+	protected <T> String prepareCreateView(TableDefinition<T> def) {

+		return "CREATE VIEW IF NOT EXISTS";

+	}

+

+	@Override

+	public <T> void prepareDropView(SQLStatement stat, TableDefinition<T> 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 <T> void prepareMerge(SQLStatement stat, String schemaName, String tableName,

+			TableDefinition<T> 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
 <tr><th></th><th>Iciql</th><th>JaQu</th></tr>

 <tr><th colspan="3">core</th></tr>

 <tr><td>deployment</td><td>small, discrete library</td><td>depends on H2 database jar file</td></tr>

-<tr><td>databases</td><td>H2, HSQL, Derby, MySQL, and PostreSQL</td><td>H2 only</td></tr>

+<tr><td>databases</td><td>H2, HSQL, Derby, MySQL, PostreSQL, and SQLite</td><td>H2 only</td></tr>

 <tr><td>logging</td><td>console, SLF4J, or custom logging</td><td>console logging</td></tr>

 <tr><td>exceptions</td><td>always includes generated statement in exception, when available</td><td>--</td></tr>

 <tr><td>column mappings</td><td>wildcard queries index result sets by column name</td><td>all result sets built by field index<br/>this can fail for wildcard queries</td></tr>

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];

 

@@ -256,6 +260,16 @@ public class IciqlSuite {
 	}

 

 	/**

+	 * 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.

 	 *

 	 * @param db

@@ -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<TestDb> 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.