Add method of looking up talk urls on ACT conferences and adding to the talk data

[catagits/App-IdiotBox.git] / share / sql / idiotbox-2.0-sqlite.sql

diff --git a/share/sql/idiotbox-2.0-sqlite.sql b/share/sql/idiotbox-2.0-sqlite.sql
new file mode 100644 (file)
index 0000000..ed4535d
--- /dev/null
+++ b/share/sql/idiotbox-2.0-sqlite.sql
@@ -0,0 +1,24 @@
+CREATE TABLE buckets (
+  slug TEXT NOT NULL PRIMARY KEY,
+  name TEXT NOT NULL,
+  act_url TEXT 
+);
+
+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,
+  act_talk_id INTEGER,
+  details TEXT NOT NULL DEFAULT '',
+  announcement_id INTEGER NOT NULL,
+  PRIMARY KEY (slug, bucket_slug),
+  FOREIGN KEY (announcement_id, bucket_slug)
+    REFERENCES announcements(id, bucket_slug)
+);