Servoy Forum

Discuss Servoy here!
https://forum.servoy.com/

SQL Editor for integration in any solution

https://forum.servoy.com/viewtopic.php?f=14&t=21001

Page 1 of 2

SQL Editor for integration in any solution

PostPosted: Fri Sep 11, 2015 12:11 pm
by Bernd.N
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.

Re: SQL Editor for integration in any solution

PostPosted: Fri Sep 11, 2015 12:26 pm
by logicimpresa
Very nice...

Remark regarding flushing

PostPosted: Fri Sep 11, 2015 12:30 pm
by Bernd.N
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)

Re: SQL Editor for integration in any solution

PostPosted: Tue Sep 29, 2015 3:35 pm
by rafig
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

Re: SQL Editor for integration in any solution

PostPosted: Tue Sep 29, 2015 3:54 pm
by Bernd.N
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();}
    }
}

Re: SQL Editor for integration in any solution

PostPosted: Tue Sep 29, 2015 4:06 pm
by rafig
Thanks!

Sourcecode

PostPosted: Thu Oct 08, 2015 12:26 pm
by Bernd.N
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();}

Re: SQL Editor for integration in any solution

PostPosted: Thu Oct 08, 2015 12:29 pm
by juan.cristobo
Thanks!!

Re: SQL Editor for integration in any solution

PostPosted: Thu Oct 22, 2015 1:13 am
by Bernd.N
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.

Re: SQL Editor for integration in any solution

PostPosted: Thu Oct 22, 2015 3:19 pm
by jflener
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);

Re: SQL Editor for integration in any solution

PostPosted: Thu Oct 22, 2015 3:55 pm
by Bernd.N
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.

Re: SQL Editor for integration in any solution

PostPosted: Thu Oct 22, 2015 4:47 pm
by jflener
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

Re: SQL Editor for integration in any solution

PostPosted: Thu Oct 22, 2015 9:15 pm
by jflener
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('`') )}

Re: SQL Editor for integration in any solution

PostPosted: Thu Oct 22, 2015 9:39 pm
by Bernd.N
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.

Re: SQL Editor for integration in any solution

PostPosted: Thu Oct 22, 2015 11:22 pm
by ptalbot
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...
All times are UTC + 1 hour [ DST ]
Page 1 of 2