/* 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',
homepage varchar(255) comment 'identifying URL',
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',
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;
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',
+ repeat_of integer comment 'notice this is a repeat of' references notice (id),
- 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),
+ index notice_repeatof_idx (repeat_of),
FULLTEXT(content)
) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
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;
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 datetime not null comment 'date this record was created',
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 oauth_application (
+ id integer auto_increment 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) 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) not null comment 'url to redirect to after authentication',
+ type tinyint default 0 comment 'type of app, 0 = browser, 1 = desktop',
+ access_type tinyint default 0 comment 'default access type, bit 1 = read, bit 2 = write',
+ 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;
-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),
+create table oauth_application_user (
+ profile_id integer not null comment 'user of the application' references profile (id),
+ application_id integer not null comment 'id of the application' references oauth_application (id),
+ access_type tinyint default 0 comment 'access type, bit 1 = read, bit 2 = write, bit 3 = revoked',
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)
+ constraint primary key (profile_id, application_id)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
/* These are used by JanRain OpenID library */
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
-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 datetime not null comment 'date the role was granted',
- constraint primary key (user_id, role)
+ 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;
+
+create table user_location_prefs (
+ user_id integer not null comment 'user who has the preference' references user (id),
+ share_location tinyint default 1 comment 'Whether to share location data',
+ 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;
+
+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;
projects / quix0rs-gnu-social.git / blobdiff