BUU has asked for the wisdom of the Perl Monks concerning the following question:

I'm working on a project thats going to have a lot of user created perl code, backed by a database. However, the user written perl code will only have access to the database, so it can't create files on the filesystem or any thing of that nature, and also will not have any access to change the schema of the database, all it can do is retrieve specific information from table.

However, occassionally the user created perl code might need to have access to database related functionality. Now given the above constraints, the code can't create it's own table in whatever database is being used, so that option is out. It also can't create files on the filesystem to emulate certain database tasks.

Now of course my immediate reaction was that I could just use something like a BerkeleyDB database, and then store all of it's data in the table the script has access to. However, due to the twists and turns of fate, the database that the berkely db will be stored in might turn out to be a berkely db as well! So my question is,

A) is it possible to do something like this, storing a database within another one
B) are there any issues that might arise from something like this?
C) Or am I stuck using storable to free/thaw my data in to the database?

Replies are listed 'Best First'.
Re: Databases with in databases
by dws (Chancellor) on Sep 25, 2003 at 08:22 UTC
    Is it possible to do something like this, storing a database within another one?

    Yes, with some definitional quibbles. If the user-supplied Perl code can get by with storing and retrieving data structures, then it might be possible to pickle data structures into BLOBs (perhaps using Storable or Data::Dumper), and then store the BLOBS in a table you're reserved for this purpose. You do, of course, lose the ability to query within the the BLOB.

    Is this what you meant by your option (C)?

[reply]
Re: Databases with in databases
by ViceRaid (Chaplain) on Sep 25, 2003 at 09:38 UTC

    I was recently asked to look at something similar at work recently, to enable keeping a "database" of contacts which would be stored in a RDBMS (Oracle, in this case). It's certainly do-able, in more ways than one: serialisation (using Storable, YAML, or Data::Dumper, for example); non-relational databases (à la BerkeleyDB); a faked RDBMS (for example, DBD::CSV); or tightly controlled limited access to another real relational database. Anyway, here's a few things to consider.

    • How much is performance an issue? If you're going to be loading up these databases hundreds of times a second, something fast like Storable, BerkeleyDB or a real RDBMS with persistent connections is gonna be a good choice;
    • How much do you trust the people using the 'database'? if you can trust them, and the functionality is well protected, perhaps giving them a sandpit relational database is the way to go. Most major RDBMS (Oracle/MySQL/PostgreSQL/whatever) allow very fine-grained configuration of user rights;
    • How much do you need the full power of SQL, as opposed to just persistent data? If you don't need it, go for a simpler serialisation-style solution.
    • Have a think through how things like transactions and commits will be handled: do you want data to be written transparently (more like tie), or do you want explicit control over when the little database is written to the big main database (more like DBI)?

    If you wanted a full relational database, and really couldn't give even limited access to limited 'real' databases to your users, then I think you'd have to use a modified DBD::CSV, with the file-based interface (based on DBD::File replaced with one that fetched the source CSV from TEXT fields in your main database

    HTH
    ViceRaid

[reply]
Re: Databases with in databases
by jdtoronto (Prior) on Sep 25, 2003 at 14:36 UTC
    As others have said it is certainly possible. I have used DBD::CSV and saved the resulting structure in a BLOB in a MySQL database. I also regularly use Data::Dumper to store structures in a MySQL text or BLOB field in situations where the structure of the data is variable. For example, right now I am using SQL::Abstract to build SQL queries within a programme. The queries are dynamically created, but it is useful to be able to recall the queries. So the complex structure which is used to build the query is saved as a text element using Data::Dumper, then it is eval'ed when we retrieve it and the hash is reconstructed.

    The type of 'recursion' that you propose, is not impossible to conceive, and if it can be conceived it can be coded!

    jdtoronto

[reply]
Re: Databases with in databases
by johndageek (Hermit) on Sep 25, 2003 at 23:00 UTC
    I gather from your description that the user created code is yours.

    A couple of questions come to mind.

    1) Why will they not give you the access you request?
    Could it be space constraints, system performance, uncomfortable with your design?

    2) You say it "Might need" database related functionality.
    If your design is this incomplete, it is no surprise the DBA will not change the schema.

    3) Your statement concerning "My immediate reaction is to hack a way around the rules".
    If this is employment related, stop, drop and roll, because if your hack cooks a production database, some one will be waving the ugly stick in your direction.

    If this is school related - Same stick, less cash value risk.

    I know the rules ain't no fun, but if you are in an environment where the rules exist, and you can not change them, there is a reason.

    Difficulties I see with the proposed solution from a programming stand point: the database you store in another database (dysiad) will not act like a database to your program, you will not be able to order data, read data selectively or any of the other cool things a database can do for you. The best I can see (since you are not allowed to use disk either) is that you will store the dysiad in the main database, slurp it into memory as an array, perhaps, manipulate it, and write it back to the main database as a complete block..

    I am willing to learn why and how this would be useful, but am also interested in why you are under these constraints.

    No flame intended, but I have done corporate recoveries from people intellegent enough to bypass the rules in some clever way.

    dageek

    If I am convinced that the rules souldn't apply to me, they probably were written for me.

[reply]
      >>I gather from your description that the user created code is yours.

      Not at all. *My* code would be the framework that is running these various bits of user submitted code.


      >>1) Why will they not give you the access you request?

      The most common answer is that my framework will only have a very limited amount of access, as in just access to one database, think low end commerical webhosting. It seems to be that it's inefficient / annoying to create a new table for every bit of user code that wants DB like functions, when just storing a pseudo db in some table in the database would be much cleaner.


      >>2) You say it "Might need" database related functionality.

      I have no clue what the user submitted code might need/do. It is beyond my control.


      >>3) Your statement concerning "My immediate reaction is to hack a way around the rules".

      This isn't employment related and any rules are merely guidelines for me. Perhaps they might be better called "wishes" instead of rules.


      >>Difficulties I see with the proposed solution from a programming stand point: the database you store in another database (dysiad) will not act like a database to your program

      Actually according to a few of the people posting above you, the database I store inside another one will be able to emulate a 'real' database down to a very low level, including the nice sql calls, just not quite as efficiently as a 'real one' might. But then I'm not really looking in to storing a million row table inside another million row table.
[reply]
Re: Databases with in databases
by graff (Chancellor) on Sep 26, 2003 at 05:45 UTC
    If there's some sort of state information or some costly-to-compute digest that someone wants to preserve across different runs of their user-supplied perl code, and you're looking for a place to store that, well... Where is this user-supplied perl code really coming from? And assuming that it outputs something to the user who supplied it, where does that output go? Is it all keyboard/screen i/o at a console, or is there a network interface of some sort? Don't the users have some place to store and fetch their perl code, or do they have to type it in from scratch every time they use this facility?

    If users are able to store their perl code somewhere, and have the ability to store the output from their code in the same place, then they'll want to structure their usage so that the output they want to keep/re-use can be attached as __DATA__ to the next bit of perl code they submit.

    That's a lot of if's and assumptions and questions -- your post was awfully vague.

[reply]

Back to Seekers of Perl Wisdom