Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

All other Modules /

Discuss all other Modules here.

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

new module: DB Plumber - the poor man's phpMyAdmin for Silverstripe


Go to End
Reply


14 Posts   3309 Views

Avatar
apiening

Community Member, 60 Posts

22 June 2010 at 10:31am

Edited: 22/06/2010 12:15pm

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

Avatar
mattclegg

Community Member, 56 Posts

23 June 2010 at 10:48pm

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?

Avatar
UncleCheese

Forum Moderator, 4093 Posts

24 June 2010 at 2:48am

** applause **

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

Avatar
monkee

Community Member, 20 Posts

27 June 2010 at 2:45am

very slick and responsive interface... i love it

Thanks a Lot!

Avatar
swaiba

Forum Moderator, 1799 Posts

29 June 2010 at 8:53pm

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

Avatar
UncleCheese

Forum Moderator, 4093 Posts

30 June 2010 at 4:00am

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

Avatar
apiening

Community Member, 60 Posts

30 June 2010 at 12:25pm

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.

Avatar
apiening

Community Member, 60 Posts

30 June 2010 at 12:29pm

Hi Saiba,

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

Thanks

Andy

Go to Top