language varchar(50) comment 'preferred language',
timezone varchar(50) comment 'timezone',
emailpost tinyint default 1 comment 'Post by email',
- jabber varchar(255) unique key comment 'jabber ID for notices',
- jabbernotify tinyint default 0 comment 'whether to send notices to jabber',
- jabberreplies tinyint default 0 comment 'whether to send notices to jabber on replies',
- jabbermicroid tinyint default 1 comment 'whether to publish xmpp microid',
- updatefrompresence tinyint default 0 comment 'whether to record updates from Jabber presence notices',
sms varchar(64) unique key comment 'sms phone number',
carrier integer comment 'foreign key to sms_carrier' references sms_carrier (id),
smsnotify tinyint default 0 comment 'whether to send notices to SMS',
create table confirm_address (
code varchar(32) not null primary key comment 'good random code',
user_id integer not null comment 'user who requested confirmation' references user (id),
- address varchar(255) not null comment 'address (email, Jabber, SMS, etc.)',
+ address varchar(255) not null comment 'address (email, xmpp, SMS, etc.)',
address_extra varchar(255) not null comment 'carrier ID, for SMS',
- address_type varchar(8) not null comment 'address type ("email", "jabber", "sms")',
+ address_type varchar(8) not null comment 'address type ("email", "xmpp", "sms")',
claimed datetime comment 'date this was claimed for queueing',
sent datetime comment 'date this was sent for queueing',
modified timestamp comment 'date this record was modified'
create table queue_item (
id integer auto_increment primary key comment 'unique identifier',
frame blob not null comment 'data: object reference or opaque string',
- transport varchar(8) not null comment 'queue for what? "email", "jabber", "sms", "irc", ...',
+ transport varchar(8) not null comment 'queue for what? "email", "xmpp", "sms", "irc", ...',
created datetime not null comment 'date this record was created',
claimed datetime comment 'date this item was claimed',
code varchar(32) not null primary key comment 'random code for an invitation',
user_id int not null comment 'who sent the invitation' references user (id),
address varchar(255) not null comment 'invitation sent to',
- address_type varchar(8) not null comment 'address type ("email", "jabber", "sms")',
+ address_type varchar(8) not null comment 'address type ("email", "xmpp", "sms")',
created datetime not null comment 'date this record was created',
index invitation_address_idx (address, address_type),
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
create table profile_tag (
- tagger integer not null comment 'user making the tag' references user (id),
+ tagger integer not null comment 'user making the tag' references profile (id),
tagged integer not null comment 'profile tagged' references profile (id),
tag varchar(64) not null comment 'hash tag associated with this notice',
modified timestamp comment 'date the tag was added',
index profile_tag_tagged_idx (tagged)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+/* people tag metadata */
+create table profile_list (
+ id integer auto_increment unique key comment 'unique identifier',
+ tagger integer not null comment 'user making the tag' references profile (id),
+ tag varchar(64) not null comment 'hash tag',
+ description text comment 'description for the tag',
+ private tinyint(1) default 0 comment 'is this list private',
+
+ created datetime not null comment 'date this record was created',
+ modified timestamp comment 'date this record was modified',
+
+ uri varchar(255) unique key comment 'universal identifier',
+ mainpage varchar(255) comment 'page for tag info info to link to',
+ tagged_count smallint not null default 0 comment 'number of people tagged',
+ subscriber_count smallint not null default 0 comment 'number of people subscribing',
+
+ constraint primary key (tagger, tag),
+ index profile_list_tag_idx (tag),
+ index profile_list_tagged_count_idx (tagged_count),
+ index profile_list_modified_idx (modified),
+ index profile_list_subscriber_count_idx (subscriber_count)
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table profile_tag_inbox (
+ profile_tag_id integer not null comment 'peopletag receiving the message' references profile_tag (id),
+ notice_id integer not null comment 'notice received' references notice (id),
+ created datetime not null comment 'date the notice was created',
+
+ constraint primary key (profile_tag_id, notice_id),
+ index profile_tag_inbox_created_idx (created),
+ index profile_tag_inbox_notice_id_idx (notice_id)
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table profile_tag_subscription (
+ profile_tag_id integer not null comment 'foreign key to profile_tag' references profile_list (id),
+
+ profile_id integer not null comment 'foreign key to profile table' references profile (id),
+ created datetime not null comment 'date this record was created',
+ modified timestamp comment 'date this record was modified',
+
+ constraint primary key (profile_tag_id, profile_id),
+ index profile_tag_subscription_profile_id_idx (profile_id),
+ index profile_tag_subscription_created_idx (created)
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
create table profile_block (
blocker integer not null comment 'user making the block' references user (id),
blocked integer not null comment 'profile that is blocked' references profile (id),
constraint primary key (group_id, profile_id),
index group_member_profile_id_idx (profile_id),
- index group_member_created_idx (created)
+ index group_member_created_idx (created),
+
+ -- To pull up a list of someone's groups in order joined
+ index group_member_profile_id_created_idx (profile_id, created)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+create table user_im_prefs (
+ user_id integer not null comment 'user' references user (id),
+ screenname varchar(255) not null comment 'screenname on this service',
+ transport varchar(255) not null comment 'transport (ex xmpp, aim)',
+ notify tinyint(1) not null default 0 comment 'Notify when a new notice is sent',
+ replies tinyint(1) not null default 0 comment 'Send replies from people not subscribed to',
+ microid tinyint(1) not null default 1 comment 'Publish a MicroID',
+ updatefrompresence tinyint(1) not null default 0 comment 'Send replies from people not subscribed to.',
+ 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, transport),
+ constraint unique key `transport_screenname_key` ( `transport` , `screenname` )
+) 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',
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+create table user_urlshortener_prefs (
+
+ user_id integer not null comment 'user' references user (id),
+ urlshorteningservice varchar(50) default 'ur1.ca' comment 'service to use for auto-shortening URLs',
+ maxurllength integer not null comment 'urls greater than this length will be shortened, 0 = always, null = never',
+ maxnoticelength integer not null comment 'notices with content greater than this value will have all urls shortened, 0 = always, null = never',
+
+ 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;
projects / quix0rs-gnu-social.git / blobdiff