Barbeque: A brainstorm on better Data interfaces
Monday, July 6th, 2009So, with the release of PHP 5.3 some of the new features got me thinking about better ways to query and interact with data. I’m tentatively calling it bbQuery, or bbQ for short.
Data Driven Apps
Web applications are typically driven by data and interactions between it. A typical situation involves: Querying data from the database, formatting as HTML, outputting. A few years ago it was just that simple. Today you have JSON, RSS, and other various formats.
Going by the standard model, you have to create each page separately for all of the various formats, duplicating code. I can’t say I solved this problem entirely, but I’m getting close.
Grab the Steaks, It’s Time for a bbQ
The goal is to be functionally similar to SQL but include a formatting layer that intelligently determines the way to display the data. The following snippet show how the old model can be transitioned to bbQ.
$DB('Children') ->select('children_id', 'dob', 'fitness', 'status') ->format(function ($row) { echo "${row['dob']} - ${row['fitness']} - ${row['status']}"; });
A simple example using the gFonted database. The first line selects the table from the database. On the second we select the columns we want in the query. The query results are then returned in to a callback from the format function. Very quickly you could create predefined formatting functions.
$DB('Children') ->select('*') ->left_join('Template', 'children_id') ->format(bbQuery::format_table); // predefined format functions
But thats far from automatic, intelligent maybe. Its impossible to guess what format anyone will need. There are a few generic formats (JSON and XML) that could be dealt with automatically.
$DB('Children') ->select('*') ->left_join('Template', 'children_id') ->limit(5) ->order_by('fitness', bbQuery::asc) // bbQuery::asc == "asc" ->set_type("application/json") ->format(); $DB('Children') ->select('*') ->left_join('Template', 'children_id') ->limit(5) // is a variable that gets overwritten ->order_by('fitness', bbQuery::desc) // order by's are string ->order_by('dob', bbQuery::asc) // and gets appended to ->set_type("application/xml") ->format();
Going the Other Direction
Sometimes its useful to put data into a database. I guess thats an understatement. Its also an understatement to say that doing so is a simple task. An example of standard data insertion:
$DB('Correct') ->insert_into('children_id', 'correct') // returns an insert object ->values(12, false);
However this could be greatly simplified. MySQL already knows the datatype for the column, so validation could be automated. The data could even be automatically inserted from a form.
<form> <input name="children_id" type="text" value="12" /> <input name="correct" type="checkbox" /> </form> <? $DB['Children'] ->insert_into('children_id', 'correct') // returns an insert object ->from_form(); ?>
And finally some other useful snippets:
$DB->query("select * from Children") ->format(bbQuery::format_table); $DB('test') ->create_table() // returns a create_table object ->column('test_id', bbQuery::id_column()) // id is special if set as a primary key, unsigned integer autoincrement ->column('test_foreign_id', bbQuery::id_column()) // just a unsigned integer ->column('test_char', bbQuery::char_column(10)) // field length as parameter ->column('test_int', bbQuery::integer_column(bbQuery::big), 0) // default as third parameter to column ->column('test_enum', bbQuery::enum_column('val', 'val3', 'val2')) // enum is varidaic ->primary_key('test_id') ->foreign_key('test_foreign_id', 'Correct'); $DB('test') ->alter_table() // returns a alter_table object ->add('test_timestamp', bbQuery::timestamp_column, bbQuery::now) ->drop('test_int'); $DB->query("truncate table test"); // format is not appropriate here
