[ic] TIPS AND TRICKS: Tree example

[interchange-users@interchange.redhat.com]

It seems people are having a bit of trouble with trees and how
they might be used. I have produced an example.

----- Example for tree with toggling categories ---

Prerequisites:

* SQL database. Trees are not recommended for DBM databases as the
  selects will be too slow.

* This example assumes you have added a field to your products
  database named comb_category. Use your SQL command line utility
  to add it:

    alter table products add column comb_category varchar(255);

  It will be populated with colon-separated categories. Make sure you
  reconfig the catalog ("Apply changes") to pick up the new field.

* You can populate it from prod_group and category with this code:

    [flag type=write table=products]
    [perl tables=products]
	my $db = $Db{products};
	my $ary = $db->query('select sku,prod_group,category from products');
	my $out = '';
	foreach $row ( @$ary) {
	    my ($sku, $pg, $cat) = @$row;
	    $db->set_field($sku, 'comb_category', "$pg:$cat")
		and $out .= "$sku = $pg:$cat<br>\n";
	}
	return $out;
    [/perl]

Procedure:

1. To start, you need a tree database table with five fields:

    code,parent_fld,child_fld,name,category

This can be configured with dbconf/*/tree.*sql:

    Database  tree  tree.txt      __SQLDSN__
    ifdef SQLUSER
    Database  tree  USER         __SQLUSER__
    endif
    ifdef SQLPASS
    Database  tree  PASS         __SQLPASS__
    endif
    Database  tree  DEFAULT_TYPE  varchar(255)
    Database  tree  COLUMN_DEF   "code=varchar(20) NOT NULL PRIMARY KEY"
    Database  tree  COLUMN_DEF   "parent_fld=varchar(32) DEFAULT '' NOT NULL"
    Database  tree  COLUMN_DEF   "child_fld=varchar(32) DEFAULT '' NOT NULL"
    Database  tree  INDEX         parent_fld
    Database  tree  INDEX         child_fld
    Database  tree  INDEX         name

The above will work for either PostgreSQL or MySQL -- if you use Oracle
or another SQL you might have to change the field type from varchar.
 
Set up the fields by putting the TAB-separated fields in products/tree.txt:

code        parent_fld      child_fld       name    category

2. Reconfig the catalog ("Apply changes") to pick up all of this.

3. In the example, we will run some code to build a tree database based
on the contents of comb_category in the products table. The example deletes
all rows from "tree" every time that is done, so beware. Place the code
below in pages/tree_build.html:

[perl tables="products tree"]

    my $db = $Db{products};
    my $ary = $db->query('select sku,comb_category from products');
    my $out = '';
    my $code = '00000';
    my $cc  = '0000';
    my %done;
    my @levels;
    foreach $row ( @$ary) {
        my ($sku, $cat) = @$row;
        next if $done{$cat};
        my $parent = 'TOP';
        my @parts = split /:/, $cat;
        my $combname = '';
        for( my $i = 0; $i < @parts; $i++) {
            my $name = $parts[$i];
            my $comb = join ":", @parts[0 .. $i];
            my $level = $levels[$i] ||= {};
            if(! $level->{$name}) {
                $level->{$name} = [ ++$code, $parent, ++$cc, $name, $comb ];
                $parent = $cc;
            }
            else {
                $parent = $level->{$name}->[2];
            }
        }
    }

    my $tdb = $Db{tree};
    $tdb->query('delete from tree');
    my @flds = qw/parent_fld child_fld name category/;
    my $count++;
    for(@levels) {
        my $hash = $_;
        for(sort keys %$hash) {
            my $rec = $hash->{$_};
            my $code = shift @$rec;
            $tdb->set_slice($code, \@flds, $rec);
	    $count++;
        }
    }
    return $count;
[/perl]

Access that page.  All you should see is an integer indicating how
many records were created in the tree table. If you see nothing, check
the error log for a message indicating how the program died. If you
see 0 or a number less than your number of categories, the comb_category
field in products must be wrong.

5. Create a page pages/tree_display.html 

<P>
[page href=@@MV_PAGE@@ form="explode=1"]Explode tree[/page]&nbsp;&nbsp;
[page href=@@MV_PAGE@@ form="collapse=1"]Collapse tree[/page]
</P>
<table>
[tree   start=TOP
		table=tree
		master=parent_fld
		subordinate=child_fld
		autodetect=1
		toggle=toggle
		memo=memo
		collapse=collapse
		explode=explode
		spacing=4
		]
	<tr>
	<td>
	[if-item-param mv_level]
			[item-calc]
					return '&nbsp' x [item-param mv_spacing];
			[/item-calc]
	[/if-item-param]
	[if-item-param mv_children]
		<A HREF="[area href=@@MV_PAGE@@ form='toggle=[item-param code]']">
		[item-calc] $Scratch->{memo}{'[item-code]'} ? '-' : '+' [/item-calc]
		</A>
	[/if-item-param]
		[page search="
			fi=products
			st=db
			bs=1
			tf=comb_category
			sf=comb_category
			se=[item-param category]
		"][item-param name]</A>
	</td>
	<td>
			mv_level=[item-param mv_level],
			mv_increment=[item-param mv_increment],
			mv_children=[item-param mv_children]
	</td>
	</tr>
[/tree]
</table>

6. Display that page. It should show a toggling display of categories --
the link on + or - sign toggles, the link on the name displays all products
in that category and its subcategories.

If you did this on foundation or some other catalog with only two-level
categories, try editing the comb_category field to add a third category
in one of the products. I changed the comb_category field in os28074 to read
"Hand Tools:Hammers:Mallets" to test the example.

----- End example ----

Hope this helps people.

-- 
Red Hat, Inc., 3005 Nichols Rd., Hamilton, OH  45013
phone +1.513.523.7621      <mheins@redhat.com>

Being against torture ought to be sort of a bipartisan thing.
-- Karl Lehenbauer