create initial database migration

1 files changed, 61 insertions, 0 deletions

diff --git a/migrations/20230218162011_blossom.sql b/migrations/20230218162011_blossom.sql
new file mode 100644
index 0000000..188bbf8
--- /dev/null
+++ b/migrations/20230218162011_blossom.sql
@@ -0,0 +1,61 @@
+PRAGMA foreign_keys = on;
+
+create table post_types (
+    name varchar(32) not null primary key
+);
+
+insert into post_types (name) values ('article'), ('note');
+
+create table posts (
+    id integer not null primary key autoincrement,
+    created_at bigint not null default (unixepoch()),
+    updated_at bigint,
+    post_type varchar(32) not null default ('note'),
+    foreign key (post_type) references post_types(name)
+);
+
+create table tags (
+    name varchar(128) not null primary key
+);
+
+create table posts_tags (
+    post_id integer not null,
+    tag varchar(128) not null,
+    foreign key (post_id) references posts(id),
+    foreign key (tag) references tags(name),
+    primary key (post_id, tag)
+);
+
+create table articles (
+    post_id integer not null,
+    source varchar(128) not null,
+    hash varchar(64) not null,
+    foreign key (post_id) references posts(id)
+);
+
+create table notes (
+    post_id integer not null,
+    content text,
+    foreign key (post_id) references posts(id)
+);
+
+create table media_types (
+    name varchar(32) not null primary key
+);
+
+insert into media_types (name) values ('image'), ('video'), ('audio');
+
+create table media (
+    id integer not null primary key autoincrement,
+    media_type varchar(32) not null,
+    source varchar(128) not null,
+    foreign key (media_type) references media_types(name)
+);
+
+create table notes_media (
+    note_id integer not null, 
+    media_id integer not null,
+    foreign key (note_id) references notes(note_id),
+    foreign key (media_id) references media(id),
+    primary key (note_id, media_id)
+);
\ No newline at end of file