Repository navigation
Expand file tree
/
Copy pathmain.test.js
More file actions
120 lines (98 loc) · 4.85 KB
/
Copy pathmain.test.js
File metadata and controls
120 lines (98 loc) · 4.85 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
111
112
113
114
115
116
117
118
119
'use strict';
const debug = true;
const parser = require('../dist/parser/sqlParser');
const testParser = function (sql) {
let ast = parser.parse(sql);
if (debug) {
console.log(
JSON.stringify(ast, null, 2)
);
console.log(
parser.stringify(ast)
);
console.log(
parser.stringify(parser.parse(parser.stringify(ast)))
);
}
return ast;
}
describe('simple sql support', function () {
it('test1', function () {
testParser('select distinct max_statement_time = 1.2 a ');
});
it('test2', function () {
testParser('select all 0x1f');
});
it('test3', function () {
testParser('select distinctrow "xx", a in (1,2)');
});
it('test4', function () {
testParser(`
select
tag_basic.gender as gender,
tag_basic.age as age,
trade_m.cate_id as cate_id,
trade_m.brand_id as bid,
sum(trade_m.amt_alipay) as amt_alipay
from
tags
where
trade_m.thedate in (201501, '201502',201503)
group by
tag_basic.gender, tag_basic.age, trade_m.cate_id, trade_m.brand_id
order by
sum(tag_trade_m.amt_alipay) desc
`);
});
it ('test5', function () {
testParser('select function(), function(1, "sd", 0x1F)');
});
it ('test6', function () {
testParser(`
select in中文 from tags
`);
});
it ('test7', function () {
testParser(`
SELECT distinct high_priority MAX_STATEMENT_TIME=1 STRAIGHT_JOIN SQL_SMALL_RESULT
SQL_BIG_RESULT SQL_BUFFER_RESULT SQL_CACHE SQL_CALC_FOUND_ROWS
fm_customer.lname AS 名字1,
fm_customer.fname AS 名字2,
fm_customer.address1 AS 地址1,fm_customer.phone1 AS 电话1,fm_customer.yearly_income AS 年收入,fm_customer.num_cars_owned AS 拥有车辆数 FROM fm_customer inner join fm_sales_link on fm_customer.customer_id = fm_sales_link.fm_customer.customer_id AND fm_sales_link.store_sales IS NOT NULL\nAND fm_sales_link.store_cost IS NOT NULL\nAND fm_sales_link.unit_sales IS NOT NULL inner join fm_store on fm_sales_link.fm_store.store_id = fm_store.store_id AND fm_store.store_name IS NOT NULL
`);
});
it ('test8', function () {
testParser(`
SELECT P1.PAYMENTNO, P1.AMOUNT,
(P1.AMOUNT * 100) / SUM(P2.AMOUNT)
FROM PENALTIES AS P1, PENALTIES AS P2
GROUP BY P1.PAYMENTNO, P1.AMOUNT
ORDER BY P1.PAYMENTNO
`);
});
it ('test9', function () {
testParser(`
SELECT PLAYERS.PLAYERNO, NAME,
(SELECT COUNT(*)
FROM PENALTIES
WHERE PLAYERS.PLAYERNO =
PENALTIES.PLAYERNO) AS NUMBER_OF_PENALTIES,
(SELECT COUNT(*)
FROM TEAMS
WHERE PLAYERS.PLAYERNO =
TEAMS.PLAYERNO) AS NUMBER_OF_TEAMS
FROM PLAYERS
`);
});
it ('test10', function () {
testParser(`
SELECT rd.*, ((rd.rd_numberofrooms) - (select sum(rn.reservation_numberofrooms) as count_reserve_room from reservation as rn WHERE rn.reservation_rd_id = rd.rd_id AND (str_to_date('$data_Check_in_date','%d-%m-%y') BETWEEN str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date(rn.reservation_check_out_date,'%d-%m-%y') OR str_to_date('$data_Check_out_date','%d-%m-%y') BETWEEN str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date(rn.reservation_check_out_date,'%d-%m-%y') OR str_to_date('$data_Check_in_date','%d-%m-%y') <= str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date('$data_Check_out_date','%d-%m-%y') ) )) as reserve
FROM room_details rd LEFT JOIN reservation rn ON rd.rd_id = rn.reservation_rd_id WHERE NOT EXISTS
(
SELECT rn.* FROM reservation rn WHERE rn.reservation_rd_id = rd.rd_id
AND (str_to_date('$data_Check_in_date','%d-%m-%y') BETWEEN str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date(rn.reservation_check_out_date,'%d-%m-%y') OR str_to_date('$data_Check_out_date','%d-%m-%y') BETWEEN str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date(rn.reservation_check_out_date,'%d-%m-%y') OR str_to_date('$data_Check_in_date','%d-%m-%y') <= str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date('$data_Check_out_date','%d-%m-%y') >= str_to_date(rn.reservation_check_out_date,'%d-%m-%y'))
AND (rd.rd_numberofrooms <= (select sum(rn.reservation_numberofrooms) as count_reserve_room from reservation as rn WHERE rn.reservation_rd_id = rd.rd_id AND (str_to_date('$data_Check_in_date','%d-%m-%y') BETWEEN str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date(rn.reservation_check_out_date,'%d-%m-%y') OR str_to_date('$data_Check_out_date','%d-%m-%y') BETWEEN str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date(rn.reservation_check_out_date,'%d-%m-%y') OR str_to_date('$data_Check_in_date','%d-%m-%y') <= str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date('$data_Check_out_date','%d-%m-%y') ) ) )
)
`);
});
});