123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449 |
- <?php
- require_once('constant.php');
- require_once('MysqlConnector.class.php');
- /*
- @nom: MysqlEntity
- @auteur: Valentin CARRUESCO (valentincarruesco@yahoo.fr)
- @date de création: 16/04/2012 02:34:15
- @description: Classe parent de tous les modèles (classe entitées) liées a la base de donnée,
- 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
- le reste du code du projet.
- */
- class MysqlEntity
- {
- private $debug = false;
- private $debugAllQuery = false;
- function sgbdType($type){
- $return = false;
- switch($type){
- case 'string':
- case 'timestamp':
- $return = 'VARCHAR(225) CHARACTER SET utf8 COLLATE utf8_general_ci';
- break;
- case 'longstring':
- $return = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
- break;
- case 'extralongstring':
- $return = 'MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
- break;
- case 'key':
- $return = 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY';
- break;
- case 'object':
- case 'integer':
- $return = 'INT(11)';
- break;
- case 'boolean':
- $return = 'INT(1)';
- break;
- default;
- $return = 'TEXT CHARACTER SET utf8 COLLATE utf8_general_ci';
- break;
- }
- return $return ;
- }
- /**
- * Protège une variable pour MySQL
- */
- protected function secure($value, $field){
- $type = false;
- // ce champ n'existe pas : on le considère comme une chaîne de caractères
- if (isset($this->object_fields[$field]))
- $type = $this->object_fields[$field];
- $return = false;
- switch($type){
- case 'key':
- case 'object':
- case 'integer':
- case 'boolean':
- $return = intval($value);
- break;
- case 'string':
- case 'timestamp':
- case 'longstring':
- default;
- $return = mysql_real_escape_string((string)$value);
- break;
- }
- return $return ;
- }
- public function __construct(){
- MysqlConnector::getInstance();
- }
- public function __destruct(){
- }
- // GESTION SQL
- /**
- * Methode de suppression de l'entité
- * @author Valentin CARRUESCO
- * @category manipulation SQL
- * @param <String> $debug=false active le debug mode (0 ou 1)
- * @return Aucun retour
- */
- public function destroy($debug=false)
- {
- $query = 'DROP TABLE IF EXISTS '.MYSQL_PREFIX.$this->TABLE_NAME.';';
- if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
- $myQuery = $this->customQuery($query);
- }
- /**
- * Methode de nettoyage de l'entité
- * @author Valentin CARRUESCO
- * @category manipulation SQL
- * @param <String> $debug=false active le debug mode (0 ou 1)
- * @return Aucun retour
- */
- public function truncate($debug=false)
- {
- $query = 'TRUNCATE TABLE '.MYSQL_PREFIX.$this->TABLE_NAME.';';
- if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
- $myQuery = $this->customQuery($query);
- }
- /**
- * Methode de creation de l'entité
- * @author Valentin CARRUESCO
- * @category manipulation SQL
- * @param <String> $debug=false active le debug mode (0 ou 1)
- * @return Aucun retour
- */
- public function create($debug=false){
- $query = 'CREATE TABLE IF NOT EXISTS `'.MYSQL_PREFIX.$this->TABLE_NAME.'` (';
- $i=false;
- foreach($this->object_fields as $field=>$type){
- if($i){$query .=',';}else{$i=true;}
- $query .='`'.$field.'` '. $this->sgbdType($type).' NOT NULL';
- }
- if (isset($this->object_fields_index)){
- foreach($this->object_fields_index as $field=>$type){
- $query .= ',KEY `index'.$field.'` (`'.$field.'`)';
- }
- }
- $query .= ')
- ENGINE InnoDB,
- DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
- ;';
- if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
- $myQuery = $this->customQuery($query);
- }
- public function massiveInsert($events){
- if (empty($events)) return;
- $query = 'INSERT INTO `'.MYSQL_PREFIX.$this->TABLE_NAME.'`(';
- $i=false;
- foreach($this->object_fields as $field=>$type){
- if($type!='key'){
- if($i){$query .=',';}else{$i=true;}
- $query .='`'.$field.'`';
- }
- }
- $query .=') select';
- $u = false;
- foreach($events as $event){
- if($u){$query .=' union select ';}else{$u=true;}
- $i=false;
- foreach($event->object_fields as $field=>$type){
- if($type!='key'){
- if($i){$query .=',';}else{$i=true;}
- $query .='"'.$this->secure($event->$field, $field).'"';
- }
- }
- }
- $query .=';';
- if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
- $this->customQuery($query);
- }
- /**
- * Methode d'insertion ou de modifications d'elements de l'entité
- * @author Valentin CARRUESCO
- * @category manipulation SQL
- * @param Aucun
- * @return Aucun retour
- */
- public function save(){
- if(isset($this->id)){
- $query = 'UPDATE `'.MYSQL_PREFIX.$this->TABLE_NAME.'`';
- $query .= ' SET ';
- $i=false;
- foreach($this->object_fields as $field=>$type){
- if($i){$query .=',';}else{$i=true;}
- $id = $this->$field;
- $query .= '`'.$field.'`="'.$this->secure($id, $field).'"';
- }
- $query .= ' WHERE `id`="'.$this->id.'";';
- }else{
- $query = 'INSERT INTO `'.MYSQL_PREFIX.$this->TABLE_NAME.'`(';
- $i=false;
- foreach($this->object_fields as $field=>$type){
- if($i){$query .=',';}else{$i=true;}
- $query .='`'.$field.'`';
- }
- $query .=')VALUES(';
- $i=false;
- foreach($this->object_fields as $field=>$type){
- if($i){$query .=',';}else{$i=true;}
- $query .='"'.$this->secure($this->$field, $field).'"';
- }
- $query .=');';
- }
- if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
- $this->customQuery($query);
- $this->id = (!isset($this->id)?mysql_insert_id():$this->id);
- }
- /**
- * Méthode de modification d'éléments de l'entité
- * @author Valentin CARRUESCO
- * @category manipulation SQL
- * @param <Array> $colonnes=>$valeurs
- * @param <Array> $colonnes (WHERE) =>$valeurs (WHERE)
- * @param <String> $operation="=" definis le type d'operateur pour la requete select
- * @param <String> $debug=false active le debug mode (0 ou 1)
- * @return Aucun retour
- */
- public function change($columns,$columns2,$operation='=',$debug=false){
- $query = 'UPDATE `'.MYSQL_PREFIX.$this->TABLE_NAME.'` SET ';
- $i=false;
- foreach ($columns as $column=>$value){
- if($i){$query .=',';}else{$i=true;}
- $query .= '`'.$column.'`="'.$this->secure($value, $column).'" ';
- }
- $query .=' WHERE ';
- $i = false;
- foreach ($columns2 as $column=>$value){
- if($i){$query .='AND ';}else{$i=true;}
- $query .= '`'.$column.'`'.$operation.'"'.$this->secure($value, $column).'" ';
- }
- if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
- $this->customQuery($query);
- }
- /**
- * Méthode de selection de tous les elements de l'entité
- * @author Valentin CARRUESCO
- * @category manipulation SQL
- * @param <String> $ordre=null
- * @param <String> $limite=null
- * @param <String> $debug=false active le debug mode (0 ou 1)
- * @return <Array<Entity>> $Entity
- */
- public function populate($order=null,$limit=null,$debug=false){
- $results = $this->loadAll(array(),$order,$limit,'=',$debug);
- return $results;
- }
- /**
- * Méthode de selection multiple d'elements de l'entité
- * @author Valentin CARRUESCO
- * @category manipulation SQL
- * @param <Array> $colonnes (WHERE)
- * @param <Array> $valeurs (WHERE)
- * @param <String> $ordre=null
- * @param <String> $limite=null
- * @param <String> $operation="=" definis le type d'operateur pour la requete select
- * @param <String> $debug=false active le debug mode (0 ou 1)
- * @return <Array<Entity>> $Entity
- */
- public function loadAll($columns,$order=null,$limit=null,$operation="=",$debug=false,$selColumn='*'){
- $objects = array();
- $whereClause = '';
- if($columns!=null && sizeof($columns)!=0){
- $whereClause .= ' WHERE ';
- $i = false;
- foreach($columns as $column=>$value){
- if($i){$whereClause .=' AND ';}else{$i=true;}
- $whereClause .= '`'.$column.'`'.$operation.'"'.$this->secure($value, $column).'"';
- }
- }
- $query = 'SELECT '.$selColumn.' FROM `'.MYSQL_PREFIX.$this->TABLE_NAME.'` '.$whereClause.' ';
- if($order!=null) $query .='ORDER BY '.$order.' ';
- if($limit!=null) $query .='LIMIT '.$limit.' ';
- $query .=';';
- if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
- $execQuery = $this->customQuery($query);
- while($queryReturn = mysql_fetch_assoc($execQuery)){
- $object = new $this->CLASS_NAME();
- foreach($this->object_fields as $field=>$type){
- if(isset($queryReturn[$field])) $object->$field = $queryReturn[$field];
- }
- $objects[] = $object;
- unset($object);
- }
- return $objects;
- }
- public function loadAllOnlyColumn($selColumn,$columns,$order=null,$limit=null,$operation="=",$debug=false){
- $objects = $this->loadAll($columns,$order,$limit,$operation,$debug,$selColumn);
- if(count($objects)==0)$objects = array();
- return $objects;
- }
- /**
- * Méthode de selection unique d'élements de l'entité
- * @author Valentin CARRUESCO
- * @category manipulation SQL
- * @param <Array> $colonnes (WHERE)
- * @param <Array> $valeurs (WHERE)
- * @param <String> $operation="=" definis le type d'operateur pour la requete select
- * @param <String> $debug=false active le debug mode (0 ou 1)
- * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
- */
- public function load($columns,$operation='=',$debug=false){
- $objects = $this->loadAll($columns,null,1,$operation,$debug);
- if(!isset($objects[0]))$objects[0] = false;
- return $objects[0];
- }
- /**
- * Méthode de selection unique d'élements de l'entité
- * @author Valentin CARRUESCO
- * @category manipulation SQL
- * @param <Array> $colonnes (WHERE)
- * @param <Array> $valeurs (WHERE)
- * @param <String> $operation="=" definis le type d'operateur pour la requete select
- * @param <String> $debug=false active le debug mode (0 ou 1)
- * @return <Entity> $Entity ou false si aucun objet n'est trouvé en base
- */
- public function getById($id,$operation='=',$debug=false){
- return $this->load(array('id'=>$id),$operation,$debug);
- }
- /**
- * Methode de comptage des éléments de l'entité
- * @author Valentin CARRUESCO
- * @category manipulation SQL
- * @param <String> $debug=false active le debug mode (0 ou 1)
- * @return<Integer> nombre de ligne dans l'entité'
- */
- public function rowCount($columns=null)
- {
- $whereClause ='';
- if($columns!=null){
- $whereClause = ' WHERE ';
- $i=false;
- foreach($columns as $column=>$value){
- if($i){$whereClause .=' AND ';}else{$i=true;}
- $whereClause .= '`'.$column.'`="'.$this->secure($value, $column).'"';
- }
- }
- $query = 'SELECT COUNT(1) FROM '.MYSQL_PREFIX.$this->TABLE_NAME.$whereClause;
- if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
- $myQuery = $this->customQuery($query);
- $number = mysql_fetch_array($myQuery);
- return $number[0];
- }
- /**
- * Méthode de suppression d'éléments de l'entité
- * @author Valentin CARRUESCO
- * @category manipulation SQL
- * @param <Array> $colonnes (WHERE)
- * @param <Array> $valeurs (WHERE)
- * @param <String> $operation="=" definis le type d'operateur pour la requete select
- * @param <String> $debug=false active le debug mode (0 ou 1)
- * @return Aucun retour
- */
- public function delete($columns,$operation='=',$debug=false){
- $whereClause = '';
- $i=false;
- foreach($columns as $column=>$value){
- if($i){$whereClause .=' AND ';}else{$i=true;}
- $whereClause .= '`'.$column.'`'.$operation.'"'.$this->secure($value, $column).'"';
- }
- $query = 'DELETE FROM `'.MYSQL_PREFIX.$this->TABLE_NAME.'` WHERE '.$whereClause.' ;';
- if($this->debug)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$query.'<br>'.mysql_error();
- $this->customQuery($query);
- }
- ///@TODO: pourquoi deux méthodes différentes qui font la même chose ?
- public function customExecute($request){
- if($this->debugAllQuery)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$request.'<br>'.mysql_error();
- $result = mysql_query($request);
- if (false===$result) {
- throw new Exception(mysql_error());
- }
- return $result;
- }
- public function customQuery($request){
- if($this->debugAllQuery)echo '<hr>'.$this->CLASS_NAME.' ('.__METHOD__ .') : Requete --> '.$request.'<br>'.mysql_error();
- $result = mysql_query($request);
- if (false===$result) {
- throw new Exception(mysql_error());
- }
- return $result;
- }
- // ACCESSEURS
- /**
- * Méthode de récuperation de l'attribut debug de l'entité
- * @author Valentin CARRUESCO
- * @category Accesseur
- * @param Aucun
- * @return <Attribute> debug
- */
- public function getDebug(){
- return $this->debug;
- }
- /**
- * Méthode de définition de l'attribut debug de l'entité
- * @author Valentin CARRUESCO
- * @category Accesseur
- * @param <boolean> $debug
- */
- public function setDebug($debug){
- $this->debug = $debug;
- }
- public function getObject_fields(){
- return $this->object_fields;
- }
- /**
- * @return <boolean> VRAI si la table existe, FAUX sinon
- */
- public function tableExists() {
- $table = MYSQL_PREFIX.$this->TABLE_NAME;
- $result = $this->customQuery("SHOW TABLES LIKE '$table'");
- $assoc = mysql_fetch_assoc($result);
- return false===$assoc ? false : true;
- }
- }
- ?>
|