|
39
|
1 # -*- coding: utf-8 -*-
|
|
|
2 #
|
|
|
3 # Copyright (C) 2005 Edgewall Software
|
|
|
4 # Copyright (C) 2005 Christopher Lenz <cmlenz@gmx.de>
|
|
|
5 # All rights reserved.
|
|
|
6 #
|
|
|
7 # This software is licensed as described in the file COPYING, which
|
|
|
8 # you should have received as part of this distribution. The terms
|
|
|
9 # are also available at http://trac.edgewall.com/license.html.
|
|
|
10 #
|
|
|
11 # This software consists of voluntary contributions made by many
|
|
|
12 # individuals. For the exact contribution history, see the revision
|
|
|
13 # history and logs, available at http://projects.edgewall.com/trac/.
|
|
|
14 #
|
|
|
15 # Author: Christopher Lenz <cmlenz@gmx.de>
|
|
|
16
|
|
|
17 import os
|
|
|
18 import re
|
|
|
19 import weakref
|
|
|
20
|
|
|
21 from trac.core import *
|
|
|
22 from trac.db.api import IDatabaseConnector
|
|
|
23 from trac.db.util import ConnectionWrapper
|
|
|
24
|
|
|
25 _like_escape_re = re.compile(r'([/_%])')
|
|
|
26
|
|
|
27 try:
|
|
|
28 import pysqlite2.dbapi2 as sqlite
|
|
|
29 have_pysqlite = 2
|
|
|
30 _ver = sqlite.sqlite_version_info
|
|
|
31 sqlite_version = _ver[0] * 10000 + _ver[1] * 100 + int(_ver[2])
|
|
|
32
|
|
|
33 class PyFormatCursor(sqlite.Cursor):
|
|
|
34 def _rollback_on_error(self, function, *args, **kwargs):
|
|
|
35 try:
|
|
|
36 return function(self, *args, **kwargs)
|
|
|
37 except sqlite.OperationalError, e:
|
|
|
38 self.cnx.rollback()
|
|
|
39 raise
|
|
|
40 def execute(self, sql, args=None):
|
|
|
41 if args:
|
|
|
42 sql = sql % (('?',) * len(args))
|
|
|
43 return self._rollback_on_error(sqlite.Cursor.execute, sql,
|
|
|
44 args or [])
|
|
|
45 def executemany(self, sql, args=None):
|
|
|
46 if args:
|
|
|
47 sql = sql % (('?',) * len(args[0]))
|
|
|
48 return self._rollback_on_error(sqlite.Cursor.executemany, sql,
|
|
|
49 args or [])
|
|
|
50
|
|
|
51 except ImportError:
|
|
|
52 try:
|
|
|
53 import sqlite
|
|
|
54 have_pysqlite = 1
|
|
|
55 _ver = sqlite._sqlite.sqlite_version_info()
|
|
|
56 sqlite_version = _ver[0] * 10000 + _ver[1] * 100 + _ver[2]
|
|
|
57
|
|
|
58 class SQLiteUnicodeCursor(sqlite.Cursor):
|
|
|
59 def _convert_row(self, row):
|
|
|
60 return tuple([(isinstance(v, str) and [v.decode('utf-8')] or [v])[0]
|
|
|
61 for v in row])
|
|
|
62 def fetchone(self):
|
|
|
63 row = sqlite.Cursor.fetchone(self)
|
|
|
64 return row and self._convert_row(row) or None
|
|
|
65 def fetchmany(self, num):
|
|
|
66 rows = sqlite.Cursor.fetchmany(self, num)
|
|
|
67 return rows != None and [self._convert_row(row)
|
|
|
68 for row in rows] or []
|
|
|
69 def fetchall(self):
|
|
|
70 rows = sqlite.Cursor.fetchall(self)
|
|
|
71 return rows != None and [self._convert_row(row)
|
|
|
72 for row in rows] or []
|
|
|
73 except ImportError:
|
|
|
74 have_pysqlite = 0
|
|
|
75
|
|
|
76 def _to_sql(table):
|
|
|
77 sql = ["CREATE TABLE %s (" % table.name]
|
|
|
78 coldefs = []
|
|
|
79 for column in table.columns:
|
|
|
80 ctype = column.type.lower()
|
|
|
81 if column.auto_increment:
|
|
|
82 ctype = "integer PRIMARY KEY"
|
|
|
83 elif len(table.key) == 1 and column.name in table.key:
|
|
|
84 ctype += " PRIMARY KEY"
|
|
|
85 elif ctype == "int":
|
|
|
86 ctype = "integer"
|
|
|
87 coldefs.append(" %s %s" % (column.name, ctype))
|
|
|
88 if len(table.key) > 1:
|
|
|
89 coldefs.append(" UNIQUE (%s)" % ','.join(table.key))
|
|
|
90 sql.append(',\n'.join(coldefs) + '\n);')
|
|
|
91 yield '\n'.join(sql)
|
|
|
92 for index in table.indices:
|
|
|
93 yield "CREATE INDEX %s_%s_idx ON %s (%s);" % (table.name,
|
|
|
94 '_'.join(index.columns), table.name, ','.join(index.columns))
|
|
|
95
|
|
|
96
|
|
|
97
|
|
|
98
|
|
|
99 class SQLiteConnector(Component):
|
|
|
100 """SQLite database support."""
|
|
|
101 implements(IDatabaseConnector)
|
|
|
102
|
|
|
103 def get_supported_schemes(self):
|
|
|
104 return [('sqlite', 1)]
|
|
|
105
|
|
|
106 def get_connection(self, path, params={}):
|
|
|
107 return SQLiteConnection(path, params)
|
|
|
108
|
|
|
109 def init_db(cls, path, params={}):
|
|
|
110 if path != ':memory:':
|
|
|
111 # make the directory to hold the database
|
|
|
112 if os.path.exists(path):
|
|
|
113 raise TracError, 'Database already exists at %s' % path
|
|
|
114 os.makedirs(os.path.split(path)[0])
|
|
|
115 cnx = sqlite.connect(path, timeout=int(params.get('timeout', 10000)))
|
|
|
116 cursor = cnx.cursor()
|
|
|
117 from trac.db_default import schema
|
|
|
118 for table in schema:
|
|
|
119 for stmt in cls.to_sql(table):
|
|
|
120 cursor.execute(stmt)
|
|
|
121 cnx.commit()
|
|
|
122
|
|
|
123 def to_sql(cls, table):
|
|
|
124 return _to_sql(table)
|
|
|
125
|
|
|
126
|
|
|
127 class SQLiteConnection(ConnectionWrapper):
|
|
|
128 """Connection wrapper for SQLite."""
|
|
|
129
|
|
|
130 __slots__ = ['_active_cursors']
|
|
|
131 poolable = False
|
|
|
132
|
|
|
133 def __init__(self, path, params={}):
|
|
|
134 assert have_pysqlite > 0
|
|
|
135 self.cnx = None
|
|
|
136 if path != ':memory:':
|
|
|
137 if not os.access(path, os.F_OK):
|
|
|
138 raise TracError, 'Database "%s" not found.' % path
|
|
|
139
|
|
|
140 dbdir = os.path.dirname(path)
|
|
|
141 if not os.access(path, os.R_OK + os.W_OK) or \
|
|
|
142 not os.access(dbdir, os.R_OK + os.W_OK):
|
|
|
143 from getpass import getuser
|
|
|
144 raise TracError, 'The user %s requires read _and_ write ' \
|
|
|
145 'permission to the database file %s and the ' \
|
|
|
146 'directory it is located in.' \
|
|
|
147 % (getuser(), path)
|
|
|
148
|
|
|
149 if have_pysqlite == 2:
|
|
|
150 self._active_cursors = weakref.WeakKeyDictionary()
|
|
|
151 timeout = int(params.get('timeout', 10.0))
|
|
|
152 cnx = sqlite.connect(path, detect_types=sqlite.PARSE_DECLTYPES,
|
|
|
153 timeout=timeout)
|
|
|
154 else:
|
|
|
155 timeout = int(params.get('timeout', 10000))
|
|
|
156 cnx = sqlite.connect(path, timeout=timeout, encoding='utf-8')
|
|
|
157
|
|
|
158 ConnectionWrapper.__init__(self, cnx)
|
|
|
159
|
|
|
160 if have_pysqlite == 2:
|
|
|
161 def cursor(self):
|
|
|
162 cursor = self.cnx.cursor(PyFormatCursor)
|
|
|
163 self._active_cursors[cursor] = True
|
|
|
164 cursor.cnx = self
|
|
|
165 return cursor
|
|
|
166
|
|
|
167 def rollback(self):
|
|
|
168 for cursor in self._active_cursors.keys():
|
|
|
169 cursor.close()
|
|
|
170 self.cnx.rollback()
|
|
|
171
|
|
|
172 else:
|
|
|
173 def cursor(self):
|
|
|
174 self.cnx._checkNotClosed("cursor")
|
|
|
175 return SQLiteUnicodeCursor(self.cnx, self.cnx.rowclass)
|
|
|
176
|
|
|
177 def cast(self, column, type):
|
|
|
178 return column
|
|
|
179
|
|
|
180 def like(self):
|
|
|
181 if sqlite_version >= 30100:
|
|
|
182 return "LIKE %s ESCAPE '/'"
|
|
|
183 else:
|
|
|
184 return 'LIKE %s'
|
|
|
185
|
|
|
186 def like_escape(self, text):
|
|
|
187 if sqlite_version >= 30100:
|
|
|
188 return _like_escape_re.sub(r'/\1', text)
|
|
|
189 else:
|
|
|
190 return text
|
|
|
191
|
|
|
192 if have_pysqlite == 2:
|
|
|
193 def get_last_id(self, cursor, table, column='id'):
|
|
|
194 return cursor.lastrowid
|
|
|
195 else:
|
|
|
196 def get_last_id(self, cursor, table, column='id'):
|
|
|
197 return self.cnx.db.sqlite_last_insert_rowid()
|
