florajs / sql-parser Goto Github PK
View Code? Open in Web Editor NEWParse SQL (select) statements into abstract syntax tree (AST) and convert ASTs back to SQL.
License: GNU General Public License v2.0
Parse SQL (select) statements into abstract syntax tree (AST) and convert ASTs back to SQL.
License: GNU General Public License v2.0
I'm having difficulty parsing a simple delete query. Is there something I'm doing wrong here?
const { Parser } = require("flora-sql-parser")
new Parser().parse('delete from "user" where id = 1')
Output:
peg$SyntaxError: Expected "$", "(", "--", "/*", "INSERT", "REPLACE", "SELECT", "UPDATE", "WITH", "return", [ \t\n\r], or end of input but "d" found.
I have the example running here on RunKit.
I'm finding unexpected behavior if my column is name type
:
const { Parser } = require("flora-sql-parser")
const parser = new Parser();
const ast = parser.parse("SELECT type FROM t");
Result in error:
peg$SyntaxError: Expected [A-Za-z0-9_:] or [A-Za-z0-9_] but " " found.
Changing the column name to something else, results in no error. However, type
is a fairly common property/column name. I'm happy to try to PR this if someone can give me an indication where to start or any other hints.
UPDATE: this works if you wrap type
in double quotes. Any thoughts about automating double quote wrapping for reserved words?
I am getting quotes around CURRENT_TIMESTAMP in my where clause after converting a query to AST and back. This is causing an invalid identifier error when executing the query.
Parsing integers with leading zeros does not work:
const { Parser } = require('flora-sql-parser');
const parser = new Parser();
//Follwing statements all throw SyntaxError
parser.parse('SELECT 042 FROM t');
parser.parse('SELECT * FROM t WHERE attr < 042');
parser.parse('SELECT * FROM t WHERE attr BETWEEN 24 AND 042');
it will throw error when i use LIKE and IS NOT NULL at the same time.
example: select * from tab where username
like '%something%' and img
is not null
throw error: SyntaxError: Expected [A-Za-z0-9_:] or [A-Za-z0-9_] but " " found.
https://www.npmjs.com/package/flora-sql-parser?activeTab=versions says there is a v0.9.3 version that has been published a couple days ago. Releases do not show this version and the latest commit correspond to tagging to v0.9.2.
Was that accidental and v0.9.2 === v0.9.3, or was there an uncommitted change added to v0.9.3?
Thanks! 🙏
When upgrading to v0.9.0, we get:
ERROR in ./node_modules/flora-sql-parser/lib/parser.js
Module not found: Error: Can't resolve './../build/pegjs-parser'
Looking at https://unpkg.com/[email protected]/ vs. https://unpkg.com/[email protected]/, it seems that the build
folder is missing.
Is that something that was forgotten at publish time?
record column not supported
x=parser.parse('SELECT cv,i FROM voucher cv JOIN item i ON i.dbid=cv.dbid AND i.cvoid=cv.void AND i.deleted=0')
In version 0.7.4, How to parse string when there is single quote?
For example:
parser.parse("select * from t where firstName=Hu's")
parser.parse("select * from t where firstName=Hu\'s")
both are not working.
Support date/time literals in the where clause:
DATE '1999-12-25'
TIME '08:23:16'
TIME '14:23:16.5'
TIMESTAMP '1999-12-25 08:23:16'
e.g.
SELECT * WHERE x = TIMESTAMP '1999-12-25 08:23:16'
const { Parser } = require('flora-sql-parser');
const parser = new Parser();
//Following statement throws SyntaxError
parser.parse('SELECT .5 * SUM FROM t');
while
parser.parse('SELECT 0.5 * SUM FROM t');
works fine
Error:
"SyntaxError: Expected "!", "$", "'", "(", "", "+", "-", "--", "/", ":", "ALL", "AVG", "CASE", "CAST", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "DATE", "DISTINCT", "EXISTS", "FALSE", "MAX", "MIN", "NOT", "NULL", "SELECT", "SESSION_USER", "SQL_BIG_RESULT", "SQL_BUFFER_RESULT", "SQL_CACHE", "SQL_CALC_FOUND_ROWS", "SQL_NO_CACHE", "SQL_SMALL_RESULT", "SUM", "SYSTEM_USER", "TIME", "TIMESTAMP", "TRUE", "USER", "WITH", """, "`", [ \t\n\r], [0-9], or [A-Za-z_] but "." found."
can not parse some sqls. would you like help me to solve this problem
example
"SELECT *, (SELECT group_concat(inner_url) inner_url FROM t_media_info b WHERE a.id = b.msg_id GROUP BY msg_id) inner_url WHERE f_step = 1 AND business_id IN (6, 3, 4, 8, 9, 12, 14, 15, 38, 39, 5) ORDER BY create_time DESC
The parser has an issue parsing a statement with a string with escaped single quotes.
In PostgreSQL, the single quote in string can be escaped with an extra single quote before it. For example, this SQL works in PostgreSQL
SELECT * FROM restaurants WHERE name = 'wendy''s'
but the parser throws an error for it
SyntaxError: Expected "!=", "%", "*", "+", "-", "--", "/", "/*", "<", "<=", "<>", "=", ">", ">=", "AND", "GROUP", "HAVING", "LIMIT", "OR", "ORDER", "UNION", [ \t\n\r], or end of input but "'" found.
The workaround is to change the way of escaping from ''
to \'
. So this SQL works in the parser
SELECT * FROM restaurants WHERE name = 'wendy\'s'
I noticed this project has an MIT license:
https://github.com/godmodelabs/flora-sql-parser/blob/master/LICENSE
However, it is "based on the SQL parser extracted from Alibaba's nquery module". The nquery module uses the GPLv2 license:
https://github.com/alibaba/nquery/blob/master/package.json#L34
That would mean this project may contain GPL code and could be mislabeled with the MIT license.
I'm curious, how the SQL parser was extracted from nquery?
First, wanted to say thanks for the great library, it's been very useful!
I've run into a problem when converting an ast selected column like this:
{
expr: {
type: ‘column_ref’,
table: ‘tableid’,
column: ‘columnname’
},
as: ‘column alias’
}
Where it'll be translated as a sql string to something like:
SELECT "tableid"."columnname" as 'column alias' from "tableid"
When sending that string to execute with PRESTO I get an error like:
No viable alternative at input 'column alias'
This is because PRESTO expects double quotes instead of single quotes for column aliases. Would y'all mind if I create a pull request to change https://github.com/godmodelabs/flora-sql-parser/blob/master/lib/sql.js#L143 to use double quotes instead of single quotes?
Thanks again!
wondering if you can add node position, and or if there is any reason for not to have it. similar to this https://github.com/redotjs/redot/blob/b5abff784e9952d12d3713f27839bed12ee173df/packages/redot-parse/dot.pegjs#L13
thanks!
Crashes on a query such as:
SELECT tablename.* FROM tablename
with
SyntaxError: Expected "'", "--", "/*", "\"", "`", [ \t\n\r] or [A-Za-z_] but "*" found.
For example, you may want to augment a row with information from another table, as in:
SELECT
preferences.*,
user.displayName
FROM
preferences,
user
WHERE
preferences.userid = user.id
The parser is able to parse SELECT * FROM db.table
just fine, but it fails to parse SELECT * FROM db."table"
and SELECT * FROM "db"."table"
properly. It fails with the error message Expected "--", "/*", [ \t\n\r], or [A-Za-z_] but "\"" found.
Planned bigger and backward incompatible changes for next major version:
when i input 'GROUP_CONCAT' to parse AST it would throw error.
exp:
'SELECT GROUP_CONCAT(DISTINCT id) AS ids FROM t'
throw error:
message: 'Expected [A-Za-z0-9_:] or [A-Za-z0-9_] but " " found.',
expected: [
{
type: 'class',
parts: [Array],
inverted: false,
ignoreCase: false
},
{
type: 'class',
parts: [Array],
inverted: false,
ignoreCase: false
},
{
type: 'class',
parts: [Array],
inverted: false,
ignoreCase: false
}
],
found: ' ',
location: {
start: { offset: 28, line: 1, column: 29 },
end: { offset: 29, line: 1, column: 30 }
},
name: 'SyntaxError'
}
by the way
how can i do if i want to add a new expression parsing rule with plugin-ins
Firstly, thanks for this tool/module...it helps a lot.
Given the following SQL statement, the parser works fine:
SELECT m.userId, m.username, m.email, a.isAdmin
FROM member m
LEFT JOIN admin a ON m.id = a.id
The following, however, fails.
SELECT m.userId, m.username, m.email, a.isAdmin
FROM member m
LEFT OUTER JOIN admin a ON m.id = a.id
My code is as follows
var sql = "SELECT m.id, m.username, m.email, a.isAdmin ";
sql += "FROM member m "
sql += "LEFT OUTER JOIN admin a ON m.id = a.id"
const Parser = require('flora-sql-parser').Parser;
const parser = new Parser();
const ast = parser.parse(sql);
console.log(ast);
The error is as follows:
/home/user/Projects/node/mysqltest/node_modules/flora-sql-parser/pegjs-parser.js:10194
throw peg$buildStructuredError(
^
SyntaxError: Expected "--", "/*", "JOIN", or [ \t\n\r] but "O" found.
at peg$buildStructuredError (/home/user/Projects/node/mysqltest/node_modules/flora-sql-parser/pegjs-parser.js:949:12)
at peg$parse (/home/user/Projects/node/mysqltest/node_modules/flora-sql-parser/pegjs-parser.js:10194:11)
at Parser.parse (/home/user/Projects/node/mysqltest/node_modules/flora-sql-parser/lib/parser.js:7:16)
at Object.<anonymous> (/home/user/Projects/node/mysqltest/parser.js:7:20)
at Module._compile (module.js:643:30)
at Object.Module._extensions..js (module.js:654:10)
at Module.load (module.js:556:32)
at tryModuleLoad (module.js:499:12)
at Function.Module._load (module.js:491:3)
at Function.Module.runMain (module.js:684:10)
解析hive类型的sql语句时,语法正确但是解析失败
select row_number() over(A by B order by C desc) as col1
from table1
解析不了row_number()
> [email protected] install F:\proj\htrack\src\client-static\node_modules\flora-sql-parser
> pegjs -o pegjs-parser.js sql.pegjs
'pegjs' is not recognized as an internal or external command,
operable program or batch file.
npm WARN optional SKIPPING OPTIONAL DEPENDENCY: fsevents@^1.0.0 (node_modules\chokidar\node_modules\fsevents):
npm WARN notsup SKIPPING OPTIONAL DEPENDENCY: Unsupported platform for [email protected]: wanted {"os":"darwin","arch":"any"} (current: {"os":"win32","arch":"x64"})
npm WARN [email protected] No repository field.
npm WARN [email protected] No license field.
npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! [email protected] install: `pegjs -o pegjs-parser.js sql.pegjs`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the [email protected] install script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.
We are using a project that uses this a dep and we can not build it for deploy with npm pack (requires no symlinks)
Given the following T-SQL Statement as input
const SQL = 'SELECT syscolumns.name, ROW_NUMBER() OVER(PARTITION BY id ORDER BY colid) rowNo from sysColumns';
When parsed into ast with the Default option:
const ast = parser.astify(SQL);
ast:
{
"columns": [
{
"as": null,
"expr": {
"column": "name",
"table": "syscolumns",
"type": "column_ref"
}
},
{
"as": "rowNo",
"expr": {
"args": {
"type": "expr_list",
"value": [
]
},
"name": "ROW_NUMBER",
"over": {
"as_window_specification": {
"parentheses": true,
"window_specification": {
"name": null,
"orderby": [
{
"expr": {
"column": "colid",
"table": null,
"type": "column_ref"
},
"type": "ASC"
}
],
"partitionby": [
{
"as": null,
"expr": {
"column": "id",
"table": null,
"type": "column_ref"
}
}
],
"window_frame_clause": null
}
},
"type": "window"
},
"type": "function"
}
}
],
"distinct": null,
"for_update": null,
"from": [
{
"as": null,
"db": null,
"table": "sysColumns"
}
],
"groupby": null,
"having": null,
"into": {
"position": null
},
"limit": null,
"options": null,
"orderby": null,
"type": "select",
"where": null,
"window": null,
"with": null
}
Converted back to T-SQL:
const sql = parser.sqlify(ast, {database: 'TransactSQL'});
sql:
SELECT [syscolumns].[name], ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [colid] ASC) AS [rowNo] FROM [sysColumns]
However, when the SQL is parsed into ast with TransactSQL option, the produced ast is missing the "as_window_specification" object and attempting to parse it back into SQL fails,
const ast = parser.astify(SQL, {database: 'TransactSQL'});
ast:
{
"columns": [
{
"as": null,
"expr": {
"column": "name",
"table": "syscolumns",
"type": "column_ref"
}
},
{
"as": "rowNo",
"expr": {
"args": {
"type": "expr_list",
"value": [
]
},
"name": "ROW_NUMBER",
"over": {
"orderby": [
{
"expr": {
"column": "colid",
"table": null,
"type": "column_ref"
},
"type": "ASC"
}
],
"partitionby": [
{
"as": null,
"expr": {
"column": "id",
"table": null,
"type": "column_ref"
}
}
]
},
"type": "function"
}
}
],
"distinct": null,
"from": [
{
"as": null,
"db": null,
"table": "sysColumns"
}
],
"groupby": null,
"having": null,
"limit": null,
"options": null,
"orderby": null,
"top": null,
"type": "select",
"where": null,
"with": null
}
Converted back to T-SQL:
const sql = parser.sqlify(ast, {database: 'TransactSQL'});
SError: unknown over type
at B (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1085075)
at Object.V [as function] (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1086132)
at wr (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1095810)
at C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1101662
at C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1101974
at Array.map (<anonymous>)
at $r (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1101558)
at lr (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1092014)
at Zr (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1111238)
at Jr (C:\projects\SQLParser\node_modules\node-sql-parser\index.js:1:1112115)
Hey there,
It looks like you just published v0.8.1, but the tag and release are missing from: https://github.com/godmodelabs/flora-sql-parser/releases
Those 2 things are very useful for things like the wonderful Renovate which opens PRs on consuming repos and adds release notes for the new versions.
Thanks again!
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.