Bug 85148 - FILEOPEN: Firebird connect to server via new driver (feature request)
Summary: FILEOPEN: Firebird connect to server via new driver (feature request)
Status: ASSIGNED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.3.2.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium enhancement
Assignee: jcsanz
URL:
Whiteboard: target:7.3.0
Keywords: easyHack, skillCpp, skillSql
Depends on:
Blocks: Database-Connectivity
  Show dependency treegraph
 
Reported: 2014-10-17 15:00 UTC by reinierolislagers
Modified: 2021-10-11 07:22 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Attachments
Connection page draft (88.35 KB, image/png)
2021-10-07 20:20 UTC, jcsanz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description reinierolislagers 2014-10-17 15:00:56 UTC
As bug 85146 indicates, it seems functionality for connecting to a Firebird server via TCP/IP rather than embedded in .odb or a separate Firebird .fdb file is already present.

It would be nice to be able to expose this in the GUI so one can connect to a Firebird server out of the box without needing to install other drivers, mess with ODBC etc.
Comment 1 Alex Thurgood 2015-01-03 17:38:42 UTC Comment hidden (no-value)
Comment 2 Tor Lillqvist 2018-01-03 09:13:32 UTC
I don't think our HSQLDB code has any similar feature, so this can't be a blocker for bug #51780.
Comment 3 Lionel Elie Mamane 2018-01-03 11:01:56 UTC
Basically we just need to ask for user and password in the UI.

In connectivity/registry/firebird/org/openoffice/Office/DataAccess/Drivers.xcu in the
<node oor:name="sdbc:firebird:*" oor:op="replace">
 ...
 <node oor:name="MetaData">

add a child like that:

        <node oor:name="Authentication" oor:op="replace">
          <prop oor:name="Value" oor:type="xs:string">
            <value>UserPassword</value>
          </prop>
        </node>

While you are at it, change the "DriverTypeDisplayName" from "Firebird file" to "Firebird external".

Then in connectivity/source/drivers/firebird/Connection.cxx in method Connection::construct, in
       if (url == "sdbc:embedded:firebird")
       {
           (...)
       }
       // External file AND/OR remote connection
       else if (url.startsWith("sdbc:firebird:"))
       {
              // HERE
       }

Get the username and password from the "info" parameter. See e.g. connectivity/source/drivers/odbc/OConnection.cxx method OConnection::Construct for an example how to do that.

Then pass the username and password to the firebird API. A bit lower you have:

            if (m_bIsEmbedded || m_bIsFile)
            {
                strcpy(userName,"sysdba");
                strcpy(userPassword,"masterkey");
            }
            else
            {
                // TODO: parse password from connection string as needed?
            }

At the place of this TODO, strncpy the username and password you have extracted from info into the userName and userPassword buffers.

For bonus points, you can implement a specific UI page just for Firebird in the wizard, like we have for MySQL. bug 43369 contains instructions on how to do that (for PostgreSQL, but can be adapted for Firebird).
Comment 4 jcsanz 2021-09-12 14:52:33 UTC
The part of the code in connectivity/source/drivers/firebird/Connection.cxx is already done. https://git.libreoffice.org/core/commit/34219c3cb737371afb4a29604e95f0e87966f02a.
The UI part remains to be done.
Comment 5 Julien Nabet 2021-10-06 18:51:56 UTC
Robert: any thoughts about patch here https://gerrit.libreoffice.org/c/core/+/122073 ?
Comment 6 Robert Großkopf 2021-10-07 05:47:04 UTC
(In reply to Julien Nabet from comment #5)
> Robert: any thoughts about patch here
> https://gerrit.libreoffice.org/c/core/+/122073 ?

Have read the comments there. I would prefer to add add "External" in opposite to "Embedded". "External" will be a connection to a server or connection to a file.

… and coding will be the next project for me after I retired in some months. We laughed a little bit about the confusion between "Gerrit" and "Robert".
Comment 7 Julien Nabet 2021-10-07 11:02:36 UTC
(In reply to Robert Großkopf from comment #6)
> (In reply to Julien Nabet from comment #5)
> > Robert: any thoughts about patch here
> > https://gerrit.libreoffice.org/c/core/+/122073 ?
> 
> Have read the comments there. I would prefer to add add "External" in
> opposite to "Embedded". "External" will be a connection to a server or
> connection to a file.
Ok, I put it in the gerrit.

> … and coding will be the next project for me after I retired in some months.
> We laughed a little bit about the confusion between "Gerrit" and "Robert".
Great!
Comment 8 Commit Notification 2021-10-07 17:05:59 UTC
jucasaca committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/418e56997c9e63a5bc669c1b79157b2a4dbf6a2c

tdf#85148 New option on database wizard to connect to Firebird Server

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 jcsanz 2021-10-07 20:20:15 UTC
Created attachment 175582 [details]
Connection page draft
Comment 10 jcsanz 2021-10-07 20:23:10 UTC
I'm working in a page to connect to a Firebird file or a Firebird Server.
It is my draft going in a correct way or shoud I do a page with options similar to MySQL?
Comment 11 Robert Großkopf 2021-10-08 05:28:00 UTC
(In reply to jcsanz from comment #10)
> I'm working in a page to connect to a Firebird file or a Firebird Server.
> It is my draft going in a correct way or shoud I do a page with options
> similar to MySQL?

I would prefer it similar to MySQL.
For the second step should be added "File".

If you are going the other way most of the content must be disabled in "Create new Database" is chosen. You can't create a new server database. So it must switch to "File Database" automatically.
Comment 12 jcsanz 2021-10-08 10:13:49 UTC
(In reply to Robert Großkopf from comment #11)

> If you are going the other way most of the content must be disabled in
> "Create new Database" is chosen. You can't create a new server database. So
> it must switch to "File Database" automatically.

With the current code it is possible to create a new database also in the server. Well, it needs a (little) modification, but I think is not very difficult. So, although I will do a connection similar to MySQL, I would allow create a database, also in the server mode.
But, yes, rethinking it, it is better to show the JDBC and ODBC possibilities together with the direct and file connection in a previous step, like MySQL.
Do you think I need more (or less) fields than the showed in the draft (server, port, charsset and role)?
Comment 13 Robert Großkopf 2021-10-08 15:10:47 UTC
(In reply to jcsanz from comment #12)
> 
> With the current code it is possible to create a new database also in the
> server. 

I'm not an expert for Firebird. I have only tested it for the German Base Handbuch. But: If it is possible to create a new database for normal user it couldn't be a server for more than private use. Here (on Linux) it is impossible to open the folder for databases and to create a new database there for normal users …

> Do you think I need more (or less) fields than the showed in the draft
> (server, port, charsset and role)?

Isn't, what you called role, the username? It is called "Role" in Firebird, but we should also show it (in tip-help?) that the username is expected.
Charset should be set to "charSet=UTF-8". Could be also shown the same way as the default for the port.
Comment 14 jcsanz 2021-10-11 07:22:23 UTC
(In reply to Robert Großkopf from comment #13)

> I'm not an expert for Firebird. I have only tested it for the German Base
> Handbuch. But: If it is possible to create a new database for normal user it
> couldn't be a server for more than private use. Here (on Linux) it is
> impossible to open the folder for databases and to create a new database
> there for normal users …

Every user that has been granted to create databases can create a database, also in the server, and the creator will be the owner of the database, with all rights over it. 
I'm mainly a Windows user, so I don´t know too much about the permissions to write files on Linux, but theoretically it is possible to create it too in a folder where everybody can write a file. We'll leave the new databases aside for now, but we'll deal with it later, if I'm able to connect ;-).

> Isn't, what you called role, the username? It is called "Role" in Firebird,
> but we should also show it (in tip-help?) that the username is expected.
> Charset should be set to "charSet=UTF-8". Could be also shown the same way
> as the default for the port.

The role refers to a security role. This is not used in embedded nor in file databases, because it is used always the admin role, but in a server a user can connect to every database with different roles (grants), and for example, connect as an admin to create a table and after that connect as a normal user to manipulate it. It seems to me that the security in Firebird is a "little" different from other databases.