text: --
  -- fgapiserver_db.sql
  --
  -- Copyright (c) 2015:
  -- Istituto Nazionale di Fisica Nucleare (INFN), Italy
  --
  -- Licensed under the Apache License, Version 2.0 (the "License");
  -- you may not use this file except in compliance with the License.
  -- You may obtain a copy of the License at
  --
  --     http://www.apache.org/licenses/LICENSE-2.0
  --
  -- Unless required by applicable law or agreed to in writing, software
  -- distributed under the License is distributed on an "AS IS" BASIS,
  -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  -- See the License for the specific language governing permissions and
  -- limitations under the License.
  --
  -- Script that creates FutureGateway APIServer
  --
  -- Author: riccardo.bruno@ct.infn.it
  -- Version: %VERSION%
  --
  --
  drop database if exists fgapiserver;
  create database fgapiserver;
  
  use fgapiserver;
  
  -- Application
  create table application (
      id           int unsigned not null auto_increment -- Application id
     ,name         varchar(64) not null                 -- Application name
     ,description  varchar(256) not null                -- Application description
     ,outcome      varchar(32)  not null                -- Application outcome (JOB,RESOURCE,...)
     ,creation     datetime                             -- Application creation timestamp
     ,enabled      boolean default false                -- Enabled application flag
     ,primary key(id)
  );
  
  set session sql_mode='NO_AUTO_VALUE_ON_ZERO';
  insert into application (id,name,description,outcome,creation,enabled)
  values (0,"infrastructures","unassigned infrastructure","INFRA",now(), false);
  insert into application (id,name,description,outcome,creation,enabled)
  values (1,"hostname","hostname tester application","JOB",now(),true);
  insert into application (id,name,description,outcome,creation,enabled)
  values (2,"SayHello","A more complex app using I/O Sandboxing","JOB",now(),true);
  
  -- Application parameters
  create table application_parameter (
      app_id        int unsigned not null  -- application id
     ,param_id      int unsigned not null  -- parameter id
     ,pname         varchar(64)  not null  -- parameter name
     ,pvalue        varchar(256)           -- parameter value
     ,pdesc         varchar(1024)          -- parameter description
     ,primary key(app_id,param_id)
     ,foreign key (app_id) references application(id)
  );
  
  -- Parameters for application helloworld
  -- App 1
  insert into application_parameter (app_id,param_id,pname,pvalue) values (1,1,'jobdesc_executable','/bin/hostname');
  insert into application_parameter (app_id,param_id,pname,pvalue) values (1,2,'jobdesc_arguments','-f');
  insert into application_parameter (app_id,param_id,pname,pvalue) values (1,3,'jobdesc_output','stdout.txt');
  insert into application_parameter (app_id,param_id,pname,pvalue) values (1,4,'jobdesc_error','stderr.txt');
  insert into application_parameter (app_id,param_id,pname,pvalue) values (1,5,'target_executor','GridEngine');
  -- App 2
  insert into application_parameter (app_id,param_id,pname,pvalue) values (2,1,'jobdesc_executable','/bin/bash');
  insert into application_parameter (app_id,param_id,pname,pvalue) values (2,2,'jobdesc_arguments','sayhello.sh');
  insert into application_parameter (app_id,param_id,pname,pvalue) values (2,3,'jobdesc_output','sayhello.out');
  insert into application_parameter (app_id,param_id,pname,pvalue) values (2,4,'jobdesc_error','sayhello.err');
  insert into application_parameter (app_id,param_id,pname,pvalue) values (2,5,'target_executor','GridEngine');
  
  -- Application files
  create table application_file (
      app_id        int unsigned not null
     ,file_id       int unsigned not null -- progressive file number for each application
     ,file          varchar(256) not null -- name of the application file
     ,path          varchar(256) not null -- full path to the application file
     ,override      boolean default false -- true if this file overrides user uploads
     ,primary key(app_id,file_id)
     ,foreign key (app_id) references application(id)
  );
  
  -- Files associated to application sayhello
  insert into application_file (app_id,file_id,file,path,override) values (2,1,'sayhello.sh' ,'apps/sayhello',false);
  insert into application_file (app_id,file_id,file,path,override) values (2,2,'sayhello.txt','apps/sayhello',false);
  
  -- Infrastructure
  create table infrastructure (
      id           int unsigned not null auto_increment    -- Infrastructure id
     ,app_id       int unsigned not null                   -- Infrastructure app_id
     ,name         varchar(256) not null                   -- Infrastructure name
     ,description  varchar(256) not null                   -- Infrastructure description
     ,creation     datetime not null                       -- Creation timestamp
     ,enabled      boolean default false not null          -- Enabled infrastructure flag
     ,vinfra       boolean default false not null          -- True if it is a virtual infrastructure
     ,primary key(id,app_id)
     ,foreign key(app_id) references application(id)
     ,index(app_id)
  );
  
  -- Infrastructure parameter
  create table infrastructure_parameter (
      infra_id     int unsigned not null -- Infrastructure Id (see infrastructure.id)
     ,param_id     int unsigned not null -- Parameter id
     ,pname        varchar(64)  not null -- Parameter name
     ,pvalue       varchar(256) not null -- Parameter value
     ,pdesc        varchar(1024)         -- App. parameter description as in specs.
     ,primary key(infra_id,param_id)
     -- There is no foreign key infrastructure.id for infra_id
     -- since it causes problems while removing records in
     -- infrastructure table. The key gets a violation even
     -- other records is the infrastructure table exist having
     -- the same infrastructure.id value
  );
  
  -- Infra for helloworld app@csgfsdk
  insert into infrastructure (id,app_id,name,description,creation,enabled)
  values (1,1,"hello@csgfsdk","hostname application localhost (SSH)",now(),true);
  -- Infra for sayhello app@csgfsdk
  insert into infrastructure (id,app_id,name,description,creation,enabled)
  values (1,2,"sayhello@csgfsdk","sayhello application localhost (SSH)",now(),true);
  -- Infra for sayhello app@nebula
  insert into infrastructure (id,app_id,name,description,creation,enabled)
  values (2,2,"sayhello@nebula","hostname application nebula-1 (rOCCI)",now(),false);
  -- Infra for helloworld app@eumed
  insert into infrastructure (id,app_id,name,description,creation,enabled)
  values (3,1,"hello@eumed","hostname application eumed (wms)",now(),false);
  
  -- Parameters for infrastructure helloworld@csgfsdk (SSH)
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (1,1,'jobservice'     ,'ssh://90.147.74.95');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (1,2,'username'       ,'jobtest');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (1,3,'password'       ,'Xvf56jZ751f');
  
  -- Parameters for infrastructure sayhello@nebula (rOCCI)
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (2, 1,'jobservice'      ,'rocci://nebula-server-01.ct.infn.it:9000');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (2, 2,'os_tpl'          ,'uuid_chain_reds_generic_vm_centos_6_6_kvm_103');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (2, 3,'resource_tpl'    ,'small');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (2, 4,'attributes_title','sayhello');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (2, 5,'eToken_host'     ,'etokenserver.ct.infn.it');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (2, 6,'eToken_port'     ,'8082');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (2, 7,'eToken_id'       ,'bc681e2bd4c3ace2a4c54907ea0c379b');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (2, 8,'voms'            ,'vo.chain-project.eu');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (2, 9,'voms_role'       ,'vo.chain-project.eu');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (2,10,'rfc_proxy'       ,'true');
  
  -- Parameters for infrastructure sayhello@eumed (wms)
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (3,1,'jobservice'     ,'wms://wms.ulakbim.gov.tr:7443/glite_wms_wmproxy_server');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (3,2,'bdii'           ,'ldap://bdii.eumedgrid.eu:2170');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (3,3,'eToken_host'    ,'etokenserver2.ct.infn.it');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (3,4,'eToken_port'    ,'8082');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (3,5,'eToken_id'      ,'bc681e2bd4c3ace2a4c54907ea0c379b');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (3,6,'voms'           ,'eumed');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (3,7,'voms_role'      ,'eumed');
  insert into infrastructure_parameter (infra_id,param_id,pname,pvalue) values (3,8,'rfc_proxy'      ,'false');
  
  -- Task table
  create table task (
       id           int unsigned not null auto_increment
      ,creation     datetime not null
      ,last_change  datetime not null
      ,app_id       int unsigned not null
      ,description  varchar(256) not null -- Human readable hob identifier
      ,status       varchar(32)  not null -- The current status of the task
      ,iosandbox    varchar(256)          -- Path to the task IO Sandbox
      ,user         varchar(256) not null -- username submitting the task
      ,primary key(id)
      ,foreign key (app_id) references application(id)
  );
  
  -- Task arguments
  create table task_arguments (
       task_id      int unsigned not null       -- id of the task owning these arguments
      ,arg_id       int unsigned not null       -- argument identifier (a progressive number)
      ,argument     varchar(256) not null       -- argument value
      ,primary key(task_id,arg_id)
      ,foreign key (task_id) references task(id)
  );
  
  -- Task input file
  create table task_input_file (
       task_id      int unsigned not null       -- id of the task owning the file
      ,file_id      int unsigned not null       -- file identifier (a progressive number)
      ,file         varchar(256) not null       -- file name
      ,path         varchar(256) default null   -- the absolute path to the file
      ,primary key(task_id,file_id)
      ,foreign key (task_id) references task(id)
  );
  
  -- Task output file
  create table task_output_file (
       task_id      int unsigned not null       -- id of the task owning the file
      ,file_id      int unsigned not null       -- file identifier (a progressive number)
      ,file         varchar(256) not null       -- file name
      ,path         varchar(256) default null   -- the absolute path to the file
      ,primary key(task_id,file_id)
      ,foreign key (task_id) references task(id)
  );
  
  -- Runtime data
  create table runtime_data (
       task_id      int unsigned  not null      -- id of the task owning data
      ,data_id      int unsigned  not null      -- data identifier (a progressive number)
      ,data_name    varchar(128)  not null      -- name of data field
      ,data_value   varchar(1024) not null      -- value of data field
      ,data_desc    varchar(2048)               -- data description
      ,data_proto   varchar(128)                -- data protocol (manages complex data)
      ,data_type    varchar(128)                -- data type (works with data_proto)
      ,creation     datetime      not null      -- When data has been written the first time
      ,last_change  datetime      not null      -- When data has been updated
      ,primary key(task_id,data_id)
      ,foreign key (task_id) references task(id)
  );
  
  -- Inifrastructure task
  -- Virtual Infrastructure are depending form the task that created it
  create table infrastructure_task (
     infra_id     int unsigned not null       -- Infrastructure Id (see infrastructure.id)
    ,task_id      int unsigned not null       -- id of the task owning this infrastructure
    ,app_id       int unsigned not null       -- id of the application responsible to create the infrastructure
    ,creation     datetime not null           -- Virtual infrastructure creation timestamp
    ,foreign key(infra_id) references infrastructure(id)
    ,foreign key(app_id) references application(id)
    ,foreign key(task_id) references task(id)
  );
  
  --
  -- Tables defining: Users, Groups and Roles
  --
  
  -- Users table, owns only user information
  create table fg_user (
      id           int unsigned not null auto_increment  -- record id
     ,name         varchar(128)  not null                -- name identifier
     ,password     varchar(4096) not null                -- shadowed password
     ,first_name   varchar(128)  not null                -- first name
     ,last_name    varchar(128)  not null                -- last name
     ,institute    varchar(256)  not null                -- institution
     ,mail         varchar(1024) not null                -- email, more addresses as comma separated values
     ,creation     datetime      not null                -- when user has been inserted
     ,modified     datetime      not null                -- when user has been modified
     ,enabled      boolean       not null default true   -- user enabling flag
     ,primary key(id,name)
  );
  
  -- User activation table
  create table fg_user_check (
      user_id        int unsigned  not null -- User id to enable
     ,token          varchar(1024) not null -- Enabling token
     ,token_expiry   datetime      not null -- Expiry date for enabling token
     ,foreign key(user_id) references fg_user(id)
  );
  
  -- Users baseline values; users and passwords must be replaced with real values
  insert into fg_user (id,name,password,first_name,last_name,institute,mail,creation,modified)
  values (1,'futuregateway',sha('futuregateway'),'FutureGateway','FutureGateway','INFN','sgw-admin@lists.indigo-datacloud.eu',now(),now());
  insert into fg_user (id,name,password,first_name,last_name,institute,mail,creation,modified)
  values (2,'test',sha('test'),'Test','Test','INFN','sgw-admin@lists.indigo-datacloud.eu',now(),now());
  insert into fg_user (id,name,password,first_name,last_name,institute,mail,creation,modified)
  values (3,'brunor',sha('brunor'),'Riccardo','Bruno','INFN','riccardo.bruno@ct.infn.it',now(),now());
  
  -- User data table; store persistent data associated to the user
  create table fg_user_data (
      user_id      int unsigned  not null -- User id owning data
     ,data_id      int unsigned  not null -- Data identifier (a progressive number)
     ,data_name    varchar(128)  not null -- name of data field
     ,data_value   varchar(1024) not null -- value of data field
     ,data_desc    varchar(2048)          -- data description
     ,data_proto   varchar(128)           -- data protocol (manages complex data)
     ,data_type    varchar(128)           -- data type (works with data_proto)
     ,creation     datetime      not null -- When data has been written the first time
     ,last_change  datetime      not null -- When data has been updated
     ,primary key(user_id,data_id,data_name)
     ,foreign key(user_id) references fg_user(id)
  );
  
  -- Groups table, users may belong to one or more groups defined below
  create table fg_group (
      id           int unsigned not null auto_increment  -- record id
     ,name         varchar(128)  not null                -- name identifier
     ,creation     datetime      not null                -- When group has been inserted
     ,modified     datetime      not null                -- When group has been modified
     ,primary key(id)
  );
  
  -- Groups baseline values
  insert into fg_group (id,name,creation,modified) values (1,'administrator',now(),now()); -- Can do anything
  insert into fg_group (id,name,creation,modified) values (2,'test',now(),now());          -- Can do tests only
  insert into fg_group (id,name,creation,modified) values (3,'users',now(),now());         -- Restricted user (view only)
  insert into fg_group (id,name,creation,modified) values (4,'developers',now(),now());    -- Restricted user (view only)
  
  --  Roles table, different actions can be done by users and groups
  create table fg_role (
      id           int unsigned not null auto_increment  -- record id
     ,name         varchar(128)  not null                -- name identifier
     ,creation     datetime      not null                -- when group has been inserted
     ,modified     datetime      not null                -- when group has been modified
     ,primary key(id)
  );
  
  -- Roles baseline values
  insert into fg_role (id,name,creation,modified) values ( 1,'app_install',now(),now());         -- Install an application
  insert into fg_role (id,name,creation,modified) values ( 2,'app_change',now(),now());          -- Modify an application
  insert into fg_role (id,name,creation,modified) values ( 3,'app_delete',now(),now());          -- Delete an application
  insert into fg_role (id,name,creation,modified) values ( 4,'app_view',now(),now());            -- Run an application
  insert into fg_role (id,name,creation,modified) values ( 5,'app_run',now(),now());             -- Run an application
  insert into fg_role (id,name,creation,modified) values ( 6,'infra_add',now(),now());           -- Add an infrastructure
  insert into fg_role (id,name,creation,modified) values ( 7,'infra_change',now(),now());        -- Change infrastructure
  insert into fg_role (id,name,creation,modified) values ( 8,'infra_delete',now(),now());        -- Delete an infrastructure
  insert into fg_role (id,name,creation,modified) values ( 9,'infra_view',now(),now());          -- View an infrastructure
  insert into fg_role (id,name,creation,modified) values (10,'infra_attach',now(),now());        -- Attach an infrastructure to an application
  insert into fg_role (id,name,creation,modified) values (11,'infra_detach',now(),now());        -- Detach an infrastructure from an application
  insert into fg_role (id,name,creation,modified) values (12,'task_delete',now(),now());         -- Delete a task
  insert into fg_role (id,name,creation,modified) values (13,'task_view',now(),now());           -- View a task
  insert into fg_role (id,name,creation,modified) values (14,'task_userdata',now(),now());       -- Manage userdata on task
  insert into fg_role (id,name,creation,modified) values (15,'user_add',now(),now());            -- Can add users
  insert into fg_role (id,name,creation,modified) values (16,'user_del',now(),now());            -- Can remove users
  insert into fg_role (id,name,creation,modified) values (17,'user_change',now(),now());         -- Can change users
  insert into fg_role (id,name,creation,modified) values (18,'group_change',now(),now());        -- Can change groups
  insert into fg_role (id,name,creation,modified) values (19,'role_change',now(),now());         -- Can change roles
  insert into fg_role (id,name,creation,modified) values (20,'user_impersonate',now(),now());    -- Can impersonate any other users
  insert into fg_role (id,name,creation,modified) values (21,'group_impersonate',now(),now());   -- Can impersonate other users in the same group
  insert into fg_role (id,name,creation,modified) values (22,'task_statuschange',now(),now());   -- Can change the status of a task
  insert into fg_role (id,name,creation,modified) values (23,'users_view',now(),now());          -- Can view users
  insert into fg_role (id,name,creation,modified) values (24,'users_change',now(),now());        -- Can change users
  insert into fg_role (id,name,creation,modified) values (25,'users_groups_view',now(),now());   -- Can view user groups
  insert into fg_role (id,name,creation,modified) values (26,'users_groups_change',now(),now()); -- Can change user groups
  insert into fg_role (id,name,creation,modified) values (27,'users_tasks_view',now(),now());    -- Can view user tasks
  insert into fg_role (id,name,creation,modified) values (28,'groups_view',now(),now());         -- Can change groups
  insert into fg_role (id,name,creation,modified) values (29,'groups_change',now(),now());       -- Can change groups
  insert into fg_role (id,name,creation,modified) values (30,'groups_apps_view',now(),now());    -- Can view applications in groups
  insert into fg_role (id,name,creation,modified) values (31,'groups_apps_change',now(),now());  -- Can change applications in groups
  insert into fg_role (id,name,creation,modified) values (32,'groups_roles_view',now(),now());   -- Can view roles in groups
  insert into fg_role (id,name,creation,modified) values (33,'groups_roles_change',now(),now()); -- Can change roles in groups
  insert into fg_role (id,name,creation,modified) values (34,'roles_view',now(),now());          -- Can view roles
  
  --
  -- CrossTables applying Roles to Groups and Users
  --
  
  -- UserGroups, associate the user membership in groups
  create table fg_user_group (
      user_id     int unsigned not null -- user id
     ,group_id    int unsigned not null -- user id
     ,creation    datetime     not null -- when association has been done
     ,foreign key (user_id) references fg_user(id)
     ,foreign key (group_id) references fg_group(id)
  );
  
  -- UserGroups baseline values
  insert into fg_user_group (user_id,group_id,creation) values (1,1,now()); -- futuregateway user to Administrator group
  insert into fg_user_group (user_id,group_id,creation) values (2,2,now()); -- test user to Test group
  insert into fg_user_group (user_id,group_id,creation) values (3,3,now()); -- brunor to generic user group
  
  -- GroupRoles, associate roles to groups
  create table fg_group_role (
      group_id    int unsigned not null -- user id
     ,role_id     int unsigned not null -- user id
     ,creation    datetime     not null -- when association has been done
     ,foreign key (group_id) references fg_group(id)
     ,foreign key (role_id) references fg_role(id)
  );
  
  -- Administrator roles (grant all privileges)
  insert into fg_group_role (group_id,role_id,creation)
  select 1,id,now() from fg_role;
  
  -- Test roles
  insert into fg_group_role (group_id,role_id,creation) values (2, 4, now()); -- Test can view applications
  insert into fg_group_role (group_id,role_id,creation) values (2, 5, now()); -- Test can run applications
  insert into fg_group_role (group_id,role_id,creation) values (2,12, now()); -- Test can delete tasks
  insert into fg_group_role (group_id,role_id,creation) values (2,13, now()); -- Test can view tasks
  insert into fg_group_role (group_id,role_id,creation) values (2,14, now()); -- Test can manage userdata on tasks
  insert into fg_group_role (group_id,role_id,creation) values (2,22, now()); -- Test can change the status of a task
  
  -- users roles
  insert into fg_group_role (group_id,role_id,creation) values (3, 4, now()); -- User group can view applications
  
  -- Developers roles (grant all privileges like administrator)
  insert into fg_group_role (group_id,role_id,creation)
  select 4,id,now() from fg_role;
  
  -- Associate applications to Groups
  create table fg_group_apps (
      group_id    int unsigned not null -- group id
     ,app_id      int unsigned not null -- app id
     ,creation    datetime     not null -- when association has been done
     ,foreign key (group_id) references fg_group(id)
     ,foreign key (app_id) references application(id)
  );
  
  insert into fg_group_apps (group_id, app_id, creation)
  select 1,id,now() from application;                                        -- Administrator access to all appications
  insert into fg_group_apps (group_id, app_id, creation) values (2,1,now()); -- Test access to hostname
  insert into fg_group_apps (group_id, app_id, creation) values (2,2,now()); -- Test access to helloworld
  insert into fg_group_apps (group_id, app_id, creation)
  select 4,id,now() from application;                                        -- Developers access to all appications
  
  
  -- AccessTokens
  -- Any API call needs an access token which uniquelly authorized and identifies the issuer
  create table fg_token (
      token    varchar(1024) not null -- access token
     ,subject  varchar(1024) not null -- Subject field related to the released token
     ,user_id  int unsigned  not null -- the associated user
     ,creation datetime      not null -- when token has been created
     ,expiry   integer                -- number of seconds of validity (default 24 hours)
  );
  
  --
  -- APIServer queue table
  --
  -- The FutureGatewat API Server  makes use of this table as a link between the REST engine
  -- and the APIServer daemon. Each change on the queue table will have effects on both
  -- the REST engine and the targeted architecture
  --
  create table as_queue (
       task_id       int unsigned not null           -- Taks reference for this GridEngine queue entry
      ,target_id     int unsigned default 0          -- For GridEngine UsersTracking' ActiveGridInteraction id reference
      ,target        varchar(32) not null            -- Targeted architecture ("GridEngine","OneDATA", ...)
      ,action        varchar(32) not null            -- A string value that identifies the requested operation (SUBMIT,GETSTATUS,GETOUTPUT...
      ,status        varchar(32) not null            -- Operation status: QUEUED,PROCESSING,PROCESSED,FAILED,DONE
      ,target_status varchar(32) default null        -- GridEngine Job Status: WAITING,SCHEDULED,RUNNING,ABORT,DONE
      ,retry         int unsigned not null default 0 -- Retry count of the task in the queue
      ,creation      datetime    not null            -- When the action is enqueued
      ,last_change   datetime    not null            -- When the record has been modified by the GridEngine last time
      ,check_ts      datetime    not null            -- Check timestamp used to implement a round-robin checking loop
      ,action_info   varchar(128)                    -- Temporary directory path containing further info to accomplish the requested operation
      ,primary key(task_id,action)
      ,foreign key (task_id) references task(id)
      ,index(task_id)
      ,index(action)
  --  ,index(target)
      ,index(last_change)
  );
  
  --
  -- Interface (Executors) tables
  --
  
  -- simple_tosca table
  create table simple_tosca (
      id           int unsigned  not null
     ,task_id      int unsigned  not null
     ,tosca_id     varchar(1024) not null
     ,tosca_status varchar(32)   not null
     ,creation     datetime      not null -- When the action is enqueued
     ,last_change  datetime      not null -- When the record has been modified by the GridEngine last time
     ,primary key(id)
  );
  
  -- tosca_idc table
  create table tosca_idc (
      id             int unsigned  not null
     ,task_id        int unsigned  not null
     ,tosca_id       varchar(1024) not null
     ,tosca_status   varchar(32)   not null
     ,tosca_endpoint varchar(1024) not null
     ,creation       datetime      not null -- When the action is enqueued
     ,last_change    datetime      not null -- When the record has been modified by the GridEngine last time
     ,primary key(id)
  );
  
  --
  -- Service registry
  --
  -- FutureGateway foresees the use of one or many components which presence is tracked
  -- by the FutureGateway database
  --
  create table srv_registry (
      uuid          char(36) not null
     ,creation      datetime      not null
     ,last_access   datetime      not null
     ,enabled       boolean default true
     ,cfg_hash      varchar(1024)     
     ,primary key(uuid)
  );
  
  -- Service configuration
  --
  -- FutureGateway uses registered services to store their configuration settings
  -- this allows safe service restarts as well as 'on the fly' change of configuration
  -- settings for all modules supporting this
  -- Configuration values are stored in the form conf_name = conf_value
  -- the field conf_enabled tells if the configuration is enabled or not
  --
  create table srv_config (
      uuid          char(36) not null
     ,name          varchar(256)  not null
     ,value         varchar(4096)
     ,enabled       boolean default true
     ,created       datetime not null
     ,modified      datetime not null
     ,foreign key (uuid) references srv_registry(uuid)
  );
  
  --
  -- Patching mechanism
  --
  -- Futuregateway provides automated scripts exploiting GITHub to automatically
  -- provide the latest available code
  -- The same functionality should be ensured to keep database content consisent
  -- with the lates code version
  --
  create table db_patches (
      id           int unsigned not null -- Patch Id
     ,version      varchar(32)  not null -- Current database version
     ,name         varchar(256) not null -- Name of the patch (it describes the involved feature)
     ,file         varchar(256) not null -- file refers to fgAPIServer/db_patches directory
     ,applied      datetime              -- Patch application timestamp
     ,primary key(id)
  );
  
  -- Default value for baseline setup (this script)
  insert into db_patches (id,version,name,file,applied) values (1,'0.0.13','baseline setup','../fgapiserver_db.sql',now());