lua-resty-model

OpenResty 超级 ORM 终于发布了

账户

xiangnanscu

仓库

https://github.com/xiangnanscu/lua-resty-model

$ opm get xiangnanscu/lua-resty-model

lua-resty-model

OpenResty 超级 PostgreSQL ORM 终于发布了！

安装

    opm get xiangnanscu/lua-resty-model

概要

    local Model = require("resty.model")
    
    
    local Usr = Model:create_model {
      table_name = 'usr',
      fields = {
        { name = 'id',         type = 'integer', primary_key = true, serial = true },
        { name = 'username',   maxlength = 5,    required = true,    unique = true },
        { name = 'permission', type = 'integer', default = 0,        max = 5 },
      }
    }
    
    local Dept = Model:create_model {
      table_name = 'dept',
      { name = 'name', maxlength = 10, unique = true }
    }
    
    local Profile = Model:create_model {
      table_name = 'profile',
      { name = 'usr_id',    reference = Usr,  reference_column = 'id' },
      { name = 'dept_name', reference = Dept, reference_column = 'name' },
      { name = 'age',       required = true,  type = 'integer',         default = 0 },
      { name = 'sex',       default = 'f',    choices = { 'f', 'm' } },
      { name = 'salary',    type = 'float',   default = 1000 },
    
    }
    
    -- create with fields: id, utime and ctime
    local Message = Model {
      table_name = 'message',
      { name = 'creator', reference = Profile, },
      { name = "target",  reference = Profile, },
      { name = 'content', maxlength = 100,     compact = false },
    }
    
    local Evaluate = Model {
      table_name = 'evaluate',
      unique_together = { 'usr_id', 'year' },
      { name = 'usr_id', reference = Usr, },
      { name = "year",   type = 'year', },
      { name = 'rank',   maxlength = 1,   default = 'C' },
    }
    
    local Log = Model:create_model {
      table_name = 'log',
      fields = {
        { name = 'id',         type = 'integer', primary_key = true, serial = true },
        { name = 'delete_id',  type = 'integer', default = 0 },
        { name = 'model_name', type = 'string',  maxlength = 20 },
        { name = 'action',     maxlength = 10, }
      }
    }
    
    local Log2 = Model:create_model {
      table_name = 'log2',
      fields = {
        { name = 'buyer',  reference = Usr, },
        { name = 'seller', reference = Usr, },
      }
    }
    
    local Log3 = Model:create_model {
      table_name = 'log3',
      fields = {
        { name = 'start_log', reference = Log2, },
        { name = 'end_log',   reference = Log2, },
      }
    }
    
    local TableModel = Model:create_model {
      { name = 'ages',  type = 'array', field = { type = 'integer', max = 2 } },
      { name = 'users', type = 'table', model = Usr }
    }

Xodel:insert(rows:table|table[]|Sql, columns?:string[])

插入一个用户

     usr:insert{permission=1, username ='u1'}:exec()


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('u1', 1)


    {
      affected_rows: 1,
    }

ok 1 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入一个用户

插入一个用户并返回一列

     usr:insert{permission=1, username ='u2'}:returning('permission'):exec()


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('u2', 1)
    RETURNING
      T.permission


    [
      {
        permission: 1,
      },
    ];

ok 2 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入一个用户并返回一列

插入一个用户并设置默认权限

     usr:insert{username ='u3'}:returning('permission'):exec()


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('u3', 0)
    RETURNING
      T.permission


    [
      {
        permission: 0,
      },
    ];

ok 3 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入一个用户并设置默认权限

插入一个用户并返回两列

     usr:insert{permission=1, username ='u4'}:returning('permission','username'):exec()


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('u4', 1)
    RETURNING
      T.permission,
      T.username


    [
      {
        permission: 1,
        username: "u4",
      },
    ];

ok 4 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入一个用户并返回两列

插入一个用户并以紧凑形式返回一列

     usr:insert{permission=1, username ='u5'}:returning('username'):compact():exec()


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('u5', 1)
    RETURNING
      T.username


    [["u5"]];

ok 5 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入一个用户并以紧凑形式返回一列

插入两个用户

     usr:insert{{permission=1, username ='u6'}, {permission=1, username ='u7'}}:exec()


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('u6', 1),
      ('u7', 1)


    {
      affected_rows: 2,
    }

ok 6 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入两个用户

插入两个用户并返回一列

     usr:insert{{permission=1, username ='u8'}, {permission=1, username ='u9'}}:returning('username'):exec()


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('u8', 1),
      ('u9', 1)
    RETURNING
      T.username


    [
      {
        username: "u8",
      },
      {
        username: "u9",
      },
    ];

ok 7 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入两个用户并返回一列

插入两个用户并返回两列

     usr:insert{{permission=2, username ='u10'}, {permission=3, username ='u11'}}:returning('username','permission'):exec()


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('u10', 2),
      ('u11', 3)
    RETURNING
      T.username,
      T.permission


    [
      {
        permission: 2,
        username: "u10",
      },
      {
        permission: 3,
        username: "u11",
      },
    ];

ok 8 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入两个用户并返回两列

插入两个用户并以扁平化形式返回一列

     usr:insert{{permission=1, username ='u12'}, {permission=1, username ='u13'}}:returning('username'):flat()


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('u12', 1),
      ('u13', 1)
    RETURNING
      T.username


    ["u12", "u13"];

ok 9 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入两个用户并以扁平化形式返回一列

插入两个用户并以扁平化形式返回两列

     usr:insert{{permission=1, username ='u14'}, {permission=2, username ='u15'}}:returning('username','permission'):flat()


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('u14', 1),
      ('u15', 2)
    RETURNING
      T.username,
      T.permission


    ["u14", 1, "u15", 2];

ok 10 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入两个用户并以扁平化形式返回两列

插入一个用户并指定列（忽略权限）

     usr:insert({permission=4, username ='u16'}, {'username'}):returning('username','permission'):exec()


    INSERT INTO
      usr AS T (username)
    VALUES
      ('u16')
    RETURNING
      T.username,
      T.permission


    [
      {
        permission: 0,
        username: "u16",
      },
    ];

ok 11 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入一个用户并指定列（忽略权限）

插入一个用户并指定列

     usr:insert({permission=4, username ='u17'}, {'username', 'permission'}):returning('username','permission'):exec()


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('u17', 4)
    RETURNING
      T.username,
      T.permission


    [
      {
        permission: 4,
        username: "u17",
      },
    ];

ok 12 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入一个用户并指定列

插入两个用户并指定列（忽略权限）

     usr:insert({{permission=4, username ='u18'},{permission=5, username ='u19'}}, {'username'}):returning('username','permission'):exec()


    INSERT INTO
      usr AS T (username)
    VALUES
      ('u18'),
      ('u19')
    RETURNING
      T.username,
      T.permission


    [
      {
        permission: 0,
        username: "u18",
      },
      {
        permission: 0,
        username: "u19",
      },
    ];

ok 13 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入两个用户并指定列（忽略权限）

插入两个用户并指定列

     usr:insert({{permission=4, username ='u20'},{permission=5, username ='u21'}}, {'username', 'permission'}):returning('username','permission'):exec()


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('u20', 4),
      ('u21', 5)
    RETURNING
      T.username,
      T.permission


    [
      {
        permission: 4,
        username: "u20",
      },
      {
        permission: 5,
        username: "u21",
      },
    ];

ok 14 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入两个用户并指定列

插入用户并设置默认权限

     usr:insert{{username ='f1'},{username ='f2'}}:flat('permission')


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('f1', 0),
      ('f2', 0)
    RETURNING
      T.permission


    [0, 0];

ok 15 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入用户并设置默认权限

插入用户，必填验证失败

ok 16 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入用户，必填验证失败

插入用户，最大长度验证失败

ok 17 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入用户，最大长度验证失败

插入用户，最大值验证失败

ok 18 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入用户，最大值验证失败

插入两个用户，最大值验证失败

ok 19 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) 插入两个用户，最大值验证失败

Xodel:create

创建

    dept:returning('*'):create{name ='d1'}


    INSERT INTO
      dept AS T (name)
    VALUES
      ('d1')
    RETURNING
      *


    [
      {
        id: 1,
        name: "d1",
      },
    ];

ok 20 - Xodel:create 创建

创建多行

    dept:returning('name'):create{{name ='d2'}, {name ='d3'}}


    INSERT INTO
      dept AS T (name)
    VALUES
      ('d2'),
      ('d3')
    RETURNING
      T.name


    [
      {
        name: "d2",
      },
      {
        name: "d3",
      },
    ];

ok 21 - Xodel:create 创建多行

Xodel:count(cond?, op?, dval?)

指定条件

    usr:count{id__lt=3}


    SELECT
      count(*)
    FROM
      usr T
    WHERE
      T.id < 3


    2;

ok 22 - Xodel:count(cond?, op?, dval?) 指定条件

使用 Xodel:all 测试

    dept:all()


    SELECT
      *
    FROM
      dept


    [
      {
        id: 1,
        name: "d1",
      },
      {
        id: 2,
        name: "d2",
      },
      {
        id: 3,
        name: "d3",
      },
    ];


    dept:count()


    SELECT
      count(*)
    FROM
      dept T


    3;

ok 23 - Xodel:count(cond?, op?, dval?) 使用 Xodel:all 测试

XodelInstance:save(names?:string[], key?:string)

基本保存

    profile{usr_id=1, dept_name='d1', age=20}:save()


    INSERT INTO
      profile AS T (salary, usr_id, dept_name, age, sex)
    VALUES
      (1000, 1, 'd1', 20, 'f')
    RETURNING
      *


    {
      age      : 20,
      dept_name: "d1",
      id       : 1,
      salary   : 1000,
      sex      : "f",
      usr_id   : 1,
    }

ok 24 - XodelInstance:save(names?:string[], key?:string) 基本保存

使用指定名称保存

    profile{usr_id=2, dept_name='d2', salary=500, sex='m', age=50}:save{'usr_id','dept_name'}


    INSERT INTO
      profile AS T (usr_id, dept_name)
    VALUES
      (2, 'd2')
    RETURNING
      *


    {
      age      : 0,
      dept_name: "d2",
      id       : 2,
      salary   : 1000,
      sex      : "f",
      usr_id   : 2,
    }

ok 25 - XodelInstance:save(names?:string[], key?:string) 使用指定名称保存

指定主键进行更新

    profile{id=1, age=33}:save()


    UPDATE profile T
    SET
      age = 33
    WHERE
      (T.id = 1)
    RETURNING
      id


    {
      age: 33,
      id : 1,
    }

ok 26 - XodelInstance:save(names?:string[], key?:string) 指定主键进行更新

忽略主键并强制创建

    profile{id=5, age=55, usr_id=3, dept_name='d3',}:save_create()


    INSERT INTO
      profile AS T (salary, usr_id, dept_name, age, sex)
    VALUES
      (1000, 3, 'd3', 55, 'f')
    RETURNING
      *


    {
      age      : 55,
      dept_name: "d3",
      id       : 3,
      salary   : 1000,
      sex      : "f",
      usr_id   : 3,
    }

ok 27 - XodelInstance:save(names?:string[], key?:string) 忽略主键并强制创建

使用错误名称保存

    profile{usr_id=1, dept_name='d1', age=20}:save{'xxxx'}

ok 28 - XodelInstance:save(names?:string[], key?:string) 使用错误名称保存

Xodel:merge(rows:table[], key?:string|string[], columns?:string[])

合并多行并返回所有列的插入行

    usr:merge({{permission=4, username ='u1'},{permission=2, username ='u22'}}, 'username'):returning('*'):exec()


    WITH
      V (username, permission) AS (
        VALUES
          ('u1'::varchar, 4::integer),
          ('u22', 2)
      ),
      U AS (
        UPDATE usr W
        SET
          permission = V.permission
        FROM
          V
        WHERE
          (V.username = W.username)
        RETURNING
          V.username,
          V.permission
      )
    INSERT INTO
      usr AS T (username, permission)
    SELECT
      V.username,
      V.permission
    FROM
      V
      LEFT JOIN U AS W ON (V.username = W.username)
    WHERE
      W.username IS NULL
    RETURNING
      *


    [
      {
        id: 24,
        permission: 2,
        username: "u22",
      },
    ];

ok 29 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) 合并多行并返回所有列的插入行

合并多行并返回指定列的插入行

    usr:merge({{username ='u23'},{username ='u24'}}, 'username'):returning('username'):exec()


    WITH
      V (username) AS (
        VALUES
          ('u23'::varchar),
          ('u24')
      ),
      U AS (
        SELECT
          V.username
        FROM
          V
          INNER JOIN usr AS W ON (V.username = W.username)
      )
    INSERT INTO
      usr AS T (username)
    SELECT
      V.username
    FROM
      V
      LEFT JOIN U AS W ON (V.username = W.username)
    WHERE
      W.username IS NULL
    RETURNING
      T.username


    [
      {
        username: "u23",
      },
      {
        username: "u24",
      },
    ];

ok 30 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) 合并多行并返回指定列的插入行

合并多行并以紧凑形式返回指定列的插入行

    usr:merge({{username ='u25'},{username ='u26'}}, 'username'):returning('username'):flat()


    WITH
      V (username) AS (
        VALUES
          ('u25'::varchar),
          ('u26')
      ),
      U AS (
        SELECT
          V.username
        FROM
          V
          INNER JOIN usr AS W ON (V.username = W.username)
      )
    INSERT INTO
      usr AS T (username)
    SELECT
      V.username
    FROM
      V
      LEFT JOIN U AS W ON (V.username = W.username)
    WHERE
      W.username IS NULL
    RETURNING
      T.username


    ["u25", "u26"];

ok 31 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) 合并多行并以紧凑形式返回指定列的插入行

合并多行并返回使用数组键的插入行

    evaluate:merge({{usr_id=1, year=2021, rank='A'},{usr_id=1, year=2022, rank='B'}}, {'usr_id', 'year'}):returning('rank'):flat()


    WITH
      V (year, usr_id, rank) AS (
        VALUES
          (2021::integer, 1::integer, 'A'::varchar),
          (2022, 1, 'B')
      ),
      U AS (
        UPDATE evaluate W
        SET
          rank = V.rank
        FROM
          V
        WHERE
          (
            V.usr_id = W.usr_id
            AND V.year = W.year
          )
        RETURNING
          V.year,
          V.usr_id,
          V.rank
      )
    INSERT INTO
      evaluate AS T (year, usr_id, rank)
    SELECT
      V.year,
      V.usr_id,
      V.rank
    FROM
      V
      LEFT JOIN U AS W ON (
        V.usr_id = W.usr_id
        AND V.year = W.year
      )
    WHERE
      W.usr_id IS NULL
    RETURNING
      T.rank


    ["A", "B"];

ok 32 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) 合并多行并返回使用数组键的插入行

合并多行并返回使用数组键和指定列的插入行

    evaluate:merge({{usr_id=2, year=2021, rank='A'},{usr_id=2, year=2022, rank='B'}}, {'usr_id', 'year'}, {'usr_id', 'year'}):returning('rank'):flat()


    WITH
      V (usr_id, year) AS (
        VALUES
          (2::integer, 2021::integer),
          (2, 2022)
      ),
      U AS (
        SELECT
          V.usr_id,
          V.year
        FROM
          V
          INNER JOIN evaluate AS W ON (
            V.usr_id = W.usr_id
            AND V.year = W.year
          )
      )
    INSERT INTO
      evaluate AS T (usr_id, year)
    SELECT
      V.usr_id,
      V.year
    FROM
      V
      LEFT JOIN U AS W ON (
        V.usr_id = W.usr_id
        AND V.year = W.year
      )
    WHERE
      W.usr_id IS NULL
    RETURNING
      T.rank


    ["C", "C"];

ok 33 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) 合并多行并返回使用数组键和指定列的插入行

合并多行，最大值验证失败

ok 34 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) 合并多行，最大值验证失败

合并多行，缺少默认唯一值导致失败

ok 35 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) 合并多行，缺少默认唯一值导致失败

Xodel:upsert(rows:table[], key?:string|string[], columns?:string[])

更新或插入多行并返回所有列的插入行

    usr:upsert({{permission=4, username ='u1'},{permission=2, username ='u27'}}, 'username'):returning('username'):exec()


    INSERT INTO
      usr AS T (username, permission)
    VALUES
      ('u1', 4),
      ('u27', 2)
    ON CONFLICT (username) DO
    UPDATE
    SET
      permission = EXCLUDED.permission
    RETURNING
      T.username


    [
      {
        username: "u1",
      },
      {
        username: "u27",
      },
    ];

ok 36 - Xodel:upsert(rows:table[], key?:string|string[], columns?:string[]) 更新或插入多行并返回所有列的插入行

更新或插入多行并以紧凑形式返回指定列的插入行

    usr:upsert({{username ='u28'},{username ='u29'}}, 'username'):returning('username'):flat()


    INSERT INTO
      usr AS T (username)
    VALUES
      ('u28'),
      ('u29')
    ON CONFLICT (username) DO NOTHING
    RETURNING
      T.username


    ["u28", "u29"];

ok 37 - Xodel:upsert(rows:table[], key?:string|string[], columns?:string[]) 更新或插入多行并以紧凑形式返回指定列的插入行

更新或插入多行并返回使用数组键的插入行

    evaluate:upsert({{usr_id=1, year=2021, rank='A'},{usr_id=1, year=2022, rank='B'}}, {'usr_id', 'year'}):returning('rank'):flat()


    INSERT INTO
      evaluate AS T (year, usr_id, rank)
    VALUES
      (2021, 1, 'A'),
      (2022, 1, 'B')
    ON CONFLICT (usr_id, year) DO
    UPDATE
    SET
      rank = EXCLUDED.rank
    RETURNING
      T.rank


    ["A", "B"];

ok 38 - Xodel:upsert(rows:table[], key?:string|string[], columns?:string[]) 更新或插入多行并返回使用数组键的插入行

更新或插入多行，最大值验证失败

ok 39 - Xodel:upsert(rows:table[], key?:string|string[], columns?:string[]) 更新或插入多行，最大值验证失败

Xodel.update

更新一个用户

     usr:update{permission=2}:where{id=1}:exec()


    UPDATE usr T
    SET
      permission = 2
    WHERE
      (T.id = 1)


    {
      affected_rows: 1,
    }

ok 40 - Xodel.update 更新一个用户

更新一个用户并返回一列

     usr:update{permission=3}:where{id=1}:returning('permission'):exec()


    UPDATE usr T
    SET
      permission = 3
    WHERE
      (T.id = 1)
    RETURNING
      T.permission


    [
      {
        permission: 3,
      },
    ];

ok 41 - Xodel.update 更新一个用户并返回一列

更新用户并以表格形式返回两列

     usr:update{permission=3}:where{id__lt=3}:returning{'permission','id'}:exec()


    UPDATE usr T
    SET
      permission = 3
    WHERE
      (T.id < 3)
    RETURNING
      T.permission,
      T.id


    [
      {
        id: 1,
        permission: 3,
      },
      {
        id: 2,
        permission: 3,
      },
    ];

ok 42 - Xodel.update 更新用户并以表格形式返回两列

更新用户并以扁平化形式返回一列

     usr:update{permission=3}:where{id__lt=3}:returning{'username'}:flat()


    UPDATE usr T
    SET
      permission = 3
    WHERE
      (T.id < 3)
    RETURNING
      T.username


    ["u1", "u2"];

ok 43 - Xodel.update 更新用户并以扁平化形式返回一列

使用外键进行where条件更新

    profile:update{age=11}:where{usr_id__username__contains='1'}:returning('age'):exec()


    UPDATE profile T
    SET
      age = 11
    FROM
      usr T1
    WHERE
      (T1.username LIKE '%1%')
      AND (T.usr_id = T1.id)
    RETURNING
      T.age


    [
      {
        age: 11,
      },
    ];

ok 44 - Xodel.update 使用外键进行where条件更新

更新并返回外键

    profile:update { sex = 'm' }:where { id = 1 }:returning('id', 'usr_id__username'):exec()


    UPDATE profile T
    SET
      sex = 'm'
    FROM
      usr T1
    WHERE
      (T.id = 1)
      AND (T.usr_id = T1.id)
    RETURNING
      T.id,
      T1.username AS usr_id__username


    [
      {
        id: 1,
        usr_id__username: "u1",
      },
    ];

ok 45 - Xodel.update 更新并返回外键

Xodel:updates(rows:table[], key?:string|string[], columns?:string[])

部分更新

    usr:updates({{permission=2, username ='u1'},{permission=3, username ='??'}}, 'username'):returning("*"):exec()


    WITH
      V (username, permission) AS (
        VALUES
          ('u1'::varchar, 2::integer),
          ('??', 3)
      )
    UPDATE usr T
    SET
      permission = V.permission
    FROM
      V
    WHERE
      (V.username = T.username)
    RETURNING
      *


    [
      {
        id: 1,
        permission: 2,
        username: "u1",
      },
    ];

ok 46 - Xodel:updates(rows:table[], key?:string|string[], columns?:string[]) 部分更新

全部更新

    usr:updates({{permission=1, username ='u1'},{permission=3, username ='u3'}}, 'username'):returning("*"):exec()


    WITH
      V (username, permission) AS (
        VALUES
          ('u1'::varchar, 1::integer),
          ('u3', 3)
      )
    UPDATE usr T
    SET
      permission = V.permission
    FROM
      V
    WHERE
      (V.username = T.username)
    RETURNING
      *


    [
      {
        id: 1,
        permission: 1,
        username: "u1",
      },
      {
        id: 3,
        permission: 3,
        username: "u3",
      },
    ];

ok 47 - Xodel:updates(rows:table[], key?:string|string[], columns?:string[]) 全部更新

Xodel.where

基本where条件

     usr:select('username','id'):where{id=1}:exec()


    SELECT
      T.username,
      T.id
    FROM
      usr T
    WHERE
      T.id = 1


    [
      {
        id: 1,
        username: "u1",
      },
    ];

ok 48 - Xodel.where 基本where条件

或where条件

     usr:select('id'):where{id=1}:or_where{id=2}:order('id'):flat()


    SELECT
      T.id
    FROM
      usr T
    WHERE
      T.id = 1
      OR T.id = 2
    ORDER BY
      T.id ASC


    [1, 2];

ok 49 - Xodel.where 或where条件

且where或条件

     usr:select('id'):where{id=1}:where_or{id=2, username='u3'}:order('id'):flat()


    SELECT
      T.id
    FROM
      usr T
    WHERE
      (T.id = 1)
      AND (
        T.username = 'u3'
        OR T.id = 2
      )
    ORDER BY
      T.id ASC


    [];

ok 50 - Xodel.where 且where或条件

或where且条件

     usr:select('id'):where{id=1}:or_where{id=2, username='u2'}:order('id'):flat()


    SELECT
      T.id
    FROM
      usr T
    WHERE
      T.id = 1
      OR T.username = 'u2'
      AND T.id = 2
    ORDER BY
      T.id ASC


    [1, 2];

ok 51 - Xodel.where 或where且条件

或where或条件

     usr:select('id'):where{id=1}:or_where_or{id=2, username='u3'}:order('id'):flat()


    SELECT
      T.id
    FROM
      usr T
    WHERE
      T.id = 1
      OR T.username = 'u3'
      OR T.id = 2
    ORDER BY
      T.id ASC


    [1, 2, 3];

ok 52 - Xodel.where 或where或条件

使用2个参数的where条件

     usr:select('id'):where('id', 3):exec()


    SELECT
      T.id
    FROM
      usr T
    WHERE
      T.id = 3


    [
      {
        id: 3,
      },
    ];

ok 53 - Xodel.where 使用2个参数的where条件

使用3个参数的where条件

     usr:select('id'):where('id', '<',  3):flat()


    SELECT
      T.id
    FROM
      usr T
    WHERE
      T.id < 3


    [1, 2];

ok 54 - Xodel.where 使用3个参数的where条件

exists where条件

    usr:where_exists(usr:where{id=1})


    SELECT
      *
    FROM
      usr T
    WHERE
      EXISTS (
        SELECT
          *
        FROM
          usr T
        WHERE
          T.id = 1
      )

ok 55 - Xodel.where exists where条件

null where条件

    usr:where_null("username")


    SELECT
      *
    FROM
      usr T
    WHERE
      T.username IS NULL

ok 56 - Xodel.where null where条件

in where条件

    usr:where_in("id", {1,2,3})


    SELECT
      *
    FROM
      usr T
    WHERE
      (T.id) IN (1, 2, 3)

ok 57 - Xodel.where in where条件

between where条件

    usr:where_between("id", 2, 4)


    SELECT
      *
    FROM
      usr T
    WHERE
      T.id BETWEEN 2 AND 4

ok 58 - Xodel.where between where条件

not where条件

    usr:where_not("username", "foo")


    SELECT
      *
    FROM
      usr T
    WHERE
      NOT (T.username = 'foo')

ok 59 - Xodel.where not where条件

not null where条件

    usr:where_not_null("username")


    SELECT
      *
    FROM
      usr T
    WHERE
      T.username IS NOT NULL

ok 60 - Xodel.where not null where条件

not in where条件

    usr:where_not_in("id", {1,2,3})


    SELECT
      *
    FROM
      usr T
    WHERE
      (T.id) NOT IN (1, 2, 3)

ok 61 - Xodel.where not in where条件

not between where条件

    usr:where_not_between("id", 2, 4)


    SELECT
      *
    FROM
      usr T
    WHERE
      T.id NOT BETWEEN 2 AND 4

ok 62 - Xodel.where not between where条件

not exists where条件

    usr:where_not_exists(usr:where{id=1})


    SELECT
      *
    FROM
      usr T
    WHERE
      NOT EXISTS (
        SELECT
          *
        FROM
          usr T
        WHERE
          T.id = 1
      )

ok 63 - Xodel.where not exists where条件

使用算术运算符where条件：\_\_gte

    usr:where{id__gte=2}:select('id')


    SELECT
      T.id
    FROM
      usr T
    WHERE
      T.id >= 2

ok 64 - Xodel.where 使用算术运算符where条件：\_\_gte

使用算术运算符where条件：\_\_ne

    usr:where{id__ne=2}:select('id')


    SELECT
      T.id
    FROM
      usr T
    WHERE
      T.id <> 2

ok 65 - Xodel.where 使用算术运算符where条件：\_\_ne

使用算术运算符where条件：\_\_lt

    usr:where{id__lt=2}:select('id')


    SELECT
      T.id
    FROM
      usr T
    WHERE
      T.id < 2

ok 66 - Xodel.where 使用算术运算符where条件：\_\_lt

使用算术运算符where条件：\_\_lte

    usr:where{id__lte=2}:select('id')


    SELECT
      T.id
    FROM
      usr T
    WHERE
      T.id <= 2

ok 67 - Xodel.where 使用算术运算符where条件：\_\_lte

使用算术运算符where条件：\_\_gt

    usr:where{id__gt=2}:select('id')


    SELECT
      T.id
    FROM
      usr T
    WHERE
      T.id > 2

ok 68 - Xodel.where 使用算术运算符where条件：\_\_gt

使用算术运算符where条件：\_\_eq

    usr:where{id__eq=2}:select('id')


    SELECT
      T.id
    FROM
      usr T
    WHERE
      T.id = 2

ok 69 - Xodel.where 使用算术运算符where条件：\_\_eq

in where条件

    usr:where{username__in={'u1','u2'}}


    SELECT
      *
    FROM
      usr T
    WHERE
      T.username IN ('u1', 'u2')

ok 70 - Xodel.where in where条件

包含where条件

    usr:where{username__contains='u'}


    SELECT
      *
    FROM
      usr T
    WHERE
      T.username LIKE '%u%'

ok 71 - Xodel.where 包含where条件

以…开头where条件

    usr:where{username__startswith='u'}


    SELECT
      *
    FROM
      usr T
    WHERE
      T.username LIKE 'u%'

ok 72 - Xodel.where 以…开头where条件

以…结尾where条件

    usr:where{username__endswith='u'}


    SELECT
      *
    FROM
      usr T
    WHERE
      T.username LIKE '%u'

ok 73 - Xodel.where 以…结尾where条件

null true where条件

    usr:where{username__null=true}


    SELECT
      *
    FROM
      usr T
    WHERE
      T.username IS NULL

ok 74 - Xodel.where null true where条件

null false where条件

    usr:where{username__null=false}


    SELECT
      *
    FROM
      usr T
    WHERE
      T.username IS NOT NULL

ok 75 - Xodel.where null false where条件

notin where条件

    usr:where{username__notin={'u1','u2'}}


    SELECT
      *
    FROM
      usr T
    WHERE
      T.username NOT IN ('u1', 'u2')

ok 76 - Xodel.where notin where条件

外键等于where条件

    profile:where{usr_id__username__eq='u1'}


    SELECT
      *
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T1.username = 'u1'

ok 77 - Xodel.where 外键等于where条件

外键in where条件

    profile:where{usr_id__username__in={'u1','u2'}}


    SELECT
      *
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T1.username IN ('u1', 'u2')

ok 78 - Xodel.where 外键in where条件

外键包含where条件

    profile:where{usr_id__username__contains='u'}


    SELECT
      *
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T1.username LIKE '%u%'

ok 79 - Xodel.where 外键包含where条件

外键以…开头where条件

    profile:where{usr_id__username__startswith='u'}


    SELECT
      *
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T1.username LIKE 'u%'

ok 80 - Xodel.where 外键以…开头where条件

外键以…结尾where条件

    profile:where{usr_id__username__endswith='u'}


    SELECT
      *
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T1.username LIKE '%u'

ok 81 - Xodel.where 外键以…结尾where条件

外键null true where条件

    profile:where{usr_id__username__null=true}


    SELECT
      *
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T1.username IS NULL

ok 82 - Xodel.where 外键null true where条件

外键null false where条件

    profile:where{usr_id__username__null=false}


    SELECT
      *
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T1.username IS NOT NULL

ok 83 - Xodel.where 外键null false where条件

外键数字运算符gte where条件

    profile:where{usr_id__permission__gte=2}


    SELECT
      *
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T1.permission >= 2

ok 84 - Xodel.where 外键数字运算符gte where条件

外键数字运算符ne where条件

    profile:where{usr_id__permission__ne=2}


    SELECT
      *
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T1.permission <> 2

ok 85 - Xodel.where 外键数字运算符ne where条件

外键数字运算符lt where条件

    profile:where{usr_id__permission__lt=2}


    SELECT
      *
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T1.permission < 2

ok 86 - Xodel.where 外键数字运算符lt where条件

外键数字运算符lte where条件

    profile:where{usr_id__permission__lte=2}


    SELECT
      *
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T1.permission <= 2

ok 87 - Xodel.where 外键数字运算符lte where条件

外键数字运算符gt where条件

    profile:where{usr_id__permission__gt=2}


    SELECT
      *
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T1.permission > 2

ok 88 - Xodel.where 外键数字运算符gt where条件

外键数字运算符eq where条件

    profile:where{usr_id__permission__eq=2}


    SELECT
      *
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T1.permission = 2

ok 89 - Xodel.where 外键数字运算符eq where条件

Xodel.select

选择外键列

    profile:select('id', 'usr_id__username'):where { id = 1 }:exec()


    SELECT
      T.id,
      T1.username AS usr_id__username
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T.id = 1


    [
      {
        id: 1,
        usr_id__username: "u1",
      },
    ];

ok 90 - Xodel.select 选择外键列

Xodel:get(cond?, op?, dval?)

基本

    usr:get{id=3}


    SELECT
      *
    FROM
      usr T
    WHERE
      T.id = 3
    LIMIT
      2


    {
      id        : 3,
      permission: 3,
      username  : "u3",
    }

ok 91 - Xodel:get(cond?, op?, dval?) 基本

模型加载外键行

    SELECT
      *
    FROM
      profile T
    WHERE
      T.id = 1
    LIMIT
      2

ok 92 - Xodel:get(cond?, op?, dval?) 模型加载外键行

按需从数据库获取额外的外键字段

    SELECT
      *
    FROM
      usr T
    WHERE
      T.id = 1
    LIMIT
      2

ok 93 - Xodel:get(cond?, op?, dval?) 按需从数据库获取额外的外键字段

使用指定列加载模型外键行

    profile:load_fk('usr_id', 'username', 'permission'):get{id=1}


    SELECT
      T.usr_id,
      T1.username AS usr_id__username,
      T1.permission AS usr_id__permission
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T.id = 1
    LIMIT
      2


    {
      usr_id: {
        permission: 1,
        username  : "u1",
      },
    }

ok 94 - Xodel:get(cond?, op?, dval?) 使用指定列加载模型外键行

使用\*加载模型外键行的所有列

    profile:load_fk('usr_id', '*'):get{id=1}


    SELECT
      T.usr_id,
      T.usr_id AS usr_id__id,
      T1.username AS usr_id__username,
      T1.permission AS usr_id__permission
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T.id = 1
    LIMIT
      2


    {
      usr_id: {
        id        : 1,
        permission: 1,
        username  : "u1",
      },
    }


    SELECT
      *
    FROM
      usr T
    WHERE
      T.id = 1
    LIMIT
      2

ok 95 - Xodel:get(cond?, op?, dval?) 使用\*加载模型外键行的所有列

使用指定列加载模型外键行，两个API相同

    profile:select("sex"):load_fk('usr_id', 'username', 'permission'):get{id=1}


    SELECT
      T.sex,
      T.usr_id,
      T1.username AS usr_id__username,
      T1.permission AS usr_id__permission
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T.id = 1
    LIMIT
      2


    {
      sex   : "m",
      usr_id: {
        permission: 1,
        username  : "u1",
      },
    }


    profile:select("sex"):load_fk('usr_id', {'username', 'permission'}):get{id=1}


    SELECT
      T.sex,
      T.usr_id,
      T1.username AS usr_id__username,
      T1.permission AS usr_id__permission
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T.id = 1
    LIMIT
      2


    {
      sex   : "m",
      usr_id: {
        permission: 1,
        username  : "u1",
      },
    }

ok 96 - Xodel:get(cond?, op?, dval?) 使用指定列加载模型外键行，两个API相同

Xodel:get(cond?, op?, dval?)

    usr:get{id__lt=3}


    SELECT
      *
    FROM
      usr T
    WHERE
      T.id < 3
    LIMIT
      2

ok 97 - Xodel:get(cond?, op?, dval?) Xodel:get(cond?, op?, dval?)

Xodel:get_or_create(params:table, defaults?:table, columns?:string[])

基本

    usr:get_or_create{username='goc'}


    WITH
      new_records (id, username) AS (
        INSERT INTO
          usr (username)
        SELECT
          'goc'
        WHERE
          NOT EXISTS (
            SELECT
              1
            FROM
              usr T
            WHERE
              T.username = 'goc'
          )
        RETURNING
          id,
          username
      )
    SELECT
      id,
      username,
      TRUE AS __is_inserted__
    FROM
      new_records new_records
    UNION ALL
    (
      SELECT
        id,
        username,
        FALSE AS __is_inserted__
      FROM
        usr T
      WHERE
        T.username = 'goc'
    )


    {
      id      : 33,
      username: "goc",
    }

ok 98 - Xodel:get_or_create(params:table, defaults?:table, columns?:string[]) 基本测试

模型 get_or_create 带默认值

    usr:get_or_create({username='goc2'}, {permission = 5})


    WITH
      new_records (id, username, permission) AS (
        INSERT INTO
          usr (username, permission)
        SELECT
          'goc2',
          5
        WHERE
          NOT EXISTS (
            SELECT
              1
            FROM
              usr T
            WHERE
              T.username = 'goc2'
          )
        RETURNING
          id,
          username,
          permission
      )
    SELECT
      id,
      username,
      permission,
      TRUE AS __is_inserted__
    FROM
      new_records new_records
    UNION ALL
    (
      SELECT
        id,
        username,
        permission,
        FALSE AS __is_inserted__
      FROM
        usr T
      WHERE
        T.username = 'goc2'
    )


    {
      id        : 34,
      permission: 5,
      username  : "goc2",
    }

ok 99 - Xodel:get_or_create(params:table, defaults?:table, columns?:string[]) 模型 get_or_create 带默认值

测试聊天模型

    INSERT INTO
      message AS T (creator, target, content)
    VALUES
      (1, 2, 'c121'),
      (1, 2, 'c122'),
      (2, 1, 'c123'),
      (1, 3, 'c131'),
      (1, 3, 'c132'),
      (3, 1, 'c133'),
      (1, 3, 'c134'),
      (2, 3, 'c231')
    RETURNING
      *


    SELECT DISTINCT
      ON (
        CASE
          WHEN creator = 1 THEN target
          ELSE creator
        END
      ) T.creator,
      T.target,
      T.content
    FROM
      message T
    WHERE
      T.target = 1
      OR T.creator = 1
    ORDER BY
      CASE
        WHEN creator = 1 THEN target
        ELSE creator
      END,
      T.id DESC

ok 100 - Xodel api: 测试聊天模型

使用表达式进行筛选

    SELECT
      T.creator,
      T.target
    FROM
      message T
    WHERE
      T.target = 2
      and T.creator = 1
      or T.target = 1
      and T.creator = 2


    SELECT
      T.creator,
      T.target
    FROM
      message T
    WHERE
      NOT (
        T.target = 2
        or T.creator = 1
      )
      AND NOT (
        T.target = 1
        or T.creator = 2
      )

ok 101 - Xodel api: 使用表达式进行筛选

使用递归联接疯狂测试 where 子句

    INSERT INTO
      message AS T (target, content, creator)
    VALUES
      (2, 'crazy', 1)
    RETURNING
      *


    SELECT
      *
    FROM
      profile T
    WHERE
      T.id = 1
    LIMIT
      2


    SELECT
      *
    FROM
      usr T
    WHERE
      T.id = 1
    LIMIT
      2


    SELECT
      T.id,
      T1.age AS creator__age,
      T2.username AS creator__usr_id__username
    FROM
      message T
      INNER JOIN profile T1 ON (T.creator = T1.id)
      INNER JOIN usr T2 ON (T1.usr_id = T2.id)
    WHERE
      T1.age = 11
      AND T.id = 9
      AND T2.username LIKE '%1%'


    SELECT
      T.id,
      T1.age AS creator__age,
      T2.username AS creator__usr_id__username
    FROM
      message T
      INNER JOIN profile T1 ON (T.creator = T1.id)
      INNER JOIN usr T2 ON (T1.usr_id = T2.id)
    WHERE
      T.id = 9

ok 102 - Xodel api: 使用递归联接疯狂测试 where 子句

等等

错误的外键名称

    models.message:where {creator__usr_id__views=0}:exec()

ok 103 - 等等错误的外键名称

错误的外键名称3

    models.message:select('creator__usr_id__views'):exec()

ok 104 - 等等错误的外键名称3

测试快捷联接

    profile:join('dept_name'):get { id = 1 }


    SELECT
      *
    FROM
      profile T
      INNER JOIN dept T1 ON (T.dept_name = T1.name)
    WHERE
      T.id = 1
    LIMIT
      2


    {
      age      : 11,
      dept_name: {
        name: "d1",
      },
      id       : 1,
      name     : "d1",
      salary   : 1000,
      sex      : "m",
      usr_id   : {
        id: 1,
      },
    }

ok 105 - 等等测试快捷联接

SQL select_as

    usr:select_as('id', 'value'):select_as('username', 'label'):where { id = 2 }:exec()


    SELECT
      T.id AS value,
      T.username AS label
    FROM
      usr T
    WHERE
      T.id = 2


    [
      {
        label: "u2",
        value: 2,
      },
    ];

ok 106 - 等等 SQL select_as

SQL select_as 外键

    profile:select_as('usr_id__permission', 'uperm'):where { id = 2 }:exec()


    SELECT
      T1.permission AS uperm
    FROM
      profile T
      INNER JOIN usr T1 ON (T.usr_id = T1.id)
    WHERE
      T.id = 2


    [
      {
        uperm: 3,
      },
    ];

ok 107 - 等等 SQL select_as 外键

SQL 注入

where 条件的键

ok 108 - SQL 注入 where 条件的键

where 条件的值

    SELECT
      *
    FROM
      usr T
    WHERE
      T.id = '1 or 1=1'

ok 109 - SQL 注入 where 条件的值

排序

ok 110 - SQL 注入排序

选择

ok 111 - SQL 注入选择

Xodel:delete(cond?, op?, dval?)

模型类删除所有

    evaluate:delete{}:exec()


    DELETE FROM evaluate T


    {
      affected_rows: 4,
    }

ok 112 - Xodel:delete(cond?, op?, dval?) 模型类删除所有

模型实例删除

    DELETE FROM message T


    DELETE FROM message T


    SELECT
      *
    FROM
      profile T
    WHERE
      T.id = 1
    LIMIT
      2


    du:delete()


    DELETE FROM profile T
    WHERE
      (T.id = 1)
    RETURNING
      T.id


    [
      {
        id: 1,
      },
    ];

ok 113 - Xodel:delete(cond?, op?, dval?) 模型实例删除

模型实例使用非主键删除

    SELECT
      *
    FROM
      usr T
    WHERE
      T.id = 1
    LIMIT
      2


    du:delete('username')


    DELETE FROM usr T
    WHERE
      (T.username = 'u1')
    RETURNING
      T.username


    [
      {
        username: "u1",
      },
    ];

ok 114 - Xodel:delete(cond?, op?, dval?) 模型实例使用非主键删除

创建带外键模型并返回所有数据

    SELECT
      *
    FROM
      usr T
    WHERE
      T.id = 3
    LIMIT
      2


    profile:returning("*"):create{usr_id=u, age=12}


    INSERT INTO
      profile AS T (usr_id, dept_name, age, sex, salary)
    VALUES
      (3, DEFAULT, 12, 'f', 1000)
    RETURNING
      *


    [
      {
        age: 12,
        id: 4,
        salary: 1000,
        sex: "f",
        usr_id: 3,
      },
    ];

ok 115 - Xodel:delete(cond?, op?, dval?) 创建带外键模型并返回所有数据

插入来自删除操作返回的数据

    SELECT
      *
    FROM
      usr T
    WHERE
      T.id = 2
    LIMIT
      2


    log:returning("*"):create(
          profile:delete { id = 2 }:returning('id'):returning_literal("usr", "delete"),
          { 'delete_id', 'model_name', "action" })


    WITH
      D (delete_id, model_name, action) AS (
        DELETE FROM profile T
        WHERE
          (T.id = 2)
        RETURNING
          T.id,
          'usr',
          'delete'
      )
    INSERT INTO
      log AS T (delete_id, model_name, action)
    SELECT
      delete_id,
      model_name,
      action
    FROM
      D
    RETURNING
      *


    [
      {
        action: "delete",
        delete_id: 2,
        id: 1,
        model_name: "usr",
      },
    ];

作者

南向(@xiangnanscu)

许可证

MIT

依赖

xiangnanscu/lua-resty-array，xiangnanscu/lua-resty-dotenv，xiangnanscu/lua-resty-fields，xiangnanscu/lua-resty-object，xiangnanscu/lua-resty-query

版本

xiangnanscu/lua-resty-model 2.43 作者： xiangnanscu

OpenResty 超级 ORM 终于发布了 2024-07-22 22:38:34
xiangnanscu/lua-resty-model 2.42 作者： xiangnanscu

OpenResty 超级 ORM 终于发布了 2024-07-21 15:32:06
xiangnanscu/lua-resty-model 2.41 作者： xiangnanscu

OpenResty 超级 ORM 终于发布了 2024-07-20 14:47:46
xiangnanscu/lua-resty-model 2.4 作者： xiangnanscu

OpenResty 超级 ORM 终于发布了 2024-07-14 14:44:01
xiangnanscu/lua-resty-model 2.3 作者： xiangnanscu

OpenResty 超级 ORM 终于发布了 2024-05-04 03:51:32
xiangnanscu/lua-resty-model 2.2 作者： xiangnanscu

OpenResty 超级 ORM 终于发布了 2024-01-25 14:08:09
xiangnanscu/lua-resty-model 2.1 作者： xiangnanscu

OpenResty 超级 ORM 终于发布了 2024-01-25 02:05:11
xiangnanscu/lua-resty-model 2.0 作者： xiangnanscu

OpenResty 超级 ORM 终于发布了 2024-01-25 01:48:51
xiangnanscu/lua-resty-model 1.3 作者： xiangnanscu

一个简单的 ORM 模块 2018-05-03 15:17:04
xiangnanscu/lua-resty-model 1.2 作者： xiangnanscu

一个简单的 ORM 模块 2018-03-24 03:57:49
xiangnanscu/lua-resty-model 1.1 作者： xiangnanscu

一个简单的 ORM 模块 2018-03-18 11:32:11
xiangnanscu/lua-resty-model 1.0 作者： xiangnanscu

一个简单的 ORM 模块 2018-03-18 11:06:11