[Phpgroupware-developers] Schema Availability to Apps

[Michael Dean]

I remember raising this issue when I first proposed the schema_proc
classes to phpGW, and while working with DCL recently, I have come
across a need for loading table schemas dynamically so I can use the
metadata to generate SQL.  This is necessary for a class I have that
generates SQL based on several attributes, including column list,
filters, joins, etc.

The issue at hand is that MSSQL and Sybase do not have native datetime
support in DCL.  I am currently implementing this functionality (and it
can/will be pushed to phpGW classes).  The reason the native support did
not exist was because the client libraries returned the default format
(Day Mon DD YYYY HH:MM:SS AM/PM), which isn't something you want to
parse.  To work around this, I need to wrap all column list references
in an abstract function.

So, what I have decided to do (rather than hard code all my dates and
timestamps) is break up the schema defined in phpgw_baseline into
multiple files under dcl/schema.  I have then created a method to load
this based on table name (LoadSchema).  The dcl/schema directory has a
bunch of schema.*.php files that individually set the table's metadata
in the phpgw_baseline array.  This means the tables_current.php would
just include all of the schema.*.php files instead of being one huge
array definition.

Anyway, I thought I'd share this function and if anyone finds it to be
useful in phpGW (I know I would), we can add this to the API.

Mike

********** Code Samples ************

/////////////// LoadSchema definition - global in scope
//
function LoadSchema($sTableName)
{
        $sConst = sprintf('SCHEMA_%s_INCLUDED', $sTableName);
        if (!defined($sConst))
        {
                if (!is_array($GLOBALS['phpgw_baseline']))
                        $GLOBALS['phpgw_baseline'] = array();

                define($sConst, 1);
                include(sprintf($GLOBALS['dcl_root'] . 'schema/schema.%s.php',
$sTableName));
        }
}

/////////////// schema.dcl_preferences.php file from app/schema dir
//
<?php
$GLOBALS['phpgw_baseline']['dcl_preferences'] = array(
        'fd' => array(
                'personnel_id' => array('type' => 'int', 'precision' => 4, 
'nullable'
=> false),
                'preferences_data' => array('type' => 'text')
        ),
        'pk' => array('personnel_id'),
        'fk' => array(),
        'ix' => array(),
        'uc' => array()
);
?>