Bug 148192 - Create a new option External Firebird file under Create new database
Summary: Create a new option External Firebird file under Create new database
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
7.3.0.3 release
Hardware: All All
: medium enhancement
Assignee: jcsanz
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Wizard
  Show dependency treegraph
 
Reported: 2022-03-25 23:06 UTC by jcsanz
Modified: 2022-12-12 12:08 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Database Wizard, which should show the right position for entries (94.39 KB, application/pdf)
2022-03-26 07:44 UTC, Robert Großkopf
Details
Image Connection to a Firebird server database (88.27 KB, image/png)
2022-03-26 23:12 UTC, jcsanz
Details
Connection to a registered Firebird server database (27.87 KB, image/png)
2022-03-26 23:19 UTC, jcsanz
Details
Connection to localhost Ubuntu Linux KDE (193.64 KB, image/png)
2022-03-27 11:08 UTC, jcsanz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description jcsanz 2022-03-25 23:06:21 UTC
Currently, in the first step of the database wizard, three options are available.
 
When selecting the first option, *Create* a new database, if the experimental functionalities have been enabled, it proposes two options Firebird embedded and HSQLDB embedded (but it does not propose to create an external Firebird archive).

However, if we select the third option, Connect to an *existing* database, in the next step, we can connect to an existing Firebird file (by selecting the Browse button) but in addition we have a *Create new* button, which allows us to create a new external Firebird file.

This behavior can mislead the user, who does not see the way to create a new Firebird database file in the option that should correspond to it (Create a new database) while the option to create the database is in Connect to an *existing* file.

So I believe that adding a new entry under Create new database that allows you to create an external Firebird database and at the same time removing the Create new button from Connect to an existing database would improve the user experience by having the options in the places where they should be expected.

In addition, the user would have the two options for creating Firebird databases (embedded and external) in the same place, allowing them to decide which is best option
Comment 1 B. Antonio Fernández 2022-03-26 00:13:19 UTC
I am of the same opinion, the user should have the option to create a firebird database to use it as external database
Comment 2 Robert Großkopf 2022-03-26 07:41:16 UTC
Seems the database wizard needs some updates.

If you choose "Create a new database" all databases, which could directly be created, should be offered. So there should be added
"External database: Firebird file"

This should be removed at the position, where a connection could made to an existing database.
Comment 3 Robert Großkopf 2022-03-26 07:44:16 UTC
Created attachment 179116 [details]
Database Wizard, which should show the right position for entries

Here three screenshots with different bugs in database wizard.
Comment 4 LucasPatan 2022-03-26 13:15:48 UTC
Logical option, The creation of a new database must be in the menu option "Create"
Comment 5 m.a.riosv 2022-03-26 14:30:08 UTC
+1
Comment 6 jcsanz 2022-03-26 18:56:59 UTC
> This should be removed at the position, where a connection could made to an
> existing database.

Since it is possible to connect to a existing database in a file o hosted in a server, only the "Create new" button should be removed, but maintaining the possibility to connect to a existing database.
Comment 7 Robert Großkopf 2022-03-26 19:52:22 UTC
(In reply to jcsanz from comment #6)
> > This should be removed at the position, where a connection could made to an
> > existing database.
> 
> Since it is possible to connect to a existing database in a file o hosted in
> a server, only the "Create new" button should be removed, but maintaining
> the possibility to connect to a existing database.

Same as in bug 148186: The internal driver for Firebird isn't the same which could be used for connecting through a server. The file must be in a path, which is writable and readable for the special user. Connecting to Firebird server is only possible by JDBC and ODBC (as I knew and have tested for Base Handbook).
Comment 8 jcsanz 2022-03-26 23:12:06 UTC
Created attachment 179131 [details]
Image Connection to a Firebird server database

It is posible see a server database attached by internal driver and by JDBC simultaneously
Comment 9 jcsanz 2022-03-26 23:19:58 UTC
Created attachment 179132 [details]
Connection to a registered Firebird server database

The connection in this case is to a registered database in the firebird server databases.config file
Comment 10 jcsanz 2022-03-26 23:24:06 UTC
(In reply to Robert Großkopf from comment #7)
> (In reply to jcsanz from comment #6)
> > > This should be removed at the position, where a connection could made to an
> > > existing database.
> > 
> > Since it is possible to connect to a existing database in a file o hosted in
> > a server, only the "Create new" button should be removed, but maintaining
> > the possibility to connect to a existing database.
> 
> Same as in bug 148186: The internal driver for Firebird isn't the same which
> could be used for connecting through a server. The file must be in a path,
> which is writable and readable for the special user. Connecting to Firebird
> server is only possible by JDBC and ODBC (as I knew and have tested for Base
> Handbook).

As you can see in the attached images, it is possible to connect to a server database by mean of the internal driver (at least in Windows, I'm not sure in some Linux)
In the first I've connected simultaneously to the same database, one connection by mean of JDBC driver and the other by mean of internal driver.
In the second image it is clear than the connection is established to the server, becouse is a connection to a registered database and you can see the is done with "firebird External" driver
Comment 11 Robert Großkopf 2022-03-27 07:50:32 UTC
(In reply to jcsanz from comment #10)
> (In reply to Robert Großkopf from comment #7)
> > (In reply to jcsanz from comment #6)

Screenshots will show a whole path. So you could connect trough direct driver with the default SYSDBA.

I'm not able to connect with direct driver to localhost here. My connection with JDBC:
firebirdsql:oo://localhost/externtest.fdb?charSet=UTF-8
and the connection with direct driver:
/srv/firebird/externtest.fdb
(which I couldn't choose through database wizard, because it is a protected folder)

If I try the same with localhost… the file couldn't be found. So I couldn't connect from any other position as my own computer.

Then I tried to change and use another user 'lotest'. I could add another user in direct connection, but I'm not possible to set 
GRANT RDB$ADMIN TO lotest;
because there is no possibility to do this with the internal driver, because user administration isn't implemented.
Changed to JDBC to do this.

I don't know if we could say the direct driver for Firebird could really work with the server database.
Comment 12 jcsanz 2022-03-27 11:08:24 UTC
Created attachment 179137 [details]
Connection to localhost Ubuntu Linux KDE
Comment 13 jcsanz 2022-03-27 11:37:31 UTC
> Then I tried to change and use another user 'lotest'. I could add another
> user in direct connection, but I'm not possible to set 
> GRANT RDB$ADMIN TO lotest;

Connection and permissions are a "little bit" cumbersome in Firebird.
You need to grant at least read rights on the data base to 'lotest' more over Admin rights

> because there is no possibility to do this with the internal driver, because
> user administration isn't implemented.

Many databases need to have a previous user before the first connection. Usually database root or database admin is implemented locally and don´t allow remote connections if not is granted specifically. I think Firebird is one of them.

> 
> I don't know if we could say the direct driver for Firebird could really
> work with the server database.

By the moment, you can connect and work with the database. May be is not working properly, but this should be another different bug, shouldn't it?
Comment 14 jcsanz 2022-03-27 11:49:56 UTC
Comment on attachment 179137 [details]
Connection to localhost Ubuntu Linux KDE

As can see in the image the connection is established by server, and not by user JC whom haven't rights on the /data folder
Comment 15 Robert Großkopf 2022-03-27 14:26:13 UTC
(In reply to jcsanz from comment #14)
> Comment on attachment 179137 [details]
> Connection to localhost Ubuntu Linux KDE
> 
> As can see in the image the connection is established by server, and not by
> user JC whom haven't rights on the /data folder

OK, could connect this way.
Server config file here says
DatabaseAccess = Restrict /srv/firebird
so the path in OpenSUSE will be localhost:/srv/firebird/externtest.fdb

Thanks for this tests!

This path isn't available in normal way through the GUI of a user. So it couldn't be fund by the database wizard, if I browse through my system. And server will only be available in this path, which is also be set by the system for all Firebird data files, which will connect to the server.

If we will offer this option also the connector to server database should
→ connect to localhost directly, means he must read the path for data from the Firebird server (DatabaseAccess)
→ allow all user-management as it is possible through JDBC
→ must also connect to a server, which will allow to open files in Firebird versions like Firebird 4.0

The internal driver will produce an error with Firebird 4 files. But the internal databases won't be changed from Firebird 3 to Firebird 4. Databases, which have been used by other people, should be also usable in future. So it could be we need different drivers for the internal and the external server database.

At this moment the only database, which could be created without problems, using the wizard, is the simple external file. And this should be available under "Create database", not under "Connect to an existing database".
Comment 16 jcsanz 2022-03-27 15:22:18 UTC
(In reply to Robert Großkopf from comment #15)
> (In reply to jcsanz from comment #14)
> > Comment on attachment 179137 [details]
> > Connection to localhost Ubuntu Linux KDE
> > 
> > As can see in the image the connection is established by server, and not by
> > user JC whom haven't rights on the /data folder
> 
> OK, could connect this way.
> Server config file here says
> DatabaseAccess = Restrict /srv/firebird
> so the path in OpenSUSE will be localhost:/srv/firebird/externtest.fdb
> 
> Thanks for this tests!
> 
> This path isn't available in normal way through the GUI of a user. So it
> couldn't be fund by the database wizard, if I browse through my system. And
> server will only be available in this path, which is also be set by the
> system for all Firebird data files, which will connect to the server.
> 

It depends on the configuration. I restricted all rights just to test, but it is possible allow read and/or execution to all users, but usually is not a good idea.

> If we will offer this option also the connector to server database should
> → connect to localhost directly, means he must read the path for data from
> the Firebird server (DatabaseAccess)

Not necessarily, according with documentation, firebird can establish tree different kinds o connections, and the kind depends on the connection string: 
-- if you use "file://..." you establish a embedded (serverless) connection, just as you do before ver 7.3. In this case you can navigate and create a database file in anywhere you have rights (may be your /home folder)
-- if you use "localhost[/port]:..." you establish a loopback connection through the server
-- if you use "ServerName[/port]:..." you establish a remote connection, also if you are in the same computer than the server


> → allow all user-management as it is possible through JDBC
I hope the same in "direct" connection too

> → must also connect to a server, which will allow to open files in Firebird
> versions like Firebird 4.0
> 
> The internal driver will produce an error with Firebird 4 files. 
yes, this is a limitation, the internal driver is ver 3 

>But the
> internal databases won't be changed from Firebird 3 to Firebird 4.

(I think it could be changed but not tested by now)

> Databases, which have been used by other people, should be also usable in
> future. So it could be we need different drivers for the internal and the
> external server database.
May be only offer the possibility of a backup/restore that is the usual way of migrate old to new database format

> 
> At this moment the only database, which could be created without problems,
> using the wizard, is the simple external file. And this should be available
> under "Create database", not under "Connect to an existing database".

Yes, completely agree with this
Comment 17 Heiko Tietze 2022-03-30 07:00:14 UTC
Sounds like a discussion of many different topics. 

Regarding the creation of a new external db, the question is whether we have to support other formats as well in this case. Dbase, PostgreSQL might be feasible but how about Calc or Text?

If we create external DB, I would expect it to happen at the first step, like Robert commented in #2 (dropdown with "Firebird embedded", "FB external", "Dbase"...) . The details could be entered at the second wizard step, similar to some connections to existing DB.

Alternatively, taking the "Create New" button from the second step in case of FB into the wizard's first step next to the dropdown of "Connect to existing DB", requires the button to be disabled for all other types. Or to create most other types of DB as well.
Comment 18 jcsanz 2022-03-30 07:27:28 UTC
(In reply to Heiko Tietze from comment #17)
> Sounds like a discussion of many different topics. 

May be two topics, create a new firebird database and connect to a existing database, but not easy to split because currently both are in the same place in the wizard (and split them into two different options is what I'm asking

> Regarding the creation of a new external db, the question is whether we have
> to support other formats as well in this case. Dbase, PostgreSQL might be
> feasible but how about Calc or Text?

We don´t have to support any *new* format, we are *already* supporting it, we can create a firebird external database and we can connect to a firebird external database. The discussion is about where should be the options to do it. Now are both in *connect* to a database, also create a new database is under *connect*

> If we create external DB, I would expect it to happen at the first step,
> like Robert commented in #2 (dropdown with "Firebird embedded", "FB
> external", "Dbase"...) . The details could be entered at the second wizard
> step, similar to some connections to existing DB.

I agree, that's what I'm requesting.

> 
> Alternatively, taking the "Create New" button from the second step in case
> of FB into the wizard's first step next to the dropdown of "Connect to
> existing DB", requires the button to be disabled for all other types. Or to
> create most other types of DB as well.

No, I think it is not necessary modify anything, just hidden the button, like it is done, for example, in the connection to a PostgeSQL direct connection.
Although I think that if we are working on firebird, and may be in a future it could be the default database, we should modify the wizard to allow the different connections to firebird in one place like it is done with mysql
Comment 19 Heiko Tietze 2022-03-30 07:46:27 UTC
(In reply to jcsanz from comment #18)
> No, I think it is not necessary modify anything, just hidden the button...

Hide controls only when it never applies in the context. Rather disable, if it works for some but not all options. This makes the UI more quiet and gives the user a clue what might be possible.
Comment 20 jcsanz 2022-03-30 09:03:16 UTC
(In reply to Heiko Tietze from comment #19)
> (In reply to jcsanz from comment #18)
> > No, I think it is not necessary modify anything, just hidden the button...
> 
> Hide controls only when it never applies in the context. Rather disable, if
> it works for some but not all options. This makes the UI more quiet and
> gives the user a clue what might be possible.

It seems to me that ADO, Microsoft Access, dBase, PostgreSQL and may be spreadsheet and writer document are the same page but with the "Create new" hidden

Any way, I was thinking in a complete new dedicated to firebird dialog, as it has mysql, where we can establish some specific parameters (some as the draft I add to tdf#85148
Comment 21 jcsanz 2022-03-30 09:06:21 UTC
> Any way, I was thinking in a complete new dedicated to firebird dialog, as
> it has mysql, where we can establish some specific parameters (some as the
> draft I add to tdf#85148

I tried to implement it by myself but without success
Comment 22 Robert Großkopf 2022-03-30 09:16:23 UTC
This bug is described for a new option in first step of the wizard → "Create a new database".
The only external database, which could be created by the wizard at this moment, is a Firebird file. This could be choosen as "Connect to an existing database". This is the wrong place.

All other databases, which might be able to be created directly should be shown as
"Create a new database" → "External database:"
We don't need a discussion for all these databases. We could add them later to this listbox, where "Firebird external file" or something else is the only entry at this moment.

Don't discuss other points like "Separate entries for Firebird like it has been made in MySQL" here. This is a separate feature request.
Comment 23 jcsanz 2022-03-30 10:08:55 UTC
(In reply to Robert Großkopf from comment #22)
> This bug is described for a new option in first step of the wizard → "Create
> a new database".
> The only external database, which could be created by the wizard at this
> moment, is a Firebird file. This could be choosen as "Connect to an existing
> database". This is the wrong place.

That's right. But incomplete.

Currently, under "Connect to a existing database" if select "Firebird external", you have two options:
1. Create a new file, button "Create New" 
2. Connect to a existing database, button "Browse"

I think every body agrees that the option 1 should be under "Create new database".

With regard to the number 2, I expect it is maintained in the same place. But if this option is maintained in that place, the button "Create new" should disappear, may be hidden (that was my proposal) and in a future (out of this request) we need a better dialog to connect to a existing firebird external database (it can be file or server database but now is not clear)

Although option 1 and 2 are not strictly related, if you move 1 to other place, you should drop it from the old position.

I hope I have now explained myself better.
Comment 24 jcsanz 2022-12-12 12:04:57 UTC
I've send a wip patch https://gerrit.libreoffice.org/c/core/+/143902?forceReload=true which try to do this.

I've got to remove the "Create new" part from "Connect to an existing database" and I've got to create a new option "Firebird external" on "Create a new database", but I'm not able to configure the dialog to create a new database.
Although I think it is the same dialog than in "Conect to..." this dialog is not taking properly the options from the Driver.xcu.

Any help is welcome. Tanks