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

Hello Monks!

I am using DBI::SQLite in memory: 
my $sqlite = DBI->connect("dbi:SQLite:dbname=:memory:","","");

[download]
I am then creating a database, populating it, and then using the following to write it to disk: 
$sqlite->sqlite_backup_to_file("/tmp/mydb.sqlite");

[download]
But, instead of writing the results to a file, I would actually like to assign that to a scalar (there are more things I need to do with it, and it would be faster than reading/writing a file on disk).

So, something along the lines of: 

my $database;

$sqlite->sqlite_backup_to_file( $database );

[download]
... is what I am looking for. I've looked around and tried a few different ways, but without any luck.

Thanks for your help!

Replies are listed 'Best First'.
Re: SQLite in memory database to scalar
by Anonymous Monk on Mar 19, 2014 at 21:41 UTC

    It looks like it should be possible in theory to copy from one in-memory database to another (SQLite Online Backup API), but it doesn't seem to be implemented in DBD::SQLite. Also, since the Backup function apparently works by doing a live copy from one SQLite database into another, you'd essentially get a second in-memory database. However, I suspect that's actually not what you want when you say you want to get a "scalar" - do you actually want a binary representation of the database as a scalar? In that case, I suspect you'll have to write the database out to a file. (BTW, Perl supports in-memory files, see open, although it doesn't look like you can pass a filehandle to sqlite_backup_to_file)

    Do you know that it is significantly slower to just read the file back in from the disk, have you tried it? What are the additional things you want to do with the copied database?

[reply]
[d/l]
      Once the database is created and populated in memory, I want to compress it, and then print it to stdout (it's a response to an http/rest request). So, it never needs to touch the file system.

      As it is now, I have to write it out from SQLite, compress it, read it in, then print it to stdout. So, yes. If I can eliminate the writing to disk part, it would really speed things up (especially under heavy load).
[reply]

        Well, aside from extending DBD::SQLite, how about writing the file to an in-memory filesystem, and using something like IO::Compress::Gzip to compress as you output the file instead of calling an external tool (as it sounds like you're doing now)?

        Also, in terms of speed, benchmarks speak much louder than words ;-)

[reply]
[d/l]
        Once the database is created and populated in memory, I want to compress it, and then print it to stdout (it's a response to an http/rest request).

        I don't think SQLite is a pretty good transport format, partly because it is binary, and partly because the file format may change (so your client needs to know different SQLite formats). Why don't you use a format that is directly readable, like JSON, XML or YAML? All of those formats won't change, can simply be stored in a perl scalar, and all of them can be compressed very well.

        Alexander

        --
        Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]
Re: SQLite in memory database to scalar
by NetWallah (Canon) on Mar 20, 2014 at 01:00 UTC
    How about creating a fifo (aka Named pipe) and passing the filesystem name for that pipe for sqlite to write to ? Your perl code could then read off that pipe and compress away. No disk I/O ! (But you may need a reader thread).

            What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against?
                  -Larry Wall, 1992

[reply]
Re: SQLite in memory database to scalar
by Your Mother (Archbishop) on Mar 19, 2014 at 21:49 UTC

    This is kind of a strange question. The initial creation puts the DB (well, the top level access to it) into a scalar. Scalars only live as long as the script and its scope though so if you want the DB to persist you will have to write it to disk and if you're doing that you should probably never use :memory: at all but just pick your DB file name and connecting using that. DBI objects (the top level scalar in this case) don't auto-serialize and the top level does not contain the DB data in a way you can flatten (without fantastic contortions anyway). You need the SQLite DBD driver to do that for you.

[reply]
[d/l]
      I don't need it to persist. I am creating it in memory and then printing it to stdout. Populating the database in memory is much, much faster than doing it on disk (which is why I am using :memory:).
[reply]
Re: SQLite in memory database to scalar
by Marshall (Canon) on Mar 20, 2014 at 06:16 UTC
    I'm not sure about your objective.
    An SQLite DB is a single file.
    Open the SQLite DB and "write" to it.

    It is possible to dynamically expand and contract the memory usage of an SQlite DB. That can be a good technique for memory intensive operation like indexing a DB. I have applications that jump the memory from 20 MB (default) to 200 MB of memory.

    In general, I would not recommend trying to force SQLite to generate a DB entirely within memory.

    In general, open the new DB and let the program do it.

[reply]

Back to Seekers of Perl Wisdom