Can I run a query inside of a QtScript function?

[JrgMyr]

I'd like to write a table analyzer function in QtScript. It receives a table name as parameter and runs a number of analysis queries against the table. Then it creates a new table and stores all analysis results there. For that I need to connect from inside the QtScript function to the database and run some SQL queries. The table analyzer would be invoked from an editor window.

1. Is it possible to run queries from inside a QtScript function?
2. Are there any syntax examples?

The code is ready as a stand-alone Perl script and can be run from a shell against the SQLite database file.
However, I would rather run it from inside the SQLiteStudio.

[JrgMyr]

This is how far I got:

function analyzeTBL();

var table = arguments[0];
var field = arguments[1];

var sql = 'SELECT COUNT(*), ' +
          'SUM(CASE WHEN ' + field + ' ISNULL THEN 0 ELSE 1 END), ' +
          'SUM(CASE ' + field + ' WHEN "" THEN 1 ELSE 0 END), ' +
          'COUNT(DISTINCT ' + field + '), ' +
          'MIN(' + field + '), ' +
          'MAX(' + field + ') ' +
          'FROM ' + table;

var record = db.eval(sql);

return 'Table ' + table + ' ' + record[0][0] + ' lines, ' +
       'field ' + field + ' ' + record[0][1] + ' values, ' +
       'thereof ' + record[0][2] + ' not blank, ' +
       'from ' + record[0][3] + ' different values.';

https://github.com/pawelsalawa/sqlitestudio/wiki/ScriptingQt says I should not run CREATE or INSERT queries inside the function.

Is there a clever way to present more results for all fields in the table (instead of four results from a single field)?

[pawelsalawa]

To be honest I don't remember why I made such a comment on manual page for ScriptingQt. For the ScriptingTcl it is slightly different - it says not to execute INSERT/DELETE/UPDATE on the very table, from which you are selecting at the moment when you are executing this funcion. This makes more sense, because if you SELECT and INSERT at the same time, you end up in the self-recursion, which will end after 2000 steps of recursion (depending on the SQLite compile-time option, but for me now it's 2000).

I think you should be fine using CREATE and INSERT, as long as you don't modify the very table you are SELECTing from at the moment. I mean for example this below - as something forbidden. The scalar function named aaa():

db.eval('INSERT INTO xyz VALUES (1)');

The SQL query:

SELECT aaa(), id FROM xyz;

Please avoid such operations! Recursion does not work efficiently with scalar functions!

[JrgMyr]

That sounds promising. Thanks a lot.
I'll extend my experimenting to include creating and inserting into a temp table.
Then the function can analyze table "abc" and insert the results into "abc_stat".