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 do...@apache.org on 2009/07/18 18:50:29 UTC
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
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);
}
-
+
}