SQL Editor for integration in any solution

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

SQL Editor for integration in any solution

Postby Bernd.N » Fri Sep 11, 2015 12:11 pm

I am lazy regarding typing and also prefer to do anything inside my own solution if possible, including SQL commands, instead of switching over to pgAdmin or some other DB tool.
That was the reason to ask Sergei Sheinin to create a most easy form that will allow to do the basic SQL commands faster than in pgAdmin, just by optimizing all aspects and by showing two large tables of the DB-tables and the table-columns.

The screenshot shows an example of a basic SELECT, which needs just two mouseclicks (on the table name and on the select button).
If I want to see specific columns, I could check them first in the columns table.

Both tables on the form are filled automatically, for example the first with
SELECT 0 as chk, table_name AS nam FROM information_schema.tables ...

In case there is interest, we could add it to Servoy Forge.
Attachments
sql editor.png
sql editor.png (40.95 KiB) Viewed 13095 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 logicimpresa » Fri Sep 11, 2015 12:26 pm

Very nice...
Gianluca Zanini
Logic Impresa - Italy
SAN Partner
Best BAP development 2014
Eager Beaver 2013
Servoy Valued Professional 2012
User avatar
logicimpresa
 
Posts: 65
Joined: Sun May 01, 2011 3:58 pm
Location: Bernate Ticino (MI) - Italy

Remark regarding flushing

Postby Bernd.N » Fri Sep 11, 2015 12:30 pm

I forgot to mention, in case you issue an SQL-UPDATE command inside your own solution, you certainly would like to see the results at once, instead of restarting your application.
That can be done by flushing. I include sample code (written by Gary Dotzlaw) that does this (scopes.nav.currentForm is a variable that is always set to the current form in our solution).

Code: Select all
function btnFlushCache(event) {
   
   var sForm = scopes.nav.currentForm,
      sTable = "",
      bSuccess = false;
   
   sTable = scopes.utils.table_getTableNameFromFoundset(forms[sForm].foundset);
   
   bSuccess = plugins.rawSQL.flushAllClientsCache(scopes.utils.DB.SERVER,sTable);
   
   if (bSuccess) {
      // application.output("Cache flushed for Table: " + sTable);
   } else {
      scopes.utils.log("Cache was not flushed for Table: " + sTable, LOGGINGLEVEL.INFO);
   }
}

function table_getTableNameFromFoundset(fs) {

   if (!fs) return null;

   var ds = fs.getDataSource(),
       sTable = null;

   if (ds) sTable = ds.split("/").pop();

   return sTable;
}



And by thinking about it, I have two ideas to optimize the form above:

a) Flush the table right away after the UPDATE was done (currently we use another button for it that is not on the screenshot)

b) Add a code snippet table to store sample SQL lines especially for non-trivial SELECT, UPDATE and INSERT commands, together with a short description, to be able to search and use that code later.
(Currently I store those snippets in a simple WIKI table, but it would make even more sense to store it on this SQL editor form)
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 rafig » Tue Sep 29, 2015 3:35 pm

Hi
Bernd.N wrote:In case there is interest, we could add it to Servoy Forge.

I'm interested!
Would love to have something like that to plug in to some of my solutions...
Thanks
Rafi
Servoy Certified Developer
Image
rafig
 
Posts: 704
Joined: Mon Dec 22, 2003 12:58 pm
Location: Watford, UK

Re: SQL Editor for integration in any solution

Postby Bernd.N » Tue Sep 29, 2015 3:54 pm

Ok, we will put it on Servoy Forge as soon as possible.

Up to then, here is already the code that invokes the SQL command to actually run and also to flush the client's cache.
That should already be sufficient to just add a SQL text area and a run button, I guess.

The resultPane in the code is a large HTML area.

sTableName is needed for flushing.
You need to set sTableName first to the actual name of the table, so the first line with the var-declaration will not work as that is taken from the form. I commented it out therefore.
Just create another field and set sTableName as the dataprovider of that field.

Code: Select all
function runSQLStatement(){
   
//   var sTableName = forms.frm_sql_editor_tbl.checkedTableName;

   if (!sqlStatement || !sqlStatement.length) {
       resultPane = 'Statement is empty';
       return;}

    if (sqlStatement.replace(/[\n\r\t\s]/g, '').substr(0,6).toUpperCase() === 'SELECT') {

       try {
          resultPane = '<html>' + databaseManager.getDataSetByQuery(scopes.utils.DB.SERVER, sqlStatement, null, -1).getAsHTML() + '</html>';}
       catch(err) {
          resultPane = err;}
    }
    else {
       
       if (plugins.rawSQL.executeSQL(scopes.utils.DB.SERVER, null, 'set datestyle="ISO, DMY";'+sqlStatement)) {
          
          resultPane =  'SQL executed successfully';}
       
          if (sqlStatement.substring(0,6).toUpperCase() === "UPDATE"
                || sqlStatement.substring(0,6).toUpperCase() === "INSERT"
                || sqlStatement.substring(0,6).toUpperCase() === "DELETE") {
             
             // flush the cache for all clients
             plugins.rawSQL.flushAllClientsCache(scopes.utils.DB.SERVER, sTableName);
          }
          
       else {
          resultPane =  plugins.rawSQL.getException().getMessage();}
    }
}
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 rafig » Tue Sep 29, 2015 4:06 pm

Thanks!
Servoy Certified Developer
Image
rafig
 
Posts: 704
Joined: Mon Dec 22, 2003 12:58 pm
Location: Watford, UK

Sourcecode

Postby Bernd.N » Thu Oct 08, 2015 12:26 pm

Hi Rafi,

it will take a while until we are able to put this to Servoy Forge, therefore I decided to post the full sourcecode and the form snapshot here.
It should be possible to take that over and build an own app with that in short time.

There are three forms involved, so below you find the .js-files for those forms.
Attached are also screenshots of the three forms.

What you have to know:
- The resultPane is a HTML_AREA
- The small buttons all have this in their onAction: setType(e,_type)
- The two table forms neither have a datasource nor dataproviders attached to their fields

In case something is not clear, just ask.

Best, Bernd


frm_sql_editor.js
Code: Select all
/**
* @type {String}
* @properties={typeid:35,uuid:"72E3D460-2889-4E65-AD6C-9D48C8C698C1"}
*/
var notepadBox = "";

/**
* @type {Number}
* @properties={typeid:35,uuid:"0C2B8EB2-229F-4204-A745-A9435928DAC0",variableType:4}
*/
var sqlCommandType = 0;

/**
* @type {String}
* @properties={typeid:35,uuid:"AA42E6DA-A567-42E6-9833-FF466876E411"}
*/
var sqlStatement = '';

/**
* @type {String}
* @properties={typeid:35,uuid:"5B73885E-5791-4766-86D9-703C9599A6D6"}
*/
var resultPane = '';

/**
* @type {Number}
* @properties={typeid:35,uuid:"C9FA5B6C-9C0C-4EBD-9861-4FC5349E221F",variableType:4}
*/
var checkedAll = 0;

/**
* @param {JSEvent} event the event that triggered the action
* @properties={typeid:24,uuid:"2713B772-46C5-4FB6-A032-6BA013231769"}
*/
function onLoad(event) {

   popColumns('frm_sql_editor_tbl');
   popColumns('frm_sql_editor_col_tbl');
   
   resultPane = 'Ready';}

/**
* @properties={typeid:24,uuid:"64BC52E6-C7ED-4807-BF8A-2885097413F9"}
* @AllowToRunInFind
*/
function runSQLStatement(){
   
   var sTableName = forms.frm_sql_editor_tbl.checkedTableName;

   if (!sqlStatement || !sqlStatement.length) {
       resultPane = 'Statement is empty';
       return;}

    if (sqlStatement.replace(/[\n\r\t\s]/g, '').substr(0,6).toUpperCase() === 'SELECT') {

//       // search for "employees" preceded by a separator
//       if (!scopes.person.currentPersonIsDeveloper && sqlStatement.search(/\semployees/gi) > -1) {
//          
//          resultPane = 'Cannot perform actions on "emloyees".'
//          return;}
       
       try {
          resultPane = '<html>' + databaseManager.getDataSetByQuery(scopes.utils.DB.SERVER, sqlStatement, null, -1).getAsHTML() + '</html>';}
       catch(err) {
          resultPane = err;}
    }
    else {
       
       if (plugins.rawSQL.executeSQL(scopes.utils.DB.SERVER, null, 'set datestyle="ISO, DMY";'+sqlStatement)) {
          
          resultPane =  'SQL executed successfully';}
       
          if (sqlStatement.substring(0,6).toUpperCase() === "UPDATE"
                || sqlStatement.substring(0,6).toUpperCase() === "INSERT"
                || sqlStatement.substring(0,6).toUpperCase() === "DELETE") {
             
             // flush the cache for all clients
             plugins.rawSQL.flushAllClientsCache(scopes.utils.DB.SERVER, sTableName);
          }
          
       else {
          resultPane =  plugins.rawSQL.getException().getMessage();}
    }
}


/**
* Populates list of available table columns
*
* @author: Sergei Sheinin
*
* @param _frm
*
* @properties={typeid:24,uuid:"FB45DD0D-8295-49E7-9E9F-1188C528AEA0"}
*/
function popColumns(_frm) {

   var ds,
      dsNam,
      fldChk,
      fldNam,
      form,
      qry = '',
      /** @type  {JSDataSet} */
      src;

   if (_frm === 'frm_sql_editor_col_tbl') {
      dsNam = '_colsInTable';
      
      if (forms.frm_sql_editor_tbl.checkedTableName !== '') {
         qry = "\
            SELECT 0 AS chk, column_name AS nam\
            FROM information_schema.columns\
            WHERE table_schema = 'public'\
            AND table_name   = '" + forms.frm_sql_editor_tbl.checkedTableName + "'\
            ORDER BY column_name;";}
      else {
         qry = 'SELECT null AS chk, null as nam WHERE 1=0;';}}
   
   else if (_frm === 'frm_sql_editor_tbl'){
      dsNam = '_tablesInSchema';
      
      qry = "\
         SELECT 0 as chk, table_name AS nam\
           FROM information_schema.tables\
          WHERE table_schema='public'\
            AND table_type='BASE TABLE'\
          ORDER BY table_name;";}

   src = databaseManager.getDataSetByQuery(scopes.utils.DB.SERVER, qry, null, -1);
   ds = src.createDataSource(dsNam, [JSColumn.INTEGER, JSColumn.TEXT]);
   form = solutionModel.getForm(_frm);
   form.dataSource = ds;

   fldNam = form.getField('nam');
   fldNam.dataProviderID = 'nam';
   fldChk = form.getField('chk');
   fldChk.dataProviderID = 'chk';

   forms[_frm].controller.recreateUI();
   forms[_frm].controller.relookup();}

   
/**
* @author: Sergei Sheinin
* @since: Sep14
* @description: Creates skeleton SQL statement according to input param _type
*
* @properties={typeid:24,uuid:"534767B7-16A2-42DF-A3BF-2D8838A76734"}
*/
function setType(e, _type){

   var i,
      mx = forms.frm_sql_editor_col_tbl.checkedColumns.length,
      col = [],
      tbl = forms.frm_sql_editor_tbl.checkedTableName,
      val;

   for (i = 0; i < mx; i += 1) {
      col.push(forms.frm_sql_editor_col_tbl.checkedColumns[i])}
   
   if (!col.length && (_type === 'select' || _type === 'select_where')) {
      col[0] = '*';}
      
   if (tbl === '' || !col.length || _type === 'custom') {
      val = '';}
   
   else {
      switch (_type) {
      
         case 'select_where':
         case 'select': {
            val = 'SELECT ' + col.join(', ') + '\n' +
               'FROM ' + tbl;
            if (_type === 'select_where') {val += '\nWHERE ';}
            if (!(col.length === 1 && col[0] === '*')) {val += '\nORDER BY ' + col[0];}
            break;}
      
         case 'update': {
            val = 'UPDATE ' + tbl + ' SET\n' +
               col.join(' =\n') + ' = \n';
            break;}
      
         case 'insert': {
            val = 'INSERT INTO ' + tbl + '\n' +
               '(' + col.join(', ') + ')\n' +
               'VALUES ( ' + Array(col.length).join(', ') + ')';
            break;}}}
      
   sqlStatement = val;

   if (_type === 'select') {
      runSQLStatement();}
}

   
/**
* @author: Sergei Sheinin
* @description: activates click on all columns
*
* @properties={typeid:24,uuid:"3FB97F0C-A605-4AED-ABB6-4BF58F9A8B24"}
*/
function clkAllColumns(){
   
   forms.frm_sql_editor_col_tbl.clkAllColumns()
}

/**
* @param {JSEvent} event
* @private
* @properties={typeid:24,uuid:"9755E8E8-EA1A-4E8F-936F-A8436483DE38"}
*/
function deleteSqlStatement(event) {

   sqlStatement = "";
}
   
/**
* @param {JSEvent} event
* @private
* @properties={typeid:24,uuid:"57250B9D-192D-4C01-82EE-D6DCC2AF3748"}
*/
function deleteNotes(event) {

   notepadBox = "";
}
   


frm_sql_editor_tbl.js
Code: Select all
/**
* @type {Number}
*
* @properties={typeid:35,uuid:"58352A9F-96F2-40F7-98FE-A4F171351C1E",variableType:4}
*/
var checkedTable = 0;

/**
* @type {String}
*
* @properties={typeid:35,uuid:"6374772F-2B76-4E85-BB07-0D13E9F64782"}
*/
var checkedTableName = '';


/**
* @author: Sergei Sheinin
* @since: '14
* @description: activated when table is clicked
*
* @param e
*
* @properties={typeid:24,uuid:"B5CB0955-DE5C-416E-864A-1BB10A6C7978"}
*/
function clickTblName(e){

   var i = controller.getSelectedIndex();
   
   foundset.getRecord(i)['chk'] = !foundset.getRecord(i)['chk'];
   
   
   getTable();}

/**
* @author: Sergei Sheinin
* @since: '14
* @description: retrieves table columns
*
* @properties={typeid:24,uuid:"D5A2A1E8-FF56-48A5-BCBA-CD25A03F6483"}
*/
function getTable(){
   
   var ds,
      /** @type {foundset} */
      fs,
      i,
      mxI,
      r,
      isSet = false,
      tbForm = solutionModel.getForm('frm_sql_editor_tbl');
   
   ds = tbForm.dataSource;
   fs = databaseManager.getFoundSet(ds);
   fs.loadAllRecords();
   
   for (i = 1, mxI = databaseManager.getFoundSetCount(fs); i <= mxI; i++) {
      r = fs.getRecord(i)
      
      if (r['chk']) {
         
         isSet = true;
         
         if (checkedTable && checkedTable !== i) {
            foundset.getRecord(checkedTable)['chk'] = 0;}
      
         checkedTable = i;}}
   
   
   if (isSet) {
      checkedTableName = fs.getRecord(checkedTable)['nam'];}
   else {
      checkedTableName = '';}
   
   forms.frm_sql_editor.sqlStatement = '';
   forms.frm_sql_editor_col_tbl.checkedColumns = [];
   forms.frm_sql_editor.popColumns('frm_sql_editor_col_tbl');}



frm_sql_editor_col_tbl.js
Code: Select all
/**
*
* @properties={typeid:35,uuid:"5A8F286C-A3D0-4825-86DC-530D89EE8EFF",variableType:-4}
*/
var checkedColumns = [];

/**
* @properties={typeid:24,uuid:"CC613050-F599-44AF-8925-029D81838622"}
*/
function getColumn(){
   
   var allChecked = 1,
      ds,
      /** @type {foundset} */
      fs,
      i,
      mxI,
      r,
      tbForm = solutionModel.getForm('frm_sql_editor_col_tbl');
   
   ds = tbForm.dataSource;
   fs = databaseManager.getFoundSet(ds);
   fs.loadAllRecords();
   
   checkedColumns = [];
   
   for (i = 1, mxI = databaseManager.getFoundSetCount(fs); i <= mxI; i++) {
      r = fs.getRecord(i);
      
      if (r['chk']) {
         checkedColumns[checkedColumns.length] = r['nam'];}
      
      else {
         allChecked = 0;}}
   
   forms.frm_sql_editor.checkedAll = allChecked;}


/**
* @properties={typeid:24,uuid:"2C5BCBFF-165F-493B-9810-5D739D98A216"}
*/
function clkColumn(){

   var i = controller.getSelectedIndex();

   foundset.getRecord(i)['chk'] = !foundset.getRecord(i)['chk'];
   
   getColumn();}


/**
* @properties={typeid:24,uuid:"7DC26469-34B7-44DE-A98F-7974A3383930"}
*/
function clkAllColumns(){
   
   var ds,
   /** @type {foundset} */
   fs,
   i,
   mxI,
   tbForm = solutionModel.getForm('frm_sql_editor_col_tbl');
   
   ds = tbForm.dataSource;
   fs = databaseManager.getFoundSet(ds);
   fs.loadAllRecords();

   for (i = 1, mxI = databaseManager.getFoundSetCount(fs); i <= mxI; i++) {
   
      foundset.getRecord(i)['chk'] = forms.frm_sql_editor.checkedAll;}
   
   getColumn();}
Attachments
frm_sql_editor_col_tbl.jpg
frm_sql_editor_col_tbl.jpg (44.34 KiB) Viewed 12954 times
frm_sql_editor_tbl.jpg
frm_sql_editor_tbl.jpg (38.83 KiB) Viewed 12954 times
sql_editor_forms.jpg
sql_editor_forms.jpg (124.51 KiB) Viewed 12954 times
Last edited by Bernd.N on Thu Oct 08, 2015 12:33 pm, 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 juan.cristobo » Thu Oct 08, 2015 12:29 pm

Thanks!!
Juan
Madrid (Spain)

Servoy 7.4.x - MySQL / SQL Server 2008-2016
Windows 10 Pro
juan.cristobo
 
Posts: 186
Joined: Thu Apr 19, 2012 9:12 am

Re: SQL Editor for integration in any solution

Postby Bernd.N » Thu Oct 22, 2015 1:13 am

As someone had problems to get it running, I looked at it and found several times
scopes.utils.DB.SERVER
which is not included in the above code.

That is just a constant and holds the database name, in our case "bob". One needs to replace the complete term with a string of the own server name.

When it is still not running, check first if the popColumns() function works that should fill both tables.
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 jflener » Thu Oct 22, 2015 3:19 pm

MySQL did not like qry = 'SELECT null AS chk, null as nam WHERE 1=0;' so I had to remove the where clause. Now getting this:

Wrapped java.lang.RuntimeException: com.servoy.j2db.dataprocessing.DataException (sql_editor\forms\frm_sql_editor.js#130)
at sql_editor\forms\frm_sql_editor.js:130 (popColumns)
src = databaseManager.getDataSetByQuery("mydb", qry, null, -1);
jflener
 
Posts: 5
Joined: Wed Oct 01, 2014 3:27 pm

Re: SQL Editor for integration in any solution

Postby Bernd.N » Thu Oct 22, 2015 3:55 pm

It could be that MySQL does not like a SELECT clause without any WHERE and without any FROM.

I would try this:
var bDummy = 0
SELECT null AS chk, null as nam WHERE bDummy = 1
FROM information_schema.columns

If the WHERE-Clause does not work, you could create another WHERE-clause that refers to some information_schema-column but is false always.

However I do not know if at MySQL the naming of the "information_schema" is the same as with postgres, it could be that there are adjustments necessary.
I would try some SQL about that first in the SQL editor, and then adjust the JS-program as soon as you found out about 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

Re: SQL Editor for integration in any solution

Postby jflener » Thu Oct 22, 2015 4:47 pm

For MySQL
SELECT 0 as chk, table_name AS nam
FROM information_schema.tables
WHERE table_schema!='information_schema'
AND table_type='BASE TABLE'
ORDER BY table_name;

SELECT null AS chk, null as nam LIMIT 0;

*editing to remove some issues
jflener
 
Posts: 5
Joined: Wed Oct 01, 2014 3:27 pm

Re: SQL Editor for integration in any solution

Postby jflener » Thu Oct 22, 2015 9:15 pm

I have everything working, thank you! I've already started tweaking ;)

handle spaces in column names:
col.push(forms.frm_sql_editor_col_tbl.checkedColumns[i].indexOf(' ')<0 ? forms.frm_sql_editor_col_tbl.checkedColumns[i] : '`'.concat(forms.frm_sql_editor_col_tbl.checkedColumns[i]).concat('`') )}
jflener
 
Posts: 5
Joined: Wed Oct 01, 2014 3:27 pm

Re: SQL Editor for integration in any solution

Postby Bernd.N » Thu Oct 22, 2015 9:39 pm

You're welcome.
Spaces in column names can be tricky, we use _ to avoid them.
I think Sergei made a nice example with that about how to bind the datasource of a table to a dataset at runtime.
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 » Thu Oct 22, 2015 11:22 pm

Bernd.N wrote:I am lazy regarding typing and also prefer to do anything inside my own solution if possible, including SQL commands, instead of switching over to pgAdmin or some other DB tool.


? How about using the SQL Explorer plugin in Servoy/Eclipse? This is quite a sophisticated tool that allows to do everything you're doing here and more...
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

Next

Return to Sharing Central

Who is online

Users browsing this forum: No registered users and 3 guests