Mix SQL from a real project

subman.sql

CREATE TABLE Subman_Publisher
(
  id                    string AUTO PRIMARY KEY, -- auto-generated, uuid
  stacks                set of string REFERENCES Subman_Stack, -- primary key ref
  subscription_count    int
);

CREATE TABLE Subman_Stack 
(
  id              string AUTO PRIMARY KEY,
  publisher       string NOT NULL REFERENCES Subman_Publisher,
  subscriptions   set of string REFERENCES Subman_Subscription
);

CREATE TABLE Subman_Subscription
(
  id              string AUTO PRIMARY KEY,
  user            string NOT NULL REFERENCES Subman_User,
  stack           string NOT NULL REFERENCES Subman_Stack,
  publisher       string NOT NULL REFERENCES Subman_Publisher,
  notify_freq     string NOT NULL,
  language        string NOT NULL,
  last_notified   int,
  active          int
);

--- Docs by time

CREATE TABLE Subman_NewDoc
(
  stack           string PRIMARY KEY REFERENCES Subman_Stack,
  timestamp       int NOT NULL SECONDARY KEY,
  docid           string NOT NULL
);

--- Updated stacks

CREATE TABLE Subman_NewStack
(
  time      string PRIMARY KEY,
  stacks    set of string REFERENCES Subman_Stack
);

--- Docs by user

CREATE TABLE Subman_Update_Digest
(
  user        string PRIMARY KEY REFERENCES Subman_User,
  docs        set of string
);

CREATE TABLE Subman_User 
(
  id              string PRIMARY KEY,
  email           string NOT NULL REFERENCES Subman_Email,
  confirmed       int NOT NULL, -- saves a lookup
  subscriptions   set of string REFERENCES Subman_Subscription
);

CREATE TABLE Subman_Email
(
  email       string PRIMARY KEY,
  user_id     string REFERENCES Subman_User,
  confirmed   int,
  confirm_id  string REFERENCES Subman_Unconfirmed
);

CREATE TABLE Subman_Unconfirmed
(
  id            string AUTO PRIMARY KEY,
  email         string NOT NULL REFERENCES Subman_Email,
  user_id       string NOT NULL REFERENCES Subman_User
);

CREATE TRIGGER inc_subscription_count
ON Subman_Subscription
AFTER INSERT 
AS
UPDATE Subman_Publisher
SET   Subman_Publisher.subscription_count = Subman_Publisher.subscription_count + 1
WHERE Subman_Subscription.stack = Subman_Stack.id
AND   Subman_Stack.publisher = Subman_Publisher.id;

CREATE TRIGGER dec_subscription_count
ON Subman_Subscription
AFTER DELETE 
AS
UPDATE Subman_Publisher
SET   Subman_Publisher.subscription_count = Subman_Publisher.subscription_count - 1
WHERE Subman_Subscription.stack = Subman_Stack.id
AND   Subman_Stack.publisher = Subman_Publisher.id;