Firstly we need to install the database module.

user@server:~/www/yoursite_domain$ ./cron.php -m setrill -f install --deploy http://setrill.org/dms/downloads/database_0_1.zip 
Setrill Framework v0.107 shell call
INFO: copying to cache/deploy/downloads/database_0_1.zip
Creating bak files
Creating directory modules/database/...Ok
installing modules/database/database.class.php
installing modules/database/database.def.php
creating config dir (not found)
--- Creating a default empty config file
WARNING: mysql submodule not found. A submodule extending database is needed
INFO: Module database installed
Removing bak files

The database module is not functional, needs a section to manage the database. We install de mysql section, that works with Mysql and MariaDB databases.

user@server:~/www/yoursite_domain$ ./cron.php -m setrill -f install --deploy http://setrill.local/dms/downloads/mysql_db_0_12.zip
Setrill Framework v0.107 shell call
INFO: copying to cache/deploy/downloads/mysql_db_0_12.zip
Creating bak files
Creating directory modules/database/mysql/...Ok
Creating directory modules/database/mysql/addons/...Ok
installing modules/database/mysql/mysql.class.php
installing modules/database/mysql/mysql_db.def.php
installing modules/database/mysql/addons/mysql.addon.php
creating config dir (not found)
--- Creating a default empty config file
DataBase Name []: mysite_domain
DataBase Server []: localhost
Database User []: user
Database Password []: ******
Database Prefix []: 
installing mysql.addon.php
INFO: Module mysql_db installed

creating Database connector..
DB conecion stablished
creating database mysite_domain
select database `mysite_domain`
Removing bak files

The Database is created. The database info is stored on config file for the module. At this moment the password is stored in plain text.

The database object will always extend the CDBobject. The database and it's settings are defined on the module, as seen above

<?php
/** @file /application/modules/main/person.class.php **/

require_once ("classes/dbobject.class.php");

class CPerson extends CDBObject {
    var $person_id;
    var $person_name;

    var $_internal; // Not a DB field

    function __construct() { parent::__construct("persons", "person_id", array("person_id")); }

    function getDefinition() {
        $types = array("person_id" => "int(11)", "person_name" => "varchar(255)");
        $default = null;
        $collation = null; // not implemented yet
        $attributes = null;
        $index = null; // not implemented yet
        return array($types, $default, $collation, $attributes, $index);
    }
}

Usually, the database objects constructors do not have parameters. If needed, they need to consider overriding the CDBObject::createNew function

The constructor takes the table_name, the table_id (could be set to null) and an array of fields considered as numeric.

Each var of the object is considered as a table field. If not, the var must start with _

The getDefinition function must be overrided to specify the database fields.

<?php
/** @file /application/modules/main/cron/install_person.php **/

global $app;

require_once ($app->getModuleFile("person.class.php"));

$person = new CPerson();
$person->install();

The object must be installed using CDObject::install function (that will create the table on DB), usually placed on module install. see Module Maintenance - Installing

The output for this manual install is

user@server:~/www/yoursite_domain$ ./cron.php -f install_person
creating Database connector..
DB conecion stablished
select database `mysite_domain`
Setrill Framework v0.107 shell call
INFO: creating table for CPerson

<?php
/** @file /application/modules/main/cron/insert_person.php **/

global $app;

require_once ($app->getModuleFile("person.class.php"));

$person = new CPerson();

$person->set_value("person_name", "jaume");

if ($person->insert()->valid()) {
    $app->console_output(INFO, "data inserted", $person);
}

if ($person->has_error()) {
    $app->console_output(ERROR, "cannot insert", $person);
}

$person->print_data();

Although the value can be assigned directly, using CDObject::set_value function ensures that any actions related to it are triggered. This can include privileges, and other actions or checks over the data

The output for this code is

user@server:~/www/yoursite_domain$ ./cron.php -f insert_person
creating Database connector..
DB conecion stablished
select database `mysite_domain`
Setrill Framework v0.107 shell call
INFO: data inserted
==CPerson==
INTERNAL FLAGS: 100000 (100000000000000000000)
person_id: 1
person_name: jaume

If failed the output could be

ERROR (123): cannot insert
==CPerson==
INTERNAL FLAGS: 100000 (100000000000000000000)
ERROR CODE: 123
person_id: ""
person_name: jaume

Code 123 matches to OBJECT_DATABASE_ERROR, see database logging to get more info about what happened.

The flag shown in output (1 << 20), matches EMPTY_SEARCH_ALLOWED. see object flags to get more info about flags

The mysql sentence executed is

 INSERT INTO persons (`person_name`) VALUES ('Jaume');

To set data from a client, see ALTERNATIVE_ACTION_INSERT

<?php
/** @file /application/modules/main/cron/update_person.php **/

global $app;

require_once ($app->getModuleFile("person.class.php"));

$person = new CPerson();

if ($person->loadFromKey(1)->valid()) {
    $app->console_output(INFO, "person name is ".$person->get_value("person_name"));
    $person->set_value("person_name", "Jaume")->update();
}

if ($person->load_by("person_name", "Jaume")->valid()) {
    $app->console_output(INFO, "loaded #".$person->get_key_value());
}

using CDObject::get_value function ensures that any actions related to it are triggered. This can include privileges, and other actions or checks over the data.

The oputput for this code is

user@server:~/www/yoursite_domain$ ./cron.php -f update_person 
creating Database connector..
DB conecion stablished
select database `mysite_domain`
Setrill Framework v0.107 shell call
INFO: person name is jaume
INFO: loaded #1

The mysql sentences are

SELECT * FROM persons WHERE `person_id` = '1'    LIMIT 1; 
UPDATE persons SET  `person_name` = 'Jaume'  WHERE `person_id` = '1';
SELECT * FROM persons WHERE `person_name` = 'Jaume'    LIMIT 1;

To update data from a client, see ALTERNATIVE_ACTION_SAVE

We need more data on our example.

<?php
/** @file /application/modules/main/cron/populate_person.php **/

global $app;

require_once ($app->getModuleFile("person.class.php"));

$person = new CPerson();

$person->reset()->set_value("person_name", "Marc")->insert();
$person->reset()->set_value("person_name", "Carles")->insert();

<?php
/** @file /application/modules/main/cron/load_persons.php **/

global $app;

require_once ($app->getModuleFile("person.class.php"));

$persons = new CPerson();

$app->console_output(INFO, "Persons Rows: " . $persons->count());

foreach ($persons->loadAll() as $person) {
    $app->console_output(INFO, "person name is ". $person->get_value("person_name"));
}

foreach ($persons->loadAll("*", array ("a" => "person_name", "b" => "Marc", "op" => "LIKE")) as $person) {
    $app->console_output(INFO, $person->get_value("person_name"). " has id #". $person->get_key_value());

Customs query conditions are automatically build.

Output for this example is

INFO: Persons Rows: 3
INFO: person name is jaume
INFO: person name is Marc
INFO: person name is Carles
INFO: Marc has id #2

The mysql sentences are

SELECT COUNT(*) as c FROM persons WHERE 1;
SELECT * FROM persons WHERE 1;
SELECT * FROM persons WHERE `person_name` LIKE 'Marc';

To serve data to a client see ALTERNATIVE_XML_GET and ALTERNATIVE_XML_LIST

Queries are build using an array structure. In it's most basic form, it is formed by 'a', and 'b', where 'a' is the field and 'b' the value to match.

The operator can be added as 'op', and is inserted betwen 'a' and 'b'.

The operator can be

• normal ones like '=', '<', '>', '<>', 'LIKE'
• 'IS', or 'IS NOT', and then the 'b' should be set to "null" as string.
• 'IN', in that case the b is a comma separated values string.
• 'BETWEEN', in that case b is a string with two values separated by comma.
• 'REGEXP'

'a' and 'b' can also be and struct following the same structure, so using OR as 'op' we can build something like ((a1 = b1) OR (a2 = b2))

We can also insert elements side by side using 'next', so we can build something like ((a1 = b1) AND (a2 = b2) AND ... AND (an = bn))

Using 'u' we can can set union as OR, to get something ((a1 = b1) OR (a2 = b2) OR ... OR (an = bn)), the operator is always set before the current current element

We can specify the name for the field with 'object' and 'objectb' for the value so the query could be like (a_name.a = b_name.b)

We can specify functions over value using 'func', and also over field as 'a_func'

And finally we can use 'a_raw' instead of 'a', and 'b_raw' instead of 'b' to set the value as is, without any checks, to avoid injection for example.

<?php
/** @file /application/modules/main/cron/query_example.php **/

global $app;

require_once($app->getModuleFile("person.class.php"));

$persons = new CPerson();

$w1 = array("a" => array("next" => array(array("a" => "person_name", "b" => "Jaume"),
                                           array("a" => "person_name", "b" => "Marc", "u" => "OR"),
                                           array("a" => "person_name", "b" => "Carles", "u" => "OR"))),
            "b" => array("a" => "person_name", "b" => "5,6", "op" => "IN", "a_func" => "LENGTH"), "op" => "AND");

$w2 = array("a" => array("a" => "person_name", "b" => "J%", "op" => "LIKE"),
            "b" => array("a" => "person_name", "b" => "Marc", "object" => "persons"), "op" => "OR");

$w = array("a" => $w1, "b" => $w2, "op" => "AND");

foreach ($persons->loadAll("*", $w) as $person) { $app->console_output(INFO, "matches #".$person->get_key_value()); }

if ($persons->has_error()) {
    $app->console_output(ERROR, "Error!", $persons);
}

Resulting in this query

SELECT * FROM persons WHERE 
(
    ((`person_name` = 'Jaume') OR (`person_name` = 'Marc') OR (`person_name` = 'Carles'))  AND 
    (LENGTH(`person_name`) IN ('5','6'))
) AND ((`person_name` LIKE 'J%') OR (`persons`.`person_name` = 'Marc'))

There is a set of static functions to help building the structure, getting a more clean code.

<?php
/** @file /application/modules/main/cron/query_example_w_statics.php **/

global $app;

require_once($app->getModuleFile("person.class.php"));

$persons = new CPerson();

$w = array("a" => CDBObject::data_query_multiple(array(CDBObject::data_query_field("person_name", "Jaume"),
                                                        CDBObject::data_query_field("person_name", "Carles", "=", "OR"),
                                                        CDBObject::data_query_field("person_name", "Marc", "=", "OR")),
                                                  "OR"),
           "b" => array("a" => "person_name", "b" => "5,6", "op" => "IN", "a_func" => "LENGTH"), "op" => "AND");

foreach ($persons->loadAll("*", $w) as $person) { $app->console_output(INFO, "matches #".$person->get_key_value()); }

if ($persons->has_error()) {
    $app->console_output(ERROR, "Error!", $persons);
}

The most insteresting part of this is the posibility to build queries dinamically.

We will need another object, remember that it must be installed.

<?php
/** @file /application/modules/main/address.class.php **/

require_once ("classes/dbobject.class.php");

class CAddress extends CDBObject {
    var $address_id;
    var $address_name;
    
    function __construct() { parent::__construct("addresses", "address_id", array("address_id")); }

    function getDefinition() {
        $types = array("address_id" => "int(11)", "address_name" => "varchar(255)");
        $default = null;
        $collation = null; // not implemented yet
        $attributes = null;
        $index = null; // not implemented yet
        return array($types, $default, $collation, $attributes, $index);
    }
}

$app->store_object(new CAddress);

We will also modify our person class to add a link to an address

<?php
/** @file /application/modules/main/person.class.php **/

require_once ("classes/dbobject.class.php");

class CPerson extends CDBObject {
    var $person_id;
    var $person_name;
    var $address_id;

    var $_internal; // Not a DB field

    function __construct() { parent::__construct("persons", "person_id", array("person_id", "address_id")); }

    function getDefinition() {
        $types = array("person_id" => "int(11)", "person_name" => "varchar(255)", "address_id" => "int(11)");
        $default = null;
        $collation = null; // not implemented yet
        $attributes = null;
        $index = null; // not implemented yet
        return array($types, $default, $collation, $attributes, $index);
    }

    function set_triggers() { $this->add_trigger(new CRelatedObjectTrigger("CAddress", "address_id", "address")); }

    function upgrade($curr_version, $where = "WHERE 1") {
        switch ($curr_version) {
        case 0.1:
            $this->alter("ADD", "address_id int(11)");
        }
    }
}

We are modifying existing object. See upgrading for more info.

<?php
/** @file /application/modules/main/cron/upgrade_person.php **/

global $app;

$version = get_argv_value('version', 'v');

require_once ($app->getModuleFile("person.class.php"));

$person = new CPerson();
if ($version !== null) {
    $person->upgrade($version);
} else {
    $app->console_output(INFO, "missing version");
}

user@server:~/www/yoursite_domain$ ./cron.php -f upgrade_person --version 0
creating Database connector..
DB conecion stablished
select database `mysite_domain`
Setrill Framework v0.107 shell call
modifying table for CPerson

This is an example using the relationship

<?php

/** Relationships example **/

$person_a = new CPerson();
$person_b = new CPerson();
$persons = new CPerson();

$address = $person_a->load_by("person_name", "Carles")->insert_address();
if ($address->valid()) {
    $address->set_value("address_name", "Lleida")->update();
}


$address = $person_a->load_by("person_name", "Jaume")->insert_address();
if ($address->valid()) {
    $address->set_value("address_name", "Catalonia")->update();
    $person_b->load_by("person_name", "Marc")->assign_address($address);
}

foreach ($persons->get_from_address($address) as $person) {
    $app->console_output(INFO,
                         "person name in ".$address->get_value("address_name")." is ".$person->get_value("person_name"));
}

$person_a->print_data();
$person_b->print_data();
$address->print_data();

Most of magical's comes from using the CRelatedObjectTrigger. Note that we are using a string as object in it's constructor. This is possible because we have stored in below class definition. We could use an object itself but in this way we avoid problems when same object is constructed while contructing and also we have will not be creating until it's necessary, so it will not be created if not used.

The Output for this example is

INFO: person name in Catalonia is jaume
INFO: person name in Catalonia is Marc
==CPerson==
INTERNAL FLAGS: 100000 (100000000000000000000)
person_id: 1
person_name: jaume
address_id: 2
==CPerson==
INTERNAL FLAGS: 100000 (100000000000000000000)
person_id: 2
person_name: Marc
address_id: 2
==CAddress==
INTERNAL FLAGS: 100000 (100000000000000000000)
address_id: 2
address_name: Catalonia

Generated mysql are

INSERT INTO addresses () VALUES ();
UPDATE persons SET  `person_name` = 'Carles' , `address_id` = '1'  WHERE `person_id` = '1';
UPDATE addresses SET  `address_name` = 'Lleida'  WHERE `address_id` = '1';
INSERT INTO addresses () VALUES ();
UPDATE persons SET  `person_name` = 'Jaume' , `address_id` = '1'  WHERE `person_id` = '1';
UPDATE addresses SET  `address_name` = 'Catalonia'  WHERE `address_id` = '2';
SELECT * FROM persons WHERE `person_name` = 'Marc';
UPDATE persons SET  `person_name` = 'Marc' , `address_id` = '2'  WHERE `person_id` = '2';
SELECT * FROM persons WHERE `address_id` = '2';

To insert a relationship from a client, see ALTERNATIVE_ACTION_ADD

Quering Relationships

<?php

class CPersonAddress extends CExtendedObject {
    function __construct() {
        CExtendedObject::__construct(new CPerson, "person");
        $this->addObj(new CAddress, "address", array("address_id" => "address_id"));
    }
}

<?php

$personAddresses = new CPersonAddress();

foreach ($personAddresses->loadAll("*", CDBObject::data_query_field("address_name", "Catalonia")) as $personAddress) {
    if ($personAddress->person_name == "jaume") {
        $app->console_output(INFO, "Jaume is here!");
        $personAddress->print_data();
    }
    $app->console_output(INFO,
                         "Name: ".$personAddress->get_value("person_name")."; Address: ".$personAddress->address_name);
}

INFO: Jaume is here!
==CPersonAddress==
INTERNAL FLAGS: 500008 (10100000000000000001000)
==>==CPerson==
INTERNAL FLAGS: 100001 (100000000000000000001)
person_id: 1
person_name: jaume
address_id: 2
NAMED as person
==>==CAddress==
INTERNAL FLAGS: 102011 (100000010000000010001)
address_id: 2
address_name: Catalonia
NAMED as address
[END]
INFO: Name: jaume; Address: Catalonia
INFO: Name: Marc; Address: Catalonia

 SELECT person.person_id,person.person_name,person.address_id,address.address_name FROM persons as person 
INNER  JOIN  addresses as address ON address.address_id =  person.address_id 
WHERE `address_name` = 'Catalonia' GROUP BY person.person_id;

Extending object on demand

<?php

class CPersonAddress extends CExtendedObject {

    function __construct() { CExtendedObject::__construct(new CPerson, "person"); }

    function expand_object_on_query($k, $v, $op) {
        $ret = null;
        switch ($k) {
        case 'address_name':
            $this->addObj(new CAddress, "address", array("address_id" => "address_id"));
            break;
        default:
            $ret = parent::expand_object_on_query($k, $v, $op);
        }
        return $ret;
    }
}

<?php

$personAddresses = new CPersonAddress();

foreach ($app->getListObjects($personAddresses, array("person_name" => "Marc")) as $personAddress) {
    // Do something
}

foreach ($app->getListObjects($personAddresses, array("address_name" => "Lleida")) as $personAddress) {
    // Do something
}

SELECT person.person_id,person.person_name,person.address_id FROM persons as person 
WHERE (`person`.`person_name` LIKE '%Marc%') OR (`person`.`person_id` is NULL) GROUP BY person.person_id;

SELECT person.person_id,person.person_name,person.address_id,address.address_name FROM persons as person 
INNER  JOIN  addresses as address ON address.address_id =  person.address_id 
WHERE ((1 = '1') OR (`person`.`person_id` is NULL)) AND (`address`.`address_name` LIKE '%Lleida%') 
GROUP BY person.person_id;

We may need to work with more than one database, eg, when importing or converting.

We need to create a link to the database. until now this has been transparent.

Same structure

Diferent or new structurecan build a object exteding CDBExternObject

This code snippet pertains to a custom application, and shows how to restore partial data from a database backup, for a especified period.

The file is intended to be placed on /cron folder of the module and to be executed from console.

 <?php

/** Restores journals from a backup table on certain period **/

class CAccountJournalBackUp extends CAccountJournal {
    function set_table($table) { return parent::set_table($table."_2020_04_29"); }
}

class CAccountJournalCurr extends CAccountJournal {
    function set_triggers() {
        parent::set_triggers();
        $this->allow_load_deleted();
    }

    function insert($omit_null = true) { return CDBObject::insert($omit_null); }

    function update($where = null, $order = "") { return CDBObject::update($where, $order); }

    function delete($where = null) { return CDBOBject::delete($where); }

    function delete_trigger_enable_real() { return true; }

    function on_valid_period($check_date = null) { return true; }
}

$curr = new CAccountJournalCurr();
$bak = new CAccountJournalBackup();

if ($app->read_optional("Restore al journals from 2019 from 2020_04_29?", "No")) {
    $app->console_output(INFO, "Deleting current data");
    $curr->delete($curr->between_start_end_query(strtotime("01-01-2019"), strtotime("31-12-2019")));
    if (!$curr->has_error()) {
        $app->console_output(INFO, "Creating new data");
        $curr->removeTriggers();
        foreach ($bak->load_all_between_start_end(strtotime("01-01-2019"), strtotime("31-12-2019")) as $ac_back) {
            if (!$curr->loadFromKey($ac_back->get_key_value())->valid()) {
                $curr->copy($ac_back)->insert();
                if ($curr->has_error()) {
                    $app->console_output(ERROR, "cannot insert, Exiting", $curr);
                    break;
                }
                $app->console_output(STEP);
            } else if ($curr->is_deleted()) {
                // The object is not present currently in period, but deleted: Replace
                $curr->copy($ac_back)->update();
                $app->console_output(STEP_ASTERISK);
            } else {
                $curr->copy($ac_back)->reset_id()->insert();
                $app->console_output(WARNING, "Object {$ac_back->str_id()} exists, inserted with {$curr->str_id()}");
            }
        }
        $curr->optimizeTable();
    } else {
        $app->console_output(ERROR, "error on delete", $curr);
    }
    $app->console_output(INFO, "End!");
} else {
    $app->console_output(INFO, "Canceled");
}

The object managing the data we want to restore, implements some triggers. Related to this code are

• CDeleteTrigger
• CPeriodTrigger

We need to create two classes extending the object, one for loading the backup data, and another one to not get interfered with triggers and stuff added on the object. We do not remove the triggers at class level, because we use functions from them.

The overrided on_valid_period function is defined on the object itself.

Although output of file is directly set as response, there is a module to get things properly shown, allowing to create content in a more structured dinamic way

generate content using jade template file.

<?php
/** Generate html content from a jade template **/

global $app;

$current->addJadeFile($app->getModuleFile("template.jade"), array("-p" => dirname(__FILE__)."/."));

Get content on data call

 
http://www.yoursite.domain/data/layout/html_content.xml?file=file&module=module§ion=section

 <?xml version="1.0" encoding="utf-8" ?>
<CONTENT><![CDATA[ html ]]></CONTENT>

You can create a deploy file for a module. The information is get from module.def.php, and this is a way to keep things in order, have clear files and things installed, and allow clean unnistalls.

user@server:~/www/yoursite_domain$ ./cron.php -m setrill -f get_deploy_file --module setrill
Setrill Framework v0.107 shell call
file created: cache/deploy/setrill_0_107.zip

The file you get contains a file named module.xml with information of the deploied module, and a deploy folder based on root folder of setrill containing the file all files that will be copied.

Create The module definition file