Bug 44069

Deploying a file-based Base database that doesn't use the internal HSQLDB connection can be difficult.  The connection string for external databases normally requires a full path to the database, DSN file, or similar.  Being that different users can deploy the databases to different directories, and even to different platforms, this requires manual configuration by the user of sometimes esoteric database settings.

For example, a Base database I recently deployed connects to a SQLite database.  I used a DSN-less ODBC connection so the end user didn't have to create an ODBC DSN to use the database.  However I still had to use Base's ODBC "Optional Settings" to specify the database file by setting it to:
  "Database=C:\Users\Kurt\Documents\Creative\Pathfinder Spell Database.db".

The end user doesn't have to create a DSN, but the user does have to edit the Base database connection to edit the ODBC "Optional Settings" to give the location on the user's system of the back-end SQLite database.

I propose inclusion of a system similar to environment variables that will allow inclusion of "macros" in connection strings that expand to different things.  In the above example, the ODBC "Optional Settings" might read:
  "Database=%DOCPATH%\Pathfinder Spell Database.db"

In the above example, "%DOCPATH%" would be evaluated by LibreOffice and expand to the directory that the document resides in.  This will allow a database to be deployed where, if the database file is kept in the same directory as the Base .odb document, the user doesn't have to do anything extra to connect to the database file.