MysqlEntity.class.php 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485
  1. <?php
  2. require_once('constant.php');
  3. require_once('MysqlConnector.class.php');
  4. /*
  5. @nom: MysqlEntity
  6. @auteur: Valentin CARRUESCO (valentincarruesco@yahoo.fr)
  7. @date de création: 16/04/2012 02:34:15
  8. @description: Classe parent de tous les modèles (classe entitées) liées a la base de donnée,
  9. cette classe est configuré pour agir avec une base MySQL, mais il est possible de redefinir ses codes SQL pour l'adapter à un autre SGBD sans affecter
  10. le reste du code du projet.
  11. */
  12. class MysqlEntity
  13. {
  14. protected $dbconnector = false;
  15. private $debug = false;
  16. private $debugAllQuery = false;
  17. function sgbdType($type){
  18. $return = false;
  19. switch($type){
  20. case 'string':
  21. case 'timestamp':
  22. $return = 'VARCHAR(225) CHARACTER SET utf8 COLLATE utf8_general_ci';
  23. break;
  24. case 'longstring':
  25. $return = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
  26. break;
  27. case 'extralongstring':
  28. $return = 'MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
  29. break;
  30. case 'key':
  31. $return = 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY';
  32. break;
  33. case 'object':
  34. case 'integer':
  35. $return = 'INT(11)';
  36. break;
  37. case 'boolean':
  38. $return = 'INT(1)';
  39. break;
  40. default;
  41. $return = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
  42. break;
  43. }
  44. return $return ;
  45. }
  46. /**
  47. * Protège une variable pour MySQL
  48. */
  49. protected function secure($value, $field){
  50. $type = false;
  51. // ce champ n'existe pas : on le considère comme une chaîne de caractères
  52. if (isset($this->object_fields[$field]))
  53. $type = $this->object_fields[$field];
  54. $return = false;
  55. switch($type){
  56. case 'key':
  57. case 'object':
  58. case 'integer':
  59. case 'boolean':
  60. $return = intval($value);
  61. break;
  62. case 'string':
  63. case 'timestamp':
  64. case 'longstring':
  65. default;
  66. $return = $this->dbconnector->connection->real_escape_string((string)$value);
  67. break;
  68. }
  69. return $return ;
  70. }
  71. public function __construct(){
  72. $this->dbconnector = MysqlConnector::getInstance();
  73. }
  74. public function __destruct(){
  75. }
  76. // GESTION SQL
  77. /**
  78. * Methode de suppression de l'entité
  79. * @author Valentin CARRUESCO
  80. * @category manipulation SQL
  81. * @return Aucun retour
  82. */
  83. public function destroy()
  84. {
  85. $query = 'DROP TABLE IF EXISTS `'.MYSQL_PREFIX.$this->TABLE_NAME.'`;';
  86. if($this->debug)echo '<hr>'.get_class($this).' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.$this->dbconnector->connection->error;
  87. $myQuery = $this->customQuery($query);
  88. }
  89. /**
  90. * Methode de nettoyage de l'entité
  91. * @author Valentin CARRUESCO
  92. * @category manipulation SQL
  93. * @return Aucun retour
  94. */
  95. public function truncate()
  96. {
  97. $query = 'TRUNCATE TABLE `'.MYSQL_PREFIX.$this->TABLE_NAME.'`;';
  98. if($this->debug)echo '<hr>'.get_class($this).' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.$this->dbconnector->connection->error;
  99. $myQuery = $this->customQuery($query);
  100. }
  101. /**
  102. * Methode de creation de l'entité
  103. * @author Valentin CARRUESCO
  104. * @category manipulation SQL
  105. * @return Aucun retour
  106. */
  107. public function create(){
  108. $query = 'CREATE TABLE IF NOT EXISTS `'.MYSQL_PREFIX.$this->TABLE_NAME.'` (';
  109. $i=false;
  110. foreach($this->object_fields as $field=>$type){
  111. if($i){$query .=',';}else{$i=true;}
  112. $query .='`'.$field.'` '. $this->sgbdType($type).' NOT NULL';
  113. }
  114. if (isset($this->object_fields_index)){
  115. foreach($this->object_fields_index as $field=>$type){
  116. $query .= ',KEY `index'.$field.'` (`'.$field.'`)';
  117. }
  118. }
  119. if (isset($this->object_fields_uniques)){
  120. foreach($this->object_fields_uniques as $field){
  121. $query .= ',UNIQUE `unique'.$field.'` (`'.$field.'`)';
  122. }
  123. }
  124. $query .= ')
  125. ENGINE InnoDB,
  126. DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
  127. ;';
  128. if($this->debug)echo '<hr>'.get_class($this).' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.$this->dbconnector->connection->error;
  129. $myQuery = $this->customQuery($query);
  130. }
  131. public function massiveInsert($events){
  132. if (empty($events)) return;
  133. $query = 'INSERT INTO `'.MYSQL_PREFIX.$this->TABLE_NAME.'`(';
  134. $i=false;
  135. foreach($this->object_fields as $field=>$type){
  136. if($type!='key'){
  137. if($i){$query .=',';}else{$i=true;}
  138. $query .='`'.$field.'`';
  139. }
  140. }
  141. $query .=') select';
  142. $u = false;
  143. foreach($events as $event){
  144. if($u){$query .=' union select ';}else{$u=true;}
  145. $i=false;
  146. foreach($event->object_fields as $field=>$type){
  147. if($type!='key'){
  148. if($i){$query .=',';}else{$i=true;}
  149. $query .='"'.$this->secure($event->$field, $field).'"';
  150. }
  151. }
  152. }
  153. $query .=';';
  154. if($this->debug)echo '<hr>'.get_class($this).' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.$this->dbconnector->connection->error;
  155. $this->customQuery($query);
  156. }
  157. /**
  158. * Methode d'insertion ou de modifications d'elements de l'entité
  159. * @author Valentin CARRUESCO
  160. * @category manipulation SQL
  161. * @param Aucun
  162. * @return Aucun retour
  163. */
  164. public function save($id_field='id'){
  165. if(isset($this->$id_field)){
  166. $query = 'UPDATE `'.MYSQL_PREFIX.$this->TABLE_NAME.'`';
  167. $query .= ' SET ';
  168. $i=false;
  169. foreach($this->object_fields as $field=>$type){
  170. if($i){$query .=',';}else{$i=true;}
  171. $id = $this->$field;
  172. $query .= '`'.$field.'`="'.$this->secure($id, $field).'"';
  173. }
  174. $query .= ' WHERE `'.$id_field.'`="'.$this->$id_field.'";';
  175. }else{
  176. $query = 'INSERT INTO `'.MYSQL_PREFIX.$this->TABLE_NAME.'`(';
  177. $i=false;
  178. foreach($this->object_fields as $field=>$type){
  179. if($i){$query .=',';}else{$i=true;}
  180. $query .='`'.$field.'`';
  181. }
  182. $query .=')VALUES(';
  183. $i=false;
  184. foreach($this->object_fields as $field=>$type){
  185. if($i){$query .=',';}else{$i=true;}
  186. $query .='"'.$this->secure($this->$field, $field).'"';
  187. }
  188. $query .=');';
  189. }
  190. if($this->debug)echo '<hr>'.get_class($this).' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.$this->dbconnector->connection->error;
  191. $this->customQuery($query);
  192. $this->$id_field = (!isset($this->$id_field)?$this->dbconnector->connection->insert_id:$this->$id_field);
  193. }
  194. /**
  195. * Méthode de modification d'éléments de l'entité
  196. * @author Valentin CARRUESCO
  197. * @category manipulation SQL
  198. * @param <Array> $colonnes=>$valeurs
  199. * @param <Array> $colonnes (WHERE) =>$valeurs (WHERE)
  200. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  201. * @return Aucun retour
  202. */
  203. public function change($columns,$columns2,$operation='='){
  204. $query = 'UPDATE `'.MYSQL_PREFIX.$this->TABLE_NAME.'` SET ';
  205. $i=false;
  206. foreach ($columns as $column=>$value){
  207. if($i){$query .=',';}else{$i=true;}
  208. $query .= '`'.$column.'`="'.$this->secure($value, $column).'" ';
  209. }
  210. $query .= $this->getWhereClause($columns2, $operation);
  211. if($this->debug)echo '<hr>'.get_class($this).' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.$this->dbconnector->connection->error;
  212. $this->customQuery($query);
  213. }
  214. /**
  215. * Méthode de selection de tous les elements de l'entité
  216. * @author Valentin CARRUESCO
  217. * @category manipulation SQL
  218. * @param <String> $ordre=null
  219. * @param <String> $limite=null
  220. * @return <Array<Entity>> $Entity
  221. */
  222. public function populate($order=null,$limit=null){
  223. $results = $this->loadAll(array(),$order,$limit,'=');
  224. return $results;
  225. }
  226. /**
  227. * Méthode de selection multiple d'elements de l'entité
  228. * @author Valentin CARRUESCO
  229. * @category manipulation SQL
  230. * @param <Array> $colonnes (WHERE)
  231. * @param <Array> $valeurs (WHERE)
  232. * @param <String> $ordre=null
  233. * @param <String> $limite=null
  234. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  235. * @return <Array<Entity>> $Entity
  236. */
  237. public function loadAll($columns,$order=null,$limit=null,$operation="=",$selColumn='*'){
  238. $objects = array();
  239. $whereClause = $this->getWhereClause($columns,$operation);
  240. $query = 'SELECT '.$selColumn.' FROM `'.MYSQL_PREFIX.$this->TABLE_NAME.'` '.$whereClause.' ';
  241. if($order!=null) $query .='ORDER BY '.$order.' ';
  242. if($limit!=null) $query .='LIMIT '.$limit.' ';
  243. $query .=';';
  244. if($this->debug)echo '<hr>'.get_class($this).' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.$this->dbconnector->connection->error;
  245. $result = $this->customQuery($query);
  246. while($queryReturn = $result->fetch_assoc()){
  247. $thisClass = get_class($this);
  248. $object = new $thisClass();
  249. foreach($this->object_fields as $field=>$type){
  250. if(isset($queryReturn[$field])) $object->$field = $queryReturn[$field];
  251. }
  252. $objects[] = $object;
  253. unset($object);
  254. }
  255. return $objects;
  256. }
  257. public function loadAllOnlyColumn($selColumn,$columns,$order=null,$limit=null,$operation="="){
  258. $objects = $this->loadAll($columns,$order,$limit,$operation,$selColumn);
  259. if(count($objects)==0)$objects = array();
  260. return $objects;
  261. }
  262. /**
  263. * Méthode de selection unique d'élements de l'entité
  264. * @author Valentin CARRUESCO
  265. * @category manipulation SQL
  266. * @param <Array> $colonnes (WHERE)
  267. * @param <Array> $valeurs (WHERE)
  268. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  269. * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
  270. */
  271. public function load($columns,$operation='='){
  272. $objects = $this->loadAll($columns,null,1,$operation);
  273. if(!isset($objects[0]))$objects[0] = false;
  274. return $objects[0];
  275. }
  276. /**
  277. * Méthode de selection unique d'élements de l'entité
  278. * @author Valentin CARRUESCO
  279. * @category manipulation SQL
  280. * @param <Array> $colonnes (WHERE)
  281. * @param <Array> $valeurs (WHERE)
  282. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  283. * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
  284. */
  285. public function getById($id,$operation='='){
  286. return $this->load(array('id'=>$id),$operation);
  287. }
  288. /**
  289. * Methode de comptage des éléments de l'entité
  290. * @author Valentin CARRUESCO
  291. * @category manipulation SQL
  292. * @return<Integer> nombre de ligne dans l'entité'
  293. */
  294. public function rowCount($columns=null)
  295. {
  296. $whereClause ='';
  297. if($columns!=null){
  298. $whereClause = ' WHERE ';
  299. $i=false;
  300. foreach($columns as $column=>$value){
  301. if($i){$whereClause .=' AND ';}else{$i=true;}
  302. $whereClause .= '`'.$column.'`="'.$this->secure($value, $column).'"';
  303. }
  304. }
  305. $query = 'SELECT COUNT(1) FROM `'.MYSQL_PREFIX.$this->TABLE_NAME.'`'.$whereClause;
  306. if($this->debug)echo '<hr>'.get_class($this).' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.$this->dbconnector->connection->error;
  307. $myQuery = $this->customQuery($query);
  308. $number = $myQuery->fetch_array();
  309. return $number[0];
  310. }
  311. /**
  312. * Méthode de suppression d'éléments de l'entité
  313. * @author Valentin CARRUESCO
  314. * @category manipulation SQL
  315. * @param <Array> $colonnes (WHERE)
  316. * @param <Array> $valeurs (WHERE)
  317. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  318. * @return Aucun retour
  319. */
  320. public function delete($columns,$operation='='){
  321. $whereClause = '';
  322. $i=false;
  323. foreach($columns as $column=>$value){
  324. if($i){$whereClause .=' AND ';}else{$i=true;}
  325. $whereClause .= '`'.$column.'`'.$operation.'"'.$this->secure($value, $column).'"';
  326. }
  327. $query = 'DELETE FROM `'.MYSQL_PREFIX.$this->TABLE_NAME.'` WHERE '.$whereClause.' ;';
  328. if($this->debug)echo '<hr>'.get_class($this).' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.$this->dbconnector->connection->error;
  329. $this->customQuery($query);
  330. }
  331. public function customQuery($request){
  332. if($this->debugAllQuery)echo '<hr>'.get_class($this).' ('.__METHOD__ .') : Requete --> '.$request.'<br>'.$this->dbconnector->connection->error;
  333. $result = $this->dbconnector->connection->query($request);
  334. $error = $this->error();
  335. if ($error) {
  336. error_log('Leed error: '.$this->error());
  337. error_log('Leed query: '.$request);
  338. }
  339. return $result;
  340. }
  341. // ACCESSEURS
  342. /**
  343. * Méthode de récuperation de l'attribut debug de l'entité
  344. * @author Valentin CARRUESCO
  345. * @category Accesseur
  346. * @param Aucun
  347. * @return <Attribute> debug
  348. */
  349. public function getDebug(){
  350. return $this->debug;
  351. }
  352. /**
  353. * Méthode de définition de l'attribut debug de l'entité
  354. * @author Valentin CARRUESCO
  355. * @category Accesseur
  356. * @param <boolean> $debug
  357. */
  358. public function setDebug($debug){
  359. $this->debug = $debug;
  360. }
  361. public function getObject_fields(){
  362. return $this->object_fields;
  363. }
  364. /**
  365. * @return <boolean> VRAI si la table existe, FAUX sinon
  366. */
  367. public function tableExists() {
  368. $table = MYSQL_PREFIX.$this->TABLE_NAME;
  369. $result = $this->customQuery("SHOW TABLES LIKE '$table'");
  370. $assoc = $result->fetch_assoc();
  371. return !is_null($assoc);
  372. }
  373. /**
  374. * Protège les requêtes contre l'injection
  375. */
  376. public function escape_string($argument) {
  377. return $this->dbconnector->connection->escape_string($argument);
  378. }
  379. /**
  380. * Méthode de récupération d'un opérateur défini dans la valeur d'un filtre avant réquête
  381. * @author Simon Alberny
  382. * @param <str> Opérateur (ex. : '=', '!=', '<', '<=', '>', '>=')
  383. * @param <str> Valeur ou opérateur valeur (ex. : '1', '< 1')
  384. * @return <array> 0: opérateur, 1: valeur
  385. */
  386. protected function getCustomQueryOperator($operation_default, $value) {
  387. $valid_operators = array('=','!=','<','<=','>','>=');
  388. $operation = $operation_default;
  389. // Modification de l'opération si contenu dans la valeur du filtre
  390. $value_list = explode(' ', $value);
  391. if((count($value_list) > 0) && (in_array($value_list[0],$valid_operators))) {
  392. $operation = $value_list[0];
  393. $value = $value_list[1];
  394. }
  395. return array($operation, $value);
  396. }
  397. /**
  398. * Définition des clauses du WHERE dans une requête à la base de données
  399. * @author Simon Alberny
  400. * @param <array> Tableau de correspondance colonne => valeur (ex. : array( 'column' => 'value', 'column2' => '!= 2' ) )
  401. * @param <str> Opérateur (ex. : '=', '!=', '<', '<=', '>', '>=')
  402. * @return <str> WHERE...
  403. */
  404. protected function getWhereClause($columns,$operation) {
  405. $whereClause = '';
  406. $operation_default = $operation;
  407. if($columns!=null && sizeof($columns)!=0){
  408. $whereClause .= ' WHERE ';
  409. $i = false;
  410. foreach($columns as $column=>$value){
  411. $customQueryOperator = $this->getCustomQueryOperator($operation_default, $value);
  412. if($i){$whereClause .=' AND ';}else{$i=true;}
  413. $whereClause .= '`'.$column.'`'.$customQueryOperator[0].'"'.$this->secure($customQueryOperator[1], $column).'"';
  414. }
  415. }
  416. return $whereClause;
  417. }
  418. public function error() {
  419. return $this->dbconnector->error();
  420. }
  421. }
  422. ?>