diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/08to09.sql | 45 | ||||
| -rw-r--r-- | db/08to09_pg.sql | 39 | ||||
| -rw-r--r-- | db/notice_source.sql | 7 | ||||
| -rw-r--r-- | db/statusnet_pg.sql | 91 |
4 files changed, 164 insertions, 18 deletions
diff --git a/db/08to09.sql b/db/08to09.sql index c6c5d7af6..ba6f38200 100644 --- a/db/08to09.sql +++ b/db/08to09.sql @@ -111,7 +111,11 @@ alter table queue_item rename to queue_item_old; alter table queue_item_new rename to queue_item; alter table consumer - add column consumer_secret varchar(255) not null comment 'secret value'; + add consumer_secret varchar(255) not null comment 'secret value'; + +alter table token + add verifier varchar(255) comment 'verifier string for OAuth 1.0a', + add verified_callback varchar(255) comment 'verified callback URL for OAuth 1.0a'; create table oauth_application ( id integer auto_increment primary key comment 'unique identifier', @@ -140,8 +144,47 @@ create table oauth_application_user ( constraint primary key (profile_id, application_id) ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; +create table inbox ( + + user_id integer not null comment 'user receiving the notice' references user (id), + notice_ids blob comment 'packed list of notice ids', + + constraint primary key (user_id) + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table conversation ( + id integer auto_increment primary key comment 'unique identifier', + uri varchar(225) unique comment 'URI of the conversation', + created datetime not null comment 'date this record was created', + modified timestamp comment 'date this record was modified' +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +-- stub entry to push the autoincrement past existing notice ids +insert into conversation (id,created) + select max(id)+1, now() from notice; + +alter table user_group + add uri varchar(255) unique key comment 'universal identifier', + add mainpage varchar(255) comment 'page for group info to link to', + drop index nickname; + +create table local_group ( + + group_id integer primary key comment 'group represented' references user_group (id), + nickname varchar(64) unique key comment 'group represented', + + created datetime not null comment 'date this record was created', + modified timestamp comment 'date this record was modified' + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +insert into local_group (group_id, nickname, created) + select id, nickname, created from user_group; + alter table file_to_post add index post_id_idx (post_id); alter table group_inbox add index group_inbox_notice_id_idx (notice_id); + 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/notice_source.sql b/db/notice_source.sql index 50660e948..6bfd5c5f1 100644 --- a/db/notice_source.sql +++ b/db/notice_source.sql @@ -9,13 +9,16 @@ VALUES ('bti','bti','http://gregkh.github.com/bti/', now()), ('choqok', 'Choqok', 'http://choqok.gnufolks.org/', now()), ('cliqset', 'Cliqset', 'http://www.cliqset.com/', now()), + ('DarterosStatus', 'Darteros Status', 'http://www.darteros.com/doc/Darteros_Status', now()), ('deskbar','Deskbar-Applet','http://www.gnome.org/projects/deskbar-applet/', now()), ('Do','Gnome Do','http://do.davebsd.com/wiki/index.php?title=Microblog_Plugin', now()), ('drupal','Drupal','http://drupal.org/', now()), - ('eventbox','EventBox','http://thecosmicmachine.com/eventbox/ ', now()), + ('eventbox','EventBox','http://thecosmicmachine.com/eventbox/', now()), + ('identica-mode','Emacs Identica-mode','http://nongnu.org/identica-mode/', now()), ('Facebook','Facebook','http://apps.facebook.com/identica/', now()), ('feed2omb','feed2omb','http://projects.ciarang.com/p/feed2omb/', now()), ('get2gnow', 'get2gnow', 'http://uberchicgeekchick.com/?projects=get2gnow', now()), + ('gNewBook', 'gNewBook', 'http://www.gnewbook.org/', now()), ('gravity', 'Gravity', 'http://mobileways.de/gravity', now()), ('Gwibber','Gwibber','http://launchpad.net/gwibber', now()), ('HelloTxt','HelloTxt','http://hellotxt.com/', now()), @@ -48,11 +51,13 @@ VALUES ('smob','SMOB','http://smob.sioc-project.org/', now()), ('socialoomphBfD4pMqz31', 'SocialOomph', 'http://www.socialoomph.com/', now()), ('spaz','Spaz','http://funkatron.com/spaz', now()), + ('StatusNet Desktop', 'StatusNet Desktop', 'http://status.net/desktop', now()), ('tarpipe','tarpipe','http://tarpipe.com/', now()), ('tjunar','Tjunar','http://nederflash.nl/boek/titels/tjunar-air', now()), ('tr.im','tr.im','http://tr.im/', now()), ('triklepost', 'Tricklepost', 'http://github.com/zcopley/tricklepost/tree/master', now()), ('tweenky','Tweenky','http://beta.tweenky.com/', now()), + ('TweetDeck', 'TweetDeck', 'http://www.tweetdeck.com/', now()), ('twhirl','Twhirl','http://www.twhirl.org/', now()), ('twibble','twibble','http://www.twibble.de/', now()), ('Twidge','Twidge','http://software.complete.org/twidge', now()), 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' */ + +); + |