1

I am currently working on a project with a pre-existing database. The Server is a clustered server with multiple Catalogs (database), and in each Catalog there are multiple Schemas with Tables. The table name format for the traditional SQL query would be [Catalog].[Schema].[Table]. This structure works for the traditional SQL.

The problem comes in when I try to flask db migrate to an sqlite database for testing. I get a number of errors depending on what I try.

I am using

  • Python 3.7
  • Flask 1.0.2
  • Flask-SQLAlchemy 2.4.0
  • Flask-Migrate 2.4.0
  • Windows 10 (not ideal, but its what I have)

I have tried the following with different results:

  • Schema only method:
class User(db.Model):
    __tablename__ = 'user'
    __table_args__ = (
        db.PrimaryKeyConstraint('userid')
        , db.ForeignKeyConstraint(('manageruserid',), ['CatalogA.SchemaA.userid'])
        , {'schema': 'CatalogA.SchemaA'}
    )

    manager_user_id = db.Column('manageruserid', db.Integer())
    user_id = db.Column('userid', db.Integer(), nullable=False)

class Tool(db.Model):
    __tablename__ = 'tool'
    __table_args__ = (
        db.PrimaryKeyConstraint('toolid')
        , db.ForeignKeyConstraint(('ownerid',), ['CatalogA.SchemaA.user.userid'])
        , {'schema': 'CatalogB.SchemaB'}
    )

    tool_id = db.Column('toolid', db.Integer())
    owner_id = db.Column('ownerid', db.Integer(), nullable=False)

when trying to upgrade it creates an error:

"sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unknown database "CatalogA.SchemaA" [SQL: CREATE TABLE "CatalogA.SchemaA".user (
        manageruserid INTEGER,
        userid INTEGER NOT NULL,
        PRIMARY KEY (userid),
        FOREIGN KEY(manageruserid) REFERENCES user (userid) )"
  • Bind With Schema (binds are setup correctly)
class User(db.Model):
    __bind_key__ = 'CatalogA'
    __tablename__ = 'user'
    __table_args__ = (
        db.PrimaryKeyConstraint('userid')
        , db.ForeignKeyConstraint(('manageruserid',), ['CatalogA.SchemaA.user.userid'])
        , {'schema': 'SchemaA'}
    )

    manager_user_id = db.Column('manageruserid', db.Integer())
    user_id = db.Column('userid', db.Integer(), nullable=False)

class Tool(db.Model):
    __bind_key__ = 'CatalogB'
    __tablename__ = 'tool'
    __table_args__ = (
        db.PrimaryKeyConstraint('toolid')
        , db.ForeignKeyConstraint(('ownerid',), ['CatalogA.SchemaA.user.userid'])
        , {'schema': 'SchemaB'}
    )

    tool_id = db.Column('toolid', db.Integer())
    owner_id = db.Column('ownerid', db.Integer(), nullable=False)

when trying to migrate it creates an error:

"sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'user.manageruserid' could not find table 'CatalogA.SchemaA.user' with which to generate a foreign key to target column 'userid'"

If I do it the Schema only method way then I can run queries on the database, but it doesn't correctly setup my test-db.

I looked for multiple hours trying to find a solution, and would love someone to help me find the way forward (if you find a link to another solution, please tell me what you searched as well to increase my google-fu).

Main questions are:

  • What is the right way to have a model for this situation?
  • Was/Is there something in the documentation which I missed for this scenario?
SuperShoot
  • 9,880
  • 2
  • 38
  • 55
Dan
  • 13
  • 1
  • 5
  • 1
    SQLite does not support what you're trying to do. If you're going to test, use the correct database (SQL Server from the looks of it). SQLite does support [attaching databases](https://www.sqlite.org/lang_attach.html), but that only gets you 1 level of "nesting" (`schema-name . table-name`). – Ilja Everilä Apr 30 '19 at 04:59
  • SQLite does not support schemas. You will need to remove that from your models if you intend to switch to SQLite. If you want to support both your original database with schemas and SQLite from a single set of models, then you are going to need to figure out a way to conditionally include or exclude the schema declaration in these models. – Miguel Grinberg May 01 '19 at 02:29

0 Answers0