Mongo Light

2023-12-10 PHP

There is a great CRM System EspoCrm.
In Espo, you can administrate the object types, their properties and their relations in the user interface.
This schema is then automatically created in a MySQL database.
This is a create feature. But for little projects, I prefer a database-less solution or at least a SQLite approach.

Cockpit provides this. You can use the MongoDB interface or SQLite.
So, I tested it and took a look at the created SQLite file.
Each object type has its own table. the table only contains an id column and a data columnšŸ¤”
In the data column is a JSON object serialized.

Cockpit uses the sqliteCreateFunction feature to registrate a php function in the pdo-adapter. This function is called in the database queries, deserializes the data column value and executes some other operations on the value.

This technique is called 'mongo-light' and should be performant in cases of up to 100k data rows.

Here is an example registration of a function:

 $pdo->sqliteCreateFunction('document_criteria',   
    function ($value) {
        return $value == 'test';
    }
    , 1);

Then you can use this function in your queries:

SELECT * FROM documents where document_criteria(title)

Here a link to the implementation used in the Cockpit-project.

❰ back