MysqlEntity.class.php 13 KB

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