diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/08to09.sql | 84 | ||||
| -rw-r--r-- | db/08to09_pg.sql | 62 | ||||
| -rw-r--r-- | db/location_namespace.sql | 5 | ||||
| -rw-r--r-- | db/statusnet.sql | 92 | ||||
| -rw-r--r-- | db/statusnet_pg.sql | 50 |
5 files changed, 268 insertions, 25 deletions
diff --git a/db/08to09.sql b/db/08to09.sql new file mode 100644 index 000000000..64640f4ce --- /dev/null +++ b/db/08to09.sql @@ -0,0 +1,84 @@ +alter table notice + modify column content text comment 'update content', + add column lat decimal(10,7) comment 'latitude', + add column lon decimal(10,7) comment 'longitude', + add column location_id integer comment 'location id if possible', + add column location_ns integer comment 'namespace for location', + drop index notice_profile_id_idx, + add index notice_profile_id_idx (profile_id,created,id); + +alter table message + modify column content text comment 'message content'; + +alter table profile + modify column bio text comment 'descriptive biography', + add column lat decimal(10,7) comment 'latitude', + add column lon decimal(10,7) comment 'longitude', + add column location_id integer comment 'location id if possible', + add column location_ns integer comment 'namespace for location'; + +alter table user_group + modify column description text comment 'group description'; + +alter table file_oembed + add column mimetype varchar(50) comment 'mime type of resource'; + +alter table fave + drop index fave_user_id_idx, + add index fave_user_id_idx (user_id,modified); + +alter table subscription + drop index subscription_subscriber_idx, + add index subscription_subscriber_idx (subscriber,created), + drop index subscription_subscribed_idx, + add index subscription_subscribed_idx (subscribed,created); + +create table deleted_notice ( + + id integer primary key comment 'identity of notice', + profile_id integer not null comment 'author of the notice', + uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI', + created datetime not null comment 'date the notice record was created', + deleted datetime not null comment 'date the notice record was created', + + index deleted_notice_profile_id_idx (profile_id) + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table config ( + + section varchar(32) comment 'configuration section', + setting varchar(32) comment 'configuration setting', + value varchar(255) comment 'configuration value', + + constraint primary key (section, setting) + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table profile_role ( + + profile_id integer not null comment 'account having the role' references profile (id), + role varchar(32) not null comment 'string representing the role', + created datetime not null comment 'date the role was granted', + + constraint primary key (profile_id, role) + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table location_namespace ( + + id integer primary key comment 'identity for this namespace', + description varchar(255) comment 'description of the namespace', + created datetime not null comment 'date the record was created', + modified timestamp comment 'date this record was modified' + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table login_token ( + user_id integer not null comment 'user owning this token' references user (id), + token char(32) not null comment 'token useable for logging in', + created datetime not null comment 'date this record was created', + modified timestamp comment 'date this record was modified', + + constraint primary key (user_id) +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; diff --git a/db/08to09_pg.sql b/db/08to09_pg.sql new file mode 100644 index 000000000..d9b57fb84 --- /dev/null +++ b/db/08to09_pg.sql @@ -0,0 +1,62 @@ +-- SQL commands to update an 0.8.x version of Laconica +-- to 0.9.x. + +--these are just comments +/* +alter table notice + modify column content text comment 'update content'; + +alter table message + modify column content text comment 'message content'; + +alter table profile + modify column bio text comment 'descriptive biography'; + +alter table user_group + modify column description text comment 'group description'; +*/ + +alter table file_oembed + add column mimetype varchar(50) /*comment 'mime type of resource'*/; + +create table config ( + + section varchar(32) /* comment 'configuration section'*/, + setting varchar(32) /* comment 'configuration setting'*/, + value varchar(255) /* comment 'configuration value'*/, + + primary key (section, setting) + +); + +create table user_role ( + + user_id integer not null /* comment 'user having the role'*/ references "user" (id), + role varchar(32) not null /* comment 'string representing the role'*/, + created timestamp /* not null comment 'date the role was granted'*/, + + primary key (user_id, role) + +); + +create table login_token ( + user_id integer not null /* comment 'user owning this token'*/ references "user" (id), + token char(32) not null /* comment 'token useable for logging in'*/, + 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) +); + +DROP index fave_user_id_idx; +CREATE index fave_user_id_idx on fave (user_id,modified); + +DROP index subscription_subscriber_idx; +CREATE index subscription_subscriber_idx ON subscription (subscriber,created); + +DROP index subscription_subscribed_idx; +CREATE index subscription_subscribed_idx ON subscription (subscribed,created); + +DROP index notice_profile_id_idx; +CREATE index notice_profile_id_idx ON notice (profile_id,created,id); + diff --git a/db/location_namespace.sql b/db/location_namespace.sql new file mode 100644 index 000000000..59b2ce67c --- /dev/null +++ b/db/location_namespace.sql @@ -0,0 +1,5 @@ +insert into location_namespace + (id, description, created) +values + (1, 'Geonames', now()), + (2, 'Where on Earth', now()); diff --git a/db/statusnet.sql b/db/statusnet.sql index 2c04f680a..18abcdfdb 100644 --- a/db/statusnet.sql +++ b/db/statusnet.sql @@ -1,13 +1,19 @@ /* 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', + bio text 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 datetime not null comment 'date this record was created', modified timestamp comment 'date this record was modified', @@ -101,8 +107,8 @@ create table subscription ( modified timestamp comment 'date this record was modified', constraint primary key (subscriber, subscribed), - index subscription_subscriber_idx (subscriber), - index subscription_subscribed_idx (subscribed), + index subscription_subscriber_idx (subscriber, created), + index subscription_subscribed_idx (subscribed, created), index subscription_token_idx (token) ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; @@ -110,7 +116,7 @@ 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), uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI', - content varchar(140) comment 'update content', + content text comment 'update content', rendered text comment 'HTML version of the content', url varchar(255) comment 'URL of any attachment (image, video, bookmark, whatever)', created datetime not null comment 'date this record was created', @@ -119,8 +125,12 @@ create table notice ( is_local tinyint default 0 comment 'notice was generated by a user', source varchar(32) comment 'source of comment, like "web", "im", or "clientname"', conversation integer comment 'id of root notice in this conversation' references notice (id), + 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', - index notice_profile_id_idx (profile_id), + index notice_profile_id_idx (profile_id,created,id), index notice_conversation_idx (conversation), index notice_created_idx (created), index notice_replyto_idx (reply_to), @@ -155,7 +165,7 @@ create table fave ( constraint primary key (notice_id, user_id), index fave_notice_id_idx (notice_id), - index fave_user_id_idx (user_id), + index fave_user_id_idx (user_id,modified), index fave_modified_idx (modified) ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; @@ -195,18 +205,6 @@ create table nonce ( constraint primary key (consumer_key, ts, nonce) ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; -/* 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 key comment 'URL for viewing, may be different from canonical', - user_id integer not null comment 'user owning this URL' references user (id), - created datetime not null comment 'date this record was created', - modified timestamp comment 'date this record was modified', - - index user_openid_user_id_idx (user_id) -) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; - /* These are used by JanRain OpenID library */ create table oid_associations ( @@ -331,7 +329,7 @@ create table message ( uri varchar(255) unique key comment 'universally unique identifier', from_profile integer not null comment 'who the message is from' references profile (id), to_profile integer not null comment 'who the message is to' references profile (id), - content varchar(140) comment 'message content', + content text comment 'message content', rendered text comment 'HTML version of the content', url varchar(255) comment 'URL of any attachment (image, video, bookmark, whatever)', created datetime not null comment 'date this record was created', @@ -380,7 +378,7 @@ create table user_group ( nickname varchar(64) unique key comment 'nickname for addressing', fullname varchar(255) comment 'display name', homepage varchar(255) comment 'URL, cached so we dont regenerate', - description varchar(140) comment 'descriptive biography', + description text comment 'group description', location varchar(255) comment 'related physical location, if any', original_logo varchar(255) comment 'original size logo', @@ -450,6 +448,7 @@ create table file_oembed ( file_id integer primary key comment 'oEmbed for that URL/file' references file (id), version varchar(20) comment 'oEmbed spec. version', type varchar(20) comment 'oEmbed type: photo, video, link, rich', + mimetype varchar(50) comment 'mime type of resource', provider varchar(50) comment 'name of this oEmbed provider', provider_url varchar(255) comment 'URL of this oEmbed provider', width integer comment 'width of oEmbed resource when available', @@ -534,4 +533,55 @@ create table session ( index session_modified_idx (modified) -) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
\ No newline at end of file +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table deleted_notice ( + + id integer primary key comment 'identity of notice', + profile_id integer not null comment 'author of the notice', + uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI', + created datetime not null comment 'date the notice record was created', + deleted datetime not null comment 'date the notice record was created', + + index deleted_notice_profile_id_idx (profile_id) + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table config ( + + section varchar(32) comment 'configuration section', + setting varchar(32) comment 'configuration setting', + value varchar(255) comment 'configuration value', + + constraint primary key (section, setting) + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table profile_role ( + + profile_id integer not null comment 'account having the role' references profile (id), + role varchar(32) not null comment 'string representing the role', + created datetime not null comment 'date the role was granted', + + constraint primary key (profile_id, role) + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table location_namespace ( + + id integer primary key comment 'identity for this namespace', + description varchar(255) comment 'description of the namespace', + created datetime not null comment 'date the record was created', + modified timestamp comment 'date this record was modified' + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table login_token ( + user_id integer not null comment 'user owning this token' references user (id), + token char(32) not null comment 'token useable for logging in', + created datetime not null comment 'date this record was created', + modified timestamp comment 'date this record was modified', + + constraint primary key (user_id) +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + diff --git a/db/statusnet_pg.sql b/db/statusnet_pg.sql index ad34720a2..8dbaf8598 100644 --- a/db/statusnet_pg.sql +++ b/db/statusnet_pg.sql @@ -115,8 +115,8 @@ create table subscription ( primary key (subscriber, subscribed) ); -create index subscription_subscriber_idx on subscription using btree(subscriber); -create index subscription_subscribed_idx on subscription using btree(subscribed); +create index subscription_subscriber_idx on subscription using btree(subscriber,created); +create index subscription_subscribed_idx on subscription using btree(subscribed,created); create sequence notice_seq; create table notice ( @@ -137,7 +137,7 @@ create table notice ( /* FULLTEXT(content) */ ); -create index notice_profile_id_idx on notice using btree(profile_id); +create index notice_profile_id_idx on notice using btree(profile_id,created,id); create index notice_created_idx on notice using btree(created); create table notice_source ( @@ -171,7 +171,7 @@ create table fave ( ); create index fave_notice_id_idx on fave using btree(notice_id); -create index fave_user_id_idx on fave using btree(user_id); +create index fave_user_id_idx on fave using btree(user_id,modified); create index fave_modified_idx on fave using btree(modified); /* tables for OAuth */ @@ -465,6 +465,7 @@ create table file_oembed ( file_id bigint default nextval('file_oembed_seq') primary key /* comment 'unique identifier' */, version varchar(20), type varchar(20), + mimetype varchar(50), provider varchar(50), provider_url varchar(255), width integer, @@ -529,6 +530,17 @@ create table session ( create index session_modified_idx on session (modified); +create table deleted_notice ( + + id integer primary key /* comment 'identity of notice'*/ , + profile_id integer /* not null comment 'author of the notice'*/, + uri varchar(255) unique /* comment 'universally unique identifier, usually a tag URI'*/, + created timestamp not null /* comment 'date the notice record was created'*/ , + deleted timestamp not null DEFAULT CURRENT_TIMESTAMP /* comment 'date the notice record was created'*/ +); + +CREATE index deleted_notice_profile_id_idx on deleted_notice (profile_id); + /* Textsearch stuff */ @@ -537,3 +549,33 @@ create index noticecontent_idx on notice using gist(to_tsvector('english',conten create trigger textsearchupdate before insert or update on profile for each row execute procedure tsvector_update_trigger(textsearch, 'pg_catalog.english', nickname, fullname, location, bio, homepage); + +create table config ( + + section varchar(32) /* comment 'configuration section'*/, + setting varchar(32) /* comment 'configuration setting'*/, + value varchar(255) /* comment 'configuration value'*/, + + primary key (section, setting) + +); + +create table user_role ( + + user_id integer not null /* comment 'user having the role'*/ references "user" (id), + role varchar(32) not null /* comment 'string representing the role'*/, + created timestamp /* not null comment 'date the role was granted'*/, + + primary key (user_id, role) + +); + +create table login_token ( + user_id integer not null /* comment 'user owning this token'*/ references "user" (id), + token char(32) not null /* comment 'token useable for logging in'*/, + 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) +); + |