create_tables.sql

.read settings.sql
BEGIN;
DROP INDEX IF EXISTS updates_token_index;
DROP TABLE IF EXISTS updates;
DROP TABLE IF EXISTS hosts;
DROP TRIGGER IF EXISTS hosts_update;
DROP TRIGGER IF EXISTS updates_update;

CREATE TABLE hosts (
	token CHAR(43) PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	domain VARCHAR(255),
	zone VARCHAR(255),
	ip4addr VARCHAR(255),
	ip6addr VARCHAR(255),
	modified DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER hosts_update AFTER UPDATE ON hosts
	FOR EACH ROW WHEN OLD.modified != DATETIME()
BEGIN
	UPDATE hosts SET modified = DATETIME() WHERE token = NEW.token;
END;

CREATE TABLE updates (
	id INTEGER NOT NULL PRIMARY KEY,
	api_key VARCHAR(255),
	token CHAR(43) NOT NULL,
	cmd VARCHAR(255) NOT NULL,
	args VARCHAR(255) NOT NULL,
	modified DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY(token) REFERENCES hosts(token)
	ON DELETE CASCADE
	ON UPDATE RESTRICT
);
CREATE INDEX updates_token_index ON updates (token);

CREATE TRIGGER updates_update AFTER UPDATE ON updates
	FOR EACH ROW WHEN OLD.modified != DATETIME()
BEGIN
	UPDATE updates SET modified = DATETIME() WHERE id = NEW.id;
END;

COMMIT;