Skip to content

Commit cb1b3ee

Browse files
taozhi8833998taozhi8833998
authored
Merge pull request taozhi8833998#1425 from taozhi8833998/feat-drop-create-trigger-mysql
Feat drop create trigger mysql
2 parents 81b669f + 7b83e78 commit cb1b3ee

6 files changed

Lines changed: 226 additions & 13 deletions

File tree

‎pegjs/mariadb.pegjs‎

Lines changed: 83 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -228,6 +228,7 @@ cmd_stmt
228228

229229
create_stmt
230230
= create_table_stmt
231+
/ create_trigger_stmt
231232
/ create_index_stmt
232233
/ create_db_stmt
233234
/ create_view_stmt
@@ -343,7 +344,7 @@ create_view_stmt
343344
= a:KW_CREATE __
344345
or:(KW_OR __ KW_REPLACE)? __
345346
al:("ALGORITHM"i __ KW_ASSIGIN_EQUAL __ ("UNDEFINED"i / "MERGE"i / "TEMPTABLE"i))? __
346-
df:("DEFINER"i __ KW_ASSIGIN_EQUAL __ ident)? __
347+
df:trigger_definer? __
347348
ss:("SQL"i __ "SECURITY"i __ ("DEFINER"i / "INVOKER"i))? __
348349
KW_VIEW __ v:table_name __ c:(LPAREN __ column_list __ RPAREN)? __
349350
KW_AS __ s:select_stmt_nake __
@@ -358,7 +359,7 @@ create_view_stmt
358359
keyword: 'view',
359360
replace: or && 'or replace',
360361
algorithm: al && al[4],
361-
definer: df && df[4],
362+
definer: df,
362363
sql_security: ss && ss[4],
363364
columns: c && c[2],
364365
select: s,
@@ -536,6 +537,67 @@ create_column_definition
536537
}
537538
}
538539

540+
trigger_definer
541+
= 'DEFINER'i __ KW_ASSIGIN_EQUAL __ u:literal_string __ '@' __ h:literal_string {
542+
const userNameSymbol = u.type === 'single_quote_string' ? '\'' : '"'
543+
const hostSymbol = h.type === 'single_quote_string' ? '\'' : '"'
544+
return `DEFINER = ${userNameSymbol}${u.value}${userNameSymbol}@${hostSymbol}${h.value}${hostSymbol}`
545+
}
546+
/ 'DEFINER'i __ KW_ASSIGIN_EQUAL __ KW_CURRENT_USER __ LPAREN __ RPAREN {
547+
return `DEFINER = CURRENT_USER()`
548+
}
549+
/ 'DEFINER'i __ KW_ASSIGIN_EQUAL __ KW_CURRENT_USER {
550+
return `DEFINER = CURRENT_USER`
551+
}
552+
trigger_time
553+
= 'BEFORE'i / 'AFTER'i
554+
trigger_event
555+
= KW_INSERT / KW_UPDATE / KW_DELETE
556+
trigger_order
557+
= f:('FOLLOWS'i / 'PRECEDES'i) __ t:ident_name {
558+
return {
559+
keyword: f,
560+
trigger: t
561+
}
562+
}
563+
trigger_body
564+
= KW_SET __ s:set_list {
565+
return {
566+
type: 'set',
567+
trigger: s
568+
}
569+
}
570+
571+
create_trigger_stmt
572+
= a:KW_CREATE __
573+
df:trigger_definer? __
574+
KW_TRIGGER __
575+
ife:if_not_exists_stmt? __
576+
t:ident_name __
577+
tt:trigger_time __
578+
te:trigger_event __
579+
KW_ON __ tb:table_name __ 'FOR'i __ 'EACH'i __ 'ROW'i __
580+
tr:trigger_order? __
581+
tbo:trigger_body __ {
582+
return {
583+
tableList: Array.from(tableList),
584+
columnList: columnListTableAlias(columnList),
585+
ast: {
586+
type: a[0].toLowerCase(),
587+
definer: df,
588+
keyword: 'trigger',
589+
for_each: 'for each row',
590+
if_not_exists: ife,
591+
trigger: t,
592+
trigger_time: tt,
593+
trigger_event: te[0],
594+
trigger_order: tr,
595+
table: tb,
596+
trigger_body: tbo,
597+
}
598+
}
599+
}
600+
539601
collate_expr
540602
= KW_COLLATE __ s:KW_ASSIGIN_EQUAL? __ ca:ident_name {
541603
return {
@@ -656,6 +718,24 @@ drop_stmt
656718
}
657719
};
658720
}
721+
/ a:KW_DROP __
722+
r:KW_TRIGGER __
723+
ife:if_exists? __
724+
t:table_base {
725+
return {
726+
tableList: Array.from(tableList),
727+
columnList: columnListTableAlias(columnList),
728+
ast: {
729+
type: a.toLowerCase(),
730+
keyword: r.toLowerCase(),
731+
prefix: ife,
732+
name: [{
733+
schema: t.db,
734+
trigger: t.table
735+
}]
736+
}
737+
};
738+
}
659739

660740
truncate_stmt
661741
= a:KW_TRUNCATE __
@@ -2860,6 +2940,7 @@ KW_LOCK = "LOCK"i !ident_start
28602940

28612941
KW_AS = "AS"i !ident_start
28622942
KW_TABLE = "TABLE"i !ident_start { return 'TABLE'; }
2943+
KW_TRIGGER = "TRIGGER"i !ident_start { return 'TRIGGER'; }
28632944
KW_TABLES = "TABLES"i !ident_start { return 'TABLES'; }
28642945
KW_DATABASE = "DATABASE"i !ident_start { return 'DATABASE'; }
28652946
KW_SCHEMA = "SCHEMA"i !ident_start { return 'SCHEMA'; }

‎pegjs/mysql.pegjs‎

Lines changed: 83 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -423,6 +423,7 @@ cmd_stmt
423423

424424
create_stmt
425425
= create_table_stmt
426+
/ create_trigger_stmt
426427
/ create_index_stmt
427428
/ create_db_stmt
428429
/ create_view_stmt
@@ -538,7 +539,7 @@ create_view_stmt
538539
= a:KW_CREATE __
539540
or:(KW_OR __ KW_REPLACE)? __
540541
al:("ALGORITHM"i __ KW_ASSIGIN_EQUAL __ ("UNDEFINED"i / "MERGE"i / "TEMPTABLE"i))? __
541-
df:("DEFINER"i __ KW_ASSIGIN_EQUAL __ ident)? __
542+
df:trigger_definer? __
542543
ss:("SQL"i __ "SECURITY"i __ ("DEFINER"i / "INVOKER"i))? __
543544
KW_VIEW __ v:table_name __ c:(LPAREN __ column_list __ RPAREN)? __
544545
KW_AS __ s:select_stmt_nake __
@@ -553,7 +554,7 @@ create_view_stmt
553554
keyword: 'view',
554555
replace: or && 'or replace',
555556
algorithm: al && al[4],
556-
definer: df && df[4],
557+
definer: df,
557558
sql_security: ss && ss[4],
558559
columns: c && c[2],
559560
select: s,
@@ -734,6 +735,67 @@ create_column_definition
734735
}
735736
}
736737

738+
trigger_definer
739+
= 'DEFINER'i __ KW_ASSIGIN_EQUAL __ u:literal_string __ '@' __ h:literal_string {
740+
const userNameSymbol = u.type === 'single_quote_string' ? '\'' : '"'
741+
const hostSymbol = h.type === 'single_quote_string' ? '\'' : '"'
742+
return `DEFINER = ${userNameSymbol}${u.value}${userNameSymbol}@${hostSymbol}${h.value}${hostSymbol}`
743+
}
744+
/ 'DEFINER'i __ KW_ASSIGIN_EQUAL __ KW_CURRENT_USER __ LPAREN __ RPAREN {
745+
return `DEFINER = CURRENT_USER()`
746+
}
747+
/ 'DEFINER'i __ KW_ASSIGIN_EQUAL __ KW_CURRENT_USER {
748+
return `DEFINER = CURRENT_USER`
749+
}
750+
trigger_time
751+
= 'BEFORE'i / 'AFTER'i
752+
trigger_event
753+
= KW_INSERT / KW_UPDATE / KW_DELETE
754+
trigger_order
755+
= f:('FOLLOWS'i / 'PRECEDES'i) __ t:ident_name {
756+
return {
757+
keyword: f,
758+
trigger: t
759+
}
760+
}
761+
trigger_body
762+
= KW_SET __ s:set_list {
763+
return {
764+
type: 'set',
765+
trigger: s
766+
}
767+
}
768+
769+
create_trigger_stmt
770+
= a:KW_CREATE __
771+
df:trigger_definer? __
772+
KW_TRIGGER __
773+
ife:if_not_exists_stmt? __
774+
t:ident_name __
775+
tt:trigger_time __
776+
te:trigger_event __
777+
KW_ON __ tb:table_name __ 'FOR'i __ 'EACH'i __ 'ROW'i __
778+
tr:trigger_order? __
779+
tbo:trigger_body __ {
780+
return {
781+
tableList: Array.from(tableList),
782+
columnList: columnListTableAlias(columnList),
783+
ast: {
784+
type: a[0].toLowerCase(),
785+
definer: df,
786+
keyword: 'trigger',
787+
for_each: 'for each row',
788+
if_not_exists: ife,
789+
trigger: t,
790+
trigger_time: tt,
791+
trigger_event: te[0],
792+
trigger_order: tr,
793+
table: tb,
794+
trigger_body: tbo,
795+
}
796+
}
797+
}
798+
737799
collate_expr
738800
= KW_COLLATE __ s:KW_ASSIGIN_EQUAL? __ ca:ident_name {
739801
return {
@@ -856,6 +918,24 @@ drop_stmt
856918
}
857919
};
858920
}
921+
/ a:KW_DROP __
922+
r:KW_TRIGGER __
923+
ife:if_exists? __
924+
t:table_base {
925+
return {
926+
tableList: Array.from(tableList),
927+
columnList: columnListTableAlias(columnList),
928+
ast: {
929+
type: a.toLowerCase(),
930+
keyword: r.toLowerCase(),
931+
prefix: ife,
932+
name: [{
933+
schema: t.db,
934+
trigger: t.table
935+
}]
936+
}
937+
};
938+
}
859939

860940
truncate_stmt
861941
= a:KW_TRUNCATE __
@@ -3157,6 +3237,7 @@ KW_LOCK = "LOCK"i !ident_start
31573237

31583238
KW_AS = "AS"i !ident_start
31593239
KW_TABLE = "TABLE"i !ident_start { return 'TABLE'; }
3240+
KW_TRIGGER = "TRIGGER"i !ident_start { return 'TRIGGER'; }
31603241
KW_TABLES = "TABLES"i !ident_start { return 'TABLES'; }
31613242
KW_DATABASE = "DATABASE"i !ident_start { return 'DATABASE'; }
31623243
KW_SCHEMA = "SCHEMA"i !ident_start { return 'SCHEMA'; }

‎src/command.js‎

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,9 @@ function commonCmdToSQL(stmt) {
1717
case 'table':
1818
clauses.push(tablesToSQL(name))
1919
break
20+
case 'trigger':
21+
clauses.push([name[0].schema ? `${identifierToSql(name[0].schema)}.` : '', identifierToSql(name[0].trigger)].filter(hasVal).join(''))
22+
break
2023
case 'database':
2124
case 'schema':
2225
case 'procedure':
@@ -26,11 +29,7 @@ function commonCmdToSQL(stmt) {
2629
clauses.push(tablesToSQL(name), stmt.options && stmt.options.map(exprToSQL).filter(hasVal).join(' '))
2730
break
2831
case 'index':
29-
clauses.push(
30-
columnRefToSQL(name),
31-
...stmt.table ? ['ON', tableToSQL(stmt.table)] : [],
32-
stmt.options && stmt.options.map(exprToSQL).filter(hasVal).join(' ')
33-
)
32+
clauses.push(columnRefToSQL(name), ...stmt.table ? ['ON', tableToSQL(stmt.table)] : [], stmt.options && stmt.options.map(exprToSQL).filter(hasVal).join(' '))
3433
break
3534
default:
3635
break

‎src/create.js‎

Lines changed: 26 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@ import { columnDefinitionToSQL } from './column'
55
import { constraintDefinitionToSQL } from './constrain'
66
import { funcToSQL } from './func'
77
import { tablesToSQL, tableOptionToSQL, tableToSQL } from './tables'
8+
import { setToSQL } from './update'
89
import { unionToSQL } from './union'
910
import { columnIdentifierToSql, columnOrderListToSQL, commonOptionConnector, commonKeywordArgsToSQL, toUpper, hasVal, identifierToSql, triggerEventToSQL, literalToSQL } from './util'
1011

@@ -53,6 +54,29 @@ function createTableToSQL(stmt) {
5354
}
5455

5556
function createTriggerToSQL(stmt) {
57+
const {
58+
definer, for_each: forEach, keyword,
59+
type, table, if_not_exists: ife,
60+
trigger, trigger_event: triggerEvent,
61+
trigger_order: triggerOrder, trigger_time: triggerTime,
62+
trigger_body: triggerBody,
63+
} = stmt
64+
const sql = [
65+
toUpper(type), definer, toUpper(keyword),
66+
toUpper(ife), identifierToSql(trigger),
67+
toUpper(triggerTime), toUpper(triggerEvent),
68+
'ON', tableToSQL(table), toUpper(forEach),
69+
triggerOrder && `${toUpper(triggerOrder.keyword)} ${identifierToSql(triggerOrder.trigger)}`,
70+
]
71+
switch (triggerBody.type) {
72+
case 'set':
73+
sql.push(commonOptionConnector('SET', setToSQL, triggerBody.trigger))
74+
break
75+
}
76+
return sql.filter(hasVal).join(' ')
77+
}
78+
79+
function createConstraintTriggerToSQL(stmt) {
5680
const {
5781
constraint, constraint_kw: constraintKw,
5882
deferrable,
@@ -159,7 +183,7 @@ function createViewToSQL(stmt) {
159183
toUpper(type),
160184
toUpper(replace),
161185
algorithm && `ALGORITHM = ${toUpper(algorithm)}`,
162-
definer && `DEFINER = ${definer}`,
186+
definer,
163187
sqlSecurity && `SQL SECURITY ${toUpper(sqlSecurity)}`,
164188
toUpper(keyword),
165189
viewName,
@@ -179,7 +203,7 @@ function createToSQL(stmt) {
179203
sql = createTableToSQL(stmt)
180204
break
181205
case 'trigger':
182-
sql = createTriggerToSQL(stmt)
206+
sql = stmt.resource === 'constraint' ? createConstraintTriggerToSQL(stmt) : createTriggerToSQL(stmt)
183207
break
184208
case 'extension':
185209
sql = createExtensionToSQL(stmt)

‎test/create.spec.js‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -603,8 +603,8 @@ describe('create', () => {
603603
expect(getParsedSql("CREATE VIEW v (mycol) AS SELECT 'abc'")).to.equal("CREATE VIEW `v` (`mycol`) AS SELECT 'abc'")
604604
})
605605
it('should support optional setting', () => {
606-
expect(getParsedSql('CREATE OR REPLACE ALGORITHM = UNDEFINED DEFINER = user SQL SECURITY INVOKER VIEW test.v AS SELECT * FROM t WITH CHECK OPTION;')).to.equal('CREATE OR REPLACE ALGORITHM = UNDEFINED DEFINER = user SQL SECURITY INVOKER VIEW `test`.`v` AS SELECT * FROM `t` WITH CHECK OPTION')
607-
expect(getParsedSql('CREATE OR REPLACE ALGORITHM = MERGE DEFINER = user SQL SECURITY INVOKER VIEW test.v AS SELECT * FROM t WITH CASCADED CHECK OPTION;')).to.equal('CREATE OR REPLACE ALGORITHM = MERGE DEFINER = user SQL SECURITY INVOKER VIEW `test`.`v` AS SELECT * FROM `t` WITH CASCADED CHECK OPTION')
606+
expect(getParsedSql('CREATE OR REPLACE ALGORITHM = UNDEFINED DEFINER = "abc"@"localhost" SQL SECURITY INVOKER VIEW test.v AS SELECT * FROM t WITH CHECK OPTION;')).to.equal('CREATE OR REPLACE ALGORITHM = UNDEFINED DEFINER = "abc"@"localhost" SQL SECURITY INVOKER VIEW `test`.`v` AS SELECT * FROM `t` WITH CHECK OPTION')
607+
expect(getParsedSql('CREATE OR REPLACE ALGORITHM = MERGE DEFINER = \'abc\'@\'localhost\' SQL SECURITY INVOKER VIEW test.v AS SELECT * FROM t WITH CASCADED CHECK OPTION;')).to.equal('CREATE OR REPLACE ALGORITHM = MERGE DEFINER = \'abc\'@\'localhost\' SQL SECURITY INVOKER VIEW `test`.`v` AS SELECT * FROM `t` WITH CASCADED CHECK OPTION')
608608
})
609609
})
610610
it('throw error when create type is unknown', () => {

‎test/mysql-mariadb.spec.js‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -608,6 +608,34 @@ describe('mysql', () => {
608608
'SELECT CONVERT(150, CHAR)'
609609
]
610610
},
611+
{
612+
title: 'drop trigger',
613+
sql: [
614+
'drop trigger schema1.trigger1',
615+
'DROP TRIGGER `schema1`.`trigger1`'
616+
]
617+
},
618+
{
619+
title: 'drop trigger if exists',
620+
sql: [
621+
'drop trigger if exists trigger1',
622+
'DROP TRIGGER IF EXISTS `trigger1`'
623+
]
624+
},
625+
{
626+
title: 'create trigger',
627+
sql: [
628+
'create trigger trigger1 before update on merge for each row set NEW.updated_at = current_timestamp()',
629+
'CREATE TRIGGER `trigger1` BEFORE UPDATE ON `merge` FOR EACH ROW SET `NEW`.`updated_at` = current_timestamp()'
630+
]
631+
},
632+
{
633+
title: 'create trigger with trigger order',
634+
sql: [
635+
'create trigger trigger1 before update on merge for each row follows trigger2 set NEW.updated_at = current_timestamp()',
636+
'CREATE TRIGGER `trigger1` BEFORE UPDATE ON `merge` FOR EACH ROW FOLLOWS `trigger2` SET `NEW`.`updated_at` = current_timestamp()'
637+
]
638+
},
611639
]
612640
SQL_LIST.forEach(sqlInfo => {
613641
const { title, sql } = sqlInfo

0 commit comments

Comments
 (0)