# Database module
The SQLite module functions as a `gen_server` which holds the SQLite handle.
It's main jobs are:
1. have an API to allow building of clients
2. be able to handle sync requests
## general info
| `post_type` numeric id | common name  | description |
|------------------------|--------------|-------------|
| 0                      | `post/text`  | a textual chat message, posted to a channel |
| 1                      | `post/delete`| the deletion of a previously published post |
| 2                      | `post/info`  | set or clear informative key/value pairs on a user  |
| 3                      | `post/topic` | set or clear a channel's topic string |
| 4                      | `post/join`  | announce membership to a channel |
| 5                      | `post/leave` | announce cessation of membership to a channel |
## channel state req
- most recent state messages for a channel
- all posts except `post/text`
## Schema sketch
Polymorphic table for all kinds of posts 
```sql
create table posts(
  -- these exist for all posts
  -- 
  id integer primary key AUTOINCREMENT,
  hash blob unique not null,
  type integer not null check (type >= 0 AND type <= 5 ),
  -- milliseconds since UNIX Epoch
  timestamp integer not null check (timestamp > 0),
  -- concatenated
  link_hashes blob not null,
  -- public key that authored this post
  public_key blob not null, 
  signature  blob not null,
  -- depends on postType
  channel text,       -- all but delete have this one
  -- join & leave don't have other fields then channel
  text text,
  topic text,
  deltedHashes blob,    -- concatenated
  infos blob,           -- encoded as (json?) map {k=>v, k=>v}
  -- `post/text`  | a textual chat message, posted to a channel |
  CONSTRAINT is_text check (
    type != 0 OR (channel is not null AND text is not null)
  ),
  -- `post/delete`| the deletion of a previously published post |
  CONSTRAINT is_delete check (
    type != 1 OR (deletedHashes is not null)
  ),
  -- `post/info`  | set or clear informative key/value pairs on a user
  CONSTRAINT is_info check (
    type != 2 OR (channel is not null AND infos is not null)
  ),
  -- `post/topic` | set or clear a channel's topic string
  CONSTRAINT is_topic check (
    type != 3 OR (channel is not null AND topic is not null)
  ),
  -- `post/join`  | announce membership to a channel
  CONSTRAINT is_join check (
    type != 4 OR (channel is not null)
  ),
  -- `post/leave` | announce cessation of membership to a channel
  CONSTRAINT is_leave check (
    type != 5 OR (channel is not null)
  )
);
```
### Ideas/concerns
- re-encode posts when answering post requests
  - let's assume that's fine for now
  - otherwise have a table where we store posts as-is
- repeat public key all the time in `posts`?
  - could have a `users` table that just has `id:public_key:name`
## common queries
### Find leaf elements in a tree (unreferenced posts)
```sql
create table links(
  id integer primary key,
  title text not null,
  path text not null
);
insert into links (id, title, path) VALUES
  (1, "things", "/1/"),
  (2, "fruit", "/1/2/"),
  (3, "apples", "/1/2/3/"),
  (4, "oranges", "/1/2/4/");
-- find them
SELECT *
FROM links
WHERE id NOT IN (
    SELECT DISTINCT substr(path, 1, instr(substr(path, 2), '/') + 1)
    FROM links
);
```
### simple ploymorphic check example
```sql
create table poly(
  -- all types share these
  id integer primary key,
  type integer not null check (type > 0 AND type < 4),
  -- depends on type
  foo text,
  foo_count integer,
  bar integer,
  baz blob,
  -- either it's a different type OR it has to fullfill these things
  constraint is_foo check (
    type != 1 OR (foo is not null AND foo_count is not null)
  ),
  constraint is_bar check (
    type != 2 OR bar is not null 
  ),
  constraint is_foo check (
    type != 3 OR baz is not null 
  )
)
```