From a968cc69994588e475218975a6fcceb90b9826f8 Mon Sep 17 00:00:00 2001 From: Patrick Georgi Date: Mon, 17 May 2010 13:47:27 +1200 Subject: updated database for postgres --- db/statusnet_pg.sql | 71 +++++++++++++++++++++++++++++++++++++++++++++++------ 1 file changed, 64 insertions(+), 7 deletions(-) diff --git a/db/statusnet_pg.sql b/db/statusnet_pg.sql index 998cc71e9..9f97566a9 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'*/ , @@ -213,17 +218,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 */ @@ -589,3 +610,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' */ + +); + -- cgit v1.2.3