Categories - Create and process categories within MySQL DB

VERSION ver.1.0


Categories allows you to create and process categories (for products/directories/shops and etc...)


 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);

   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);
   my $one_id  = $obj->add(type=>'item',name=>'The One',category=>$film_id);
   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);
   my $xp20_id = $obj->add(type=>'item',name=>'Athlon XP 2000+',category=>$cpu_id);
   my $xp21_id = $obj->add(type=>'item',name=>'Athlon XP 2100+',category=>$cpu_id);
   my $hdd1_id = $obj->add(type=>'item',name=>'Maxtor 80 GB',category=>$hdd_id);
   my $hdd2_id = $obj->add(type=>'item',name=>'Maxtor 120 GB',category=>$hdd_id);

   # 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,
      foreach $l (@res)
         my ($type,$id,$parent_category,$name,$route_path) = @$l;
         print "Type:   $type<BR>\n";
         print "ID:     $id<BR>\n";
         print "PARENT: $parent_category<BR>\n";
         print "NAME:   $name<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');


    # Delete ROOT category, so all items/categories are deleted!
    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'};
  my $type            = $inp{'type'};

  $path =~ s~$separator~\\~sgi;
  $path =~ s~\\(.*?)\x0~\\~sgi;
  print $path."$name<BR>";
 # --- Script ends here ---


 That is simple function reference:

 $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);
    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!

 $state = $object->is_tables_exists(name=>'name_of_category_object');
    Check database structure only.

 $state = $object->create_tables(name=>'name_of_category_object');
    Create table structure (database should exist!)

 $state = $object->clear_cache();
    Clear categories cache.

 @cats = $object->preload_categories(name=>'name_of_category_object', sort=>'NAME', reverse=>'N');
    Create categories cache and return array of all categories. @cats is array of references to hashes;
           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'};

 @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');
          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.
    @res is array of reference to arrays. Every element of array (dereferenced is array too) has follow structure:
          [0] - 'I' or 'C' (Item or Category),
          [1] - ID of Item/Category,
          [2] - PARENT (category ID),
          [3] - NAME of Item/Category,
          [4] - If route=>'Y' this will be full path to this Item/Category (//id\x0Computers//id\x0CPU...)
                where respective ID is separated from respective NAME with \x0 !
 $id = $object->add(type=>'ITEM', category=>'0', name=>'Name_Of_Element', check=>'N');
           type      - is type of element ('ITEM' and 'CATEGORY'),
           category  - is ID of parent (0 is root),
           name      - name of new item/category,
           check     - test tables structure.
           $id is ID of created element.
 $cnt = $object->del(type=>'ITEM', id=>'0', check=>'N', preload=>'Y');
            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.
 $cnt = $object->modify(type=>'ITEM', id=>'id_of_element', newcid=>'id_of_new_parent', check=>'N',
                        name=>'new_name_of_element', preload=>'Y');
            type      - is type of element ('ITEM' and 'CATEGORY'),
            id        - is ID of Item/Category,
            check     - test tables structure,
            name      - new name item/category (if you dismiss filed, NAME 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.

 $cnt = $object->traverse(cid=>'id_of_category', eval=>\&callback_sub', check=>'N',
                          sort=>'NAME', reverse=>'N', preload=>'Y');
      This sub traverse in width.
            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);
 $cnt = $object->deep_traverse(id=>'id_of_category', level=>'0', separator=>'//', path=>'//',
                               eval=>\&callback_sub', sort=>'NAME', reverse=>'N', check=>'N',
      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:

      where 'name' will be available only for Items (type=>'I'), but not for categories (type=>'C')
 $cnt = $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)!


 Julian Lishev - Bulgaria, Sofia,