empire-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From doeb...@apache.org
Subject svn commit: r1032278 - in /incubator/empire-db/trunk: empire-db-examples/empire-db-example-basic/ empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/ empire-db/src/main/java/org/apache/empire/db/ empire-db/src/main/ja...
Date Sun, 07 Nov 2010 14:03:11 GMT
Author: doebele
Date: Sun Nov  7 14:03:11 2010
New Revision: 1032278

URL: http://svn.apache.org/viewvc?rev=1032278&view=rev
Log:
EMPIREDB-62
Added limitRows() for Oracle

Added:
    incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/OracleRowNumExpr.java
  (with props)
Modified:
    incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/config.xml
    incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/pom.xml
    incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
    incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
    incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBQuery.java
    incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java
    incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java

Modified: incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/config.xml
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/config.xml?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/config.xml (original)
+++ incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/config.xml Sun Nov
 7 14:03:11 2010
@@ -21,7 +21,7 @@
 
 	<properties>
 		<!-- provider name must match the property-section containing the connection data -->
-		<databaseProvider>sqlserver</databaseProvider>
+		<databaseProvider>hsqldb</databaseProvider>
 	</properties>
 
 	<properties-hsqldb>

Modified: incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/pom.xml
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/pom.xml?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/pom.xml (original)
+++ incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/pom.xml Sun Nov 
7 14:03:11 2010
@@ -60,11 +60,11 @@
 		    <version>5.1.6</version>
 		</dependency>
 
-		<!-- ojdbc  
+		<!-- ojdbc   
 		<dependency>
 		    <groupId>com.oracle</groupId>
-		    <artifactId>ojdbc14</artifactId>
-		    <version>10.2.0.3.0</version>
+		    <artifactId>ojdbc</artifactId>
+		    <version>14</version>
 		</dependency>
 		-->
 		

Modified: incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
(original)
+++ incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
Sun Nov  7 14:03:11 2010
@@ -36,6 +36,7 @@ import org.apache.empire.db.h2.DBDatabas
 import org.apache.empire.db.hsql.DBDatabaseDriverHSql;
 import org.apache.empire.db.mysql.DBDatabaseDriverMySQL;
 import org.apache.empire.db.oracle.DBDatabaseDriverOracle;
+import org.apache.empire.db.oracle.OracleRowNumExpr;
 import org.apache.empire.db.postgresql.DBDatabaseDriverPostgreSQL;
 import org.apache.empire.db.sqlserver.DBDatabaseDriverMSSQL;
 import org.apache.empire.xml.XMLWriter;
@@ -417,9 +418,15 @@ public class SampleApp 
         cmd.where(EMP.LASTNAME.length().isGreaterThan(0));
         cmd.orderBy(EMP.LASTNAME, EMP.FIRSTNAME);
 
-        cmd.limitRows(20);
-        if (db.getDriver().isSupported(DBDriverFeature.QUERY_SKIP_ROWS))
-            cmd.skipRows(1);
+        /*
+        // Example for limitRows() and skipRows()
+        if (db.getDriver().isSupported(DBDriverFeature.QUERY_LIMIT_ROWS))
+        {	// set maximum number of rows
+        	cmd.limitRows(20);
+            if (db.getDriver().isSupported(DBDriverFeature.QUERY_SKIP_ROWS))
+                cmd.skipRows(1);
+        }
+        */
         
 		// Query Records and print output
 		DBReader reader = new DBReader();

Modified: incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
(original)
+++ incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
Sun Nov  7 14:03:11 2010
@@ -470,7 +470,7 @@ public abstract class DBCommand extends 
     {
         if (where == null)
             where = new ArrayList<DBCompareExpr>();
-        setCompare(where, expr);
+        setConstraint(where, expr);
     }
 
     /**
@@ -496,6 +496,17 @@ public abstract class DBCommand extends 
         }
         return null;
     }
+    
+    /**
+     * removes a constraint on a particular column from the where clause
+     * @param col the column expression for which to remove the constraint
+     */
+    public void removeWhereConstraintOn(DBColumnExpr col)
+    {
+        if (where == null)
+        	return;
+        removeConstraintOn(where, col);
+    }
 
     /**
      * Returns a copy of the defined joins.
@@ -513,7 +524,6 @@ public abstract class DBCommand extends 
 
     /**
      * Adds a list of constraints to the command.
-     * 
      * @param constraints list of constraints
      */
     public void addWhereConstraints(List<DBCompareExpr> constraints)
@@ -526,16 +536,25 @@ public abstract class DBCommand extends 
     }
 
     /**
-     * Sets a having contraint.
-     * 
+     * adds a constraint to the having clause.
      * @param expr the DBCompareExpr object
      */
-    // having
     public void having(DBCompareExpr expr)
     {
         if (having == null)
             having = new ArrayList<DBCompareExpr>();
-        setCompare(having, expr);
+        setConstraint(having, expr);
+    }
+    
+    /**
+     * removes a constraint on a particular column from the where clause
+     * @param col the column expression for which to remove the constraint
+     */
+    public void removeHavingConstraintOn(DBColumnExpr col)
+    {
+        if (having == null)
+        	return;
+        removeConstraintOn(having, col);
     }
 
     /**
@@ -701,13 +720,11 @@ public abstract class DBCommand extends 
     }
 
     /**
-     * Compares the DBCompareExpr object with the Elements
-     * of the Vector 'where' or 'having'.
-     * 
-     * @param list the Vector 'where' or 'having'
+     * adds a constraint to the 'where' or 'having' collections 
+     * @param list the 'where' or 'having' list
      * @param expr the DBCompareExpr object
      */
-    protected void setCompare(List<DBCompareExpr> list, DBCompareExpr expr)
+    protected void setConstraint(List<DBCompareExpr> list, DBCompareExpr expr)
     { // adds a comparison to the where or having list
         for (int i = 0; i < list.size(); i++)
         { // check expression
@@ -718,10 +735,33 @@ public abstract class DBCommand extends 
             list.set(i, expr);
             return;
         }
-        // neue expression, or possible another expression
-        // for the same column when allowMultiple == true
+        // add expression
         list.add(expr);
     }
+    
+    /**
+     * removes a constraint on a particular column to the 'where' or 'having' collections

+     * @param list the 'where' or 'having' list
+     * @param col the column expression for which to remove the constraint
+     */
+    protected void removeConstraintOn(List<DBCompareExpr> list, DBColumnExpr col)
+    {
+        if (list == null)
+        	return;
+        for(DBCompareExpr cmp : list)
+        {	// Check whether it is a compare column expr.
+            if (!(cmp instanceof DBCompareColExpr))
+            	continue;
+            // Compare columns
+            DBColumnExpr c = ((DBCompareColExpr)cmp).getColumnExpr();
+            DBColumn udc = c.getUpdateColumn();
+            if (c.equals(col) || (udc!=null && udc.equals(col.getUpdateColumn())))
+            {	// found the column
+            	list.remove(cmp);
+            	return;
+            }
+        }
+    }
 
     /**
      * Gets a list of all tables referenced by the query.

Modified: incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBQuery.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBQuery.java?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBQuery.java (original)
+++ incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/DBQuery.java Sun
Nov  7 14:03:11 2010
@@ -454,7 +454,7 @@ public class DBQuery extends DBRowSet
                 { // Check whether
                     DBCompareColExpr cmpExpr = (DBCompareColExpr) cmp;
                     DBColumn col = cmpExpr.getColumnExpr().getUpdateColumn();
-                    if (col.getRowSet() == table)
+                    if (col!=null && col.getRowSet() == table)
                         upd.where(cmp);
                 } 
                 else

Modified: incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java
(original)
+++ incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBCommandOracle.java
Sun Nov  7 14:03:11 2010
@@ -37,6 +37,7 @@ public class DBCommandOracle extends DBC
     protected DBCompareExpr startWith  = null;
     // optimizerHint
     protected String        optimizerHint  = null;
+    protected OracleRowNumExpr	rowNumExpr = null;
 
     /**
      * Constructs an oracle command object.
@@ -89,6 +90,25 @@ public class DBCommandOracle extends DBC
     {
         this.startWith = expr;
     }
+    
+    @Override
+    public boolean limitRows(int numRows)
+    {
+    	if (rowNumExpr==null)
+    		rowNumExpr = new OracleRowNumExpr(getDatabase());
+    	// Add the constraint
+    	where(rowNumExpr.isLessOrEqual(numRows));
+        return success();
+    }
+     
+    @Override
+    public void clearLimit()
+    {
+    	if (rowNumExpr!=null)
+    		removeWhereConstraintOn(rowNumExpr);
+    	// constraint removed
+    	rowNumExpr = null;
+    }
 
     /**
      * Creates the SQL statement the special characteristics of

Modified: incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java?rev=1032278&r1=1032277&r2=1032278&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java
(original)
+++ incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java
Sun Nov  7 14:03:11 2010
@@ -47,9 +47,6 @@ import org.apache.empire.db.DBView;
 /**
  * This class provides support for the Oracle database system.<br>
  * Oracle Version 9 or higher is required.
- * 
- *
- * 
  */
 public class DBDatabaseDriverOracle extends DBDatabaseDriver
 {
@@ -105,8 +102,10 @@ public class DBDatabaseDriverOracle exte
     {
         switch (type)
         {   // return support info 
-            case CREATE_SCHEMA: return false;
-            case SEQUENCES:     return true;
+            case CREATE_SCHEMA: 	return false;
+            case SEQUENCES:     	return true;
+            case QUERY_LIMIT_ROWS:  return true;
+            case QUERY_SKIP_ROWS:   return false;
             default:
                 // All other features are not supported by default
                 return false;

Added: incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/OracleRowNumExpr.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/OracleRowNumExpr.java?rev=1032278&view=auto
==============================================================================
--- incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/OracleRowNumExpr.java
(added)
+++ incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/OracleRowNumExpr.java
Sun Nov  7 14:03:11 2010
@@ -0,0 +1,146 @@
+/*
+ * 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.oracle;
+
+// Java
+import java.util.Set;
+
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumn;
+import org.apache.empire.db.DBColumnExpr;
+import org.apache.empire.db.DBDatabase;
+import org.apache.empire.xml.XMLUtil;
+import org.w3c.dom.Element;
+
+
+/**
+ * implements a column expression for the Oracle rownum function
+ */
+public class OracleRowNumExpr extends DBColumnExpr
+{
+    public final DBDatabase   db;
+
+    /**
+     * Constructs a new OracleRowNumExpr object.
+     * 
+     * @param db the database
+     */
+    public OracleRowNumExpr(DBDatabase db)
+    {
+        this.db = db;
+    }
+
+    /**
+     * Returns the current DBDatabase object.
+     * @return the current DBDatabase object
+     */
+    @Override
+    public DBDatabase getDatabase()
+    {
+        return db;
+    }
+
+    /**
+     * Returns the data type of the DBColumnExpr object.
+     * 
+     * @return the data type
+     */
+    @Override
+    public DataType getDataType()
+    {
+        return DataType.INTEGER;
+    }
+
+    /**
+     * Returns the column name.
+     * 
+     * @return the column name
+     */
+    @Override
+    public String getName()
+    {
+        return "rownum";
+    }
+
+    /** this helper function calls the DBColumnExpr.addXML(Element, long) method */
+    @Override
+    public Element addXml(Element parent, long flags)
+    {
+        Element elem = XMLUtil.addElement(parent, "column");
+        elem.setAttribute("name", getName());
+        elem.setAttribute("function", "rownum");
+        return elem;
+    }
+
+    /**
+     * Returns null.
+     * 
+     * @return null
+     */
+    @Override
+    public DBColumn getUpdateColumn()
+    {
+        return null;
+    }
+
+    /**
+     * Always returns false
+     * @return false
+     */
+    @Override
+    public boolean isAggregate()
+    {
+        return false;
+    }
+
+    /**
+     * Creates the SQL-Command.
+     * 
+     * @param buf the SQL-Command
+     * @param context the current SQL-Command context
+     */
+    @Override
+    public void addSQL(StringBuilder buf, long context)
+    {
+    	if (!(db.getDriver() instanceof DBDatabaseDriverOracle))
+    	{
+    		log.warn("Oracle RowNumExpression can be used with Oracle databases only!");
+    	}
+        buf.append("rownum");
+    }
+
+    /**
+     * @see org.apache.empire.db.DBExpr#addReferencedColumns(Set)
+     */
+    @Override
+    public void addReferencedColumns(Set<DBColumn> list)
+    {   // nothing to do!
+        return;
+    }
+
+    /**
+     * @see java.lang.Object#equals(Object) 
+     */
+    @Override
+    public boolean equals(Object other)
+    {
+    	return (other instanceof OracleRowNumExpr);
+    }
+
+}

Propchange: incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/oracle/OracleRowNumExpr.java
------------------------------------------------------------------------------
    svn:mime-type = text/plain



Mime
View raw message