← Back to team overview

maria-developers team mailing list archive

Re: mysql improvement idea - field type "file"


Actually I believe that could be implemented as a storage engine? The
database would be the par of the file system that mysqld can access for both
read and write. Each table in it would be a folder and every distinct value
a file. A few additional syntactical constructs would be needed (but lots of
storage engines already have such). And such tables could be restricted to
one column (an 'array of files' does not make sense here I think).

CREATE TABLE webportals ENGINE FILESYSTEM AT /var/lib/mysql; -- will create
a folder named 'webportals' if not exists.
INSERT INTO webportals 'myfunnywebsiteportal.html' VALUES ('a string');--
will create file 'myfunnywebsiteportal.html' if not exists and populate it
with 'a string'. LOAD DATA could be used to populate the file too.

It has some security concerns though to access file system like that!
Indexing of data is an issue too - but such tables will probably have few
records - most often a few files only -  so maybe not really an issue. But
such engine could also have an equivalent to the .MYI file.

(Just one half cent from a non-coder.  And maybe PBXT 'Blob-streaming
engine' does something of the kind already? And the Microsoft text-ODBC
driver actually already does something similar in considering a folder in
file system a table equivalent)


On Thu, May 19, 2011 at 16:51, Tõnu Samuel <tonu@xxxxxx> wrote:

> (
> 1. Sent this mail few days ago to Monty but no answer yet. I know, he is
> busy man.
> 2. I use name "MySQL" because this is real name for me. Oracle still
> sucks:P
> )
> Hi!
> We all have seen cases where people try to use SQL for something what
> does not fit there. Reason is simple - file system itself is very good
> database for some type of objects and queries.
> One common MySQL usage scenario is web portal. Often portal has some
> sort of news and photos accompanying it. My idea is to make some sort of
> clever way to store images within MySQL making their read/write as fast
> as possible and meanwhile giving extra value to user who shouldn't care
> about data integrity keeping data in and out of SQL coherent. Also would
> be cool to get all required data with single query.
> I think best way to implement this is to introduce field type "file"
> which is stored outside of usual data storage. Only his physical
> location on disk is stored in MyISAM or similar. This is exactly same as
> people keep file name in CHAR currently but we provide method to fetch
> file contents with same query. Also we can implement cool replication to
> keep same file on all servers consistent, way to backup things via
> mysqldump etc....
> Actually idea is bit wider but what you think generally?
>  Tõnu
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-developers
> Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp