PHP framework I (database operation encapsulation)

Self-developed PHP framework I (database operation encapsulation) compiling of the PDO layer encapsulation and model-based methods of self-developed PHP Framework

If you are a sprayer and ask me how to use the wheel to make so much effort, then let's go and read my article. why should I write my own framework? All the code of the framework is displayed on the author's Github, and an instance of the inventory management information system is provided. for details about the Github account, refer to the author's introduction.

This is the first article of the self-writing framework. later, I plan to write routing articles, small components articles, and factory articles.

Database operations can be said to be the core of web applications. they directly decide what your program is doing and read the headlines. it is obvious that this is an article related to PDO encapsulation, once again, I would like to thank the Yii2.0 framework for its inspiration for the design model. Let's get started.

Encapsulation is divided into two classes: Connection class | Command class

First of all, as PHP, a request will correspond to a PHP thread. in this environment, it is not a waste for a thread to have multiple database connections. therefore, I use the Singleton mode to share database connections throughout the request lifecycle.

// Connection class private static $ _ instance; private function _ construct () {} private function _ clone () {} // Single instance public static function getinstance () {if (! (Self: $ _ instance instanceof self) {self: $ _ instance = new self ();} return self ::$ _ instance ;} // called when the class is recycled. close the database connection public function _ destruct () {$ this-> close ();}

With the Singleton mode, we need to perform actual database connection operations, configure the database through config, and use the unique syntax dsn of PDO:

return [    'dsn'      => 'mysql:host=127.0.0.1;dbname=dbname',    'user'     => 'root',    'password' => 'pass',    'charset'  => 'utf8',    'slaves' => [        [            'dsn'      => 'mysql:host=127.0.0.1;dbname=dbname',            'user'     => 'root',            'password' => 'pass',            'charset'  => 'utf8',        ],    ],];

The following are Connection Variables. this design stores all Connection instantiation on the slave server in a private variable for unified management.

// The database connection attribute public $ connect; private $ db; // database connection information // server information, private attribute private $ dsn; private $ user; private $ pass; private $ charset; private $ rightNowDb; // server name of the private attribute of the current server information private $ PDOSlaves; // database connection attribute after server instantiation

Obtain the database connection information.

• An error is thrown: One of the PDO: DB Parameter is empty!
• It is recorded within the system error day (in the errordb. log folder). The LogWrite class is a log record class encapsulated by the author and is called in a chain.

private function getInfo() {    $this->db = require (dirname(__FILE__).'/../../config/db.php');    if ($this->db['dsn'] && $this->db['user'] && $this->db['password']) {        $this->dsn        = $this->db['dsn'];        $this->user       = $this->db['user'];        $this->pass       = $this->db['password'];        $this->charset    = $this->db['charset']?$this->db['charset']:'utf8';        $this->rightNowDb = 'master';    } else {        $this->err('One of the PDO::DB Parameter is empty!');    }}private function err($err) {    $err = 'ErrInfo: '.$err;    LogWrite::getinstance()->IntoWhere('errordb')->Info($err)->execute();    throw new Exception($err);}

The PDO connection method. this is the PDO connection method. use new PDO for operations.

• Execute SQL statements using exec
• GetAttribute

private function nowConnect() {    try {        $connect = new PDO($this->dsn, $this->user, $this->pass);    } catch (PDOException $e) {        $this->err($e->getMessage());    }    if (!$connect) {        $this->err('PDO connect error');    }    $connect->exec('SET NAMES '.$this->charset);    $connect->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));    return $connect;}

Database shutdown and database activity methods (simple, directly post code without explanation)

public function getConnect() {    $this->getInfo();    if ($this->isActive()) {        return $this->connect;    } else {        $this->connect = $this->nowConnect();        return $this->connect;    }}//if there is activepublic function isActive() {    return $this->connect;}//close connectionpublic function close() {    if ($this->isActive()) {        $this->connect = null;    }}

The following figure shows the method for actually using the database connection. if the programmer uses the database connection method, these methods are the most commonly used, that is, getting the connection.

• Obtain the connection usage judgment. if it is not active, call the encapsulated PDO connection method and assign it to the connect attribute in the class. This attribute is returned.

public function getConnect() {    $this->getInfo();    if ($this->isActive()) {        return $this->connect;    } else {        $this->connect = $this->nowConnect();        return $this->connect;    }}

The following is the connection code from the server.

• From the server, take the value from the PDOSlaves attribute to determine the key-value pairs in PDOSlaves corresponding to different slave servers connected to the same master server.
• The setToSlaves method converts the private database connection attribute to the database to be operated.
• SetMaster private database connection attribute changed back to master server
• Everyone has seen return $ this;, which is the core of chain call!

public function getSlavesConnect($num) {    $this->setToSlaves($num);    $key = 'slave'.$num;    if ($this->PDOSlaves[$key]) {        return $this->PDOSlaves[$key];    } else {        $connect               = $this->nowConnect();        $this->PDOSlaves[$key] = $connect;        return $this->PDOSlaves[$key];    }}//Serval attributes change to slaver DataBasepublic function setToSlaves($num) {    if ($this->db['slaves'][$num]['dsn'] && $this->db['slaves'][$num]['user'] && $this->db['slaves'][$num]['password']) {        $this->dsn        = $this->db['slaves'][$num]['dsn'];        $this->user       = $this->db['slaves'][$num]['user'];        $this->pass       = $this->db['slaves'][$num]['password'];        $this->rightNowDb = 'slaves'.$num;    } else {        $this->err('slaves '.$num.':: missing info!');    }}public function setMaster() {    $this->getInfo();    return $this;}public function getRightNowDb() {    return $this->rightNowDb;}

Many people have doubts about this. I have introduced so many methods that they are connected to databases. it is of no use to connect to databases! I still do not do some operations on the database, and according to the current introduction, two classes should be added for unrelated connections. this is not very low! It is better to encapsulate it into one!

It's good to have this idea! I was thinking about it because I opened the Yii2.0 source code with the same idea. but then I saw his solution to this problem.

• There is a database operation factory method in the database connection.
• SQL availability

public function createCommand($sql = null) {    //first connect the db    $command = new Command([            'db'  => $this->getConnect(),            'sql' => $sql,        ]);    return $command;}public function createSlavesComm($num = 0, $sql = null) {    $command = new Command([            'db'  => $this->getSlavesConnect($num),            'sql' => $sql,        ]);    return $command;}

This is the code for all database connections. The following describes the Command class. The first is the constructor and some attributes. the database connection is obviously stored in the pdo attribute of the class.

// This save SQL wordprivate $ SQL; // pdo connectprivate $ pdo; // the pdo statementprivate $ pdoStmt; // the last db select is in hereprivate $ lastCommandDb; private $ dataType = PDO: FETCH_ASSOC; // The default attribute of data retrieved from the database // Connection. php to new a commandpublic function _ construct ($ arr) {$ this-> SQL = $ arr ['SQL']? $ Arr ['SQL']: ''; $ this-> pdo = $ arr ['DB'];}

Database SQL search is divided into two steps: preparation and execution. Using the PDO method, if an error occurs, an error is thrown, logs are recorded, and execution is performed without preparation, the system will throw new PDOException ('pdo is Fail to use execute before prepare! '); Error

//Must handleprivate function prepare() {    //if there have stmt    if ($this->pdoStmt) {        $this->lastCommandDb = $this->pdoStmt;    }    $this->pdoStmt = $this->pdo->prepare($this->sql);}//execute it and returnprivate function execute($method) {    if ($this->pdoStmt) {        $pdoStmt = $this->pdoStmt;        $pdoStmt->execute();        $res = $pdoStmt->$method($this->dataType);        if (!$res) {            $msg = 'The result is empty, The sql word is :: '.$this->sql;            LogWrite::getinstance()->IntoWhere('errordb')->Info($msg)->execute();            return false;        }        return $res;    } else {        throw new PDOException('PDO is Fail to use execute before prepare!');    }}

The following describes transactions. as a database operation, transactions are required. if there are no transactions, fatal errors often occur. suddenly speaking, transactions may be abrupt, the following is a simple example:

Different people in the bank will correspond to different accounts, each of which is A database record. the following simulates A transfer business: A transfers 100 yuan to B.

When A transfers money from account A to Account B, the money in account A minus 100 is successful. when 100 yuan is added to the account in account B, for some reason (execution time timeout and internal system errors due to deadlocks), the transfer transaction actually fails because the account B does not have more than 100 yuan, however, once there is no transaction constraint, there is only one commit error, and there is no record or rollback, it is actually fatal for a transfer business !!!!

On this basis, the concept of transactions was born. PDO also has a complete set of solutions for it. then we will encapsulate it below.

//transction handleprivate function transction() {    try {        $this->pdo->beginTransaction();        $res = $this->pdo->exec($this->sql);        if ($this->pdo->errorInfo()[0] != '00000') {            throw new PDOException('DB Error::Fail to change the database!!  The sql is: '.$this->sql.' The Error is :: '.$this->pdo->errorInfo()[2]);        }        $this->pdo->commit();        return true;    } catch (PDOException $e) {        $this->pdo->rollback();        LogWrite::getinstance()->IntoWhere('errordb')->Info($e)->execute();        return false;    }}//change it latelyprivate function transctions(array $sqlArr = array()) {    try {        $this->pdo->beginTransaction();        foreach ($sqlArr as $value) {            $res = $this->pdo->exec($value);            print_r($this->pdo->errorInfo());            if ($this->pdo->errorInfo()[0] != '00000') {                throw new PDOException('DB Error::Fail to change the database!!  The sql is: '.$value.' The Error is :: '.$this->pdo->errorInfo()[2]);            }        }        $this->pdo->commit();        return true;    } catch (PDOException $e) {        $this->pdo->rollback();        LogWrite::getinstance()->IntoWhere('errordb')->Info($e)->execute();        return false;    }}

The above is the encapsulation of some basic methods, so how should we use these methods? of course, it's just adding, deleting, modifying, and querying!

public function queryAll($fetchMode = null) {    return $this->queryInit('fetchAll', $fetchMode);}public function queryOne($fetchMode = null) {    return $this->queryInit('fetch', $fetchMode);}//insert into databasepublic function insert($table, $arr) {    $this->sql = Merj::sql()->insert($table)->value($arr)->sqlVal();    return $this->transction();}//insert serval databasepublic function insertSomeVal($table, array $key, array $arr) {    $this->sql = Merj::sql()->insert($table)->servalValue($key, $arr)->sqlVal();    return $this->transction();}//update the databasepublic function update($table, $arr, $where) {    $this->sql = Merj::sql()->update($table)->set($arr)->where($where)->sqlVal();    return $this->transction();}public function updateTrans(array $sqlArr = array()) {    return $this->transctions($sqlArr);}//delete one recordpublic function delete($table, $whereArr) {    $this->sql = Merj::sql()->delete($table)->where($whereArr)->sqlVal();    return $this->transction();}private function queryInit($method, $fetchMode = null) {    if ($fetchMode) {        $this->dataType = $fetchMode;    }    if ($this->sql && $this->pdo) {        $this->prepare();        $result = $this->execute($method);        return $result?$result:'';    } else {        $err = 'Sql or PDO is empty; The sql is '.$this->sql;        LogWrite::getinstance()->IntoWhere('errordb')->Info($this->sql)->execute();        throw new PDOException($err);        return false;    }}

There are many methods, which are actually regular.

• This factory method Merj: SQL () is a chained method that instantiates the classes that splice SQL statements.

  public static function sql() {  return new QueryBuilder();}

  That's right. The framework applies the concatenation method of chained calls of SQL statements.
• Query method call queryInit method
• Add, delete, modify, and use the transction method just mentioned.
• If you need to use the complete method, just like below, is it very convenient!

  $ Connect = Connection: getinstance (); $ connect-> createCommand ($ SQL)-> queryOne (); $ connect-> createCommand ($ SQL)-> queryAll (); $ connect: db ()-> createCommand ()-> insert ('tablename', ['Val key1' => 'val1 ', 'Val key2' => 'val2',]); Merj: db ()-> createSlavesComm (0, 'select * FROM content')-> queryAll (); // operate the database on the slave server

The model class method section of the database is displayed. all models inherit this class:

/*** Return a value in a record * @ param the value to be retrieved * @ param the value corresponding to the primary key * @ return returns a value **/public function findOnlyOne ($ target, $ idVal) {$ SQL = Merj: SQL ()-> select ($ target)-> from ($ this-> tableName) -> where ([$ this-> primKey => $ idVal,])-> sqlVal (); $ rows = Merj: db ()-> createCommand ($ SQL) -> queryOne (); if ($ rows) {return $ rows [$ target];} else {return false ;}} /*** return a record * @ param primary key attribute * @ param primary key value * @ return returns this record **/public function findOneRecord ($ userIdVal) {$ SQL = Merj: SQL ()-> select ()-> from ($ this-> tableName) -> where ([$ this-> primKey => $ userIdVal,])-> sqlVal (); $ rows = Merj: db ()-> createCommand ($ SQL) -> queryOne (); if ($ rows) {return $ rows;} else {return false ;}} /*** use an SQL statement to find * @ param SQL words * @ return results **/public function findBySql ($ SQL) {return Merj: db () -> createCommand ($ SQL)-> queryAll ();}/*** @ param Insert info * @ return success or not **/public function insertOne ($ arr) {return Merj: db ()-> createCommand ()-> insert ($ this-> tableName, $ arr );} /*** @ param Insert infos * @ return success or not **/public function insertNum (array $ key = array (), array $ arr = array ()) {return Merj: db ()-> createCommand ()-> insertSomeVal ($ this-> tableName, $ key, $ arr );} /*** UPDATE a record * @ param * @ return success or not **/public function updateOneRec ($ arrUpDate, $ idVal) {return Merj: db () -> createCommand ()-> update ($ this-> tableName, $ arrUpDate, [$ this-> primKey => $ idVal,]);} /*** UPDATE multiple SQL statements * @ param SQL array * @ return success or not **/public function updateTrans ($ sqlArr) {return Merj: db () -> createCommand ()-> updateTrans ($ sqlArr );} /*** delete a record * @ param where $ arr * @ return success or not **/public function deleteOne ($ arr) {return Merj: db () -> createCommand ()-> delete ($ this-> tableName, $ arr );} /*** object to array * @ param object * @ return array **/public function obj_arr ($ obj) {if (is_object ($ obj )) {$ array = (array) $ obj;} if (is_array ($ obj) {foreach ($ obj as $ key => $ value) {$ array [$ key] = $ this-> obj_arr ($ value) ;}return $ array;} public function jsonUtf8Out ($ arr) {foreach ($ arr as $ key => $ value) {if (is_array ($ value )) {$ arr [$ key] = $ this-> jsonUtf8Out ($ value);} else {$ arr [$ key] = urlencode ($ value );}} return $ arr ;}

The last two methods are two recursive methods.

• Object into array
• The urlencode of the element in the array is translated into Chinese by the json_encode method of json. Therefore, you need to enable urlencode of all elements in the array before urldecode.

Okay! After talking about this, I am going to have dinner! There will be another day of college entrance examination tomorrow. I wish you all the best!