SQL Editor for integration in any solution

Bernd.N · September 11, 2015, 10:11am

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.

logicimpresa · September 11, 2015, 10:26am

Very nice…

Bernd.N · September 11, 2015, 10:30am

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).

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)

rafig · September 29, 2015, 1:35pm

Hi

Bernd.N:
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

Bernd.N · September 29, 2015, 1:54pm

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.

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();}
    }
}

rafig · September 29, 2015, 2:06pm

Thanks!

Bernd.N · October 8, 2015, 10:26am

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

/**
 * @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

/**
 * @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

/**
 *
 * @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();}

juan.cristobo · October 8, 2015, 10:29am

Thanks!!

Bernd.N · October 21, 2015, 11:13pm

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.

jflener · October 22, 2015, 1:19pm

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);

Bernd.N · October 22, 2015, 1:55pm

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.

jflener · October 22, 2015, 2:47pm

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 · October 22, 2015, 7:15pm

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.indexOf(’ ')<0 ? forms.frm_sql_editor_col_tbl.checkedColumns : ‘'.concat(forms.frm_sql_editor_col_tbl.checkedColumns*).concat('’) )}*

Bernd.N · October 22, 2015, 7:39pm

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.

ptalbot · October 22, 2015, 9:22pm

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.

? 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…

Bernd.N · October 22, 2015, 10:23pm

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:

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
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
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:

ptalbot · October 22, 2015, 10:46pm

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.

Bernd.N · October 22, 2015, 10:50pm

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.

Bernd.N · October 24, 2015, 6:39pm

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:

/**
 * 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 = "";
}

sergei.sheinin · November 21, 2015, 1:24pm

SQL Editor now available for download at Servoy Forge: Overview - SQLEditor - ServoyForge

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

Sergei
http://js2dx.com

Topic		Replies	Views
Today I learned Classic Servoy	20	30022	March 14, 2017
Servoy Advanced Programming Guide For FileMaker Developers Classic Servoy	29	24952	September 4, 2003
Servoy, the GOOD, the BAD, the UGLY Classic Servoy	12	15928	June 24, 2005
Manage Results from SQL Searches Classic Servoy	49	31464	March 27, 2004
Viewing the SQL query results in horizontal format. Classic Servoy	41	23350	July 24, 2007

SQL Editor for integration in any solution

Related topics