PC World 1998 December/PCWorld_1998-12_cd.iso/software/sybase/ASA/asa60.exe/data1.cab/scripts_files/upgrade.sql

Text File | 1998-07-27 | 30KB | 1,050 lines

--  ************************************************************************
--  *       Copyright (C) 1998 by Sybase Inc.  All rights                   *
--  *       reserved. No part of this software may be reproduced            *
--  *       in any form or by any means - graphic, electronic or            *
--  *       mechanical, including photocopying, recording, taping           *
--  *       or information storage and retrieval systems - except           *
--  *       with the written permission of Sybase Inc.                      *
--  *************************************************************************
--
--  WARNING: This is a program generated file. Do not edit.
--




CREATE VARIABLE @page_size        smallint
go
CREATE VARIABLE @encryption        char(1)
go
CREATE VARIABLE @blank_padding        char(1)
go
CREATE VARIABLE @case_sensitivity    char(1)
go
CREATE VARIABLE @default_collation    char(10)
go
CREATE VARIABLE @database_version    smallint
go
CREATE VARIABLE @sysinfo_exists        char(1)
go
CREATE VARIABLE @avoid_view_collisions    char(1)
go
set @database_version = @newest_database_version
go
if @upgrade_type <=  10  then
  begin
    declare num int;

    select count(*) into num from SYS.SYSTABLE
    where table_name='SYSPUBLICATION' and creator=0;
    if num = 1 then
    select count(*) into num from SYS.SYSPUBLICATION;
    if num = 0 then
        alter table SYS.SYSTABLE modify replicate null;
        update SYS.SYSTABLE set replicate='N';
        commit;
        alter table SYS.SYSTABLE modify replicate not null;
        commit;
    end if
    end if
  end
end if
go
if exists( select * from SYS.SYSTABLE where table_name = 'SYSINFO' and
    creator = 0 ) then
    SET @sysinfo_exists = 'Y';
    SELECT page_size, encryption, blank_padding, case_sensitivity, default_collation
      INTO @page_size, @encryption, @blank_padding, @case_sensitivity, @default_collation
    FROM SYS.SYSINFO;
else
    set @sysinfo_exists = 'N';
end if
go
SET @avoid_view_collisions = 'N'
go
if exists( select * from SYS.SYSTABLE where table_name = 'SYSCOLUMNS' and
    creator = 0 and table_type = 'VIEW' ) THEN
    drop view SYS.SYSCOLUMNS;
else
    set @avoid_view_collisions = 'Y';
end if
go
if exists( select * from SYS.SYSTABLE where table_name = 'SYSINDEXES' and
    creator = 0 and table_type = 'VIEW' )  THEN
    drop view SYS.SYSINDEXES;
else
    SET @avoid_view_collisions = 'Y';
end if
go
begin
    for dropviews as viewcurs cursor for select table_name from SYS.SYSTABLE
        where creator = 0 and table_type = 'VIEW'
    do
    execute immediate with quotes 'drop view  "SYS"."' ||  table_name || '"';
    end for;
end
go
commit work
go

drop table SYS.DUMMY
go
if @sysinfo_exists = 'Y' then
    drop table SYS.SYSINFO;
end if
go


if @upgrade_type >=  3  then
  begin
    declare dbo_user_id int;
    select user_id into dbo_user_id from SYS.SYSUSERPERM where user_name='dbo';
    for dropviews as viewcurs cursor for
        select table_name from SYS.SYSTABLE
        where creator = dbo_user_id and table_type = 'VIEW'
    do
    execute immediate with quotes 'drop view  "dbo"."' ||  table_name || '"';
    end for;

    for dropprocs as proccurs cursor for
        select proc_name from SYS.SYSPROCEDURE
        where creator = dbo_user_id
    do
    execute immediate with quotes 'drop procedure "dbo"."' ||  proc_name || '"';
    end for;

    drop table dbo.RowGenerator;
  end
end if
go
commit work
go
if not exists( select * from SYS.SYSDOMAIN where domain_id =  24  ) then
    if @upgrade_type <  5  then
        insert into SYS.SYSDOMAIN values (  24 , 'bit' );
    else
        insert into SYS.SYSDOMAIN values (  24 , 'bit', -7, 1 );
    end if;
    commit work;
end if
go
if not exists( select * from SYS.SYSDOMAIN where domain_id =  25  ) then
    if @upgrade_type <  5  then
        insert into SYS.SYSDOMAIN values (  25 , 'java.lang.Object' );
    else
        insert into SYS.SYSDOMAIN values (  25 , 'java.lang.Object', 0, NULL );
    end if;
    commit work;
end if
go
if not exists( select * from SYS.SYSDOMAIN where domain_id =  20  ) then
    if @upgrade_type <  5  then
        insert into SYS.SYSDOMAIN values (  20 , 'bigint' );
        insert into SYS.SYSDOMAIN values (  21 , 'unsigned int' );
        insert into SYS.SYSDOMAIN values (  22 , 'unsigned smallint' );
        insert into SYS.SYSDOMAIN values (  23 , 'unsigned bigint' );
        insert into SYS.SYSDOMAIN values (  26 , 'java serialization' );
        insert into SYS.SYSDOMAIN values (  27 , 'decimal' );
        insert into SYS.SYSDOMAIN values (  28 , 'varbinary' );
    else
        insert into SYS.SYSDOMAIN values (  20 , 'bigint', -5, 20 );
        insert into SYS.SYSDOMAIN values (  21 , 'unsigned int', -9, 10 );
        insert into SYS.SYSDOMAIN values (  22 , 'unsigned smallint', -10, 5 );
        insert into SYS.SYSDOMAIN values (  23 , 'unsigned bigint', -11, 21 );
        insert into SYS.SYSDOMAIN values (  26 , 'java serialization', -4, NULL );
        insert into SYS.SYSDOMAIN values (  27 , 'decimal', 2, NULL );
        insert into SYS.SYSDOMAIN values (  28 , 'varbinary', -2, NULL );
    end if;
    commit work;
end if
go
import upgrflag.sql
if @upgrade_type <  3  then
    begin
    declare dbo_user_id        int;

    INSERT INTO SYS.SYSDOMAIN VALUES (  19 , 'tinyint' );
    commit work;

    if @collation_size <= 1024 then
        alter table SYS.SYSCOLLATION modify collation_order BINARY(1280);
        update SYS.SYSCOLLATION SET collation_order = collation_order || @flags;
        COMMIT WORK;
    end if;

    select max( user_id )+1 into dbo_user_id from SYS.SYSUSERPERM;
    insert into SYSUSERPERM values (
        dbo_user_id, 'dbo', NULL, 'Y', 'Y', 'N', 'Y', NULL );
    insert into SYSGROUP values ( dbo_user_id,  2  );
    insert into SYSGROUP values (  0 , dbo_user_id );
    commit work;
    end
end if
go


if @upgrade_type <  3  then
    alter table SYS.SYSTABLE add replicate char(1);
    update SYS.SYSTABLE set replicate = 'N';
    alter table SYS.SYSTABLE modify replicate char(1) not null;
    alter table SYS.SYSCOLUMN add "check" long varchar NULL;
    alter table SYS.SYSCOLUMN add user_type smallint NULL;
    alter table SYS.SYSUSERPERM add publishauth char(1);
    update SYS.SYSUSERPERM set publishauth = 'N';
    commit work;
    alter table SYS.SYSUSERPERM modify publishauth char(1) NOT NULL;
    commit work;
end if
go
if @upgrade_type =  2  then
    alter table SYS.SYSPROCEDURE add replicate   char(1);
    update SYS.SYSPROCEDURE set replicate = 'N';
    alter table SYS.SYSPROCEDURE modify replicate NOT NULL;
    alter table SYS.SYSPROCPARM ADD "default" long varchar NULL;
    commit work;
end if
go
if @upgrade_type <  3  then
CREATE TABLE SYS.SYSPUBLICATION (
    publication_id     smallint         NOT NULL,
    creator         smallint         NOT NULL,
    publication_name    char(128)       NOT NULL,
    remarks        long varchar,
    PRIMARY KEY (publication_id)
)
 ;
GRANT SELECT on SYS.SYSPUBLICATION to SYS
 ;
create table SYS.SYSARTICLE (
    publication_id     smallint         NOT NULL,
    table_id         smallint         NOT NULL,
    where_expr        long varchar,
    subscribe_by_expr    long varchar,
    query        char(1)        NOT NULL,
    PRIMARY KEY (publication_id, table_id)
)
 ;
GRANT SELECT on SYS.SYSARTICLE to SYS
 ;
create table SYS.SYSARTICLECOL (
    publication_id     smallint         NOT NULL,
    table_id         smallint         NOT NULL,
    column_id         smallint         NOT NULL,
    PRIMARY KEY (publication_id, table_id, column_id)
)
 ;
GRANT SELECT on SYS.SYSARTICLECOL to SYS
 ;
create table SYS.SYSREMOTEUSER (
    user_id         smallint         NOT NULL,
    consolidate        char(1)        NOT NULL,
    type_id        smallint    NOT NULL,
    address        long varchar    NOT NULL,
    frequency        char(1)        NOT NULL,
    send_time        time,
    log_send        numeric(20,0)    NOT NULL,
    time_sent        timestamp,
    log_sent        numeric(20,0)    NOT NULL,
    confirm_sent    numeric(20,0)    NOT NULL,
    send_count        integer        NOT NULL,
    resend_count    integer        NOT NULL,
    time_received    timestamp,
    log_received    numeric(20,0)    NOT NULL,
    confirm_received    numeric(20,0),
    receive_count    integer        NOT NULL,
    rereceive_count    integer        NOT NULL,
    PRIMARY KEY (user_id)
)
 ;
GRANT SELECT on SYS.SYSREMOTEUSER to SYS
 ;
create table SYS.SYSSUBSCRIPTION (
    publication_id     smallint         NOT NULL,
    user_id         smallint         NOT NULL,
    subscribe_by    char(128)    NOT NULL,
    created        numeric(20,0)    NOT NULL,
    started        numeric(20,0),
    PRIMARY KEY (publication_id, user_id, subscribe_by)
)
 ;
GRANT SELECT on SYS.SYSSUBSCRIPTION to SYS
 ;
CREATE table SYS.SYSUSERMESSAGES (
        error    int        NOT NULL,
        uid         smallint         NOT NULL,
        description varchar(255)    NOT NULL,
        langid    smallint    NOT NULL,
        unique (error,langid)
        )
 ;
GRANT SELECT on SYS.SYSUSERMESSAGES TO SYS, PUBLIC
 ;

CREATE table SYS.SYSUSERTYPE (
        type_id        smallint    NOT NULL,
        creator         smallint         NOT NULL,
        domain_id        smallint    NOT NULL,
        nulls        char(1)        NOT NULL,
        width        smallint    NOT NULL,
        scale        smallint    NOT NULL,
        type_name        char(128)    NOT NULL,
        "default"        long varchar    NULL,
        "check"        long varchar    NULL,
        format_str      char(128),
        super_type_id    smallint    NULL,
        unique    (type_name),
        primary key (type_id)
        )
 ;
ALTER TABLE SYS.SYSUSERTYPE ADD FOREIGN KEY super_type_id references
    SYS.SYSUSERTYPE( type_id )
 ;
GRANT SELECT on SYS.SYSUSERTYPE TO SYS, PUBLIC
 ;
CREATE table SYS.SYSEXTENT (
        file_id    smallint    NOT NULL,
            extent_id   smallint        NOT NULL,
            first_page  int             NOT NULL,
            last_page   int             NOT NULL,
            file_name   long varchar    NOT NULL,
        primary key (file_id, extent_id),
        )
 ;
GRANT SELECT on SYS.SYSEXTENT TO SYS, PUBLIC
 ;
CREATE table SYS.SYSREMOTETYPE (
        type_id        smallint        NOT NULL,
        type_name        char(128)       NOT NULL,
        publisher_address    long varchar    NOT NULL,
        remarks        long varchar,
        PRIMARY KEY (type_id)
    )
 ;
GRANT SELECT on SYS.SYSREMOTETYPE to SYS
 ;
end if
go
if @upgrade_type <  3  then
ALTER table SYSPUBLICATION add foreign key for SYSUSERPERM
            ( creator is user_id )
 ;

ALTER table SYSARTICLE add foreign key for SYSPUBLICATION
 ;

ALTER table SYSARTICLE add foreign key for SYSTABLE
 ;

ALTER table SYSARTICLECOL add foreign key for SYSARTICLE
 ;

ALTER table SYSARTICLECOL add foreign key for SYSCOLUMN
 ;

ALTER table SYSREMOTEUSER add foreign key for SYSUSERPERM
 ;

ALTER table SYSSUBSCRIPTION add foreign key for SYSPUBLICATION
 ;

ALTER table SYSSUBSCRIPTION add foreign key for SYSREMOTEUSER
 ;

ALTER table SYSREMOTEUSER add foreign key for SYSREMOTETYPE
 ;
ALTER table SYSUSERTYPE add foreign key for SYSUSERPERM
        (creator is user_id)
 ;
ALTER table SYSUSERTYPE add foreign key for SYSDOMAIN
 ;
ALTER table SYSCOLUMN add foreign key for SYSUSERTYPE
        (user_type is type_id)
 ;
ALTER table SYSEXTENT add foreign key for SYSFILE
 ;
end if
go
if @upgrade_type <=  3  then
    alter table SYS.SYSCOLUMN add format_str char(128) NULL;

    if @upgrade_type =  3  then


    alter table SYS.SYSUSERTYPE add format_str char(128) NULL;
    alter table SYS.SYSUSERTYPE delete foreign key SYSUSERPERM;
    alter table SYS.SYSUSERTYPE
            add foreign key for sysuserperm
            (creator IS user_id);
    alter table SYS.SYSREMOTE delete frequency_units,
            delete send_frequency;
    alter table SYS.SYSREMOTE rename SYSREMOTEUSER;
    end if;

    if @upgrade_type <=  4  then
    alter table SYS.SYSUSERPERM add remotedbaauth char(1);
    update SYS.SYSUSERPERM set remotedbaauth = 'N';
    commit work;
    alter table SYS.SYSUSERPERM
            modify remotedbaauth char(1) NOT NULL;
    end if
end if
go
if @upgrade_type <  3  then
    INSERT INTO SYS.SYSREMOTETYPE ( type_id, type_name, publisher_address )
        VALUES ( 0, 'FILE', '' )
 ;
    INSERT INTO SYS.SYSREMOTETYPE ( type_id, type_name, publisher_address )
        VALUES ( 1, 'MAPI', '' )
 ;
    INSERT INTO SYS.SYSREMOTETYPE ( type_id, type_name, publisher_address )
        VALUES ( 2, 'VIM', '' )
 ;
    INSERT INTO SYS.SYSREMOTETYPE ( type_id, type_name, publisher_address )
        VALUES ( 3, 'SMTP', '' )
 ;
    INSERT INTO SYS.SYSREMOTETYPE ( type_id, type_name, publisher_address )
        VALUES ( 4, 'FTP', '' )
 ;
else
    begin
    declare ftp_count int;
    select count(*) into ftp_count from SYS.SYSREMOTETYPE where type_name = 'FTP';
    if ftp_count = 0 then
        INSERT INTO SYS.SYSREMOTETYPE ( type_id, type_name, publisher_address )
            VALUES ( 4, 'FTP', '' )
    end if
    end
end if
go
if @upgrade_type =  3  then
    insert into SYSREMOTETYPE (type_id, type_name, publisher_address)
        select number(*), address_type, '' from SYSREMOTEUSER
        group by address_type;
    drop index remote_address;
    alter table SYSREMOTEUSER
        add    type_id            smallint,
        add    log_send        numeric(20,0),
        add    frequency        char(1),
        add    send_time        time,
        add    send_count        integer,
        add    receive_count        integer;
    alter table SYSREMOTEUSER
        rename  user_type  to  consolidate;
    update SYSREMOTEUSER u
        set type_id = ( select type_id from SYSREMOTETYPE t
            where t.type_name = u.address_type ),
        consolidate = ( if consolidate = 'C' then 'Y' else 'N' endif ),
        log_send = log_sent,
               frequency = 'A',
               send_count = 0,
               receive_count = 0;
    alter table SYSREMOTEUSER modify type_id NOT NULL,
                  modify log_send NOT NULL,
                  modify frequency NOT NULL,
                  modify send_count NOT NULL,
                  modify receive_count NOT NULL,
                  delete address_type;
    alter table SYSREMOTEUSER add foreign key for SYSREMOTETYPE;
    create unique index remote_address on SYSREMOTEUSER
    ( type_id, address );
end if
go
if @upgrade_type <  5  then
    GRANT DBA to dbo;
    alter table SYS.SYSDOMAIN add type_id    smallint NULL;
    alter table SYS.SYSDOMAIN add "precision"    smallint NULL;
end if
go
UPDATE SYS.SYSDOMAIN set type_id = 5, "precision" = 5 where domain_id = 1
go
UPDATE SYS.SYSDOMAIN set type_id = 4, "precision" = 10 where domain_id = 2
go
UPDATE SYS.SYSDOMAIN set type_id = 2, "precision" = NULL where domain_id = 3
go
UPDATE SYS.SYSDOMAIN set type_id = 7, "precision" = 7 where domain_id = 4
go
UPDATE SYS.SYSDOMAIN set type_id = 8, "precision" = 15 where domain_id = 5
go
UPDATE SYS.SYSDOMAIN set type_id = 9, "precision" = NULL where domain_id = 6
go
UPDATE SYS.SYSDOMAIN set type_id = 1, "precision" = NULL where domain_id = 7
go
UPDATE SYS.SYSDOMAIN set type_id = 1, "precision" = NULL where domain_id = 8
go
UPDATE SYS.SYSDOMAIN set type_id = 12, "precision" = NULL where domain_id = 9
go
UPDATE SYS.SYSDOMAIN set type_id = -1, "precision" = NULL where domain_id = 10
go
UPDATE SYS.SYSDOMAIN set type_id = -2, "precision" = NULL where domain_id = 11
go
UPDATE SYS.SYSDOMAIN set type_id = -4, "precision" = NULL where domain_id = 12
go
UPDATE SYS.SYSDOMAIN set type_id = 11, "precision" = NULL where domain_id = 13
go
UPDATE SYS.SYSDOMAIN set type_id = 10, "precision" = NULL where domain_id = 14
go
UPDATE SYS.SYSDOMAIN set type_id = -6, "precision" = 3 where domain_id = 19
go
UPDATE SYS.SYSDOMAIN set type_id = -5, "precision" = 20 where domain_id = 20
go
UPDATE SYS.SYSDOMAIN set type_id = -9, "precision" = 10 where domain_id = 21
go
UPDATE SYS.SYSDOMAIN set type_id = -10, "precision" = 5 where domain_id = 22
go
UPDATE SYS.SYSDOMAIN set type_id = -11, "precision" = 21 where domain_id = 23
go
UPDATE SYS.SYSDOMAIN set type_id = -7, "precision" = 1 where domain_id = 24
go
UPDATE SYS.SYSDOMAIN set type_id = 0, "precision" = NULL where domain_id = 25
go
UPDATE SYS.SYSDOMAIN set type_id = -4, "precision" = NULL where domain_id = 26
go
UPDATE SYS.SYSDOMAIN set type_id = 2, "precision" = NULL where domain_id = 27
go
UPDATE SYS.SYSDOMAIN set type_id = -2, "precision" = NULL where domain_id = 28
go
if @upgrade_type <  5  then
    alter table SYS.SYSDOMAIN modify type_id NOT NULL;
end if
go
import saopts.sql
if @upgrade_type <  6  then
    ALTER TABLE SYS.SYSCOLPERM
        ADD privilege_type smallint NULL,
        ADD is_grantable char(1) NULL;
    UPDATE SYS.SYSCOLPERM
        SET privilege_type = 8, is_grantable = 'N';
    UPDATE SYS.SYSCOLPERM SET is_grantable = 'Y'
        WHERE EXISTS (SELECT 1 FROM SYS.SYSTABLEPERM TPERMS
            WHERE TPERMS.ttable_id = SYS.SYSCOLPERM.table_id
            AND TPERMS.grantee = SYS.SYSCOLPERM.grantee
            AND TPERMS.grantor = SYS.SYSCOLPERM.grantor
            AND TPERMS.updatecols = 'G');
    ALTER TABLE SYS.SYSCOLPERM
        MODIFY privilege_type NOT NULL;
    ALTER TABLE SYS.SYSCOLPERM
        MODIFY is_grantable NOT NULL;
    ALTER TABLE SYS.SYSCOLPERM
        DELETE PRIMARY KEY;
    ALTER TABLE SYS.SYSCOLPERM
        ADD PRIMARY KEY
        ( table_id, grantee, grantor, column_id, privilege_type );
end if
go
if @upgrade_type <  7  then
    if @upgrade_type >  2  then
    alter table SYS.SYSARTICLE add query char(1);
    update SYS.SYSARTICLE set query = 'N';
    alter table SYS.SYSARTICLE modify query NOT NULL;
    end if
end if
go
if @upgrade_type <=  8  then
CREATE TABLE SYS.SYSSQLSERVERTYPE (
        ss_user_type        smallint    NOT NULL,
        ss_domain_id        smallint      NOT NULL,
        ss_type_name        varchar(30)    NOT NULL,
        primary_sa_domain_id    smallint    NOT NULL,
        primary_sa_user_type    smallint    NULL,
        PRIMARY KEY ( ss_user_type )
    )
 ;
GRANT SELECT on SYS.SYSSQLSERVERTYPE TO SYS, PUBLIC
 ;
CREATE TABLE SYS.SYSTYPEMAP (
        ss_user_type        smallint     NOT NULL,
        sa_domain_id        smallint     NOT NULL,
        sa_user_type        smallint    NULL,
        nullable            char(1)    NULL
    )
 ;
GRANT SELECT on SYS.SYSTYPEMAP TO SYS, PUBLIC
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  1 ,    47,    'char',         8 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  2 ,    39,    'varchar',     9 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  3 ,    45,    'binary',     11 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  4 ,37,    'varbinary',     11 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  5 ,    48,    'tinyint',     19 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  6 ,    52,    'smallint',     1 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  7 ,    56,    'int',         2 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  8 ,    62,    'float',     5 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  10 ,    63,    'numeric',     3 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  11 ,    60,    'money',     3 ,        101 )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  12 ,    61,    'datetime',     13 ,   103 )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  13 ,    38,    'intn',         2 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  14 ,    109,    'floatn',     5 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  15 ,    111,    'datetimn',     13 ,   103 )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  16 ,    50,    'bit',         24 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  17 ,    110,    'moneyn',     3 ,        101 )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  18 ,    39,    'sysname',     9 ,        108 )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  19 ,    35,    'text',         10 , 105 )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  20 ,    34,    'image',     12 ,  106 )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  21 ,122,    'smallmoney',     3 ,        102 )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  22 ,58,    'smalldatetime', 13 ,   104 )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  23 ,    59,    'real',         4 ,        NULL )
 ;
 ;
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  26 ,    55,    'decimal',     3 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  27 ,    106,    'decimaln',     3 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  28 ,    108,    'numericn',     3 ,        NULL )
 ;
INSERT INTO SYS.SYSSQLSERVERTYPE
    VALUES (  80 ,37,    'timestamp',     13 ,   NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  6 ,     1 ,        NULL,    NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  7 ,     2 ,        NULL,    'N' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  13 ,     2 ,        NULL,    'Y' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  10 ,     3 ,        NULL,    'N' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  28 ,     3 ,        NULL,    'Y' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  23 ,     4 ,        NULL,    NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  8 ,     5 ,        NULL,    'N' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  14 ,     5 ,        NULL,    'Y' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  12 ,     6 ,        NULL,    'N' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  15 ,     6 ,        NULL,    'Y' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  1 ,     7 ,        NULL,    NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  2 ,     9 ,        NULL,    NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  1 ,     8 ,        NULL,    NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  19 ,     10 , NULL,    NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  3 ,     11 ,        NULL,    NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  20 ,     12 ,  NULL,    NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  80 , 13 ,   NULL,    NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  12 ,     14 ,        NULL,    'N' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  15 ,     14 ,        NULL,    'Y' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  5 ,     19 ,        NULL,    NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  16 ,     24 ,        NULL,    NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  11 ,     3 ,        101,     'N' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  17 ,     3 ,        101,     'Y' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  21 , 3 ,    102,     NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  12 ,  13 ,   103,     'N' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  15 ,  13 ,   103,     'Y' )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  22 , 13 ,  104,  NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  19 ,     10 , 105,     NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  20 ,     12 ,  106,     NULL )
 ;
INSERT INTO SYS.SYSTYPEMAP
    VALUES (  18 ,     9 ,        108,     NULL )
 ;
COMMIT WORK
 ;
ALTER table SYSTYPEMAP add foreign key for SYSDOMAIN
        (sa_domain_id is domain_id)
 ;
end if
go
if @upgrade_type <=  9  then
CREATE TABLE SYS.SYSLOGIN (
        integrated_login_id        char(128)     NOT NULL,
        login_uid             smallint          NOT NULL,
        remarks            long varchar,
        PRIMARY KEY ( integrated_login_id )
    )
 ;
end if
go
if @upgrade_type <=  10  then
    alter table SYS.SYSPROCPARM add user_type int;
    create table SYS.SYSJAR (
            jar_id         smallint        not null,
            creator         smallint         not null,
            jar_name    long varchar    not null,
            jar_file    long varchar,
            create_time    timestamp    not null,
            update_time    timestamp    not null,
            remarks        long varchar,
            primary key ( jar_id )
        );
    create unique index jar_name on SYS.SYSJAR ( jar_name );

    create table SYS.SYSJAVACLASS (
            class_id         smallint     not null,
            replaced_by         smallint ,
            creator         smallint         not null,
            jar_id         smallint ,
            type_id        smallint,
            class_name        long varchar    not null,
            public        char(1)        not null,
            component_id     smallint ,
            create_time        timestamp    not null,
            update_time        timestamp    not null,
            class_descriptor    long binary,
            remarks            long varchar,
            primary key ( class_id ),
            foreign key ( replaced_by )
                references SYS.SYSJAVACLASS( class_id )
        );

    create table SYS.SYSJARCOMPONENT (
            component_id         smallint     not null,
            jar_id             smallint ,
            component_name        long varchar,
            component_type        char(1),
            create_time            timestamp    not null,
            contents            long binary,
            remarks            long varchar,
            primary key ( component_id )
        );

    if @upgrade_type >=  3  then
    alter table SYSUSERTYPE add super_type_id smallint NULL;

    alter table SYSUSERTYPE add foreign key super_type_id references
            SYSUSERTYPE( type_id );
    end if;
    alter table SYSJARCOMPONENT add foreign key for SYSJAR;

    alter table SYSJAVACLASS add foreign key for SYSJAR;

    alter table SYSJAVACLASS add foreign key for SYSJARCOMPONENT;

    alter table SYSJAVACLASS add foreign key for SYSUSERPERM
        ( creator is user_id );

    alter table SYSJAVACLASS add foreign key for SYSUSERTYPE
end if
go
if @upgrade_type <=  10  then
    alter table SYS.SYSCOLUMN add column_type char(1) NULL;
    commit work;
end if
go
ALTER TABLE SYS.SYSTABLE ADD existing_obj    char(1)
go
ALTER TABLE SYS.SYSTABLE ADD remote_location    long varchar
go
ALTER TABLE SYS.SYSTABLE ADD remote_objtype    char(1)
go
ALTER TABLE SYS.SYSTABLE ADD srvid        int
go
ALTER TABLE SYS.SYSCOLUMN ADD remote_name varchar(128)
go
ALTER TABLE SYS.SYSCOLUMN ADD remote_type  smallint
go
if @upgrade_type <=  10  then
    create table SYS.SYSCOLLATIONMAPPINGS (
            collation_label    char(10)    not null,
        collation_name  char(128)        not null,
        cs_label    char(128),
        so_case_label    char(128),
        so_caseless_label char(128),
        jdk_label char(128),
        primary key ( collation_label ),
    );
    commit work;
end if
go
import collsqmp.sql
import custmap.sql
commit work;
import sysviews.sql
CREATE TABLE SYS.DUMMY ( dummy_col INT NOT NULL )
go
INSERT INTO SYS.DUMMY VALUES ( 0 )
go
GRANT SELECT on SYS.DUMMY to SYS
go

CREATE TABLE SYSINFO (
        page_size        smallint    NOT NULL,
        encryption        char(1)        NOT NULL,
        blank_padding    char(1)        NOT NULL,
        case_sensitivity    char(1)        NOT NULL,
        default_collation    char(10),
        database_version    smallint    NOT NULL,
        classes_version    char(10)
    )
go
INSERT INTO SYSINFO VALUES
    (@page_size,
     @encryption,
     @blank_padding,
     @case_sensitivity,
     @default_collation,
     @database_version,
     '1.1.3' )
go
GRANT SELECT on SYS.SYSINFO to SYS
go
COMMIT WORK
go
if @upgrade_type <=  10  then
    update SYS.SYSCOLUMN set column_type = 'R';
    commit work;
    alter table SYS.SYSCOLUMN modify column_type NOT NULL;
    commit work;
end if
go
if @upgrade_type <  4  then
CREATE DOMAIN money            numeric(19,4)
 ;
CREATE DOMAIN smallmoney    numeric(10,4)
 ;
CREATE DOMAIN datetime            timestamp
 ;
CREATE DOMAIN smalldatetime        timestamp
 ;
CREATE DOMAIN text        long varchar
 ;
CREATE DOMAIN image            long binary
 ;
CREATE DOMAIN oldbit            tinyint
 ;
CREATE DOMAIN sysname            varchar(30) not null
 ;
end if
go
import  dbovws50.sql
if @upgrade_type <  3  then
CREATE UNIQUE INDEX publication_name ON SYSPUBLICATION
    ( publication_name, creator )
 ;

CREATE UNIQUE INDEX remote_address ON SYSREMOTEUSER
    ( type_id, address )
 ;

CREATE INDEX remote_frequency ON SYSREMOTEUSER
    ( frequency, send_time )
 ;

CREATE UNIQUE INDEX remote_type ON SYSREMOTETYPE
    ( type_name )
 ;

COMMIT WORK
 ;
end if
go
if not exists ( select * from SYS.SYSUSERPERM where user_name = 'rs_systabgroup' ) then
    if @upgrade_type <=  10  then

grant connect to rs_systabgroup
 ;
grant group to rs_systabgroup
 ;
grant resource to rs_systabgroup
 ;
commit work
 ;

create table rs_systabgroup.rs_lastcommit (
    origin          int         primary key,
    origin_qid      binary(36),
    secondary_qid   binary(36),
    origin_time     datetime,
    commit_time     datetime )
 ;

create table rs_systabgroup.rs_threads (
    id            int        primary key,
    seq            int )
 ;
commit work
 ;
    end if
end if
go
import  sybprc50.sql
CREATE TABLE SYS.SYSSERVERS (
        srvid        int     NOT NULL,
        srvname        varchar(128)    NOT NULL,
        srvclass        long varchar    NOT NULL,
        srvinfo        long varchar,
        srvreadonly        char(1)        NOT NULL,
        PRIMARY KEY ( srvid )
    )
go
GRANT SELECT on SYS.SYSSERVERS TO SYS
go
CREATE TABLE SYS.SYSEXTERNLOGINS (
        user_id         smallint      NOT NULL,
        srvid        int     NOT NULL,
        remote_login    varchar(128),
        remote_password    varbinary(128),
        PRIMARY KEY ( user_id, srvid )
    )
go
GRANT SELECT on SYS.SYSEXTERNLOGINS TO SYS
go
alter table SYS.SYSEXTERNLOGINS add foreign key for SYS.SYSUSERPERM
    ( user_id is user_id )
go

alter table SYS.SYSEXTERNLOGINS add foreign key for SYS.SYSSERVERS
    ( srvid is srvid )
go

alter table SYS.SYSTABLE add foreign key for SYS.SYSSERVERS
    ( srvid is srvid )
go
commit work
go
if @upgrade_type <=  3  then



    begin
    declare username VARCHAR(130);
    declare password VARCHAR(130);
    declare pwdcurs insensitive cursor for select user_name, password
        from SYS.SYSUSERPERM where password is not NULL;

    alter table SYS.SYSUSERPERM modify password binary(128) NULL;
    commit;

    open pwdcurs with hold;
    PassWordLoop:
     loop
        fetch next pwdcurs into username, password;
        if SQLSTATE = '02000' then
           leave PassWordLoop;
        end if;
        execute immediate with quotes 'grant connect to "' || username ||
                  '" identified by "' || password || '"';
    end loop;
    close pwdcurs;

    alter table SYS.SYSUSERPERM modify password binary(36) NULL;
    commit;
    end
end if
go
if @upgrade_type <=  10  then
    create table dbo.EXCLUDEOBJECT (
        name char( 128 ),
        type char(1),
        PRIMARY KEY( name, type )
        );
    grant select on dbo.EXCLUDEOBJECT to PUBLIC;
    commit;
end if
go
import mkexclud.sql
import instjava.sql
commit
go
DROP VARIABLE @page_size
go
DROP VARIABLE @encryption
go
DROP VARIABLE @blank_padding
go
DROP VARIABLE @case_sensitivity
go
DROP VARIABLE @default_collation
go
DROP VARIABLE @database_version
go
DROP VARIABLE @sysinfo_exists
go
DROP VARIABLE @avoid_view_collisions
go
DROP VARIABLE @install_java_classes
go
DROP VARIABLE @flags