sqlite3.pl

sqlite3.pl

#!/usr/bin/perl
$0 =~ s@.*/@@;
sub usage {
    my $usage = <<'END_USAGE';
               _ _ _       _____        _ 
     ___  __ _| (_) |_ ___|___ /  _ __ | |
    / __|/ _` | | | __/ _ \ |_ \ | '_ \| |
    \__ \ (_| | | | ||  __/___) || |_) | |
    |___/\__, |_|_|\__\___|____(_) .__/|_|
            |_|                  |_|      

    This script is used to execute SQL queries on a SQLite database.
END_USAGE
    $usage .= "\n\nUsage: $0 <dbfile> <query> <query_args>\n";
    $usage .= "Example: $0 /tmp/test.db 'SELECT * FROM test_table WHERE id = ? AND name = ?' 1 'John Doe'\n";
    print $usage;
    exit 1;
}

use strict;
use warnings;
use utf8;
use Encode qw(decode encode);
utf8::decode($_) for @ARGV;
use open IO => ':utf8';

use DBI;
use SQL::Parser;
my $parser = SQL::Parser->new('Ansi');

# Set this to 1 to enable debug messages.
my $_SQLITE3_DEBUG;
if (defined $ENV{'SQLITE3_DEBUG'} && $ENV{'SQLITE3_DEBUG'} == 1) {
    $_SQLITE3_DEBUG = 1;
} else {
    $_SQLITE3_DEBUG = 0;
}

my $dbname = shift @ARGV;
if (!defined $dbname) {
    usage();
}
my $dbpath = "dbi:SQLite:dbname=$dbname";

my $dbh = DBI->connect($dbpath, "", "", { RaiseError => 1 }) or die $DBI::errstr;
$dbh->do("PRAGMA foreign_keys = OFF");
$dbh->do("PRAGMA encoding = 'UTF-8'");

sub do_query {
    my ($query, @query_args) = @_;

    if (!defined $query) {
        usage();
    }
    
    # Check if sqlite3 is installed.
    if (!`which sqlite3`) {
        print "sqlite3 not found, please install sqlite3\n";
        exit 1;
    }

    if ($_SQLITE3_DEBUG == 1) {
        print STDERR "Debug: Executing query:\n\t\t$query\n\t\tArguments: @query_args\n";
    }

    my $sth = $dbh->prepare($query);
    $sth->execute(@query_args);


    # Return rows if any in awk-able format.
    while (my @row = $sth->fetchrow_array()) {
        print join("\t", @row) . "\n";
    }

    $sth->finish();
}

my $sqli = shift @ARGV;
my @args = @ARGV;
do_query($sqli, @args);
$dbh->disconnect();