myMVC_module_DB
3.4.0
pdogit clone --branch 3.x https://github.com/gueff/myMVC.git myMVC_3.xDi folder konfigurasi modul utama Anda, buat konfigurasi db Anda. (@see https://mymvc.ueffing.net/3.3.x/configuration#modules-config-folder)
Contoh konfigurasi db untuk lingkungan 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() , yang berarti kami menyimpan rahasia kami di file /.env .Kelas PHP sebagai representasi dari tabel DB
File: 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 seperti yang dinyatakan dalam properti $aFieldid Bidang Tabel, stampChange dan stampCreate ditambahkan secara otomatisDataType/DTFooModelTableUser.phpMembuat tabel dan menambahkan kunci asing
File: 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 seperti yang dinyatakan dalam properti $aFieldid Bidang Tabel, stampChange dan stampCreate ditambahkan secara otomatisid_FooModelTableGroup -Pointing ke tabel FooModelTableGroup - ditambahkan dengan metode setForeignKey()DataType/DTFooModelTableUser.php File: 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 ;
} Buat file db.php (Anda dapat menyebutkannya sesuka Anda) di folder acara modul MyMVC Anda dan menyatakan binding sebagai berikut.
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 ()
);
}
}
),
]); Di kelas pengontrol utama Anda, cukup buat instanciasi baru kelas DBINIT Anda. Tempat yang baik adalah metode __construct() .
namespace Foo Controller ;
use Foo Model DB ;
public function __construct ()
{
DB :: init ();
}Setelah itu Anda dapat mengakses tableClass Anda dari mana -mana - bahkan dari templat frontend:
Penggunaan
DB :: $ oFooModelTableUser ->. . .<method> . . . create (masukkan) Oleh karena itu objek dari tipe data terkait harus dipasang dan diberikan kepada metode create . Di sini misalnya dengan datatype "dtfoomodeltableUser" ke 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 meminta tupel tertentu dan mengembalikan objek tipe data sesuai dengan tabel yang diminta.
retrieveTupel - Diidentifikasi oleh id
/** @var FooDataTypeDTFooModelTableUser $oDTFooModelTableUser */
$ oDTFooModelTableUser = DB :: $ oFooModelTableUser -> retrieveTupel (
DTFooModelTableUser:: create ()
-> set_id ( 2 )
) retrieve mengembalikan array objek tipe data sesuai dengan tabel yang diminta.
retrieve : Dapatkan semua set data
/** @var FooDataTypeDTFooModelTableUser[] $aDTFooModelTableUser */
$ aDTFooModelTableUser = DB :: $ oFooModelTableUser -> retrieveTupel (); retrieve : Dapatkan set data tertentu
/** @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 : Dapatkan Dataset dengan Pesanan Sortir
/** @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 : Dapatkan 30 Dataset Pertama (Batasi 0,30)
/** @var FooDataTypeDTFooModelTableUser[] $aDTFooModelTableUser */
$ aDTFooModelTableUser = DB :: $ oFooModelTableUser -> retrieve (
null ,
DTArrayObject:: create ()
-> add_aKeyValue (
DTKeyValue:: create ()
-> set_sValue ( ' LIMIT 0,30 ' )
)
) updateTupel : Perbarui tupel tertentu - diidentifikasi oleh 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 objek akan diperbarui. update : Perbarui semua tupel yang dipengaruhi oleh klausa 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 : Hapus tupel spesifik ini - diidentifikasi oleh id
/** @var boolean $bSuccess */
$ bSuccess = DB :: $ oFooModelTableUser -> deleteTupel (
DTFooModelTableUser:: create ()
-> set_id ( 2 )
) delete : Hapus semua tupel yang dipengaruhi oleh klausa di mana
$ 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 ();Mengembalikan array dengan info bidang meja
$ aFieldInfo = DB :: $ oFooModelTableUser -> getFieldInfo ();Contoh kembali
// 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',
],
]
Contoh SQL Menggunakan $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',
))
Contoh SQL Menggunakan $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
Anda dapat mencatat kueri SQL dengan mendengarkan acara.
Buat file sql.php di folder acara modul MyMVC Anda dan nyatakan binding sebagai berikut.
/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 );
