Split string by ;

Question

I've checked quite a lot, but I can't get a hang of it.

I need to split a sql dump into queries.

what I need s basically to take a string like this:

DROP TABLE IF EXISTS `GDN_Activity`;

CREATE TABLE `GDN_Activity` (
  `ActivityID` int(11) NOT NULL AUTO_INCREMENT,
  `ActivityTypeID` int(11) NOT NULL,
  `NotifyUserID` int(11) NOT NULL DEFAULT '0',
  `ActivityUserID` int(11) DEFAULT NULL,
  `RegardingUserID` int(11) DEFAULT NULL,
  `Photo` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `HeadlineFormat` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Story` text COLLATE utf8_unicode_ci,
  `Format` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Route` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `RecordType` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `RecordID` int(11) DEFAULT NULL,
  `InsertUserID` int(11) DEFAULT NULL,
  `DateInserted` datetime NOT NULL,
  `InsertIPAddress` varchar(39) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DateUpdated` datetime NOT NULL,
  `Notified` tinyint(4) NOT NULL DEFAULT '0',
  `Emailed` tinyint(4) NOT NULL DEFAULT '0',
  `Data` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`ActivityID`),
  KEY `IX_Activity_Notify` (`NotifyUserID`,`Notified`),
  KEY `IX_Activity_Recent` (`NotifyUserID`,`DateUpdated`),
  KEY `IX_Activity_Feed` (`NotifyUserID`,`ActivityUserID`,`DateUpdated`),
  KEY `IX_Activity_DateUpdated` (`DateUpdated`),
  KEY `FK_Activity_InsertUserID` (`InsertUserID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



LOCK TABLES `GDN_Activity` WRITE;
INSERT INTO `GDN_Activity` VALUES (1,17,-1,5,NULL,NULL,'{ActivityUserID,You} joined.','Welcome Aboard!',NULL,NULL,NULL,NULL,NULL,
'2014-04-30 08:53:43','127.0.0.1','2014-04-30 08:53:49',0,0,'a:1:{s:15:\"ActivityUserIDs\";a:2:{i:0;s:1:\"2\";i:1;s:1:\"1\";}}'),(2,15,-1,2,4,NULL,
'{RegardingUserID,you} &rarr; {ActivityUserID,you}',
'Ping! An activity post is a public way to talk at someone. When you update your status here, it posts it on your activity feed.','Html',
NULL,NULL,NULL,4,'2014-04-30 08:53:49',NULL,'2014-04-30 08:53:49',0,0,NULL),(3,15,-1,5,3,NULL,
'{RegardingUserID,you} &rarr; {ActivityUserID,you}',
'Ping! An activity post is a public way to talk at someone. When you update your status here, it posts it on your activity feed.','Html',
NULL,NULL,NULL,3,'2014-04-30 08:53:52',NULL,'2014-04-30 08:53:52',0,0,NULL);
UNLOCK TABLES;:-)

What i need to do is to get every query separately. The issue is that if I split the file by ; it will also split the string that contain ;, not only those that end in ;.

Like this part: a:1:{s:15:\"ActivityUserIDs\";a:2:{i:0;s:1:\"2\";i:1;s:1:\"1\";}}. I don;t want this to be split.

I have tried with both preg_match() and preg_split(), but I cannot get the desired result.

I tried this as a pattern: /[\;]+/ and multiple other patters around it, but i cannot get it to work.

I also tried to replace ; where there are no '' around it with ** and then explode it, but still no result.

thanks.

You are only interested in the `;` preceeding a newline. Split by that? (except for the smily in the last line) — ZeissS, Apr 30 '14 at 12:06
a new line after the ; is not guaranteed. That's my main issue — Indra, Apr 30 '14 at 12:22

score 5 · Accepted Answer · answered Apr 30 '14 at 12:12

5

What you are looking for is a SQL Parser

You might want to look here

answered Apr 30 '14 at 12:12

Guns

2,678
2
23
51

1

Yes! Using regexes for this stuff is fragile as hell. Even if it "works". – monocell Apr 30 '14 at 12:14
Adding a new class to the project won't work as my client was specific about only using out code – Indra Apr 30 '14 at 12:24
@Indra, You might want to study the Class specified in the answer and come up with a solution then – Guns Apr 30 '14 at 12:28
1

Thanks @Guns. I think that is the most sane solution – Indra Apr 30 '14 at 12:31

score 1 · Answer 2 · answered Apr 30 '14 at 12:11

Split by ;\n instead of just ;.

This does require each query to be on it's own line (or multiple lines). You might still get some anomalies but this is a 99% solution. Use a parser if you want to be certain. SQL is not context free so in theory a regular expression can not parse it.

$s = "test;
test2;
test3;a;
test4
;";

preg_match_all("/(.*?);(\r?\n|$)/s", $s, $matches);
var_dump($matches[1]);

gives:

array(4) {
    [0]=> string(4) "test"
    [1]=> string(5) "test2"
    [2]=> string(7) "test3;a"
    [3]=> string(7) "test4 "
}

I also tested it on your input and it seem to work fine.

Thanks. It works for the small files, but I have a very large DB. So this is only a partial solution. — Indra, Apr 30 '14 at 12:33

Split string by ;

2 Answers2