mirror of https://github.com/rocboss/paopao-ce
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.
45 lines
1.1 KiB
45 lines
1.1 KiB
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")
|
|
);
|
|
|
|
CREATE INDEX "idx_post_metric_post_id_rank_score"
|
|
ON "p_post_metric" (
|
|
"post_id" ASC,
|
|
"rank_score" ASC
|
|
);
|
|
|
|
INSERT INTO p_post_metric (post_id, rank_score, created_on)
|
|
SELECT id AS post_id,
|
|
comment_count+upvote_count*2+collection_count*4 AS rank_score,
|
|
created_on
|
|
FROM p_post
|
|
WHERE is_del=0;
|
|
|
|
-- 原来的可见性: 0公开 1私密 2好友可见 3关注可见
|
|
-- 现在的可见性: 0私密 10充电可见 20订阅可见 30保留 40保留 50好友可见 60关注可见 70保留 80保留 90公开
|
|
UPDATE p_post AS a
|
|
SET visibility = (
|
|
SELECT
|
|
CASE visibility
|
|
WHEN 0 THEN 90
|
|
WHEN 1 THEN 0
|
|
WHEN 2 THEN 50
|
|
WHEN 3 THEN 60
|
|
ELSE 0
|
|
END
|
|
FROM
|
|
p_post AS b
|
|
WHERE
|
|
a.ID = b.ID
|
|
);
|