Code

Ticket #1258: mssql.py

File mssql.py, 5.6 KB (added by Cheng <czhang.cmu+web@…>, 8 years ago)

django/core/db/backend/mssql.py

Line 
1"""
2Microsoft SQL database backend for Django.
3
4Requires pymssql: http://pymssql.sourceforge.net
5"""
6
7from django.core.db import base, typecasts
8from django.core.db.dicthelpers import *
9import pymssql as Database
10
11DatabaseError = Database.DatabaseError
12
13class DatabaseWrapper:
14    def __init__(self):
15        self.connection = None
16        self.queries = []
17
18    def cursor(self):
19        from django.conf.settings import DATABASE_USER, DATABASE_NAME, DATABASE_HOST, DATABASE_PORT, DATABASE_PASSWORD, DEBUG, TIME_ZONE
20        if self.connection is None:
21            if DATABASE_NAME == '' or DATABASE_HOST == '' or DATABASE_USER == '':
22                from django.core.exceptions import ImproperlyConfigured
23                raise ImproperlyConfigured, "You need to specify DATABASE_NAME and DATABASE_HOST and DATABASE_USER in your Django settings file."
24            self.connection = Database.connect(host=DATABASE_HOST+':'+DATABASE_PORT,user=DATABASE_USER,password=DATABASE_PASSWORD,database=DATABASE_NAME)
25            #self.connection.set_isolation_level(1) # make transactions transparent to all cursors
26        cursor = self.connection.cursor()
27        #cursor.execute("SET TIME ZONE %s", [TIME_ZONE])
28        if DEBUG:
29            return base.CursorDebugWrapper(cursor, self)
30        return cursor
31
32    def commit(self):
33        return self.connection.commit()
34
35    def rollback(self):
36        if self.connection:
37            return self.connection.rollback()
38
39    def close(self):
40        if self.connection is not None:
41            self.connection.close()
42            self.connection = None
43
44    def quote_name(self,name):
45        if name.startswith('[') and name.endswith(']'):
46            return name # Quoting once is enough.
47        return '[%s]' % name
48
49def get_last_insert_id(cursor, table_name, pk_name):
50    cursor.execute("SELECT %s FROM %s WHERE %s = @@IDENTITY" % (pk_name, table_name, pk_name))
51    return cursor.fetchone()[0]
52
53def get_date_extract_sql(lookup_type, table_name):
54    return "DATEPART(%s, %s)" % (lookup_type, table_name)
55
56def get_date_trunc_sql(lookup_type, field_name):
57    if lookup_type=='year':
58        return "Convert(datetime, Convert(varchar, DATEPART(year, %s)) + '/01/01')" % field_name
59    if lookup_type=='month':
60        return "Convert(datetime, Convert(varchar, DATEPART(year, %s)) + '/' + Convert(varchar, DATEPART(month, %s)) + '/01')" % (field_name, field_name)
61    if lookup_type=='day':
62        return "Convert(datetime, Convert(varchar(12), %s))" % field_name
63
64def get_limit_offset_sql(limit, offset=None):
65    sql = "TOP %s " % limit
66    #if offset and offset != 0:
67    #    sql += " OFFSET %s" % offset
68    #TODO
69    return sql
70
71def get_random_function_sql():
72    return "RAND()"
73
74def get_table_list(cursor):
75    "Returns a list of table names in the current database."
76    cursor.execute("""
77        select name
78        from dbo.sysobjects
79        where OBJECTPROPERTY(id, N'IsUserTable') = 1 and status>0""")
80    return [row[0] for row in cursor.fetchall()]
81
82def get_relations(cursor, table_name):
83    """
84    Returns a dictionary of {field_index: (field_index_other_table, other_table)}
85    representing all relationships to the given table. Indexes are 0-based.
86    """
87    relations = {}
88    cursor.execute("""
89        select o2.name, fkey1, rkey1
90        from sysreferences r join sysobjects o2 on r.rkeyid = o2.id,
91             sysobjects o
92        where o.parent_obj = object_id(N'[%s]')
93            AND r.constid = o.id
94            AND o.xtype = 'F'
95            AND r.keycnt = 1 """ % table_name)
96    #1 key relation is not comprehensive maybe compound as the comment below ?
97    for row in cursor.fetchall():
98        relations[row[1]-1] = (row[2]-1, row[0])
99    return relations
100
101OPERATOR_MAPPING = {
102    'exact': '= %s',
103    'iexact': 'LIKE %s',
104    'contains': 'LIKE %s',
105    'icontains': 'LIKE %s',
106    'ne': '!= %s',
107    'gt': '> %s',
108    'gte': '>= %s',
109    'lt': '< %s',
110    'lte': '<= %s',
111    'startswith': 'LIKE %s',
112    'endswith': 'LIKE %s',
113    'istartswith': 'LIKE %s',
114    'iendswith': 'LIKE %s',
115}
116
117# This dictionary maps Field objects to their associated MS SQL column
118# types, as strings. Column-type strings can contain format strings; they'll
119# be interpolated against the values of Field.__dict__ before being output.
120# If a column type is set to None, it won't be included in the output.
121DATA_TYPES = {
122    'AutoField':         'int IDENTITY (1, 1)',
123    'BooleanField':      'bit',
124    'CharField':         'varchar(%(maxlength)s)',
125    'CommaSeparatedIntegerField': 'varchar(%(maxlength)s)',
126    'DateField':         'datetime',
127    'DateTimeField':     'datetime',
128    'EmailField':        'varchar(75)',
129    'FileField':         'varchar(100)',
130    'FloatField':        'numeric(%(max_digits)s, %(decimal_places)s)',
131    'ImageField':        'varchar(100)',
132    'IntegerField':      'int',
133    'IPAddressField':    'varchar(15)',
134    'ManyToManyField':   None,
135    'NullBooleanField':  'bit',
136    'OneToOneField':     'int',
137    'PhoneNumberField':  'varchar(20)',
138    'PositiveIntegerField': 'int CONSTRAINT [CK_int_pos_%(name)s] CHECK ([%(name)s] > 0)',
139    'PositiveSmallIntegerField': 'smallint CONSTRAINT [CK_smallint_pos_%(name)s] CHECK ([%(name)s] > 0)',
140    'SlugField':         'varchar(50)',
141    'SmallIntegerField': 'smallint',
142    'TextField':         'text',
143    'TimeField':         'datetime',
144    'URLField':          'varchar(200)',
145    'USStateField':      'varchar(2)',
146}
147
148# Maps type codes to Django Field types.
149DATA_TYPES_REVERSE = {
150}
151
152def get_table_description(cursor, table_name):
153    "Returns a description of the table, with the DB-API cursor.description interface."
154    raise NotImplementedError
155