summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorEvan Prodromou <evan@prodromou.name>2008-05-05 09:50:19 -0400
committerEvan Prodromou <evan@prodromou.name>2008-05-05 09:50:19 -0400
commit3b89aee0b77b9f483021c536499ea89ca747439d (patch)
tree4ad800116f0205d6162d19cdca1be276015c226f
initial database
darcs-hash:20080505135019-84dde-f35cef3a6f3c990937376c21e5e6d70c8eb22f80.gz
-rw-r--r--stoica.sql58
1 files changed, 58 insertions, 0 deletions
diff --git a/stoica.sql b/stoica.sql
new file mode 100644
index 000000000..f7f32ad76
--- /dev/null
+++ b/stoica.sql
@@ -0,0 +1,58 @@
+/* local and remote users have profiles */
+
+create table profile (
+ id integer auto_increment primary key comment 'unique identifier',
+ nickname varchar(64) not null comment 'nickname or username',
+ fullname varchar(255) comment 'display name',
+ profileurl varchar(255) comment 'URL, cached so we dont regenerate',
+ homepage varchar(255) comment 'identifying URL',
+ bio varchar(140) comment 'descriptive biography',
+ location varchar(255) comment 'physical location',
+ created datetime not null comment 'date this record was created',
+ modified timestamp comment 'date this record was modified',
+
+ index profile_nickname_idx (nickname)
+);
+
+/* local users */
+
+create table user (
+ id integer primary key comment 'foreign key to profile table' references profile (id),
+ password varchar(255) comment 'salted password, can be null for OpenID users',
+ email varchar(255) unique key comment 'email address for password recovery etc.',
+ created datetime not null comment 'date this record was created',
+ modified timestamp comment 'date this record was modified'
+);
+
+/* remote people */
+
+create table remote_profile (
+ id integer primary key comment 'foreign key to profile table' references profile (id),
+ url varchar(255) unique key comment 'URL we use for updates from this profile (distinct from "home page" url)',
+ created datetime not null comment 'date this record was created',
+ modified timestamp comment 'date this record was modified'
+);
+
+create table subscription (
+ subscriber integer not null comment 'profile listening',
+ subscribed integer not null comment 'profile being listened to',
+ token varchar(255) comment 'authorization token',
+ created datetime not null comment 'date this record was created',
+ modified timestamp comment 'date this record was modified',
+
+ constraint primary key (subscriber, subscribed),
+ index subscription_subscriber_idx (subscriber),
+ index subscription_subscribed_idx (subscribed)
+);
+
+create table notice (
+ id integer auto_increment primary key comment 'unique identifier',
+ profile_id integer not null comment 'who made the update' references profile (id),
+ content varchar(140) comment 'update content',
+ rendered varchar(140) comment 'pre-rendered content',
+ url varchar(255) comment 'URL of any attachment (image, video, bookmark, whatever)',
+ created datetime not null comment 'date this record was created',
+ modified timestamp comment 'date this record was modified',
+
+ index notice_profile_id_idx (profile_id)
+); \ No newline at end of file