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()