select distinct max(version) into databaseBuild from sv;
// Now that we have a more robust checking scheme, rerun the update for
// build 2 in case it was not done properly.
if databaseBuild = 2 then
set databaseBuild = 1;
end if;
// Proceed with Build Modification
// Beta 11 and prior
if databaseBuild < 1 then
Message 'Update to build 1';
Message '1: Fix HTML type in MimeTypes';
update mimetypes set description='text/html' where description='text/htm;';
if not exists(select column_name from sys.syscolumn key join sys.systable
where sys.syscolumn.column_name = 'ReceiveDate' and
sys.systable.table_name = 'messages') then
Message '1: Add ReceiveDate column to messages table';
alter table messages add
ReceiveDate date null;
end if
;
if not exists(select column_name from sys.syscolumn key join sys.systable
where sys.syscolumn.column_name = 'ReceiveTime' and
sys.systable.table_name = 'messages') then
Message '1: Add ReceiveTime column to messages table';
alter table messages add
ReceiveTime time null;
end if
;
if not exists(select column_name from sys.syscolumn key join sys.systable
where sys.syscolumn.column_name = 'LastDeliveryDateTime' and
sys.systable.table_name = 'messages') then
Message '1: Add LastDeliveryDateTime column to messages table';
alter table messages add
LastDeliveryDateTime datetime;
end if
;
Message 'Update build number to 1';
update sv set version=1;
end if
;
// Successive version go below as that is how updates will be applied in series.
// Beta 12
if databaseBuild < 2 then
message 'Update to build 2';
message '2: Update new user area to use default template';
update subsysin set BannerGraphic = 'newuser.gif'
where instparam='newusr.sht';
update subsysin set InstParam = 'area.sht'
where instparam='newusr.sht';
if not exists(select column_name from sys.syscolumn key join sys.systable
where sys.syscolumn.column_name = 'id' and
sys.systable.table_name = 'cgi_path') then
message '2: Add ID column to cgi_path table';
alter table cgi_path add ID integer default autoincrement;
end if
;
message '2: Assign IDs to existing CGIs';
update cgi_path set id=1 where cgi='imagemap';
update cgi_path set id=2 where cgi='whocgi';
update cgi_path set id=3 where cgi='newuser';
if exists(select table_name from sys.systable
where table_name = 'systeminformation') then
message '2: Delete all entries from systeminformation';
delete from systeminformation;
end if;
if not exists(select column_name from sys.syscolumn key join sys.systable
where sys.syscolumn.column_name = 'server' and
sys.systable.table_name = 'systeminformation') then
message '2: Add server column to the systeminformation table';
alter table systeminformation add server char(81) default '';
message '2: Make sure all server entries are empty strings.';
end if
;
message '2: Make sure all server entries are empty strings.';
message '2: Add server descriptions to systeminformation table';
update systeminformation set server='httpsapp' where unitid=1;
update systeminformation set server='httpsapp' where unitid=2;
update systeminformation set server='email' where unitid=3;
update systeminformation set server='email' where unitid=4;
update systeminformation set server='email' where unitid=5;
alter table systeminformation modify server not null;
if not exists(select column_name from sys.syscolumn key join sys.systable
where sys.syscolumn.column_name = 'virtid' and
sys.systable.table_name = 'area_dirs') then
message '2: add virtid column to area_dirs table';
alter table area_dirs add virtid integer;
end if
;
if not exists(select table_name from sys.systable
where sys.systable.table_name = 'attachments') then
message '2: Create attachments table';
create table attachments(
attachid integer primary key default autoincrement not null,
fname long varchar not null default '',
stored long varchar not null default ''
);
end if
;
if not exists(select table_name from sys.systable
where sys.systable.table_name = 'msgattach') then
message '2: Create msgattach table';
create table msgattach(
msgbase integer not null,
attachid integer not null,
msgid integer not null
)
end if
;
message 'Update build number to 2';
update sv set version=2;
end if;
if databaseBuild < 3 then
message 'Update to build 3';
if exists(select column_name from sys.syscolumn key join sys.systable
where sys.systable.table_name='cgi_path' and
sys.syscolumn.column_name = 'id') then
// Check to see if the cgiid column already exists
if exists(select column_name from sys.syscolumn key join sys.systable
where sys.systable.table_name='cgi_path' and
sys.syscolumn.column_name='cgiid') then
alter table cgi_path delete id;
else
message '3: Rename id column to cgiid in cgi_path table';
alter table cgi_path rename id to cgiid;
end if;
end if;
if exists(select table_name from sys.systable
where sys.systable.table_name='cgi_path') then
if not exists(select server from systeminformation
where server='email') then
message '3: Add email entries into systeminformation table';
Insert into SystemInformation (KeyWord,Value,server) values ('EmailRetry','60','email');
Insert into SystemInformation (KeyWord,Value,server) values ('EmailNotify','120','email');
Insert into SystemInformation (KeyWord,Value,server) values ('EmailBounce','1440','email');
end if;
end if;
message 'Update build number to 3';
update sv set version = 3;
end if;
if dataBaseBuild < 4 then
message 'Begin build 4 update.';
if exists(select table_name
from sys.systable where table_name = 'area_dirs') then
message '4: Drop the area_dirs table.';
drop table area_dirs;
end if;
if not exists(select column_name from sys.syscolumn key join sys.systable
where sys.systable.table_name='subsysin' and
sys.syscolumn.column_name='directory') then
message '4: Adding directory column to subsysin table.';
alter table subsysin add directory long varchar;
update subsysin set directory = '';
alter table subsysin modify directory not null default '';
end if;
if not exists(select action from configuration
where action = 'set_master_user') then
message '4: Adding action ssifiles to the configuration table.';
insert into configuration(action, ssifile) values ('set_master_user','setmasur.sht');
insert into configuration(action, ssifile) values ('edit_profile_else','editpfel.sht');
insert into configuration(action, ssifile) values ('new_account_user','nauser.sht');
insert into configuration(action, ssifile) values ('new_account_user_update','naupdate.sht');
insert into configuration(action, ssifile) values ('delete_profile','delprofl.sht');
insert into configuration(action, ssifile) values ('new_profile','newprofl.sht');
insert into configuration(action, ssifile) values ('edit_profiles','editpros.sht');
insert into configuration(action, ssifile) values ('remove_profile_sub','remprsub.sht');
insert into configuration(action, ssifile) values ('add_prof_sub_list_err','adpfsble.sht');
insert into configuration(action, ssifile) values ('add_prof_sub_list','adpfsublt.sht');
insert into configuration(action, ssifile) values ('add_prof_sub','addpfsub.sht');
insert into configuration(action, ssifile) values ('edit_profile_error','edproerr.sht');
insert into configuration(action, ssifile) values ('virt_area_dirs','virtards.sht');
insert into configuration(action, ssifile) values ('new_virt_area_dir','nwvtardr.sht');
insert into configuration(action, ssifile) values ('edit_virt_area_dirs','edvtardr.sht');
insert into configuration(action, ssifile) values ('edit_virt_area_dirs_else','edvtarde.sht');
end if;
if exists(select column_name from sys.syscolumn key join sys.systable
where sys.systable.table_name='attachments' and
sys.syscolumn.column_name='attachid') then
message '4: Make the attachid (attachments) autoincrement.';
alter table attachments modify attachid default autoincrement;
end if;
if exists (select descript from subsysin
where id = 100 and descript like '%config%') then
message '4: Modify the config area from 100 to -1.';
update subsysin set id=-1 where id=100;
update subsysin set descript = 'Configuration Access' where id=-1;
update sub_prof set instanceid = -1 where instanceid=100;
update area_sub set instanceid = -1 where instanceid=100;
end if;
if not exists(select descript from subsysin
where descript = 'Modem Server Access') then
message '4: Add magic areas for PPP/Email/Read access.';
Insert Into SubSysIn (id,typeid,descript) VALUES (-2, 3, 'Modem Server Access');
Insert Into SubSysIn (id,typeid,descript) VALUES (-3, 3, 'E-Mail Server Access');
Insert Into SubSysIn (id,typeid,descript) VALUES (-4, 3, 'Read All Messages');
Insert Into Area_Sub (AreaId, InstanceId) values (-1, -2);
Insert Into Area_Sub (AreaId, InstanceId) values (-1, -3);
Insert Into Area_Sub (AreaId, InstanceId) values (-1, -4);
end if;
if exists(select table_name from sys.systable
where table_name = 'cgi_path') then
if not exists(select cgi from cgi_path where cgi='filebase') then
message '4: Add CGI_PATH entries for filebase and msgbase.';
Insert into cgi_path (cgi, location) values ('filebase', '.\');
Insert into cgi_path (cgi, location) values ('msgbase', '.\');
end if;
end if;
if not exists(select column_name from sys.syscolumn key join sys.systable
where sys.systable.table_name='messages' and
sys.syscolumn.column_name='LastWarning') then
message '4: Add LastWarning column to the messages table.';
alter table messages add LastWarning datetime;
end if;
message 'Update build number to 4';
update sv set version = 4;
message 'End of build 4 update.';
end if;
if dataBaseBuild < 5 then
message '5: Begin build 5 update';
if not exists(select column_name from sys.syscolumn key join sys.systable
where sys.systable.table_name='messages' and
sys.syscolumn.column_name='MsgRaw') then
message '5: Add MsgRaw column to the message table.';
alter table messages add MsgRaw long binary;
message '5: Set the MsgRaw column to be empty strings.';
update messages set MsgRaw = '';
alter table messages modify MsgRaw NOT NULL DEFAULT '';
end if;
if exists(select table_name from sys.systable
where sys.systable.table_name = 'users') then
message '5: Deleting duplicate aliases';
update users set alias = LEFT(alias,30)+STRING(users.id)
where users.alias in
(select alias from users group by alias having count(*) > 1);
message '5: Reloading user table with alias constraint.';
UNLOAD TABLE users to '.\\_mwdata_.dat';
drop table users;
CREATE TABLE Users(
ID integer NOT NULL PRIMARY KEY DEFAULT AUTOINCREMENT,
Alias char(41) NOT NULL DEFAULT '',
FName char(21) NOT NULL DEFAULT '',
LName char(21) NOT NULL DEFAULT '',
Street1 char(81) NOT NULL DEFAULT '',
Street2 char(81) NOT NULL DEFAULT '',
City char(41) NOT NULL DEFAULT '',
State char(21) NOT NULL DEFAULT '',
Zip char(11) NOT NULL DEFAULT '',
Country char(41) NOT NULL DEFAULT '',
Passwd char(21) NOT NULL DEFAULT '',
Account integer NOT NULL DEFAULT 0,
vphone char(21) NOT NULL DEFAULT '',
dphone char(21) NOT NULL DEFAULT '',
email char(81) NOT NULL DEFAULT '',
company char(81) NOT NULL DEFAULT '',
pcbuser char(26) NOT NULL DEFAULT '',
personaldir char(81) not null default '',
UNIQUE (Alias)
);
load table users from '.\\_mwdata_.dat';
CREATE INDEX UsersIDX on Users (Alias, FName, LName);
end if;
if not exists (select trigname from sys.systriggers
where trigname = 'AttachProfs') then
message '5: Create AttachProfs trigger';
create trigger AttachProfs before delete order 1 on "DBA".Users
referencing old as UserRec
for each row
begin
delete from user_profile where UserRec.ID = user_profile.userid
end;
end if;
if not exists (select trigname from sys.systriggers
where trigname = 'AttachUsers') then
message '5: Create AttachUsers trigger';
create trigger AttachUsers before delete order 1 on "DBA".Accounts
referencing old as AcctRec
for each row
begin
delete from users where Users.Account = AcctRec.ID
end;
end if;
if not exists(select index_name from sys.sysindex
where index_name='MSG_MessageID') then
message '5:Added MSG_MessageID index.';
CREATE INDEX MSG_MessageID on "DBA".Messages (MsgID);
end if;
if not exists(select index_name from sys.sysindex
where index_name='MSG_MsgB_MsgBID') then
message '5:Added MSG_MsgB_MsgBID index.';
CREATE INDEX MSG_MsgB_MsgBID on "DBA".MsgB_Msg (MbID);
end if;
if not exists(select index_name from sys.sysindex
where index_name='MSG_MsgB_MsgID') then
message '5:Added MSG_MsgB_MsgID index.';
CREATE INDEX MSG_MsgB_MsgID on "DBA".MsgB_Msg (MID);
end if;
if not exists(select index_name from sys.sysindex
where index_name='MSG_MessageTO') then
message '5:Added MSG_MessageTO index.';
CREATE INDEX MSG_MessageTO on "DBA".Msg_User (UsrId);
end if;
message 'Update buld number to 5';
update sv set version = 5;
message 'End of build 5 update.';
end if;
if dataBaseBuild < 6 then
message 'Begin build 6 update';
// Remove any trailing .DLL extensions in the QueryScript column of the
// SubSysTp table.
message '6: Remove .dll from the subsystp table.';
update subsystp set queryscript = LEFT(queryscript,LENGTH(queryscript)-4) where RIGHT(queryScript,4) = '.dll';
update subsystp set typeparam = LEFT(typeparam,PATINDEX('%.dll%',typeparam)-1)+RIGHT(typeparam,LENGTH(typeparam)-(PATINDEX('%.dll%',typeparam)+3)) where typeparam like '%.dll%';
if not exists (select table_name from sys.systable
where table_name = 'whosonline') then
message '6: Add the WhosOnline table';
CREATE TABLE WhosOnline (
ID integer PRIMARY KEY DEFAULT AUTOINCREMENT NOT NULL,
UserName char(81) NOT NULL DEFAULT '',
ServerName char(41) NOT NULL DEFAULT '',
MachineName char(41) NOT NULL DEFAULT '',
LoggedOn timestamp NOT NULL DEFAULT '80-01-01 00:00'
);
end if;
if not exists(select index_name from sys.sysindex
where index_name='MSG_MsgB_MsgBID') then
message '5:Added MSG_MsgB_MsgBID index.';
CREATE INDEX MSG_MsgB_MsgBID on "DBA".MsgB_Msg (MbID);
end if;
if not exists(select index_name from sys.sysindex
where index_name='MSG_MsgB_MsgID') then
message '5:Added MSG_MsgB_MsgID index.';
CREATE INDEX MSG_MsgB_MsgID on "DBA".MsgB_Msg (MID);
end if;
if not exists(select index_name from sys.sysindex
where index_name='MSG_MessageTO') then
message '5:Added MSG_MessageTO index.';
CREATE INDEX MSG_MessageTO on "DBA".Msg_User (UsrId);
end if;
message '6: Make message post and read dates real dates.';
alter table messages modify PostDate date;
alter table messages modify ReadDate date;
message '6: Erase entries from the server table.';
delete from servers;
if exists(select table_name from sys.systable
where table_name = 'cgi_path') then
if not exists(select cgi from cgi_path where cgi='area') then
message '6: Add CGI_PATH entry for area.dll.';
Insert into cgi_path (cgi, location) values ('area', '.\');
