You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@roller.apache.org by sn...@apache.org on 2005/12/21 17:01:15 UTC
svn commit: r358311 [1/2] - in
/incubator/roller/trunk/web/WEB-INF/dbscripts: ./ hsqldb/ mysql/ postgresql/
Author: snoopdave
Date: Wed Dec 21 08:00:58 2005
New Revision: 358311
URL: http://svn.apache.org/viewcvs?rev=358311&view=rev
Log:
Checking in old migraton scritps for databases supported before 2.0
Added:
incubator/roller/trunk/web/WEB-INF/dbscripts/
incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/
incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/094-to-095-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/095-to-096-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/096-to-097-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/097-to-098-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/098-to-099-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/098-to-100-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/100-to-110-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/110-to-120-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/120-to-130-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/130-to-200-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/
incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/094-to-095-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/095-to-096-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/096-to-097-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/097-to-098-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/098-to-099-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/098-to-100-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/100-to-110-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/110-to-120-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/120-to-130-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/130-to-200-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/
incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/094-to-095-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/095-to-096-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/096-to-097-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/097-to-098-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/098-to-099-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/098-to-100-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/100-to-110-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/110-to-120-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/120-to-130-migration.sql
incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/130-to-200-migration.sql
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/094-to-095-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/094-to-095-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/094-to-095-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/094-to-095-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,134 @@
+-- we will drop & recreate every table so
+-- that the new named constraints can be used
+-- for HSQLDB replace text datatypes with longvarchar
+-- for Postresql replace bit datatype with boolean
+
+CREATE TABLE tempuser AS SELECT * FROM user;
+DROP TABLE user;
+create table rolleruser (
+ id varchar(255) not null primary key,
+ username varchar(255) not null,
+ password varchar(255) not null,
+ fullname varchar(255) not null,
+ emailaddress varchar(255) not null
+);
+INSERT INTO rolleruser SELECT * FROM tempuser;
+
+CREATE TABLE temprole AS SELECT * FROM role;
+DROP TABLE role;
+create table role (
+ id varchar(255) not null primary key,
+ role varchar(255) not null,
+ username varchar(255) not null
+);
+INSERT INTO role SELECT * FROM temprole;
+
+CREATE TABLE temppage AS SELECT * FROM page;
+DROP TABLE page;
+create table page (
+ id varchar(255) not null primary key,
+ name varchar(255) not null,
+ description varchar(255) null,
+ link varchar(255) null,
+ websiteid varchar(255) not null,
+ template longvarchar not null,
+ updatetime timestamp not null
+);
+INSERT INTO page
+ (id, name, description, websiteid, template, updatetime)
+ SELECT id, name, description, websiteid, template, updatetime FROM temppage;
+
+CREATE TABLE tempwebsite AS SELECT * FROM website;
+DROP TABLE website;
+create table website (
+ id varchar(255) not null primary key,
+ name varchar(255) not null,
+ description varchar(255) not null,
+ userid varchar(255) not null,
+ defaultpageid varchar(255) default 0 not null,
+ weblogdayid varchar(255) not null,
+ enablebloggerapi bit default 0 not null,
+ bloggercatid varchar(255) null
+);
+INSERT INTO website SELECT * FROM tempwebsite;
+
+CREATE TABLE tempfolder AS SELECT * FROM folder;
+DROP TABLE folder;
+create table folder (
+ id varchar(255) not null primary key,
+ name varchar(255) not null,
+ description varchar(255) null,
+ parentid varchar(255) null,
+ websiteid varchar(255) not null
+);
+INSERT INTO folder SELECT id,name,description,parentid,websiteid
+ FROM tempfolder;
+UPDATE folder SET parentid = NULL WHERE name='root' AND parentid=0;
+
+CREATE TABLE tempbookmark AS SELECT * FROM bookmark;
+DROP TABLE bookmark;
+create table bookmark (
+ id varchar(255) not null primary key,
+ folderid varchar(255) not null,
+ name varchar(255) not null,
+ description varchar(255) null,
+ url varchar(255) not null,
+ priority integer default 100 not null,
+ image varchar(255) null
+);
+INSERT INTO bookmark SELECT * FROM tempbookmark;
+ALTER TABLE bookmark ADD COLUMN (weight integer default 0 not null);
+ALTER TABLE bookmark ADD COLUMN (feedurl varchar(255) null);
+
+CREATE TABLE tempweblogcategory AS SELECT * FROM weblogcategory;
+DROP TABLE weblogcategory;
+create table weblogcategory (
+ id varchar(255) not null primary key,
+ name varchar(255) not null,
+ description varchar(255) null,
+ websiteid varchar(255) not null,
+ image varchar(255) null
+);
+INSERT INTO weblogcategory SELECT * FROM tempweblogcategory;
+
+CREATE TABLE tempweblogentry AS SELECT * FROM weblogentry;
+DROP TABLE weblogentry;
+create table weblogentry (
+ id varchar(255) not null primary key,
+ anchor varchar(255) not null,
+ title varchar(255) not null,
+ text longvarchar not null,
+ pubtime timestamp not null,
+ updatetime timestamp not null,
+ websiteid varchar(255) not null,
+ categoryid varchar(255) not null
+);
+INSERT INTO weblogentry SELECT * FROM tempweblogentry;
+
+CREATE TABLE tempnewsfeed AS SELECT * FROM newsfeed;
+DROP TABLE newsfeed;
+create table newsfeed (
+ id varchar(255) not null primary key,
+ name varchar(255) not null,
+ description varchar(255) not null,
+ link varchar(255) not null,
+ websiteid varchar(255) not null
+);
+INSERT INTO newsfeed SELECT * FROM tempnewsfeed;
+
+-- Now add the constraints
+alter table rolleruser add constraint rolleruser_username_uq unique ( username );
+
+alter table website add constraint website_userid_fk foreign key ( userid ) references rolleruser ( id );
+
+alter table folder add constraint folder_websiteid_fk foreign key ( websiteid ) references website( id );
+alter table folder add constraint folder_parentid_fk foreign key ( parentid ) references folder( id );
+
+alter table bookmark add constraint bookmark_folderid_fk foreign key ( folderid ) references folder( id );
+
+alter table weblogcategory add constraint weblogcategory_websiteid_fk foreign key ( websiteid ) references website( id );
+
+alter table weblogentry add constraint weblogentry_websiteid_fk foreign key ( websiteid ) references website( id );
+alter table weblogentry add constraint weblogentry_categoryid_fk foreign key ( categoryid ) references weblogcategory( id );
+
+alter table newsfeed add constraint newsfeed_websiteid_fk foreign key ( websiteid ) references website( id );
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/095-to-096-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/095-to-096-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/095-to-096-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/095-to-096-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,21 @@
+
+ALTER TABLE weblogentry ADD COLUMN (publishentry @BOOLEAN_SQL_TYPE2@ not null);
+
+ALTER TABLE website ADD COLUMN (editorpage varchar(255) not null);
+UPDATE website SET editorpage = 'editor-ekit.jsp';
+
+CREATE TABLE temprole AS SELECT * FROM role;
+DROP TABLE role;
+create table role (
+ id varchar(255) not null primary key,
+ role varchar(255) not null,
+ username varchar(255) not null,
+ userid varchar(255) not null
+);
+INSERT INTO role
+ (id, role, username, userid)
+ SELECT temprole.id, role, temprole.username, rolleruser.id
+ FROM temprole, rolleruser
+ WHERE temprole.username = rolleruser.username;
+ALTER TABLE role ADD CONSTRAINT role_userid_fk
+ foreign key ( userid ) references rolleruser( id );
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/096-to-097-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/096-to-097-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/096-to-097-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/096-to-097-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,54 @@
+
+create table if not exists comment (
+ id varchar(255) not null primary key,
+ entryid varchar(255) not null,
+ name varchar(255) null,
+ email varchar(255) null,
+ url varchar(255) null,
+ content longvarchar null,
+ posttime timestamp not null
+);
+create index comment_entryid_index on comment( entryid );
+
+
+-- Referer tracks URLs that refer to websites and entries
+create table referer (
+ id varchar(48) not null primary key,
+ websiteid varchar(48) not null,
+ entryid varchar(48),
+ datestr varchar(10),
+ refurl varchar(255) not null,
+ refpermalink varchar(255),
+ reftime timestamp,
+ requrl varchar(255),
+ title varchar(255),
+ excerpt longvarchar null,
+ dayhits integer default 0 not null,
+ totalhits integer default 0 not null,
+ visible bit default 0 not null,
+ duplicate bit default 0 not null
+);
+create index referer_websiteid_index on referer( websiteid );
+create index referer_entryid_index on referer( entryid );
+
+
+alter table website add column (allowcomments bit default 1 not null);
+
+alter table website add column (ignorewords longvarchar null);
+
+alter table comment add constraint comment_entryid_fk
+ foreign key ( entryid ) references weblogentry( id );
+
+alter table folder add constraint folder_websiteid_fk
+ foreign key ( websiteid ) references website( id );
+
+alter table folder add constraint folder_entryid_fk
+ foreign key ( entryid ) references weblogentry( id );
+
+alter table referer add constraint referer_websiteid_fk
+ foreign key ( websiteid ) references website( id );
+
+alter table referer add constraint referer_entryid_fk
+ foreign key ( entryid ) references weblogentry( id );
+
+
\ No newline at end of file
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/097-to-098-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/097-to-098-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/097-to-098-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/097-to-098-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,80 @@
+-- Eliminate use of reserved keywords
+
+-- role -> userrole
+CREATE TABLE temprole AS SELECT * FROM role;
+DROP TABLE role;
+CREATE table userrole (
+ id varchar(48) not null primary key,
+ rolename varchar(255) not null,
+ username varchar(255) not null,
+ userid varchar(48) not null
+);
+INSERT INTO userrole
+ (id, rolename, username, userid)
+ SELECT id, role, username, userid
+ FROM temprole;
+ALTER table userrole add constraint userrole_userid_fk
+ foreign key ( userid ) references rolleruser( id );
+create index userrole_userid_index on userrole( userid );
+
+
+-- password -> passphrase
+CREATE TABLE tempuser AS SELECT * FROM rolleruser;
+DROP TABLE rolleruser;
+CREATE table rolleruser (
+ id varchar(48) not null primary key,
+ username varchar(255) not null,
+ passphrase varchar(255) not null,
+ fullname varchar(255) not null,
+ emailaddress varchar(255) not null,
+ datecreated timestamp not null,
+ userenabled bit default 1 not null
+);
+-- Populate the new table, filling datecreated using least pubtime of any of the user's weblog entries.
+INSERT INTO rolleruser
+ (id, username, passphrase, fullname, emailaddress, datecreated)
+ SELECT t.id, t.username, t.password, t.fullname, t.emailaddress, MIN(e.pubtime)
+ FROM tempuser t, website w, weblogentry e
+ WHERE t.id = w.userid and w.id = e.websiteid
+ GROUP BY t.id;
+
+ALTER table rolleruser add constraint rolleruser_username_uq unique ( username );
+
+
+-- page -> webpage
+CREATE TABLE temppage AS SELECT * FROM page;
+DROP TABLE page;
+create table webpage AS SELECT * FROM temppage;
+
+
+-- Drop bad indexes: some were poorly named, others just wrong
+-- these may or may not exist
+-- alter table website drop index userid_index;
+-- alter table folder drop index webisteid_index;
+-- alter table folder drop index parentid_index;
+-- alter table bookmark drop index folderid_index;
+-- alter table weblogcategory drop index websiteid_index;
+-- alter table weblogentry drop index websiteid_index;
+-- alter table weblogentry drop index categoryid_index;
+-- alter table newsfeed drop index websiteid_index;
+-- alter table comment drop index entryid_index;
+-- alter table referer drop index websiteid_index;
+-- alter table referer drop index entryid_index;
+
+-- Add good indexes to replace the bad ones
+create index website_userid_index on website( userid );
+create index folder_websiteid_index on folder( websiteid );
+create index folder_parentid_index on folder( parentid );
+create index bookmark_folderid_index on bookmark( folderid );
+create index weblogcategory_websiteid_index on weblogcategory( websiteid );
+create index weblogentry_websiteid_index on weblogentry( websiteid );
+create index weblogentry_categoryid_index on weblogentry( categoryid );
+create index newsfeed_websiteid_index on newsfeed( websiteid );
+create index comment_entryid_index on comment( entryid );
+create index referer_websiteid_index on referer( websiteid );
+create index referer_entryid_index on referer( entryid );
+
+-- Only use these when you are certain of the upgrade
+-- drop table temprole;
+-- drop table tempuser;
+-- drop table temppage;
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/098-to-099-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/098-to-099-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/098-to-099-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/098-to-099-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,5 @@
+-- add new attribute to WeblogEntry
+alter table weblogentry add column (autoformatentry BOOLEAN_SQL_TYPE not null);
+
+-- add new attribute to Website
+alter table website add column (autoformatdefault BOOLEAN_SQL_TYPE not null);
\ No newline at end of file
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/098-to-100-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/098-to-100-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/098-to-100-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/098-to-100-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,107 @@
+-- add new attribute to Comment
+alter table comment add column notify bit;
+alter table comment add column spam bit;
+alter table comment add column remotehost varchar(128);
+update comment set spam=0, notify=0, posttime=posttime;
+
+-- add new attribute to WeblogEntry
+alter table weblogentry add column link varchar(255);
+alter table weblogentry add column plugins varchar(255);
+alter table weblogentry add column allowcomments bit;
+alter table weblogentry add column commentdays integer;
+alter table weblogentry add column rightToLeft bit;
+alter table weblogentry add column pinnedtomain bit;
+update weblogentry set pubtime=pubtime, updatetime=updatetime, allowcomments=1, pinnedtomain=0;
+
+-- add new attributes to Website
+alter table website add column editortheme varchar(255);
+alter table website add column locale varchar(20);
+alter table website add column timezone varchar(50);
+alter table website add column defaultcatid varchar(48);
+alter table website add column defaultplugins varchar(255);
+alter table website add column emailcomments bit;
+alter table website add column emailfromaddress varchar(255);
+alter table website add column isenabled bit;
+update website set emailcomments=0, isenabled=1, locale='en', timezone='America/New_York';
+
+-- reset possibly bad bloggercategoryid settings, repairIfNeeded will fix them
+update website set bloggercatid=null;
+
+-- weblog categories are now hierarchical
+create table weblogcategoryassoc (
+ id varchar(48) not null primary key,
+ categoryid varchar(48) not null,
+ ancestorid varchar(40),
+ relation varchar(20) not null
+);
+create index weblogcategoryassoc_categoryid_index on weblogcategoryassoc( categoryid );
+create index weblogcategoryassoc_ancestorid_index on weblogcategoryassoc( ancestorid );
+create index weblogcategoryassoc_relation_index on weblogcategoryassoc( relation );
+
+create table folderassoc (
+ id varchar(48) not null primary key,
+ folderid varchar(48) not null,
+ ancestorid varchar(40),
+ relation varchar(20) not null
+);
+create index folderassoc_folderid_index on folderassoc( folderid );
+create index folderassoc_ancestorid_index on folderassoc( ancestorid );
+
+-- Configuration options for Roller, should only ever be one row
+create table rollerconfig (
+ id varchar(48) not null primary key,
+ sitedescription varchar(255) null,
+ sitename varchar(255) null,
+ emailaddress varchar(255) null,
+ absoluteurl varchar(255) null,
+ adminusers varchar(255) null,
+ encryptpasswords bit default 0 not null,
+ algorithm varchar(10) null,
+ newuserallowed bit default 0 not null,
+ editorpages varchar(255) null,
+ userthemes varchar(255) not null,
+ indexdir varchar(255) null,
+ memdebug bit default 0 not null,
+ autoformatcomments bit default 0 not null,
+ escapecommenthtml bit default 1 not null,
+ emailcomments bit default 0 not null,
+ enableaggregator bit default 0 not null,
+ enablelinkback bit default 0 not null,
+ rsscachetime integer default 3000 not null,
+ rssusecache bit default 1 not null,
+ uploadallow varchar(255) null,
+ uploadforbid varchar(255) null,
+ uploadenabled bit default 1 not null,
+ uploaddir varchar(255) not null,
+ uploadpath varchar(255) not null,
+ uploadmaxdirmb decimal(5,2) default 4.0 not null,
+ uploadmaxfilemb decimal(5,2) default 1.5 not null,
+ dbversion varchar(10) null
+);
+alter table rollerconfig add column refspamwords longvarchar;
+update rollerconfig set refspamwords='';
+
+-- new usercookie table for remember me feature
+create table usercookie (
+ id varchar(48) not null primary key,
+ username varchar(255) not null,
+ cookieid varchar(100) not null,
+ datecreated timestamp not null
+);
+create index usercookie_username_index on usercookie( username );
+create index usercookie_cookieid_index on usercookie( cookieid );
+
+create index rolleruser_userenabled_index on rolleruser( userenabled );
+
+create index referer_refurl_index on referer( refurl );
+create index referer_requrl_index on referer( requrl );
+create index referer_datestr_index on referer( datestr );
+create index referer_refpermalink_index on referer( refpermalink );
+create index referer_duplicate_index on referer( duplicate );
+
+create index webpage_name_index on webpage( name );
+create index webpage_link_index on webpage( link );
+
+create index website_isenabled_index on website( isenabled );
+
+
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/100-to-110-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/100-to-110-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/100-to-110-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/100-to-110-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,12 @@
+
+create table entryattribute (
+ id varchar(48) not null primary key,
+ entryid varchar(48) not null,
+ name varchar(255) not null,
+ value text not null
+);
+create index entryattribute_entryid_index on entryattribute( entryid );
+alter table entryattribute add constraint entryattribute_name_uq unique ( entryid, name );
+
+alter table entryattribute add constraint att_entryid_fk
+ foreign key ( entryid ) references weblogentry( id );
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/110-to-120-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/110-to-120-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/110-to-120-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/110-to-120-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,131 @@
+create table roller_properties (
+ name varchar(255) not null primary key,
+ value longvarchar
+);
+insert into roller_properties (name,value) values ('roller.database.version','120');
+
+create table rag_group_subscription (
+ id varchar(48) not null primary key,
+ group_id varchar(48) not null,
+ subscription_id varchar(48) not null
+);
+create index rag_group_subscription_gid on rag_group_subscription(group_id);
+create index rag_group_subscription_sid on rag_group_subscription(subscription_id);
+
+create table rag_config (
+ id varchar(48) not null primary key,
+ default_group_id varchar(48),
+ title varchar(255) not null,
+ description varchar(255),
+ site_url varchar(255),
+ output_dir varchar(255),
+ cache_dir varchar(255) not null,
+ template_dir varchar(255),
+ main_page varchar(255),
+ admin_name varchar(255),
+ admin_email varchar(255) not null,
+ group_page varchar(255),
+ proxy_host varchar(255),
+ proxy_port integer default -1
+);
+
+create table rag_group (
+ id varchar(48) not null primary key,
+ handle varchar(255) not null,
+ title varchar(255) not null,
+ description varchar(255),
+ cat_restriction longvarchar,
+ group_page varchar(255),
+ max_page_entries integer default 30,
+ max_feed_entries integer default 30
+);
+alter table rag_group add constraint rag_group_handle_uq unique ( handle );
+create index rag_group_handle on rag_group(handle);
+
+create table rag_subscription (
+ id varchar(48) not null primary key,
+ title varchar(255),
+ feed_url varchar(255) not null,
+ site_url varchar(255),
+ author varchar(255),
+ last_updated timestamp,
+ inbound_links integer default -1,
+ inbound_blogs integer default -1
+);
+alter table rag_subscription add constraint rag_feed_url_uq unique ( feed_url );
+create index rag_subscription_feed_url on rag_subscription(feed_url);
+
+create table rag_entry (
+ id varchar(48) not null primary key,
+ subscription_id varchar(48) not null,
+ handle varchar(255),
+ title varchar(255),
+ guid varchar(255),
+ permalink longvarchar not null,
+ author varchar(255),
+ content longvarchar,
+ categories longvarchar,
+ published timestamp not null,
+ updated timestamp
+);
+create index rag_entry_sid on rag_entry(subscription_id);
+
+
+-- Ping Feature Tables
+
+create table pingtarget (
+ id varchar(48) not null primary key,
+ name varchar(255) not null, -- short descriptive name of the ping target
+ pingurl varchar(255) not null, -- URL to receive the ping
+ websiteid varchar(48) null, -- if not null, this is a custom target defined by the associated website
+ condition integer default 0 not null, -- condition code
+ lastsuccess timestamp null -- last successful use
+);
+create index pingtarget_websiteid_index on pingtarget( websiteid );
+
+-- auto ping configurations
+create table autoping (
+ id varchar(48) not null primary key,
+ websiteid varchar(48) not null, -- fk reference to website for which this auto ping configuration applies
+ pingtargetid varchar(48) not null -- fk reference to the ping target to be pinged when the website changes
+);
+create index autoping_websiteid_index on autoping( websiteid );
+create index autoping_pingtargetid_index on autoping( pingtargetid );
+
+create table pingcategory (
+ id varchar(48) not null primary key,
+ autopingid varchar(48) not null, -- fk reference to auto ping configuration
+ categoryid varchar(48) not null -- fk reference to category
+);
+create index pingcategory_autopingid_index on pingcategory( autopingid );
+create index pingcategory_categoryid_index on pingcategory( categoryid );
+
+create table pingqueueentry (
+ id varchar(48) not null primary key,
+ entrytime timestamp not null, -- timestamp of original entry onto the ping queue
+ pingtargetid varchar(48) not null, -- weak fk reference to ping target (not constrained)
+ websiteid varchar(48) not null, -- weak fk reference to website originating the ping (not constrained)
+ attempts integer not null -- number of ping attempts that have been made for this entry
+);
+create index pingqueueentry_entrytime_index on pingqueueentry( entrytime );
+create index pingqueueentry_pingtargetid_index on pingqueueentry( pingtargetid );
+create index pingqueueentry_websiteid_index on pingqueueentry( websiteid );
+
+-- and Ping constraints
+
+alter table pingtarget add constraint pingtarget_websiteid_fk
+ foreign key (websiteid) references website(id);
+
+alter table autoping add constraint autoping_websiteid_fk
+ foreign key (websiteid) references website(id);
+
+alter table autoping add constraint autoping_pingtargetid_fk
+ foreign key (pingtargetid) references pingtarget(id);
+
+alter table pingcategory add constraint pingcategory_autopingid_fk
+ foreign key (autopingid) references autoping(id);
+
+alter table pingcategory add constraint pingcategory_categoryid_fk
+ foreign key (categoryid) references weblogcategory(id);
+
+
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/120-to-130-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/120-to-130-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/120-to-130-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/120-to-130-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,3 @@
+update roller_properties set value='130' where name='roller.database.version';
+update website set editortheme='custom';
+alter table website alter column defaultpageid drop not null;
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/130-to-200-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/130-to-200-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/130-to-200-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/hsqldb/130-to-200-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,301 @@
+
+-- User permissions within a website
+-- permission_mask: bitmask 000 limited, 001 author, 011 admin
+-- pending: pending user acceptance of invitation to join website
+create table roller_user_permissions (
+ id varchar(48) not null primary key,
+ website_id varchar(48) not null,
+ user_id varchar(48) not null,
+ permission_mask integer not null,
+ pending bit default 1 not null
+);
+
+-- Add new handle field to uniquely identify websites in URLs
+
+alter table website add column handle varchar(255);
+alter table website alter handle varchar(255) default '';
+update website set handle='';
+alter table website alter handle varchar(255) not null ;
+
+alter table website add column datecreated timestamp;
+alter table website alter datecreated timestamp default '20050101';
+update website set datecreated='20050101';
+alter table website alter datecreated timestamp not null;
+
+alter table website add column emailaddress varchar(255);
+alter table website alter emailaddress varchar(255) default '';
+update website set emailaddress='';
+alter table website alter handle varchar(255) not null;
+
+create index website_handle_index on website(handle);
+
+-- this constraint won't work for upgrades until the handle column is
+-- populated with data, otherwise all columns are '' which will not
+-- satisfy the 'unique' condition
+-- alter table website add constraint website_handle_uq unique (handle);
+
+-- Add userid to weblogentry so we can track original creator of entry
+alter table weblogentry add column userid varchar(48);
+alter table weblogentry alter userid varchar(48) default '';
+update weblogentry set userid='';
+alter table weblogentry alter userid varchar(48) not null;
+
+alter table weblogentry add column status varchar(20);
+alter table weblogentry alter status varchar(20) default '';
+update weblogentry set status='';
+alter table weblogentry alter status varchar(20) not null;
+
+create index we_status_idx on weblogentry(status);
+create index weblogentry_userid_index on weblogentry(userid);
+
+alter table rolleruser add column isenabled bit;
+alter table rolleruser alter isenabled boolean default true;
+update rolleruser set isenabled=true;
+alter table rolleruser alter isenabled boolean not null;
+
+alter table rolleruser add column locale varchar(50);
+alter table rolleruser alter locale varchar(50) default '';
+update rolleruser set locale='';
+alter table rolleruser alter locale varchar(50) not null;
+
+alter table rolleruser add column timezone varchar(50);
+alter table rolleruser alter timezone varchar(50) default '';
+update rolleruser set timezone='';
+alter table rolleruser alter timezone varchar(50) not null;
+
+create index user_isenabled_index on rolleruser( isenabled );
+
+-- -----------------------------------------------------
+
+-- Audit log records time and comment about change
+-- user_id: user that made change
+-- object_id: id of associated object, if any
+-- object_class: name of associated object class (e.g. WeblogEntryData)
+-- comment: description of change
+-- change_time: time that change was made
+create table roller_audit_log (
+ id varchar(48) not null primary key,
+ user_id varchar(48) not null,
+ object_id varchar(48),
+ object_class varchar(255),
+ comment_text varchar(255) not null,
+ change_time timestamp
+);
+
+
+-- -----------------------------------------------------
+
+-- make "pubtime" use NULL for default values. this allows us to leave
+-- the "pubtime" for an entry unset until the entry is actually published.
+--
+-- sadly this needs to be done in a specific manner for each db, so check
+-- the db_*.properties file for each db to see how it's done.
+;
+
+
+-- -----------------------------------------------------
+-- For ROL-754. MySQL 5.x introduced a new keyword "condition"
+-- which made the use of "condition" as a column name in the "pingtarget" table illegal.
+-- This renames the column to "conditioncode". There is a corresponding change in the
+-- Hibernate mapping metadata.
+
+-- Create the new column. If your database will not autopopulate new columns with default values, you may
+-- have to remove the "not null" clause here.
+alter table pingtarget add column conditioncode integer;
+alter table pingtarget alter conditioncode set default 0;
+update pingtarget set conditioncode=0;
+alter table pingtarget alter conditioncode set not null;
+
+-- Transfer old column data to the new column. This is not critical as currently it is not used, and
+-- later the data will be generated by usage in the ping processor.
+update pingtarget set conditioncode=condition;
+
+-- Drop the old column
+-- Don't do this until you're sure you don't need to back-off to Roller 1.2
+-- alter table pingtarget drop column condition;
+
+-- -----------------------------------------------------
+
+-- Removing all indexes, foreign key with long names to support DB2
+
+alter table website drop foreign key website_userid_fk;
+alter table userrole drop foreign key userrole_userid_fk;
+alter table webpage drop foreign key weblogpage_websiteid_fk;
+alter table weblogentry drop foreign key weblogentry_websiteid_fk;
+alter table weblogentry drop foreign key weblogentry_categoryid_fk;
+alter table weblogcategory drop foreign key weblogcategory_websiteid_fk;
+alter table comment drop foreign key comment_entryid_fk;
+alter table entryattribute drop foreign key att_entryid_fk;
+alter table referer drop foreign key referer_entryid_fk;
+alter table referer drop foreign key referer_websiteid_fk;
+alter table folder drop foreign key folder_websiteid_fk;
+alter table bookmark drop foreign key bookmark_folderid_fk;
+alter table newsfeed drop foreign key newsfeed_websiteid_fk;
+alter table pingtarget drop foreign key pingtarget_websiteid_fk;
+alter table autoping drop foreign key autoping_websiteid_fk;
+alter table autoping drop foreign key autoping_pingtargetid_fk;
+alter table pingcategory drop foreign key pingcategory_autopingid_fk;
+alter table pingcategory drop foreign key pingcategory_categoryid_fk;
+
+
+alter table userrole drop index userrole_userid_index;
+alter table userrole drop index userrole_username_index;
+alter table usercookie drop index usercookie_username_index;
+alter table usercookie drop index usercookie_cookieid_index;
+alter table webpage drop index webpage_name_index;
+alter table webpage drop index webpage_link_index;
+alter table webpage drop index webpage_id_index;
+alter table website drop index website_id_index;
+alter table website drop index website_userid_index;
+alter table website drop index website_isenabled_index;
+alter table folder drop index folder_websiteid_index;
+alter table folderassoc drop index folderassoc_folderid_index;
+alter table folderassoc drop index folderassoc_ancestorid_index;
+alter table folderassoc drop index folderassoc_relation_index;
+alter table bookmark drop index bookmark_folderid_index;
+alter table weblogcategory drop index weblogcategory_websiteid_index;
+alter table weblogcategoryassoc drop index weblogcategoryassoc_categoryid_index;
+alter table weblogcategoryassoc drop index weblogcategoryassoc_ancestorid_index;
+alter table weblogcategoryassoc drop index weblogcategoryassoc_relation_index;
+alter table weblogentry drop index weblogentry_websiteid_index;
+alter table weblogentry drop index weblogentry_categoryid_index;
+alter table weblogentry drop index weblogentry_pubtime_index;
+alter table weblogentry drop index weblogentry_pinnedtomain_index;
+alter table weblogentry drop index weblogentry_publishentry_index;
+alter table newsfeed drop index newsfeed_websiteid_index;
+alter table comment drop index comment_entryid_index;
+alter table pingtarget drop index pingtarget_websiteid_index;
+alter table autoping drop index autoping_websiteid_index;
+alter table autoping drop index autoping_pingtargetid_index;
+alter table pingcategory drop index pingcategory_autopingid_index;
+alter table pingcategory drop index pingcategory_categoryid_index;
+alter table pingqueueentry drop index pingqueueentry_entrytime_index;
+alter table pingqueueentry drop index pingqueueentry_pingtargetid_index;
+alter table pingqueueentry drop index pingqueueentry_websiteid_index;
+alter table referer drop index referer_websiteid_index;
+alter table referer drop index referer_entryid_index;
+alter table referer drop index referer_refurl_index;
+alter table referer drop index referer_requrl_index;
+alter table referer drop index referer_datestr_index;
+alter table referer drop index referer_refpermalink_index;
+alter table referer drop index referer_duplicate_index;
+alter table entryattribute drop index entryattribute_entryid_index;
+alter table rag_group_subscription drop index rag_group_subscription_gid;
+alter table rag_group_subscription drop index rag_group_subscription_sid;
+alter table rag_group drop index rag_group_handle;
+alter table rag_subscription drop index rag_subscription_feed_url;
+alter table rag_entry drop index rag_entry_sid;
+
+
+-- Adding all new indexes with short names
+
+create index ur_userid_idx on userrole( userid );
+create index ur_username_idx on userrole( username );
+create index uc_username_idx on usercookie( username );
+create index uc_cookieid_idx on usercookie( cookieid );
+create index wp_name_idx on webpage( name );
+create index wp_link_idx on webpage( link );
+create index wp_id_idx on webpage( websiteid );
+create index ws_userid_idx on website(userid);
+create index ws_isenabled_idx on website(isenabled);
+create index fo_websiteid_idx on folder( websiteid );
+create index fa_folderid_idx on folderassoc( folderid );
+create index fa_ancestorid_idx on folderassoc( ancestorid );
+create index fa_relation_idx on folderassoc( relation );
+create index bm_folderid_idx on bookmark( folderid );
+create index wc_websiteid_idx on weblogcategory( websiteid );
+create index wca_categoryid_idx on weblogcategoryassoc( categoryid );
+create index wca_ancestorid_idx on weblogcategoryassoc( ancestorid );
+create index wca_relation_idx on weblogcategoryassoc( relation );
+create index we_websiteid_idx on weblogentry( websiteid );
+create index we_categoryid_idx on weblogentry( categoryid );
+create index we_pubtime_idx on weblogentry( pubtime,publishentry,websiteid );
+create index we_pinnedtom_idx on weblogentry(pinnedtomain);
+create index we_pubentry_idx on weblogentry(publishentry);
+create index we_userid_idx on weblogentry(userid);
+create index nf_websiteid_idx on newsfeed( websiteid );
+create index co_entryid_idx on comment( entryid );
+create index pt_websiteid_idx on pingtarget( websiteid );
+create index ap_websiteid_idx on autoping( websiteid );
+create index ap_pingtid_idx on autoping( pingtargetid );
+create index pc_autopingid_idx on pingcategory( autopingid );
+create index pc_categoryid_idx on pingcategory( categoryid );
+create index pqe_entrytime_idx on pingqueueentry( entrytime );
+create index pqe_pingtid_idx on pingqueueentry( pingtargetid );
+create index pqe_websiteid_idx on pingqueueentry( websiteid );
+create index ref_websiteid_idx on referer( websiteid );
+create index ref_entryid_idx on referer( entryid );
+create index ref_refurl_idx on referer( refurl );
+create index ref_requrl_idx on referer( requrl );
+create index ref_datestr_idx on referer( datestr );
+create index ref_refpermlnk_idx on referer( refpermalink );
+create index ref_duplicate_idx on referer( duplicate );
+create index ea_entryid_idx on entryattribute( entryid );
+create index raggs_gid_idx on rag_group_subscription(group_id);
+create index raggs_sid_idx on rag_group_subscription(subscription_id);
+create index rage_sid_idx on rag_entry(subscription_id);
+
+-- Now add the foreign key relationships
+
+-- user, role and website
+alter table website add constraint ws_userid_fk
+ foreign key ( userid ) references rolleruser ( id ) ;
+
+alter table userrole add constraint ur_userid_fk
+ foreign key ( userid ) references rolleruser( id ) ;
+
+-- page, entry, category, comment
+alter table webpage add constraint wp_websiteid_fk
+ foreign key ( websiteid ) references website( id ) ;
+
+alter table weblogentry add constraint we_websiteid_fk
+ foreign key ( websiteid ) references website( id ) ;
+
+alter table weblogentry add constraint wc_categoryid_fk
+ foreign key ( categoryid ) references weblogcategory( id ) ;
+
+alter table weblogcategory add constraint wc_websiteid_fk
+ foreign key ( websiteid ) references website( id ) ;
+
+alter table comment add constraint co_entryid_fk
+ foreign key ( entryid ) references weblogentry( id ) ;
+
+alter table entryattribute add constraint att_entryid_fk
+ foreign key ( entryid ) references weblogentry( id ) ;
+
+-- referer
+alter table referer add constraint ref_entryid_fk
+ foreign key ( entryid ) references weblogentry( id ) ;
+
+alter table referer add constraint ref_websiteid_fk
+ foreign key ( websiteid ) references website( id ) ;
+
+-- folder and bookmark
+alter table folder add constraint fo_websiteid_fk
+ foreign key ( websiteid ) references website( id ) ;
+
+alter table bookmark add constraint bm_folderid_fk
+ foreign key ( folderid ) references folder( id ) ;
+
+-- newsfeed
+alter table newsfeed add constraint nf_websiteid_fk
+ foreign key ( websiteid ) references website( id ) ;
+
+-- pingtarget, autoping, pingcategory
+alter table pingtarget add constraint pt_websiteid_fk
+ foreign key (websiteid) references website(id) ;
+
+alter table autoping add constraint ap_websiteid_fk
+ foreign key (websiteid) references website(id) ;
+
+alter table autoping add constraint ap_pingtargetid_fk
+ foreign key (pingtargetid) references pingtarget(id) ;
+
+alter table pingcategory add constraint pc_autopingid_fk
+ foreign key (autopingid) references autoping(id) ;
+
+alter table pingcategory add constraint pc_categoryid_fk
+ foreign key (categoryid) references weblogcategory(id) ;
+
+-- Oracle compatability DDL
+alter table comment rename to roller_comment;
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/094-to-095-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/094-to-095-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/094-to-095-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/094-to-095-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,134 @@
+-- we will drop & recreate every table so
+-- that the new named constraints can be used
+-- for HSQLDB replace text datatypes with longvarchar
+-- for Postresql replace bit datatype with boolean
+
+CREATE TABLE tempuser AS SELECT * FROM user;
+DROP TABLE user;
+create table rolleruser (
+ id varchar(255) not null primary key,
+ username varchar(255) not null,
+ password varchar(255) not null,
+ fullname varchar(255) not null,
+ emailaddress varchar(255) not null
+);
+INSERT INTO rolleruser SELECT * FROM tempuser;
+
+CREATE TABLE temprole AS SELECT * FROM role;
+DROP TABLE role;
+create table role (
+ id varchar(255) not null primary key,
+ role varchar(255) not null,
+ username varchar(255) not null
+);
+INSERT INTO role SELECT * FROM temprole;
+
+CREATE TABLE temppage AS SELECT * FROM page;
+DROP TABLE page;
+create table page (
+ id varchar(255) not null primary key,
+ name varchar(255) not null,
+ description varchar(255) null,
+ link varchar(255) null,
+ websiteid varchar(255) not null,
+ template text not null,
+ updatetime timestamp not null
+);
+INSERT INTO page
+ (id, name, description, websiteid, template, updatetime)
+ SELECT id, name, description, websiteid, template, updatetime FROM temppage;
+
+CREATE TABLE tempwebsite AS SELECT * FROM website;
+DROP TABLE website;
+create table website (
+ id varchar(255) not null primary key,
+ name varchar(255) not null,
+ description varchar(255) not null,
+ userid varchar(255) not null,
+ defaultpageid varchar(255) default 0 not null,
+ weblogdayid varchar(255) not null,
+ enablebloggerapi bit default 0 not null,
+ bloggercatid varchar(255) null
+);
+INSERT INTO website SELECT * FROM tempwebsite;
+
+CREATE TABLE tempfolder AS SELECT * FROM folder;
+DROP TABLE folder;
+create table folder (
+ id varchar(255) not null primary key,
+ name varchar(255) not null,
+ description varchar(255) null,
+ parentid varchar(255) null,
+ websiteid varchar(255) not null
+);
+INSERT INTO folder SELECT id,name,description,parentid,websiteid
+ FROM tempfolder;
+UPDATE folder SET parentid = NULL WHERE name='root' AND parentid=0;
+
+CREATE TABLE tempbookmark AS SELECT * FROM bookmark;
+DROP TABLE bookmark;
+create table bookmark (
+ id varchar(255) not null primary key,
+ folderid varchar(255) not null,
+ name varchar(255) not null,
+ description varchar(255) null,
+ url varchar(255) not null,
+ priority integer default 100 not null,
+ image varchar(255) null
+);
+INSERT INTO bookmark SELECT * FROM tempbookmark;
+ALTER TABLE bookmark ADD COLUMN (weight integer default 0 not null);
+ALTER TABLE bookmark ADD COLUMN (feedurl varchar(255) null);
+
+CREATE TABLE tempweblogcategory AS SELECT * FROM weblogcategory;
+DROP TABLE weblogcategory;
+create table weblogcategory (
+ id varchar(255) not null primary key,
+ name varchar(255) not null,
+ description varchar(255) null,
+ websiteid varchar(255) not null,
+ image varchar(255) null
+);
+INSERT INTO weblogcategory SELECT * FROM tempweblogcategory;
+
+CREATE TABLE tempweblogentry AS SELECT * FROM weblogentry;
+DROP TABLE weblogentry;
+create table weblogentry (
+ id varchar(255) not null primary key,
+ anchor varchar(255) not null,
+ title varchar(255) not null,
+ text text not null,
+ pubtime timestamp not null,
+ updatetime timestamp not null,
+ websiteid varchar(255) not null,
+ categoryid varchar(255) not null
+);
+INSERT INTO weblogentry SELECT * FROM tempweblogentry;
+
+CREATE TABLE tempnewsfeed AS SELECT * FROM newsfeed;
+DROP TABLE newsfeed;
+create table newsfeed (
+ id varchar(255) not null primary key,
+ name varchar(255) not null,
+ description varchar(255) not null,
+ link varchar(255) not null,
+ websiteid varchar(255) not null
+);
+INSERT INTO newsfeed SELECT * FROM tempnewsfeed;
+
+-- Now add the constraints
+alter table rolleruser add constraint rolleruser_username_uq unique ( username );
+
+alter table website add constraint website_userid_fk foreign key ( userid ) references rolleruser ( id );
+
+alter table folder add constraint folder_websiteid_fk foreign key ( websiteid ) references website( id );
+alter table folder add constraint folder_parentid_fk foreign key ( parentid ) references folder( id );
+
+alter table bookmark add constraint bookmark_folderid_fk foreign key ( folderid ) references folder( id );
+
+alter table weblogcategory add constraint weblogcategory_websiteid_fk foreign key ( websiteid ) references website( id );
+
+alter table weblogentry add constraint weblogentry_websiteid_fk foreign key ( websiteid ) references website( id );
+alter table weblogentry add constraint weblogentry_categoryid_fk foreign key ( categoryid ) references weblogcategory( id );
+
+alter table newsfeed add constraint newsfeed_websiteid_fk foreign key ( websiteid ) references website( id );
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/095-to-096-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/095-to-096-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/095-to-096-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/095-to-096-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,21 @@
+
+ALTER TABLE weblogentry ADD COLUMN (publishentry @BOOLEAN_SQL_TYPE2@ not null);
+
+ALTER TABLE website ADD COLUMN (editorpage varchar(255) not null);
+UPDATE website SET editorpage = 'editor-ekit.jsp';
+
+CREATE TABLE temprole AS SELECT * FROM role;
+DROP TABLE role;
+create table role (
+ id varchar(255) not null primary key,
+ role varchar(255) not null,
+ username varchar(255) not null,
+ userid varchar(255) not null
+);
+INSERT INTO role
+ (id, role, username, userid)
+ SELECT temprole.id, role, temprole.username, rolleruser.id
+ FROM temprole, rolleruser
+ WHERE temprole.username = rolleruser.username;
+ALTER TABLE role ADD CONSTRAINT role_userid_fk
+ foreign key ( userid ) references rolleruser( id );
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/096-to-097-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/096-to-097-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/096-to-097-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/096-to-097-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,54 @@
+
+create table if not exists comment (
+ id varchar(255) not null primary key,
+ entryid varchar(255) not null,
+ name varchar(255) null,
+ email varchar(255) null,
+ url varchar(255) null,
+ content text null,
+ posttime timestamp not null
+);
+create index comment_entryid_index on comment( entryid );
+
+
+-- Referer tracks URLs that refer to websites and entries
+create table referer (
+ id varchar(48) not null primary key,
+ websiteid varchar(48) not null,
+ entryid varchar(48),
+ datestr varchar(10),
+ refurl varchar(255) not null,
+ refpermalink varchar(255),
+ reftime timestamp,
+ requrl varchar(255),
+ title varchar(255),
+ excerpt text null,
+ dayhits integer default 0 not null,
+ totalhits integer default 0 not null,
+ visible bit default 0 not null,
+ duplicate bit default 0 not null
+);
+create index referer_websiteid_index on referer( websiteid );
+create index referer_entryid_index on referer( entryid );
+
+
+alter table website add column (allowcomments bit default 1 not null);
+
+alter table website add column (ignorewords text null);
+
+alter table comment add constraint comment_entryid_fk
+ foreign key ( entryid ) references weblogentry( id );
+
+alter table folder add constraint folder_websiteid_fk
+ foreign key ( websiteid ) references website( id );
+
+alter table folder add constraint folder_entryid_fk
+ foreign key ( entryid ) references weblogentry( id );
+
+alter table referer add constraint referer_websiteid_fk
+ foreign key ( websiteid ) references website( id );
+
+alter table referer add constraint referer_entryid_fk
+ foreign key ( entryid ) references weblogentry( id );
+
+
\ No newline at end of file
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/097-to-098-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/097-to-098-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/097-to-098-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/097-to-098-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,80 @@
+-- Eliminate use of reserved keywords
+
+-- role -> userrole
+CREATE TABLE temprole AS SELECT * FROM role;
+DROP TABLE role;
+CREATE table userrole (
+ id varchar(48) not null primary key,
+ rolename varchar(255) not null,
+ username varchar(255) not null,
+ userid varchar(48) not null
+);
+INSERT INTO userrole
+ (id, rolename, username, userid)
+ SELECT id, role, username, userid
+ FROM temprole;
+ALTER table userrole add constraint userrole_userid_fk
+ foreign key ( userid ) references rolleruser( id );
+create index userrole_userid_index on userrole( userid );
+
+
+-- password -> passphrase
+CREATE TABLE tempuser AS SELECT * FROM rolleruser;
+DROP TABLE rolleruser;
+CREATE table rolleruser (
+ id varchar(48) not null primary key,
+ username varchar(255) not null,
+ passphrase varchar(255) not null,
+ fullname varchar(255) not null,
+ emailaddress varchar(255) not null,
+ datecreated timestamp not null,
+ userenabled bit default 1 not null
+);
+-- Populate the new table, filling datecreated using least pubtime of any of the user's weblog entries.
+INSERT INTO rolleruser
+ (id, username, passphrase, fullname, emailaddress, datecreated)
+ SELECT t.id, t.username, t.password, t.fullname, t.emailaddress, MIN(e.pubtime)
+ FROM tempuser t, website w, weblogentry e
+ WHERE t.id = w.userid and w.id = e.websiteid
+ GROUP BY t.id;
+
+ALTER table rolleruser add constraint rolleruser_username_uq unique ( username );
+
+
+-- page -> webpage
+CREATE TABLE temppage AS SELECT * FROM page;
+DROP TABLE page;
+create table webpage AS SELECT * FROM temppage;
+
+
+-- Drop bad indexes: some were poorly named, others just wrong
+-- these may or may not exist
+-- alter table website drop index userid_index;
+-- alter table folder drop index webisteid_index;
+-- alter table folder drop index parentid_index;
+-- alter table bookmark drop index folderid_index;
+-- alter table weblogcategory drop index websiteid_index;
+-- alter table weblogentry drop index websiteid_index;
+-- alter table weblogentry drop index categoryid_index;
+-- alter table newsfeed drop index websiteid_index;
+-- alter table comment drop index entryid_index;
+-- alter table referer drop index websiteid_index;
+-- alter table referer drop index entryid_index;
+
+-- Add good indexes to replace the bad ones
+create index website_userid_index on website( userid );
+create index folder_websiteid_index on folder( websiteid );
+create index folder_parentid_index on folder( parentid );
+create index bookmark_folderid_index on bookmark( folderid );
+create index weblogcategory_websiteid_index on weblogcategory( websiteid );
+create index weblogentry_websiteid_index on weblogentry( websiteid );
+create index weblogentry_categoryid_index on weblogentry( categoryid );
+create index newsfeed_websiteid_index on newsfeed( websiteid );
+create index comment_entryid_index on comment( entryid );
+create index referer_websiteid_index on referer( websiteid );
+create index referer_entryid_index on referer( entryid );
+
+-- Only use these when you are certain of the upgrade
+-- drop table temprole;
+-- drop table tempuser;
+-- drop table temppage;
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/098-to-099-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/098-to-099-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/098-to-099-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/098-to-099-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,5 @@
+-- add new attribute to WeblogEntry
+alter table weblogentry add column (autoformatentry BOOLEAN_SQL_TYPE not null);
+
+-- add new attribute to Website
+alter table website add column (autoformatdefault BOOLEAN_SQL_TYPE not null);
\ No newline at end of file
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/098-to-100-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/098-to-100-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/098-to-100-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/098-to-100-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,107 @@
+-- add new attribute to Comment
+alter table comment add column notify bit;
+alter table comment add column spam bit;
+alter table comment add column remotehost varchar(128);
+update comment set spam=0, notify=0, posttime=posttime;
+
+-- add new attribute to WeblogEntry
+alter table weblogentry add column link varchar(255);
+alter table weblogentry add column plugins varchar(255);
+alter table weblogentry add column allowcomments bit;
+alter table weblogentry add column commentdays integer;
+alter table weblogentry add column rightToLeft bit;
+alter table weblogentry add column pinnedtomain bit;
+update weblogentry set pubtime=pubtime, updatetime=updatetime, allowcomments=1, pinnedtomain=0;
+
+-- add new attributes to Website
+alter table website add column editortheme varchar(255);
+alter table website add column locale varchar(20);
+alter table website add column timezone varchar(50);
+alter table website add column defaultcatid varchar(48);
+alter table website add column defaultplugins varchar(255);
+alter table website add column emailcomments bit;
+alter table website add column emailfromaddress varchar(255);
+alter table website add column isenabled bit;
+update website set emailcomments=0, isenabled=1, locale='en', timezone='America/New_York';
+
+-- reset possibly bad bloggercategoryid settings, repairIfNeeded will fix them
+update website set bloggercatid=null;
+
+-- weblog categories are now hierarchical
+create table weblogcategoryassoc (
+ id varchar(48) not null primary key,
+ categoryid varchar(48) not null,
+ ancestorid varchar(40),
+ relation varchar(20) not null
+);
+create index weblogcategoryassoc_categoryid_index on weblogcategoryassoc( categoryid );
+create index weblogcategoryassoc_ancestorid_index on weblogcategoryassoc( ancestorid );
+create index weblogcategoryassoc_relation_index on weblogcategoryassoc( relation );
+
+create table folderassoc (
+ id varchar(48) not null primary key,
+ folderid varchar(48) not null,
+ ancestorid varchar(40),
+ relation varchar(20) not null
+);
+create index folderassoc_folderid_index on folderassoc( folderid );
+create index folderassoc_ancestorid_index on folderassoc( ancestorid );
+
+-- Configuration options for Roller, should only ever be one row
+create table rollerconfig (
+ id varchar(48) not null primary key,
+ sitedescription varchar(255) null,
+ sitename varchar(255) null,
+ emailaddress varchar(255) null,
+ absoluteurl varchar(255) null,
+ adminusers varchar(255) null,
+ encryptpasswords bit default 0 not null,
+ algorithm varchar(10) null,
+ newuserallowed bit default 0 not null,
+ editorpages varchar(255) null,
+ userthemes varchar(255) not null,
+ indexdir varchar(255) null,
+ memdebug bit default 0 not null,
+ autoformatcomments bit default 0 not null,
+ escapecommenthtml bit default 1 not null,
+ emailcomments bit default 0 not null,
+ enableaggregator bit default 0 not null,
+ enablelinkback bit default 0 not null,
+ rsscachetime integer default 3000 not null,
+ rssusecache bit default 1 not null,
+ uploadallow varchar(255) null,
+ uploadforbid varchar(255) null,
+ uploadenabled bit default 1 not null,
+ uploaddir varchar(255) not null,
+ uploadpath varchar(255) not null,
+ uploadmaxdirmb decimal(5,2) default 4.0 not null,
+ uploadmaxfilemb decimal(5,2) default 1.5 not null,
+ dbversion varchar(10) null
+);
+alter table rollerconfig add column refspamwords text;
+update rollerconfig set refspamwords='';
+
+-- new usercookie table for remember me feature
+create table usercookie (
+ id varchar(48) not null primary key,
+ username varchar(255) not null,
+ cookieid varchar(100) not null,
+ datecreated timestamp not null
+);
+create index usercookie_username_index on usercookie( username );
+create index usercookie_cookieid_index on usercookie( cookieid );
+
+create index rolleruser_userenabled_index on rolleruser( userenabled );
+
+create index referer_refurl_index on referer( refurl(40) );
+create index referer_requrl_index on referer( requrl(40) );
+create index referer_datestr_index on referer( datestr );
+create index referer_refpermalink_index on referer( refpermalink(40) );
+create index referer_duplicate_index on referer( duplicate );
+
+create index webpage_name_index on webpage( name(40) );
+create index webpage_link_index on webpage( link(40) );
+
+create index website_isenabled_index on website( isenabled );
+
+
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/100-to-110-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/100-to-110-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/100-to-110-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/100-to-110-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,12 @@
+
+create table entryattribute (
+ id varchar(48) not null primary key,
+ entryid varchar(48) not null,
+ name varchar(255) not null,
+ value text not null
+);
+create index entryattribute_entryid_index on entryattribute( entryid );
+alter table entryattribute add constraint entryattribute_name_uq unique ( entryid, name );
+
+alter table entryattribute add constraint att_entryid_fk
+ foreign key ( entryid ) references weblogentry( id );
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/110-to-120-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/110-to-120-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/110-to-120-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/110-to-120-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,131 @@
+create table roller_properties (
+ name varchar(255) not null primary key,
+ value text
+);
+insert into roller_properties (name,value) values ('roller.database.version','120');
+
+create table rag_group_subscription (
+ id varchar(48) not null primary key,
+ group_id varchar(48) not null,
+ subscription_id varchar(48) not null
+);
+create index rag_group_subscription_gid on rag_group_subscription(group_id(40));
+create index rag_group_subscription_sid on rag_group_subscription(subscription_id(40));
+
+create table rag_config (
+ id varchar(48) not null primary key,
+ default_group_id varchar(48),
+ title varchar(255) not null,
+ description varchar(255),
+ site_url varchar(255),
+ output_dir varchar(255),
+ cache_dir varchar(255) not null,
+ template_dir varchar(255),
+ main_page varchar(255),
+ admin_name varchar(255),
+ admin_email varchar(255) not null,
+ group_page varchar(255),
+ proxy_host varchar(255),
+ proxy_port integer default -1
+);
+
+create table rag_group (
+ id varchar(48) not null primary key,
+ handle varchar(255) not null,
+ title varchar(255) not null,
+ description varchar(255),
+ cat_restriction text,
+ group_page varchar(255),
+ max_page_entries integer default 30,
+ max_feed_entries integer default 30
+);
+alter table rag_group add constraint rag_group_handle_uq unique ( handle(40) );
+create index rag_group_handle on rag_group(handle(40));
+
+create table rag_subscription (
+ id varchar(48) not null primary key,
+ title varchar(255),
+ feed_url varchar(255) not null,
+ site_url varchar(255),
+ author varchar(255),
+ last_updated timestamp,
+ inbound_links integer default -1,
+ inbound_blogs integer default -1
+);
+alter table rag_subscription add constraint rag_feed_url_uq unique ( feed_url(100) );
+create index rag_subscription_feed_url on rag_subscription(feed_url(40));
+
+create table rag_entry (
+ id varchar(48) not null primary key,
+ subscription_id varchar(48) not null,
+ handle varchar(255),
+ title varchar(255),
+ guid varchar(255),
+ permalink text not null,
+ author varchar(255),
+ content text,
+ categories text,
+ published timestamp not null,
+ updated timestamp
+);
+create index rag_entry_sid on rag_entry(subscription_id(40));
+
+
+-- Ping Feature Tables
+
+create table pingtarget (
+ id varchar(48) not null primary key,
+ name varchar(255) not null, -- short descriptive name of the ping target
+ pingurl varchar(255) not null, -- URL to receive the ping
+ websiteid varchar(48) null, -- if not null, this is a custom target defined by the associated website
+ condition integer default 0 not null, -- condition code
+ lastsuccess timestamp null -- last successful use
+);
+create index pingtarget_websiteid_index on pingtarget( websiteid );
+
+-- auto ping configurations
+create table autoping (
+ id varchar(48) not null primary key,
+ websiteid varchar(48) not null, -- fk reference to website for which this auto ping configuration applies
+ pingtargetid varchar(48) not null -- fk reference to the ping target to be pinged when the website changes
+);
+create index autoping_websiteid_index on autoping( websiteid );
+create index autoping_pingtargetid_index on autoping( pingtargetid );
+
+create table pingcategory (
+ id varchar(48) not null primary key,
+ autopingid varchar(48) not null, -- fk reference to auto ping configuration
+ categoryid varchar(48) not null -- fk reference to category
+);
+create index pingcategory_autopingid_index on pingcategory( autopingid );
+create index pingcategory_categoryid_index on pingcategory( categoryid );
+
+create table pingqueueentry (
+ id varchar(48) not null primary key,
+ entrytime timestamp not null, -- timestamp of original entry onto the ping queue
+ pingtargetid varchar(48) not null, -- weak fk reference to ping target (not constrained)
+ websiteid varchar(48) not null, -- weak fk reference to website originating the ping (not constrained)
+ attempts integer not null -- number of ping attempts that have been made for this entry
+);
+create index pingqueueentry_entrytime_index on pingqueueentry( entrytime );
+create index pingqueueentry_pingtargetid_index on pingqueueentry( pingtargetid );
+create index pingqueueentry_websiteid_index on pingqueueentry( websiteid );
+
+-- and Ping constraints
+
+alter table pingtarget add constraint pingtarget_websiteid_fk
+ foreign key (websiteid) references website(id);
+
+alter table autoping add constraint autoping_websiteid_fk
+ foreign key (websiteid) references website(id);
+
+alter table autoping add constraint autoping_pingtargetid_fk
+ foreign key (pingtargetid) references pingtarget(id);
+
+alter table pingcategory add constraint pingcategory_autopingid_fk
+ foreign key (autopingid) references autoping(id);
+
+alter table pingcategory add constraint pingcategory_categoryid_fk
+ foreign key (categoryid) references weblogcategory(id);
+
+
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/120-to-130-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/120-to-130-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/120-to-130-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/120-to-130-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,3 @@
+update roller_properties set value='130' where name='roller.database.version';
+update website set editortheme='custom';
+alter table website modify defaultpageid varchar(48) DEFAULT '';
Added: incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/130-to-200-migration.sql
URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/130-to-200-migration.sql?rev=358311&view=auto
==============================================================================
--- incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/130-to-200-migration.sql (added)
+++ incubator/roller/trunk/web/WEB-INF/dbscripts/mysql/130-to-200-migration.sql Wed Dec 21 08:00:58 2005
@@ -0,0 +1,301 @@
+
+-- User permissions within a website
+-- permission_mask: bitmask 000 limited, 001 author, 011 admin
+-- pending: pending user acceptance of invitation to join website
+create table roller_user_permissions (
+ id varchar(48) not null primary key,
+ website_id varchar(48) not null,
+ user_id varchar(48) not null,
+ permission_mask integer not null,
+ pending bit default 1 not null
+);
+
+-- Add new handle field to uniquely identify websites in URLs
+
+alter table website add column handle varchar(255);
+alter table website modify handle varchar(255) default '';
+update website set handle='';
+alter table website modify handle varchar(255) not null ;
+
+alter table website add column datecreated timestamp;
+alter table website modify datecreated timestamp default '20050101';
+update website set datecreated='20050101';
+alter table website modify datecreated timestamp not null;
+
+alter table website add column emailaddress varchar(255);
+alter table website modify emailaddress varchar(255) default '';
+update website set emailaddress='';
+alter table website modify handle varchar(255) not null;
+
+create index website_handle_index on website(handle);
+
+-- this constraint won't work for upgrades until the handle column is
+-- populated with data, otherwise all columns are '' which will not
+-- satisfy the 'unique' condition
+-- alter table website add constraint website_handle_uq unique (handle(40));
+
+-- Add userid to weblogentry so we can track original creator of entry
+alter table weblogentry add column userid varchar(48);
+alter table weblogentry modify userid varchar(48) default '';
+update weblogentry set userid='';
+alter table weblogentry modify userid varchar(48) not null;
+
+alter table weblogentry add column status varchar(20);
+alter table weblogentry modify status varchar(20) default '';
+update weblogentry set status='';
+alter table weblogentry modify status varchar(20) not null;
+
+create index we_status_idx on weblogentry(status);
+create index weblogentry_userid_index on weblogentry(userid);
+
+alter table rolleruser add column isenabled bit;
+alter table rolleruser modify isenabled bit default 1;
+update rolleruser set isenabled=true;
+alter table rolleruser modify isenabled bit not null;
+
+alter table rolleruser add column locale varchar(50);
+alter table rolleruser modify locale varchar(50) default '';
+update rolleruser set locale='';
+alter table rolleruser modify locale varchar(50) not null;
+
+alter table rolleruser add column timezone varchar(50);
+alter table rolleruser modify timezone varchar(50) default '';
+update rolleruser set timezone='';
+alter table rolleruser modify timezone varchar(50) not null;
+
+create index user_isenabled_index on rolleruser( isenabled );
+
+-- -----------------------------------------------------
+
+-- Audit log records time and comment about change
+-- user_id: user that made change
+-- object_id: id of associated object, if any
+-- object_class: name of associated object class (e.g. WeblogEntryData)
+-- comment: description of change
+-- change_time: time that change was made
+create table roller_audit_log (
+ id varchar(48) not null primary key,
+ user_id varchar(48) not null,
+ object_id varchar(48),
+ object_class varchar(255),
+ comment_text varchar(255) not null,
+ change_time timestamp
+);
+
+
+-- -----------------------------------------------------
+
+-- make "pubtime" use NULL for default values. this allows us to leave
+-- the "pubtime" for an entry unset until the entry is actually published.
+--
+-- sadly this needs to be done in a specific manner for each db, so check
+-- the db_*.properties file for each db to see how it's done.
+alter table weblogentry modify pubtime DATETIME NULL;
+
+
+-- -----------------------------------------------------
+-- For ROL-754. MySQL 5.x introduced a new keyword "condition"
+-- which made the use of "condition" as a column name in the "pingtarget" table illegal.
+-- This renames the column to "conditioncode". There is a corresponding change in the
+-- Hibernate mapping metadata.
+
+-- Create the new column. If your database will not autopopulate new columns with default values, you may
+-- have to remove the "not null" clause here.
+alter table pingtarget add column conditioncode integer;
+alter table pingtarget modify conditioncode integer default 0;
+update pingtarget set conditioncode=0;
+alter table pingtarget modify conditioncode integer not null;
+
+-- Transfer old column data to the new column. This is not critical as currently it is not used, and
+-- later the data will be generated by usage in the ping processor.
+update pingtarget set conditioncode=condition;
+
+-- Drop the old column
+-- Don't do this until you're sure you don't need to back-off to Roller 1.2
+-- alter table pingtarget drop column condition;
+
+-- -----------------------------------------------------
+
+-- Removing all indexes, foreign key with long names to support DB2
+
+alter table website drop foreign key website_userid_fk;
+alter table userrole drop foreign key userrole_userid_fk;
+alter table webpage drop foreign key weblogpage_websiteid_fk;
+alter table weblogentry drop foreign key weblogentry_websiteid_fk;
+alter table weblogentry drop foreign key weblogentry_categoryid_fk;
+alter table weblogcategory drop foreign key weblogcategory_websiteid_fk;
+alter table comment drop foreign key comment_entryid_fk;
+alter table entryattribute drop foreign key att_entryid_fk;
+alter table referer drop foreign key referer_entryid_fk;
+alter table referer drop foreign key referer_websiteid_fk;
+alter table folder drop foreign key folder_websiteid_fk;
+alter table bookmark drop foreign key bookmark_folderid_fk;
+alter table newsfeed drop foreign key newsfeed_websiteid_fk;
+alter table pingtarget drop foreign key pingtarget_websiteid_fk;
+alter table autoping drop foreign key autoping_websiteid_fk;
+alter table autoping drop foreign key autoping_pingtargetid_fk;
+alter table pingcategory drop foreign key pingcategory_autopingid_fk;
+alter table pingcategory drop foreign key pingcategory_categoryid_fk;
+
+
+alter table userrole drop index userrole_userid_index;
+alter table userrole drop index userrole_username_index;
+alter table usercookie drop index usercookie_username_index;
+alter table usercookie drop index usercookie_cookieid_index;
+alter table webpage drop index webpage_name_index;
+alter table webpage drop index webpage_link_index;
+alter table webpage drop index webpage_id_index;
+alter table website drop index website_id_index;
+alter table website drop index website_userid_index;
+alter table website drop index website_isenabled_index;
+alter table folder drop index folder_websiteid_index;
+alter table folderassoc drop index folderassoc_folderid_index;
+alter table folderassoc drop index folderassoc_ancestorid_index;
+alter table folderassoc drop index folderassoc_relation_index;
+alter table bookmark drop index bookmark_folderid_index;
+alter table weblogcategory drop index weblogcategory_websiteid_index;
+alter table weblogcategoryassoc drop index weblogcategoryassoc_categoryid_index;
+alter table weblogcategoryassoc drop index weblogcategoryassoc_ancestorid_index;
+alter table weblogcategoryassoc drop index weblogcategoryassoc_relation_index;
+alter table weblogentry drop index weblogentry_websiteid_index;
+alter table weblogentry drop index weblogentry_categoryid_index;
+alter table weblogentry drop index weblogentry_pubtime_index;
+alter table weblogentry drop index weblogentry_pinnedtomain_index;
+alter table weblogentry drop index weblogentry_publishentry_index;
+alter table newsfeed drop index newsfeed_websiteid_index;
+alter table comment drop index comment_entryid_index;
+alter table pingtarget drop index pingtarget_websiteid_index;
+alter table autoping drop index autoping_websiteid_index;
+alter table autoping drop index autoping_pingtargetid_index;
+alter table pingcategory drop index pingcategory_autopingid_index;
+alter table pingcategory drop index pingcategory_categoryid_index;
+alter table pingqueueentry drop index pingqueueentry_entrytime_index;
+alter table pingqueueentry drop index pingqueueentry_pingtargetid_index;
+alter table pingqueueentry drop index pingqueueentry_websiteid_index;
+alter table referer drop index referer_websiteid_index;
+alter table referer drop index referer_entryid_index;
+alter table referer drop index referer_refurl_index;
+alter table referer drop index referer_requrl_index;
+alter table referer drop index referer_datestr_index;
+alter table referer drop index referer_refpermalink_index;
+alter table referer drop index referer_duplicate_index;
+alter table entryattribute drop index entryattribute_entryid_index;
+alter table rag_group_subscription drop index rag_group_subscription_gid;
+alter table rag_group_subscription drop index rag_group_subscription_sid;
+alter table rag_group drop index rag_group_handle;
+alter table rag_subscription drop index rag_subscription_feed_url;
+alter table rag_entry drop index rag_entry_sid;
+
+
+-- Adding all new indexes with short names
+
+create index ur_userid_idx on userrole( userid );
+create index ur_username_idx on userrole( username(40) );
+create index uc_username_idx on usercookie( username(40) );
+create index uc_cookieid_idx on usercookie( cookieid(40) );
+create index wp_name_idx on webpage( name(40) );
+create index wp_link_idx on webpage( link(40) );
+create index wp_id_idx on webpage( websiteid );
+create index ws_userid_idx on website(userid);
+create index ws_isenabled_idx on website(isenabled);
+create index fo_websiteid_idx on folder( websiteid );
+create index fa_folderid_idx on folderassoc( folderid );
+create index fa_ancestorid_idx on folderassoc( ancestorid );
+create index fa_relation_idx on folderassoc( relation );
+create index bm_folderid_idx on bookmark( folderid );
+create index wc_websiteid_idx on weblogcategory( websiteid );
+create index wca_categoryid_idx on weblogcategoryassoc( categoryid );
+create index wca_ancestorid_idx on weblogcategoryassoc( ancestorid );
+create index wca_relation_idx on weblogcategoryassoc( relation );
+create index we_websiteid_idx on weblogentry( websiteid );
+create index we_categoryid_idx on weblogentry( categoryid );
+create index we_pubtime_idx on weblogentry( pubtime,publishentry,websiteid );
+create index we_pinnedtom_idx on weblogentry(pinnedtomain);
+create index we_pubentry_idx on weblogentry(publishentry);
+create index we_userid_idx on weblogentry(userid);
+create index nf_websiteid_idx on newsfeed( websiteid );
+create index co_entryid_idx on comment( entryid );
+create index pt_websiteid_idx on pingtarget( websiteid );
+create index ap_websiteid_idx on autoping( websiteid );
+create index ap_pingtid_idx on autoping( pingtargetid );
+create index pc_autopingid_idx on pingcategory( autopingid );
+create index pc_categoryid_idx on pingcategory( categoryid );
+create index pqe_entrytime_idx on pingqueueentry( entrytime );
+create index pqe_pingtid_idx on pingqueueentry( pingtargetid );
+create index pqe_websiteid_idx on pingqueueentry( websiteid );
+create index ref_websiteid_idx on referer( websiteid );
+create index ref_entryid_idx on referer( entryid );
+create index ref_refurl_idx on referer( refurl(40) );
+create index ref_requrl_idx on referer( requrl(40) );
+create index ref_datestr_idx on referer( datestr );
+create index ref_refpermlnk_idx on referer( refpermalink(40) );
+create index ref_duplicate_idx on referer( duplicate );
+create index ea_entryid_idx on entryattribute( entryid );
+create index raggs_gid_idx on rag_group_subscription(group_id(40));
+create index raggs_sid_idx on rag_group_subscription(subscription_id(40));
+create index rage_sid_idx on rag_entry(subscription_id(40));
+
+-- Now add the foreign key relationships
+
+-- user, role and website
+alter table website add constraint ws_userid_fk
+ foreign key ( userid ) references rolleruser ( id ) ;
+
+alter table userrole add constraint ur_userid_fk
+ foreign key ( userid ) references rolleruser( id ) ;
+
+-- page, entry, category, comment
+alter table webpage add constraint wp_websiteid_fk
+ foreign key ( websiteid ) references website( id ) ;
+
+alter table weblogentry add constraint we_websiteid_fk
+ foreign key ( websiteid ) references website( id ) ;
+
+alter table weblogentry add constraint wc_categoryid_fk
+ foreign key ( categoryid ) references weblogcategory( id ) ;
+
+alter table weblogcategory add constraint wc_websiteid_fk
+ foreign key ( websiteid ) references website( id ) ;
+
+alter table comment add constraint co_entryid_fk
+ foreign key ( entryid ) references weblogentry( id ) ;
+
+alter table entryattribute add constraint att_entryid_fk
+ foreign key ( entryid ) references weblogentry( id ) ;
+
+-- referer
+alter table referer add constraint ref_entryid_fk
+ foreign key ( entryid ) references weblogentry( id ) ;
+
+alter table referer add constraint ref_websiteid_fk
+ foreign key ( websiteid ) references website( id ) ;
+
+-- folder and bookmark
+alter table folder add constraint fo_websiteid_fk
+ foreign key ( websiteid ) references website( id ) ;
+
+alter table bookmark add constraint bm_folderid_fk
+ foreign key ( folderid ) references folder( id ) ;
+
+-- newsfeed
+alter table newsfeed add constraint nf_websiteid_fk
+ foreign key ( websiteid ) references website( id ) ;
+
+-- pingtarget, autoping, pingcategory
+alter table pingtarget add constraint pt_websiteid_fk
+ foreign key (websiteid) references website(id) ;
+
+alter table autoping add constraint ap_websiteid_fk
+ foreign key (websiteid) references website(id) ;
+
+alter table autoping add constraint ap_pingtargetid_fk
+ foreign key (pingtargetid) references pingtarget(id) ;
+
+alter table pingcategory add constraint pc_autopingid_fk
+ foreign key (autopingid) references autoping(id) ;
+
+alter table pingcategory add constraint pc_categoryid_fk
+ foreign key (categoryid) references weblogcategory(id) ;
+
+-- Oracle compatability DDL
+alter table comment rename to roller_comment;