Ticket #1258: mssql.py

"""
Microsoft SQL database backend for Django.

Requires pymssql: http://pymssql.sourceforge.net
"""

from django.core.db import base, typecasts
from django.core.db.dicthelpers import *
import pymssql as Database

DatabaseError = Database.DatabaseError

class DatabaseWrapper:
    def __init__(self):
        self.connection = None
        self.queries = []

    def cursor(self):
        from django.conf.settings import DATABASE_USER, DATABASE_NAME, DATABASE_HOST, DATABASE_PORT, DATABASE_PASSWORD, DEBUG, TIME_ZONE
        if self.connection is None:
            if DATABASE_NAME == '' or DATABASE_HOST == '' or DATABASE_USER == '':
                from django.core.exceptions import ImproperlyConfigured
                raise ImproperlyConfigured, "You need to specify DATABASE_NAME and DATABASE_HOST and DATABASE_USER in your Django settings file."
            self.connection = Database.connect(host=DATABASE_HOST+':'+DATABASE_PORT,user=DATABASE_USER,password=DATABASE_PASSWORD,database=DATABASE_NAME)
            #self.connection.set_isolation_level(1) # make transactions transparent to all cursors
        cursor = self.connection.cursor()
        #cursor.execute("SET TIME ZONE %s", [TIME_ZONE])
        if DEBUG:
            return base.CursorDebugWrapper(cursor, self)
        return cursor

    def commit(self):
        return self.connection.commit()

    def rollback(self):
        if self.connection:
            return self.connection.rollback()

    def close(self):
        if self.connection is not None:
            self.connection.close()
            self.connection = None

    def quote_name(self,name):
        if name.startswith('[') and name.endswith(']'):
            return name # Quoting once is enough.
        return '[%s]' % name

def get_last_insert_id(cursor, table_name, pk_name):
    cursor.execute("SELECT %s FROM %s WHERE %s = @@IDENTITY" % (pk_name, table_name, pk_name))
    return cursor.fetchone()[0]

def get_date_extract_sql(lookup_type, table_name):
    return "DATEPART(%s, %s)" % (lookup_type, table_name)

def get_date_trunc_sql(lookup_type, field_name):
    if lookup_type=='year':
        return "Convert(datetime, Convert(varchar, DATEPART(year, %s)) + '/01/01')" % field_name
    if lookup_type=='month':
        return "Convert(datetime, Convert(varchar, DATEPART(year, %s)) + '/' + Convert(varchar, DATEPART(month, %s)) + '/01')" % (field_name, field_name)
    if lookup_type=='day':
        return "Convert(datetime, Convert(varchar(12), %s))" % field_name

def get_limit_offset_sql(limit, offset=None):
    sql = "TOP %s " % limit
    #if offset and offset != 0:
    #    sql += " OFFSET %s" % offset
    #TODO
    return sql

def get_random_function_sql():
    return "RAND()"

def get_table_list(cursor):
    "Returns a list of table names in the current database."
    cursor.execute("""
        select name
        from dbo.sysobjects
        where OBJECTPROPERTY(id, N'IsUserTable') = 1 and status>0""")
    return [row[0] for row in cursor.fetchall()]

def get_relations(cursor, table_name):
    """
    Returns a dictionary of {field_index: (field_index_other_table, other_table)}
    representing all relationships to the given table. Indexes are 0-based.
    """
    relations = {}
    cursor.execute("""
        select o2.name, fkey1, rkey1
        from sysreferences r join sysobjects o2 on r.rkeyid = o2.id,
             sysobjects o
        where o.parent_obj = object_id(N'[%s]')
            AND r.constid = o.id
            AND o.xtype = 'F'
            AND r.keycnt = 1 """ % table_name)
    #1 key relation is not comprehensive maybe compound as the comment below ?
    for row in cursor.fetchall():
        relations[row[1]-1] = (row[2]-1, row[0])
    return relations

OPERATOR_MAPPING = {
    'exact': '= %s',
    'iexact': 'LIKE %s',
    'contains': 'LIKE %s',
    'icontains': 'LIKE %s',
    'ne': '!= %s',
    'gt': '> %s',
    'gte': '>= %s',
    'lt': '< %s',
    'lte': '<= %s',
    'startswith': 'LIKE %s',
    'endswith': 'LIKE %s',
    'istartswith': 'LIKE %s',
    'iendswith': 'LIKE %s',
}

# 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)',
    'BooleanField':      'bit',
    'CharField':         'varchar(%(maxlength)s)',
    'CommaSeparatedIntegerField': 'varchar(%(maxlength)s)',
    'DateField':         'datetime',
    'DateTimeField':     'datetime',
    'EmailField':        'varchar(75)',
    'FileField':         'varchar(100)',
    'FloatField':        'numeric(%(max_digits)s, %(decimal_places)s)',
    'ImageField':        'varchar(100)',
    'IntegerField':      'int',
    'IPAddressField':    'varchar(15)',
    'ManyToManyField':   None,
    'NullBooleanField':  'bit',
    'OneToOneField':     'int',
    'PhoneNumberField':  'varchar(20)',
    'PositiveIntegerField': 'int CONSTRAINT [CK_int_pos_%(name)s] CHECK ([%(name)s] > 0)',
    'PositiveSmallIntegerField': 'smallint CONSTRAINT [CK_smallint_pos_%(name)s] CHECK ([%(name)s] > 0)',
    'SlugField':         'varchar(50)',
    'SmallIntegerField': 'smallint',
    'TextField':         'text',
    'TimeField':         'datetime',
    'URLField':          'varchar(200)',
    'USStateField':      'varchar(2)',
}

# Maps type codes to Django Field types.
DATA_TYPES_REVERSE = {
}

def get_table_description(cursor, table_name):
    "Returns a description of the table, with the DB-API cursor.description interface."
    raise NotImplementedError