How to do some query with sqlite.bz2 file

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

Hi everybody,

I have a sqlite database compressed with bzip2. I need to do some query into database without unzip into a file but using only a memory buffer. I tried with this code but didn't work and I didn't understand why.

use DBD::mysql;
use Time::Local;
use strict ;
use warnings ;
use IO::Uncompress::Bunzip2 qw(bunzip2 $Bunzip2Error) ;
use IO::File ;

my $input = shift;
my $buffer ;
bunzip2 $input => \$buffer, BinModeOut => 1 or die "bunzip2 failed: $B
+unzip2Error\n";
opendb(\$buffer);
exit;

sub opendb
{
    my $file = shift;
    
    my $dbsql = DBI->connect("dbi:SQLite:${file}", "", "",    {RaiseEr
+ror => 1, AutoCommit => 1});

    my $sq_1;
    my $sh_1;

    $sq_1 = "select * from tbl_xxx";
    
    $sh_1 = $dbsql->prepare($sq_1);
    $sh_1->execute();
    
    while( my $field = $sh_1->fetchrow_hashref()) {
        print "$field->{'pos'},";
    }
    print "\n";
}
[download]

Someone could help me.

Thanks you in advance.

pwp71

Comment on How to do some query with sqlite.bz2 file Download Code

Replies are listed 'Best First'.
Re: How to do some query with sqlite.bz2 file by Corion (Patriarch) on Jan 20, 2015 at 15:17 UTC
Since the C library of `sqlite` directly takes the filename, you cannot work on a compressed sqlite file directly. How do I read from a compressed SQLite FTS4 database with DBD::SQLite? shows how to compress columns in SQLite. If you do enough source diving in DBD::SQLite, you might find a way how to hand off the raw memory buffer to a SQLite `:memory:` database, but I don't see this supported handily. See the documentation for open() in SQLite.	[reply] [d/l] [select]
Re^2: How to do some query with sqlite.bz2 file by jfroebe (Parson) on Jan 20, 2015 at 15:40 UTC
Depending on the operating system, it may be doable IF you mount the compressed file with loop back and FUSE. I know it can be done with gzip files but I don't know if it can be done with bzip2 files. Jason L. Froebe Blog, Tech Blog	[reply]
Re^2: How to do some query with sqlite.bz2 file by pwp71 (Initiate) on Jan 20, 2015 at 16:04 UTC
Thanks you	[reply]
Re: How to do some query with sqlite.bz2 file by chacham (Prior) on Jan 20, 2015 at 19:00 UTC
$sq_1 = "select from tbl_xxx";* Side comment on the SQL. * should be used in EXISTS, COUNT(), and ad-hoc queries, but not elsewhere. It is good practice (and self-documenting!) to specify the required columns. It also helps avoid bugs do to unhandled columns or column reordering.	[reply]
Re: How to do some query with sqlite.bz2 file by locked_user sundialsvc4 (Abbot) on Jan 20, 2015 at 16:58 UTC
I can see that your code intends to unzip the file into a buffer, and then to open and query the contents of that buffer ... but, exactly what happened when you “tried it?” What messages did you receive? Do you know or can you determine whether, for instance, the `bunzip2` step actually worked? For that matter, [how] do you know that the contents of the zipped-file are, in fact, correct? “Never assume.™” Leave no stone face-up. And, okay, what difference would it make if you did uncompress it into a (temporary) file? If you unzipped it to a file, queried the file, then deleted the file? If one variation of an approach doesn’t work, and another variation’s readily at hand which is almost the same thing, perhaps try “plan B” ...
Re^2: How to do some query with sqlite.bz2 file by pwp71 (Initiate) on Jan 21, 2015 at 14:07 UTC
This is the message exited: DBD::SQLite::db prepare failed: no such table: tbl_xxx at Z:\tmp\test_import_tmpBI\tt\test.pl line 25. About first part (bz2 extraction) works correctly. I've tried to save the buffer into a file and then open file created. This is ok. I would use buffer because I have sqlite files starting from 1.5~2.0GB and compressed about 150~300MB. My intention was use only RAM. I have 1GB RAM disk but isn't possible to expand it. It's not enough. Thank you pwp71	[reply]