Hey people!

 I'm looking for some help in creating a reg exp that will find any create table and its associated inserts from a mysqldump file.

The strutcture of the dump is as follows:

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `fname` varchar(255) NOT NULL,
  `lname` varchar(255) NOT NULL
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (<snip>)
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `products`
--
etc

So i'm thinking a pattern that will get everything between one "--Table Structure" comment and the next, can be considered matching data. How can i make a pattern that will catch this much data? Is it possible?


 

How about a pattern of:

--\s+-- Table structure for table `\w+`\s+--

and use the regex 'split' function. Each response will  be the text you are after (plus possible extraneous items at the start  which represent the text before the first heading - there may also be junk in the last entry depending on the way the file is constructed).

Susan 

Thank you for the reply. Unfortunately it does not get the data i am after, although i am slightly unsure by what you mean by the split function - i am using php so used preg_split (i got an empty array):

print_r(preg_split('/--\s+-- Table structure for table `\w+`\s+--/', $dumpContent));
 

Using preg_match_all worked better, but only matched the first line - the "-- Table Structure for table x --":

preg_match_all('/--\s+-- Table structure for table `\w+`\s+--/', $contents, $matches);

I need the table creation statement and the inserts underneath it, to be considered as a single match, with only the following "-- Table Structure..." statement as a marker for the end of the current match and beginning of the next one. It sounds so simple but has been quite fiendish. I am able to obtain just the create table statements with:

create table[^(]+/im 

 ...but this does not match the insert statements, which i also need (and preferably the junk / lock table / charset statements too).

 

 

 

 

<?php
$sourcestring="your source string";
preg_match_all('/-- Table structure for table .*?(?=-- Table structure for table |$)/s',$sourcestring,$matches);
echo "<pre>".print_r($matches,true);
?>

---

looking for a new regex book?
Regular Expressions Cookbook

Thanks for offering that, but it matches everything from the first " -- Table Structure" comment to the end of the file into a single match :(

Using Doug's pattern in the PHP regex tester (http://nancywalshee03.freehostia.com/regextester/regex_tester.php) with your test data produces

Array
(
    [0] => Array
        (
            [0] => -- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `fname` varchar(255) NOT NULL,
  `lname` varchar(255) NOT NULL
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (<snip>)
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

--

            [1] => -- Table structure for table `products`
--
etc
        )

)

 

This looks like what you are after. As you are having problems with both our suggestions, I suspect that there may be something with way your source text is created. but I'm not sure what!

Susan

 

It's interesting you say that... i was just writing this:

-{1,2}\n?[-]+\s[a-zA-Z\s`;@_=\d(),'\/\*.!?-]+\s? 

 (be kind, i'm no expert!) and it was matching things pretty well, until it got to the insert statements - those fields contain all sorts of characters (as you can see i tried adding each and every one to the rule!) - the insert statements were not in the example, so that is, i think, where i'm confusing you all. I cannot post the insert statements as they contains users personal information, but if you see where i'm coming from with the above rule, how can i allow absolutely any character (as in $, *  ( ) - _ / ? < > .@ # , i'm currently stuck on a colon etc) to be matched, as a lot of the insert values contain at least some of these i'm sure?

 ps. by the way, i was using that myregext tester site to to create the above statement - excellent, excellent site. I can now get rid of regex coach!

very very strange... i copied and pasted some of the dump (including insert statements) to the myregex tester site, and as you said, Dougs pattern works like a charm... yet the same statement on my server only returns a single array item. To clarify, my code is as follows:

            $handle = fopen($backupFile, "r");
            $contents = fread($handle, filesize($backupFile));
            fclose($handle);          
            $matches = array();
            preg_match_all('/-- Table structure for table .*?(?=-- Table structure for table |$)/s', $contents, $matches);
            print_r($matches);
 

I'm sure nothing looks out of the ordinary there - what's going wrong where? Thanks for all your help getting me this far.

ignore my posts (it's 5 am here!) everything works perfectly.

 

Thank you two for the help, its very much appreciated :) 

hmmm i think i've come across something that's interfering with the matching.

 

Basically the matching stops after this entry:

--
-- Table structure for table `combo_desc`
--

DROP TABLE IF EXISTS `combo_desc`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `combo_desc` (
  `id` int(4) unsigned NOT NULL auto_increment,
  `combo_name` varchar(200) collate latin1_general_ci default NULL,
  `field_name` varchar(100) collate latin1_general_ci default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `combo_desc`
--

LOCK TABLES `combo_desc` WRITE;
/*!40000 ALTER TABLE `combo_desc` DISABLE KEYS */;
INSERT INTO `combo_desc` VALUES (1,'Rebuild Ring','rebuild_ring'),(2,'NODE','node'),(3,'Fibre/RF','fibre'),(4,'Build Type','t2_type'),(5,'Locality','locality'),(6,'Town','town'),(7,'County','county'),(8,'Reason','reason'),(9,'Way Leave Status','way_leave_status'),(10,'Way Leaver and Contact','way_leaver_contact'),(11,'T2 Status','t2_status'),(12,'Council','council'),(13,'By Whom','by_whom'),(14,'Completion Status','completion_status'),(15,'Council Walk off','council_walk'),(21,'Activity Type','drawing_issue'),(20,'Variation Order','variation_order'),(22,'Concession','concession'),(23,'Concession Number','concession_number'),(24,'Concession Status','concession_status'),(25,'Concession Document','concession_document');
/*!40000 ALTER TABLE `combo_desc` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `information`
--
 

 

...basically, it never reaches the "Table Structure for table `data`" it is a humungous entry in the sql file, is it reaching a memory limit? - could i perhaps send you a copy of the sql file so you could see what i mean? Really puzzled by this.

 

How big is the text file you are searching?

---

looking for a new regex book?
Regular Expressions Cookbook

the file is only 2,007kb, uncompressed. Rar'd it is about 200kb.

The section that seems to hold everything up i think might be because of the prescence of slashes in the values? Would that be prematurely terminating the pattern matching? 

Is the file available by URL?

---

looking for a new regex book?
Regular Expressions Cookbook

no, i'm developing locally. can i send it through here?

No, use some service such as:

http://www.driveway.com/

Click on browse and select the file to upload no e-mail addr/tags are required.  Then post the link result here.

---

looking for a new regex book?
Regular Expressions Cookbook