β

Erlang简易的MySQL ORM实现

Roowe❤ 483 阅读

目的

在此之前,我写过一篇,如何在erlang去拼接SQL语句,《在erlang简单使用SQL语句》

现在,在这个基础上,对record做一层封装,我将一个#player{}扔到某个函数,就能帮我写数据库或者,删掉,或者更新等的一些常用几种db操作。

注:本文讨论的是,针对单record,也就是一张表,所以下面,你也不会看到多张表,联表什么的,现在大家只是把数据库做持久化而已,逻辑全部在内存做。也就是说player和goods要搞基,也是在内存,而不是通过SQL语句。

老项目的做法

%% 更新用户卡数据
update_user_card(RecUserCard) ->
    [id | FieldList] = record_info(fields, rec_user_card),
    ValueList = lists:nthtail(2, tuple_to_list(RecUserCard)),
    ZipList = lists:zip(FieldList, ValueList),
    db_center:update(user_card, ZipList, [{id, RecUserCard#rec_user_card.id}]).

上面这段代码比较典型,抽象地讲,update无非就是db table update_clause, where_clause等几个东西,但是我们代码当时各种复制粘帖,有时候对这些代码还是比较烦躁。

这种,相比那些直接将字段写死在代码又稍微好点,数据库将nickname2改为nickname的时候,代码不用动,因为我们做了从数据库映射成record声明的东西,声明变了,也就是record_info变了,再后面也就和数据库对上了。

新的做法

声明一个record_mysql_info,然后新加表的时候,声明下里面的定义。接着传给db_mysql_base就可以,什么zip之类就不用写了。

没有带r的就是针对这个表的操作,带r的就是传record进来。考虑有时候并不是操作record,而是操作该表,所以提供通用的没有record的操作,再特殊点,就直接去调erl_mysql,还不行就能自己拼写SQL(假设多表操作很少用,就不做支持了)。

核心代码也就那么一点点啦。

%% 约定:
%% 1、第一个字段是键值,作为key存在,跟mnesia的规则一样,简化底层代码,提高运行效率,通过约束减少不必要的运算
-module(db_mysql_base).
 
-export([select/2, select/3, update/3, delete/2]).
 
-export([
         r_update/2,
         r_delete/2,
         r_insert/2, 
         r_list_insert_withnot_id/2,
         r_list_insert_with_id/2
        ]).
 
-include_lib("emysql/include/emysql.hrl").
-record(record_mysql_info,{
          db_pool,
          table_name,
          fields,
          record_name,
          mod
         }).
 
 
select(RecordMysqlInfo, WhereClause) -> 
    select(RecordMysqlInfo, WhereClause, undefined).
 
select(#record_mysql_info{
          db_pool = DbPool,
          table_name = TableName,
          record_name = RecordName,
          mod = ModelMod
         }, WhereClause, Extras) ->
    SQL = iolist_to_binary(erl_mysql:select('*', TableName, WhereClause, Extras)),
    run_rows(DbPool, SQL,
             fun(List) ->
                     [ModelMod:out_db_hook(list_to_tuple([RecordName|Vals])) || Vals <- List]
             end).
 
update(#record_mysql_info{
          db_pool = DbPool,
          table_name = TableName
         }, UpdateClause, WhereClause) ->
    SQL = iolist_to_binary(erl_mysql:update(TableName, UpdateClause, WhereClause)),
    run_affected(DbPool, SQL).
 
delete(#record_mysql_info{
          db_pool = DbPool,
          table_name = TableName
         }, WhereClause) ->
    SQL = iolist_to_binary(erl_mysql:delete(TableName, WhereClause)),
    run_affected(DbPool, SQL).
 
%% 下面带r的是,是针对record的接口
%% 返回值{ok, Record},会自动处理,需不需要加上insert_id
%% 或者{error, Result}
r_insert(#record_mysql_info{
            db_pool = DbPool,
            table_name = TableName,
            fields = [_|RestFields] = Fields,
            mod = ModelMod
         }, Record) when is_tuple(Record)->
    [UndefId|RestVals] = Vals = record_to_vals(ModelMod, Record),
    {FilterUndefIdFields, FilterUndefIdVals} = 
        if
            UndefId =:= undefined ->
                {RestFields, RestVals};
            true ->
                {Fields, Vals}
        end,    
    SQL = iolist_to_binary(erl_mysql:insert(TableName, {FilterUndefIdFields, [FilterUndefIdVals]})),
    case emysql:execute(DbPool, SQL) of
        Result when is_record(Result, ok_packet) ->
            if 
                UndefId =:= undefined ->
                    {ok, setelement(2, Record, emysql_util:insert_id(Result))};
                true ->
                    {ok, Record}
            end;
        Result when is_record(Result, error_packet) ->
            {error, Result}
    end.
 
%% 多行插入的混合接口的返回值实在不好做,干脆要么有id要么没有id,
%% 在表设计的时候已经决定了这点,正常设计不会出现有时候要向MySQL要id,有时候又不要。
%% 多行插入也就日志系统使用会有一定的优化,其他场合应该都一条一条插入
r_list_insert_withnot_id(#record_mysql_info{
                            db_pool = DbPool,
                            table_name = TableName,
                            fields = [_|RestFields],
                            mod = ModelMod
                           }, RecordList) 
  when is_list(RecordList) ->    
    FilterUndefIdValsList = [tl(record_to_vals(ModelMod, Record)) || Record <- RecordList],
    SQL = iolist_to_binary(erl_mysql:insert(TableName, {RestFields, FilterUndefIdValsList})),    
    run_affected(DbPool, SQL).
 
r_list_insert_with_id(#record_mysql_info{
                         db_pool = DbPool,
                         table_name = TableName,
                         fields = Fields,
                         mod = ModelMod
                        }, RecordList) 
  when is_list(RecordList) ->
    ValsList = [record_to_vals(ModelMod, Record) || Record <- RecordList],
    SQL = iolist_to_binary(erl_mysql:insert(TableName, {Fields, ValsList})),    
    run_affected(DbPool, SQL).
 
 
%% update list的返回值不好定,所以不支持
r_update(#record_mysql_info{
            db_pool = DbPool,
            table_name = TableName,
            fields = [IdField|RestFields],
            mod = ModelMod
           }, Record) ->
    [Id|RestVals] = record_to_vals(ModelMod, Record),
    WhereClause = {IdField, '=', Id},
    UpdateClause = lists:zip(RestFields, RestVals),
    SQL = iolist_to_binary(erl_mysql:update(TableName, UpdateClause, WhereClause)),
    run_affected(DbPool, SQL).
 
 
r_delete(#record_mysql_info{
            db_pool = DbPool,
            table_name = TableName,
            fields = [IdField|_]
           }, Record) 
  when is_tuple(Record) ->
    Id = element(2, Record),
    WhereClause = {IdField, '=', Id},
    SQL = iolist_to_binary(erl_mysql:delete(TableName, WhereClause)),
    run_affected(DbPool, SQL);
r_delete(#record_mysql_info{
            db_pool = DbPool,
            table_name = TableName,
            fields = [IdField|_]
           }, RecordList) 
  when is_list(RecordList) ->
    Ids = [element(2, Record) || Record <- RecordList],
    WhereClause = {IdField, 'in', Ids},
    SQL = iolist_to_binary(erl_mysql:delete(TableName, WhereClause)),
    run_affected(DbPool, SQL).
 
 
%% -------------------- emysql封装 --------------------
run_affected(DbPool, SQL) ->
    case emysql:execute(DbPool, SQL) of
        Result when is_record(Result, ok_packet) ->
            {ok, emysql_util:affected_rows(Result)};
        Result when is_record(Result, error_packet) ->
            {error, Result}
    end.
 
%% run_rows(DbPool, SQL) ->
%%     run_rows(DbPool, SQL, fun(A) -> A end).
 
run_rows(DbPool, SQL, ResultFun) ->
    case emysql:execute(DbPool, SQL) of
        #result_packet{
           rows = Result
          } ->
            {ok, ResultFun(Result)};
        Result when is_record(Result, error_packet) ->
            {error, Result}
    end.
 
%% in_db_hook 存入数据库之前的操作
record_to_vals(ModelMod, Record) 
  when is_tuple(Record)->
    tl(tuple_to_list(ModelMod:in_db_hook(Record)));
record_to_vals(ModelMod, RecordList) 
  when is_list(RecordList)->
    [tl(tuple_to_list(ModelMod:in_db_hook(Record))) || Record <- RecordList].

使用例子

-module(db_player).
 
-export([select_by_id/1]).
 
-export([in_db_hook/1, out_db_hook/1]).
 
-include("db_player.hrl").
-include("define_mysql.hrl").
 
-define(TABLE_CONF, #record_mysql_info{
                       db_pool = db_game,
                       table_name = player,
                       record_name = player,
                       mod = ?MODULE,
                       fields = record_info(fields, player)
                      }).
 
 
%% --------------------通用代码--------------------
-export([update/1, delete/1, insert/1, r_list_insert_withnot_id/1, r_list_insert_with_id/1]).
 
update(Record)->
    db_mysql_base:r_update(?TABLE_CONF, Record).
 
delete(RecordOrList) ->
    db_mysql_base:r_delete(?TABLE_CONF, RecordOrList).
 
insert(Record) ->
    db_mysql_base:r_insert(?TABLE_CONF, Record).
 
r_list_insert_withnot_id(List) ->
    db_mysql_base:r_list_insert_withnot_id(?TABLE_CONF, List).
 
r_list_insert_with_id(List) ->
    db_mysql_base:r_list_insert_with_id(?TABLE_CONF, List).
 
in_db_hook(Record) ->
    Record.
 
out_db_hook(Record) ->
    Record
 
%% ----------------------------------------
 
select_by_id(Id) ->
    db_mysql_base:select(?TABLE_CONF, {id, '=', Id}).

有了db_player,我们就传#player{}就可以了,再也不用关心数据库字段啥,拼SQL啥,哪个表什么的等等,配好定义,一劳永逸。

作者:Roowe❤
33.3% Linux 33.3% Mathematics 33.3% Inspiration
原文地址:Erlang简易的MySQL ORM实现, 感谢原作者分享。

发表评论