SQL Editor for integration in any solution

Share business templates, ideas, experiences, etc with fellow Servoy developers here

SQL Editors

Postby Bernd.N » Fri Oct 23, 2015 12:23 am

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
Last edited by Bernd.N on Fri Oct 23, 2015 12:55 am, edited 1 time in total.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: SQL Editor for integration in any solution

Postby ptalbot » Fri Oct 23, 2015 12:46 am

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.
Patrick Talbot
Freelance - Open Source - Servoy Valued Professional
https://www.servoyforge.net
Velocity rules! If you don't use it, you don't know what you're missing!
User avatar
ptalbot
 
Posts: 1654
Joined: Wed Mar 11, 2009 5:13 am
Location: Montreal, QC

Re: SQL Editor for integration in any solution

Postby Bernd.N » Fri Oct 23, 2015 12:50 am

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.
Attachments
sql editor button.jpg
sql editor button.jpg (20.22 KiB) Viewed 8268 times
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Table Size Output

Postby Bernd.N » Sat Oct 24, 2015 8:39 pm

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 = "";
}
Attachments
table_index_sizes.jpg
table_index_sizes.jpg (164.69 KiB) Viewed 8228 times
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: SQL Editor for integration in any solution

Postby sergei.sheinin » Sat Nov 21, 2015 3:24 pm

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
Attachments
screen2.png
Screenshot of SQL Editor
screen2.png (116.69 KiB) Viewed 8009 times
screen1.png
Table/Index size functioin
screen1.png (67.94 KiB) Viewed 8009 times
Sergei Sheinin
JavaScript, RDBMS
http://js2dx.com
sergei.sheinin
 
Posts: 79
Joined: Wed May 07, 2014 3:22 pm

Re: SQL Editor for integration in any solution

Postby dfernandez » Wed Nov 25, 2015 12:03 am

Great, thank you!

Did you test it with NGClient?
dfernandez
 
Posts: 87
Joined: Wed Feb 29, 2012 4:04 pm

NGClient

Postby Bernd.N » Wed Nov 25, 2015 7:07 am

No, not so far, though it should be pretty easy to adopt it.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Previous

Return to Sharing Central

Who is online

Users browsing this forum: No registered users and 4 guests

cron