From 6841765fcbfadbd4e3006f5fbcea45194c92e5a1 Mon Sep 17 00:00:00 2001
From: Tom Smeding <tom@tomsmeding.com>
Date: Sun, 19 Jan 2025 22:14:03 +0100
Subject: server: register & login

---
 server/schema.sql | 62 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 62 insertions(+)
 create mode 100644 server/schema.sql

(limited to 'server/schema.sql')

diff --git a/server/schema.sql b/server/schema.sql
new file mode 100644
index 0000000..54eac1f
--- /dev/null
+++ b/server/schema.sql
@@ -0,0 +1,62 @@
+-- database schema version 1
+
+pragma foreign_keys = on;
+
+create table Meta (
+  version integer not null
+);
+insert into Meta(version) values (1);
+
+create table Users (
+  id integer primary key,
+  username text unique not null,
+  passhash blob not null
+);
+
+create table Logins (
+  user integer not null,
+  token text unique not null,
+  expire integer not null  -- unix timestamp, token invalid >= this time
+);
+create index Logins_user on Logins (user);
+
+create table Files (
+  id integer primary key,
+  owner integer not null,
+  path text unique not null,  -- names separated with single '/', neither start nor end with '/'
+
+  foreign key (owner) references Users(id) on delete cascade
+);
+
+-- Undo history of a file
+create table Updates (
+  id integer primary key,
+  file integer not null,
+  idx integer not null,  -- index in the update history of this file; first is 0
+
+  position integer not null,
+  -- If old_value is null & new_value exists:
+  --   insert item _before_ the given position; the new item has itemid.
+  -- If old_value exists & new_value is null:
+  --   delete item at the given position; that item had itemid.
+  -- If old_value exists & new_value exists:
+  --   modify the item at the given position, which has itemid.
+  -- If old_value is null & new_value is null:
+  --   <disallowed>.
+  itemid integer not null,
+  old_value blob null,
+  new_value blob null,
+
+  foreign key (file) references Files(id) on delete cascade,
+  unique (file, idx)
+);
+
+-- Current state of a file
+create table Chunks (
+  id integer primary key,
+  file integer not null,
+  position integer not null,
+  contents blob not null,
+
+  unique (file, position)
+);
-- 
cgit v1.2.3-70-g09d2