Creating the PPUPDATE User

Automatic update from database server requires the PPUPDATE user existence, whose schema is used to store distribution file of the Foresight Analytics Platform on update.

The PPUPDATE user is used to store distribution file of Foresight Analytics Platform on database server. The distribution file is saved to the database of this user and is taken from it on updating Foresight Analytics Platform on client workstations. Depending on DBMS version, scripts that are used to create the PPUPDATE user are slightly different.

NOTE. Creating the PPUPDATE user is only available for the Microsoft SQL Server and Oracle DBMS.

Creating the PPUPDATE User on MS SQL Server

A user can be created using the following script:

execute sp_addlogin @loginame = 'PPUPDATE', @passwd = 'PPUPDATE', @defdb = 'master'

where:

PPUPDATE. User name and password.

To run the script, the Query Analyzer application can be used, which is included in the standard package for both the front and back ends of Microsoft SQL Server DBMS.

The user must be created by the DBMS administrator.

The following script can be used to create a database:

use master;

create database PPUPDATE;

where:

PPUPDATE. Name of the created database.

The script must be executed to grant permissions for the database to the PPUPDATE user:

use PPUPDATE

exec sp_changedbowner PPUPDATE

where:

PPUPDATE. Database and user name.

The first script and the second script must be run consecutively to create tables in the database.

Script1:

create table B_UPD (

LNAME varchar(127) not null,

UPD numeric(8,0) not null,

STAMP datetime not null,

FNAME varchar(127) not null,

DNAME varchar(127) null,

FSIZE numeric(24,4) null

)

alter table B_UPD

add constraint PK_B_UPD primary key (LNAME, UPD)

create index IX_B_UPD on B_UPD (

LNAME

)

create table B_UPDD (

LNAME varchar(127) not null,

UPD numeric(8,0) not null,

DAT image null

)

alter table B_UPDD

add constraint PK_B_UPDD primary key (LNAME, UPD)

Script2:

if exists (select 1

from sysobjects

where id = object_id('tu_b_upd')

and type = 'TR')

drop trigger tu_b_upd

if exists (select 1

from sysobjects

where id = object_id('td_b_upd')

and type = 'TR')

drop trigger td_b_upd

if exists (select 1

from sysobjects

where id = object_id('ti_b_updd')

and type = 'TR')

drop trigger ti_b_updd

if exists (select 1

from sysobjects

where id = object_id('tu_b_updd')

and type = 'TR')

drop trigger tu_b_updd

/* Update trigger "tu_b_upd" for table "B_UPD" */

create trigger tu_b_upd on B_UPD for update as

begin

declare

@numrows int,

@numnull int,

@errno int,

@errmsg varchar(255)

select @numrows = @@rowcount

if @numrows = 0

return

/* Cannot modify parent code in "B_UPD" if children still exist in "B_UPDD" */

if update(LNAME) or

update(UPD)

begin

if exists (select 1

from B_UPDD t2, inserted i1, deleted d1

where t2.LNAME = d1.LNAME

and t2.UPD = d1.UPD

and (i1.LNAME != d1.LNAME

or i1.UPD != d1.UPD))

begin

select @errno = 50005,

@errmsg = 'Children still exist in "B_UPDD". Cannot modify parent code in "B_UPD".'

goto error

end

return

/* Errors handling */

error:

raiserror (N'%d: %s', 10, 1, @errno, @errmsg)

rollback transaction

end

/* Delete trigger "td_b_upd" for table "B_UPD" */

create trigger td_b_upd on B_UPD for delete as

begin

declare

@numrows int,

@errno int,

@errmsg varchar(255)

select @numrows = @@rowcount

if @numrows = 0

return

/* Delete all children in "B_UPDD" */

delete B_UPDD

from B_UPDD t2, deleted t1

where t2.LNAME = t1.LNAME

and t2.UPD = t1.UPD

return

/* Errors handling */

error:

raiserror (N'%d: %s', 10, 1, @errno, @errmsg)

rollback transaction

end

/* Insert trigger "ti_b_updd" for table "B_UPDD" */

create trigger ti_b_updd on B_UPDD for insert as

begin

declare

@numrows int,

@numnull int,

@errno int,

@errmsg varchar(255)

select @numrows = @@rowcount

if @numrows = 0

return

/* Parent "B_UPD" must exist when inserting a child in "B_UPDD" */

if update(LNAME) or

update(UPD)

begin

if (select count(*)

from B_UPD t1, inserted t2

where t1.LNAME = t2.LNAME

and t1.UPD = t2.UPD) != @numrows

begin

select @errno = 50002,

@errmsg = 'Parent does not exist in "B_UPD". Cannot create child in "B_UPDD".'

goto error

end

return

/* Errors handling */

error:

raiserror (N'%d: %s', 10, 1, @errno, @errmsg)

rollback transaction

end

/* Update trigger "tu_b_updd" for table "B_UPDD" */

create trigger tu_b_updd on B_UPDD for update as

begin

declare

@numrows int,

@numnull int,

@errno int,

@errmsg varchar(255)

select @numrows = @@rowcount

if @numrows = 0

return

/* Parent "B_UPD" must exist when updating a child in "B_UPDD" */

if update(LNAME) or

update(UPD)

begin

if (select count(*)

from B_UPD t1, inserted t2

where t1.LNAME = t2.LNAME

and t1.UPD = t2.UPD) != @numrows

begin

select @errno = 50003,

@errmsg = 'B_UPD" does not exist. Cannot modify child in "B_UPDD".'

goto error

end

return

/* Errors handling */

error:

raiserror (N'%d: %s', 10, 1, @errno, @errmsg)

rollback transaction

end

After all the steps have been completed successfully, the PPUPDATE user can be used to create a copy of platform version to be updated at database server.

Creating the PPUPDATE User on Oracle Server

A user can be created using the following script:

CREATE USER PPUPDATE IDENTIFIED BY PPUPDATE DEFAULT TABLESPACE Users TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;

GRANT CONNECT TO PPUPDATE;

GRANT RESOURCE TO PPUPDATE;

REVOKE UNLIMITED TABLESPACE FROM PPUPDATE;

ALTER USER PPUPDATE QUOTA UNLIMITED ON Users;

COMMIT;

Where Users is the name of tablespace, in which the PPUPDATE user is to be created.

The script must be run using standard database management methods under the name of a user who has permissions to create users at the database server.

NOTE. In Oracle 12c Release 1 or later the "resource" role does not give the "unlimited" privilege. The attempt to get this privilege will result in error. If Oracle 12c Release 1 server or later is used, skip the command:
REVOKE UNLIMITED TABLESPACE FROM PPUPDATE

Use standard database management methods to run the following script under the name of previously created PPUPDATE user:

create table B_UPD (

LNAME VARCHAR2(127) not null,

UPD NUMBER(8,0) not null,

STAMP DATE not null,

FNAME VARCHAR2(127) not null,

DNAME VARCHAR2(127),

FSIZE NUMBER(24,4)

)

alter table B_UPD

add constraint PK_B_UPD primary key (LNAME, UPD)

create table B_UPDD (

LNAME VARCHAR2(127) not null,

UPD NUMBER(8,0) not null,

DAT LONG RAW

)

alter table B_UPDD

add constraint PK_B_UPDD primary key (LNAME, UPD)

alter table B_UPDD

add constraint FK_UPDD2UPD foreign key (LNAME, UPD)

references B_UPD (LNAME, UPD)

on delete cascade

After all the steps have been completed successfully, the PPUPDATE user can be used to create a copy of Foresight Analytics Platform version to be updated at database server.