michaelsingleton has asked for the wisdom of the Perl Monks concerning the following question:

First, thanks in advance! I am rather new to Perl and need to some help with a particular section of a short script written another so I can fix it for my problem. I'm extracting sqlite blob data. ==Begin Script== 

#!/usr/bin/perl
use strict;
use vars qw { $FILE };

$FILE = shift;
if ($FILE eq "") {
    die "Syntax: $0 [filename]\n";
}

&parse($FILE);

sub parse {
    my($FILE) = @_;
    open(FILE, "<$FILE") || die "$FILE: $!";
    mkdir("./maptiles-output", 0755);
    while(<FILE>) {
# This is part I'm trying to figure out 
       chomp;
        my $j = 0;
        my $contents = $_;
        next unless ($contents =~ /^INSERT /);
        my ($junk1, $sql, $junk) = split(/\(|\)/, $contents);
        my ($zoom, $x, $y, $flags, $length, $data) = split(/\,/, $sql)
+;
        $data =~ s/^X'//;
        $data =~ s/'$//;
        my $filename = "./maptiles-output/$x,$y\@$zoom.png";
        next if int(length($data))<128;
        print $filename . "\n";
        open(OUT, ">$filename") || die "$filename: $!";
        print int(length($data)) . "\n";
        while($j < length($data)) {
            my $hex = "0x" . substr($data, $j, 2);
            print OUT chr(hex($hex));
            $j += 2;
        }
        close(OUT);
    }
    close(FILE);
}

[download]

Replies are listed 'Best First'.
Re: help figuring out what a section of script does
by Corion (Patriarch) on Mar 19, 2017 at 15:21 UTC 
        while(<FILE>) { # this reads through $FILE, line by line
       chomp;       # remove end of line character
        my $j = 0;
        my $contents = $_;
        next unless ($contents =~ /^INSERT /); # process only lines wh
+ich start with INSERT
        my ($junk1, $sql, $junk) = split(/\(|\)/, $contents); # get th
+e values from what could be an SQL INSERT statement?!
        my ($zoom, $x, $y, $flags, $length, $data) = split(/\,/, $sql)
+; # get the columns
        $data =~ s/^X'//; # remove X' at start
        $data =~ s/'$//; # remove single quote at end
        my $filename = "./maptiles-output/$x,$y\@$zoom.png";
        next if int(length($data))<128; # skip bogus data?

        # Write what appears to be hex-encoded data to a PNG file?!
        print $filename . "\n";
        open(OUT, ">$filename") || die "$filename: $!";
        print int(length($data)) . "\n";
        while($j < length($data)) {
            my $hex = "0x" . substr($data, $j, 2);
            print OUT chr(hex($hex));
            $j += 2;
        }
        close(OUT);
    }

    [download]

    Maybe you want a look at SQL::Parser for parsing the SQL?

[reply]
[d/l]

      That awesome, and yes it's an output of BLOB SQLite data (unknown format so far, I think IOS 8 just directly writes the map image to SQLlite directly). So far, I get no output so I'm still figuring that out. I'll try the SQLParser too

[reply]
Re: help figuring out what a section of script does
by Marshall (Canon) on Mar 20, 2017 at 00:28 UTC
    You say that, "I'm extracting sqlite blob data".

    That is a fine idea, but I figure that your approach is wrong.

    If you are extracting a piece of data from an SQLite DB, you should use the Perl DBI (which automatically will load support for SQLite) to do that instead of parsing the binary file yourself.

    An important thing is to have a good SQLite DB viewer. I use a Firefox plug-in, Firefox SQLite Manager. There are other possibilities.

    I think what you want to do are SQL statement(s) that will read the BLOB's (Binary Large Object) as a single Perl variable, like $blob. Once you have this .png image or whatever this BLOB represents, then display it.

    I highly recommend against parsing the SQLite binary yourself.

[reply]

      Let me give that a whirl! Thanks

[reply]
Re: help figuring out what a section of script does
by kcott (Archbishop) on Mar 19, 2017 at 14:50 UTC

    G'day michaelsingleton,

    Welcome to the Monastery.

    While myself and others would be more than happy to help you, your post, as it is currently presented — all code and commentary as a single line of paragraph text — is virtually unreadable.

    Please put plain text in <p>...</p> tags; and code and data in <code>...</code> tags.

    There's more information about this in "Writeup Formatting Tips" and "How do I post a question effectively?".

    — Ken

[reply]
[d/l]
[select]
Re: help figuring out what a section of script does
by Anonymous Monk on Mar 19, 2017 at 15:29 UTC
    It's reading a file of sql insert statements like this: 
    INSERT INTO FOO VALUES (1,2,3,4,5,X'6789')

    [download]
    It uses the first three numbers to build a filename. The X'' part at the end is hexadecimal-coded. It decodes that part and writes it to the file. The decode loop of the script would be better written as 
    print OUT unpack("H*",$data);

    [download]
    But that's as may be.
[reply]
[d/l]
[select]
[reply]
[d/l]

        True, but in all fairness I'm new, so I need help identifying what fish I need to fish for ;). At least I'm learning to fish in the Perl pond.

[reply]
Re: help figuring out what a section of script does
by LanX (Saint) on Mar 19, 2017 at 14:42 UTC
    Hi

    Posts are HTML formatted.

    please put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

    thanks :)

    Cheers Rolf
    (addicted to the Perl Programming Language and ☆☆☆☆ :)
    Je suis Charlie!

[reply]
[d/l]
[select]
Re: help figuring out what a section of script does
by pme (Monsignor) on Mar 19, 2017 at 15:14 UTC
[reply]

      Sure! There are two BLOB fields, and I think this one is the image data (as it's labeled 'image' lol)

       564D50340000070001004E0000000A0000000A0058000000850000000B00DD0000000B0000001400E80000001C0300001F0004040000AA0000003C00AE040000
[reply]
[d/l]

        The start of that string is VMP4 in ASCII. It that the complete data or just a part of it ?

        poj
[reply]
[d/l]

Back to Seekers of Perl Wisdom