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.