empire-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From doeb...@apache.org
Subject svn commit: r795395 - in /incubator/empire-db/trunk: empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java
Date Sat, 18 Jul 2009 16:50:29 GMT
Author: doebele
Date: Sat Jul 18 16:50:28 2009
New Revision: 795395

URL: http://svn.apache.org/viewvc?rev=795395&view=rev
Log:
EMPIREDB-41

Modified:
    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/postgresql/DBDatabaseDriverPostgreSQL.java

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=795395&r1=795394&r2=795395&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
Sat Jul 18 16:50:28 2009
@@ -82,7 +82,7 @@
 
 			// STEP 2: Choose a driver
 			System.out.println("*** Step 2: getDatabaseProvider() ***");
-			DBDatabaseDriver driver = getDatabaseDriver(config.getDatabaseProvider());
+			DBDatabaseDriver driver = getDatabaseDriver(config.getDatabaseProvider(), conn);
 
             // STEP 3: Open Database (and create if not existing)
             System.out.println("*** Step 3: openDatabase() ***");
@@ -193,7 +193,7 @@
      * Valid Providers are "oracle", "sqlserver" and "hsqldb".
      * </PRE>
      */
-    private static DBDatabaseDriver getDatabaseDriver(String provider)
+    private static DBDatabaseDriver getDatabaseDriver(String provider, Connection conn)
     {
         if (provider.equalsIgnoreCase("mysql"))
         {
@@ -226,6 +226,8 @@
             DBDatabaseDriverPostgreSQL driver = new DBDatabaseDriverPostgreSQL();
             // Set Driver specific properties (if any)
             driver.setDatabaseName(config.getSchemaName());
+            // Create the reverse function that is needed by this sample
+            driver.createReverseFunction(conn);
             return driver;
         }
         else if (provider.equalsIgnoreCase("h2"))
@@ -393,7 +395,6 @@
         // Hint: Since the reverse() function is not supported by HSQLDB there is special
treatment for HSQL
         DBColumnExpr PHONE_LAST_DASH;
         if ( db.getDriver() instanceof DBDatabaseDriverHSql 
-        		|| db.getDriver() instanceof DBDatabaseDriverPostgreSQL
         		|| db.getDriver() instanceof DBDatabaseDriverDerby
         		|| db.getDriver() instanceof DBDatabaseDriverH2)
              PHONE_LAST_DASH = EMP.PHONE_NUMBER.indexOf("-", EMP.PHONE_NUMBER.indexOf("-").plus(1)).plus(1);
// HSQLDB only

Modified: incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java?rev=795395&r1=795394&r2=795395&view=diff
==============================================================================
--- incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java
(original)
+++ incubator/empire-db/trunk/empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java
Sat Jul 18 16:50:28 2009
@@ -19,6 +19,7 @@
 package org.apache.empire.db.postgresql;
 
 import java.sql.Connection;
+import java.sql.SQLException;
 import java.util.GregorianCalendar;
 import java.util.Iterator;
 
@@ -53,6 +54,25 @@
 {
     private static final Log log = LogFactory.getLog(DBDatabaseDriverPostgreSQL.class);
     
+    private static final String CREATE_REVERSE_FUNCTION =
+        "CREATE OR REPLACE FUNCTION reverse(TEXT) RETURNS TEXT AS '\n" +
+        "DECLARE\n" +
+        "   original ALIAS FOR $1;\n" +
+        "   reversed TEXT := \'\';\n" +
+        "   onechar  VARCHAR;\n" +
+        "   mypos    INTEGER;\n" +
+        "BEGIN\n" +
+        "   SELECT LENGTH(original) INTO mypos;\n" + 
+        "   LOOP\n" +
+        "      EXIT WHEN mypos < 1;\n" +
+        "      SELECT substring(original FROM mypos FOR 1) INTO onechar;\n" +
+        "      reversed := reversed || onechar;\n" +
+        "      mypos := mypos -1;\n" +
+        "   END LOOP;\n" +
+        "   RETURN reversed;\n" +
+        "END\n" +
+        "' LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT";    
+    
     /**
      * Defines the PostgreSQL command type.
      */ 
@@ -200,7 +220,27 @@
     {
         this.databaseName = databaseName;
     }
-
+    
+    /**
+     * Creates the reverse function in postgre sql that returns the reverse of a string value.
+     * The reverse function may be helpful in SQL to analyse a text field from its end.
+     * This function must be called manually by the application depending on whether it needs
to use this function or not.<br/>
+     * The current implementation does not check, whether the reverse function already exists.
+     * If the functions exists it will be replaced and true is returned.
+     * @param conn a valid database connection
+     * @return true if the reverse function was created sucessfully or false otherwise
+     */
+    public boolean createReverseFunction(Connection conn)
+    {
+        try {
+            log.info("Creating reverse function: " + CREATE_REVERSE_FUNCTION);
+            return (executeSQL(CREATE_REVERSE_FUNCTION, null, conn, null)>=0);
+        } catch(SQLException e) {
+            log.error("Unable to create reverse function!", e);
+            return error(e);
+        }
+    }
+    
     /**
      * Creates a new PostgreSQL command object.
      * 
@@ -264,9 +304,9 @@
             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_not_available_in_pgsql(?)";//"reverse(?)";
+            case SQL_FUNC_REVERSE:            return "reverse(?)"; // In order to use this
function createReverseFunction() must be called first!
             case SQL_FUNC_STRINDEX:           return "strpos(?, {0})"; 
-            case SQL_FUNC_STRINDEXFROM:       return "strindexfrom_not_available_in_pgsql({0},
?, {1})";//"locate({0}, ?, {1})"; 
+            case SQL_FUNC_STRINDEXFROM:       return "strindexfrom_not_available_in_pgsql({0},
?, {1})"; // "locate({0}, ?, {1})"; 
             case SQL_FUNC_LENGTH:             return "length(?)";
             case SQL_FUNC_UPPER:              return "upper(?)";
             case SQL_FUNC_LOWER:              return "lcase(?)";
@@ -835,5 +875,5 @@
         appendElementName(sql, name);
         return script.addStmt(sql);
     }
-
+    
 }



Mime
View raw message