1
from sqlobject.dbconnection import DBAPI
2
from sqlobject.col import popKey
9
class SQLiteConnection(DBAPI):
11
supportTransactions = True
15
def __init__(self, filename, autoCommit=1, **kw):
20
from pysqlite2 import dbapi2 as sqlite
26
self.filename = filename # full path to sqlite-db-file
27
self._memory = filename == ':memory:'
31
"You must use sqlite2 to use in-memory databases")
36
opts["isolation_level"] = None
39
warnings.warn(DeprecationWarning("pysqlite2 does not support the encoding option"))
40
opts["detect_types"] = sqlite.PARSE_DECLTYPES
41
for col_type in "text", "char", "varchar":
42
sqlite.register_converter(col_type, stop_pysqlite2_converting_strings_to_unicode)
43
sqlite.register_converter(col_type.upper(), stop_pysqlite2_converting_strings_to_unicode)
45
from sqlite import encode, decode
48
sqlite.encode = base64.encodestring
49
sqlite.decode = base64.decodestring
51
sqlite.encode = encode
52
sqlite.decode = decode
54
if sqlite2_Binary is None:
55
sqlite2_Binary = sqlite.Binary
56
sqlite.Binary = lambda s: sqlite2_Binary(sqlite.encode(s))
58
opts['autocommit'] = bool(autoCommit)
60
opts['encoding'] = popKey(kw, 'encoding')
62
opts['mode'] = int(popKey(kw, 'mode'), 0)
64
opts['timeout'] = float(popKey(kw, 'timeout'))
65
if 'check_same_thread' in kw:
66
opts["check_same_thread"] = bool(popKey(kw, 'check_same_thread'))
67
# use only one connection for sqlite - supports multiple)
68
# cursors per connection
69
self._connOptions = opts
70
DBAPI.__init__(self, **kw)
72
self._threadOrigination = {}
74
self._memoryConn = sqlite.connect(
75
self.filename, **self._connOptions)
77
def connectionFromURI(cls, uri):
78
user, password, host, port, path, args = cls._parseURI(uri)
79
assert host is None, (
80
"SQLite can only be used locally (with a URI like "
81
"sqlite:///file or sqlite:/file, not %r)" % uri)
82
assert user is None and password is None, (
83
"You may not provide usernames or passwords for SQLite "
85
if path == "/:memory:":
87
return cls(filename=path, **args)
88
connectionFromURI = classmethod(connectionFromURI)
91
return 'sqlite:///%s' % self.filename
93
def getConnection(self):
94
# SQLite can't share connections between threads, and so can't
95
# pool connections. Since we are isolating threads here, we
96
# don't have to worry about locking as much.
98
return self.makeConnection()
99
threadid = thread.get_ident()
100
if (self._pool is not None
101
and self._threadPool.has_key(threadid)):
102
conn = self._threadPool[threadid]
103
del self._threadPool[threadid]
104
if conn in self._pool:
105
self._pool.remove(conn)
107
conn = self.makeConnection()
108
if self._pool is not None:
109
self._threadOrigination[id(conn)] = threadid
110
self._connectionNumbers[id(conn)] = self._connectionCount
111
self._connectionCount += 1
114
if self._pool is not None:
115
s += ' pool=[%s]' % ', '.join([str(self._connectionNumbers[id(v)]) for v in self._pool])
116
self.printDebug(conn, s, 'Pool')
119
def releaseConnection(self, conn, explicit=False):
122
threadid = self._threadOrigination.get(id(conn))
123
DBAPI.releaseConnection(self, conn, explicit=explicit)
124
if (self._pool is not None and threadid
125
and not self._threadPool.has_key(threadid)):
126
self._threadPool[threadid] = conn
128
if self._pool and conn in self._pool:
129
self._pool.remove(conn)
132
def _setAutoCommit(self, conn, auto):
135
conn.isolation_level = None
137
conn.isolation_level = ""
139
conn.autocommit = auto
141
def _setIsolationLevel(self, conn, level):
142
if not using_sqlite2:
144
conn.isolation_level = level
146
def makeConnection(self):
148
return self._memoryConn
149
return sqlite.connect(self.filename, **self._connOptions)
151
def _queryInsertID(self, conn, soInstance, id, names, values):
152
table = soInstance.sqlmeta.table
153
idName = soInstance.sqlmeta.idName
156
names = [idName] + names
157
values = [id] + values
158
q = self._insertSQL(table, names, values)
160
self.printDebug(conn, q, 'QueryIns')
162
# lastrowid is a DB-API extension from "PEP 0249":
164
id = int(c.lastrowid)
166
self.printDebug(conn, id, 'QueryIns', 'result')
169
def _insertSQL(self, table, names, values):
172
# INSERT INTO table () VALUES () isn't allowed in
173
# SQLite (though it is in other databases)
174
return ("INSERT INTO %s VALUES (NULL)" % table)
176
return DBAPI._insertSQL(self, table, names, values)
178
def _queryAddLimitOffset(self, query, start, end):
180
return "%s LIMIT %i" % (query, end)
182
return "%s LIMIT 0 OFFSET %i" % (query, start)
183
return "%s LIMIT %i OFFSET %i" % (query, end-start, start)
185
def createColumn(self, soClass, col):
186
return col.sqliteCreateSQL()
188
def createReferenceConstraint(self, soClass, col):
191
def createIDColumn(self, soClass):
192
key_type = {int: "INTEGER", str: "TEXT"}[soClass.sqlmeta.idType]
193
return '%s %s PRIMARY KEY' % (soClass.sqlmeta.idName, key_type)
195
def joinSQLType(self, join):
196
return 'INT NOT NULL'
198
def tableExists(self, tableName):
199
result = self.queryOne("SELECT tbl_name FROM sqlite_master WHERE type='table' AND tbl_name = '%s'" % tableName)
200
# turn it into a boolean:
201
return not not result
203
def createIndexSQL(self, soClass, index):
204
return index.sqliteCreateIndexSQL(soClass)
206
def addColumn(self, tableName, column):
207
self.query('ALTER TABLE %s ADD COLUMN %s' %
209
column.sqliteCreateSQL()))
211
def delColumn(self, tableName, column):
212
pass # Oops! There is no DROP COLUMN in SQLite
214
def stop_pysqlite2_converting_strings_to_unicode(s):