Hi erverybody,

if you're not interested in my grief, just try it out: http://silverstripe.org/database-plumber-module/

I've been working with the new SS database adapters a lot lately. Debugging SQL queries while switching between MySQL, SQL Server, Postgres and SQLite can be quite a pain:

• My phpMyAdmin by default uses backticks instead of double quotes. So if you want to just copy a failing query from an SS error msg to phpMyAdmin you always have to replace one withe the other.

• For SQL Server I'm using a virtual win 2008 box on a remote machine. The machine is terribly slow. A right click in SQL Server Management Studio for my SS database takes !10 SECONDS! to get the context menu, not speaking of the Management Studio using a different syntax then the one in PHP (square brackets instead of doublequotes and other peculiarities)

• I have 3 different SQLite versions on my mac (?) and the command line, the firefox plugin client and SS are all using differnt ones. The problem is that the different versions behave VERY differently, so you can't just try a query in your firefox sqlite manager and be sure that you get the same result in SS.

• I don't have a Postgres client installed at all.

This is all manageble but wouldn't it be nice to just have a box to hack in your SQL command and use your native SS database connection? I added some db browsing and record CRUD functions.

I hope it will be useful for someone else as well. It's alpha at the moment and has some issues in IE which I will look into soon, so try out with FF/Chrome/Safari. Your feedback would be appreciated.

Cheers

Andy

Looks nice and slick in Chrome, and pretty quick when deleting rows. Be more useful when you have the SQL command box, or at least some sort of sql builder with predefined fields for selecting or viewing?

** applause **

Welcome to all of my SS sites, DB Plumber. We've got a long day ahead of us. :)

very slick and responsive interface... i love it

Thanks a Lot!

Wow, this is great! Thanks you so much!

I have a suggestion for improvement which is something I have missed terribly from MSSQL Query analyser - the ability to run a couple of queries and get results. there is a "Array to Text Table Generation Class" (http://tonylandis.com/php/php-text-tables-class/) that can display the results in text and then it is easy to display two different selects into one large text area... this other thing I would suggest is a larger SQL area for entering more complex SQL and lastly something that would save some useful queries to save re-entering them all the time.

the SQL can be split at the ';' and then each can table can be created with the following...

$result = mysql_query($strSQL,$connection);
$arr_table_result= format_mysql_fetch_result_to_array($result);
$renderer = new ArrayToTextTable($arr_table_result);
$renderer->showHeaders(true);
$strText = $renderer->render(true);

function format_mysql_fetch_result_to_array($result)
{
    $table_result=array();
    $r=0;
    if ($result)
    {
		while($row = mysql_fetch_assoc($result))
		{
			$arr_row=array();
			$c=0;

			while ($c < mysql_num_fields($result))
			{
				$col = mysql_fetch_field($result, $c);
				$arr_row[$col -> name] = $row[$col -> name];
				$c++;
			}
			$table_result[$r] = $arr_row;
			$r++;
		}
	}
    return $table_result;
}

Barry

Help! Quotes aren't escaped! Updating a field with ' in the value makes it barf!

Hi UncleCheese,

in the forms right? Good catch. Fixed in r107299. Please update.

Thanks

Andy

P.S.: ANSI compliant escaping uses double single quotes '' (?!). This works with all the adapters. MySQL allows good old backslash \' too.

Hi Saiba,

I thought about adding support for saving and reusing commands too. I put it on my nice-to-have list.

Thanks

Andy