Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 2b09bfe

Browse filesBrowse files
lutterlutter
committed
store: Move SQL validation tests to YAML test file
1 parent 0da4377 commit 2b09bfe
Copy full SHA for 2b09bfe

File tree

Expand file treeCollapse file tree

2 files changed

+71
-201
lines changed
Open diff view settings
Filter options
Expand file treeCollapse file tree

2 files changed

+71
-201
lines changed
Open diff view settings
Collapse file

‎store/postgres/src/sql/parser_tests.yaml‎

Copy file name to clipboardExpand all lines: store/postgres/src/sql/parser_tests.yaml
+71-24Lines changed: 71 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -7,16 +7,16 @@
77

88
- sql: select symbol, address from token where decimals > 10
99
ok: >
10-
SELECT symbol, address FROM (
11-
SELECT id, address, symbol, name, decimals FROM "sgd0815"."token" WHERE block_range @> 2147483647) AS token
12-
WHERE decimals > 10
10+
select symbol, address from (
11+
select id, address, symbol, name, decimals from "sgd0815"."token" where block_range @> 2147483647) as token
12+
where decimals > 10
1313
- sql: >
1414
with tokens as (
1515
select * from (values
16-
('0x0000000000000000000000000000000000000000','ETH','Ethereum',18),
17-
('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48','USDC','USD Coin',6)
16+
('0x0000000000000000000000000000000000000000','eth','ethereum',18),
17+
('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48','usdc','usd coin',6)
1818
) as t(address,symbol,name,decimals))
19-
select date, t.symbol, SUM(amount)/pow(10,t.decimals) as amount
19+
select date, t.symbol, sum(amount)/pow(10,t.decimals) as amount
2020
from (select
2121
date(to_timestamp(block_timestamp) at time zone 'utc') as date,
2222
token, amount
@@ -33,21 +33,68 @@
3333
group by tp.date, t.symbol, t.decimals
3434
order by tp.date desc, amount desc
3535
ok: >
36-
WITH tokens AS (
37-
SELECT * FROM (
38-
VALUES ('0x0000000000000000000000000000000000000000', 'ETH', 'Ethereum', 18),
39-
('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', 'USDC', 'USD Coin', 6))
40-
AS t (address, symbol, name, decimals))
41-
SELECT date, t.symbol, SUM(amount) / pow(10, t.decimals) AS amount
42-
FROM (SELECT date(to_timestamp(block_timestamp) AT TIME ZONE 'utc') AS date, token, amount
43-
FROM (SELECT id, timestamp, pool, token_0, token_1, sender, recipient, origin, amount_0, amount_1, amount_usd, sqrt_price_x96, tick, log_index
44-
FROM "sgd0815"."swap" WHERE block$ <= 2147483647) AS sm,
45-
UNNEST(sm.amounts_in, sm.tokens_in) AS smi (amount, token)
46-
UNION ALL
47-
SELECT date(to_timestamp(block_timestamp) AT TIME ZONE 'utc') AS date, token, amount
48-
FROM (SELECT id, timestamp, pool, token_0, token_1, sender, recipient, origin, amount_0, amount_1, amount_usd, sqrt_price_x96, tick, log_index
49-
FROM "sgd0815"."swap" WHERE block$ <= 2147483647) AS sm,
50-
UNNEST(sm.amounts_out, sm.tokens_out) AS smo (amount, token)) AS tp
51-
JOIN tokens AS t ON t.address = tp.token
52-
GROUP BY tp.date, t.symbol, t.decimals
53-
ORDER BY tp.date DESC, amount DESC
36+
with tokens as (
37+
select * from (
38+
values ('0x0000000000000000000000000000000000000000', 'eth', 'ethereum', 18),
39+
('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', 'usdc', 'usd coin', 6))
40+
as t (address, symbol, name, decimals))
41+
select date, t.symbol, sum(amount) / pow(10, t.decimals) as amount
42+
from (select date(to_timestamp(block_timestamp) at time zone 'utc') as date, token, amount
43+
from (select id, timestamp, pool, token_0, token_1, sender, recipient, origin, amount_0, amount_1, amount_usd, sqrt_price_x96, tick, log_index
44+
from "sgd0815"."swap" where block$ <= 2147483647) as sm,
45+
unnest(sm.amounts_in, sm.tokens_in) as smi (amount, token)
46+
union all
47+
select date(to_timestamp(block_timestamp) at time zone 'utc') as date, token, amount
48+
from (select id, timestamp, pool, token_0, token_1, sender, recipient, origin, amount_0, amount_1, amount_usd, sqrt_price_x96, tick, log_index
49+
from "sgd0815"."swap" where block$ <= 2147483647) as sm,
50+
unnest(sm.amounts_out, sm.tokens_out) as smo (amount, token)) as tp
51+
join tokens as t on t.address = tp.token
52+
group by tp.date, t.symbol, t.decimals
53+
order by tp.date desc, amount desc
54+
- name: pg_sleep forbidden
55+
sql: select pool from swap where '' = (select cast(pg_sleep(5) as text))
56+
err: Unknown or unsupported function pg_sleep
57+
- name: table functions forbidden
58+
sql: >
59+
select vid, k.sname
60+
from swap,
61+
lateral(select current_schemas as sname from current_schemas(true)) as k
62+
err: Unknown or unsupported function current_schemas
63+
- name: function without parens forbidden
64+
sql: select input_token from swap where '' = (select user)
65+
err: Unknown or unsupported function user
66+
- name: aggregation allowed
67+
sql: >
68+
select token0, sum(amount0) as total_amount
69+
from swap
70+
group by token0
71+
having sum(amount0) > 1000
72+
ok: >
73+
SELECT token0, sum(amount0) AS total_amount
74+
FROM (SELECT id, timestamp, pool, token_0, token_1, sender, recipient, origin, amount_0, amount_1, amount_usd, sqrt_price_x96, tick, log_index
75+
FROM "sgd0815"."swap" WHERE block$ <= 2147483647) AS swap
76+
GROUP BY token0
77+
HAVING sum(amount0) > 1000
78+
- name: arbitrary function forbidden
79+
sql: >
80+
select token0 from swap
81+
where '' = (select cast(do_strange_math(amount_in) as text))
82+
err: Unknown or unsupported function do_strange_math
83+
- name: create table forbidden
84+
sql: create table foo (id int primary key);
85+
err: Only SELECT query is supported
86+
- name: insert forbidden
87+
sql: insert into foo values (1);
88+
err: Only SELECT query is supported
89+
- name: CTE allowed
90+
sql: with foo as (select 1) select * from foo
91+
ok: with foo as (select 1) select * from foo
92+
- name: CTE with insert forbidden
93+
sql: with foo as (insert into target values(1)) select * from bar
94+
err: Only SELECT query is supported
95+
- name: only single statement
96+
sql: select 1; select 2;
97+
err: Multi statement is not supported
98+
- name: unknown tables forbidden
99+
sql: select * from unknown_table
100+
err: Unknown table unknown_table
Collapse file

‎store/postgres/src/sql/validation.rs‎

Copy file name to clipboardExpand all lines: store/postgres/src/sql/validation.rs
-177Lines changed: 0 additions & 177 deletions
Original file line numberDiff line numberDiff line change
@@ -164,180 +164,3 @@ impl VisitorMut for Validator<'_> {
164164
ControlFlow::Continue(())
165165
}
166166
}
167-
168-
#[cfg(test)]
169-
mod test {
170-
use graph::prelude::BLOCK_NUMBER_MAX;
171-
172-
use super::*;
173-
use crate::sql::{constants::SQL_DIALECT, test::make_layout};
174-
175-
fn validate(sql: &str) -> Result<(), Error> {
176-
let mut statements = sqlparser::parser::Parser::parse_sql(&SQL_DIALECT, sql).unwrap();
177-
178-
const GQL: &str = "
179-
type Swap @entity {
180-
id: ID!
181-
sender: Bytes!
182-
inputAmount: BigDecimal!
183-
inputToken: Bytes!
184-
amountOut: BigDecimal!
185-
outputToken: Bytes!
186-
slippage: BigDecimal!
187-
referralCode: String
188-
blockNumber: Int!
189-
blockTimestamp: Timestamp!
190-
transactionHash: Bytes!
191-
}";
192-
193-
let layout = make_layout(GQL);
194-
195-
let mut validator = Validator::new(&layout, BLOCK_NUMBER_MAX);
196-
197-
validator.validate_statements(&mut statements)
198-
}
199-
200-
#[test]
201-
fn test_function_disallowed() {
202-
let result = validate(
203-
"
204-
SELECT
205-
input_token
206-
FROM swap
207-
WHERE '' = (
208-
SELECT
209-
CAST(pg_sleep(5) AS text
210-
)
211-
)",
212-
);
213-
assert_eq!(result, Err(Error::UnknownFunction("pg_sleep".to_owned())));
214-
}
215-
216-
#[test]
217-
fn test_table_function_disallowed() {
218-
let result = validate(
219-
"
220-
SELECT
221-
vid,
222-
k.sname
223-
FROM swap,
224-
LATERAL(
225-
SELECT
226-
current_schemas as sname
227-
FROM current_schemas(true)
228-
) as k",
229-
);
230-
assert_eq!(
231-
result,
232-
Err(Error::UnknownFunction("current_schemas".to_owned()))
233-
);
234-
}
235-
236-
#[test]
237-
fn test_function_disallowed_without_paranthesis() {
238-
let result = validate(
239-
"
240-
SELECT
241-
input_token
242-
FROM swap
243-
WHERE '' = (
244-
SELECT user
245-
)",
246-
);
247-
assert_eq!(result, Err(Error::UnknownFunction("user".to_owned())));
248-
}
249-
250-
#[test]
251-
fn test_function_allowed() {
252-
let result = validate(
253-
"
254-
SELECT
255-
input_token,
256-
SUM(input_amount) AS total_amount
257-
FROM swap
258-
GROUP BY input_token
259-
HAVING SUM(input_amount) > 1000
260-
",
261-
);
262-
assert_eq!(result, Ok(()));
263-
}
264-
265-
#[test]
266-
fn test_function_unknown() {
267-
let result = validate(
268-
"
269-
SELECT
270-
input_token
271-
FROM swap
272-
WHERE '' = (
273-
SELECT
274-
CAST(do_strange_math(amount_in) AS text
275-
)
276-
)",
277-
);
278-
assert_eq!(
279-
result,
280-
Err(Error::UnknownFunction("do_strange_math".to_owned()))
281-
);
282-
}
283-
284-
#[test]
285-
fn test_not_select_ddl() {
286-
let result = validate(
287-
"
288-
CREATE TABLE foo (id INT PRIMARY KEY);
289-
",
290-
);
291-
assert_eq!(result, Err(Error::NotSelectQuery));
292-
}
293-
294-
#[test]
295-
fn test_not_select_insert() {
296-
let result = validate(
297-
"
298-
INSERT INTO foo VALUES (1);
299-
",
300-
);
301-
assert_eq!(result, Err(Error::NotSelectQuery));
302-
}
303-
304-
#[test]
305-
fn test_common_table_expression() {
306-
let result = validate(
307-
"
308-
WITH foo AS (SELECT 1) SELECT * FROM foo;
309-
",
310-
);
311-
assert_eq!(result, Ok(()));
312-
}
313-
314-
#[test]
315-
fn test_common_table_expression_with_effect() {
316-
let result = validate(
317-
"
318-
WITH foo AS (INSERT INTO target VALUES(1)) SELECT * FROM bar;
319-
",
320-
);
321-
assert_eq!(result, Err(Error::NotSelectQuery));
322-
}
323-
324-
#[test]
325-
fn test_no_multi_statement() {
326-
let result = validate(
327-
"
328-
SELECT 1; SELECT 2;
329-
",
330-
);
331-
assert_eq!(result, Err(Error::MultiStatementUnSupported));
332-
}
333-
334-
#[test]
335-
fn test_table_unknown() {
336-
let result = validate(
337-
"
338-
SELECT * FROM unknown_table;
339-
",
340-
);
341-
assert_eq!(result, Err(Error::UnknownTable("unknown_table".to_owned())));
342-
}
343-
}

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.