dynamic schema with postgresql

[Joe Martin]

I need to create a new schema with some tables in it whenever a new company record is added.

Below are my entities (defined with Flask-SqlAlchemy framework extension):

class Company(db.Model):

    __tablename__ = 'company'

    __table_args__ = {"schema":"app"}

    id = db.Column(db.Integer, primary_key=True)

    name = db.Column(db.String(100), nullable=False)

class Customer(db.Model):

    __tablename__ = 'customer'

    company_id = db.Column(db.Integer, db.ForeignKey('app.company.id', onupdate='CASCADE', ondelete='CASCADE'), nullable=False)

    id = db.Column(db.Integer, primary_key=True)

    code = db.Column(db.String(20), nullable=False)

    name = db.Column(db.String(100), nullable=False)

Now when a company is inserted the following code executes:

            company_schema = 'c' + str(company_id)

            db.session.execute(CreateSchema(company_schema))

            db.session.commit()

            meta = db.MetaData(bind=db.engine)

            for table in db.metadata.tables.items(): 

                if table[1].name not in ('company'):

                    table[1].tometadata(meta, company_schema) 

            meta.create_all()

The above will throw an error: 

    NoReferencedTableError: Foreign key associated with column 'customer.company_id' could not find table 'company'

After some googling, I found that tometadata() changes constraint schema too. I also found a code, 

suggested by Michael Bayer (http://www.mail-archive.com/sqlal...@googlegroups.com/msg13008.html): 

def copy_table(table, metadata, schema_map):

    args = []

    for c in table.columns:

        args.append(c.copy())

    for c in table.constraints:

        if isinstance(c, db.ForeignKeyConstraint):

            elem = list(c.elements)[0]

            schema = schema_map[elem.column.table.schema]

        else:

            schema=None

        args.append(c.copy(schema=schema))

    return db.Table(table.name, metadata, schema=schema_map[table.schema], *args)

Now I replaced tometadata() with the above function:

    copy_table(table[1], meta, {None: schema_name, 'app': 'app'})

    print meta.tables.items()

But the error is the same:  NoReferencedTableError. However, print clearly shows that 

for company_id constraint, schema hasn't changed:

('c28.customer', 

Table('customer', MetaData(bind=Engine(postgresql://postgres:concept@localhost:5432/clients)), 

Column('company_id', Integer(), ForeignKey('app.company.id'), table=<customer>, nullable=False), 

Column('id', Integer(), table=<customer>, primary_key=True, nullable=False), 

Column('description', String(length=100), table=<customer>, nullable=False), schema='c28')) 

I also tried to define ForeignKey as follows, but the error is the same:

company_id = db.Column(db.Integer, db.ForeignKey(Company.__table__.c.id, onupdate='CASCADE', ondelete='CASCADE'), nullable=False)

Can someone kindly help me with this issue?

[Michael Bayer]

Well that's because you are specifically not copying the "company" table over to that new MetaData object.   customer.c.company_id has a ForeignKey in it, which has a string "app.company.id" in it, how can it find the Table that points towards ?   

since you're just looking for "create table " here and nothing else (I'm hoping, also FYI no DBA in the world would let you build an app that creates tables on the fly like this), copy the whole metadata over to the new one, but when you copy over "company" itself, don't supply an alternate schema, just leave it with "app".

[Joe Martin]

Thank you for your reply. Then I thought the following would work:

            company_schema = 'c' + str(company_id)

            db.session.execute(CreateSchema(company_schema))

            db.session.commit()

            meta = db.MetaData(bind=db.engine)

            for table in db.metadata.tables.items(): 

                if table[1].name == 'customer':

                    table[1].tometadata(meta, company_schema) 

                elif table[1].name == 'company':

                    table[1].tometadata(meta, 'app') # or  table[1].tometadata(meta)?

            print meta.tables.items()

            meta.create_all()

Now I see print meta shows 2 tables, but somehow error is still the same:

    "NoReferencedTableError: Foreign key associated with column 'customer.company_id' 

    could not find table 'company' with which to generate a foreign key to target column 'id' "

However, with my original metadata I was able to create both tables: app.company and public.customer.

So, I'm confused with the issue. Thanks for your time.

[Michael Bayer]

On Sep 11, 2013, at 10:36 PM, Joe Martin <jand...@gmail.com> wrote:

Thank you for your reply. Then I thought the following would work:

            company_schema = 'c' + str(company_id)

            db.session.execute(CreateSchema(company_schema))

            db.session.commit()

            meta = db.MetaData(bind=db.engine)

            for table in db.metadata.tables.items(): 

                if table[1].name == 'customer':

                    table[1].tometadata(meta, company_schema) 

                elif table[1].name == 'company':

                    table[1].tometadata(meta, 'app') # or  table[1].tometadata(meta)?

            print meta.tables.items()

            meta.create_all()

Now I see print meta shows 2 tables, but somehow error is still the same:

    "NoReferencedTableError: Foreign key associated with column 'customer.company_id' 

    could not find table 'company' with which to generate a foreign key to target column 'id' "

However, with my original metadata I was able to create both tables: app.company and public.customer.

So, I'm confused with the issue. Thanks for your time.

The way it works is this:

a Table object that has a ForeignKey() object inside of it, is going to want to find the target column that this ForeignKey points to.

There are two ways to specify it.  One is via string:

ForeignKey("<schemaname>.tablename.columname")

if you use that approach, the MetaData for this table *must have a Table with exactly that target name present*.  The table[1].name == 'company' conditional you have seems to be doing this, but then the error you're showing me doesn't include "app" inside of it, so perhaps you want to do tometadata(meta, None), not sure.

the other way, which might make this all easier, is to put the actual Column object in the ForeignKey:

ForeignKey(my_company_table.c.id)

if you do it that way, then you can go back to your original approach where you don't copy "company" at all - if ForeignKey is given the Column directly, then no string lookup in the MetaData is needed and you can point to any Table anywhere.

[Joe Martin]

Thank you for your kind help; it was really helpful.