I have posted a sample dataface application here. It is just part of an application I am building for the Imaginative Education Research Group at Simon Fraser University to manage lesson plans.

I have just about had it with web servers that give no shell access and operate in safe mode. I wanted to install Dataface on my webspace here at sjhannah.com. The full installation is about 2.6 megs as a compressed tar file (much much bigger uncompressed) so it is kind of unfeasible to upload the uncompressed files to the server. Given the tendency of ftp to hang before during and after each file is uploaded, it would likely take 5 to 6 hours to upload the conventional way using an FTP client.

What I really needed to be able to do was upload the compressed tar archive to the server, then extract its contents on the server. Sounds easy, right. Well not when your server doesn’t provide shell access and PHP is executed in safe mode. Okay, Okay, normally you could create a PHP script like the following:

< ?
system("tar -xvfz filename.tar.gz");

?>

Except for the fact that my server doesn’t even provide ‘tar’ in the path – so even this command won’t work on my server.

PEAR to the rescue!!!

PEAR is a repository for reusable PHP classses. When I need to get something done in PHP, PEAR is usually the first place where I look. Luckily I found a great class called Achive_Tar that provides a pure PHP solution for Tar file manipulation.

After downloading and installing the class in my web space I wrote a new script to take care of the extraction:

< ?
require_once 'Archive/Tar.php';
$archive = new Archive_Tar($_GET['filename']);

$res = $archive->extract();
if ( PEAR::isError($res) ){
	echo $res->toString();
}

?>

This script takes a get parameter ‘filename’ to specify the name of the file to be extracted. It then extracts that file into the same directory!! And voila!! we can run this script and extract our file.

This is just a sample of how to deal with related records using the dataface library.

< ?
    require_once '/path/to/dataface/init.php';
    init(__FILE__, 'http://mydomain.com/path/to/dataface');
    require_once 'Dataface/Application.php';
    $app =& Dataface_Application::getInstance();

    require_once 'Dataface/Table.php';
    $studentsTable =& Dataface_Table::loadTable('Students');

    $studentsTable->addRelationship(
	    'courses', 		// the name of the relationship to add
	    array(
		    'Courses.CourseID' => 'StudentCourses.CourseID',
		    'StudentCourses.StudentID' => '$StudentID'
		    )
    );

    require_once 'dataface-public-api.php';

    $student =& df_get_record('Students', array('StudentID'=>1000));

    foreach ( $s tudent->getRelatedRecords('courses') as $course ){
	    echo $course['Name']."\n";
    }
    ?>

 This was pretty easy, especially since most of this code would be tucked away in a configuration file leaving you with 2 nice lines to get the student’s courses:


$student =& df_get_record('Students', array('StudentID'=>1000));
$courses = $student->getRelatedRecords('courses');


In developing Dataface, I have had to use a number of existing libraries to make my life easier.  One such library is the SQL Parser library in PEAR.  This class will parse an SQL query into a data structure that can be manipulated quite easily.  The library also comes with the complementary SQL_Compiler class that can compile these data structures back into an SQL statement.  This can be handy if you want to be able to add filters, column lists, or sort orders to sql queries, or if you want to break a query down into its component parts.

 Simple Usage

require_once ‘SQL/Parser.php’;
$parser = new SQL_Parser();
$struct = $parser->parse("SELECT a,b,c FROM Foo");
print_r($struct);

The above code sample will output the following:

Array
(
    [command] => select
    [column_tables] => Array
        (
            [0] =>
            [1] =>
            [2] =>
        )

    [column_names] => Array
        (
            [0] => a
            [1] => b
            [2] => c
        )

    [column_aliases] => Array
        (
            [0] =>
            [1] =>
            [2] =>
        )

    [table_names] => Array
        (
            [0] => foo
        )

    [table_aliases] => Array
        (
            [0] =>
        )

    [table_join_clause] => Array
        (
            [0] =>
        )

)

 

This is a fairly simple representation and it should be straight forward what all this means, but I’ll go over it here.  The statement SELECT a, b, c FROM Foo is parsed into the above array with the following keys:

• command – The SQL command (eg: SELECT, UPDATE, INSERT, etc..)
• column_names – The names of the columns that are included in the query.
• column_aliases – If you have a column name of the form foo as bar, indicating that you are aliasing that column, this will be an array of the aliases for each column. 
• column_tables – If you have a column name of the form Foo.bar (indicating column bar from table Foo) then this is an array of the table names.
• table_names – The names of the tables in the FROM clause.
• table_aliases – The aliases of the tables in the FROM clause.  This array has corresponding indices to the table_names array.
• table_join_clause – A structure to represent the join clause of the query.
• where_clause – A structure to represent the where clause of the query.
• sort_order – A structure to represent the ORDER BY clause of the query.

How about a slightly more complex query as an example to see how these structures work.

require_once ‘SQL/Parser.php’;
$parser = new SQL_Parser(null, ‘MySQL’);
$struct = $parser->parse("SELECT F.a as column1, B.b as column2 FROM Foo F inner join Bar B on F.c=B.c where column1 = ‘val1’ and column2 = ‘val2’");
print_r($struct);

This would output:

Array
(
    [command] => select
    [column_tables] => Array
        (
            [0] => F
            [1] => B
        )

    [column_names] => Array
        (
            [0] => a
            [1] => b
        )

    [column_aliases] => Array
        (
            [0] => column1
            [1] => column2
        )

    [table_names] => Array
        (
            [0] => Foo
            [1] => Bar
        )

    [table_aliases] => Array
        (
            [0] => F
            [1] => B
        )

    [table_join_clause] => Array
        (
            [0] =>
            [1] => Array
                (
                    [arg_1] => Array
                        (
                            [value] => F.c
                            [type] => ident
                        )

                    [op] => =
                    [arg_2] => Array
                        (
                            [value] => B.c
                            [type] => ident
                        )

                )

        )

    [table_join] => Array
        (
            [0] => inner join
        )

    [where_clause] => Array
        (
            [arg_1] => Array
                (
                    [arg_1] => Array
                        (
                            [value] => column1
                            [type] => ident
                        )

                    [op] => =
                    [arg_2] => Array
                        (
                            [value] => val1
                            [type] => text_val
                        )

                )

            [op] => and
            [arg_2] => Array
                (
                    [arg_1] => Array
                        (
                            [value] => column2
                            [type] => ident
                        )

                    [op] => =
                    [arg_2] => Array
                        (
                            [value] => val2
                            [type] => text_val
                        )

                )

        )

)

Comments

As you can see the column_names, column_aliases, and column_tables arrays all have corresponding indices.  i.e. $struct[‘column_aliases’][$i] contains the alias (if it is specified) for the column whose name is stored in $struct[‘column_names’][$i] and the table for the column can be found in $struct[‘column_tables’][$i].

There is a similar correspondence between the table_names and table_aliases arrays.

Dataface is coming along slowing but surely.  It is still only publicly available via sourceforge CVS but it is growing in features.  I played around with soap a little over the holidays and have begun to build a Web service interface.  Next on the slate is Dataface Desktop Edition !!  Watch out filemaker, open source just got a whole lot better!

I have started up a Sourceforge project for Dataface.  There are no releases yet, but the CVS is up-to-date.  Check it out at http://www.sourceforge.net/projects/dataface/.

Tonight I am working on adding a flexible permissions system to my PHP application ‘Dataface’.  I have a predisposition towards using PEAR classes where I can so naturally I decided to try LiveUser.  Unfortunately the documentation is scarce, and all but to-the-point.  I was, however impressed with this short article (http://jystewart.net/process/archives/2005/07/configuring-liveuser) that cuts to the chase on how to set up Live User.  I recommend this one to anyone looking for User management in a PHP application.