SQL DDL for buckets, announcements and videos

Folded the announcement_videos table into videos since in our initial model
a video cannot exist without an announcement; we can always re-introduce
the table later if that changes

diff --git a/share/sql/idiotbox-1.0-sqlite.sql b/share/sql/idiotbox-1.0-sqlite.sql
new file mode 100644 (file)
index 0000000..c9d24ae
--- /dev/null
+++ b/share/sql/idiotbox-1.0-sqlite.sql
@@ -0,0 +1,20 @@
+CREATE TABLE buckets (
+  slug TEXT NOT NULL PRIMARY KEY,
+  name TEXT NOT NULL
+);
+
+CREATE TABLE announcements (
+  id INTEGER NOT NULL PRIMARY KEY,
+  made_at DATETIME NOT NULL,
+  bucket_slug TEXT REFERENCES buckets(slug)
+);
+
+CREATE TABLE videos (
+  slug TEXT NOT NULL,
+  bucket_slug TEXT NOT NULL REFERENCES buckets(slug),
+  name TEXT NOT NULL,
+  author TEXT NOT NULL,
+  details TEXT NOT NULL DEFAULT '',
+  announcement_id INTEGER REFERENCES announcements(id),
+  PRIMARY KEY (slug, bucket_slug)
+);