Code Tip #1: PEAR SQL Parser

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 indicesi.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 Coming Along

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!

Happy New Year

OK.. so I haven’t been keeping up with my blog posts.  Here’s one for the new year 2006.  I spent boxing day through January 1st in Calgary with my girlfriend’s family.  It was nice and relaxing.  Didn’t really even notice the cold.  I did, have to endure the airplane from hell, however.  (A short ‘aside’ – I this was my very first plane travel experience). — on the last 20 minutes or so of the flight home was like riding a roller coaster blind. 

By God’s grace I survived and did not throw up (although my breakfast was beginning to surface in the back of my throat.  At one point I even yelled out "Holy f**k!" when the plane hit some major turbulence.   From now on, call me BA Baracas and tranquilize me before stepping on the plane.

Back at work now for another semester of web development and mathematics studies.  I will be taking Math 443 (Combinatorial Theory) and Math 439 (Algebraic Systems) this semester.  Looks interesting …

ClassHoles : Top quality comedy

I went to a fundraiser last night for a local comedy troup called Classholes.  A good friend of mine, Mike Berdan, is part of this troup so I may be a little biased in my opinions of the troup.  All I can say is that if you are looking for top quality live entertainment in Vancouver, it doesn’t get any better than Classholes.  They do a mixture of live comedy sketches and recorded sketches similar to the Saturday Night Live format.  In fact, the comedy is generally of higher calibre than SNL.  In fairness to SNL, they have to produce something once per week, and Classholes takes months to perfect a show.  Anyways, their next show is in February 2006.  Check it out.  For more info check out http://www.classholes.com

Aeon Flux

Just went to see Aeon Flux tonight.  Thanks to Beverly’s Auntie Nona for the tickets.  Aeon Flux, starring Charlize Theron was an exciting (but somewhat typical) science fiction film set 400 years in the future after man-kind has survived a plague that killed 99% of its population.  I don’t want to give away any spoilers here, but I will say that it was a fun movie to watch, even if the acting suffered from the stereotypical scifi manufactured seriousness from time to time.

I give it 3 stars (out of 5) 

PHP User Management

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.

He-Man DVD Released

I purchased the newly released He-man DVD last week.  It has 33 episodes from season 1 (1983).  A total of 65 eps were made for each season so there will be more box sets to come.  In addition to the episodes, there is a documentary on the making of He-man that is quite well made.  All I can say is that this box set will keep the nostalgic fires burning for quite some time.

Ramblings about Xataface, Java, and other software development issues