DBTables - Intro

A 4GL programmatic interface to building web applications. DBTables allows a programmer-administrator to generate web skeletons from existing data table sets and their relationships, as defined through SQL DDL.

In other words, DBTables allows a programmer to synthetically map database tables to listings, forms and generated content.

And within our out-of-the-box framework, the same programmer can visually access the GUIs, generate some more, fine-tune the elements on the screen, elect to display or hide elements, and all this visual fine-tuning gets aggregated as a secondary layer of DDL, which can always be overridden by new programming input from programmers working in PHP.

The goal of this project is to find an equilibrium between visual programming, and textual knitting, allowing both worlds to coexist on the same, production, and live system.

The building blocks of DBTables

The objects used in assembling DBTables programmatically can also be reused individually at your leisure for different types of applications. As a whole, they constitute the framework, which the automated engine can develop upon, in a consistent manner. As a programmer, you don't need to harness all the gritty-details, you can use those components that suit you, but rest assured, if you're lazy, you'll eventually use them all as one big app. :D

CORE\DBTable

The main object to use. If you want to knit your own interfaces in one fell swoop, the place to start is with an index.php page which instances this object pointing to a database and tableName of your election. The ViewType parameter allows to pick the type of view to render (Add/Edit/List/Delete/Confirm-Delete) which allows for pretty much all the possible view renderings in a typical database interface. Typically on an index page we'll use the List viewType to generate a first listing of our target data, which the UI will drive on its own to allow the user to navigate within the dataset.

In its simplest form, by default, DBTable will read the DDL information stored in the database for the target object then guess and render the found data fields accordingly, to the best of its abilities.

Once an interface has been rendered on the user screen (usually the developer's the first times), every little visual detail can be tuned, changed, transformed to the app developer's will. Right-clicking on headers, labels and navigation panels will bring up your UI-editing options. Once customized by the app developer, the UI customization details are saved to the Framework Database, and thereafter the engine will automatically render the UI interfaces by combining the DDL -and- UI customizations.

CORE\DBTableRenderer

An overloading class to CORE\DBTable, which includes the UI engine itself. In mid-april 2020, we decided to re-split the database logic (with the relational compiling and decompiling) from the user interface logic. Originally the system was built in such a way, but in 2018-2019 the two objects were fused together for a major cleanup/revamp in the recursive aspects. Now that this recursive aspect has been satisfyingly fixed for good, we're returning to our original split logic.

This allows us to override the DBTable as DBTableModeler/Renderer/Compiler/etc... :)

Meanwhile, the following objects now tie to DBTableRenderer, instead of DBTable directly. ;) (important thing to note.)

CORE\DBTable\FilterParameter

A neat little trickster that solves many headaches. You build a FilterParameter object by combining fields, operators and their values, in an array format. This FilterParameter object can then be used to generate a convenient SQL WHERE clause (with <, >, != and even the "IN sub-select" operator), and serialized for easy saving and transport. The convenience offered by its base64 conversion, is that once saved to a database (such as in a Digest table), you can quickly search on the base64 string for equivalence. But most importantly, it has some neat methods for generating hidden input fields on the fly, and maintaining its trueness even through JSON+Ajax+SQL+URLencoding. ;) And you can deal with objects or views composing more than 1 "key" or "filter" field, as 1 variable throughout your scripts.

$filterParameters = new \CORE\DBTable\FilterParameter();

$filterParameters->newFilter("SYS_User.SYS_Group_ID", $session->get_Org_GroupID(), "eq");

A short example on how to instantiate a FilterParameter object for use with our User administrative listing interface, in this case we're limiting the User Listing to the current user's organisational group ID, effectively limiting his view to his organisation colleagues. If the user were an extranet user in this case, his view would be limited to his external organisation.

CORE\DBTable\OrderByParameter

A bit like the above FilterParameter, but this one is used specifically for ORDER BY clauses. It also integrates in the framework directly by providing visual HTML components to table header fields for order by compositions on the fly in List Components. (link to a howto/example here...)

CORE\DBTable\ButtonSet

Another convenience born out of laziness, this one allows to compile Buttons, which get submitted along with Field definitions, table settings, OrderByParameter and FilterParameter objects to the DBTable generator to genially compile your favorite views, in a consistent fashion across your web application(s). Furthermore, the ButtonSet object also supports anonymous PHP callbacks, which can be used to turn on/off your buttons as a list of records is generated, or augment your data rows with Bootstrap badges*.

Our buttons integrate with Bootstrap 4, and FontAwesome 3,4 and 5.

Typical use for buttons:

$Buttons = new \CORE\DBTable\ButtonSet();
$Buttons->setButton("DELETE", "Delete_User( ':uriparams:' )", array("en"=>"Delete User","fr"=>"Effacer cet usager","es"=>"Borrar este usuario"),"btn-warning");
$Buttons->setButton("EDIT","Edit_User( ':uriparams:' )", array("en"=>"Edit user","fr"=>"Editer","es"=>"Editar el usuario"),"btn-primary");

This example creates a pair of buttons for our user listing interface, the :uriparams: will get automatically replaced by the table's primary key and can be used in your own javascript functions to redirect the user to the appropriate interface.

Of course, buttons are a bit more useful with their companion objects; DTable\Callbacks, described a bit further down. And they're magical if left to their own renderings, the engine will normally generate all the necessary buttons and labels according to the configured Access Permissions for each type of User Level, and for each View Type.

CORE\DBTable\Lister

An advanced list manager that allows you to visually build, manage, inspect and re-use lists from many sources, in dropdown format throughout interactive forms, with inter-list dependencies. Lists can be defined through brute-force key-value mapping, SQL queries, database Wizardry, numerical ranges with adjustable stepping, and even raw, [multi-lingual, for those with the need], files. The mechanisms for defining Lists is available in the framework's GUI for the lazy people. And remains available forever in raw file format (for packaging and distribution purposes, of course).

The Lister object is transparently loaded by the rendering engine when rendering different user views, upon encountering fields that reference a list, we'll load & cache the specific list for rendering.

The object can be re-used as-is in your PHP coding.

The Lister object comes with its own documented chapter (@todo: link to it here)

CORE\DBTable\Field

This object is used by CORE\DBTable directly, since we manipulate the logical presentation of fields (even if we don't render them), at all "technical" levels.

The crux of the DBTable framework. This is the simmered results of more than 25 years of R&D'n on 4GL systems. It works in a 3-way fashion, allowing programmers to supply field definitions, designers to modify them through the GUI, and finally, in the rawest of all formats, directly from the database. All programming and visual tuning is finally an augmentation of the database "infrastructure", allowing you to supply multi-lingual views across the board, predefine key components such as files, cryptographic database storage, hashed data fields, images, dropdowns linked to lists, permissions on at the field level, and much much more.

To define field sets in your PHP pages to be rendered with DBTables, suffice to declare your own keyed array structure and feed it to the engine. (We could conceive a more programmatic approach to it, but arrays are nice, easy to copy, and everybody understands them.)

For example, given  the Administrative GUI's User Listing field structure as such:

$Field_Structure_Overrides = array(
    "default"=>array("Flag_Display"=>false,"Flag_Capture"=>false),
    "SYS_User_ID"=>array("Flag_Display"=>false),
    "SYS_Group_ID"=>array("Field_Names"=>array("en"=>"Parent Group","fr"=>"Groupe parent","es"=>"Grupo"),"DBUI_Type"=>"dropdown","DBUI_List_Name"=>"Groups - System-wide","Flag_Display"=>true),
    "SYS_Organization_Type"=>array("Field_Names"=>array("en"=>"Organisation relationship","fr"=>"Relation du groupe parent","es"=>"Relacion con el grupo"),"DBUI_Type"=>"dropdown","DBUI_List_Name"=>"User.OrganizationTypes","Flag_Display"=>true),
    "Access_Level"=>array("Field_Names"=>array("en"=>"Privilege Level","fr"=>"Privilège usager","es"=>"Privilegio del usuario"),"DBUI_Type"=>"dropdown","DBUI_List_Name"=>"User.Access_Level","Flag_Display"=>true),
    "User_Type"=>array("Field_Names"=>array("en"=>"User Type","fr"=>"Type Usager","es"=>"Tipo Usuario"),"DBUI_Type"=>"dropdown","DBUI_List_Name"=>"User.Type","Flag_Display"=>true),
    "Username"=>array("Field_Names"=>array("en"=>"Username","fr"=>"Usager","es"=>"Usuario"),"DBUI_Type"=>"text","Flag_Display"=>true),
    "Status"=>array("Field_Names"=>array("en"=>"Status","fr"=>"Status","es"=>"Estado"),"DBUI_Type"=>"dropdown","DBUI_List_Name"=>"UserStatus","Flag_Display"=>true),
    "Real_Name"=>array("Field_Names"=>array("en"=>"Real Name","fr"=>"Nom Complet","es"=>"Nombre Completo"),"DBUI_Type"=>"text","Flag_Display"=>true)
);

Below we find the general definition of our Field array structure, each field should be defined as "Fieldname"=>array(options), and the entire Field structure one array.

Option name	Option value type	Description	DDL correspondence
Field_Names	array of langcode=>string	define the displayed field names for each language you wish to support. Its important that the language codes correspond to the language codes you do support system-wide (en, fr, es, etc..)	actual DDL field names
DBUI_Type	string	defines the type of field to be rendered, consult the DBUI Field Types in a section below for more details.	field types*
DBUI_List_Name	string	defines a List Name to map to this field (usually in the form of a dropdown, radio or checkbox rendering)	set and enum are converted to DB-stored lists, the rest must be defined manually
Flag_Display	Boolean (true or false)	if the field should be displayed or not	The system automatically generates all fields as for display, unless they're auto_increment or series based on numeric values, in which case they're turned off from display and capture.
Flag_Capture	Boolean (true or false)	if the field should be captured or not	Mandatory fields in the database are automatically set to Flag_Capture=true, in general the interfaces will also generate all non-described fields, unless a default setting has been defined
Flag_Mandatory	Boolean (true or false)	if the field should be made mandatory in the UI or not.	Not null fields in the database are automatically converted to mandatory fields in the UI, the programmer can still override them to off, since our queries are complete and will ignore fields that are not captured.
Default_Value	corresponding value to the field	Allows the programmer to supply a default value when rendering the field. (If the underlying value would be null and the field set to mandatory/not null for example)	Default value from DDL

The above parameter list is by far complete, and a whole parameter range is specific to the field types you wish to render. We invite you to consult the DBTable\Field documentation for an in-depth view (@todo; link to that article)

Generating User Interfaces from DBTables

The basics of loading DBTable interfaces is pretty straightforward, just like any DB API, you get to instance your DBTable object honing in a particular database and table. You supply an optional array of fields and display/capture parameters, a filter to your SQL quering and render it all either in List or Detailed view. This technique can  be used to embed DBTable objects in any type of web page.

The Framework also comes with a superset of tools built on top of the DBTable engine, which allows us to generate Modules and Pagesets all at once. Also, without any built PHP script or interfaces, it is possible to browse the database through the DBTable default interfaces, these same interfaces are used to test out your rendering as you're building DBTable in the GUI interfaces as well. This is the power of DBTables, be it for lazy people that just want to plug in a nice interface to their database, or the detailist that prefers to build his own UI using bricks that interconnect properly, DBTable offers interfacing at all levels.

You can also edit the above object parameters in the database itself to your liking, using SQL and accessing the Framework's system tables directly.

The only caveat lies with cryptographic storage, if some of your fields are ciphered or rendered hashes of other fields, then you must access them through any of the available GUIs for their proper conversions. See our ongoing blog about the security within our framework.