[Lazarus] Cross Platform SQL Client/Editor

[Michael Van Canneyt]

On Sat, 2 Jan 2016, Anthony Walter wrote:

> Michael,
>
> I checked out the lasted code from
> svn/fpc/trunk/packages/fcl-db/src/datadict and
> svn/lazarus/trunk/tools/lazdatadesktop.
>
> Everything builds fine and it looks/works a lot better. Although I can now
> connect to a remote mssql server, I found some problems with the "Run
> query".
>
> Here is a short issue list of the problems I nottice off-hand:
>
> 1) It doesn't handle typos or incorrect SQL statement very well. The app
> presents an uncaught exception error dialog and asks you for permission to
> terminate lazdatadesktop.

This is the standard exception dialog of lazarus.

>
> 2) It doesn't allow a selected block of text to be run, rather it tries to
> execute everything in the query window (see also issue #7).

This is by design.

I personally consider this use case to be fundamentally flawed.
How am I to choose the difference between the 2 cases ?

But I am willing to reconsider this, as I know this is a personal preference.
So how about adding a second button to just execute the selection ?

>
> 3) It doesn't allow for multiple datasets to be returned.

This is by design. SQLDB does not allow this either (if it does for an
engine, it is purely by accident). You can execute 1 statement at a time.

Allowing more statements would require advanced parsing of SQL to detect the
various statements and submit them separately to the engine.

I have plans for a script window, however. (see below about this all)

> 4) Running "select columns into dest_table from source_table" crashes
> lazdatadesktop as described in issue #1

Probably because SQLDB detects this as a select statement, not as an insert
statement. This syntax is specific to MS-SQL server. Normally, you do a

"insert into desttable (fields) select fields from sourcetable"

Please report a bug about this.

> 5) Running "create database new_database" crashes lazdatadesktop as
> described in issue #1.

What is the exact error ?

> 6) It seems to want to return a live editable dataset and you must have to
> press a stop button before the run query button is enabled again. There
> should be some option to allow for a query to be run either without being
> live, or change run to allow it to close then open a new query.

I am not sure what you mean here ?

>
> 7) I'm not sure how useful the next/previous buttons are. I think most
> people tend to want to maintain a big script they keep on disk containing
> "if exists () drop table/procedure/function" followed by a "create
> table/procedure/function" statement. They don't want a bunch of small SQL
> clips kept in memory which they use forward and back arrows to navigate.

I have never seen anyone work like what you describe. 
I do use scripts of course.

The "big script" use case - as you put it - is not meant for this window.

The window is there to test and execute single statements. Keeping that in
mind, the history mechanism is useful. It exists in all other tools I use or
have used.

But as I said:

I have plans for a script window, which is what you are looking for.

You should keep in mind that the ability to execute multiple statements at once is not
supported by all SQL engines. I am not aware of what MS-SQL server does, but
offhand, the only engine I know that does it is Postgres. MySQL and Firebird
certainly do not.

SQLDB is not designed for it, so support for this depends on a separate
scripting tool which detects the statements (see the sqlscript unit), and
using the sqlscript tool, I will build a scripting window.

>
> 8) There should be a database selector to allow the user to change
> databases on connections which support that functionality.

That approach is simply not possible using SQLDB. 
in SQLDB, 1 connection = 1 database.

But see below.

> To fix:
>
> All error messages related to SQL execution issues (table already exists,
> invalid statements, ect) should be routed to a message area below the
> script window. In this way a user can examine the message while trying to
> fix their SQL, rather than having to dismiss a window and trying to
> remember the details of the error message while trying to fix their SQL. In
> all cases, users should never be presented with error dialog asking for
> permission to terminate the app because their SQL is invalid.

Agreed 100%, now the window just uses the default exception mechanism.
I will work on this at once.

>
> Create database and database switching need to work. If I have a SQL
> client/editor which cannot run a  "create database" statement, then that
> editor is broken IMO.

Agreed about the create.

Switching : see below.

> Every SQL client/editor where the connection supports multiple databases
> (mssql, firebird, mysql, postgre) should give users the ability to switch
> databases without the need to define new connections.

If you write this, I think you have never used firebird :)

>  Users should be able
> to see the databases for a given connection and have an easy way to switch
> the connection to a different database, possible integrated with the "use"
> SQL command.

As I said, in SQLDB :  Database = Connection.

You are maybe not aware of this, but many database engines simply do not support
the idea that you present. You use MS-SQL server, if it exists there this is a 
concept that exists purely in that architecture.

Postgres, MySQL, Firebird, sqlite : you always connect to a specific
database, a connection is not to a server instance. 
(same goes for some other embedded engines I know of)

So, all tools I have ever used use require a connection to a specific database. 
You need to specify the database as the first thing.

You must realize that lazarus database desktop is a general purpose tool;
we can only go so far in accomodating the specifics of certain database
engines.

That being said:

What we can do, is see if we can create a component that queries the
databases available on a server for engines that support it, and register
all databases at once in the list of connections.

But firebird and sqlite for example, do not support this feature. You cannot
ask a list of databases on a server. (local machine in the case of sqlite)

Based on this, in the query window, we can maybe present a dropdown of
databases on the same server, but then we'd need to re-establish connections
and whatnot in the background.

This is going to be hell to support :/


> Multiple dataset return should be supported. My program (at the top of this
> thread) handles it, along with proper error message handling. I'd say the
> live/editable dataset thing should be an extra option and not the default,
> but that's me. Maybe we need a poll on this but I'd be curious to know how
> many people who are typing end executing SQL prefer to just get results,
> error messages or a live datagrid?

So, what to do in the case of a select statement ?

> Execution of selected text should be supported.

No problems there.

Michael.