Thread: what would tar file FDW look like?

<div dir="ltr"><div class="gmail_default" style="font-family:tahoma,sans-serif;color:#000000">I'm starting to work on a
tarFDW as a proxy for a much more specific FDW. (It's the 'faster to build two and toss the first away' approach - tar
letsme get the FDW stuff nailed down before attacking the more complex container.) It could also be useful in its own
right,or as the basis for a zip file FDW.</div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000"><br/></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">Ihave figured out that in one mode the FDW mapping that would take
thename of the tarball as an option and produce a relation that has all of the metadata for the contained files -
filename,size, owner, timestamp, etc. I can use the same approach I used for the /etc/passwd FDW for that.</div><div
class="gmail_default"style="font-family:tahoma,sans-serif;color:#000000"><br /></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">(BTWthe current version is at <a
href="https://github.com/beargiles/passwd-fdw">https://github.com/beargiles/passwd-fdw</a>.It's skimpy on automated
testsuntil I can figure out how to handle the user mapping but it works.)</div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000"><br/></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">Theproblem is the second mode where I pull a single file out of the
FDW.I've identified three approachs so far:</div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000"><br/></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">1.A FDW mapping specific to each file. It would take the name of
thetarfile and the embedded file. Cleanest in some ways but it would be a real pain if you're reading a tarball
dynamically.</div><divclass="gmail_default" style="font-family:tahoma,sans-serif;color:#000000"><br /></div><div
class="gmail_default"style="font-family:tahoma,sans-serif;color:#000000">2. A user-defined function that takes the name
ofthe tarball and file and returns a blob. This is the traditional approach but why bother with a FDW then? It also
bringsup access control issues since it requires disclosure of the tarball name to the user. A FDW could hide
that.</div><divclass="gmail_default" style="font-family:tahoma,sans-serif;color:#000000"><br /></div><div
class="gmail_default"style="font-family:tahoma,sans-serif;color:#000000">3. A user-defined function that takes a tar
FDWand the name of a file and returns a blob. I think this is the best approach but I don't know if I can specify a FDW
asa parameter or how to access it.</div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000"><br/></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">I'veskimmed the existing list of FDW but didn't find anything that
canserve as a model. The foreign DB are closest but, again, they aren't designed for dynamic use where you want to do
somethingwith every file in an archive / table in a foreign DB.</div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000"><br/></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">Isthere an obvious approach? Or is it simply a bad match for FDW
andshould be two standard UDF?  (One returns the metadata, the second returns the specific file.)</div><div
class="gmail_default"style="font-family:tahoma,sans-serif;color:#000000"><br /></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">Thanks,</div><divclass="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000"><br/></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">Bear</div></div>

On Mon, Aug 17, 2015 at 3:14 PM, Bear Giles <bgiles@coyotesong.com> wrote:
> I'm starting to work on a tar FDW as a proxy for a much more specific FDW.
> (It's the 'faster to build two and toss the first away' approach - tar lets
> me get the FDW stuff nailed down before attacking the more complex
> container.) It could also be useful in its own right, or as the basis for a
> zip file FDW.

Hm. tar may be a bad fit where zip may be much easier. Tar has no
index or table of contents. You have to scan the entire file to find
all the members. IIRC Zip does have a table of contents at the end of
the file.

The most efficient way to process a tar file is to describe exactly
what you want to happen with each member and then process it linearly
from start to end (or until you've found the members you're looking
for). Trying to return meta info and then go looking for individual
members will be quite slow and have a large startup cost.


-- 
greg

On 08/17/2015 10:14 AM, Bear Giles wrote:
> I'm starting to work on a tar FDW as a proxy for a much more specific 
> FDW. (It's the 'faster to build two and toss the first away' approach 
> - tar lets me get the FDW stuff nailed down before attacking the more 
> complex container.) It could also be useful in its own right, or as 
> the basis for a zip file FDW.
>
> I have figured out that in one mode the FDW mapping that would take 
> the name of the tarball as an option and produce a relation that has 
> all of the metadata for the contained files - filename, size, owner, 
> timestamp, etc. I can use the same approach I used for the /etc/passwd 
> FDW for that.
>
> (BTW the current version is at 
> https://github.com/beargiles/passwd-fdw. It's skimpy on automated 
> tests until I can figure out how to handle the user mapping but it works.)
>
> The problem is the second mode where I pull a single file out of the 
> FDW. I've identified three approachs so far:
>
> 1. A FDW mapping specific to each file. It would take the name of the 
> tarfile and the embedded file. Cleanest in some ways but it would be a 
> real pain if you're reading a tarball dynamically.
>
> 2. A user-defined function that takes the name of the tarball and file 
> and returns a blob. This is the traditional approach but why bother 
> with a FDW then? It also brings up access control issues since it 
> requires disclosure of the tarball name to the user. A FDW could hide 
> that.
>
> 3. A user-defined function that takes a tar FDW and the name of a file 
> and returns a blob. I think this is the best approach but I don't know 
> if I can specify a FDW as a parameter or how to access it.
>
> I've skimmed the existing list of FDW but didn't find anything that 
> can serve as a model. The foreign DB are closest but, again, they 
> aren't designed for dynamic use where you want to do something with 
> every file in an archive / table in a foreign DB.
>
> Is there an obvious approach? Or is it simply a bad match for FDW and 
> should be two standard UDF?  (One returns the metadata, the second 
> returns the specific file.)
>
>


I would probably do something like this:

In this mode, define a table that has <path, blob>. To get the blob for 
a single file, just do "select blob from fdwtable where path = 
'/path/to/foo'". Make sure you process the qual in the FDW.

e.g.
   create foreign table tarblobs (path text, blob bytea)   server tarfiles options (filename  '/path/to/tarball', mode
'contents');


cheers

andrew

<div dir="ltr"><div class="gmail_default" style="font-family:tahoma,sans-serif;color:#000000">I've written readers for
bothfrom scratch. Tar isn't that bad since it's blocked - you read the header, skip forward N blocks, continue. The
hardestpart is setting up the decompression libraries if you want to support tar.gz or tar.bz2 files.</div><div
class="gmail_default"style="font-family:tahoma,sans-serif;color:#000000"><br /></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">Zipfiles are more complex. You have (iirc) 5 control blocks - start
ofarchive, start of file, end of file, start of index, end of archive, and the information in the control block is
prettylimited. That's not a huge burden since there's support for extensions for things like the unix file metadata.
Onecomplication is that you need to support compression from the start.</div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000"><br/></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">Zipfiles support two types of encryption. There's a really weak
versionthat almost nobody supports and a much stronger modern version that's subject to license restrictions.  (Some
peopleuse the weak version on embedded systems because of legal requirements to /do something/, no matter how
lame.)</div><divclass="gmail_default" style="font-family:tahoma,sans-serif;color:#000000"><br /></div><div
class="gmail_default"style="font-family:tahoma,sans-serif;color:#000000">There are third-party libraries, of course,
butthat introduces dependencies. Both formats are simple enough to write from scratch.</div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000"><br/></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">Iguess my bigger question is if there's an interest in either or
bothfor "real" use. I'm doing this as an exercise but am willing to contrib the code if there's a general interest in
it.</div><divclass="gmail_default" style="font-family:tahoma,sans-serif;color:#000000"><br /></div><div
class="gmail_default"style="font-family:tahoma,sans-serif;color:#000000">(BTW the more complex object I'm working on is
the.p12 keystore for digital certificates and private keys. We have everything we need in the openssl library so
there'sno additional third-party dependencies. I have a minimal FDW for the digital certificate itself and am now
workingon a way to access keys stored in a standard format on the filesystem instead of in the database itself. A
naturalfit is a specialized archive FDW. Unlike tar and zip it will have two payloads, the digital certificate and the
(optionallyencrypted) private key. It has searchable metadata, e.g., finding all records with a specific
subject.)</div><divclass="gmail_default" style="font-family:tahoma,sans-serif;color:#000000"><br /></div><div
class="gmail_default"style="font-family:tahoma,sans-serif;color:#000000">Bear</div></div><div class="gmail_extra"><br
/><divclass="gmail_quote">On Mon, Aug 17, 2015 at 8:29 AM, Greg Stark <span dir="ltr"><<a
href="mailto:stark@mit.edu"target="_blank">stark@mit.edu</a>></span> wrote:<br /><blockquote class="gmail_quote"
style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">On Mon, Aug 17, 2015 at 3:14 PM,
BearGiles <<a href="mailto:bgiles@coyotesong.com">bgiles@coyotesong.com</a>> wrote:<br /> > I'm starting to
workon a tar FDW as a proxy for a much more specific FDW.<br /> > (It's the 'faster to build two and toss the first
away'approach - tar lets<br /> > me get the FDW stuff nailed down before attacking the more complex<br /> >
container.)It could also be useful in its own right, or as the basis for a<br /> > zip file FDW.<br /><br
/></span>Hm.tar may be a bad fit where zip may be much easier. Tar has no<br /> index or table of contents. You have to
scanthe entire file to find<br /> all the members. IIRC Zip does have a table of contents at the end of<br /> the
file.<br/><br /> The most efficient way to process a tar file is to describe exactly<br /> what you want to happen with
eachmember and then process it linearly<br /> from start to end (or until you've found the members you're looking<br />
for).Trying to return meta info and then go looking for individual<br /> members will be quite slow and have a large
startupcost.<br /><span class="HOEnZb"><font color="#888888"><br /><br /> --<br /> greg<br
/></font></span></blockquote></div><br/></div>