This is an automated email from the ASF dual-hosted git repository.
doebele pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/empire-db.git
The following commit(s) were added to refs/heads/master by this push:
new 75383e4 EMPIREDB-381 Sequence Name initialization
75383e4 is described below
commit 75383e400c51f316880f445ceeaefa7c9ae05300
Author: Rainer Döbele <[email protected]>
AuthorDate: Mon Mar 7 11:11:00 2022 +0100
EMPIREDB-381 Sequence Name initialization
---
.../org/apache/empire/samples/db/SampleApp.java | 2 +-
.../org/apache/empire/dbms/DBMSHandlerBase.java | 5 +-
.../dbms/postgresql/DBMSHandlerPostgreSQL.java | 341 ++++++++++++---------
.../dbms/postgresql/PostgresDDLGenerator.java | 69 ++++-
.../dbms/postgresql/DBMSHandlerPostgreSQLTest.java | 2 +-
5 files changed, 259 insertions(+), 160 deletions(-)
diff --git
a/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
b/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
index 583cecc..8354216 100644
---
a/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
+++
b/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java
@@ -216,7 +216,7 @@ public class SampleApp
// Special cases
if (dbms instanceof DBMSHandlerPostgreSQL)
{ // Create the reverse function that is needed by this sample
- ((DBMSHandlerPostgreSQL)dbms).createReverseFunction(conn);
+
((DBMSHandlerPostgreSQL)dbms).getDDLGenerator().setCreateReverseFunction(true);
}
// done
diff --git
a/empire-db/src/main/java/org/apache/empire/dbms/DBMSHandlerBase.java
b/empire-db/src/main/java/org/apache/empire/dbms/DBMSHandlerBase.java
index b98c3b6..8edaa2d 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/DBMSHandlerBase.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/DBMSHandlerBase.java
@@ -81,6 +81,9 @@ public abstract class DBMSHandlerBase implements DBMSHandler
"select", "udpate",
"insert", "alter", "delete",
"order" };
protected final Set<String> reservedSQLKeywords;
+
+ // Postfix for auto-generated Sequence names
+ protected String SEQUENCE_NAME_POSTFIX = "_SEQ";
/**
* DBMSCommand
@@ -416,7 +419,7 @@ public abstract class DBMSHandlerBase implements DBMSHandler
StringBuilder b = new StringBuilder(column.getRowSet().getName());
b.append("_");
b.append(column.getName());
- b.append("_SEQ");
+ b.append(SEQUENCE_NAME_POSTFIX);
seqName = b.toString();
// Store as default for later use
column.setDefaultValue(seqName);
diff --git
a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBMSHandlerPostgreSQL.java
b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBMSHandlerPostgreSQL.java
index c1516ee..9fbe395 100644
---
a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBMSHandlerPostgreSQL.java
+++
b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBMSHandlerPostgreSQL.java
@@ -22,18 +22,21 @@ import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.GregorianCalendar;
+import java.util.HashMap;
+import java.util.Map;
import org.apache.empire.commons.ObjectUtils;
import org.apache.empire.commons.StringUtils;
import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumn;
import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBDDLGenerator;
import org.apache.empire.db.DBDDLGenerator.DDLActionType;
import org.apache.empire.db.DBDatabase;
import org.apache.empire.db.DBObject;
import org.apache.empire.db.DBSQLScript;
+import org.apache.empire.db.DBTable;
import org.apache.empire.db.DBTableColumn;
-import org.apache.empire.db.exceptions.EmpireSQLException;
import org.apache.empire.db.exceptions.QueryNoResultException;
import org.apache.empire.db.expr.column.DBValueExpr;
import org.apache.empire.dbms.DBMSFeature;
@@ -56,27 +59,10 @@ public class DBMSHandlerPostgreSQL extends DBMSHandlerBase
private static final Logger log =
LoggerFactory.getLogger(DBMSHandlerPostgreSQL.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";
-
private String databaseName;
+
+ private boolean usePostgresSerialType = true;
private DBDDLGenerator<?> ddlGenerator = null; // lazy creation
@@ -86,113 +72,8 @@ public class DBMSHandlerPostgreSQL extends DBMSHandlerBase
public DBMSHandlerPostgreSQL()
{
setReservedKeywords();
- }
-
- private void addReservedKeyWord(final String keyWord){
- boolean added = reservedSQLKeywords.add(keyWord.toLowerCase());
- if(!added){
- log.debug("Existing keyWord added: " + keyWord);
- }
- }
-
- private void setReservedKeywords(){
- // list of reserved keywords
- //
http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
- addReservedKeyWord("ALL".toLowerCase());
- addReservedKeyWord("ANALYSE".toLowerCase());
- addReservedKeyWord("ANALYZE".toLowerCase());
- addReservedKeyWord("AND".toLowerCase());
- addReservedKeyWord("ANY".toLowerCase());
- addReservedKeyWord("ARRAY".toLowerCase());
- addReservedKeyWord("AS".toLowerCase());
- addReservedKeyWord("ASC".toLowerCase());
- addReservedKeyWord("ASYMMETRIC".toLowerCase());
- addReservedKeyWord("AUTHORIZATION".toLowerCase());
- addReservedKeyWord("BETWEEN".toLowerCase());
- addReservedKeyWord("BINARY".toLowerCase());
- addReservedKeyWord("BOTH".toLowerCase());
- addReservedKeyWord("CASE".toLowerCase());
- addReservedKeyWord("CAST".toLowerCase());
- addReservedKeyWord("CHECK".toLowerCase());
- addReservedKeyWord("COLLATE".toLowerCase());
- //addReservedKeyWord("COLUMN".toLowerCase());
- //addReservedKeyWord("CONSTRAINT".toLowerCase());
- addReservedKeyWord("CREATE".toLowerCase());
- addReservedKeyWord("CROSS".toLowerCase());
- addReservedKeyWord("CURRENT_DATE".toLowerCase());
- addReservedKeyWord("CURRENT_ROLE".toLowerCase());
- addReservedKeyWord("CURRENT_TIME".toLowerCase());
- addReservedKeyWord("CURRENT_TIMESTAMP".toLowerCase());
- addReservedKeyWord("CURRENT_USER".toLowerCase());
- addReservedKeyWord("DEFAULT".toLowerCase());
- addReservedKeyWord("DEFERRABLE".toLowerCase());
- addReservedKeyWord("DESC".toLowerCase());
- addReservedKeyWord("DISTINCT".toLowerCase());
- addReservedKeyWord("DO".toLowerCase());
- addReservedKeyWord("ELSE".toLowerCase());
- addReservedKeyWord("END".toLowerCase());
- addReservedKeyWord("EXCEPT".toLowerCase());
- addReservedKeyWord("FALSE".toLowerCase());
- addReservedKeyWord("FOR".toLowerCase());
- addReservedKeyWord("FOREIGN".toLowerCase());
- addReservedKeyWord("FREEZE".toLowerCase());
- addReservedKeyWord("FROM".toLowerCase());
- addReservedKeyWord("FULL".toLowerCase());
- addReservedKeyWord("GRANT".toLowerCase());
- //addReservedKeyWord("GROUP".toLowerCase());
- addReservedKeyWord("HAVING".toLowerCase());
- addReservedKeyWord("ILIKE".toLowerCase());
- addReservedKeyWord("IN".toLowerCase());
- addReservedKeyWord("INITIALLY".toLowerCase());
- addReservedKeyWord("INNER".toLowerCase());
- addReservedKeyWord("INTERSECT".toLowerCase());
- addReservedKeyWord("INTO".toLowerCase());
- addReservedKeyWord("IS".toLowerCase());
- addReservedKeyWord("ISNULL".toLowerCase());
- addReservedKeyWord("JOIN".toLowerCase());
- addReservedKeyWord("LEADING".toLowerCase());
- addReservedKeyWord("LEFT".toLowerCase());
- addReservedKeyWord("LIKE".toLowerCase());
- addReservedKeyWord("LIMIT".toLowerCase());
- addReservedKeyWord("LOCALTIME".toLowerCase());
- addReservedKeyWord("LOCALTIMESTAMP".toLowerCase());
- addReservedKeyWord("NATURAL".toLowerCase());
- addReservedKeyWord("NEW".toLowerCase());
- addReservedKeyWord("NOT".toLowerCase());
- addReservedKeyWord("NOTNULL".toLowerCase());
- addReservedKeyWord("NULL".toLowerCase());
- addReservedKeyWord("OFF".toLowerCase());
- addReservedKeyWord("OFFSET".toLowerCase());
- addReservedKeyWord("OLD".toLowerCase());
- addReservedKeyWord("ON".toLowerCase());
- addReservedKeyWord("ONLY".toLowerCase());
- addReservedKeyWord("OR".toLowerCase());
- addReservedKeyWord("ORDER".toLowerCase());
- addReservedKeyWord("OUTER".toLowerCase());
- addReservedKeyWord("OVERLAPS".toLowerCase());
- addReservedKeyWord("PLACING".toLowerCase());
- addReservedKeyWord("PRIMARY".toLowerCase());
- addReservedKeyWord("REFERENCES".toLowerCase());
- addReservedKeyWord("RETURNING".toLowerCase());
- addReservedKeyWord("RIGHT".toLowerCase());
- //addReservedKeyWord("SELECT".toLowerCase());
- addReservedKeyWord("SESSION_USER".toLowerCase());
- addReservedKeyWord("SIMILAR".toLowerCase());
- addReservedKeyWord("SOME".toLowerCase());
- addReservedKeyWord("SYMMETRIC".toLowerCase());
- //addReservedKeyWord("TABLE".toLowerCase());
- addReservedKeyWord("THEN".toLowerCase());
- addReservedKeyWord("TO".toLowerCase());
- addReservedKeyWord("TRAILING".toLowerCase());
- addReservedKeyWord("TRUE".toLowerCase());
- addReservedKeyWord("UNION".toLowerCase());
- addReservedKeyWord("UNIQUE".toLowerCase());
- //addReservedKeyWord("USER".toLowerCase());
- addReservedKeyWord("USING".toLowerCase());
- addReservedKeyWord("VERBOSE".toLowerCase());
- addReservedKeyWord("WHEN".toLowerCase());
- addReservedKeyWord("WHERE".toLowerCase());
- addReservedKeyWord("WITH".toLowerCase());
+ // Set default Sequencen name Postfix
+ SEQUENCE_NAME_POSTFIX = "_SEQUENCE";
}
/**
@@ -205,6 +86,24 @@ public class DBMSHandlerPostgreSQL extends DBMSHandlerBase
}
/**
+ * Returns whether or not the Postgres Serial Type is used for Identity
columns
+ * @return true if Postgres Serial Type is used or false if INTEGER is used
+ */
+ public boolean isUsePostgresSerialType()
+ {
+ return usePostgresSerialType;
+ }
+
+ /**
+ * Sets whether or not the Postgres Serial Type is used for Identity
columns
+ * @param usePostgresSerialType true if Postgres Serial Type should be
used or false if INTEGER should be used
+ */
+ public void setUsePostgresSerialType(boolean usePostgresSerialType)
+ {
+ this.usePostgresSerialType = usePostgresSerialType;
+ }
+
+ /**
* Sets the name for the database / schema<br>
* This names is required for creating a database.<br>
* When a name is set, the dbms will automatically execute 'USE dbname'
when the database is opened.
@@ -216,22 +115,15 @@ public class DBMSHandlerPostgreSQL extends DBMSHandlerBase
}
/**
- * Creates the reverse function in postgre sql that returns the reverse of
a string value.
- * The reverse function may be helpful in SQL to analyze 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
+ * Initialize Database on open
*/
- public void createReverseFunction(Connection conn)
+ @Override
+ public void attachDatabase(DBDatabase db, Connection conn)
{
- try {
- log.info("Creating reverse function: " + CREATE_REVERSE_FUNCTION);
- executeSQL(CREATE_REVERSE_FUNCTION, null, conn, null);
- } catch(SQLException e) {
- log.error("Unable to create reverse function!", e);
- throw new EmpireSQLException(this, e);
- }
+ super.attachDatabase(db, conn);
+ // set Sequence names
+ if (isUsePostgresSerialType())
+ initSerialSequenceNames(db, conn);
}
/**
@@ -426,6 +318,16 @@ public class DBMSHandlerPostgreSQL extends DBMSHandlerBase
GregorianCalendar cal = new GregorianCalendar();
return new java.sql.Timestamp(cal.getTimeInMillis());
}
+
+ /**
+ * Returns the Postgres DDL Generator
+ */
+ public PostgresDDLGenerator getDDLGenerator()
+ {
+ if (this.ddlGenerator==null)
+ this.ddlGenerator = new PostgresDDLGenerator(this);
+ return (PostgresDDLGenerator)this.ddlGenerator;
+ }
/**
* @see DBMSHandler#getDDLScript(DDLActionType, DBObject, DBSQLScript)
@@ -433,10 +335,8 @@ public class DBMSHandlerPostgreSQL extends DBMSHandlerBase
@Override
public void getDDLScript(DDLActionType type, DBObject dbo, DBSQLScript
script)
{
- if (ddlGenerator==null)
- ddlGenerator = new PostgresDDLGenerator(this);
// forward request
- ddlGenerator.getDDLScript(type, dbo, script);
+ getDDLGenerator().getDDLScript(type, dbo, script);
}
/**
@@ -455,4 +355,157 @@ public class DBMSHandlerPostgreSQL extends DBMSHandlerBase
}
+ protected void addReservedKeyWord(final String keyWord){
+ boolean added = reservedSQLKeywords.add(keyWord.toLowerCase());
+ if(!added){
+ log.debug("Existing keyWord added: " + keyWord);
+ }
+ }
+
+ protected void setReservedKeywords(){
+ // list of reserved keywords
+ //
http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
+ addReservedKeyWord("ALL".toLowerCase());
+ addReservedKeyWord("ANALYSE".toLowerCase());
+ addReservedKeyWord("ANALYZE".toLowerCase());
+ addReservedKeyWord("AND".toLowerCase());
+ addReservedKeyWord("ANY".toLowerCase());
+ addReservedKeyWord("ARRAY".toLowerCase());
+ addReservedKeyWord("AS".toLowerCase());
+ addReservedKeyWord("ASC".toLowerCase());
+ addReservedKeyWord("ASYMMETRIC".toLowerCase());
+ addReservedKeyWord("AUTHORIZATION".toLowerCase());
+ addReservedKeyWord("BETWEEN".toLowerCase());
+ addReservedKeyWord("BINARY".toLowerCase());
+ addReservedKeyWord("BOTH".toLowerCase());
+ addReservedKeyWord("CASE".toLowerCase());
+ addReservedKeyWord("CAST".toLowerCase());
+ addReservedKeyWord("CHECK".toLowerCase());
+ addReservedKeyWord("COLLATE".toLowerCase());
+ //addReservedKeyWord("COLUMN".toLowerCase());
+ //addReservedKeyWord("CONSTRAINT".toLowerCase());
+ addReservedKeyWord("CREATE".toLowerCase());
+ addReservedKeyWord("CROSS".toLowerCase());
+ addReservedKeyWord("CURRENT_DATE".toLowerCase());
+ addReservedKeyWord("CURRENT_ROLE".toLowerCase());
+ addReservedKeyWord("CURRENT_TIME".toLowerCase());
+ addReservedKeyWord("CURRENT_TIMESTAMP".toLowerCase());
+ addReservedKeyWord("CURRENT_USER".toLowerCase());
+ addReservedKeyWord("DEFAULT".toLowerCase());
+ addReservedKeyWord("DEFERRABLE".toLowerCase());
+ addReservedKeyWord("DESC".toLowerCase());
+ addReservedKeyWord("DISTINCT".toLowerCase());
+ addReservedKeyWord("DO".toLowerCase());
+ addReservedKeyWord("ELSE".toLowerCase());
+ addReservedKeyWord("END".toLowerCase());
+ addReservedKeyWord("EXCEPT".toLowerCase());
+ addReservedKeyWord("FALSE".toLowerCase());
+ addReservedKeyWord("FOR".toLowerCase());
+ addReservedKeyWord("FOREIGN".toLowerCase());
+ addReservedKeyWord("FREEZE".toLowerCase());
+ addReservedKeyWord("FROM".toLowerCase());
+ addReservedKeyWord("FULL".toLowerCase());
+ addReservedKeyWord("GRANT".toLowerCase());
+ //addReservedKeyWord("GROUP".toLowerCase());
+ addReservedKeyWord("HAVING".toLowerCase());
+ addReservedKeyWord("ILIKE".toLowerCase());
+ addReservedKeyWord("IN".toLowerCase());
+ addReservedKeyWord("INITIALLY".toLowerCase());
+ addReservedKeyWord("INNER".toLowerCase());
+ addReservedKeyWord("INTERSECT".toLowerCase());
+ addReservedKeyWord("INTO".toLowerCase());
+ addReservedKeyWord("IS".toLowerCase());
+ addReservedKeyWord("ISNULL".toLowerCase());
+ addReservedKeyWord("JOIN".toLowerCase());
+ addReservedKeyWord("LEADING".toLowerCase());
+ addReservedKeyWord("LEFT".toLowerCase());
+ addReservedKeyWord("LIKE".toLowerCase());
+ addReservedKeyWord("LIMIT".toLowerCase());
+ addReservedKeyWord("LOCALTIME".toLowerCase());
+ addReservedKeyWord("LOCALTIMESTAMP".toLowerCase());
+ addReservedKeyWord("NATURAL".toLowerCase());
+ addReservedKeyWord("NEW".toLowerCase());
+ addReservedKeyWord("NOT".toLowerCase());
+ addReservedKeyWord("NOTNULL".toLowerCase());
+ addReservedKeyWord("NULL".toLowerCase());
+ addReservedKeyWord("OFF".toLowerCase());
+ addReservedKeyWord("OFFSET".toLowerCase());
+ addReservedKeyWord("OLD".toLowerCase());
+ addReservedKeyWord("ON".toLowerCase());
+ addReservedKeyWord("ONLY".toLowerCase());
+ addReservedKeyWord("OR".toLowerCase());
+ addReservedKeyWord("ORDER".toLowerCase());
+ addReservedKeyWord("OUTER".toLowerCase());
+ addReservedKeyWord("OVERLAPS".toLowerCase());
+ addReservedKeyWord("PLACING".toLowerCase());
+ addReservedKeyWord("PRIMARY".toLowerCase());
+ addReservedKeyWord("REFERENCES".toLowerCase());
+ addReservedKeyWord("RETURNING".toLowerCase());
+ addReservedKeyWord("RIGHT".toLowerCase());
+ //addReservedKeyWord("SELECT".toLowerCase());
+ addReservedKeyWord("SESSION_USER".toLowerCase());
+ addReservedKeyWord("SIMILAR".toLowerCase());
+ addReservedKeyWord("SOME".toLowerCase());
+ addReservedKeyWord("SYMMETRIC".toLowerCase());
+ //addReservedKeyWord("TABLE".toLowerCase());
+ addReservedKeyWord("THEN".toLowerCase());
+ addReservedKeyWord("TO".toLowerCase());
+ addReservedKeyWord("TRAILING".toLowerCase());
+ addReservedKeyWord("TRUE".toLowerCase());
+ addReservedKeyWord("UNION".toLowerCase());
+ addReservedKeyWord("UNIQUE".toLowerCase());
+ //addReservedKeyWord("USER".toLowerCase());
+ addReservedKeyWord("USING".toLowerCase());
+ addReservedKeyWord("VERBOSE".toLowerCase());
+ addReservedKeyWord("WHEN".toLowerCase());
+ addReservedKeyWord("WHERE".toLowerCase());
+ addReservedKeyWord("WITH".toLowerCase());
+ }
+
+ /**
+ * Initializes the Sequence names of SERIAL and BIGSERIAL columns
+ * @param db the database for which to set the sequence names
+ * @param conn the connection
+ */
+ protected void initSerialSequenceNames(DBDatabase db, Connection conn)
+ {
+ // Find all identity columns
+ Map<String, DBTableColumn> identiyColumns = new HashMap<String,
DBTableColumn>();
+ for (DBTable t : db.getTables())
+ {
+ DBColumn[] key = t.getKeyColumns();
+ if (key!=null && key.length>0 &&
key[0].getDataType()==DataType.AUTOINC)
+ { // add to map
+ String name = key[0].getFullName();
+ DBTableColumn col = (DBTableColumn)key[0];
+ identiyColumns.put(name, col);
+ // show currently assigned sequence name
+ log.info("Initial sequence name for {} is {}", name,
col.getDefaultValue());
+ }
+ }
+ // Query from database
+ ResultSet rset = null;
+ try {
+ String sql = "SELECT column, seqname from ???";
+ rset = executeQuery(sql, null, false, conn);
+ while (rset.next())
+ {
+ String colName = rset.getString(1); // 1 = first column
+ String seqName = rset.getString(2); // 2 = second column
+ DBTableColumn col = identiyColumns.get(colName);
+ if (col!=null)
+ col.setDefaultValue(seqName); // set the sequence name
+ else
+ log.warn("Table column {} not found.", colName);
+ }
+ }
+ catch (SQLException e)
+ { // Don't know what to do
+ log.error("Failed to query Postgres Sequence names:
"+e.getMessage(), e);
+ } finally {
+ if (rset!=null)
+ this.closeResultSet(rset);
+ }
+ }
+
}
diff --git
a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresDDLGenerator.java
b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresDDLGenerator.java
index edfda3a..598a81a 100644
---
a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresDDLGenerator.java
+++
b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresDDLGenerator.java
@@ -29,6 +29,30 @@ import org.apache.empire.db.DBTableColumn;
public class PostgresDDLGenerator extends DBDDLGenerator<DBMSHandlerPostgreSQL>
{
+ /*
+ * Script for creating the Reverse-Function
+ */
+ 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";
+
+ private boolean createReverseFunction = false;
+
public PostgresDDLGenerator(DBMSHandlerPostgreSQL dbms)
{
super(dbms);
@@ -37,6 +61,22 @@ public class PostgresDDLGenerator extends
DBDDLGenerator<DBMSHandlerPostgreSQL>
}
/**
+ * Returns whether the reverse function should be created with the database
+ */
+ public boolean isCreateReverseFunction()
+ {
+ return createReverseFunction;
+ }
+
+ /**
+ * Set whether to create the reverse function with the database
+ */
+ public void setCreateReverseFunction(boolean createReverseFunction)
+ {
+ this.createReverseFunction = createReverseFunction;
+ }
+
+ /**
* sets PostgreSQL specific data types
*/
private void initDataTypes()
@@ -55,9 +95,9 @@ public class PostgresDDLGenerator extends
DBDDLGenerator<DBMSHandlerPostgreSQL>
{ // Auto increment
int bytes = Math.abs((int)size);
if (bytes>= 8) {
- sql.append("BIGSERIAL");
+ sql.append(dbms.isUsePostgresSerialType() ? "BIGSERIAL" :
DATATYPE_INT_BIG);
} else {
- sql.append("SERIAL");
+ sql.append(dbms.isUsePostgresSerialType() ? "SERIAL" :
DATATYPE_INTEGER);
}
//String seqName = createSequenceName(c);
//sql.append(" DEFAULT nextval('"+seqName+"')");
@@ -81,16 +121,23 @@ public class PostgresDDLGenerator extends
DBDDLGenerator<DBMSHandlerPostgreSQL>
@Override
protected void createDatabase(DBDatabase db, DBSQLScript script)
{
- // Create all Sequences
- for (DBTable table : db.getTables())
- {
- for (DBColumn dbColumn : table.getColumns()) {
- DBTableColumn c = (DBTableColumn) dbColumn;
- if (c.getDataType() == DataType.AUTOINC) {
- createSequence(db, c, script);
+ // Use SERIAL DataType?
+ if (dbms.isUsePostgresSerialType()==false)
+ { // Not using SERIAL
+ // Create all Sequences ourselves
+ for (DBTable table : db.getTables())
+ {
+ for (DBColumn dbColumn : table.getColumns()) {
+ DBTableColumn c = (DBTableColumn) dbColumn;
+ if (c.getDataType() == DataType.AUTOINC) {
+ createSequence(db, c, script);
+ }
}
}
}
+ // create reverse function
+ if (createReverseFunction)
+ script.addStmt(CREATE_REVERSE_FUNCTION);
// default processing
super.createDatabase(db, script);
}
@@ -111,10 +158,6 @@ public class PostgresDDLGenerator extends
DBDDLGenerator<DBMSHandlerPostgreSQL>
sql.append(" --\r\n");
sql.append("CREATE SEQUENCE ");
db.appendQualifiedName(sql, seqName, null);
-
-// create sequence foo_id_seq;
-// select setval('foo_id_seq', (select max(id) from foo));
-
sql.append(" INCREMENT BY 1 START WITH 1 MINVALUE 0");
// executeDLL
script.addStmt(sql);
diff --git
a/empire-db/src/test/java/org/apache/empire/dbms/postgresql/DBMSHandlerPostgreSQLTest.java
b/empire-db/src/test/java/org/apache/empire/dbms/postgresql/DBMSHandlerPostgreSQLTest.java
index b9b0ec8..2d650bb 100644
---
a/empire-db/src/test/java/org/apache/empire/dbms/postgresql/DBMSHandlerPostgreSQLTest.java
+++
b/empire-db/src/test/java/org/apache/empire/dbms/postgresql/DBMSHandlerPostgreSQLTest.java
@@ -55,7 +55,7 @@ public class DBMSHandlerPostgreSQLTest
{
Connection conn = dbResource.getConnection();
DBMSHandler dbms = dbResource.newDriver();
- ((DBMSHandlerPostgreSQL)dbms).createReverseFunction(conn);
+
((DBMSHandlerPostgreSQL)dbms).getDDLGenerator().setCreateReverseFunction(true);
Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT
reverse('reverseme')");
