— — Table structure for table tb_user — CREATE TABLE tb_user ( id bigint unsigned NOT NULL AUTO_INCREMENT, username varchar(15) COLLATE utf8mb4_general_ci NOT NULL, email varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, password varchar(64) COLLATE utf8mb4_general_ci NOT NULL, display_name varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, banner_url varchar(255) COLLATE utf8mb4_general_ci NOT NULL, avatar_url varchar(255) COLLATE utf8mb4_general_ci NOT NULL, phone varchar(32) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, description text COLLATE utf8mb4_general_ci NOT NULL, website varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, language varchar(8) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, time_zone varchar(8) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, location varchar(16) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, verified tinyint unsigned NOT NULL DEFAULT ‘0’, suspended tinyint unsigned NOT NULL DEFAULT ‘0’, dob_day tinyint unsigned NOT NULL DEFAULT ‘0’, is_visible_dob_day tinyint unsigned NOT NULL DEFAULT ‘0’, dob_month tinyint unsigned NOT NULL DEFAULT ‘0’, is_visible_dob_month tinyint unsigned NOT NULL DEFAULT ‘0’, dob_year smallint unsigned NOT NULL DEFAULT ‘0’, is_visible_dob_year tinyint unsigned NOT NULL DEFAULT ‘0’, activate_code varchar(6) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, is_activated tinyint unsigned NOT NULL DEFAULT ‘0’, created_at bigint NOT NULL DEFAULT ‘0’, updated_at bigint NOT NULL DEFAULT ‘0’, PRIMARY KEY (id), UNIQUE KEY uiq_username (username), UNIQUE KEY uiq_email (email) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
— — Table structure for table tb_user_extended — CREATE TABLE tb_user_extended ( user_id bigint unsigned NOT NULL, count_followers int unsigned NOT NULL DEFAULT ‘0’, count_followings int unsigned NOT NULL DEFAULT ‘0’, count_posts int unsigned NOT NULL DEFAULT ‘0’, count_likes int unsigned NOT NULL DEFAULT ‘0’, pinned_post_id bigint unsigned NOT NULL DEFAULT ‘0’, last_login bigint NOT NULL DEFAULT ‘0’, timeline_updated_at bigint NOT NULL DEFAULT ‘0’, created_at bigint NOT NULL DEFAULT ‘0’, updated_at bigint NOT NULL DEFAULT ‘0’, PRIMARY KEY (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
— — Table structure for table tb_user_reset — CREATE TABLE tb_user_reset ( id bigint unsigned NOT NULL AUTO_INCREMENT, user_id bigint unsigned NOT NULL, username varchar(16) COLLATE utf8mb4_general_ci NOT NULL, email varchar(255) COLLATE utf8mb4_general_ci NOT NULL, reset_code varchar(64) COLLATE utf8mb4_general_ci NOT NULL, created_at bigint NOT NULL, updated_at bigint NOT NULL DEFAULT ‘0’, PRIMARY KEY (id), KEY idx_email (email), KEY idx_username (username) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
— — Table structure for table tb_reserved_username — CREATE TABLE tb_reserved_username ( id bigint unsigned NOT NULL AUTO_INCREMENT, reserved_username varchar(16) COLLATE utf8mb4_general_ci NOT NULL, notes text COLLATE utf8mb4_general_ci NOT NULL, created_by int unsigned NOT NULL, created_at bigint unsigned NOT NULL, updated_at bigint unsigned NOT NULL, PRIMARY KEY (id), UNIQUE KEY reserved_username (reserved_username) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
— — Table structure for table tb_post — CREATE TABLE tb_post ( id bigint unsigned NOT NULL AUTO_INCREMENT, user_id bigint unsigned NOT NULL, post_type tinyint unsigned NOT NULL COMMENT ‘0: original post; 1: repost; 2: quoted repost; 3: comment (or reply)’, original_type tinyint DEFAULT ‘0’, original_id bigint unsigned NOT NULL, content_text varchar(300) COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘Text content; Max length 280’, image_url varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, video_url varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, hashtags text COLLATE utf8mb4_general_ci NOT NULL, location varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, place varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, source tinyint unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘The source label of this post, e.g., iPhone, Android, Web, Default 0’, reply_settings tinyint unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘Shows you who can reply to a given Tweet. Fields returned are “everyone”, “mentioned_users”, and “followers”. default 0 “reply_settings”: “everyone” ‘, is_deleted tinyint unsigned NOT NULL DEFAULT ‘0’, created_at bigint NOT NULL DEFAULT ‘0’, updated_at bigint NOT NULL DEFAULT ‘0’, deleted_at bigint NOT NULL DEFAULT ‘0’, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
— — Table structure for table tb_post_extended — CREATE TABLE tb_post_extended ( post_id bigint unsigned NOT NULL, user_id bigint unsigned NOT NULL, count_comments int unsigned NOT NULL DEFAULT ‘0’, count_likes int unsigned NOT NULL DEFAULT ‘0’, count_reposts bigint NOT NULL DEFAULT ‘0’, count_quote int unsigned NOT NULL DEFAULT ‘0’, count_shares int unsigned NOT NULL DEFAULT ‘0’, count_reported bigint NOT NULL DEFAULT ‘0’, is_deleted tinyint unsigned NOT NULL DEFAULT ‘0’, created_at bigint NOT NULL DEFAULT ‘0’, updated_at bigint NOT NULL DEFAULT ‘0’, deleted_at bigint NOT NULL DEFAULT ‘0’ , PRIMARY KEY (post_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
— — Table structure for table tb_post_like — CREATE TABLE tb_post_like ( id bigint unsigned NOT NULL AUTO_INCREMENT, content_type tinyint DEFAULT ‘0’ COMMENT ‘0 post, 1 reply’, record_id bigint unsigned NOT NULL, liker_id bigint unsigned NOT NULL, is_like tinyint unsigned NOT NULL DEFAULT ‘0’, created_at bigint unsigned NOT NULL DEFAULT ‘0’, updated_at bigint unsigned NOT NULL DEFAULT ‘0’, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
— — Table structure for table tb_post_reply — CREATE TABLE tb_post_reply ( id bigint unsigned NOT NULL AUTO_INCREMENT, user_id bigint unsigned NOT NULL, post_id bigint unsigned NOT NULL, content_text varchar(300) COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘Text content; Max length 280’, image_url varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, video_url varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, hashtags text COLLATE utf8mb4_general_ci NOT NULL, location varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, place varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ”, source tinyint unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘The source label of this post, e.g., iPhone, Android, Web, Default 0’, is_deleted tinyint unsigned NOT NULL DEFAULT ‘0’, created_at bigint NOT NULL DEFAULT ‘0’, deleted_at bigint NOT NULL DEFAULT ‘0’, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
— — Table structure for table tb_post_reply_extended — CREATE TABLE tb_post_reply_extended ( post_reply_id bigint unsigned NOT NULL, user_id bigint unsigned NOT NULL, post_id bigint unsigned NOT NULL, count_comments int unsigned NOT NULL DEFAULT ‘0’, count_likes int unsigned NOT NULL DEFAULT ‘0’, count_reposts bigint NOT NULL DEFAULT ‘0’, count_quote int unsigned NOT NULL DEFAULT ‘0’, count_shares int unsigned NOT NULL DEFAULT ‘0’, count_reported bigint NOT NULL DEFAULT ‘0’, is_deleted tinyint unsigned NOT NULL DEFAULT ‘0’, created_at bigint NOT NULL DEFAULT ‘0’, updated_at bigint NOT NULL DEFAULT ‘0’, deleted_at bigint NOT NULL DEFAULT ‘0’, PRIMARY KEY (post_reply_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
— — Table structure for table tb_post_reply_hashtag — CREATE TABLE tb_post_reply_hashtag ( id bigint unsigned NOT NULL AUTO_INCREMENT, content_type tinyint DEFAULT NULL COMMENT ‘0 post, 1 reply’, record_id bigint unsigned NOT NULL, hashtag_id bigint unsigned NOT NULL, created_at bigint NOT NULL DEFAULT ‘0’, updated_at bigint NOT NULL DEFAULT ‘0’, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
— — Table structure for table tb_recommended_hashtag — CREATE TABLE tb_recommended_hashtag ( id int NOT NULL AUTO_INCREMENT, hashtag_id int NOT NULL, rank int NOT NULL DEFAULT ‘0’, created_at bigint unsigned DEFAULT ‘0’, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
— — Table structure for table tb_recommended_user — CREATE TABLE tb_recommended_user ( id int NOT NULL AUTO_INCREMENT, user_id bigint unsigned DEFAULT NULL, rank int DEFAULT ‘0’, created_at bigint unsigned DEFAULT ‘0’, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
— — Table structure for table tb_reported_content — CREATE TABLE tb_reported_content ( id bigint unsigned NOT NULL AUTO_INCREMENT, content_type tinyint DEFAULT ‘0’ COMMENT ‘0 post, 1 reply’, record_id bigint unsigned NOT NULL, post_user_id bigint unsigned NOT NULL, reporter_id bigint unsigned NOT NULL, report_reason tinyint unsigned NOT NULL, created_at bigint NOT NULL DEFAULT ‘0’, updated_at bigint NOT NULL DEFAULT ‘0’, PRIMARY KEY (id), KEY idx_post_id (record_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
— — Table structure for table tb_reported_user — CREATE TABLE tb_reported_user ( id bigint unsigned NOT NULL AUTO_INCREMENT, user_id bigint unsigned NOT NULL, reporter_id bigint unsigned NOT NULL, report_reason tinyint unsigned NOT NULL, created_at bigint unsigned NOT NULL DEFAULT ‘0’, updated_at bigint unsigned NOT NULL DEFAULT ‘0’, PRIMARY KEY (id), KEY idx_user_id_reporter_id (user_id,reporter_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
— — Table structure for table tb_user_relationship — CREATE TABLE tb_user_relationship ( id bigint unsigned NOT NULL AUTO_INCREMENT, user_id bigint unsigned NOT NULL, target_user bigint unsigned NOT NULL, status_follow tinyint unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘0: unfollow; 1: follow; 2: block’, status_mute tinyint unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘0: unmute; 1: mute’, created_at bigint NOT NULL DEFAULT ‘0’, updated_at bigint NOT NULL DEFAULT ‘0’, PRIMARY KEY (id), UNIQUE KEY uiq_user_id_follwer_id (user_id,target_user), KEY idx_follower_id (target_user) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Timeline处理细节:1.在cache中存放timeline的post id list,每次直接从cache中获取;2.用户如果是登陆后进入首页,登陆后异步生成timeline(生成时间小于3s),然后进入首页后再请求timeline,就可以直接从缓存获取到;3.用户如果已经是登陆状态,打开首页,前端从浏览器缓存中获取老的timeline数据先进行显示,同时异步请求timeline进行刷新。4.对于重要的用户,发了推之后,利用kafka的异步机制,主动刷新在线的关注者的timeline,以方便重要的post可以第一时间发出。