Added: incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/094-to-095-migration.sql URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/094-to-095-migration.sql?rev=358311&view=auto ============================================================================== --- incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/094-to-095-migration.sql (added) +++ incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/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 boolean default false 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/postgresql/095-to-096-migration.sql URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/095-to-096-migration.sql?rev=358311&view=auto ============================================================================== --- incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/095-to-096-migration.sql (added) +++ incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/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/postgresql/096-to-097-migration.sql URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/096-to-097-migration.sql?rev=358311&view=auto ============================================================================== --- incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/096-to-097-migration.sql (added) +++ incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/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 boolean default false not null, + duplicate boolean default false not null +); +create index referer_websiteid_index on referer( websiteid ); +create index referer_entryid_index on referer( entryid ); + + +alter table website add column (allowcomments boolean default true 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/postgresql/097-to-098-migration.sql URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/097-to-098-migration.sql?rev=358311&view=auto ============================================================================== --- incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/097-to-098-migration.sql (added) +++ incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/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 boolean default true 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/postgresql/098-to-099-migration.sql URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/098-to-099-migration.sql?rev=358311&view=auto ============================================================================== --- incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/098-to-099-migration.sql (added) +++ incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/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/postgresql/098-to-100-migration.sql URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/098-to-100-migration.sql?rev=358311&view=auto ============================================================================== --- incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/098-to-100-migration.sql (added) +++ incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/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 boolean; +alter table comment add column spam boolean; +alter table comment add column remotehost varchar(128); +update comment set spam=false, notify=false, 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 boolean; +alter table weblogentry add column commentdays integer; +alter table weblogentry add column rightToLeft boolean; +alter table weblogentry add column pinnedtomain boolean; +update weblogentry set pubtime=pubtime, updatetime=updatetime, allowcomments=true, pinnedtomain=false; + +-- 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 boolean; +alter table website add column emailfromaddress varchar(255); +alter table website add column isenabled boolean; +update website set emailcomments=false, isenabled=true, 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 boolean default false not null, + algorithm varchar(10) null, + newuserallowed boolean default false not null, + editorpages varchar(255) null, + userthemes varchar(255) not null, + indexdir varchar(255) null, + memdebug boolean default false not null, + autoformatcomments boolean default false not null, + escapecommenthtml boolean default true not null, + emailcomments boolean default false not null, + enableaggregator boolean default false not null, + enablelinkback boolean default false not null, + rsscachetime integer default 3000 not null, + rssusecache boolean default true not null, + uploadallow varchar(255) null, + uploadforbid varchar(255) null, + uploadenabled boolean default true 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 ); +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/postgresql/100-to-110-migration.sql URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/100-to-110-migration.sql?rev=358311&view=auto ============================================================================== --- incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/100-to-110-migration.sql (added) +++ incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/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/postgresql/110-to-120-migration.sql URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/110-to-120-migration.sql?rev=358311&view=auto ============================================================================== --- incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/110-to-120-migration.sql (added) +++ incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/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); +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 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 ); +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 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); + + +-- 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/postgresql/120-to-130-migration.sql URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/120-to-130-migration.sql?rev=358311&view=auto ============================================================================== --- incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/120-to-130-migration.sql (added) +++ incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/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/postgresql/130-to-200-migration.sql URL: http://svn.apache.org/viewcvs/incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/130-to-200-migration.sql?rev=358311&view=auto ============================================================================== --- incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/130-to-200-migration.sql (added) +++ incubator/roller/trunk/web/WEB-INF/dbscripts/postgresql/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 boolean default true 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 set default ''; +update website set handle=''; +alter table website alter handle set not null ; + +alter table website add column datecreated timestamp; +alter table website alter datecreated set default '20050101'; +update website set datecreated='20050101'; +alter table website alter datecreated set not null; + +alter table website add column emailaddress varchar(255); +alter table website alter emailaddress set default ''; +update website set emailaddress=''; +alter table website alter handle set 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 set default ''; +update weblogentry set userid=''; +alter table weblogentry alter userid set not null; + +alter table weblogentry add column status varchar(20); +alter table weblogentry alter status set default ''; +update weblogentry set status=''; +alter table weblogentry alter status set not null; + +create index we_status_idx on weblogentry(status); +create index weblogentry_userid_index on weblogentry(userid); + +alter table rolleruser add column isenabled boolean; +alter table rolleruser alter isenabled set default true; +update rolleruser set isenabled=true; +alter table rolleruser alter isenabled set not null; + +alter table rolleruser add column locale varchar(50); +alter table rolleruser alter locale set default ''; +update rolleruser set locale=''; +alter table rolleruser alter locale set not null; + +alter table rolleruser add column timezone varchar(50); +alter table rolleruser alter timezone set default ''; +update rolleruser set timezone=''; +alter table rolleruser alter timezone set 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 alter pubtime drop not 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 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;
