dynamic mysql columns

[Marc Dojka]

I'm thinking of switching a site from php to python on web2py, however
I've got a question on its mysql support. I'm trying to figure out
how web2py handles tables where the column layout isn't fixed.
Meaning that it's possible for columns to be created, used, and
deleted by the app while it's running. Currently I assign api names
to these columns (ms1, ms2, ...). There is a seperate table that
tells the app some information about these columns (and that they
exist). Everything works great in php land. Let me know. Thanks.

[mdipierro]

In web2py you can do something like:

# define a table 'meta' that describes another table 'from_meta'
db.define_table('meta',Field('colname'),Field('coltype'))
# make sure it contains something
if not db(db.meta.id>0).count()
db.meta.insert(colname='your_name',coltype='string'))
# define from_meta based on the content of table meta
meta = db(db.meta.id>0).select()
db.define_table('from_meta',*[Field(m.colname,m.coltype) for m in
meta],migrate=True)

as you edit records in 'meta', table 'from_meta' is automatically
altered accordingly. you do not need any other code. T3 uses this
mechanism.

This is one of the coolest things I like in the DAL. Try do this in
any other Python web framework.

Massimo

[Thadeus Burgess]

I have to ask...

Why on earth do you have dynamic columns? Can you tell us more as to why
you *need* this, maybe there is a better way.

You can always dynamically build your db.define_table() statements, the
DAL will migrate them as needed.

SO first, build your dynamic fields in a python list

my_fields = []

for what_i_need in what_i_base_it_off_of:
if what_i_need == what_i_want:
my_fields.append(Field('ms1'))
else:
my_fields.append(Field('ms2'))

db.define_table('SuperDynamicTable', *my_fields)

--
ThadeusB

[DenesL]

Very interesting method.
In theory all tables can be defined and generated this way.
Tables could be defined via forms, enforcing the proper creation
sequence (tables with fields that reference other tables will have to
be created later in the process). Or the generation has to be
controlled via a flag.

I wonder if there are any advantages/uses to this?.