This article as published back in 2009. This blog entry brings all the pieces together again.
The libferris virtual filesystem allows you to mount things that
are not normally thought of as filesystems. Examples include XML
files, Berkeley db4 ISAM files, relational databases, system log
files, as well as applications like Firefox, Evolution, XWindow,
emacs and Amarok.
Version 1.3.0 of libferris has enhanced support for SQLite
databases. This includes both mounting an SQLite database as a
filesystem and exposing any libferris virtual filesystem to
SQLite as a virtual table. You can have as many libferris backed
virtual tables as you like in SQLite, so you can join
information from multiple sources (XML, syslog, db4 files) using
SQL queries.
In this article series I will explain how to mount SQLite as a
filesystem so you can manipulate it with the familiar command
line tools (ls, cat, redirection), how to go the other way and
expose libferris filesystems like mounted XML to SQLite for
manipulation. I'll finish up with a discussion of how libferris
lets you mount semi structured plain text files like log files
as a filesystem and how you can then expose those to SQLite for
analysis.
libferris tries to perform what are traditionally seen as
filesystem mounting operations for you automatically. For
example, if you list an XML file using ferrisls without using
the
-d option to list the entry itself rather than
its contents, then libferris will try to mount that XML file and
show it to you as a filesystem. This allows clients to access
and store information into XML and db4 files without needing to
know any of the details. A URL could be for a normal directory
on an ext3 filesystem or for part of an XML file, it doesn't
matter.
Mounting SQLite databases is now done automatically
too. Consider the simple SQLite database shown below:
$ touch /tmp/test.sqlite
$ sqlite3 /tmp/test.sqlite
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table foo ( id int primary key, msg varchar );
sqlite> insert into foo values (4,'hello there');
sqlite> insert into foo values (5,'foo bar');
Without doing anything special, you can get at the data in this
SQLite database through any libferris client. If you prefer you
can also use FerrisFUSE to expose it through the Linux kernel to
any program. For those unfamiliar,
FUSE allows a filesystem that
appears through the Linux kernel to be served by an application
running as a normal application process. This means FerrisFUSE
runs like a daemon and can use libferris to store information
while making it available to the Linux kernel like any other
traditional (ext3) filesystem. All of a sudden you can run vi
or emacs to edit a tuple in an SQLite database. The libferris
distribution contains clones of the traditional ls, cat, cp,
etc. tools which use libferris to perform their work. This way
you don't have to setup any FUSE mounts or the like if you just
want to ls, cat or redirect information into a libferris
filesystem.
When you mount a table or SQL query result from a relational
database with libferris, each column from a tuple is exposed
using the standard filesystem Extended Attribute (EA)
interface. The EA interface allows key=value pairs to be
associated with a file. You can think of EA simply as metadata
for files and directories, the familiar lstat(2) information of
size, mtime, owner and group of a file are exposed as EA by
libferris. In fact, you can get and set the byte contents of a
file through the
content EA of a file. Making
everything available through the EA interface allows you to
filter and sort a directory based on lstat metadata as well as
user supplied metadata -- it's all just EA.
Shown below, I first list the table from the SQLite database and
then view a tuple with the fcat command. When you read a tuple
as a file, libferris exposes it as an XML document. In places
that do not have a direct mapping, libferris normally tries to
do the most sane thing it can. For a tuple, you have to expose
all the fields in some delimited format because libferris can't
know what fields are the most important. So using XML makes the
most sense because it offers explicit key=value serialization
and is easily digestible by other applications. You can also
use the
-a option to fcat to select a single single
column (EA) which avoids the XML.
$ ferrisls -lh /tmp/test.sqlite/foo
40 4
36 5
$ fcat /tmp/test.sqlite/foo/5
<context id="5" msg="foo bar" />
$ fcat -a msg /tmp/test.sqlite/foo/4
hello there
As you can see above, the long listing format from ferrisls only
shows the size and name of each file. All of the information
shown by ls is exposed as EA by libferris. But there is no
modification time, or file ownership privileges stored for a
tuple in the SQLite database, so no value is available. This
makes the ferrisls long listing seem fairly spartan.
You can also ask a libferris directory what it thinks is the
most interesting EA (the recommended-ea) for the user. The
ferrisls -0 option uses this feature to let you dig into strange
filesystems. Shown below, you can see the
msg and
id column from the database are shown when using -0
to see the recommended-ea. Because the columns that are shown
by recommended-ea can be arbitrary EA, you might like to use the
--show-headings option to have ferrisls tell you the name of
each EA.
$ ferrisls -0 /tmp/test.sqlite/foo
4 hello there 4 id
5 foo bar 5 id
$ ferrisls -0 --show-headings /tmp/test.sqlite/foo
id msg name primary-key
4 hello there 4 id
5 foo bar 5 id
ferrisls also lets you see its output in XML or RDF/XML using
the --xml or --rdf options respectively. Apart from being able
to easily parse the output, the XML output lets you see the
names of the EA for each value.
$ ferrisls --xml /tmp/test.sqlite/foo
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<ferrisls>
<ferrisls id="" msg="" name="foo" primary-key="id" url="file:///tmp/test.sqlite/foo">
<context id="4" msg="hello there" name="4" primary-key="id"/>
<context id="5" msg="foo bar" name="5" primary-key="id"/>
</ferrisls>
</ferrisls>
While such read access is a huge step forward for unlocking the
data in an SQLite file, you might like to update data as
well. Because the redirection commands in bash only know about
kernel filesystems, there is the ferris-redirect command which
performs a similar role to the shell
>,
>> and
>| family of
primitives. The ferris-redirect command reads from standard
input and writes that data to a nominated URL. The
-T option to ferris-redirect truncates the file
before writing to it, and the
--ea command is used
to select an EA to write to instead of writing to the contents
of the file.
$ echo -n "welcome to libferris" \
| ferris-redirect -T --ea msg /tmp/test.sqlite/foo/5
$ ferrisls --xml /tmp/test.sqlite/foo
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<ferrisls>
<ferrisls id="" msg="" name="foo" primary-key="id" url="file:///tmp/test.sqlite/foo">
<context id="4" msg="hello there" name="4" primary-key="id"/>
<context id="5" msg="welcome to libferris" name="5" primary-key="id"/>
</ferrisls>
</ferrisls>
I added support for mounting PostgreSQL database to libferris
years ago. There are still some features unique to the
PostgreSQL support in libferris such as calling database
functions through a virtual filesystem. You can also mount other
databases that QtSQL can access using libferris.
Going the other way
Lets now turn our attention to doing the opposite: exposing
libferris as a relation to SQLite. You might have some data in
an XML file that you need to join with data stored in Berkeley
db4 or an existing SQLite database. Your first thought might be
to whip up a Perl script to bring the data together. With
libferris you don't have to write any of this glue code.
As SQLite includes support for virtual tables, you can expose
any libferris virtual filesystem as a table to SQLite. Because
XML is just another virtual filesystem to libferris you can
easily expose it to SQLite and join the data with a normal SQL
query.
To use libferris from SQLite, you'll need an sqlite client
binary that can load shared libraries. Allowing binary libraries
to be loaded into your SQLite session is a bit of a security
issue, the code could potentially do anything. As such, the
packaged client for Fedora does not allow loading shared
libraries. It is very easy to download the sqlite source and
recompile it with shared library support though, and you only
have to install the single
sqlite3 binary somewhere
in order to get at libferris virtual tables.
Firstly you'll need to load the libferris support using the
.load directive at an sqlite prompt. Data is
exposed into SQLite as a virtual table using the
create
virtual table statement. The format for this statement
varies depending on the code implementing the virtual table. For
libferris, the parameters are: The URL to expose, a string
containing key=value pairs to effect this exposure, and the EA
you want exposed, expressed in the normal
create
table format. Note that you can only specify the name and
datatype for each EA (column) you want, you can't put
constraints on them like foreign or primary keys. To avoid
confusion, I'll call the URL supplied as the first parameter to
create virtual table the "base URL".
Issuing the below two statements at an sqlite prompt will bring
libferris support into the current session and create a virtual
table which shows the /tmp/test directory with various lstat(2)
information as well as the MD5 checksum and file contents as
columns in the new SQLite table. Note that there is an implicit
first column in the created virtual table which is always the
file URL (primary key) so you don't have to specify that
manually.
$ sqlite3
.load libferrissqlitevtable.so
create virtual table fs using libferris(
'file:///tmp/test',
'',
name text,
size int,
md5 text,
path text,
mtime int,
atime int,
ctime int,
is-dir int,
mtime-display text,
content text );
There is a short list of
common EA
descriptions that are available in libferris. If you are
unsure what an EA looks like, use the
--show-ea
option to ferrisls to select which EA you want to see for a
URL.
--show-ea takes a comma separated list of
which EA you want to see. The special
ea-names EA
will list the names of all EA that are available for a URL as
a comma separated string. The second command below generates a
newline separated version in a temporary file.
$ ferrisls --show-ea=name,foo URL
$ fls --show-ea=ea-names URL | tr ',' '\n' >|/tmp/o
$ vi /tmp/o
Turning our attention back to the
create virtual table SQLite function again.
There are currently two parameters which can be specified in the second parameter as comma
separated key=value pairs. If
you specify
recursive=true then the table exposes
the entire filesystem recursively from the base URL, otherwise
only the directory of the base URL is exposed. This lets you
choose if you want the directory or entire filesystem tree from
the base URL to be exposed. If you specify
add-rowid-to-base-filesystem=true then stable 64bit
row-ids are added as needed to files in the exposed table. You
need stable row-ids to allow the SQL
update
.. where and
insert into statements to work
as expected.
There are a few things to keep in mind when exposing a
filesystem as a relational table. For example, EA that are shown
in ferrisls and native libferris clients with a '-' in them are
translated to use '_' instead because SQLite doesn't like the
dash. Likewise, ':' is translated into '_colon_'. So in the
above create virtual table, the
is-dir and
mtime-display EA will be available in the SQL
columns
is_dir and
mtime_display
respectively.
To get at the above virtual fs table, you can use the normal SQL
select command:
select is_dir,name,url from fs
order by url desc;
...
0|df2|file:///tmp/test/df2
0|df1|file:///tmp/test/df1
...
Because some calculated EA like the MD5 checksum are exposed as
columns you can grab those from a query too. The libferris
virtual table implementation includes code to allow you to pick
files in a where clause using their path or URL and libferris
will optimize the lookup in a manner similar to (but not quite
as efficiently) as if you had created a relational database
index on those columns. Note that if you use the URL in the
where clause, you need to specify the
file:// part
too, and the URL must exactly match what you are looking for. If
you are not getting the result you expect when searching for
yourURL, use
ferrisls --show-ea=url yourURL and
make sure the result exactly equals yourURL.
select md5,size,mtime,mtime_display,name,url from fs
where path = '/tmp/test/df1';
Exposing a normal filesystem directory to SQLite is not very exciting,
but keep in mind that anything you can see through libferris you can
see through an SQLite virtual table backed by libferris. So if you
have an XML file:
$ cat customers.xml
<customers>
<customer id="3" givenname="Foo" familyname="Bar"/>
<customer id="15" givenname="Bobby" familyname="McGee"/>
<customer id="131" givenname="Ziggy" familyname="Stardust" />
</customers>
You can expose it to SQLite and query based on any attributes in the XML file
as shown below.
$ cat test-xml.sql
sqlite> .load libferrissqlitevtable.so
sqlite> create virtual table fs using libferris(
'customers.xml/customers',
'',
name text,
size int,
id int,
givenname text,
familyname text
content text
);
sqlite> select * from fs
order by givenname desc;
file:///ferris/exposures/sqlitevtab/customers.xml/customers/131|131|0|131|Ziggy|Stardust
file:///ferris/exposures/sqlitevtab/customers.xml/customers/3|3|0|3|Foo|Bar
file:///ferris/exposures/sqlitevtab/customers.xml/customers/15|15|0|15|Bobby|McGee
You can also create a filesystem index with libferris and expose
desktop search results as a virtual sqlite table. For example,
the following searches an image collection for a particular
bridge using the EA Query (eaq://) libferris virtual filesystem.
For more information on creating and using the desktop search
functionality in libferris see the
Linux Journal
article.
$ cat query.sql
.load libferrissqlitevtable.so
create virtual table fs using libferris(
'eaq://(url=~Akashi-Kaikyo-Bridge)',
'',
name text,
size int,
md5 text,
path text,
mtime int, atime int, ctime int,
is-dir int,
mtime-display text,
width int, height int, content text );
select is_dir,width,height,url from fs
order by url desc;
...
0|1840|1232|file:///digital_pictures/2004-japan/Akashi-Kaikyo-Bridge-main-section2.png
0|1840|1232|file:///digital_pictures/2004-japan/Akashi-Kaikyo-Bridge-main-section.png
...
Updating XML through SQL commands
Imagine that you have the customers.xml file shown below. You
have exposed it to SQLite and used it to join with information
from tables in the database to resolve queries. All is well, but
you would like to add a new customer to the XML file. While you
might consider opening up a text or XML editor on the XML file
itself, you would really like to just keep using SQL.
$ cat customers.xml
<customers>
<customer id="3" givenname="Foo" familyname="Bar"/>
<customer id="15" givenname="Bobby" familyname="McGee"/>
<customer id="131" givenname="Ziggy" familyname="Stardust" />
</customers>
Currently you have to specify everything
including the URL of the new file you wish to create in the SQL
INSERT statement as shown below. Note that the ID does not need
to be part of the URL, you only need to tell libferris you want
to create a new
customer element. A planned
addition here is to allow libferris to understand the
autoincrement keyword during the creation of the virtual SQLite
table. This would let you be able to supply a NULL for the ID
column and have one automatically generated for you, even though
this is backed by an XML file.
.load libferrissqlitevtable.so
create virtual table fs using libferris(
'file:///tmp/customers.xml/customers',
'add-rowid-to-base-filesystem=true',
id int,
givenname text,
familyname text
);
insert into fs values (
'file:///tmp/customers.xml/customers/customer',
300,
'this is the new given name',
'new sirname here' );
At this stage your customers.xml file will include a new element
for the entry you INSERTed above:
$ cat customers.xml
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<customers ferris-sqlite-next-rowid="1">
<customer familyname="Bar" givenname="Foo" id="3"/>
...
<customer familyname="new sirname here" ferris-sqlite-rowid="1"
givenname="this is the new given name" id="300"/>
</customers>
The little differences that surfaced for the SQL INSERT statement
completely disappear when using SQL UPDATE as shown below:
update fs set familyname = 'Anderson' where id = 300;
As you can see above, setting add-rowid-to-base-filesystem=true
in the create virtual table statement causes libferris to save
ferris-sqlite-rowid data for files in the virtual filesystem. If
these attributes can be saved into the file itself, either
directly as in XML, or to filesystem Extended Attributes then
they are stored there. If neither of these are possible then
libferris stores the ferris-sqlite-rowid data in a personal RDF
store.
Plain text log files and SQLite
Those familiar with awk and Perl don't really think too much
about splitting a log file into its various columns. But a log
file stored as a plain text file on a filesystem is hard to
directly query from a relational database. One might wish to
know the number of connections from each IP address and the
total number of bytes send to each of those IP addresses. For
such a query the SQL is fairly quick to conjure up but the data
is not in an agreeable format for SQL.
Version 1.3.0 of libferris also includes support for mounting
text files with a regular structure as a virtual filesystem. In its
most basic form this extends to splitting up each line of a file
using a nominated separator character and making a virtual file
for each line with the individual fields exposed as EA. For
example, using the comma as the delimiter you could mount a
comma separated file as a libferris filesystem. Once its a
libferris filesystem you can then turn around and use SQLite to
query that filesystem.
In an awk script, the fields that are generated by splitting up
a line (record) of input are available through the $1, $2,
etc. variables. By default the fields are exposed using similar
names in libferris but you can also specify a comma separated
list of identifiers for the fields. These identifiers give the
name of an extended attribute (EA) which will have the field
value. Perhaps an example would best clarify things.
The test2.csv file shown below contains three fields separated
by commas. The first ferris-redirect command sets the
ferris-recordfile-field-names EA for the file which instructs
libferris the names you would like to give to the columns. The
second ferris-redirect tells libferris what separates the fields
in the file. This is like the FS variable from awk and can be a
regular expression.
Finally the ferris-type is set to recordfile to tell libferris
that it should try to mount the file by parsing it if the user
tries to list it like a directory. Because recordfiles are just
plain text files, libferris has no quick way of knowing if it
can or should try to parse the file without you explicitly
setting the ferris-type to recordfile.
Notice in the ferrisls command that the fields have the names
which you specified using the ferris-recordfile-field-names
attribute.
$ cat test2.csv
cola,colb,colc
seca,secb,secc
third1,third2,third3,third4
$ echo -n 'colA,secondColumn,lastColumn' \
| ferris-redirect -T --ea=ferris-recordfile-field-names test2.csv
$ echo -n ',' \
| ferris-redirect -T --ea=ferris-recordfile-fs test2.csv
$ echo -n recordfile \
| ferris-redirect -T --ea=ferris-type test2.csv
$ fls --xml test2.csv
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<ferrisls>
<ferrisls name="/tmp/testrecordfile/test2.csv" url="file:///tmp/testrecordfile/test2.csv">
<context colA="cola" lastColumn="colc" name="0" secondColumn="colb"/>
<context colA="seca" lastColumn="secc" name="1" secondColumn="secb"/>
<context colA="third1" lastColumn="third3" name="2" secondColumn="third2"/>
</ferrisls>
</ferrisls>
Since its annoying and tedious to set these field separators for
each file, you can set the ferris-recordfile-metadata-url EA to
an absolute or relative name where the metadata about field
names and separators is kept. In the case shown below, ferris
will look in ~/.ferris/recordfile-types.db/apache_access_log for
the info about how to split up the file.
$ echo -n apache_access_log \
| ferris-redirect -T -x --ea=ferris-recordfile-metadata-url /var/log/httpd/access_log
$ echo -n recordfile \
| ferris-redirect -T --ea=ferris-type /var/log/httpd/access_log
The ferris-recordfile-metadata-url is simply a level of
indirection. Instead of libferris looking at the
ferris-recordfile-fs EA on the /var/log/httpd/access_log file it
will instead look for the ferris-recordfile-fs EA on
~/.ferris/recordfile-types.db/apache_access_log. This way you
can setup the information for field names and the regular
expressions used to split up various file types in one place and
simply refer files of that format to the correct
recordfile-types.db URL.
Trying to parse apache access logs using a fixed regular
expression for the field separator is not as simple as with
comma separated files. This is because the separator changes as
you move across a line in the access log. To enable parsing of
less regular files libferris supports the
ferris-recordfile-fsplit EA which defines a single regular
expression which includes regular expression (captures) to
explicitly pick out the fields from the input. The fsplit
regular expression (regex) is normally much longer than a field
separator (FS) regex but you can easily change what
the regex searches for after each matched field.
Shown below is a partial implementation of a fsplit capturing
regex for parsing apache access log files. The
recordfile-types.db comes with libferris as part of the /etc/skel
files for
~/.ferris. Note that you can easily
explore and update the data stored in a Berkeley db file because
libferris can mount those too.
$ fls --xml ~/.ferris/recordfile-types.db
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<ferrisls>
<ferrisls name="/ferris/dot-ferris/recordfile-types.db" url="file:///ferris/dot-ferris/recordfile-types.db">
<context ferris-recordfile-field-names="ip,f2,f3,date,req,response,sz,f8"
ferris-recordfile-fsplit="^([0-9]+\.[0-9]+\.[0-9]+\.[0-9]+) ([^ ]+) ([^ ]+) \[(.+)\] "(.*)" ([0-9]+) ([0-9]+|\-).*"
name="apache_access_log"/>
...
</ferrisls>
</ferrisls>
Once you have set the ferris-type to recordfile and set the
metadata URL as shown above, then you can dig into the log file
as a filesystem as shown below. Note that if you wanted to
import the log somewhere else you can use the --xml or --rdf
options to ferrisls to expose this filesystem as XML or RDF/XML
respectively.
$ fls -0 access_log
0 - - 14/Mar/2009:14:37:06 +1000 585 192.168.100.200 GET / HTTP/1.1 200
1 - - 14/Mar/2009:14:37:06 +1000 287 192.168.100.200 GET /favicon.ico HTTP/1.1 404
But rather than exporting the data as XML, why not just use the
SQLite virtual table interface to query it directly as shown
below. First I make a new virtual table from the log file and
then perform the most basic query on it. The second query shows
how many requests each IP address made and how many bytes were
sent to each IP address.
sqlite> .load libferrissqlitevtable.so
sqlite> create virtual table logfile using libferris(
'file:///tmp/access_log',
'',
ip text,
req test,
response text,
date text,
sz int
);
sqlite> select * from logfile order by ip desc;
file:///tmp/access_log/0|192.168.100.200|GET / HTTP/1.1|200|14/Mar/2009:14:37:06 +1000|585
file:///tmp/access_log/1|192.168.100.200|GET /favicon.ico HTTP/1.1|404|14/Mar/2009:14:37:06 +1000|287
...
sqlite> select count(*),max(ip),sum(sz) from logfile group by ip;
15|192.168.100.200|1798021
3|192.168.110.200|42722
1|192.168.130.200|16927
2|192.168.133.200|271977
Most log files contain a timestamp containing the date and time
that a request or event occurred. In the above query, we
declare the
date column as part of the virtual
table but don't use it in the query. There is one major glitch
-- having the date field as plain text makes performing date
comparisons or grouping by date almost impossible. You are at
the mercy of the string to date functions of your SQL
environment.
To get around this, support was added to libferris to have it
parse arbitrary date and time EA and generate a new EA-epoch
attribute exposing the UNIX epoch value (seconds since 1970).
The epoch time is the lowest common denominator in that it
requires no parsing for sqlite to interpret it properly and you
can perform date and interval comparisons directly on the epoch.
The below command tells libferris to try to parse the
date EA as a time value using the given strftime(3)
time format string. The format fully matches the apache
access_log time format and so libferris will generate a new
date-epoch EA containing the seconds since 1970 for the date EA.
Note that recordfile-types.db that ships with libferris has all
of these modifications already in place for you but knowing how
things are setup lets you parse files in other formats too.
As you can see, I now use the date-epoch column when creating
the virtual table because that value is much more useful to
SQLite. Selecting just the date_epoch column shows you the
number of seconds as an integer. The date_epoch can be made more
digestible with the datetime() SQLite function. If you have more
than one EA that you want to parse into an epoch time, separate
then with the pipe character, for example,
a=%b/%Y|b=%H:%M:%S
echo -n 'date=%d/%b/%Y:%H:%M:%S %z' \
| ferris-redirect --ea ferris-recordfile-field-timeparsers \
recordfile-types.db/apache_access_log
sqlite> .load libferrissqlitevtable.so
sqlite> create virtual table logfile using libferris(
'file:///tmp/access_log',
'',
ip text,
req test,
response text,
date-epoch int,
sz int
);
sqlite> select url,ip,date_epoch,datetime(date_epoch,'unixepoch') from logfile order by ip desc;
file:///tmp/access_log/19|192.168.133.200|1237005517|2009-03-14 04:38:37
file:///tmp/access_log/20|192.168.133.200|1237005517|2009-03-14 04:38:37
...
This leads you to move interesting queries such as the one shown
below which gives a day by day breakdown of bandwidth usage for
the last 60 days. The first part of the where clause converts
the current time into a UNIX epoch value so that the time from
the log file can be subtracted and we can exclude events that
are older than 60 days.
select strftime('%Y-%m-%d',datetime(date_epoch,'unixepoch')) as Date, sum(sz) as Bytes
from logfile
where strftime('%s',datetime('now')) - date_epoch <= 60*60*24*60
group by Date;
Wrap up
If you are more familiar with XML technologies, libferris can
also expose itself as a virtual document to
XQuery.
Currently libferris can expose itself to FUSE, SQLite, XQuery
and as a virtual Xerces-C DOM. A core ideal of libferris is
liberation, you should be able to see your data as a virtual
filesystem, be it from an XML or db4 file, from a relational
database, or from an application like Firefox or emacs. Once
information is available to libferris you should be able to get
at it from whatever tool best suits your current task, a
filesystem, a table in a relational database, or a document in
an XQuery.
