Bug 117253 - FIREBIRD: Migration: Migration routine does not import HSQL TEXT TABLES.
Summary: FIREBIRD: Migration: Migration routine does not import HSQL TEXT TABLES.
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.0.alpha0+
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2018-04-26 14:30 UTC by Drew Jensen
Modified: 2018-06-05 21:29 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Extract the *.zip-file. Open the database. HSQLDB could read table, Migration doesen't work (3.42 KB, application/zip)
2018-04-27 06:00 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Drew Jensen 2018-04-26 14:30:06 UTC
Attempting to migrate an ODB file with a TEXT table (a table definition linked to an external text file) are ignored.

The migration (assuming no other problems) runs to completion without error but the text table definitions are lost.

(example to follow, but will be an hour or so)
Comment 1 Drew Jensen 2018-04-26 14:47:30 UTC
Sorry, I hit save to quickly.

Firebird does have support for external files also but the implementation is somewhat different from HSQLdb.

HSQLdb embedded in ODB files uses a restricted naming scheme for the external files and requires that the files be stored in either the same directory as the ODB file or a directory below that.

Firebird 3.0 also offers this restricted mode for their 'external file' support. Which would, it seems, allow the same restrictions as now.

The fb engine must be configured for this support using the configuration item: 
ExternalFileAccess = (None, Full, Restricted)

For restricted mode a list of directories are supplied and relative naming is allowed. ExternalFileAccess = Restrict /some/directory

Two pieces of functionality available in HSQL are not available in fb.

1: The ability to create a text table from a SELECT INTO statement, in HSQL if you supply a file name and it does not exist the file is created with a structure to match the result set and the data copied into it. Under fb you can only do SELECT INTO a text table that has already been defined and that already exists on disk.

2: In HSQL you can change which file file a TEXT TABLE definition points to with a SET command. In FB the table definition must be dropped and recreated to do that.

I don't know what the current configuration setting is. Executing a CREATE TABLE command in the SQL window will run to completion and the table defintion is displayed, but no column information is saved, it is a table with zero columns. (I know, needs a different issue)
Comment 2 Drew Jensen 2018-04-26 14:51:14 UTC
One more difference.

Under HSQL text tables may contain blob fields.
Under fb external files do not support blob fields.
Comment 3 Robert Großkopf 2018-04-27 06:00:57 UTC
Created attachment 141666 [details]
Extract the *.zip-file. Open the database. HSQLDB could read table, Migration doesen't work

I could confirm the buggy behavior.
Extract the attached *.zip-file.
Make a copy of the database in the same folder - the original database is unusable afterwords ...
Open the database.
Press "Yes" to migrate.
This error appears:

firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE Adressen failed
*SQL error code = -607
*Invalid command
*Table Adressen does not exist
caused by
'ALTER TABLE "Adressen" ALTER COLUMN "ID" RESTART WITH 4'

Table "Adressen" hasn't been migrated.
Comment 4 Robert Großkopf 2018-04-27 06:14:32 UTC
Firebird doesn't support UPDATE and DELETE for text-tables. So it might be a better idea for some users to import text-tables to internal tables of Firebird. The complete migration of the attached example with the same functionality seems to be impossible to me when reading the documentation for Firebird 2.5.
Comment 5 Robert Großkopf 2018-04-27 07:02:56 UTC
Seems external tables are disabled for the internal Firebird database. When running
CREATE TABLE TEST_TXT
EXTERNAL FILE 'test.txt' (
message CHAR(100) );

There isn't created test.txt.

When I refresh the tables I could see TEST_TXT - but without any row and any column.

Trying to insert values through direct SQL shows:1: 
firebird_sdbc error:
*Use of external file at location /home/robby_daten/Lotest/test.txt is not allowed by server configuration

The one point: External files are not allowed.
The second: The database is at /home/robby/Downloads/... - Firebird searches for the file at the position I started my LO-testversion.

Now I try to delete the table in my database-file, but it is impossible through the GUI, only works in direct SQL (DROP TABLE TEST_TXT;)