empire-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From franci...@apache.org
Subject 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/
Date Wed, 08 Aug 2012 08:47:15 GMT
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);
+        }
+    }
+}



Mime
View raw message