Generate new models and schema at runtime

views:

128

answers:

Generate new models and schema at runtime

Let's say your app enables users to create their own tables in the database to hold their own, custom data. Each table would have it's own schema. What are some good approaches?

My first stab involved dynamically creating migration files and model files bu I'd like to run this on heroku where you can't write to the filesystem.

I'm thinking eval may be the way to go to create and run the migration class and the model class. But I want to make sure the model class exists when a new process of the app is spawned. Can probably do this by storing these class definition with each user as they create new tables and then run through them all at startup. But now it's convulted enough that I may be missing something obvious.

+2 A:

It's probably a better idea not to generate new classes on runtime. Besides all of the security risks, each thread's startup time will be abominable if you ever get a significant number of users.

I would suggest rethinking your app design and aim at generic tables to hold the user's custom data. If you have examples of data structures that users can create we might be able to help.

Ron Gejman 2009-11-22 15:20:54

You're right about scaling nightmares. I'll go back to thinking about a generic table like you suggest. The user's imported data structure would be like a yml dump of a database from somewhere else. So there could be a variable number of column and each column could be a different type (string, Boolean, date...). The data would also need to be queried on efficiently.

realdeal 2009-11-22 15:52:02

If that's the case then I would suggest spinning off "mini-apps" for each user.

Ron Gejman 2009-11-22 15:53:42

@Ron: Can you please explain a little bit both drawbacks that you mention, security and performance? I was planning to do similar thing with my app, to dynamically create app models based on metadata stored in database. Everything would happen upon Rails application start.Of course, I don't like the idea of users being able to create arbitrary number of tables with arbitrary number of columns, but I would like to store metadata in the database, opposed to having them stored as ruby models.

Mladen Jablanović 2009-11-22 18:17:48

How are you supposed to check that the classes users are creating don't already exist (and are simply not (yet) loaded at runtime)? If they do exist, and you didn't check/realize, they could be adding (or replacing) methods in those classes, posing security risks. Then, since you'd have to be `evaling` all of the class creations, you would have to have really severe sanitization procedures to prevent someone getting into your system.In terms of speed: just imagine for yourself the memory burden placed on the app when you have hundreds or thousands of models and hundreds or thousands of tables

Ron Gejman 2009-11-22 20:01:49

So, performance problem doesn't come from the idea itself, but from allowing users to abuse it, right? So, simply, 1) put restrictions on the number of tables allowed and 2) implement class_eval logic in such manner that no sanitization is needed. Regarding the naming collision, simple prefixing tablenames with either usernames or userids will do the job.So, basically, what I'm saying is that there's nothing wrong with the idea of creating models dynamically, problems can arise only with actual implementation and intended usage.

Mladen Jablanović 2009-11-23 08:17:29

There is (was?) a cool Wiki project, called Informl. It was a Wiki, not just for web pages but for web applications. (Get it? It's informal because it's a Wiki, it's got forms because it is an application, and it's user-generated, thus Web 2.0, which means that according to an official UN resolution it is legally required to have a name which is missing a vwl.)

So, in other words, it was not just about user-generated content, but also user-generated structured data.

They did this by generating PostgreSQL-specific SQL at runtime to create new tables and then have ActiveRecord reload the schemas.

The code is up on RubyForge. It's based on Rails 1.2.3. I guess you could do much better than that today, especially with the upcoming extensibility interfaces in Rails 3.

Jörg W Mittag 2009-11-22 18:56:00

Have you thought about a non-sql database for those tables? Look at CouchDB - there are several plugins on Github that integrate it with rails. Records in the database are JSON documents, with arbitrary key-value structure. May be perfect for a user-defined schema.

Ben 2009-11-22 23:59:27

I'm considering Mongodb after sitting on their talk at Rubyconf. I just wanted to first see the feasibility of an SQL/AR solution since I'd like to do this with Heroku which is Postgres by default. Might look at Couch again too!

realdeal 2009-11-23 00:32:44

ansaurus

tags:

views:

answers:

Generate new models and schema at runtime

related questions