Now let's assume that this will be the start of a large scale application and it's intended to serve the basis for other tree-like implementations. What will be the approach for writing a re-usable code ? How the software architecture and design will be looking ?
I will provide below the UI and datbase sketches togheter with the source code with some explanations. Anyone can easily try it on a CGI enabled HTTPD and MySQL DB.
Typing in parent node ID in “PID” box and clicking “Add” button should add a new node, to the node with that ID or produce an error message in case invalid node ID is entered.
The database contains a single table which for the above UI sketch looks like following:
Then, I have two CGI scripts, first - very small for creating the initial DB table:
#!/usr/bin/perl
use warnings;
use strict;
use DBI;
use DBD::mysql;
use CGI qw( :standard );
my $dbh = DBI->connect( "DBI:mysql:btree", "root", "", { RaiseError =>
+ 1 } );
my $string = "CREATE TABLE Nodes (
NID INT NOT NULL AUTO_INCREMENT,
PNID INT NOT NULL,
DEPTH INT NOT NULL,
PRIMARY KEY(NID) )";
$dbh->do( $string );
$dbh->do( q{ INSERT INTO Nodes ( PNID, DEPTH ) VALUES ( 0, 0 ) } );
$dbh->disconnect();
print header(), start_html( "Database Creation" );
print h4( "The btree Database Tables Has Been Created" );
Here, database btree, table Nodes.
#!/usr/bin/perl
use warnings;
use strict;
use DBI;
use DBD::mysql;
use CGI qw( :standard );
my $dbh = DBI->connect( "DBI:mysql:btree", "root", "", { RaiseError =>
+ 1 } );
my ( $sth, $results, $err );
if ( param("Add") ) {
my $pnid = param("PNID");
$sth = $dbh->prepare( q{ SELECT DEPTH FROM Nodes WHERE NID = ?
+ } );
$sth->execute($pnid);
$results = $sth->fetchrow_arrayref();
warn( $DBI::errstr ) if ( $DBI::err );
if ( $sth->rows == 0 ) {
$err = 1;
} else {
my $query = "INSERT INTO Nodes ( PNID, DEPTH ) VALUES ( "
+. $pnid . ", " . ( $results->[0] + 1 ) . " )";
$dbh->do( $query );
}
$sth->finish();
}
#$sth = $dbh->prepare( q{ SELECT * FROM Nodes ORDER BY DEPTH ASC, PNID
+ ASC, NID ASC } );
$sth = $dbh->prepare( q{ SELECT DISTINCT( PNID ), DEPTH FROM Nodes ORD
+ER BY DEPTH ASC } );
$sth->execute();
$results = $sth->fetchall_arrayref();
warn( $DBI::errstr ) if ( $DBI::err );
$sth->finish();
print header(), start_html( "Tree Manager" );
print h3( { -align => "center" }, "Tree Manager" ),
hr(),
table( { -border => 0, -width => "100%" }, Tr( th( { -align =>
+ "left", -colspan => "2" }, "Current Tree:" ) ),
Tr
+( th( { -width => "5%" }, "Depth" ), th( "Tree Nodes" ) ) );
my ( $pnid, $content, $td, $depth, $tman );
for ( @$results ) {
if ( $depth != $_->[1] ) { # here we change the tree level
$tman .= Tr( td( { -align => "center", -width => "5%" }, $
+depth ), $td );
$td = "";
}
$depth = $_->[1];
$sth = $dbh->prepare( q{ SELECT * FROM Nodes WHERE PNID = ? }
+);
$sth->execute($_->[0]);
$pnid = $sth->fetchall_arrayref();
warn( $DBI::errstr ) if ( $DBI::err );
for ( @$pnid ) {
$content .= " [P: $_->[1] ID: $_->[0]] ";
}
$td .= td( { -align => "center" }, $content );
$content = "";
}
$dbh->disconnect();
$sth->finish();
$tman .= Tr( td( { -align => "center", -width => "5%" }, $depth ), $td
+ );
print
table( { -border => 1, -width => "100%" }, $tman );
print h5("Check you enetered correctly the parrent node!") if $err;
print
start_form(),
table( { -border => 0, -width => "100%" }, Tr( th( { -align => "le
+ft", -width => "10%" }, "Add Node To:" ),
td( textfield( -name => "PNID", -size => 10 ) ) ),
Tr( td( submit( "Add" ) ), td() ) ),
end_form();
print end_html();
Please note, how can one improve the SQL query for tree traversal ?
And, back to my request for comments... what will be the imrovements one can make in terms of scalabilty and code re-usage ?
