You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
paopao-ce/scripts/paopao-postgres.sql

428 lines
17 KiB

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
DROP TABLE IF EXISTS p_attachment;
CREATE TABLE p_attachment (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL DEFAULT 0,
file_size BIGINT NOT NULL,
img_width BIGINT NOT NULL DEFAULT 0,
img_height BIGINT NOT NULL DEFAULT 0,
"type" SMALLINT NOT NULL DEFAULT 1, -- 1图片、2视频、3其他附件
content VARCHAR(255) NOT NULL DEFAULT '',
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0 -- 是否删除 0为未删除、1为已删除
);
CREATE INDEX idx_attachment_user_id ON p_attachment USING btree (id);
DROP TABLE IF EXISTS p_captcha;
CREATE TABLE p_captcha (
id BIGSERIAL PRIMARY KEY,
phone VARCHAR(16),
captcha VARCHAR(16),
use_times INTEGER NOT NULL DEFAULT 0,
expired_on BIGINT NOT NULL DEFAULT 0,
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0 -- 是否删除 0为未删除、1为已删除'
);
CREATE INDEX idx_captcha_phone ON p_captcha USING btree (phone);
CREATE INDEX idx_captcha_expired_on ON p_captcha USING btree (expired_on);
CREATE INDEX idx_captcha_use_times ON p_captcha USING btree (use_times);
DROP TABLE IF EXISTS p_comment;
CREATE TABLE p_comment (
id BIGSERIAL PRIMARY KEY,
post_id BIGINT NOT NULL DEFAULT 0,
user_id BIGINT NOT NULL DEFAULT 0,
ip VARCHAR(64) NOT NULL DEFAULT '',
ip_loc VARCHAR(64) NOT NULL DEFAULT '',
is_essence SMALLINT NOT NULL DEFAULT 0,
reply_count INT NOT NULL DEFAULT 0, -- 回复数
thumbs_up_count INT NOT NULL DEFAULT 0, -- 点赞数
thumbs_down_count INT NOT NULL DEFAULT 0, -- 点踩数
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX idx_comment_post_id ON p_comment USING btree (post_id);
CREATE INDEX idx_comment_user_id ON p_comment USING btree (user_id);
DROP TABLE IF EXISTS p_comment_content;
CREATE TABLE p_comment_content (
id BIGSERIAL PRIMARY KEY,
comment_id BIGINT NOT NULL DEFAULT 0,
user_id BIGINT NOT NULL DEFAULT 0,
content TEXT NOT NULL DEFAULT '',
"type" SMALLINT NOT NULL DEFAULT 2, -- 类型1标题2文字段落3图片地址4视频地址5语音地址6链接地址,
sort BIGINT NOT NULL DEFAULT 100,
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX idx_comment_content_comment_id ON p_comment_content USING btree (comment_id);
CREATE INDEX idx_comment_content_user_id ON p_comment_content USING btree (user_id);
CREATE INDEX idx_comment_content_type ON p_comment_content USING btree ("type");
CREATE INDEX idx_comment_content_sort ON p_comment_content USING btree (sort);
DROP TABLE IF EXISTS p_comment_reply;
CREATE TABLE p_comment_reply (
id BIGSERIAL PRIMARY KEY,
comment_id BIGINT NOT NULL DEFAULT 0,
user_id BIGINT NOT NULL DEFAULT 0,
at_user_id BIGINT NOT NULL DEFAULT 0,
content TEXT NOT NULL DEFAULT '',
ip VARCHAR(64) NOT NULL DEFAULT '',
ip_loc VARCHAR(64) NOT NULL DEFAULT '',
thumbs_up_count int NOT NULL DEFAULT 0, -- 点赞数
thumbs_down_count int NOT NULL DEFAULT 0, -- 点踩数
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX idx_comment_reply_comment_id ON p_comment_reply USING btree (comment_id);
CREATE TABLE p_comment_metric (
id BIGSERIAL PRIMARY KEY,
comment_id BIGINT NOT NULL,
rank_score BIGINT NOT NULL DEFAULT 0,
incentive_score INT NOT NULL DEFAULT 0,
decay_factor INT NOT NULL DEFAULT 0,
motivation_factor INT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0,
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0
);
CREATE INDEX idx_comment_metric_comment_id_rank_score ON p_comment_metric USING btree (comment_id, rank_score);
DROP TABLE IF EXISTS p_tweet_comment_thumbs;
CREATE TABLE p_tweet_comment_thumbs (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
tweet_id BIGINT NOT NULL,
comment_id BIGINT NOT NULL,
reply_id BIGINT,
comment_type SMALLINT NOT NULL DEFAULT 0, -- 评论类型 0为推文评论、1为评论回复
is_thumbs_up SMALLINT NOT NULL DEFAULT 0, -- 是否点赞 0 为否 1为是
is_thumbs_down SMALLINT NOT NULL DEFAULT 0, -- 是否点踩 0 为否 1为是
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0 -- 是否删除 0 为未删除、1 为已删除
);
CREATE INDEX idx_tweet_comment_thumbs_uid_tid ON p_tweet_comment_thumbs USING btree (user_id, tweet_id);
DROP TABLE IF EXISTS p_message;
CREATE TABLE p_message (
id BIGSERIAL PRIMARY KEY,
sender_user_id BIGINT NOT NULL DEFAULT 0,
receiver_user_id BIGINT NOT NULL DEFAULT 0,
"type" SMALLINT NOT NULL DEFAULT 1,
brief VARCHAR(255) NOT NULL DEFAULT '',
content VARCHAR(255) NOT NULL DEFAULT '',
post_id BIGINT NOT NULL DEFAULT 0,
comment_id BIGINT NOT NULL DEFAULT 0,
reply_id BIGINT NOT NULL DEFAULT 0,
is_read SMALLINT NOT NULL DEFAULT 0,
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX idx_message_receiver_user_id ON p_message USING btree (receiver_user_id);
CREATE INDEX idx_message_is_read ON p_message USING btree (is_read);
CREATE INDEX idx_message_type ON p_message USING btree ("type");
CREATE SEQUENCE IF NOT EXISTS post_id_seq AS BIGINT MINVALUE 1080017989 NO MAXVALUE;
DROP TABLE IF EXISTS p_post;
CREATE TABLE p_post (
id BIGINT NOT NULL DEFAULT nextval('post_id_seq'::regclass),
user_id BIGINT NOT NULL DEFAULT 0,
comment_count BIGINT NOT NULL DEFAULT 0,
collection_count BIGINT NOT NULL DEFAULT 0,
upvote_count BIGINT NOT NULL DEFAULT 0,
share_count BIGINT NOT NULL DEFAULT 0,
visibility SMALLINT NOT NULL DEFAULT 0, -- 可见性: 0私密 10充电可见 20订阅可见 30保留 40保留 50好友可见 60关注可见 70保留 80保留 90公开
is_top SMALLINT NOT NULL DEFAULT 0, -- 是否置顶
is_essence SMALLINT NOT NULL DEFAULT 0, -- 是否精华
is_lock SMALLINT NOT NULL DEFAULT 0, -- 是否锁定
latest_replied_on BIGINT NOT NULL DEFAULT 0, -- 最新回复时间
tags VARCHAR(255) NOT NULL DEFAULT '',
attachment_price BIGINT NOT NULL DEFAULT 0, -- 附件价格(分)
ip VARCHAR(64) NOT NULL DEFAULT '', -- IP地址
ip_loc VARCHAR(64) NOT NULL DEFAULT '', -- IP城市地址
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
CREATE INDEX idx_post_user_id ON p_post USING btree (user_id);
CREATE INDEX idx_post_visibility ON p_post USING btree (visibility);
DROP TABLE IF EXISTS p_post_metric;
CREATE TABLE p_post_metric (
ID BIGSERIAL PRIMARY KEY,
post_id BIGINT NOT NULL,
rank_score BIGINT NOT NULL DEFAULT 0,
incentive_score INT NOT NULL DEFAULT 0,
decay_factor INT NOT NULL DEFAULT 0,
motivation_factor INT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0,
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0
);
CREATE INDEX idx_post_metric_post_id_rank_score ON p_post_metric USING btree (post_id, rank_score);
DROP TABLE IF EXISTS p_post_attachment_bill;
CREATE TABLE p_post_attachment_bill (
id BIGSERIAL PRIMARY KEY,
post_id BIGINT NOT NULL DEFAULT 0,
user_id BIGINT NOT NULL DEFAULT 0,
paid_amount BIGINT NOT NULL DEFAULT 0, -- 支付金额
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX idx_post_attachment_bill_post_id ON p_post_attachment_bill USING btree (post_id);
CREATE INDEX idx_post_attachment_bill_user_id ON p_post_attachment_bill USING btree (user_id);
DROP TABLE IF EXISTS p_post_collection;
CREATE TABLE p_post_collection (
id BIGSERIAL PRIMARY KEY,
post_id BIGINT NOT NULL DEFAULT 0,
user_id BIGINT NOT NULL DEFAULT 0,
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX idx_post_collection_post_id ON p_post_collection USING btree (post_id);
CREATE INDEX idx_post_collection_user_id ON p_post_collection USING btree (user_id);
DROP TABLE IF EXISTS p_post_content;
CREATE TABLE p_post_content (
id BIGSERIAL PRIMARY KEY,
post_id BIGINT NOT NULL DEFAULT 0,
user_id BIGINT NOT NULL DEFAULT 0,
content TEXT NOT NULL DEFAULT '',
"type" SMALLINT NOT NULL DEFAULT 2, -- 类型1标题2文字段落3图片地址4视频地址5语音地址6链接地址7附件资源8收费资源
sort SMALLINT NOT NULL DEFAULT 100,
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX idx_post_content_post_id ON p_post_content USING btree (post_id);
CREATE INDEX idx_post_content_user_id ON p_post_content USING btree (user_id);
DROP TABLE IF EXISTS p_post_star;
CREATE TABLE p_post_star (
id BIGSERIAL PRIMARY KEY,
post_id BIGINT NOT NULL DEFAULT 0,
user_id BIGINT NOT NULL DEFAULT 0,
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX idx_post_star_post_id ON p_post_star USING btree (post_id);
CREATE INDEX idx_post_star_user_id ON p_post_star USING btree (user_id);
DROP TABLE IF EXISTS p_tag;
CREATE TABLE p_tag (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL DEFAULT 0,
tag VARCHAR(255) NOT NULL,
quote_num BIGINT NOT NULL DEFAULT 0, -- 引用数
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0
);
CREATE UNIQUE INDEX idx_tag_tag ON p_tag USING btree (tag);
CREATE INDEX idx_tag_user_id ON p_tag USING btree (user_id);
CREATE INDEX idx_tag_quote_num ON p_tag USING btree (quote_num);
DROP TABLE IF EXISTS p_topic_user;
CREATE TABLE p_topic_user (
ID BIGSERIAL PRIMARY KEY,
topic_id BIGINT NOT NULL,-- 标签ID
user_id BIGINT NOT NULL,-- 创建者ID
alias_name VARCHAR ( 255 ),-- 别名
remark VARCHAR ( 512 ),-- 备注
quote_num BIGINT,-- 引用数
is_top SMALLINT NOT NULL DEFAULT 0,-- 是否置顶 0 为未置顶、1 为已置顶
is_pin SMALLINT NOT NULL DEFAULT 0,-- 是否钉住 0 为未钉住、1 为已钉住
created_on BIGINT NOT NULL DEFAULT 0,-- 创建时间
modified_on BIGINT NOT NULL DEFAULT 0,-- 修改时间
deleted_on BIGINT NOT NULL DEFAULT 0,-- 删除时间
is_del SMALLINT NOT NULL DEFAULT 0,-- 是否删除 0 为未删除、1 为已删除
reserve_a VARCHAR ( 255 ),-- 保留字段a
reserve_b VARCHAR ( 255 ) -- 保留字段b
);
CREATE UNIQUE INDEX idx_topic_user_uid_tid ON p_topic_user USING btree ( topic_id, user_id );
CREATE INDEX idx_topic_user_uid_ispin ON p_topic_user USING btree ( user_id, is_pin );
CREATE SEQUENCE IF NOT EXISTS user_id_seq AS BIGINT MINVALUE 100058 NO MAXVALUE;
DROP TABLE IF EXISTS p_user;
CREATE TABLE p_user (
id BIGINT NOT NULL DEFAULT nextval('user_id_seq'::regclass),
nickname VARCHAR(32) NOT NULL DEFAULT '',
username VARCHAR(32) NOT NULL DEFAULT '',
phone VARCHAR(16) NOT NULL DEFAULT '', -- 手机号
password VARCHAR(32) NOT NULL DEFAULT '', -- MD5密码
salt VARCHAR(16) NOT NULL DEFAULT '', -- 盐值
status SMALLINT NOT NULL DEFAULT 1, -- 状态1正常2停用
avatar VARCHAR(255) NOT NULL DEFAULT '',
balance BIGINT NOT NULL, -- 用户余额(分)
is_admin BOOLEAN NOT NULL DEFAULT false, -- 是否管理员
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX idx_user_username ON p_user USING btree (username);
CREATE INDEX idx_user_phone ON p_user USING btree (phone);
CREATE TABLE p_user_metric (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
tweets_count INT NOT NULL DEFAULT 0,
latest_trends_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0,
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0
);
CREATE INDEX idx_user_metric_user_id_tweets_count_trends ON p_user_metric USING btree (user_id, tweets_count, latest_trends_on);
DROP TABLE IF EXISTS p_following;
CREATE TABLE p_following (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
follow_id BIGINT NOT NULL,
is_del SMALLINT NOT NULL DEFAULT 0, -- 是否删除, 0否, 1是
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0
);
CREATE INDEX idx_following_user_follow ON p_following USING btree (user_id, follow_id);
DROP TABLE IF EXISTS p_contact;
CREATE TABLE p_contact (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
friend_id BIGINT NOT NULL,
group_id BIGINT NOT NULL DEFAULT 0, -- 好友分组ID:默认为0无分组
remark VARCHAR(32) NOT NULL DEFAULT '', -- 好友备注
status SMALLINT NOT NULL DEFAULT 0, -- 好友状态: 1请求好友, 2已好友, 3拒绝好友, 4已删好友
is_top SMALLINT NOT NULL DEFAULT 0, -- 是否置顶, 0否, 1是
is_black SMALLINT NOT NULL DEFAULT 0, -- 是否为黑名单, 0否, 1是
is_del SMALLINT NOT NULL DEFAULT 0, -- 否删除好友, 0否, 1是
notice_enable SMALLINT NOT NULL DEFAULT 0, -- 是否有消息提醒, 0否, 1是
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0
);
CREATE UNIQUE INDEX idx_contact_user_friend ON p_contact USING btree (user_id,friend_id);
CREATE INDEX idx_contact_user_friend_status ON p_contact USING btree (user_id, friend_id, status);
DROP TABLE IF EXISTS p_contact_group;
CREATE TABLE p_contact_group (
id BIGSERIAL PRIMARY KEY,
user_id int NOT NULL DEFAULT 0,
name VARCHAR(32) NOT NULL DEFAULT '', -- 分组名称
is_del SMALLINT NOT NULL DEFAULT 1, -- 是否删除, 0否, 1是
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0
);
DROP TABLE IF EXISTS p_wallet_recharge;
CREATE TABLE p_wallet_recharge (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL DEFAULT 0,
amount BIGINT NOT NULL DEFAULT 0, -- 充值金额
trade_no VARCHAR(64) NOT NULL DEFAULT '', -- 支付宝订单号
trade_status VARCHAR(32) NOT NULL DEFAULT '', -- 交易状态
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0 -- 是否删除 0 为未删除、1 为已删除
);
CREATE INDEX idx_wallet_recharge_user_id ON p_wallet_recharge USING btree (user_id);
CREATE INDEX idx_wallet_recharge_trade_no ON p_wallet_recharge USING btree (trade_no);
CREATE INDEX idx_wallet_recharge_trade_status ON p_wallet_recharge USING btree (trade_status);
DROP TABLE IF EXISTS p_wallet_statement;
CREATE TABLE p_wallet_statement (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL DEFAULT 0,
change_amount BIGINT NOT NULL DEFAULT 0, -- 变动金额
balance_snapshot BIGINT NOT NULL DEFAULT 0, -- 资金快照
reason VARCHAR(255) NOT NULL, -- 变动原因
post_id BIGINT NOT NULL DEFAULT 0, -- 关联动态
created_on BIGINT NOT NULL DEFAULT 0,
modified_on BIGINT NOT NULL DEFAULT 0,
deleted_on BIGINT NOT NULL DEFAULT 0,
is_del SMALLINT NOT NULL DEFAULT 0
);
CREATE INDEX idx_wallet_statement_user_id ON p_wallet_statement USING btree (user_id);
DROP VIEW IF EXISTS p_post_by_media;
CREATE VIEW p_post_by_media AS
SELECT post.*
FROM
( SELECT DISTINCT post_id FROM p_post_content WHERE ( TYPE = 3 OR TYPE = 4 OR TYPE = 7 OR TYPE = 8 ) AND is_del = 0 ) media
JOIN p_post post ON media.post_id = post.ID
WHERE
post.is_del = 0;
DROP VIEW IF EXISTS p_post_by_comment;
CREATE VIEW p_post_by_comment AS
SELECT P.*, C.user_id comment_user_id
FROM
(
SELECT
post_id,
user_id
FROM
p_comment
WHERE
is_del = 0 UNION
SELECT
post_id,
reply.user_id user_id
FROM
p_comment_reply reply
JOIN p_comment COMMENT ON reply.comment_id = COMMENT.ID
WHERE
reply.is_del = 0
AND COMMENT.is_del = 0
)
C JOIN p_post P ON C.post_id = P.ID
WHERE
P.is_del = 0;
DROP VIEW IF EXISTS p_user_relation;
CREATE VIEW p_user_relation AS
SELECT user_id, friend_id he_uid, 5 AS style
FROM p_contact WHERE status=2 AND is_del=0
UNION
SELECT user_id, follow_id he_uid, 10 AS style
FROM p_following WHERE is_del=0;