Page 2 of 2

SQL Editors

PostPosted: Fri Oct 23, 2015 12:23 am
by Bernd.N
Hi Patrick,

I guess the SQL Explorer can do 100 times more. And besides that there are certainly dbSchema (Gary swears by it) and dbVis

http://eclipsesql.sourceforge.net/screenshots.php
http://www.dbschema.com/
https://www.dbvis.com/

Our tiny SQL Editor has three functions that we did not find elsewhere:
1) it is integrated into our own solution, so that we can SELECT and UPDATE data without switching to pgAdmin, also when we are with a customer using the solution
2) getting the names of tables and fields with a mouseclick, from large tables that show at once, instead of typing them in or opening table/field-trees with the +-button, which needs time
3) change (flush) any updated data right away in our client, so that we can see the actual changes at once without starting the client new

From Foxpro, I was used to have direct access to my data in my own solution, and I wanted that feeling again.
That was also the reason why we created the Data Record Editor:
https://www.servoyforge.net/attachments/download/1571/Screenshot_Example.PNG
https://www.servoyforge.net/projects/editwindow

Re: SQL Editor for integration in any solution

PostPosted: Fri Oct 23, 2015 12:46 am
by ptalbot
Right. So you mean you use this at runtime on a deployed server, not in developer. Got it.
I was just pointing that in Developer there's already a powerful tool to do all this.

Re: SQL Editor for integration in any solution

PostPosted: Fri Oct 23, 2015 12:50 am
by Bernd.N
Yes, correct. And I use it also during development, I added a button to our solution so that I can call that SQL Editor form at once with a mouseclick.
So all small UPDATEs or SELECTs I do right there.

Table Size Output

PostPosted: Sat Oct 24, 2015 8:39 pm
by Bernd.N
I added now a button to show all table and index sizes in kB with code I found on the web.
Here's the code:

Code: Select all
/**
* Shows table and index sizes
*
* Found at https://wiki.postgresql.org/wiki/Index_Maintenance
* (size columns were slightly modified to show kB always)
*
* @param {JSEvent} event
* @private
* @properties={typeid:24,uuid:"A8BBB1CC-726B-462C-AD4E-F8C7FFCFF96A"}
*/
function btnTableIndexSizes(event) {

   sqlStatement =
      "SELECT \
          t.tablename, \
          indexname, \
          to_char(c.reltuples, '999,999,999') AS num_rows, \
          pg_relation_size(quote_ident(t.tablename)::text) / 1024 AS table_size, \
          pg_relation_size(quote_ident(indexrelname)::text) / 1024 AS index_size, \
          CASE WHEN indisunique THEN 'Y' \
             ELSE 'N' \
          END AS UNIQUE, \
          idx_scan AS number_of_scans, \
          idx_tup_read AS tuples_read, \
          idx_tup_fetch AS tuples_fetched \
      FROM pg_tables t \
      LEFT OUTER JOIN pg_class c ON t.tablename=c.relname \
      LEFT OUTER JOIN \
          ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x \
                 JOIN pg_class c ON c.oid = x.indrelid \
                 JOIN pg_class ipg ON ipg.oid = x.indexrelid \
                 JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid ) \
          AS foo \
          ON t.tablename = foo.ctablename \
      WHERE t.schemaname = 'public' \
      ORDER BY 1,2" ;

   runSQLStatement();
   
   sqlStatement = "";
}

Re: SQL Editor for integration in any solution

PostPosted: Sat Nov 21, 2015 3:24 pm
by sergei.sheinin
SQL Editor now available for download at Servoy Forge: https://www.servoyforge.net/projects/sqleditor

Includes feature for listing table/index size of Postgres database.



Sergei
http://js2dx.com

Re: SQL Editor for integration in any solution

PostPosted: Wed Nov 25, 2015 12:03 am
by dfernandez
Great, thank you!

Did you test it with NGClient?

NGClient

PostPosted: Wed Nov 25, 2015 7:07 am
by Bernd.N
No, not so far, though it should be pretty easy to adopt it.