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   3217 Views

Avatar
apiening

22 June 2010 at 10:31am (Last edited: 22 June 2010 12:15pm), Community Member, 60 Posts

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

23 June 2010 at 10:48pm Community Member, 56 Posts

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

24 June 2010 at 2:48am 4085 Posts

** applause **

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

Avatar
monkee

27 June 2010 at 2:45am Community Member, 20 Posts

very slick and responsive interface... i love it

Thanks a Lot!

Avatar
swaiba

29 June 2010 at 8:53pm Forum Moderator, 1796 Posts

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

30 June 2010 at 4:00am 4085 Posts

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

Avatar
apiening

30 June 2010 at 12:25pm Community Member, 60 Posts

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

30 June 2010 at 12:29pm Community Member, 60 Posts

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