Skip to content

Commit 1ff6893

Browse files
taozhi8833998taozhi8833998
committed
feat: support drop view, and if else stmt in tsql
1 parent e2fb5dc commit 1ff6893

8 files changed

Lines changed: 91 additions & 10 deletions

File tree

‎.github/workflows/npm-publish-github-packages.yml‎

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
# This workflow will run tests using node and then publish a package to GitHub Packages when a release is created
22
# For more information see: https://docs.github.com/en/actions/publishing-packages/publishing-nodejs-packages
33

4-
name: Node.js Package node-sql-parser to gpr
4+
name: node-sql-parser package to npm and gpr
55

66
on:
77
release:
@@ -18,6 +18,21 @@ jobs:
1818
- run: npm install
1919
- run: npm test
2020

21+
publish-npm:
22+
needs: build
23+
runs-on: ubuntu-latest
24+
steps:
25+
- uses: actions/checkout@v3
26+
- uses: actions/setup-node@v3
27+
with:
28+
node-version: 16
29+
registry-url: https://registry.npmjs.org/
30+
- run: npm install
31+
- run: npm run build
32+
- run: cd output/prod && npm publish
33+
env:
34+
NODE_AUTH_TOKEN: ${{secrets.PUBLISH_NPM_TOKEN}}
35+
2136
publish-gpr:
2237
needs: build
2338
runs-on: ubuntu-latest

‎pegjs/mariadb.pegjs‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -673,7 +673,6 @@ drop_stmt
673673
ife:if_exists? __
674674
t:table_ref_list __
675675
op:view_options? {
676-
if(t) t.forEach(tt => tableList.add(`${a}::${tt.db}::${tt.table}`));
677676
return {
678677
tableList: Array.from(tableList),
679678
columnList: columnListTableAlias(columnList),

‎pegjs/mysql.pegjs‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -872,7 +872,6 @@ drop_stmt
872872
ife:if_exists? __
873873
t:table_ref_list __
874874
op:view_options? {
875-
if(t) t.forEach(tt => tableList.add(`${a}::${tt.db}::${tt.table}`));
876875
return {
877876
tableList: Array.from(tableList),
878877
columnList: columnListTableAlias(columnList),

‎pegjs/transactsql.pegjs‎

Lines changed: 41 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -202,15 +202,16 @@
202202
}
203203

204204
start
205-
= head:start_item __ tail:(__ KW_GO __ start_item)* {
205+
= head:start_item __ tail:(__ KW_GO __ start_item?)* {
206206
if (!tail || tail.length === 0) return head
207207
delete head.tableList
208208
delete head.columnList
209209
let cur = head
210210
for (let i = 0; i < tail.length; i++) {
211-
delete tail[i][3].tableList
212-
delete tail[i][3].columnList
213-
cur.go_next = tail[i][3]
211+
const item = tail[i][3] || []
212+
delete item.tableList
213+
delete item.columnList
214+
cur.go_next = item
214215
cur.go = 'go'
215216
cur = cur.go_next
216217
}
@@ -222,7 +223,7 @@ start
222223
}
223224

224225
start_item
225-
= __ n:(multiple_stmt / cmd_stmt / crud_stmt) {
226+
= __ n:(multiple_stmt / cmd_stmt / crud_stmt) __ SEMICOLON? {
226227
return n
227228
}
228229

@@ -239,6 +240,7 @@ cmd_stmt
239240
/ unlock_stmt
240241
/ declare_stmt
241242
/ exec_stmt
243+
/ if_else_stmt
242244

243245
create_stmt
244246
= create_table_stmt
@@ -651,6 +653,24 @@ exec_variable
651653
value: e,
652654
}
653655
}
656+
657+
if_else_stmt
658+
= 'if'i __ ie:expr __ ia:crud_stmt __ s:SEMICOLON? __ g:KW_GO? __ el:(KW_ELSE __ crud_stmt)? __ es:SEMICOLON? {
659+
return {
660+
tableList: Array.from(tableList),
661+
columnList: columnListTableAlias(columnList),
662+
ast: {
663+
type: 'if',
664+
keyword: 'if',
665+
boolean_expr: ie,
666+
semicolons: [s || '', es || ''],
667+
go: g,
668+
if_expr: ia,
669+
else_expr: el && el[2],
670+
}
671+
}
672+
}
673+
654674
drop_index_opt
655675
= head:(ALTER_ALGORITHM / ALTER_LOCK) tail:(__ (ALTER_ALGORITHM / ALTER_LOCK))* {
656676
return createList(head, tail, 1)
@@ -707,6 +727,21 @@ drop_stmt
707727
}
708728
};
709729
}
730+
/ a:KW_DROP __
731+
r:KW_VIEW __
732+
ife:if_exists? __
733+
t:table_ref_list {
734+
return {
735+
tableList: Array.from(tableList),
736+
columnList: columnListTableAlias(columnList),
737+
ast: {
738+
type: a.toLowerCase(),
739+
keyword: r.toLowerCase(),
740+
prefix: ife,
741+
name: t,
742+
}
743+
};
744+
}
710745

711746

712747
truncate_stmt
@@ -2897,7 +2932,7 @@ EOF = !.
28972932

28982933
//begin procedure extension
28992934
proc_stmts
2900-
= proc_stmt*
2935+
= proc_stmt+
29012936

29022937
proc_stmt
29032938
= &{ varList = []; return true; } __ s:(assign_stmt / return_stmt) {

‎src/command.js‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,7 @@ import { createDefinitionToSQL } from './create'
33
import { identifierToSql, hasVal, toUpper } from './util'
44
import { exprToSQL } from './expr'
55
import { tablesToSQL, tableToSQL } from './tables'
6+
import astToSQL from './sql'
67

78
function callToSQL(stmt) {
89
const type = 'CALL'
@@ -130,12 +131,27 @@ function declareToSQL(stmt) {
130131
return result.join(' ')
131132
}
132133

134+
function ifToSQL(stmt) {
135+
const {
136+
boolean_expr: boolExpr,
137+
else_expr: elseExpr,
138+
if_expr: ifExpr,
139+
go,
140+
semicolons,
141+
type,
142+
} = stmt
143+
const result = [toUpper(type), exprToSQL(boolExpr), `${astToSQL(ifExpr.ast)}${semicolons[0]}`, toUpper(go)]
144+
if (elseExpr) result.push('ELSE', `${astToSQL(elseExpr.ast)}${semicolons[1]}`)
145+
return result.filter(hasVal).join(' ')
146+
}
147+
133148
export {
134149
callToSQL,
135150
commonCmdToSQL,
136151
deallocateToSQL,
137152
declareToSQL,
138153
descToSQL,
154+
ifToSQL,
139155
renameToSQL,
140156
useToSQL,
141157
setVarToSQL,

‎src/sql.js‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
import { unionToSQL, multipleToSQL } from './union'
22

3-
const supportedTypes = ['analyze', 'attach', 'select', 'deallocate', 'delete', 'exec', 'update', 'insert', 'drop', 'rename', 'truncate', 'call', 'desc', 'use', 'alter', 'set', 'create', 'lock', 'unlock', 'declare', 'show', 'replace']
3+
const supportedTypes = ['analyze', 'attach', 'select', 'deallocate', 'delete', 'exec', 'update', 'insert', 'drop', 'rename', 'truncate', 'call', 'desc', 'use', 'alter', 'set', 'create', 'lock', 'unlock', 'declare', 'show', 'replace', 'if']
44

55
function checkSupported(expr) {
66
const ast = expr && expr.ast ? expr.ast : expr

‎src/union.js‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,7 @@ import {
1010
deallocateToSQL,
1111
declareToSQL,
1212
descToSQL,
13+
ifToSQL,
1314
useToSQL,
1415
renameToSQL,
1516
setVarToSQL,
@@ -32,6 +33,7 @@ const typeToSQLFn = {
3233
delete : deleteToSQL,
3334
exec : execToSQL,
3435
update : updateToSQL,
36+
if : ifToSQL,
3537
insert : insertToSQL,
3638
drop : commonCmdToSQL,
3739
truncate : commonCmdToSQL,

‎test/transactsql.spec.js‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -208,4 +208,19 @@ describe('transactsql', () => {
208208
sql = 'ALTER VIEW [dbo].[reporting_class] (id, active) with ENCRYPTION, SCHEMABINDING AS SELECT [ClassHexID], [DepartmentID] AS class_source FROM [Class] WHERE [Class].[active] = 1 with check option'
209209
expect(getParsedSql(sql)).to.be.equal('ALTER VIEW [dbo].[reporting_class] ([id], [active]) WITH ENCRYPTION, SCHEMABINDING AS SELECT [ClassHexID], [DepartmentID] AS [class_source] FROM [Class] WHERE [Class].[active] = 1 WITH CHECK OPTION')
210210
})
211+
describe('if else', () => {
212+
it('should support if only statement', () => {
213+
const sql = `IF EXISTS(SELECT 1 from sys.views where name='MyView' and type='v')
214+
DROP view MyView;
215+
GO`
216+
expect(getParsedSql(sql)).to.be.equal("IF EXISTS(SELECT 1 FROM [sys].[views] WHERE [name] = 'MyView' AND [type] = 'v') DROP VIEW [MyView]; GO")
217+
})
218+
it('should support if else statement', () => {
219+
const sql = `IF DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')
220+
SELECT 'Weekend';
221+
ELSE
222+
SELECT 'Weekday';`
223+
expect(getParsedSql(sql)).to.be.equal("IF DATENAME([weekday], GETDATE()) IN (N'Saturday', N'Sunday') SELECT 'Weekend'; ELSE SELECT 'Weekday';")
224+
})
225+
})
211226
})

0 commit comments

Comments
 (0)