[RESOLVED] Preg_match and creating SQL strings...

[bpat1434]

I'm just trying to take a file (it has a bunch of SQL stored procedures) and parse it and execute it via mysql_query. I've got the general SQL stuff working, so I thought I could adapt it. So here's what I've got:

<?php
	$spcs = glob('./install/*.sproc');
	natsort($spcs);
	$tmp = realpath('./install');
	if(substr($tmp, -1) == '/')
		$tmp = substr($tmp, 0, -1);

foreach($spcs as $item)
{
	echo '<h2>' . $item . '</h2>';
	$sql = file_get_contents(realpath($item));
	$lines = explode("\n", $sql);
	$current_query = $delimiter = '';
	$failure = array();
	foreach($lines as $count=>$line)
	{
		if(substr($line, 0, 9) == 'DELIMITER')
		{
			$current_query = trim($line);
			if(preg_match('~^DELIMITER (.*?)$~s', $line, $matches, $err, $err))
				$delimiter = trim($matches[1]);

			echo '<strong>Delimiter:</strong> ' . $delimiter . '<br />';

			$current_query = '';
			continue;
		}

		if(substr($line, 0, 2) != '--')
			$current_query .= "\n" . trim($line);

		$pattern = '~'.$delimiter.'[\s]*$~s';
		if(empty($current_query) || preg_match($pattern, $line) == 0 && $count != count($lines)) { continue; }

		echo $current_query . '<hr />
';
			$current_query = '';
		}
	}
?>

Now, unfortunately this makes each line be a different query instead of having like 10 different procedures group across 100 lines..

You can use this as a sample stored procedure file (I keep the stored procedures in <filename>.sproc files).

DELIMITER |

DROP PROCEDURE IF EXISTS addBoycott;|

CREATE PROCEDURE addBoycott (IN orgID SMALLINT,IN userID SMALLINT,IN boycottName VARCHAR(60),IN boycottDesc LONGTEXT,IN endDate Date, IN goalAmount SMALLINT, IN invitationMessage LONGTEXT, IN thankyouMessage LONGTEXT, IN recipientFormalName TEXT, IN recipientEmail TEXT, IN instructions LONGTEXT, IN messageSubject TEXT, IN messageHeader LONGTEXT, IN messageBody LONGTEXT, IN messageFooter LONGTEXT, IN talkingPoints TEXT, OUT boycottID SMALLINT)
BEGIN
    DECLARE error_msg TEXT;
    DECLARE lineageID SMALLINT;
    DECLARE campaignID SMALLINT;
    DECLARE userCampaignID SMALLINT;
    DECLARE `Constraint Violation`
        CONDITION FOR SQLSTATE '23000';

DECLARE EXIT HANDLER FOR
    `Constraint Violation` ROLLBACK;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;
    INSERT INTO `boycott` (`org_id`, `user_id`, `name`, `description`, `end_date`, `goal_amount`, `invitation_message`, `thankyou_message`, `recipient_formal_name`, `recipient_email`, `instructions`, `message_subject`, `message_header`, `message_body`, `message_footer`, `talking_points`)
        VALUES (orgID, userID, boycottName, boycottDesc, endDate, goalAmount, invitationMessage, thankyouMessage, recipientFormalName, recipientEmail, instructions, messageSubject, messageHeader, messageBody, messageFooter, talkingPoints);

    SET boycottID = last_insert_id();

    CALL addBoycottLineage(userID, boycottID, userID, lineageID);

    INSERT INTO `campaign` (`fk_id`, `type_id`, `name`)
        VALUES (boycottID, 1, boycottName);

    SET campaignID = last_insert_id();

    CALL addUserCampaign (userID, campaignID, goalAmount, userID, userCampaignID);
COMMIT;
END|

Any ideas why it's not being grouped right? i.e. it's spitting out each line instead of groups like:

[code]DELIMITER |

DROP PROCEDURE IF EXISTS addBoycott;|

CREATE PROCEDURE addBoycott (IN orgID SMALLINT,IN userID SMALLINT,IN boycottName VARCHAR(60),IN boycottDesc LONGTEXT,IN endDate Date, IN goalAmount SMALLINT, IN invitationMessage LONGTEXT, IN thankyouMessage LONGTEXT, IN recipientFormalName TEXT, IN recipientEmail TEXT, IN instructions LONGTEXT, IN messageSubject TEXT, IN messageHeader LONGTEXT, IN messageBody LONGTEXT, IN messageFooter LONGTEXT, IN talkingPoints TEXT, OUT boycottID SMALLINT)
BEGIN
DECLARE error_msg TEXT;
DECLARE lineageID SMALLINT;
DECLARE campaignID SMALLINT;
DECLARE userCampaignID SMALLINT;
DECLARE Constraint Violation
CONDITION FOR SQLSTATE '23000';

DECLARE EXIT HANDLER FOR
    `Constraint Violation` ROLLBACK;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;
    INSERT INTO `boycott` (`org_id`, `user_id`, `name`, `description`, `end_date`, `goal_amount`, `invitation_message`, `thankyou_message`, `recipient_formal_name`, `recipient_email`, `instructions`, `message_subject`, `message_header`, `message_body`, `message_footer`, `talking_points`)
        VALUES (orgID, userID, boycottName, boycottDesc, endDate, goalAmount, invitationMessage, thankyouMessage, recipientFormalName, recipientEmail, instructions, messageSubject, messageHeader, messageBody, messageFooter, talkingPoints);

    SET boycottID = last_insert_id();

    CALL addBoycottLineage(userID, boycottID, userID, lineageID);

    INSERT INTO `campaign` (`fk_id`, `type_id`, `name`)
        VALUES (boycottID, 1, boycottName);

    SET campaignID = last_insert_id();

    CALL addUserCampaign (userID, campaignID, goalAmount, userID, userCampaignID);
COMMIT;

END|[/code]

[bpat1434]

I feel like an idiot... when using a delimiter that is a "special" character, I forgot to escape it. So by escaping the proper characters ( |, $, (, ), [, ] ...) it works as expected.... don't I feel stupid....