sqlalchemy.ForeignKeyConstraint

Here are the examples of the python api sqlalchemy.ForeignKeyConstraint taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

55 Examples 7

Example 1

Project: alembic
Source File: test_batch.py
View license
    def test_drop_fk(self):
        impl = self._named_fk_fixture()
        fk = ForeignKeyConstraint([], [], name='ufk')
        impl.drop_constraint(fk)
        new_table = self._assert_impl(
            impl, colnames=['id', 'email', 'user_id'],
            ddl_not_contains="CONSTRANT fk1")
        eq_(
            list(new_table.foreign_keys),
            []
        )

Example 2

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.provide_metadata
    def test_fk_table_auto_alter_constraint_create(self):
        metadata = self.metadata

        Table("a", metadata,
              Column('id', Integer, primary_key=True),
              Column('bid', Integer),
              ForeignKeyConstraint(["bid"], ["b.id"])
              )
        Table(
            "b", metadata,
            Column('id', Integer, primary_key=True),
            Column("aid", Integer),
            ForeignKeyConstraint(["aid"], ["a.id"], name="bfk"))
        self._assert_cyclic_constraint(
            metadata, auto=True, sqlite_warning=True)

Example 3

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.provide_metadata
    def test_fk_table_use_alter_constraint_create(self):
        metadata = self.metadata

        Table("a", metadata,
              Column('id', Integer, primary_key=True),
              Column('bid', Integer),
              ForeignKeyConstraint(["bid"], ["b.id"])
              )
        Table(
            "b", metadata,
            Column('id', Integer, primary_key=True),
            Column("aid", Integer),
            ForeignKeyConstraint(
                ["aid"], ["a.id"], use_alter=True, name="bfk"))
        self._assert_cyclic_constraint(metadata)

Example 4

Project: sqlalchemy
Source File: test_constraints.py
View license
    def test_render_add_fk_constraint_stringcol(self):
        t, t2 = self._constraint_create_fixture()

        constraint = ForeignKeyConstraint(["b"], ["t2.a"])
        t.append_constraint(constraint)
        self.assert_compile(
            schema.AddConstraint(constraint),
            "ALTER TABLE tbl ADD FOREIGN KEY(b) REFERENCES t2 (a)"
        )

Example 5

Project: sqlalchemy
Source File: test_constraints.py
View license
    def test_render_add_fk_constraint_realcol(self):
        t, t2 = self._constraint_create_fixture()

        constraint = ForeignKeyConstraint([t.c.a], [t2.c.b])
        t.append_constraint(constraint)
        self.assert_compile(
            schema.AddConstraint(constraint),
            "ALTER TABLE tbl ADD FOREIGN KEY(a) REFERENCES t2 (b)"
        )

Example 6

Project: pygtfs
Source File: gtfs_entities.py
View license
def create_foreign_keys(*key_names):
    """ Create foreign key constraints, always including feed_id,
        and relying on convention that key name is the same"""
    constraints = []
    for key in key_names:
        table, field = key.split('.')
        constraints.append(ForeignKeyConstraint(["feed_id", field],
                                                [table+".feed_id", key]))
    return tuple(constraints)

Example 7

View license
    @declarative.declared_attr
    def __table_args__(cls):
        return (
            sa.ForeignKeyConstraint(['device_id'],
                                    ['midonet_gateway_devices.id'],
                                    ondelete="CASCADE"),
            model_base.BASEV2.__table_args__,
        )

Example 8

Project: oslo.db
Source File: test_exc_filters.py
View license
    def test_raise(self):
        matched = self.assertRaises(
            exception.DBNonExistentConstraint,
            self.engine.execute,
            sqla.schema.DropConstraint(
                sqla.ForeignKeyConstraint(["id"], ["baz.id"],
                                          name="bar_fkey",
                                          table=self.table_1)),
        )
        self.assertInnerException(
            matched,
            "ProgrammingError",
            "constraint \"bar_fkey\" of relation "
            "\"resource_foo\" does not exist\n",
            "ALTER TABLE resource_foo DROP CONSTRAINT bar_fkey",
        )
        self.assertEqual("resource_foo", matched.table)
        self.assertEqual("bar_fkey", matched.constraint)

Example 9

Project: oslo.db
Source File: test_exc_filters.py
View license
    def test_raise(self):
        matched = self.assertRaises(
            exception.DBNonExistentConstraint,
            self.engine.execute,
            sqla.schema.DropConstraint(
                sqla.ForeignKeyConstraint(["id"], ["baz.id"],
                                          name="bar_fkey",
                                          table=self.table_1)),
        )
        # NOTE(jd) Cannot check precisely with assertInnerException since MySQL
        # error are not the same depending on its version…
        self.assertIsInstance(matched.inner_exception,
                              (sqlalchemy.exc.InternalError,
                               sqlalchemy.exc.OperationalError))
        if matched.table is not None:
            self.assertEqual("resource_foo", matched.table)
        if matched.constraint is not None:
            self.assertEqual("bar_fkey", matched.constraint)

Example 10

Project: dokomoforms
Source File: answer.py
View license
def _answer_mixin_table_args():
    return (
        sa.ForeignKeyConstraint(
            ['id', 'the_allow_other', 'the_allow_dont_know'],
            ['answer.id', 'answer.allow_other', 'answer.allow_dont_know'],
            onupdate='CASCADE', ondelete='CASCADE'
        ),
        sa.CheckConstraint(
            # other is null if "other" responses are not allowed
            "(the_allow_other) OR (other IS NULL)",
            name='check_whether_other_is_allowed'
        ),
        sa.CheckConstraint(
            # dont_know is null if "dont_know" responses are not allowed
            "(the_allow_dont_know) OR (dont_know IS NULL)",
            name='check_whether_dont_know_is_allowed'
        ),
        sa.CheckConstraint(
            """
            (CASE WHEN (main_answer IS NOT NULL) AND
                       (other       IS     NULL) AND
                       (dont_know   IS     NULL)
                THEN 1 ELSE 0 END) +
            (CASE WHEN (main_answer IS     NULL) AND
                       (other       IS NOT NULL) AND
                       (dont_know   IS     NULL)
                THEN 1 ELSE 0 END) +
            (CASE WHEN (main_answer IS     NULL) AND
                       (other       IS     NULL) AND
                       (dont_know   IS NOT NULL)
                THEN 1 ELSE 0 END) =
            1
            """,
            name='only_one_answer_type_check'
        ),
    )

Example 11

Project: iktomi
Source File: replication.py
View license
    def test_replication_composite_pk_relationship(self):
        # Schema
        class Category1(self.Base):
            id = Column(Integer, primary_key=True)
        class Node1(self.Base):
            id = Column(Integer, primary_key=True)
            category_id = Column(ForeignKey(Category1.id), nullable=False)
            category = relationship(Category1)
            parent_id = Column(Integer)
            parent = relationship('Node1', remote_side=id)
            __table_args__ = (
                ForeignKeyConstraint([parent_id, category_id],
                                     [id, category_id]),
            )
        class Category2(self.Base):
            id = Column(Integer, primary_key=True)
        class Node2(self.Base):
            id = Column(Integer, primary_key=True)
            category_id = Column(ForeignKey(Category2.id), nullable=False)
            category = relationship(Category2)
            parent_id = Column(Integer)
            parent = relationship('Node2', remote_side=id)
            __table_args__ = (
                ForeignKeyConstraint([parent_id, category_id],
                                     [id, category_id]),
            )
        self.create_all()
        # Data
        with self.db.begin():
            category1 = Category1(id=2)
            node11 = Node1(id=2, category=category1)
            node12 = Node1(id=4, category=category1, parent=node11)
            category2 = Category2(id=2)
            node21 = Node2(id=2, category=category2)
            self.db.add_all([node12, node21])
        self.assertEqual(node12.parent, node11)
        # Test
        with self.db.begin():
            node22 = replication.replicate(node12, Node2)
        self.assertIsNotNone(node22)
        self.assertEqual(node22.parent, node21)

Example 12

Project: alembic
Source File: test_autogen_diffs.py
View license
    def setUp(self):
        self.metadata = m = MetaData()
        t = Table(
            't', m,
            Column('id', Integer(), primary_key=True),
            Column('x', Integer())
        )
        self.ix = Index('ix1', t.c.id)
        fk = ForeignKeyConstraint(['t_id'], ['t.id'])
        q = Table(
            'q', m,
            Column('t_id', Integer()),
            fk
        )
        self.table = t
        self.fk = fk
        self.ck = CheckConstraint(t.c.x > 5)
        t.append_constraint(self.ck)
        self.uq = UniqueConstraint(q.c.t_id)
        self.pk = t.primary_key

Example 13

Project: alembic
Source File: test_autogen_fks.py
View license
    def test_remove_fk(self):
        m1 = MetaData()
        m2 = MetaData()

        Table('table', m1,
              Column('test', String(10), primary_key=True),
              mysql_engine='InnoDB')

        Table('user', m1,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', String(10), server_default="x"),
              Column('test2', String(10)),
              ForeignKeyConstraint(['test2'], ['table.test']),
              mysql_engine='InnoDB')

        Table('table', m2,
              Column('test', String(10), primary_key=True),
              mysql_engine='InnoDB')

        Table('user', m2,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', String(10), server_default="x"),
              Column('test2', String(10)),
              mysql_engine='InnoDB'
              )

        diffs = self._fixture(m1, m2)

        self._assert_fk_diff(
            diffs[0], "remove_fk",
            "user", ['test2'],
            'table', ['test'],
            conditional_name="servergenerated"
        )

Example 14

Project: alembic
Source File: test_autogen_fks.py
View license
    def test_add_fk(self):
        m1 = MetaData()
        m2 = MetaData()

        Table('table', m1,
              Column('id', Integer, primary_key=True),
              Column('test', String(10)),
              mysql_engine='InnoDB')

        Table('user', m1,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', String(10), server_default="x"),
              Column('test2', String(10)),
              mysql_engine='InnoDB')

        Table('table', m2,
              Column('id', Integer, primary_key=True),
              Column('test', String(10)),
              mysql_engine='InnoDB')

        Table('user', m2,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', String(10), server_default="x"),
              Column('test2', String(10)),
              ForeignKeyConstraint(['test2'], ['table.test']),
              mysql_engine='InnoDB')

        diffs = self._fixture(m1, m2)

        self._assert_fk_diff(
            diffs[0], "add_fk",
            "user", ["test2"],
            "table", ["test"]
        )

Example 15

Project: alembic
Source File: test_autogen_fks.py
View license
    def test_no_change(self):
        m1 = MetaData()
        m2 = MetaData()

        Table('table', m1,
              Column('id', Integer, primary_key=True),
              Column('test', String(10)),
              mysql_engine='InnoDB')

        Table('user', m1,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', String(10), server_default="x"),
              Column('test2', Integer),
              ForeignKeyConstraint(['test2'], ['table.id']),
              mysql_engine='InnoDB')

        Table('table', m2,
              Column('id', Integer, primary_key=True),
              Column('test', String(10)),
              mysql_engine='InnoDB')

        Table('user', m2,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', String(10), server_default="x"),
              Column('test2', Integer),
              ForeignKeyConstraint(['test2'], ['table.id']),
              mysql_engine='InnoDB')

        diffs = self._fixture(m1, m2)

        eq_(diffs, [])

Example 16

Project: alembic
Source File: test_autogen_fks.py
View license
    def test_no_change_composite_fk(self):
        m1 = MetaData()
        m2 = MetaData()

        Table('table', m1,
              Column('id_1', String(10), primary_key=True),
              Column('id_2', String(10), primary_key=True),
              mysql_engine='InnoDB')

        Table('user', m1,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', String(10), server_default="x"),
              Column('other_id_1', String(10)),
              Column('other_id_2', String(10)),
              ForeignKeyConstraint(['other_id_1', 'other_id_2'],
                                   ['table.id_1', 'table.id_2']),
              mysql_engine='InnoDB')

        Table('table', m2,
              Column('id_1', String(10), primary_key=True),
              Column('id_2', String(10), primary_key=True),
              mysql_engine='InnoDB'
              )

        Table('user', m2,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', String(10), server_default="x"),
              Column('other_id_1', String(10)),
              Column('other_id_2', String(10)),
              ForeignKeyConstraint(['other_id_1', 'other_id_2'],
                                   ['table.id_1', 'table.id_2']),
              mysql_engine='InnoDB')

        diffs = self._fixture(m1, m2)

        eq_(diffs, [])

Example 17

Project: alembic
Source File: test_autogen_fks.py
View license
    def test_add_composite_fk_with_name(self):
        m1 = MetaData()
        m2 = MetaData()

        Table('table', m1,
              Column('id_1', String(10), primary_key=True),
              Column('id_2', String(10), primary_key=True),
              mysql_engine='InnoDB')

        Table('user', m1,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', String(10), server_default="x"),
              Column('other_id_1', String(10)),
              Column('other_id_2', String(10)),
              mysql_engine='InnoDB')

        Table('table', m2,
              Column('id_1', String(10), primary_key=True),
              Column('id_2', String(10), primary_key=True),
              mysql_engine='InnoDB')

        Table('user', m2,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', String(10), server_default="x"),
              Column('other_id_1', String(10)),
              Column('other_id_2', String(10)),
              ForeignKeyConstraint(['other_id_1', 'other_id_2'],
                                   ['table.id_1', 'table.id_2'],
                                   name='fk_test_name'),
              mysql_engine='InnoDB')

        diffs = self._fixture(m1, m2)

        self._assert_fk_diff(
            diffs[0], "add_fk",
            "user", ['other_id_1', 'other_id_2'],
            'table', ['id_1', 'id_2'],
            name="fk_test_name"
        )

Example 18

Project: alembic
Source File: test_autogen_fks.py
View license
    def test_remove_composite_fk(self):
        m1 = MetaData()
        m2 = MetaData()

        Table('table', m1,
              Column('id_1', String(10), primary_key=True),
              Column('id_2', String(10), primary_key=True),
              mysql_engine='InnoDB')

        Table('user', m1,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', String(10), server_default="x"),
              Column('other_id_1', String(10)),
              Column('other_id_2', String(10)),
              ForeignKeyConstraint(['other_id_1', 'other_id_2'],
                                   ['table.id_1', 'table.id_2'],
                                   name='fk_test_name'),
              mysql_engine='InnoDB')

        Table('table', m2,
              Column('id_1', String(10), primary_key=True),
              Column('id_2', String(10), primary_key=True),
              mysql_engine='InnoDB')

        Table('user', m2,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', String(10), server_default="x"),
              Column('other_id_1', String(10)),
              Column('other_id_2', String(10)),
              mysql_engine='InnoDB')

        diffs = self._fixture(m1, m2)

        self._assert_fk_diff(
            diffs[0], "remove_fk",
            "user", ['other_id_1', 'other_id_2'],
            "table", ['id_1', 'id_2'],
            conditional_name="fk_test_name"
        )

Example 19

Project: alembic
Source File: test_autogen_fks.py
View license
    def test_add_fk_colkeys(self):
        m1 = MetaData()
        m2 = MetaData()

        Table('table', m1,
              Column('id_1', String(10), primary_key=True),
              Column('id_2', String(10), primary_key=True),
              mysql_engine='InnoDB')

        Table('user', m1,
              Column('id', Integer, primary_key=True),
              Column('other_id_1', String(10)),
              Column('other_id_2', String(10)),
              mysql_engine='InnoDB')

        Table('table', m2,
              Column('id_1', String(10), key='tid1', primary_key=True),
              Column('id_2', String(10), key='tid2', primary_key=True),
              mysql_engine='InnoDB')

        Table('user', m2,
              Column('id', Integer, primary_key=True),
              Column('other_id_1', String(10), key='oid1'),
              Column('other_id_2', String(10), key='oid2'),
              ForeignKeyConstraint(['oid1', 'oid2'],
                                   ['table.tid1', 'table.tid2'],
                                   name='fk_test_name'),
              mysql_engine='InnoDB')

        diffs = self._fixture(m1, m2)

        self._assert_fk_diff(
            diffs[0], "add_fk",
            "user", ['other_id_1', 'other_id_2'],
            'table', ['id_1', 'id_2'],
            name="fk_test_name"
        )

Example 20

Project: alembic
Source File: test_autogen_fks.py
View license
    def test_no_change_colkeys(self):
        m1 = MetaData()
        m2 = MetaData()

        Table('table', m1,
              Column('id_1', String(10), primary_key=True),
              Column('id_2', String(10), primary_key=True),
              mysql_engine='InnoDB')

        Table('user', m1,
              Column('id', Integer, primary_key=True),
              Column('other_id_1', String(10)),
              Column('other_id_2', String(10)),
              ForeignKeyConstraint(['other_id_1', 'other_id_2'],
                                   ['table.id_1', 'table.id_2']),
              mysql_engine='InnoDB')

        Table('table', m2,
              Column('id_1', String(10), key='tid1', primary_key=True),
              Column('id_2', String(10), key='tid2', primary_key=True),
              mysql_engine='InnoDB')

        Table('user', m2,
              Column('id', Integer, primary_key=True),
              Column('other_id_1', String(10), key='oid1'),
              Column('other_id_2', String(10), key='oid2'),
              ForeignKeyConstraint(['oid1', 'oid2'],
                                   ['table.tid1', 'table.tid2']),
              mysql_engine='InnoDB')

        diffs = self._fixture(m1, m2)

        eq_(diffs, [])

Example 21

Project: alembic
Source File: test_autogen_fks.py
View license
    def test_remove_connection_fk(self):
        m1 = MetaData()
        m2 = MetaData()

        ref = Table(
            'ref', m1, Column('id', Integer, primary_key=True),
            mysql_engine='InnoDB')
        t1 = Table(
            't', m1, Column('x', Integer), Column('y', Integer),
            mysql_engine='InnoDB')
        t1.append_constraint(
            ForeignKeyConstraint([t1.c.x], [ref.c.id], name="fk1")
        )
        t1.append_constraint(
            ForeignKeyConstraint([t1.c.y], [ref.c.id], name="fk2")
        )

        ref = Table(
            'ref', m2, Column('id', Integer, primary_key=True),
            mysql_engine='InnoDB')
        Table(
            't', m2, Column('x', Integer), Column('y', Integer),
            mysql_engine='InnoDB')

        def include_object(object_, name, type_, reflected, compare_to):
            return not (
                isinstance(object_, ForeignKeyConstraint) and
                type_ == 'foreign_key_constraint'
                and reflected and name == 'fk1')

        diffs = self._fixture(m1, m2, object_filters=include_object)

        self._assert_fk_diff(
            diffs[0], "remove_fk",
            't', ['y'], 'ref', ['id'],
            conditional_name='fk2'
        )
        eq_(len(diffs), 1)

Example 22

Project: alembic
Source File: test_autogen_fks.py
View license
    def test_add_metadata_fk(self):
        m1 = MetaData()
        m2 = MetaData()

        Table(
            'ref', m1,
            Column('id', Integer, primary_key=True), mysql_engine='InnoDB')
        Table(
            't', m1,
            Column('x', Integer), Column('y', Integer), mysql_engine='InnoDB')

        ref = Table(
            'ref', m2, Column('id', Integer, primary_key=True),
            mysql_engine='InnoDB')
        t2 = Table(
            't', m2, Column('x', Integer), Column('y', Integer),
            mysql_engine='InnoDB')
        t2.append_constraint(
            ForeignKeyConstraint([t2.c.x], [ref.c.id], name="fk1")
        )
        t2.append_constraint(
            ForeignKeyConstraint([t2.c.y], [ref.c.id], name="fk2")
        )

        def include_object(object_, name, type_, reflected, compare_to):
            return not (
                isinstance(object_, ForeignKeyConstraint) and
                type_ == 'foreign_key_constraint'
                and not reflected and name == 'fk1')

        diffs = self._fixture(m1, m2, object_filters=include_object)

        self._assert_fk_diff(
            diffs[0], "add_fk",
            't', ['y'], 'ref', ['id'],
            name='fk2'
        )
        eq_(len(diffs), 1)

Example 23

Project: alembic
Source File: test_autogen_fks.py
View license
    def test_change_fk(self):
        m1 = MetaData()
        m2 = MetaData()

        r1a = Table(
            'ref_a', m1,
            Column('a', Integer, primary_key=True),
            mysql_engine='InnoDB'
        )
        Table(
            'ref_b', m1,
            Column('a', Integer, primary_key=True),
            Column('b', Integer, primary_key=True),
            mysql_engine='InnoDB'
        )
        t1 = Table(
            't', m1, Column('x', Integer),
            Column('y', Integer), Column('z', Integer),
            mysql_engine='InnoDB')
        t1.append_constraint(
            ForeignKeyConstraint([t1.c.x], [r1a.c.a], name="fk1")
        )
        t1.append_constraint(
            ForeignKeyConstraint([t1.c.y], [r1a.c.a], name="fk2")
        )

        Table(
            'ref_a', m2,
            Column('a', Integer, primary_key=True),
            mysql_engine='InnoDB'
        )
        r2b = Table(
            'ref_b', m2,
            Column('a', Integer, primary_key=True),
            Column('b', Integer, primary_key=True),
            mysql_engine='InnoDB'
        )
        t2 = Table(
            't', m2, Column('x', Integer),
            Column('y', Integer), Column('z', Integer),
            mysql_engine='InnoDB')
        t2.append_constraint(
            ForeignKeyConstraint(
                [t2.c.x, t2.c.z], [r2b.c.a, r2b.c.b], name="fk1")
        )
        t2.append_constraint(
            ForeignKeyConstraint(
                [t2.c.y, t2.c.z], [r2b.c.a, r2b.c.b], name="fk2")
        )

        def include_object(object_, name, type_, reflected, compare_to):
            return not (
                isinstance(object_, ForeignKeyConstraint) and
                type_ == 'foreign_key_constraint'
                and name == 'fk1'
            )

        diffs = self._fixture(m1, m2, object_filters=include_object)

        self._assert_fk_diff(
            diffs[0], "remove_fk",
            't', ['y'], 'ref_a', ['a'],
            name='fk2'
        )
        self._assert_fk_diff(
            diffs[1], "add_fk",
            't', ['y', 'z'], 'ref_b', ['a', 'b'],
            name='fk2'
        )
        eq_(len(diffs), 2)

Example 24

Project: alembic
Source File: test_autogen_fks.py
View license
    def _fk_opts_fixture(self, old_opts, new_opts):
        m1 = MetaData()
        m2 = MetaData()

        Table('table', m1,
              Column('id', Integer, primary_key=True),
              Column('test', String(10)),
              mysql_engine='InnoDB')

        Table('user', m1,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('tid', Integer),
              ForeignKeyConstraint(['tid'], ['table.id'], **old_opts),
              mysql_engine='InnoDB')

        Table('table', m2,
              Column('id', Integer, primary_key=True),
              Column('test', String(10)),
              mysql_engine='InnoDB')

        Table('user', m2,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('tid', Integer),
              ForeignKeyConstraint(['tid'], ['table.id'], **new_opts),
              mysql_engine='InnoDB')

        return self._fixture(m1, m2)

Example 25

Project: alembic
Source File: test_autogen_indexes.py
View license
    def test_nothing_changed_two(self):
        m1 = MetaData()
        m2 = MetaData()

        Table('nothing_changed', m1,
              Column('id1', Integer, primary_key=True),
              Column('id2', Integer, primary_key=True),
              Column('x', String(20), unique=True),
              mysql_engine='InnoDB'
              )
        Table('nothing_changed_related', m1,
              Column('id1', Integer),
              Column('id2', Integer),
              ForeignKeyConstraint(
                  ['id1', 'id2'],
                  ['nothing_changed.id1', 'nothing_changed.id2']),
              mysql_engine='InnoDB'
              )

        Table('nothing_changed', m2,
              Column('id1', Integer, primary_key=True),
              Column('id2', Integer, primary_key=True),
              Column('x', String(20), unique=True),
              mysql_engine='InnoDB'
              )
        Table('nothing_changed_related', m2,
              Column('id1', Integer),
              Column('id2', Integer),
              ForeignKeyConstraint(
                  ['id1', 'id2'],
                  ['nothing_changed.id1', 'nothing_changed.id2']),
              mysql_engine='InnoDB'
              )

        diffs = self._fixture(m1, m2)
        eq_(diffs, [])

Example 26

Project: alembic
Source File: test_autogen_indexes.py
View license
    def test_nothing_changed_implicit_composite_fk_index_named(self):
        m1 = MetaData()
        m2 = MetaData()

        Table("nothing_changed", m1,
              Column('id', Integer, primary_key=True),
              Column('other_id_1', Integer),
              Column('other_id_2', Integer),
              Column('foo', Integer),
              ForeignKeyConstraint(
                  ['other_id_1', 'other_id_2'], ['nc2.id1', 'nc2.id2'],
                  name='fk_my_table_other_table'
              ),
              mysql_engine='InnoDB')
        Table('nc2', m1,
              Column('id1', Integer, primary_key=True),
              Column('id2', Integer, primary_key=True),
              mysql_engine='InnoDB')

        Table("nothing_changed", m2,
              Column('id', Integer, primary_key=True),
              Column('other_id_1', Integer),
              Column('other_id_2', Integer),
              Column('foo', Integer),
              ForeignKeyConstraint(
                  ['other_id_1', 'other_id_2'], ['nc2.id1', 'nc2.id2'],
                  name='fk_my_table_other_table'
              ),
              mysql_engine='InnoDB')
        Table('nc2', m2,
              Column('id1', Integer, primary_key=True),
              Column('id2', Integer, primary_key=True),
              mysql_engine='InnoDB')
        diffs = self._fixture(m1, m2)
        eq_(diffs, [])

Example 27

Project: alembic
Source File: test_batch.py
View license
    def _multi_fk_fixture(self, table_args=(), table_kwargs={}, schema=None):
        m = MetaData()
        if schema:
            schemaarg = "%s." % schema
        else:
            schemaarg = ""

        t = Table(
            'tname', m,
            Column('id', Integer, primary_key=True),
            Column('email', String()),
            Column('user_id_1', Integer, ForeignKey('%suser.id' % schemaarg)),
            Column('user_id_2', Integer, ForeignKey('%suser.id' % schemaarg)),
            Column('user_id_3', Integer),
            Column('user_id_version', Integer),
            ForeignKeyConstraint(
                ['user_id_3', 'user_id_version'],
                ['%suser.id' % schemaarg, '%suser.id_version' % schemaarg]),
            schema=schema
        )
        return ApplyBatchImpl(t, table_args, table_kwargs, False)

Example 28

Project: sqlalchemy
Source File: history_meta.py
View license
def _history_mapper(local_mapper):
    cls = local_mapper.class_

    # set the "active_history" flag
    # on on column-mapped attributes so that the old version
    # of the info is always loaded (currently sets it on all attributes)
    for prop in local_mapper.iterate_properties:
        getattr(local_mapper.class_, prop.key).impl.active_history = True

    super_mapper = local_mapper.inherits
    super_history_mapper = getattr(cls, '__history_mapper__', None)

    polymorphic_on = None
    super_fks = []

    def _col_copy(col):
        orig = col
        col = col.copy()
        orig.info['history_copy'] = col
        col.unique = False
        col.default = col.server_default = None
        return col

    properties = util.OrderedDict()
    if not super_mapper or \
            local_mapper.local_table is not super_mapper.local_table:
        cols = []
        version_meta = {"version_meta": True}  # add column.info to identify
                                               # columns specific to versioning

        for column in local_mapper.local_table.c:
            if _is_versioning_col(column):
                continue

            col = _col_copy(column)

            if super_mapper and \
                    col_references_table(column, super_mapper.local_table):
                super_fks.append(
                    (
                        col.key,
                        list(super_history_mapper.local_table.primary_key)[0]
                    )
                )

            cols.append(col)

            if column is local_mapper.polymorphic_on:
                polymorphic_on = col

            orig_prop = local_mapper.get_property_by_column(column)
            # carry over column re-mappings
            if len(orig_prop.columns) > 1 or \
                    orig_prop.columns[0].key != orig_prop.key:
                properties[orig_prop.key] = tuple(
                    col.info['history_copy'] for col in orig_prop.columns)

        if super_mapper:
            super_fks.append(
                (
                    'version', super_history_mapper.local_table.c.version
                )
            )

        # "version" stores the integer version id.  This column is
        # required.
        cols.append(
            Column(
                'version', Integer, primary_key=True,
                autoincrement=False, info=version_meta))

        # "changed" column stores the UTC timestamp of when the
        # history row was created.
        # This column is optional and can be omitted.
        cols.append(Column(
            'changed', DateTime,
            default=datetime.datetime.utcnow,
            info=version_meta))

        if super_fks:
            cols.append(ForeignKeyConstraint(*zip(*super_fks)))

        table = Table(
            local_mapper.local_table.name + '_history',
            local_mapper.local_table.metadata,
            *cols,
            schema=local_mapper.local_table.schema
        )
    else:
        # single table inheritance.  take any additional columns that may have
        # been added and add them to the history table.
        for column in local_mapper.local_table.c:
            if column.key not in super_history_mapper.local_table.c:
                col = _col_copy(column)
                super_history_mapper.local_table.append_column(col)
        table = None

    if super_history_mapper:
        bases = (super_history_mapper.class_,)

        if table is not None:
            properties['changed'] = (
                (table.c.changed, ) +
                tuple(super_history_mapper.attrs.changed.columns)
            )

    else:
        bases = local_mapper.base_mapper.class_.__bases__
    versioned_cls = type.__new__(type, "%sHistory" % cls.__name__, bases, {})

    m = mapper(
        versioned_cls,
        table,
        inherits=super_history_mapper,
        polymorphic_on=polymorphic_on,
        polymorphic_identity=local_mapper.polymorphic_identity,
        properties=properties
    )
    cls.__history_mapper__ = m

    if not super_history_mapper:
        local_mapper.local_table.append_column(
            Column('version', Integer, default=1, nullable=False)
        )
        local_mapper.add_property(
            "version", local_mapper.local_table.c.version)

Example 29

Project: sqlalchemy
Source File: util.py
View license
def drop_all_tables(engine, inspector, schema=None, include_names=None):
    from sqlalchemy import Column, Table, Integer, MetaData, \
        ForeignKeyConstraint
    from sqlalchemy.schema import DropTable, DropConstraint

    if include_names is not None:
        include_names = set(include_names)

    with engine.connect() as conn:
        for tname, fkcs in reversed(
                inspector.get_sorted_table_and_fkc_names(schema=schema)):
            if tname:
                if include_names is not None and tname not in include_names:
                    continue
                conn.execute(DropTable(
                    Table(tname, MetaData(), schema=schema)
                ))
            elif fkcs:
                if not engine.dialect.supports_alter:
                    continue
                for tname, fkc in fkcs:
                    if include_names is not None and \
                            tname not in include_names:
                        continue
                    tb = Table(
                        tname, MetaData(),
                        Column('x', Integer),
                        Column('y', Integer),
                        schema=schema
                    )
                    conn.execute(DropConstraint(
                        ForeignKeyConstraint(
                            [tb.c.x], [tb.c.y], name=fkc)
                    ))

Example 30

Project: sqlalchemy
Source File: test_lazy_relations.py
View license
    @classmethod
    def define_tables(cls, metadata):
        Table(
            'a', metadata,
            Column("id1", Integer, primary_key=True),
            Column("id2", Integer, primary_key=True),
        )

        Table(
            "b_sameorder", metadata,
            Column("id", Integer, primary_key=True),
            Column('a_id1', Integer),
            Column('a_id2', Integer),
            ForeignKeyConstraint(['a_id1', 'a_id2'], ['a.id1', 'a.id2'])
        )

        Table(
            "b_differentorder", metadata,
            Column("id", Integer, primary_key=True),
            Column('a_id1', Integer),
            Column('a_id2', Integer),
            ForeignKeyConstraint(['a_id1', 'a_id2'], ['a.id1', 'a.id2'])
        )

Example 31

Project: sqlalchemy
Source File: test_rel_fn.py
View license
    @classmethod
    def setup_class(cls):
        m = MetaData()
        cls.left = Table('lft', m,
            Column('id', Integer, primary_key=True),
            Column('x', Integer),
            Column('y', Integer),
        )
        cls.right = Table('rgt', m,
            Column('id', Integer, primary_key=True),
            Column('lid', Integer, ForeignKey('lft.id')),
            Column('x', Integer),
            Column('y', Integer),
        )
        cls.right_multi_fk = Table('rgt_multi_fk', m,
            Column('id', Integer, primary_key=True),
            Column('lid1', Integer, ForeignKey('lft.id')),
            Column('lid2', Integer, ForeignKey('lft.id')),
        )

        cls.selfref = Table('selfref', m,
            Column('id', Integer, primary_key=True),
            Column('sid', Integer, ForeignKey('selfref.id'))
        )
        cls.composite_selfref = Table('composite_selfref', m,
            Column('id', Integer, primary_key=True),
            Column('group_id', Integer, primary_key=True),
            Column('parent_id', Integer),
            ForeignKeyConstraint(
                ['parent_id', 'group_id'],
                ['composite_selfref.id', 'composite_selfref.group_id']
            )
        )
        cls.m2mleft = Table('m2mlft', m,
            Column('id', Integer, primary_key=True),
        )
        cls.m2mright = Table('m2mrgt', m,
            Column('id', Integer, primary_key=True),
        )
        cls.m2msecondary = Table('m2msecondary', m,
            Column('lid', Integer, ForeignKey('m2mlft.id'), primary_key=True),
            Column('rid', Integer, ForeignKey('m2mrgt.id'), primary_key=True),
        )
        cls.m2msecondary_no_fks = Table('m2msecondary_no_fks', m,
            Column('lid', Integer, primary_key=True),
            Column('rid', Integer, primary_key=True),
        )
        cls.m2msecondary_ambig_fks = Table('m2msecondary_ambig_fks', m,
            Column('lid1', Integer, ForeignKey('m2mlft.id'), primary_key=True),
            Column('rid1', Integer, ForeignKey('m2mrgt.id'), primary_key=True),
            Column('lid2', Integer, ForeignKey('m2mlft.id'), primary_key=True),
            Column('rid2', Integer, ForeignKey('m2mrgt.id'), primary_key=True),
        )
        cls.base_w_sub_rel = Table('base_w_sub_rel', m,
            Column('id', Integer, primary_key=True),
            Column('sub_id', Integer, ForeignKey('rel_sub.id'))
        )
        cls.rel_sub = Table('rel_sub', m,
            Column('id', Integer, ForeignKey('base_w_sub_rel.id'),
                                primary_key=True)
        )
        cls.base = Table('base', m,
            Column('id', Integer, primary_key=True),
            Column('flag', Boolean)
        )
        cls.sub = Table('sub', m,
            Column('id', Integer, ForeignKey('base.id'),
                                primary_key=True),
        )
        cls.sub_w_base_rel = Table('sub_w_base_rel', m,
            Column('id', Integer, ForeignKey('base.id'),
                                primary_key=True),
            Column('base_id', Integer, ForeignKey('base.id'))
        )
        cls.sub_w_sub_rel = Table('sub_w_sub_rel', m,
            Column('id', Integer, ForeignKey('base.id'),
                                primary_key=True),
            Column('sub_id', Integer, ForeignKey('sub.id'))
        )
        cls.right_w_base_rel = Table('right_w_base_rel', m,
            Column('id', Integer, primary_key=True),
            Column('base_id', Integer, ForeignKey('base.id'))
        )

        cls.three_tab_a = Table('three_tab_a', m,
            Column('id', Integer, primary_key=True),
        )
        cls.three_tab_b = Table('three_tab_b', m,
            Column('id', Integer, primary_key=True),
            Column('aid', Integer, ForeignKey('three_tab_a.id'))
        )
        cls.three_tab_c = Table('three_tab_c', m,
            Column('id', Integer, primary_key=True),
            Column('aid', Integer, ForeignKey('three_tab_a.id')),
            Column('bid', Integer, ForeignKey('three_tab_b.id'))
        )

        cls.composite_target = Table('composite_target', m,
            Column('uid', Integer, primary_key=True),
            Column('oid', Integer, primary_key=True),
        )

        cls.composite_multi_ref = Table('composite_multi_ref', m,
            Column('uid1', Integer),
            Column('uid2', Integer),
            Column('oid', Integer),
            ForeignKeyConstraint(("uid1", "oid"),
                        ("composite_target.uid", "composite_target.oid")),
            ForeignKeyConstraint(("uid2", "oid"),
                        ("composite_target.uid", "composite_target.oid")),
            )

        cls.purely_single_col = Table('purely_single_col', m,
            Column('path', String)
            )

Example 32

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.provide_metadata
    def test_pk_fk_constraint_create(self):
        metadata = self.metadata

        Table('employees', metadata,
              Column('id', Integer),
              Column('soc', String(40)),
              Column('name', String(30)),
              PrimaryKeyConstraint('id', 'soc')
              )
        Table('elements', metadata,
              Column('id', Integer),
              Column('stuff', String(30)),
              Column('emp_id', Integer),
              Column('emp_soc', String(40)),
              PrimaryKeyConstraint('id', name='elements_primkey'),
              ForeignKeyConstraint(['emp_id', 'emp_soc'],
                                   ['employees.id', 'employees.soc'])
              )
        self.assert_sql_execution(
            testing.db,
            lambda: metadata.create_all(checkfirst=False),
            CompiledSQL('CREATE TABLE employees ('
                        'id INTEGER NOT NULL, '
                        'soc VARCHAR(40) NOT NULL, '
                        'name VARCHAR(30), '
                        'PRIMARY KEY (id, soc)'
                        ')'
                        ),
            CompiledSQL('CREATE TABLE elements ('
                        'id INTEGER NOT NULL, '
                        'stuff VARCHAR(30), '
                        'emp_id INTEGER, '
                        'emp_soc VARCHAR(40), '
                        'CONSTRAINT elements_primkey PRIMARY KEY (id), '
                        'FOREIGN KEY(emp_id, emp_soc) '
                        'REFERENCES employees (id, soc)'
                        ')'
                        )
        )

Example 33

Project: sqlalchemy
Source File: test_constraints.py
View license
    @testing.force_drop_names('a', 'b')
    def test_fk_cant_drop_cycled_unnamed(self):
        metadata = MetaData()

        Table("a", metadata,
              Column('id', Integer, primary_key=True),
              Column('bid', Integer),
              ForeignKeyConstraint(["bid"], ["b.id"])
              )
        Table(
            "b", metadata,
            Column('id', Integer, primary_key=True),
            Column("aid", Integer),
            ForeignKeyConstraint(["aid"], ["a.id"]))
        metadata.create_all(testing.db)
        if testing.db.dialect.supports_alter:
            assert_raises_message(
                exc.CircularDependencyError,
                "Can't sort tables for DROP; an unresolvable foreign key "
                "dependency exists between tables: a, b.  Please ensure "
                "that the ForeignKey and ForeignKeyConstraint objects "
                "involved in the cycle have names so that they can be "
                "dropped using DROP CONSTRAINT.",
                metadata.drop_all, testing.db
            )
        else:
            with expect_warnings(
                    "Can't sort tables for DROP; an unresolvable "
                    "foreign key dependency "):
                with self.sql_execution_asserter() as asserter:
                    metadata.drop_all(testing.db, checkfirst=False)

            asserter.assert_(
                AllOf(
                    CompiledSQL("DROP TABLE a"),
                    CompiledSQL("DROP TABLE b")
                )
            )

Example 34

Project: sqlalchemy
Source File: test_constraints.py
View license
    def test_deferrable_table_fk(self):
        factory = lambda **kw: ForeignKeyConstraint(['b'], ['tbl.a'], **kw)
        self._test_deferrable(factory)

Example 35

Project: sqlalchemy
Source File: test_constraints.py
View license
    def test_create_table_omit_fks(self):
        fkcs = [
            ForeignKeyConstraint(['a'], ['remote.id'], name='foo'),
            ForeignKeyConstraint(['b'], ['remote.id'], name='bar'),
            ForeignKeyConstraint(['c'], ['remote.id'], name='bat'),
        ]
        m = MetaData()
        t = Table(
            't', m,
            Column('a', Integer),
            Column('b', Integer),
            Column('c', Integer),
            *fkcs
        )
        Table('remote', m, Column('id', Integer, primary_key=True))

        self.assert_compile(
            schema.CreateTable(t, include_foreign_key_constraints=[]),
            "CREATE TABLE t (a INTEGER, b INTEGER, c INTEGER)"
        )
        self.assert_compile(
            schema.CreateTable(t, include_foreign_key_constraints=fkcs[0:2]),
            "CREATE TABLE t (a INTEGER, b INTEGER, c INTEGER, "
            "CONSTRAINT foo FOREIGN KEY(a) REFERENCES remote (id), "
            "CONSTRAINT bar FOREIGN KEY(b) REFERENCES remote (id))"
        )

Example 36

Project: jsontableschema-sql-py
Source File: mappers.py
View license
def descriptor_to_columns_and_constraints(prefix, bucket, descriptor):
    """Convert descriptor to SQLAlchemy columns and constraints.
    """

    # Init
    columns = []
    constraints = []
    tablename = bucket_to_tablename(prefix, bucket)

    # Mapping
    mapping = {
        'string': Text,
        'number': Float,
        'integer': Integer,
        'boolean': Boolean,
        'object': JSONB,
        'array': JSONB,
        'date': Date,
        'time': Time,
        'datetime': DateTime,
        'geojson': JSONB,
    }

    # Fields
    for field in descriptor['fields']:
        try:
            column_type = mapping[field['type']]
        except KeyError:
            message = 'Type "%s" of field "%s" is not supported'
            message = message % (field['type'], field['name'])
            raise TypeError(message)
        nullable = not field.get('constraints', {}).get('required', False)
        column = Column(field['name'], column_type, nullable=nullable)
        columns.append(column)

    # Primary key
    pk = descriptor.get('primaryKey', None)
    if pk is not None:
        if isinstance(pk, six.string_types):
            pk = [pk]
        constraint = PrimaryKeyConstraint(*pk)
        constraints.append(constraint)

    # Foreign keys
    fks = descriptor.get('foreignKeys', [])
    for fk in fks:
        fields = fk['fields']
        resource = fk['reference']['resource']
        foreign_fields = fk['reference']['fields']
        if isinstance(fields, six.string_types):
            fields = [fields]
        if resource != 'self':
            tablename = bucket_to_tablename(prefix, resource)
        if isinstance(foreign_fields, six.string_types):
            foreign_fields = [foreign_fields]
        composer = lambda field: '.'.join([tablename, field])
        foreign_fields = list(map(composer, foreign_fields))
        constraint = ForeignKeyConstraint(fields, foreign_fields)
        constraints.append(constraint)

    return (columns, constraints)

Example 37

Project: indico
Source File: management.py
View license
def delete_all_tables(db):
    """Drops all tables in the database"""
    conn = db.engine.connect()
    transaction = conn.begin()
    inspector = Inspector.from_engine(db.engine)
    metadata = MetaData()

    all_schema_tables = get_all_tables(db)
    tables = []
    all_fkeys = []
    for schema, schema_tables in all_schema_tables.iteritems():
        for table_name in schema_tables:
            fkeys = [ForeignKeyConstraint((), (), name=fk['name'])
                     for fk in inspector.get_foreign_keys(table_name, schema=schema)
                     if fk['name']]
            tables.append(Table(table_name, metadata, *fkeys, schema=schema))
            all_fkeys.extend(fkeys)

    for fkey in all_fkeys:
        conn.execute(DropConstraint(fkey))
    for table in tables:
        conn.execute(DropTable(table))
    for schema in all_schema_tables:
        if schema != 'public':
            row = conn.execute("""
                SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname || '(' || oidvectortypes(proargtypes) || ')'
                FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
                WHERE ns.nspname = '{}'  order by proname;
            """.format(schema))
            for stmt, in row:
                conn.execute(stmt)
            conn.execute(DropSchema(schema))
    transaction.commit()

Example 38

Project: DataGristle
Source File: metadata.py
View license
    def table_create(self):
        """ Handles creation of collection table.
        """

        self.collection = Table('collection',
              self.metadata,
              Column('collection_id'   , Integer       , nullable=False   ,
                     primary_key=True),
              Column('schema_id'       , Integer       , nullable=False  ),
              Column('collection_name' , String(40)    , nullable=False  ),
              Column('collection_desc' , String(256)   , nullable=False  ),
              UniqueConstraint('schema_id', 'collection_name',
                               name='collection_uk1'),
              ForeignKeyConstraint(columns=['schema_id'],
                                   refcolumns=['schema.schema_id'],
                                   name='collection_fk1',
                                   ondelete='CASCADE'),
              extend_existing=True)
        self._table      = self.collection
        self._table_name = 'collection'
        return self._table

Example 39

Project: DataGristle
Source File: metadata.py
View license
    def table_create(self):
        """ Handles creation of collection table.
        """

        self.collection = Table('collection',
              self.metadata,
              Column('collection_id'   , Integer       , nullable=False   ,
                     primary_key=True),
              Column('schema_id'       , Integer       , nullable=False  ),
              Column('collection_name' , String(40)    , nullable=False  ),
              Column('collection_desc' , String(256)   , nullable=False  ),
              UniqueConstraint('schema_id', 'collection_name',
                               name='collection_uk1'),
              ForeignKeyConstraint(columns=['schema_id'],
                                   refcolumns=['schema.schema_id'],
                                   name='collection_fk1',
                                   ondelete='CASCADE'),
              extend_existing=True)
        self._table      = self.collection
        self._table_name = 'collection'
        return self._table

Example 40

Project: DataGristle
Source File: metadata.py
View license
    def table_create(self):
        """ Creates the 'field' table.
        """

        self.field = Table('field',
            self.metadata,
            Column('field_id'        , Integer     , nullable=False   ,
                   primary_key=True),
            Column('collection_id'   , Integer     , nullable=False  ),
            Column('field_name'      , String(40)  , nullable=False  ),
            Column('field_desc'      , String(256) , nullable=True   ),
            Column('field_order'     , Integer     , nullable=True   ),
            Column('field_type'      , String(10)  , nullable=True   ),
            Column('field_len'       , Integer     , nullable=True   ),
            Column('element_name'    , String(60)  , nullable=True   ),
            UniqueConstraint('collection_id',
                   'field_name',
                   name='field_uk1'),
            ForeignKeyConstraint(columns=['collection_id'],
                   refcolumns=['collection.collection_id'],
                   name='field_fk1',
                   ondelete='CASCADE'),
            ForeignKeyConstraint(columns=['element_name'],
                   refcolumns=['element.element_name'],
                   name='field_fk2',
                   ondelete='RESTRICT'),
            CheckConstraint('field_len > 0',
                   name='field_len_ck1'),
            CheckConstraint("field_type in ('string','int','date','time','timestamp','float')",
                   name='field_len_ck2'),
            CheckConstraint("( (element_name IS NULL AND field_type IS NOT NULL) \
                   OR (element_name IS NOT NULL AND field_type IS NULL) ) ",
                   name='field_ck3') ,
            CheckConstraint("  (field_type IS NULL AND field_len IS NULL)   \
                   OR (field_type  = 'string' AND field_len IS NOT NULL)    \
                   OR (field_type <> 'string' AND field_len IS NULL)  ",
                   name='field_ck4') ,
            extend_existing=True )
        self._table      = self.field
        self._table_name = 'field'
        self.instance    = None # assigned in InstanceTools
        return self._table

Example 41

Project: DataGristle
Source File: metadata.py
View license
    def table_create(self):
        """ Creates the 'field' table.
        """

        self.field = Table('field',
            self.metadata,
            Column('field_id'        , Integer     , nullable=False   ,
                   primary_key=True),
            Column('collection_id'   , Integer     , nullable=False  ),
            Column('field_name'      , String(40)  , nullable=False  ),
            Column('field_desc'      , String(256) , nullable=True   ),
            Column('field_order'     , Integer     , nullable=True   ),
            Column('field_type'      , String(10)  , nullable=True   ),
            Column('field_len'       , Integer     , nullable=True   ),
            Column('element_name'    , String(60)  , nullable=True   ),
            UniqueConstraint('collection_id',
                   'field_name',
                   name='field_uk1'),
            ForeignKeyConstraint(columns=['collection_id'],
                   refcolumns=['collection.collection_id'],
                   name='field_fk1',
                   ondelete='CASCADE'),
            ForeignKeyConstraint(columns=['element_name'],
                   refcolumns=['element.element_name'],
                   name='field_fk2',
                   ondelete='RESTRICT'),
            CheckConstraint('field_len > 0',
                   name='field_len_ck1'),
            CheckConstraint("field_type in ('string','int','date','time','timestamp','float')",
                   name='field_len_ck2'),
            CheckConstraint("( (element_name IS NULL AND field_type IS NOT NULL) \
                   OR (element_name IS NOT NULL AND field_type IS NULL) ) ",
                   name='field_ck3') ,
            CheckConstraint("  (field_type IS NULL AND field_len IS NULL)   \
                   OR (field_type  = 'string' AND field_len IS NOT NULL)    \
                   OR (field_type <> 'string' AND field_len IS NULL)  ",
                   name='field_ck4') ,
            extend_existing=True )
        self._table      = self.field
        self._table_name = 'field'
        self.instance    = None # assigned in InstanceTools
        return self._table

Example 42

Project: DataGristle
Source File: metadata.py
View license
    def table_create(self):
        """ Creates the 'field_value' table.
        """

        self.field_value = Table('field_value',
            self.metadata,
            Column('field_id'        , Integer     , nullable=False  ),
            Column('fv_value'        , String(256) , nullable=False  ),
            Column('fv_desc'         , String(2048),                 ),
            Column('fv_issues'       , String(2048),                 ),
            UniqueConstraint('field_id',
                   'fv_value',
                   name='field_value_uk1'),
            ForeignKeyConstraint(columns=['field_id'],
                   refcolumns=['field.field_id'],
                   name='field_value_fk1',
                   ondelete='CASCADE'),
            extend_existing=True )
        self._table      = self.field_value
        self._table_name = 'field_value'
        return self._table

Example 43

Project: DataGristle
Source File: metadata.py
View license
    def table_create(self):
        """ Creates the 'field_value' table.
        """

        self.field_value = Table('field_value',
            self.metadata,
            Column('field_id'        , Integer     , nullable=False  ),
            Column('fv_value'        , String(256) , nullable=False  ),
            Column('fv_desc'         , String(2048),                 ),
            Column('fv_issues'       , String(2048),                 ),
            UniqueConstraint('field_id',
                   'fv_value',
                   name='field_value_uk1'),
            ForeignKeyConstraint(columns=['field_id'],
                   refcolumns=['field.field_id'],
                   name='field_value_fk1',
                   ondelete='CASCADE'),
            extend_existing=True )
        self._table      = self.field_value
        self._table_name = 'field_value'
        return self._table

Example 44

Project: DataGristle
Source File: metadata.py
View license
    def table_create(self):
        """ Creates the 'instance' table.
        """
        self.instance = Table('instance' ,
             self.metadata           ,
             Column('instance_id'    ,
                    Integer          ,
                    nullable=False   ,
                    primary_key=True),
             Column('schema_id'      ,
                    Integer          ,
                    nullable=False  ),
             Column('instance_name'  ,
                    String(255)      ,
                    nullable=False  ),
             UniqueConstraint('schema_id','instance_name', name='instance_uk1'),
             ForeignKeyConstraint(columns=['schema_id'],
                                  refcolumns=['schema.schema_id'],
                                  name='instance_fk1',
                                  ondelete='CASCADE'),
             extend_existing=True    )

        self._table      = self.instance
        self._table_name = 'instance'
        return self._table

Example 45

Project: DataGristle
Source File: metadata.py
View license
    def table_create(self):
        """ Creates the 'instance' table.
        """
        self.instance = Table('instance' ,
             self.metadata           ,
             Column('instance_id'    ,
                    Integer          ,
                    nullable=False   ,
                    primary_key=True),
             Column('schema_id'      ,
                    Integer          ,
                    nullable=False  ),
             Column('instance_name'  ,
                    String(255)      ,
                    nullable=False  ),
             UniqueConstraint('schema_id','instance_name', name='instance_uk1'),
             ForeignKeyConstraint(columns=['schema_id'],
                                  refcolumns=['schema.schema_id'],
                                  name='instance_fk1',
                                  ondelete='CASCADE'),
             extend_existing=True    )

        self._table      = self.instance
        self._table_name = 'instance'
        return self._table

Example 46

Project: DataGristle
Source File: metadata.py
View license
    def table_create(self):
        """ Creates 'analysis_profile' table.
        """
        self.analysis_profile = Table('analysis_profile' ,
             self.metadata           ,
             Column('analysis_profile_id'    ,
                    Integer          ,
                    nullable=False   ,
                    primary_key=True),
             Column('instance_id'    ,
                    Integer          ,
                    nullable=False  ),
             Column('collection_id'  ,
                    Integer          ,
                    nullable=False  ),
             Column('analysis_profile_name'  ,
                    String(255)      ,
                    nullable=False  ),
             UniqueConstraint('instance_id','collection_id',
                              'analysis_profile_name',
                              name='analysis_profile_k1'),
             ForeignKeyConstraint(columns=['instance_id'],
                                  refcolumns=['instance.instance_id'],
                                  name='analysis_profile_fk1',
                                  ondelete='CASCADE'),
             ForeignKeyConstraint(columns=['collection_id'],
                                  refcolumns=['collection.collection_id'],
                                  name='analysis_profile_fk1',
                                  ondelete='CASCADE'),
             extend_existing=True    )

        self._table      = self.analysis_profile
        self._table_name = 'analysis_profile'
        return self._table

Example 47

Project: DataGristle
Source File: metadata.py
View license
    def table_create(self):
        """ Creates 'analysis_profile' table.
        """
        self.analysis_profile = Table('analysis_profile' ,
             self.metadata           ,
             Column('analysis_profile_id'    ,
                    Integer          ,
                    nullable=False   ,
                    primary_key=True),
             Column('instance_id'    ,
                    Integer          ,
                    nullable=False  ),
             Column('collection_id'  ,
                    Integer          ,
                    nullable=False  ),
             Column('analysis_profile_name'  ,
                    String(255)      ,
                    nullable=False  ),
             UniqueConstraint('instance_id','collection_id',
                              'analysis_profile_name',
                              name='analysis_profile_k1'),
             ForeignKeyConstraint(columns=['instance_id'],
                                  refcolumns=['instance.instance_id'],
                                  name='analysis_profile_fk1',
                                  ondelete='CASCADE'),
             ForeignKeyConstraint(columns=['collection_id'],
                                  refcolumns=['collection.collection_id'],
                                  name='analysis_profile_fk1',
                                  ondelete='CASCADE'),
             extend_existing=True    )

        self._table      = self.analysis_profile
        self._table_name = 'analysis_profile'
        return self._table

Example 48

Project: DataGristle
Source File: metadata.py
View license
    def table_create(self):
        """ Creates the 'analysis' table.
        """
        # issue: default relies on python - not database, so could be bypassed
        # by raw sql inserts.
        #            onupdate=datetime.datetime.now()   ,
        #            server_default=func.current_timestamp()   ,
        #            server_default=u'CURRENT_TIMESTAMP',
        #            default='CURRENT_TIMESTAMP',
        self.analysis = Table('analysis' ,
             self.metadata           ,
             Column('analysis_id'    ,
                    Integer          ,
                    nullable=False   ,
                    primary_key=True),
             Column('instance_id'    ,
                    Integer          ,
                    nullable=False  ),
             Column('analysis_profile_id'  ,
                    Integer          ,
                    nullable=True   ),
             Column('analysis_timestamp'  ,
                    DATETIME         ,
                    default=datetime.datetime.now,
                    onupdate=datetime.datetime.now,
                    nullable=False  ),
             Column('analysis_tool'  ,
                    String(20)       ,
                    nullable=False  ),
             UniqueConstraint('instance_id','analysis_timestamp',
                    name='analysis_uk1'),
             ForeignKeyConstraint(columns=['instance_id'],
                    refcolumns=['instance.instance_id'],
                    name='analysis_fk1',
                    ondelete='CASCADE'),
             ForeignKeyConstraint(columns=['analysis_profile_id'],
                    refcolumns=['analysis_profile.analysis_profile_id'],
                    name='analysis_fk2',
                    ondelete='CASCADE'),
             extend_existing=True    )

        self._table      = self.analysis
        self._table_name = 'analysis'
        self.insert_defaulted.append('analysis_timestamp')
        self.update_defaulted.append('analysis_timestamp')
        return self._table

Example 49

Project: DataGristle
Source File: metadata.py
View license
    def table_create(self):
        """ Creates the 'analysis' table.
        """
        # issue: default relies on python - not database, so could be bypassed
        # by raw sql inserts.
        #            onupdate=datetime.datetime.now()   ,
        #            server_default=func.current_timestamp()   ,
        #            server_default=u'CURRENT_TIMESTAMP',
        #            default='CURRENT_TIMESTAMP',
        self.analysis = Table('analysis' ,
             self.metadata           ,
             Column('analysis_id'    ,
                    Integer          ,
                    nullable=False   ,
                    primary_key=True),
             Column('instance_id'    ,
                    Integer          ,
                    nullable=False  ),
             Column('analysis_profile_id'  ,
                    Integer          ,
                    nullable=True   ),
             Column('analysis_timestamp'  ,
                    DATETIME         ,
                    default=datetime.datetime.now,
                    onupdate=datetime.datetime.now,
                    nullable=False  ),
             Column('analysis_tool'  ,
                    String(20)       ,
                    nullable=False  ),
             UniqueConstraint('instance_id','analysis_timestamp',
                    name='analysis_uk1'),
             ForeignKeyConstraint(columns=['instance_id'],
                    refcolumns=['instance.instance_id'],
                    name='analysis_fk1',
                    ondelete='CASCADE'),
             ForeignKeyConstraint(columns=['analysis_profile_id'],
                    refcolumns=['analysis_profile.analysis_profile_id'],
                    name='analysis_fk2',
                    ondelete='CASCADE'),
             extend_existing=True    )

        self._table      = self.analysis
        self._table_name = 'analysis'
        self.insert_defaulted.append('analysis_timestamp')
        self.update_defaulted.append('analysis_timestamp')
        return self._table

Example 50

Project: DataGristle
Source File: metadata.py
View license
    def table_create(self):
        """ Creates the 'collection_analysis' table.
        """
        self.collection_analysis = Table('collection_analysis' ,
             self.metadata           ,
             Column('ca_id'          ,
                    Integer          ,
                    nullable=False   ,
                    primary_key=True ),
             Column('analysis_id'    ,
                    Integer          ,
                    nullable=False  ),
             Column('collection_id'  ,
                    Integer          ,
                    nullable=False  ),
             Column('ca_name'        ,
                    String(80)       ,
                    nullable=False  ),
             Column('ca_location'    ,
                    String(256)      ,
                    nullable=False  ),
             Column('ca_row_cnt'     ,
                    Integer          ,
                    nullable=True   ),
             Column('ca_field_cnt'   ,
                    Integer          ,
                    nullable=True   ),
             Column('ca_delimiter'   ,
                    String(10)       ,
                    nullable=True   ),
             Column('ca_hasheader'   ,
                    Boolean          ,
                    nullable=True   ),
             Column('ca_quoting'     ,
                    String(20)       ,
                    nullable=True   ),
             Column('ca_quote_char'  ,
                    String(1)       ,
                    nullable=True   ),
             UniqueConstraint('analysis_id','collection_id',
                    name='collection_analysis_uk1'),
             ForeignKeyConstraint(columns=['analysis_id'],
                    refcolumns=['analysis.analysis_id'],
                    name='collection_analysis_fk1',
                    ondelete='CASCADE'),
             ForeignKeyConstraint(columns=['collection_id'],
                    refcolumns=['collection.collection_id'],
                    name='collection_analysis_fk2',
                    ondelete='CASCADE'),
             extend_existing=True    )

        self._table      = self.collection_analysis
        self._table_name = 'collection_analysis'
        return self._table