myMVC_module_DB

mymvc_module_db

• 1。要求
• 2。存储库
• 3。创造
  • 3.1。创建DB Config
  • 3.2。创建一个具体表类
  • 3.3。创建用于每个DB访问的DBINIT类
  • 3.4。让生成数据类型类的OpenAPI YAML模式文件
• 4。用法
  • 4.1。创造
  • 4.2。取回
  • 4.3。更新
  • 4.4。删除
  • 4.5。数数
  • 4.6。校验和
  • 4.7。 getfieldinfo
  • 4.8。 SQL
• 5。事件
  • 5.1。记录SQL

---

1。要求

• Linux
• php> = 8
  • pdo扩展
• myMVC 3.x
  • git clone --branch 3.x https://github.com/gueff/myMVC.git myMVC_3.x
  • 文档：https：//mymvc.ueffing.net/
  • github：https：//github.com/gueff/mymvc/tree/3.3.x

---

2。存储库

• https://github.com/gueff/mymvc_module_db

---

3。创造

3.1。创建DB Config

在主模块的配置文件夹中，创建您的数据库配置。 （@See https://mymvc.ueffing.net/3.3.x/configuration#modules-config-folder）

develop环境的DB配置示例

 //-------------------------------------------------------------------------------------
// 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() ，这意味着我们将秘密存储在/.env文件中。

3.2。创建一个具体表类

php类作为DB表的表示

文件： 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
        );
    }
}

• 创建桌子FooModelTableUser
  • 表有email中的几个字段...如属性$aField中所声明的lastname
    • ？自动添加表字段id ， stampChange和stampCreate
    • 不要手动添加这些字段
• 生成数据类型类DataType/DTFooModelTableUser.php

---

创建桌子并添加外键

文件： 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 ' )
        );
    }
}

• 创建桌子FooModelTableUser
  • 表有email中的几个字段...如属性$aField中所声明的lastname
    • ？自动添加表字段id ， stampChange和stampCreate
    • 不要手动添加这些字段
• 外键id_FooModelTableGroup点到表FooModelTableGroup通过Method setForeignKey()添加
• 生成数据类型类DataType/DTFooModelTableUser.php

---

3.3。创建用于每个DB访问的DBINIT类

文件： 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 ;
}

---

3.4。让生成数据类型类的OpenAPI YAML模式文件

在MYMVC模块的事件文件夹中创建一个文件db.php （您可以按照自己的意愿命名），并如下声明绑定。

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 ()
                );
            }
        }
    ),
]);

---

4。用法

在您的主控制器类中，只需创建DBINIT类的新实例即可。一个好地方是__construct()方法。

 namespace Foo  Controller ;

use Foo  Model  DB ;

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

之后，您可以从任何地方访问桌面 - 即使是从前端模板中：

用法

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

4.1。创造

因此， create （插入）必须将其相关数据类型的对象分配并授予该方法create对象。在这里，例如，使用数据类型“ dtfoomodeltableuser”到tableclass“模块/foo/model/db/tableeser”：

 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 ' ))
);

4.2。取回

retrieveTupel要求特定的tupel，并根据请求的表返回数据类型对象。

retrieveTupel由id标识

 /** @var FooDataTypeDTFooModelTableUser $oDTFooModelTableUser */
$ oDTFooModelTableUser = DB :: $ oFooModelTableUser -> retrieveTupel (
    DTFooModelTableUser:: create ()
        -> set_id ( 2 )
)

• 获取ID = 2的用户对象

根据请求的表retrieve数据类型对象的数组。

retrieve ：获取所有数据集

 /** @var FooDataTypeDTFooModelTableUser[] $aDTFooModelTableUser */
$ aDTFooModelTableUser = DB :: $ oFooModelTableUser -> retrieveTupel ();

retrieve ：获取特定数据集

 /** @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 ：获取带排序订单的数据集

 /** @var FooDataTypeDTFooModelTableUser[] $aDTFooModelTableUser */
$ aDTFooModelTableUser = DB :: $ oFooModelTableUser -> retrieve (
    DTArrayObject:: create ()
        -> add_aKeyValue (
            DTKeyValue:: create ()
                -> set_sKey ( ' email ' )
                -> set_mOptional1 ( ' LIKE ' )
                -> set_sValue ( ' [e-mail]% ' )
        ),
    DTArrayObject:: create ()
        -> add_aKeyValue (
            DTKeyValue:: create ()
                -> set_sValue ( ' ORDER BY id ASC ' )
        )
);

retrieve ：获取第一个30个数据集（限制0,30）

 /** @var FooDataTypeDTFooModelTableUser[] $aDTFooModelTableUser */
$ aDTFooModelTableUser = DB :: $ oFooModelTableUser -> retrieve (
    null ,
    DTArrayObject:: create ()
        -> add_aKeyValue (
            DTKeyValue:: create ()
                -> set_sValue ( ' LIMIT 0,30 ' )
        )
)

4.3。更新

updateTupel ：更新特定的Tupel-由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的等效数据集Tupel将被更新。

update ：更新所有受White子句影响的TUPEL

 /** @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 ' )
        )
);

4.4。删除

deleteTupel ：删除此特定的Tupel-由id标识

 /** @var boolean $bSuccess */
$ bSuccess = DB :: $ oFooModelTableUser -> deleteTupel (
    DTFooModelTableUser:: create ()
        -> set_id ( 2 )
)

delete ：删除所有受White子句影响的TUPEL

 $ bSuccess = DB :: $ oFooModelTableUser -> delete (
    // where
    DTArrayObject:: create ()
        -> add_aKeyValue (
            DTKeyValue:: create ()
                -> set_sKey ( ' stampCreate ' )
                -> set_mOptional1 ( ' < ' )
                -> set_sValue ( ' 2023-06-19 00:00:00 ' )
        )
);

4.5。数数

 // 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 ' )
    )
);

4.6。校验和

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

4.7。 getfieldinfo

带有表字段信息的返回数组

 $ aFieldInfo = DB :: $ oFooModelTableUser -> getFieldInfo ();

示例返回

 // 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',
    ],
]

4.8。 SQL

SQL示例使用$oPDO查询..获取

 /**
 * @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示例使用$oPDO查询.. 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',
    )],
]

---

5。事件

 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

5.1。记录SQL

您可以通过收听事件来记录SQL查询。

在MYMVC模块的事件文件夹中创建一个文件sql.php ，并按照以下方式声明绑定。

/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 );