Implementing SQL on files

Implementing SQL on files

[Anuradha Ratnaweera]

Hi all,

I am starting to write some code to add a feature which I think would be
very useful, and like to get comments and suggessions from LKML.  Please
ignore this mail if it sounds like nonsense ;-)

Also, if this is already happenning somewhere, please enlighten me.

Short version: This feature will add a "table" file type and SQL
executioin premitives to the kernel, and also relevent userspace
programs.

Still reading?  Ok, here are the glory details:

Longer version
--------------

Both the two popular open source database systems, Postgres and Mysql
are going through the filesystem layer to manage databases, as opposed
to some commercial counterparts that talk to the block IO layer
directly.  Also, most databases implement user management seperate from
the UNIX user database.  And the database is seperate from the rest of
the filesystem.

The feature I am planning to implenet will overcome these limitations by
implementing a "table" filetype, and the primitive SQL operations in the
kernel.

Parsing SQL, optimizing etc. will happen at the user space.

But here comes the interesting part. I am planning to do this without
breaking VFS and traditional UNIX syscalls.  To clarify, consider this 
table:

Name                Version
----                -------
David Weinehall     2.0
Alan Cox            2.2
Marcelo Tosatti     2.4

Using the userspace tools, one can create a "table" file (say
maintainers), and insert the data to that file.  Each file (or may be
filesystem) has two characters (or strings) associated with them: field
seperator and record seperator.  Say, colon and newline.  If I cat the
file:

% cat maintainers
David Weinehall:2.0
Alan Cox:2.2
Marcelo Tosatti:2.4
%

Now, if I want to add something to the table, either I can use the
relevenet userspace tools, but the following also will work.

% echo 'Linus Torvalds:2.6' > maintainers
%

Even if one uses vi to edit the file instead of a simple echo, it won't
get that complicated.

The nice thing about this is that now we don't need a seperate
authentication mechanism.  Also, the database no longer needs to be a
seperate entity (it need not exist at first place!).

As far as I can see, the best way to implement this is as a ReiserFS 4
module.

Like to hear some comments and suggessions.

Thanks for reading all the way here ;-)

	Anuradha

-- 

http://www.linux.lk/~anuradha/

Re: Implementing SQL on files

[Måns Rullgård]

Anuradha Ratnaweera <anuradha@linux.lk> writes:

> Hi all,
>
> I am starting to write some code to add a feature which I think would be
> very useful, and like to get comments and suggessions from LKML.  Please
> ignore this mail if it sounds like nonsense ;-)
>
> Also, if this is already happenning somewhere, please enlighten me.
>
> Short version: This feature will add a "table" file type and SQL
> executioin premitives to the kernel, and also relevent userspace
> programs.

Things like SQL belong in user space.


[...]

> Using the userspace tools, one can create a "table" file (say
> maintainers), and insert the data to that file.  Each file (or may be
> filesystem) has two characters (or strings) associated with them: field
> seperator and record seperator.  Say, colon and newline.  If I cat the
> file:
>
> % cat maintainers
> David Weinehall:2.0
> Alan Cox:2.2
> Marcelo Tosatti:2.4
> %
>
> Now, if I want to add something to the table, either I can use the
> relevenet userspace tools, but the following also will work.
>
> % echo 'Linus Torvalds:2.6' > maintainers

/etc/passwd

-- 
Måns Rullgård
mru@kth.se

Re: Implementing SQL on files

[Jon Smirl]

Check out Reiser4. Their long term plan is to implement a 
database in a file system. http://www.namesys.com 

Re: Implementing SQL on files

[Hans Reiser]

I hate SQL but I will cooperate with persons seeking to use our storage 
layer to support it.

Re: Implementing SQL on files

[Anuradha Ratnaweera]

On Sun, 2004-02-15 at 11:39, Hans Reiser wrote:
>
> I hate SQL but I will cooperate with persons seeking to use our storage 
> layer to support it.

Thanks Hans.  I am not an SQL fan either.  But more than SQL this
is about database like storage simantics in the filesystem. I had
this idea in my mind for some time, but the real motivation came
after seeing the Reiserfs 4 page, notably this section:

http://www.namesys.com/v4/v4.html#design_flaws_details

and hopefully the plugin design will make life much easier.

By looking at the feedback I got off the list, I think there is a
couple of points I need to clarify.

o The plan is _not_ to implement a fully flegded database inside
  the kernel.  Only the storage (well, "only" may not be the most
  appropriate word here, because this involves a _lot_ of work)
  and the relation operation primitives - or database operation
  primitives to be more general - are implemented in the kernel
  space.

o Userspace tools are necessary to parse a query, optimize if
  necessary, and pass the result (which is a tree) to the kernel.
  And the nodes of the parsed tree will map to the primitives
  implemented in the kernel.

  And most likely, I would be using an exiting database to do the
  heavy userspace work (suggested by Peter Zaitsev of MySQL).

Thanks for all the suggessions and comments (mostly offline).

	Anuradha

-- 

http://www.linux.lk/~anuradha/