lua-resty-model
openresty super orm finally comes out
$ opm get xiangnanscu/lua-resty-model
lua-resty-model
openresty super orm finally comes out!
Requirements
Synopsis
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[])
insert one user
usr:insert{permission=1, username ='u1'}:exec()
INSERT INTO
usr (username, permission)
VALUES
('u1', 1)
{
affected_rows: 1,
}
ok 1 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user
insert one user returning one column
usr:insert{permission=1, username ='u2'}:returning('permission'):exec()
INSERT INTO
usr (username, permission)
VALUES
('u2', 1)
RETURNING
usr.permission
[
{
permission: 1,
},
]
ok 2 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user returning one column
insert one user with default permission
usr:insert{username ='u3'}:returning('permission'):exec()
INSERT INTO
usr (username, permission)
VALUES
('u3', 0)
RETURNING
usr.permission
[
{
permission: 0,
},
]
ok 3 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user with default permission
insert one user returning two columns
usr:insert{permission=1, username ='u4'}:returning('permission','username'):exec()
INSERT INTO
usr (username, permission)
VALUES
('u4', 1)
RETURNING
usr.permission,
usr.username
[
{
permission: 1,
username : "u4",
},
]
ok 4 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user returning two columns
insert one user returning one column in compact form
usr:insert{permission=1, username ='u5'}:returning('username'):compact():exec()
INSERT INTO
usr (username, permission)
VALUES
('u5', 1)
RETURNING
usr.username
[
[
"u5",
],
]
ok 5 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user returning one column in compact form
insert two users
usr:insert{{permission=1, username ='u6'}, {permission=1, username ='u7'}}:exec()
INSERT INTO
usr (username, permission)
VALUES
('u6', 1),
('u7', 1)
{
affected_rows: 2,
}
ok 6 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users
insert two users returning one column
usr:insert{{permission=1, username ='u8'}, {permission=1, username ='u9'}}:returning('username'):exec()
INSERT INTO
usr (username, permission)
VALUES
('u8', 1),
('u9', 1)
RETURNING
usr.username
[
{
username: "u8",
},
{
username: "u9",
},
]
ok 7 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users returning one column
insert two users returning two columns
usr:insert{{permission=2, username ='u10'}, {permission=3, username ='u11'}}:returning('username','permission'):exec()
INSERT INTO
usr (username, permission)
VALUES
('u10', 2),
('u11', 3)
RETURNING
usr.username,
usr.permission
[
{
permission: 2,
username : "u10",
},
{
permission: 3,
username : "u11",
},
]
ok 8 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users returning two columns
insert two users returning one column in flatten form
usr:insert{{permission=1, username ='u12'}, {permission=1, username ='u13'}}:returning('username'):flat()
INSERT INTO
usr (username, permission)
VALUES
('u12', 1),
('u13', 1)
RETURNING
usr.username
[
"u12",
"u13",
]
ok 9 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users returning one column in flatten form
insert two users returning two columns in flatten form
usr:insert{{permission=1, username ='u14'}, {permission=2, username ='u15'}}:returning('username','permission'):flat()
INSERT INTO
usr (username, permission)
VALUES
('u14', 1),
('u15', 2)
RETURNING
usr.username,
usr.permission
[
"u14",
1,
"u15",
2,
]
ok 10 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users returning two columns in flatten form
insert one user with specific columns (permission being ignored)
usr:insert({permission=4, username ='u16'}, {'username'}):returning('username','permission'):exec()
INSERT INTO
usr (username)
VALUES
('u16')
RETURNING
usr.username,
usr.permission
[
{
permission: 0,
username : "u16",
},
]
ok 11 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user with specific columns (permission being ignored)
insert one user with specific columns
usr:insert({permission=4, username ='u17'}, {'username', 'permission'}):returning('username','permission'):exec()
INSERT INTO
usr (username, permission)
VALUES
('u17', 4)
RETURNING
usr.username,
usr.permission
[
{
permission: 4,
username : "u17",
},
]
ok 12 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user with specific columns
insert two users with specific columns (permission being ignored)
usr:insert({{permission=4, username ='u18'},{permission=5, username ='u19'}}, {'username'}):returning('username','permission'):exec()
INSERT INTO
usr (username)
VALUES
('u18'),
('u19')
RETURNING
usr.username,
usr.permission
[
{
permission: 0,
username : "u18",
},
{
permission: 0,
username : "u19",
},
]
ok 13 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users with specific columns (permission being ignored)
insert two users with specific columns
usr:insert({{permission=4, username ='u20'},{permission=5, username ='u21'}}, {'username', 'permission'}):returning('username','permission'):exec()
INSERT INTO
usr (username, permission)
VALUES
('u20', 4),
('u21', 5)
RETURNING
usr.username,
usr.permission
[
{
permission: 4,
username : "u20",
},
{
permission: 5,
username : "u21",
},
]
ok 14 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users with specific columns
insert users with default permission
usr:insert{{username ='f1'},{username ='f2'}}:flat('permission')
INSERT INTO
usr (username, permission)
VALUES
('f1', 0),
('f2', 0)
RETURNING
usr.permission
[
0,
0,
]
ok 15 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert users with default permission
insert one user validate required failed
ok 16 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user validate required failed
insert one user validate maxlength failed
ok 17 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user validate maxlength failed
insert one user validate max failed
ok 18 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user validate max failed
insert two users validate max failed
ok 19 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users validate max failed
Xodel:create
create
dept:returning('*'):create{name ='d1'}
INSERT INTO
dept (name)
VALUES
('d1')
RETURNING
*
[
{
id : 1,
name: "d1",
},
]
ok 20 - Xodel:create create
create multiple rows
dept:returning('name'):create{{name ='d2'}, {name ='d3'}}
INSERT INTO
dept (name)
VALUES
('d2'),
('d3')
RETURNING
dept.name
[
{
name: "d2",
},
{
name: "d3",
},
]
ok 21 - Xodel:create create multiple rows
Xodel:count(cond?, op?, dval?)
specify condition
usr:count{id__lt=3}
SELECT
count(*)
FROM
usr
WHERE
usr.id < 3
2
ok 22 - Xodel:count(cond?, op?, dval?) specify condition
test with 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
3
ok 23 - Xodel:count(cond?, op?, dval?) test with Xodel:all
XodelInstance:save(names?:string[], key?:string)
save basic
profile{usr_id=1, dept_name='d1', age=20}:save()
INSERT INTO
profile (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) save basic
save with specific names
profile{usr_id=2, dept_name='d2', salary=500, sex='m', age=50}:save{'usr_id','dept_name'}
INSERT INTO
profile (dept_name, usr_id)
VALUES
('d2', 2)
RETURNING
*
{
age : 0,
dept_name: "d2",
id : 2,
salary : 1000,
sex : "f",
usr_id : 2,
}
ok 25 - XodelInstance:save(names?:string[], key?:string) save with specific names
save with primary key specified to update
profile{id=1, age=33}:save()
UPDATE profile
SET
age = 33
WHERE
profile.id = 1
RETURNING
id
{
age: 33,
id : 1,
}
ok 26 - XodelInstance:save(names?:string[], key?:string) save with primary key specified to update
save with primary key ignored and force create
profile{id=5, age=55, usr_id=3, dept_name='d3',}:save_create()
INSERT INTO
profile (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) save with primary key ignored and force create
save with wrong name
profile{usr_id=1, dept_name='d1', age=20}:save{'xxxx'}
ok 28 - XodelInstance:save(names?:string[], key?:string) save with wrong name
Xodel:merge(rows:table[], key?:string|string[], columns?:string[])
merge multiple rows returning inserted rows with all columns
usr:merge({{permission=4, username ='u1'},{permission=2, username ='u22'}}, 'username'):returning('*'):exec()
WITH
V (permission, username) AS (
VALUES
(4::integer, 'u1'::varchar),
(2, 'u22')
),
U AS (
UPDATE usr AS T
SET
permission = V.permission
FROM
V
WHERE
V.username = T.username
RETURNING
V.permission,
V.username
)
INSERT INTO
usr (permission, username)
SELECT
V.permission,
V.username
FROM
V
LEFT JOIN U AS T ON (V.username = T.username)
WHERE
T.username IS NULL
RETURNING
*
[
{
id : 24,
permission: 2,
username : "u22",
},
]
ok 29 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows returning inserted rows with all columns
merge multiple rows returning inserted rows with specific columns
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 T ON (V.username = T.username)
)
INSERT INTO
usr (username)
SELECT
V.username
FROM
V
LEFT JOIN U AS T ON (V.username = T.username)
WHERE
T.username IS NULL
RETURNING
usr.username
[
{
username: "u23",
},
{
username: "u24",
},
]
ok 30 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows returning inserted rows with specific columns
merge multiple rows returning inserted rows with specific columns in compact form
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 T ON (V.username = T.username)
)
INSERT INTO
usr (username)
SELECT
V.username
FROM
V
LEFT JOIN U AS T ON (V.username = T.username)
WHERE
T.username IS NULL
RETURNING
usr.username
[
"u25",
"u26",
]
ok 31 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows returning inserted rows with specific columns in compact form
merge multiple rows returning inserted rows with array key
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 AS T
SET
rank = V.rank
FROM
V
WHERE
V.usr_id = T.usr_id
AND V.year = T.year
RETURNING
V.year,
V.usr_id,
V.rank
)
INSERT INTO
evaluate (year, usr_id, rank)
SELECT
V.year,
V.usr_id,
V.rank
FROM
V
LEFT JOIN U AS T ON (
V.usr_id = T.usr_id
AND V.year = T.year
)
WHERE
T.usr_id IS NULL
RETURNING
evaluate.rank
[
"A",
"B",
]
ok 32 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows returning inserted rows with array key
merge multiple rows returning inserted rows with array key and specific columns
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 T ON (
V.usr_id = T.usr_id
AND V.year = T.year
)
)
INSERT INTO
evaluate (usr_id, year)
SELECT
V.usr_id,
V.year
FROM
V
LEFT JOIN U AS T ON (
V.usr_id = T.usr_id
AND V.year = T.year
)
WHERE
T.usr_id IS NULL
RETURNING
evaluate.rank
[
"C",
"C",
]
ok 33 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows returning inserted rows with array key and specific columns
merge multiple rows validate max failed
ok 34 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows validate max failed
merge multiple rows missing default unique value failed
ok 35 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows missing default unique value failed
Xodel:upsert(rows:table[], key?:string|string[], columns?:string[])
upsert multiple rows returning inserted rows with all columns
usr:upsert({{permission=4, username ='u1'},{permission=2, username ='u27'}}, 'username'):returning('username'):exec()
INSERT INTO
usr (permission, username)
VALUES
(4, 'u1'),
(2, 'u27')
ON CONFLICT (username) DO
UPDATE
SET
permission = EXCLUDED.permission
RETURNING
usr.username
[
{
username: "u1",
},
{
username: "u27",
},
]
ok 36 - Xodel:upsert(rows:table[], key?:string|string[], columns?:string[]) upsert multiple rows returning inserted rows with all columns
upsert multiple rows returning inserted rows with specific columns in compact form
usr:upsert({{username ='u28'},{username ='u29'}}, 'username'):returning('username'):flat()
INSERT INTO
usr (username)
VALUES
('u28'),
('u29')
ON CONFLICT (username) DO NOTHING
RETURNING
usr.username
[
"u28",
"u29",
]
ok 37 - Xodel:upsert(rows:table[], key?:string|string[], columns?:string[]) upsert multiple rows returning inserted rows with specific columns in compact form
upsert multiple rows returning inserted rows with array key
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 (year, usr_id, rank)
VALUES
(2021, 1, 'A'),
(2022, 1, 'B')
ON CONFLICT (usr_id, year) DO
UPDATE
SET
rank = EXCLUDED.rank
RETURNING
evaluate.rank
[
"A",
"B",
]
ok 38 - Xodel:upsert(rows:table[], key?:string|string[], columns?:string[]) upsert multiple rows returning inserted rows with array key
upsert multiple rows validate max failed
ok 39 - Xodel:upsert(rows:table[], key?:string|string[], columns?:string[]) upsert multiple rows validate max failed
Xodel.update
update one user
usr:update{permission=2}:where{id=1}:exec()
UPDATE usr
SET
permission = 2
WHERE
usr.id = 1
{
affected_rows: 1,
}
ok 40 - Xodel.update update one user
update one user returning one column
usr:update{permission=3}:where{id=1}:returning('permission'):exec()
UPDATE usr
SET
permission = 3
WHERE
usr.id = 1
RETURNING
usr.permission
[
{
permission: 3,
},
]
ok 41 - Xodel.update update one user returning one column
update users returning two columns in table form
usr:update{permission=3}:where{id__lt=3}:returning{'permission','id'}:exec()
UPDATE usr
SET
permission = 3
WHERE
usr.id < 3
RETURNING
usr.permission,
usr.id
[
{
id : 1,
permission: 3,
},
{
id : 2,
permission: 3,
},
]
ok 42 - Xodel.update update users returning two columns in table form
update users returning one column in flatten form
usr:update{permission=3}:where{id__lt=3}:returning{'username'}:flat()
UPDATE usr
SET
permission = 3
WHERE
usr.id < 3
RETURNING
usr.username
[
"u1",
"u2",
]
ok 43 - Xodel.update update users returning one column in flatten form
update by where with foreignkey
profile:update{age=11}:where{usr_id__username__contains='1'}:returning('age'):exec()
UPDATE profile
SET
age = 11
FROM
usr T1
WHERE
(profile.usr_id = T1.id)
AND (T1.username LIKE '%1%')
RETURNING
profile.age
[
{
age: 11,
},
]
ok 44 - Xodel.update update by where with foreignkey
update returning foreignkey
profile:update { sex = 'm' }:where { id = 1 }:returning('id', 'usr_id__username'):exec()
UPDATE profile
SET
sex = 'm'
FROM
usr T1
WHERE
(profile.id = 1)
AND (profile.usr_id = T1.id)
RETURNING
profile.id,
T1.username AS usr_id__username
[
{
id : 1,
usr_id__username: "u1",
},
]
ok 45 - Xodel.update update returning foreignkey
Xodel:updates(rows:table[], key?:string|string[], columns?:string[])
updates partial
usr:updates({{permission=2, username ='u1'},{permission=3, username ='??'}}, 'username'):returning("*"):exec()
WITH
V (permission, username) AS (
VALUES
(2::integer, 'u1'::varchar),
(3, '??')
)
UPDATE usr
SET
permission = V.permission
FROM
V
WHERE
V.username = usr.username
RETURNING
*
[
{
id : 1,
permission: 2,
username : "u1",
},
]
ok 46 - Xodel:updates(rows:table[], key?:string|string[], columns?:string[]) updates partial
updates all
usr:updates({{permission=1, username ='u1'},{permission=3, username ='u3'}}, 'username'):returning("*"):exec()
WITH
V (permission, username) AS (
VALUES
(1::integer, 'u1'::varchar),
(3, 'u3')
)
UPDATE usr
SET
permission = V.permission
FROM
V
WHERE
V.username = usr.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[]) updates all
Xodel.where
where basic
usr:select('username','id'):where{id=1}:exec()
SELECT
usr.username,
usr.id
FROM
usr
WHERE
usr.id = 1
[
{
id : 1,
username: "u1",
},
]
ok 48 - Xodel.where where basic
where or
usr:select('id'):where{id=1}:or_where{id=2}:order('id'):flat()
SELECT
usr.id
FROM
usr
WHERE
usr.id = 1
OR usr.id = 2
ORDER BY
usr.id ASC
[
1,
2,
]
ok 49 - Xodel.where where or
and where or
usr:select('id'):where{id=1}:where_or{id=2, username='u3'}:order('id'):flat()
SELECT
usr.id
FROM
usr
WHERE
(usr.id = 1)
AND (
usr.id = 2
OR usr.username = 'u3'
)
ORDER BY
usr.id ASC
[
]
ok 50 - Xodel.where and where or
or where and
usr:select('id'):where{id=1}:or_where{id=2, username='u2'}:order('id'):flat()
SELECT
usr.id
FROM
usr
WHERE
usr.id = 1
OR usr.id = 2
AND usr.username = 'u2'
ORDER BY
usr.id ASC
[
1,
2,
]
ok 51 - Xodel.where or where and
or where or
usr:select('id'):where{id=1}:or_where_or{id=2, username='u3'}:order('id'):flat()
SELECT
usr.id
FROM
usr
WHERE
usr.id = 1
OR usr.id = 2
OR usr.username = 'u3'
ORDER BY
usr.id ASC
[
1,
2,
3,
]
ok 52 - Xodel.where or where or
where condition by 2 args
usr:select('id'):where('id', 3):exec()
SELECT
usr.id
FROM
usr
WHERE
usr.id = 3
[
{
id: 3,
},
]
ok 53 - Xodel.where where condition by 2 args
where condition by 3 args
usr:select('id'):where('id', '<', 3):flat()
SELECT
usr.id
FROM
usr
WHERE
usr.id < 3
[
1,
2,
]
ok 54 - Xodel.where where condition by 3 args
where exists
usr:where_exists(usr:where{id=1})
SELECT
*
FROM
usr
WHERE
EXISTS (
SELECT
*
FROM
usr
WHERE
usr.id = 1
)
ok 55 - Xodel.where where exists
where null
usr:where_null("username")
SELECT
*
FROM
usr
WHERE
usr.username IS NULL
ok 56 - Xodel.where where null
where in
usr:where_in("id", {1,2,3})
SELECT
*
FROM
usr
WHERE
(usr.id) IN (1, 2, 3)
ok 57 - Xodel.where where in
where between
usr:where_between("id", 2, 4)
SELECT
*
FROM
usr
WHERE
usr.id BETWEEN 2 AND 4
ok 58 - Xodel.where where between
where not
usr:where_not("username", "foo")
SELECT
*
FROM
usr
WHERE
NOT (usr.username = 'foo')
ok 59 - Xodel.where where not
where not null
usr:where_not_null("username")
SELECT
*
FROM
usr
WHERE
usr.username IS NOT NULL
ok 60 - Xodel.where where not null
where not in
usr:where_not_in("id", {1,2,3})
SELECT
*
FROM
usr
WHERE
(usr.id) NOT IN (1, 2, 3)
ok 61 - Xodel.where where not in
where not between
usr:where_not_between("id", 2, 4)
SELECT
*
FROM
usr
WHERE
usr.id NOT BETWEEN 2 AND 4
ok 62 - Xodel.where where not between
where not exists
usr:where_not_exists(usr:where{id=1})
SELECT
*
FROM
usr
WHERE
NOT EXISTS (
SELECT
*
FROM
usr
WHERE
usr.id = 1
)
ok 63 - Xodel.where where not exists
where by arithmetic operator: __ne
usr:where{id__ne=2}:select('id')
SELECT
usr.id
FROM
usr
WHERE
usr.id <> 2
ok 64 - Xodel.where where by arithmetic operator: __ne
where by arithmetic operator: __lt
usr:where{id__lt=2}:select('id')
SELECT
usr.id
FROM
usr
WHERE
usr.id < 2
ok 65 - Xodel.where where by arithmetic operator: __lt
where by arithmetic operator: __lte
usr:where{id__lte=2}:select('id')
SELECT
usr.id
FROM
usr
WHERE
usr.id <= 2
ok 66 - Xodel.where where by arithmetic operator: __lte
where by arithmetic operator: __gt
usr:where{id__gt=2}:select('id')
SELECT
usr.id
FROM
usr
WHERE
usr.id > 2
ok 67 - Xodel.where where by arithmetic operator: __gt
where by arithmetic operator: __gte
usr:where{id__gte=2}:select('id')
SELECT
usr.id
FROM
usr
WHERE
usr.id >= 2
ok 68 - Xodel.where where by arithmetic operator: __gte
where by arithmetic operator: __eq
usr:where{id__eq=2}:select('id')
SELECT
usr.id
FROM
usr
WHERE
usr.id = 2
ok 69 - Xodel.where where by arithmetic operator: __eq
where in
usr:where{username__in={'u1','u2'}}
SELECT
*
FROM
usr
WHERE
usr.username IN ('u1', 'u2')
ok 70 - Xodel.where where in
where contains
usr:where{username__contains='u'}
SELECT
*
FROM
usr
WHERE
usr.username LIKE '%u%'
ok 71 - Xodel.where where contains
where startswith
usr:where{username__startswith='u'}
SELECT
*
FROM
usr
WHERE
usr.username LIKE 'u%'
ok 72 - Xodel.where where startswith
where endswith
usr:where{username__endswith='u'}
SELECT
*
FROM
usr
WHERE
usr.username LIKE '%u'
ok 73 - Xodel.where where endswith
where null true
usr:where{username__null=true}
SELECT
*
FROM
usr
WHERE
usr.username IS NULL
ok 74 - Xodel.where where null true
where null false
usr:where{username__null=false}
SELECT
*
FROM
usr
WHERE
usr.username IS NOT NULL
ok 75 - Xodel.where where null false
where notin
usr:where{username__notin={'u1','u2'}}
SELECT
*
FROM
usr
WHERE
usr.username NOT IN ('u1', 'u2')
ok 76 - Xodel.where where notin
where foreignkey eq
profile:where{usr_id__username__eq='u1'}
SELECT
*
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
T1.username = 'u1'
ok 77 - Xodel.where where foreignkey eq
where foreignkey in
profile:where{usr_id__username__in={'u1','u2'}}
SELECT
*
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
T1.username IN ('u1', 'u2')
ok 78 - Xodel.where where foreignkey in
where foreignkey contains
profile:where{usr_id__username__contains='u'}
SELECT
*
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
T1.username LIKE '%u%'
ok 79 - Xodel.where where foreignkey contains
where foreignkey startswith
profile:where{usr_id__username__startswith='u'}
SELECT
*
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
T1.username LIKE 'u%'
ok 80 - Xodel.where where foreignkey startswith
where foreignkey endswith
profile:where{usr_id__username__endswith='u'}
SELECT
*
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
T1.username LIKE '%u'
ok 81 - Xodel.where where foreignkey endswith
where foreignkey null true
profile:where{usr_id__username__null=true}
SELECT
*
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
T1.username IS NULL
ok 82 - Xodel.where where foreignkey null true
where foreignkey null false
profile:where{usr_id__username__null=false}
SELECT
*
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
T1.username IS NOT NULL
ok 83 - Xodel.where where foreignkey null false
where foreignkey number operator ne
profile:where{usr_id__permission__ne=2}
SELECT
*
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
T1.permission <> 2
ok 84 - Xodel.where where foreignkey number operator ne
where foreignkey number operator lt
profile:where{usr_id__permission__lt=2}
SELECT
*
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
T1.permission < 2
ok 85 - Xodel.where where foreignkey number operator lt
where foreignkey number operator lte
profile:where{usr_id__permission__lte=2}
SELECT
*
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
T1.permission <= 2
ok 86 - Xodel.where where foreignkey number operator lte
where foreignkey number operator gt
profile:where{usr_id__permission__gt=2}
SELECT
*
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
T1.permission > 2
ok 87 - Xodel.where where foreignkey number operator gt
where foreignkey number operator gte
profile:where{usr_id__permission__gte=2}
SELECT
*
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
T1.permission >= 2
ok 88 - Xodel.where where foreignkey number operator gte
where foreignkey number operator eq
profile:where{usr_id__permission__eq=2}
SELECT
*
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
T1.permission = 2
ok 89 - Xodel.where where foreignkey number operator eq
Xodel.select
select fk column
profile:select('id', 'usr_id__username'):where { id = 1 }:exec()
SELECT
profile.id,
T1.username AS usr_id__username
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
profile.id = 1
[
{
id : 1,
usr_id__username: "u1",
},
]
ok 90 - Xodel.select select fk column
Xodel:get(cond?, op?, dval?)
basic
usr:get{id=3}
SELECT
*
FROM
usr
WHERE
usr.id = 3
LIMIT
2
{
id : 3,
permission: 3,
username : "u3",
}
ok 91 - Xodel:get(cond?, op?, dval?) basic
model load foreign row
SELECT
*
FROM
profile
WHERE
profile.id = 1
LIMIT
2
ok 92 - Xodel:get(cond?, op?, dval?) model load foreign row
fetch extra foreignkey field from database on demand
SELECT
*
FROM
usr
WHERE
usr.id = 1
LIMIT
2
ok 93 - Xodel:get(cond?, op?, dval?) fetch extra foreignkey field from database on demand
model load foreign row with specified columns
profile:load_fk('usr_id', 'username', 'permission'):get{id=1}
SELECT
profile.usr_id,
T1.username AS usr_id__username,
T1.permission AS usr_id__permission
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
profile.id = 1
LIMIT
2
{
usr_id: {
permission: 1,
username : "u1",
},
}
ok 94 - Xodel:get(cond?, op?, dval?) model load foreign row with specified columns
model load foreign row with all columns by *
profile:load_fk('usr_id', '*'):get{id=1}
SELECT
profile.usr_id,
profile.usr_id AS usr_id__id,
T1.username AS usr_id__username,
T1.permission AS usr_id__permission
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
profile.id = 1
LIMIT
2
{
usr_id: {
id : 1,
permission: 1,
username : "u1",
},
}
SELECT
*
FROM
usr
WHERE
usr.id = 1
LIMIT
2
ok 95 - Xodel:get(cond?, op?, dval?) model load foreign row with all columns by *
model load foreign row with specified columns two api are the same
profile:select("sex"):load_fk('usr_id', 'username', 'permission'):get{id=1}
SELECT
profile.sex,
profile.usr_id,
T1.username AS usr_id__username,
T1.permission AS usr_id__permission
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
profile.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
profile.sex,
profile.usr_id,
T1.username AS usr_id__username,
T1.permission AS usr_id__permission
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
profile.id = 1
LIMIT
2
{
sex : "m",
usr_id: {
permission: 1,
username : "u1",
},
}
ok 96 - Xodel:get(cond?, op?, dval?) model load foreign row with specified columns two api are the same
Xodel:get(cond?, op?, dval?)
usr:get{id__lt=3}
SELECT
*
FROM
usr
WHERE
usr.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[])
basic
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
WHERE
usr.username = 'goc'
)
RETURNING
id,
username
)
SELECT
id,
username,
TRUE AS __is_inserted__
FROM
new_records AS new_records
UNION ALL
(
SELECT
id,
username,
FALSE AS __is_inserted__
FROM
usr
WHERE
usr.username = 'goc'
)
{
id : 33,
username: "goc",
}
ok 98 - Xodel:get_or_create(params:table, defaults?:table, columns?:string[]) basic
model get_or_create with defaults
usr:get_or_create({username='goc2'}, {permission = 5})
WITH
new_records (id, permission, username) AS (
INSERT INTO
usr (permission, username)
SELECT
5,
'goc2'
WHERE
NOT EXISTS (
SELECT
1
FROM
usr
WHERE
usr.username = 'goc2'
)
RETURNING
id,
permission,
username
)
SELECT
id,
permission,
username,
TRUE AS __is_inserted__
FROM
new_records AS new_records
UNION ALL
(
SELECT
id,
permission,
username,
FALSE AS __is_inserted__
FROM
usr
WHERE
usr.username = 'goc2'
)
{
id : 34,
permission: 5,
username : "goc2",
}
ok 99 - Xodel:get_or_create(params:table, defaults?:table, columns?:string[]) model get_or_create with defaults
test chat model
INSERT INTO
message (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
) message.creator,
message.target,
message.content
FROM
message
WHERE
message.creator = 1
OR message.target = 1
ORDER BY
CASE
WHEN creator = 1 THEN target
ELSE creator
END,
message.id DESC
ok 100 - Xodel api: test chat model
where by exp
SELECT
message.creator,
message.target
FROM
message
WHERE
message.creator = 1
and message.target = 2
or message.creator = 2
and message.target = 1
SELECT
message.creator,
message.target
FROM
message
WHERE
NOT (
message.creator = 1
or message.target = 2
)
AND NOT (
message.creator = 2
or message.target = 1
)
ok 101 - Xodel api: where by exp
go crazy with where clause with recursive join
INSERT INTO
message (content, creator, target)
VALUES
('crazy', 1, 2)
RETURNING
*
SELECT
*
FROM
profile
WHERE
profile.id = 1
LIMIT
2
SELECT
*
FROM
usr
WHERE
usr.id = 1
LIMIT
2
SELECT
message.id,
T1.age AS creator__age,
T2.username AS creator__usr_id__username
FROM
message
INNER JOIN profile T1 ON (message.creator = T1.id)
INNER JOIN usr T2 ON (T1.usr_id = T2.id)
WHERE
T1.age = 11
AND message.id = 9
AND T2.username LIKE '%1%'
SELECT
message.id,
T1.age AS creator__age,
T2.username AS creator__usr_id__username
FROM
message
INNER JOIN profile T1 ON (message.creator = T1.id)
INNER JOIN usr T2 ON (T1.usr_id = T2.id)
WHERE
message.id = 9
ok 102 - Xodel api: go crazy with where clause with recursive join
etc
wrong fk name
models.message:where {creator__usr_id__views=0}:exec()
ok 103 - etc wrong fk name
wrong fk name3
models.message:select('creator__usr_id__views'):exec()
ok 104 - etc wrong fk name3
test shortcuts join
profile:join('dept_name'):get { id = 1 }
SELECT
*
FROM
profile
INNER JOIN dept dept ON (profile.dept_name = dept.name)
WHERE
profile.id = 1
LIMIT
2
{
age : 11,
dept_name: {
name: "d1",
},
id : 1,
name : "d1",
salary : 1000,
sex : "m",
usr_id : {
id: 1,
},
}
ok 105 - etc test shortcuts join
sql select_as
usr:select_as('id', 'value'):select_as('username', 'label'):where { id = 2 }:exec()
SELECT
usr.id AS value,
usr.username AS label
FROM
usr
WHERE
usr.id = 2
[
{
label: "u2",
value: 2,
},
]
ok 106 - etc sql select_as
sql select_as foreignkey
profile:select_as('usr_id__permission', 'uperm'):where { id = 2 }:exec()
SELECT
T1.permission AS uperm
FROM
profile
INNER JOIN usr T1 ON (profile.usr_id = T1.id)
WHERE
profile.id = 2
[
{
uperm: 3,
},
]
ok 107 - etc sql select_as foreignkey
sql injection
where key
ok 108 - sql injection where key
where value
SELECT
*
FROM
usr
WHERE
usr.id = '1 or 1=1'
ok 109 - sql injection where value
order
ok 110 - sql injection order
select
ok 111 - sql injection select
Xodel:delete(cond?, op?, dval?)
model class delete all
evaluate:delete{}:exec()
DELETE FROM evaluate
{
affected_rows: 4,
}
ok 112 - Xodel:delete(cond?, op?, dval?) model class delete all
model instance delete
DELETE FROM message
DELETE FROM message
SELECT
*
FROM
profile
WHERE
profile.id = 1
LIMIT
2
du:delete()
DELETE FROM profile
WHERE
profile.id = 1
RETURNING
profile.id
[
{
id: 1,
},
]
ok 113 - Xodel:delete(cond?, op?, dval?) model instance delete
model instance delete use non primary key
SELECT
*
FROM
usr
WHERE
usr.id = 1
LIMIT
2
du:delete('username')
DELETE FROM usr
WHERE
usr.username = 'u1'
RETURNING
usr.username
[
{
username: "u1",
},
]
ok 114 - Xodel:delete(cond?, op?, dval?) model instance delete use non primary key
create with foreign model returning all
SELECT
*
FROM
usr
WHERE
usr.id = 3
LIMIT
2
profile:returning("*"):create{usr_id=u, age=12}
INSERT INTO
profile (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?) create with foreign model returning all
insert from delete returning
SELECT
*
FROM
usr
WHERE
usr.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
WHERE
profile.id = 2
RETURNING
profile.id,
'usr',
'delete'
)
INSERT INTO
log(delete_id, model_name, action)
SELECT
delete_id,
model_name,
action
FROM
d
RETURNING
*
[
{
action : "delete",
delete_id : 2,
id : 1,
model_name: "usr",
},
]
Authors
Nan Xiang(@xiangnanscu)
License
mit
Dependencies
xiangnanscu/lua-resty-array, xiangnanscu/lua-resty-dotenv, xiangnanscu/lua-resty-fields, xiangnanscu/lua-resty-migrate, xiangnanscu/lua-resty-object, xiangnanscu/lua-resty-query, xiangnanscu/lua-resty-sql, xiangnanscu/lua-resty-utils
Versions
-
openresty super orm finally comes out 2024-01-25 14:08:09
-
openresty super orm finally comes out 2024-01-25 02:05:11
-
openresty super orm finally comes out 2024-01-25 01:48:51
-
a simple orm module 2018-05-03 15:17:04
-
a simple orm module 2018-03-24 03:57:49
-
a simple orm module 2018-03-18 11:32:11
-
a simple orm module 2018-03-18 11:06:11