def load_dialect_impl(self, dialect: DefaultDialect) -> TypeEngine: # RNC
if dialect.name == 'mysql':
return dialect.type_descriptor(
sqlalchemy.dialects.mysql.DATETIME(fsp=6))
elif dialect.name == 'mssql': # Microsoft SQL Server
return dialect.type_descriptor(sqlalchemy.dialects.mssql.DATETIME2)
else:
return dialect.type_descriptor(self.impl)
def load_dialect_impl(self, dialect: DefaultDialect) -> TypeEngine: # RNC
if dialect.name == 'mysql':
return dialect.type_descriptor(
sqlalchemy.dialects.mysql.DATETIME(fsp=6))
elif dialect.name == 'mssql': # Microsoft SQL Server
return dialect.type_descriptor(sqlalchemy.dialects.mssql.DATETIME2)
else:
return dialect.type_descriptor(self.impl)
def before_exec(self, conn, clauseelement, multiparams, params):
if not isinstance(clauseelement, (dml.Delete, dml.Update)):
return
if multiparams and multiparams[0]:
return
query = select([clauseelement.table])
if clauseelement._whereclause is not None:
query = query.where(clauseelement._whereclause)
stmt_redo = clauseelement.compile(dialect=DefaultDialect())
self.session.add(
RedoAction(
object_type=self.object_type,
stack_id=self.stack_id,
capture_id=self.last_capture + 1,
stmt=str(stmt_redo),
params=stmt_redo.params,
))
if isinstance(clauseelement, dml.Delete):
for row in conn.execute(query):
stmt_undo = (dml.Insert(clauseelement.table).values(
**{k: v
for (k, v) in row.items()
if v is not None}).compile(dialect=DefaultDialect()))
self.session.add(
UndoAction(
object_type=self.object_type,
stack_id=self.stack_id,
capture_id=self.last_capture + 1,
stmt=str(stmt_undo),
params=stmt_undo.params,
))
elif isinstance(clauseelement, dml.Update):
for row in conn.execute(query):
stmt_undo = (dml.Update(clauseelement.table).values(
**{
column.name: row[column.name]
for column in clauseelement.parameters.keys()
}).where(
and_(*[
column.__eq__(row[column.name]) for column in
clauseelement.table.primary_key.columns.values()
])).compile(dialect=DefaultDialect()))
self.session.add(
UndoAction(
object_type=self.object_type,
stack_id=self.stack_id,
capture_id=self.last_capture + 1,
stmt=str(stmt_undo),
params=stmt_undo.params,
))
def after_exec(self, conn, clauseelement, multiparams, params, result):
if isinstance(clauseelement, dml.Insert):
new_pk = dict(
zip(
clauseelement.table.primary_key.columns.keys(),
result.inserted_primary_key,
))
where_clause = and_(*[
column.__eq__(value) for (column, value) in zip(
clauseelement.table.primary_key.columns.values(),
result.inserted_primary_key,
)
])
stmt_redo = clauseelement.values({
**{c.name: c.default.arg
for c in result.prefetch_cols()},
**{
c.name: c.server_default.arg
for c in result.postfetch_cols()
},
**{k: v
for (k, v) in multiparams[0].items() if v is not None},
**new_pk,
}).compile(dialect=DefaultDialect())
stmt_undo = (delete(
clauseelement.table).where(where_clause).compile(
dialect=DefaultDialect()))
self.session.add(
RedoAction(
object_type=self.object_type,
stack_id=self.stack_id,
capture_id=self.last_capture + 1,
stmt=str(stmt_redo),
params=stmt_redo.params,
))
self.session.add(
UndoAction(
object_type=self.object_type,
stack_id=self.stack_id,
capture_id=self.last_capture + 1,
stmt=str(stmt_undo),
params=stmt_undo.params,
))
def render_python_code(
up_or_down_op,
sqlalchemy_module_prefix="sa.",
alembic_module_prefix="op.",
render_as_batch=False,
imports=(),
render_item=None,
migration_context=None,
):
"""Render Python code given an :class:`.UpgradeOps` or
:class:`.DowngradeOps` object.
This is a convenience function that can be used to test the
autogenerate output of a user-defined :class:`.MigrationScript` structure.
"""
opts = {
"sqlalchemy_module_prefix": sqlalchemy_module_prefix,
"alembic_module_prefix": alembic_module_prefix,
"render_item": render_item,
"render_as_batch": render_as_batch,
}
if migration_context is None:
from ..runtime.migration import MigrationContext
from sqlalchemy.engine.default import DefaultDialect
migration_context = MigrationContext.configure(dialect=DefaultDialect())
autogen_context = AutogenContext(migration_context, opts=opts)
autogen_context.imports = set(imports)
return render._indent(render._render_cmd_body(up_or_down_op, autogen_context))
def process_cursor_execute(self, statement, parameters, context, executemany):
if not context:
return
_received_parameters = context.compiled_parameters
# recompile from the context, using the default dialect
compiled = context.compiled.statement.\
compile(dialect=DefaultDialect(), column_keys=context.compiled.column_keys)
_received_statement = re.sub(r'\n', '', str(compiled))
equivalent = self.statement == _received_statement
if self.params:
if util.callable(self.params):
params = self.params(context)
else:
params = self.params
if not isinstance(params, list):
params = [params]
# do a positive compare only
for param, received in zip(params, _received_parameters):
for k, v in param.iteritems():
if k not in received or received[k] != v:
equivalent = False
break
else:
params = {}
self._result = equivalent
if not self._result:
self._errmsg = "Testing for compiled statement %r partial params %r, " \
"received %r with params %r" % (self.statement, params, _received_statement, _received_parameters)
def process_cursor_execute(self, statement, parameters, context,
executemany):
if not context:
return
_received_parameters = list(context.compiled_parameters)
# recompile from the context, using the default dialect
compiled = \
context.compiled.statement.compile(dialect=DefaultDialect(),
column_keys=context.compiled.column_keys)
_received_statement = re.sub(r'\n', '', str(compiled))
equivalent = self.statement == _received_statement
if self.params:
if util.callable(self.params):
params = self.params(context)
else:
params = self.params
if not isinstance(params, list):
params = [params]
all_params = list(params)
all_received = list(_received_parameters)
while params:
param = dict(params.pop(0))
for k, v in context.compiled.params.iteritems():
param.setdefault(k, v)
if param not in _received_parameters:
equivalent = False
break
else:
_received_parameters.remove(param)
if _received_parameters:
equivalent = False
else:
params = {}
all_params = {}
all_received = []
self._result = equivalent
if not self._result:
print 'Testing for compiled statement %r partial params '\
'%r, received %r with params %r' % (self.statement,
all_params, _received_statement, all_received)
self._errmsg = \
'Testing for compiled statement %r partial params %r, '\
'received %r with params %r' % (self.statement,
all_params, _received_statement, all_received)
def __new__(cls, dialect):
"""
Gets the given SQL dialect instance by name.
Args:
dialect (str): The SQL dialect name
Returns:
type: The SQL dialect instance
"""
if dialect == 'default':
return DefaultDialect()
if dialect in ['firebird', 'mssql', 'mysql', 'oracle', 'postgresql',
'sqlite', 'sybase']:
return dialects.registry.load(dialect)()
raise UnsupportedDialectError('Unsupported dialect: {}'.format(dialect))
def test_render_server_default_non_native_boolean(self):
c = Column('updated_at',
Boolean(),
server_default=false(),
nullable=False)
dialect = DefaultDialect()
autogen_context = {
'opts': {
'sqlalchemy_module_prefix': 'sa.',
'alembic_module_prefix': 'op.',
},
'dialect': dialect
}
result = autogenerate.render._render_column(c, autogen_context)
eq_ignore_whitespace(
result, 'sa.Column(\'updated_at\', sa.Boolean(), '
'server_default=sa.text(!U\'0\'), '
'nullable=False)')
def getDialect(dialect):
'''
Get the given SQL dialect instance by name.
Arguments:
dialect (str): The SQL dialect name
Returns:
type: The SQL dialect instance
'''
if dialect == 'default':
return DefaultDialect()
if dialect in [
'firebird', 'mssql', 'mysql', 'oracle', 'postgresql', 'sqlite',
'sybase'
]:
return dialects.registry.load(dialect)()
raise Exception('Unsupported dialect: {}'.format(dialect))
def __init__(self, *args, **kwargs):
DefaultDialect.__init__(self, *args, **kwargs)
self.convert_unicode = True
def load_dialect_impl(self, dialect: DefaultDialect) -> Any:
return dialect.type_descriptor(CHAR(32))
def load_dialect_impl(self, dialect: DefaultDialect) -> Any:
return dialect.type_descriptor(types.TEXT())
def load_dialect_impl(self, dialect: DefaultDialect) -> Any:
return (dialect.type_descriptor(CHAR(36)) if self.uuid_format
== "string" else dialect.type_descriptor(CHAR(32)))
class BaseDb:
"""Base functionality common to all types of sqlalchemy databases that we support."""
# Subclasses should override with a specific IdentifierPreparer, or identifier quoting will not work properly.
preparer = IdentifierPreparer(DefaultDialect())
@classmethod
def create_engine(cls, spec):
"""Create an engine for connecting to the database specified."""
raise NotImplementedError()
@classmethod
def quote(cls, identifier):
"""Conditionally quote the given identifier (ie, if it is a keyword or contains reserved characters."""
# Subclasses should override cls.preparer with a specific IdentifierPreparer.
return cls.preparer.quote(identifier)
@classmethod
def quote_table(cls, table_name, db_schema=None):
return cls.preparer.format_table(sa.table(table_name,
schema=db_schema))
@classmethod
def list_tables(cls, sess, db_schema=None):
"""
Find all the user tables (not system tables) in the database (or in a specific db_schema).
Returns a dict of {table_name: table_title}
"""
raise NotImplementedError()
@classmethod
def db_schema_searchpath(cls, sess):
"""Returns a list of the db_schemas that the connection is configured to search in by default."""
raise NotImplementedError()
@classmethod
def _pool_class(cls):
# Ordinarily, sqlalchemy engine's maintain a pool of connections ready to go.
# When running tests, we run lots of kart commands, and each command creates an engine, and each engine
# maintains a pool. This can quickly exhaust the database's allowed connection limit.
# One fix would be to share engines between kart commands run during tests that are connecting to the same DB.
# But this fix is simpler for now: disable the pool during testing.
return NullPool if "PYTEST_CURRENT_TEST" in os.environ else None
@classmethod
def _replace_localhost_with_ip(cls, url_netloc):
def _get_localhost_ip(*args, **kwargs):
return socket.gethostbyname("localhost")
return re.sub(r"\blocalhost\b", _get_localhost_ip, url_netloc)
@classmethod
def _append_query_to_url(cls, uri, new_query_dict):
url = urlsplit(uri)
url_query = cls._append_to_query(url.query, new_query_dict)
return urlunsplit([url.scheme, url.netloc, url.path, url_query, ""])
@classmethod
def _append_to_query(cls, existing_query, new_query_dict):
query_dict = parse_qs(existing_query)
# ignore new keys if they're already set in the querystring
return urlencode({**new_query_dict, **query_dict}, doseq=True)
@classmethod
def drop_all_in_schema(cls, sess, db_schema):
"""Drops all tables, routines, and sequences in schema db_schema."""
for thing in ("table", "routine", "sequence"):
cls._drop_things_in_schema(cls, sess, db_schema, thing)
def _drop_things_in_schema(cls, sess, db_schema, thing):
r = sess.execute(
sa.text(
f"SELECT {thing}_name FROM information_schema.{thing}s WHERE {thing}_schema=:db_schema;"
),
{"db_schema": db_schema},
)
thing_identifiers = ", ".join(
(cls.quote_table(row[0], db_schema=db_schema) for row in r))
if thing_identifiers:
sess.execute(f"DROP {thing} IF EXISTS {thing_identifiers};")
from sqlalchemy.engine.default import DefaultDialect default = DefaultDialect()
