warden_3.0_postgres.sql

SET TimeZone='+00:00';

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

--
-- Database: "warden3"
--

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

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

CREATE TABLE IF NOT EXISTS "categories" (
  "id" int NOT NULL UNIQUE,
  "category" text NOT NULL,
  "subcategory" text DEFAULT NULL,
  "cat_subcat" text NOT NULL
);

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,
  "hostname" text NOT NULL,
  "note" text NULL,
  "valid" boolean NOT NULL DEFAULT true,
  "name" text NOT NULL,
  "secret" text NULL,
  "read" boolean NOT NULL DEFAULT true,
  "debug" boolean NOT NULL DEFAULT false,
  "write" boolean NOT NULL DEFAULT false,
  "test" boolean NOT NULL DEFAULT false
);

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),
  "idea_id" text UNIQUE,
  "received" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "client_id" int NOT NULL REFERENCES "clients" ("id"),
  "data" bytea NOT NULL,
  "valid" boolean NOT NULL DEFAULT true
);

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,
  "tag" text NOT NULL
);

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")
);