svn commit: r467202 - in /incubator/roller/trunk/metadata/database: 300-to-310-migration.vm control.vm createdb.vm

[sn...@apache.org]

Author: snoopdave
Date: Mon Oct 23 19:20:24 2006
New Revision: 467202

URL: http://svn.apache.org/viewvc?view=rev&rev=467202
Log:
Remove primary-key and not null constraints from rag_group_subscription id. SQL tested on MySQL and Derby. Oracle, PostgreSQL and HSQLDB SQL copied from docs, but not yet tested

Modified:
    incubator/roller/trunk/metadata/database/300-to-310-migration.vm
    incubator/roller/trunk/metadata/database/control.vm
    incubator/roller/trunk/metadata/database/createdb.vm

Modified: incubator/roller/trunk/metadata/database/300-to-310-migration.vm
URL: http://svn.apache.org/viewvc/incubator/roller/trunk/metadata/database/300-to-310-migration.vm?view=diff&rev=467202&r1=467201&r2=467202
==============================================================================
--- incubator/roller/trunk/metadata/database/300-to-310-migration.vm (original)
+++ incubator/roller/trunk/metadata/database/300-to-310-migration.vm Mon Oct 23 19:20:24 2006
@@ -45,4 +45,16 @@
     websiteid       varchar(48) not null,
     dailyhits	    integer
 );
-create index rhc_websiteid_idx on roller_hitcounts( websiteid );
+create index rhc_websiteid_idx on roller_hitcounts(websiteid);
+
+-- The rag_group_subscription table has been simplified and no longer needs a 
+-- primary key but we don't want to drop the field just yet because that would 
+-- make it impossible to abort a 3.1 upgrade and return to 3.0. 
+#dropNotNullFromTableId("rag_group_subscription")
+
+
+
+
+
+
+

Modified: incubator/roller/trunk/metadata/database/control.vm
URL: http://svn.apache.org/viewvc/incubator/roller/trunk/metadata/database/control.vm?view=diff&rev=467202&r1=467201&r2=467202
==============================================================================
--- incubator/roller/trunk/metadata/database/control.vm (original)
+++ incubator/roller/trunk/metadata/database/control.vm Mon Oct 23 19:20:24 2006
@@ -21,20 +21,41 @@
 **#
 #macro(addColumnNotNull $table $column $type $default)
 #if($DBTYPE == "MYSQL" || $DBTYPE=="HSQDB")
-alter table $table add column $column $type default $default not null;  
+    alter table $table add column $column $type default $default not null;  
 #elseif ($DBTYPE == "POSTGRESQL")
-alter table $table add column $column $type;
-alter table $table alter $column set default $default;
-update $table set $column=$default;
-alter table $table alter $column set not null;
+    alter table $table add column $column $type;
+    alter table $table alter $column set default $default;
+    update $table set $column=$default;
+    alter table $table alter $column set not null;
 #elseif ($DBTYPE == "HSQLDB")
-alter table $table add column $column $type default $default not null; 
+    alter table $table add column $column $type default $default not null; 
 #elseif ($DBTYPE == "DERBY" || $DBTYPE == "DB2")
-alter table $table add column $column $type with default $default not null;
+    alter table $table add column $column $type with default $default not null;
 #elseif ($DBTYPE == "ORACLE")
-alter table $table add $column $type default $default not null;  
+    alter table $table add $column $type default $default not null;  
 #end
 #end
+
+
+#**
+Special macro to drop NOT NULL requirement from an 'id' column.
+**#
+#macro(dropNotNullFromTableId $table)
+#if($DBTYPE == "MYSQL")
+    alter table $table drop primary key;
+    alter table $table modify id varchar(48) null;
+#elseif ($DBTYPE == "POSTGRESQL")
+    alter table $table alter column drop not null;
+#elseif ($DBTYPE == "HSQLDB")
+    alter table $table alter column id varchar(48) null;
+#elseif ($DBTYPE == "DERBY" || $DBTYPE == "DB2")
+    alter table $table drop primary key;
+    alter table $table alter column id null;
+#elseif ($DBTYPE == "ORACLE")
+    alter table $table drop id not null;
+#end
+#end
+
 
 #** Loop through templates, generate database scripts **#
 #foreach ($template in $templates) 

Modified: incubator/roller/trunk/metadata/database/createdb.vm
URL: http://svn.apache.org/viewvc/incubator/roller/trunk/metadata/database/createdb.vm?view=diff&rev=467202&r1=467201&r2=467202
==============================================================================
--- incubator/roller/trunk/metadata/database/createdb.vm (original)
+++ incubator/roller/trunk/metadata/database/createdb.vm Mon Oct 23 19:20:24 2006
@@ -408,7 +408,6 @@
 alter table entryattribute add constraint ea_name_uq unique ( entryid, name$!INDEXSIZE );
 
 create table rag_group_subscription (
-    id               varchar(48) not null primary key,
     group_id         varchar(48) not null,
     subscription_id  varchar(48) not null
 );