#!/usr/bin/perl

# Lite SQLite Database Shell

# This script is licensed under the FDL (Free Documentataion License)
# The complete license text can be found at http://www.gnu.org/copyleft/fdl.html
#
# A really quick and dirty sqlite shell using SAL

use strict;
use CGI;
use SAL::DBI;

# Set up the CGI object, get any parameters, and initialize some vars
my $q = new CGI;
my $db = $q->param('db') || ':memory:';

# Set up the database
my $dbo_factory = new SAL::DBI;
my $dbo_data = $dbo_factory->spawn_sqlite($db);

print "\nLSQL - Lite SQLite Shell\n\nThis program is licensed under the Free Documentation License v1.2\n(http://www.gnu.org/copyleft/fdl.html)\n\n";

my $query;
my @params = ();
while (1) {
	print "[lsql] > ";
	my $line = <STDIN>;
	chomp($line);
	$line =~ s/^\s+//;
	$line =~ s/\s+$//;
	if ($line !~ /^quit$/i) {
		if ($line =~ /^go$/i) {
			print "\nExecuting: $query\n";
			my $num_rows = 0;
			my $num_cols = 0;
			my $query_type;

			$query_type = dbGetQueryType($query);

			if ($query_type =~ /^select$/i) {
				($num_rows, $num_cols) = dbEXECUTE($query);
				displayTable($num_rows, $num_cols);

			} elsif ($query_type =~ /^insert$/i) {
				$num_rows = dbDO($query);

			} elsif ($query_type =~ /^update$/i) {
				$num_rows = dbDO($query);

			} elsif ($query_type =~ /^create table$/i) {
				$num_rows = dbDO($query);

			} elsif ($query_type =~ /^pragma$/i) {
				($num_rows, $num_cols) = dbEXECUTE($query);
				displayTable($num_rows, $num_cols);

			} elsif ($query_type =~ /^cmdversion$/i) {
				my $eng_ver = $dbo_data->{connection}{dbh}->sqlite_version();
				print "SQLite Engine Version:  $eng_ver\n";

			} elsif ($query_type =~ /^cmdhelp$/i) {
				print "Help\n";
				print "----\n";

			} else {
				print "Syntax Error.\n\n";
			}

			print "($num_rows rows affected)\n\n";
			$query = '';

		} else {
			$query .= $line . ' ';
		}
	} else {
		last;
	}
}
print "\n";

sub dbGetQueryType {
	my $query = shift;

	$query =~ s/^\s+//;
	$query =~ s/\s+$//;
	if ($query =~ /^select /i) {
		return 'SELECT';
	} elsif ($query =~ /^insert /) {
		return 'INSERT';
	} elsif ($query =~ /^update /) {
		return 'UPDATE';
	} elsif ($query =~ /^create table /) {
		return 'CREATE TABLE';
	} elsif ($query =~ /^pragma /) {
		return 'PRAGMA';
	} elsif ($query =~ /^version/) {
		return 'CMDVersion';
	} elsif ($query =~ /^help/) {
		return 'CMDHelp';
	} else {
		return 'UNKNOWN';
	}
}

sub dbEXECUTE {
	my $query = shift;
	my ($w, $h) = $dbo_data->execute($query);
	return ($h, $w);
}

sub dbDO {
	my $query = shift;
	my ($h) = $dbo_data->do($query);
	return $h;
}

sub displayTable {
	my $rows = shift;
	my $cols = shift;

	for (my $r=0; $r < $rows; $r++) {
		print "\n";
		my $record;
		for (my $c=0; $c <= $cols; $c++) {
			my $v = $dbo_data->{data}->[$r][$c];
			$record .= "$v, ";
		}
		$record =~ s/,\s+$//;
		print $record;
	}
	print "\n\n";
}