# $Id: sqlhelper.pm 5568 2006-06-19 14:35:56Z lo-lan-do $
#
# A few SQL helper functions
#
### AUTHOR/COPYRIGHT
# This file is copyright 2004 Roland Mas <99.roland.mas@aist.enst.fr>.
#
# This is Free Software; you can redistribute it and/or modify it under the
# terms of the GNU General Public License version 2, as published by the
# Free Software Foundation.
#
### USAGE
# drop_view_if_exists ("view_name") ;
# drop_table_if_exists ("table_name") ;
# drop_index_if_exists ("index_name") ;
# drop_sequence_if_exists ("sequence_name") ;
# remove_plugin_from_groups ("plugin_name") ;
# remove_plugin_from_users ("plugin_name") ;
#
### BUGS
# * No real bugs known -- yet
#
### TODO

use strict ;

use subs qw/ &get_plugin_id &remove_plugin_from_groups
    &remove_plugin_from_users &drop_table_if_exists
    &drop_index_if_exists &drop_sequence_if_exists
    &drop_view_if_exists &bump_sequence_to &update_plugin_db_version
    &get_plugin_db_version &debug &create_plugin_metadata_table
    &is_lesser &is_greater/ ;

sub get_plugin_id ( $$ ) ;
sub remove_plugin_from_groups ( $$ ) ;
sub remove_plugin_from_users ( $$ ) ;
sub table_exists ( $$ ) ;
sub drop_table_if_exists ( $$ ) ;
sub drop_index_if_exists ( $$ ) ;
sub drop_sequence_if_exists ( $$ ) ;
sub drop_view_if_exists ( $$ ) ;
sub bump_sequence_to ( $$$ ) ;
sub update_plugin_db_version ( $$$ ) ;
sub get_plugin_db_version ( $$ ) ;
sub create_plugin_metadata_table ( $$$ ) ;
sub is_lesser ( $$ ) ;
sub is_greater ( $$ ) ;
sub debug ( $ ) ;

sub table_exists ( $$ ) {
    my $dbh = shift or die "Not enough arguments" ;
    my $tname = shift or die  "Not enough arguments" ;
    my $query = "SELECT count(*) FROM pg_class WHERE relname='$tname' AND relkind='r'" ;
    my $sth = $dbh->prepare ($query) ;
    $sth->execute () ;
    my @array = $sth->fetchrow_array () ;
    $sth->finish () ;

    if ($array [0] != 0) {
	return 1 ;
    } else {
	return 0 ;
    }
}

sub drop_table_if_exists ( $$ ) {
    my $dbh = shift or die "Not enough arguments" ;
    my $tname = shift or die  "Not enough arguments" ;
    my $query = "SELECT count(*) FROM pg_class WHERE relname='$tname' AND relkind='r'" ;
    my $sth = $dbh->prepare ($query) ;
    $sth->execute () ;
    my @array = $sth->fetchrow_array () ;
    $sth->finish () ;

    if ($array [0] != 0) {
	# debug "Dropping table $tname" ;
	$query = "DROP TABLE $tname" ;
	# debug $query ;
	$sth = $dbh->prepare ($query) ;
	$sth->execute () ;
	$sth->finish () ;
    }
}

sub drop_sequence_if_exists ( $$ ) {
    my $dbh = shift or die "Not enough arguments" ;
    my $sname = shift or die  "Not enough arguments" ;
    my $query = "SELECT count(*) FROM pg_class WHERE relname='$sname' AND relkind='S'" ;
    my $sth = $dbh->prepare ($query) ;
    $sth->execute () ;
    my @array = $sth->fetchrow_array () ;
    $sth->finish () ;

    if ($array [0] != 0) {
	# debug "Dropping sequence $sname" ;
	$query = "DROP SEQUENCE $sname" ;
	# debug $query ;
	$sth = $dbh->prepare ($query) ;
	$sth->execute () ;
	$sth->finish () ;
    }
}

sub drop_index_if_exists ( $$ ) {
    my $dbh = shift or die "Not enough arguments" ;
    my $iname = shift or die  "Not enough arguments" ;
    my $query = "SELECT count(*) FROM pg_class WHERE relname='$iname' AND relkind='i'" ;
    my $sth = $dbh->prepare ($query) ;
    $sth->execute () ;
    my @array = $sth->fetchrow_array () ;
    $sth->finish () ;

    if ($array [0] != 0) {
	# debug "Dropping index $iname" ;
	$query = "DROP INDEX $iname" ;
	# debug $query ;
	$sth = $dbh->prepare ($query) ;
	$sth->execute () ;
	$sth->finish () ;
    }
}

sub drop_view_if_exists ( $$ ) {
    my $dbh = shift or die "Not enough arguments" ;
    my $vname = shift or die  "Not enough arguments" ;

    my $query = "SELECT count(*) FROM pg_class WHERE relname='$vname' AND relkind='v'" ;
    my $sth = $dbh->prepare ($query) ;
    $sth->execute () ;
    my @array = $sth->fetchrow_array () ;
    $sth->finish () ;

    if ($array [0] != 0) {
	# debug "Dropping view $vname" ;
	$query = "DROP VIEW $vname" ;
	# debug $query ;
	$sth = $dbh->prepare ($query) ;
	$sth->execute () ;
	$sth->finish () ;
    }
}

sub bump_sequence_to ( $$$ ) {
    my $dbh = shift or die "Not enough arguments" ;
    my $seqname = shift or die "Not enough arguments" ;
    my $targetvalue = shift or die "Not enough arguments" ;

    my ($sth, @array) ;

    do {
	my $query = "select nextval ('$seqname')" ;
	$sth = $dbh->prepare ($query) ;
	$sth->execute () ;
	@array = $sth->fetchrow_array () ;
	$sth->finish () ;
    } until $array[0] >= $targetvalue ;
}

sub get_plugin_id ( $$ ) {
    my $dbh = shift or die "Not enough arguments" ;
    my $pluginname = shift or die "Not enough arguments" ;
    
    my $pluginid = -1 ;
    
    my $query = "SELECT plugin_id FROM plugins WHERE plugin_name = '$pluginname'" ;
    my $sth = $dbh->prepare ($query) ;
    $sth->execute () ;
    if (my @array = $sth->fetchrow_array ()) {
	$pluginid = $array [0] ;
    }
    $sth->finish () ;
    
    return $pluginid ;
}

sub remove_plugin_from_groups ( $$ ) {
    my $dbh = shift or die "Not enough arguments" ;
    my $pluginid = shift or die "Not enough arguments" ;
    
    my $query = "DELETE FROM group_plugin WHERE plugin_id = $pluginid" ;
    my $sth = $dbh->prepare ($query) ;
    $sth->execute () ;
    $sth->finish () ;
}

sub remove_plugin_from_users ( $$ ) {
    my $dbh = shift or die "Not enough arguments" ;
    my $pluginid = shift or die "Not enough arguments" ;
    
    my $query = "DELETE FROM user_plugin WHERE plugin_id = $pluginid" ;
    my $sth = $dbh->prepare ($query) ;
    $sth->execute () ;
    $sth->finish () ;
}

sub update_plugin_db_version ( $$$ ) {
    my $dbh = shift or die "Not enough arguments" ;
    my $pluginname = shift or die "Not enough arguments" ;
    my $v = shift or die "Not enough arguments" ;

    my $tablename = "plugin_" .$pluginname . "_meta_data" ;

    debug "Updating $tablename table." ;
    my $query = "UPDATE $tablename SET value = '$v' WHERE key = 'db-version'" ;
    # debug $query ;
    my $sth = $dbh->prepare ($query) ;
    $sth->execute () ;
    $sth->finish () ;
}

sub get_plugin_db_version ( $$ ) {
    my $dbh = shift or die "Not enough arguments" ;
    my $pluginname = shift or die "Not enough arguments" ;

    my $tablename = "plugin_" .$pluginname . "_meta_data" ;

    my $query = "SELECT value FROM $tablename WHERE key = 'db-version'" ;
    # debug $query ;
    my $sth = $dbh->prepare ($query) ;
    $sth->execute () ;
    my @array = $sth->fetchrow_array () ;
    $sth->finish () ;

    my $version = $array [0] ;

    return $version ;
}

sub debug ( $ ) {
    my $v = shift ;
    chomp $v ;
    print STDERR "$v\n" ;
}

sub create_plugin_metadata_table ( $$$ ) {
    my $dbh = shift or die "Not enough arguments" ;
    my $pluginname = shift or die "Not enough arguments" ;
    my $v = shift || "0" ;

    my $tablename = "plugin_" .$pluginname . "_meta_data" ;
    # Do we have the metadata table?

    my $query = "SELECT count(*) FROM pg_class WHERE relname = '$tablename' and relkind = 'r'";
    # debug $query ;
    my $sth = $dbh->prepare ($query) ;
    $sth->execute () ;
    my @array = $sth->fetchrow_array () ;
    $sth->finish () ;

    # Let's create this table if we have it not

    if ($array [0] == 0) {
	debug "Creating $tablename table." ;
	$query = "CREATE TABLE $tablename (key varchar primary key, value text not null)" ;
	# debug $query ;
	$sth = $dbh->prepare ($query) ;
	$sth->execute () ;
	$sth->finish () ;
    }

    $query = "SELECT count(*) FROM $tablename WHERE key = 'db-version'";
    # debug $query ;
    $sth = $dbh->prepare ($query) ;
    $sth->execute () ;
    @array = $sth->fetchrow_array () ;
    $sth->finish () ;

    # Empty table?  We'll have to fill it up a bit

    if ($array [0] == 0) {
	debug "Inserting first data into $tablename table." ;
	$query = "INSERT INTO $tablename (key, value) VALUES ('db-version', '$v')" ;
	# debug $query ;
	$sth = $dbh->prepare ($query) ;
	$sth->execute () ;
	$sth->finish () ;
    }
}

sub is_lesser ( $$ ) {
    my $v1 = shift || 0 ;
    my $v2 = shift || 0 ;

    my $rc = system "dpkg --compare-versions $v1 lt $v2" ;

    return (! $rc) ;
}

sub is_greater ( $$ ) {
    my $v1 = shift || 0 ;
    my $v2 = shift || 0 ;

    my $rc = system "dpkg --compare-versions $v1 gt $v2" ;

    return (! $rc) ;
}

1 ;