Python sqlalchemy.Unicode() Examples

def upgrade():
    try:
        op.create_table('passwordreset',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('recoverycode', sa.Unicode(length=255), nullable=False),
        sa.Column('username', sa.Unicode(length=64), nullable=False),
        sa.Column('realm', sa.Unicode(length=64), nullable=False),
        sa.Column('resolver', sa.Unicode(length=64), nullable=True),
        sa.Column('email', sa.Unicode(length=255), nullable=True),
        sa.Column('timestamp', sa.DateTime(), nullable=True),
        sa.Column('expiration', sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint('id')
        )
        op.create_index(op.f('ix_passwordreset_realm'), 'passwordreset',
                        ['realm'], unique=False)
        op.create_index(op.f('ix_passwordreset_username'), 'passwordreset',
                        ['username'], unique=False)
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table passwordreset already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add table 'passwordreset'")
        print (exx) 

def upgrade():
    userstatus.create(op.get_bind())
    op.add_column(
        'users',
        sa.Column('status', sa.Enum(*userstatus_choices, name='userstatus'), nullable=True)
    )
    op.add_column('users', sa.Column('status_info', sa.Unicode(), nullable=True))

    # Set user's status field.
    conn = op.get_bind()
    query = textwrap.dedent(
        "UPDATE users SET status = 'active', status_info = 'migrated' WHERE is_active = 't';"
    )
    conn.execute(query)
    query = textwrap.dedent(
        "UPDATE users SET status = 'inactive', status_info = 'migrated' WHERE is_active <> 't';"
    )
    conn.execute(query)

    op.alter_column('users', column_name='status', nullable=False)
    op.drop_column('users', 'is_active') 

def upgrade():
    try:
        op.create_table('eventhandlercondition',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('eventhandler_id', sa.Integer(), nullable=True),
        sa.Column('Key', sa.Unicode(length=255), nullable=False),
        sa.Column('Value', sa.Unicode(length=2000), nullable=True),
        sa.Column('comparator', sa.Unicode(length=255), nullable=True),
        sa.ForeignKeyConstraint(['eventhandler_id'], ['eventhandler.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('eventhandler_id', 'Key', name='ehcix_1')
        )
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table eventhandlercondition already exists.")
        else:
            print("Table already exists")
            print(exx)

    except Exception as exx:
        print("Could not add Table eventhandlercondition")
        print (exx) 

def test_basic_override(self):
        meta = self.metadata
        table = Table(
            "override_test",
            meta,
            Column("col1", sa.Integer, primary_key=True),
            Column("col2", sa.String(20)),
            Column("col3", sa.Numeric),
        )
        table.create()

        meta2 = MetaData(testing.db)
        table = Table(
            "override_test",
            meta2,
            Column("col2", sa.Unicode()),
            Column("col4", sa.String(30)),
            autoload=True,
        )

        self.assert_(isinstance(table.c.col1.type, sa.Integer))
        self.assert_(isinstance(table.c.col2.type, sa.Unicode))
        self.assert_(isinstance(table.c.col4.type, sa.String)) 

def upgrade():
    try:
        op.add_column('policy', sa.Column('adminuser', sa.Unicode(length=256), nullable=True))
    except Exception as exx:
        print('Adding of column "adminuser" in table policy failed: {!r}'.format(exx))
        print('This is expected behavior if this column already exists.')

    # Now that we added the column in the table, we can move the "user" from admin-policies to
    # the "adminuser" column

    try:
        bind = op.get_bind()
        session = orm.Session(bind=bind)
        pol_name = None
        for policy in session.query(Policy).filter(Policy.user != "", Policy.scope == "admin"):
            pol_name = policy.name
            # move the "user" to the "adminuser"
            policy.adminuser = policy.user
            policy.user = u""
        session.commit()
    except Exception as exx:
        session.rollback()
        print("Failed to migrate column adminuser in policies due to error in policy '{0!s}'.".format(pol_name))
        print(exx) 

def upgrade(engine_name):
    op.create_table('contact',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Unicode(), nullable=True),
    sa.Column('url', sa.Unicode(), nullable=True),
    sa.Column('phone', sa.Unicode(), nullable=True),
    sa.Column('email', sa.Unicode(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='datamart'
    )
    op.create_table('rel_contact_administrativedivision_hazardtype',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('contact_id', sa.Integer(), nullable=False),
    sa.Column('administrativedivision_id', sa.Integer(), nullable=False),
    sa.Column('hazardtype_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['administrativedivision_id'], ['datamart.administrativedivision.id'], ),
    sa.ForeignKeyConstraint(['contact_id'], ['datamart.contact.id'], ),
    sa.ForeignKeyConstraint(['hazardtype_id'], ['datamart.enum_hazardtype.id'], ),
    sa.PrimaryKeyConstraint('id'),
    schema='datamart'
    )
    op.create_index(op.f('ix_datamart_rel_contact_administrativedivision_hazardtype_contact_id'), 'rel_contact_administrativedivision_hazardtype', ['contact_id'], unique=False, schema='datamart')
    op.create_index(op.f('ix_datamart_rel_contact_administrativedivision_hazardtype_hazardtype_id'), 'rel_contact_administrativedivision_hazardtype', ['hazardtype_id'], unique=False, schema='datamart') 

def get_execution_table(metadata, tablename):
    return sqlalchemy.Table(
        tablename, metadata,
        sqlalchemy.Column('eid', sqlalchemy.Unicode(191, _warn_on_bytestring=False),
                          primary_key=True),
        sqlalchemy.Column('hostname', sqlalchemy.Text, nullable=True),
        sqlalchemy.Column('pid', sqlalchemy.Integer, nullable=True),
        sqlalchemy.Column('state', sqlalchemy.Integer, nullable=False),
        sqlalchemy.Column('scheduled_time', sqlalchemy.DateTime(timezone=True), nullable=False,
                          default=utils.get_current_datetime),
        sqlalchemy.Column('updated_time', sqlalchemy.DateTime(timezone=True),
                          default=utils.get_current_datetime, onupdate=utils.get_current_datetime),
        sqlalchemy.Column('description', sqlalchemy.Text, nullable=True),
        sqlalchemy.Column('result', sqlalchemy.Text, nullable=True),
        sqlalchemy.Column('job_id', sqlalchemy.Text, nullable=False),
        sqlalchemy.Column('task_id', sqlalchemy.Text, nullable=True)) 

def upgrade():
    try:
        op.create_table('clientapplication',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('ip', sa.Unicode(length=255), nullable=False),
        sa.Column('hostname', sa.Unicode(length=255), nullable=True),
        sa.Column('clienttype', sa.Unicode(length=255), nullable=False),
        sa.Column('lastseen', sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('ip', 'clienttype', name='caix')
        )
        op.create_index(op.f('ix_clientapplication_clienttype'), 'clientapplication', ['clienttype'], unique=False)
        op.create_index(op.f('ix_clientapplication_id'), 'clientapplication', ['id'], unique=False)
        op.create_index(op.f('ix_clientapplication_ip'), 'clientapplication', ['ip'], unique=False)
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table clientapplication already exists.")
        else:
            print("Table already exists")
            print(exx)

    except Exception as exx:
        print("Could not add Table clientapplication")
        print (exx) 

def upgrade():
    try:
        op.create_table('authcache',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('first_auth', sa.DateTime(), nullable=True),
        sa.Column('last_auth', sa.DateTime(), nullable=True),
        sa.Column('username', sa.Unicode(length=64), nullable=True),
        sa.Column('resolver', sa.Unicode(length=120), nullable=True),
        sa.Column('realm', sa.Unicode(length=120), nullable=True),
        sa.Column('client_ip', sa.Unicode(length=40), nullable=True),
        sa.Column('user_agent', sa.Unicode(length=120), nullable=True),
        sa.Column('authentication', sa.Unicode(length=64), nullable=True),
        sa.PrimaryKeyConstraint('id')
        )
        op.create_index(op.f('ix_authcache_realm'), 'authcache', ['realm'], unique=False)
        op.create_index(op.f('ix_authcache_resolver'), 'authcache', ['resolver'], unique=False)
        op.create_index(op.f('ix_authcache_username'), 'authcache', ['username'], unique=False)
    except Exception as exx:
        print ("Could not add table 'authcache' - probably already exists!")
        print (exx) 

def upgrade():
    try:
        try:
            create_seq(Sequence('policycondition_seq'))
        except Exception as _e:
            pass
        op.create_table('policycondition',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('policy_id', sa.Integer(), nullable=False),
        sa.Column('section', sa.Unicode(length=255), nullable=False),
        sa.Column('Key', sa.Unicode(length=255), nullable=False),
        sa.Column('comparator', sa.Unicode(length=255), nullable=False),
        sa.Column('Value', sa.Unicode(length=2000), nullable=False),
        sa.Column('active', sa.Boolean(), nullable=False),
        sa.ForeignKeyConstraint(['policy_id'], ['policy.id'], ),
        sa.PrimaryKeyConstraint('id'),
        mysql_row_format='DYNAMIC'
        )
    except Exception as exx:
        print("Could not create table policycondition: {!r}".format(exx))
    try:
        op.drop_column('policy', 'condition')
    except Exception as exx:
        print("Could not drop column policy.condition: {!r}".format(exx)) 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('image',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('alt', sa.Unicode(length=256), nullable=True),
    sa.Column('path', sa.String(length=128), nullable=False),
    sa.Column('public_id', sa.String(length=128), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('product_image',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('product_id', sa.Integer(), nullable=False),
    sa.Column('image_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['image_id'], ['image.id'], ),
    sa.ForeignKeyConstraint(['product_id'], ['product.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ### 

def upgrade():
    try:
        op.create_table('smtpserver',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('identifier', sa.Unicode(length=255), nullable=False),
        sa.Column('server', sa.Unicode(length=255), nullable=False),
        sa.Column('port', sa.Integer(), nullable=True),
        sa.Column('username', sa.Unicode(length=255), nullable=True),
        sa.Column('password', sa.Unicode(length=255), nullable=True),
        sa.Column('sender', sa.Unicode(length=255), nullable=True),
        sa.Column('tls', sa.Boolean(), nullable=True),
        sa.Column('description', sa.Unicode(length=2000), nullable=True),
        sa.PrimaryKeyConstraint('id')
        )
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Column smtpserver already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add table 'smtpserver'")
        print (exx) 

def upgrade():

    try:
        op.alter_column('subscription', 'level', type_=sa.Unicode(80),
                        existing_type=sa.Unicode(30))
    except Exception as exx:
        print("Could not make field 'level' longer.")
        print (exx)
    try:
        op.alter_column('subscription', 'application', type_=sa.Unicode(80),
                        existing_type=sa.Unicode(30))
    except Exception as exx:
        print("Could not make field 'application' longer.")
        print (exx)

    try:
        op.alter_column('subscription', 'for_name', type_=sa.Unicode(80),
                            existing_type=sa.Unicode(30))
    except Exception as exx:
        print("Could not make field 'for_name' longer.")
        print (exx) 

def upgrade():
    try:
        op.create_table('radiusserver',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('identifier', sa.Unicode(length=255), nullable=False),
        sa.Column('server', sa.Unicode(length=255), nullable=False),
        sa.Column('port', sa.Integer(), nullable=True),
        sa.Column('secret', sa.Unicode(length=255), nullable=True),
        sa.Column('description', sa.Unicode(length=2000), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('identifier')
        )
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table 'radiusserver' already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add table 'radiusserver'")
        print (exx) 

def upgrade():

    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('category_group',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Unicode(length=100), nullable=False),
    sa.Column('description', sa.UnicodeText(), nullable=True),
    sa.Column('grouporder', sa.Float(), nullable=False),
    sa.Column('date_created', sa.DateTime(), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('name')
    )
    op.create_table('population_group',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Unicode(length=100), nullable=False),
    sa.Column('description', sa.UnicodeText(), nullable=True),
    sa.Column('grouporder', sa.Float(), nullable=False),
    sa.Column('date_created', sa.DateTime(), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('name')
    )
    op.add_column(u'category', sa.Column('grouping_id', sa.Integer(), nullable=True))
    op.add_column(u'population', sa.Column('grouping_id', sa.Integer(), nullable=True))
    ### end Alembic commands ### 

def test_create_table_legacy_oid_mapping(self):
        mapping_file = os.path.join(os.path.dirname(__file__), "mapping_v1.yml")

        content = parse_from_yaml(mapping_file)
        account_mapping = content["Insert Contacts"]

        with temporary_dir() as d:
            tmp_db_path = os.path.join(d, "temp.db")

            engine, metadata = create_db_file(tmp_db_path)
            t = create_table(account_mapping, metadata)
            assert t.name == "contacts"
            assert isinstance(t.columns["sf_id"].type, Unicode)
            assert isinstance(t.columns["first_name"].type, Unicode)
            assert isinstance(t.columns["last_name"].type, Unicode)
            assert isinstance(t.columns["email"].type, Unicode) 

def upgrade():
    op.alter_column('board', 'background_position', nullable=True, server_default=None)
    board = sa.Table(
        'board',
        sa.MetaData(),
        sa.Column('background_position', sa.Unicode),
    )
    bind = op.get_bind()
    bind.execute(
        board.update().where(
            board.c.background_position == u'repeat'
        ).values(background_position=u'tile')
    )
    bind.execute(
        board.update().where(
            board.c.background_position == u'cover'
        ).values(background_position=u'fill')
    ) 

def test_sql_bulk_insert_from_records__sqlite(self):
        engine, metadata = create_db_memory()
        fields = [
            Column("id", Integer(), primary_key=True, autoincrement=True),
            Column("sf_id", Unicode(24)),
        ]
        id_t = Table("TestTable", metadata, *fields)
        id_t.create()
        model = type("TestModel", (object,), {})
        mapper(model, id_t)

        util = bulkdata.utils.SqlAlchemyMixin()
        util.metadata = metadata
        session = create_session(bind=engine, autocommit=False)
        util.session = session
        connection = session.connection()

        util._sql_bulk_insert_from_records(
            connection=connection,
            table="TestTable",
            columns=("id", "sf_id"),
            record_iterable=([f"{x}", f"00100000000000{x}"] for x in range(10)),
        )

        assert session.query(model).count() == 10 

def test_generate_db_entry_table(self):
        assert self.ds.fact_table.table.name == 'test__facts', \
            self.ds.fact_table.table.name
        cols = self.ds.fact_table.table.c
        assert '_id' in cols, cols
        assert isinstance(cols['_id'].type, Unicode)
        assert 'year' in cols, cols
        assert isinstance(cols['year'].type, Integer)
        assert 'amount' in cols
        assert isinstance(cols['amount'].type, Integer)
        assert 'field' in cols
        assert isinstance(cols['field'].type, Unicode)
        assert 'to_label' in cols, cols
        assert isinstance(cols['to_label'].type, Unicode)
        assert 'func_label' in cols
        assert isinstance(cols['func_label'].type, Unicode)
        assert_raises(KeyError, cols.__getitem__, 'foo') 

def create_table(mapping, metadata):
    """Given a mapping data structure (from mapping.yml) and SQLAlchemy
       metadata, create a table matching the mapping.

       Mapping should be a dict-like with keys "fields", "table" and
       optionally "oid_as_pk" and "record_type" """

    fields = []
    _handle_primary_key(mapping, fields)

    # make a field list to create
    for field in fields_for_mapping(mapping):
        if mapping["oid_as_pk"] and field["sf"] == "Id":
            continue
        fields.append(Column(field["db"], Unicode(255)))

    if "record_type" in mapping:
        fields.append(Column("record_type", Unicode(255)))
    t = Table(mapping["table"], metadata, *fields)
    if t.exists():
        raise BulkDataException(f"Table already exists: {mapping['table']}")
    return t 

def on_connect(self):
        if self.cx_oracle_ver < (5,):
            # no output type handlers before version 5
            return

        cx_Oracle = self.dbapi

        def output_type_handler(cursor, name, defaultType,
                                size, precision, scale):
            # convert all NUMBER with precision + positive scale to Decimal
            # this almost allows "native decimal" mode.
            if self.supports_native_decimal and \
                    defaultType == cx_Oracle.NUMBER and \
                    precision and scale > 0:
                return cursor.var(
                    cx_Oracle.STRING,
                    255,
                    outconverter=self._to_decimal,
                    arraysize=cursor.arraysize)
            # if NUMBER with zero precision and 0 or neg scale, this appears
            # to indicate "ambiguous".  Use a slower converter that will
            # make a decision based on each value received - the type
            # may change from row to row (!).   This kills
            # off "native decimal" mode, handlers still needed.
            elif self.supports_native_decimal and \
                    defaultType == cx_Oracle.NUMBER \
                    and not precision and scale <= 0:
                return cursor.var(
                    cx_Oracle.STRING,
                    255,
                    outconverter=self._detect_decimal,
                    arraysize=cursor.arraysize)
            # allow all strings to come back natively as Unicode
            elif self.coerce_to_unicode and \
                    defaultType in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR):
                return cursor.var(util.text_type, size, cursor.arraysize)

        def on_connect(conn):
            conn.outputtypehandler = output_type_handler

        return on_connect 

def _create_table(self, mapping):
        """Create a table for the given mapping."""
        model_name = f"{mapping['table']}Model"
        mapper_kwargs = {}
        self.models[mapping["table"]] = type(model_name, (object,), {})

        t = create_table(mapping, self.metadata)

        if "RecordTypeId" in mapping["fields"]:
            # We're using Record Type Mapping support.
            mapping["record_type_table"] = mapping["sf_object"] + "_rt_mapping"
            # If multiple mappings point to the same table, don't recreate the table
            if mapping["record_type_table"] not in self.models:
                self._create_record_type_table(mapping["record_type_table"])

        if not mapping["oid_as_pk"]:
            mapping["sf_id_table"] = mapping["table"] + "_sf_id"
            # If multiple mappings point to the same table, don't recreate the table
            if mapping["sf_id_table"] not in self.models:
                sf_id_model_name = f"{mapping['sf_id_table']}Model"
                self.models[mapping["sf_id_table"]] = type(
                    sf_id_model_name, (object,), {}
                )
                sf_id_fields = [
                    Column("id", Integer(), primary_key=True, autoincrement=True),
                    Column("sf_id", Unicode(24)),
                ]
                id_t = Table(mapping["sf_id_table"], self.metadata, *sf_id_fields)
                mapper(self.models[mapping["sf_id_table"]], id_t)

        mapper(self.models[mapping["table"]], t, **mapper_kwargs) 

def upgrade(engine_name):
    op.add_column('administrativedivision', sa.Column('name_fr', sa.Unicode(), nullable=True), schema='datamart')
    op.add_column('administrativedivision', sa.Column('name_es', sa.Unicode(), nullable=True), schema='datamart') 

def _initialize_id_table(self, mapping, should_reset_table):
        """initalize or find table to hold the inserted SF Ids

        The table has a name like xxx_sf_ids and has just two columns, id and sf_id.

        If the table already exists, should_reset_table determines whether to
        drop and recreate it or not.
        """
        id_table_name = f"{mapping['table']}_sf_ids"

        already_exists = id_table_name in self.metadata.tables

        if already_exists and not should_reset_table:
            return id_table_name

        if not hasattr(self, "_initialized_id_tables"):
            self._initialized_id_tables = set()
        if id_table_name not in self._initialized_id_tables:
            if already_exists:
                self.metadata.remove(self.metadata.tables[id_table_name])
            id_table = Table(
                id_table_name,
                self.metadata,
                Column("id", Unicode(255), primary_key=True),
                Column("sf_id", Unicode(18)),
            )
            if id_table.exists():
                id_table.drop()
            id_table.create()
            self._initialized_id_tables.add(id_table_name)
        return id_table_name 

def upgrade():
    op.create_table(
        'apscheduler_jobs',
        sa.Column('id', sa.Unicode(191, _warn_on_bytestring=False),
                  nullable=False),
        sa.Column('next_run_time', sa.Float(25), index=True),
        sa.Column('job_state', sa.LargeBinary, nullable=False),
        sa.Column('service_id', sa.Integer(), nullable=False),
        sa.Column('tag', models.JSONEncodedDict(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.ForeignKeyConstraint(['service_id'], ['services.id'])
    ) 

def test_create_table_modern_id_mapping(self):
        mapping_file = os.path.join(os.path.dirname(__file__), "mapping_v2.yml")
        content = parse_from_yaml(mapping_file)
        account_mapping = content["Insert Contacts"]

        with temporary_dir() as d:
            tmp_db_path = os.path.join(d, "temp.db")

            engine, metadata = create_db_file(tmp_db_path)
            t = create_table(account_mapping, metadata)
            assert t.name == "contacts"
            assert isinstance(t.columns["id"].type, Integer)
            assert isinstance(t.columns["first_name"].type, Unicode)
            assert isinstance(t.columns["last_name"].type, Unicode)
            assert isinstance(t.columns["email"].type, Unicode) 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('users',
    sa.Column('id', sa.Unicode(length=30), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('directories',
    sa.Column('user_id', sa.Unicode(length=30), nullable=False),
    sa.Column('name', sa.Unicode(length=70), nullable=False),
    sa.Column('parent_user_id', sa.Unicode(length=30), nullable=True),
    sa.Column('parent_name', sa.Unicode(length=70), nullable=True),
    sa.CheckConstraint(u"left(name, 1) = '/'", name=u'directories_startwith_slash'),
    sa.CheckConstraint(u"length(regexp_replace(name, '[^/]+', '', 'g')) - 1= length(regexp_replace(parent_name, '[^/]+', '', 'g'))", name=u'directories_slash_count'),
    sa.CheckConstraint(u"right(name, 1) = '/'", name=u'directories_endwith_slash'),
    sa.CheckConstraint(u'(parent_name IS NULL AND parent_user_id IS NULL) OR (parent_name IS NOT NULL AND parent_user_id IS NOT NULL)', name=u'directories_null_user_id_match'),
    sa.CheckConstraint(u'position(parent_name in name) != 0', name=u'directories_parent_name_prefix'),
    sa.CheckConstraint(u'user_id = parent_user_id', name=u'directories_match_user_id'),
    sa.ForeignKeyConstraint(['parent_user_id', 'parent_name'], [u'directories.user_id', u'directories.name'], ),
    sa.ForeignKeyConstraint(['user_id'], [u'users.id'], ),
    sa.PrimaryKeyConstraint('user_id', 'name')
    )
    op.create_table('files',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Unicode(length=40), nullable=False),
    sa.Column('user_id', sa.Unicode(length=30), nullable=False),
    sa.Column('parent_name', sa.Unicode(length=70), nullable=False),
    sa.Column('content', sa.LargeBinary(length=100000), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.ForeignKeyConstraint(['user_id', 'parent_name'], [u'directories.user_id', u'directories.name'], ),
    sa.ForeignKeyConstraint(['user_id'], [u'users.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('checkpoints',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('file_id', sa.Integer(), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.ForeignKeyConstraint(['file_id'], [u'files.id'], onupdate=u'CASCADE', ondelete=u'CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ### 

def upgrade():

    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('resource', sa.Column('is_approved', sa.Boolean(), server_default='1', nullable=False))
    op.add_column('resource', sa.Column('submitted_date', sa.DateTime(), nullable=True))
    op.add_column('resource', sa.Column('submitted_ip', sa.Unicode(length=45), nullable=True))
    op.add_column('resource', sa.Column('submitted_user_id', sa.Integer(), nullable=True))
    ### end Alembic commands ### 

def test_unicode_warnings(self):
        metadata = MetaData(self.engine)
        table1 = Table(
            "mytable",
            metadata,
            Column(
                "col1",
                Integer,
                primary_key=True,
                test_needs_autoincrement=True,
            ),
            Column("col2", Unicode(30)),
        )
        metadata.create_all()
        i = [1]

        # the times here is cranked way up so that we can see
        # pysqlite clearing out its internal buffer and allow
        # the test to pass
        @testing.emits_warning()
        @profile_memory()
        def go():

            # execute with a non-unicode object. a warning is emitted,
            # this warning shouldn't clog up memory.

            self.engine.execute(
                table1.select().where(table1.c.col2 == "foo%d" % i[0])
            )
            i[0] += 1

        try:
            go()
        finally:
            metadata.drop_all() 

def upgrade():

    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('population',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Unicode(length=100), nullable=False),
    sa.Column('description', sa.UnicodeText(), nullable=True),
    sa.Column('keywords', sa.UnicodeText(), nullable=True),
    sa.Column('visible', sa.Boolean(), nullable=False),
    sa.Column('date_created', sa.DateTime(), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('name')
    )
    ### end Alembic commands ###