myMVC_module_DB
3.4.0
pdogit clone --branch 3.x https://github.com/gueff/myMVC.git myMVC_3.xNa pasta de configuração do seu módulo principal, crie sua configuração de banco de dados. (@see https://mymvc.ueffing.net/3.3.x/configuration#modules-config-folder)
Exemplo de configuração do banco de dados para develop ambientes
//-------------------------------------------------------------------------------------
// 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() , o que significa que armazenamos nossos segredos no arquivo /.env .Classe PHP como uma representação da tabela de banco de dados
Arquivo: 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 , conforme declarado em propriedade $aFieldid dos campos da tabela, stampChange e stampCreate são adicionados automaticamenteDataType/DTFooModelTableUser.phpCriando uma tabela e adicionando uma chave estrangeira
Arquivo: 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 , conforme declarado em propriedade $aFieldid dos campos da tabela, stampChange e stampCreate são adicionados automaticamenteid_FooModelTableGroup - Ponto para a tabela FooModelTableGroup - é adicionada pelo método setForeignKey()DataType/DTFooModelTableUser.php Arquivo: 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 ;
} Crie um arquivo db.php (você pode nomeá -lo como quiser) na pasta de eventos do seu módulo MyMVC e declarar as ligações da seguinte maneira.
file /modules/ { /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 ()
);
}
}
),
]); Na sua classe principal de controladores , basta criar uma nova instância da sua classe DBINIT. Um bom lugar é o método __construct() .
namespace Foo Controller ;
use Foo Model DB ;
public function __construct ()
{
DB :: init ();
}Depois disso, você pode acessar sua tela de mesa de todos os lugares - mesmo a partir de modelos de front -end:
Uso
DB :: $ oFooModelTableUser ->. . .<method> . . . create (inserir) Portanto, um objeto de seu tipo de dados relacionado deve ser instaciado e fornecido ao método create . Aqui, por exemplo, com o Datatype "dtfoomodeltableUser" para módulos/foo/model/db/tabela ":
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 pede um Tupel específico e retorna o objeto Datatype de acordo com a tabela solicitada.
retrieveTupel - Identificado por id
/** @var FooDataTypeDTFooModelTableUser $oDTFooModelTableUser */
$ oDTFooModelTableUser = DB :: $ oFooModelTableUser -> retrieveTupel (
DTFooModelTableUser:: create ()
-> set_id ( 2 )
) retrieve retorna uma matriz de objetos de tipo de dados de acordo com a tabela solicitada.
retrieve : Obtenha todos os conjuntos de dados
/** @var FooDataTypeDTFooModelTableUser[] $aDTFooModelTableUser */
$ aDTFooModelTableUser = DB :: $ oFooModelTableUser -> retrieveTupel (); retrieve : Obtenha conjuntos de dados 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 : Obtenha conjuntos de dados com ordem de classificação
/** @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 : Obtenha os 30 primeiros conjuntos de dados (limite 0,30)
/** @var FooDataTypeDTFooModelTableUser[] $aDTFooModelTableUser */
$ aDTFooModelTableUser = DB :: $ oFooModelTableUser -> retrieve (
null ,
DTArrayObject:: create ()
-> add_aKeyValue (
DTKeyValue:: create ()
-> set_sValue ( ' LIMIT 0,30 ' )
)
) updateTupel : Atualize um 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 do objeto será atualizado. update : Atualize todos os Tupel que são afetados pela 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 : Exclua este tuel específico - identificado por id
/** @var boolean $bSuccess */
$ bSuccess = DB :: $ oFooModelTableUser -> deleteTupel (
DTFooModelTableUser:: create ()
-> set_id ( 2 )
) delete : Exclua todos os tupel que são afetados pela cláusula onde
$ 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 ();Retorna a matriz com informações dos campos de tabela
$ aFieldInfo = DB :: $ oFooModelTableUser -> getFieldInfo ();Exemplo de retorno
// 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',
],
]
Exemplo SQL usando $oPDO consulta .. busca
/**
* @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',
))
Exemplo SQL usando $oPDO consulta .. 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
Você pode registrar consultas SQL ouvindo eventos.
Crie um arquivo sql.php na pasta de eventos do seu módulo MyMVC e declare as ligações da seguinte forma.
/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 );
