From 7cf7609a7e236347374c1b21777a9698bcd1726c Mon Sep 17 00:00:00 2001 From: dj3c1t Date: Sat, 8 Dec 2012 23:43:08 +0100 Subject: [PATCH] mysql : gestion des erreurs en exceptions --- web/app/data/impl/mw_mysql.php | 100 +++++++++------- web/app/data/modules/sql/mw_data_config.php | 75 ++++++------ web/app/data/modules/sql/mw_data_users.php | 179 ++++++++++++++++------------ 3 files changed, 200 insertions(+), 154 deletions(-) diff --git a/web/app/data/impl/mw_mysql.php b/web/app/data/impl/mw_mysql.php index 22bd3ce..da86798 100644 --- a/web/app/data/impl/mw_mysql.php +++ b/web/app/data/impl/mw_mysql.php @@ -1,7 +1,7 @@ EXTENTION_OK; } - function mw_mysql($host, $base, $user, $password) - { $this->host = $host; + function mw_mysql($host, $base, $user, $password){ + $this->host = $host; $this->base = $base; $this->user = $user; $this->password = $password; $this->EXTENTION_OK = function_exists("mysql_connect"); } - function connect($host, $base, $user, $password) - { $this->link = @mysql_connect($host, $user, $password); - if(!$this->link) return null; + function connect($host, $base, $user, $password){ + $this->link = @mysql_connect($host, $user, $password); + if(!$this->link) throw new Exception($this->exception_out("Impossible d'etablir une connection au serveur")); @mysql_query("SET NAMES 'utf8'"); - if($base) - { $connected = @mysql_select_db($base, $this->link); - if(!$connected) return null; + if($base){ + $connected = @mysql_select_db($base, $this->link); + if(!$connected) throw new Exception($this->exception_out("Impossible de selectioner la base ".$base)); } return true; } - function select_db($db_name) - { $this->base = $db_name; - if(!$this->link) - { if(!$this->connect($this->host, $this->base, $this->user, $this->password)) return null; - } - return $this->query("USE ".$db_name); + function select_db($db_name){ + $this->base = $db_name; + if(!$this->link) $this->connect($this->host, $this->base, $this->user, $this->password); + return $this->query("USE ".$db_name); } - function table_exists($table_name) - { $sql = "SHOW TABLES LIKE '".$table_name."'"; - $rst = $this->query($sql); - if(isset($rst)) - { $exists = false; - $v_rst = $this->fetch_assoc($rst); - if($v_rst) $exists = true; - $this->free_result($rst); - return $exists; - } - return null; + function table_exists($base, $table){ + if(!$this->link) $this->connect($this->host, $this->base, $this->user, $this->password); + $this->select_db($base); + $rst = $this->query("SHOW TABLES"); + while($v_rst = mysql_fetch_array($rst)){ + if(strcmp($v_rst[0], $table) == 0) return true; + } + mysql_free_result($rst); + return false; + } + + function query($query_string){ + if(!$this->link) $this->connect($this->host, $this->base, $this->user, $this->password); + $result = @mysql_query($query_string, $this->link); + if(!$result) throw new Exception($this->exception_out("Syntaxe invalide dans une requete")); + return $result; } - function query($query_string) - { if(!$this->link) - { if(!$this->connect($this->host, $this->base, $this->user, $this->password)) return null; + function fetch_assoc($rst){ + if($this->link){ + if($rst){ + return mysql_fetch_assoc($rst); + } + else throw new Exception($this->exception_out("fetch_assoc sans result handler")); } - $result = @mysql_query($query_string, $this->link); - if(!$result) return null; - return $result; + else throw new Exception($this->exception_out("fetch_assoc sans connexion")); } - function fetch_assoc($rst) - { if($rst && $this->link) return mysql_fetch_assoc($rst); - return null; + function insert_id(){ + if($this->link){ + return mysql_insert_id($this->link); + } + else throw new Exception($this->exception_out("insert_id sans connexion")); } - function insert_id() - { if($this->link) return mysql_insert_id($this->link); - return null; + function free_result($rst){ + if($this->link){ + if($rst){ + return mysql_free_result($rst); + } + else throw new Exception($this->exception_out("free_result sans result handler")); + } + else throw new Exception($this->exception_out("free_result sans connexion")); } - function free_result($rst) - { if($rst && $this->link) return mysql_free_result($rst); - return null; + function close(){ + if($this->link) return mysql_close($this->link); + return true; } - function close() - { if($this->link) return mysql_close($this->link); - return null; + function exception_out($message){ + return "[erreur] mysql : ".$message; } } diff --git a/web/app/data/modules/sql/mw_data_config.php b/web/app/data/modules/sql/mw_data_config.php index 0d6f4d2..ca45faa 100644 --- a/web/app/data/modules/sql/mw_data_config.php +++ b/web/app/data/modules/sql/mw_data_config.php @@ -5,57 +5,64 @@ function config($key = null){ $sgbd = $this->sgbd(); $value = false; - if(isset($key)){ - $sql = - "SELECT `value` FROM #--config" - ." WHERE `key`=".$this->eq($key); - $rst = $sgbd->query($sql); - if(!isset($rst)) return false; - if($v_rst = $sgbd->fetch_assoc($rst)) $value = $v_rst["value"]; - else $value = ""; - $sgbd->free_result($rst); - } - else{ - $value = array(); - $sql = - "SELECT * FROM #--config"; - $rst = $sgbd->query($sql); - if(!isset($rst)) return false; - while($v_rst = $sgbd->fetch_assoc($rst)) $value[$v_rst["key"]] = $v_rst["value"]; - $sgbd->free_result($rst); + try{ + if(isset($key)){ + $value = ""; + $sql = "SELECT `value` FROM #--config WHERE `key`=".$this->eq($key); + $rst = $sgbd->query($sql); + if($v_rst = $sgbd->fetch_assoc($rst)) $value = $v_rst["value"]; + $sgbd->free_result($rst); + } + else{ + $value = array(); + $sql = "SELECT * FROM #--config"; + $rst = $sgbd->query($sql); + while($v_rst = $sgbd->fetch_assoc($rst)) $value[$v_rst["key"]] = $v_rst["value"]; + $sgbd->free_result($rst); + } } + catch(Exception $e) { $value = false; } return $value; } function config_exists($key){ $sgbd = $this->sgbd(); $exists = false; - $sql = "SELECT count(*) as n FROM #--config" - ." WHERE `key`=".$this->eq($key); - $rst = $sgbd->query($sql); - if(!isset($rst)) return false; - if($v_rst = $sgbd->fetch_assoc($rst)) $exists = $v_rst["n"]; - $sgbd->free_result($rst); + try{ + $sql = "SELECT count(*) as n FROM #--config WHERE `key`=".$this->eq($key); + $rst = $sgbd->query($sql); + if($v_rst = $sgbd->fetch_assoc($rst)) $exists = $v_rst["n"]; + $sgbd->free_result($rst); + } + catch(Exception $e) { $exists = false; } return $exists; } function set_config($key, $value){ $sgbd = $this->sgbd(); - if($this->config_exists($key)) $sql = - "UPDATE #--config" - ." SET `value`=".$this->eq($value) - ." WHERE `key`=".$this->eq($key); - else $sql = - "INSERT INTO #--config" - ." VALUES(NULL, ".$this->eq($key).", ".$this->eq($value).")"; - $rst = $sgbd->query($sql); - if(!isset($rst)) return false; + if(($exists = $this->config_exists($key)) === false) return false; + try{ + if($exists) $sql = + "UPDATE #--config" + ." SET `value`=".$this->eq($value) + ." WHERE `key`=".$this->eq($key); + else $sql = + "INSERT INTO #--config" + ." VALUES(NULL, ".$this->eq($key).", ".$this->eq($value).")"; + $sgbd->query($sql); + } + catch(Exception $e) { return false; } return true; } function del_config($key){ $sgbd = $this->sgbd(); - return $sgbd->query("DELETE FROM #--config WHERE `key`=".$this->eq($key)) ? true : false; + try{ + $sql = "DELETE FROM #--config WHERE `key`=".$this->eq($key); + $sgbd->query($sql); + } + catch(Exception $e) { return false; } + return true; } } diff --git a/web/app/data/modules/sql/mw_data_users.php b/web/app/data/modules/sql/mw_data_users.php index cfa4fd9..3377c15 100644 --- a/web/app/data/modules/sql/mw_data_users.php +++ b/web/app/data/modules/sql/mw_data_users.php @@ -15,88 +15,109 @@ $sgbd = $this->sgbd(); $env = $this->env(); $users = array("list" => array(), "total" => 0); - $SELECT = "SELECT *"; - $FROM = " FROM #--users"; - $WHERE = ""; - $WHERE .= (isset($alpha) ? ($WHERE ? " AND" : " WHERE")." LEFT(login, 1)=".$this->eq($alpha) : ""); - $WHERE .= (isset($status) ? ($WHERE ? " AND" : " WHERE")." status=".$this->eq($status) : ""); - $LIMIT = ($env->config("max_list") ? " LIMIT ".$env->config("max_list")." OFFSET ".$start : ""); - $sql = "SELECT count(*) as n FROM(".$SELECT.$FROM.$WHERE.") res"; - $rst = $sgbd->query($sql); - if(!isset($rst)) return false; - if($v_rst = $sgbd->fetch_assoc($rst)) $users["total"] = $v_rst["n"]; - $sgbd->free_result($rst); - if($users["total"] > 0){ - $sql = "SELECT * FROM(".$SELECT.$FROM.$WHERE.$LIMIT.") res"; + try{ + $SELECT = "SELECT *"; + $FROM = " FROM #--users"; + $WHERE = ""; + $WHERE .= (isset($alpha) ? ($WHERE ? " AND" : " WHERE")." LEFT(login, 1)=".$this->eq($alpha) : ""); + $WHERE .= (isset($status) ? ($WHERE ? " AND" : " WHERE")." status=".$this->eq($status) : ""); + $LIMIT = ($env->config("max_list") ? " LIMIT ".$env->config("max_list")." OFFSET ".$start : ""); + $sql = "SELECT count(*) as n FROM(".$SELECT.$FROM.$WHERE.") res"; $rst = $sgbd->query($sql); - if(!isset($rst)) return false; - while($v_rst = $sgbd->fetch_assoc($rst)) $users["list"][$v_rst["id"]] = $v_rst; + if($v_rst = $sgbd->fetch_assoc($rst)) $users["total"] = $v_rst["n"]; $sgbd->free_result($rst); + if($users["total"] > 0){ + $sql = "SELECT * FROM(".$SELECT.$FROM.$WHERE.$LIMIT.") res"; + $rst = $sgbd->query($sql); + while($v_rst = $sgbd->fetch_assoc($rst)) $users["list"][$v_rst["id"]] = $v_rst; + $sgbd->free_result($rst); + } } + catch(Exception $e) { $users = false; } return $users; } function user_by_id($id){ $sgbd = $this->sgbd(); $user = array(); - $sql = "SELECT * from #--users WHERE id=".$this->eq($id); - $rst = $sgbd->query($sql); - if(!isset($rst)) return false; - if($v_rst = $sgbd->fetch_assoc($rst)) $user = $v_rst; - $sgbd->free_result($rst); + try{ + $sql = "SELECT * from #--users WHERE id=".$this->eq($id); + $rst = $sgbd->query($sql); + if($v_rst = $sgbd->fetch_assoc($rst)) $user = $v_rst; + $sgbd->free_result($rst); + } + catch(Exception $e) { $user = false; } return $user; } function user($login){ $sgbd = $this->sgbd(); $user = array(); - $sql = "SELECT * from #--users WHERE login=".$this->eq($login); - $rst = $sgbd->query($sql); - if(!isset($rst)) return false; - if($v_rst = $sgbd->fetch_assoc($rst)) $user = $v_rst; - $sgbd->free_result($rst); + try{ + $sql = "SELECT * from #--users WHERE login=".$this->eq($login); + $rst = $sgbd->query($sql); + if($v_rst = $sgbd->fetch_assoc($rst)) $user = $v_rst; + $sgbd->free_result($rst); + } + catch(Exception $e) { $user = false; } return $user; } function user_exists($login){ $sgbd = $this->sgbd(); $EXISTS = 0; - $sql = "SELECT count(*) as n from #--users WHERE login=".$this->eq($login); - $rst = $sgbd->query($sql); - if(!isset($rst)) return false; - if($v_rst = $sgbd->fetch_assoc($rst)) $EXISTS = $v_rst["n"]; - $sgbd->free_result($rst); + try{ + $sql = "SELECT count(*) as n from #--users WHERE login=".$this->eq($login); + $rst = $sgbd->query($sql); + if($v_rst = $sgbd->fetch_assoc($rst)) $EXISTS = $v_rst["n"]; + $sgbd->free_result($rst); + } + catch(Exception $e) { $EXISTS = false; } return $EXISTS; } function add_user($login, $password, $email, $status){ $sgbd = $this->sgbd(); - $sql = - "INSERT INTO #--users(login, password, email, status) VALUES" - ."( ".$this->eq($login) - .", ".$this->eq($password) - .", ".$this->eq($email) - .", ".$status - .")"; - return $sgbd->query($sql); + $user_id = false; + try{ + $sql = + "INSERT INTO #--users(login, password, email, status) VALUES" + ."( ".$this->eq($login) + .", ".$this->eq($password) + .", ".$this->eq($email) + .", ".$status + .")"; + $sgbd->query($sql); + $user_id = $sgbd->insert_id(); + } + catch(Exception $e) { $user_id = false; } + return $user_id; } function set_user($id, $login, $password, $email, $status){ $sgbd = $this->sgbd(); - $sql = - "UPDATE #--users SET" - ." login=".$this->eq($login) - .", password=".$this->eq($password) - .", email=".$this->eq($email) - .", status=".$status - ." WHERE id=".$id; - return $sgbd->query($sql); + try{ + $sql = + "UPDATE #--users SET" + ." login=".$this->eq($login) + .", password=".$this->eq($password) + .", email=".$this->eq($email) + .", status=".$status + ." WHERE id=".$id; + $sgbd->query($sql); + } + catch(Exception $e) { return false; } + return true; } function del_user($login){ $sgbd = $this->sgbd(); - $sql = "DELETE FROM #--users WHERE login=".$this->eq($login); - return $sgbd->query($sql); + try{ + $sql = "DELETE FROM #--users WHERE login=".$this->eq($login); + $sgbd->query($sql); + } + catch(Exception $e) { return false; } + return true; } # ---------------------------------------------------------------------------------------- @@ -111,11 +132,13 @@ function init_user_status($status = array()){ $sgbd = $this->sgbd(); $this->user_status = array(); - $sql = "SELECT * FROM #--user_status"; - $rst = $sgbd->query($sql); - if(!isset($rst)) return false; - while($v_rst = $sgbd->fetch_assoc($rst)) $this->user_status[$v_rst["id"]] = $v_rst; - $sgbd->free_result($rst); + try{ + $sql = "SELECT * FROM #--user_status"; + $rst = $sgbd->query($sql); + while($v_rst = $sgbd->fetch_assoc($rst)) $this->user_status[$v_rst["id"]] = $v_rst; + $sgbd->free_result($rst); + } + catch(Exception $e) { $this->user_status = false; } return $this->user_status; } @@ -123,11 +146,13 @@ if(!isset($this->user_status)) return false; $sgbd = $this->sgbd(); $this->action_status = array(); - $sql = "SELECT * FROM #--action_status"; - $rst = $sgbd->query($sql); - if(!isset($rst)) return false; - while($v_rst = $sgbd->fetch_assoc($rst)) $this->action_status[$v_rst["id"]] = $v_rst; - $sgbd->free_result($rst); + try{ + $sql = "SELECT * FROM #--action_status"; + $rst = $sgbd->query($sql); + while($v_rst = $sgbd->fetch_assoc($rst)) $this->action_status[$v_rst["id"]] = $v_rst; + $sgbd->free_result($rst); + } + catch(Exception $e) { $this->action_status = false; } return $this->action_status; } @@ -140,30 +165,34 @@ function get_action_status($mod, $controller = "index", $action = "index", $set_status = array()){ $sgbd = $this->sgbd(); $status = array(); - $sql = - "SELECT action, id_status" - ." FROM #--action_status" - ." WHERE action=".$this->eq($mod) - ." OR action=".$this->eq($mod."/".$controller) - ." OR action=".$this->eq($mod."/".$controller."/".$action); - $rst = $sgbd->query($sql); - if(!isset($rst)) return false; - while($v_rst = $sgbd->fetch_assoc($rst)){ - if(!isset($status[$v_rst["action"]])) $status[$v_rst["action"]] = array(); - $status[$v_rst["action"]][$v_rst["id_status"]] = true; + try{ + $sql = + "SELECT action, id_status" + ." FROM #--action_status" + ." WHERE action=".$this->eq($mod) + ." OR action=".$this->eq($mod."/".$controller) + ." OR action=".$this->eq($mod."/".$controller."/".$action); + $rst = $sgbd->query($sql); + while($v_rst = $sgbd->fetch_assoc($rst)){ + if(!isset($status[$v_rst["action"]])) $status[$v_rst["action"]] = array(); + $status[$v_rst["action"]][$v_rst["id_status"]] = true; + } + $sgbd->free_result($rst); } - $sgbd->free_result($rst); + catch(Exception $e) { $status = false; } return $status; } function creation_default_status(){ $sgbd = $this->sgbd(); $default_status = 0; - $sql = "SELECT id FROM #--user_status WHERE creation_default=1 LIMIT 0,1"; - $rst = $sgbd->query($sql); - if(!isset($rst)) return false; - if($v_rst = $sgbd->fetch_assoc($rst)) $default_status = $v_rst["id"]; - $sgbd->free_result($rst); + try{ + $sql = "SELECT id FROM #--user_status WHERE creation_default=1 LIMIT 0,1"; + $rst = $sgbd->query($sql); + if($v_rst = $sgbd->fetch_assoc($rst)) $default_status = $v_rst["id"]; + $sgbd->free_result($rst); + } + catch(Exception $e) { $default_status = false; } return $default_status; } -- 2.1.4