Ticket #1258: mssql.py

File mssql.py, 5.6 KB (added by Cheng <czhang.cmu+web@…>, 19 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
Back to Top