--
-- init.sql -- create database
--
-- Copyright (c) 2021-2022 David Demelier <markand@malikania.fr>
--
-- Permission to use, copy, modify, and/or distribute this software for any
-- purpose with or without fee is hereby granted, provided that the above
-- copyright notice and this permission notice appear in all copies.
--
-- THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
-- WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
-- MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
-- ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
-- WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
-- ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
-- OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
--

CREATE TABLE IF NOT EXISTS `project`(
	`name` TEXT NOT NULL PRIMARY KEY,
	`desc` TEXT NOT NULL,
	`url` TEXT NOT NULL,
	`script` TEXT NOT NULL,
	`date` INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);

CREATE TABLE IF NOT EXISTS `worker`(
	`name` TEXT NOT NULL PRIMARY KEY,
	`desc` TEXT NOT NULL,
	`date` INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);

CREATE TABLE IF NOT EXISTS `job`(
	`tag` TEXT NOT NULL,
	`project_name` INTEGER NOT NULL REFERENCES `project` (name),
	`date` INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);

CREATE TABLE IF NOT EXISTS `jobresult`(
	`job_id` INTEGER NOT NULL REFERENCES `job` (rowid),
	`worker_name` INTEGER NOT NULL REFERENCES `worker` (name),
	`console` TEXT DEFAULT NULL,
	`exitcode` INTEGER DEFAULT 0,
	`sigcode` INTEGER DEFAULT 0,
	`date` INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);

CREATE TABLE IF NOT EXISTS `property`(
	`key` TEXT PRIMARY KEY NOT NULL,
	`value` TEXT
);