USE PandaMetaDB;

-- Instances: pilotqueue, pilotqueue_bnl
CREATE TABLE `pilotqueue` (
       `jobid` varchar(100) NOT NULL default '',   -- batch system's job identifier
       `tpid` varchar(120) NOT NULL default '',     -- AutoPilot job identifier
       `url` varchar(200) NOT NULL default '',     -- URL for job info
       `nickname` varchar(60) NOT NULL default '', -- queue nickname
       `system` varchar(20) NOT NULL default '',   -- queue system
       `user` varchar(60) NOT NULL default '',     -- username from which update was done
       `host` varchar(60) NOT NULL default '',     -- host from which update was done
       `submithost` varchar(60) NOT NULL default '', -- submit host name
       `workernode` varchar(60) NOT NULL default '', -- worker node name
       `schedd_name` varchar(60) NOT NULL default '', -- schedd (glidein) name for pilot factory
       `queueid` varchar(60) NOT NULL default '',
       `type` varchar(20) NOT NULL default '',     -- pilot type
       `PandaID` int,                              -- PandaID(s) run by pilot
       `tcheck` timestamp,                         -- time of the latest status check
       `state` varchar(30) NOT NULL default '',    -- current state
       `tstate` timestamp,                         -- time current state was entered
       `tenter` timestamp,
       `tsubmit` timestamp,
       `taccept` timestamp,
       `tschedule` timestamp,
       `tstart` timestamp,
       `tend` timestamp,
       `tdone` timestamp,
       `tretrieve` timestamp,
       `status` varchar(20) NOT NULL default '',
       `errcode` int not null default 0,
       `errinfo` varchar(150) NOT NULL default '',
       `message` text,
       PRIMARY KEY (`tpid`),
       INDEX (`PandaID`),
       INDEX (`nickname`),
       INDEX (`queueid`),
       INDEX (`state`)
) TYPE=MyISAM;


CREATE TABLE `cloudconfig` (
       `name` varchar(20) NOT NULL default '',           -- cloud name
       `description` varchar(50) NOT NULL default '',    -- cloud description
       `tier1` varchar(20) NOT NULL default '',          -- Site name of Tier 1
       `tier1SE` varchar(200) NOT NULL default '',       -- Destination SE for Tier 1 data consolidation
       `relocation` varchar(10)  NOT NULL default '',    -- relocation policy
       `weight` int NOT NULL default 0,                  -- Tier 1 weight
       `server` varchar(100) NOT NULL default '',        -- URL of Panda server handling this cloud
       `status` varchar(20) NOT NULL default '',         -- online, offline
       `transtimelo` int NOT NULL default 0,             -- optional cloud-specific transferring state timeout, low priority
       `transtimehi` int NOT NULL default 0,             -- high priority
       `waittime` int NOT NULL default 0,                -- optional wait state timeout
       `space` int NOT NULL default 0,
       `validation` varchar(20) NOT NULL default '',
       `fasttrack` varchar(20) NOT NULL default '',
       `comment` varchar(200) NOT NULL default '',
       `moduser` varchar(30) NOT NULL default '',
       `modtime` timestamp,
       `mcshare` int NOT NULL default 0,
       `countries` varchar(80) not null default '',
       PRIMARY KEY (`name`)
) TYPE=MyISAM;

CREATE TABLE `cloudspace` (
       `cloud` varchar(20) NOT NULL default '',           -- cloud name
       `store` varchar(50) NOT NULL default '',          -- storage name
       `space` int NOT NULL default 0,                   -- total space
       `freespace` int NOT NULL default 0,               -- available space
       `moduser` varchar(30) NOT NULL default '',
       `modtime` timestamp,
       PRIMARY KEY (`cloud`,`store`)
) TYPE=MyISAM;

CREATE TABLE `schedconfig` (
       `name` varchar(60) NOT NULL default 'default',  -- configuration tag for a set of queues
       `nickname` varchar(60) NOT NULL default '',     -- nickname for this queue
       `queue` varchar(60) NOT NULL default '',        -- name of this queue
       `siteid` varchar(60) NOT NULL default '',       -- PandaSiteID
       `localqueue` varchar(20) NOT NULL default '',
       `system` varchar(60) NOT NULL default '',       -- system the queue uses. lcg, lsf,...
       `sysconfig` varchar(20) NOT NULL default '',    -- name of config in sysconfig table
       `environ` varchar(250) NOT NULL default '',     -- any needed env vars
       `gatekeeper` varchar(40) NOT NULL default '',
       `jobmanager` varchar(80) NOT NULL default '',
       `se` varchar(250) NOT NULL default '',           -- Storage element name. Output SE, if different from input.
       `seopt` varchar(255) NOT NULL default '',        -- Optional override SE
       `sein` varchar(60) NOT NULL default '',         -- default input SE, if different from output
       `seinopt` varchar(60) NOT NULL default '',      -- optional override input SE, if different from default
       `sepath` varchar(80) NOT NULL default '',
       `seprodpath` varchar(200) NOT NULL default '',
       `setokens` varchar(80) NOT NULL default '',
       `lfchost` varchar(80) NOT NULL default '',
       `lfcpath` varchar(80) NOT NULL default '',
       `lfcprodpath` varchar(80) NOT NULL default '',
       `ddm` varchar(120) NOT NULL default '',          -- siteddm config name
       `envsetup` varchar(200) not null default '',    -- command(s) to set up local environment
       `envsetupin` varchar(200) not null default '',    -- command(s) to set up local environment
       `jdladd` text NOT NULL default '',
       `globusadd` varchar(100) NOT NULL default '',
       `jdl` varchar(60) NOT NULL default '',          -- jdl name
       `jdltxt` text,                                  -- jdl itself
       `version` varchar(60) NOT NULL default '',      -- system version
       `site` varchar(60) NOT NULL default '',         -- site. CE name?
       `region` varchar(60) NOT NULL default '',       -- region (US, France, etc.)
       `cloud` varchar(60)  NOT NULL default '',       -- production cloud
       `gstat` varchar(60) NOT NULL default '',        -- site name in gstat
       `tags` varchar(200) NOT NULL default '',        -- tags to group queues
       `cmd` varchar(200) NOT NULL default '',         -- submit command (%s = jdl file)
       `lastmod` timestamp,
       `errinfo` varchar(80) not null default '',
       `nqueue` int NOT NULL default 0,                -- default number of pilots to maintain in queue
       `comment` text,
       `releases` varchar(500) not null default '',
       `memory` int not null default 0,
       `maxtime` int not null default 0,
       `space` int not null default 0,                 -- available disk space in GB
       `tspace` timestamp,
       `appdir` varchar(80) not null default '',
       `datadir` varchar(80) not null default '',
       `tmpdir` varchar(80) not null default '',
       `wntmpdir` varchar(80) not null default '',
       `recoverdir` varchar(80) not null default '',
       `dq2url` varchar(80) not null default '',
       `special_par` varchar(80) not null default '',
       `python_path` varchar(80) not null default '',
       `nodes` integer not null default 0,
       `copytool` varchar(80) not null default '',
       `copytoolin` varchar(180) not null default '',
       `copysetup` varchar(200) not null default '',
       `copysetupin` varchar(200) not null default '',
       `copyprefix` varchar(160) not null default '',
       `copyprefixin` varchar(80) not null default '',
       `proxy` varchar(80) NOT NULL default '',
       `retry` varchar(10) not null default '',
       `queuehours` mediumint not null default 0,
       `cmtconfig` varchar(250) NOT NULL default '',
       `glexec` varchar(10) not null default '',
       `defaulttoken` varchar(100) not null default '',
       `priorityoffset` varchar(60) not null default '',
       `allowedgroups` varchar(100) not null default '',
       `status` varchar(10) not null default '',
       `pcache` varchar(100) not null default '',
       PRIMARY KEY (`nickname`)
) TYPE=MyISAM;

CREATE TABLE `schedinstance` (
       `name` varchar(60) NOT NULL default 'default',  -- configuration tag for a set of queues
       `nickname` varchar(60) NOT NULL default '',     -- nickname for this queue
       `pandasite` varchar(60) NOT NULL default '',    -- site used to identify to Panda
       `nqueue` int NOT NULL default 0,                -- number of pilots to maintain in queue
       `nqueued` int NOT NULL default 0,               -- number of pilots currently queued
       `nrunning` int NOT NULL default 0,              -- number of pilots currently running
       `nfinished` int NOT NULL default 0,             -- number of pilots exited cleanly
       `nfailed` int NOT NULL default 0,               -- number of pilots exited badly
       `naborted` int NOT NULL default 0,              -- number of pilots killed waiting
       `njobs` int NOT NULL default 0,                 -- number of Panda jobs currently running
       `tvalid` timestamp,                             -- time of last validation (successful job)
       `lastmod` timestamp,
       `errinfo` varchar(80) not null default '',
       `ndone` int not null default 0,                 -- number of done jobs, for time avg
       `totrunt` int not null default 0,               -- sum of job durations for done jobs, for time avg
       `comment` text,
       PRIMARY KEY (`nickname`,`pandasite`)
       INDEX (`nickname`),
) TYPE=MyISAM;

CREATE TABLE `taginfo` (
       `tag` varchar(30) NOT NULL default '',          -- tag name
       `description` varchar(100) NOT NULL default '', -- tag description
       `nqueues` int NOT NULL default 0,
       `queues` text NOT NULL default '',
       PRIMARY KEY (`tag`)
) TYPE=MyISAM;


CREATE TABLE `siteddm` (
       `name` varchar(60) NOT NULL default '',         -- site name
       `incmd`  varchar(60) NOT NULL default '',         -- data mover command
       `inpath` varchar(200) NOT NULL default '',      -- input url with path
       `inopts` varchar(60) NOT NULL default '',       -- cmd opts for input copy
       `outcmd`  varchar(60) NOT NULL default '',         -- data mover command
       `outopts` varchar(60) NOT NULL default '',      -- cmd opts for output copy
       `outpath` varchar(200) NOT NULL default '',      -- output url with path
       PRIMARY KEY (`name`)
) TYPE=MyISAM;

CREATE TABLE `servicelist` (
       `id` int NOT NULL AUTO_INCREMENT,
       `name` varchar(60) NOT NULL default 'default',  -- unique service name
       `host` varchar(80) NOT NULL default '',         -- service host
       `pid` int,                                      -- process ID
       `userid` varchar(40) NOT NULL default '',       -- user running the service
       `type` varchar(30) NOT NULL default '',         -- service type
       `grp` varchar(20) NOT NULL default '',          -- service group
       `description` varchar(200) NOT NULL default '', -- description of service
       `url` varchar(200) NOT NULL default '',         -- service URL
       `testurl` varchar(200) NOT NULL default '',     -- test URL
       `response` varchar(200) NOT NULL default '',    -- test URL response regexp
       `tresponse` int,                                -- time to get test response (sec)
       `tstart` timestamp,                             -- last start time
       `tstop` timestamp,                              -- last stop time
       `tcheck` timestamp,                             -- time of last status check
       `cyclesec` int,                                 -- cycle time in seconds
       `status` varchar(20) NOT NULL default '',       -- status at last report
       `lastmod` timestamp,
       `config` varchar(200),                          -- service configuration
       `message` text,                                 -- message, log
       `restartcmd` text,                              -- restart information
       `doaction` text,                                -- to send command to service
       PRIMARY KEY (`id`),
       UNIQUE (`name`, `host`, `config`, `userid`)
) TYPE=MyISAM;

CREATE TABLE `pilottype` (
       `name` varchar(60) NOT NULL default '',  -- identifying name for pilot type
       `script` varchar(60) NOT NULL default '',  -- script name in AutoPilot svn
       `url` varchar(200) NOT NULL default '',  -- url location of script
       `system` varchar(60) NOT NULL default '',  -- systems it works on (lcg, osg, ...)
       PRIMARY KEY (`name`)
) TYPE=MyISAM;

CREATE TABLE `submithosts` (
       `name` varchar(60) NOT NULL default '',      -- identifying name
       `nickname` varchar(20) NOT NULL default '',  -- short name
       `host` varchar(60) NOT NULL default '',      -- regexp to match hostname
       `system` varchar(60) NOT NULL default '',    -- system (lcg, osg)
       `rundir` varchar(200) NOT NULL default '',   -- job dirs created under here
       `runurl` varchar(200) NOT NULL default '',   -- url for inspecting run dirs
       `pilotqueue` varchar(20) NOT NULL default '',-- DB table used for pilot queue
       `jdltxt` text,                               -- scheduler submission jdl for schedd glidein hosts
       PRIMARY KEY (`host`)
) TYPE=MyISAM;

CREATE TABLE `jdllist` (
       `name` varchar(60) NOT NULL default '',      -- identifying name for jdl
       `host` varchar(60) NOT NULL default '',      -- regexp to match hostname
       `system` varchar(20) NOT NULL default '',    -- system (lcg, osg)
       `jdl` text,                                  -- jdl
       PRIMARY KEY (`name`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS sysconfig;                     -- configure submission system
CREATE TABLE `sysconfig` (
       `name` varchar(60) NOT NULL default '',      -- identifying name
       `system` varchar(20) NOT NULL default '',    -- system (lcg, osg)
       `config` text,
       PRIMARY KEY (`name`, `system`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS proxykey;                     -- configure submission system
CREATE TABLE `proxykey` (
       `id` int NOT NULL AUTO_INCREMENT,
       `dn` varchar(100) NOT NULL default '',       -- user's DN
       `credname` varchar(40) NOT NULL default '',       -- proxy key
       `created` timestamp,
       `expires` timestamp,
       `origin` varchar(80) NOT NULL default '',    -- hostname of the machine where the key was generated
       `myproxy` varchar(80) NOT NULL default '',   -- address of the myproxy server where the key is stored
       PRIMARY KEY (`id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS pandaconfig;                     -- record Panda configuration information
CREATE TABLE `pandaconfig` (
       `name` varchar(60) NOT NULL default '',        -- identifying config name
       `controller` varchar(20) NOT NULL default '',  -- pilotController version
       PRIMARY KEY (`name`)
) TYPE=MyISAM;
