warden_3.0_postgres.sql

SET TimeZone='+00:00';

CREATE COLLATION IF NOT EXISTS case_insensitive (
  provider = icu,
  locale = 'und-u-ks-level2',
  deterministic = false
);

-- ---------------------------------------------------------

--
-- Database: "warden3"
--

-- --------------------------------------------------------

--
-- Table structure for table "categories"
--

CREATE TABLE IF NOT EXISTS "categories" (
  "id" int NOT NULL UNIQUE CHECK ("id" >= 0),
  "category" text NOT NULL COLLATE case_insensitive,
  "subcategory" text DEFAULT NULL COLLATE case_insensitive,
  "cat_subcat" text NOT NULL COLLATE case_insensitive
);

CREATE INDEX IF NOT EXISTS "cat_sub" ON "categories" ("cat_subcat");

-- --------------------------------------------------------

--
-- Table structure for table "clients"
--

CREATE TABLE IF NOT EXISTS "clients" (
  "id" SERIAL PRIMARY KEY,
  "registered" timestamp NOT NULL DEFAULT '1970-01-01 00:00:00',
  "requestor" text NOT NULL COLLATE case_insensitive,
  "hostname" text NOT NULL COLLATE case_insensitive,
  "note" text NULL COLLATE case_insensitive,
  "valid" smallint NOT NULL DEFAULT '1' CHECK ("valid" >= 0),
  "name" text NOT NULL,
  "secret" text NULL,
  "read" smallint NOT NULL DEFAULT '1' CHECK ("read" >= 0),
  "debug" smallint NOT NULL DEFAULT '0' CHECK ("debug" >= 0),
  "write" smallint NOT NULL DEFAULT '0' CHECK ("write" >= 0),
  "test" smallint NOT NULL DEFAULT '0' CHECK ("test" >= 0)
);

CREATE INDEX IF NOT EXISTS "clients_1" ON "clients" ("valid", "secret", "hostname");
CREATE INDEX IF NOT EXISTS "clients_2" ON "clients" ("valid", "name");

-- --------------------------------------------------------

--
-- Table structure for table "events"
--

CREATE TABLE IF NOT EXISTS "events" (
  "id" bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY (MINVALUE 2),
  "received" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "client_id" int NOT NULL REFERENCES "clients" ("id"),
  "data" bytea NOT NULL,
  "valid" smallint NOT NULL DEFAULT '1' CHECK ("valid" >= 0)
);

CREATE INDEX IF NOT EXISTS "id" ON "events" ("id", "client_id");
CREATE INDEX IF NOT EXISTS "received" ON "events" ("received");

-- --------------------------------------------------------

--
-- Table structure for table "event_category_mapping"
--

CREATE TABLE IF NOT EXISTS "event_category_mapping" (
  "event_id" bigint NOT NULL REFERENCES "events" ("id") ON DELETE CASCADE,
  "category_id" int NOT NULL,
  PRIMARY KEY ("event_id", "category_id"),
  CONSTRAINT "event_category_mapping_category_id_fk" FOREIGN KEY ("category_id") REFERENCES "categories" ("id")
);

-- --------------------------------------------------------

--
-- Table structure for table "last_events"
--

CREATE TABLE IF NOT EXISTS "last_events" (
  "id" SERIAL PRIMARY KEY,
  "client_id" int NOT NULL REFERENCES "clients" ("id"),
  "event_id" bigint REFERENCES "events" ("id"),
  "timestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS "client_id" ON "last_events" ("client_id", "event_id");

-- --------------------------------------------------------

--
-- Table structure for table "tags"
--

CREATE TABLE IF NOT EXISTS "tags" (
  "id" int NOT NULL UNIQUE CHECK ("id" >= 0),
  "tag" text NOT NULL COLLATE case_insensitive
);

CREATE INDEX IF NOT EXISTS "id_tag_name" ON "tags" ("id", "tag");
CREATE INDEX IF NOT EXISTS "tag_name" ON "tags" ("tag");

-- --------------------------------------------------------

--
-- Table structure for table "event_tag_mapping"
--

CREATE TABLE IF NOT EXISTS "event_tag_mapping" (
  "event_id" bigint NOT NULL REFERENCES "events" ("id") ON DELETE CASCADE,
  "tag_id" int NOT NULL,
  PRIMARY KEY ("event_id", "tag_id"),
  CONSTRAINT "event_tag_mapping_tag_id_fk" FOREIGN KEY ("tag_id") REFERENCES "tags" ("id")
);