ORM on php for MySQL, reality (part one)
After a long search for a library of interest to me in php to communicate with MySQL, I sat down and wrote my most suitable for use in projects. This topic will take a small series of articles that will be useful not only for professional web application developers, but also for beginners. It should be noted that the ORM library presented below, which, by the way, I called kitty , is the result of long suffering and is not a mandatory library of all projects.
The library in my vision should have two files (at least in the initial stages):
Let's start with the last one. The image file of the database must contain classes that are similar in name to the tables and contain fields in accordance with the columns of the tables. Those. if we have a authors table with the fields idauthor , Name , Year (ID, name, Years of life), then the class will look like this:
The ID must follow first.
Usually, the database image class contains all the tables that kitty automatically generates, but more on that later (you will not fit everything in this article), and it connects after kitty. Now let's get down to the most interesting, to our library.
')
The kitty class is an abstract class and has (in my vision) two key properties:
An instance of the $ db class stores a connection to the database using the improved mysqli class.
An instance of the $ stack class stores the stack of queries and the results of these queries using the SplStack class.
At this property ended, all succinctly and simply, now let's move on to the sweet.
In this article, we will look at several main class methods in order to understand the basic idea of ​​building a library. In OOP, there are static methods that are declared in memory once and do not change, and are not static, appearing in memory when an instance is created and living with it while the instance lives.
The static methods of this library are generalized, not necessary for database instances. Non-static methods cover the range of one or more database instances.
The key static method for connecting to a database is setup:
As a parameter, we pass an instance of the mysqli class and the encoding, which is by default utf8 . During initialization, an instance of MySQLi and a stack are logged. The result of the response is a query, i.e. check on the correctness of the connection. The line kitty :: setup (new mysqli) is the only library setting.
The encoding is set by the setEncoding request. The method code is presented below:
In the event of an error, put the request and the error on the stack, and accordingly return false .
The error receiving function is very concise:
We return the error text (error) and the error code (errno).
This feature is implemented by the mysqli_real_escape_string function, but it is long and takes two parameters. Let's replace, for convenience, this function with the one presented below:
The function accepts a string and returns escaped for the SQL query. With it, we forget about SQL injection , which is an important fact!
To select the fields of the table, and more specifically the properties of the class of the table, we use php tools to work with classes.
The function code is shown below:
The function takes all the properties and filters them. If a property is an object, and it also chooses stack and db, then it is not included. The output array with the fields of the table. When calling authors :: _ getVars (); the function will return the array array (“idauthor”, “Name”, “Year”) .
Data sampling is a delicate subject for ORM libraries and the question is how to get the data and how to present it.
In the current article we will consider only one variant of the request.
The method is static and selects one instance from the database by identifier (findID).
The function code is shown below:
The code is described in detail by comments and does not require additional description.
You can get an instance as follows:
Enough of static methods, let's move on to non-static ones. Methods that relate to a specific instance.
Above, we selected an instance of the author with identifier 2. If the request succeeds, then we will have an instance of the class:
Changing parameters is very simple, but how to save?
Save as easy. Below is the function code to save:
The code is equipped with comments and does not require additional description. To change the name in accordance with the previous example, you must run the following code:
The Save function has a great RenderField function. The function is very important, it is static and is responsible for the correctness of the query construction, its code is presented below:
The function determines when the text should be in single quotes, and when without.
And what if you need to add an instance to the database. You can create it as an instance of the class by running the code:
The add function code is shown below:
And finally, the removal. In php, there is no delete function and we will not break the tradition, so we will call the method Remove ();
To remove the author's entry from the previous examples, you must run the code:
Select an instance and delete. Everything is very simple and concise! The function code for deletion is shown below:
This ORM library is not a monster of the modern world, especially since I just started writing it, but it is quite suitable for use in small projects. The following will discuss the automatic generation of a database model.
The library in my vision should have two files (at least in the initial stages):
- library file - kitty.php;
- The database model object image file is modeldb.php.
Let's start with the last one. The image file of the database must contain classes that are similar in name to the tables and contain fields in accordance with the columns of the tables. Those. if we have a authors table with the fields idauthor , Name , Year (ID, name, Years of life), then the class will look like this:
class authors extends kitty { public $idauthor; public $Name; public $Year; } The ID must follow first.
Usually, the database image class contains all the tables that kitty automatically generates, but more on that later (you will not fit everything in this article), and it connects after kitty. Now let's get down to the most interesting, to our library.
')
Class properties kitty
The kitty class is an abstract class and has (in my vision) two key properties:
private static $db; // private static $stack; // An instance of the $ db class stores a connection to the database using the improved mysqli class.
An instance of the $ stack class stores the stack of queries and the results of these queries using the SplStack class.
At this property ended, all succinctly and simply, now let's move on to the sweet.
Kitty class methods
In this article, we will look at several main class methods in order to understand the basic idea of ​​building a library. In OOP, there are static methods that are declared in memory once and do not change, and are not static, appearing in memory when an instance is created and living with it while the instance lives.
The static methods of this library are generalized, not necessary for database instances. Non-static methods cover the range of one or more database instances.
Static methods
The key static method for connecting to a database is setup:
static public function setup(mysqli $dbi,$enc = "utf8"){ if (is_object($dbi)){ self::$db = $dbi; self::$stack = new SplStack(); // return self::setEncoding($enc); // }else{ throw new Exception(" $dbi mysqli", 1); return false; } } As a parameter, we pass an instance of the mysqli class and the encoding, which is by default utf8 . During initialization, an instance of MySQLi and a stack are logged. The result of the response is a query, i.e. check on the correctness of the connection. The line kitty :: setup (new mysqli) is the only library setting.
The encoding is set by the setEncoding request. The method code is presented below:
static function setEncoding($enc){ $result = self::$db->query("SET NAMES '$enc'"); // self::$stack->push("SET NAMES '$enc' [".($result ? "TRUE" : self::getError())."]"); return $result ? true : false; // } In the event of an error, put the request and the error on the stack, and accordingly return false .
The error receiving function is very concise:
static function getError(){ return self::$db->error." [".self::$db->errno."]"; } We return the error text (error) and the error code (errno).
Each self-respecting ORM library must contain screening (Ph.D., Assoc. Prof. Kovzhenkin VS)
This feature is implemented by the mysqli_real_escape_string function, but it is long and takes two parameters. Let's replace, for convenience, this function with the one presented below:
private static function escape($string) { return mysqli_real_escape_string(self::$db,$string); } The function accepts a string and returns escaped for the SQL query. With it, we forget about SQL injection , which is an important fact!
To select the fields of the table, and more specifically the properties of the class of the table, we use php tools to work with classes.
The function code is shown below:
private static function _getVars(){ return array_filter(get_class_vars(get_called_class()),function($elem){ if (!is_object($elem)) return true; }); } The function takes all the properties and filters them. If a property is an object, and it also chooses stack and db, then it is not included. The output array with the fields of the table. When calling authors :: _ getVars (); the function will return the array array (“idauthor”, “Name”, “Year”) .
Data retrieval
Data sampling is a delicate subject for ORM libraries and the question is how to get the data and how to present it.
In the current article we will consider only one variant of the request.
The method is static and selects one instance from the database by identifier (findID).
The function code is shown below:
static function findID($id){ if (is_numeric($id)){ // , $query = "SELECT * FROM `".get_called_class()."` WHERE `".key(self::_getVars())."` = $id LIMIT 1"; $result = self::$db->query($query); // self::$stack->push($query." [".$result->num_rows."]"); // if ($result->num_rows == 1){ // $row = $result->fetch_object(); // $cName = get_called_class(); // $rClass = new $cName(); // foreach ($row as $key => $value) $rClass->$key = $value; // return $rClass; // } else return false; // , } else return false; // } The code is described in detail by comments and does not require additional description.
You can get an instance as follows:
$auth = authors::findID(2); if ($auth){ // }else{ // } Non static methods
Enough of static methods, let's move on to non-static ones. Methods that relate to a specific instance.
Above, we selected an instance of the author with identifier 2. If the request succeeds, then we will have an instance of the class:
$auth->idauthor = 2; $auth->Name = " "; $auth->Year = "1818—1883"; Changing parameters is very simple, but how to save?
Save as easy. Below is the function code to save:
public function Save(){ // - UPDATE $id = key(self::_getVars()); // if (!isset($this->$id) || empty($this->$id)) return $this->Add(); // , $query = "UPDATE `".get_called_class()."` SET "; // $columns = self::_getVars(); // $Update = array(); // foreach ($columns as $k => $v) { // if ($id != $k) // $Update[] = "`".$k."` = ".self::RenderField($this->$k); // } $query .= join(", ",$Update); // $query .= " WHERE `$id` = ".self::escape($this->$id)." LIMIT 1"; // $result = self::$db->query($query); self::$stack->push($query." [".($result ? "TRUE" : self::getError())."]"); // return ($result) ? true : false; // } The code is equipped with comments and does not require additional description. To change the name in accordance with the previous example, you must run the following code:
$auth->Name = " "; echo $auth->Save() ? "" : " (("; The Save function has a great RenderField function. The function is very important, it is static and is responsible for the correctness of the query construction, its code is presented below:
private static function RenderField($field){ $r = ""; // switch (gettype($field)) { // case "integer": case "float": // int float $r = $field; break; case "NULL": $r = "NULL"; break; // NULL case "boolean": $r = ($field) ? "true" : "false"; break; // boolean case "string": // $p_function = "/^[a-zA-Z_]+\((.)*\)/"; // preg_match($p_function, $field,$mathes); // if (isset($mathes[0])){ // , $p_value = "/\((.+)\)/"; // preg_match($p_value, $field,$mValue); // if (isset($mValue[0]) && !empty($mValue[0])){ // $pv = trim($mValue[0],"()"); // $pv = "'".self::escape($pv)."'"; // $r = preg_replace($p_value, "($pv)" , $field); // } else $r = $field; // } else $r = "'".self::escape($field)."'"; // break; default: $r = "'".self::escape($field)."'"; break; // } return $r; // } The function determines when the text should be in single quotes, and when without.
And what if you need to add an instance to the database. You can create it as an instance of the class by running the code:
$auth = new authors(); $auth->Name = " "; $auth->Year = "1918-1983"; $auth->Add(); The add function code is shown below:
public function Add(){ // - INSERT $query = "INSERT INTO `".get_called_class()."` ("; // $columns = self::_getVars(); // $q_column = array(); // $q_data = array(); // foreach ($columns as $k => $v){ // $q_column[] = "`".$k."`"; // $q_data[] = self::RenderField($this->$k); // } $query .= join(", ",$q_column).") VALUES ("; // $query .= join(", ",$q_data).")"; // $result = self::$db->query($query); // $insert_id = self::$db->insert_id; // self::$stack->push($query." [".($result ? $insert_id : self::getError())."]"); // return ($result) ? $insert_id : false; // } Deleting an object
And finally, the removal. In php, there is no delete function and we will not break the tradition, so we will call the method Remove ();
To remove the author's entry from the previous examples, you must run the code:
$auth = authors::findID(2); $auth->Remove(); Select an instance and delete. Everything is very simple and concise! The function code for deletion is shown below:
public function Remove(){ // - DELETE $id = key(self::_getVars()); // if (!empty($this->$id)){ // $qDel = "DELETE FROM `".get_called_class()."` WHERE `$id` = ".$this->$id." LIMIT 1"; $rDel = self::$db->query($qDel); // self::$stack->push($qDel." [".($rDel ? "TRUE" : self::getError())."]"); // return $rDel ? true:false; // } else return false; // } This ORM library is not a monster of the modern world, especially since I just started writing it, but it is quite suitable for use in small projects. The following will discuss the automatic generation of a database model.
Source: https://habr.com/ru/post/305786/
All Articles