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.
[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:
- A connection to Oracle was made as SYSTEM
- 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.
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).
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.
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?
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
hi cool staff i like it ..
if you need any help
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 ?