myMVC_module_DB
3.4.0
pdogit clone --branch 3.x https://github.com/gueff/myMVC.git myMVC_3.xEn la carpeta de configuración de su módulo principal, cree su configuración DB. (@see https://mymvc.ueffing.net/3.3.x/configuration#modules-config-folder)
Ejemplo de configuración de DB para entornos develop
//-------------------------------------------------------------------------------------
// 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() , lo que significa que almacenamos nuestros secretos en el archivo /.env .Clase PHP como una representación de la tabla DB
Archivo: modules/Foo/Model/Table/User.php
<?php
namespace Foo Model Table ;
use DB Model Db ;
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
);
}
}FooModelTableUseremail ... lastname según lo declarado en la propiedad $aFieldid de campos de tabla, stampChange y stampCreate se agregan automáticamenteDataType/DTFooModelTableUser.phpCrear una tabla y agregar una clave extranjera
Archivo: modules/Foo/Model/Table/User.php
<?php
namespace Foo Model Table ;
use DB Model Db ;
use DB DataType DB Foreign ;
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 ' )
);
}
}FooModelTableUseremail ... lastname según lo declarado en la propiedad $aFieldid de campos de tabla, stampChange y stampCreate se agregan automáticamenteid_FooModelTableGroup -pointing a la tabla FooModelTableGroup - se agrega mediante el método setForeignKey()DataType/DTFooModelTableUser.php Archivo: 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 Foo Model ;
use DB Model DbInit ;
use DB Trait DbInitTrait ;
class DB extends DbInit
{
use DbInitTrait;
/**
* @var FooModelTableUser
*/
public static $ oFooModelTableUser ;
} Cree un archivo db.php (puede nombrarlo como desee) en la carpeta de eventos de su módulo mymvc y declare los enlaces de la siguiente manera.
file /modules/{MODULE}/etc/event/db.php
<?php
MVC Event:: 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 === MVC Registry:: isRegistered ( ' DB::openapi ' ))
{
MVC Registry:: set ( ' DB::openapi ' , true );
// generate /modules/{MODULE}/DataType/DTTables.yaml
$ sYamlFile = DB Model Openapi:: createDTYamlOnDTClasses (
// pass instance of your concrete DB Class
Foo Model DB :: init ()
);
}
}
),
]); En su clase de controlador principal, simplemente cree una nueva instanciación de su clase DBinit. Un buen lugar es el método __construct() .
namespace Foo Controller ;
use Foo Model DB ;
public function __construct ()
{
DB :: init ();
}Después de eso, puede acceder a su mantel desde todas partes, incluso desde plantillas de frontend:
Uso
DB :: $ oFooModelTableUser ->. . .<method> . . . create (insertar) Por lo tanto, un objeto de su tipo de datos relacionado debe instacionarse y administrarse al método create . Aquí, por ejemplo, con el tipo de datos "dtfoomodeltableUser" a thebreclass "módulos/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 solicita un tupel específico y devuelve el objeto DataType de acuerdo con la tabla solicitada.
retrieveTupel - IDENTIFICADO POR id
/** @var FooDataTypeDTFooModelTableUser $oDTFooModelTableUser */
$ oDTFooModelTableUser = DB :: $ oFooModelTableUser -> retrieveTupel (
DTFooModelTableUser:: create ()
-> set_id ( 2 )
) retrieve devuelve una matriz de objetos de tipo de datos de acuerdo con la tabla solicitada.
retrieve : Obtenga todos los conjuntos de datos
/** @var FooDataTypeDTFooModelTableUser[] $aDTFooModelTableUser */
$ aDTFooModelTableUser = DB :: $ oFooModelTableUser -> retrieveTupel (); retrieve : Obtenga conjuntos de datos específicos
/** @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 : Obtenga conjuntos de datos con orden de orden
/** @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 : Obtenga los primeros 30 conjuntos de datos (límite 0,30)
/** @var FooDataTypeDTFooModelTableUser[] $aDTFooModelTableUser */
$ aDTFooModelTableUser = DB :: $ oFooModelTableUser -> retrieve (
null ,
DTArrayObject:: create ()
-> add_aKeyValue (
DTKeyValue:: create ()
-> set_sValue ( ' LIMIT 0,30 ' )
)
) updateTupel : actualizar un tupel específico, identificado por 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 de Object. update : Actualizar todo el Tupel que se ven afectados por la cláusula WHERE
/** @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 : Elimine este tupel específico, identificado por id
/** @var boolean $bSuccess */
$ bSuccess = DB :: $ oFooModelTableUser -> deleteTupel (
DTFooModelTableUser:: create ()
-> set_id ( 2 )
) delete : Elimine todo el Tupel que se ve afectado por la cláusula WHERE
$ 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 ();Devuelve la matriz con información de campos de tabla
$ aFieldInfo = DB :: $ oFooModelTableUser -> getFieldInfo ();Ejemplo de devolución
// 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',
],
]
Ejemplo de SQL usando $oPDO consulta .. buscar
/**
* @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',
))
Ejemplo de SQL usando $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
Puede registrar consultas SQL escuchando eventos.
Cree un archivo sql.php en la carpeta de eventos de su módulo mymvc y declare los enlaces de la siguiente manera.
/modules/{MODULE}/etc/event/sql.php
#-------------------------------------------------------------
# declare bindings
$ aEvent = [
' db.model.db.create.sql ' => array (
function ( MVC DataType DTArrayObject $ oDTArrayObject ) {
MVC Log:: write ( $ oDTArrayObject -> getDTKeyValueByKey ( ' sSql ' )-> get_sValue (), ' sql.log ' );
}
),
' db.model.db.insert.sql ' => array (
function ( MVC DataType DTArrayObject $ oDTArrayObject ) {
MVC Log:: write ( $ oDTArrayObject -> getDTKeyValueByKey ( ' sSql ' )-> get_sValue (), ' sql.log ' );
}
),
' db.model.db.retrieve.sql ' => array (
function ( MVC DataType DTArrayObject $ oDTArrayObject ) {
MVC Log:: write ( $ oDTArrayObject -> getDTKeyValueByKey ( ' sSql ' )-> get_sValue (), ' sql.log ' );
}
),
' db.model.db.update.sql ' => array (
function ( MVC DataType DTArrayObject $ oDTArrayObject ) {
MVC Log:: write ( $ oDTArrayObject -> getDTKeyValueByKey ( ' sSql ' )-> get_sValue (), ' sql.log ' );
}
),
' db.model.db.delete.sql ' => array (
function ( MVC DataType DTArrayObject $ oDTArrayObject ) {
MVC Log:: write ( $ oDTArrayObject -> getDTKeyValueByKey ( ' sSql ' )-> get_sValue (), ' sql.log ' );
}
),
' db.model.db.createTable.sql ' => array (
function ( MVC DataType DTArrayObject $ oDTArrayObject ) {
MVC Log:: write ( $ oDTArrayObject -> getDTKeyValueByKey ( ' sSql ' )-> get_sValue (), ' sql.log ' );
}
),
];
#-------------------------------------------------------------
# process: bind the declared ones
MVC Event:: processBindConfigStack ( $ aEvent );
