sqlalchemy.CheckConstraint

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

def languages_column(column_name) -> sa.Column:
    """A TEXT[] column of length > 0.

    Return an ARRAY(TEXT, as_tuple=True) column.

    :param column_name: the name of the column
    :returns: a SQLAlchemy Column for a non-null ARRAY(TEXT, as_tuple=True)
              type.
    """
    return sa.Column(
        pg.ARRAY(pg.TEXT, as_tuple=True),
        sa.CheckConstraint(
            'COALESCE(ARRAY_LENGTH({}, 1), 0) > 0'.format(column_name)
        ),
        nullable=False,
        default=['English'],
    )

def languages_constraint(column_name, languages_column_name) -> sa.Constraint:
    """CHECK CONSTRAINT for a translatable column.

    Checks that all of the languages in the languages column exist as keys in
    the translatable column.

    :param column_name: the name of the translatable column
    :param languages_column_name: the name of the TEXT[] column containing the
                                  languages.
    :return: a SQLAlchemy Constraint to ensure that all the required
             translations are available.
    """
    return sa.CheckConstraint(
        "{} ?& {}".format(column_name, languages_column_name),
        name='all_{}_languages_present_in_{}'.format(
            column_name, languages_column_name
        ),
    )

    def _literal_ck_fixture(
            self, copy_from=None, table_args=(), table_kwargs={}):
        m = MetaData()
        if copy_from is not None:
            t = copy_from
        else:
            t = Table(
                'tname', m,
                Column('id', Integer, primary_key=True),
                Column('email', String()),
                CheckConstraint("email LIKE '%@%'")
            )
        return ApplyBatchImpl(t, table_args, table_kwargs, False)

    def _sql_ck_fixture(self, table_args=(), table_kwargs={}):
        m = MetaData()
        t = Table(
            'tname', m,
            Column('id', Integer, primary_key=True),
            Column('email', String())
        )
        t.append_constraint(CheckConstraint(t.c.email.like('%@%')))
        return ApplyBatchImpl(t, table_args, table_kwargs, False)

    def test_rename_col_literal_ck_workaround(self):
        impl = self._literal_ck_fixture(
            copy_from=Table(
                'tname', MetaData(),
                Column('id', Integer, primary_key=True),
                Column('email', String),
            ),
            table_args=[CheckConstraint("emol LIKE '%@%'")])

        impl.alter_column('tname', 'email', name='emol')
        new_table = self._assert_impl(
            impl, ddl_contains="CHECK (emol LIKE '%@%')",
            colnames=["id", "email"])
        eq_(
            len([c for c in new_table.constraints
                if isinstance(c, CheckConstraint)]), 1)
        eq_(new_table.c.email.name, 'emol')

    def test_add_check_constraint_already_named_from_schema(self):
        m1 = MetaData(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"})
        ck = CheckConstraint("im a constraint", name="cc1")
        Table('t', m1, Column('x'), ck)

        context = op_fixture(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"})

        op.create_table(
            "some_table",
            Column('x', Integer, ck),
        )
        context.assert_(
            "CREATE TABLE some_table "
            "(x INTEGER CONSTRAINT ck_t_cc1 CHECK (im a constraint))"
        )

    def test_add_check_constraint_inline_on_table(self):
        context = op_fixture(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"})
        op.create_table(
            "some_table",
            Column('x', Integer),
            CheckConstraint("im a constraint", name="cc1")
        )
        context.assert_(
            "CREATE TABLE some_table "
            "(x INTEGER, CONSTRAINT ck_some_table_cc1 CHECK (im a constraint))"
        )

    def test_add_check_constraint_inline_on_table_w_f(self):
        context = op_fixture(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"})
        op.create_table(
            "some_table",
            Column('x', Integer),
            CheckConstraint("im a constraint", name=op.f("ck_some_table_cc1"))
        )
        context.assert_(
            "CREATE TABLE some_table "
            "(x INTEGER, CONSTRAINT ck_some_table_cc1 CHECK (im a constraint))"
        )

    def test_add_check_constraint_inline_on_column(self):
        context = op_fixture(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"})
        op.create_table(
            "some_table",
            Column('x', Integer, CheckConstraint("im a constraint", name="cc1"))
        )
        context.assert_(
            "CREATE TABLE some_table "
            "(x INTEGER CONSTRAINT ck_some_table_cc1 CHECK (im a constraint))"
        )

    def test_add_check_constraint_inline_on_column_w_f(self):
        context = op_fixture(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"})
        op.create_table(
            "some_table",
            Column(
                'x', Integer,
                CheckConstraint("im a constraint", name=op.f("ck_q_cc1")))
        )
        context.assert_(
            "CREATE TABLE some_table "
            "(x INTEGER CONSTRAINT ck_q_cc1 CHECK (im a constraint))"
        )

    def test_column_level_ck_name(self):
        t = Table(
            'tbl',
            MetaData(),
            Column(
                'a',
                Integer,
                CheckConstraint(
                    "a > 5",
                    name="ck_a_greater_five")))
        self.assert_compile(
            schema.CreateTable(t),
            "CREATE TABLE tbl (a INTEGER CONSTRAINT "
            "ck_a_greater_five CHECK (a > 5))"
        )

    def test_deferrable_column_check(self):
        t = Table('tbl', MetaData(),
                  Column('a', Integer),
                  Column('b', Integer,
                         CheckConstraint('a < b',
                                         deferrable=True,
                                         initially='DEFERRED')))

        self.assert_compile(
            schema.CreateTable(t),
            "CREATE TABLE tbl (a INTEGER, b INTEGER CHECK (a < b) "
            "DEFERRABLE INITIALLY DEFERRED)"
        )

    def test_render_ck_constraint_inline(self):
        t, t2 = self._constraint_create_fixture()

        CheckConstraint('a < b', name="my_test_constraint",
                        deferrable=True, initially='DEFERRED',
                        table=t)

        # before we create an AddConstraint,
        # the CONSTRAINT comes out inline
        self.assert_compile(
            schema.CreateTable(t),
            "CREATE TABLE tbl ("
            "a INTEGER, "
            "b INTEGER, "
            "CONSTRAINT my_test_constraint CHECK (a < b) "
            "DEFERRABLE INITIALLY DEFERRED"
            ")"
        )

    def test_render_ck_constraint_external(self):
        t, t2 = self._constraint_create_fixture()

        constraint = CheckConstraint('a < b', name="my_test_constraint",
                                     deferrable=True, initially='DEFERRED',
                                     table=t)

        self.assert_compile(
            schema.AddConstraint(constraint),
            "ALTER TABLE tbl ADD CONSTRAINT my_test_constraint "
            "CHECK (a < b) DEFERRABLE INITIALLY DEFERRED"
        )

    def test_external_ck_constraint_cancels_internal(self):
        t, t2 = self._constraint_create_fixture()

        constraint = CheckConstraint('a < b', name="my_test_constraint",
                                     deferrable=True, initially='DEFERRED',
                                     table=t)

        schema.AddConstraint(constraint)

        # once we make an AddConstraint,
        # inline compilation of the CONSTRAINT
        # is disabled
        self.assert_compile(
            schema.CreateTable(t),
            "CREATE TABLE tbl ("
            "a INTEGER, "
            "b INTEGER"
            ")"
        )

    def test_render_drop_constraint(self):
        t, t2 = self._constraint_create_fixture()

        constraint = CheckConstraint('a < b', name="my_test_constraint",
                                     deferrable=True, initially='DEFERRED',
                                     table=t)

        self.assert_compile(
            schema.DropConstraint(constraint),
            "ALTER TABLE tbl DROP CONSTRAINT my_test_constraint"
        )

    def test_render_drop_constraint_cascade(self):
        t, t2 = self._constraint_create_fixture()

        constraint = CheckConstraint('a < b', name="my_test_constraint",
                                     deferrable=True, initially='DEFERRED',
                                     table=t)

        self.assert_compile(
            schema.DropConstraint(constraint, cascade=True),
            "ALTER TABLE tbl DROP CONSTRAINT my_test_constraint CASCADE"
        )

    def test_render_check_constraint_sql_literal(self):
        t, t2 = self._constraint_create_fixture()

        constraint = CheckConstraint(t.c.a > 5)

        self.assert_compile(
            schema.AddConstraint(constraint),
            "ALTER TABLE tbl ADD CHECK (a > 5)"
        )

    @declared_attr
    def name(cls):
        """The URL name of this object, unique across all instances of this model"""
        if cls.__name_blank_allowed__:
            return Column(Unicode(cls.__name_length__), nullable=False, unique=True)
        else:
            return Column(Unicode(cls.__name_length__), CheckConstraint("name <> ''"), nullable=False, unique=True)

    @declared_attr
    def name(cls):
        """The URL name of this object, unique within a parent container"""
        if cls.__name_blank_allowed__:
            return Column(Unicode(cls.__name_length__), nullable=False)
        else:
            return Column(Unicode(cls.__name_length__), CheckConstraint("name <> ''"), nullable=False)

    @declared_attr
    def name(cls):
        """The URL name of this object, non-unique"""
        if cls.__name_blank_allowed__:
            return Column(Unicode(cls.__name_length__), nullable=False)
        else:
            return Column(Unicode(cls.__name_length__), CheckConstraint("name <> ''"), nullable=False)

    @declared_attr
    def name(cls):
        """The URL name of this instance, non-unique"""
        if cls.__name_blank_allowed__:
            return Column(Unicode(cls.__name_length__), nullable=False)
        else:
            return Column(Unicode(cls.__name_length__), CheckConstraint("name <> ''"), nullable=False)

    def table_create(self):
        """ Creates the 'element' table.
        """
        self.element = Table('element'                                      ,
                       self.metadata                                        ,
                       Column('element_name', String(60),  nullable=False   ,
                                                           primary_key=True),
                       Column('element_desc', String(255), nullable=False)  ,
                       Column('element_type', String(10),  nullable=False)  ,
                       Column('element_len' , Integer,     nullable=True)   ,
                       CheckConstraint ('element_len > 0 ',
                                        name='element_ck1')    ,
                       extend_existing=True )

        self._table      = self.element
        self._table_name = 'element'

        return self._table

    def table_create(self):
        """ Creates the 'element' table.
        """
        self.element = Table('element'                                      ,
                       self.metadata                                        ,
                       Column('element_name', String(60),  nullable=False   ,
                                                           primary_key=True),
                       Column('element_desc', String(255), nullable=False)  ,
                       Column('element_type', String(10),  nullable=False)  ,
                       Column('element_len' , Integer,     nullable=True)   ,
                       CheckConstraint ('element_len > 0 ',
                                        name='element_ck1')    ,
                       extend_existing=True )

        self._table      = self.element
        self._table_name = 'element'

        return self._table

    def table_create(self):
        self._table_name = 'animal'
        self.animal = Table(self._table_name    ,
                        self.metadata           ,
                        Column('animal_name'    ,
                               String(40)       ,
                               nullable=False   ,
                               primary_key=True),
                        Column('animal_desc'    ,
                               String(255)      ,
                               nullable=False)  ,
                        Column('animal_age'     ,
                               Integer          ,
                               nullable=False  ),
                        CheckConstraint('animal_age < 100'))
        self._table  = self.animal

        return self._table

    def table_create(self):
        self._table_name = 'animal'
        self.animal = Table(self._table_name    ,
                        self.metadata           ,
                        Column('animal_name'    ,
                               String(40)       ,
                               nullable=False   ,
                               primary_key=True),
                        Column('animal_desc'    ,
                               String(255)      ,
                               nullable=False)  ,
                        Column('animal_age'     ,
                               Integer          ,
                               nullable=False  ),
                        CheckConstraint('animal_age < 100'))
        self._table  = self.animal

        return self._table

def json_column(column_name: str, *, default=None) -> sa.Column:
    """A JSONB column.

    Return a column of type JSONB for use in models. Use this for entries like

        <language>: <text>

    :param column_name: the name of the column
    :param default: the column default (default value None, meaning no column
                    default)
    :return: a SQLAlchemy Column for a non-null JSONB type.
    """
    return sa.Column(
        pg.json.JSONB,
        sa.CheckConstraint(
            "{} @> '{{}}'".format(column_name),
            name='{}_valid_json_check'.format(column_name),
        ),
        nullable=False,
        server_default=default,
    )

    @classmethod
    def _get_db_schema(cls):
        schema = cls.schema

        m = MetaData(schema=schema)

        Table('user', m,
              Column('id', Integer, primary_key=True),
              Column('name', String(50)),
              Column('a1', Text),
              Column("pw", String(50)),
              Index('pw_idx', 'pw')
              )

        Table('address', m,
              Column('id', Integer, primary_key=True),
              Column('email_address', String(100), nullable=False),
              )

        Table('order', m,
              Column('order_id', Integer, primary_key=True),
              Column("amount", Numeric(8, 2), nullable=False,
                     server_default=text("0")),
              CheckConstraint('amount >= 0', name='ck_order_amount')
              )

        Table('extra', m,
              Column("x", CHAR),
              Column('uid', Integer, ForeignKey('user.id'))
              )

        return m

    @classmethod
    def _get_model_schema(cls):
        schema = cls.schema

        m = MetaData(schema=schema)

        Table('user', m,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', Text, server_default="x")
              )

        Table('address', m,
              Column('id', Integer, primary_key=True),
              Column('email_address', String(100), nullable=False),
              Column('street', String(50)),
              UniqueConstraint('email_address', name="uq_email")
              )

        Table('order', m,
              Column('order_id', Integer, primary_key=True),
              Column('amount', Numeric(10, 2), nullable=True,
                     server_default=text("0")),
              Column('user_id', Integer, ForeignKey('user.id')),
              CheckConstraint('amount > -1', name='ck_order_amount'),
              )

        Table('item', m,
              Column('id', Integer, primary_key=True),
              Column('description', String(100)),
              Column('order_id', Integer, ForeignKey('order.order_id')),
              CheckConstraint('len(description) > 5')
              )
        return m

    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

    @classmethod
    def _get_db_schema(cls):
        schema = cls.schema

        m = MetaData(schema=schema)

        Table('user', m,
              Column('id', Integer, primary_key=True),
              Column('name', String(50)),
              Column('a1', Text),
              Column("pw", String(50)),
              Index('pw_idx', 'pw')
              )

        Table('address', m,
              Column('id', Integer, primary_key=True),
              Column('email_address', String(100), nullable=False),
              )

        Table('order', m,
              Column('order_id', Integer, primary_key=True),
              Column("amount", Numeric(8, 2), nullable=False,
                     server_default=text("0")),
              CheckConstraint('amount >= 0', name='ck_order_amount')
              )

        Table('extra', m,
              Column("x", CHAR),
              Column('uid', Integer, ForeignKey('user.id'))
              )

        return m

    @classmethod
    def _get_model_schema(cls):
        schema = cls.schema

        m = MetaData(schema=schema)

        Table('user', m,
              Column('id', Integer, primary_key=True),
              Column('name', String(50), nullable=False),
              Column('a1', Text, server_default="x")
              )

        Table('address', m,
              Column('id', Integer, primary_key=True),
              Column('email_address', String(100), nullable=False),
              Column('street', String(50)),
              UniqueConstraint('email_address', name="uq_email")
              )

        Table('order', m,
              Column('order_id', Integer, primary_key=True),
              Column('amount', Numeric(10, 2), nullable=True,
                     server_default=text("0")),
              Column('user_id', Integer, ForeignKey('user.id')),
              CheckConstraint('amount > -1', name='ck_order_amount'),
              )

        Table('item', m,
              Column('id', Integer, primary_key=True),
              Column('description', String(100)),
              Column('order_id', Integer, ForeignKey('order.order_id')),
              CheckConstraint('len(description) > 5')
              )
        return m

    @testing.requires.check_constraints
    @testing.provide_metadata
    def test_check_constraint_create(self):
        metadata = self.metadata

        Table('foo', metadata,
              Column('id', Integer, primary_key=True),
              Column('x', Integer),
              Column('y', Integer),
              CheckConstraint('x>y'))
        Table('bar', metadata,
              Column('id', Integer, primary_key=True),
              Column('x', Integer, CheckConstraint('x>7')),
              Column('z', Integer)
              )

        self.assert_sql_execution(
            testing.db,
            lambda: metadata.create_all(checkfirst=False),
            AllOf(
                CompiledSQL('CREATE TABLE foo ('
                            'id INTEGER NOT NULL, '
                            'x INTEGER, '
                            'y INTEGER, '
                            'PRIMARY KEY (id), '
                            'CHECK (x>y)'
                            ')'
                            ),
                CompiledSQL('CREATE TABLE bar ('
                            'id INTEGER NOT NULL, '
                            'x INTEGER CHECK (x>7), '
                            'z INTEGER, '
                            'PRIMARY KEY (id)'
                            ')'
                            )
            )
        )

    def test_deferrable_table_check(self):
        factory = lambda **kw: CheckConstraint('a < b', **kw)
        self._test_deferrable(factory)

    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

    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

    def table_create(self):
        """ Creates the 'field_analysis' table.
        """
        self.field_analysis = Table('field_analysis' ,
             self.metadata           ,
             Column('fa_id'          ,
                    Integer          ,
                    nullable=False   ,
                    primary_key=True),
             Column('ca_id'          ,
                    Integer          ,
                    nullable=False  ),
             Column('field_id'       ,
                    Integer          ,
                    nullable=False  ),
             Column('fa_type'        ,
                    String(10)       ,
                    nullable=True   ),
             Column('fa_unique_cnt'  ,
                    Integer          ,
                    nullable=True   ),
             Column('fa_known_cnt'   ,
                    Integer          ,
                    nullable=True   ),
             Column('fa_unknown_cnt' ,
                    Integer          ,
                    nullable=True   ),
             Column('fa_min'         ,
                    String(256)      ,
                    nullable=True   ),
             Column('fa_max'         ,
                    String(256)      ,
                    nullable=True   ),
             Column('fa_mean'        ,
                    Float            ,
                    nullable=True   ),
             Column('fa_median'      ,
                    Float            ,
                    nullable=True   ),
             Column('fa_stddev'      ,
                    Float            ,
                    nullable=True   ),
             Column('fa_variance'    ,
                    Float            ,
                    nullable=True   ),
             Column('fa_min_len'     ,
                    Integer          ,
                    nullable=True   ),
             Column('fa_max_len'     ,
                    Integer          ,
                    nullable=True   ),
             Column('fa_mean_len'    ,
                    Integer          ,
                    nullable=True   ),
             Column('fa_case'        ,
                    String(10)       ,
                    nullable=True   ),
             UniqueConstraint('ca_id','field_id'               ,
                              name='field_analysis_uk1')       ,
             ForeignKeyConstraint(columns=['ca_id']            ,
                                  refcolumns=['collection_analysis.ca_id'],
                                  name='field_analysis_fk1'    ,
                                  ondelete='CASCADE'),
             ForeignKeyConstraint(columns=['field_id']         ,
                                  refcolumns=['field.field_id'],
                                  name='field_analysis_fk2'    ,
                                  ondelete='CASCADE'),
             CheckConstraint ("fa_case IN ('lower','upper','mixed','unk')",
                              name='field_analysis_ck1')       ,
             extend_existing=True    )

        self._table      = self.field_analysis
        self._table_name = 'field_analysis'
        return self._table

    def table_create(self):
        """ Creates the 'field_analysis' table.
        """
        self.field_analysis = Table('field_analysis' ,
             self.metadata           ,
             Column('fa_id'          ,
                    Integer          ,
                    nullable=False   ,
                    primary_key=True),
             Column('ca_id'          ,
                    Integer          ,
                    nullable=False  ),
             Column('field_id'       ,
                    Integer          ,
                    nullable=False  ),
             Column('fa_type'        ,
                    String(10)       ,
                    nullable=True   ),
             Column('fa_unique_cnt'  ,
                    Integer          ,
                    nullable=True   ),
             Column('fa_known_cnt'   ,
                    Integer          ,
                    nullable=True   ),
             Column('fa_unknown_cnt' ,
                    Integer          ,
                    nullable=True   ),
             Column('fa_min'         ,
                    String(256)      ,
                    nullable=True   ),
             Column('fa_max'         ,
                    String(256)      ,
                    nullable=True   ),
             Column('fa_mean'        ,
                    Float            ,
                    nullable=True   ),
             Column('fa_median'      ,
                    Float            ,
                    nullable=True   ),
             Column('fa_stddev'      ,
                    Float            ,
                    nullable=True   ),
             Column('fa_variance'    ,
                    Float            ,
                    nullable=True   ),
             Column('fa_min_len'     ,
                    Integer          ,
                    nullable=True   ),
             Column('fa_max_len'     ,
                    Integer          ,
                    nullable=True   ),
             Column('fa_mean_len'    ,
                    Integer          ,
                    nullable=True   ),
             Column('fa_case'        ,
                    String(10)       ,
                    nullable=True   ),
             UniqueConstraint('ca_id','field_id'               ,
                              name='field_analysis_uk1')       ,
             ForeignKeyConstraint(columns=['ca_id']            ,
                                  refcolumns=['collection_analysis.ca_id'],
                                  name='field_analysis_fk1'    ,
                                  ondelete='CASCADE'),
             ForeignKeyConstraint(columns=['field_id']         ,
                                  refcolumns=['field.field_id'],
                                  name='field_analysis_fk2'    ,
                                  ondelete='CASCADE'),
             CheckConstraint ("fa_case IN ('lower','upper','mixed','unk')",
                              name='field_analysis_ck1')       ,
             extend_existing=True    )

        self._table      = self.field_analysis
        self._table_name = 'field_analysis'
        return self._table

    @declarative.declared_attr
    def __table_args__(cls):
        return (sqlalchemy.CheckConstraint('started_at <= ended_at',
                                           name="ck_started_before_ended"),
                COMMON_TABLES_ARGS)

    def test_clone_table_retains_constraints(self):
        meta = MetaData()
        meta.bind = self.engine
        parent = Table('parent',
                       meta,
                       Column('id', String(36), primary_key=True,
                              nullable=False),
                       Column('A', Integer),
                       Column('B', Integer),
                       Column('C', Integer,
                              CheckConstraint('C>100', name="above 100")),
                       Column('D', Integer, unique=True),

                       UniqueConstraint('A', 'B', name='uix_1')
                       )
        parent.create()

        child = Table('child',
                      meta,
                      Column('id', String(36),
                             ForeignKey('parent.id', name="parent_ref"),
                             primary_key=True,
                             nullable=False),
                      Column('A', Boolean, default=False)
                      )
        child.create()

        ignorecols = [
            parent.c.D.name,
        ]

        new_parent = migrate_utils.clone_table('new_parent', parent, meta,
                                               ignorecols=ignorecols)
        new_child = migrate_utils.clone_table('new_child', child, meta)

        self.assertTrue(_has_constraint(new_parent.constraints,
                                        UniqueConstraint, 'uix_1'))
        self.assertTrue(_has_constraint(new_parent.c.C.constraints,
                                        CheckConstraint, 'above 100'))
        self.assertTrue(_has_constraint(new_child.constraints,
                                        ForeignKeyConstraint, 'parent_ref'))

    def test_clone_table_ignores_constraints(self):
        meta = MetaData()
        meta.bind = self.engine
        table = Table('constraints_check',
                      meta,
                      Column('id', String(36), primary_key=True,
                             nullable=False),
                      Column('A', Integer),
                      Column('B', Integer),
                      Column('C', Integer,
                             CheckConstraint('C>100', name="above 100")),

                      UniqueConstraint('A', 'B', name='uix_1')
                      )
        table.create()

        ignorecons = [
            'uix_1',
        ]

        new_table = migrate_utils.clone_table('constraints_check_tmp', table,
                                              meta, ignorecons=ignorecons)
        self.assertFalse(_has_constraint(new_table.constraints,
                                         UniqueConstraint, 'uix_1'))