def update_attributes(self, username, hostname, user_attrs):
        """Change the attributes of an existing user."""
        LOG.debug("Changing user attributes for user %s." % username)
        user = self._get_user(username, hostname)

        new_name = user_attrs.get('name')
        new_host = user_attrs.get('host')
        new_password = user_attrs.get('password')

        if new_name or new_host or new_password:

            with self.local_sql_client(self.mysql_app.get_engine()) as client:

                if new_password is not None:
                    uu = sql_query.SetPassword(user.name, host=user.host,
                                               new_password=new_password)

                    t = text(str(uu))
                    client.execute(t)

                if new_name or new_host:
                    uu = sql_query.RenameUser(user.name, host=user.host,
                                              new_user=new_name,
                                              new_host=new_host)
                    t = text(str(uu))
                    client.execute(t)

 def __create_postgre_resource(self, app, key):
     bind = metadata.bind
     # Creates a connection with permission to create users and databases
     metadata.bind = self.__generate_connection_uri(
                                 self.__root_user, self.__root_password)
     # Create database cannot be executed as a transaction block so we 
     # should change the isolation level to create a database
     metadata.bind.engine.connect().\
         connection.connection.set_isolation_level(0)
     
     # Generate data and queries
     database_name, user_name, password = self.__create_random_data()
     
     # It is needed to concatenate this way to avoid the usual but 
     # incompatible way to escape strings while executing some admin-level 
     # sql commands
     sql_create_user = \
             "CREATE USER " + user_name + \
             " WITH PASSWORD '" + password + "';"
     
     sql_create_database =  \
             "CREATE DATABASE " + database_name + \
             " WITH OWNER " + user_name + \
             " ENCODING 'UTF8';" # @TODO: Check if it is correct 
     # Show the queries
     print("LOG: " + sql_create_user)
     print("LOG: " + sql_create_database)
     # Perform the queries
     text(sql_create_user, metadata.bind).execute(user=user_name, password=password)
     text(sql_create_database, metadata.bind).execute(database=database_name)
     #text(sql_revoke_permissions, metadata.bind).execute(user = user_name)
     # Restores the old database
     metadata.bind = bind
     return database_name, user_name, password

def upgrade(engine):
    c = engine.connect()

    files = list(c.execute(r"select files.id, "
                           "        files.filename, "
                           "        files.title, "
                           "        content_items.created_by "
                           " from files"
                           " join content_items on content_items.id = files.id"
                           ))

    for id, filename, title, who in files:
        engine.echo = True
        c.execute(text("SET ututi.active_user TO :uid"), uid=who)
        new_filename = cleanupFileName(filename)
        new_title = cleanupFileName(title)
        if (new_filename != filename or
            new_title != title):
            c.execute(text("update files"
                           "   set title = :title,"
                           "   filename  = :filename"
                           "   where id  = :id"),
                      title=new_title,
                      filename=new_filename,
                      id=id)

def rebuild_spatial_db():
    conn = spatial_model.get_spatial_db_connection()
    res = conn.execute(text('SELECT id FROM collection WHERE id = :id'),
                       {'id': TEST_COLLECTION_ID})
    if res.rowcount:
        conn.execute(text('DELETE FROM collection WHERE id = :id'),
                       {'id': TEST_COLLECTION_ID})

    def test_within_distance(self):
        """
        Because SDO_WITHIN_DISTANCE requires a spatial index for the geometry used
        as first parameter, we have to insert out test geometries into tables,
        unlike to the other databases.

        Note that Oracle uses meter as unit for the tolerance value for geodetic coordinate
        systems (like 4326)!
        """
        # test if SDO_functions._within_distance is called correctly
        eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location, 'POINT(0 0)', 0)).count(), 1)
        eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location, 'POINT(0 0)', 0.1)).count(), 1)
        eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location, 'POINT(9 9)', 100000)).count(), 0)

        eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location,
                                                       'Polygon((-5 -5, 5 -5, 5 5, -5 5, -5 -5))', 0)).count(), 3)

        eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location,
                                                       'Polygon((-10 -10, 10 -10, 10 10, -10 10, -10 -10))', 0)).count(), 4)

        eq_(session.query(Spot).filter(functions._within_distance(Spot.spot_location,
                                                       'Polygon((-10 -10, 10 -10, 10 10, -10 10, -10 -10))', 200000)).count(), 5)

        # test if SDO_GEOM.functions._within_distance is called correctly
        eq_(session.scalar(select([text('1')], from_obj=['dual']).where(
                                                    functions._within_distance('POINT(0 0)', 'POINT(0 0)', 0,
                                                                    {'tol' : 0.00000005}))), 1)
        eq_(session.scalar(select([text('1')], from_obj=['dual']).where(
                                                    functions._within_distance('POINT(0 0)', 'POINT(0 0)', 0,
                                                                    {'dim1' : text(diminfo),
                                                                     'dim2' : text(diminfo)}))), 1)

def mass_transit_cleanup():
    """Delete and vacuum mass transit live location data older than configured
    interval, for example
        MASS_TRANSIT_LIVE_KEEP_DAYS = 7"""

    # keep all data if nothing configured
    days = app.config.get("MASS_TRANSIT_LIVE_KEEP_DAYS")
    if not days:
        return

    # Snap deletion to daystart; be noisy as these can take quite a long time.
    # Also delete martians from the future, no use in preferring those forever.
    log.info("Deleting mass_transit_data older than %d days...", days)
    query = text("""
        DELETE FROM mass_transit_data
        WHERE time < date_trunc('day', now() - interval ':days days')
           OR time > date_trunc('day', now() + interval '2 days')""")
    delrows = db.engine.execute(query, days=days).rowcount
    log.info("Deleted %d rows of mass_transit_data.", delrows)
    if not delrows:
        return

    # vacuum to reuse space; cannot be wrapped in transaction, so another conn
    log.info("Vacuuming and analyzing mass_transit_data...")
    query = text("VACUUM ANALYZE mass_transit_data")
    conn = db.engine.connect().execution_options(isolation_level="AUTOCOMMIT")
    conn.execute(query)
    conn.close()
    log.info("Vacuuming and analyzing mass_transit_data complete.")

 def run(self, **kw):
     conn = safe_engine().connect()
     for rs in chain(self.network.relation_schemata,
         self.network.entity_schemata):
         q = rs.cls.view()
         conn.execute(text(str(q)), current=True)
     return conn.execute(text(self.query), **kw)

def init_udl_tenant_sequences(udl2_conf):
    # Create and sync sequence for each tenant on udl database if it doesn't exist
    with get_udl_connection() as udl_conn:
        all_tenants = udl2_conf.get(PRODUCTION_NAMESPACE)
        udl_schema_name = udl2_conf.get(UDL_NAMESPACE).get(Constants.DB_SCHEMA)
        # dict to keep track of tenant sequence values for each tenant defined in the ini
        all_tenant_sequences = {}
        for tenant in all_tenants:
            tenant_seq_name = Constants.TENANT_SEQUENCE_NAME(tenant)
            tenant_schema_name = all_tenants.get(tenant).get(Constants.DB_SCHEMA)
            # unique identifier for each tenant
            key = all_tenants.get(tenant).get(Constants.URL) + ':' + tenant_schema_name
            # check if we have already visited the tenant prod schema
            if not key in all_tenant_sequences:
                with get_prod_connection(tenant) as prod_conn:
                    prod_seq_result = prod_conn.execute(text("select nextval(\'{schema_name}.{seq_name} \')".
                                                             format(schema_name=tenant_schema_name,
                                                                    seq_name=Constants.SEQUENCE_NAME)))
                    all_tenant_sequences[key] = prod_seq_result.fetchone()[0]
            # check if the global tenant sequence exists in udl database
            if not sequence_exists(udl_conn, tenant_seq_name):
                # create sequence if does not exist
                udl_conn.execute(CreateSequence(Sequence(name=tenant_seq_name, increment=1)))
            # update and set the current val for the tenant sequence
            udl_conn.execute(text("select setval(\'{schema_name}.{seq_name} \', {value}, {called})".
                                  format(schema_name=udl_schema_name, seq_name=tenant_seq_name,
                                         value=all_tenant_sequences[key], called=True)))

 def test_protocol_count_filter_box(self):
     """Get the feature count with a box as filter"""
     proto = Protocol(session, Spot)
     request = FakeRequest({})
     
     request.params['bbox'] = '-10,-10,10,10'
     eq_(proto.count(request), '4')
     
     request.params['tolerance'] = '200000'
     eq_(proto.count(request), '5')
     
     # query features that are inside a bbox that uses a different CRS
     # note that we either have to specify a tolerance ('tol') or 
     # dimension information ('dim1' and 'dim2')
     filter = create_default_filter(request, Spot, additional_params={'tol': '0.005'})
     request.params['bbox'] = '-12.3364241712925,-10.0036833569465,7.66304367998925,9.9979519038951'
     request.params['epsg'] = '2210'
     request.params['tolerance'] = '0'
     eq_(proto.count(request, filter=filter), '5')
     
     # dimension information array for 54004
     # see http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_objrelschema.htm#i1010905
     diminfo = "MDSYS.SDO_DIM_ARRAY("\
         "MDSYS.SDO_DIM_ELEMENT('LONGITUDE', -20037508, 20037508, 0.005),"\
         "MDSYS.SDO_DIM_ELEMENT('LATITUDE', -19929239, 19929239, 0.005)"\
         ")"
     
     request.params['bbox'] = '-975862.822682856,-999308.345117013,1027887.98627823,999373.702609189'
     request.params['epsg'] = '54004' # Oracles SRID number for World Mercator
     filter = create_default_filter(request, Spot, 
                                    additional_params={'dim1': text(diminfo),
                                                       'dim2' : text(diminfo)})
     eq_(proto.count(request, filter=filter), '3')

def upgrade():
    op.create_table(
        'User',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('is_created', sa.TIMESTAMP(), server_default=text('CURRENT_TIMESTAMP'), nullable=False),
        sa.Column('is_updated', sa.TIMESTAMP(), server_default=text('CURRENT_TIMESTAMP'), nullable=False),
        sa.Column('is_deleted', sa.TIMESTAMP(), nullable=True),
        sqlite_autoincrement=True,
        )

    op.create_table(
        'UserAttribute',
        sa.Column('user_id', sa.Integer, primary_key=True),
        sa.Column('is_created', sa.TIMESTAMP(), server_default=text('CURRENT_TIMESTAMP'), nullable=False),
        sa.Column('is_updated', sa.TIMESTAMP(), server_default=text('CURRENT_TIMESTAMP'), nullable=False),
        sa.Column('name', sa.Unicode, default=u''),
        sa.Column('email', sa.Unicode, default=u''),
        sa.Column('password', sa.Unicode, default=u''),
        sa.Column('first_name', sa.Unicode, default=u''),
        sa.Column('middle_name', sa.Unicode, default=u''),
        sa.Column('last_name', sa.Unicode, default=u''),
        sa.Column('address', sa.Unicode, default=u''),
        sa.Column('birth_date', sa.DateTime, nullable=True),
        sa.Column('contact', sa.Unicode, default=u''),
        )

 def update_attributes(self, username, hostname, user_attrs):
     """Change the attributes of one existing user."""
     LOG.debug("Changing the user attributes")
     LOG.debug("User is %s" % username)
     user = self._get_user(username, hostname)
     db_access = set()
     grantee = set()
     with LocalSqlClient(get_engine()) as client:
         q = query.Query()
         q.columns = ["grantee", "table_schema"]
         q.tables = ["information_schema.SCHEMA_PRIVILEGES"]
         q.group = ["grantee", "table_schema"]
         q.where = ["privilege_type != 'USAGE'"]
         t = text(str(q))
         db_result = client.execute(t)
         for db in db_result:
             grantee.add(db['grantee'])
             if db['grantee'] == "'%s'@'%s'" % (user.name, user.host):
                 db_name = db['table_schema']
                 db_access.add(db_name)
     with LocalSqlClient(get_engine()) as client:
         uu = query.UpdateUser(user.name, host=user.host,
                               clear=user_attrs.get('password'),
                               new_user=user_attrs.get('name'),
                               new_host=user_attrs.get('host'))
         t = text(str(uu))
         client.execute(t)
         if user_attrs.get('name') is not None:
             if user_attrs['name'] not in grantee:
                 if user_attrs.get('host') is None:
                     host = user.host
                 else:
                     host = user_attrs.get('host')
                 self.grant_access(user_attrs['name'], host, db_access)

 def update_attributes(self, username, hostname, user_attrs):
     """Change the attributes of an existing user."""
     LOG.debug("Changing user attributes for user %s." % username)
     user = self._get_user(username, hostname)
     db_access = set()
     grantee = set()
     with self.local_sql_client(self.mysql_app.get_engine()) as client:
         q = sql_query.Query()
         q.columns = ["grantee", "table_schema"]
         q.tables = ["information_schema.SCHEMA_PRIVILEGES"]
         q.group = ["grantee", "table_schema"]
         q.where = ["privilege_type != 'USAGE'"]
         t = text(str(q))
         db_result = client.execute(t)
         for db in db_result:
             grantee.add(db['grantee'])
             if db['grantee'] == "'%s'@'%s'" % (user.name, user.host):
                 db_name = db['table_schema']
                 db_access.add(db_name)
     with self.local_sql_client(self.mysql_app.get_engine()) as client:
         uu = sql_query.UpdateUser(user.name, host=user.host,
                                   clear=user_attrs.get('password'),
                                   new_user=user_attrs.get('name'),
                                   new_host=user_attrs.get('host'))
         t = text(str(uu))
         client.execute(t)
         uname = user_attrs.get('name') or username
         host = user_attrs.get('host') or hostname
         find_user = "'%s'@'%s'" % (uname, host)
         if find_user not in grantee:
             self.grant_access(uname, host, db_access)

 def list_users(self):
     """List users that have access to the database"""
     LOG.debug(_("---Listing Users---"))
     users = []
     client = LocalSqlClient(get_engine())
     with client:
         mysql_user = models.MySQLUser()
         t = text("""select User from mysql.user where host !=
                  'localhost';""")
         result = client.execute(t)
         LOG.debug("result = " + str(result))
         for row in result:
             LOG.debug("user = " + str(row))
             mysql_user = models.MySQLUser()
             mysql_user.name = row['User']
             # Now get the databases
             t = text("""SELECT grantee, table_schema
                         from information_schema.SCHEMA_PRIVILEGES
                         group by grantee, table_schema;""")
             db_result = client.execute(t)
             for db in db_result:
                 matches = re.match("^'(.+)'@", db['grantee'])
                 if matches is not None and \
                    matches.group(1) == mysql_user.name:
                     mysql_db = models.MySQLDatabase()
                     mysql_db.name = db['table_schema']
                     mysql_user.databases.append(mysql_db.serialize())
             users.append(mysql_user.serialize())
     LOG.debug("users = " + str(users))
     return users

    def enable_root(self, root_password=None):
        """Enable the root user global access and/or
           reset the root password.
        """
        user = models.MySQLRootUser(root_password)
        with self.local_sql_client(self.mysql_app.get_engine()) as client:
            print(client)
            try:
                cu = sql_query.CreateUser(user.name, host=user.host)
                t = text(str(cu))
                client.execute(t, **cu.keyArgs)
            except exc.OperationalError as err:
                # Ignore, user is already created, just reset the password
                # TODO(rnirmal): More fine grained error checking later on
                LOG.debug(err)
        with self.local_sql_client(self.mysql_app.get_engine()) as client:
            print(client)
            uu = sql_query.UpdateUser(user.name, host=user.host,
                                      clear=user.password)
            t = text(str(uu))
            client.execute(t)

            LOG.debug("CONF.root_grant: %s CONF.root_grant_option: %s." %
                      (CONF.root_grant, CONF.root_grant_option))

            g = sql_query.Grant(permissions=CONF.root_grant,
                                user=user.name,
                                host=user.host,
                                grant_option=CONF.root_grant_option,
                                clear=user.password)

            t = text(str(g))
            client.execute(t)
            return user.serialize()

 def update_attributes(self, username, hostname, user_attrs):
     """Change the attributes of one existing user."""
     LOG.debug("Changing the user attributes")
     LOG.debug("User is %s" % username)
     user = self._get_user(username, hostname)
     db_access = set()
     grantee = set()
     with LocalSqlClient(get_engine()) as client:
         q = sql_query.Query()
         q.columns = ["grantee", "table_schema"]
         q.tables = ["information_schema.SCHEMA_PRIVILEGES"]
         q.group = ["grantee", "table_schema"]
         q.where = ["privilege_type != 'USAGE'"]
         t = text(str(q))
         db_result = client.execute(t)
         for db in db_result:
             grantee.add(db["grantee"])
             if db["grantee"] == "'%s'@'%s'" % (user.name, user.host):
                 db_name = db["table_schema"]
                 db_access.add(db_name)
     with LocalSqlClient(get_engine()) as client:
         uu = sql_query.UpdateUser(
             user.name,
             host=user.host,
             clear=user_attrs.get("password"),
             new_user=user_attrs.get("name"),
             new_host=user_attrs.get("host"),
         )
         t = text(str(uu))
         client.execute(t)
         uname = user_attrs.get("name") or username
         host = user_attrs.get("host") or hostname
         find_user = "'%s'@'%s'" % (uname, host)
         if find_user not in grantee:
             self.grant_access(uname, host, db_access)

def cluster_legs(limit):
    """New leg ends and places are clustered live by triggers; this can be used
    to cluster data created earlier."""

    print("cluster_legs up to", limit)

    with db.engine.begin() as t:
        t.execute(text("SELECT legs_cluster(:limit)"), limit=limit)
        t.execute(text("SELECT leg_ends_cluster(:limit)"), limit=limit)

def upgrade(migrate_engine):
    meta = MetaData()
    meta.bind = migrate_engine

    t = Table(TABLE_NAME, meta, autoload=True)
    default = (expression.text('0') if migrate_engine.name == 'sqlite'
               else expression.text('false'))
    preserve_ephemeral_col = Column(COLUMN_NAME, Boolean,
                                    server_default=default)
    t.create_column(preserve_ephemeral_col)

    def has_table(self, connection, table_name, schema=None):

        if schema is None:
            schema=self.default_schema_name

        stmt = select([column('tablename')],
                      from_obj=[text('dbc.tablesvx')]).where(
                          and_(text('creatorname=:user'),
                               text('tablename=:name')))
        res = connection.execute(stmt, user=schema, name=table_name).fetchone()
        return res is not None

def drop_all_tables_and_sequences():
    for table in get_table_list_from_db():
        try:
            execute(text("DROP TABLE %s CASCADE" % table))
        except (SQLAlchemyError, e):
            print(e)

    for seq in get_seq_list_from_db():
        try:
            execute(text("DROP SEQUENCE %s CASCADE" % table))
        except (SQLAlchemyError, e):
            print(e)

 def __delete_mysql_resource(self, database_name, user_name):
     bind = metadata.bind
     # Creates a connection with permission to create users and databases
     metadata.bind = self.__generate_connection_uri(self.__root_user, self.__root_password)
     # Generate data and queries
     sql_delete_database = "DROP DATABASE `:database`;"
     sql_delete_user = "DROP USER :[email protected]"
     # Perform the queries
     text(sql_delete_database, metadata.bind).execute(database = database_name)
     text(sql_delete_user, metadata.bind).execute(user = user_name)
     # Restores the old database_name
     metadata.bind = bind