Hello Monks

I am trying to parse a (My)SQL INSERT statement's VALUES section and convert it to a Perl Data Structure. Ideally into an array of hashes, each hash being a table row. My data is produced by mysqldump --no-tablespaces.

The order of data in each of the VALUES row (within a pair of brackets) is the same as the order of columns specified in the CREATE just above. So I parse the CREATE (lamely) in order to find the column names. Then I must parse, edit: and convert into a Perl array, the VALUES section which looks like a Perl array, (1,2,3),(4,5,6), and so, lazily, I do an eval which relieves me from dealing with what's inside the VALUES quoted strings (which can be anything, html etc.)

So, my question is what is the best way to parse (My)SQL and create an array of hashes for each table's data+structure. Ideally I would prefer a CPAN module (I tried SQL::Parser but it failed to read the whole SQL dump, complained about "---"). But my eval works with my dump (so far).

Here is a gist of what I do in a large scale:

my $tablename = 'mytable';

my $sqlcontent = <<'EOS';
/* create statement before the insert */
CREATE TABLE `mytable` (
  `id` int,
  `content` varchar(1000)
)  ENGINE=InnoDB;

INSERT INTO `mytable` VALUES
  (1,'abc\"xyz\"'),(2,'blah')
;
UNLOCK TABLES;
EOS

my ($statements, $values)
  = $sqlcontent
  =~ /CREATE\s+TABLE\s+`${tablename}`\s+(\(.+\))\s+ENGINE=.+?;\s+
      .+?INSERT\s+INTO\s+`${tablename}`\s+VALUES\s+(.+?);\s+    
      .*?UNLOCK\s+TABLES;
     /sx
;

# Below is problematic but works for restricted cases
my @values = eval $values;
die $@ if $@;
print join("\n", @values)."\n";

[download]

This small issue is part of my efforts to upgrade a DB schema (by adding or removing table columns), processing the database data and inserting it back into the DB

In reply to Convert an SQL insert into a Perl data structure by bliako

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.