#!/usr/bin/perl
#use strict;
use DBI;
my %conf = (
'dbhost' => "mysql host",
'dbbase' => "mysql database",
'dbuser' => "mysql username",
'dbpswd' => "mysql password",
'dbprefix' => "contact_",
'name' => "dcontacts",
'version' => "0.2",
'author' => "dorainm",
'email' => "dorainm\@gmail.com",
);
my $dargv = @ARGV;
if( $ARGV[0] eq "--help" ) {
&display_usage();
}
## Re/Install the database
elsif( $ARGV[0] eq "INSTALL" ) {
printf( "INSTALL operation will CLEAR all the DATA if it is EXIST!\n" );
printf( "Are you SURE (yes/NO) ? " );
$answer = <STDIN>;
chomp $answer;
if( $answer eq "yes" ) {
&dtable_reinstall();
printf "Done\n";
} else {
printf "Cancel\n";
}
}
## backup the databases
elsif( $ARGV[0] eq "BACKUP" ) {
&dcontacts_backup();
}
## search AND
elsif( $ARGV[0] eq "AND" ) {
local ($rows,$cols,@retres) = &dcontacts_search( "AND" );
local $prename;
for( $i=0; $i<$rows; $i++ ) {
if( $prevname ne $retres[$i][0] ) {
$prevname = $retres[$i][0];
printf "-[ $prevname ]------------------------------------------------------\n";
}
printf "[%s] %12s : %s\n", $retres[$i][3], $retres[$i][1], $retres[$i][2];
}
}
## search OR
elsif( $ARGV[0] eq "OR" ) {
local ($rows,$cols,@retres) = &dcontacts_search( "OR" );
local $prename;
for( $i=0; $i<$rows; $i++ ) {
if( $prevname ne $retres[$i][0] ) {
$prevname = $retres[$i][0];
printf "-[ $prevname ]------------------------------------------------------\n";
}
printf "[%s] %12s : %s\n", $retres[$i][3], $retres[$i][1], $retres[$i][2];
}
}
## add a new name
elsif( $dargv == 1 )
{
local $newname = $ARGV[0];
if( &dname_exist( $newname ) ) {
## display the information about the name
#printf "error: the name '$newname' has been already exist\n";
local $rows, $cols, @retres;
($rows,$cols,@retres) = &dcontacts_query( $newname );
printf "-[ $newname ]-------------------------------------------------($rows)--\n";
for( $i=0; $i<$rows; $i++ ) {
printf "[%s] %12s : %s\n", $retres[$i][2], $retres[$i][0], $retres[$i][1];
}
printf "-----------------------------------------------------------------\n";
} else {
&dname_create( $newname );
}
}
## update the name
elsif( $dargv == 2 )
{
local $oldname = $ARGV[0];
local $newname = $ARGV[1];
if( ! &dname_exist( $oldname ) ) {
printf "error: the old name '$newname' has NOT been exist\n";
} elsif( $newname eq "NULL" ) {
## NULL : delete the name
&dname_remove( $oldname );
} elsif( &dname_exist( $newname ) ) {
printf "error: the new name '$newname' has been already exist\n";
} else {
&dname_update( $oldname, $newname )
}
}
## add a new item
elsif( $dargv == 3 )
{
local $dname = $ARGV[0];
local $dkind = $ARGV[1];
local $ditem = $ARGV[2];
if( ! &dname_exist( $dname ) ) {
&dname_create( $dname );
}
if( ! &dkind_exist( $dkind ) ) {
&dkind_create( $dkind );
}
if( &ditem_exist( $dname, $dkind, $ditem ) ) {
printf "it already has the item as '$ditem'\n";
} else {
&ditem_create( $dname, $dkind, $ditem );
}
}
elsif( $dargv == 4 )
{
local $dname = $ARGV[0];
local $dkind = $ARGV[1];
local $dolditem = $ARGV[2];
local $dnewitem = $ARGV[3];
if( ! &dname_exist( $dname ) ) {
printf "nobody has the name '$dname'\n";
} elsif( ! &dkind_exist( $dkind ) ) {
printf "there are no kind named '$dname' of '$dname'\n";
} elsif( ! &ditem_exist( $dname, $dkind, $dolditem ) ) {
printf "there are no such item as '$dolditem'\n";
} elsif( $dnewitem eq "NULL" ) {
## remove item
&ditem_remove( $dname, $dkind, $dolditem );
} elsif( &ditem_exist( $dname, $dkind, $dnewitem ) ) {
printf "it already has the item as '$dnewitem'\n";
} else {
&ditem_update( $dname, $dkind, $dolditem, $dnewitem );
}
}
else {
&display_usage();
}
exit 0;
sub dname_exist()
{
return ( &dmysql_value( $conf{'dbprefix'} . "names", "n_name", "`n_name`='$_[0]'" ) );
}
sub dname_create()
{
local $sql = "INSERT INTO `$conf{'dbprefix'}names` VALUE ('','$_[0]');";
&dmysql_execute( $sql );
}
## dname_update old_name, new_name
sub dname_update()
{
local $sql = "UPDATE `$conf{'dbprefix'}names` SET `n_name`='$_[1]' WHERE `n_name`='$_[0]';";
&dmysql_execute( $sql );
}
sub dname_remove()
{
local $sql = "DELETE FROM `$conf{'dbprefix'}names` WHERE `n_name`='$_[0]';";
&dmysql_execute( $sql );
}
sub dcontacts_backup()
{
if( $conf{'dbpswd'} ) {
$cmd = "mysqldump -h$conf{'dbhost'} -u$conf{'dbuser'} -p$conf{'dbpswd'} --databases $conf{'dbbase'} > ~/.dcontacts/backup/" . &ddatetime . ".sql";
} else {
$cmd = "mysqldump -h$conf{'dbhost'} -u$conf{'dbuser'} --databases $conf{'dbbase'} > ~/.dcontacts/backup/" . &ddatetime . ".sql";
}
`mkdir -p ~/.dcontacts/backup`;
`$cmd`;
}
sub dcontacts_query()
{
local $rows, $cols, @retres;
# local $sql = "SELECT `$conf{'dbprefix'}names`.`n_name`,`$conf{'dbprefix'}kinds`.`k_caption`,`$conf{'dbprefix'}items`.`i_content` FROM `$conf{'dbprefix'}names`,`$conf{'dbprefix'}kinds`,`$conf{'dbprefix'}items` WHERE `$conf{'dbprefix'}items`.`n_id`=(SELECT `n_id` FROM `$conf{'dbprefix'}names` WHERE `n_name`='$_[0]') AND `$conf{'dbprefix'}names`.`n_id`=`$conf{'dbprefix'}items`.`n_id` AND `$conf{'dbprefix'}kinds`.`k_id`=`$conf{'dbprefix'}items`.`k_id`;";
local $sql = "SELECT `$conf{'dbprefix'}kinds`.`k_caption`,`$conf{'dbprefix'}items`.`i_content`,`$conf{'dbprefix'}items`.`i_date` FROM `$conf{'dbprefix'}names`,`$conf{'dbprefix'}kinds`,`$conf{'dbprefix'}items` WHERE `$conf{'dbprefix'}items`.`n_id`=(SELECT `n_id` FROM `$conf{'dbprefix'}names` WHERE `n_name`='$_[0]') AND `$conf{'dbprefix'}names`.`n_id`=`$conf{'dbprefix'}items`.`n_id` AND `$conf{'dbprefix'}kinds`.`k_id`=`$conf{'dbprefix'}items`.`k_id`;";
( $rows, $cols, @retres ) = &dmysql_query( $sql );
return ($rows,$cols,@retres);
}
# dcontacts_search $type
sub dcontacts_search()
{
local $rows, $cols, @retres;
local $sql = "SELECT `$conf{'dbprefix'}names`.`n_name`,`$conf{'dbprefix'}kinds`.`k_caption`,`$conf{'dbprefix'}items`.`i_content`,`$conf{'dbprefix'}items`.`i_date` FROM `$conf{'dbprefix'}names`,`$conf{'dbprefix'}kinds`,`$conf{'dbprefix'}items` WHERE `$conf{'dbprefix'}names`.`n_id`=`$conf{'dbprefix'}items`.`n_id` AND `$conf{'dbprefix'}kinds`.`k_id`=`$conf{'dbprefix'}items`.`k_id`";
if( $dargv > 1 ) {
if( $_[0] eq "AND" ) {
$sql .= " AND ( 1";
for( $i=1; $i<@ARGV; $i++ ) {
local $keyword = $ARGV[$i];
$sql .= " AND ( `$conf{'dbprefix'}items`.`n_id`=(SELECT `n_id` FROM `$conf{'dbprefix'}names` WHERE `n_name` LIKE '%$keyword%') OR `$conf{'dbprefix'}items`.`k_id`=(SELECT `k_id` FROM `$conf{'dbprefix'}kinds` WHERE `k_caption` LIKE '%$keyword%') OR `$conf{'dbprefix'}items`.`i_content` LIKE '%$keyword%' )";
}
$sql .= " );";
} else {
$sql .= " AND ( 0";
for( $i=1; $i<@ARGV; $i++ ) {
local $keyword = $ARGV[$i];
$sql .= " OR ( `$conf{'dbprefix'}items`.`n_id`=(SELECT `n_id` FROM `$conf{'dbprefix'}names` WHERE `n_name` LIKE '%$keyword%') OR `$conf{'dbprefix'}items`.`k_id`=(SELECT `k_id` FROM `$conf{'dbprefix'}kinds` WHERE `k_caption` LIKE '%$keyword%') OR `$conf{'dbprefix'}items`.`i_content` LIKE '%$keyword%' )";
}
$sql .= " );";
}
}
( $rows, $cols, @retres ) = &dmysql_query( $sql );
return ($rows,$cols,@retres);
}
sub dtable_reinstall()
{
local $sql;
&dmysql_execute( "DROP TABLE IF EXISTS `$conf{'dbprefix'}names`;" );
$sql = "CREATE TABLE `$conf{'dbprefix'}names` (";
$sql .= " `n_id` mediumint unsigned NOT NULL auto_increment,";
$sql .= " `n_name` varchar(20) NOT NULL,";
$sql .= " PRIMARY KEY (`n_id`)";
$sql .= ") ENGINE=MyISAM DEFAULT CHARSET=utf8;";
&dmysql_execute( $sql );
&dmysql_execute( "DROP TABLE IF EXISTS `$conf{'dbprefix'}kinds`;\n" );
$sql = "CREATE TABLE `$conf{'dbprefix'}kinds` (\n";
$sql .= " `k_id` smallint unsigned NOT NULL auto_increment,\n";
$sql .= " `k_caption` varchar(20) NOT NULL,\n";
$sql .= " PRIMARY KEY (`k_id`)\n";
$sql .= ") ENGINE=MyISAM DEFAULT CHARSET=utf8;\n";
&dmysql_execute( $sql );
&dmysql_execute( "DROP TABLE IF EXISTS `$conf{'dbprefix'}items`;\n" );
$sql = "CREATE TABLE `$conf{'dbprefix'}items` (\n";
$sql .= " `i_id` bigint unsigned NOT NULL auto_increment,\n";
$sql .= " `n_id` mediumint unsigned NOT NULL,\n";
$sql .= " `k_id` smallint unsigned NOT NULL,\n";
$sql .= " `i_content` varchar(100) NOT NULL,\n";
$sql .= " `i_date` datetime NOT NULL,\n";
$sql .= " PRIMARY KEY (`i_id`)\n";
$sql .= ") ENGINE=MyISAM DEFAULT CHARSET=utf8;\n";
&dmysql_execute( $sql );
}
sub dkind_exist()
{
return ( &dmysql_value( $conf{'dbprefix'} . "kinds", "k_caption", "`k_caption`='$_[0]'" ) );
}
sub dkind_create()
{
local $sql = "INSERT INTO `$conf{'dbprefix'}kinds` VALUE ('','$_[0]');";
&dmysql_execute( $sql );
}
## dkind_update old_kind, new_kind
sub dkind_update()
{
local $sql = "UPDATE `$conf{'dbprefix'}kinds` SET `k_caption`='$_[1]' WHERE `k_caption`='$_[0]';";
&dmysql_execute( $sql );
}
sub dkind_remove()
{
local $sql = "DELETE FROM `$conf{'dbprefix'}kinds` WHERE `k_caption`='$_[0]';";
&dmysql_execute( $sql );
}
sub ditem_exist()
{
local $rows, $cols, @retres;
local $sql = "SELECT `i_id` FROM `$conf{'dbprefix'}items` WHERE `n_id`=(SELECT `n_id` FROM `$conf{'dbprefix'}names` WHERE `n_name`='$_[0]') AND `k_id`=(SELECT `k_id` FROM `$conf{'dbprefix'}kinds` WHERE `k_caption`='$_[1]') AND `i_content`='$_[2]';";
($rows, $cols, @retres) = &dmysql_query( $sql );
return $rows;
}
## ditem_create name kind item
sub ditem_create()
{
local $sql = "INSERT INTO `$conf{'dbprefix'}items` VALUE ('',(SELECT `n_id` FROM `$conf{'dbprefix'}names` WHERE `n_name`='$_[0]'),(SELECT `k_id` FROM `$conf{'dbprefix'}kinds` WHERE `k_caption`='$_[1]'),'$_[2]','" . &ddate . "');";
# printf $sql;
&dmysql_execute( $sql );
}
## ditem_update name kind old_item new_item
sub ditem_update()
{
local $sql = "UPDATE `$conf{'dbprefix'}items` SET `i_content`='$_[3]',`i_date`='" . &ddate . "' WHERE `n_id`=(SELECT `n_id` FROM `$conf{'dbprefix'}names` WHERE `n_name`='$_[0]') AND `k_id`=(SELECT `k_id` FROM `$conf{'dbprefix'}kinds` WHERE `k_caption`='$_[1]') AND `i_content`='$_[2]';";
&dmysql_execute( $sql );
}
sub ditem_remove()
{
local $sql = "DELETE FROM `$conf{'dbprefix'}items` WHERE `n_id`=(SELECT `n_id` FROM `$conf{'dbprefix'}names` WHERE `n_name`='$_[0]') AND `k_id`=(SELECT `k_id` FROM `$conf{'dbprefix'}kinds` WHERE `k_caption`='$_[1]') AND `i_content`='$_[2]';";
&dmysql_execute( $sql );
}
sub display_usage()
{
printf "$conf{'name'} $conf{'version'}\n";
printf "Copyright (C) 2008 $conf{'author'} <$conf{'email'}>\n";
printf "Usage :\n";
printf "Help Info. : $conf{'name'} --help\n";
printf "Re/Install : $conf{'name'} INSTALL\n";
printf "Backup Data : $conf{'name'} BACKUP\n";
printf "Create Name : $conf{'name'} newname\n";
printf "Update Name : $conf{'name'} oldname newname\n";
printf "Remove Name : $conf{'name'} oldname NULL\n";
printf "Create Item : $conf{'name'} name kind newitem\n";
printf "Update Item : $conf{'name'} name kind olditem newitem\n";
printf "Remove Item : $conf{'name'} name kind olditem NULL\n";
printf "AND Search : $conf{'name'} AND keyword,...\n";
printf "OR Search : $conf{'name'} OR keyword,...\n";
return;
}
sub ddate()
{
local ($sec,$min,$hour,$mday,$month,$year,$yday,$isdst) = localtime(time());
$year += 1900;
$month += 1;
return ("$year-$month-$mday $hour:$min:$sec");
}
sub ddatetime()
{
local ($sec,$min,$hour,$mday,$month,$year,$yday,$isdst) = localtime(time());
$year += 1900;
$month += 1;
return ("$year-$month-$mday_$hour:$min:$sec");
}
sub dmysql_query
{
local @retres;
local @ref;
local $dbh = DBI->connect("DBI:mysql:database=$conf{'dbbase'};host=$conf{'dbhost'}",$conf{'dbuser'},$conf{'dbpswd'});
local $sth = $dbh->prepare( $_[0] );
$sth->execute();
@ref=$sth->fetchrow_array();
@retres[0] = [@ref];
local $rows = $sth->rows;
local $cols = $#ref+1;
local $i=1;
if( $rows ) {
while( @ref=$sth->fetchrow_array() )
{
@retres[$i] = [@ref];
$i++;
};
} else {
$cols = 0;
@retres = "";
}
$sth->finish();
$dbh->disconnect();
($rows, $cols, @retres);
}
sub dmysql_value
{
local $retres;
local @ref;
# local $dbh = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost",$dbuser,$dbcode);
local $dbh = DBI->connect("DBI:mysql:database=$conf{'dbbase'};host=$conf{'dbhost'}",$conf{'dbuser'},$conf{'dbpswd'});
local $sql = "SELECT `" . $_[1] . "` FROM `" . $_[0] . "`" . ($_[2]?" WHERE ".$_[2]:"") . ";";
local $sth = $dbh->prepare( $sql );
$sth->execute();
@ref=$sth->fetchrow_array();
$retres=$ref[0];
$sth->finish();
$dbh->disconnect();
$retres;
}
############
# dmysql_query ( sql as string ) as boolean
#
sub dmysql_execute
{
# my $dbh = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost",$dbuser,$dbcode);
local $dbh = DBI->connect("DBI:mysql:database=$conf{'dbbase'};host=$conf{'dbhost'}",$conf{'dbuser'},$conf{'dbpswd'});
my $retval = $dbh->do( $_[0] );
$dbh->disconnect();
if(!defined($retval))
{
0;
}
else
{
1;
};
};
1;
|
