You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@empire-db.apache.org by fr...@apache.org on 2012/08/08 10:47:15 UTC
svn commit: r1370705 - in /empire-db/trunk/empire-db: ./
src/main/java/org/apache/empire/db/sqlite/ src/test/java/org/apache/empire/
src/test/java/org/apache/empire/db/
src/test/java/org/apache/empire/db/sqlite/
Author: francisdb
Date: Wed Aug 8 08:47:14 2012
New Revision: 1370705
URL: http://svn.apache.org/viewvc?rev=1370705&view=rev
Log:
EMPIREDB-58 Create a SQLite Driver (patch by Dimitar Simeonov)
Added:
empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/sqlite/
empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLite.java
empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/sqlite/SQLiteDDLGenerator.java
empire-db/trunk/empire-db/src/test/java/org/apache/empire/db/sqlite/
empire-db/trunk/empire-db/src/test/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLiteTest.java
Modified:
empire-db/trunk/empire-db/pom.xml
empire-db/trunk/empire-db/src/test/java/org/apache/empire/DBResource.java
empire-db/trunk/empire-db/src/test/java/org/apache/empire/db/IntegerTest.java
Modified: empire-db/trunk/empire-db/pom.xml
URL: http://svn.apache.org/viewvc/empire-db/trunk/empire-db/pom.xml?rev=1370705&r1=1370704&r2=1370705&view=diff
==============================================================================
--- empire-db/trunk/empire-db/pom.xml (original)
+++ empire-db/trunk/empire-db/pom.xml Wed Aug 8 08:47:14 2012
@@ -46,7 +46,12 @@
<version>1.2.16</version>
<scope>test</scope>
</dependency>
-
+ <dependency>
+ <groupId>org.xerial</groupId>
+ <artifactId>sqlite-jdbc</artifactId>
+ <version>3.7.2</version>
+ <scope>test</scope>
+ </dependency>
<dependency>
<groupId>hsqldb</groupId>
<artifactId>hsqldb</artifactId>
Added: empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLite.java
URL: http://svn.apache.org/viewvc/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLite.java?rev=1370705&view=auto
==============================================================================
--- empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLite.java (added)
+++ empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLite.java Wed Aug 8 08:47:14 2012
@@ -0,0 +1,562 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you 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 org.apache.empire.db.sqlite;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.text.DateFormat;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.util.Date;
+import java.util.GregorianCalendar;
+import java.util.List;
+
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBCmdType;
+import org.apache.empire.db.DBColumn;
+import org.apache.empire.db.DBColumnExpr;
+import org.apache.empire.db.DBCommand;
+import org.apache.empire.db.DBDDLGenerator;
+import org.apache.empire.db.DBDatabase;
+import org.apache.empire.db.DBDatabaseDriver;
+import org.apache.empire.db.DBDriverFeature;
+import org.apache.empire.db.DBJoinType;
+import org.apache.empire.db.DBObject;
+import org.apache.empire.db.DBSQLScript;
+import org.apache.empire.db.expr.join.DBJoinExpr;
+import org.apache.empire.exceptions.NotImplementedException;
+import org.apache.empire.exceptions.UnexpectedReturnValueException;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+/**
+ * This class provides support for the SQLite database system.<br>
+ *
+ */
+public class DBDatabaseDriverSQLite extends DBDatabaseDriver
+{
+ private final static long serialVersionUID = 1L;
+ private static final Logger log = LoggerFactory.getLogger(DBDatabaseDriverSQLite.class);
+
+ /**
+ * Defines the SQLite command type.
+ */
+ public static class DBCommandSQLite extends DBCommand
+ {
+ private static final long serialVersionUID = 1L;
+
+ /**
+ * @param db
+ * the database
+ * @see org.apache.empire.db.DBCommand
+ */
+ public DBCommandSQLite(DBDatabase db)
+ {
+ super(db);
+ }
+
+ @Override
+ public DBJoinExpr join(DBColumnExpr left, DBColumn right, DBJoinType joinType)
+ {
+ // http://www.sqlite.org/omitted.html
+ if (joinType != DBJoinType.LEFT) { throw new NotImplementedException(joinType, left + " join " + right); }
+ DBJoinExpr join = new DBJoinExpr(left, right, joinType);
+ join(join);
+ return join;
+ }
+
+ @Override
+ public void addJoins(List<DBJoinExpr> joinExprList)
+ {
+ for (DBJoinExpr joinExpr : joinExprList)
+ {
+ if (joinExpr.getType() != DBJoinType.LEFT) { throw new NotImplementedException(joinExpr.getType(), joinExpr.getLeft() + " join " + joinExpr.getLeft()); }
+ }
+ /*
+ * Iterator<DBJoinExpr> iterator = joinExprList.iterator(); for
+ * (DBJoinExpr joinExpr = null; iterator.hasNext(); joinExpr =
+ * iterator.next()) { if(joinExpr.getType() != DBJoinType.LEFT) {
+ * iterator.remove(); } }
+ */
+ super.addJoins(joinExprList);
+
+ }
+
+ }
+
+ private DBDDLGenerator<?> ddlGenerator = null; // lazy creation
+
+ /**
+ * Constructor for the SQLite database driver.
+ */
+ public DBDatabaseDriverSQLite()
+ {
+ setReservedKeywords();
+ }
+
+ private void addReservedKeyWord(final String keyWord)
+ {
+ boolean added = reservedSQLKeywords.add(keyWord.toLowerCase());
+ if (!added)
+ {
+ log.debug("Existing keyWord added: " + keyWord);
+ }
+ }
+
+ @Override
+ public int executeSQL(String sqlCmd, Object[] sqlParams, Connection conn, DBSetGenKeys genKeys) throws SQLException
+ {
+ Statement stmt = null;
+ int count = 0;
+ try
+ {
+ if (sqlParams != null)
+ { // Use a prepared statement
+ PreparedStatement pstmt = conn.prepareStatement(sqlCmd);
+ stmt = pstmt;
+ prepareStatement(pstmt, sqlParams, conn);
+ count = pstmt.executeUpdate();
+ }
+ else
+ { // Execute a simple statement
+ stmt = conn.createStatement();
+ count = stmt.executeUpdate(sqlCmd);
+ }
+ // Retrieve any auto-generated keys
+ if (genKeys != null && count > 0)
+ { // Return Keys
+ ResultSet rs = stmt.getGeneratedKeys();
+ try
+ {
+ while (rs.next())
+ {
+ genKeys.set(rs.getObject(1));
+ }
+ }
+ finally
+ {
+ rs.close();
+ }
+ }
+ }
+ finally
+ {
+ close(stmt);
+ }
+ return count;
+ }
+
+ private void setReservedKeywords()
+ {
+ // list of reserved keywords
+ // http://www.sqlite.org/lang_keywords.html
+ addReservedKeyWord("ABORT".toLowerCase());
+ addReservedKeyWord("ACTION".toLowerCase());
+ addReservedKeyWord("ADD".toLowerCase());
+ addReservedKeyWord("AFTER".toLowerCase());
+ addReservedKeyWord("ALL".toLowerCase());
+ addReservedKeyWord("ALTER".toLowerCase());
+ addReservedKeyWord("ANALYZE".toLowerCase());
+ addReservedKeyWord("AND".toLowerCase());
+ addReservedKeyWord("AS".toLowerCase());
+ addReservedKeyWord("ASC".toLowerCase());
+ addReservedKeyWord("ATTACH".toLowerCase());
+ addReservedKeyWord("AUTOINCREMENT".toLowerCase());
+ addReservedKeyWord("BEFORE".toLowerCase());
+ addReservedKeyWord("BEGIN".toLowerCase());
+ addReservedKeyWord("BETWEEN".toLowerCase());
+ addReservedKeyWord("BY".toLowerCase());
+ addReservedKeyWord("CASCADE".toLowerCase());
+ addReservedKeyWord("CASE".toLowerCase());
+ addReservedKeyWord("CAST".toLowerCase());
+ addReservedKeyWord("CHECK".toLowerCase());
+ addReservedKeyWord("COLLATE".toLowerCase());
+ addReservedKeyWord("COLUMN".toLowerCase());
+ addReservedKeyWord("COMMIT".toLowerCase());
+ addReservedKeyWord("CONFLICT".toLowerCase());
+ addReservedKeyWord("CONSTRAINT".toLowerCase());
+
+ addReservedKeyWord("CREATE".toLowerCase());
+ addReservedKeyWord("CROSS".toLowerCase());
+ addReservedKeyWord("CURRENT_DATE".toLowerCase());
+ addReservedKeyWord("CURRENT_TIME".toLowerCase());
+ addReservedKeyWord("CURRENT_DATETIME".toLowerCase());
+ addReservedKeyWord("DATABASE".toLowerCase());
+ addReservedKeyWord("DEFAULT".toLowerCase());
+ addReservedKeyWord("DEFERRABLE".toLowerCase());
+ addReservedKeyWord("DEFERRED".toLowerCase());
+ addReservedKeyWord("DELETE".toLowerCase());
+ addReservedKeyWord("DESC".toLowerCase());
+ addReservedKeyWord("DETACH".toLowerCase());
+ addReservedKeyWord("DISTINCT".toLowerCase());
+ addReservedKeyWord("DROP".toLowerCase());
+ addReservedKeyWord("EACH".toLowerCase());
+ addReservedKeyWord("ELSE".toLowerCase());
+ addReservedKeyWord("END".toLowerCase());
+ addReservedKeyWord("ESCAPE".toLowerCase());
+ addReservedKeyWord("EXCEPT".toLowerCase());
+ addReservedKeyWord("EXCLUSIVE".toLowerCase());
+ addReservedKeyWord("EXISTS".toLowerCase());
+ addReservedKeyWord("EXPLAIN".toLowerCase());
+ addReservedKeyWord("FAIL".toLowerCase());
+ addReservedKeyWord("FOR".toLowerCase());
+ addReservedKeyWord("FOREIGN".toLowerCase());
+
+ addReservedKeyWord("FROM".toLowerCase());
+ addReservedKeyWord("FULL".toLowerCase());
+ addReservedKeyWord("GLOB".toLowerCase());
+ addReservedKeyWord("GROUP".toLowerCase());
+ addReservedKeyWord("HAVING".toLowerCase());
+ addReservedKeyWord("IF".toLowerCase());
+ addReservedKeyWord("IGNORE".toLowerCase());
+ addReservedKeyWord("IMMEDIATE".toLowerCase());
+ addReservedKeyWord("IN".toLowerCase());
+ addReservedKeyWord("INDEX".toLowerCase());
+ addReservedKeyWord("INDEXED".toLowerCase());
+ addReservedKeyWord("INITIALLY".toLowerCase());
+ addReservedKeyWord("INNER".toLowerCase());
+ addReservedKeyWord("INSERT".toLowerCase());
+ addReservedKeyWord("INSTEAD".toLowerCase());
+ addReservedKeyWord("INTERSECT".toLowerCase());
+ addReservedKeyWord("INTO".toLowerCase());
+ addReservedKeyWord("IS".toLowerCase());
+ addReservedKeyWord("ISNULL".toLowerCase());
+ addReservedKeyWord("JOIN".toLowerCase());
+ addReservedKeyWord("KEY".toLowerCase());
+ addReservedKeyWord("LEFT".toLowerCase());
+ addReservedKeyWord("LIKE".toLowerCase());
+ addReservedKeyWord("LIMIT".toLowerCase());
+ addReservedKeyWord("MATCH".toLowerCase());
+
+ addReservedKeyWord("NATURAL".toLowerCase());
+ addReservedKeyWord("NO".toLowerCase());
+ addReservedKeyWord("NOT".toLowerCase());
+ addReservedKeyWord("NOTNULL".toLowerCase());
+ addReservedKeyWord("NULL".toLowerCase());
+ addReservedKeyWord("OF".toLowerCase());
+ addReservedKeyWord("OFFSET".toLowerCase());
+ addReservedKeyWord("ON".toLowerCase());
+ addReservedKeyWord("OR".toLowerCase());
+ addReservedKeyWord("ORDER".toLowerCase());
+ addReservedKeyWord("OUTER".toLowerCase());
+ addReservedKeyWord("PLAN".toLowerCase());
+ addReservedKeyWord("PRAGMA".toLowerCase());
+ addReservedKeyWord("PRIMARY".toLowerCase());
+ addReservedKeyWord("QUERY".toLowerCase());
+ addReservedKeyWord("RAISE".toLowerCase());
+ addReservedKeyWord("REFERENCES".toLowerCase());
+ addReservedKeyWord("REGEXP".toLowerCase());
+ addReservedKeyWord("REINDEX".toLowerCase());
+ addReservedKeyWord("RELEASE".toLowerCase());
+ addReservedKeyWord("RENAME".toLowerCase());
+ addReservedKeyWord("REPLACE".toLowerCase());
+ addReservedKeyWord("RESTRICT".toLowerCase());
+ addReservedKeyWord("RIGHT".toLowerCase());
+ addReservedKeyWord("ROLLBACK".toLowerCase());
+
+ addReservedKeyWord("ROW".toLowerCase());
+ addReservedKeyWord("SAVEPOINT".toLowerCase());
+ addReservedKeyWord("SELECT".toLowerCase());
+ addReservedKeyWord("SET".toLowerCase());
+ addReservedKeyWord("TABLE".toLowerCase());
+ addReservedKeyWord("TEMP".toLowerCase());
+ addReservedKeyWord("TEMPORARY".toLowerCase());
+ addReservedKeyWord("THEN".toLowerCase());
+ addReservedKeyWord("TO".toLowerCase());
+ addReservedKeyWord("TRANSACTION".toLowerCase());
+ addReservedKeyWord("TRIGGER".toLowerCase());
+ addReservedKeyWord("UNION".toLowerCase());
+ addReservedKeyWord("UNIQUE".toLowerCase());
+ addReservedKeyWord("UPDATE".toLowerCase());
+ addReservedKeyWord("USING".toLowerCase());
+ addReservedKeyWord("VACUUM".toLowerCase());
+ addReservedKeyWord("VALUES".toLowerCase());
+ addReservedKeyWord("VIEW".toLowerCase());
+ addReservedKeyWord("VIRTUAL".toLowerCase());
+ addReservedKeyWord("WHEN".toLowerCase());
+ addReservedKeyWord("WHERE".toLowerCase());
+ }
+
+ /**
+ * Creates a new SQLite command object.
+ *
+ * @return the new DBCommandSQLite object
+ */
+ @Override
+ public DBCommand createCommand(DBDatabase db)
+ {
+ if (db == null)
+ return null;
+ // create command object
+ return new DBCommandSQLite(db);
+ }
+
+ /**
+ * Returns whether or not a particular feature is supported by this driver
+ *
+ * @param type
+ * type of requested feature. @see DBDriverFeature
+ * @return true if the features is supported or false otherwise
+ */
+ @Override
+ public boolean isSupported(DBDriverFeature type)
+ {
+ switch (type)
+ { // return support info
+ case QUERY_LIMIT_ROWS:
+ return true;
+ case QUERY_SKIP_ROWS:
+ return true;
+ default:
+ // All other features are not supported by default
+ return false;
+ }
+ }
+
+ /**
+ * Gets an sql phrase template for this database system.<br>
+ *
+ * @see DBDatabaseDriver#getSQLPhrase(int)
+ * @return the phrase template
+ */
+ @Override
+ public String getSQLPhrase(int phrase)
+ {
+ switch (phrase)
+ {
+
+ // sql-phrases
+ case SQL_NULL_VALUE:
+ return "null";
+ case SQL_PARAMETER:
+ return " ? ";
+ case SQL_RENAME_TABLE:
+ return " ";
+ case SQL_RENAME_COLUMN:
+ return " AS ";
+ case SQL_DATABASE_LINK:
+ return "@";
+ case SQL_QUOTES_OPEN:
+ return "`";
+ case SQL_QUOTES_CLOSE:
+ return "`";
+ case SQL_CONCAT_EXPR:
+ return "concat(?, {0})";
+ // data types
+ case SQL_BOOLEAN_TRUE:
+ return "1";
+ case SQL_BOOLEAN_FALSE:
+ return "0";
+ case SQL_CURRENT_DATE:
+ return "SELECT date('now','localtime');";
+ case SQL_DATE_PATTERN:
+ return "yyyy-MM-dd";
+ case SQL_DATE_TEMPLATE:
+ return "'{0}'";
+ case SQL_CURRENT_DATETIME:
+ return "NOW()";
+ case SQL_DATETIME_PATTERN:
+ return "yyyy-MM-dd hh:mm:ss.sss";
+ case SQL_DATETIME_TEMPLATE:
+ return "'{0}'";
+ // functions
+ case SQL_FUNC_COALESCE:
+ return "coalesce(?, {0})";
+ case SQL_FUNC_SUBSTRING:
+ return "substring(?, {0})";
+ case SQL_FUNC_SUBSTRINGEX:
+ return "substring(?, {0}, {1})";
+ case SQL_FUNC_REPLACE:
+ return "replace(?, {0}, {1})";
+ case SQL_FUNC_REVERSE:
+ return "reverse(?)";
+ case SQL_FUNC_STRINDEX:
+ return "instr(?, {0})";
+ case SQL_FUNC_STRINDEXFROM:
+ return "locate({0}, ?, {1})";
+ case SQL_FUNC_LENGTH:
+ return "length(?)";
+ case SQL_FUNC_UPPER:
+ return "upper(?)";
+ case SQL_FUNC_LOWER:
+ return "lcase(?)";
+ case SQL_FUNC_TRIM:
+ return "trim(?)";
+ case SQL_FUNC_LTRIM:
+ return "ltrim(?)";
+ case SQL_FUNC_RTRIM:
+ return "rtrim(?)";
+ case SQL_FUNC_ESCAPE:
+ return "? escape '{0}'";
+ // Numeric
+ case SQL_FUNC_ABS:
+ return "abs(?)";
+ case SQL_FUNC_ROUND:
+ return "round(?,{0})";
+ case SQL_FUNC_TRUNC:
+ return "truncate(?,{0})";
+ case SQL_FUNC_CEILING:
+ return "ceiling(?)";
+ case SQL_FUNC_FLOOR:
+ return "floor(?)";
+ // Date
+ case SQL_FUNC_DAY:
+ return "day(?)";
+ case SQL_FUNC_MONTH:
+ return "month(?)";
+ case SQL_FUNC_YEAR:
+ return "year(?)";
+ // Aggregation
+ case SQL_FUNC_SUM:
+ return "sum(?)";
+ case SQL_FUNC_MAX:
+ return "max(?)";
+ case SQL_FUNC_MIN:
+ return "min(?)";
+ case SQL_FUNC_AVG:
+ return "avg(?)";
+ // Others
+ case SQL_FUNC_DECODE:
+ return "case ? {0} end";
+ case SQL_FUNC_DECODE_SEP:
+ return " ";
+ case SQL_FUNC_DECODE_PART:
+ return "when {0} then {1}";
+ case SQL_FUNC_DECODE_ELSE:
+ return "else {0}";
+ // Not defined
+ default:
+ log.error("SQL phrase " + String.valueOf(phrase) + " is not defined!");
+ return "?";
+ }
+ }
+
+ @Override
+ public Object getResultValue(ResultSet rset, int columnIndex, DataType dataType) throws SQLException
+ {
+ if (dataType == DataType.DATETIME)
+ {
+ // SQLite does not have a Date type, or any kind of type :(
+ String datePattern = getSQLPhrase(SQL_DATETIME_PATTERN);
+ DateFormat dateFormat = new SimpleDateFormat(datePattern);
+ try
+ {
+ Date timestamp = dateFormat.parse(rset.getString(columnIndex));
+ return new java.sql.Timestamp(timestamp.getTime());
+ }
+ catch (ParseException e)
+ {
+ throw new UnexpectedReturnValueException(rset.getString(columnIndex), "getResultValue");
+ }
+ }
+ else if (dataType == DataType.CLOB)
+ {
+ java.sql.Clob clob = rset.getClob(columnIndex);
+ return ((clob != null) ? clob.getSubString(1, (int) clob.length()) : null);
+ }
+ else if (dataType == DataType.BLOB)
+ { // Get bytes of a binary large object
+ java.sql.Blob blob = rset.getBlob(columnIndex);
+ return ((blob != null) ? blob.getBytes(1, (int) blob.length()) : null);
+ }
+ else
+ {
+ return rset.getObject(columnIndex);
+ }
+ }
+
+ /**
+ * @see DBDatabaseDriver#getConvertPhrase(DataType, DataType, Object)
+ */
+ @Override
+ public String getConvertPhrase(DataType destType, DataType srcType, Object format)
+ {
+ switch (destType)
+ {
+ // Convert to text
+ case TEXT:
+ case CHAR:
+ if (format != null)
+ { // Convert using a format string
+ if (srcType == DataType.INTEGER || srcType == DataType.AUTOINC)
+ {
+ log.error("getConvertPhrase: unknown type " + destType);
+ return "?";
+ }
+ else
+ {
+ return "to_char(?, '" + format.toString() + "')";
+ }
+ }
+ return "convert(?, CHAR)";
+ case INTEGER:
+ {
+ return "convert(?, BIGINT)";
+ }
+ case DECIMAL:
+ {
+ return "convert(?, DECIMAL)";
+ }
+ case FLOAT:
+ {
+ return "convert(?, DOUBLE)";
+ }
+ // Unknown Type
+ default:
+ log.error("getConvertPhrase: unknown type (" + String.valueOf(destType));
+ return "?";
+ }
+ }
+
+ /**
+ * Overridden. Returns a timestamp that is used for record updates created
+ * by the database server.
+ *
+ * @return the current date and time of the database server.
+ */
+ @Override
+ public java.sql.Timestamp getUpdateTimestamp(Connection conn)
+ {
+ // Default implementation
+ GregorianCalendar cal = new GregorianCalendar();
+ return new java.sql.Timestamp(cal.getTimeInMillis());
+ }
+
+ /**
+ * @see DBDatabaseDriver#getDDLScript(DBCmdType, DBObject, DBSQLScript)
+ */
+ @Override
+ public void getDDLScript(DBCmdType type, DBObject dbo, DBSQLScript script)
+ {
+ if (ddlGenerator == null)
+ ddlGenerator = new SQLiteDDLGenerator(this);
+ // forward request
+ ddlGenerator.getDDLScript(type, dbo, script);
+ }
+
+ @Override
+ public Object getNextSequenceValue(DBDatabase db, String SeqName, int minValue, Connection conn)
+ {
+ throw new NotImplementedException(db, " sequence values are assigned dynamicaly from sqlite ");
+ }
+
+}
Added: empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/sqlite/SQLiteDDLGenerator.java
URL: http://svn.apache.org/viewvc/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/sqlite/SQLiteDDLGenerator.java?rev=1370705&view=auto
==============================================================================
--- empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/sqlite/SQLiteDDLGenerator.java (added)
+++ empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/sqlite/SQLiteDDLGenerator.java Wed Aug 8 08:47:14 2012
@@ -0,0 +1,136 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you 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 org.apache.empire.db.sqlite;
+
+import java.util.Iterator;
+import java.util.Map;
+import java.util.Map.Entry;
+
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumn;
+import org.apache.empire.db.DBDDLGenerator;
+import org.apache.empire.db.DBExpr;
+import org.apache.empire.db.DBIndex;
+import org.apache.empire.db.DBRelation;
+import org.apache.empire.db.DBSQLScript;
+import org.apache.empire.db.DBTable;
+import org.apache.empire.db.DBTableColumn;
+
+public class SQLiteDDLGenerator extends DBDDLGenerator<DBDatabaseDriverSQLite>
+{
+ public SQLiteDDLGenerator(DBDatabaseDriverSQLite driver)
+ {
+ super(driver);
+ // set SQLite specific data types
+ initDataTypes();
+ }
+
+ /**
+ * sets SQLite specific data types
+ *
+ * @param driver
+ */
+ private void initDataTypes()
+ { // Override data types
+ DATATYPE_INTEGER = "INTEGER";
+ DATATYPE_BOOLEAN = "BOOLEAN";
+ DATATYPE_TIMESTAMP = "DATETIME DEFAULT CURRENT_TIMESTAMP";
+ }
+
+
+ @Override
+ protected void createTable(DBTable t, DBSQLScript script)
+ {
+ StringBuilder sql = new StringBuilder();
+ sql.append("-- creating table ");
+ sql.append(t.getName());
+ sql.append(" --\r\n");
+ sql.append("CREATE TABLE ");
+ t.addSQL(sql, DBExpr.CTX_FULLNAME);
+ sql.append(" (");
+ boolean addSeparator = false;
+ Iterator<DBColumn> columns = t.getColumns().iterator();
+ while (columns.hasNext())
+ {
+ DBTableColumn c = (DBTableColumn) columns.next();
+ if (c.getDataType() == DataType.UNKNOWN)
+ continue; // Ignore and continue;
+ // Append column
+ sql.append((addSeparator) ? ",\r\n " : "\r\n ");
+ appendColumnDesc(c, false, sql);
+ addSeparator = true;
+
+ }
+ // Foreign Key
+ Map<DBColumn, DBColumn> referencesMap = t.getColumnReferences();
+ if (referencesMap != null)
+ {
+ DBColumn source = null,target = null;
+ for (Entry<DBColumn, DBColumn> entry : referencesMap.entrySet())
+ {
+ source = entry.getKey();
+ target = entry.getValue();
+ sql.append((addSeparator) ? ",\r\n " : "\r\n ");
+ sql.append("FOREIGN KEY (");
+ source.addSQL(sql, DBExpr.CTX_NAME);
+ sql.append(") REFERENCES ");
+ sql.append(target.getRowSet().getName());
+ sql.append(" (");
+ target.addSQL(sql, DBExpr.CTX_NAME);
+ sql.append(")");
+ }
+ }
+
+ // Primary Key
+ DBIndex pk = t.getPrimaryKey();
+ if (pk != null)
+ { // add the primary key
+ sql.append(",\r\n");
+ if (namePrimaryKeyConstraint)
+ {
+ sql.append(" CONSTRAINT ");
+ appendElementName(sql, pk.getName());
+ }
+ sql.append(" PRIMARY KEY (");
+ addSeparator = false;
+ // columns
+ DBColumn[] keyColumns = pk.getColumns();
+ for (int i = 0; i < keyColumns.length; i++)
+ {
+ sql.append((addSeparator) ? ", " : "");
+ keyColumns[i].addSQL(sql, DBExpr.CTX_NAME);
+ addSeparator = true;
+ }
+ sql.append(")");
+ }
+ sql.append(")");
+ // Create the table
+ addCreateTableStmt(t, sql, script);
+ // Create all Indexes
+ createTableIndexes(t, pk, script);
+ }
+
+
+
+ @Override
+ protected void createRelation(DBRelation r, DBSQLScript script)
+ {
+ // http://www.sqlite.org/foreignkeys.html
+ }
+}
\ No newline at end of file
Modified: empire-db/trunk/empire-db/src/test/java/org/apache/empire/DBResource.java
URL: http://svn.apache.org/viewvc/empire-db/trunk/empire-db/src/test/java/org/apache/empire/DBResource.java?rev=1370705&r1=1370704&r2=1370705&view=diff
==============================================================================
--- empire-db/trunk/empire-db/src/test/java/org/apache/empire/DBResource.java (original)
+++ empire-db/trunk/empire-db/src/test/java/org/apache/empire/DBResource.java Wed Aug 8 08:47:14 2012
@@ -29,6 +29,7 @@ import org.apache.empire.db.derby.DBData
import org.apache.empire.db.h2.DBDatabaseDriverH2;
import org.apache.empire.db.hsql.DBDatabaseDriverHSql;
import org.apache.empire.db.postgresql.DBDatabaseDriverPostgreSQL;
+import org.apache.empire.db.sqlite.DBDatabaseDriverSQLite;
import org.apache.empire.db.sqlserver.DBDatabaseDriverMSSQL;
import org.junit.rules.ExternalResource;
@@ -97,6 +98,10 @@ public class DBResource extends External
}
public enum DB{
+ SQLITE(
+ "org.sqlite.JDBC",
+ "jdbc:sqlite::memory:",
+ DBDatabaseDriverSQLite.class),
HSQL(
"org.hsqldb.jdbcDriver",
"jdbc:hsqldb:mem:data/derby/test",
Modified: empire-db/trunk/empire-db/src/test/java/org/apache/empire/db/IntegerTest.java
URL: http://svn.apache.org/viewvc/empire-db/trunk/empire-db/src/test/java/org/apache/empire/db/IntegerTest.java?rev=1370705&r1=1370704&r2=1370705&view=diff
==============================================================================
--- empire-db/trunk/empire-db/src/test/java/org/apache/empire/db/IntegerTest.java (original)
+++ empire-db/trunk/empire-db/src/test/java/org/apache/empire/db/IntegerTest.java Wed Aug 8 08:47:14 2012
@@ -34,6 +34,7 @@ import org.apache.empire.db.hsql.DBDatab
import org.apache.empire.db.mysql.DBDatabaseDriverMySQL;
import org.apache.empire.db.oracle.DBDatabaseDriverOracle;
import org.apache.empire.db.postgresql.DBDatabaseDriverPostgreSQL;
+import org.apache.empire.db.sqlite.DBDatabaseDriverSQLite;
import org.apache.empire.db.sqlserver.DBDatabaseDriverMSSQL;
import org.junit.Ignore;
import org.junit.Test;
@@ -48,6 +49,16 @@ public class IntegerTest {
private static final Logger LOGGER = LoggerFactory.getLogger(IntegerTest.class);
+
+ @Test
+ public void testSQlitedb() {
+ SampleConfig config = new SampleConfig();
+ config.databaseProvider = "sqlite";
+ config.jdbcClass = "org.sqlite.JDBC";
+ config.jdbcURL = "jdbc:sqlite::memory:";
+ testLongInteger(config);
+ }
+
@Test
public void testHsqldb() {
SampleConfig config = new SampleConfig();
@@ -135,6 +146,10 @@ public class IntegerTest {
}
private DBDatabaseDriver getDatabaseDriver(SampleConfig config, Connection conn) {
+ if (config.databaseProvider.equalsIgnoreCase("sqlite")) {
+ DBDatabaseDriverSQLite driver = new DBDatabaseDriverSQLite();
+ return driver;
+ }
if (config.databaseProvider.equalsIgnoreCase("mysql")) {
DBDatabaseDriverMySQL driver = new DBDatabaseDriverMySQL();
driver.setDatabaseName(config.schemaName);
Added: empire-db/trunk/empire-db/src/test/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLiteTest.java
URL: http://svn.apache.org/viewvc/empire-db/trunk/empire-db/src/test/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLiteTest.java?rev=1370705&view=auto
==============================================================================
--- empire-db/trunk/empire-db/src/test/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLiteTest.java (added)
+++ empire-db/trunk/empire-db/src/test/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLiteTest.java Wed Aug 8 08:47:14 2012
@@ -0,0 +1,163 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you 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 org.apache.empire.db.sqlite;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNotNull;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.util.Date;
+
+import org.apache.empire.DBResource;
+import org.apache.empire.DBResource.DB;
+import org.apache.empire.data.DataMode;
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.CompanyDB;
+import org.apache.empire.db.DBCmdType;
+import org.apache.empire.db.DBDatabase;
+import org.apache.empire.db.DBDatabaseDriver;
+import org.apache.empire.db.DBRecord;
+import org.apache.empire.db.DBSQLScript;
+import org.apache.empire.db.DBTable;
+import org.apache.empire.db.DBTableColumn;
+import org.junit.Rule;
+import org.junit.Test;
+
+
+public class DBDatabaseDriverSQLiteTest{
+
+ @Rule
+ public DBResource dbResource = new DBResource(DB.SQLITE);
+
+ @Test
+ public void test()
+ {
+ Connection conn = dbResource.getConnection();
+
+ DBDatabaseDriver driver = dbResource.newDriver();
+ CompanyDB db = new CompanyDB();
+ db.open(driver, dbResource.getConnection());
+ DBSQLScript script = new DBSQLScript();
+ db.getCreateDDLScript(db.getDriver(), script);
+ script.run(db.getDriver(), dbResource.getConnection(), false);
+
+ DBRecord dep = new DBRecord();
+ dep.create(db.DEPARTMENT);
+ dep.setValue(db.DEPARTMENT.NAME, "junit");
+ dep.setValue(db.DEPARTMENT.BUSINESS_UNIT, "test");
+ dep.update(conn);
+
+ Date date = dep.getDateTime(db.DEPARTMENT.UPDATE_TIMESTAMP);
+ assertNotNull("Date is null", date);
+ assertTrue("No departments", dep.getInt(db.DEPARTMENT.ID) > 0);
+
+ DBRecord emp = new DBRecord();
+ emp.create(db.EMPLOYEE);
+ emp.setValue(db.EMPLOYEE.FIRSTNAME, "junit");
+ emp.setValue(db.EMPLOYEE.LASTNAME, "test");
+ emp.setValue(db.EMPLOYEE.GENDER, "m");
+ emp.setValue(db.EMPLOYEE.DEPARTMENT_ID, dep.getInt(db.DEPARTMENT.ID));
+ emp.update(conn);
+
+ date = emp.getDateTime(db.EMPLOYEE.UPDATE_TIMESTAMP);
+ assertNotNull("Date is null", date);
+ assertTrue("Employee id O or less", emp.getInt(db.EMPLOYEE.ID) > 0);
+
+ int id = emp.getInt(db.EMPLOYEE.ID);
+
+ // Update an Employee
+ emp = new DBRecord();
+ emp.read(db.EMPLOYEE, id, conn);
+ // Set
+ emp.setValue(db.EMPLOYEE.PHONE_NUMBER, "123456");
+ emp.update(conn);
+
+ emp = new DBRecord();
+ emp.read(db.EMPLOYEE, id, conn);
+
+ assertEquals("123456", emp.getString(db.EMPLOYEE.PHONE_NUMBER));
+
+ script = new DBSQLScript();
+ db.getDriver().getDDLScript(DBCmdType.DROP, db.EMPLOYEE, script);
+ db.getDriver().getDDLScript(DBCmdType.DROP, db.DEPARTMENT, script);
+ script.run(db.getDriver(), conn, true);
+ }
+
+
+
+ /**
+ * See https://issues.apache.org/jira/browse/EMPIREDB-151
+ */
+ @Test
+ public void testSequence(){
+ Connection conn = dbResource.getConnection();
+
+ DBDatabaseDriver driver = dbResource.newDriver();
+ SeqDB db = new SeqDB();
+ db.open(driver, dbResource.getConnection());
+ DBSQLScript script = new DBSQLScript();
+ db.getCreateDDLScript(db.getDriver(), script);
+ script.run(db.getDriver(), dbResource.getConnection(), false);
+
+ DBRecord data = new DBRecord();
+ data.create(db.DATA);
+ data.setValue(db.DATA.VALUE, "test");
+ data.update(conn);
+
+ final Object id = data.getLong(db.DATA.ID);
+
+ DBRecord read = new DBRecord();
+ read.read(db.DATA, id, conn);
+
+ assertEquals("test", read.getString(db.DATA.VALUE));
+
+ script = new DBSQLScript();
+ db.getDriver().getDDLScript(DBCmdType.DROP, db.DATA, script);
+ script.run(db.getDriver(), conn, true);
+ }
+
+ /**
+ * This is the basic database for testing
+ *
+ */
+ private class SeqDB extends DBDatabase
+ {
+ private final static long serialVersionUID = 1L;
+ public final Data DATA = new Data(this);
+ }
+
+ /**
+ * For testing SEQUENCE auto generation stuff
+ */
+ public static class Data extends DBTable
+ {
+ private final static long serialVersionUID = 1L;
+ public final DBTableColumn ID;
+ public final DBTableColumn VALUE;
+
+ public Data(DBDatabase db)
+ {
+ super("DATA", db);
+ ID = addColumn("DATA_ID", DataType.AUTOINC, 0, DataMode.AutoGenerated);
+ VALUE = addColumn("VALUE", DataType.TEXT, 256, DataMode.NotNull);
+ setPrimaryKey(ID);
+ }
+ }
+}