* This file is part of AjaXplorer.
*
* AjaXplorer is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* 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. See the
* GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with AjaXplorer. If not, see .
*
* The latest code can be found at .
*/
defined('AJXP_EXEC') or die( 'Access not allowed');
/**
* @package info.ajaxplorer.plugins
* AJXP_Plugin to access a MySQL Server and make AjaXplorer act like a phpMyAdmin
*/
class mysqlAccessDriver extends AbstractAccessDriver
{
/** The user name */
var $user;
/** The user password */
var $password;
function initRepository(){
$this->user = $this->repository->getOption("DB_USER");
$this->password = $this->repository->getOption("DB_PASS");
$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->password);
if(!$link) {
throw new AJXP_Exception("Cannot connect to server!");
}
if(!@mysql_select_db($dbname, $link)){
throw new AJXP_Exception("Cannot find database!");
}
return $link;
}
function closeDbLink($link){
if(!mysql_close($link)){
throw new AJXP_Exception("Cannot close connection!");
}
}
function switchAction($action, $httpVars, $fileVars){
$repo = ConfService::getRepository();
if(!isSet($this->actions[$action])) return;
parent::accessPreprocess($action, $httpVars, $fileVars);
$xmlBuffer = "";
foreach($httpVars as $getName=>$getValue){
$$getName = AJXP_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, "%23")!==false){
$parts = explode("%23", $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($indexcreateDbLink();
$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 = explode("_", $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 = explode(".", $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("");
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();
AJXP_XMLWriter::sendFilesListComponentConfig('');
$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 "";
}
print "";
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);
}
}
try {
$result = $this->showRecords($query, $tableName, $currentPage);
}catch (AJXP_Exception $ex){
unset($_SESSION["LAST_SQL_QUERY"]);
throw $ex;
}
AJXP_XMLWriter::header();
$blobCols = array();
$columnsString = '';
foreach ($result["COLUMNS"] as $col){
$columnsString .= "cleanFlagString($col["FLAGS"])."\" field_pk=\"".(preg_match("/primary/", $col["FLAGS"])?"1":"0")."\" field_null=\"".(preg_match("/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"];
}
}
$columnsString .= '';
AJXP_XMLWriter::sendFilesListComponentConfig($columnsString);
//print '';
if($result["TOTAL_PAGES"] > 1){
AJXP_XMLWriter::renderPaginationData($count, $currentPage,$result["TOTAL_PAGES"]);
}
foreach ($result["ROWS"] as $arbitIndex => $row){
print '$value){
if(in_array($key, $blobCols)){
$sizeStr = "-NULL";
if(strlen($value)) $sizeStr = "-".AJXP_Utils::roundSize(strlen($sizeStr));
print "$key=\"BLOB$sizeStr\" ";
}else{
$value = str_replace("\"", "", $value);
$value = AJXP_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_'.$arbitIndex.'.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") || (isset($reload_file_list)) )
{
$xmlBuffer .= AJXP_XMLWriter::reloadDataNode("", "", 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 = AJXP_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 = explode(" ", $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";
}
}
/* <--- add a slash at the beggining of this line to switch between the 2 functions
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 listTables(){
$repo = ConfService::getRepository();
$result = mysql_query("SHOW TABLES FROM `".$repo->getOption("DB_NAME")."` LIKE '".$repo->getOption("DB_PTRN")."%'");
$allTables = array();
while ($row = mysql_fetch_row($result)) {
$allTables[] = $row[0];
}
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){
throw 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(preg_match("/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 !=''){
//$sql=mysql_real_escape_string($sql);
$result= @mysql_query(stripslashes($sql));
if($result){
AJXP_Logger::logAction("exec", array($sql));
return $result;
}else{
throw new AJXP_Exception($sql.":".mysql_error());
}
}else{
throw new AJXP_Exception('Empty Query');
}
}
}
?>