Source Code for Module trac.db.sqlite_backend

 1 # -*- coding: utf-8 -*- 
 2 # 
 3 # Copyright (C)2005-2010 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.org/wiki/TracLicense. 
 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://trac.edgewall.org/log/. 
 14 # 
 15 # Author: Christopher Lenz <cmlenz@gmx.de> 
 16 
 17 import os 
 18 import re 
 19 import weakref 
 20 
 21 from trac .config  import ListOption  
 22 from trac .core  import * 
 23 from trac .db .api  import IDatabaseConnector  
 24 from trac .db .util  import ConnectionWrapper , IterableCursor  
 25 from trac .util  import get_pkginfo , getuser  
 26 from trac .util .translation  import _  
 27 
 28 _like_escape_re  = re.compile(r'([/_%])') 
 29 
 30 _glob_escape_re  = re.compile(r'[*?\[]') 
 31 
 32 try: 
 33  import pysqlite2.dbapi2 as sqlite 
 34  have_pysqlite  = 2 
 35 except ImportError: 
 36  try: 
 37  import sqlite3 as sqlite 
 38  have_pysqlite  = 2 
 39  except ImportError: 
 40  have_pysqlite  = 0 
 41 
 42 if have_pysqlite  == 2: 
 43  # Force values to integers because PySQLite 2.2.0 had (2, 2, '0') 
 44  sqlite_version  = tuple([int(x ) for x  in sqlite.sqlite_version_info]) 
 45  sqlite_version_string  = sqlite.sqlite_version  
 46 
 47 -  class PyFormatCursor (sqlite.Cursor): 
 48 -  def _rollback_on_error (self, function, *args, **kwargs): 
 49  try: 
 50  return function(self, *args , **kwargs) 
 51  except sqlite.DatabaseError: 
 52  self.cnx .rollback () 
 53  raise 
 54 -  def execute (self, sql, args=None): 
 55  if args : 
 56  sql  = sql  % (('?',) * len(args )) 
 57  return self._rollback_on_error(sqlite.Cursor.execute , sql , 
 58  args  or []) 
 59 -  def executemany (self, sql, args): 
 60  if not args : 
 61  return 
 62  sql  = sql  % (('?',) * len(args [0])) 
 63  return self._rollback_on_error(sqlite.Cursor.executemany , sql , 
 64  args ) 
 65 
 66  # EagerCursor taken from the example in pysqlite's repository: 
 67  # 
 68  # http://code.google.com/p/pysqlite/source/browse/misc/eager.py 
 69  # 
 70  # Only change is to subclass it from PyFormatCursor instead of 
 71  # sqlite.Cursor. 
 72 
 73 -  class EagerCursor (PyFormatCursor): 
 74 -  def __init__ (self, con): 
 75  PyFormatCursor .__init__ (self, con) 
 76  self.rows = [] 
 77  self.pos = 0 
 78 
 79 -  def execute (self, *args): 
 80  result = PyFormatCursor .execute (self, *args ) 
 81  self.rows = PyFormatCursor .fetchall (self) 
 82  self.pos = 0 
 83  return result 
 84 
 85 -  def fetchone (self): 
 86  try: 
 87  row = self.rows[self.pos] 
 88  self.pos += 1 
 89  return row 
 90  except IndexError: 
 91  return None 
 92 
 93 -  def fetchmany (self, num=None): 
 94  if num is None: 
 95  num = self.arraysize 
 96 
 97  result = self.rows[self.pos:self.pos+num] 
 98  self.pos += num 
 99  return result 
100 
101 -  def fetchall (self): 
102  result = self.rows[self.pos:] 
103  self.pos = len(self.rows) 
104  return result 
105 
106 
107 # Mapping from "abstract" SQL types to DB-specific types 
108 _type_map  = { 
109  'int': 'integer', 
110  'int64': 'integer', 
111 } 
112 
113 
114 - def _to_sql (table): 
115  sql  = ["CREATE TABLE %s (" % table.name ] 
116  coldefs = [] 
117  for column in table.columns: 
118  ctype = column.type .lower() 
119  ctype = _type_map .get (ctype, ctype) 
120  if column.auto_increment: 
121  ctype = "integer PRIMARY KEY" 
122  elif len(table.key) == 1 and column.name  in table.key: 
123  ctype += " PRIMARY KEY" 
124  coldefs.append(" %s %s" % (column.name , ctype)) 
125  if len(table.key) > 1: 
126  coldefs.append(" UNIQUE (%s)" % ','.join (table.key)) 
127  sql .append(',\n'.join (coldefs) + '\n);') 
128  yield '\n'.join (sql ) 
129  for index in table.indices: 
130  unique = index.unique and 'UNIQUE' or '' 
131  yield "CREATE %s INDEX %s_%s_idx ON %s (%s);" % (unique, table.name , 
132  '_'.join (index.columns), table.name , ','.join (index.columns)) 
133 
134 
135 - class SQLiteConnector (Component): 
136  """Database connector for SQLite. 
137  
138  Database URLs should be of the form: 
139  {{{ 
140  sqlite:path/to/trac.db 
141  }}} 
142  """ 
143  implements (IDatabaseConnector ) 
144 
145  extensions  = ListOption ('sqlite', 'extensions', 
146  doc="""Paths to sqlite extensions, relative to Trac environment's 
147  directory or absolute. (''since 0.12'')""") 
148 
149 -  def __init__ (self): 
150  self._version = None 
151  self.error  = None 
152  self._extensions = None 
153 
154 -  def get_supported_schemes (self): 
155  if not have_pysqlite : 
156  self.error  = _ ("Cannot load Python bindings for SQLite") 
157  elif sqlite_version  >= (3, 3, 3) and sqlite.version_info[0] == 2 and \ 
158  sqlite.version_info < (2, 0, 7): 
159  self.error  = _ ("Need at least PySqlite %(version)s or higher", 
160  version ='2.0.7') 
161  elif (2, 5, 2) <= sqlite.version_info < (2, 5, 5): 
162  self.error  = _ ("PySqlite 2.5.2 - 2.5.4 break Trac, please use " 
163  "2.5.5 or higher") 
164  yield ('sqlite', self.error  and -1 or 1) 
165 
166 -  def get_connection (self, path, log=None, params={}): 
167  if not self._version: 
168  self._version = get_pkginfo (sqlite).get ( 
169  'version', '%d.%d.%s' % sqlite.version_info) 
170  self.env .systeminfo.extend([('SQLite', sqlite_version_string ), 
171  ('pysqlite', self._version)]) 
172  self.required  = True 
173  # construct list of sqlite extension libraries 
174  if self._extensions is None: 
175  self._extensions = [] 
176  for extpath in self.extensions : 
177  if not os.path .isabs(extpath): 
178  extpath = os.path .join (self.env .path , extpath) 
179  self._extensions.append(extpath) 
180  params ['extensions'] = self._extensions 
181  return SQLiteConnection (path , log , params ) 
182 
183 -  def init_db (self, path, log=None, params={}): 
184  if path  != ':memory:': 
185  # make the directory to hold the database 
186  if os.path .exists (path ): 
187  raise TracError (_ ('Database already exists at %(path)s', 
188  path =path )) 
189  dir = os.path .dirname(path ) 
190  if not os.path .exists (dir): 
191  os.makedirs (dir) 
192  if isinstance(path , unicode): # needed with 2.4.0 
193  path  = path .encode('utf-8') 
194  cnx  = sqlite.connect(path , timeout =int(params .get ('timeout', 10000))) 
195  cursor  = cnx .cursor () 
196  from trac .db_default  import schema  
197  for table in schema : 
198  for stmt in self.to_sql (table): 
199  cursor .execute (stmt) 
200  cnx .commit () 
201 
202 -  def to_sql (self, table): 
203  return _to_sql(table) 
204 
205 -  def alter_column_types (self, table, columns): 
206  """Yield SQL statements altering the type of one or more columns of 
207  a table. 
208  
209  Type changes are specified as a `columns` dict mapping column names 
210  to `(from, to)` SQL type tuples. 
211  """ 
212  for name , (from_, to) in sorted(columns.iteritems()): 
213  if _type_map .get (to, to) != _type_map .get (from_, from_): 
214  raise NotImplementedError('Conversion from %s to %s is not ' 
215  'implemented' % (from_, to)) 
216  return () 
217 
218 -  def backup (self, dest_file): 
219  """Simple SQLite-specific backup of the database. 
220  
221  @param dest_file: Destination file basename 
222  """ 
223  import shutil 
224  db_str = self.config .get ('trac', 'database') 
225  try: 
226  db_str = db_str[:db_str.index('?')] 
227  except ValueError: 
228  pass 
229  db_name = os.path .join (self.env .path , db_str[7:]) 
230  shutil.copy(db_name, dest_file) 
231  if not os.path .exists (dest_file): 
232  raise TracError (_ ("No destination file created")) 
233  return dest_file 
234 
235 
236 - class SQLiteConnection (ConnectionWrapper): 
237  """Connection wrapper for SQLite.""" 
238 
239  __slots__ = ['_active_cursors', '_eager'] 
240 
241  poolable  = have_pysqlite  and sqlite_version  >= (3, 3, 8) \ 
242  and sqlite.version_info >= (2, 5, 0) 
243 
244 -  def __init__ (self, path, log=None, params={}): 
245  if have_pysqlite  == 0: 
246  raise TracError (_ ("Cannot load Python bindings for SQLite")) 
247  self.cnx  = None 
248  if path  != ':memory:': 
249  if not os.access(path , os.F_OK): 
250  raise TracError (_ ('Database "%(path)s" not found.', path =path )) 
251 
252  dbdir = os.path .dirname(path ) 
253  if not os.access(path , os.R_OK + os.W_OK) or \ 
254  not os.access(dbdir, os.R_OK + os.W_OK): 
255  raise TracError ( 
256  _ ('The user %(user)s requires read _and_ write ' 
257  'permissions to the database file %(path)s ' 
258  'and the directory it is located in.', 
259  user=getuser (), path =path )) 
260 
261  self._active_cursors = weakref.WeakKeyDictionary() 
262  timeout  = int(params .get ('timeout', 10.0)) 
263  self._eager = params .get ('cursor', 'eager') == 'eager' 
264  # eager is default, can be turned off by specifying ?cursor= 
265  if isinstance(path , unicode): # needed with 2.4.0 
266  path  = path .encode('utf-8') 
267  cnx  = sqlite.connect(path , detect_types=sqlite.PARSE_DECLTYPES, 
268  check_same_thread=sqlite_version  < (3, 3, 1), 
269  timeout =timeout ) 
270  # load extensions 
271  extensions  = params .get ('extensions', []) 
272  if len(extensions ) > 0: 
273  cnx .enable_load_extension(True) 
274  for ext in extensions : 
275  cnx .load_extension(ext) 
276  cnx .enable_load_extension(False) 
277 
278  ConnectionWrapper .__init__ (self, cnx , log ) 
279 
280 -  def cursor (self): 
281  cursor  = self.cnx .cursor ((PyFormatCursor , EagerCursor )[self._eager]) 
282  self._active_cursors[cursor ] = True 
283  cursor .cnx  = self 
284  return IterableCursor (cursor , self.log ) 
285 
286 -  def rollback (self): 
287  for cursor  in self._active_cursors.keys (): 
288  cursor .close () 
289  self.cnx .rollback () 
290 
291 -  def cast (self, column, type): 
292  if sqlite_version  >= (3, 2, 3): 
293  return 'CAST(%s AS %s)' % (column, _type_map .get (type , type )) 
294  elif type  == 'int': 
295  # hack to force older SQLite versions to convert column to an int 
296  return '1*' + column 
297  else: 
298  return column 
299 
300 -  def concat (self, *args): 
301  return '||'.join (args ) 
302 
303 -  def like (self): 
304  """Return a case-insensitive LIKE clause.""" 
305  if sqlite_version  >= (3, 1, 0): 
306  return "LIKE %s ESCAPE '/'" 
307  else: 
308  return 'LIKE %s' 
309 
310 -  def like_escape (self, text): 
311  if sqlite_version  >= (3, 1, 0): 
312  return _like_escape_re .sub(r'/1円', text ) 
313  else: 
314  return text  
315 
316 -  def prefix_match (self): 
317  """Return a case sensitive prefix-matching operator.""" 
318  return 'GLOB %s' 
319 
320 -  def prefix_match_value (self, prefix): 
321  """Return a value for case sensitive prefix-matching operator.""" 
322  return _glob_escape_re .sub(lambda m: '[%s]' % m.group (0), prefix) + '*' 
323 
324 -  def quote (self, identifier): 
325  """Return the quoted identifier.""" 
326  return "`%s`" % identifier.replace ('`', '``') 
327 
328 -  def get_last_id (self, cursor, table, column='id'): 
329  return cursor .lastrowid 
330 
331 -  def update_sequence (self, cursor, table, column='id'): 
332  # SQLite handles sequence updates automagically 
333  # http://www.sqlite.org/autoinc.html 
334  pass 
335 
336 -  def drop_table (self, table): 
337  cursor  = self.cursor () 
338  cursor .execute ("DROP TABLE IF EXISTS " + self.quote (table)) 
339