-- 
--  Copyrights     : CNRS
--  Author         : Oleg Lodygensky
--  Acknowledgment : XtremWeb-HEP is based on XtremWeb 1.8.0 by inria : http://www.xtremweb.net/
--  Web            : http://www.xtremweb-hep.org
--  
--       This file is part of XtremWeb-HEP.
-- 
--     XtremWeb-HEP is free software: you can redistribute it and/or modify
--     it under the terms of the GNU General Public License as published by
--     the Free Software Foundation, either version 3 of the License, or
--     (at your option) any later version.
-- 
--     XtremWeb-HEP is distributed in the hope that it will be useful,
--     but WITHOUT ANY WARRANTY; without even the implied warranty of
--     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--     GNU General Public License for more details.
-- 
--     You should have received a copy of the GNU General Public License
--     along with XtremWeb-HEP.  If not, see <http://www.gnu.org/licenses/>.
-- 
-- 



-- 
-- Version : 6.0.1
-- 
-- File    : xwsetupdb.sql
-- Purpose : this file contains the needed SQL commands to 
--           create the XWHEP database
-- Usage   : this is used by xtremweb.database
-- 

-- 
-- you must uncomment the two next commands and set the appropriate
-- database name

CREATE DATABASE IF NOT EXISTS @DBNAME@;

USE @DBNAME@;

--   connect @DBNAME@;

-- 
-- please don't forget to look at the end of this file
-- to set your xtremweb database user privileges
-- 


-- 
-- next contains applications
-- 
CREATE TABLE IF NOT EXISTS apps (
-- primay key
        uid char(50) NOT NULL,
-- optionnal. user UID
        ownerUID char(50),
-- secondary key. if your change length, don t forget to change AppInterface.APPNAMELENGTH
        name char(100),
-- optionnal. true is app is a service
        isService char(5) DEFAULT 'false',
-- this defines access rights 'a la' linux FS
-- see xtremweb.common.XWAccessRights.java
        accessRights int(4) DEFAULT 0x700,
-- average execution time. updated on work completion
        avgExecTime int(15) default 0,
-- optionnal. minimum memory need
        minMemory int(10) default 0,
-- optionnal. minimum CPU speed need
        minCPUSpeed int(10) default 0,
-- completed jobs counter. updated on work completion
        nbJobs int(15) default 0,
-- pending jobs counter. updated on work submission
        pendingJobs int(15) default 0,
-- running jobs counter. updated on work request
        runningJobs int(15) default 0,
-- error jobs counter. updated on job error
   	 errorJobs int(15) default 0,
-- application web page
        webpage char(200),
-- optionnal. this is the default STDIN, if any. this is an URI
-- If set, this is automatically provided to any job, except if job defines its own STDIN
-- works.stdin may be set to NULLUID to force no STDIN even if this apps.stdin is defined
        defaultStdinURI char(200),
-- optionnal. this is the base environments, if any. this is an URI
-- If set, this is automatically provided to any job
-- If set, this is installed **after** defaultdirin or works.dirin
-- to ensure those last do not override any file contained in basedirin
        baseDirinURI char(200),
-- optionnal. this is the default environment, if any. this is an URI
-- If set, this is automatically provided to any job, except if job defines its own DIRIN
-- works.dirin may be set to NULLUID to force no DIRIN even if this apps.dirin is defined
-- This is installed before basedirin to ensure this does not override
-- any of the apps.basedirin files
        defaultDirinURI char(200),
-- optionnal. this is the linux    ix86  binary, if any. this is an URI
        linux_ix86URI char(200),
-- optionnal. this is the linux    amd64 binary, if any. this is an URI
        linux_amd64URI char(200),
-- optionnal. this is the linux    x86 64 binary, if any. this is an URI
        linux_x86_64URI char(200),
-- optionnal. this is the linux    intel itanium binary, if any. this is an URI
        linux_ia64URI char(200),
-- optionnal. this is the linux    ppc   binary, if any. this is an URI
        linux_ppcURI char(200),
-- optionnal. this is the mac os 10 ix86  binary, if any. this is an URI
        macos_ix86URI char(200),
-- optionnal. this is the mac os 10 x86_64  binary, if any. this is an URI
        macos_x86_64URI char(200),
-- optionnal. this is the mac os 10 ppc   binary, if any. this is an URI
        macos_ppcURI char(200),
-- optionnal. this is the win32    ix86  binary, if any. this is an URI
        win32_ix86URI char(200),
-- optionnal. this is the win32    amd64 binary, if any. this is an URI
        win32_amd64URI char(200),
-- optionnal. this is the win32   x86_64 binary, if any. this is an URI
        win32_x86_64URI char(200),
-- optionnal. this is the java           binary, if any. this is an URI
        javaURI char(200),
-- optionnal. this is the osf1     alpha binary, if any. this is an URI
        osf1_alphaURI char(200),
-- optionnal. this is the osf1     sparc binary, if any. this is an URI
        osf1_sparcURI char(200),
-- optionnal. this is the solaris  alpha binary, if any. this is an URI
        solaris_alphaURI char(200),
-- optionnal. this is the solaris  sparc binary, if any. this is an URI
        solaris_sparcURI char(200),
-- optionnal. this is the linux    ix86  library, if any. this is an URI
        ldlinux_ix86URI char(200),
-- optionnal. this is the linux    amd64 library, if any. this is an URI
        ldlinux_amd64URI char(200),
-- optionnal. this is the linux    x86 64 library, if any. this is an URI
        ldlinux_x86_64URI char(200),
-- optionnal. this is the linux    intel itanium library, if any. this is an URI
        ldlinux_ia64URI char(200),
-- optionnal. this is the linux    ppc   library, if any. this is an URI
        ldlinux_ppcURI char(200),
-- optionnal. this is the mac os x ix86  library, if any. this is an URI
        ldmacos_ix86URI char(200),
-- optionnal. this is the mac os x ix86  library, if any. this is an URI
        ldmacos_x86_64URI char(200),
-- optionnal. this is the mac os x ppc   library, if any. this is an URI
        ldmacos_ppcURI char(200),
-- optionnal. this is the win32    ix86  library, if any. this is an URI
        ldwin32_ix86URI char(200),
-- optionnal. this is the win32    amd64 library, if any. this is an URI
        ldwin32_amd64URI char(200),
-- optionnal. this is the win32   x86_64 library, if any. this is an URI
        ldwin32_x86_64URI char(200),
-- optionnal. this is the osf1     alpha library, if any. this is an URI
        ldosf1_alphaURI char(200),
-- optionnal. this is the osf1     sparc library, if any. this is an URI
        ldosf1_sparcURI char(200),
-- optionnal. this is the solaris  alpha library, if any. this is an URI
        ldsolaris_alphaURI char(200),
-- optionnal. this is the solaris  sparc library, if any. this is an URI
        ldsolaris_sparcURI char(200),
-- optionnal, launch shell scripts
-- since 8.0.0
        launchscriptshuri  varchar(200),
-- optionnal, launch MS command scripts
-- since 8.0.0
	launchscriptcmduri varchar(200),
-- optionnal, unload shell scripts
-- since 8.0.0
   	unloadscriptshuri  varchar(200),
-- optionnal, unload MS command scripts
-- since 8.0.0
	unloadscriptcmduri varchar(200),
-- optional, env vars
-- since 8.0.0
        envvars varchar(200),
-- this defines the application type
-- "none" : undefined; no job will run
-- "binary" : the worker must download the binary
-- "shared" : this is a shared application : the worker don't download any bianry
-- "virtualbox" : this is a script for virtualbox shared application
-- "vmware" : this is a script for vmware shared application
-- since 8.0.0
   	type varchar(50) DEFAULT 'none',
-- optional, needed packages on worker side
-- since 8.0.0
        neededpackages varchar(200),

-- true is this row has been deleted
        isdeleted char(5) DEFAULT 'false',

        PRIMARY KEY (uid),
        CONSTRAINT UNIQUE INDEX apps_uniq_name(name),
	INDEX owner(owneruid)
) @DBENGINE@; 


-- 
-- since 7.0.0 deleted rows are stored in history table
-- 
CREATE TABLE IF NOT EXISTS apps_history LIKE apps;
-- ALTER TABLE apps_history ADD PRIMARY KEY USING HASH  (uid):
-- ALTER TABLE apps_history ADD CONSTRAINT UNIQUE INDEX (name):

-- 
-- next contains datas : binary, input files, results
-- 
CREATE TABLE IF NOT EXISTS datas (
-- primary key
        uid char(50) NOT NULL,
-- may be {user, app, work} UID
        ownerUID char(50),
-- this is the URI of the content
        uri char(200),
-- this defines access rights 'a la' linux FS
-- see xtremweb.common.XWAccessRights.java
        accessRights int(4) DEFAULT 0x700,
-- symbolic file name (i.e. alias name)
        name char(200),
-- how many times it is used. can be deleted if 0
        links int(4),
-- last access date
        accessDate datetime,
-- creation date
        insertionDate datetime,
-- status. see common/XWStatus.java
        status char(20),
-- see common/DataType.java
        type char(20),
-- optionnal. mainly necessary if data is an app binary
        os char(20),
-- optionnal. mainly necessary if data is an app binary
        cpu char(20),
-- data md5
        md5 char(50),
-- effective file size
        size bigint,
-- optionnal. used by replication
        sendToClient char(5) DEFAULT 'false',
-- optionnal. used by replication
        replicated char(5) DEFAULT 'false',
-- true is row has been deleted 
        isdeleted char(5) DEFAULT 'false',

        PRIMARY KEY (uid),
	INDEX owner(owneruid)
) @DBENGINE@; 

-- 
-- since 7.0.0 deleted rows are stored in history table
-- 
CREATE TABLE IF NOT EXISTS datas_history LIKE datas;
-- ALTER TABLE datas_history ADD PRIMARY KEY USING HASH (uid);


-- 
-- next aims to group jobs
-- groups are not erased when the client disconnect : they are kept between client sessions
-- 
CREATE TABLE IF NOT EXISTS groups (
-- primary key
        uid char(50) NOT NULL,
-- group name
        name char(50) NOT NULL,
-- session UID
        sessionUID char(50),
-- since 5.8.0
-- this defines access rights 'a la' linux FS
-- see xtremweb.common.XWAccessRights.java
        accessRights int(4) DEFAULT 0x700,
-- owner (user) UID
        ownerUID char(50) NOT NULL,
-- true is this row has been deleted
        isdeleted char(5) DEFAULT 'false',

        PRIMARY KEY (uid),
	INDEX owner(owneruid),
	INDEX sessionuid(sessionuid)
) @DBENGINE@;

-- 
-- since 7.0.0 deleted rows are stored in history table
-- 
CREATE TABLE IF NOT EXISTS groups_history LIKE groups;
-- ALTER TABLE groups_history ADD PRIMARY KEY USING HASH (uid);


-- 
-- next contains worker definitions
-- 
CREATE TABLE IF NOT EXISTS hosts (
-- primay key
        uid char(50) NOT NULL,
-- this is the amount of simultaneous jobs
        poolworksize int(2) default 0,
-- completed jobs counter. updated on work completion
        nbJobs int(15) default 0,
-- pending jobs counter. updated on work submission
        pendingJobs int(15) default 0,
-- running jobs counter. updated on work request
        runningJobs int(15) default 0,
-- error jobs counter. updated on job error
   	 errorJobs int(15) default 0,
-- how many time to wait before this host is considered as lost
-- default is provided by server, but this may also be defined from host config
        timeOut int(15),
-- average execution time. updated on work completion
        avgExecTime int(15) default 0,
-- last communication time (helps to determine whether this host is lost)
        lastAlive datetime,
-- this host name
        name char(100),
-- how many time this host has been connected
        nbconnections int(10),
-- This is the IP address as provided by worker itself.
-- This may be  a NATed IP
-- Since XWHEP 5.7.3 this length is 50 to comply to IP V6
        natedipaddr char(50),
-- This is the IP address obtained at connexion time.
-- This is set by server at connexion time.
-- This may be different from NATed IP.
-- Since XWHEP 5.7.3 this length is 50 to comply to IP V6
        ipaddr char(50),
-- MAC address
        hwaddr char(20),
-- time zone
        timezone char(50),
-- OS name
        os char(20),
-- OS version 
-- since XWHEP 6.0.0
        osversion char(50),
-- Java version 
-- since XWHEP 6.0.0
        javaversion char(50),
-- Java data model (32 or 64 bits)
-- since XWHEP 6.0.0
        javadatamodel int(4),
-- CPU name
        cputype char(20),
-- CPU counter
        cpunb int(2),
-- CPU model
        cpumodel char(50),
-- CPU speed
        cpuspeed bigint default 0,
-- total RAM
        totalmem bigint default 0,
-- total SWAP
        totalswap bigint default 0,
-- total space on tmp partition
        totaltmp bigint default 0,
-- free space on tmp partition
        freetmp bigint default 0,
-- time diff from host to server
        timeShift int(15),
-- average ping to server
        avgping int(20),
-- ping amount to server
        nbping int(10),
-- upload bandwidth usage (in Mb/s)
        uploadbandwidth float,
-- download bandwidth usage (in Mb/s)
        downloadbandwidth float,
-- user UID
        ownerUID char(50),
-- this defines access rights 'a la' linux FS
-- see xtremweb.common.XWAccessRights.java
-- since 5.8.0
        accessRights int(4) DEFAULT 0x700,
-- this defines the percentage of CPU usable by the worker
-- since 8.0.0
        cpuLoad int(3) DEFAULT 50,
-- Project this worker wants to limit its participation
-- In practice, this is a usergroup name
-- How it works : if a worker runs under an identity that belongs to a usergroup
-- this worker can execute any public or group job for the users of its group.
-- This worker may want to run group jobs only. It can do so by setting project to its usergroup label
        project char(50),
-- this flag tells whether this host may be used 
-- if a host has ever generated any error, this flag is automatically set to false
-- so that we don't use faulty worker
        active char(5) DEFAULT 'true',
-- this flag tells whether this host may run jobs accordingly to its local activation policy
-- this flag is provided by worker with alive signal
        available char(5) DEFAULT 'false',
-- this flag tells whether this host accepts to run jobs that listen for incoming connections
-- since 8.0.0
        incomingconnections char(5) DEFAULT 'false',
-- this flag tells whether this host accept application binary 
-- if false, this host accept to execute services written in java only
        acceptbin char(5) DEFAULT 'true',
-- this is this host XWHEP software version
        version char(50) NOT NULL,
-- this flag tells whether this host is collecting traces (CPU, memory, disk usage)
        traces char(5) DEFAULT 'false',
-- true is this row has been deleted
        isdeleted char(5) DEFAULT 'false',
-- XWHEP 5.7.3 : this is set to true if the worker is run on SG ressource (e.g. EGEE)
-- XWHEP 7.0.0 : this is deprecated; please use sgid instead 
        pilotjob char(5) DEFAULT 'false',
-- XWHEP 7.0.0 : this is the Service Grid Identifier; this is set by the DG 2 SG bridge, if any
        sgid char(200),
-- XWHEP 7.2.0 : this is a job URI; this is for SpeQuLoS (EDGI/JRA2). 
-- If this is set, the worker will receive this job in priority,
-- if available, and according to the match making - CPU, OS...
-- This has a higher priority than batchid
        jobid char(200),
-- XWHEP 7.2.0 : this is a job group URI; this is for SpeQuLoS (EDGI/JRA2).
-- If this is set, the worker will receive a job from this group in priority,
-- if any, and according to the match making - CPU, OS...
-- This has a lower priority than jobid
        batchid char(200),
-- XWHEP 7.0.0 this is not used
        userproxy char(200),
-- optional, list of coma separated applications shared by the worker
-- since 8.0.0 
        sharedapps     varchar(200),
-- optional, list of coma separated libraries shared by the worker
-- since 8.0.0 
	sharedpackages varchar(200),
-- optional, list of coma separated data shared by the worker
-- since 8.0.0 (not used yet)
	shareddatas varchar(200),

        PRIMARY KEY (uid),
	INDEX owner (owneruid)
        ) @DBENGINE@; 

-- 
-- since 7.0.0 deleted rows are stored in history table
-- 
CREATE TABLE IF NOT EXISTS hosts_history LIKE hosts;
-- ALTER TABLE hosts_history ADD PRIMARY KEY USING HASH (uid);


-- 
-- next aims to group jobs
-- sessions are automatically erased (and all its jobs) when the client disconnect
-- 
CREATE TABLE IF NOT EXISTS sessions (
-- primary key
        uid char(50) NOT NULL,
-- session name
        name char(50) NOT NULL,
-- since 5.8.0
-- this defines access rights 'a la' linux FS
-- see xtremweb.common.XWAccessRights.java
        accessRights int(4) DEFAULT 0x700,
-- owner (user) UID
        ownerUID char(50) NOT NULL,
-- true is this row has been deleted
        isdeleted char(5) DEFAULT 'false',

        PRIMARY KEY (uid),
	INDEX owner (owneruid)
) @DBENGINE@;


-- 
-- since 7.0.0 deleted rows are stored in history table
-- 
CREATE TABLE IF NOT EXISTS sessions_history LIKE sessions;
-- ALTER TABLE sessions_history ADD PRIMARY KEY USING HASH (uid);


-- 
-- next contains task definition
-- a task is a work associated to a worker
-- tasks can not be submitted by users : they must submit jobs
-- tasks are automatically created by server
-- each work has a set of associated tasks, at least one
-- a new task is created each time a task is lost
-- 
CREATE TABLE IF NOT EXISTS tasks (
-- primay key
        uid char(50) NOT NULL,
-- since 5.8.0
        ownerUID char(50),
-- since 8.0.0
        workUID char(50) NOT NULL,
-- since 5.8.0
-- this defines access rights 'a la' linux FS
-- see xtremweb.common.XWAccessRights.java
        accessRights int(4) DEFAULT 0x700,
-- host UID
        hostUID char(50),
-- instanciation counter
        trial int(11),
-- status. see common/XWStatus.java
        status char(20),
-- when the server put this task into queue
        InsertionDate datetime,
-- first instanciation date
        StartDate datetime,
-- last instanciation date
        LastStartDate datetime,
-- is it necessary ?
        AliveCount int(11),
-- is it necessary ?
        LastAlive datetime,
-- when this task has been removed
        removalDate datetime,
-- last instanciation duration
        duration bigint default 0,
-- true is row has been deleted 
        isdeleted char(5) DEFAULT 'false',

        PRIMARY KEY (uid),
	INDEX hostuid  (hostuid),
	INDEX owner (owneruid)
) @DBENGINE@;

CREATE INDEX fk_tasks_works_idx ON tasks (workUID ASC); 
 
-- 
-- since 7.0.0 deleted rows are stored in history table
-- 
CREATE TABLE IF NOT EXISTS tasks_history LIKE tasks;
-- ALTER TABLE tasks_history ADD PRIMARY KEY USING HASH (uid);


-- 
-- next used to store traces from workers (to trace CPU, RAM, Disk etc. activities)
-- this is not used any more
-- 
CREATE TABLE IF NOT EXISTS traces (
-- primary key
        uid char(50) NOT NULL,
-- host UID
        hostUID char(50) DEFAULT '' NOT NULL,
-- 
        login char(25) DEFAULT '' NOT NULL,
        arrivalDate datetime NOT NULL,
        startDate datetime NOT NULL,
        endDate datetime NOT NULL,
-- since 5.8.0
        ownerUID char(50),
-- since 5.8.0
-- this defines access rights 'a la' linux FS
-- see xtremweb.common.XWAccessRights.java
        accessRights int(4) DEFAULT 0x700,
-- data URI
        data char(200) DEFAULT '' NOT NULL,
-- true is this row has been deleted
        isdeleted char(5) DEFAULT 'false',

        PRIMARY KEY (uid),
	INDEX hostuid  (hostuid),
	INDEX owner (owneruid)
) @DBENGINE@;

-- 
-- since 7.0.0 deleted rows are stored in history table
-- 
CREATE TABLE IF NOT EXISTS traces_history LIKE traces;
-- ALTER TABLE traces_history ADD PRIMARY KEY USING HASH (uid);


-- 
-- next contains user definitions
-- 
CREATE TABLE IF NOT EXISTS users (
-- primay key
        uid char(50) NOT NULL,
-- optionnal. user group UID
        usergroupUID char(50),
-- completed jobs counter. updated on work completion
        nbJobs int(15) default 0,
-- pending jobs counter. updated on work submission
        pendingJobs int(15) default 0,
-- running jobs counter. updated on work request
        runningJobs int(15) default 0,
-- error jobs counter. updated on job error
   	 errorJobs int(15) default 0,
-- average execution time. updated on work completion
        usedCpuTime bigint default 0,
-- this is the X.509 proxy file content
        certificate text,
-- since 5.8.0
        ownerUID char(50),
-- since 5.8.0
-- this defines access rights 'a la' linux FS
-- see xtremweb.common.XWAccessRights.java
        accessRights int(4) DEFAULT 0x700,
-- user login. if your change length, don t forget to change UserInterface.USERLOGINLENGTH
        login char(250) NOT NULL,
-- user passworkd
        password char(50) DEFAULT '' NOT NULL,
-- user email
        email char(50) DEFAULT '' NOT NULL,
-- optionnal, user first name
        fname char(25),
-- optionnal, user last name
        lname char(25),
-- user country
        country char(25),
-- user rights. see common/UserRights.java
        rights char(20) DEFAULT 'NONE',
-- true is this user connect using private/public keys pair
        challenging char(5) DEFAULT 'false',
-- true is this row has been deleted
        isdeleted char(5) DEFAULT 'false',

        PRIMARY KEY (uid),
        CONSTRAINT UNIQUE INDEX users_uniq_login(login),
	INDEX groupuid (usergroupuid),
	INDEX owner (owneruid)
) @DBENGINE@;

-- 
-- since 7.0.0 deleted rows are stored in history table
-- 
CREATE TABLE IF NOT EXISTS users_history LIKE users;
-- ALTER TABLE users_history ADD PRIMARY KEY USING HASH (uid);
-- ALTER TABLE users_history ADD CONSTRAINT UNIQUE INDEX (login):


-- 
-- next contains group of users
-- 
CREATE TABLE IF NOT EXISTS usergroups (
-- primay key
        uid char(50) NOT NULL,
-- user group label
        label char(50),
-- since 5.8.0
        ownerUID char(50),
-- since 5.8.0
-- this defines access rights 'a la' linux FS
-- see xtremweb.common.XWAccessRights.java
        accessRights int(4) DEFAULT 0x700,
-- application web page
        webpage char(200),
-- true is this can be a "project"
-- this is always true, except for worker and administrator user groups
        project char(5) DEFAULT 'true',
-- true is this row has been deleted
        isdeleted char(5) DEFAULT 'false',

        PRIMARY KEY (uid),
        CONSTRAINT UNIQUE INDEX usergroups_uniq_label(label),
	INDEX owner(owneruid)
        ) @DBENGINE@; 

-- 
-- since 7.0.0 deleted rows are stored in history table
-- 
CREATE TABLE IF NOT EXISTS usergroups_history LIKE usergroups;
-- ALTER TABLE usergroups_history ADD PRIMARY KEY USING HASH (uid);


-- 
-- next contains work definitions as submitted by users
-- 
CREATE TABLE IF NOT EXISTS works (
-- primay key
        uid char(50) NOT NULL,
-- this is the X.509 user proxy URI to identify the owner of this work. this is not a certificate
        userproxy char(200),
-- optionnal. expected host UID
        expectedhostUID char(50),
-- this defines access rights 'a la' linux FS
-- see xtremweb.common.XWAccessRights.java
        accessRights int(4) DEFAULT 0x700,
-- XWHEP 7.2.0 : this is the Service Grid Identifier; this is set by the DG 2 SG bridge, if any
        sgid char(200),
-- optionnal. session UID
        sessionUID char(50),
-- optionnal. group UID (we call it 'groupUID' since 'group' is a MySql reserved word)
-- this is not an usergroup but a group !
        groupUID char(50),
-- optionnal. user label
        label char(150),
-- application UID
        appUID char(50),
-- user UID
        ownerUID char(50),
-- status. see common/XWStatus.java
        status char(20),
-- wallclocktime : how many seconds a job can be computed
-- the job is stopped as the wall clocktime is reached
-- if wallclocktime < 0, the job is not stopped
-- since 8.2.0
        wallclocktime int(10),
-- how many time should we try to compute
        maxRetry int(3),
-- how many time have we tried to compute
-- since 8.0.0
        retry int(3),
-- optionnal. disk space needed
-- this is in Mb
        diskSpace int(10) default 0,
-- optionnal. minimum memory needed
        minMemory int(10) default 0,
-- optionnal. minimum CPU speed needed
        minCPUSpeed int(10) default 0,
-- application return code
        returnCode int(3),
-- for replication
        server char(200),
-- this work command line to provide to application
        cmdLine char(255),
-- the job may be a server that needs to listen to some ports
-- this is a comma separated integer list
-- privileged ports (lower than 1024) are ignored
-- default : null (no port)
-- since 8.0.0
        listenport char(200),
-- the SmartSockets addresses to connect to the listened ports
-- this is set if listenport != null
-- this is a comma separated SmartSockets addresses list
-- default : null
-- since 8.0.0
        smartsocketaddr text,
-- This is the column index of a semicolon list containing tuple of 
-- SmartSockets address and local port.
-- This helps a job running on XWHEP worker side to connect to a server
-- like application running on XWHEP client side.
-- e.g. "Saddr1, port1; Saddr2, port2" 
-- default : null
-- since 8.0.0
        smartsocketclient text,
-- data URI. this is the STDIN. If not set, apps.stdin is used by default
-- NULLUID may be used to force no STDIN even if apps.stdin is defined
-- see common/UID.java#NULLUID
        stdinURI char(200),
-- data URI. this is the DIRIN. If not set, apps.dirin is used by default
-- NULLURI may be used to force no DIRIN even if apps.dirin is defined
-- see common/UID.java#NULLUID
-- This is installed before apps.basedirin to ensure this does not override
-- any of the apps.basedirin files
        dirinURI char(200),
-- data URI
        resultURI char(200),
-- when the server received this work
        arrivalDate datetime,
-- when this work has been completed
        completedDate datetime,
-- when this work result has been available
        resultDate datetime,
-- when this work has been downloaded by the worker
-- since 8.0.0
        readydate datetime,
-- when this work data have been downloaded by the worker
-- since 8.0.0
        datareadydate datetime,
-- when this work has been started on worker
-- since 8.0.0
        compstartdate datetime,
-- when this work work has been ended on worker
-- since 8.0.0
        compenddate datetime,
-- error message
        error_msg char(255),
-- used for replication
        sendToClient char(5) DEFAULT 'false',
-- used for replication
        local char(5) DEFAULT 'true',
-- used for replication
        active char(5) DEFAULT 'true',
-- optionnal. used for replication
        replicated char(5) DEFAULT 'false',
-- is it a service (see apps.isService)
        isService char(5) DEFAULT 'false',
-- true is row has been deleted 
        isdeleted char(5) DEFAULT 'false',
-- opional,
-- since 8.0.0
        envvars varchar(200),

        PRIMARY KEY (uid),
        INDEX works_status_active (status, active),
	INDEX app (appuid),
	INDEX session_uid (sessionuid),
	INDEX group_uid (groupuid),
	INDEX owner (owneruid)
) @DBENGINE@;

CREATE INDEX fk_works_hosts_idx ON works (expectedhostUID); 
 
-- 
-- since 7.0.0 deleted rows are stored in history table
-- 
CREATE TABLE IF NOT EXISTS works_history LIKE works;
-- ALTER TABLE works_history ADD PRIMARY KEY USING HASH (uid);
-- ALTER TABLE works_history ADD INDEX (appuid);


-- 
-- Insert a priviliged user to be able to manage your platform
-- 
-- 

-- insert into usergroups (uid,label,project) values("@ADMINGROUPUID@","admin", "false");
-- insert into usergroups (uid,label,project) values("@PUBLICWORKERSGROUPUID@","publicworkers", "false");
-- accessrights = 1792 = 0x700
insert into users (uid,login,password,fname,lname,email,rights,owneruid,accessrights) values ("@ADMINUID@","@DBADMIN@","@DBADMINPASSWORD@", "@DBADMINFNAME@", "@DBADMINLNAME@", "@DBADMINEMAIL@","SUPER_USER","@ADMINUID@","1792");
insert into users (uid,login,password,fname,lname,email,rights,owneruid,accessrights) values ("@WORKERUID@","@DBWORKER@","@DBWORKERPASSWORD@", "@DBADMINFNAME@", "@DBADMINLNAME@", "@DBADMINEMAIL@","WORKER_USER","@ADMINUID@","1792");







-- 
-- End Of File
-- 
