From 26b7237aab8ec05526814be18603d18f537b3978 Mon Sep 17 00:00:00 2001 From: William Carroll Date: Sun, 9 Aug 2020 10:22:12 +0100 Subject: Sketch database schema Defining a few tables in init.sql to sketch a few records that I need to persist. --- .../sandbox/learnpianochords/src/server/init.sql | 41 ++++++++++++++++++++++ 1 file changed, 41 insertions(+) create mode 100644 website/sandbox/learnpianochords/src/server/init.sql (limited to 'website/sandbox/learnpianochords/src/server/init.sql') 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; -- cgit 1.4.1