about summary refs log tree commit diff
path: root/website/sandbox/learnpianochords/src/server/init.sql
diff options
context:
space:
mode:
authorWilliam Carroll <wpcarro@gmail.com>2020-08-09T09·22+0100
committerWilliam Carroll <wpcarro@gmail.com>2020-08-09T09·22+0100
commit26b7237aab8ec05526814be18603d18f537b3978 (patch)
tree1af08c396b0b886b8b083cb5c86151375cce9258 /website/sandbox/learnpianochords/src/server/init.sql
parent9df2b49afd4217edb0307a2b17d04a5978bd5b8c (diff)
Sketch database schema
Defining a few tables in init.sql to sketch a few records that I need to
persist.
Diffstat (limited to 'website/sandbox/learnpianochords/src/server/init.sql')
-rw-r--r--website/sandbox/learnpianochords/src/server/init.sql41
1 files changed, 41 insertions, 0 deletions
diff --git a/website/sandbox/learnpianochords/src/server/init.sql b/website/sandbox/learnpianochords/src/server/init.sql
new file mode 100644
index 000000000000..c220bd440636
--- /dev/null
+++ b/website/sandbox/learnpianochords/src/server/init.sql
@@ -0,0 +1,41 @@
+BEGIN TRANSACTION;
+
+DROP TABLE IF EXISTS GoogleLinkedAccounts;
+DROP TABLE IF EXISTS PayingCustomers;
+DROP TABLE IF EXISTS Sessions;
+
+-- Store some of the information that Google provides to us from the JWT.
+CREATE TABLE GoogleLinkedAccounts (
+  accountUUID TEXT CHECK(LENGTH(uuid) == 36) NOT NULL UNIQUE,
+  email TEXT NOT NULL UNIQUE,
+  tsCreated TEXT NOT NULL, -- 'YYYY-MM-DD HH:MM:SS'
+  givenName TEXT,
+  familyName TEXT,
+  fullName TEXT,
+  pictureURL TEXT,
+  locale TEXT,
+  PRIMARY KEY (accountUUID)
+);
+
+-- Track which of our customers have a paid account.
+-- Defines a one-to-one relationship between:
+--   GoogleLinkedAccounts and PayingCustomers
+CREATE TABLE PayingCustomers (
+  accountUUID TEXT,
+  tsCreated TEXT,
+  PRIMARY KEY (accountUUID),
+  FOREIGN KEY (accountUUID) REFERENCES GoogleLinkedAccounts ON DELETE CASCADE
+);
+
+-- Define mobile and web sessions for our users.
+-- Defines a one-to-many relationship between:
+--   GoogleLinkedAccounts and Sessions
+CREATE TABLE Sessions (
+  sessionUUID TEXT CHECK(LENGTH(sessionUUID) == 36) NOT NULL UNIQUE,
+  accountUUID TEXT,
+  tsCreated TEXT NOT NULL, -- 'YYYY-MM-DD HH:MM:SS'
+  PRIMARY KEY (sessionUUID)
+  FOREIGN KEY(accountUUID) REFERENCES GoogleLinkedAccounts ON DELETE CASCADE
+);
+
+COMMIT;