msb-public
/
paopao-ce
mirror of https://github.com/rocboss/paopao-ce
1
0
Code Issues Projects Releases Wiki Activity
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.
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'main'
${ noResults }
paopao-ce/scripts/paopao-sqlite3.sql

731 lines
19 KiB
Raw Permalink Blame History

PRAGMA foreign_keys = false;
-- ----------------------------
-- Table structure for p_attachment
-- ----------------------------
DROP TABLE IF EXISTS "p_attachment";
CREATE TABLE "p_attachment" (
"id" integer NOT NULL,
"user_id" integer NOT NULL,
"file_size" integer NOT NULL,
"img_width" integer NOT NULL,
"img_height" integer NOT NULL,
"type" integer NOT NULL,
"content" text(255) NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_captcha
-- ----------------------------
DROP TABLE IF EXISTS "p_captcha";
CREATE TABLE "p_captcha" (
"id" integer NOT NULL,
"phone" text(16) NOT NULL,
"captcha" text(16) NOT NULL,
"use_times" integer NOT NULL,
"expired_on" integer NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_comment
-- ----------------------------
DROP TABLE IF EXISTS "p_comment";
CREATE TABLE "p_comment" (
"id" integer NOT NULL,
"post_id" integer NOT NULL,
"user_id" integer NOT NULL,
"ip" text(64) NOT NULL,
"ip_loc" text(64) NOT NULL,
"is_essence" integer NOT NULL DEFAULT 0,
"thumbs_up_count" integer NOT NULL DEFAULT 0, -- 点赞数
"thumbs_down_count" integer NOT NULL DEFAULT 0, -- 点踩数
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_comment_content
-- ----------------------------
DROP TABLE IF EXISTS "p_comment_content";
CREATE TABLE "p_comment_content" (
"id" integer NOT NULL,
"comment_id" integer NOT NULL,
"user_id" integer NOT NULL,
"content" text NOT NULL,
"type" integer NOT NULL,
"sort" integer NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_comment_reply
-- ----------------------------
DROP TABLE IF EXISTS "p_comment_reply";
CREATE TABLE "p_comment_reply" (
"id" integer NOT NULL,
"comment_id" integer NOT NULL,
"user_id" integer NOT NULL,
"at_user_id" integer NOT NULL,
"content" text NOT NULL,
"ip" text(64) NOT NULL,
"ip_loc" text(64) NOT NULL,
"thumbs_up_count" integer NOT NULL DEFAULT 0, -- 点赞数
"thumbs_down_count" integer NOT NULL DEFAULT 0, -- 点踩数
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_comment_metric
-- ----------------------------
CREATE TABLE p_comment_metric (
"id" integer,
"comment_id" integer NOT NULL,
"rank_score" integer NOT NULL DEFAULT 0,
"incentive_score" integer NOT NULL DEFAULT 0,
"decay_factor" integer NOT NULL DEFAULT 0,
"motivation_factor" integer NOT NULL DEFAULT 0,
"is_del" integer NOT NULL DEFAULT 0,
"created_on" integer NOT NULL DEFAULT 0,
"modified_on" integer NOT NULL DEFAULT 0,
"deleted_on" integer NOT NULL DEFAULT 0,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_tweet_comment_thumbs
-- ----------------------------
DROP TABLE IF EXISTS p_tweet_comment_thumbs;
CREATE TABLE "p_tweet_comment_thumbs" (
"id" integer PRIMARY KEY,
"user_id" integer NOT NULL,
"tweet_id" integer NOT NULL,
"comment_id" integer NOT NULL,
"reply_id" integer,
"comment_type" integer NOT NULL DEFAULT 0, -- 评论类型 0为推文评论、1为评论回复
"is_thumbs_up" integer NOT NULL DEFAULT 0, -- 是否点赞 0 为否 1为是
"is_thumbs_down" integer NOT NULL DEFAULT 0, -- 是否点踩 0 为否 1为是
"created_on" integer NOT NULL DEFAULT 0,
"modified_on" integer NOT NULL DEFAULT 0,
"deleted_on" integer NOT NULL DEFAULT 0,
"is_del" integer NOT NULL DEFAULT 0 -- 是否删除 0 为未删除、1 为已删除
);
-- ----------------------------
-- Table structure for p_following
-- ----------------------------
DROP TABLE IF EXISTS "p_following";
CREATE TABLE "p_following" (
"id" integer NOT NULL,
"user_id" integer NOT NULL,
"follow_id" integer NOT NULL,
"is_del" integer NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_contact
-- ----------------------------
DROP TABLE IF EXISTS "p_contact";
CREATE TABLE "p_contact" (
"id" integer NOT NULL,
"user_id" integer NOT NULL,
"friend_id" integer NOT NULL,
"group_id" integer NOT NULL,
"remark" text(32) NOT NULL,
"status" integer NOT NULL,
"notice_enable" integer NOT NULL,
"is_top" integer NOT NULL,
"is_black" integer NOT NULL,
"is_del" integer NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_contact_group
-- ----------------------------
DROP TABLE IF EXISTS "p_contact_group";
CREATE TABLE "p_contact_group" (
"id" integer NOT NULL,
"user_id" integer NOT NULL,
"name" text(32) NOT NULL,
"is_del" integer NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_message
-- ----------------------------
DROP TABLE IF EXISTS "p_message";
CREATE TABLE "p_message" (
"id" integer NOT NULL,
"sender_user_id" integer NOT NULL,
"receiver_user_id" integer NOT NULL,
"type" integer NOT NULL,
"brief" text(255) NOT NULL,
"content" text(255) NOT NULL,
"post_id" integer NOT NULL,
"comment_id" integer NOT NULL,
"reply_id" integer NOT NULL,
"is_read" integer NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_post
-- ----------------------------
DROP TABLE IF EXISTS "p_post";
CREATE TABLE "p_post" (
"id" integer NOT NULL,
"user_id" integer NOT NULL,
"comment_count" integer NOT NULL,
"collection_count" integer NOT NULL,
"upvote_count" integer NOT NULL,
"share_count" integer NOT NULL,
"is_top" integer NOT NULL,
"is_essence" integer NOT NULL,
"is_lock" integer NOT NULL,
"latest_replied_on" integer NOT NULL,
"tags" text(255) NOT NULL,
"attachment_price" integer NOT NULL,
"ip" text(64) NOT NULL,
"ip_loc" text(64) NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
"visibility" integer NOT NULL, -- 可见性: 0私密 10充电可见 20订阅可见 30保留 40保留 50好友可见 60关注可见 70保留 80保留 90公开
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_post_metric
-- ----------------------------
DROP TABLE IF EXISTS "p_post_metric";
CREATE TABLE "p_post_metric" (
"id" integer NOT NULL,
"post_id" integer NOT NULL,
"rank_score" integer NOT NULL,
"incentive_score" integer NOT NULL DEFAULT 0,
"decay_factor" integer NOT NULL DEFAULT 0,
"motivation_factor" integer NOT NULL DEFAULT 0,
"is_del" integer NOT NULL DEFAULT 0,
"created_on" integer NOT NULL DEFAULT 0,
"modified_on" integer NOT NULL DEFAULT 0,
"deleted_on" integer NOT NULL DEFAULT 0,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_post_attachment_bill
-- ----------------------------
DROP TABLE IF EXISTS "p_post_attachment_bill";
CREATE TABLE "p_post_attachment_bill" (
"id" integer NOT NULL,
"post_id" integer NOT NULL,
"user_id" integer NOT NULL,
"paid_amount" integer NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_post_collection
-- ----------------------------
DROP TABLE IF EXISTS "p_post_collection";
CREATE TABLE "p_post_collection" (
"id" integer NOT NULL,
"post_id" integer NOT NULL,
"user_id" integer NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_post_content
-- ----------------------------
DROP TABLE IF EXISTS "p_post_content";
CREATE TABLE "p_post_content" (
"id" integer NOT NULL,
"post_id" integer NOT NULL,
"user_id" integer NOT NULL,
"content" text NOT NULL,
"type" integer NOT NULL,
"sort" integer NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_post_star
-- ----------------------------
DROP TABLE IF EXISTS "p_post_star";
CREATE TABLE "p_post_star" (
"id" integer NOT NULL,
"post_id" integer NOT NULL,
"user_id" integer NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_tag
-- ----------------------------
DROP TABLE IF EXISTS "p_tag";
CREATE TABLE "p_tag" (
"id" integer NOT NULL,
"user_id" integer NOT NULL,
"tag" text(255) NOT NULL,
"quote_num" integer NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_topic_user
-- ----------------------------
DROP TABLE IF EXISTS "p_topic_user";
CREATE TABLE "p_topic_user" (
"id" integer,
"topic_id" integer NOT NULL,-- 标签ID
"user_id" integer NOT NULL,-- 创建者ID
"alias_name" text ( 255 ),-- 别名
"remark" text ( 512 ),-- 备注
"quote_num" integer,-- 引用数
"is_top" integer NOT NULL DEFAULT 0,-- 是否置顶 0 为未置顶、1 为已置顶
"created_on" integer NOT NULL DEFAULT 0,-- 创建时间
"modified_on" integer NOT NULL DEFAULT 0,-- 修改时间
"deleted_on" integer NOT NULL DEFAULT 0,-- 删除时间
"is_del" integer NOT NULL DEFAULT 0,-- 是否删除 0 为未删除、1 为已删除
"reserve_a" text,-- 保留字段a
"reserve_b" text,-- 保留字段b
PRIMARY KEY ( "id" )
);
-- ----------------------------
-- Table structure for p_user
-- ----------------------------
DROP TABLE IF EXISTS "p_user";
CREATE TABLE "p_user" (
"id" integer NOT NULL,
"nickname" text(32) NOT NULL,
"username" text(32) NOT NULL,
"phone" text(16) NOT NULL,
"password" text(32) NOT NULL,
"salt" text(16) NOT NULL,
"status" integer NOT NULL,
"avatar" text(255) NOT NULL,
"balance" integer NOT NULL,
"is_admin" integer NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_user_metric
-- ----------------------------
CREATE TABLE "p_user_metric" (
"id" integer,
"user_id" integer NOT NULL,
"tweets_count" integer NOT NULL DEFAULT 0,
"latest_trends_on" integer NOT NULL DEFAULT 0,
"is_del" integer NOT NULL DEFAULT 0,
"created_on" integer NOT NULL DEFAULT 0,
"modified_on" integer NOT NULL DEFAULT 0,
"deleted_on" integer NOT NULL DEFAULT 0,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_wallet_recharge
-- ----------------------------
DROP TABLE IF EXISTS "p_wallet_recharge";
CREATE TABLE "p_wallet_recharge" (
"id" integer NOT NULL,
"user_id" integer NOT NULL,
"amount" integer NOT NULL,
"trade_no" text(64) NOT NULL,
"trade_status" text(32) NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("id")
);
-- ----------------------------
-- Table structure for p_wallet_statement
-- ----------------------------
DROP TABLE IF EXISTS "p_wallet_statement";
CREATE TABLE "p_wallet_statement" (
"id" integer NOT NULL,
"user_id" integer NOT NULL,
"change_amount" integer NOT NULL,
"balance_snapshot" integer NOT NULL,
"reason" text(255) NOT NULL,
"post_id" integer NOT NULL,
"created_on" integer NOT NULL,
"modified_on" integer NOT NULL,
"deleted_on" integer NOT NULL,
"is_del" integer NOT NULL,
PRIMARY KEY ("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;
-- ----------------------------
-- Indexes structure for table p_attachment
-- ----------------------------
CREATE INDEX "idx_attachment_user_id"
ON "p_attachment" (
"user_id" ASC
);
-- ----------------------------
-- Indexes structure for table p_captcha
-- ----------------------------
CREATE INDEX "idx_captcha_expired_on"
ON "p_captcha" (
"expired_on" ASC
);
CREATE INDEX "idx_captcha_phone"
ON "p_captcha" (
"phone" ASC
);
CREATE INDEX "idx_captcha_use_times"
ON "p_captcha" (
"use_times" ASC
);
-- ----------------------------
-- Indexes structure for table p_comment
-- ----------------------------
CREATE INDEX "idx_comment_post_id"
ON "p_comment" (
"post_id" ASC
);
CREATE INDEX "idx_comment_user_id"
ON "p_comment" (
"user_id" ASC
);
-- ----------------------------
-- Indexes structure for table p_comment_content
-- ----------------------------
CREATE INDEX "idx_comment_content_comment_id"
ON "p_comment_content" (
"comment_id" ASC
);
CREATE INDEX "idx_comment_content_sort"
ON "p_comment_content" (
"sort" ASC
);
CREATE INDEX "idx_comment_content_type"
ON "p_comment_content" (
"type" ASC
);
CREATE INDEX "idx_comment_content_user_id"
ON "p_comment_content" (
"user_id" ASC
);
-- ----------------------------
-- Indexes structure for table p_comment_reply
-- ----------------------------
CREATE INDEX "idx_comment_reply_comment_id"
ON "p_comment_reply" (
"comment_id" ASC
);
-- ----------------------------
-- Indexes structure for table p_comment_metric
-- ----------------------------
CREATE INDEX "idx_comment_metric_comment_id_rank_score"
ON "p_comment_metric" (
"comment_id" ASC,
"rank_score" ASC
);
-- ----------------------------
-- Indexes structure for table idx_tweet_comment_thumbs_uid_tid
-- ----------------------------
CREATE INDEX "idx_tweet_comment_thumbs_uid_tid"
ON "p_tweet_comment_thumbs"(
"user_id" ASC,
"tweet_id" ASC
);
-- ----------------------------
-- Indexes structure for table p_following
-- ----------------------------
CREATE INDEX "idx_following_user_follow"
ON "p_following" (
"user_id" ASC,
"follow_id" ASC
);
-- ----------------------------
-- Indexes structure for table p_contact
-- ----------------------------
CREATE UNIQUE INDEX "idx_contact_user_friend"
ON "p_contact" (
"user_id" ASC,
"friend_id" ASC
);
CREATE INDEX "idx_contact_user_friend_status"
ON "p_contact" (
"user_id" ASC,
"friend_id" ASC,
"status" ASC
);
-- ----------------------------
-- Indexes structure for table p_message
-- ----------------------------
CREATE INDEX "idx_message_is_read"
ON "p_message" (
"is_read" ASC
);
CREATE INDEX "idx_message_receiver_user_id"
ON "p_message" (
"receiver_user_id" ASC
);
CREATE INDEX "idx_message_type"
ON "p_message" (
"type" ASC
);
-- ----------------------------
-- Indexes structure for table p_post
-- ----------------------------
CREATE INDEX "idx_post_user_id"
ON "p_post" (
"user_id" ASC
);
CREATE INDEX "idx_post_visibility"
ON "p_post" (
"visibility" ASC
);
-- ----------------------------
-- Indexes structure for table idx_post_metric_post_id_rank_score
-- ----------------------------
CREATE INDEX "idx_post_metric_post_id_rank_score"
ON "p_post_metric" (
"post_id" ASC,
"rank_score" ASC
);
-- ----------------------------
-- Indexes structure for table p_post_attachment_bill
-- ----------------------------
CREATE INDEX "idx_post_attachment_bill_post_id"
ON "p_post_attachment_bill" (
"post_id" ASC
);
CREATE INDEX "idx_post_attachment_bill_user_id"
ON "p_post_attachment_bill" (
"user_id" ASC
);
-- ----------------------------
-- Indexes structure for table p_post_collection
-- ----------------------------
CREATE INDEX "idx_post_collection_post_id"
ON "p_post_collection" (
"post_id" ASC
);
CREATE INDEX "idx_post_collection_user_id"
ON "p_post_collection" (
"user_id" ASC
);
-- ----------------------------
-- Indexes structure for table p_post_content
-- ----------------------------
CREATE INDEX "idx_post_content_post_id"
ON "p_post_content" (
"post_id" ASC
);
CREATE INDEX "idx_post_content_user_id"
ON "p_post_content" (
"user_id" ASC
);
-- ----------------------------
-- Indexes structure for table p_post_star
-- ----------------------------
CREATE INDEX "idx_post_star_post_id"
ON "p_post_star" (
"post_id" ASC
);
CREATE INDEX "idx_post_star_user_id"
ON "p_post_star" (
"user_id" ASC
);
-- ----------------------------
-- Indexes structure for table p_tag
-- ----------------------------
CREATE UNIQUE INDEX "idx_tag"
ON "p_tag" (
"tag" ASC
);
CREATE INDEX "idx_tag_quote_num"
ON "p_tag" (
"quote_num" ASC
);
CREATE INDEX "idx_tag_user_id"
ON "p_tag" (
"user_id" ASC
);
-- ----------------------------
-- Indexes structure for table p_topic_user
-- ----------------------------
CREATE UNIQUE INDEX "idx_topic_user_uid_tid"
ON "p_topic_user" (
"topic_id",
"user_id"
);
-- ----------------------------
-- Indexes structure for table p_user
-- ----------------------------
CREATE INDEX "idx_user_phone"
ON "p_user" (
"phone" ASC
);
CREATE UNIQUE INDEX "idx_user_username"
ON "p_user" (
"username" ASC
);
-- ----------------------------
-- Indexes structure for table p_user_metric
-- ----------------------------
CREATE INDEX "idx_user_metric_user_id_tweets_count_trends"
ON "p_user_metric" (
"user_id" ASC,
"tweets_count" ASC,
"latest_trends_on" ASC
);
-- ----------------------------
-- Indexes structure for table p_wallet_recharge
-- ----------------------------
CREATE INDEX "idx_wallet_recharge_trade_no"
ON "p_wallet_recharge" (
"trade_no" ASC
);
CREATE INDEX "idx_wallet_recharge_trade_status"
ON "p_wallet_recharge" (
"trade_status" ASC
);
CREATE INDEX "idx_wallet_recharge_user_id"
ON "p_wallet_recharge" (
"user_id" ASC
);
-- ----------------------------
-- Indexes structure for table p_wallet_statement
-- ----------------------------
CREATE INDEX "idx_wallet_statement_user_id"
ON "p_wallet_statement" (
"user_id" ASC
);
PRAGMA foreign_keys = true;
Reference in new issue View Git Blame Copy Permalink