def __init__(self, *args, **kwargs):
super(DatabaseWrapper, self).__init__(*args, **kwargs)
if 'OPTIONS' in self.settings_dict:
self.MARS_Connection = self.settings_dict['OPTIONS'].get(
'MARS_Connection', False)
self.datefirst = self.settings_dict['OPTIONS'].get('datefirst', 7)
self.unicode_results = self.settings_dict['OPTIONS'].get(
'unicode_results', False)
if _DJANGO_VERSION >= 13:
self.features = DatabaseFeatures(self)
else:
raise Exception("The version is %s" % _DJANGO_VERSION)
self.features = DatabaseFeatures()
self.client = DatabaseClient(self)
self.creation = DatabaseCreation(self)
self.introspection = DatabaseIntrospection(self)
if _DJANGO_VERSION >= 12:
self.ops = DatabaseOperations(self)
self.validation = BaseDatabaseValidation(self)
else:
self.ops = DatabaseOperations()
self.validation = BaseDatabaseValidation()
self.connection = None
def __init__(self, *args, **kwargs):
super(DatabaseWrapper, self).__init__(*args, **kwargs)
if 'OPTIONS' in self.settings_dict:
self.MARS_Connection = self.settings_dict['OPTIONS'].get('MARS_Connection', False)
self.datefirst = self.settings_dict['OPTIONS'].get('datefirst', 7)
self.unicode_results = self.settings_dict['OPTIONS'].get('unicode_results', False)
if _DJANGO_VERSION >= 13:
self.features = DatabaseFeatures(self)
else:
raise Exception("The version is %s" % _DJANGO_VERSION)
self.features = DatabaseFeatures()
self.client = DatabaseClient(self)
self.creation = DatabaseCreation(self)
self.introspection = DatabaseIntrospection(self)
if _DJANGO_VERSION >= 12:
self.ops = DatabaseOperations(self)
self.validation = BaseDatabaseValidation(self)
else:
self.ops = DatabaseOperations()
self.validation = BaseDatabaseValidation()
self.connection = None
def __init__(self, *args, **kwargs):
super(DatabaseWrapper, self).__init__(*args, **kwargs)
opts = self.settings_dict["OPTIONS"]
# capability for multiple result sets or cursors
self.supports_mars = False
self.open_cursor = None
# Some drivers need unicode encoded as UTF8. If this is left as
# None, it will be determined based on the driver, namely it'll be
# False if the driver is a windows driver and True otherwise.
#
# However, recent versions of FreeTDS and pyodbc (0.91 and 3.0.6 as
# of writing) are perfectly okay being fed unicode, which is why
# this option is configurable.
if 'driver_needs_utf8' in opts:
self.driver_charset = 'utf-8'
else:
self.driver_charset = opts.get('driver_charset', None)
# data type compatibility to databases created by old django-pyodbc
self.use_legacy_datetime = opts.get('use_legacy_datetime', False)
# interval to wait for recovery from network error
interval = opts.get('connection_recovery_interval_msec', 0.0)
self.connection_recovery_interval_msec = float(interval) / 1000
# make lookup operators to be collation-sensitive if needed
collation = opts.get('collation', None)
if collation:
self.operators = dict(self.__class__.operators)
ops = {}
for op in self.operators:
sql = self.operators[op]
if sql.startswith('LIKE '):
ops[op] = '%s COLLATE %s' % (sql, collation)
self.operators.update(ops)
self.features = DatabaseFeatures(self)
self.ops = DatabaseOperations(self)
self.client = DatabaseClient(self)
self.creation = DatabaseCreation(self)
self.introspection = DatabaseIntrospection(self)
self.validation = BaseDatabaseValidation(self)
def __init__(self, *args, **kwargs):
super(DatabaseWrapper, self).__init__(*args, **kwargs)
opts = self.settings_dict["OPTIONS"]
# capability for multiple result sets or cursors
self.supports_mars = False
# Some drivers need unicode encoded as UTF8. If this is left as
# None, it will be determined based on the driver, namely it'll be
# False if the driver is a windows driver and True otherwise.
#
# However, recent versions of FreeTDS and pyodbc (0.91 and 3.0.6 as
# of writing) are perfectly okay being fed unicode, which is why
# this option is configurable.
if 'driver_needs_utf8' in opts:
self.driver_charset = 'utf-8'
else:
self.driver_charset = opts.get('driver_charset', None)
# data type compatibility to databases created by old django-pyodbc
self.use_legacy_datetime = opts.get('use_legacy_datetime', False)
# interval to wait for recovery from network error
interval = opts.get('connection_recovery_interval_msec', 0.0)
self.connection_recovery_interval_msec = float(interval) / 1000
# make lookup operators to be collation-sensitive if needed
collation = opts.get('collation', None)
if collation:
self.operators = dict(self.__class__.operators)
ops = {}
for op in self.operators:
sql = self.operators[op]
if sql.startswith('LIKE '):
ops[op] = '%s COLLATE %s' % (sql, collation)
self.operators.update(ops)
self.features = DatabaseFeatures(self)
self.ops = DatabaseOperations(self)
self.client = DatabaseClient(self)
self.creation = DatabaseCreation(self)
self.introspection = DatabaseIntrospection(self)
self.validation = BaseDatabaseValidation(self)
class DatabaseWrapper(BaseDatabaseWrapper):
drv_name = None
driver_needs_utf8 = None
MARS_Connection = False
unicode_results = False
datefirst = 7
# Collations: http://msdn2.microsoft.com/en-us/library/ms184391.aspx
# http://msdn2.microsoft.com/en-us/library/ms179886.aspx
# T-SQL LIKE: http://msdn2.microsoft.com/en-us/library/ms179859.aspx
# Full-Text search: http://msdn2.microsoft.com/en-us/library/ms142571.aspx
# CONTAINS: http://msdn2.microsoft.com/en-us/library/ms187787.aspx
# FREETEXT: http://msdn2.microsoft.com/en-us/library/ms176078.aspx
operators = {
# Since '=' is used not only for string comparision there is no way
# to make it case (in)sensitive. It will simply fallback to the
# database collation.
'exact': '= %s',
'iexact': "= UPPER(%s)",
'contains': "LIKE %s ESCAPE '\\' COLLATE " + collation,
'icontains': "LIKE UPPER(%s) ESCAPE '\\' COLLATE "+ collation,
'gt': '> %s',
'gte': '>= %s',
'lt': '< %s',
'lte': '<= %s',
'startswith': "LIKE %s ESCAPE '\\' COLLATE " + collation,
'endswith': "LIKE %s ESCAPE '\\' COLLATE " + collation,
'istartswith': "LIKE UPPER(%s) ESCAPE '\\' COLLATE " + collation,
'iendswith': "LIKE UPPER(%s) ESCAPE '\\' COLLATE " + collation,
# TODO: remove, keep native T-SQL LIKE wildcards support
# or use a "compatibility layer" and replace '*' with '%'
# and '.' with '_'
'regex': 'LIKE %s COLLATE ' + collation,
'iregex': 'LIKE %s COLLATE ' + collation,
# TODO: freetext, full-text contains...
}
def __init__(self, *args, **kwargs):
super(DatabaseWrapper, self).__init__(*args, **kwargs)
if 'OPTIONS' in self.settings_dict:
self.MARS_Connection = self.settings_dict['OPTIONS'].get('MARS_Connection', False)
self.datefirst = self.settings_dict['OPTIONS'].get('datefirst', 7)
self.unicode_results = self.settings_dict['OPTIONS'].get('unicode_results', False)
if _DJANGO_VERSION >= 13:
self.features = DatabaseFeatures(self)
else:
raise Exception("The version is %s" % _DJANGO_VERSION)
self.features = DatabaseFeatures()
self.client = DatabaseClient(self)
self.creation = DatabaseCreation(self)
self.introspection = DatabaseIntrospection(self)
if _DJANGO_VERSION >= 12:
self.ops = DatabaseOperations(self)
self.validation = BaseDatabaseValidation(self)
else:
self.ops = DatabaseOperations()
self.validation = BaseDatabaseValidation()
self.connection = None
def _cursor(self):
new_conn = False
settings_dict = self.settings_dict
db_str, user_str, passwd_str, port_str = None, None, None, None
if _DJANGO_VERSION >= 12:
options = settings_dict['OPTIONS']
if settings_dict['NAME']:
db_str = settings_dict['NAME']
if settings_dict['HOST']:
host_str = settings_dict['HOST']
else:
host_str = 'localhost'
if settings_dict['USER']:
user_str = settings_dict['USER']
if settings_dict['PASSWORD']:
passwd_str = settings_dict['PASSWORD']
if settings_dict['PORT']:
port_str = settings_dict['PORT']
else:
options = settings_dict['DATABASE_OPTIONS']
if settings_dict['DATABASE_NAME']:
db_str = settings_dict['DATABASE_NAME']
if settings_dict['DATABASE_HOST']:
host_str = settings_dict['DATABASE_HOST']
else:
host_str = 'localhost'
if settings_dict['DATABASE_USER']:
user_str = settings_dict['DATABASE_USER']
if settings_dict['DATABASE_PASSWORD']:
passwd_str = settings_dict['DATABASE_PASSWORD']
if settings_dict['DATABASE_PORT']:
port_str = settings_dict['DATABASE_PORT']
if self.connection is None:
new_conn = True
if not db_str:
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured('You need to specify NAME in your Django settings file.')
cstr_parts = []
if 'driver' in options:
driver = options['driver']
else:
if os.name == 'nt':
driver = 'SQL Server'
else:
driver = 'FreeTDS'
if 'dsn' in options:
cstr_parts.append('DSN=%s' % options['dsn'])
else:
# Only append DRIVER if DATABASE_ODBC_DSN hasn't been set
cstr_parts.append('DRIVER={%s}' % driver)
if os.name == 'nt' or driver == 'FreeTDS' and \
options.get('host_is_server', False):
if port_str:
host_str += ',%s' % port_str
cstr_parts.append('SERVER=%s' % host_str)
else:
cstr_parts.append('SERVERNAME=%s' % host_str)
if user_str:
cstr_parts.append('UID=%s;PWD=%s' % (user_str, passwd_str))
else:
if driver in ('SQL Server', 'SQL Native Client'):
cstr_parts.append('Trusted_Connection=yes')
else:
cstr_parts.append('Integrated Security=SSPI')
cstr_parts.append('DATABASE=%s' % db_str)
if self.MARS_Connection:
cstr_parts.append('MARS_Connection=yes')
if 'extra_params' in options:
cstr_parts.append(options['extra_params'])
connstr = ';'.join(cstr_parts)
autocommit = options.get('autocommit', False)
if self.unicode_results:
self.connection = Database.connect(connstr, \
autocommit=autocommit, \
unicode_results='True')
else:
self.connection = Database.connect(connstr, \
autocommit=autocommit)
connection_created.send(sender=self.__class__)
cursor = self.connection.cursor()
if new_conn:
# Set date format for the connection. Also, make sure Sunday is
# considered the first day of the week (to be consistent with the
# Django convention for the 'week_day' Django lookup) if the user
# hasn't told us otherwise
cursor.execute("SET DATEFORMAT ymd; SET DATEFIRST %s" % self.datefirst)
if self.ops._get_sql_server_ver(self.connection) < 2005:
self.creation.data_types['TextField'] = 'ntext'
if self.driver_needs_utf8 is None:
self.driver_needs_utf8 = True
self.drv_name = self.connection.getinfo(Database.SQL_DRIVER_NAME).upper()
if self.drv_name in ('SQLSRV32.DLL', 'SQLNCLI.DLL', 'SQLNCLI10.DLL'):
self.driver_needs_utf8 = False
# http://msdn.microsoft.com/en-us/library/ms131686.aspx
if self.ops._get_sql_server_ver(self.connection) >= 2005 and self.drv_name in ('SQLNCLI.DLL', 'SQLNCLI10.DLL') and self.MARS_Connection:
# How to to activate it: Add 'MARS_Connection': True
# to the DATABASE_OPTIONS dictionary setting
self.features.can_use_chunked_reads = True
# FreeTDS can't execute some sql queries like CREATE DATABASE etc.
# in multi-statement, so we need to commit the above SQL sentence(s)
# to avoid this
if self.drv_name.startswith('LIBTDSODBC') and not self.connection.autocommit:
self.connection.commit()
return CursorWrapper(cursor, self.driver_needs_utf8)
class DatabaseWrapper(BaseDatabaseWrapper):
drv_name = None
driver_needs_utf8 = None
MARS_Connection = False
unicode_results = False
datefirst = 7
# Collations: http://msdn2.microsoft.com/en-us/library/ms184391.aspx
# http://msdn2.microsoft.com/en-us/library/ms179886.aspx
# T-SQL LIKE: http://msdn2.microsoft.com/en-us/library/ms179859.aspx
# Full-Text search: http://msdn2.microsoft.com/en-us/library/ms142571.aspx
# CONTAINS: http://msdn2.microsoft.com/en-us/library/ms187787.aspx
# FREETEXT: http://msdn2.microsoft.com/en-us/library/ms176078.aspx
operators = {
# Since '=' is used not only for string comparision there is no way
# to make it case (in)sensitive. It will simply fallback to the
# database collation.
'exact': '= %s',
'iexact': "= UPPER(%s)",
'contains': "LIKE %s ESCAPE '\\' COLLATE " + collation,
'icontains': "LIKE UPPER(%s) ESCAPE '\\' COLLATE " + collation,
'gt': '> %s',
'gte': '>= %s',
'lt': '< %s',
'lte': '<= %s',
'startswith': "LIKE %s ESCAPE '\\' COLLATE " + collation,
'endswith': "LIKE %s ESCAPE '\\' COLLATE " + collation,
'istartswith': "LIKE UPPER(%s) ESCAPE '\\' COLLATE " + collation,
'iendswith': "LIKE UPPER(%s) ESCAPE '\\' COLLATE " + collation,
# TODO: remove, keep native T-SQL LIKE wildcards support
# or use a "compatibility layer" and replace '*' with '%'
# and '.' with '_'
'regex': 'LIKE %s COLLATE ' + collation,
'iregex': 'LIKE %s COLLATE ' + collation,
# TODO: freetext, full-text contains...
}
def __init__(self, *args, **kwargs):
super(DatabaseWrapper, self).__init__(*args, **kwargs)
if 'OPTIONS' in self.settings_dict:
self.MARS_Connection = self.settings_dict['OPTIONS'].get(
'MARS_Connection', False)
self.datefirst = self.settings_dict['OPTIONS'].get('datefirst', 7)
self.unicode_results = self.settings_dict['OPTIONS'].get(
'unicode_results', False)
self.features = DatabaseFeatures()
self.ops = DatabaseOperations()
self.client = DatabaseClient(self)
self.creation = DatabaseCreation(self)
self.introspection = DatabaseIntrospection(self)
if _DJANGO_VERSION >= 12:
self.validation = BaseDatabaseValidation(self)
else:
self.validation = BaseDatabaseValidation()
self.connection = None
def _cursor(self):
new_conn = False
settings_dict = self.settings_dict
db_str, user_str, passwd_str, port_str = None, None, None, None
if _DJANGO_VERSION >= 12:
options = settings_dict['OPTIONS']
if settings_dict['NAME']:
db_str = settings_dict['NAME']
if settings_dict['HOST']:
host_str = settings_dict['HOST']
else:
host_str = 'localhost'
if settings_dict['USER']:
user_str = settings_dict['USER']
if settings_dict['PASSWORD']:
passwd_str = settings_dict['PASSWORD']
if settings_dict['PORT']:
port_str = settings_dict['PORT']
else:
options = settings_dict['DATABASE_OPTIONS']
if settings_dict['DATABASE_NAME']:
db_str = settings_dict['DATABASE_NAME']
if settings_dict['DATABASE_HOST']:
host_str = settings_dict['DATABASE_HOST']
else:
host_str = 'localhost'
if settings_dict['DATABASE_USER']:
user_str = settings_dict['DATABASE_USER']
if settings_dict['DATABASE_PASSWORD']:
passwd_str = settings_dict['DATABASE_PASSWORD']
if settings_dict['DATABASE_PORT']:
port_str = settings_dict['DATABASE_PORT']
if self.connection is None:
new_conn = True
if not db_str:
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured(
'You need to specify NAME in your Django settings file.')
cstr_parts = []
if 'driver' in options:
driver = options['driver']
else:
if os.name == 'nt':
driver = 'SQL Server'
else:
driver = 'FreeTDS'
if 'dsn' in options:
cstr_parts.append('DSN=%s' % options['dsn'])
else:
# Only append DRIVER if DATABASE_ODBC_DSN hasn't been set
cstr_parts.append('DRIVER={%s}' % driver)
if os.name == 'nt' or driver == 'FreeTDS' and \
options.get('host_is_server', False):
if port_str:
host_str += ',%s' % port_str
cstr_parts.append('SERVER=%s' % host_str)
else:
cstr_parts.append('SERVERNAME=%s' % host_str)
if user_str:
cstr_parts.append('UID=%s;PWD=%s' % (user_str, passwd_str))
else:
if driver in ('SQL Server', 'SQL Native Client'):
cstr_parts.append('Trusted_Connection=yes')
else:
cstr_parts.append('Integrated Security=SSPI')
cstr_parts.append('DATABASE=%s' % db_str)
if self.MARS_Connection:
cstr_parts.append('MARS_Connection=yes')
if 'extra_params' in options:
cstr_parts.append(options['extra_params'])
connstr = ';'.join(cstr_parts)
autocommit = options.get('autocommit', False)
if self.unicode_results:
self.connection = Database.connect(connstr, \
autocommit=autocommit, \
unicode_results='True')
else:
self.connection = Database.connect(connstr, \
autocommit=autocommit)
connection_created.send(sender=self.__class__)
cursor = self.connection.cursor()
if new_conn:
# Set date format for the connection. Also, make sure Sunday is
# considered the first day of the week (to be consistent with the
# Django convention for the 'week_day' Django lookup) if the user
# hasn't told us otherwise
cursor.execute("SET DATEFORMAT ymd; SET DATEFIRST %s" %
self.datefirst)
if self.ops._get_sql_server_ver(self.connection) < 2005:
self.creation.data_types['TextField'] = 'ntext'
if self.driver_needs_utf8 is None:
self.driver_needs_utf8 = True
self.drv_name = self.connection.getinfo(
Database.SQL_DRIVER_NAME).upper()
if self.drv_name in ('SQLSRV32.DLL', 'SQLNCLI.DLL',
'SQLNCLI10.DLL'):
self.driver_needs_utf8 = False
# http://msdn.microsoft.com/en-us/library/ms131686.aspx
if self.ops._get_sql_server_ver(
self.connection) >= 2005 and self.drv_name in (
'SQLNCLI.DLL',
'SQLNCLI10.DLL') and self.MARS_Connection:
# How to to activate it: Add 'MARS_Connection': True
# to the DATABASE_OPTIONS dictionary setting
self.features.can_use_chunked_reads = True
# FreeTDS can't execute some sql queries like CREATE DATABASE etc.
# in multi-statement, so we need to commit the above SQL sentence(s)
# to avoid this
if self.drv_name.startswith(
'LIBTDSODBC') and not self.connection.autocommit:
self.connection.commit()
return CursorWrapper(cursor, self.driver_needs_utf8)
class DatabaseWrapper(BaseDatabaseWrapper):
vendor = 'microsoft'
# This dictionary maps Field objects to their associated MS SQL column
# types, as strings. Column-type strings can contain format strings; they'll
# be interpolated against the values of Field.__dict__ before being output.
# If a column type is set to None, it won't be included in the output.
data_types = {
'AutoField': 'int IDENTITY (1, 1)',
'BigIntegerField': 'bigint',
'BinaryField': 'varbinary(max)',
'BooleanField': 'bit',
'CharField': 'nvarchar(%(max_length)s)',
'CommaSeparatedIntegerField': 'nvarchar(%(max_length)s)',
'DateField': 'date',
'DateTimeField': 'datetime2',
'DecimalField': 'numeric(%(max_digits)s, %(decimal_places)s)',
'DurationField': 'bigint',
'FileField': 'nvarchar(%(max_length)s)',
'FilePathField': 'nvarchar(%(max_length)s)',
'FloatField': 'double precision',
'IntegerField': 'int',
'IPAddressField': 'nvarchar(15)',
'GenericIPAddressField': 'nvarchar(39)',
'NullBooleanField': 'bit',
'OneToOneField': 'int',
'PositiveIntegerField': 'int',
'PositiveSmallIntegerField': 'smallint',
'SlugField': 'nvarchar(%(max_length)s)',
'SmallIntegerField': 'smallint',
'TextField': 'nvarchar(max)',
'TimeField': 'time',
'UUIDField': 'char(32)',
}
data_type_check_constraints = {
'PositiveIntegerField': '[%(column)s] >= 0',
'PositiveSmallIntegerField': '[%(column)s] >= 0',
}
operators = {
# Since '=' is used not only for string comparision there is no way
# to make it case (in)sensitive.
'exact': '= %s',
'iexact': "= UPPER(%s)",
'contains': "LIKE %s ESCAPE '\\'",
'icontains': "LIKE UPPER(%s) ESCAPE '\\'",
'gt': '> %s',
'gte': '>= %s',
'lt': '< %s',
'lte': '<= %s',
'startswith': "LIKE %s ESCAPE '\\'",
'endswith': "LIKE %s ESCAPE '\\'",
'istartswith': "LIKE UPPER(%s) ESCAPE '\\'",
'iendswith': "LIKE UPPER(%s) ESCAPE '\\'",
}
# The patterns below are used to generate SQL pattern lookup clauses when
# the right-hand side of the lookup isn't a raw string (it might be an expression
# or the result of a bilateral transformation).
# In those cases, special characters for LIKE operators (e.g. \, *, _) should be
# escaped on database side.
#
# Note: we use str.format() here for readability as '%' is used as a wildcard for
# the LIKE operator.
pattern_esc = r"REPLACE(REPLACE(REPLACE({}, '\', '\\'), '%%', '\%%'), '_', '\_')"
pattern_ops = {
'contains': "LIKE '%%' + {} + '%%'",
'icontains': "LIKE '%%' + UPPER({}) + '%%'",
'startswith': "LIKE {} + '%%'",
'istartswith': "LIKE UPPER({}) + '%%'",
'endswith': "LIKE '%%' + {}",
'iendswith': "LIKE '%%' + UPPER({})",
}
Database = Database
SchemaEditorClass = DatabaseSchemaEditor
_codes_for_networkerror = (
'08S01',
'08S02',
)
_sql_server_versions = {
9: 2005,
10: 2008,
11: 2012,
12: 2014,
}
# https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-csharp-retry-windows/
_transient_error_numbers = (
'4060',
'10928',
'10929',
'40197',
'40501',
'40613',
'49918',
'49919',
'49920',
)
def __init__(self, *args, **kwargs):
super(DatabaseWrapper, self).__init__(*args, **kwargs)
opts = self.settings_dict["OPTIONS"]
# capability for multiple result sets or cursors
self.supports_mars = False
# Some drivers need unicode encoded as UTF8. If this is left as
# None, it will be determined based on the driver, namely it'll be
# False if the driver is a windows driver and True otherwise.
#
# However, recent versions of FreeTDS and pyodbc (0.91 and 3.0.6 as
# of writing) are perfectly okay being fed unicode, which is why
# this option is configurable.
if 'driver_needs_utf8' in opts:
self.driver_charset = 'utf-8'
else:
self.driver_charset = opts.get('driver_charset', None)
# data type compatibility to databases created by old django-pyodbc
self.use_legacy_datetime = opts.get('use_legacy_datetime', False)
# interval to wait for recovery from network error
interval = opts.get('connection_recovery_interval_msec', 0.0)
self.connection_recovery_interval_msec = float(interval) / 1000
# make lookup operators to be collation-sensitive if needed
collation = opts.get('collation', None)
if collation:
self.operators = dict(self.__class__.operators)
ops = {}
for op in self.operators:
sql = self.operators[op]
if sql.startswith('LIKE '):
ops[op] = '%s COLLATE %s' % (sql, collation)
self.operators.update(ops)
self.features = DatabaseFeatures(self)
self.ops = DatabaseOperations(self)
self.client = DatabaseClient(self)
self.creation = DatabaseCreation(self)
self.introspection = DatabaseIntrospection(self)
self.validation = BaseDatabaseValidation(self)
def create_cursor(self):
return CursorWrapper(self.connection.cursor(), self)
def get_connection_params(self):
settings_dict = self.settings_dict
if settings_dict['NAME'] == '':
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured(
"settings.DATABASES is improperly configured. "
"Please supply the NAME value.")
conn_params = settings_dict.copy()
if conn_params['NAME'] is None:
conn_params['NAME'] = 'master'
return conn_params
def get_new_connection(self, conn_params):
database = conn_params['NAME']
host = conn_params.get('HOST', 'localhost')
user = conn_params.get('USER', None)
password = conn_params.get('PASSWORD', None)
port = conn_params.get('PORT', None)
default_driver = 'SQL Server' if os.name == 'nt' else 'FreeTDS'
options = conn_params.get('OPTIONS', {})
driver = options.get('driver', default_driver)
dsn = options.get('dsn', None)
# unixODBC uses string 'FreeTDS'; iODBC requires full path to lib
if driver == 'FreeTDS' or driver.endswith('/libtdsodbc.so'):
driver_is_freetds = True
else:
driver_is_freetds = False
# Microsoft driver names assumed here are:
# * SQL Server
# * SQL Native Client
# * SQL Server Native Client 10.0/11.0
# * ODBC Driver 11 for SQL Server
ms_drivers = re.compile('.*SQL (Server$|(Server )?Native Client)')
cstr_parts = []
if dsn:
cstr_parts.append('DSN=%s' % dsn)
else:
# Only append DRIVER if DATABASE_ODBC_DSN hasn't been set
if os.path.isabs(driver):
cstr_parts.append('DRIVER=%s' % driver) # iODBC compatible
else:
cstr_parts.append('DRIVER={%s}' % driver)
if ms_drivers.match(driver) or driver_is_freetds and \
options.get('host_is_server', False):
if port:
host += ';PORT=%s' % port
cstr_parts.append('SERVER=%s' % host)
else:
cstr_parts.append('SERVERNAME=%s' % host)
if user:
cstr_parts.append('UID=%s;PWD=%s' % (user, password))
else:
if ms_drivers.match(driver):
cstr_parts.append('Trusted_Connection=yes')
else:
cstr_parts.append('Integrated Security=SSPI')
cstr_parts.append('DATABASE=%s' % database)
if ms_drivers.match(driver) and not driver == 'SQL Server':
self.supports_mars = True
if self.supports_mars:
cstr_parts.append('MARS_Connection=yes')
if options.get('extra_params', None):
cstr_parts.append(options['extra_params'])
connstr = ';'.join(cstr_parts)
unicode_results = options.get('unicode_results', False)
timeout = options.get('connection_timeout', 0)
retries = options.get('connection_retries', 5)
backoff_time = options.get('connection_retry_backoff_time', 5)
conn = None
retry_count = 0
need_to_retry = False
while conn is None:
try:
conn = Database.connect(connstr,
unicode_results=unicode_results,
timeout=timeout)
except Exception as e:
for error_number in self._transient_error_numbers:
if error_number in e.args[1]:
if error_number in e.args[1] and retry_count < retries:
time.sleep(backoff_time)
need_to_retry = True
retry_count = retry_count + 1
else:
need_to_retry = False
break
if not need_to_retry:
raise
return conn
def init_connection_state(self):
drv_name = self.connection.getinfo(Database.SQL_DRIVER_NAME).upper()
driver_is_freetds = drv_name.startswith('LIBTDSODBC')
driver_is_sqlsrv32 = drv_name == 'SQLSRV32.DLL'
if driver_is_freetds:
self.supports_mars = False
try:
drv_ver = self.connection.getinfo(Database.SQL_DRIVER_VER)
ver = tuple(map(int, drv_ver.split('.')[:2]))
if ver < (0, 95):
# FreeTDS can't execute some sql queries like CREATE DATABASE etc.
# in multi-statement, so we need to commit the above SQL sentence(s)
# to avoid this
self.connection.commit()
except:
# unknown driver version
pass
elif driver_is_sqlsrv32:
self.supports_mars = False
ms_drv_names = re.compile('^(LIB)?(SQLN?CLI|MSODBCSQL)')
if driver_is_sqlsrv32 or ms_drv_names.match(drv_name):
self.driver_charset = None
# http://msdn.microsoft.com/en-us/library/ms131686.aspx
if self.supports_mars and ms_drv_names.match(drv_name):
self.features.can_use_chunked_reads = True
if self.sql_server_version < 2008:
self.features.has_bulk_insert = False
settings_dict = self.settings_dict
cursor = self.create_cursor()
# Set date format for the connection. Also, make sure Sunday is
# considered the first day of the week (to be consistent with the
# Django convention for the 'week_day' Django lookup) if the user
# hasn't told us otherwise
options = settings_dict.get('OPTIONS', {})
datefirst = options.get('datefirst', 7)
cursor.execute('SET DATEFORMAT ymd; SET DATEFIRST %s' % datefirst)
# http://blogs.msdn.com/b/sqlnativeclient/archive/2008/02/27/microsoft-sql-server-native-client-and-microsoft-sql-server-2008-native-client.aspx
try:
val = cursor.execute('SELECT SYSDATETIME()').fetchone()[0]
if isinstance(val, text_type):
# the driver doesn't support the modern datetime types
self.use_legacy_datetime = True
except:
# the server doesn't support the modern datetime types
self.use_legacy_datetime = True
if self.use_legacy_datetime:
self._use_legacy_datetime()
self.features.supports_microsecond_precision = False
def is_usable(self):
try:
self.create_cursor().execute("SELECT 1")
except Database.Error:
return False
else:
return True
def schema_editor(self, *args, **kwargs):
"Returns a new instance of this backend's SchemaEditor"
return DatabaseSchemaEditor(self, *args, **kwargs)
@cached_property
def sql_server_version(self):
with self.temporary_connection() as cursor:
cursor.execute("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)")
ver = cursor.fetchone()[0]
ver = int(ver.split('.')[0])
if not ver in self._sql_server_versions:
raise NotImplementedError('SQL Server v%d is not supported.' % ver)
return self._sql_server_versions[ver]
@cached_property
def to_azure_sql_db(self):
with self.temporary_connection() as cursor:
cursor.execute("SELECT CAST(SERVERPROPERTY('EngineEdition') AS integer)")
return cursor.fetchone()[0] == EDITION_AZURE_SQL_DB
def _execute_foreach(self, sql, table_names=None):
cursor = self.cursor()
if table_names is None:
table_names = self.introspection.table_names(cursor)
for table_name in table_names:
cursor.execute(sql % self.ops.quote_name(table_name))
def _get_trancount(self):
with self.connection.cursor() as cursor:
return cursor.execute('SELECT @@TRANCOUNT').fetchone()[0]
def _on_error(self, e):
if e.args[0] in self._codes_for_networkerror:
try:
# close the stale connection
self.close()
# wait a moment for recovery from network error
time.sleep(self.connection_recovery_interval_msec)
except:
pass
self.connection = None
def _savepoint(self, sid):
with self.cursor() as cursor:
cursor.execute('SELECT @@TRANCOUNT')
trancount = cursor.fetchone()[0]
if trancount == 0:
cursor.execute(self.ops.start_transaction_sql())
cursor.execute(self.ops.savepoint_create_sql(sid))
def _savepoint_commit(self, sid):
# SQL Server has no support for partial commit in a transaction
pass
def _savepoint_rollback(self, sid):
with self.cursor() as cursor:
# FreeTDS v0.95 requires TRANCOUNT that is greater than 0
cursor.execute('SELECT @@TRANCOUNT')
trancount = cursor.fetchone()[0]
if trancount > 0:
cursor.execute(self.ops.savepoint_rollback_sql(sid))
def _set_autocommit(self, autocommit):
with self.wrap_database_errors:
allowed = not autocommit
if not allowed:
# FreeTDS v0.95 requires TRANCOUNT that is greater than 0
allowed = self._get_trancount() > 0
if allowed:
self.connection.autocommit = autocommit
def _use_legacy_datetime(self):
for field in ('DateField', 'DateTimeField', 'TimeField'):
self.data_types[field] = 'datetime'
def check_constraints(self, table_names=None):
self._execute_foreach('ALTER TABLE %s WITH CHECK CHECK CONSTRAINT ALL',
table_names)
def disable_constraint_checking(self):
# Azure SQL Database doesn't support sp_msforeachtable
#cursor.execute('EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"')
if not self.needs_rollback:
self._execute_foreach('ALTER TABLE %s NOCHECK CONSTRAINT ALL')
return not self.needs_rollback
def enable_constraint_checking(self):
# Azure SQL Database doesn't support sp_msforeachtable
#cursor.execute('EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"')
if not self.needs_rollback:
self.check_constraints()
class DatabaseWrapper(BaseDatabaseWrapper):
_DJANGO_VERSION = _DJANGO_VERSION
vendor = "microsoft"
operators = {
# Since '=' is used not only for string comparision there is no way
# to make it case (in)sensitive.
"exact": "= %s",
"iexact": "= UPPER(%s)",
"contains": "LIKE %s ESCAPE '\\'",
"icontains": "LIKE UPPER(%s) ESCAPE '\\'",
"gt": "> %s",
"gte": ">= %s",
"lt": "< %s",
"lte": "<= %s",
"startswith": "LIKE %s ESCAPE '\\'",
"endswith": "LIKE %s ESCAPE '\\'",
"istartswith": "LIKE UPPER(%s) ESCAPE '\\'",
"iendswith": "LIKE UPPER(%s) ESCAPE '\\'",
}
_codes_for_networkerror = ("08S01", "08S02")
_sql_server_versions = {9: 2005, 10: 2008, 11: 2012}
Database = Database
def __init__(self, *args, **kwargs):
super(DatabaseWrapper, self).__init__(*args, **kwargs)
opts = self.settings_dict["OPTIONS"]
# capability for multiple result sets or cursors
self.supports_mars = opts.get("MARS_Connection", False)
self.open_cursor = None
# Some drivers need unicode encoded as UTF8. If this is left as
# None, it will be determined based on the driver, namely it'll be
# False if the driver is a windows driver and True otherwise.
#
# However, recent versions of FreeTDS and pyodbc (0.91 and 3.0.6 as
# of writing) are perfectly okay being fed unicode, which is why
# this option is configurable.
self.driver_needs_utf8 = opts.get("driver_needs_utf8", False)
# data type compatibility to databases created by old django-pyodbc
self.use_legacy_datetime = opts.get("use_legacy_datetime", False)
# interval to wait for recovery from network error
interval = opts.get("connection_recovery_interval_msec", 0.0)
self.connection_recovery_interval_msec = float(interval) / 1000
# make lookup operators to be collation-sensitive if needed
collation = opts.get("collation", None)
if collation:
self.operators = dict(self.__class__.operators)
ops = {}
for op in self.operators:
sql = self.operators[op]
if sql.startswith("LIKE "):
ops[op] = "%s COLLATE %s" % (sql, collation)
self.operators.update(ops)
self.features = DatabaseFeatures(self)
self.ops = DatabaseOperations(self)
self.client = DatabaseClient(self)
self.creation = DatabaseCreation(self)
self.introspection = DatabaseIntrospection(self)
self.validation = BaseDatabaseValidation(self)
def close(self):
self.validate_thread_sharing()
if self.connection is None:
return
if self.open_cursor:
try:
self.open_cursor.close()
except:
pass
try:
self.connection.close()
except Database.Error:
# In some cases (database restart, network connection lost etc...)
# the connection to the database is lost without giving Django a
# notification. If we don't set self.connection to None, the error
# will occur a every request.
logger.warning("pyodbc error while closing the connection.", exc_info=sys.exc_info())
raise
finally:
self.connection = None
self.open_cursor = None
self.set_clean()
def create_cursor(self):
return CursorWrapper(self._create_cursor(), self)
def get_connection_params(self):
settings_dict = self.settings_dict
if not settings_dict["NAME"]:
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured("settings.DATABASES is improperly configured. " "Please supply the NAME value.")
return settings_dict
def get_new_connection(self, conn_params):
database = conn_params["NAME"]
host = conn_params.get("HOST", "localhost")
user = conn_params.get("USER", None)
password = conn_params.get("PASSWORD", None)
port = conn_params.get("PORT", None)
default_driver = "SQL Server" if os.name == "nt" else "FreeTDS"
options = conn_params.get("OPTIONS", {})
driver = options.get("driver", default_driver)
dsn = options.get("dsn", None)
# Microsoft driver names assumed here are:
# * SQL Server
# * SQL Native Client
# * SQL Server Native Client 10.0/11.0
# * ODBC Driver 11 for SQL Server
ms_drivers = re.compile(".*SQL (Server$|(Server )?Native Client)")
cstr_parts = []
if dsn:
cstr_parts.append("DSN=%s" % dsn)
else:
# Only append DRIVER if DATABASE_ODBC_DSN hasn't been set
cstr_parts.append("DRIVER={%s}" % driver)
if ms_drivers.match(driver) or driver == "FreeTDS" and options.get("host_is_server", False):
if port:
host += ";PORT=%s" % port
cstr_parts.append("SERVER=%s" % host)
else:
cstr_parts.append("SERVERNAME=%s" % host)
if user:
cstr_parts.append("UID=%s;PWD=%s" % (user, password))
else:
if ms_drivers.match(driver):
cstr_parts.append("Trusted_Connection=yes")
else:
cstr_parts.append("Integrated Security=SSPI")
cstr_parts.append("DATABASE=%s" % database)
if self.supports_mars:
cstr_parts.append("MARS_Connection=yes")
if options.get("extra_params", None):
cstr_parts.append(options["extra_params"])
connstr = ";".join(cstr_parts)
unicode_results = options.get("unicode_results", False)
conn = Database.connect(connstr, unicode_results=unicode_results)
drv_name = conn.getinfo(Database.SQL_DRIVER_NAME).upper()
driver_is_freetds = drv_name.startswith("LIBTDSODBC")
if driver_is_freetds:
self.use_legacy_datetime = True
self.supports_mars = False
ms_drv_names = re.compile("^((LIB)?SQLN?CLI|LIBMSODBCSQL)")
if drv_name == "SQLSRV32.DLL" or ms_drv_names.match(drv_name):
self.driver_needs_utf8 = False
# http://msdn.microsoft.com/en-us/library/ms131686.aspx
if self.supports_mars and ms_drv_names.match(drv_name):
# How to to activate it: Add 'MARS_Connection': True
# to the OPTIONS dictionary setting
self.features.can_use_chunked_reads = True
# FreeTDS can't execute some sql queries like CREATE DATABASE etc.
# in multi-statement, so we need to commit the above SQL sentence(s)
# to avoid this
if driver_is_freetds and not conn_params["AUTOCOMMIT"]:
conn.commit()
return conn
def init_connection_state(self):
if self.sql_server_version < 2008:
self.use_legacy_datetime = True
self.features.has_bulk_insert = False
if self.use_legacy_datetime:
self.creation.use_legacy_datetime()
self.features.supports_microsecond_precision = False
settings_dict = self.settings_dict
cursor = self._create_cursor()
# Set date format for the connection. Also, make sure Sunday is
# considered the first day of the week (to be consistent with the
# Django convention for the 'week_day' Django lookup) if the user
# hasn't told us otherwise
options = settings_dict.get("OPTIONS", {})
datefirst = options.get("datefirst", 7)
cursor.execute("SET DATEFORMAT ymd; SET DATEFIRST %s" % datefirst)
def is_usable(self):
try:
# use a pyodbc cursor directly, bypassing Django's utilities.
self._create_cursor().execute("SELECT 1")
except Database.Error:
return False
else:
return True
@cached_property
def sql_server_version(self):
with self.temporary_connection():
# use a pyodbc cursor directly, bypassing Django's utilities.
cursor = self._create_cursor()
cursor.execute("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)")
ver = cursor.fetchone()[0]
ver = int(ver.split(".")[0])
if not ver in self._sql_server_versions:
raise NotImplementedError("SQL Server v%d is not supported." % ver)
return self._sql_server_versions[ver]
@cached_property
def to_azure_sql_db(self):
with self.temporary_connection():
# use a pyodbc cursor directly, bypassing Django's utilities.
cursor = self._create_cursor()
cursor.execute("SELECT CAST(SERVERPROPERTY('EngineEdition') AS integer)")
return cursor.fetchone()[0] == EDITION_AZURE_SQL_DB
def _create_cursor(self):
if self.supports_mars:
cursor = self.connection.cursor()
else:
if not self.open_cursor:
self.open_cursor = self.connection.cursor()
cursor = self.open_cursor
return cursor
def _cursor_closed(self, cursor):
if not self.supports_mars:
self.open_cursor = None
def _execute_foreach(self, sql, table_names=None):
cursor = self.cursor()
if not table_names:
table_names = self.introspection.get_table_list(cursor)
for table_name in table_names:
cursor.execute(sql % self.ops.quote_name(table_name))
def _on_error(self, e):
if e.args[0] in self._codes_for_networkerror:
try:
# close the stale connection
self.close()
# wait a moment for recovery from network error
import time
time.sleep(self.connection_recovery_interval_msec)
except:
pass
self.connection = None
def _savepoint(self, sid):
cursor = self.cursor()
cursor.execute("SELECT @@TRANCOUNT")
trancount = cursor.fetchone()[0]
if trancount == 0:
cursor.execute(self.ops.start_transaction_sql())
cursor.execute(self.ops.savepoint_create_sql(sid))
def _savepoint_commit(self, sid):
# SQL Server has no support for partial commit in a transaction
pass
def _set_autocommit(self, autocommit):
if autocommit:
self.connection.commit()
else:
self.connection.rollback()
self.connection.autocommit = autocommit
def check_constraints(self, table_names=None):
self._execute_foreach("ALTER TABLE %s WITH CHECK CHECK CONSTRAINT ALL", table_names)
def disable_constraint_checking(self):
# Windows Azure SQL Database doesn't support sp_msforeachtable
# cursor.execute('EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"')
self._execute_foreach("ALTER TABLE %s NOCHECK CONSTRAINT ALL")
return True
def enable_constraint_checking(self):
# Windows Azure SQL Database doesn't support sp_msforeachtable
# cursor.execute('EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"')
self.check_constraints()
class DatabaseWrapper(BaseDatabaseWrapper):
_DJANGO_VERSION = _DJANGO_VERSION
vendor = 'microsoft'
operators = {
# Since '=' is used not only for string comparision there is no way
# to make it case (in)sensitive.
'exact': '= %s',
'iexact': "= UPPER(%s)",
'contains': "LIKE %s ESCAPE '\\'",
'icontains': "LIKE UPPER(%s) ESCAPE '\\'",
'gt': '> %s',
'gte': '>= %s',
'lt': '< %s',
'lte': '<= %s',
'startswith': "LIKE %s ESCAPE '\\'",
'endswith': "LIKE %s ESCAPE '\\'",
'istartswith': "LIKE UPPER(%s) ESCAPE '\\'",
'iendswith': "LIKE UPPER(%s) ESCAPE '\\'",
}
_codes_for_networkerror = (
'08S01',
'08S02',
)
_sql_server_versions = {
9: 2005,
10: 2008,
11: 2012,
12: 2014,
}
Database = Database
def __init__(self, *args, **kwargs):
super(DatabaseWrapper, self).__init__(*args, **kwargs)
opts = self.settings_dict["OPTIONS"]
# capability for multiple result sets or cursors
self.supports_mars = False
self.open_cursor = None
# Some drivers need unicode encoded as UTF8. If this is left as
# None, it will be determined based on the driver, namely it'll be
# False if the driver is a windows driver and True otherwise.
#
# However, recent versions of FreeTDS and pyodbc (0.91 and 3.0.6 as
# of writing) are perfectly okay being fed unicode, which is why
# this option is configurable.
self.driver_needs_utf8 = opts.get('driver_needs_utf8', False)
# data type compatibility to databases created by old django-pyodbc
self.use_legacy_datetime = opts.get('use_legacy_datetime', False)
# interval to wait for recovery from network error
interval = opts.get('connection_recovery_interval_msec', 0.0)
self.connection_recovery_interval_msec = float(interval) / 1000
# make lookup operators to be collation-sensitive if needed
collation = opts.get('collation', None)
if collation:
self.operators = dict(self.__class__.operators)
ops = {}
for op in self.operators:
sql = self.operators[op]
if sql.startswith('LIKE '):
ops[op] = '%s COLLATE %s' % (sql, collation)
self.operators.update(ops)
self.features = DatabaseFeatures(self)
self.ops = DatabaseOperations(self)
self.client = DatabaseClient(self)
self.creation = DatabaseCreation(self)
self.introspection = DatabaseIntrospection(self)
self.validation = BaseDatabaseValidation(self)
def create_cursor(self):
if self.supports_mars:
cursor = self._create_cursor()
else:
if not self.open_cursor or not self.open_cursor.active:
self.open_cursor = self._create_cursor()
cursor = self.open_cursor
return cursor
def get_connection_params(self):
settings_dict = self.settings_dict
if settings_dict['NAME'] == '':
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured(
"settings.DATABASES is improperly configured. "
"Please supply the NAME value.")
conn_params = settings_dict.copy()
if conn_params['NAME'] is None:
conn_params['NAME'] = 'master'
return conn_params
def get_new_connection(self, conn_params):
database = conn_params['NAME']
host = conn_params.get('HOST', 'localhost')
user = conn_params.get('USER', None)
password = conn_params.get('PASSWORD', None)
port = conn_params.get('PORT', None)
default_driver = 'SQL Server' if os.name == 'nt' else 'FreeTDS'
options = conn_params.get('OPTIONS', {})
driver = options.get('driver', default_driver)
dsn = options.get('dsn', None)
# Microsoft driver names assumed here are:
# * SQL Server
# * SQL Native Client
# * SQL Server Native Client 10.0/11.0
# * ODBC Driver 11 for SQL Server
ms_drivers = re.compile('.*SQL (Server$|(Server )?Native Client)')
cstr_parts = []
if dsn:
cstr_parts.append('DSN=%s' % dsn)
else:
# Only append DRIVER if DATABASE_ODBC_DSN hasn't been set
cstr_parts.append('DRIVER={%s}' % driver)
if ms_drivers.match(driver) or driver == 'FreeTDS' and \
options.get('host_is_server', False):
if port:
host += ';PORT=%s' % port
cstr_parts.append('SERVER=%s' % host)
else:
cstr_parts.append('SERVERNAME=%s' % host)
if user:
cstr_parts.append('UID=%s;PWD=%s' % (user, password))
else:
if ms_drivers.match(driver):
cstr_parts.append('Trusted_Connection=yes')
else:
cstr_parts.append('Integrated Security=SSPI')
cstr_parts.append('DATABASE=%s' % database)
if ms_drivers.match(driver) and not driver == 'SQL Server':
self.supports_mars = True
if self.supports_mars:
cstr_parts.append('MARS_Connection=yes')
if options.get('extra_params', None):
cstr_parts.append(options['extra_params'])
connstr = ';'.join(cstr_parts)
unicode_results = options.get('unicode_results', False)
conn = Database.connect(connstr, unicode_results=unicode_results)
drv_name = conn.getinfo(Database.SQL_DRIVER_NAME).upper()
driver_is_freetds = drv_name.startswith('LIBTDSODBC')
driver_is_sqlsrv32 = drv_name == 'SQLSRV32.DLL'
driver_is_snac9 = drv_name == 'SQLNCLI.DLL'
if driver_is_freetds or driver_is_sqlsrv32:
self.use_legacy_datetime = True
self.supports_mars = False
elif driver_is_snac9:
self.use_legacy_datetime = True
ms_drv_names = re.compile('^(LIB)?(SQLN?CLI|MSODBCSQL)')
if driver_is_sqlsrv32 or ms_drv_names.match(drv_name):
self.driver_needs_utf8 = False
# http://msdn.microsoft.com/en-us/library/ms131686.aspx
if self.supports_mars and ms_drv_names.match(drv_name):
self.features.can_use_chunked_reads = True
# FreeTDS can't execute some sql queries like CREATE DATABASE etc.
# in multi-statement, so we need to commit the above SQL sentence(s)
# to avoid this
if driver_is_freetds and not conn_params['AUTOCOMMIT']:
conn.commit()
return conn
def init_connection_state(self):
if self.sql_server_version < 2008:
self.use_legacy_datetime = True
self.features.has_bulk_insert = False
if self.use_legacy_datetime:
self.creation.use_legacy_datetime()
self.features.supports_microsecond_precision = False
settings_dict = self.settings_dict
cursor = self.create_cursor()
# Set date format for the connection. Also, make sure Sunday is
# considered the first day of the week (to be consistent with the
# Django convention for the 'week_day' Django lookup) if the user
# hasn't told us otherwise
options = settings_dict.get('OPTIONS', {})
datefirst = options.get('datefirst', 7)
cursor.execute('SET DATEFORMAT ymd; SET DATEFIRST %s' % datefirst)
def is_usable(self):
try:
self.create_cursor().execute("SELECT 1")
except Database.Error:
return False
else:
return True
def schema_editor(self, *args, **kwargs):
"Returns a new instance of this backend's SchemaEditor"
return DatabaseSchemaEditor(self, *args, **kwargs)
@cached_property
def sql_server_version(self):
with self.temporary_connection() as cursor:
cursor.execute("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)")
ver = cursor.fetchone()[0]
ver = int(ver.split('.')[0])
if not ver in self._sql_server_versions:
raise NotImplementedError('SQL Server v%d is not supported.' % ver)
return self._sql_server_versions[ver]
@cached_property
def to_azure_sql_db(self):
with self.temporary_connection() as cursor:
cursor.execute("SELECT CAST(SERVERPROPERTY('EngineEdition') AS integer)")
return cursor.fetchone()[0] == EDITION_AZURE_SQL_DB
def _close(self):
if self.open_cursor:
try:
self.open_cursor.close()
except:
pass
finally:
self.open_cursor = None
super(DatabaseWrapper, self)._close()
def _create_cursor(self):
return CursorWrapper(self.connection.cursor(), self)
def _execute_foreach(self, sql, table_names=None):
cursor = self.cursor()
if not table_names:
table_names = self.introspection.get_table_list(cursor)
for table_name in table_names:
cursor.execute(sql % self.ops.quote_name(table_name))
def _on_error(self, e):
if e.args[0] in self._codes_for_networkerror:
try:
# close the stale connection
self.close()
# wait a moment for recovery from network error
import time
time.sleep(self.connection_recovery_interval_msec)
except:
pass
self.connection = None
def _savepoint(self, sid):
cursor = self.cursor()
cursor.execute('SELECT @@TRANCOUNT')
trancount = cursor.fetchone()[0]
if trancount == 0:
cursor.execute(self.ops.start_transaction_sql())
cursor.execute(self.ops.savepoint_create_sql(sid))
def _savepoint_commit(self, sid):
# SQL Server has no support for partial commit in a transaction
pass
def _set_autocommit(self, autocommit):
with self.wrap_database_errors:
if autocommit:
self.connection.commit()
else:
self.connection.rollback()
self.connection.autocommit = autocommit
def check_constraints(self, table_names=None):
self._execute_foreach('ALTER TABLE %s WITH CHECK CHECK CONSTRAINT ALL',
table_names)
def disable_constraint_checking(self):
# Azure SQL Database doesn't support sp_msforeachtable
#cursor.execute('EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"')
self._execute_foreach('ALTER TABLE %s NOCHECK CONSTRAINT ALL')
return True
def enable_constraint_checking(self):
# Azure SQL Database doesn't support sp_msforeachtable
#cursor.execute('EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"')
self.check_constraints()
class DatabaseWrapper(BaseDatabaseWrapper):
vendor = 'microsoft'
# This dictionary maps Field objects to their associated MS SQL column
# types, as strings. Column-type strings can contain format strings; they'll
# be interpolated against the values of Field.__dict__ before being output.
# If a column type is set to None, it won't be included in the output.
data_types = {
'AutoField': 'int IDENTITY (1, 1)',
'BigIntegerField': 'bigint',
'BinaryField': 'varbinary(max)',
'BooleanField': 'bit',
'CharField': 'nvarchar(%(max_length)s)',
'CommaSeparatedIntegerField': 'nvarchar(%(max_length)s)',
'DateField': 'date',
'DateTimeField': 'datetime2',
'DecimalField': 'numeric(%(max_digits)s, %(decimal_places)s)',
'DurationField': 'bigint',
'FileField': 'nvarchar(%(max_length)s)',
'FilePathField': 'nvarchar(%(max_length)s)',
'FloatField': 'double precision',
'IntegerField': 'int',
'IPAddressField': 'nvarchar(15)',
'GenericIPAddressField': 'nvarchar(39)',
'NullBooleanField': 'bit',
'OneToOneField': 'int',
'PositiveIntegerField': 'int',
'PositiveSmallIntegerField': 'smallint',
'SlugField': 'nvarchar(%(max_length)s)',
'SmallIntegerField': 'smallint',
'TextField': 'nvarchar(max)',
'TimeField': 'time',
'UUIDField': 'char(32)',
}
data_type_check_constraints = {
'PositiveIntegerField': '[%(column)s] >= 0',
'PositiveSmallIntegerField': '[%(column)s] >= 0',
}
operators = {
# Since '=' is used not only for string comparision there is no way
# to make it case (in)sensitive.
'exact': '= %s',
'iexact': "= UPPER(%s)",
'contains': "LIKE %s ESCAPE '\\'",
'icontains': "LIKE UPPER(%s) ESCAPE '\\'",
'gt': '> %s',
'gte': '>= %s',
'lt': '< %s',
'lte': '<= %s',
'startswith': "LIKE %s ESCAPE '\\'",
'endswith': "LIKE %s ESCAPE '\\'",
'istartswith': "LIKE UPPER(%s) ESCAPE '\\'",
'iendswith': "LIKE UPPER(%s) ESCAPE '\\'",
}
# The patterns below are used to generate SQL pattern lookup clauses when
# the right-hand side of the lookup isn't a raw string (it might be an expression
# or the result of a bilateral transformation).
# In those cases, special characters for LIKE operators (e.g. \, *, _) should be
# escaped on database side.
#
# Note: we use str.format() here for readability as '%' is used as a wildcard for
# the LIKE operator.
pattern_esc = r"REPLACE(REPLACE(REPLACE({}, '\', '\\'), '%%', '\%%'), '_', '\_')"
pattern_ops = {
'contains': "LIKE '%%' + {} + '%%'",
'icontains': "LIKE '%%' + UPPER({}) + '%%'",
'startswith': "LIKE {} + '%%'",
'istartswith': "LIKE UPPER({}) + '%%'",
'endswith': "LIKE '%%' + {}",
'iendswith': "LIKE '%%' + UPPER({})",
}
Database = Database
SchemaEditorClass = DatabaseSchemaEditor
_codes_for_networkerror = (
'08S01',
'08S02',
)
_sql_server_versions = {
9: 2005,
10: 2008,
11: 2012,
12: 2014,
}
# https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-csharp-retry-windows/
_transient_error_numbers = (
'4060',
'10928',
'10929',
'40197',
'40501',
'40613',
'49918',
'49919',
'49920',
)
def __init__(self, *args, **kwargs):
super(DatabaseWrapper, self).__init__(*args, **kwargs)
opts = self.settings_dict["OPTIONS"]
# capability for multiple result sets or cursors
self.supports_mars = False
# Some drivers need unicode encoded as UTF8. If this is left as
# None, it will be determined based on the driver, namely it'll be
# False if the driver is a windows driver and True otherwise.
#
# However, recent versions of FreeTDS and pyodbc (0.91 and 3.0.6 as
# of writing) are perfectly okay being fed unicode, which is why
# this option is configurable.
if 'driver_needs_utf8' in opts:
self.driver_charset = 'utf-8'
else:
self.driver_charset = opts.get('driver_charset', None)
# data type compatibility to databases created by old django-pyodbc
self.use_legacy_datetime = opts.get('use_legacy_datetime', False)
# interval to wait for recovery from network error
interval = opts.get('connection_recovery_interval_msec', 0.0)
self.connection_recovery_interval_msec = float(interval) / 1000
# make lookup operators to be collation-sensitive if needed
collation = opts.get('collation', None)
if collation:
self.operators = dict(self.__class__.operators)
ops = {}
for op in self.operators:
sql = self.operators[op]
if sql.startswith('LIKE '):
ops[op] = '%s COLLATE %s' % (sql, collation)
self.operators.update(ops)
self.features = DatabaseFeatures(self)
self.ops = DatabaseOperations(self)
self.client = DatabaseClient(self)
self.creation = DatabaseCreation(self)
self.introspection = DatabaseIntrospection(self)
self.validation = BaseDatabaseValidation(self)
def create_cursor(self):
return CursorWrapper(self.connection.cursor(), self)
def get_connection_params(self):
settings_dict = self.settings_dict
if settings_dict['NAME'] == '':
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured(
"settings.DATABASES is improperly configured. "
"Please supply the NAME value.")
conn_params = settings_dict.copy()
if conn_params['NAME'] is None:
conn_params['NAME'] = 'master'
return conn_params
def get_new_connection(self, conn_params):
database = conn_params['NAME']
host = conn_params.get('HOST', 'localhost')
user = conn_params.get('USER', None)
password = conn_params.get('PASSWORD', None)
port = conn_params.get('PORT', None)
default_driver = 'SQL Server' if os.name == 'nt' else 'FreeTDS'
options = conn_params.get('OPTIONS', {})
driver = options.get('driver', default_driver)
dsn = options.get('dsn', None)
# unixODBC uses string 'FreeTDS'; iODBC requires full path to lib
if driver == 'FreeTDS' or driver.endswith('/libtdsodbc.so'):
driver_is_freetds = True
else:
driver_is_freetds = False
# Microsoft driver names assumed here are:
# * SQL Server
# * SQL Native Client
# * SQL Server Native Client 10.0/11.0
# * ODBC Driver 11 for SQL Server
ms_drivers = re.compile('.*SQL (Server$|(Server )?Native Client)')
cstr_parts = {}
if dsn:
cstr_parts['DSN'] = dsn
else:
# Only append DRIVER if DATABASE_ODBC_DSN hasn't been set
cstr_parts['DRIVER'] = driver
if ms_drivers.match(driver) or driver_is_freetds and \
options.get('host_is_server', False):
if port:
cstr_parts['PORT'] = str(port)
cstr_parts['SERVER'] = host
else:
cstr_parts['SERVERNAME'] = host
if user:
cstr_parts['UID'] = user
cstr_parts['PWD'] = password
else:
if ms_drivers.match(driver):
cstr_parts['Trusted_Connection'] = 'yes'
else:
cstr_parts['Integrated Security'] = 'SSPI'
cstr_parts['DATABASE'] = database
if ms_drivers.match(driver) and not driver == 'SQL Server':
self.supports_mars = True
if self.supports_mars:
cstr_parts['MARS_Connection'] = 'yes'
connstr = encode_connection_string(cstr_parts)
# extra_params are glued on the end of the string without encoding,
# so it's up to the settings writer to make sure they're appropriate -
# use encode_connection_string if constructing from external input.
if options.get('extra_params', None):
connstr += ';' + options['extra_params']
unicode_results = options.get('unicode_results', False)
timeout = options.get('connection_timeout', 0)
retries = options.get('connection_retries', 5)
backoff_time = options.get('connection_retry_backoff_time', 5)
conn = None
retry_count = 0
need_to_retry = False
while conn is None:
try:
conn = Database.connect(connstr,
unicode_results=unicode_results,
timeout=timeout)
except Exception as e:
for error_number in self._transient_error_numbers:
if error_number in e.args[1]:
if error_number in e.args[1] and retry_count < retries:
time.sleep(backoff_time)
need_to_retry = True
retry_count = retry_count + 1
else:
need_to_retry = False
break
if not need_to_retry:
raise
return conn
def init_connection_state(self):
drv_name = self.connection.getinfo(Database.SQL_DRIVER_NAME).upper()
driver_is_freetds = drv_name.startswith('LIBTDSODBC')
driver_is_sqlsrv32 = drv_name == 'SQLSRV32.DLL'
if driver_is_freetds:
self.supports_mars = False
try:
drv_ver = self.connection.getinfo(Database.SQL_DRIVER_VER)
ver = tuple(map(int, drv_ver.split('.')[:2]))
if ver < (0, 95):
# FreeTDS can't execute some sql queries like CREATE DATABASE etc.
# in multi-statement, so we need to commit the above SQL sentence(s)
# to avoid this
self.connection.commit()
except:
# unknown driver version
pass
elif driver_is_sqlsrv32:
self.supports_mars = False
ms_drv_names = re.compile('^(LIB)?(SQLN?CLI|MSODBCSQL)')
if driver_is_sqlsrv32 or ms_drv_names.match(drv_name):
self.driver_charset = None
# http://msdn.microsoft.com/en-us/library/ms131686.aspx
if self.supports_mars and ms_drv_names.match(drv_name):
self.features.can_use_chunked_reads = True
if self.sql_server_version < 2008:
self.features.has_bulk_insert = False
settings_dict = self.settings_dict
cursor = self.create_cursor()
# Set date format for the connection. Also, make sure Sunday is
# considered the first day of the week (to be consistent with the
# Django convention for the 'week_day' Django lookup) if the user
# hasn't told us otherwise
options = settings_dict.get('OPTIONS', {})
datefirst = options.get('datefirst', 7)
cursor.execute('SET DATEFORMAT ymd; SET DATEFIRST %s' % datefirst)
# http://blogs.msdn.com/b/sqlnativeclient/archive/2008/02/27/microsoft-sql-server-native-client-and-microsoft-sql-server-2008-native-client.aspx
try:
val = cursor.execute('SELECT SYSDATETIME()').fetchone()[0]
if isinstance(val, text_type):
# the driver doesn't support the modern datetime types
self.use_legacy_datetime = True
except:
# the server doesn't support the modern datetime types
self.use_legacy_datetime = True
if self.use_legacy_datetime:
self._use_legacy_datetime()
self.features.supports_microsecond_precision = False
def is_usable(self):
try:
self.create_cursor().execute("SELECT 1")
except Database.Error:
return False
else:
return True
def schema_editor(self, *args, **kwargs):
"Returns a new instance of this backend's SchemaEditor"
return DatabaseSchemaEditor(self, *args, **kwargs)
@cached_property
def sql_server_version(self):
with self.temporary_connection() as cursor:
cursor.execute("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)")
ver = cursor.fetchone()[0]
ver = int(ver.split('.')[0])
if not ver in self._sql_server_versions:
raise NotImplementedError('SQL Server v%d is not supported.' % ver)
return self._sql_server_versions[ver]
@cached_property
def to_azure_sql_db(self):
with self.temporary_connection() as cursor:
cursor.execute("SELECT CAST(SERVERPROPERTY('EngineEdition') AS integer)")
return cursor.fetchone()[0] == EDITION_AZURE_SQL_DB
def _execute_foreach(self, sql, table_names=None):
cursor = self.cursor()
if table_names is None:
table_names = self.introspection.table_names(cursor)
for table_name in table_names:
cursor.execute(sql % self.ops.quote_name(table_name))
def _get_trancount(self):
with self.connection.cursor() as cursor:
return cursor.execute('SELECT @@TRANCOUNT').fetchone()[0]
def _on_error(self, e):
if e.args[0] in self._codes_for_networkerror:
try:
# close the stale connection
self.close()
# wait a moment for recovery from network error
time.sleep(self.connection_recovery_interval_msec)
except:
pass
self.connection = None
def _savepoint(self, sid):
with self.cursor() as cursor:
cursor.execute('SELECT @@TRANCOUNT')
trancount = cursor.fetchone()[0]
if trancount == 0:
cursor.execute(self.ops.start_transaction_sql())
cursor.execute(self.ops.savepoint_create_sql(sid))
def _savepoint_commit(self, sid):
# SQL Server has no support for partial commit in a transaction
pass
def _savepoint_rollback(self, sid):
with self.cursor() as cursor:
# FreeTDS v0.95 requires TRANCOUNT that is greater than 0
cursor.execute('SELECT @@TRANCOUNT')
trancount = cursor.fetchone()[0]
if trancount > 0:
cursor.execute(self.ops.savepoint_rollback_sql(sid))
def _set_autocommit(self, autocommit):
with self.wrap_database_errors:
allowed = not autocommit
if not allowed:
# FreeTDS v0.95 requires TRANCOUNT that is greater than 0
allowed = self._get_trancount() > 0
if allowed:
self.connection.autocommit = autocommit
def _use_legacy_datetime(self):
for field in ('DateField', 'DateTimeField', 'TimeField'):
self.data_types[field] = 'datetime'
def check_constraints(self, table_names=None):
self._execute_foreach('ALTER TABLE %s WITH CHECK CHECK CONSTRAINT ALL',
table_names)
def disable_constraint_checking(self):
# Azure SQL Database doesn't support sp_msforeachtable
#cursor.execute('EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"')
if not self.needs_rollback:
self._execute_foreach('ALTER TABLE %s NOCHECK CONSTRAINT ALL')
return not self.needs_rollback
def enable_constraint_checking(self):
# Azure SQL Database doesn't support sp_msforeachtable
#cursor.execute('EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"')
if not self.needs_rollback:
self.check_constraints()
