forked from taozhi8833998/node-sql-parser
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathsqlite.spec.js
More file actions
83 lines (75 loc) · 3.1 KB
/
sqlite.spec.js
File metadata and controls
83 lines (75 loc) · 3.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
const { expect } = require('chai');
const Parser = require('../src/parser').default
describe('sqlite', () => {
const parser = new Parser();
function getParsedSql(sql, opt = { database: 'sqlite' }) {
const ast = parser.astify(sql, opt);
return parser.sqlify(ast, opt);
}
it('should support analyze', () => {
const sql = 'analyze schemaName.tableName'
expect(getParsedSql(sql)).to.be.equal('ANALYZE `schemaName`.`tableName`')
})
it('should support attach', () => {
const sql = "attach database 'c:\sqlite\db\contacts.db' as contacts;"
expect(getParsedSql(sql)).to.be.equal("ATTACH DATABASE 'c:\sqlite\db\contacts.db' AS `contacts`")
})
it('should support json function in from clause', () => {
const sql = `SELECT json_extract(value, '$.id') AS author_id
FROM
post,
json_each(post.author, '$')
GROUP BY
author_id;`
expect(getParsedSql(sql)).to.be.equal("SELECT json_extract(`value`, '$.id') AS `author_id` FROM `post`, json_each(`post`.`author`, '$') GROUP BY `author_id`")
})
it('should support || in where clause', () => {
const sql = `SELECT *
FROM
pets
LEFT JOIN(
SELECT * FROM user
WHERE user.name = "pepe" || "rone"
) u ON pets.owner = u.id
GROUP BY pets.id;`
expect(getParsedSql(sql)).to.be.equal("SELECT * FROM `pets` LEFT JOIN (SELECT * FROM `user` WHERE `user`.`name` = 'pepe' || 'rone') AS `u` ON `pets`.`owner` = `u`.`id` GROUP BY `pets`.`id`")
})
it('should support or combine with )', () => {
let sql = `SELECT *
FROM
pets
LEFT JOIN(
SELECT * FROM user
WHERE user.code = UPPER("test")
OR user.name = "pepe") u ON pets.owner = u.id
GROUP BY pets.id;`
expect(getParsedSql(sql)).to.be.equal("SELECT * FROM `pets` LEFT JOIN (SELECT * FROM `user` WHERE `user`.`code` = UPPER('test') OR `user`.`name` = 'pepe') AS `u` ON `pets`.`owner` = `u`.`id` GROUP BY `pets`.`id`")
sql = `SELECT *
FROM
pets
LEFT JOIN(
SELECT * FROM user
WHERE user.name = "pepe" || "rone"
OR user.code = UPPER("test")
OR user.code = UPPER("more_test")
) u ON pets.owner = u.id
GROUP BY pets.id;`
expect(getParsedSql(sql)).to.be.equal("SELECT * FROM `pets` LEFT JOIN (SELECT * FROM `user` WHERE `user`.`name` = 'pepe' || 'rone' OR `user`.`code` = UPPER('test') OR `user`.`code` = UPPER('more_test')) AS `u` ON `pets`.`owner` = `u`.`id` GROUP BY `pets`.`id`")
})
it('should support json as function name', () => {
const sql = `SELECT
id,
json_object(
'hasGeometry',
CASE
WHEN json_extract(floor.rect, '$') IS '{"boundariesList":[]}' THEN json('false')
ELSE json('true')
END
) as "metadata"
FROM
floor
WHERE
floor.id = 1;`
expect(getParsedSql(sql)).to.be.equal("SELECT `id`, json_object('hasGeometry', CASE WHEN json_extract(`floor`.`rect`, '$') IS '{\"boundariesList\":[]}' THEN json('false') ELSE json('true') END) AS `metadata` FROM `floor` WHERE `floor`.`id` = 1")
})
})