SQLite Forum

Perl backup of database
Login

Perl backup of database

(1) By Trudge on 2020-08-14 23:02:32 [link] [source]

I have an existing web-based SQLite DB and access it with Perl. I would like to be able to do a backup of it, but have not had any joy so far. I can make a copy of the db file, but I would like to do a 'dump' to get the SQL. Am I missing something? Advice and suggestions welcome.

(2) By Warren Young (wyoung) on 2020-08-14 23:33:42 in reply to 1 [link] [source]

I think you want VACUUM INTO.

(3) By Trudge on 2020-08-15 00:32:01 in reply to 1 [link] [source]

Thank you for that. I had not looked at VACUUM. But while it does create and write to a file, it is not ASCII, but includes some binary as well. I'd like to save the data to a pure SQl file, so I can use it later in other scenarios. I run on a Mac so in Terminal I did

vacuum into "/Users/user/Desktop/fubar.sql";

I need to run the command via Perl so I've tried a few 'system' commands and have come close but no cigar.

(4.1) By Stephan Beal (stephan) on 2020-08-15 02:12:50 edited from 4.0 in reply to 3 [link] [source]

... but I would like to do a 'dump' to get the SQL

... so I've tried a few 'system' commands

A system call along the lines of "echo .dump | sqlite3 thedb > outfile.sql" ought to do the trick, assuming a unixy OS.

Edit: escaped pipe symbol.

(5) By Larry Brasfield (LarryBrasfield) on 2020-08-15 02:21:32 in reply to 4.1 [link] [source]

It works the same on Windows (of any recent vintage) in either the old, not-so-venerated cmd.exe shell or in PowerShell.

(6) By Trudge on 2020-08-15 02:31:13 in reply to 4.1 [source]

OMG. Thank you so much. That worked perfectly. The file is exactly what I was hoping. Plus my Perl variables work.

system("echo .dump sqlite3 $SRCDIR/kba.db > $TARDIR/$Today.sql"); # with escaped pipe

(7) By Trudge on 2020-08-15 02:32:56 in reply to 6 [link] [source]

OMG. Thank you so much. That worked perfectly. The file is exactly what I was hoping. Plus my Perl variables work.

system("echo .dump | sqlite3 $SRCDIR/kba.db > $TARDIR/$Today.sql"); # with escaped pipe

(8) By Stephan Beal (stephan) on 2020-08-15 02:35:23 in reply to 7 [link] [source]

For future reference: if you try to edit a post which is awaiting moderation, a new copy of the post gets created in order to avoid race conditions between the moderator and the person editing the post which could lead to the edit being approved based on its old content.

(9) By Trudge on 2020-08-15 03:34:07 in reply to 8 [link] [source]

Yes, as you can tell I've not done this before. My bad.

(10) By Warren Young (wyoung) on 2020-08-15 15:34:36 in reply to 6 [link] [source]

There are several possible improvements here:

  1. You don't need the pipe: the sqlite3 command will also take SQL and shell commands as parameters after any flags and the database name.

  2. We can get rid of the stdout redirection by using Perl's built-in I/O redirection.

  3. ...which then means we don't need to use system(), which is dangerous when used with variables if their content can come from user-provided sources, since system() invokes the shell, so you're subject to complicated parsing rules over user-provided data.

All of that together gives an alternative like this:

die "No such backup directory $TARDIR!\n" unless -d $TARDIR;
my $bfile = "$TARDIR/$Today.sql";
open my $backup, '>', $bfile or die "Cannot create backup file $bfile: $!\n";
select $bfile;   # send STDOUT to backup file
my $dfile = "$SRCDIR/kba.db";
open my $cmd, '|-', '/usr/bin/sqlite3', $dfile, '.dump'
       or die "Failed to dump DB $dfile: $!\n";
close $cmd or die "Backup to $dfile failed: $!\n";
select STDOUT;   # restore STDOUT redirect

This has several non-obvious virtues not already called out above:

  • This is many more lines of code, but it's safer and allows a lot more error checking and diagnosis, all made possible because we're breaking the process up into smaller steps, so we can check each one separately.

  • Explicitly passing the path to the sqlite3 binary is safer than searching for it in the PATH, so that should be done wherever practical. If someone can put a sqlite3 executable into your PATH ahead of the one you expected to run, they take over all running permissions of your script.

  • Use of the LIST form of open means the call to sqlite3 cannot fail due to spaces or quotes in file names. It can also prevent games like splitting commands by injecting a semicolon into one of the variables. Consider:

    $SRCDIR = "./junk; rm ~/.profile ; touch ";
    
  • Despite its complexity, it is more efficient, not requiring a call to an external shell. And on primitive systems, it avoids a call to /bin/echo as well.

(11) By Trudge on 2020-08-15 16:05:42 in reply to 10 [link] [source]

Well, that is certainly above and beyond. It looks like you're familiar with Perl as well. Good to know.

I've done some reading about calling a shell and some of the issues involved. I tend to do what I can get to work, and worry about efficiency later - self-taught Perl leaves some knowledge gaps.

Will have to do more reading on using 'echo'. I've heard and read about it but never used it. This is a good test case. And thank you for the alternative solution. TMTOWTDI.

(12) By Trudge on 2020-08-22 00:38:48 in reply to 10 [link] [source]

Well, this is typical of my work flow (2 ahead, 3 back). I've implemented your alternative code but something is still wonky. It writes the output SQL to the screen instead of a file, but does create a 0-length file properly named.

my $Outfile = "$TARDIR/$Today.sql";
my $Infile = "$SRCDIR/kba.db";
die "No such backup directory $TARDIR!\n" unless -d $TARDIR;
open my $backup, '>', $Outfile or die "Cannot create backup file $Outfile: $!\n";
select $Outfile;   # send STDOUT to backup file
open my $cmd, '|-', '/usr/bin/sqlite3', '$Infile', '.dump' or die "Failed to dump DB $Outfile: $!\n";
close $cmd or die "Backup to $Outfile failed: $!\n";
select STDOUT;   # restore STDOUT redirect

This seems very close if the output can get to the right place.

I've also tried 'echo .dump sqlite3 kba.db > Today.sql' from a Terminal window and that works perfectly.

But in a script 'system("echo .dump | sqlite3 $SRCDIR/kba.db > $TARDIR/$Today.sql");' does not create a file.

(13) By Warren Young (wyoung) on 2020-08-22 01:34:20 in reply to 12 [link] [source]

Rather than debug this complex version, try the simpler alternative posted afterward.

(14) By anonymous on 2020-08-22 09:26:22 in reply to 12 [link] [source]

The Perl command uses an undefined variable $Today, which will be interpolated as the empty string. As a result probably the hidden file $TARDIR/.sql is created. Try using $TARDIR/Today.sql (without dollar sign) instead.

(16.1) By Warren Young (wyoung) on 2020-08-22 14:00:28 edited from 16.0 in reply to 14 [link] [source]

$Today, $TARDIR and such come from up-thread. My code is meant to drop into the program where they are defined; it is not meant to be a complete running example.

(15) By Larry Brasfield (LarryBrasfield) on 2020-08-22 13:34:34 in reply to 12 [link] [source]

Adding to anonymous' tip re $Today, you can do yourself a great favor in future Perl programming by preceding your scripts with: use strict; . This causes a complaint to issue when never-declared variables are used instead of their springing into existence upon first use with the default value, undef.

(17) By Trudge on 2020-08-22 14:25:38 in reply to 15 [link] [source]

Sorry for the confusion. $Today is previously defined to get the date today. As is $SRCDIR & $TARDIR.

And yes I start all my scripts with

  use strict;
  use warnings;

Also have a BEGIN block to catch errors:

BEGIN { open (STDERR,">> $0.txt"); print STDERR "n", scalar localtime, "n"; } $|=1;