An Oracle Schema Filesystem

I’m one of those types that always needs a project. If I run out of projects using the things I know, I take on a project that involves something I don’t know whatsoever.

For instance, building a filesystem in C that connects to Oracle and uses queries in order to create inode structures.

It’s not done by a long shot; hell, it’s not even presentable, but I’m still kind of proud of it since:

• I didn’t know C when I started it
• It’s a neat concept
• If I can remember how I did it, it could be an interesting piece of software

I won’t pretend that I know how to do complex filesystem work. I did have some help.

FUSE – Filesystems In Userspace

The main component (other than C itself and the OCI) is a software package called FUSE. Basically it is an interface to the kernel that allows user created pass-through filesystems. Instead of creating a filesystem that maps only to disk, you can create directory structures, files, etc. that map to anything you can imagine. I have seen a Wikipedia Filesystem, a Zip Filesystem, and several others that are very interesting. One in particular struck me as a good idea: DBToy from thesaguaros.com. DBToy is a database browser for use with MySQL or Postgres. Using that tool as a guide, I was able to hack out a version for Oracle.

Example Usage

 

But What Did It Do?

When I ran the orafs program, two things happened:

1. A connection to Oracle was made as SYSTEM
2. A FUSE filesystem was mounted on the mountdir directory using API calls

This allowed me to cd to the mountdir directory and use standard OS commands like ls -ltr. The first level directory is schema names. In each schema directory are directories for each table. In each table directory is a file called data.csv. It doesn’t actually contain anything yet, but hey, it’s a start. Just being able to navigate through Oracle’s data dictionary at the OS level is neat enough.

Also notice that to remove it, I had to use the umount command. This isn’t a fake mounting of any kind, it truly did get mounted at the OS level!

Nothing On Disk

One of the coolest features of FUSE is that nothing had to be written at all to disk. The data from the data dictionary was not written to disk at any time. The orafs program used no flat files or anything of the sort. When ls -ltr is called, it actually did a query on the fly. For instance, select table_name from dba_tables where owner = ?. My code passes results to FUSE, and FUSE makes it understandable to the OS.

Great, But Why?

Yeah, that’s the big question. The reason I even did it in the first place was that I thought it would be a neat concept. Then it was a challenge. Now it’s something for which I have absolutely no time!

But if I had things my way, here’s what I’d love to do with it:

• Have 3 files in each table subdirectory: data.xml, data.csv, and ddl.sql. The data.xml and data.csv files would have all of the rows from the table arranged in the proper format. This table would not actually be queried until you opened the file in some way. Imagine, simply doing cp data.csv /home/oracle/table.csv to get a comma delimited dump of your data! ddl.sql would, of course, contain the DDL to regenerate the object.
• Create symbolic links to child tables based on constraints
• Use the creation date from DBA_OBJECTS and DBA_USERS to set the dates when browsing the directory
• Allow command line args for login/password instead of hardcoding. I had this at one point but got rid of it due to coding issues
• Figure out how to use malloc() and calloc() properly so I don’t waste memory

It’s a big list that I’ll probably never get through; however, it doesn’t hurt to have goals.

In fact, imagine some other uses for FUSE with Oracle (maybe I’ll tackle them one day):

• Mount an ASM diskgroup, allowing ASMCMD-like commands for browsing, but also allowing cp commands to a normal filesystem using RMAN commands in the code
• Mount a table that contains a BLOB and a column containing the file name of each row. You could actually see the BLOBs on disk with the proper filename, all dynamic from the DB without duplicating space usage

I may actually try to learn some of the FUSE bindings I’ve seen. FUSE-J for instance is a Java binding, which would probably make the coding MUCH easier. However, the thought of rewriting all the code I’ve done so far for orafs makes my head hurt.

Anyone good with C and OCI that would like to see the code? I’d be happy to share, maybe someone else can do something with it. After you fix all my horrible code, of course.

6 Responses to “An Oracle Schema Filesystem”

1. Chris Neumueller says:

   June 15, 2008 at 1:14 pm

   I really like that . Apart from being a cool hack, it might be a nice addition to what XML DB provides.

   By the way, filesystem access to databases is not a new concept. During the ODBMS boom, several databases added NFS access to their objects. See for example the Shore project’s Value Added Server (http://www.cs.wisc.edu/shore/) and ODE’s OdeFS (http://citeseer.ist.psu.edu/310215.html).
2. The Oracle Alchemist says:

   June 15, 2008 at 1:24 pm

   Shhh, you’re bursting my bubble.

   Just kidding, thanks for the info! I’ve done a ton of fun stuff with ODBMS, but it’s really not common enough to post too much on here. Maybe when I run out of other blog fodder.
3. Ben Prusinski says:

   June 18, 2008 at 3:26 pm

   Hey Steve,

   Kudos for taking on a new technical challenge. Very interesting- do you have plans to use direct SGA access with C to play around with Oracle memory as part of the project?

   Regards,
   Ben Prusinski
   http://oracle-magician.blogspot.com/
4. Daniel Paten says:

   July 15, 2008 at 11:27 am

   Hey !!! Very good idea,

   Yes very interesting … Do you have plan to share your code ? I am very interesting to test it and insert support for blob use . Insert / Read blob colon as as “virtual” file ?
   Thanks in advance
   Regards
   Daniel Paten
5. baruch says:

   October 26, 2008 at 9:42 am

   hi cool staff i like it ..

   if you need any help

   barucho@gmail.com
   xpert.com
6. Vincent Rogier says:

   September 10, 2009 at 11:41 am

   Hi,

   I’m the designer of the OCILIB library (C driver for Oracle), a widely used C wrapper that has a JBDC like interface around OCI.

   I’m was thinking to design and write an open source complete oracle virtual filesystem (using fuse on unix and doran on windows) that provides db browsing, data copy, export, etc…

   I was wondering that if you would you share the code you wrote ?

   Best regards,

   Vincent