/* local and remote users have profiles */
-
create sequence profile_seq;
create table profile (
id bigint default nextval('profile_seq') primary key /* comment 'unique identifier' */,
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' */,
emailnotifyfav integer default 1 /* comment 'Notify by email of favorites' */,
emailnotifynudge integer default 1 /* comment 'Notify by email of nudges' */,
emailnotifymsg integer default 1 /* comment 'Notify by email of direct messages' */,
- emailnotifyattn integer default 1 /* command 'Notify by email of @-replies' */,
+ emailnotifyattn integer default 1 /* command 'Notify by email of @-replies' */,
emailmicroid integer default 1 /* comment 'whether to publish email microid' */,
language varchar(50) /* comment 'preferred language' */,
timezone varchar(50) /* comment 'timezone' */,
uri varchar(255) unique /* comment 'universally unique identifier, usually a tag URI' */,
autosubscribe integer default 0 /* comment 'automatically subscribe to users who subscribe to us' */,
urlshorteningservice varchar(50) default 'ur1.ca' /* comment 'service to use for auto-shortening URLs' */,
- inboxed integer default 0 /* comment 'has an inbox been created for this user?' */,
+ inboxed integer default 0 /* comment 'has an inbox been created for this user?' */,
design_id integer /* comment 'id of a design' */references design(id),
viewdesigns integer default 1 /* comment 'whether to view user-provided designs'*/,
created timestamp not null default CURRENT_TIMESTAMP /* comment 'date this record was created' */,
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 (
reply_to integer /* comment 'notice replied to (usually a guess)' */ references notice (id) ,
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)
-
+ conversation integer /*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'*/ ,
+ repeat_of integer /* comment 'notice this is a repeat of' */ references notice (id)
/* 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 (
);
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 */
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' */,
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' */,
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 */
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' */
);
);
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);
nickname varchar(255) /* comment 'nickname on foreign service' */,
created timestamp not null default CURRENT_TIMESTAMP /* comment 'date this record was created' */,
modified timestamp /* comment 'date this record was modified' */,
-
+
primary key (id, service)
);
service int not null /* comment 'foreign key to service' */ references foreign_service (id),
credentials varchar(255) /* comment 'authc credentials, typically a password' */,
noticesync int not null default 1 /* comment 'notice synchronisation, bit 1 = sync outgoing, bit 2 = sync incoming, bit 3 = filter local replies' */,
- friendsync int not null default 2 /* comment 'friend synchronisation, bit 1 = sync outgoing, bit 2 = sync incoming */,
+ friendsync int not null default 2 /* comment 'friend synchronisation, bit 1 = sync outgoing, bit 2 = sync incoming */,
profilesync int not null default 1 /* comment 'profile synchronization, bit 1 = sync outgoing, bit 2 = sync incoming' */,
last_noticesync timestamp default null /* comment 'last time notices were imported' */,
last_friendsync timestamp default null /* comment 'last time friends were imported' */,
subscriber int not null /* comment 'subscriber on foreign service' */ ,
subscribed int not null /* comment 'subscribed user' */ ,
created timestamp not null default CURRENT_TIMESTAMP /* comment 'date this record was created' */,
-
+
primary key (service, subscriber, subscribed)
);
create index foreign_subscription_subscriber_idx on foreign_subscription using btree(subscriber);
created timestamp not null default CURRENT_TIMESTAMP /* comment 'date this record was created' */,
modified timestamp /* comment 'date this record was modified' */,
source varchar(32) /* comment 'source of comment, like "web", "im", or "clientname"' */
-
+
);
create index message_from_idx on message using btree(from_profile);
create index message_to_idx on message using btree(to_profile);
mini_logo varchar(255) /* comment 'mini logo' */,
design_id integer /*comment 'id of a design' */ references design(id),
-
created timestamp not null default CURRENT_TIMESTAMP /* comment 'date this record was created' */,
modified timestamp /* comment 'date this record was modified' */
);
create index group_inbox_created_idx on group_inbox using btree(created);
-
/*attachments and URLs stuff */
create sequence file_seq;
create table file (
id bigint default nextval('file_seq') primary key /* comment 'unique identifier' */,
- url varchar(255) unique,
- mimetype varchar(50),
- size integer,
- title varchar(255),
- date integer,
+ url varchar(255) unique,
+ mimetype varchar(50),
+ size integer,
+ title varchar(255),
+ date integer,
protected integer,
filename text /* comment 'if a local file, name of the file' */,
modified timestamp default CURRENT_TIMESTAMP /* comment 'date this record was modified'*/
file_id bigint default nextval('file_oembed_seq') primary key /* comment 'unique identifier' */,
version varchar(20),
type varchar(20),
- mimetype varchar(50),
+ mimetype varchar(50),
provider varchar(50),
provider_url varchar(255),
width integer,
height integer,
html text,
title varchar(255),
- author_name varchar(50),
- author_url varchar(255),
- url varchar(255)
+ author_name varchar(50),
+ author_url varchar(255),
+ url varchar(255)
);
create sequence file_redirection_seq;
create table file_redirection (
- url varchar(255) primary key,
- file_id bigint,
- redirections integer,
+ url varchar(255) primary key,
+ file_id bigint,
+ redirections integer,
httpcode integer
);
create sequence file_thumbnail_seq;
create table file_thumbnail (
- file_id bigint primary key,
- url varchar(255) unique,
- width integer,
- height integer
+ file_id bigint primary key,
+ url varchar(255) unique,
+ width integer,
+ height integer
);
create sequence file_to_post_seq;
create table file_to_post (
- file_id bigint,
- post_id bigint,
+ file_id bigint,
+ post_id bigint,
primary key (file_id, post_id)
);
id varchar(32) primary key /* comment 'session ID'*/,
session_data text /* comment 'session data'*/,
created timestamp not null DEFAULT CURRENT_TIMESTAMP /* comment 'date this record was created'*/,
- modified integer DEFAULT extract(epoch from CURRENT_TIMESTAMP) /* comment 'date this record was modified'*/
+ modified integer DEFAULT extract(epoch from CURRENT_TIMESTAMP) /* comment 'date this record was modified'*/
);
create index session_modified_idx on session (modified);
CREATE index deleted_notice_profile_id_idx on deleted_notice (profile_id);
-
/* Textsearch stuff */
create index textsearch_idx on profile using gist(textsearch);
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'*/,
);
-create table user_role (
+create table profile_role (
- user_id integer not null /* comment 'user having the role'*/ references "user" (id),
+ profile_id integer not null /* comment 'account having the role'*/ references profile (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)
+ primary key (profile_id, role)
+
+);
+
+create table location_namespace (
+
+ id integer /*comment 'identity for this namespace'*/,
+ description text /* comment 'description of the namespace'*/ ,
+ created integer not null /*comment 'date the record was created*/ ,
+ /* modified timestamp comment 'date this record was modified',*/
+ primary key (id)
);
create table login_token (
- user_id integer not null /* comment 'user owning this token'*/ references user (id),
+ 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'*/,
- constraint primary key (user_id)
+ 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' */
+
);
projects / quix0rs-gnu-social.git / blobdiff