X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=db%2Flaconica.sql;h=da2a1c89dfff14245a7c066843c5197480ef5ea8;hb=6593092bfd08efa9fe5dbd83a85a4fe1cac2b936;hp=b6de405f9073fc83c2acf0320db6436d44b9da01;hpb=e1361cdf489fc87aecb84261c5e2b7b47ce9c829;p=quix0rs-gnu-social.git diff --git a/db/laconica.sql b/db/laconica.sql index b6de405f90..da2a1c89df 100644 --- a/db/laconica.sql +++ b/db/laconica.sql @@ -13,7 +13,7 @@ create table profile ( index profile_nickname_idx (nickname), FULLTEXT(nickname, fullname, location, bio, homepage) -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; create table avatar ( profile_id integer not null comment 'foreign key to profile table' references profile (id), @@ -28,7 +28,7 @@ create table avatar ( constraint primary key (profile_id, width, height), index avatar_profile_id_idx (profile_id) -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; create table sms_carrier ( id integer auto_increment primary key comment 'primary key for SMS carrier', @@ -36,7 +36,7 @@ create table sms_carrier ( email_pattern varchar(255) not null comment 'sprintf pattern for making an email address from a phone number', created datetime not null comment 'date this record was created', modified timestamp comment 'date this record was modified' -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; /* local users */ @@ -47,10 +47,14 @@ create table user ( email varchar(255) unique key comment 'email address for password recovery etc.', incomingemail varchar(255) unique key comment 'email address for post-by-email', emailnotifysub tinyint default 1 comment 'Notify by email of subscriptions', + emailmicroid tinyint default 1 comment 'whether to publish email microid', + 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), @@ -61,9 +65,9 @@ create table user ( autosubscribe tinyint default 0 comment 'automatically subscribe to users who subscribe to us', created datetime not null comment 'date this record was created', modified timestamp comment 'date this record was modified', - + index user_smsemail_idx (smsemail) -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; /* remote people */ @@ -74,7 +78,7 @@ create table remote_profile ( updateprofileurl varchar(255) comment 'URL we use for updates to this profile', created datetime not null comment 'date this record was created', modified timestamp comment 'date this record was modified' -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; create table subscription ( subscriber integer not null comment 'profile listening', @@ -87,7 +91,7 @@ create table subscription ( constraint primary key (subscriber, subscribed), index subscription_subscriber_idx (subscriber), index subscription_subscribed_idx (subscribed) -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; create table notice ( @@ -101,12 +105,20 @@ create table notice ( modified timestamp comment 'date this record was modified', reply_to integer comment 'notice replied to (usually a guess)' references notice (id), is_local tinyint default 0 comment 'notice was generated by a user', - + source varchar(32) comment 'source of comment, like "web", "im", or "clientname"', + index notice_profile_id_idx (profile_id), - index notice_created_idx (profile_id), - + index notice_created_idx (created), FULLTEXT(content) -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; + +create table notice_source ( + code varchar(32) primary key not null comment 'source code', + name varchar(255) not null comment 'name of the source', + url varchar(255) not null comment 'url to link to', + created datetime not null comment 'date this record was created', + modified timestamp comment 'date this record was modified' +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; create table reply ( @@ -114,13 +126,26 @@ create table reply ( profile_id integer not null comment 'profile replied to' references profile (id), modified timestamp not null comment 'date this record was modified', replied_id integer comment 'notice replied to (not used, see notice.reply_to)', - + constraint primary key (notice_id, profile_id), index reply_notice_id_idx (notice_id), index reply_profile_id_idx (profile_id), index reply_replied_id_idx (replied_id) -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; + +create table fave ( + + notice_id integer not null comment 'notice that is the favorite' references notice (id), + user_id integer not null comment 'user who likes this notice' references user (id), + modified timestamp not null comment 'date this record was modified', + + constraint primary key (notice_id, user_id), + index fave_notice_id_idx (notice_id), + index fave_user_id_idx (user_id), + index fave_modified_idx (modified) + +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; /* tables for OAuth */ @@ -130,7 +155,7 @@ create table consumer ( created datetime not null comment 'date this record was created', modified timestamp comment 'date this record was modified' -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; create table token ( consumer_key varchar(255) not null comment 'unique identifier, root URL' references consumer (consumer_key), @@ -143,7 +168,7 @@ create table token ( modified timestamp comment 'date this record was modified', constraint primary key (consumer_key, tok) -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; create table nonce ( consumer_key varchar(255) not null comment 'unique identifier, root URL', @@ -156,7 +181,7 @@ create table nonce ( constraint primary key (consumer_key, tok, nonce), constraint foreign key (consumer_key, tok) references token (consumer_key, tok) -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; /* One-to-many relationship of user to openid_url */ @@ -168,26 +193,26 @@ create table user_openid ( modified timestamp comment 'date this record was modified', index user_openid_user_id_idx (user_id) -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; /* These are used by JanRain OpenID library */ create table oid_associations ( server_url BLOB, - handle VARCHAR(255), + handle VARCHAR(255) character set latin1, secret BLOB, issued INTEGER, lifetime INTEGER, assoc_type VARCHAR(64), PRIMARY KEY (server_url(255), handle) -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; create table oid_nonces ( server_url VARCHAR(2047), timestamp INTEGER, salt CHAR(40), UNIQUE (server_url(255), timestamp, salt) -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; create table confirm_address ( code varchar(32) not null primary key comment 'good random code', @@ -198,13 +223,13 @@ create table confirm_address ( 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' -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; create table remember_me ( code varchar(32) not null primary key comment 'good random code', user_id integer not null comment 'user who is logged in' references user (id), modified timestamp comment 'date this record was modified' -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; create table queue_item ( @@ -215,5 +240,49 @@ create table queue_item ( index queue_item_created_idx (created) -) ENGINE=MyISAM; +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; + +/* Hash tags */ +create table notice_tag ( + tag varchar( 64 ) not null comment 'hash tag associated with this notice', + notice_id integer not null comment 'notice tagged' references notice (id), + created datetime not null comment 'date this record was created', + constraint primary key (tag, notice_id), + index notice_tag_created_idx (created) +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; + +/* Synching with foreign services */ + +create table foreign_service ( + id int not null primary key comment 'numeric key for service', + name varchar(32) not null unique key comment 'name of the service', + description varchar(255) comment 'description', + created datetime not null comment 'date this record was created', + modified timestamp comment 'date this record was modified' +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; + +create table foreign_user ( + id int not null comment 'unique numeric key on foreign service', + service int not null comment 'foreign key to service' references foreign_service(id), + uri varchar(255) not null unique key comment 'identifying URI', + nickname varchar(255) comment 'nickname on foreign service', + user_id int comment 'link to user on this system, if exists' references user (id), + credentials varchar(255) comment 'authc credentials, typically a password', + created datetime not null comment 'date this record was created', + modified timestamp comment 'date this record was modified', + + constraint primary key (id, service), + index foreign_user_user_id_idx (user_id) +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin; + +create table foreign_subscription ( + service int not null comment 'service where relationship happens' references foreign_service(id), + subscriber int not null comment 'subscriber on foreign service' references foreign_user (id), + subscribed int not null comment 'subscribed user' references foreign_user (id), + created datetime not null comment 'date this record was created', + + constraint primary key (service, subscriber, subscribed), + index foreign_subscription_subscriber_idx (subscriber), + index foreign_subscription_subscribed_idx (subscribed) +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin;