Public Member Functions | |
connect () | |
Establish a connection to database. | |
execute () | |
execute the generated Query | |
exportRaw () | |
Export the result array after executing the query. | |
getForeignKey () | |
Finds the Foreign keys from the tables. | |
getPrimaryKey () | |
Finds the Primary Key's of a Table. | |
disConnect () | |
List all Columns in the Table. | |
listCols ($table) | |
List all Columns in the Table. | |
Private Member Functions | |
__generate () | |
Generates the SQL Query depending upon the Lexims and If $flag is set to true returns it else(default) stores it into $this->sql;. |
For internal use only.
you dont need to know much about this class if you are not extending Zigmoyd. e.g. If you are just using Zigmoyd you dont need to do much hassle with this class.
Definition at line 32 of file mysql.php.
OrmDriver_mysql::connect | ( | ) |
Establish a connection to database.
Reimplemented from OrmDriver.
Definition at line 38 of file mysql.php.
References OrmDriver::connAlias(), and perror().
00038 { 00039 $hostStr = (isset($this->connData['port']) && strlen($this->connData['port'])>0) ? $this->connData['host'].':'.$this->connData['port'] : $this->connData['host']; 00040 if(!$this->conn = mysql_connect($this->connData['host'], $this->connData['user'], $this->connData['psw'])){ 00041 perror("<code>ZigOrm.Drivers.MySQL.Connection.Initialization</code><br />Unable to establish connection to database for ".$this->connAlias); 00042 return false; 00043 } 00044 if(!mysql_select_db($this->connData['database'], $this->conn)){ 00045 perror("<code>ZigOrm.Drivers.MySQL.Connection.Initialization</code><br />Couldn't select Database ".$this->connData['database']); 00046 return false; 00047 } 00048 return true; 00049 }
OrmDriver_mysql::__generate | ( | ) | [private] |
Generates the SQL Query depending upon the Lexims and If $flag is set to true returns it else(default) stores it into $this->sql;.
For internal use only.
When you are making a driver most of the changes you need to do is in this method.
$flag | bool |
Definition at line 56 of file mysql.php.
References $__ZigOrm, OrmDriver::qType(), and OrmDriver::whereLexToStr().
00056 { 00057 global $__ZigOrm; 00058 if(strlen($this->lex['where']) < 3 && is_array($this->lex['whereLex']) && count($this->lex['whereLex']) >= 1){ 00059 //Experimental Block 00060 //foreach($this->lex['whereLex'] as $i => $wObj){ 00061 // $this->lex['whereLex'][$i]->colName = $this->caller->realTableName.'.'.$wObj->colName; 00062 //} 00063 //Exp 00064 $this->lex['where'] = $this->whereLexToStr($this->lex['whereLex']); 00065 //var_dump($this->lex); 00066 } 00067 if($this->caller->isJoined){ 00068 if(is_array($this->caller->joiner->lex['whereLex']) && count($this->caller->joiner->lex['whereLex']) >= 1){ 00069 foreach($this->caller->joiner->lex['whereLex'] as $i => $wObj){ 00070 $this->caller->joiner->lex['whereLex'][$i]->colName = $this->caller->joiner->realTableName.'.'.$wObj->colName; 00071 } 00072 if(strlen($this->lex['where']) <= 3){ 00073 $this->lex['where'] = $this->whereLexToStr($this->caller->joiner->lex['whereLex']); 00074 }else{ 00075 $this->lex['where'] .= " and ".$this->whereLexToStr($this->caller->joiner->lex['whereLex']); 00076 } 00077 if(strlen($this->lex['where']) > 3){ 00078 foreach($this->caller->joiner->fk as $colAlias => $rObj){ 00079 if($rObj->table == $this->caller->realTableName){ 00080 $this->lex['where'] .= " and ".$this->caller->realTableName.".".$rObj->col." = ".$this->caller->joiner->realTableName.".".$colAlias." "; 00081 } 00082 } 00083 } 00084 //var_dump($this->lex); 00085 } 00086 } 00087 switch($this->qType){ 00088 case ZIGORM_Q_SELECT: 00089 //{ Grab the items to select 00090 if(!$this->lex['distinct']){ 00091 $select_str = "select\n\t"; 00092 }else{ 00093 $select_str = "select distinct\n\t"; 00094 } 00095 foreach($this->lex['select'] as $attr => $flag){ 00096 if($flag){ 00097 $select_str .= "`".$attr."` as ".$this->lex['aliasProperCaps'][$attr].", \n\t"; 00098 } 00099 } 00100 $select_str = rtrim($select_str, ", \n\t"); 00101 //} 00102 if($this->lex['table'])$select_str .= "\n\tfrom `".$this->lex['table']."`";//from table_name 00103 if($this->caller->isJoined && strlen($this->lex['where']) >= 3){ 00104 $select_str .= ", `".$this->caller->joiner->realTableName."`"; 00105 } 00106 if(strlen($this->lex['where']) >= 4)$select_str .= "\n\twhere\n\t\t".$this->lex['where'];//set the Where Query 00107 //{ Order 00108 //if(strlen($this->lex['order']) > 0)$select_str .= "\n\torder by\n\t\t".$this->lex['order'];//set The order By 00109 $oStr = 'order by '; 00110 foreach($this->lex['order'] as $i => $ord){ 00111 $oStr .= "`$ord[col]` $ord[way]"; 00112 if($i != count($this->lex['order'])-1)$oStr .= ','; 00113 } 00114 if(count($this->lex['order']) >= 1)$select_str .= "\n".$oStr; 00115 //} 00116 if(strlen($this->lex['limit']) > 0)$select_str .= "\n\tlimit\n\t\t".$this->lex['limit'];//Set the Limit 00117 if(strlen($this->lex['group']) > 0)$select_str .= "\n\tgroup by\n\t\t".$this->lex['group'];//set The Group By 00118 if(strlen($this->lex['having']) > 0)$select_str .= "\n\thaving\n\t\t".$this->lex['having'];//set The Having 00119 $ret_sql = $select_str; 00120 break; 00121 case ZIGORM_Q_JOIN: 00122 $select_str = ($this->lex['distinct']) ? "select distinct\n\t" : "select\n\t"; 00123 //{ Fetch the select Cols of the Currssnt Table 00124 foreach($this->lex['select'] as $attr => $flag){ 00125 if($flag){ 00126 $select_str .= $this->lex['table'].'.'.$attr." as ".$this->lex['aliasProperCaps'][$attr].", \n\t"; 00127 } 00128 } 00129 //} 00130 //{ Fetch the select Cols of all foreign tables 00131 if(is_array($this->caller->fk)){ 00132 foreach($this->caller->fk as $srcCol => $fkObj){ 00133 $fkRes =& $this->caller->{$__ZigOrm->dataDict->tables[$fkObj->table]}; 00134 if(is_array($fkRes->lex['select'])){ 00135 foreach($fkRes->lex['select'] as $colName => $selFlag){ 00136 if($selFlag){ 00137 $select_str .= "\n\t".$fkRes->lex['table'].".".$colName." as ".$fkRes->lex['aliasProperCaps'][$colName].", "; 00138 } 00139 } 00140 } 00141 } 00142 } 00143 //} 00144 $localWhereStr = ''; 00145 //{ Build the Where Query for this table 00146 if(is_array($this->lex['whereLex'])){ 00147 foreach($this->lex['whereLex'] as $i => $whereObj){ 00148 if(!strlen($whereObj->cnj))$whereObj->cnj = 'and '; 00149 if(strlen(trim($localWhereStr)) < 4)$whereObj->cnj = ""; 00150 $localWhereStr .= $whereObj->cnj.' '.$this->lex['table'].".$whereObj->colName $whereObj->op '$whereObj->value'\n\t"; 00151 } 00152 } 00153 //} 00154 //{ Local Order 00155 $localOrderStr = ''; 00156 if(is_array($this->lex['order']) && count($this->lex['order'])){ 00157 foreach($this->lex['order'] as $i => $ord){ 00158 $localOrderStr .= $this->lex['table'].".$ord[col] $ord[way]"; 00159 if($i != count($this->lex['order'])-1)$localOrderStr .= ','; 00160 } 00161 } 00162 //} 00163 $formStr = ''; 00164 //{ Build the Where Query for Foreign table and build the form String and order Query Too 00165 $foreignWhereStr = ''; 00166 if(is_array($this->caller->fk)){ 00167 foreach($this->caller->fk as $srcCol => $fkObj){ 00168 $formStr .= $fkObj->table.",\n\t"; 00169 $fkRes =& $this->caller->{$__ZigOrm->dataDict->tables[$fkObj->table]}; 00170 if(is_array($fkRes->lex['whereLex'])){ 00171 foreach($fkRes->lex['whereLex'] as $i => $whereObj){ 00172 if(!strlen($whereObj->cnj))$whereObj->cnj = 'and '; 00173 if(strlen(trim($foreignWhereStr)) < 4)$whereObj->cnj = ""; 00174 $foreignWhereStr .= $whereObj->cnj." ".$fkObj->table.".$whereObj->colName $whereObj->op '$whereObj->value'\n\t"; 00175 } 00176 } 00177 //{{ Foreign Order 00178 $foreignOrderStr = ''; 00179 if(is_array($fkRes->lex['order']) && count($fkRes->lex['order'])){ 00180 foreach($fkRes->lex['order'] as $i => $ord){ 00181 $foreignOrderStr .= $fkObj->table.".$ord[col] $ord[way]"; 00182 if($i != count($fkRes->lex['order'])-1)$foreignOrderStr .= ','; 00183 } 00184 } 00185 //}} 00186 } 00187 } 00188 //} 00189 switch($this->lex['join']['type']){ 00190 case ZIGORM_Q_AUTOJOIN: 00191 //{ Add the Join on to the where Syntax 00192 $joinWhereStr = ''; 00193 foreach($this->lex['join']['on'] as $tableName => $colrelR){ 00194 if(is_array($colrelR)){ 00195 foreach($colrelR as $localCol => $foreignCol){ 00196 if(strlen(trim($joinWhereStr)) > 4)$conj = 'and'; else $conj = ''; 00197 $joinWhereStr .= "$conj ".$this->lex['table']."."."$localCol = $tableName.$foreignCol\n\t\t"; 00198 } 00199 } 00200 } 00201 //} 00202 //{ Prepare the ultimate Where Query 00203 if(strlen(trim($localWhereStr)) > 2)$Where[0] = "(\n\t\t".trim($localWhereStr)."\n\t)"; 00204 if(strlen(trim($foreignWhereStr)) > 2)$Where[1] = "(\n\t\t".trim($foreignWhereStr)."\n\t)"; 00205 if(strlen(trim($joinWhereStr)) > 2)$Where[2] = "(\n\t\t".trim($joinWhereStr)."\n\t)"; 00206 $whereStr = implode("and", $Where); 00207 //} 00208 //{ Order Str 00209 $orderStr = ''; 00210 if(strlen($localOrderStr))$orderStr = $localOrderStr; 00211 if(strlen($foreignOrderStr))$orderStr .= ",$foreignOrderStr"; 00212 if(strlen($orderStr) >= 2)$orderStr = "order by $orderStr"; 00213 //} 00214 $formStr = "\n\t".$this->lex['table'].",\n\t".trim($formStr, " ,\n"); 00215 $ret_sql = trim($select_str, "\n\r\t ,")."\nfrom\n\t".trim($formStr, "\n\t, ")."\nwhere\n\t".$whereStr."\n$orderStr"; 00216 break; 00217 case ZIGORM_JOIN_INNER: 00218 case ZIGORM_JOIN_LEFT: 00219 case ZIGORM_JOIN_RIGHT: 00220 //{ Reconstruct teh Form Query 00221 $formStr = $this->lex['table']; 00222 //} 00223 //{ make the join Query 00224 switch($this->lex['join']['type']){ 00225 case ZIGORM_JOIN_INNER: 00226 $joinClause = 'inner join'; 00227 break; 00228 case ZIGORM_JOIN_LEFT: 00229 $joinClause = 'left join'; 00230 break; 00231 case ZIGORM_JOIN_RIGHT: 00232 $joinClause = 'right join'; 00233 break; 00234 } 00235 $joinWhereStr = ''; 00236 foreach($this->lex['join']['on'] as $tableName => $colrelR){ 00237 foreach($colrelR as $localCol => $foreignCol){ 00238 $joinWhereStr .= "$joinClause $tableName on\n\t".$this->lex['table'].".$localCol = $tableName.$foreignCol\n"; 00239 } 00240 } 00241 //} 00242 $whereStr = trim($joinWhereStr); 00243 $tmpWhere = ''; 00244 if(strlen(trim($localWhereStr)))$tmpWhere .= trim($localWhereStr); 00245 if(strlen(trim($foreignWhereStr))){ 00246 if(!strlen(trim($tmpWhere))){ 00247 $tmpWhere .= "(".trim($foreignWhereStr).")"; 00248 }else{ 00249 $tmpWhere .= "and (".trim($foreignWhereStr).")"; 00250 } 00251 } 00252 if(strlen(trim($tmpWhere)))$tmpWhere = "\nwhere\t".$tmpWhere; 00253 $whereStr .= "\n".$tmpWhere; 00254 //} 00255 $ret_sql = trim($select_str, "\n\r\t ,")."\nfrom\n\t".trim($formStr, "\n\t, ")."\n".trim($whereStr); 00256 //var_dump($ret_sql); 00257 break; 00258 case ZIGORM_JOIN_CROSS: 00259 //{ Reconstruct teh Form Query 00260 $formStr = $this->lex['table']; 00261 //} 00262 //{ make the join Query 00263 $joinWhereStr = 'cross join '; 00264 foreach($this->lex['join']['on'] as $tableName => $colrelR){ 00265 $joinWhereStr .= $tableName."\n"; 00266 } 00267 //} 00268 //{ Prepare the ultimate Where Query 00269 if(strlen(trim($localWhereStr)) > 2)$Where[0] = "(\n\t\t".trim($localWhereStr)."\n\t)"; 00270 if(strlen(trim($foreignWhereStr)) > 2)$Where[1] = "(\n\t\t".trim($foreignWhereStr)."\n\t)"; 00271 if(strlen(trim($joinWhereStr)) > 2)$Where[2] = "\n".trim($joinWhereStr)."\n\t"; 00272 $whereStr = implode("and", $Where); 00273 //} 00274 $ret_sql = $select_str."\nfrom\n\t".trim($formStr, "\n\t, ").$whereStr; 00275 break; 00276 case ZIGORM_JOIN_NATURAL: 00277 //{ Reconstruct teh Form Query 00278 $formStr = $this->lex['table']; 00279 //} 00280 //{ make the join Query 00281 $joinWhereStr = 'natural join '; 00282 foreach($this->lex['join']['on'] as $tableName => $colrelR){ 00283 $joinWhereStr .= $tableName."\n"; 00284 } 00285 //} 00286 //{ Prepare the ultimate Where Query 00287 if(strlen(trim($localWhereStr)) > 2)$Where[0] = "(\n\t\t".trim($localWhereStr)."\n\t)"; 00288 if(strlen(trim($foreignWhereStr)) > 2)$Where[1] = "(\n\t\t".trim($foreignWhereStr)."\n\t)"; 00289 if(strlen(trim($joinWhereStr)) > 2)$Where[2] = "\n".trim($joinWhereStr)."\n\t"; 00290 $whereStr = implode("and", $Where); 00291 //} 00292 $ret_sql = $select_str."\nfrom\n\t".trim($formStr, "\n\t, ").$whereStr; 00293 break; 00294 default: 00295 perror('<code>ZigOrm.Drivers.MySQL</code><br />Invalid join Type '.$this->lex['join']['type']); 00296 exit; 00297 } 00298 break; 00299 case ZIGORM_Q_INSERT: 00300 //{ Grab The attributes and their Values 00301 $insert_str = "insert into `".$this->lex['table']."`("; 00302 foreach($this->lex['attributes'] as $attr => $value){ 00303 if(strlen($value) > 0){ 00304 $val[] = $value; 00305 $insert_str .= "`".$attr."`, "; 00306 } 00307 } 00308 $insert_str = rtrim($insert_str, ', '); 00309 $insert_str .= ")\nvalues("; 00310 if(isset($val) && is_array($val) && count($val) >= 1){ 00311 foreach($val as $value){ 00312 $insert_str .= "".$value.", "; 00313 } 00314 }else{ 00315 perror('<code>ZigOrm.Drivers.MySQL.Query.Insert</code><br />Nothing to Insert'); 00316 } 00317 $insert_str = rtrim($insert_str, ', '); 00318 $insert_str .= ')'; 00319 $ret_sql = $insert_str; 00320 //} 00321 break; 00322 case ZIGORM_Q_UPDATE: 00323 $update_str = 'update '.$this->lex['table']."\n\tset\n\t"; 00324 //{ Grab the Attributes and their Values 00325 foreach($this->lex['attributes'] as $attr => $value){ 00326 if(strlen($value) > 0){ 00327 $update_str .= '`'.$attr."` = $value,\n\t"; 00328 } 00329 } 00330 $update_str = rtrim($update_str, ",\n\t"); 00331 //} 00332 if(strlen($this->lex['where']) >= 4)$update_str .= "\n\t where ".$this->lex['where'];//set the Where Query 00333 if(strlen($this->lex['limit']) > 0)$update_str .= "\n\t limit ".$this->lex['limit'];//Set the Limit 00334 $oStr = 'order by '; 00335 foreach($this->lex['order'] as $i => $ord){ 00336 $oStr .= "`$ord[col]` $ord[way]"; 00337 if($i != count($this->lex['order'])-1)$oStr .= ','; 00338 } 00339 if(count($this->lex['order']) >= 1)$update_str .= "\n".$oStr; 00340 $ret_sql = $update_str; 00341 break; 00342 case ZIGORM_Q_REMOVE: 00343 $remove_str = "delete from ".$this->lex['table']."\n\t"; 00344 if(strlen($this->lex['where']) >= 4)$remove_str .= "\n\t where ".$this->lex['where'];//set the Where Query 00345 if(strlen($this->lex['limit']) > 0)$remove_str .= "\n\t limit ".$this->lex['limit'];//Set the Limit 00346 $oStr = 'order by '; 00347 foreach($this->lex['order'] as $i => $ord){ 00348 $oStr .= "`$ord[col]` $ord[way]"; 00349 if($i != count($this->lex['order'])-1)$oStr .= ','; 00350 } 00351 if(count($this->lex['order']) >= 1)$remove_str .= "\n".$oStr; 00352 $ret_sql = $remove_str; 00353 break; 00354 case ZIGORM_Q_TRUNCATE: 00355 $ret_sql = "truncate table `".$this->lex['table']."`\n\t"; 00356 break; 00357 case ZIGORM_Q_SQL: 00358 $ret_sql = $this->sql; 00359 break; 00360 default: 00361 perror('<code>ZigOrm.Drivers.MySQL</code><br />Invalid Query Type '.$this->qType); 00362 exit; 00363 } 00364 if(!isset($ret_sql))return false; 00365 $this->sql = $ret_sql; 00366 //echo $this->sql; 00367 }
OrmDriver_mysql::execute | ( | ) |
execute the generated Query
Reimplemented from OrmDriver.
Definition at line 373 of file mysql.php.
References zLogger::debug(), and perror().
00373 { 00374 $this->__generate(); 00375 zLogger::debug("Executing Query (Base64Encoded) `".base64_encode($this->sql)."`", "orm.driver.MySql"); 00376 if(!$this->res = mysql_query($this->sql, $this->conn)){ 00377 perror('<code>ZigOrm.Drivers.MySQL.Execute</code><br />Failed to Execute Query<pre style="background-color: #FFFFFF;font-weight: normal;border: 1px dashed #B52D1E;padding-left: 2px;margin-left: 2px;">'.trim($this->sql).'</pre>MySQl Error : <code style="background-color: #FFFFFF;font-weight: normal;">'.mysql_error().'</code><br />MySQL Error No. : <code style="background-color: #FFFFFF;font-weight: normal;">'.mysql_errno().'</code>'); 00378 return false; 00379 } 00380 $this->numRows = @mysql_num_rows($this->res); 00381 $this->affected_rows = mysql_affected_rows($this->conn); 00382 return true; 00383 }
OrmDriver_mysql::exportRaw | ( | ) |
Export the result array after executing the query.
Reimplemented from OrmDriver.
Definition at line 389 of file mysql.php.
References $app_data, and $val.
00389 { 00390 global $app_data; 00391 //$modelClassName = get_class($this->caller).'RowSet'; 00392 $modelClassName = 'stdClass'; 00393 if($this->numRows > 0){ 00394 $record = array(); 00395 while($row = mysql_fetch_array($this->res, MYSQL_ASSOC)){ 00396 $i = count($record); 00397 foreach($row as $col => $val){ 00398 if(!isset($record[$i]) || !is_object($record[$i])){ 00399 $record[$i] = new $modelClassName; 00400 $record[$i]->colList = array(); 00401 //$record[$i]->handle =& $this; 00402 } 00403 $record[$i]->colList[$col] = $val; 00404 } 00405 } 00406 return $record; 00407 } 00408 }
OrmDriver_mysql::getForeignKey | ( | ) |
Finds the Foreign keys from the tables.
Definition at line 414 of file mysql.php.
References zLogger::debug(), and perror().
00414 { 00415 zLogger::debug("Finding Foreign Key of Table ".$this->lex['table']." On Database ".$this->connData['database'], 'orm.driver.MySql'); 00416 $sql = "SELECT 00417 u.column_name as src_col, 00418 u.referenced_table_name as ref_table, 00419 u.referenced_column_name as ref_col 00420 FROM information_schema.table_constraints AS c 00421 INNER JOIN information_schema.key_column_usage AS u USING( constraint_schema, constraint_name ) 00422 WHERE 00423 c.constraint_type = 'FOREIGN KEY' AND 00424 c.table_schema='".$this->connData['database']."' AND 00425 c.table_name='".$this->lex['table']."'"; 00426 if(!$res = mysql_query($sql, $this->conn)){ 00427 perror('<code>Zigmoyd.orm.Drivers.MySQL.getForeignKey</code><br />Failed to get Foreign key of Table '.$this->lex['table'].'<br />MySQl Error : <code style="background-color: #FFFFFF;font-weight: normal;">'.mysql_error().'</code><br />MySQL Error No. : <code style="background-color: #FFFFFF;font-weight: normal;">'.mysql_errno().'</code>'); 00428 return false; 00429 } 00430 if(@mysql_num_rows($res) <= 0){ 00431 return false; 00432 } 00433 $record = array(); 00434 while($row = mysql_fetch_object($res)){ 00435 $record[] = $row; 00436 } 00437 return $record; 00438 }
OrmDriver_mysql::getPrimaryKey | ( | ) |
Finds the Primary Key's of a Table.
Definition at line 444 of file mysql.php.
References zLogger::debug(), and perror().
00444 { 00445 zLogger::debug("Finding Primary Key of Table ".$this->lex['table']." On Database ".$this->connData['database'], 'orm.driver.MySql'); 00446 /*$sql = "select 00447 `column_name` 00448 from 00449 information_schema.KEY_COLUMN_USAGE 00450 where 00451 `table_schema` = '".$this->connData['database']."' and 00452 `table_name` = '".$this->lex['table']."' and 00453 `constraint_name` = 'primary'";*/ 00454 $sql = "desc ".$this->lex['table']; 00455 if(!$res = mysql_query($sql, $this->conn)){ 00456 perror('<code>Zigmoyd.orm.Drivers.MySQL.getPrimaryKey</code><br />Failed to get Foreign key of Table '.$this->lex['table'].'<br />MySQl Error : <code style="background-color: #FFFFFF;font-weight: normal;">'.mysql_error().'</code><br />MySQL Error No. : <code style="background-color: #FFFFFF;font-weight: normal;">'.mysql_errno().'</code>'); 00457 return false; 00458 } 00459 if(@mysql_num_rows($res) <= 0){ 00460 return false; 00461 } 00462 $record = array(); 00463 while($row = mysql_fetch_object($res)){ 00464 if($row->Key == "PRI"){ 00465 $record[] = $row->Field; 00466 } 00467 } 00468 return $record; 00469 }
OrmDriver_mysql::disConnect | ( | ) |
OrmDriver_mysql::listCols | ( | $ | table | ) |
List all Columns in the Table.
Definition at line 483 of file mysql.php.
References $__ZigOrm.
00483 { 00484 global $__ZigOrm; 00485 $colRes = mysql_list_fields($this->connData['database'], $table, $this->conn); 00486 if(!$colRes)return false; 00487 $cols = array(); 00488 $i = 0; 00489 while($i < mysql_num_fields($colRes)){ 00490 $cols[] = mysql_field_name($colRes, $i); 00491 ++$i; 00492 } 00493 if(count($cols) <= 0)return false; 00494 return $cols; 00495 }