summaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/08to09_pg.sql39
-rw-r--r--db/statusnet_pg.sql91
2 files changed, 114 insertions, 16 deletions
diff --git a/db/08to09_pg.sql b/db/08to09_pg.sql
index 2eac5dadf..498a94e68 100644
--- a/db/08to09_pg.sql
+++ b/db/08to09_pg.sql
@@ -81,3 +81,42 @@ ALTER TABLE profile ADD COLUMN lon decimal(10,7) /*comment 'longitude'*/;
ALTER TABLE profile ADD COLUMN location_id integer /* comment 'location id if possible'*/;
ALTER TABLE profile ADD COLUMN location_ns integer /* comment 'namespace for location'*/;
+ALTER TABLE consumer add COLUMN consumer_secret varchar(255) not null ; /*comment 'secret value'*/
+
+ALTER TABLE token ADD COLUMN verifier varchar(255); /* comment 'verifier string for OAuth 1.0a',*/
+ALTER TABLE token ADD COLUMN verified_callback varchar(255); /* comment 'verified callback URL for OAuth 1.0a',*/
+
+create table queue_item_new (
+ id serial /* comment 'unique identifier'*/,
+ frame bytea not null /* comment 'data: object reference or opaque string'*/,
+ transport varchar(8) not null /*comment 'queue for what? "email", "jabber", "sms", "irc", ...'*/,
+ created timestamp not null default CURRENT_TIMESTAMP /*comment 'date this record was created'*/,
+ claimed timestamp /*comment 'date this item was claimed'*/,
+ PRIMARY KEY (id)
+);
+
+insert into queue_item_new (frame,transport,created,claimed)
+ select ('0x' || notice_id::text)::bytea,transport,created,claimed from queue_item;
+alter table queue_item rename to queue_item_old;
+alter table queue_item_new rename to queue_item;
+
+ALTER TABLE confirm_address ALTER column sent set default CURRENT_TIMESTAMP;
+
+create table user_location_prefs (
+ user_id integer not null /*comment 'user who has the preference'*/ references "user" (id),
+ share_location int default 1 /* comment 'Whether to share location data'*/,
+ created timestamp not null /*comment 'date this record was created'*/,
+ modified timestamp /* comment 'date this record was modified'*/,
+
+ primary key (user_id)
+);
+
+create table inbox (
+
+ user_id integer not null /* comment 'user receiving the notice' */ references "user" (id),
+ notice_ids bytea /* comment 'packed list of notice ids' */,
+
+ primary key (user_id)
+
+);
+
diff --git a/db/statusnet_pg.sql b/db/statusnet_pg.sql
index 998cc71e9..2db98550c 100644
--- a/db/statusnet_pg.sql
+++ b/db/statusnet_pg.sql
@@ -8,6 +8,10 @@ create table profile (
homepage varchar(255) /* comment 'identifying URL' */,
bio varchar(140) /* comment 'descriptive biography' */,
location varchar(255) /* comment 'physical location' */,
+ lat decimal(10,7) /* comment 'latitude'*/ ,
+ lon decimal(10,7) /* comment 'longitude'*/ ,
+ location_id integer /* comment 'location id if possible'*/ ,
+ location_ns integer /* comment 'namespace for location'*/ ,
created timestamp not null default CURRENT_TIMESTAMP /* comment 'date this record was created' */,
modified timestamp /* comment 'date this record was modified' */,
@@ -132,6 +136,7 @@ create table notice (
is_local integer default 0 /* comment 'notice was generated by a user' */,
source varchar(32) /* comment 'source of comment, like "web", "im", or "clientname"' */,
conversation integer /*id of root notice in this conversation' */ references notice (id),
+ location varchar(255) /* comment 'physical location' */,
lat decimal(10,7) /* comment 'latitude'*/ ,
lon decimal(10,7) /* comment 'longitude'*/ ,
location_id integer /* comment 'location id if possible'*/ ,
@@ -182,6 +187,7 @@ create index fave_modified_idx on fave using btree(modified);
create table consumer (
consumer_key varchar(255) primary key /* comment 'unique identifier, root URL' */,
+ consumer_secret varchar(255) not null /* comment 'secret value', */,
seed char(32) not null /* comment 'seed for new tokens by this consumer' */,
created timestamp not null default CURRENT_TIMESTAMP /* comment 'date this record was created' */,
@@ -195,6 +201,9 @@ create table token (
type integer not null default 0 /* comment 'request or access' */,
state integer default 0 /* comment 'for requests 0 = initial, 1 = authorized, 2 = used' */,
+ verifier varchar(255) /*comment 'verifier string for OAuth 1.0a'*/,
+ verified_callback varchar(255) /*comment 'verified callback URL for OAuth 1.0a'*/,
+
created timestamp not null default CURRENT_TIMESTAMP /* comment 'date this record was created' */,
modified timestamp /* comment 'date this record was modified' */,
@@ -213,17 +222,33 @@ create table nonce (
primary key (consumer_key, ts, nonce)
);
-/* One-to-many relationship of user to openid_url */
-
-create table user_openid (
- canonical varchar(255) primary key /* comment 'Canonical true URL' */,
- display varchar(255) not null unique /* comment 'URL for viewing, may be different from canonical' */,
- user_id integer not null /* comment 'user owning this URL' */ references "user" (id) ,
+create sequence oauth_application_seq;
+create table oauth_application (
+ id bigint default nextval('oauth_application_seq') primary key /* comment 'unique identifier' */,
+ owner integer not null /* comment 'owner of the application' */ references profile (id),
+ consumer_key varchar(255) not null /* comment 'application consumer key' */ references consumer (consumer_key),
+ name varchar(255) unique not null /* comment 'name of the application' */,
+ description varchar(255) /* comment 'description of the application' */,
+ icon varchar(255) not null /* comment 'application icon' */,
+ source_url varchar(255) /* comment 'application homepage - used for source link' */,
+ organization varchar(255) /* comment 'name of the organization running the application' */,
+ homepage varchar(255) /* comment 'homepage for the organization' */,
+ callback_url varchar(255) /* comment 'url to redirect to after authentication' */,
+ "type" integer default 0 /* comment 'type of app, 1 = browser, 2 = desktop' */,
+ access_type integer default 0 /* comment 'default access type, bit 1 = read, bit 2 = write' */,
created timestamp not null default CURRENT_TIMESTAMP /* comment 'date this record was created' */,
modified timestamp /* comment 'date this record was modified' */
+);
+create table oauth_application_user (
+ profile_id integer not null /* 'user of the application' */ references profile (id),
+ application_id integer not null /* 'id of the application' */ references oauth_application (id),
+ access_type integer default 0 /* 'access type, bit 1 = read, bit 2 = write' */,
+ token varchar(255) /* 'request or access token' */,
+ created timestamp not null default CURRENT_TIMESTAMP /* 'date this record was created' */,
+ modified timestamp /* 'date this record was modified' */,
+ primary key (profile_id, application_id)
);
-create index user_openid_user_id_idx on user_openid using btree(user_id);
/* These are used by JanRain OpenID library */
@@ -251,7 +276,7 @@ create table confirm_address (
address_extra varchar(255) not null default '' /* comment 'carrier ID, for SMS' */,
address_type varchar(8) not null /* comment 'address type ("email", "jabber", "sms")' */,
claimed timestamp /* comment 'date this was claimed for queueing' */,
- sent timestamp /* comment 'date this was sent for queueing' */,
+ sent timestamp default CURRENT_TIMESTAMP /* comment 'date this was sent for queueing' */,
modified timestamp /* comment 'date this record was modified' */
);
@@ -262,14 +287,12 @@ create table remember_me (
);
create table queue_item (
-
- notice_id integer not null /* comment 'notice queued' */ references notice (id) ,
- transport varchar(8) not null /* comment 'queue for what? "email", "jabber", "sms", "irc", ...' */,
- created timestamp not null default CURRENT_TIMESTAMP /* comment 'date this record was created' */,
- claimed timestamp /* comment 'date this item was claimed' */,
-
- primary key (notice_id, transport)
-
+ id serial /* comment 'unique identifier'*/,
+ frame bytea not null /* comment 'data: object reference or opaque string'*/,
+ transport varchar(8) not null /*comment 'queue for what? "email", "jabber", "sms", "irc", ...'*/,
+ created timestamp not null default CURRENT_TIMESTAMP /*comment 'date this record was created'*/,
+ claimed timestamp /*comment 'date this item was claimed'*/,
+ PRIMARY KEY (id)
);
create index queue_item_created_idx on queue_item using btree(created);
@@ -589,3 +612,39 @@ create table login_token (
primary key (user_id)
);
+create table user_location_prefs (
+ user_id integer not null /* comment 'user who has the preference' */ references "user" (id),
+ share_location integer default 1 /* comment 'Whether to share location data' */,
+ created timestamp not null DEFAULT CURRENT_TIMESTAMP /* comment 'date this record was created' */,
+ modified timestamp /* comment 'date this record was modified' */,
+
+ primary key (user_id)
+);
+
+create table inbox (
+
+ user_id integer not null /* comment 'user receiving the notice' */ references "user" (id),
+ notice_ids bytea /* comment 'packed list of notice ids' */,
+
+ primary key (user_id)
+
+);
+
+create sequence conversation_seq;
+create table conversation (
+ id bigint default nextval('conversation_seq') primary key /* comment 'unique identifier' */,
+ uri varchar(225) unique /* comment 'URI of the conversation' */,
+ created timestamp not null DEFAULT CURRENT_TIMESTAMP /* comment 'date this record was created' */,
+ modified timestamp /* comment 'date this record was modified' */
+);
+
+create table local_group (
+
+ group_id integer primary key /* comment 'group represented' */ references user_group (id),
+ nickname varchar(64) unique /* comment 'group represented' */,
+
+ created timestamp not null DEFAULT CURRENT_TIMESTAMP /* comment 'date this record was created' */,
+ modified timestamp /* comment 'date this record was modified' */
+
+);
+