Categories - Create and process categories within MySQL DB
Index
SYNOPSIS
SYNTAX
AUTHOR
Code Index:
NAME
Categories - Create and process categories within MySQL DB
VERSION
DESCRIPTION
Categories allows you to create and process categories (for products/directories/shops and etc...)
SYNOPSIS
There is an example that you may use in your own CGI scripts:
# --- Script begin here ---
use Categories;
# NOTE: new() method will create needed DB structure in MySQL (database & tables) if they not exist!
# Please create database before execute this script or DB USER must have privilege to create DBs!
$obj = Categories->new(database => 'catsdb', user => 'db_user', pass => 'db_pass', host => 'localhost');
# OR
# $obj = Categories->new(dbh => $mysql_dbh_handler);
if($obj)
{
my $comp_id = $obj->add(type=>'category',name=>'Computers',category=>0);
my $film_id = $obj->add(type=>'category',name=>'Films',category=>0);
my $matr_id = $obj->add(type=>'item',name=>'The Matrix',category=>$film_id,value=>'');
my $one_id = $obj->add(type=>'item',name=>'The One',category=>$film_id,value=>'');
my $cpu_id = $obj->add(type=>'category',name=>'CPU',category=>$comp_id);
my $hdd_id = $obj->add(type=>'category',name=>'HDD',category=>$comp_id);
my $xp18_id = $obj->add(type=>'item',name=>'Athlon XP 1800+',category=>$cpu_id,value=>'');
my $xp20_id = $obj->add(type=>'item',name=>'Athlon XP 2000+',category=>$cpu_id,value=>'');
my $xp21_id = $obj->add(type=>'item',name=>'Athlon XP 2100+',category=>$cpu_id,value=>'');
my $hdd1_id = $obj->add(type=>'item',name=>'Maxtor 80 GB',category=>$hdd_id,value=>'30 months warranty');
my $hdd2_id = $obj->add(type=>'item',name=>'Maxtor 120 GB',category=>$hdd_id,value=>'36 months warranty');
my @res = $obj->read(path=>'//Computers//HDD//*',sort=>'ID',preload=>YES,reverse=>NO,partial=>'YES');
print "<HR>";
if(scalar(@res))
{
foreach $l (@res)
{
my ($id,$parent_category,$name,$value,$route_path) = @$l;
print "ID: $id<BR>\n";
print "PARENT: $parent_category<BR>\n";
print "NAME: $name<BR>\n";
print "VALUE: $value<BR>\n";
$route_path =~ s~//~\\~sgi;
$route_path =~ s~\\(.*?)\x0~\\~sgi;
print "PATH: $route_path<BR>\n";
print "<HR>";
}
}
# Find categories and items (filter=>ALL) that has NAME (by=>NAME) 'The Matrix' order by ID (sort=>ID)
# and return multiple results (multiple=>'YES') if available, also return rout path to this element
# (route=>'YES') using category cache (preload=>'YES') to speed up searching. However 'preload' option may be
# worse if categories table is too long, because script load whole table and may crush if not enough memmory!
my @res = $obj->find('search'=>'The Matrix','sort'=>'ID','by'=>'NAME','filter'=>'ALL','multiple'=>'YES',
'route'=>'YES','preload'=>'YES','partial'=>'NO','reverse'=>'NO');
if(scalar(@res))
{
foreach $l (@res)
{
my ($type,$id,$parent_category,$name,$value,$route_path) = @$l;
print "Type: $type<BR>\n";
print "ID: $id<BR>\n";
print "PARENT: $parent_category<BR>\n";
print "NAME: $name<BR>\n";
print "VALUE: $value<BR>\n";
$route_path =~ s~//~\\~sgi;
$route_path =~ s~\\(.*?)\x0~\\~sgi;
print "PATH: $route_path<BR>\n";
}
}
print "<HR>";
# Modify: Change PARENT/CID and/or NAME
$obj->modify(id=>$xp21_id,type=>'item',name=>'Duron 1300 MHz',value=>'6 months warranty');
$obj->modify(id=>$comp_id,type=>'category',name=>'PC');
$obj->modify(id=>$cpu_id,type=>'category',newcid=>0);
$obj->deep_traverse('preload'=>'YES','id'=>0,'level'=>0,'path'=>'//','eval'=>\&Walk,'sort'=>'NAME');
# Delete ROOT category, so all items/categories are deleted!
$obj->del(type=>'category',id=>0);
}
else
{
print $Categories::error;
}
sub Walk
{
my $self = shift;
my %inp = @_;
my $id = $inp{'id'};
my $level = $inp{'level'};
my $separator = $inp{'separator'};
my $path = $inp{'path'};
my $name = $inp{'name'}; # ITEM only ($type='I')!
my $value = $inp{'value'}; # ITEM only ($type='I')!
my $type = $inp{'type'};
$path =~ s~$separator~\\~sgi;
$path =~ s~\\(.*?)\x0~\\~sgi;
print $path."$name"."[$value]<BR>";
}
# --- Script ends here ---
SYNTAX
That is simple function reference:
# Create object of Categories type
$object = Categories->new(database=>'catsdb', user=>'db_user', pass=>'db_pass', host=>'localhost',
port=>'3306', create=>'Y', checkdb=>'Y', name=>'catdb', dbh=>$connect_db_handler);
Where:
database - is your DB where categories (tables) will be placed. If database not exist module
will try to create one for you, using supplied user and password. [REQUIRED if $dbh empty]
user/pass - is your DB user and password [REQUIRED if $dbh empty]
host - MySQL DB host
port - your MySQL port
create - module will attempt to create DB and/or tables
checkdb - module will try to check DB structure
name - name of category object
dbh - you can supply already connected database handler instead of database/user/pass/host/port!
# Test database structure (tables only)
$state = $object->is_tables_exists(name=>'name_of_category_object');
# Create database structure (tables only - database must exist!)
$state = $object->create_tables(name=>'name_of_category_object');
Create table structure (database should exist!)
# Clear categories cache
$state = $object->clear_cache();
# Reload/create categories cache and return array of all categories. @cats is array of references to hashes;
@cats = $object->preload_categories(name=>'name_of_category_object', sort=>'NAME', reverse=>'N');
Where:
sort - is name of column (order by),
reverse - reverse results (DESC)
HINT: $ref = $cats[0]; %hash = %$ref; $name = $hash{'NAME'}; $id = $hash{'ID'}; $parent = $hash{'PARENT'};
# Find category/item by 'by' column, searching by 'search' keyword (may be on part of word 'partial'=>'Y');
# also it may return full path to located item/category (route=>'Y') using categories cache (preload=>'Y')
@res = $object->find(caseinsensitive=>'Y', filter=>'ITEMS', multiple=>'Y', by=>'ID', sort=>'NAME',
reverse=>'N', partial=>'N', search=>'keyword', check=>'N', route=>'N',
separator=>'//', preload=>'Y');
Where:
caseinsensitive - search is caseinsensitive,
filter - define where sub must search (ITEMS,CATEGORIES,ALL),
multiple - allows muliple results,
by - search BY column,
sort - 'order by' all results,
reverse - reverse all results,
partial - allows partial search ( LIKE %something%),
search - search keyword,
check - test tables structure,
route - find path to root,
separator - use follow separator to separate categories in route path,
preload - allows categories cache.
Method returns array of references to arrays results in follow syntax:
@res = ($ref_1[,$ref_2,...]), where $ref_n is reference to array with follow structure:
@$ref_n = ([0],[1],[2],[3],[4],[5]);
where:
[0] - 'I' or 'C' (Item or Category),
[1] - ID of Item/Category,
[2] - PARENT (category ID),
[3] - NAME of Item/Category,
[4] - VALUE of Item (ONLY) or empty string if Category,
[5] - If route=>'Y' this will has follow syntax:
$separator.$ID."\x0".$CATEGORY_NAME[$separator...]
# Add ITEM/CATEGORY to categories tree. If proceed ITEM you can set 'value' to it.
$id = $object->add(type=>'ITEM', category=>'0', name=>'Name_Of_Element', check=>'N', value=>'Items only');
Where:
type - is type of element ('ITEM' and 'CATEGORY'),
category - is ID of parent (0 is root),
name - name of new item/category,
value - value of item (only)
check - test tables structure.
$id is ID of created element.
# Delete ITEM/CATEGORY (recursive) by it's own ID
$cnt = $object->del(type=>'ITEM', id=>'0', check=>'N', preload=>'Y');
Where:
type - is type of element ('ITEM' and 'CATEGORY'),
id - is ID of Item/Category (0 is root),
check - test tables structure,
preload - allows categories cache.
$cnt is number of affected(deleted) rows.
# Modify(update/rename/move) given ITEM/CATEGORY. If some parameter missed method will not change
# current ITEM value (name,parent,value)
$cnt = $object->modify(type=>'ITEM', id=>'id_of_element', newcid=>'id_of_new_parent', check=>'N',
name=>'new_name_of_element', value=>'Items only', preload=>'Y');
Where:
type - is type of element ('ITEM' and 'CATEGORY'),
id - is ID of Item/Category,
check - test tables structure,
name - new name of item/category (if you dismiss filed, NAME will not be affected!),
value - new value of item (if you dismiss filed, VALUE will not be affected!),
newcid - new Parent category ID (if you dismiss filed, PARENT will not be affected!),
preload - allows categories cache.
$cnt is number of affected(deleted) rows.
# Traverse category tree (in width) and calling 'eval' callback sub for any found category!
$cnt = $object->traverse(cid=>'id_of_category', eval=>\&callback_sub', check=>'N',
sort=>'NAME', reverse=>'N', preload=>'Y');
This sub traverse in width.
Where:
cid - ID of category that should be traversed,
eval - reference to sub that will be called for every category,
it will be called as: &$eval($self,'id'=>$current,'parent'=>$cid);
# Traverse category tree (in deep) and calling 'eval' callback sub for any found CATEGORY/ITEM!
$object->deep_traverse(id=>'id_of_category', level=>'0', separator=>'//', path=>'//',
eval=>\&callback_sub', sort=>'NAME', reverse=>'N', check=>'N',
preload=>'Y');
deep_traverse is recursive sub and it traverse in deep. At fist step level should be '0' and
path '//' (like separator); eval is also reference to callback sub and it will be called as:
&$evala($self,'id'=>$id,'level'=>$level,'type'=>$whereis,'path'=>$path,
'name'=>$item_name,'value'=>$item_value,'separator'=>$separator);
where 'name'/'value' will be available only for Items (type=>'I'), but not for categories (type=>'C')
# Load found categories/items for 'cid' category only! (no recurse)
@res = $object->load_category(cid=>'id_of_category', sort=>'NAME', reverse=>'N', preload=>'Y');
This method will load only Items/Categories of 'cid' category (without recurse)!
# Read category/item[s] properties(data) searching by registry-like path (only by 'NAME')!
@res = $object->read(path=>'//path//to//category//item', sort=>'ID', reverse=>'N', preload=>'Y',
partial=>'N', caseinsensitive=>'Y', separator=>'//', preload=>'Y', check=>'N');
This method try to locate category or item[s] only by 'NAME' starting by 'root' category(PARENT=0),
Where:
path - is 'registry'-like path to category/item[s]. If path ends with '//' (separator)
then searching is proceed for category, other else for item. If name of item is
'*' then all items of this 'path' will be fetched!
partial - This allows searhing for partial items (not for path or categories)! Moreover to
use '*' feature you must set partial=>'YES'
Method returns array of references to arrays results in follow syntax:
@res = ($ref_1[,$ref_2,...]), where $ref_n is reference to array with follow structure:
@$ref_n = ($ID,$PARENT_ID,$NAME,$VALUE,$PATH);
$PATH has follow syntax: $separator.$ID."\x0".$CATEGORY_NAME[$separator...]
AUTHOR
Julian Lishev - Bulgaria, Sofia,
e-mail: julian@proscriptum.com,
www.proscriptum.com
package Categories;
################################################
# Powerful Categories Module (Object Model)
# written by Julian Lishev
#
# Module purposes:
# To create and manage categories within MySQL DB
################################################
################################################
# SQL structure for MySQL DB support:
# Please edit this array only if you know what
# you doing!
# If DB structure is not available module
# will attempt to create one for you using
# structure below!
# Supported "templates" for elements of arrays:
# %%database%%, %%name%%, %%user%%, %%pass%%
################################################
# Available methods:
# new(), is_tables_exists(), create_tables(),
# clear_cache(), preload_categories(), find(),
# add(), del(), modify(), traverse(), error(),
# deep_traverse(), load_category(), read()
################################################
@Categories::structure_tables =
(
'_categories', # First row must correspondence to first row of array below and so on.. !!!
'_items',
);
@Categories::structure =
(
"CREATE TABLE %%name%%$Categories::structure_tables[0]
(
ID BIGINT(1) AUTO_INCREMENT PRIMARY KEY,
PARENT BIGINT(1) DEFAULT 0,
NAME VARCHAR(100),
INDEX index_1 (PARENT),
KEY index_2 (NAME(3))
)
",
"CREATE TABLE %%name%%$Categories::structure_tables[1]
(
ID BIGINT(1) AUTO_INCREMENT PRIMARY KEY,
CID BIGINT(1) DEFAULT 0,
NAME VARCHAR(100),
VALUE VARCHAR(255) binary,
INDEX index_1 (CID),
KEY index_2 (NAME(3)),
KEY index_3 (VALUE(3))
)
",
);
################################################
# PLEASE DO NOT EDIT BELOW!
################################################
use strict;
# ----- Global members for all objects -----
$Categories::debugging = 0;
$Categories::error = '';
BEGIN
{
use vars qw($VERSION @ISA @EXPORT);
$VERSION = "1.0";
@ISA = qw(Exporter);
@EXPORT = qw();
}
sub AUTOLOAD
{
my $self = shift;
my $type = ref($self) or die "$self is not an object";
my $name = $Categories::AUTOLOAD;
$name =~ s/.*://; # Strip fully-qualified portion
$name = lc($name);
unless (exists $self->{__subs}->{$name})
{
print "Can't access '$name' field in class $type";
exit;
}
my $ref = $self->{__subs}->{$name};
&$ref($self,@_);
}
sub new
{
my $proto = shift;
my $class = ref($proto) || $proto;
my $self = {};
my %inp = @_;
$self->{'create'} = $inp{'create'} || 'Y';
$self->{'checkdb'} = $inp{'checkdb'} || 'Y';
$self->{'name'} = $inp{'name'} || 'catdb';
$self->{'database'} = $inp{'database'} || undef;
$self->{'user'} = $inp{'user'} || '';
$self->{'pass'} = $inp{'pass'} || '';
$self->{'host'} = $inp{'host'} || 'localhost';
$self->{'port'} = abs(int($inp{'port'})) || '3306';
$self->{'structure'} = $inp{'structure'} || \@Categories::structure;
$self->{'structure_tables'} = $inp{'structure_tables'} || \@Categories::structure_tables;
$self->{'dbh'} = $inp{'dbh'} || undef;
$self->{'error'} = '';
$self->{'categories'} = '';
$self->{'__subs'} = {};
$self->{'__subs'}->{'init'} = $inp{'init'} || \&__category_init;
bless($self,$class);
if($self->init() eq undef)
{
return(undef);
}
return($self);
}
sub _set_val_Categories
{
my $self = shift(@_);
my $name = shift(@_);
my @params = @_;
if(defined($_[0]))
{
my $code = '$self->{'."'$name'".'} = $_[0];';
eval $code;
return($_[0]);
}
else
{
my $code = '$code = $self->{'."'$name'".'};';
eval $code;
return($code);
}
}
sub error { shift->_set_val_Categories('error', @_); $Categories::error = $_[0];}
sub create { shift->_set_val_Categories('create', @_); }
sub checkdb { shift->_set_val_Categories('checkdb', @_); }
sub database { shift->_set_val_Categories('database', @_); }
sub name { shift->_set_val_Categories('name', @_); }
sub user { shift->_set_val_Categories('user', @_); }
sub pass { shift->_set_val_Categories('pass', @_); }
sub host { shift->_set_val_Categories('host', @_); }
sub port { shift->_set_val_Categories('port', abs(int($_[0]))); }
sub structure { shift->_set_val_Categories('structure', @_); }
sub structure_tables { shift->_set_val_Categories('structure_tables', @_); }
sub categories { shift->_set_val_Categories('categories', @_); }
sub dbh { shift->_set_val_Categories('dbh', @_); }
sub __category_init
{
my ($self) = @_;
my $code = << 'CODE_TERM';
if($self->{'dbh'} eq undef)
{
use DBI;
}
CODE_TERM
eval $code;
if($@ ne '')
{
$self->error("Can't load module DBI.pm");
return(undef);
}
# Check database (try connect) if db handler is empty
if($self->{'dbh'} eq undef)
{
my $port = $self->{'port'} eq '' ? '' : ';port='.$self->{'port'};
my $dbh = DBI->connect("DBI:mysql:".$self->{'database'}.":".$self->{'host'}.$port,$self->{'user'},$self->{'pass'});
if($dbh)
{
$self->{'dbh'} = $dbh;
}
else
{
if($self->{'checkdb'} =~ m/^(Y|YES|1)$/si)
{
if($DBI::err == 1049) # 1049 Unknown database
{
if($self->{'create'} =~ m/^(Y|YES|1)$/si)
{
my $drh = DBI->install_driver("mysql");
my $rc = $drh->func('createdb', $self->{'database'}, $self->{'host'}, $self->{'user'}, $self->{'pass'}, 'admin');
if($rc)
{
my $port = $self->{'port'} eq '' ? '' : ';port='.$self->{'port'};
my $dbh = DBI->connect("DBI:mysql:".$self->{'database'}.":".$self->{'host'}.$port,$self->{'user'},$self->{'pass'});
if($dbh)
{
$self->{'dbh'} = $dbh;
}
else
{
$self->error("Can't connect to database '".$self->{'database'}."'(database is just created)! Error message: ".$DBI::errstr);
return(undef);
}
}
else
{
$self->error("Error message: ".$DBI::errstr);
return(undef);
}
}
else
{
$self->error("Can't connect to database '".$self->{'database'}."'! Error message: ".$DBI::errstr);
return(undef);
}
}
elsif($DBI::err == 1045) # Access denied
{
$self->error("Can't connect to database '".$self->{'database'}."'! Access denied!");
return(undef);
}
else
{
$self->error("Can't connect to database '".$self->{'database'}."'! Error message: ".$DBI::errstr);
return(undef);
}
}
else
{
$self->error("Can't connect to database '".$self->{'database'}."'!");
return(undef);
}
}
}
my $cats_ref = $self->{'structure_tables'};
my @cats_s_table = @$cats_ref;
my $cats_ref = $self->{'structure'};
my @cats_table = @$cats_ref;
# Check tables
if($self->{'checkdb'} =~ m/^(Y|YES|1)$/si)
{
if($self->{'dbh'})
{
my $dbh = $self->{'dbh'};
my $sth = $dbh->prepare("SHOW TABLES");
$sth->execute;
my $numRows = $sth->rows;
my ($i,$l,$ind);
my @rows;
for($i=0; $i < $numRows; $i++)
{
my $aref = $sth->fetchrow_arrayref();
push(@rows,$$aref[0]);
}
$sth->finish();
$ind = 0;
foreach $l (@cats_s_table)
{
$l =~ s/\%\%name\%\%/$self->{'name'}/sgi;
$l =~ s/\%\%database\%\%/$self->{'database'}/sgi;
$l =~ s/\%\%user\%\%/$self->{'user'}/sgi;
$l =~ s/\%\%pass\%\%/$self->{'pass'}/sgi;
my $counter = 0;
foreach $i (@rows)
{
if(lc($self->{'name'}.$l) eq lc($i))
{
$counter++;
}
}
if((!$counter) and ($self->{'create'} =~ m/^(Y|YES|1)$/si))
{
my $sqlq = $cats_table[$ind];
$sqlq =~ s/\%\%name\%\%/$self->{'name'}/sgi;
$sqlq =~ s/\%\%database\%\%/$self->{'database'}/sgi;
$sqlq =~ s/\%\%user\%\%/$self->{'user'}/sgi;
$sqlq =~ s/\%\%pass\%\%/$self->{'pass'}/sgi;
if(!$dbh->do($sqlq))
{
$self->error("Can't create table '".$self->{'name'}.$l."'! Error message: ".$DBI::errstr);
return(undef);
}
}
$ind++;
}
}
}
return(1);
}
sub is_tables_exists
{
my $self = shift;
my $name = shift || $self->{'name'};
my $counter = 0;
my $cats_ref = $self->{'structure_tables'};
my @cats_s_table = @$cats_ref;
my $cats_ref = $self->{'structure'};
my @cats_table = @$cats_ref;
if($self->{'dbh'})
{
my $dbh = $self->{'dbh'};
my $sth = $dbh->prepare("SHOW TABLES");
$sth->execute;
my $numRows = $sth->rows;
my ($i,$l,$ind);
my @rows;
for($i=0; $i < $numRows; $i++)
{
my $aref = $sth->fetchrow_arrayref();
push(@rows,$$aref[0]);
}
$sth->finish();
$ind = 0;
foreach $l (@cats_s_table)
{
$l =~ s/\%\%name\%\%/$name/sgi;
$l =~ s/\%\%database\%\%/$self->{'database'}/sgi;
$l =~ s/\%\%user\%\%/$self->{'user'}/sgi;
$l =~ s/\%\%pass\%\%/$self->{'pass'}/sgi;
foreach $i (@rows)
{
if(lc($name.$l) eq lc($i))
{
$counter++;
}
}
$ind++;
}
}
else
{
$self->error("Database handler is 'undef'! Please connect to DB fisrt!");
return(undef);
}
if($counter != scalar(@cats_s_table)) {return(0);}
return(1);
}
sub create_tables
{
my $self = shift;
my $name = shift || $self->{'name'};
my $counter = 0;
my $matched = 0;
my $t_counter = 0;
my $cats_ref = $self->{'structure_tables'};
my @cats_s_table = @$cats_ref;
my $cats_ref = $self->{'structure'};
my @cats_table = @$cats_ref;
if($self->{'dbh'})
{
my $dbh = $self->{'dbh'};
my $sth = $dbh->prepare("SHOW TABLES");
$sth->execute;
my $numRows = $sth->rows;
my ($i,$l,$ind);
my @rows;
for($i=0; $i < $numRows; $i++)
{
my $aref = $sth->fetchrow_arrayref();
push(@rows,$$aref[0]);
}
$sth->finish();
$ind = 0;
foreach $l (@cats_s_table)
{
$l =~ s/\%\%name\%\%/$name/sgi;
$l =~ s/\%\%database\%\%/$self->{'database'}/sgi;
$l =~ s/\%\%user\%\%/$self->{'user'}/sgi;
$l =~ s/\%\%pass\%\%/$self->{'pass'}/sgi;
$counter = 0;
foreach $i (@rows)
{
if(lc($name.$l) eq lc($i))
{
$counter++;
}
}
if(!$counter)
{
my $sqlq = $cats_table[$ind];
$sqlq =~ s/\%\%name\%\%/$name/sgi;
$sqlq =~ s/\%\%database\%\%/$self->{'database'}/sgi;
$sqlq =~ s/\%\%user\%\%/$self->{'user'}/sgi;
$sqlq =~ s/\%\%pass\%\%/$self->{'pass'}/sgi;
if(!$dbh->do($sqlq))
{
$self->error("Can't create table '".$name.$l."'! Error message: ".$DBI::errstr);
}
else
{
$t_counter++;
}
}
else
{
$matched++;
}
$ind++;
}
}
else
{
$self->error("Database handler is 'undef'! Please connect to DB fisrt!");
return(undef);
}
if(($t_counter+$matched) != scalar(@cats_s_table)) {return(0);}
return(1);
}
sub clear_cache
{
my $self = shift;
$self->{'categories'} = '';
return(1);
}
sub preload_categories
{
my $self = shift;
my %inp = @_;
my $name = $inp{'name'} || $self->name();
my $sort = $inp{'sort'} || 'NAME'; # Sort Items/Categories by $sort
my $reverse = $inp{'reverse'} || undef; # Reverse selected Categories
my @cats = ();
my $dbh = $self->{'dbh'};
my $cats_ref = $self->{'structure_tables'};
my @cats_s_table = @$cats_ref;
my $cats_ref = $self->{'structure'};
my @cats_table = @$cats_ref;
if(ref($self->{'categories'}))
{
my $r = $self->{'categories'};
@cats = @$r;
}
else
{
my $order = " ORDER BY $sort";
my $rev = '';
if($reverse =~ m/^(Y|YES|1)$/si){$rev = ' DESC';}
my $q = "SELECT * FROM ".$name.$cats_s_table[0].$order.$rev;
my $sth = $dbh->prepare($q);
$sth->execute();
my $ref;
while ($ref = $sth->fetchrow_hashref())
{
my %row = %$ref;
push(@cats,\%row);
}
$sth->finish();
$self->{'categories'} = \@cats;
}
return(@cats);
}
sub find
{
my $self = shift;
my %inp = @_;
my $caseinsensitive = $inp{'caseinsensitive'} || 'Y';
my $filter = $inp{'filter'} || 'ITEMS'; # Items only, don't mutch categories,
# applicables are: 'ITEMS','ALL','CATEGORIES'
my $multiple = $inp{'multiple'} || 'Y'; # Return many rows of results.
my $by = $inp{'by'} || 'ID'; # Search by 'ID', applicables are:
# 'ID','NAME','CID','PARENT','VALUE'
my $sort = $inp{'sort'} || 'NAME'; # Sort by 'NAME', applicables are:
# 'ID','NAME','CID','PARENT','VALUE'
my $reverse = $inp{'reverse'} || undef; # Reverse selected Categories
my $partial = $inp{'partial'} || undef; # Allows search on partial keyword
my $search = $inp{'search'} || undef;
my $check = $inp{'check'} || undef; # Check mode
my $route = $inp{'route'} || 'N';
my $separator = $inp{'separator'} || '//';
my $preload = $inp{'preload'} || 'Y'; # Default load all categories in memmory before
# searching. This speed up process but for very
# large DBs this may crush!
my @cats = ();
my @res = ();
my @tmp = ();
my $dbh = $self->{'dbh'};
my $limits = '';
my $order = '';
my $where = '';
my $srch = '';
my $case = '';
my $cats_ref = $self->{'structure_tables'};
my @cats_s_table = @$cats_ref;
my $cats_ref = $self->{'structure'};
my @cats_table = @$cats_ref;
if($search eq undef)
{
$self->error("'Search' text is empty!");
return(undef);
}
if(($by =~ m/^PARENT$/i) and ($filter =~ m/^ITEMS$/i))
{
$self->error("Can't search by PARENT in ITEMS context!");
return(undef);
}
if(($sort =~ m/^PARENT$/i) and ($filter =~ m/^ITEMS$/i))
{
$self->error("Can't sort(search) by PARENT in ITEMS context!");
return(undef);
}
if($check ne undef)
{
if(!$self->is_tables_exists())
{
$self->error("Database(table) structure is not available!");
return(undef);
}
}
if($partial =~ m/^(Y|YES|1)$/si)
{
$search = '%'.$search.'%';
$case = ' LIKE ';
}
else
{
$case = ' = ';
}
if($dbh)
{
if(($preload =~ m/^(Y|YES|1)$/si) and ($route =~ m/^(Y|YES|1)$/si))
{
@cats = $self->preload_categories('sort'=>$sort,'reverse'=>$reverse);
}
if($caseinsensitive =~ m/^(Y|YES|1)$/si)
{
$search = uc($search);
$where = " WHERE UPPER(".$by.")".$case;
}
else
{
$where = " WHERE ".$by.$case;
}
if($multiple =~ m/^(Y|YES|1)$/si)
{
$limits = '';
}
else
{
$limits = ' LIMIT 0,1';
}
$order = " ORDER BY $sort";
my $rev = '';
if($reverse =~ m/^(Y|YES|1)$/si){$rev = ' DESC';}
$srch = $dbh->quote($search);
if($filter =~ m/^(ITEMS|ALL)$/si)
{
my $q = "SELECT * FROM ".$self->name().$cats_s_table[1].$where.$srch.$order.$limits.$rev;
my $sth = $dbh->prepare($q);
$sth->execute();
my $ref;
while ($ref = $sth->fetchrow_arrayref())
{
my @row = @$ref;
unshift(@row,'I');
push(@res,\@row);
}
$sth->finish();
}
if($filter =~ m/^(CATEGORIES|ALL)$/si)
{
my $q = "SELECT * FROM ".$self->name().$cats_s_table[0].$where.$srch.$order.$limits.$rev;
my $sth = $dbh->prepare($q);
$sth->execute();
my $ref;
while ($ref = $sth->fetchrow_arrayref())
{
my @row = @$ref;
unshift(@row,'C');
push(@res,\@row);
}
$sth->finish();
}
if($route =~ m/^(Y|YES|1)$/si)
{
my $t;
foreach $t (@res)
{
my $found = 0;
my @row = @$t;
my $CID = $row[2]; # Get PARENT or CID field.
my $iname = '';
my $path = $separator;
while($CID != 0)
{
if($preload =~ m/^(Y|YES|1)$/si)
{
my $sc;
foreach $sc (@cats)
{
my %rh = %$sc;
my $id = $rh{'ID'};
if($id eq $CID)
{
$CID = $rh{'PARENT'};
my $ID = $rh{'ID'};
$iname = $rh{'NAME'};
$path = $separator.$ID."\x0".$iname.$path;
$found = 1; last;
}
}
if(!$found) {$CID = 0;}
}
else
{
my $qCID = $dbh->quote($CID);
my $q = "SELECT ID,PARENT,NAME FROM ".$self->name().$cats_s_table[0]." WHERE ID=$qCID";
my $sth = $dbh->prepare($q);
$sth->execute();
my $ref;
$ref = $sth->fetchrow_arrayref();
if(ref($ref))
{
my @row = @$ref;
$CID = $row[1];
$iname = $row[2];
my $ID = $row[0];
$path = $separator.$ID."\x0".$iname.$path;
}
else
{
$CID = 0;
}
$sth->finish();
}
}
push(@row,$path);
push(@tmp,\@row);
}
@res = @tmp;
}
return(@res);
}
else
{
$self->error("Database handler is 'undef'! Please connect to DB fisrt!");
return(undef);
}
return(undef);
}
sub add
{
my $self = shift;
my %inp = @_;
my $type = $inp{'type'} || 'ITEM'; # Type: 'ITEM' or 'CATEGORY'
my $category = $inp{'category'} || undef; # Category ID; 0 is root
my $name = $inp{'name'} || undef; # Item/Category name
my $value = $inp{'value'} || undef; # Item value (for type 'ITEM' only)
my $check = $inp{'check'} || undef; # Check mode
my $q;
my $dbh = $self->{'dbh'};
my $cats_ref = $self->{'structure_tables'};
my @cats_s_table = @$cats_ref;
my $cats_ref = $self->{'structure'};
my @cats_table = @$cats_ref;
if($name eq undef)
{
$self->error("Can't ADD item/category with empty name!");
return(undef);
}
if($check ne undef)
{
if(!$self->is_tables_exists())
{
$self->error("Database(table) structure is not available!");
return(undef);
}
}
if($category eq undef) {$category = 0;}
my $q_category = $dbh->quote($category);
my $q_name = $dbh->quote($name);
if($type =~ m/^ITEM/si)
{
my $q_value = $dbh->quote($value);
$q = "INSERT INTO ".$self->name().$cats_s_table[1]." SET CID=$q_category, NAME=$q_name, VALUE=$q_value";
}
elsif($type =~ m/^CATEGORY/si)
{
$q = "INSERT INTO ".$self->name().$cats_s_table[0]." SET PARENT=$q_category, NAME=$q_name";
$self->{'categories'} = ''; # Category changes are made, we need to reload categiries!
}
else
{
$self->error("Unrecognized type!");
return(undef);
}
my $sth = $dbh->prepare($q);
$sth->execute();
my $row = $dbh->{'mysql_insertid'};
$sth->finish();
return($row);
}
sub del
{
my $self = shift;
my %inp = @_;
my $type = $inp{'type'} || 'ITEM'; # Type: 'ITEM' or 'CATEGORY'
my $id = $inp{'id'}; # Item/Category id
my $check = $inp{'check'} || undef; # Check mode
my $preload = $inp{'preload'} || 'Y'; # Default load all categories in memmory before
# searching. This speed up process but for very
# large DBs this may crush!
my $q;
my $dbh = $self->{'dbh'};
my $row;
my $cats_ref = $self->{'structure_tables'};
my @cats_s_table = @$cats_ref;
my $cats_ref = $self->{'structure'};
my @cats_table = @$cats_ref;
if($id eq undef)
{
$self->error("Can't DEL item/category with empty id!");
return(undef);
}
if($check ne undef)
{
if(!$self->is_tables_exists())
{
$self->error("Database(table) structure is not available!");
return(undef);
}
}
my $q_id = $dbh->quote($id);
if($type =~ m/^ITEM/si)
{
$q = "DELETE FROM ".$self->name().$cats_s_table[1]." WHERE ID=$q_id";
my $sth = $dbh->prepare($q);
$sth->execute();
$row = $sth->rows();
$sth->finish();
}
elsif($type =~ m/^CATEGORY/si)
{
$row = $self->traverse('eval'=>\&__category_del,'cid'=>$id,'preload'=>$preload);
$self->{'categories'} = ''; # Category changes are made, we need to reload categiries!
}
else
{
$self->error("Unrecognized type!");
return(undef);
}
return($row);
}
sub __category_del
{
my $self = shift;
my %inp = @_;
my $id = $inp{'id'};
my $parent = $inp{'parent'};
my $dbh = $self->{'dbh'};
my $qCID = $dbh->quote($id);
my $q;
my $count = 0;
my $cats_ref = $self->{'structure_tables'};
my @cats_s_table = @$cats_ref;
my $cats_ref = $self->{'structure'};
my @cats_table = @$cats_ref;
if($id != 0)
{
$q = "DELETE FROM ".$self->name().$cats_s_table[0]." WHERE ID=$qCID";
my $sth = $dbh->prepare($q);
$sth->execute();
$count += $sth->rows;
$sth->finish();
}
$q = "DELETE FROM ".$self->name().$cats_s_table[1]." WHERE CID=$qCID";
my $sth = $dbh->prepare($q);
$sth->execute();
$count += $sth->rows;
$sth->finish();
return($count);
}
sub modify
{
my $self = shift;
my %inp = @_;
my $type = $inp{'type'} || 'ITEM'; # Type: 'ITEM' or 'CATEGORY'
my $id = $inp{'id'} || undef; # Item/Category id
my $newcid = $inp{'newcid'}; # New 'parent' ID (CID/PARENT)
my $check = $inp{'check'} || undef; # Check mode
my $name = $inp{'name'} || undef; # ITEM/CATEGORY name
my $value = exists($inp{'value'}) ? $inp{'value'} : undef; # ITEM value
my $q;
my ($table_name,$set);
my $aff = 0;
my $dbh = $self->{'dbh'};
my $cats_ref = $self->{'structure_tables'};
my @cats_s_table = @$cats_ref;
my $cats_ref = $self->{'structure'};
my @cats_table = @$cats_ref;
if($id eq undef)
{
$self->error("Can't MODIFY item/category with empty id!");
return(undef);
}
if($check ne undef)
{
if(!$self->is_tables_exists())
{
$self->error("Database(table) structure is not available!");
return(undef);
}
}
if($type =~ m/^ITEM/si)
{
$table_name = $self->name().$cats_s_table[1];
if($newcid ne undef)
{
my $qcid = $dbh->quote($newcid);
$set = "CID=$qcid";
}
if($value ne undef)
{
my $qvalue = $dbh->quote($value);
if($set) {$set .= ",";}
$set .= "VALUE=$qvalue";
}
}
elsif($type =~ m/^CATEGORY/si)
{
$table_name = $self->name().$cats_s_table[0];
if($newcid ne undef)
{
my $qcid = $dbh->quote($newcid);
$set = "PARENT=$qcid";
}
}
else
{
$self->error("Unrecognized type!");
return(undef);
}
if($name ne undef)
{
my $qname = $dbh->quote($name);
if($set ne '') {$set .= ",";}
$set .= "NAME=$qname";
}
if($set ne '')
{
my $q_id = $dbh->quote($id);
$q = "UPDATE $table_name SET $set WHERE ID=$q_id";
my $sth = $dbh->prepare($q);
$sth->execute();
$aff = $sth->rows;
$sth->finish();
}
if($aff)
{
$self->clear_cache();
}
return($aff);
}
sub traverse
{
my $self = shift;
my %inp = @_;
my $cid = $inp{'cid'}; # Category id
my $evala = $inp{'eval'} || undef; # Code that will be evaluated
my $check = $inp{'check'} || undef; # Check mode
my $sort = $inp{'sort'} || 'NAME'; # Sort Items/Categories by $sort
my $reverse = $inp{'reverse'} || undef; # Reverse selected Categories
my $preload = $inp{'preload'} || 'Y'; # Default load all categories in memmory before
# searching. This speed up process but for very
# large DBs this may crush!
my $q;
my $dbh = $self->{'dbh'};
my @queue = ();
my @cats = ();
my $current = '';
my $cnt = 0;
my $cats_ref = $self->{'structure_tables'};
my @cats_s_table = @$cats_ref;
my $cats_ref = $self->{'structure'};
my @cats_table = @$cats_ref;
if($cid eq undef)
{
$self->error("Can't TRAVERSE item/category with empty cid!");
return(undef);
}
if($check ne undef)
{
if(!$self->is_tables_exists())
{
$self->error("Database(table) structure is not available!");
return(undef);
}
}
if($preload =~ m/^(Y|YES|1)$/si)
{
@cats = $self->preload_categories('sort'=>$sort,'reverse'=>$reverse);
}
if($cid == 0)
{
my $qCID = $dbh->quote($cid);
if(scalar(@cats))
{
my $sc;
foreach $sc (@cats)
{
my %rh = %$sc;
my $cid = $rh{'PARENT'};
if($cid == 0)
{
my $id = $rh{'ID'};
unshift(@queue,$id);
}
}
}
else
{
$q = "SELECT ID FROM ".$self->name().$cats_s_table[0]." WHERE PARENT=$qCID";
my $sth = $dbh->prepare($q);
$sth->execute();
my $ref;
while($ref = $sth->fetchrow_arrayref())
{
my @row = @$ref;
unshift(@queue,$row[0]);
}
$sth->finish();
}
$current = 0;
my $qCID = $dbh->quote($current);
$cnt++;
if(ref($evala))
{
&$evala($self,'id'=>$current,'parent'=>$cid);
}
else
{
eval $evala;
}
}
else
{
unshift(@queue,$cid);
}
while(scalar(@queue))
{
$current = pop(@queue);
my $qCID = $dbh->quote($current);
if(scalar(@cats))
{
my $sc;
foreach $sc (@cats)
{
my %rh = %$sc;
my $cid = $rh{'PARENT'};
if($cid == $current)
{
my $id = $rh{'ID'};
unshift(@queue,$id);
}
}
}
else
{
$q = "SELECT ID FROM ".$self->name().$cats_s_table[0]." WHERE PARENT=$qCID";
my $sth = $dbh->prepare($q);
$sth->execute();
my $ref;
while($ref = $sth->fetchrow_arrayref())
{
my @row = @$ref;
unshift(@queue,$row[0]);
}
$sth->finish();
}
$cnt++;
if(ref($evala))
{
&$evala($self,'id'=>$current,'parent'=>$cid);
}
else
{
eval $evala;
}
}
return($cnt);
}
sub deep_traverse
{
my $self = shift;
my %inp = @_;
my $id = $inp{'id'};
my $level = $inp{'level'};
my $separator = $inp{'separator'} || '//';
my $path = $inp{'path'};
my $evala = $inp{'eval'} || undef; # Code that will be evaluated
my $sort = $inp{'sort'} || 'NAME'; # Sort Items/Categories by $sort
my $reverse = $inp{'reverse'} || undef; # Reverse selected Categories
my $preload = $inp{'preload'} || 'Y'; # Default load all categories in memmory before
# searching. This speed up process but for very
# large DBs this may crush!
my $dbh = $self->{'dbh'};
my @cats = ();
my $q;
my ($i,$item,$item_name,$item_value);
my $whereis = 'C';
my $cats_ref = $self->{'structure_tables'};
my @cats_s_table = @$cats_ref;
if($preload =~ m/^(Y|YES|1)$/si)
{
@cats = $self->preload_categories('sort'=>$sort,'reverse'=>$reverse);
}
$level++;
if(ref($evala))
{
&$evala($self,'id'=>$id,'level'=>$level,'type'=>$whereis,'path'=>$path,'separator'=>$separator);
}
else
{
eval $evala;
}
my @all_level_rows = ();
my @all_level_rows_names = ();
my @all_level_rows_values = ();
my $current_index = 0;
my $all_current_rows = 0;
my $qCID = $dbh->quote($id);
if(scalar(@cats))
{
my $sc;
foreach $sc (@cats)
{
my %rh = %$sc;
my $cid = $rh{'PARENT'};
if($cid == $id)
{
my ($id,$name) = ($rh{'ID'},$rh{'NAME'});
unshift(@all_level_rows,$id);
unshift(@all_level_rows_names,$name);
}
}
}
else
{
my $order = " ORDER BY $sort";
my $rev = '';
if($reverse =~ m/^(Y|YES|1)$/si){$rev = ' DESC';}
$q = "SELECT ID,NAME FROM ".$self->name().$cats_s_table[0]." WHERE PARENT=$qCID".$order.$rev;
my $sth = $dbh->prepare($q);
$sth->execute();
my $ref;
while($ref = $sth->fetchrow_arrayref())
{
my @row = @$ref;
unshift(@all_level_rows,$row[0]);
unshift(@all_level_rows_names,$row[1]);
}
$sth->finish();
}
$all_current_rows = scalar(@all_level_rows);
foreach $i (1..$all_current_rows)
{
my $this = pop(@all_level_rows);
my $this_name = pop(@all_level_rows_names);
$self->deep_traverse(id=>$this,level=>$level,separator=>$separator,
path=>$path.$this."\x0".$this_name.$separator,eval=>$evala);
}
@all_level_rows = ();
@all_level_rows_names = ();
@all_level_rows_values = ();
my $qCID = $dbh->quote($id);
my $order = " ORDER BY $sort";
my $rev = '';
if($reverse =~ m/^(Y|YES|1)$/si){$rev = ' DESC';}
$q = "SELECT ID,NAME,VALUE FROM ".$self->name().$cats_s_table[1]." WHERE CID=$qCID".$order.$rev;
my $sth = $dbh->prepare($q);
$sth->execute();
my $ref;
while($ref = $sth->fetchrow_arrayref())
{
my @row = @$ref;
push(@all_level_rows,$row[0]);
push(@all_level_rows_names,$row[1]);
push(@all_level_rows_values,$row[2]);
}
$sth->finish();
$whereis = 'I';
foreach $item (@all_level_rows)
{
$item_name = shift(@all_level_rows_names);
$item_value = shift(@all_level_rows_values);
if(ref($evala))
{
&$evala($self,'id'=>$id,'level'=>$level,'type'=>$whereis,'path'=>$path,'name'=>$item_name,
'value'=>$item_value,'separator'=>$separator);
}
else
{
eval $evala;
}
}
return(1);
}
sub load_category
{
my $self = shift;
my %inp = @_;
my $cid = $inp{'cid'}; # Category id
my $sort = $inp{'sort'} || 'NAME'; # Sort Items/Categories by $sort
my $reverse = $inp{'reverse'} || undef; # Reverse selected Categories
my $preload = $inp{'preload'} || 'Y'; # Default load all categories in memmory before
# searching. This speed up process but for very
# large DBs this may crush!
my @res;
my @cats;
my $dbh = $self->{'dbh'};
my $q;
my $cats_ref = $self->{'structure_tables'};
my @cats_s_table = @$cats_ref;
if($preload =~ m/^(Y|YES|1)$/si)
{
@cats = $self->preload_categories('sort'=>$sort,'reverse'=>$reverse);
}
my $qCID = $dbh->quote($cid);
if(scalar(@cats))
{
my $sc;
foreach $sc (@cats)
{
my %rh = %$sc;
my $parent = $rh{'PARENT'};
if($parent == $cid)
{
my @mf = ('C',$rh{'ID'},$rh{'NAME'},'');
unshift(@res,\@mf);
}
}
}
else
{
$q = "SELECT ID,NAME FROM ".$self->name().$cats_s_table[0]." WHERE PARENT=$qCID";
my $sth = $dbh->prepare($q);
$sth->execute();
my $ref;
while($ref = $sth->fetchrow_arrayref())
{
my @row = @$ref;
my @mf = ('C',$row[0],$row[1],'');
unshift(@res,\@mf);
}
$sth->finish();
}
$q = "SELECT ID,NAME,VALUE FROM ".$self->name().$cats_s_table[1]." WHERE CID=$qCID";
my $sth = $dbh->prepare($q);
$sth->execute();
my $ref;
while($ref = $sth->fetchrow_arrayref())
{
my @row = @$ref;
my @mf = ('I',$row[0],$row[1],$row[2]);
push(@res,\@mf);
}
$sth->finish();
return(@res);
}
sub read
{
my $self = shift;
my %inp = @_;
my $caseinsensitive = $inp{'caseinsensitive'} || 'Y';
my $sort = $inp{'sort'} || 'ID'; # Sort by 'NAME', applicables are:
# 'ID','NAME','CID','PARENT','VALUE'
my $reverse = $inp{'reverse'} || undef; # Reverse selected Categories
my $path = $inp{'path'} || undef;
my $partial = $inp{'partial'} || undef; # Allows search on partial keyword (ITEMS only)
my $check = $inp{'check'} || undef; # Check mode
my $separator = $inp{'separator'} || '//';
my $preload = $inp{'preload'} || 'Y'; # Default load all categories in memmory before
# searching. This speed up process but for very
# large DBs this may crush!
my @cats = ();
my @res = ();
my @parts = ();
my $item = '';
my $dbh = $self->{'dbh'};
my $order = '';
my $where = '';
my ($l,$value,$mpar,$mpath) = ();
my $parent = '0';
my $result = '';
my $case = '';
my $cats_ref = $self->{'structure_tables'};
my @cats_s_table = @$cats_ref;
my $cats_ref = $self->{'structure'};
my @cats_table = @$cats_ref;
if($path eq undef)
{
$self->error("'Path' is empty!");
return(undef);
}
if($check ne undef)
{
if(!$self->is_tables_exists())
{
$self->error("Database(table) structure is not available!");
return(undef);
}
}
if($dbh)
{
if(($preload =~ m/^(Y|YES|1)$/si))
{
@cats = $self->preload_categories('sort'=>$sort,'reverse'=>$reverse);
}
$order = " ORDER BY $sort";
my $rev = '';
if($reverse =~ m/^(Y|YES|1)$/si){$rev = ' DESC';}
my $qsep = quotemeta($separator);
@parts = split(/$separator/s,$path);
if($path =~ m/$separator$/s)
{
$item = '';
}
else
{
$item = pop(@parts);
}
foreach $l (@parts)
{
if($parent eq undef)
{
$self->error("Can't find part of category path! Please check you category tree!");
return(undef);
}
$l =~ s/\ {1,}$//si;
$l =~ s/^\ {1,}//si;
if($l ne '')
{
if($preload =~ m/^(Y|YES|1)$/si)
{
my $sc;
my $state = 0;
foreach $sc (@cats)
{
my %rh = %$sc;
my $cid = $rh{'PARENT'};
my $name = $rh{'NAME'};
if($caseinsensitive =~ m/^(Y|YES|1)$/si)
{
if((uc($name) eq uc($l)) and ($cid == $parent))
{
my $ID = $rh{'ID'};
$result = $ID;
$mpar = $parent;
$mpath .= $separator.$ID."\x0".$name;
$state = 1;
last;
}
}
else
{
if(($name eq $l) and ($cid == $parent))
{
my $ID = $rh{'ID'};
$result = $ID;
$mpar = $parent;
$mpath .= $separator.$ID."\x0".$name;
$state = 1;
last;
}
}
}
if(!$state) {$result = undef;}
$parent = $result;
}
else
{
if($caseinsensitive =~ m/^(Y|YES|1)$/si)
{
$l = uc($l);
$where = " WHERE UPPER(NAME)=";
}
else
{
$where = " WHERE NAME=";
}
my $qCID = $dbh->quote($parent);
my $qname = $dbh->quote($l);
my $q = "SELECT ID,PARENT,NAME FROM ".$self->name().$cats_s_table[0].$where."$qname AND PARENT=$qCID".$order;
my $sth = $dbh->prepare($q);
$sth->execute();
my $ref;
$ref = $sth->fetchrow_arrayref();
if(ref($ref))
{
my @row = @$ref;
my $ID = $row[0];
$result = $ID;
$mpar = $parent;
$mpath .= $separator.$ID."\x0".$l;
}
else
{
$result = undef;
}
$sth->finish();
$parent = $result;
}
}
}
if($item ne '')
{
if($item eq '*') {$item = '';}
if($partial =~ m/^(Y|YES|1)$/si)
{
$item = '%'.$item.'%';
$case = ' LIKE ';
}
else
{
$case = ' = ';
}
if($caseinsensitive =~ m/^(Y|YES|1)$/si)
{
$l = uc($item);
$where = " WHERE UPPER(NAME)$case";
}
else
{
$where = " WHERE NAME$case";
}
my $qCID = $dbh->quote($result);
my $qname = $dbh->quote($l);
my $q = "SELECT ID,CID,NAME,VALUE FROM ".$self->name().$cats_s_table[1].$where."$qname AND CID=$qCID".$order;
my $sth = $dbh->prepare($q);
$sth->execute();
my $ref;
if($sth->rows())
{
while($ref = $sth->fetchrow_arrayref())
{
my @row = @$ref;
push(@row,$mpath.$separator);
push(@res,\@row);
}
$sth->finish();
}
else
{
$self->error("Can't find item specified by category path! Please check you category tree!");
return(undef);
}
}
else
{
my @row = ($result,$mpar,'','',$mpath);
push(@res,\@row);
}
return(@res);
}
else
{
$self->error("Database handler is 'undef'! Please connect to DB fisrt!");
return(undef);
}
return(undef);
}
sub DESTROY
{
my $self = shift;
if($self->{'dbh'})
{
my $dbh = $self->{'dbh'};
$dbh->disconnect();
}
1;
}
1;
__END__