This website uses Cookies to provide you with the best possible service. Please see our Privacy Policy for more information. Click the check box below to accept cookies. Then confirm with a click on "Save".  
Status: 2024-01-21
← Policy
CLI Wrapping →

Database ⛁

Credentials

Edit the db.* settings in your /.env file.

#-----------------------------------------------------
# My Application

# Environment
MVC_ENV=develop

#-----------------------------------------------------
# DB
db.type=mysql
db.host=127.0.0.1
db.port=3306
db.dbname=Emvicy1x
db.username=root
db.password=

Creation

2.1. Create DB Config

In your main module's config environment folder edit your DB Config. (@see /1.x/configuration#Modules-environment-config-file)

The main config file resides in _db.php.

Overwrite the settings from _db.php in your concrete environment config file e.g. develop.php (depends on what your MVC_ENV is set to.)

For example you might want to turn on Logging for develop environment:

Db Config example for develop environments


//----------------------------------------------------------------------------------------------------------------------
// DB
// watch database log; e.g.:      cd /tmp; tail -f Foo*.log

require realpath(__DIR__) . '/_db.php';

// consider a logrotate mechanism for this logfile as it may grow quickly
$aConfig['MODULE']['Foo']['DB']['logging']['general_log'] = 'ON'; // consider to set it to ON for develop or test environments only

example: file _db.php - (modules/Foo/etc/config/Foo/config/_db.php) 

<?php

//######################################################################################################################
// Module DB

$aConfig['MODULE']['Foo']['DB'] = array(

    'db' => array(
        'type' => getenv('db.type'),
        'host' => getenv('db.host'),
        'port' => getenv('db.port'),
        'username' => getenv('db.username'),
        'password' => getenv('db.password'),
        'dbname' => getenv('db.dbname'),
        'charset' => 'utf8',
    ),
    'caching' => array(
        'enabled' => true,
        'lifetime' => '1', # minutes
    ),
    'logging' => array(
        'log_output' => 'FILE',

        // consider to turn it on for develop and test environments only
        'general_log' => 'OFF',

        // 1) make sure write access is given to the folder
        // as long as the db user is going to write and not the webserver user
        // 2) consider a logrotate mechanism for this logfile as it may grow quickly
        'general_log_file' => $aConfig['MVC_LOG_FILE_DB_DIR'] . getenv('db.dbname') . '_' . getenv('MVC_ENV') . '.log',
    ),
);

2.2. Creating a concrete Table Class

PHP Class as a Representation of the DB Table

file: modules/Foo/Model/Table/User.php

<?php

namespace Foo\Model\Table;

use MVC\DB\DataType\DB\Foreign;
use MVC\DB\Model\Db;

class User extends Db
{
    /**
     * @var array
     */
    protected $aField = array();

    /**
     * @param array $aDbConfig
     * @throws \ReflectionException
     */
    public function __construct(array $aDbConfig = array())
    {
        $this->aField = array(
            'email'     => 'varchar(255)    COLLATE utf8_general_ci NOT NULL UNIQUE',
            'active'    => "int(1)          DEFAULT '0'             NOT NULL",
            'uuid'      => "varchar(36)     COLLATE utf8_general_ci NOT NULL UNIQUE COMMENT 'uuid permanent'",
            'uuidtmp'   => "varchar(36)     COLLATE utf8_general_ci NOT NULL UNIQUE COMMENT 'uuid; changes on create|login'",
            'password'  => 'varchar(60)     COLLATE utf8_general_ci NOT NULL',
            'nickname'  => "varchar(10)     COLLATE utf8_general_ci NOT NULL",
            'forename'  => "varchar(25)     COLLATE utf8_general_ci NOT NULL",
            'lastname'  => "varchar(25)     COLLATE utf8_general_ci NOT NULL",
        );

        // basic creation of the table
        parent::__construct(
            $this->aField,
            $aDbConfig
        );
    }
}
  • creates the Table FooModelTableUser
    • Table has several fields from email ... lastname as declared in property $aField
      • 🛈 The Table fields id, stampChange and stampCreate are added automatically
      • do not add these fields by manually
  • generates a DataType Class \Foo\DataType\DTFooModelTableUser in modules/Foo/DataType/

Creating a Table and adding a Foreign Key

file: modules/Foo/Model/Table/User.php

<?php

namespace Foo\Model\Table;

use MVC\DB\DataType\DB\Foreign;
use MVC\DB\Model\Db;

class User extends Db
{
    /**
     * @var array
     */
    protected $aField = array();

    /**
     * @param array $aDbConfig
     * @throws \ReflectionException
     */
    public function __construct(array $aDbConfig = array())
    {
        $this->aField = array(
            'email'     => 'varchar(255)    COLLATE utf8_general_ci NOT NULL UNIQUE',
            'active'    => "int(1)          DEFAULT '0'             NOT NULL",
            'uuid'      => "varchar(36)     COLLATE utf8_general_ci NOT NULL UNIQUE COMMENT 'uuid permanent'",
            'uuidtmp'   => "varchar(36)     COLLATE utf8_general_ci NOT NULL UNIQUE COMMENT 'uuid; changes on create|login'",
            'password'  => 'varchar(60)     COLLATE utf8_general_ci NOT NULL',
            'nickname'  => "varchar(10)     COLLATE utf8_general_ci NOT NULL",
            'forename'  => "varchar(25)     COLLATE utf8_general_ci NOT NULL",
            'lastname'  => "varchar(25)     COLLATE utf8_general_ci NOT NULL",
        );

        // basic creation of the table
        parent::__construct(
            $this->aField,
            $aDbConfig
        );
        $this->setForeignKey(
            Foreign::create()
                ->set_sForeignKey('id_TableGroup')
                ->set_sReferenceTable('FooModelTableGroup')
        );
    }
}
  • creates the Table FooModelTableUser
    • Table has several fields from email ... lastname as declared in property $aField
      • 🛈 The Table fields id, stampChange and stampCreate are added automatically
      • do not add these fields by manually
  • The foreign key id_TableGroup -pointing to table FooModelTableGroup- is added by method set_sForeignKey()
  • generates a DataType Class \Foo\DataType\DTFooModelTableUser in modules/Foo/DataType/

2.3. Creating a DBInit class that is used for each DB access

example: modules/Foo/Model/DB.php 

<?php

/**
 * - register your db table classes as static properties.
 * - add a doctype to each static property
 * - these doctypes must contain the vartype information about the certain class
 * @example
 *      @var Foo\Model\TableUser
 *      public static $oFooModelTableUser;
 * ---
 * [!]  it is important to declare the vartype expanded with a full path
 *      avoid to make use of `use ...` support
 *      otherwise the classes could not be read correctly
 */

namespace Foo\Model;

use DB\Model\DbInit;
use DB\Trait\DbInitTrait;

class DB extends DbInit
{
    use DbInitTrait;

    /**
     * @var \Foo\Model\TableUser
     */
    public static $oFooModelTableUser;
}

2.4. Let generate an openapi yaml schema file for data type classes

This builds an openapi.yaml DTTables.yaml in the primary module's DataType folder based on data type classes of the DB tables.

if not already exists, create a file db.php in the event folder of your Emvicy module and declare the bindings as follows.

example: /modules/Foo/etc/event/db.php

<?php

\MVC\Event::processBindConfigStack([

    'mvc.db.model.db.construct.saveCache' => [
        /*
         * let create an openapi yaml file according to DB Table DataType Classes when the DataBase Tables setup changes
         */
        function() {
            // one-timer
            if (false === \MVC\Registry::isRegistered('DB::openapi'))
            {
                \MVC\Registry::set('DB::openapi', true);
                // generate /modules/{MODULE}/DataType/DTTables.yaml
                $sYamlFile = \MVC\DB\Model\Openapi::createDTYamlOnDTClasses(
                    // pass instance of your concrete DB Class
                    \Foo\Model\DB::init()
                );
            }
        },
    ],
]);

3. Usage

In your main Controller class just create a new Instanciation of your DBInit class. A good place is the __construct() method.

namespace Foo\Controller;

use Foo\Model\DB;

public function __construct ()
{
    DB::init();
}

after that you can access your TableClass from everywhere - even from frontend templates:

Usage

DB::$oFooModelTableUser->...<method>...

3.1. create

therefore an object of its related Datatype must be instanciated and given to the method create. Here e.g. with Datatype "DTFooModelTableUser" to TableClass "modules/Foo/Model/Table/User":

example create: put data into table 

// create DataType object
$oDTFooModelTableUser = DTFooModelTableUser::create()
    ->set_id_TableGroup(1)
    ->set_email('foo@example.com')
    ->set_password('...password...')
    ->set_forename('foo')
    ->set_lastname('bar')
    ->set_nickname('foo')
    ->set_uuid(Strings::uuid4())
    ->set_uuidtmp(Strings::uuid4())
    ->set_active(1);

// put DataType object into Table and get updated object back
/** @var \Foo\DataType\DTFooModelTableUser $oDTFooModelTableUser */
$oDTFooModelTableUser = DB::$oFooModelTableUser->create(
    $oDTFooModelTableUser
);

// on success, `$oDTFooModelTableUser` now has an id (auto increment of database table) set
// on fail, id = 0
$iId = $oDTFooModelTableUser->get_id();

3.2. retrieve

getOnId: returns tupel object or field of tupel by id

example getOnId: get Object from table where id=2 

/** @var \Foo\DataType\DTFooModelTableUser $oDTFooModelTableUser */
$oDTFooModelTableUser = DB::$oFooModelTableUser->getOnId(2);

example getOnId: get email from table where id=2 

/** @var string $sEmail */
$sEmail = DB::$oFooModelTableUser->getOnId(2, DTFooModelTableUser::getPropertyName_email()); # using name helper
/** @var string $sEmail */
$sEmail = DB::$oFooModelTableUser->getOnId(2, 'email'); # plain text

retrieveTupel asks for a specific Tupel and returns the DataType Object according to the requested Table.

example retrieveTupel: get Object from table where id=2

/** @var \Foo\DataType\DTFooModelTableUser $oDTFooModelTableUser */
$oDTFooModelTableUser = DB::$oFooModelTableUser->retrieveTupel(
    DTFooModelTableUser::create()->set_id(2)
);

retrieve returns an array of DataType Objects according to the requested Table.

example retrieve: get all Datasets

/** @var \Foo\DataType\DTFooModelTableUser[] $aDTFooModelTableUser */
$aDTFooModelTableUser = DB::$oFooModelTableUser->retrieve();

example retrieve: get specific Datasets

/** @var \Foo\DataType\DTFooModelTableUser[] $aDTFooModelTableUser */
$aDTFooModelTableUser = DB::$oFooModelTableUser->retrieve(
    [ // where using name helper
        DTDBWhere::create()->set_sKey( DTFooModelTableUser::getPropertyName_email() )->set_sRelation('LIKE')->set_sValue('%example%')
    ]
);
/** @var \Foo\DataType\DTFooModelTableUser[] $aDTFooModelTableUser */
$aDTFooModelTableUser = DB::$oFooModelTableUser->retrieve(
    [ // where
        DTDBWhere::create()->set_sKey('email')->set_sRelation('LIKE')->set_sValue('%example%')
    ]
);

example retrieve: get Datasets with options

/** @var \Foo\DataType\DTFooModelTableUser[] $aDTFooModelTableUser */
$aDTFooModelTableUser = DB::$oFooModelTableUser->retrieve(
    [ // where
        DTDBWhere::create()->set_sKey( DTFooModelTableUser::getPropertyName_email() )->set_sRelation('LIKE')->set_sValue('%example%')
    ],
    [ // option
        DTDBOption::create()->set_sValue('ORDER BY `email` ASC'),
        DTDBOption::create()->set_sValue('LIMIT 0,10'),
    ]
);

example retrieve: get first 30 Datasets (LIMIT 0,30)

/** @var \Foo\DataType\DTFooModelTableUser[] $aDTFooModelTableUser */
$aDTFooModelTableUser = DB::$oFooModelTableUser->retrieve(aDTDBOption: [
    DTDBOption::create()->set_sValue('LIMIT 0, 30')
]);
  • here the named Parameter aDTDBOption is used to address the right parameter correctly

3.3. update

example updateTupel: update Object in table where id=2

// retrieve User object with id=2
/** @var \Foo\DataType\DTFooModelTableUser $oDTFooModelTableUser */
$oDTFooModelTableUser = DB::$oFooModelTableUser->getOnId(2);

// modify User object
$oDTFooModelTableUser->set_nickname('ABC');

// update tupel with modified object
$bSuccess = DB::$oFooModelTableUser->updateTupel(
    $oDTFooModelTableUser
);

example update: update all Tupel with data defined in set (array) which are affected by the where clause (array)

/** @var boolean $bSuccess */
$bSuccess = DB::$oFooModelTableUser->update(
    [ // set
        DTDBSet::create()->set_sKey( DTFooModelTableUser::getPropertyName_active() )->set_sValue(0),
        DTDBSet::create()->set_sKey( DTFooModelTableUser::getPropertyName_email() )->set_sValue('bar@example.com')
    ],
    [ // where
        DTDBWhere::create()->set_sKey( DTFooModelTableUser::getPropertyName_email() )->set_sRelation('LIKE')->set_sValue('%example.com')
    ]
);

update via SQL Statement 

DB::$oPDO->query("UPDATE `FooModelTableUser` SET `active` = '0' WHERE `email` = 'foo@example.com'");

3.4. delete

deleteTupel: delete this one specific Tupel - identified only by id (id is required; other values do not have an effect)

// delete User object (you retrieved before)
$bSuccess = DB::$oFooModelTableUser->deleteTupel(
    $oDTFooModelTableUser
);

// example deleting by setting object explicitly
$bSuccess = DB::$oFooModelTableUser->deleteTupel(
    DTFooModelTableUser::create()->set_id(2)
);

delete: delete all Tupel which are affected by the where clause (array)

// example setting key and value directly
$bSuccess = DB::$oFooModelTableUser->delete([ // array of where clauses
    DTDBWhere::create()->set_sKey('id')->set_sValue(2),
    DTDBWhere::create()->set_sKey('active')->set_sValue(0),
]);

// example setting the key using Property getter
$bSuccess = DB::$oFooModelTableUser->delete([ // array of where clauses
    DTDBWhere::create()->set_sKey(DTFooModelTableUser::getPropertyName_id())->set_sValue(2),
    DTDBWhere::create()->set_sKey(DTFooModelTableUser::getPropertyName_active())->set_sValue(0),
]);

// example setting the key using Property getter; take values from User object (you retrieved before)
$bSuccess = DB::$oFooModelTableUser->delete([ // array of where clauses
    DTDBWhere::create()->set_sKey(DTFooModelTableUser::getPropertyName_id())->set_sValue($oDTFooModelTableUser->get_id()),
    DTDBWhere::create()->set_sKey(DTFooModelTableUser::getPropertyName_active())->set_sValue($oDTFooModelTableUser->get_active()),
]);

3.5. count

// Amount of all Datasets
$iAmount = DB::$oFooModelTableUser->count();

// Amount of specific Datasets
$iAmount = DB::$oFooModelTableUser->count([
    DTDBWhere::create()->set_sKey( DTFooModelTableUser::getPropertyName_email() )->set_sRelation('LIKE')->set_sValue('%example.com')
]);

3.6. checksum

// Returns a checksum of the table
$iChecksum = DB::$oFooModelTableUser->checksum();
// type: integer
1138916503

3.7. getFieldInfo

returns array with table fields info

get info of certain field email 

// get info of certain field `email`
$aFieldInfo = DB::$oFooModelTableUser->getFieldInfo('email');

example return 

// type: array, items: 12
[
    'Field' => 'email',
    'Type' => 'varchar(255)',
    'Collation' => 'utf8_general_ci',
    'Null' => 'NO',
    'Key' => 'UNI',
    'Default' => NULL,
    'Extra' => '',
    'Privileges' => 'select,insert,update,references',
    'Comment' => '',
    '_php' => 'string',
    '_type' => 'varchar',
    '_typeValue' => 255,
]

get info of all fields 

// get info of all fields
$aFieldInfo = DB::$oFooModelTableUser->getFieldInfo();

example return (shortened) 

// type: array, items: 9
[
    'id_TableGroup' => [
        'Field' => 'id_TableGroup',
        'Type' => 'int(11)',
        'Collation' => NULL,
        'Null' => 'YES',
        'Key' => 'MUL',
        'Default' => NULL,
        'Extra' => '',
        'Privileges' => 'select,insert,update,references',
        'Comment' => '',
        '_php' => 'int',
        '_type' => 'int',
        '_typeValue' => 11,
    ],
    'email' => [
        'Field' => 'email',
        'Type' => 'varchar(255)',
        'Collation' => 'utf8_general_ci',
        'Null' => 'NO',
        'Key' => 'UNI',
        'Default' => NULL,
        'Extra' => '',
        'Privileges' => 'select,insert,update,references',
        'Comment' => '',
        '_php' => 'string',
        '_type' => 'varchar',
        '_typeValue' => 255,
    ],
    ...
]

3.8. SQL

fetchRow: select a single tupel (a row)

/** @var \Foo\DataType\DTFooModelTableUser $oDTFooModelTableUser */
$oDTFooModelTableUser = DTFooModelTableUser::create(
    DB::$oPDO->fetchRow("SELECT * FROM `FooModelTableUser` WHERE id = '2'")
);
  • here we select the entry which id = 2
  • we put in into the table's Datatype object of type \Foo\DataType\DTFooModelTableUser

fetchAll: select all tupel (multiple rows)

/** @var \Foo\DataType\DTFooModelTableUser[] $aDTFooModelTableUser */
$aDTFooModelTableUser = array_map(
    function($aData){
        return DTFooModelTableUser::create($aData);
    },
    DB::$oPDO->fetchAll("SELECT * FROM `FooModelTableUser` WHERE email LIKE '%example.com'")
);
  • here we select all entries which email is like '%example.com'
  • we map them all into the table's Datatype object
  • so we get an array of type DTFooModelTableUser[]

query: insert 

DB::$oPDO->query("INSERT INTO  `FooModelTableUser` 
    (`stampChange`,`stampCreate`,`id_TableGroup`,`email`,`active`,`uuid`,`uuidtmp`,`password`,`nickname`,`forename`,`lastname`) 
    VALUES ('2023-12-01 18:53:33',
            '2023-12-01 18:53:33',
            '1','foo2@example.com',
            '1',
            '7cb3c040-36f1-4aa0-ae3a-8ef19d0667aa',
            '236dde6b-f6a1-440f-afd2-393291331642',
            '" . password_hash('...password...', PASSWORD_DEFAULT) . "',
            'foo2',
            'foo2',
            'bar2');"
);

query: update

DB::$oPDO->query("UPDATE `FooModelTableUser` SET `active` = '0' WHERE `email` = 'foo@example.com'");

4. Events

see Database Events

4.1. Logging SQL

logging at application side

Consider to set to true for develop environments only: logging request into MVC_LOG_FILE_SQL.

enable sql logging in your config file 

$aConfig['MVC_LOG_SQL'] = true;

if it not already exists, create a file db.php in the event folder of your module and declare the bindings as follows.

Emvicy logs each action into the logfile specified in the var MVC_LOG_FILE_SQL; which is per default: application/log/sql.log.

/modules/{MODULE}/etc/event/db.php

\MVC\Event::processBindConfigStack([

    'mvc.db.model.*.sql' => array(
        /*
         * log *all* SQL Statements, if enabled via config
         */
        function(string $sSql) {
            if (true === \MVC\Config::get_MVC_LOG_SQL())
            {
                \MVC\Log::write(
                    \MVC\Strings::tidy($sSql),
                    \MVC\Config::get_MVC_LOG_FILE_SQL()
                );
            }
        }
    ),
]);

logging at database engine side

if you set general_log to ON iny your DB Config, the database logs each action into the logfile specified in the var MVC_LOG_FILE_DB_DIR.

$aConfig['MVC_LOG_FILE_DB_DIR'] = '/tmp/';
  • make sure write access is given to the folder as long as the db user is going to write and not the webserver user
  • consider a logrotate mechanism for this logfile as it may grow quickly
← Policy
CLI Wrapping →