Return-Path: 
 <empire-db-commits-return-458-apmail-incubator-empire-db-commits-archive=incubator.apache.org@incubator.apache.org>
Delivered-To: apmail-incubator-empire-db-commits-archive@minotaur.apache.org
Received: (qmail 24541 invoked from network); 18 Jul 2009 16:49:53 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3)
  by minotaur.apache.org with SMTP; 18 Jul 2009 16:49:53 -0000
Received: (qmail 11412 invoked by uid 500); 18 Jul 2009 16:50:58 -0000
Delivered-To: apmail-incubator-empire-db-commits-archive@incubator.apache.org
Received: (qmail 11391 invoked by uid 500); 18 Jul 2009 16:50:58 -0000
Mailing-List: contact empire-db-commits-help@incubator.apache.org;
 run by ezmlm
Precedence: bulk
List-Help: <mailto:empire-db-commits-help@incubator.apache.org>
List-Unsubscribe: <mailto:empire-db-commits-unsubscribe@incubator.apache.org>
List-Post: <mailto:empire-db-commits@incubator.apache.org>
List-Id: <empire-db-commits.incubator.apache.org>
Reply-To: empire-db-dev@incubator.apache.org
Delivered-To: mailing list empire-db-commits@incubator.apache.org
Received: (qmail 11381 invoked by uid 99); 18 Jul 2009 16:50:58 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 18 Jul 2009 16:50:58 +0000
X-ASF-Spam-Status: No, hits=-2000.0 required=10.0
	tests=ALL_TRUSTED
X-Spam-Check-By: apache.org
Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 18 Jul 2009 16:50:49 +0000
Received: by eris.apache.org (Postfix, from userid 65534)
	id 4072823888D0; Sat, 18 Jul 2009 16:50:29 +0000 (UTC)
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
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 -0000
To: empire-db-commits@incubator.apache.org
From: doebele@apache.org
X-Mailer: svnmailer-1.0.8
Message-Id: <20090718165029.4072823888D0@eris.apache.org>
X-Virus-Checked: Checked by ClamAV on apache.org

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);
     }
-
+    
 }