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

[root@615349 orafs]# ls -ltr
total 148
-rw-r--r--  1 oracle dba   1542 Sep 23  2007 orafs.h
drwxr-xr-x  2 root   root  4096 Sep 23  2007 old
-rw-r--r--  1 oracle dba    804 Jun 15 00:13 makeit
-rw-r--r--  1 oracle dba   5824 Jun 15 00:17 orafs.c
drwxr-xr-x  2 root   root  4096 Jun 15 00:19 mount
drwxr-xr-x  5 root   root  4096 Jun 15 01:21 backup
-rw-r--r--  1 oracle dba  14070 Jun 15 01:21 ora_driver.c
-rw-r--r--  1 root   root 15084 Jun 15 01:21 ora_driver.o
-rw-r--r--  1 root   root 13972 Jun 15 01:21 orafs.o
-rwxr-xr-x  1 root   root 29612 Jun 15 01:21 orafs
[root@615349 orafs]# ls -ltr mountdir
total 0
[root@615349 orafs]# ./orafs mountdir
[root@615349 orafs]# cd mountdir
[root@615349 mountdir]# ls -ltr
total 0
drwxr-xr-x  2 root root 0 Dec 31  1969 TSMSYS
drwxr-xr-x  2 root root 0 Dec 31  1969 SCOTT
drwxr-xr-x  2 root root 0 Dec 31  1969 OUTLN
drwxr-xr-x  2 root root 0 Dec 31  1969 HR
drwxr-xr-x  2 root root 0 Dec 31  1969 FLOWS_FILES
drwxr-xr-x  2 root root 0 Dec 31  1969 FLOWS_020100
drwxr-xr-x  2 root root 0 Dec 31  1969 DIP
drwxr-xr-x  2 root root 0 Dec 31  1969 DBSNMP
drwxr-xr-x  2 root root 0 Dec 31  1969 CTXSYS
drwxr-xr-x  2 root root 0 Dec 31  1969 ANONYMOUS
[root@615349 mountdir]# cd HR
[root@615349 HR]# ls -ltr
total 0
drwxr-xr-x  2 root root 0 Dec 31  1969 REGIONS
drwxr-xr-x  2 root root 0 Dec 31  1969 LOCATIONS
drwxr-xr-x  2 root root 0 Dec 31  1969 JOBS
drwxr-xr-x  2 root root 0 Dec 31  1969 JOB_HISTORY
drwxr-xr-x  2 root root 0 Dec 31  1969 EMPLOYEES
drwxr-xr-x  2 root root 0 Dec 31  1969 DEPARTMENTS
drwxr-xr-x  2 root root 0 Dec 31  1969 COUNTRIES
drwxr-xr-x  2 root root 0 Dec 31  1969 CDEMO81_EMP
drwxr-xr-x  2 root root 0 Dec 31  1969 CDEMO81_DEPT
drwxr-xr-x  2 root root 0 Dec 31  1969 CAR
[root@615349 HR]# cd EMPLOYEES
[root@615349 EMPLOYEES]# ls -ltr
total 0
-r--r--r--  1 root root 0 Dec 31  1969 data.csv
[root@615349 EMPLOYEES]# cd ../COUNTRIES
[root@615349 COUNTRIES]# ls -ltr
total 0
-r--r--r--  1 root root 0 Dec 31  1969 data.csv
[root@615349 COUNTRIES]# cd ../../..
[root@615349 orafs]# umount mountdir

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.