Support DELIMITER syntax

[Awk34]

Disclaimer: I don't know a whole lot of SQL

So my dev team decided that we wanted a Node script that could drop and re-create all of our stored procedures whenever something changes. The problem was that this library doesn't support the delimiter syntax. I made a kind of hacky way to split statements from my .sql (loaded to an in-memory string) file by custom delimiters into an array of separate statements.

Do you guys have a suggested way of working with something like this? How about suggestions for where to start for a PR?

Ex:

DELIMITER $$
DROP PROCEDURE IF EXISTS `Thing_Insert`$$
CREATE PROCEDURE `Thing_Insert`(
	argA int,
	argB nvarchar(50),
	argC nvarchar(50),
	argD datetime,
	argE bit)
BEGIN

	insert into Thing (A, B, C, D, E)
		select argA, argB, argC, argD, argE;

END$$
DELIMITER ;

...etc

/**
 * Separates individual SQL statements, split by a delimiter, into an array of statements
 * @param {String} text
 * @returns {String[]}
 */
function separateSqlCommands(text) {
    let lines = text.split('\n')
        .filter(str => {
            return str.length
                && !/^\n*$/.test(str);
        });

    let delimiter = ';';
    const DELIMITER_REGEX = /DELIMITER (.+)/;

    let statements = [];

    let accumulator = '';

    for(const line of lines) {
        let regexpResult = DELIMITER_REGEX.exec(line);
        if(regexpResult) {
            statements = statements.concat(accumulator.split(delimiter));

            accumulator = '';
            delimiter = regexpResult[1];
        } else {
            accumulator += line + ' ';
        }
    }

    statements = statements.filter(statement => !/^\s*$/.test(statement))
        .map(statement => {
            if(statement[0] === ' ') statement = statement.substr(1);

            return statement.replace(/\t/g, ' ')
                .replace(/ +/g, ' ');
        })
        .map(statement => statement.replace(/ +/g, ' '));

    return statements;
}

example output:

[ 'drop procedure `Thing_Insert`',
  'CREATE PROCEDURE `Thing_Insert`( argA int, argB nvarchar(50), argC nvarchar(50), argD datetime, argE bit) BEGIN insert into Thing (A, B, C, D, E) select argA, argB, argC, argD, argE; END' ]

[uldissturms]

👍 Having the same issue.
Seems to be linked to #1653 and #280.
Not sure if this will get implemented, will try and call mysql client directly for time being.

[kpetrow]

I think you should use DROP PROCEDURE IF EXISTS -- just in case it aint there
and simple batch script:
mysql -u username -p database_name < file.sql
no need for node or mysql module

[Awk34]

@kpetrow while using the mysql CLI directly is always an option, the point here is that one might want to extract out the individual SQL commands and programmatically work with them.

I've updated my OP to use DROP PROCEDURE IF EXISTS

[kpetrow]

If you are going to work with individual statements then you are going to need to manually parse anyways? If you are going to slam into db use CLI? But i get your point...

[dougwilson]

The hard part about this is that all this module does right now is implement the MySQL protocol, and supporting DELIMITER means that the module would have to implement and entire SQL dialect parser to get that added, but if you want to put a PR together, that's certainly good! I know the separateSqlCommands is just what you are using now, so I'm sure you're not surprised that it has a few edge cases that would have to be fixed to actually add it here :)

Do you guys have a suggested way of working with something like this?

I would suggest that if you don't even need to keep it in a .sql file that you can load into the mysql command line client, then simply store the literal procedure content alone. For example, if you just store your procedure above as

CREATE PROCEDURE `Thing_Insert`(
	argA int,
	argB nvarchar(50),
	argC nvarchar(50),
	argD datetime,
	argE bit)
BEGIN

	insert into Thing (A, B, C, D, E)
		select argA, argB, argC, argD, argE;

END;

Then you can easily use connection.query to run that and create the procedure. You can run the drop if exists as a connection.query call before that one to get the same effect.

How about suggestions for where to start for a PR?

If you are looking to add support for DELIMITER the first step is to either find a MySQL-dialect parser or build one. Then you can add support for DELIMITER and it can be used to slice up the queries to send over the protocol.

[kpetrow]

Can you give an example of an edge case?

[dougwilson]

Hi @kpetrow , the code above has a lot of issues, the easiest to see are the following:

1. The occurrence of ADELIMITER $$ will incorrectly change the delimiter to $$.
2. The function will incorrectly match a delimiter that is within a string in the SQL.
3. The word DELIMITER is not required to be in upper-case, but this will only match upper-case occurrences.
4. The line foo DELIMITER $$ will incorrectly change the delimiter to $$.
5. The line DELIMITER "$$" will incorrectly change the delimiter to "$$" instead of $$.

I can certainly continue on, but it's really just diminishing returns when really the answer is to just fix up the edge cases by implementing it exactly how MySQL implements it.

[kpetrow]

I was thinking nested delimeters, but was curious if there was SP, trigger or something that would would need to be implemented in some odd way. Thanks

[dougwilson]

I haven't seen that, but not sure if that's even a thing. That's probably why the best answer is just to implement it exactly how the MySQL client is doing it :) Then we don't have to guess, haha. The protocol was just implemented from the MySQL protocol documentation, but I'm not familiar with any documentation around the delimiter and how it works, exactly.

[surruk51]

re-inventing the wheel is not a good idea. It presents maintenance and compatibility issues. There is a solution already. See: https://dev.mysql.com/doc/refman/5.7/en/c-api-multiple-queries.html

Why not just interface to that?

[Awk34]

@dougwilson Yeah, I was sure there would be plenty of cases where my code wouldn't always work. It was mainly a proof of concept that works well for my use case. Maybe others will find it useful.

I found a SQL parser for JS here: https://github.com/forward/sql-parser. The problem is, at this point, I'm not sure if this should be built into this library, or added as a separate module for working with SQL files using the DELIMITER syntax.

[ben-page]

I thought I'd clarify a little for anyone coming here looking for DELIMITER support. DELIMITER is a feature of the MySQL CLI, not the MySQL Server. The MySQL CLI interprets queries containing semicolons as multiple statements, splits them, and sends them to the server individually. DELIMITER allows you to control the splitting behavior.

Unfortunately for the OP, mysql doesn't have this feature. He needs it because he wants to execute .sql files generated for the MySQL CLI with mysql rather than the MySQL CLI.

Fortunately for most people, you probably don't need DELIMITER support. In most cases you can just run the queries one at a time with multipleStatements set to false. If the query contains a semicolon (such as in a CREATE PROCEDURE statement), it will not cause any problems.

For example if you split the OP's queries, they run just fine:

'use strict';
const mysql = require('mysql');

const config = {
    host: 'localhost',
    user: 'user',
    password: 'password',
    database: 'database',
    multipleStatements: false //false is the default, added for emphasis
};

const dropQuery = 'DROP PROCEDURE IF EXISTS `Thing_Insert`';
const createQuery = `CREATE PROCEDURE \`Thing_Insert\`(
        argA int,
        argB nvarchar(50),
        argC nvarchar(50),
        argD datetime,
        argE bit)
    BEGIN
    
        insert into Thing (A, B, C, D, E)
            select argA, argB, argC, argD, argE;
    
    END`;

const connection = mysql.createConnection(config);
connection.connect(function(err) {
    if (err) {
        console.error('error connecting: ' + err.stack);
        return;
    }
    
    connection.query(dropQuery, function(err2) {
        if (err2) {
            console.error('error dropping: ' + err2.stack);
            return;
        }
        
        console.log('drop succeeded');
        
        connection.query(createQuery, function(err3) {
            if (err3) {
                console.error('error creating: ' + err3.stack);
                return;
            }
    
            console.log('create succeeded');
            connection.end();
        });
    });
});

[lagden]

@ben-page and @dougwilson

Removing the DELIMITER works!!

const _sql = sql.toString().replace(/DELIMITER ;?;/gm, '').replace(/;;/gm, ';')

Thanks!