#!/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 .
|
