#!/usr/bin/perl
use strict;
use DBI;
use CGI qw(:standard);

my $database = 'cvkb';
my ($script_url, $css_url, $host, $server_type, $data_source, $login, 
+$password);
my %db_cols;
$db_cols{'all'} = 'oid,question,answer,author,keywords,sites,server,cl
+ient,application,fixed_in';
$db_cols{'list'} = 'oid,question';
$db_cols{'add'} = 'question,answer,author,keywords,sites,server,client
+,application,fixed_in';
$db_cols{'edit'} = 'question,answer,author,keywords,sites,server,clien
+t,application,fixed_in';

my %fld_type = (
  'answer'=>'textarea',
  'question'=>'textarea',
);

my $msg;

$login = 'foobar';
$password = 'barfoo';
$host = 'GONK1';
$server_type = 'my-sql';
$data_source = "DBI:mysql:database=$database;host=$host";
$script_url = './cvfaq.cgi';
$css_url = '/cvfaq/cvfaq.css';

my $error_file = './cvkb.err';
my $log_file = './cvkb.sql';
my $log_sql = 1;

### Main Program

open(ERR, ">>$error_file");
open(LOG, ">>$log_file") if $log_sql;

my $dbcon =
DBI->connect($data_source,$login,$password,{PrintError=>0,RaiseError=>
+0,AutoCommit=>1});
my $connect_error = DBI::errstr;
if($connect_error){
  $msg .= $connect_error . ' : ' . $data_source;
  kb_top();kb_content('Connect Error', 0);kb_tail();
  exit 0;
}

clean_input();
my $action = param('action');
CGI::delete('action');

if(!$action or $action eq 'list_kbs'){
  return_list_page();
  exit 0;
}
elsif($action eq 'add'){
  my @cols = split /,/, $db_cols{'add'};
  my $insert_vals;
  foreach(@cols){$insert_vals .= "'" . param($_) . "',";}
  chop $insert_vals;
  my $sql = "insert into kb ($db_cols{'add'}) values ($insert_vals)";
  my $qh = run_sql($sql);
  $sql = "select max(oid) from kb";
  $qh = run_sql($sql);
  my @data = $qh->fetchrow_array;
  return_kb_page($data[0]);
}
else{
  return_full_page('Unknown command: $action',0);
  exit 0;
}
exit 0;

#### Sub-Routines ####

sub run_sql{
  my $sql = $_[0];
  $dbcon->{LongReadLen}=5000;
  $dbcon->{LongTruncOk}=1;
  my $qh = $dbcon->prepare($sql);
  if($dbcon->errstr){
    $msg .= $dbcon->errstr . ' : ' . $sql;
    print ERR gettime() . $msg . "\n\n";
    return 0;
  }
  else{
    $qh->execute;
    if($qh->errstr){
      $msg .= $qh->errstr . ' : ' . $sql;
      print ERR gettime() . $msg . "\n\n";
      return 0;
    }
  }
  print LOG gettime() . ': ' . $sql . "\n" if $log_sql;
  return $qh;
}

sub return_kbs{
  my($sql, $qh, @data, @result);
  if(ref $_[1]){
    my $list = join ',', @{$_[1]};
    $sql = "select $_[0] from kb where oid in($list) order by oid";
  }
  elsif($_[1]){
    $sql = "select $_[0] from kb where oid = $_[1] order by oid";
  }
  else{
    $sql = "select $_[0] from kb order by oid";
  }
  $qh = run_sql($sql);
  if($qh){
    while(@data = $qh->fetchrow_array){
      push @result, [@data];
    }
    $qh->finish;
  }
  return @result;
}

sub return_kb_page{
  my @kb;
  my $content = '<table>';
  @kb = return_kbs($db_cols{'all'},$_[0]);
  my @labels = split /,/, $db_cols{'all'};
  my $count = 0;
  foreach(@{$kb[0]}){
    $_ = html_escape($_);
    if($fld_type{$labels[$count]} eq 'textarea'){
      s/^(.*)$/<p>$1<\/p>/s;
      s/^\s*$/<\/p><p>/gm;
    }
    $content .= "<tr><th>" . ucfirst $labels[$count] .
      "</th><td>$_</td></tr>\n";
    $count ++;
  }
  $content .= '<tr><td>' . start_form(-method=>'GET') . hidden('oid', 
+$_[0]) .
    hidden('action','edit_form') . submit(-value=>'Edit KB') .
    end_form() . '</td></tr>';
  $content .= '</table>';
  return_full_page('CV KB', $content);
}
[download]