Viewing file:  class.mysqlAccessDriver.php (21.48 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
/**
* @package info.ajaxplorer.plugins
*
* Copyright 2007-2009 Charles du Jeu
* This file is part of AjaXplorer.
* The latest code can be found at http://www.ajaxplorer.info/
*
* This program is published under the LGPL Gnu Lesser General Public License.
* You should have received a copy of the license along with AjaXplorer.
*
* The main conditions are as follow :
* You must conspicuously and appropriately publish on each copy distributed
* an appropriate copyright notice and disclaimer of warranty and keep intact
* all the notices that refer to this License and to the absence of any warranty;
* and give any other recipients of the Program a copy of the GNU Lesser General
* Public License along with the Program.
*
* If you modify your copy or copies of the library or any portion of it, you may
* distribute the resulting library provided you do so under the GNU Lesser
* General Public License. However, programs that link to the library may be
* licensed under terms of your choice, so long as the library itself can be changed.
* Any translation of the GNU Lesser General Public License must be accompanied by the
* GNU Lesser General Public License.
*
* If you copy or distribute the program, you must accompany it with the complete
* corresponding machine-readable source code or with a written offer, valid for at
* least three years, to furnish the complete corresponding machine-readable source code.
*
* Any of the above conditions can be waived if you get permission from the copyright holder.
* AjaXplorer is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
* without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
*
* Description : Access a MySQL database and use AjaXplorer as a phpMyAdmin.
* @todo Put the DB content encoding as a driver option, then manually for current encoding
* in SystemTextEncoding.
*/
class mysqlAccessDriver extends AbstractAccessDriver
{
/** The user name */
var $user;
/** The user password */
var $password;
function mysqlAccessDriver($driverName, $filePath, $repository, $optOptions = NULL)
{
$this->user = $optOptions ? $optOptions["user"] : $repository->getOption("DB_USER");
$this->password = $optOptions ? $optOptions["password"] : $repository->getOption("DB_PASS");
parent::AbstractAccessDriver($driverName, $filePath, $repository);
}
function initRepository(){
$link = $this->createDbLink();
$this->closeDbLink($link);
}
function createDbLink(){
$link = FALSE;
//Connects to the MySQL Database.
$host = $this->repository->getOption("DB_HOST");
$dbname = $this->repository->getOption("DB_NAME");
$link = @mysql_connect($host, $this->user, $this->pass);
if(!$link) {
$ajxpExp = new AJXP_Exception("Cannot connect to server!");
AJXP_Exception::errorToXml($ajxpExp);
}
if(!@mysql_select_db($dbname, $link)){
$ajxpExp = new AJXP_Exception("Cannot find database!");
AJXP_Exception::errorToXml($ajxpExp);
}
return $link;
}
function closeDbLink($link){
if(!mysql_close($link)){
return new AJXP_Exception("Cannot close connection!");
}
}
function switchAction($action, $httpVars, $fileVars){
if(!isSet($this->actions[$action])) return;
$xmlBuffer = "";
foreach($httpVars as $getName=>$getValue){
$$getName = Utils::securePath($getValue);
}
$selection = new UserSelection();
$selection->initFromHttpVars($httpVars);
if(isSet($dir) && $action != "upload") {
$safeDir = $dir;
$dir = SystemTextEncoding::fromUTF8($dir);
}
// FILTER DIR PAGINATION ANCHOR
if(isSet($dir) && strstr($dir, "#")!==false){
$parts = split("#", $dir);
$dir = $parts[0];
$page = $parts[1];
}
if(isSet($dest)) {
$dest = SystemTextEncoding::fromUTF8($dest);
}
$mess = ConfService::getMessages();
switch($action)
{
//------------------------------------
// ONLINE EDIT
//------------------------------------
case "edit_record";
$isNew = false;
if(isSet($record_is_new) && $record_is_new == "true") $isNew = true;
$tableName = $_POST["table_name"];
$pkName = $_POST["pk_name"];
$query = "";
$arrValues = array();
foreach ($_POST as $key=>$value){
if(substr($key, 0, strlen("ajxp_mysql_")) == "ajxp_mysql_"){
$newKey = substr($key, strlen("ajxp_mysql_"));
$arrValues[$newKey] = $value;
}
}
if($isNew){
$string = "";
$index = 0;
foreach ($arrValues as $k=>$v){
// CHECK IF AUTO KEY!!!
$string .= "'".addslashes(SystemTextEncoding::fromUTF8($v))."'";
if($index < count($arrValues)-1) $string.=",";
$index++;
}
$query = "INSERT INTO $tableName VALUES ($string)";
}else{
$string = "";
$index = 0;
foreach ($arrValues as $k=>$v){
if($k == $pkName){
$pkValue = $v;
}else{
$string .= $k."='".addslashes(SystemTextEncoding::fromUTF8($v))."'";
if($index<count($arrValues)-1) $string.=",";
}
$index++;
}
$query = "UPDATE $tableName SET $string WHERE $pkName='$pkValue'";
}
$link = $this->createDbLink();
$res = $this->execQuery($query);
$this->closeDbLink($link);
if(is_a($res, "AJXP_Exception")){
$errorMessage = $res->messageId;
}else{
$logMessage = $query;
$reload_file_list = true;
}
break;
//------------------------------------
// CHANGE COLUMNS OR CREATE TABLE
//------------------------------------
case "edit_table":
$link = $this->createDbLink();
if(isSet($httpVars["current_table"])){
if(isSet($httpVars["delete_column"])){
$query = "ALTER TABLE ".$httpVars["current_table"]." DROP COLUMN ".$httpVars["delete_column"];
$res = $this->execQuery($query);
if(is_a($res, "AJXP_Exception")){
$errorMessage = $res->messageId;
}else{
$logMessage = $query;
$reload_file_list = true;
}
$this->closeDbLink($link);
break;
}
if(isSet($httpVars["add_column"])){
$defString = $this->makeColumnDef($httpVars, "add_field_");
$query = "ALTER TABLE ".$httpVars["current_table"]." ADD COLUMN ($defString)";
if(isSet($httpVars["add_field_pk"]) && $httpVars["add_field_pk"]=="1"){
$query.= ", ADD PRIMARY KEY (".$httpVars["add_field_name"].")";
}
if(isSet($httpVars["add_field_index"]) && $httpVars["add_field_index"]=="1"){
$query.= ", ADD INDEX (".$httpVars["add_field_name"].")";
}
if(isSet($httpVars["add_field_uniq"]) && $httpVars["add_field_uniq"]=="1"){
$query.= ", ADD UNIQUE (".$httpVars["add_field_name"].")";
}
$res = $this->execQuery($query);
if(is_a($res, "AJXP_Exception")){
$errorMessage = $res->messageId;
}else{
$logMessage = $query;
$reload_file_list = true;
}
$this->closeDbLink($link);
break;
}
}
$fields = array("origname","name", "default", "null", "size", "type", "flags", "pk", "index", "uniq");
$rows = array();
foreach ($httpVars as $k=>$val){
$split = split("_", $k);
if(count($split) == 3 && $split[0]=="field" && is_numeric($split[2]) && in_array($split[1], $fields)){
if(!isSet($rows[intval($split[2])])) $rows[intval($split[2])] = array();
$rows[intval($split[2])][$split[1]] = $val;
}
}
if(isSet($current_table)){
$qMessage = '';
foreach ($rows as $row){
$sizeString = ($row["size"]!=""?"(".$row["size"].")":"");
$defString = ($row["default"]!=""?" DEFAULT ".$row["default"]."":"");
$query = "ALTER TABLE $current_table CHANGE ".$row["origname"]." ".$row["name"]." ".$row["type"].$sizeString.$defString." ".$row["null"];
$res = $this->execQuery(trim($query));
if(is_a($res, "AJXP_Exception")){
$errorMessage = $res->messageId;
$this->closeDbLink($link);
break;
}else{
$qMessage .= $query;
$reload_file_list = true;
}
}
$logMessage = $qMessage;
}else if(isSet($new_table)){
$fieldsDef = "";
$pks = array();
$indexes = array();
$uniqs = array();
foreach ($rows as $index=>$row){
$fieldsDef .= $this->makeColumnDef($row);
// Analyse keys
if($row["pk"] == "1")$pks[] = $row["name"];
if($row["index"]=="1") $indexes[] = $row["name"];
if($row["uniq"]=="1") $uniqs[] = $row["name"];
if($index < count($rows)-1){
$fieldsDef.=",";
}
}
if(count($pks)){
$fieldsDef.= ",PRIMARY KEY (".join(",", $pks).")";
}
if(count($indexes)){
$fieldsDef.=",INDEX (".join(",", $indexes).")";
}
if(count($uniqs)){
$fieldsDef.=",UNIQUE (".join(",", $uniqs).")";
}
$query = "CREATE TABLE $new_table ($fieldsDef)";
$res = $this->execQuery((trim($query)));
if(is_a($res, "AJXP_Exception")){
$errorMessage = $res->messageId;
}else{
$logMessage = $query;
$reload_file_list = true;
$reload_current_node = true;
}
}
$this->closeDbLink($link);
break;
//------------------------------------
// SUPPRIMER / DELETE
//------------------------------------
case "delete_table":
case "delete_record":
$dir = basename($dir);
$link = $this->createDbLink();
if(trim($dir) == ""){
// ROOT NODE => DROP TABLES
$tables = $selection->getFiles();
$query = "DROP TABLE";
foreach ($tables as $index => $tableName){
$tables[$index] = basename($tableName);
}
$query.= " ".join(",", $tables);
$res = $this->execQuery($query);
$reload_current_node = true;
}else{
// TABLE NODE => DELETE RECORDS
$tableName = $dir;
$pks = $selection->getFiles();
foreach ($pks as $key => $pkString){
$parts = split("\.", $pkString);
array_pop($parts); // remove .pk extension
array_shift($parts); // remove record prefix
foreach ($parts as $index => $pkPart){
$parts[$index] = str_replace("__", "='", $pkPart)."'";
}
$pks[$key] = "(".implode(" AND ", $parts).")";
}
$query = "DELETE FROM $tableName WHERE ". implode(" OR ", $pks);
$res = $this->execQuery($query);
}
AJXP_Exception::errorToXml($res);
if(is_a($res, "AJXP_Exception")){
$errorMessage = $res->messageId;
}else{
$logMessage = $query;
$reload_file_list = true;
}
$this->closeDbLink($link);
break;
//------------------------------------
// RENOMMER / RENAME
//------------------------------------
case "set_query":
$query = $httpVars["query"];
$_SESSION["LAST_SQL_QUERY"] = $query;
print("<tree store=\"true\"></tree>");
break;
//------------------------------------
// XML LISTING
//------------------------------------
case "ls":
if(!isSet($dir) || $dir == "/") $dir = "";
$searchMode = $fileListMode = $completeMode = false;
if(isSet($mode)){
if($mode == "search") $searchMode = true;
else if($mode == "file_list") $fileListMode = true;
else if($mode == "complete") $completeMode = true;
}
$link = $this->createDbLink();
AJXP_Exception::errorToXml($link);
if($dir == ""){
AJXP_XMLWriter::header();
$tables = $this->listTables();
print '<columns switchDisplayMode="list" switchGridMode="filelist"><column messageString="Table Name" attributeName="ajxp_label" sortType="String"/><column messageString="Byte Size" attributeName="bytesize" sortType="NumberKo"/><column messageString="Count" attributeName="count" sortType="Number"/></columns>';
$icon = ($mode == "file_list"?"sql_images/mimes/ICON_SIZE/table_empty.png":"sql_images/mimes/ICON_SIZE/table_empty_tree.png");
foreach ($tables as $tableName){
$size = $this->getSize($tableName);
$count = $this->getCount($tableName);
print "<tree is_file=\"0\" text=\"$tableName\" filename=\"/$tableName\" bytesize=\"$size\" count=\"$count\" icon=\"$icon\" ajxp_mime=\"table\" />";
}
print "<tree is_file=\"0\" text=\"Search Results\" ajxp_node=\"true\" filename=\"/ajxpmysqldriver_searchresults\" bytesize=\"-\" count=\"-\" icon=\"".($mode == "file_list"?"search.png":CLIENT_RESOURCES_FOLDER."/images/crystal/mimes/16/search.png")."\"/>";
AJXP_XMLWriter::close();
}else{
$tableName = basename($dir);
if(isSet($page))$currentPage = $page;
else $currentPage = 1;
$query = "SELECT * FROM $tableName";
$searchQuery = false;
if($tableName == "ajxpmysqldriver_searchresults"){
if(isSet($_SESSION["LAST_SQL_QUERY"])){
$query = $_SESSION["LAST_SQL_QUERY"];
$matches = array();
if(preg_match("/SELECT [\S, ]* FROM (\S*).*/i", $query, $matches)!==false){
$tableName = $matches[1];
$searchQuery = true;
}else{
break;
}
}else{
break;
}
}
if(isSet($order_column)){
$query .= " ORDER BY $order_column ".strtoupper($order_direction);
if(!isSet($_SESSION["AJXP_ORDER_DATA"])){
$_SESSION["AJXP_ORDER_DATA"] = array();
}
$_SESSION["AJXP_ORDER_DATA"][$this->repository->getUniqueId()."_".$tableName] = array("column" => $order_column, "dir" => $order_direction);
}else if(isSet($_SESSION["AJXP_ORDER_DATA"])){
if(isSet($_SESSION["AJXP_ORDER_DATA"][$this->repository->getUniqueId()."_".$tableName])){
$order_column = $_SESSION["AJXP_ORDER_DATA"][$this->repository->getUniqueId()."_".$tableName]["column"];
$order_direction = $_SESSION["AJXP_ORDER_DATA"][$this->repository->getUniqueId()."_".$tableName]["dir"];
$query .= " ORDER BY $order_column ".strtoupper($order_direction);
}
}
$result = $this->showRecords($query, $tableName, $currentPage);
if($searchQuery && is_a($result, "AJXP_Exception")){
unset($_SESSION["LAST_SQL_QUERY"]); // Do not store wrong query!
}
AJXP_Exception::errorToXml($result);
AJXP_XMLWriter::header();
$blobCols = array();
print '<columns switchDisplayMode="list" switchGridMode="grid">';
foreach ($result["COLUMNS"] as $col){
print "<column messageString=\"".$col["NAME"]."\" attributeName=\"".$col["NAME"]."\" field_name=\"".$col["NAME"]."\" field_type=\"".$col["TYPE"]."\" field_size=\"".$col["LENGTH"]."\" field_flags=\"".$this->cleanFlagString($col["FLAGS"])."\" field_pk=\"".(eregi("primary", $col["FLAGS"])?"1":"0")."\" field_null=\"".(eregi("not_null", $col["FLAGS"])?"NOT_NULL":"NULL")."\" sortType=\"".$this->sqlTypeToSortType($col["TYPE"])."\" field_default=\"".$col["DEFAULT"]."\"/>";
if(stristr($col["TYPE"],"blob")!==false && ($col["FLAGS"]!="" && stristr($col["FLAGS"], "binary"))){
$blobCols[]=$col["NAME"];
}
}
print '</columns>';
print '<pagination total="'.$result["TOTAL_PAGES"].'" current="'.$currentPage.'" remote_order="true" currentOrderCol="'.$order_column.'" currentOrderDir="'.$order_direction.'"/>';
foreach ($result["ROWS"] as $row){
print '<tree ';
$pkString = "";
foreach ($row as $key=>$value){
if(in_array($key, $blobCols)){
$sizeStr = "-NULL";
if(strlen($value)) $sizeStr = "-".Utils::roundSize(strlen($sizeStr));
print "$key=\"BLOB$sizeStr\" ";
}else{
$value = str_replace("\"", "", $value);
$value = Utils::xmlEntities($value);
print $key.'="'.SystemTextEncoding::toUTF8($value).'" ';
if($result["HAS_PK"]>0){
if(in_array($key, $result["PK_FIELDS"])){
$pkString .= $key."__".$value.".";
}
}
}
}
if($result["HAS_PK"] > 0){
print 'filename="record.'.$pkString.'pk" ';
print 'is_file="1" ajxp_mime="pk"/>';
}else{
print 'filename="record.no_pk" ';
print 'is_file="1" ajxp_mime="row"/>';
}
}
AJXP_XMLWriter::close();
}
$this->closeDbLink($link);
exit(1);
break;
}
if(isset($logMessage) || isset($errorMessage))
{
$xmlBuffer .= AJXP_XMLWriter::sendMessage((isSet($logMessage)?$logMessage:null), (isSet($errorMessage)?$errorMessage:null), false);
}
if(isset($requireAuth))
{
$xmlBuffer .= AJXP_XMLWriter::requireAuth(false);
}
if(isset($reload_current_node) && $reload_current_node == "true")
{
$xmlBuffer .= AJXP_XMLWriter::reloadCurrentNode(false);
}
if(isset($reload_dest_node) && $reload_dest_node != "")
{
$xmlBuffer .= AJXP_XMLWriter::reloadNode($reload_dest_node, false);
}
if(isset($reload_file_list))
{
$xmlBuffer .= AJXP_XMLWriter::reloadFileList($reload_file_list, false);
}
return $xmlBuffer;
}
function getSize($tablename){
$repo = ConfService::getRepository();
$dbname = $repo->getOption("DB_NAME");
$like="";
$total="";
$t=0;
if($tablename !=""){
$like=" like '$tablename'";
}
$sql= "SHOW TABLE STATUS FROM $dbname $like";
$result=$this->execQuery($sql);
if($result){
while($rec = mysql_fetch_array($result)){
$t+=($rec['Data_length'] + $rec['Index_length']);
}
$total = Utils::roundSize($t);
}else{
$total="Unknown";
}
return($total);
}
function getCount($tableName){
$sql = "SELECT count(*) FROM $tableName";
$result = $this->execQuery($sql);
$t = 0;
if($result){
while($res = mysql_fetch_array($result)){
$t+=$res[0];
}
}
return $t;
}
function getColumnData($tableName, $columnName){
$sql = "SHOW COLUMNS FROM $tableName LIKE '$columnName'";
$res = $this->execQuery($sql);
if($res){
return mysql_fetch_array($res);
// ["Field", "Type", "Null", "Key", "Default", "Extra"] => Type is like "enum('a', 'b', 'c')"
}
}
function makeColumnDef($row, $prefix="", $suffix=""){
$defString = "";
if(isSet($row[$prefix."default".$suffix]) && trim($row[$prefix."default".$suffix]) != ""){
$defString = " DEFAULT ".$row[$prefix."default".$suffix];
}
$sizeString = ($row[$prefix."size".$suffix]!=""?"(".$row[$prefix."size".$suffix].")":"");
$fieldsDef = $row[$prefix."name".$suffix]." ".$row[$prefix."type".$suffix].$sizeString.$defString." ".$row[$prefix."null".$suffix]." ".$row[$prefix."flags".$suffix];
return trim($fieldsDef);
}
function cleanFlagString($flagString){
$arr = split(" ", $flagString);
$newFlags = array();
foreach ($arr as $flag){
if($flag == "primary_key" || $flag == "null" || $flag == "not_null"){
continue;
}
$newFlags[] = $flag;
}
return join(" ", $newFlags);
}
function sqlTypeToSortType($fieldType){
switch ($fieldType){
case "int":
return "Number";
case "string":
case "datetime":
case "timestamp":
return "String";
case "blob":
return "NumberKo";
default:
return "String";
}
}
function listTables(){
$repo = ConfService::getRepository();
$result = mysql_list_tables($repo->getOption("DB_NAME"));
$numtab = mysql_num_rows ($result);
$allTables = array();
for ($i =0; $i < $numtab; $i++) {
$table = trim(mysql_tablename($result, $i));
$allTables[] = $table;
}
return $allTables;
}
function showRecords($query, $tablename, $currentPage=1, $rpp=50, $searchval='' ){
$repo = ConfService::getRepository();
$dbname=$repo->getOption("DB_NAME");
$result=$this->execQuery($query);
$columns = array();
$rows = array();
if(is_a($result, "AJXP_Exception")) return $result;
$num_rows = mysql_num_rows($result);
$pg=$currentPage-1;
if(isset($_POST['first'])){
$pg=0;
}else if(isset($_POST['back'])){
$pg=$pg-1;
}else if(isset($_POST['next'])){
$pg++;
}else if(isset($_POST['last'])){
$pgs = $num_rows/$rpp;
$pg=ceil($pgs)-1;
}
if($pg < 0 ){
$pg=0;
}
if($pg > $num_rows/$rpp){
$pg=ceil($num_rows/$rpp)-1;
}
$totalPages = ceil($num_rows/$rpp);
$beg = $pg * $rpp;
$flds = mysql_num_fields($result);
$fields = @mysql_list_fields( $dbname, $tablename);
if(!$fields){
return new AJXP_Exception("Non matching fields for table '$tablename'");
}
$z=0;
$x=0;
$pkfield=array();
// MAKE COLUMNS HEADER
for ($i = 0; $i < $flds; $i++) {
$c=$i+1;
$title=mysql_field_name($fields, $i);
$type=mysql_field_type($fields, $i);
$size=mysql_field_len($fields, $i);
$flagstring = mysql_field_flags ($fields, $i);
$colData = $this->getColumnData($tablename, $title);
$colDataType = $colData["Type"];
if(preg_match("/(.*)\((.*)\)/", $colDataType, $matches)){
$type = $matches[1];
$size = $matches[2];
}
$columns[] = array("NAME" => $title, "TYPE"=>$type, "LENGTH"=>$size, "FLAGS"=>$flagstring, "DEFAULT"=>$colData["Default"]);
//Find the primary key
$flagstring = mysql_field_flags ($result, $i);
if(eregi("primary",$flagstring )){
$pk[$z] = $i;
$pkfield[$z]= mysql_field_name($fields, $i);
$z++;
}
}
$v=$flds+1;
if($z > 0){
$cpk=count($pk);
}else{
$cpk=0;
}
// MAKE ROWS RESULT
for ($s=$beg; $s < $beg + $rpp; $s++){
if($s < $num_rows){
if (!mysql_data_seek ($result, $s)) {
continue;
}
$row=mysql_fetch_array($result);
if(!isset($pk)){
$pk=' ';
$pkfield= array();
}
$values = array();
for($col = 0; $col < $flds; $col ++)
{
$values[mysql_field_name($fields, $col)] = stripslashes($row[$col]);
}
$rows[] = $values;
}
}
return array("COLUMNS" => $columns, "ROWS" => $rows, "HAS_PK"=>$cpk, "TOTAL_PAGES"=>$totalPages, "PK_FIELDS"=>$pkfield);
}
function execQuery($sql =''){
$output='';
if($sql !=''){
$result= @mysql_query( $sql );
if($result){
AJXP_Logger::logAction("exec", array($sql));
return $result;
}else{
return new AJXP_Exception($sql.":".mysql_error());
}
}else{
return new AJXP_Exception('Empty Query');
}
}
}
?>
|