PHP Classes

File: as_admintool_sqlqry.php

Recommend this page to a friend!
  Classes of Alexander Selifonov   site administrator tool set   as_admintool_sqlqry.php   Download  
File: as_admintool_sqlqry.php
Role: Auxiliary script
Content type: text/plain
Description: Plugin module: SQL queries execute and explain
Class: site administrator tool set
Web interface to manage site resources
Author: By
Last change: some bug fixes in javascript block
Date: 14 years ago
Size: 11,755 bytes
 

Contents

Class file image Download
<? /** * @package as_admintool * @desc as_admintool_sqlqry.php - "SQL query" plugin for as_admintool.php * @author Alexander Selifonov <as-works@narod.ru> * @copyright Alexander Selifonov 2007 * @link http://as-works.narod.ru/en/php/ * @version 1.003.024 * modified 20.03.2008 (dd.mm.yyyy) ================================================================================ */ require_once('as_dbutils.php'); // DB access wrapper class define('ASADM_SQLQUERY','sqlqry'); // ID type for this module if(!defined('ASADM_MAXRECORDS')) define('ASADM_MAXRECORDS',800); // SQL query: maximal records returned define('ASADM_QRYPARAM',4); // number of parameter fields shown for SQL query define('ASADM_SQRYWHEIGHT',100); // SQL query textarea height (px) # mandatory string - registering plugin # example: $as_admt_plugins['unique_id'] = array('html-drawing_func','executing_func'); CAsAdminTool::RegisterPlugin('sqlqry','ASAdmt_sql_Form','ASAdmt_sql_exec'); // interface localization here ! if(empty($as_iface['predef-qry'])) $as_iface['predef-qry'] ='pre-defined queries...'; if(empty($as_iface['execqry'])) $as_iface['execqry'] ='Execute Query'; if(empty($as_iface['explainqry'])) $as_iface['explainqry'] ='Explain Query'; if(empty($as_iface['qryresult'])) $as_iface['qryresult'] ='Execution result'; if(empty($as_iface['msg_qrydone'])) $as_iface['msg_qrydone'] ='Query executed'; if(empty($as_iface['msg_qryerror'])) $as_iface['msg_qryerror'] ='Query error'; # $as_adm_qryaccess : SQL runing access: # 0 - only pre-defined queries allowed (sqltext field is hidden), no 'explain query' button # 1 - user can write queries, but only reading data, UPDATING queries will be denied # 2 - full access, all queries executed if(!isset($as_adm_qryaccess)) $as_adm_qryaccess = 0; # ASAdmt_sql_Form - function for drawing "SQL query" screen page # first par $pginfo is array: [0] - pageid, [1],[2]-max page size (width,height) function ASAdmt_sql_Form($pginfo,$qrylist='',$par2=false,$par3=false) { global $as_iface, $as_cssclass, $as_adm_qryaccess; $pageid=isset($pginfo[0])? $pginfo[0]: 0; $lwidth = isset($pginfo[1])? $pginfo[1]: 800; $lheight = isset($pginfo[2])? $pginfo[2]: 600; $rest_h = $lheight - 115; $self = $_SERVER['PHP_SELF']; static $sql_js_drawn = false; $dbname = is_string($par2)? $par2 : ''; if(!$sql_js_drawn) { #<3> draw only once !!! $sql_js_drawn = true; ?> <script language='javascript'> var as_admt_stdsqls = []; // array for predefined queries var as_admt_subpars = []; function SqlQry_ChangeStdQry(pageid,obj) { var fm = asGetObj("as_admt_sqlform_"+pageid); var iqry = obj.selectedIndex; if(iqry <=0) fm.sqltext.value = 'select * from'; else fm.sqltext.value = as_admt_stdsqls[pageid][iqry][0].replace(/{CRLF}/g,"\r\n"); for(ik=1; ik<=<?= ASADM_QRYPARAM ?>; ik++) { asGetObj("sqprm_"+pageid+"_"+ik).innerHTML= ((iqry<=0 || as_admt_stdsqls[pageid][iqry][ik]==undefined)? ('&amp;P'+ik) : as_admt_stdsqls[pageid][iqry][ik]); } if(typeof(as_admt_subpars[pageid][iqry])!='undefined') { fm.subpars.value=as_admt_subpars[pageid][iqry]; } } var ajax_sqlqrybusy = false; function Admt_RunSqlQry(pageid,bexplain) { if (ajax_sqlqrybusy) return false; fm = window.asGetObj("as_admt_sqlform_"+pageid); // alert('KT-form element : '+fm.name); if(fm.sqltext.value=='') { alert('empty sqltext'); return false; } var xmlreq = NewXMLHttpRequest(); if(!xmlreq) { return false; } ajax_sqlqrybusy = true; xmlreq.onreadystatechange= function() { //<3> if (xmlreq.readyState == 4) { //<3A> // alert(xmlreq.responseText); //debug var spl = xmlreq.responseText.split("{|}"); delete xmlreq; ajax_sqlqrybusy = false; if(spl.length < 2) { asGetObj("sqlresult_"+pageid).innerHTML ='<?=$as_iface['msg_wrongreply']?> '+spl[0]; } else { asGetObj("sqlresult_"+pageid).innerHTML = spl[1]; } //<4> } //<3A> } //<3> xmlreq.open('POST','<?=$self?>',true); xmlreq.setRequestHeader("Content-Type", postcont); params = 'adm_action_type=sqlqry&pageid=' + pageid + '&'+ComputeParamString('as_admt_sqlform_'+pageid); if(bexplain==1) params += '&b_explain=1'; xmlreq.send(params); asGetObj("sqlresult_"+pageid).innerHTML = "<?=$as_iface['msg_waiting']?>"; return false; } </script> <? } #<3> $stdsqls = array(); if(is_array($qrylist)) $stdsqls = $qrylist; elseif(is_file($qrylist)) { $tlst = file($qrylist); foreach($tlst as $strk) { $strk = trim($strk); if($strk=='' || $strk[0]=='#') continue; $tval = explode('|',$strk); if(count($tval)<2) continue; // if(!empty($tval[1])) $stdsqls[] = $tval; //[0] = $tval[1]; } } if(count($stdsqls)>0) { echo "<script language='javascript'>\n as_admt_stdsqls[$pageid] = [];\n as_admt_subpars[$pageid] = [];\n"; $km=1; for($kk=0; $kk<count($stdsqls); $kk++) { $key = $stdsqls[$kk][0]; $subpars = ''; if(strlen($stdsqls[$kk][1])>1) { $allval = "\"{$stdsqls[$kk][1]}\""; for($nn=2;$nn<=ASADM_QRYPARAM+1;$nn++) { if(isset($stdsqls[$kk][$nn]) ) { if($stdsqls[$kk][$nn][0]==='#') $subpars .= ($subpars===''? '':'|').$stdsqls[$kk][$nn]; else $allval .= ",\"{$stdsqls[$kk][$nn]}\""; } } echo " as_admt_stdsqls[$pageid][$km] = [$allval];\n as_admt_subpars[$pageid][$km] = \"$subpars\";\n"; $km++; } } echo "</script>\n"; } ?> <table id='asadt_tbl_<?=$pageid?>'> <tr><form name='as_admt_sqlform_<?=$pageid?>'><input type='hidden' name='subpars' value='' /> <!--td><?=$as_iface['parameters']?> :</td></tr--> <tr> <? if(!empty($dbname)) echo "<input type=hidden name='_dbname_' value='$dbname'>"; if(count($stdsqls)>0) { $rest_h -=40; echo "<tr><td colspan=4>{$as_iface['predef-qry']}<br><SELECT name='stdqry' style='width:200' onChange='SqlQry_ChangeStdQry($pageid,this)'> <OPTION value='0'>{$as_iface['predef-qry']}</OPTION>"; for($kk=0; $kk<count($stdsqls); $kk++) /* as $kname=>$kval)*/ { $kname = $stdsqls[$kk][0]; echo ( (strlen($stdsqls[$kk][1])>1)? "<OPTION value='$kname'>$kname</OPTION>" : "<OPTGROUP label='$kname'>"); } echo "</SELECT></td></tr>"; } for($kkp=1; $kkp<=ASADM_QRYPARAM; $kkp++) { if($kkp>4 && ($kkp % 5 ==1)) { $rest_h -=36; echo "</tr><tr>"; } // NN parameter per line echo "<td><span id='sqprm_{$pageid}_{$kkp}'> &amp;P{$kkp}</span><br><input type='TEXT' name='qparm{$kkp}' class='{$as_cssclass['textfield']}' style='width:120'></td>\n"; } $attrib = ($as_adm_qryaccess>=1)? '':'READONLY'; // with $as_adm_qryaccess=0 user won't even see SQL query text - just parameter fields if($as_adm_qryaccess>0) { $rest_h -=ASADM_SQRYWHEIGHT; $qryfield = "<tr><td><textarea name='sqltext' class='ibox' style='width:100%; Height=".ASADM_SQRYWHEIGHT."' {$attrib}>select * from</textarea></td></tr>"; } else { $qryfield = "<input type='hidden' name='sqltext' value=''>"; } ?> </tr></table> <div align=center> <table width='98%'> <?=$qryfield?> <tr><td><button class='button' name='runsql' onClick='Admt_RunSqlQry(<?=$pageid?>);return false'><?=$as_iface['execqry']?></button> <? if($as_adm_qryaccess>=1) { ?> &nbsp; <button class='button' name='expsql' onClick='Admt_RunSqlQry(<?=$pageid?>,1);return false'><?=$as_iface['explainqry']?></button> <? } ?> </td></tr> <tr><td><?=$as_iface['qryresult']?></td></tr></form> <tr><td><div id='sqlresult_<?=$pageid?>' class='<?=$as_cssclass['resultarea']?>' style='overflow:auto; height:<?=$rest_h?>px; width:<?=$lwidth-20?>px;'>&nbsp;</div></td></tr> </tr> </table> </div> <? } // # ASAdmt_sql_exec - function for executing query and returning result through AJAX function ASAdmt_sql_exec($parms) { global $as_iface,$as_cssclass, $as_adm_qryaccess, $as_dbengine; $pageid = isset($parms['pageid'])? $parms['pageid'] : '1'; if($as_adm_qryaccess<2) { $qarr = explode(' ',trim($parms['sqltext'])); $first = strtolower($qarr[0]); if(!in_array($first, array('select','show','desc','describe','explain'))) { return "$pageid{|}UPDATES NOT ALLOWED ! ($first - operator denied or unknown)"; } } $subpars = empty($parms['subpars'])? '': explode('|',$parms['subpars']); // additional parameters: "href columns" etc. $s_from = array(); $s_to = array(); $dbname = isset($parms['_dbname_']) ? $parms['_dbname_']: ''; if(strlen($dbname)) { $seldb = $as_dbengine->select_db($dbname); } for($kk=1 ; $kk<=ASADM_QRYPARAM; $kk++) { if(isset($parms['qparm'.$kk])) { $s_from[] = '&P'.$kk; $s_to[]=$parms['qparm'.$kk]; } } $sqry = isset($parms['sqltext'])? $parms['sqltext'] : ''; $sqry = str_replace($s_from, $s_to, $sqry); $sqry = trim(stripslashes($sqry)); if(empty($sqry)) { return $ret; } $ret = "$pageid{|}"; # <table _width='900px' border=0 cellspacing=0 cellpadding=0><tr class='head' style='text-align:left'><td><b>$sqry</b></td></tr></table>\n"; $qrylist = explode("/\r",$sqry); $explain = empty($parms['b_explain'])?false:true; foreach($qrylist as $no=>$oneqry) { $ret .= ASAdmt_RunOneSql($oneqry,$explain).'<br />'; } return $ret; } /** * @desc ASAdmt_RunOneSql performs one SQL query and returns <table> with record values or error text **/ function ASAdmt_RunOneSql($querytext,$explain=false) { global $as_iface,$as_cssclass, $as_adm_qryaccess, $as_dbengine; $ret = ''; $result = ($explain)? $as_dbengine->sql_explain($querytext) : $as_dbengine->sql_query($querytext); if($result) { //<2> if(is_resource($result)) { //<3> // show result recordset $ret .="<table border=0 cellspacing=1 >\n"; $header = 0; $ii=0; while (($row = $as_dbengine->fetch_assoc($result)) && (ASADM_MAXRECORDS==0 || $ii<=ASADM_MAXRECORDS)) { //<4> $values = array_values($row); // I'll need index-based values for HREF column composing if($header < 1) { //<5> $header = 1; $ret .="<tr>"; // class='{$as_cssclass['trowhead']}' foreach($row as $col_name=>$col_value) { if(is_string($col_name)) $ret .="<td class='{$as_cssclass['tdhead']}'>$col_name</td>"; } if(is_array($subpars)) for($ipar=0;$ipar<count($subpars);$ipar++) $ret.="<td class='{$as_cssclass['tdhead']}'>&nbsp;</td>"; $ret .='</tr>'; reset($row); // back to first element ! } //<5> $ii++; $classname = ($ii % 2) ? $as_cssclass['trowodd']:$as_cssclass['troweven']; $ret .= "\n<tr class='$classname'>"; foreach($row as $col_name=>$col_value) if(is_string($col_name)) $ret .= "<td>$col_value</td>"; if(is_array($subpars)) for($ipar=0;$ipar<count($subpars);$ipar++) { $onepar = explode('^',$subpars[$ipar]); $colvalue = ''; switch($onepar[0]) { case '#HREF': $colvalue=@str_replace('{ID}',$values[$onepar[1]],$onepar[2]); break; default: $colvalue=$onepar[0]; break; } $ret .="<td>$colvalue</td>"; } $ret .='</tr>'; } //<4> $as_dbengine->free_result($result); $ret .="</table></div>\n"; }//<3> else $ret .= $as_iface['msg_qrydone']. ' (rows affected: '.$as_dbengine->affected_rows().')'; }//<2> else $ret .= $as_iface['msg_qryerror'].' :'.$as_dbengine->sql_error(); return $ret; } ?>