Cliqon has implemented the PHP PDO mechanism plus the Redbean ORM for a significant number of years. As the PDO extension is a fundamental part of PHP and the Redbean ORM is regular maintained and updated, there appears no good reason to change this situation.

Client Server SQL

For many years, Cliqon has used a database pattern which can loosely be described as a document database or an enhanced entity-attribute-value (EAV) database model to store data. The idea behind it was inherited from Sharepoint and therefore it predates the modern NoSQL databases such as Mongo and Couch by some years. At the same time, we continue to believe that a traditional SQL client-server database such as MySQL or Postgres is the most reliable, inexpensive and extensible solution for storing data. Therefore the database pattern that we have used for this and the last version of Cliqon is a hybrid pattern which we believe takes the best attributes of the Document pattern but expresses them within the confines of a traditional SQL Server.


The database tables in Cliqon are defined on the basis of what type of information is stored in them and not the specific nature of the information. However we are not so bound to our own rules that when a logical and practical reason exists for having a table specific to a particular purpose, then we can and do create one. The production version of Cliqon is primarily a content management system. A system containing content that is common to all Cliqon systems (primarily involving the administration) and content that will be unique to your system or your type of system. Therefore we have just two primary tables – dbcollection and dbitem. The two tables are almost identical (dbitem supports archival version control which dbcollection does not need). Both have some columns holding structured content, that can be indexed, such as group, category, type, reference, order and parent. Both have one textual field which will hold absolutely any amount of information that you can conceive in key - value pairs in JSON (or TOML) format. This format is so flexible that one can store the content of image files and documents in their raw digitised form rather than storing them as files on the system with references to those files in the database.


As we implied, we are not absolutely wedded to document storage. We do provide separate tables for Users, Logs and Session Management. When Cliqon is used to support applications that are more transaction based, such as our auction or order processing and stock control, we do add a transactions table, known as dbtransaction.


However we can no longer describe a database table by what it contains, such as the news, blog or menu table. We have to coin another word and for these concepts, for that we use the concept of a Collection. Thus a Collection consists of a number of the structural or predefined columns together with a variable number of contextual or informational fields which are frequently multi-lingual.

PDO

The description on the PHP website of PDO states that the PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server. PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data.


The words that it is a consistent interface is regrettably not completely true. There are certain minor differences that can catch out the unwary developer. Add in the Redbean ORM improves the level of consistency but there are still a few minor glitches. Over the past few years we have encountered differences in the areas of searching, limiting or slicing a recordset and inconsistencies between concepts such as TEXT or LONGTEXT.


In principle , PHP PDO supports a wide variety of database engines plus the file based engine, Sqlite. We have done all of our development work using MySQL and we would recommend that for a production environment. Sqlite can be used for development. We have not had the opportunity to test other engines such as Postgres and Firebird.


The PHP code to create the default PDO / Redbean connection is found in /includes/database.php. This script reads the default configuration settings from /config/config.cfg, as created during the installation process.


We use the expression "default connection" because with PDO and Redbean, it is possible to setup alternative or additional database connections for the purposes of reading and writing to other databases concurrently.


There are only one or two occasions in the Cliqon scripts where a PDO connection is used natively (Redbean is not used), therefore we will not provide any examples of its use within Cliqon.

Redbean

Cliqon implements Redbean Version 5. The website for Redbean is at https://redbeanphp.com/. This site provides a decent guide plus links to the support forum and API reference. The production version of Redbean consists of one PHP file and this is located in /framework/core/ with a file name of Rb.php. Once instantiated (which we do in Startup), all of its methods are available as static calls.  


Cliqon only has need to utilise a limited number of methods and facilities that Redbean has to offer. As a Developer you will have access to all of the methods that Redbean offers and to the various methods within Cliqon that encapsulate a number of Redbean calls into one method. Access to all of the methods can be coded into your own Classes and also into the Templates directly.


The following table describes the most widely used Redbean and Cliqon code snippets.


$sql = "SELECT * FROM dbcollection WHERE c_type = ? ORDER BY c_reference ASC";

$rawset = R::getAll($sql, ['string']);

$recordset = D::extractAndMergeRecordset($rawset);


$rs = object_encode($recordset);

return ['flag' => 'Ok', 'data' => $rs];

This is the classic three lines of code required to obtain a recordset, called a collection, for a given type, in this case a set of strings. The "extract and merge" method creates one flat recordset of structured columns (field names commencing with c_) and the unstructured fields (field names commencing with d_) that are contained in c_document.


For good measure we will encode the data as a JSON object and return it in response to an AJAX call from the client web browser.

$sql = "SELECT * FROM dbitem WHERE c_type = ? AND c_reference = ?";

$rawrow = R::getRow($sql, ['news', 'news(2)']);

$rowset = D::extractAndMergeRow($rawrow);

This snippet returns a row

$sql = "SELECT c_status FROM dbitem WHERE id = ?";

$val = R::getCell($sql, ['47']);

And this snippet returns a value, in this case c_status = "active"

$indb = R::dispense('dbitem'):

$indb->c_reference = $rq['c_reference'];

.....

.....

$indb->c_document = json_encode($doc);

$indb->c_lastmodified = Q::lastMod();

$indb->c_whomodified = Q::whoMod();

$result = R::store($indb);

if(is_numeric($result)) {

 return ['flag' => 'Ok'];

} else {

 return ['flag' => 'NotOk', 'msg' => $result];

}

This is an example of the code to respond to the posting of a form, using AJAX, to insert a record into the database.


There is a another snippet prior to the example given (outside of the scope of this section) that separates Request fields starting with c_ from fields starting with d_. Fieldls like c_reference or c_lastmodified will be inserted individually. Fields starting with d_, will be appended to an array called $doc. You will note how the JSON encoded $doc is then inserted into the c_document field.

$updb = R::load('dbcollection', $rq['id']):

.........

.........

$updb->c_revision = Q::getNextNumber($args);

$updb->c_lastmodified = Q::lastMod();

$updb->c_whomodified = Q::whoMod();

$result = R::store($updb);

Redbean treats updating virtually the same as inserting. Indeed if the Record ID did not exist, it would perform an insert.


You will not how in both cases we have routines for managing versions of the records, who modified the record last and when that modification occurred. For the Table dbitem, all records are backed up into dbarchive before the row is ubdated.

$updb = R::findOne('dbcollection', 'WHERE c_type = ? AND c_reference = ?', ['string', $rq['str(42)']]):

The initial Redbean call is functionally equivalent to the equivalent call in the previous two examples. However the "findOne" call will be used when the Record ID is unavailable. The fact that one can do this, highlights another important aspect about Redbean. Traditionally, we would expect that the "type" of the "thing" that is produced by the call to be an array, whereas it is an Object (with array like characteristics when reading). Redbean calls these "things", "a bean or beans".

$table = "dbcollection"; $fld = "c_reference";

$sql = "UPDATE $table SET $fld = ? WHERE id = ?";

$result = R::exec($sql, ['str(14)'], ['357']);

This snippet demonstrates that Redbean can execute a SQL query and bind PHP string variables (because the SQL statement is bounded by double apostrophes), SQL statement variables and values for those variables.

R::debug(true) ........ R::debug(false)

Invokes echoing of a debug log for any Redbean calls within the debug switch


There is a popular latin expression "Reductio ab absurdum" which roughly means that you can reduce a thing to an even smaller point but having achieved it, it would have no useful purpose or meaning. We could take some of the lines of code in the above snippets and reduce them to just one line but with lots of arguments or parameters but we believe they would be more difficult to read and still have the same number of characters to compose and understand.


There might be one practical exception to this rule of not producing concatenated widgets, however, this is not the way that we currently code Cliqon. Please consider an example of a template in which you have created a list or table. Because we are the authors of Cliqon, we tend to prepare the data for a table or list in of the two following ways. We prepare it as an array variable which is parsed by the template engine when the template is rendered:


<ul>

@foreach($list as $l => $item)

<li>$item.text</li>

@endforeach

</ul>


Or we publish the data as a JSON string and send it to a Vue routine:


<ul>

<li v-for="(item, i) in list">{{item.text}}</li>

</ul>


The first example requires that the Developer writes PHP for a Method invoked by a Controller, the second requires the same code for the Controller and the publishing of the array of data as JSON and fed through the pagescript to a Javascript function.


Another and potentially less intrusive way of achieving the same result is as follows:


<ul>

@foreach(W::getList($arg1, $arg2, $arg3) as $l => $item)

<li>$item.text</li>

@endforeach

</ul>


In order to make this happen, we had to create a Method in Cms.Php, Website.Php or finally YourPlugin.Php which responds to W::getList(). However, even that is not exactly true, because the following is valid:


<?php

$sql = "SELECT * FROM dbcollection WHERE c_type = ? ORDER BY c_reference ASC";

$rawset = R::getAll($sql, ['string']);

$list = D::extractAndMergeRecordset($rawset);

?>

<ul>

@foreach($list as $l => $item)

<li>$item.d_text[$idiom]</li>

@endforeach

</ul>


We like to think that this is why we are permitted to call Cliqon versatile:

Created with the Personal Edition of HelpNDoc: Free CHM Help documentation generator