init.sql

CREATE TABLE devices(id INTEGER PRIMARY KEY AUTOINCREMENT, 
	name VARCHAR(200) NOT NULL, 
	location TEXT DEFAULT NULL,
	manual TEXT DEFAULT NULL,
	sticker_id VARCHAR(100) UNIQUE NOT NULL,
	comment TEXT DEFAULT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL);

CREATE TRIGGER devices_ts_update
	AFTER UPDATE ON devices FOR EACH ROW
	WHEN NEW.updated_at <= OLD.updated_at
BEGIN
	UPDATE devices SET updated_at = CURRENT_TIMESTAMP WHERE id=OLD.id;
END;

CREATE TABLE audits(id INTEGER PRIMARY KEY AUTOINCREMENT,
	invalid BOOLEAN DEFAULT FALSE,
	device_id INTEGER NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL);
-- TODO: valid_until

CREATE TRIGGER audits_ts_update
	AFTER UPDATE ON audits FOR EACH ROW
	WHEN NEW.updated_at <= OLD.updated_at
BEGIN
	UPDATE audits SET updated_at = CURRENT_TIMESTAMP WHERE id=OLD.id;
END;

CREATE TABLE externals(id INTEGER PRIMARY KEY AUTOINCREMENT, 
	name VARCHAR(200) NOT NULL, 
	url_pattern TEXT NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL);

CREATE TABLE externals_links (id INTEGER PRIMARY KEY AUTOINCREMENT, 
	external_id INTEGER DEFAULT NULL, 
	device_id INTEGER NOT NULL,
	url TEXT NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL);