myMVC_module_DB
3.4.0
pdo extensiongit clone --branch 3.x https://github.com/gueff/myMVC.git myMVC_3.xIn your main module's config folder create your DB Config. (@see https://mymvc.ueffing.net/3.3.x/configuration#Modules-config-folder)
Db Config example for develop environments
//-------------------------------------------------------------------------------------
// Module DB
$aConfig['MODULE']['DB'] = array(
'db' => array(
'type' => 'mysql',
'host' => '127.0.0.1',
'port' => 3306,
'username' => getenv('db.username'),
'password' => getenv('db.password'),
'dbname' => getenv('db.dbname'),
'charset' => 'utf8'
),
'caching' => array(
'enabled' => true,
'lifetime' => '7200'
),
'logging' => array(
'log_output' => 'FILE',
// consider to turn it on for develop and test environments only
'general_log' => strtoupper('on'), # on | 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' => '/tmp/' . getenv('db.dbname') . '.log',
)
);getenv(), which means we store our secrets in the /.env file.PHP Class as a Representation of the DB Table
file: modules/Foo/Model/Table/User.php
<?php
namespace FooModelTable;
use DBModelDb;
class User extends Db
{
/**
* @var array
*/
protected $aField = array(
'email' => "varchar(255) COLLATE utf8_general_ci NOT NULL",
'active' => "int(1) DEFAULT '0' NOT NULL",
'uuid' => "varchar(36) COLLATE utf8_general_ci COMMENT 'uuid permanent' NOT NULL",
'uuidtmp' => "varchar(36) COLLATE utf8_general_ci COMMENT 'uuid; changes on create|login' NOT NULL",
'password' => "varchar(60) COLLATE utf8_general_ci COMMENT 'password_hash()' 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",
);
/**
* @param array $aDbConfig
* @throws ReflectionException
*/
public function __construct(array $aDbConfig = array())
{
// basic creation of the table
parent::__construct(
$this->aField,
$aDbConfig
);
}
}FooModelTableUser
email ... lastname as declared in property $aField
id, stampChange and stampCreate are added automaticallyDataType/DTFooModelTableUser.phpCreating a Table and adding a Foreign Key
file: modules/Foo/Model/Table/User.php
<?php
namespace FooModelTable;
use DBModelDb;
use DBDataTypeDBForeign;
class User extends Db
{
/**
* @var array
*/
protected $aField = array(
'email' => "varchar(255) COLLATE utf8_general_ci NOT NULL",
'active' => "int(1) DEFAULT '0' NOT NULL",
'uuid' => "varchar(36) COLLATE utf8_general_ci COMMENT 'uuid permanent' NOT NULL",
'uuidtmp' => "varchar(36) COLLATE utf8_general_ci COMMENT 'uuid; changes on create|login' NOT NULL",
'password' => "varchar(60) COLLATE utf8_general_ci COMMENT 'password_hash()' 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",
);
/**
* @param array $aDbConfig
* @throws ReflectionException
*/
public function __construct(array $aDbConfig = array())
{
// basic creation of the table
parent::__construct(
$this->aField,
$aDbConfig
);
$this->setForeignKey(
Foreign::create()
->set_sForeignKey('id_FooModelTableGroup')
->set_sReferenceTable('FooModelTableGroup')
);
}
}FooModelTableUser
email ... lastname as declared in property $aField
id, stampChange and stampCreate are added automaticallyid_FooModelTableGroup -pointing to table FooModelTableGroup- is added by method setForeignKey()DataType/DTFooModelTableUser.phpfile: 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 FooModelTableUser
* 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 FooModel;
use DBModelDbInit;
use DBTraitDbInitTrait;
class DB extends DbInit
{
use DbInitTrait;
/**
* @var FooModelTableUser
*/
public static $oFooModelTableUser;
}create a file db.php (you can name it as you like) in the event folder of your myMVC module and declare the bindings as follows.
file /modules/{MODULE}/etc/event/db.php
<?php
MVCEvent::processBindConfigStack([
// let create an openapi yaml file
// according to DB Table DataType Classes
// when the DataBase Tables setup changes
'db.model.db.construct.saveCache' => array(
function(string $sTableName = '') {
// one-timer
if (false === MVCRegistry::isRegistered('DB::openapi'))
{
MVCRegistry::set('DB::openapi', true);
// generate /modules/{MODULE}/DataType/DTTables.yaml
$sYamlFile =DBModelOpenapi::createDTYamlOnDTClasses(
// pass instance of your concrete DB Class
FooModelDB::init()
);
}
}
),
]);In your main Controller class just create a new Instanciation of your DBInit class.
A good place is the __construct() method.
namespace FooController;
use FooModelDB;
public function __construct ()
{
DB::init();
}after that you can access your TableClass from everywhere - even from frontend templates:
Usage
DB::$oFooModelTableUser->...<method>...create (INSERT)
therefore an object of its related Datatype must be instaciated and given to the method create.
Here e.g. with Datatype "DTFooModelTableUser" to TableClass "modules/Foo/Model/DB/TableUser":
DB::$oFooModelTableUser->create(
DTFooModelTableUser::create()
->set_id_FooModelTableGroup(1)
->set_uuid(Strings::uuid4())
->set_email('[email protected]')
->set_forename('foo')
->set_lastname('bar')
->set_nickname('foo')
->set_password(password_hash('...password...', PASSWORD_DEFAULT))
->set_active(1)
->set_stampChange(date('Y-m-d H:i:s'))
->set_stampCreate(date('Y-m-d H:i:s'))
);retrieveTupel asks for a specific Tupel and returns the DataType Object according to the requested Table.
retrieveTupel - identified by id
/** @var FooDataTypeDTFooModelTableUser $oDTFooModelTableUser */
$oDTFooModelTableUser = DB::$oFooModelTableUser->retrieveTupel(
DTFooModelTableUser::create()
->set_id(2)
)retrieve returns an array of DataType Objects according to the requested Table.
retrieve: get all Datasets
/** @var FooDataTypeDTFooModelTableUser[] $aDTFooModelTableUser */
$aDTFooModelTableUser = DB::$oFooModelTableUser->retrieveTupel();retrieve: get specific Datasets
/** @var FooDataTypeDTFooModelTableUser[] $aDTFooModelTableUser */
$aDTFooModelTableUser = DB::$oFooModelTableUser->retrieve(
DTArrayObject::create()
->add_aKeyValue(
DTKeyValue::create()
->set_sKey('stampChange')
->set_mOptional1('LIKE')
->set_sValue('2021-06-19')
);
);retrieve: get Datasets with sort order
/** @var FooDataTypeDTFooModelTableUser[] $aDTFooModelTableUser */
$aDTFooModelTableUser = DB::$oFooModelTableUser->retrieve(
DTArrayObject::create()
->add_aKeyValue(
DTKeyValue::create()
->set_sKey('email')
->set_mOptional1('LIKE')
->set_sValue('%@example.com%')
),
DTArrayObject::create()
->add_aKeyValue(
DTKeyValue::create()
->set_sValue('ORDER BY id ASC')
)
);retrieve: get first 30 Datasets (LIMIT 0,30)
/** @var FooDataTypeDTFooModelTableUser[] $aDTFooModelTableUser */
$aDTFooModelTableUser = DB::$oFooModelTableUser->retrieve(
null,
DTArrayObject::create()
->add_aKeyValue(
DTKeyValue::create()
->set_sValue('LIMIT 0,30')
)
)updateTupel: update a specific Tupel - identified by id
// get Tupel
/** @var FooDataTypeDTFooModelTableUser $oDTFooModelTableUser */
$oDTFooModelTableUser = DB::$oFooModelTableUser->retrieveTupel(
DTFooModelTableUser::create()->set_id(2)
)
// modify Tupel
$oDTFooModelTableUser->set_nickname('XYZ');
// update Tupel
/** @var boolean $bSuccess */
$bSuccess = DB::$oFooModelTableUser->updateTupel(
$oDTFooModelTableUser
);id will be updated.update: update all Tupel which are affected by the where clause
/** @var boolean $bSuccess */
$bSuccess = DB::$oFooModelTableUser->update(
DTFooModelTableUser::create()
->set_active('1'),
// where
DTArrayObject::create()
->add_aKeyValue(
DTKeyValue::create()
->set_sKey('active')
->set_mOptional1('=')
->set_sValue('0')
)
);deleteTupel: delete this specific Tupel - identified by id
/** @var boolean $bSuccess */
$bSuccess = DB::$oFooModelTableUser->deleteTupel(
DTFooModelTableUser::create()
->set_id(2)
)delete: delete all Tupel which are affected by the where clause
$bSuccess = DB::$oFooModelTableUser->delete(
// where
DTArrayObject::create()
->add_aKeyValue(
DTKeyValue::create()
->set_sKey('stampCreate')
->set_mOptional1('<')
->set_sValue('2023-06-19 00:00:00')
)
);// Amount of all Datasets
$iAmount = DB::$oFooModelTableUser->count();
// Amount of specific Datasets
$iAmount = DB::$oFooModelTableUser->count(
DTArrayObject::create()
->add_aKeyValue(
DTKeyValue::create()
->set_sKey('stampChange')
->set_mOptional1('=')
->set_sValue('2021-06-19')
)
);// Returns a checksum of the table
$iChecksum = DB::$oFooModelTableUser->checksum();returns array with table fields info
$aFieldInfo = DB::$oFooModelTableUser->getFieldInfo();example return
// type: array, items: 9
[
'id_FooModelTableGroup' => [
'Field' => 'id_FooModelTableGroup',
'Type' => 'int(11)',
'Null' => 'YES',
'Key' => 'MUL',
'Default' => NULL,
'Extra' => '',
'php' => 'int',
],
'email' => [
'Field' => 'email',
'Type' => 'varchar(255)',
'Null' => 'NO',
'Key' => '',
'Default' => NULL,
'Extra' => '',
'php' => 'string',
],
'active' => [
'Field' => 'active',
'Type' => 'int(1)',
'Null' => 'NO',
'Key' => '',
'Default' => '0',
'Extra' => '',
'php' => 'int',
],
'uuid' => [
'Field' => 'uuid',
'Type' => 'varchar(36)',
'Null' => 'NO',
'Key' => '',
'Default' => NULL,
'Extra' => '',
'php' => 'string',
],
'uuidtmp' => [
'Field' => 'uuidtmp',
'Type' => 'varchar(36)',
'Null' => 'NO',
'Key' => '',
'Default' => NULL,
'Extra' => '',
'php' => 'string',
],
'password' => [
'Field' => 'password',
'Type' => 'varchar(60)',
'Null' => 'NO',
'Key' => '',
'Default' => NULL,
'Extra' => '',
'php' => 'string',
],
'nickname' => [
'Field' => 'nickname',
'Type' => 'varchar(10)',
'Null' => 'NO',
'Key' => '',
'Default' => NULL,
'Extra' => '',
'php' => 'string',
],
'forename' => [
'Field' => 'forename',
'Type' => 'varchar(25)',
'Null' => 'NO',
'Key' => '',
'Default' => NULL,
'Extra' => '',
'php' => 'string',
],
'lastname' => [
'Field' => 'lastname',
'Type' => 'varchar(25)',
'Null' => 'NO',
'Key' => '',
'Default' => NULL,
'Extra' => '',
'php' => 'string',
],
]
SQL example using $oPDO query .. fetch
/**
* @return FooDataTypeDTFooModelTableUser
* @throws ReflectionException
*/
public function getUserObject()
{
// get result & cast to datatype object
$oDTFooModelTableUser = DTFooModelTableUser::create(
(array) DB::$oPDO
->query("SELECT * FROM `FooModelTableUser` WHERE `id` = '1'")
->fetch(PDO::FETCH_ASSOC)
);
return $oDTFooModelTableUser
}// type: object
FooDataTypeDTFooModelTableUser::__set_state(array(
'id' => 1,
'stampChange' => '2023-09-28 10:18:03',
'stampCreate' => '2023-09-28 10:16:14',
'id_TableGroup' => 1,
'email' => '[email protected]',
'active' => 1,
'uuid' => '8b838038-5dd7-11ee-8620-2cf05d0841fd',
'uuidtmp' => '8b838839-5dd7-11ee-8620-2cf05d0841fd',
'password' => '*******************************************',
'nickname' => 'admin',
'forename' => 'foo',
'lastname' => 'bar',
))
SQL example using $oPDO query .. fetchAll
/**
* @return array|FooDataTypeDTFooModelTableUser[]
* @throws ReflectionException
*/
public function getUserObjectsArray()
{
// get result & cast all results to datatype objects
$aDTFooModelTableUser = array_map(
function($aData){
return DTFooModelTableUser::create($aData);
},
(array) DB::$oPDO
->query("SELECT * FROM `FooModelTableUser` WHERE `active` = '1'")
->fetchAll(PDO::FETCH_ASSOC)
);
return $aDTFooModelTableUser
}// type: array, items: 2
[
0 => [
FooDataTypeDTFooModelTableUser::__set_state(array(
'id' => 1,
'stampChange' => '2023-09-28 10:18:03',
'stampCreate' => '2023-09-28 10:16:14',
'id_TableGroup' => 1,
'email' => '[email protected]',
'active' => 1,
'uuid' => '8b838038-5dd7-11ee-8620-2cf05d0841fd',
'uuidtmp' => '8b838839-5dd7-11ee-8620-2cf05d0841fd',
'password' => '*******************************************',
'nickname' => 'admin',
'forename' => 'foo',
'lastname' => 'bar',
)],
1 => [
FooDataTypeDTFooModelTableUser::__set_state(array(
'id' => 2,
'stampChange' => '2023-09-28 10:18:03',
'stampCreate' => '2023-09-28 10:16:14',
'id_TableGroup' => 1,
'email' => '[email protected]',
'active' => 1,
'uuid' => '1b838038-5dd7-11ee-8620-2cf05d0841fd',
'uuidtmp' => '2b838839-5dd7-11ee-8620-2cf05d0841fd',
'password' => '*******************************************',
'nickname' => 'foo',
'forename' => 'foo2',
'lastname' => 'bar2',
)],
]
db.model.db.construct.saveCache
db.model.db.setSqlLoggingState.exception
db.model.db.setForeignKey.exception
db.model.db.checkIfTableExists.exception
db.model.db.createTable.exception
db.model.db.synchronizeFields.exception
db.model.db.synchronizeFields.delete.exception
db.model.db.synchronizeFields.insert.exception
db.model.db.synchronizeFields.update.exception
db.model.db.create.sql
db.model.db.createTable.sql
db.model.db.insert.sql
db.model.db.create.exception
db.model.db.retrieve.sql
db.model.db.retrieve.exception
db.model.db.count.sql
db.model.db.count.exception
db.model.db.update.sql
db.model.db.update.exception
db.model.db.delete.sql
db.model.db.delete.exception
you can log SQL queries by listening to events.
create a file sql.php in the event folder of your myMVC module
and declare the bindings as follows.
/modules/{MODULE}/etc/event/sql.php
#-------------------------------------------------------------
# declare bindings
$aEvent = [
'db.model.db.create.sql' => array(
function(MVCDataTypeDTArrayObject $oDTArrayObject) {
MVCLog::write($oDTArrayObject->getDTKeyValueByKey('sSql')->get_sValue(), 'sql.log');
}
),
'db.model.db.insert.sql' => array(
function(MVCDataTypeDTArrayObject $oDTArrayObject) {
MVCLog::write($oDTArrayObject->getDTKeyValueByKey('sSql')->get_sValue(), 'sql.log');
}
),
'db.model.db.retrieve.sql' => array(
function(MVCDataTypeDTArrayObject $oDTArrayObject) {
MVCLog::write($oDTArrayObject->getDTKeyValueByKey('sSql')->get_sValue(), 'sql.log');
}
),
'db.model.db.update.sql' => array(
function(MVCDataTypeDTArrayObject $oDTArrayObject) {
MVCLog::write($oDTArrayObject->getDTKeyValueByKey('sSql')->get_sValue(), 'sql.log');
}
),
'db.model.db.delete.sql' => array(
function(MVCDataTypeDTArrayObject $oDTArrayObject) {
MVCLog::write($oDTArrayObject->getDTKeyValueByKey('sSql')->get_sValue(), 'sql.log');
}
),
'db.model.db.createTable.sql' => array(
function(MVCDataTypeDTArrayObject $oDTArrayObject) {
MVCLog::write($oDTArrayObject->getDTKeyValueByKey('sSql')->get_sValue(), 'sql.log');
}
),
];
#-------------------------------------------------------------
# process: bind the declared ones
MVCEvent::processBindConfigStack($aEvent);
