Hello,
here is the patch. I modified the appendColumnDesc() method of the
postgresql and derby driver so it suits my needs. I ran some example
code against postgresql 8.4.5 and apache derby 10.6.2 - created the
database, inserted some data and executed some selects.
hope this is of some help.
kind regards,
Eike
On [Wed, 22.12.2010 16:01], Rainer Döbele wrote:
> Hi Eike,
>
> your suggested solution is exactly the one I would recommend.
> (Besides I would suggest to make the condition "size>=8" rather than
> "size==8")
>
> I cannot test postgre SQL, but if you send us your implementation of
> appendColumnDesc we will check it and change our implementation accordingly.
>
> Thanks and regards,
> Rainer
>
> Eike Kettner wrote:
> > from: Eike Kettner [mailto:[email protected]]
> > to: [email protected]
> > re: how to use bigint integers
> >
> > Hello,
> >
> > I have a question regarding the integer type and ddl script generation.
> > I'm using empire-db with postgre-sql and apache derby. I'd like the
> > primary keys to be BIGINT and not INT and I didn't find a good way how
> > to tell empiredb when to use BIGINT and when to use INT - the datatype
> > is DataType.INTEGER and the drivers for postgre and derby do always say
> > INT (not sure about the newest trunk version).
> >
> > What I do is overwriting the method
> >
> > boolean appendColumnDesc(DBTableColumn c, StringBuilder sql, boolean
> > alter)
> >
> > of the DBDatabaseDriver class and checking for an given size. So a
> > column with DataType.INTEGER and size=8 is then created as BIGINT.
> >
> > Did I miss something, or how would you recommend to address this?
> >
> > Thanks in advance
> > Eike
> >
>
--
email: [email protected] https://www.eknet.org pgp: 481161A0
Index: empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java
===================================================================
--- empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java (Revision 1052196)
+++ empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java (Arbeitskopie)
@@ -18,11 +18,6 @@
*/
package org.apache.empire.db.postgresql;
-import java.sql.Connection;
-import java.sql.SQLException;
-import java.util.GregorianCalendar;
-import java.util.Iterator;
-
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.empire.commons.Errors;
@@ -44,7 +39,12 @@
import org.apache.empire.db.DBTableColumn;
import org.apache.empire.db.DBView;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.GregorianCalendar;
+import java.util.Iterator;
+
/**
* This class provides support for the PostgreSQL database system.
*
@@ -657,24 +657,33 @@
{
case INTEGER:
{ // Integer type
- sql.append("INT");
- int size = (int)c.getSize();
- if (size>0)
- { // Set Integer length
- sql.append("(");
- sql.append(String.valueOf(size));
- sql.append(")");
+ int size = (int) c.getSize();
+ if (size >= 8) {
+ sql.append("BIGINT");
+ } else {
+ sql.append("INT");
}
+// if (size>0)
+// { // Set Integer length
+// sql.append("(");
+// sql.append(String.valueOf(size));
+// sql.append(")");
+// }
break;
- }
+ }
case AUTOINC:
{ // Auto increment
- sql.append("INT");
-
- //String seqName = createSequenceName(c);
+ int size = (int) c.getSize();
+ if (size >= 8) {
+ sql.append("BIGSERIAL");
+ } else {
+ sql.append("SERIAL");
+ }
+
+ //String seqName = createSequenceName(c);
//sql.append(" DEFAULT nextval('"+seqName+"')");
break;
- }
+ }
case TEXT:
{ // Check fixed or variable length
int size = Math.abs((int) c.getSize());
@@ -730,7 +739,7 @@
sql.append("CHAR(36)");
break;
case UNKNOWN:
- log.error("Cannot append column of Data-Type 'UNKNOWN'");
+ //log.error("Cannot append column of Data-Type 'UNKNOWN'");
return false;
}
// Default Value
Index: empire-db/src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java
===================================================================
--- empire-db/src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java (Revision 1052196)
+++ empire-db/src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java (Arbeitskopie)
@@ -18,10 +18,6 @@
*/
package org.apache.empire.db.derby;
-import java.sql.Connection;
-import java.util.GregorianCalendar;
-import java.util.Iterator;
-
import org.apache.empire.commons.Errors;
import org.apache.empire.commons.StringUtils;
import org.apache.empire.data.DataType;
@@ -42,7 +38,11 @@
import org.apache.empire.db.DBView;
import org.apache.empire.db.oracle.DBDatabaseDriverOracle.BooleanType;
+import java.sql.Connection;
+import java.util.GregorianCalendar;
+import java.util.Iterator;
+
/**
* This class provides support for the Derby database system.
*
@@ -509,23 +509,32 @@
{
case INTEGER:
{ // Integer type
- sql.append("INT");
int size = (int)c.getSize();
- if (size>0)
- { // Set Integer length
- sql.append("(");
- sql.append(String.valueOf(size));
- sql.append(")");
+ if (size >= 8) {
+ sql.append("BIGINT");
+ } else {
+ sql.append("INT");
}
+// if (size>0)
+// { // Set Integer length
+// sql.append("(");
+// sql.append(String.valueOf(size));
+// sql.append(")");
+// }
break;
- }
+ }
case AUTOINC:
{ // Auto increment
- sql.append("INT");
- if (useSequenceTable==false)
+ int size = (int)c.getSize();
+ if (size >= 8) {
+ sql.append("BIGINT");
+ } else {
+ sql.append("INT");
+ }
+ if (!isUseSequenceTable())
sql.append(" GENERATED ALWAYS AS IDENTITY");
break;
- }
+ }
case TEXT:
{ // Check fixed or variable length
int size = Math.abs((int) c.getSize());
@@ -553,9 +562,10 @@
sql.append("TIMESTAMP");
break;
case BOOL:
- if ( booleanType==BooleanType.CHAR )
- sql.append("CHAR(1)");
- else sql.append("SMALLINT");
+// if ( booleanType== DBDatabaseDriverOracle.BooleanType.CHAR ) <- this looks weird to me :), i commented it out since it is always false for derby
+// sql.append("CHAR(1)");
+// else
+ sql.append("SMALLINT");
break;
case DOUBLE:
sql.append("DOUBLE");
