抖声后端开发记录(5)｜ 青训营笔记

这是我参与「第五届青训营 」伴学笔记创作活动的第 17 天

想给项目加个redis中间件。哎，一开始没有给这个中间件留位置，现在想要来加实在是麻烦。而且用的redis_fdw还有各种各样的功能限制，所以放弃了。其实就差搭上了，但是时间已经不允许了，一边还有毕业论文的事情更加紧急，靠我一人写不完这个项目了。这个项目的最终成果就是这样了。一队根本用不到那么多人，干活的两个就可以完成了。我的队友们都不说话，真的让人难受。

redis

我的想法是，将postgre的数据使用redisfdw的方式同步到redis。redis数据导入我还没找到好方法，但是如果redis和pg是一块运行的，且从一开始数据就进行同步的话，其实可以保证一致性问题。

我们用的pg是基于alpine的，为了使用redisfdw，需要编译插件。

之前用的alpine是3.7版本的，太老了，gcc版本都只有4.8。从docker上拉了个apline3.17版本的pg来用。

这里使用的rw-redis_fdw。另一个高星的redis_fdw没有办法插入变key的zset，所以换了这个，但这个redis_fdw也有点问题。总之就是凑合。

FROM postgres:alpine3.17

RUN sed -i 's/dl-cdn.alpinelinux.org/mirrors.ustc.edu.cn/g' /etc/apk/repositories && \
    apk update &&\
    set -eux && \
    apk add hiredis && \
    apk add --no-cache --virtual .build-deps hiredis-dev gcc libc-dev clang llvm unzip make && \
    wget https://lanpesk-package-proxy.obs.cn-north-4.myhuaweicloud.com/rw_redis_fdw-master.zip && \
    unzip rw_redis_fdw-master.zip && cd rw_redis_fdw-master && \
    make && make install && \
    apk del --no-network .build-deps && \
    rm -f rw_redis_fdw-master.zip;

ENTRYPOINT ["docker-entrypoint.sh"]

EXPOSE 5432

CMD ["postgres"]

用这个dockerfile来构建我们的postgre，这个docker会编译好我们的插件的。解释一下啊RUN的指令吧：

sed -i 's/dl-cdn.alpinelinux.org/mirrors.ustc.edu.cn/g' /etc/apk/repositories && apk update

换源， alpine使用的apk包管理器。

apk add hiredis && \
apk add --no-cache --virtual .build-deps hiredis-dev gcc libc-dev clang llvm unzip make && \

安装hiredis模块，我们的插件需要用到这个模块，

--no-cache不保存缓存，--virtual 设定包存储位置。方便后面清除。 编译这个redis组件需要用到gcc，所以需要安装gcc和开发库libc-dev。连接需要clang和llvm。使用make来管理编译， 最后一个unzip用来解压的。

apk del --no-network .build-deps

删除我们之前安装的编译所需的依赖。这些依赖占了300m。

对了这个rw-redis_fdw使用的是一个fork的版本，原版没有对pg15做支持。

按照我们的表结构可以开始创建redisfdw了。

CREATE EXTENSION IF NOT EXISTS redis_fdw;
CREATE SERVER redis_server FOREIGN DATA WRAPPER redis_fdw OPTIONS (host 'redis', port '6379');
CREATE USER MAPPING FOR PUBLIC SERVER redis_server OPTIONS (password '');

创建拓展，和服务，以及交互用的用户。

随后创建我们需要的外表即可，例如:

create foreign table if not exists rd_videos_timeseq (score bigint, member text, index int) server redis_server options(database '1', tabletype 'zset', key 'video_time_seq');

创建处理函数，以及在对应的表上创建对应的触发器。

CREATE EXTENSION IF NOT EXISTS redis_fdw;
CREATE SERVER redis_server FOREIGN DATA WRAPPER redis_fdw OPTIONS (host 'redis', port '6379');
CREATE USER MAPPING FOR PUBLIC SERVER redis_server OPTIONS (password '');

--- all the object value is a json string.

--- user info is single, dont need zset to sort.
create foreign table if not exists rd_users (key text, value text) server redis_server options (database '0'); 
--- this function is for User table, every insert update will trigger this, (del is not support)
--- all interaction (except del) will update this key-value in redis
create or replace function UserInteraction() returns trigger as $trigger_user_interaction$
begin
  insert into rd_users values ('user_'||new.id, row_to_json(new));
  return new;
end;
$trigger_user_interaction$ language plpgsql;
create or replace trigger user_interaction after insert or update on users for each row execute function UserInteraction();



create foreign table if not exists rd_videos (key text, value text) server redis_server options (database '0'); 
 --- use the create time as score
create foreign table if not exists rd_videos_timeseq (score bigint, member text, index int) server redis_server options(database '1', tabletype 'zset', key 'video_time_seq');
--- use create time as score, key 'user_videos_{user_id}'
create foreign table if not exists rd_user_videos (key text, member text, score bigint, index int) server redis_server options (database '1', tabletype 'zset'); 

--- trigger for video insert and update.
create or replace function VideoInsert() returns trigger as $trigger_video_insert$
begin 
  insert into rd_videos values ('video_'||new.id, row_to_json(new));
  insert into rd_videos_timeseq values (new.created_at, ''||new.id, 0);
  insert into rd_user_videos values ('user_videos_'||new.user_id, new.id, new.created_at, 0);
  return new;
end;
$trigger_video_insert$ language plpgsql;
create or replace function VideoUpdate() returns trigger as $trigger_video_update$
begin 
  insert into rd_videos values ('video_'||new.id, row_to_json(new));
  return new;
end;
$trigger_video_update$ language plpgsql;
create or replace trigger video_insert after insert on videos for each row execute function VideoInsert();
create or replace trigger video_update after update on videos for each row execute function VideoUpdate();


---! due to the rw-redis_fdw api, the delete operation cant be execute.
--- No support for update (app dont have this function)
-- create foreign table rd_comment (key text, value text) server redis_server options (database '1');
--- use the 'video_comment_{videos_id}' as the key, use create time as score
create foreign table if not exists rd_video_comment (key text, member text, score bigint, index int) server redis_server options (database '1', tabletype 'zset'); 
create or replace function OnInsertComment() returns trigger as $trigger_comment_insert$
begin
--   insert into rd_comment values ('comment_'||new.id, row_to_json(new));
  insert into rd_video_comment values ('video_comment_'||new.video_id, row_to_json(new), new.created_at, 0);
  return new;
end;
$trigger_comment_insert$ language plpgsql;
create or replace trigger video_comment_add after insert on comments for each row execute function OnInsertComment();

---! due to the rw-redis_fdw api, the delete operation cant be execute.
--- use create time as score, key 'user_favorite_{user_id}'
create foreign table if not exists rd_user_favorite (key text, member text, score bigint, index int, expiry bigint) server redis_server options (database '0', tabletype 'zset'); 
create or replace function DoFavorite() returns trigger as $trigger_do_favorite$
begin 
  insert into rd_user_favorite values ('user_favorite_'||new.user_id, ''||new.video_id, new.created_at, 0);
  return new;
end;
$trigger_do_favorite$ language plpgsql;
create or replace trigger user_do_favorite after insert on favorites for each row execute function DoFavorite();


--- use create time as score, key 'chat_{chat-key}'
create foreign table if not exists rd_user_chat (key text, member text, score bigint, index int, expiry bigint) server redis_server options (database '0', tabletype 'zset');
create or replace function ChatAdd() returns trigger as $chat_add$
begin
  insert into rd_user_chat values ('chat_'||new.chat_key, row_to_json(new), new.created_at);
  return new;
end;
$chat_add$ language plpgsql;
create or replace trigger chat_add after insert on chat_records for each row execute function ChatAdd();

---! due to the rw-redis_fdw api, the delete operation cant be execute.
--- use create time as score, key 'user_follow_{user_id}'
create foreign table if not exists rd_user_follow (key text, member text, score bigint, index int, expiry bigint) server redis_server options (database '0', tabletype 'zset');
--- use create time as score, key 'user_follower_{user_id}'
create foreign table if not exists rd_user_follower (key text, member text, score bigint, index int, expiry bigint) server redis_server options (database '0', tabletype 'zset');
create or replace function DoFollow() returns trigger as $do_follow$
begin 
  insert into rd_user_follow values ('user_follow_'||new.from_id, ''||new.to_id, new.created_at);
  insert into rd_user_follower values ('user_follower_'||new.to_id, ''||new.from_id, new.created_at);
  return new;
end;
$do_follow$ language plpgsql;
create or replace trigger do_follow after insert on relations for each row execute function DoFollow();

这样我们插入时，就会在redis更新数据了。