NGAS DB UPGRADE PLAN FOR FEBRUARY 2003:
=======================================
In this document the DB changes needed in connection with the upgrade of the 
NGAS system in Garching and at La Silla.

The parties invovled in the DB upgrade are:

o ESOECF, EDAT (Daniel Brandt).
o OLASLS, Anton Scherml
o ESOECF/OLASLS, Jens Knudstrup


==DB Table Changes:
-------------------
The following changes have been introduced to the NGAS DB:


1. Table: "ngas_disks":
o Columns Removed:
protected, associated_disk_id

o Columns Added:
last_check              datetime        null
last_host_id            varchar(32)     null


2. Table: "ngas_files":
No changes.


3. Table: "ngas_hosts":
o Columns with changed names:
ngas_port->srv_port
ngas_retrieve->srv_retrieve
ngas_version->srv_version

o Columns Removed:
None.

o Columns Added:
srv_archive             tinyint         null
srv_process             tinyint         null
srv_data_checking       tinyint         null
srv_state               varchar(20)     null
srv_suspended           tinyint         null
srv_req_wake_up_srv     varchar(32)     null
srv_req_wake_up_time    datetime        null


4. Table: "ngas_disks_hist" - NEW TABLE:
The contents of this table is maintained by NG/AMS. It is used to store
information about important events in the life-time of a disk. Once 
contents has been inserted, it is never deleted. It should be replicated
from LS. See SQL script below how to create this table.


5. Table: "ngas_subscribers" - NEW TABLE:
The contents of this table is maintained by NG/AMS. Each NG/AMS Server in
connection with the NGAS DB uses this for keeping a persistent image of the
Subscribers. NG/AMS will add and remove entries from this table. 

It is not necessary to replication this from LS (Susbcription Service not
used at LS). See SQL script below how to create this table.


6. Table: "ngas_subscr_back_log" - NEW TABLE:
The contents of this table is maintained by NG/AMS. Each NG/AMS Server in
connection with the NGAS DB uses this for keeping a persistent image of files
that could not be delivered to Subscribers. NG/AMS will add and remove 
entries from this table. 

It is not necessary to replication this from LS (Susbcription 


==Replication:
--------------
The present implementation of the replication should be checked by EDAT
and the DB changes reflected also in the context of the replication. 
If explicit information is needed in this connection, it's maybe easier to
get together and to wirk out the details.


==Information to be corrected:
------------------------------
ngas_file.format: ngas/fits -> image/x-fits


==SQL Scripts:
--------------
=SQL script to create table "ngas_disks_hist":

/* --------------------------------------------------------------------- */
print "Creating ngas_disks_hist table"
go
Create table ngas_disks_hist
(
        disk_id                 varchar(128)    not null,
        hist_date               datetime        not null,
        hist_origin             varchar(64)     not null,
        hist_synopsis           varchar(255)    not null,
        hist_descr_mime_type    varchar(64)     null,
        hist_descr              text            null
)
go
print "Create index on ngas_disks_hist"
create index ngas_disks_hist_disk_id  on ngas_disks_hist(disk_id)
create index ngas_disks_hist_date     on ngas_disks_hist(hist_date)
create index ngas_disks_hist_origin   on ngas_disks_hist(hist_origin)
go
print "Granting"
grant insert, update, select on ngas_disks_hist to ngas
go
grant select on ngas_disks_hist to public
go
/* --------------------------------------------------------------------- */

=SQL script to create table "ngas_subscribers":

/* --------------------------------------------------------------------- */
print "Creating ngas_subscribers table"
go
Create table ngas_subscribers
(
        host_id                         varchar(32)     not null,
        srv_port                        int             not null,
        subscr_prio                     tinyint         not null,
        subscr_id                       varchar(255)    not null,
        subscr_url                      varchar(255)    not null,
        subscr_start_date               datetime        null,
        subscr_filter_plugin            varchar(64)     null,
        subscr_filter_plugin_pars       varchar(128)    null,
        last_file_ingestion_date        datetime        null
)
go
print "Create index on ngas_subscribers"
create unique index subscr_id_idx on ngas_subscribers(subscr_id)
go
create unique index host_id_srv_port_idx on ngas_subscribers(host_id, srv_port)
go
print "Granting"
grant insert, update, select on ngas_subscribers to ngas
go
grant select on ngas_subscribers to public
go
/* --------------------------------------------------------------------- */

=SQL script to create table "ngas_subscr_back_log":

/* --------------------------------------------------------------------- */
print "Creating ngas_subscr_back_log table"
go
Create table ngas_subscr_back_log
(
        host_id                         varchar(32)     not null,
        srv_port                        int             not null,
        subscr_id                       varchar(255)    not null,
        subscr_url                      varchar(255)    not null,
        file_id                         varchar(64)     not null,
        file_name                       varchar(255)    not null,
        file_version                    int             not null,
        ingestion_date                  datetime        not null,
        format                          varchar(32)     not null
)
go
print "Granting"
grant insert, update, select on ngas_subscr_back_log to ngas
go
grant select on ngas_subscr_back_log to public
go
/* --------------------------------------------------------------------- */


--- oOo ---