Dynamic SQL

I used to enjoy programming.

I stopped enjoying programming with the release of XHTML and PHP 5 (which included the PDO Object). XHTML made the code for web pages excessively terse. The ugly PDO statement in PHP somehow made me feel sick every time I used it.

I finally had to confront the beast and ask why the PDO object makes me feel like puking.

I went back and was looked at the code I was writing before the release of PDO and it dawned on me.

Through the years I had developed a rather dynamic approach to SQL.

The way my code worked is that I would pull the DDL for a group of Tables from the database. (DDL refers to the CREATE TABLE Statement). My program would generate HTML forms from the DDL.

When you press submit, the program would, again, pull the DDL from the database. Analyze the data according to the defined constraints on the table and dynamically produce the SQL. The program would call mysql_query and insert the data.

In other words, I could create a web form in the time that it takes to write a CREATE TABLE Statement.

I was actually preparing to submit the programs I used to GitHub.

PHP programmers: Imagine how much more productive you could be if the program you used automatically generated forms from your SQL Tables, and imagine that same program generated the SQL to validate and insert the code into your database.

The PDO program is built around a static approach to SQL.

The flow of a PDO program is as follows:

You open a connection. Define a SQL Statement. Bind the Data to the Statement then execute the query.

PDO assumes that that SQL is static. It expects the SQL statement to be fully formed before looking at the data.

My programs were all building SQL statements step by step. They would only execute the SQL if the data passed all of the constraints.

I was able to do more with the mysql_query() command than the PDOStatement Object because the former allowed for dynamic creation of SQL while the latter is built on a regressive static model for SQL.

To prove that the dynamic approach to SQL is better. Let's imagine a simple User_Preference table with 20 defined items.

The user pulls up the preferences page and update only one or two of the twenty items.

My program cannot know, in advance, what preferences the user will change until it looks at the data.

PDO fails because it expects the program to define the SQL before looking at the data.

The program must either loop through the data twice (The first time to create the SQL statement, and second time to bind the data to the statement) or it needs to make a copy of the data in a buffer to bind to the SQL statement.

I downloaded the source code for PHP. I was actually considering creating a branch of PDO that would allow for the creation of SQL statements. But, I don't feel comfortable with my skills in c at this time (and my web site is broken).

So, what I want to do is to get back to the point where I can generate forms from the DDL Statement for a single table.

Since this regressive PDO is  the biggest block to this goal. I decided to start with it. Here is the public test page for the code. Here is the code for the Test Page and Object.

The base of this program is code that I wrote a decade ago. The original program worked as follows. It defined the constraints on the data. It then looked at the data in $_POST and built a SQL query.

The flow for this version is: The program makes a copy of an array. It then looks at the definitions for the columns and finally constructs the SQL. It is very inefficient.


Yes, I wrote this code on 1/30/2015. I spent from 1/30 to 2/3 writing test scripts. I wanted the test page to work against live data. But I finally decided I didn't like having a page that just generates INSERT and UPDATE commands for no reason out in public.

The Test Page now just generates some sample SQL, but does not execute the code.

The next step is to create an object that generates a web form. That will be easier. Unfortunately, I have to spend the next several hours running chores.

Perhaps, if I complete this project, I can return to the point where creating web pages was fun.