incomingemail varchar(255) unique key comment 'email address for post-by-email',
emailnotifysub tinyint default 1 comment 'Notify by email of subscriptions',
emailnotifyfav tinyint default 1 comment 'Notify by email of favorites',
+ emailnotifynudge tinyint default 1 comment 'Notify by email of nudges',
+ emailnotifymsg tinyint default 1 comment 'Notify by email of direct messages',
emailmicroid tinyint default 1 comment 'whether to publish email microid',
language varchar(50) comment 'preferred language',
timezone varchar(50) comment 'timezone',
smsemail varchar(255) comment 'built from sms and carrier',
uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI',
autosubscribe tinyint 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 tinyint default 0 comment 'has an inbox been created for this user?',
created datetime not null comment 'date this record was created',
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_subscribed_idx (subscribed),
+ index subscription_token_idx (token)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
create table notice (
foreign_id int comment 'link ' references foreign_user(id),
service int not null comment 'foreign key to service' references foreign_service(id),
credentials varchar(255) comment 'authc credentials, typically a password',
- noticesync tinyint not null default 1 comment 'notice synchronization, bit 1 = sync outgoing, bit 2 = sync incoming',
+ noticesync tinyint not null default 1 comment 'notice synchronization, bit 1 = sync outgoing, bit 2 = sync incoming, bit 3 = filter local replies',
friendsync tinyint not null default 2 comment 'friend synchronization, bit 1 = sync outgoing, bit 2 = sync incoming',
profilesync tinyint not null default 1 comment 'profile synchronization, bit 1 = sync outgoing, bit 2 = sync incoming',
created datetime not null comment 'date this record was created',
index message_to_idx (to_profile),
index message_created_idx (created)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table notice_inbox (
+
+ user_id integer not null comment 'user receiving the message' references user (id),
+ notice_id integer not null comment 'notice received' references notice (id),
+ created datetime not null comment 'date the notice was created',
+ source tinyint default 1 comment 'reason it is in the inbox; 1=subscription',
+
+ constraint primary key (user_id, notice_id),
+ index notice_inbox_notice_id_idx (notice_id)
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table profile_tag (
+ tagger integer not null comment 'user making the tag' references user (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',
+
+ constraint primary key (tagger, tagged, tag),
+ index profile_tag_modified_idx (modified),
+ index profile_tag_tagger_tag_idx (tagger, tag)
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+