Contents Previous Next

11.5 Sample application: Drawing DB schema

As a final example we shortly discuss how the canvas type of graph was used to generate the DB schema for the DDDA architecture.

The library php file "utils/misc/imgdbschema.php" included in the distribution contains some utility classes to make the drawing of table schemes easier. It contains two basic classes, Class ImgDBTable and Class ImgDBSchema. The first class understand how to draw an image illustrating a single table. The second class is responsible for automatically extract all the relevant information from a DB to draw a complete DB Schema.

Before going into this a little bit more we show what an example of this might look like.



Figure 190: Example of using the canvas graph style together with the imgdbschema.php library to semi-automatically generate a DB schema [src]  

Before going on it should be noted that the ImgDBSchema assumes that the DB can be accessed through a DB abstraction layer modeled after the abstraction layer available in the 'jpdb.php' file in the DDDA architecture. This abstraction layer assumes a MySQL database in the bottom. This specific dependency of this particular abstraction layer is the reason why these classes is not included in the generic canvas tools file.

The second thing to note is that this library does not contain a complete automatic-layout engine but rather a very simple automatic system which, if nothing else is specified, just puts the table in a rectangular grid. A complete graph layout engine would simple be to much to write in this context. This is also a very difficult optimization problem and sofar not even any of the available research programs that tries this can achieve a satisfactory layout without manual intervention.

The critical lines in the code to generate the above graph is

 $tblposadj=array($tlo ,0,$tblwidth+ $tlo+2,0 ,2*$tblwidth+ $tlo+4,0 ,-1,16,- 1,16);
$dbschema  = new ImgDBSchema ("jpgraph_doc" ,"FormatTblName" ,"FormatFldName" );
$dbschema ->SetMargin($leftm, $topm);
$dbschema ->SetTableWidth ($tblwidth);
$dbschema ->Stroke($this-> img,$this->iscale ,$tblposadj);

The rest of the code in the file is just to setup the canvas, add an indented rectangle to group some tables and generate a footer with the date and time this image was generated.

The first line instantiates a new ImgDBSCheme layout engine asking it to draw an image for the database 'jpgraph_doc'. The following two arguments specify two callback functions for formatting the text for header and each field in a table.

The next line specify the top left margin where the drawing of the tables should be started.

The third line specify the width of a single table. The final lines starts the engine and draws all tables in the database to the canvas. The final argument requires some further explanation. This is an offset (x,y) from the top left corner how each individual table should be positioned. If the value is -1 indicates that the default value should be used. If this array is not specified then the tables will simple arranged line by line.

The full source code for drawing this DB schema example is shown below.

(File: dbschemaex1.php)
<?php
/*=======================================================================
// File:     DBSCHEMAEX1.PHP
// Description:    Draw a DB schema of the DDDA architecture
// Created:     2002-08-25
// Ver:        $Id: dbschemaex1.php,v 1.1 2002/08/27 20:08:57 aditus Exp $
//
// License:     This code is released under QPL
//              Copyright (C) 2001,2002 Johan Persson
// Note:        The actual drawing of the tables are semi-automatically
//              but you can easily adjust the individual tables position
//              with the 'tblposadj' array. 
//
//========================================================================
*/
include  "../jpgraph.php";
include 
"../jpgraph_canvas.php";
include 
"../jpgraph_canvtools.php";
include 
"../imgdbschema.inc";
include 
"../jpdb.php" ;


// Global callback to format the table header names
function  FormatTblName( $aName) {
    
// We want to replace any specifi references to the
    // 'JpGraph' project with the generic '<project>'
    
return  str_replace( 'JpGraph', '<project>' $aName);
}

// Global callback to format each field name in the table
function  FormatFldName( $aName,$aTable) {
    return 
$aName ;
}


class 
Driver  {

    var 
$ig$img$iscale $ishape;
    var 
$iymax ,$ixmax;
    var 
$iwidth ,$iheight;

    function 
Driver () {

    
// Define Image size and coordinate grid space to work within
    
$this ->iwidth 600;
    
$this ->iheight750;
    
$this ->iymax  50;
    
$this ->ixmax  55;

    
// Setup a basic canvas
    
$this ->ig = new CanvasGraph( $this->iwidth,$this ->iheight,'auto');
    
$this ->img $this-> ig->img;

    
// Define the scale to be used
    
$this ->iscale  = new CanvasScale ($this->ig);
    
$this ->iscale->Set( 0,$this->ixmax ,0,$this-> iymax);
    
$this ->ishape  = new Shape($this-> ig,$this->iscale );

    
// A small frame around the canvas
    
$this ->ig->SetMargin( 2,3,2 ,3);
    
$this ->ig->SetMarginColor( "teal");
    
$this ->ig->InitFrame();

    }

    function 
Run () {

    
$leftm =1.5;    // Left margin (for table schemes) 
    
$topm=5;     // Top margin (for table schemes) 
    
$tblwidth =15;    // Individual table width
    
$tlo=1;         // Offset for top line

    // Add the background color for the project specific tables
    
$this ->ishape->IndentedRectangle($leftm, $topm-1,3 *$tblwidth+$tlo+ 6,45,
                     
$tlo +2*$tblwidth+ 2,30,CORNER_BOTTOMLEFT,
                     
'lightblue');

    
// Stroke the tables (series of x,y offsets, If =-1 then use the
    // automtic positioning
    
$tblposadj =array($tlo,0, $tblwidth+$tlo+2 ,0,2* $tblwidth+$tlo+4 ,
             
0 ,-1,16,- 1,16);
    
$dbschema  = new ImgDBSchema ('jpgraph_doc' ,'FormatTblName' ,'FormatFldName' );
    
$dbschema ->SetMargin($leftm, $topm);
    
$dbschema ->SetTableWidth ($tblwidth);
    
$dbschema ->Stroke($this-> img,$this->iscale ,$tblposadj);

    
$tt  = new CanvasRectangleText ();
    
$tt->SetFillColor( '');
    
$tt->SetColor( '');
    
$tt->SetFontColor( 'navy');

    
// Add explanation
    
$tt->SetFont( FF_ARIAL, FS_NORMAL,12);
    
$tt->Set( 'Project specific tables', $tblwidth+ $leftm+3,16 ,15);
    
$tt->Stroke( $this->img,$this ->iscale);

    
// Add title
    
$tt->SetColor( '');
    
$tt->SetFont( FF_VERDANA, FS_BOLD,26);
    
$tt->Set( 'DDDA - DB Schema', 9,0.5,30 );
    
$tt->Stroke( $this->img,$this ->iscale);

    
// Add a version and date
    
$tt->SetFillColor( 'yellow');
    
$tt->SetFont( FF_FONT1, FS_NORMAL,10);
    
$tt->Set( "Generated: ". date("ymd H:i",time ()),1,$this-> iymax*0.96,15 ); 
    
$tt->Stroke( $this->img,$this ->iscale);

    
$this ->ig->Stroke();
    }
}

$driver  = new Driver ();
$driver->Run();

?>


Contents Previous Next