(note (code cslai))

My evil evil form and database library

Writing a usable form and database library has always been a painful experience. So why bother re-inventing the wheel when there are so many to choose from already? I am writing one mostly for learning purpose. After numerous attempts, I finally get my form and database library in shape. It is nowhere complete, but nor it is perfect, but it is currently the implementation that is closest to my original design. I will keep working on it so it can be used in my personal projects in the future.

While the whole library is still very much a proof-of-concept and work in progress, so there’s been no much document written about it. Eventually I would probably start writing tests for part of the library (I still have not figure out how to test PDO codes). For now I am gonna document how the library can be used.

Database Library

I always wanted a lightweight database library that I can still able to write SQL. However most of the libraries out there are trying to “save” me from writing them. The one that is closest to what I have in mind would be Drupal’s way to deal with database. However I am trying to implement something less ambitious here. It does not do much magic, and is only capable of running specific queries only.

In order to use this library, one would want to define a schema for the table (representing in JSON format, as long as it is fed to the library as an array the file format doesn’t matter).


{
"relation": {
"type": "table",
"name": "post",
"schema": "app",
"primary_key": ["id"]
},
"column": {
"id": {
"data_type": "serial"
},
"title": {
"data_type": "character varying",
"not_null": true
},
"content": {
"data_type": "text",
"not_null": true
}
},
"query": {
"save": {
"statement": "INSERT INTO \"%s\".\"%s\" (\"%s\", \"%s\") VALUES (%s, %s)",
"param": [
"relation:schema",
"relation:name",
"column:title:name",
"column:content:name",
"column:title:handler",
"column:content:handler"
]
},
"retrieve": {
"statement": "SELECT %s, %s FROM \"%s\".\"%s\" WHERE %s = %s",
"param": [
"column:title:name",
"column:content:name",
"relation:schema",
"relation:name",
"column:id:name",
"column:id:handler"
]
}
"widget": {
"statement": "SELECT %s as value, %s as name FROM %s.%s ORDER BY %s",
"param": [
"column:id:name",
"column:title:name",
"relation:schema",
"relation:name",
"column:title:name"
]
},
"check": {
"statement": "SELECT EXISTS(SELECT %s as value, %s as name FROM %s.%s WHERE %s = %s)",
"param": [
"column:id:name",
"column:title:name",
"relation:schema",
"relation:name",
"column:id:name",
"column:id:handler"
]
}
}
}

While the library is only capable of working on one table at a time, I am still working on other use cases. Unfortunately the database library can’t do much with the schema right now though, but I would like to make it generate CREATE TABLE statements by reading these schema files.

The important part of the schema currently is actually the query part. This is where the actual statements are defined. While it is mentioned earlier that there’s no way to work with multiple tables, it is not entirely true. If needed, horrible SELECT statements can be defined here. It is intentional to make the statement definition sprintf’ish, mainly because I want to be able to produce not only prepared statement, but also proper select statement in plain text form for logging purpose in future.

To use the library, just create a pdo object, and feed it to the generator.


$pdo = new PDO(....);
$generator = \coolsilon\database\relation\generator($pdo, json_reader());

where json_reader() returns a closure where we can throw in a filename to get the above schema in assoc array form (I have a file library to help in reading the files, but will cover it in future posts). Therefore, to make the generator to generate a new relation “object”, just call it using call_user_func, as follows:


$relation = call_user_func($generator, $file_name_of_the_schema);

As shown in the code above, json_reader() is expected to return a closure to be able to deduce the location of a given file name. To use this relation “object” to execute statements, do this:


$statement = call_user_func($relation, $key_of_statement); // PDOStatement

So now the relation “object” returns a proper PDOStatement object. Then one can do whatever he/she wants with it. However, the database library has a function to execute query if you want to:


\coolsilon\database\statement\execute($statement, $data); // returns the same PDOStatement object

Pretty much just it for now. Please flip to the next page for brief introduction to the form library.

Just a quick note, regarding the json_reader() closure, the function can take any argument even when it is used with the form library. As long as the returned function can turn whatever you pass in (directly in code, or through form schema) into an an array

Next we shall talk about the evil form library.

Form Library

I want a form library that is simple, actually most part of my form library is similar to what other scripts offer. However, mine has some integration with the above mentioned database library. For example, consider the following form template:


{
"form": {
"method": "post",
"name": "post"
},
"field": {
"title": {
"required": true,
"widget": {
"type": "text"
},
"validation": {
}
},
"reference": {
"required": true,
"widget": {
"type": "lookup",
"schema": "post.json",
"query": "widget",
"field": {
"value": "value",
"display": "name"
},
"validation": "check"
}
}
}
}

Nothing much to expect, the schema should be self-explainable enough. The only part that may be odd would be the lookup field named reference. It is basically a select widget, that grabs the option from a schema stored in post.json (json_reader() function should return a function that can turn this filename to proper assoc array). The “query” key is the actual statement in the schema expected to return the result with “field” option describes the value and display columns.

Unlike the database library, the form library need no explicit creation, just read the above schema file and return a respective associative array. Therefore it can be as simple as this


$form = json_decode($file_of_form_schema, TRUE);

However, if CSRF protection is needed, just pass the form array to the init function, as follows


$form = \coolsilon\form\init(json_decode($file_of_form_schema, TRUE));

What init() does is that a new hidden token field is added to the form. Next we would want to send the form for validation, as follows:


$generator = \coolsilon\database\relation\generator($pdo, json_reader());
$validation_result = \coolsilon\form\validate($form, $generator, \coolsilon\form\input\read($_POST));

The generator is needed just in case certain form needs access to the database for validation purpose. Then read() function removes the ‘field-‘ prefix to the array keys so that the form library can recognize them properly. If you data comes without the ‘field-‘ prefix you can skip this step.


$data = array('title' => 'foo', 'reference' => 1);
$validation_result = \coolsilon\form\validate($form, $generator, $data);

The function validate() itself would return TRUE if the input data passes all validation. However, if it fails then an array containing the error messages will be returned. The current form of a function that does validation is as follows. While the library is still under construction, I am not gonna talk much about the validation function itself for now.

Getting the actual markup of the form is easy, just pass in the parameters as follows:


// a blank form
echo \coolsilon\form\markup($form, $generator);
// a form with some default data
echo \coolsilon\form\markup($form, $generator, array('title' => 'foo'));
// a form with previous submitted data that is invalid
echo \coolsilon\form\markup($form, $generator, \coolsilon\form\input\read($_POST), $validation_result);

Why procedural?

I know PHP has great OOP support starting in version 5+. However, I start to appreciate functional programming more these days. Especially after PHP 5.3+ I can start abusing anonymous function and closure, it really change my way of writing codes and even further irritate my peers more. However, there are still rooms for improvement as I really doubt if I would remember what the code does if left for a couple of days.

Another problem with the current design is the namespacing problem. I am not sure if I like this feature introduced in recent PHP releases. However, after using it I really doubt so. Look at those ridiculous \coolsilon\database\… function calls. I think sooner or later I will re-organize the code to depend less on it, at least not multiple layers of namespaces. Other than that I am quite happy with the current state.

The actual library can be found here, feel free to try playing with it. Just a reminder, a lot of things may be broken right now.

Exit mobile version