<?php
/**************************************************************************\
* SQL Auto Complete Demo *
* Copyright (C) 2008 by Thomas Bley (http://www.simple-groupware.de) *
* ------------------------------------------------------------------------ *
* This program is free software; you can redistribute it and/or *
* modify it under the terms of the GNU General Public License Version 2 *
* as published by the Free Software Foundation; only version 2 *
* of the License, no later version. *
* *
* This program 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 General Public License for more details. *
* *
* You should have received a copy of the GNU General Public License *
* Version 2 along with this program; if not, write to the Free Software *
* Foundation, Inc., 59 Temple Place - Suite 330, Boston, *
* MA 02111-1307, USA. *
\**************************************************************************/
error_reporting(E_ALL);
mysql_connect('localhost', 'root', '');
if (!empty($_GET['func'])) {
$result = call_user_func_array(array('funcs', $_GET['func']), explode(',', $_GET['params']));
echo json_encode($result);
exit;
}
class funcs {
static function get_databases($prefix) {
$result = array();
$res = mysql_query("SHOW databases like '{$prefix}%'");
while ($row = mysql_fetch_array($res)) {
$result[] = array($row[0].'.',$row[0]);
}
mysql_free_result($res);
return $result;
}
static function get_tables($prefix) {
$result = array();
if (!strpos($prefix,'.')) return array();
$prefix = explode('.', $prefix);
if ($prefix[0]=='') return array();
$database = mysql_escape_string($prefix[0]);
$prefix = mysql_escape_string($prefix[1]);
$res = mysql_query("SELECT table_name,table_rows,table_comment
FROM information_schema.tables
WHERE table_schema = '{$database}'
AND table_name like '{$prefix}%'");
$letters = range('a', 'k');
$result[] = array("","---- {$database} ----");
while ($row = mysql_fetch_object($res)) {
$letter = '';
$count = count($result);
for ($i=0; $i<strlen($count); $i++) $letter .= $letters[substr($count,$i,1)];
$result[] = array("{$database}.{$row->table_name} {$letter}","{$row->table_name} <small>({$row->table_rows} rows {$row->table_comment})</small>");
}
mysql_free_result($res);
return $result;
}
static function get_columns($prefix,$alias) {
$result = array();
$prefix .= '..';
$prefix = explode('.', $prefix);
if ($prefix[0]=='' or $prefix[1]=='') return array();
$database = mysql_escape_string($prefix[0]);
$table = mysql_escape_string($prefix[1]);
$prefix = mysql_escape_string($prefix[2]);
$res = mysql_query("SELECT column_name, column_type, column_comment
FROM information_schema.columns
WHERE table_schema = '{$database}'
AND table_name = '{$table}'
AND column_name like '{$prefix}%'");
if ($alias==null) $alias = "{$table}.";
$result[] = array("","---- {$table} ({$database}) ----");
while ($row = mysql_fetch_object($res)) {
$result[] = array("{$alias}{$row->column_name}","{$row->column_name} <small>({$row->column_type} {$row->column_comment})</small>");
}
mysql_free_result($res);
return $result;
}
}
mysql_close();
?>
<html>
<body>
Database: <input type="text" id="database" value="" /><br/>
<select size="2" id="selectbox" style="width:30%; height:200px; float:right;">
</select>
<textarea id="codebox" style="width:70%; height:200px;">
</textarea>
<input type="button" value="+" onclick="set_height(obj('sqlquery'),100); set_height(obj('selectbox'),100);" />
<input type="button" value="-" onclick="set_height(obj('query'),-100); set_height(obj('selectbox'),-100);" />
<input type="submit" value="Execute Query" title="Alt+e / Alt+Shift+e" accesskey="e" onclick="alert('test');" />
<script>
var token_id = 0;
var last_token = null;
var last_value = null;
var cache = [];
obj("codebox").focus();
obj("codebox").onkeyup = obj("codebox").onclick = function() {
return keyup(this,obj('selectbox'),val('database'));
}
obj("codebox").onkeydown = function(event) {
return keydown(event,this,obj('selectbox'));
}
function cache_get(func, cache_id) {
if (cache[func]==null) cache[func] = [];
if (cache[func][cache_id]!=null) {
return cache[func][cache_id];
}
/* stronger query cache, TODO needs to be accessed in async mode
else if (cache[func]!=null) {
for (var i in cache[func]) {
if (cache_id.indexOf(i)==0) {
return false;
var result = [];
for (j in cache[func][i]) {
if (cache[func][i][j][0]=='' || cache[func][i][j][0].indexOf(cache_id)!=-1) {
result.push(cache[func][i][j]);
}
}
if (result.length>0) return result;
} } }
*/
return false;
}
function call(func, params, callback, params_callback) {
var cache_id = params.join(',');
if (cached = cache_get(func, cache_id)) {
callback(cached, params_callback);
return;
}
var token = token_id;
var xmlhttp = window.ActiveXObject ? new ActiveXObject("Microsoft.XMLHTTP") : new XMLHttpRequest();
xmlhttp.open("GET", "test_sql.php?func="+escape(func)+"¶ms="+params.join(','), true);
xmlhttp.onreadystatechange = function() {
if (xmlhttp.readyState == 4) {
if (token != token_id) return; // request outdated
var result = [];
try {
if (xmlhttp.status == 200) {
eval('result = '+xmlhttp.responseText);
cache[func][cache_id] = result;
callback(result, params_callback);
} else alert("Error: "+func+" "+xmlhttp.status+" "+xmlhttp.statusText+" "+xmlhttp.responseText);
} catch (e) {
alert("Error: "+result+" "+e+" "+func);
} } }
xmlhttp.send(null);
}
function get_last_keyword(obj) {
var keywords = {'select':'','from':'','where':'','order':'','update':'','into':'','set':''};
var tokens = obj.value.substr(0,obj.selectionStart).split(' ');
for (var i=tokens.length-1; i>=0; i--) {
if (keywords[tokens[i]] != null) return tokens[i];
}
return '';
}
function get_last_token(obj) {
var token = obj.value.substr(0,obj.selectionStart);
pos = token.lastIndexOfArr([',',' ']);
if (pos!=-1) token = token.substr(pos+1);
return token.trim();
}
function select_show(options, params) {
var box = params[0];
var obj = params[1];
for (var i=0; i<options.length; i++) {
box.innerHTML += '<option value="'+options[i][0]+'" ondblclick="obj(\''+obj.id+'\').focus(); select_insert(obj(\''+obj.id+'\'),obj(\''+box.id+'\'));">'+
options[i][1]+'</option>';
}
if (options.length>0) box.innerHTML += '<option> </option>';
}
function select_hide(box) {
box.innerHTML = '';
}
function select_insert(input,box) {
if (box.selectedIndex==-1) return;
var text = box.options[box.selectedIndex].value;
var pos = input.selectionStart;
var token = get_last_token(input);
if (get_last_keyword(input) != token) pos = pos - token.length;
input.value = input.value.substr(0,pos) + text + input.value.substr(input.selectionEnd);
input.selectionStart = pos + text.length;
input.selectionEnd = pos + text.length;
}
function find_tables(obj) {
var tables = null;
var re = /(?:from|into|update)(.*?)(?:where|order|set|$)/;
if (m = re.exec(obj.value)) {
tables = [];
var items = m[1].split(',');
for (var i=0; i<items.length; i++) {
items[i] = items[i].trim().split(' ');
if (items[i][1]==null) items[i][1] = items[i][0];
tables[items[i][1]] = items[i][0];
}
}
return tables;
}
function keydown(event,obj,box) {
keycode = event.which;
if (box.innerHTML.length > 0) {
if (keycode==27) {
select_hide(box);
return false;
}
if (keycode==13) {
select_insert(obj,box);
return false;
}
if (keycode==40) { // cursor down
if (box.options.length-1 > box.selectedIndex) box.selectedIndex++;
// else select_hide(box);
return false;
}
if (keycode==38) { // cursor up
if (box.selectedIndex > -1) box.selectedIndex--; else select_hide(box);
return false;
} } }
function keyup(obj,box,database) {
var keyword = get_last_keyword(obj);
var token = get_last_token(obj);
var prefix = '';
if (token!=keyword) prefix = token;
if (obj.value != last_value) token_id++;
if (obj.value.indexOf("'")==-1 && last_token != keyword + token) {
if (['select','where','order','set'].indexOf(keyword)!=-1) {
var tables = find_tables(obj);
if (pos = token.indexOf('.') && pos != -1) {
var table = prefix.substr(0,pos);
select_hide(box);
if (tables!=null && tables[table]!=null) {
prefix = tables[table] + prefix.substr(pos);
call('get_columns',[prefix,table+'.'],select_show,[box, obj]);
} else {
call('get_columns',[prefix,null],select_show,[box, obj]);
}
} else if (tables!=null) {
var alias = '';
select_hide(box);
for (var i in tables) {
if (tables.length > 1) alias = tables[i]+'.';
call('get_columns',[tables[i],alias],select_show,[box, obj]);
} } }
if (['from','into','update'].indexOf(keyword)!=-1) {
select_hide(box);
if (prefix.length!=1) {
if (prefix.indexOf('.')==-1) call('get_databases',[prefix], select_show, [box, obj]);
if (prefix.indexOf('.')!=-1) call('get_tables',[prefix],select_show, [box, obj]);
if (database!='') call('get_tables',[database+'.'+prefix],select_show, [box, obj]);
} } }
last_value = obj.value;
last_token = keyword + token;
}
function obj(id) {
return document.getElementById(id);
}
function val(id) {
return document.getElementById(id).value;
}
String.prototype.trim = function () {
return this.replace(/\s*$/,'').replace(/^\s*/,'');
}
String.prototype.lastIndexOfArr = function (arr) {
var pos = -1;
for (var i=0; i<arr.length; i++) {
var pos2 = this.lastIndexOf(arr[i]);
if (pos2 > pos) pos = pos2;
}
return pos;
}
function set_height(obj,diff_size) {
if (!obj) return;
obj.style.height=obj.offsetHeight+diff_size+'px';
}
</script>
</body>
</html>