MetaWorlds Beta 17/MSQMW17.ZIP/DATA.Z/update10.sql

Text File | 1997-01-16 | 20.7 KB | 558 lines

/******************************************************************************/
//            Beta 10
//
/******************************************************************************/
SET OPTION ON_ERROR = CONTINUE;
DROP PROCEDURE "dba".PreElevenUpdate;
drop function ImportPCBUsers;
create function "dba".ImportPCBUsers(in sAlias char(26),in sFirst char(26),
     in sLast char(26),in sStreet1 char(81),in sStreet2 char(81),in sCity char(41),
     in sState char(21),in sZip char(21),in sCountry char(41),in sPassword char(21),
     in sVoicePhone char(21),in sDataPhone char(21),in sEmailAddr char(81),
     in sCompany char(61),in sPCBUser char(26),in vAcctID integer,in vProfID integer)
returns integer
begin
  declare vNewID integer;
  if(select id from users where fname=sFirst and lname=sLast and pcbuser=sPCBUser) is null then
    set vNewID=(select max(id) from users)+1;
    insert into users values(
      vNewID,sAlias,sFirst,sLast,sStreet1,sStreet2,sCity,sState,sZip,sCountry,sPassword,vAcctID,sVoicePhone,sDataPhone,sEmailAddr,sCompany,sPCBUser,'');
    insert into user_profile values(vNewID,vProfID)
  else
    set vNewID=-1
  end if
  ;
  return(vNewID)
end;

drop function "dba".ImportConferences;
create function "dba".ImportConferences(in sConfName char(81),in vConfNum char(21),in vConfID char(21),in vDirNum char(21),in sDirDesc char(81),in vWhichOpt integer)
returns integer
begin
  declare ReturnValue integer;
  declare vNewConfID integer;
  declare vNewMsgID integer;
  declare vNewFileID integer;
  declare tStr char(81);
  if vWhichOpt=0 then // Adding a new area & message base or just message base
    set vNewConfID=(select id from subsysin where descript=sConfName and typeid=0);
    if vNewConfID is null then
      insert into subsysin(TypeID,Instparam,Descript) values(0,'area_nb.sht',sConfName);
      set vNewConfID=@@identity;
      insert into area_sub values(0,vNewConfID);
      insert into areas values(vNewConfID)
    end if
    ;
    set vNewMsgID=(select max(ID) from subsysin)+1;
    set tStr=cast(vNewMsgID as char(5))+'&conf='+vConfNum;
    insert into subsysin(id,typeid,instparam,descript) values(vNewMsgID,5,tStr,'Messages');
    insert into area_sub values(vNewConfID,vNewMsgID);
    insert into areas values(vNewMsgID);
    set ReturnValue=vNewConfID
  else // Adding a new file directory
    set tStr='conf='+vConfNum;
    set vNewFileID=(select id from subsysin where descript=sDirDesc and typeid=6 and(locate(subsysin.instparam,tStr)>0));
    if vNewFileID is null then
      insert into subsysin(typeid,descript) values(6,sDirDesc);
      set vNewFileID=@@identity;
      set tStr=cast(vNewFileID as char(5))+'&DOCUMENT=pcbfile.sht&conf='+vConfNum+'&dir='+vDirNum;
      update subsysin set instparam=tStr where id=vNewFileID;
      insert into area_sub values(vConfID,vNewFileID);
      insert into areas values(vNewFileID)
    end if
    ;
    set ReturnValue=vNewFileID
  end if
  ;
  return(ReturnValue)
end;

unload table configuration to '.\\_mwdata_.dat';
drop table configuration;
CREATE TABLE Configuration(
  action      long varchar NOT NULL DEFAULT '',
  ssifile     long varchar NOT NULL DEFAULT ''
);
load table configuration from '.\\_mwdata_.dat';


unload table area_dirs to '.\\_mwdata_.dat';
drop table area_dirs;
CREATE TABLE area_dirs(
  area_id     integer NOT NULL DEFAULT AUTOINCREMENT,
  directory   long varchar NOT NULL DEFAULT ''
);
load table area_dirs from '.\\_mwdata_.dat';


unload table browser_hosts to '.\\_mwdata_.dat';
drop table browser_hosts;
CREATE TABLE browser_hosts(
  server_Name long varchar UNIQUE NOT NULL DEFAULT '',
  root          long varchar NOT NULL DEFAULT ''
);
load table browser_hosts from '.\\_mwdata_.dat';


unload table accounts to '.\\_mwdata_.dat';
drop table accounts;
CREATE TABLE Accounts(
  Id          integer NOT NULL PRIMARY KEY DEFAULT AUTOINCREMENT,
  MasterUser    integer NOT NULL DEFAULT 0
);
load table accounts from '.\\_mwdata_.dat';


unload table acctinst to '.\\_mwdata_.dat';
drop table acctinst;
CREATE TABLE AcctInst(
  ID          integer NOT NULL DEFAULT AUTOINCREMENT,
  AccountID   integer NOT NULL DEFAULT 0,
  UserId      integer NOT NULL DEFAULT 0,
  TypeDesc    long varchar NOT NULL DEFAULT '',
  InstncDesc    long varchar NOT NULL DEFAULT '',
  Charge      long varchar NOT NULL DEFAULT '',
  ChDate      long varchar NOT NULL DEFAULT ''
);
load table acctinst from '.\\_mwdata_.dat';


unload table area_sub to '.\\_mwdata_.dat';
drop table area_sub;
CREATE TABLE Area_Sub(
  AreaID      integer NOT NULL DEFAULT 0,
  InstanceId    integer NOT NULL DEFAULT 0
);
load table area_sub from '.\\_mwdata_.dat';


unload table sub_prof to '.\\_mwdata_.dat';
drop table sub_prof;
CREATE TABLE Sub_Prof(
  InstanceID    integer NOT NULL DEFAULT 0,
  ProfileId   integer NOT NULL DEFAULT 0
);
load table sub_prof from '.\\_mwdata_.dat';


unload table areas to '.\\_mwdata_.dat';
drop table areas;
CREATE TABLE Areas(
  ID          integer NOT NULL PRIMARY KEY DEFAULT 0
);
load table areas from '.\\_mwdata_.dat';


update files set ddate = '80/01/01' where ddate=NULL;
alter table files modify ddate date;
alter table files modify size integer;
unload table files to '.\\_mwdata_.dat';
drop table files;
CREATE TABLE Files(
  ID          integer NOT NULL PRIMARY KEY DEFAULT AUTOINCREMENT,
  URL          long varchar NOT NULL DEFAULT '',
  Descript    long varchar NOT NULL DEFAULT '',
  Size          integer NOT NULL DEFAULT 0,
  DDate       date NOT NULL DEFAULT '80/01/01'
);
load table files from '.\\_mwdata_.dat';


unload table flb_file to '.\\_mwdata_.dat';
drop table flb_file;
CREATE TABLE FLB_File(
  FbID          integer DEFAULT AUTOINCREMENT NOT NULL,
  FID          integer NOT NULL DEFAULT 0
);
load table flb_file from '.\\_mwdata_.dat';


unload table profiles to '.\\_mwdata_.dat';
drop table profiles;
CREATE TABLE Profiles(
  ID          integer NOT NULL PRIMARY KEY DEFAULT AUTOINCREMENT,
  Name          long varchar NOT NULL DEFAULT 'Undefined'
);
load table profiles from '.\\_mwdata_.dat';


unload table subsysin to '.\\_mwdata_.dat';
drop table subsysin;
CREATE TABLE SubSysIn(
  ID          integer NOT NULL PRIMARY KEY DEFAULT AUTOINCREMENT,
  TypeId      integer NOT NULL DEFAULT 0,
  InstParam   long varchar NOT NULL DEFAULT 'area_nb.sht',
  Descript    long varchar NOT NULL DEFAULT 'Undefined',
  Button      long varchar NOT NULL DEFAULT '',
  BannerGraphic long varchar NOT NULL DEFAULT ''
);
load table subsysin from '.\\_mwdata_.dat';


unload table subsystp to '.\\_mwdata_.dat';
drop table subsystp;
CREATE TABLE SubSysTP(
  ID          integer DEFAULT AUTOINCREMENT NOT NULL,
  Descript    long varchar NOT NULL DEFAULT 'Undefined',
  QueryScript long varchar NOT NULL DEFAULT '',
  TypeParam   long varchar NOT NULL DEFAULT '',
  Button      long varchar NOT NULL DEFAULT ''
);
load table subsystp from '.\\_mwdata_.dat';


unload table user_profile to '.\\_mwdata_.dat';
drop table user_profile;
CREATE TABLE User_Profile(
  UserID      integer NOT NULL DEFAULT 0,
  ProfileID   integer NOT NULL DEFAULT 0
);
load table user_profile from '.\\_mwdata_.dat';


unload table sappsif to '.\\_mwdata_.dat';
drop table sappsif;
CREATE TABLE SappSIF(
  ID        integer NOT NULL PRIMARY KEY DEFAULT AUTOINCREMENT,
  MachineName    long varchar NOT NULL DEFAULT '<default>',
  ServiceName    long varchar NOT NULL DEFAULT '',
  CommandLine    long varchar NOT NULL DEFAULT ''
);
load table sappsif from '.\\_mwdata_.dat';


unload table machines to '.\\_mwdata_.dat';
drop table machines;
CREATE TABLE Machines(
  ID        integer PRIMARY KEY DEFAULT AUTOINCREMENT,
  MachineName    long varchar NOT NULL DEFAULT '',
  License    integer NOT NULL DEFAULT 0
);
load table machines from '.\\_mwdata_.dat';


unload table servers to '.\\_mwdata_.dat';
drop table servers;
CREATE TABLE Servers (
  id          integer PRIMARY KEY DEFAULT AUTOINCREMENT NOT NULL,
  name          char(72) NOT NULL DEFAULT 'Unknown Server',
  machineId   integer NOT NULL DEFAULT 0,
  defaultTimeOut  integer NOT NULL DEFAULT 0,      // In minutes
  FOREIGN KEY (machineId) REFERENCES Machines (id)
);
load table servers from '.\\_mwdata_.dat';


unload table logcategory to '.\\_mwdata_.dat';
drop table logcategory;
CREATE TABLE LogCategory (
  CategoryId    INTEGER DEFAULT AUTOINCREMENT NOT NULL,
  Category    long varchar NOT NULL UNIQUE DEFAULT 'Unknown',
  Permissions    INTEGER DEFAULT 0 NOT NULL,
  Description    long varchar NOT NULL DEFAULT 'No description',
  PRIMARY KEY(CategoryId)
);
load table logcategory from '.\\_mwdata_.dat';


unload table log to '.\\_mwdata_.dat';
drop table log;
CREATE TABLE Log (
  Id         INTEGER DEFAULT AUTOINCREMENT NOT NULL,
  CategoryId     INTEGER NOT NULL DEFAULT 0,
  LogType     INTEGER NOT NULL DEFAULT 0,
  LogTime     timestamp NOT NULL DEFAULT '80/01/01 00:00',
  theUser     long varchar NOT NULL DEFAULT '',
  Computer     long varchar NOT NULL DEFAULT '',
  Source     long varchar NOT NULL DEFAULT '',
  Description     long varchar NOT NULL DEFAULT '',
  PRIMARY KEY(Id),
  FOREIGN KEY (categoryId) REFERENCES LogCategory (CategoryId)
);
load table log from '.\\_mwdata_.dat';


unload table commdevices to '.\\_mwdata_.dat';
drop table commdevices;
CREATE TABLE CommDevices(
  ID          integer NOT NULL PRIMARY KEY DEFAULT AUTOINCREMENT,
  MACHINE      long varchar NOT NULL DEFAULT '',
  DeviceName      long varchar NOT NULL DEFAULT '',
  AttachedTo      long varchar NOT NULL DEFAULT '',
  UseDevice      integer NOT NULL DEFAULT 0
);
load table commdevices from '.\\_mwdata_.dat';


unload table commdeviceusage to '.\\_mwdata_.dat';
drop table commdeviceusage;
CREATE TABLE CommDeviceUsage(
  DeviceID    integer NOT NULL DEFAULT 0,
  UsageID     integer NOT NULL DEFAULT 0,
  ID          integer NOT NULL PRIMARY KEY DEFAULT AUTOINCREMENT
);
load table commdeviceusage from '.\\_mwdata_.dat';


unload table usagetypes to '.\\_mwdata_.dat';
drop table usagetypes;
CREATE TABLE UsageTypes (
  ID          integer NOT NULL PRIMARY KEY DEFAULT AUTOINCREMENT,
  type          long varchar NOT NULL DEFAULT ''
);
load table usagetypes from '.\\_mwdata_.dat';


unload table callhistory to '.\\_mwdata_.dat';
drop table callhistory;
CREATE TABLE CallHistory (
  ID          integer NOT NULL PRIMARY KEY DEFAULT AUTOINCREMENT,
  Device      integer NOT NULL DEFAULT 0,
  LogOn       TimeStamp NOT NULL DEFAULT '80/01/01 00:00',
  LogOff      TimeStamp NOT NULL DEFAULT '80/01/01 00:00',
  UserID      integer NOT NULL DEFAULT 0,
  CallType    integer NOT NULL DEFAULT 0,
  ThruPut     integer NOT NULL DEFAULT 0,
  Remarks     long varchar NOT NULL DEFAULT '',
  actions     long varchar NOT NULL DEFAULT ''
);
load table callhistory from '.\\_mwdata_.dat';


unload table ipaddr to '.\\_mwdata_.dat';
drop table ipaddr;
CREATE TABLE IPAddr (
   Addr       char(16) NOT NULL PRIMARY KEY DEFAULT '0.0.0.0',
   Machine    long varchar NOT NULL DEFAULT '<default>',
   Description      long varchar NOT NULL DEFAULT 'Undefined'
);
load table ipaddr from '.\\_mwdata_.dat';


drop table useron;
CREATE TABLE UserOn (
  id          integer PRIMARY KEY DEFAULT AUTOINCREMENT NOT NULL,
  userId      integer NOT NULL DEFAULT 0,
  machineId   integer NOT NULL DEFAULT 0,
  serverId    integer NOT NULL DEFAULT 0,
  timeOut     integer NOT NULL DEFAULT 0,        // time in minutes
  actionTime    TimeStamp NOT NULL DEFAULT '80/01/01 00:00:00',
  FOREIGN KEY (userId)      REFERENCES Users (id),
  FOREIGN KEY (machineId) REFERENCES Machines (id),
  FOREIGN KEY (serverId)  REFERENCES Servers(id)
);


unload table cgi_path to '.\\_mwdata_.dat';
drop table cgi_path;
CREATE TABLE cgi_path (
  cgi        long varchar UNIQUE NOT NULL DEFAULT '',
  location    long varchar NOT NULL DEFAULT ''
);
load table cgi_path from '.\\_mwdata_.dat';

unload table mimetypes to '.\\_mwdata_.dat';
drop table mimetypes;
CREATE TABLE MimeTypes (
  MimeId    integer  UNIQUE NOT NULL DEFAULT AUTOINCREMENT,
  Extension    char(11) UNIQUE NOT NULL DEFAULT '',
  Description    char(81) NOT NULL DEFAULT ''
);
load table mimetypes from '.\\_mwdata_.dat';
CREATE INDEX MimeTypesIdx on MimeTypes (Extension);

unload table counturl to '.\\_mwdata_.dat';
drop table counturl;
CREATE TABLE counturl(
  url        long varchar NOT NULL DEFAULT '',
  count     integer NOT NULL DEFAULT 0
);
load table counturl from '.\\_mwdata_.dat';


unload table messages to '.\\_mwdata_.dat';
drop table messages;
CREATE TABLE Messages(
  MsgID           integer NOT NULL PRIMARY KEY DEFAULT AUTOINCREMENT,
  MsgSubject          long varchar NOT NULL DEFAULT '',
  MsgFrom          long varchar NOT NULL DEFAULT '',
  MsgTo           long varchar NOT NULL DEFAULT '',
  MsgBody          long binary NOT NULL DEFAULT '',
  MsgPrivate          integer NOT NULL DEFAULT 0,
  PostDate          long varchar,
  PostTime          long varchar,
  ReadDate          long varchar,
  ReadTime          long varchar,
  ContentType          long varchar NOT NULL DEFAULT '',
  ContentTransferEncoding long varchar NOT NULL DEFAULT ''
);
load table messages from '.\\_mwdata_.dat';


unload table msg_user to '.\\_mwdata_.dat';
drop table msg_user;
CREATE TABLE Msg_User(
  MsgID           integer NOT NULL DEFAULT 0,
  UsrID           integer NOT NULL DEFAULT 0
);
load table msg_user from '.\\_mwdata_.dat';


unload table msgb_msg to '.\\_mwdata_.dat';
drop table msgb_msg;
CREATE TABLE MsgB_Msg(
  MbID              integer NOT NULL DEFAULT 0,
  MID              integer NOT NULL DEFAULT 0
);
load table msgb_msg from '.\\_mwdata_.dat';


unload table lmrs to '.\\_mwdata_.dat';
drop table lmrs;
CREATE TABLE LMRS(
  UsrID           integer NOT NULL DEFAULT 0,
  MbID              integer NOT NULL DEFAULT 0,
  LMR              integer NOT NULL DEFAULT 0
);
load table lmrs from '.\\_mwdata_.dat';


unload table mail_prot to '.\\_mwdata_.dat';
drop table mail_prot;
create table mail_prot
(
  id    integer NOT NULL DEFAULT 0,
  name    long varchar NOT NULL DEFAULT 'Undefined'
);
load table mail_prot from '.\\_mwdata_.dat';


unload table mail_prot_instance to '.\\_mwdata_.dat';
drop table mail_prot_instance;
CREATE TABLE mail_prot_instance
(
  id        integer NOT NULL DEFAULT AUTOINCREMENT,
  prot_id    integer NOT NULL DEFAULT 0,
  address    long varchar NOT NULL DEFAULT '',
  msgb_id    integer NOT NULL DEFAULT 0
);
load table mail_prot_instance from '.\\_mwdata_.dat';


unload table mpi_msg to '.\\_mwdata_.dat';
drop table mpi_msg;
create table mpi_msg
(
  pi_id   integer NOT NULL DEFAULT 0,
  msg_id  integer NOT NULL DEFAULT 0,
);
load table mpi_msg from '.\\_mwdata_.dat';


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 '',
);
load table users from '.\\_mwdata_.dat';

alter table users add personaldir char(81) default '';
update users set personaldir = '';
alter table users modify personaldir not null;

CREATE INDEX UsersIDX on Users (Alias, FName, LName);

drop table user_dirs;
drop table events;

create table SystemInformation(
  UnitID     integer PRIMARY KEY DEFAULT AUTOINCREMENT NOT NULL,
  KeyWord    char(41) NOT NULL DEFAULT '',
  Value      char(81) NOT NULL DEFAULT ''
);

Insert into SystemInformation (KeyWord,Value) values ('UserTimeOut','5');
Insert into SystemInformation (KeyWord,Value) values ('PCBDatFile','.\\pcboard.dat');

delete from mimetypes;

//************************************************************************************************
// added 12-14-96  // Mime Types for HTML pages.

insert into MimeTypes (Extension,Description) values ('shtml','text/x-server-parsed-html');
insert into MimeTypes (Extension,Description) values ('shtm','text/x-server-parsed-html');
insert into MimeTypes (Extension,Description) values ('sht','text/x-server-parsed-html');
insert into MimeTypes (Extension,Description) values ('htp','text/x-server-parsed-html');
insert into MimeTypes (Extension,Description) values ('smh','text/x-server-parsed-html');
insert into MimeTypes (Extension,Description) values ('sma','text/x-server-parsed-html');
insert into MimeTypes (Extension,Description) values ('ans','text/x-server-parsed-html');
insert into MimeTypes (Extension,Description) values ('pcb','text/x-server-parsed-html');
insert into MimeTypes (Extension,Description) values ('gif','image/gif');
insert into MimeTypes (Extension,Description) values ('jpg','image/jpeg');
insert into MimeTypes (Extension,Description) values ('jpeg','image/jpeg');
insert into MimeTypes (Extension,Description) values ('bmp','image/x-bmp');
insert into MimeTypes (Extension,Description) values ('htm','text/html');
insert into MimeTypes (Extension,Description) values ('html','text/html');
insert into MimeTypes (Extension,Description) values ('url','wwwserver/redirection');
insert into MimeTypes (Extension,Description) values ('wav','audio/x-wav');
insert into MimeTypes (Extension,Description) values ('mid','audio/midi');
insert into MimeTypes (Extension,Description) values ('avi','video/avi');
insert into MimeTypes (Extension,Description) values ('mov','video/quicktime');
insert into MimeTypes (Extension,Description) values ('vox','audio/voxware');
insert into MimeTypes (Extension,Description) values ('wri','application/mswrite');
insert into MimeTypes (Extension,Description) values ('wrl','x-world/x-vrml');
insert into MimeTypes (Extension,Description) values ('zip','application/zip');
insert into MimeTypes (Extension,Description) values ('tif','image/tiff');
insert into MimeTypes (Extension,Description) values ('tiff','image/tiff');
insert into MimeTypes (Extension,Description) values ('txt','text/plain');
insert into MimeTypes (Extension,Description) values ('vos','video/vosaic');
insert into MimeTypes (Extension,Description) values ('ram','application/x-pn-realaudio');
insert into MimeTypes (Extension,Description) values ('rc','text/plain');
insert into MimeTypes (Extension,Description) values ('rtf','application/rtf');
insert into MimeTypes (Extension,Description) values ('rtx','text/richtext');
insert into MimeTypes (Extension,Description) values ('snd','audio/basic');
insert into MimeTypes (Extension,Description) values ('mpeg','video/mpeg');
insert into MimeTypes (Extension,Description) values ('mpg','video/mpeg');
insert into MimeTypes (Extension,Description) values ('pdf','application/pdf');
insert into MimeTypes (Extension,Description) values ('ps','application/postscript');
insert into MimeTypes (Extension,Description) values ('qt','video/quicktime');
insert into MimeTypes (Extension,Description) values ('ra','application/x-pn-realaudio');
insert into MimeTypes (Extension,Description) values ('ls','application/x-javascript');
insert into MimeTypes (Extension,Description) values ('lzh','application/x-lzh');
insert into MimeTypes (Extension,Description) values ('map','wwwserver/imagemap');
insert into MimeTypes (Extension,Description) values ('mocha','application/x-javascript');
insert into MimeTypes (Extension,Description) values ('mpe','video/mpeg');
insert into MimeTypes (Extension,Description) values ('ini','text/plain');
insert into MimeTypes (Extension,Description) values ('java','text/x-java-source');
insert into MimeTypes (Extension,Description) values ('js','application/x-javascript');
insert into MimeTypes (Extension,Description) values ('exe','application/octet-stream');
insert into MimeTypes (Extension,Description) values ('gz','application/x-gzip');
insert into MimeTypes (Extension,Description) values ('h','text/plain');
insert into MimeTypes (Extension,Description) values ('hlp','application/x-winhelp');
insert into MimeTypes (Extension,Description) values ('class','application/x-java-class');
insert into MimeTypes (Extension,Description) values ('cpp','text/plain');
insert into MimeTypes (Extension,Description) values ('doc','application/msword');
insert into MimeTypes (Extension,Description) values ('arj','application/x-arj');
insert into MimeTypes (Extension,Description) values ('au','audio/basic');
insert into MimeTypes (Extension,Description) values ('bin','application/octet-stream');
insert into MimeTypes (Extension,Description) values ('c','text/plain');

commit work;