svn commit: r553276 - in /roller/trunk/apps/weblogger/src/sql: 310-to-320-migration.vm 310-to-400-migration.vm 320-to-400-migration.vm dbscripts.properties droptables.sql

[ag...@apache.org]

Author: agilliland
Date: Wed Jul  4 10:16:53 2007
New Revision: 553276

URL: http://svn.apache.org/viewvc?view=rev&rev=553276
Log:
fixing up db scripts a bit and moving old 310 to 320 upgrade work into 4.0 upgrade script.


Added:
    roller/trunk/apps/weblogger/src/sql/310-to-400-migration.vm
      - copied, changed from r552896, roller/trunk/apps/weblogger/src/sql/320-to-400-migration.vm
Removed:
    roller/trunk/apps/weblogger/src/sql/310-to-320-migration.vm
    roller/trunk/apps/weblogger/src/sql/320-to-400-migration.vm
Modified:
    roller/trunk/apps/weblogger/src/sql/dbscripts.properties
    roller/trunk/apps/weblogger/src/sql/droptables.sql

Copied: roller/trunk/apps/weblogger/src/sql/310-to-400-migration.vm (from r552896, roller/trunk/apps/weblogger/src/sql/320-to-400-migration.vm)
URL: http://svn.apache.org/viewvc/roller/trunk/apps/weblogger/src/sql/310-to-400-migration.vm?view=diff&rev=553276&p1=roller/trunk/apps/weblogger/src/sql/320-to-400-migration.vm&r1=552896&p2=roller/trunk/apps/weblogger/src/sql/310-to-400-migration.vm&r2=553276
==============================================================================
--- roller/trunk/apps/weblogger/src/sql/320-to-400-migration.vm (original)
+++ roller/trunk/apps/weblogger/src/sql/310-to-400-migration.vm Wed Jul  4 10:16:53 2007
@@ -1,5 +1,5 @@
 #** 
-3xx-to-400-migration.vm: Velocity template that generates vendor-specific database scripts 
+310-to-400-migration.vm: Velocity template that generates vendor-specific database scripts 
 
 DON'T RUN THIS, IT'S NOT A DATABASE CREATION SCRIPT!!!
 **#
@@ -26,10 +26,58 @@
 insert into rag_planet (id,title,handle) values ('zzz_default_planet_zzz','Default Planet','zzz_default_planet_zzz');
 update rag_group set planet_id='zzz_default_planet_zzz';
 
+-- remove old id column of group subscription table
+alter table rag_group_subscription drop column id;
+
 -- drop old planet config table
 drop table if exists rag_config;
 
 
+-- upgrade the way hierarchical objects are modeled
+
+-- add new parentid column to weblogcategory table
+#addColumnNull("weblogcategory" "parentid" "varchar(48)")
+create index ws_parentid_idx on weblogcategory( parentid );
+
+-- add new path column to weblogcategory table
+#addColumnNull("weblogcategory" "path" "varchar(255)")
+create index ws_path_idx on weblogcategory( path );
+
+-- need to add this index for existing folder.parentid
+create index fo_parentid_idx on folder( parentid );
+
+-- add new path column to folder table
+#addColumnNull("folder" "path" "varchar(255)")
+create index fo_path_idx on folder( path );
+
+
+-- update comment handling
+
+-- add new fields to comment table to support CommentValidators
+#addColumnNull("roller_comment" "referrer" "varchar(255)")
+#addColumnNull("roller_comment" "useragent" "varchar(255)")
+
+-- add new status field to comment table to simplify queries
+#addColumnNotNull("roller_comment" "status" "varchar(20)" "'APPROVED'")
+
+-- new status column needs an index
+create index co_status_idx on roller_comment(status);
+
+-- update existing data to use new status column
+update roller_comment set status = 'APPROVED', posttime=posttime where approved=1;
+update roller_comment set status = 'PENDING', posttime=posttime where pending=1;
+update roller_comment set status = 'SPAM', posttime=posttime where spam=1;
+update roller_comment set status = 'DISAPPROVED', posttime=posttime where approved=0 and spam=0 and pending=0;
+
+
+-- better support for doing scheduled entries
+
+-- add new status option 'SCHEDULED' for future published entries
+update weblogentry set status = 'SCHEDULED', pubtime=pubtime, updatetime=updatetime where pubtime > now();
+
+-- add new client column to roller_tasklock table
+#addColumnNull("roller_tasklock" "client" "varchar(255)")
+
 -- new column to support account activation by email
 #addColumnNull("rolleruser" "activationcode" "varchar(48)")
 
@@ -57,9 +105,34 @@
 update website set enablemultilang = 1 where showalllangs = 0;
 
 
+-- some missing foreign key constraints
+alter table roller_user_permissions add constraint up_userid_fk
+    foreign key ( user_id ) references rolleruser( id ) $!db.ADDL_FK_PARAMS ;
+
+alter table roller_user_permissions add constraint up_websiteid_fk
+    foreign key ( website_id ) references website( id ) $!db.ADDL_FK_PARAMS ;
+
+
+-- some various indexes to improve performance
+create index rhc_dailyhits_idx on roller_hitcounts( dailyhits );
+create index we_combo1_idx on weblogentry(status, pubtime, websiteid);
+create index we_combo2_idx on weblogentry(websiteid, pubtime, status);
+create index co_combo1_idx on roller_comment(status, posttime);
+
+-- remove old indexes that are no longer of value
+drop index we_pubtime_idx on weblogentry;
+drop index we_pubentry_idx on weblogentry;
+-- drop index co_pending_idx on roller_comment;
+-- drop index co_approved_idx on roller_comment;
+
+-- fix wacky indexs which ended up with a size constraint
+drop index rage_sid_idx on rag_entry;
+create index rage_sid_idx on rag_entry(subscription_id);
+drop index raggs_gid_idx on rag_group_subscription;
+create index raggs_gid_idx on rag_group_subscription(group_id);
+drop index raggs_sid_idx on rag_group_subscription;
+create index raggs_sid_idx on rag_group_subscription(subscription_id);
 
--- remove old id column of group subscription table
-alter table rag_group_subscription drop column id;
 
 -- remove old usercookie table which has been unused since 0.x?
 drop table if exists usercookie;
@@ -84,3 +157,4 @@
 --alter table website drop column weblogdayid;
 --alter table weblogentry drop column publishentry;
 --alter table weblogentry drop column link;
+

Modified: roller/trunk/apps/weblogger/src/sql/dbscripts.properties
URL: http://svn.apache.org/viewvc/roller/trunk/apps/weblogger/src/sql/dbscripts.properties?view=diff&rev=553276&r1=553275&r2=553276
==============================================================================
--- roller/trunk/apps/weblogger/src/sql/dbscripts.properties (original)
+++ roller/trunk/apps/weblogger/src/sql/dbscripts.properties Wed Jul  4 10:16:53 2007
@@ -7,5 +7,5 @@
 # list all db templates to generate, separated by spaces
 templates=createdb 200-to-210-migration 210-to-230-migration \
 230-to-240-migration 240-to-300-migration 300-to-310-migration \
-310-to-320-migration 320-to-400-migration
+310-to-400-migration
 

Modified: roller/trunk/apps/weblogger/src/sql/droptables.sql
URL: http://svn.apache.org/viewvc/roller/trunk/apps/weblogger/src/sql/droptables.sql?view=diff&rev=553276&r1=553275&r2=553276
==============================================================================
--- roller/trunk/apps/weblogger/src/sql/droptables.sql (original)
+++ roller/trunk/apps/weblogger/src/sql/droptables.sql Wed Jul  4 10:16:53 2007
@@ -21,7 +21,6 @@
 drop table rag_group;
 drop table rag_planet;
 drop table rag_properties;
-drop table rag_config;
 
 -- non-associated tables
 drop table newsfeed;