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
110 lines (97 loc) · 4.73 KB
/
sqlite.spec.js
File metadata and controls
110 lines (97 loc) · 4.73 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
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
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")
})
it('should support glob operator', () => {
const sql = "SELECT device.id FROM device WHERE device.model GLOB '*XYZ';"
expect(getParsedSql(sql)).to.be.equal("SELECT `device`.`id` FROM `device` WHERE `device`.`model` GLOB '*XYZ'")
})
it('should support create table...as', () => {
const sql = `CREATE TABLE IF NOT EXISTS stg_devices AS SELECT * FROM devices WHERE 1 = 0;`
expect(getParsedSql(sql)).to.be.equal('CREATE TABLE IF NOT EXISTS `stg_devices` AS SELECT * FROM `devices` WHERE 1 = 0')
})
it('should support escape single quote', () => {
const sql = "SELECT name, 'doesn''t smoke' FROM people WHERE name = 'John';"
expect(getParsedSql(sql)).to.be.equal("SELECT `name`, 'doesn''t smoke' FROM `people` WHERE `name` = 'John'")
})
it('should support create with autoincrement, boolean type and definition could be empty', () => {
let sql = 'CREATE TABLE `foobar1` (`id` integer not null primary key autoincrement, `name` varchar(255), `batch` boolean, `migration_time` datetime)'
expect(getParsedSql(sql)).to.be.equal('CREATE TABLE `foobar1` (`id` INTEGER NOT NULL AUTOINCREMENT PRIMARY KEY, `name` VARCHAR(255), `batch` BOOLEAN, `migration_time` DATETIME)')
sql = 'CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample)'
expect(getParsedSql(sql)).to.be.equal('CREATE TABLE `sqlite_stat4` (`tbl`, `idx`, `neq`, `nlt`, `ndlt`, `sample`)')
})
it('should support with clause table name', () => {
const sql = 'with `e` as (select * from employees) SELECT name,`e`.`hired_on` FROM `e`'
expect(getParsedSql(sql)).to.be.equal('WITH `e` AS (SELECT * FROM `employees`) SELECT `name`, `e`.`hired_on` FROM `e`')
})
})