[PATCH] autoincrement on postgres

[Bill Schneider <bs...@vecna.com>]

This patch fixes two bugs with autoincrement on postgres:

1. re-loading the data model: PostgreSQL creates an implicit sequence 
(e.g., foo_foo_id_seq) for each "serial" column.  This must be dropped 
before the table can be re-created: dropping the table doesn't drop the 
sequence.  (drop.vm)

2. "native" mode: wasn't working because Torque expects to retrieve IDs 
after insertion from a call to currval('FOO_SEQ'), but the sequence 
Postgres creates doesn't match that name.  The quick fix is to create 
the sequence FOO_SEQ to match the sequence name on other DBs that use 
sequences (Oracle) and alter the table to use that sequence for any 
auto-increment columns.

(A better fix might be to have the sequence name itself be somehow 
generated from the database adapters, or to always use postgres-style 
naming for sequences even in Oracle.)

This will probably give strange results in the not-so-common case where 
is more than one autoincrement column in the same table.

Enjoy! (and hope I did formatting right this time)

-- Bill

Index: src/templates/sql/base/postgresql/drop.vm
===================================================================
RCS file: 
/home/cvspublic/jakarta-turbine-torque/src/templates/sql/base/postgresql/drop.vm,v
retrieving revision 1.1.1.1
diff -u -r1.1.1.1 drop.vm
--- src/templates/sql/base/postgresql/drop.vm	2 Aug 2001 05:08:40 -0000	
1.1.1.1
+++ src/templates/sql/base/postgresql/drop.vm	18 Feb 2002 20:34:46 -0000
@@ -1,4 +1,13 @@
  drop table $table.Name;
+##
+## drop any leftover implicitly-created columns
+##
+#foreach ($col in $table.Columns)
+#if ($col.isAutoIncrement())
+drop sequence ${table.Name}_${col.Name}_seq;
+#end
+#end
+##
  #if ($table.IdMethod == "native")
  drop sequence $table.SequenceName;
-#end
\ No newline at end of file
+#end
Index: src/templates/sql/base/postgresql/table.vm
===================================================================
RCS file: 
/home/cvspublic/jakarta-turbine-torque/src/templates/sql/base/postgresql/table.vm,v
retrieving revision 1.2
diff -u -r1.2 table.vm
--- src/templates/sql/base/postgresql/table.vm	15 Nov 2001 13:23:46 -0000	
1.2
+++ src/templates/sql/base/postgresql/table.vm	18 Feb 2002 20:34:46 -0000
@@ -15,3 +15,18 @@

  );
  #if($index.length() > 0)$strings.chop($index,1)#end
+
+## WRS 2/18/02
+## 1. create a new sequence for the table
+## 2. alter the serial columns to use ours
+## 3. the default implicitly-created sequences will be dropped when
+## the data model is reloaded (see drop.vm)
+#if ($table.IdMethod == "native")
+create sequence $table.SequenceName;
+#foreach ($col in $table.Columns)
+#if ($col.isAutoIncrement())
+alter table $table.Name alter $col.Name
+   set default nextval('$table.SequenceName');
+#end
+#end
+#end


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>