At 19:34 12/10/2008, you wrote:

>On Sunday 12 October 2008 12:16, Helen Borrie wrote:
>> 3. Now, you might argue that all it takes is to put the data file tree
>> beneath the Firebird root, e.g. in ../datafiles (similar to UDFAccess) and
>> set up ExternalFileAccess RESTRICT to the relative tree address. Then you
>> could just plonk all your external files there and define external tables
>> to point to this platform-independent location. Doesn't work. For
>> example, pathing the <filespec> argument as
>> 'datafiles\specialdir\special.data' results (on Windows) in a violation of
>> your RESTRICT list as Firebird attempts to open a file in
>> c:\windows\system32\datafiles\specialdir.
>
>Helen, you are not completely precise here. Yep, it does not work for a file
>with pathname. But it works just fine for a simple file name. For example,
>with line in firebird.conf:
>
>ExternalFileAccess = Restrict /opt/extern
>
>I specify default location of external files. And can use it. After:
>
>SQL> create table qq external file 'zz' (x int);
>SQL> insert into qq values(1);
>SQL> commit;
>
>I have file /opt/extern/zz. If I rename '/opt/extern' to be '/opt/external'
>and change appropriately line in firebird.conf, I can access it successfully.

Not on Windows, Alex. Sorry.

>Moreover, with line of firebird.conf:
>
>ExternalFileAccess = Restrict /var/log;/opt/external
>
>and external file 'zz' in /opt/external, it's still accessible. I.e. the file
>is searched for in all Restrict entries.

"Searched for" wasn't the issue with respect to ExternalFileAccess. It was talking about creating the table definition. If you have a Windows box available, please try it.

>Certainly I agree that it will be good to support relative paths too. Can you
>add a tracker item for it?
>
>> So - is it possible to do some or all of the following:
>>
>> 1. Add a new config parameter for ExternalFileRoot to take care of the
>> platform dependence problem. There can be no default, obviously, so
>> enforce ExternalFileAccess=NONE, regardless of current settings, if
>> ExternalFileRoot is empty or invalid.
>
>Currently any item of restrict list works as external file root. In case when
>file with desired name is missing in any of them, new one will be created in
>the first dir. That behavior was discussed in devel a few years ago.

Sigh. Alex. I will send you a gbak of a database made on Windows that will demonstrate how this does not work and you will see for yourself how, logically, it cannot work as currently implemented.

>> 2. Allow (encourage? enforce?) a UDFAccess-style relative path for the
>> <filespec> argument of CREATE TABLE...EXTERNAL FILE
>
>I do not understand big difference between this and p.1 - same theng said in
>other manner...

You don't understand it because you believe it works.

(1) designates a root directory for the file path to *any* external data files. So, for example, on Winserver1 you might have:

ExternalFileRoot = d:\datafiles

while on Winserver2 you might have:
ExternalFileRoot = c:\imports

and on linserver you might have
ExternalFileRoot = /var/imports

and you want to ship your database with a table defined as:

create table atable
external file 'deliveries\latest.data'
..........

Then on Winserver1 it would write (or read) data from d:\datafiles\deliveries\latest.data

while on Winserver2, c:\imports\deliveries\latest.data

and on linserver

/var/imports/deliveries/latest.data

without deploying a database that will be unreadable.

ExternalFileAccess RESTRICT works fine for *finding* the allowed locations if they exist but there's nothing implemented on Windows to make Firebird pick up any part of any tree root specified there and apply it as any kind $fileroot variable. I didn't test this database on Linux. If you're sure it works that way on Linux then this is likely a bug in Windows. Do you know whether it works on other *x platforms?

>> 3. Make Firebird interpret slash and backslash in the subdirectory parts
>> of the strings appropriately for the platform it's running on.
>
>If it does not work in any place of engine - this is a bug, not worth even
>discussing in architect. Just add an item to the tracker:)

It doesn't work in this context. I assumed it doesn't because the other things are not implemented either.

>> 4. And - of course - wake up and fix the code for handling delimited text
>> record formats and get REAL LOVE by enabling an external table to suck in
>> the contents of text or binary files (as XML processors can do) for
>> treatment as clobs and blobs.
>
>This is what is really worth discussing here. And it's really much to discuss
>before doing something.
>
>First problem. Currently external engines access is actively developed by the
>project. Looks like very soon we will be able access virtually any file
>format using ODBC. Would not extending of external tables be just a
>duplication of our efforts in that direction?

First I have heard of *anything* productive happening around ODBC. Is that what the recent interest by Redsoft in the ODBC driver is about? A cross-platform ODBC driver, perhaps?

Helen