Re: [Lazarus] Cross Platform SQL Client/Editor
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. 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). 3) It doesn't allow for multiple datasets to be returned. 4) Running "select columns into dest_table from source_table" crashes lazdatadesktop as described in issue #1 5) Running "create database new_database" crashes lazdatadesktop as described in issue #1. 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. 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. 8) There should be a database selector to allow the user to change databases on connections which support that functionality. 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. 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. 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. 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. 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? Execution of selected text should be supported. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Cross Platform SQL Client/Editor
On Sat, 2 Jan 2016, Anthony Walter wrote: Michael, Thanks for the changes. I am checking them out and will create a new build to test the changes. If it works I may contribute a few changes to improve usability such as a connection wizard or some other dialog to guide first time users in creating/configuring a database connection. I know from my experience that I had to tweak the mssql connection to allow for statements such as "create database". I will test this. Also there should be a database selector, which when dealing with mssql allows the same connection to address a different database on the same server instance. You can register a connection string dialog per category/type of database, the current dialog is a generic one which is simply registered for any kind of database. But it is possible to register a specialized one for SQL server. Again I will check for this. Please do. Note that I haven't got MS-SQL server myself, so I am unable to perfom any testing myself for this kind of server. But Laco (Lacak in Mantis) is active in DB development and has MS SQL server experience, I have asked him already to test (and possibly improve) the ms sql datadict component. But any other area: feel free to mail and suggest/ask for improvements. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Cross Platform SQL Client/Editor
Michael, Thanks for the changes. I am checking them out and will create a new build to test the changes. If it works I may contribute a few changes to improve usability such as a connection wizard or some other dialog to guide first time users in creating/configuring a database connection. I know from my experience that I had to tweak the mssql connection to allow for statements such as "create database". I will test this. Also there should be a database selector, which when dealing with mssql allows the same connection to address a different database on the same server instance. Again I will check for this. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Cross Platform SQL Client/Editor
On Sat, 2 Jan 2016, Anthony Walter wrote: Michael, Just FYI currently there is no "fpddmssql.pp" (the dd is for data dictionary I suppose) so no, it's not as easy as just adding a unit name to the project source. New units must be created and a register function must be create for mssql to appear in the connections list. I have just committed initial support for this. And as far as your surprise at my lack of research goes, you need to rethink about discovery. The first thing your app does is show a blank list view of data dictionaries rather than help users create a database connection first. Next after switching to the connections tab you have to know to right click in the blank list view to get a context menu which gives you the option to create a connection. Forcing users to find your hidden options by using right click context menus is terrible design. Next we get to the point where these is no option for mssql. You say you simply include a unit, but in reality no mssql connection register unit currently exists, so it's not that simple. I was writing in the belief this unit existed. I was wrong, but this is now remedied. When you despair if people "didn't investigate very deeply", how far do you want users to go? You need to understand that most people are not going to go through a deep investigation before trying to resolve problems deciding if your tool suits them or even works for their needs. Before a _seasoned developer_ (I would say you qualify?) starts writing his own tool, I do expect him to investigate, yes. Let me explain: - It should be clear what the database desktop is for (more or less). It's clearly not a browser or image viewer. - That being the case, if it still doesn't do something you want, then that is a reason for at least investigating. - If the code is not clear: ask on the mailing list, this IS open source. I cannot imagine that this is more work than reimplementing it from scratch. To improve your tool I'd suggest: If there is no stored connection, you should have a database connection setup screen as the first thing users see when launching your app. Well, I have added a popup saying what to do. I don't believe starting the dialog as once as you suggest is the right thing to do, so I offer people the choice. Clicking one of the 2 'Ok' buttons take them to the appropriate dialog, as you suggest. If there are any hidden right click menus, you should supplement them with items in a menu menu or a toolbar with appropriate icons and tooltips. As I said in a previous mail: The item in the menu was there, since day one. This, and this alone, prompted my remark about depth of investigation. That it is not there in the toolbar was indeed a lack which has now been remedied. Provide in the sources code at least a comment about how to add additional database providers. Done. Also calling database providers (what the rest of the world calls them) "dictionaries" (RegisterDictionaryEngine) isn't helping the situation any. The name is there simply because of the architecture of the tool: the intent was (and still is, though secondary) to promote the use of the dictionary in DB apps. They make life easier. You cannot add a connection type without a corresponding data dictionary engine. I have made the connections more prominent by putting them first. I hope that recent commits convince you that we do want people to work with these tools, and that we're open for suggestions. I repeat: comments, suggestions, patches are welcome. Certainly where UI design is concerned, which is - admittedly - not my forte. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Cross Platform SQL Client/Editor
Michael, Just FYI currently there is no "fpddmssql.pp" (the dd is for data dictionary I suppose) so no, it's not as easy as just adding a unit name to the project source. New units must be created and a register function must be create for mssql to appear in the connections list. And as far as your surprise at my lack of research goes, you need to rethink about discovery. The first thing your app does is show a blank list view of data dictionaries rather than help users create a database connection first. Next after switching to the connections tab you have to know to right click in the blank list view to get a context menu which gives you the option to create a connection. Forcing users to find your hidden options by using right click context menus is terrible design. Next we get to the point where these is no option for mssql. You say you simply include a unit, but in reality no mssql connection register unit currently exists, so it's not that simple. When you despair if people "didn't investigate very deeply", how far do you want users to go? You need to understand that most people are not going to go through a deep investigation before trying to resolve problems deciding if your tool suits them or even works for their needs. To improve your tool I'd suggest: If there is no stored connection, you should have a database connection setup screen as the first thing users see when launching your app. If there are any hidden right click menus, you should supplement them with items in a menu menu or a toolbar with appropriate icons and tooltips. Provide in the sources code at least a comment about how to add additional database providers. Also calling database providers (what the rest of the world calls them) "dictionaries" (RegisterDictionaryEngine) isn't helping the situation any. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Cross Platform SQL Client/Editor
On Sat, 2 Jan 2016, Sven Barth wrote: Am 02.01.2016 15:55 schrieb "Anthony Walter" : Sven, I don't see MSSQL support. I don't want to setup ODBC DSNs, It *does* support MSSQL. It supports all databases that FPC supports. On my system LazDataDesktop does not show MSSQL as an option: http://cache.getlazarus.org/images/where-is-mssql.png Hmm... Then the unit was not added. Simply file a bug report then as FPC supports it definitely and thus there's no reason why the LazDataDesktop shouldn't... The (historical) reason is explained in my mail. I am working on it right now, together with the 'test connection' button in the new connection dialog. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Cross Platform SQL Client/Editor
Am 02.01.2016 15:55 schrieb "Anthony Walter" : > > Sven, > > >> I don't see MSSQL support. I don't want to setup ODBC DSNs, > > > It *does* support MSSQL. It supports all databases that FPC supports. > > On my system LazDataDesktop does not show MSSQL as an option: > > http://cache.getlazarus.org/images/where-is-mssql.png Hmm... Then the unit was not added. Simply file a bug report then as FPC supports it definitely and thus there's no reason why the LazDataDesktop shouldn't... Regards, Sven -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Cross Platform SQL Client/Editor
Sven, >> I don't see MSSQL support. I don't want to setup ODBC DSNs, > It *does* support MSSQL. It supports all databases that FPC supports. On my system LazDataDesktop does not show MSSQL as an option: http://cache.getlazarus.org/images/where-is-mssql.png -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Cross Platform SQL Client/Editor
On Sat, 2 Jan 2016, Graeme Geldenhuys wrote: On 2016-01-02 09:38, Michael Van Canneyt wrote: but it is very frustrating to see the wheel being reinvented 100 times over. +1 The perils of open source in general. Not everything needs to be reinvented over and over - sometimes fixes and tweaks are all that should be required to move forward. In this spirit: See my other mail about the lazarus desktop... Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
[Lazarus] Lazarus database desktop improvements...
Hello, Following some recent criticism on the Lazarus database desktop: I have, in revision 51118, added a new interface to the database desktop, which is hopefully more in line with what current trends are, and which is considered more intuitive. (being 45, I realize I am old school, so please bear with me) The connections/dictionaries are shown at the left in a tree, which is always visible. I have added some icons in various places. (probably horrible looking, but then so am I after the recent festivities ;) ) There is also a button to start a new connection. It is not yet finished. Some ideas that still must be done; I'll probably work on some of them today. - treeview should be read-only, but when I activated that, the tree just - disappeared ? Needs investigating. - Add a context menu to new tree view. Should be easily done. - Add a 'Test connection' button to the new connection dialog. The old legacy interface can still be activated using a new menu setting in the 'View' menu. All this just to show that it really isn't that hard to do, we're open for criticism, and we'd really like people to use and like the tools we provide with Lazarus. If they like the start, maybe they will be more likely to send patches and improve the tools even more. I invite people to test and to give comments, patches, suggestions. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Cross Platform SQL Client/Editor
On 2016-01-02 09:38, Michael Van Canneyt wrote: > but it is very frustrating to see the wheel being reinvented 100 times over. +1 The perils of open source in general. Not everything needs to be reinvented over and over - sometimes fixes and tweaks are all that should be required to move forward. Regards, - Graeme - -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Cross Platform SQL Client/Editor
Graeme Geldenhuys wrote: On 2016-01-02 00:11, Mark Morgan Lloyd wrote: I've yet to see a GUI frontend which is remotely usable when attached to a table with a few billion rows. Then I guess you have never seen the setting in most GUI frontends that limit the return result to say 500 records max. All the GUI SQL Editors I've used supports this. In fact, they normally use the feature that already exists in most SQL database servers to accomplish this. Actually yes, I have. And almost always found it broken once one starts trying to give it complex selection criteria. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Cross Platform SQL Client/Editor
On Sat, 2 Jan 2016, Graeme Geldenhuys wrote: On 2016-01-02 00:10, Anthony Walter wrote: Straight off I don't see a SQL editor either. It of course has that - the Laz Data Desktop just doesn't have a very intuitive interface. It also has some bugs or oddities. But saying that, it also has some useful features like extendible code generation etc. Patches or suggestions for changes are highly appreciated. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Cross Platform SQL Client/Editor
On Fri, 1 Jan 2016, Anthony Walter wrote: Michael, I don't want a "visual" table builder and I'm unsure how many others do. I don't want a visual table builder either, where did you see that functionality ? If you are referring to the data dictionary: this is not the only part of the desktop. All I want is something to connect me to a database engine where I can then execute bits of SQL (it executes what you highlight), which is what my program does. This is what the database desktop also does. Lazarus database desktop seems geared towards everything I don't want. I don't see MSSQL support. That is a matter of including the mssql unit. It didn't exist at the time the database desktop was written. I don't want to setup ODBC DSNs, I just want a straight MSSQL connection. You will have it. You don't need to set up ODBC at all ? I don't see an simple create/test connection screen. Eh ? What do you see under the 'Connections' menu ? Right clicking in a blank list view to get a menu and then selecting an option is just bad design. See above. Straight off I don't see a SQL editor either. Of course not, you need a connection first. Then you'll jump straight to the sql editor. The sql editor is no good without a connection. So I think you'll understand why I don't want to use Lazarus database No, I honestly don't. I do understand from what you write that you didn't investigate very deeply. desktop, and no I don't want to improve it. I'd rather have a stand alone program for anyone to use on any platform which can connect to MSSQL (or MySQL) and let you edit and execute SQL. And finally I don't see the point of using the Lazarus moniker for such a tool. Sure, suit yourself. That is the despair and frustration of Open source: everybody suits himself. The Database tool is committed to the lazarus SVN in the hope that people will improve on it. Its design may be improved. I am aware the design is not intuitive, but there is simply a historical reason for the current design. It is not set in stone, far from it. We cannot do everything outselves: - Create basic functionality in rtl/fcl/lcl - Fix bugs - Create documentation - Create useful tools - Provide help on mailing lists If everyone shoots off at yet another tangent instead of improving what exists, no progress is made. Please do not take this as a token that I do not appreciate your efforts, but it is very frustrating to see the wheel being reinvented 100 times over. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus