Create a userAutoupdate from database server requires the PPUPDATE user, whose schema is used to store distribution file of Foresight Analytics Platform on update.
The PPUPDATE user is used to store distribution file of Foresight Analytics Platform at 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.
Creating a user requires:
A user can be created using the following script:
execute sp_addlogin @loginame = 'PPUPDATE', @passwd = 'PPUPDATE', @defdb = 'master'
go
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.
A user must be created by the DBMS administrator.
The following script can be used to create a database:
use master;
go
create database PPUPDATE;
go
where:
PPUPDATE. Name of the created database.
The script must executed to grant permissions for the database to the PPUPDATE user:
use PPUPDATE
go
exec sp_changedbowner PPUPDATE
go
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
)
go
alter table B_UPD
add constraint PK_B_UPD primary key (LNAME, UPD)
go
create index IX_B_UPD on B_UPD (
LNAME
)
go
create table B_UPDD (
LNAME varchar(127) not null,
UPD numeric(8,0) not null,
DAT image null
)
go
alter table B_UPDD
add constraint PK_B_UPDD primary key (LNAME, UPD)
go
Script2:
if exists (select 1
from sysobjects
where id = object_id('tu_b_upd')
and type = 'TR')
drop trigger tu_b_upd
go
if exists (select 1
from sysobjects
where id = object_id('td_b_upd')
and type = 'TR')
drop trigger td_b_upd
go
if exists (select 1
from sysobjects
where id = object_id('ti_b_updd')
and type = 'TR')
drop trigger ti_b_updd
go
if exists (select 1
from sysobjects
where id = object_id('tu_b_updd')
and type = 'TR')
drop trigger tu_b_updd
go
/* 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
end
return
/* Errors handling */
error:
raiserror (N'%d: %s', 10, 1, @errno, @errmsg)
rollback transaction
end
go
/* 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
go
/* 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
end
return
/* Errors handling */
error:
raiserror (N'%d: %s', 10, 1, @errno, @errmsg)
rollback transaction
end
go
/* 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
end
return
/* Errors handling */
error:
raiserror (N'%d: %s', 10, 1, @errno, @errmsg)
rollback transaction
end
go
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 a user requires:
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 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. On using Oracle 12c Release 1 server or later 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.
See also:
Updating Foresight Analytics Platform