MysqlEntity.class.php 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449
  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. private $debug = false;
  15. private $debugAllQuery = false;
  16. function sgbdType($type){
  17. $return = false;
  18. switch($type){
  19. case 'string':
  20. case 'timestamp':
  21. $return = 'VARCHAR(225) CHARACTER SET utf8 COLLATE utf8_general_ci';
  22. break;
  23. case 'longstring':
  24. $return = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
  25. break;
  26. case 'extralongstring':
  27. $return = 'MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
  28. break;
  29. case 'key':
  30. $return = 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY';
  31. break;
  32. case 'object':
  33. case 'integer':
  34. $return = 'INT(11)';
  35. break;
  36. case 'boolean':
  37. $return = 'INT(1)';
  38. break;
  39. default;
  40. $return = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
  41. break;
  42. }
  43. return $return ;
  44. }
  45. /**
  46. * Protège une variable pour MySQL
  47. */
  48. protected function secure($value, $field){
  49. $type = false;
  50. // ce champ n'existe pas : on le considère comme une chaîne de caractères
  51. if (isset($this->object_fields[$field]))
  52. $type = $this->object_fields[$field];
  53. $return = false;
  54. switch($type){
  55. case 'key':
  56. case 'object':
  57. case 'integer':
  58. case 'boolean':
  59. $return = intval($value);
  60. break;
  61. case 'string':
  62. case 'timestamp':
  63. case 'longstring':
  64. default;
  65. $return = mysql_real_escape_string((string)$value);
  66. break;
  67. }
  68. return $return ;
  69. }
  70. public function __construct(){
  71. MysqlConnector::getInstance();
  72. }
  73. public function __destruct(){
  74. }
  75. // GESTION SQL
  76. /**
  77. * Methode de suppression de l'entité
  78. * @author Valentin CARRUESCO
  79. * @category manipulation SQL
  80. * @param <String> $debug=false active le debug mode (0 ou 1)
  81. * @return Aucun retour
  82. */
  83. public function destroy($debug=false)
  84. {
  85. $query = 'DROP TABLE IF EXISTS '.MYSQL_PREFIX.$this->TABLE_NAME.';';
  86. if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
  87. $myQuery = $this->customQuery($query);
  88. }
  89. /**
  90. * Methode de nettoyage de l'entité
  91. * @author Valentin CARRUESCO
  92. * @category manipulation SQL
  93. * @param <String> $debug=false active le debug mode (0 ou 1)
  94. * @return Aucun retour
  95. */
  96. public function truncate($debug=false)
  97. {
  98. $query = 'TRUNCATE TABLE '.MYSQL_PREFIX.$this->TABLE_NAME.';';
  99. if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
  100. $myQuery = $this->customQuery($query);
  101. }
  102. /**
  103. * Methode de creation de l'entité
  104. * @author Valentin CARRUESCO
  105. * @category manipulation SQL
  106. * @param <String> $debug=false active le debug mode (0 ou 1)
  107. * @return Aucun retour
  108. */
  109. public function create($debug=false){
  110. $query = 'CREATE TABLE IF NOT EXISTS `'.MYSQL_PREFIX.$this->TABLE_NAME.'` (';
  111. $i=false;
  112. foreach($this->object_fields as $field=>$type){
  113. if($i){$query .=',';}else{$i=true;}
  114. $query .='`'.$field.'` '. $this->sgbdType($type).' NOT NULL';
  115. }
  116. if (isset($this->object_fields_index)){
  117. foreach($this->object_fields_index as $field=>$type){
  118. $query .= ',KEY `index'.$field.'` (`'.$field.'`)';
  119. }
  120. }
  121. $query .= ')
  122. ENGINE InnoDB,
  123. DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
  124. ;';
  125. if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
  126. $myQuery = $this->customQuery($query);
  127. }
  128. public function massiveInsert($events){
  129. if (empty($events)) return;
  130. $query = 'INSERT INTO `'.MYSQL_PREFIX.$this->TABLE_NAME.'`(';
  131. $i=false;
  132. foreach($this->object_fields as $field=>$type){
  133. if($type!='key'){
  134. if($i){$query .=',';}else{$i=true;}
  135. $query .='`'.$field.'`';
  136. }
  137. }
  138. $query .=') select';
  139. $u = false;
  140. foreach($events as $event){
  141. if($u){$query .=' union select ';}else{$u=true;}
  142. $i=false;
  143. foreach($event->object_fields as $field=>$type){
  144. if($type!='key'){
  145. if($i){$query .=',';}else{$i=true;}
  146. $query .='"'.$this->secure($event->$field, $field).'"';
  147. }
  148. }
  149. }
  150. $query .=';';
  151. if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
  152. $this->customQuery($query);
  153. }
  154. /**
  155. * Methode d'insertion ou de modifications d'elements de l'entité
  156. * @author Valentin CARRUESCO
  157. * @category manipulation SQL
  158. * @param Aucun
  159. * @return Aucun retour
  160. */
  161. public function save(){
  162. if(isset($this->id)){
  163. $query = 'UPDATE `'.MYSQL_PREFIX.$this->TABLE_NAME.'`';
  164. $query .= ' SET ';
  165. $i=false;
  166. foreach($this->object_fields as $field=>$type){
  167. if($i){$query .=',';}else{$i=true;}
  168. $id = $this->$field;
  169. $query .= '`'.$field.'`="'.$this->secure($id, $field).'"';
  170. }
  171. $query .= ' WHERE `id`="'.$this->id.'";';
  172. }else{
  173. $query = 'INSERT INTO `'.MYSQL_PREFIX.$this->TABLE_NAME.'`(';
  174. $i=false;
  175. foreach($this->object_fields as $field=>$type){
  176. if($i){$query .=',';}else{$i=true;}
  177. $query .='`'.$field.'`';
  178. }
  179. $query .=')VALUES(';
  180. $i=false;
  181. foreach($this->object_fields as $field=>$type){
  182. if($i){$query .=',';}else{$i=true;}
  183. $query .='"'.$this->secure($this->$field, $field).'"';
  184. }
  185. $query .=');';
  186. }
  187. if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
  188. $this->customQuery($query);
  189. $this->id = (!isset($this->id)?mysql_insert_id():$this->id);
  190. }
  191. /**
  192. * Méthode de modification d'éléments de l'entité
  193. * @author Valentin CARRUESCO
  194. * @category manipulation SQL
  195. * @param <Array> $colonnes=>$valeurs
  196. * @param <Array> $colonnes (WHERE) =>$valeurs (WHERE)
  197. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  198. * @param <String> $debug=false active le debug mode (0 ou 1)
  199. * @return Aucun retour
  200. */
  201. public function change($columns,$columns2,$operation='=',$debug=false){
  202. $query = 'UPDATE `'.MYSQL_PREFIX.$this->TABLE_NAME.'` SET ';
  203. $i=false;
  204. foreach ($columns as $column=>$value){
  205. if($i){$query .=',';}else{$i=true;}
  206. $query .= '`'.$column.'`="'.$this->secure($value, $column).'" ';
  207. }
  208. $query .=' WHERE ';
  209. $i = false;
  210. foreach ($columns2 as $column=>$value){
  211. if($i){$query .='AND ';}else{$i=true;}
  212. $query .= '`'.$column.'`'.$operation.'"'.$this->secure($value, $column).'" ';
  213. }
  214. if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
  215. $this->customQuery($query);
  216. }
  217. /**
  218. * Méthode de selection de tous les elements de l'entité
  219. * @author Valentin CARRUESCO
  220. * @category manipulation SQL
  221. * @param <String> $ordre=null
  222. * @param <String> $limite=null
  223. * @param <String> $debug=false active le debug mode (0 ou 1)
  224. * @return <Array<Entity>> $Entity
  225. */
  226. public function populate($order=null,$limit=null,$debug=false){
  227. $results = $this->loadAll(array(),$order,$limit,'=',$debug);
  228. return $results;
  229. }
  230. /**
  231. * Méthode de selection multiple d'elements de l'entité
  232. * @author Valentin CARRUESCO
  233. * @category manipulation SQL
  234. * @param <Array> $colonnes (WHERE)
  235. * @param <Array> $valeurs (WHERE)
  236. * @param <String> $ordre=null
  237. * @param <String> $limite=null
  238. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  239. * @param <String> $debug=false active le debug mode (0 ou 1)
  240. * @return <Array<Entity>> $Entity
  241. */
  242. public function loadAll($columns,$order=null,$limit=null,$operation="=",$debug=false,$selColumn='*'){
  243. $objects = array();
  244. $whereClause = '';
  245. if($columns!=null && sizeof($columns)!=0){
  246. $whereClause .= ' WHERE ';
  247. $i = false;
  248. foreach($columns as $column=>$value){
  249. if($i){$whereClause .=' AND ';}else{$i=true;}
  250. $whereClause .= '`'.$column.'`'.$operation.'"'.$this->secure($value, $column).'"';
  251. }
  252. }
  253. $query = 'SELECT '.$selColumn.' FROM `'.MYSQL_PREFIX.$this->TABLE_NAME.'` '.$whereClause.' ';
  254. if($order!=null) $query .='ORDER BY '.$order.' ';
  255. if($limit!=null) $query .='LIMIT '.$limit.' ';
  256. $query .=';';
  257. if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
  258. $execQuery = $this->customQuery($query);
  259. while($queryReturn = mysql_fetch_assoc($execQuery)){
  260. $object = new $this->CLASS_NAME();
  261. foreach($this->object_fields as $field=>$type){
  262. if(isset($queryReturn[$field])) $object->$field = $queryReturn[$field];
  263. }
  264. $objects[] = $object;
  265. unset($object);
  266. }
  267. return $objects;
  268. }
  269. public function loadAllOnlyColumn($selColumn,$columns,$order=null,$limit=null,$operation="=",$debug=false){
  270. $objects = $this->loadAll($columns,$order,$limit,$operation,$debug,$selColumn);
  271. if(count($objects)==0)$objects = array();
  272. return $objects;
  273. }
  274. /**
  275. * Méthode de selection unique d'élements de l'entité
  276. * @author Valentin CARRUESCO
  277. * @category manipulation SQL
  278. * @param <Array> $colonnes (WHERE)
  279. * @param <Array> $valeurs (WHERE)
  280. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  281. * @param <String> $debug=false active le debug mode (0 ou 1)
  282. * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
  283. */
  284. public function load($columns,$operation='=',$debug=false){
  285. $objects = $this->loadAll($columns,null,1,$operation,$debug);
  286. if(!isset($objects[0]))$objects[0] = false;
  287. return $objects[0];
  288. }
  289. /**
  290. * Méthode de selection unique d'élements de l'entité
  291. * @author Valentin CARRUESCO
  292. * @category manipulation SQL
  293. * @param <Array> $colonnes (WHERE)
  294. * @param <Array> $valeurs (WHERE)
  295. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  296. * @param <String> $debug=false active le debug mode (0 ou 1)
  297. * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
  298. */
  299. public function getById($id,$operation='=',$debug=false){
  300. return $this->load(array('id'=>$id),$operation,$debug);
  301. }
  302. /**
  303. * Methode de comptage des éléments de l'entité
  304. * @author Valentin CARRUESCO
  305. * @category manipulation SQL
  306. * @param <String> $debug=false active le debug mode (0 ou 1)
  307. * @return<Integer> nombre de ligne dans l'entité'
  308. */
  309. public function rowCount($columns=null)
  310. {
  311. $whereClause ='';
  312. if($columns!=null){
  313. $whereClause = ' WHERE ';
  314. $i=false;
  315. foreach($columns as $column=>$value){
  316. if($i){$whereClause .=' AND ';}else{$i=true;}
  317. $whereClause .= '`'.$column.'`="'.$this->secure($value, $column).'"';
  318. }
  319. }
  320. $query = 'SELECT COUNT(1) FROM '.MYSQL_PREFIX.$this->TABLE_NAME.$whereClause;
  321. if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
  322. $myQuery = $this->customQuery($query);
  323. $number = mysql_fetch_array($myQuery);
  324. return $number[0];
  325. }
  326. /**
  327. * Méthode de suppression d'éléments de l'entité
  328. * @author Valentin CARRUESCO
  329. * @category manipulation SQL
  330. * @param <Array> $colonnes (WHERE)
  331. * @param <Array> $valeurs (WHERE)
  332. * @param <String> $operation="=" definis le type d'operateur pour la requete select
  333. * @param <String> $debug=false active le debug mode (0 ou 1)
  334. * @return Aucun retour
  335. */
  336. public function delete($columns,$operation='=',$debug=false){
  337. $whereClause = '';
  338. $i=false;
  339. foreach($columns as $column=>$value){
  340. if($i){$whereClause .=' AND ';}else{$i=true;}
  341. $whereClause .= '`'.$column.'`'.$operation.'"'.$this->secure($value, $column).'"';
  342. }
  343. $query = 'DELETE FROM `'.MYSQL_PREFIX.$this->TABLE_NAME.'` WHERE '.$whereClause.' ;';
  344. if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
  345. $this->customQuery($query);
  346. }
  347. ///@TODO: pourquoi deux méthodes différentes qui font la même chose ?
  348. public function customExecute($request){
  349. if($this->debugAllQuery)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$request.'<br>'.mysql_error();
  350. $result = mysql_query($request);
  351. if (false===$result) {
  352. throw new Exception(mysql_error());
  353. }
  354. return $result;
  355. }
  356. public function customQuery($request){
  357. if($this->debugAllQuery)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$request.'<br>'.mysql_error();
  358. $result = mysql_query($request);
  359. if (false===$result) {
  360. throw new Exception(mysql_error());
  361. }
  362. return $result;
  363. }
  364. // ACCESSEURS
  365. /**
  366. * Méthode de récuperation de l'attribut debug de l'entité
  367. * @author Valentin CARRUESCO
  368. * @category Accesseur
  369. * @param Aucun
  370. * @return <Attribute> debug
  371. */
  372. public function getDebug(){
  373. return $this->debug;
  374. }
  375. /**
  376. * Méthode de définition de l'attribut debug de l'entité
  377. * @author Valentin CARRUESCO
  378. * @category Accesseur
  379. * @param <boolean> $debug
  380. */
  381. public function setDebug($debug){
  382. $this->debug = $debug;
  383. }
  384. public function getObject_fields(){
  385. return $this->object_fields;
  386. }
  387. /**
  388. * @return <boolean> VRAI si la table existe, FAUX sinon
  389. */
  390. public function tableExists() {
  391. $table = MYSQL_PREFIX.$this->TABLE_NAME;
  392. $result = $this->customQuery("SHOW TABLES LIKE '$table'");
  393. $assoc = mysql_fetch_assoc($result);
  394. return false===$assoc ? false : true;
  395. }
  396. }
  397. ?>