Logo Search packages:      
Sourcecode: s3ql version File versions  Download package

database.py

00001 '''
database.py - this file is part of S3QL (http://s3ql.googlecode.com)

Copyright (C) 2008-2009 Nikolaus Rath <Nikolaus@rath.org>

This program can be distributed under the terms of the GNU LGPL.


Module Attributes:
-----------

:initsql:      SQL commands that are executed whenever a new
               connection is created.
                 
'''

from __future__ import division, print_function

import logging
import apsw
import os
import types
from .common import QuietError

__all__ = ['Connection', 'NoUniqueValueError', 'NoSuchRowError' ]

log = logging.getLogger("database")

sqlite_ver = tuple([ int(x) for x in apsw.sqlitelibversion().split('.') ])
if sqlite_ver < (3, 7, 0):
    raise QuietError('SQLite version too old, must be 3.7.0 or newer!\n')
        
initsql = ('PRAGMA foreign_keys = OFF',
           'PRAGMA locking_mode = EXCLUSIVE',
           'PRAGMA recursize_triggers = on',
           'PRAGMA page_size = 4096',
           'PRAGMA wal_autocheckpoint = 25000',
           'PRAGMA temp_store = FILE',
           'PRAGMA legacy_file_format = off',
           )
    
00042 class Connection(object):
    '''
    This class wraps an APSW connection object. It should be used instead of any
    native APSW cursors.
    
    It provides methods to directly execute SQL commands and creates apsw
    cursors dynamically.
    
    Instances are not thread safe. They can be passed between threads,
    but must not be called concurrently.
    
    Instances also takes care of converting bytes objects into buffer
    objects and back, so that they are stored as BLOBS in the database. If you
    want to store TEXT, you need to supply unicode objects instead. (This
    functionality is only needed under Python 2.x, under Python 3.x the apsw
    module already behaves in the correct way).
    
    Attributes
    ----------
    
    :conn:     apsw connection object
    :cur:      default cursor, to be used for all queries
               that do not return a ResultSet (i.e., that finalize
               the cursor when they return)
    '''

    def __init__(self, file_, fast_mode=False):
        self.conn = apsw.Connection(file_)
        self.file = file_
        
        cur = self.conn.cursor()
        for s in initsql:
            cur.execute(s)

        self.fast_mode(fast_mode)
        
00078     def fast_mode(self, on):
        '''Switch to fast, but insecure mode
        
        In fast mode, SQLite operates as quickly as possible, but
        application and system crashes may lead to data corruption.
        '''
        
        # WAL mode causes trouble with e.g. copy_tree, so we
        # always disable WAL for now. See 
        # http://article.gmane.org/gmane.comp.db.sqlite.general/65243
        on = True 
        cur = self.conn.cursor()
        if on:
            cur.execute('PRAGMA synchronous = OFF')
            cur.execute('PRAGMA journal_mode = OFF')
        else:                
            cur.execute('PRAGMA synchronous = NORMAL')
            cur.execute('PRAGMA journal_mode = WAL')
            
        
    def close(self):
        self.conn.close()
        
00101     def get_size(self):
        '''Return size of database file'''
    
        if self.file is not None and self.file not in ('', ':memory:'):
            return os.path.getsize(self.file)
        else:
            return 0
            
00109     def query(self, *a, **kw):
        '''Execute the given SQL statement. Return ResultSet.
        
        Transforms buffer() to bytes() and vice versa. If the
        caller may not retrieve all rows of the result, it
        should delete the `ResultSet` object has soon as 
        possible to terminate the SQL statement.
        '''

        return ResultSet(self._execute(*a, **kw))

00120     def execute(self, *a, **kw):
        '''Execute the given SQL statement. Return number of affected rows '''

        self._execute(*a, **kw)
        return self.changes()

00126     def rowid(self, *a, **kw):
        """Execute SQL statement and return last inserted rowid"""

        self._execute(*a, **kw)
        return self.conn.last_insert_rowid()

00132     def _execute(self, statement, bindings=None):
        '''Execute the given SQL statement 
        
        This method takes care of converting str/bytes to buffer
        objects.
        '''

        if isinstance(bindings, types.GeneratorType):
            bindings = list(bindings)

        # Convert bytes to buffer
        if isinstance(bindings, dict):
            newbindings = dict()
            for key in bindings:
                if isinstance(bindings[key], bytes):
                    newbindings[key] = buffer(bindings[key])
                else:
                    newbindings[key] = bindings[key]
        elif isinstance(bindings, (list, tuple)):
            newbindings = [ (val if not isinstance(val, bytes) else buffer(val))
                           for val in bindings ]
        else:
            newbindings = bindings

        if bindings is not None:
            return self.conn.cursor().execute(statement, newbindings)
        else:
            return self.conn.cursor().execute(statement)

00161     def has_val(self, *a, **kw):
        '''Execute statement and check if it gives result rows'''

        res = self._execute(*a, **kw)
        try:
            res.next()
        except StopIteration:
            return False
        else:
            # Finish the active SQL statement
            res.close()
            return True

00174     def get_val(self, *a, **kw):
        """Execute statement and return first element of first result row.
        
        If there is no result row, raises `NoSuchRowError`. If there is more
        than one row, raises `NoUniqueValueError`.
        """

        return self.get_row(*a, **kw)[0]

00183     def get_list(self, *a, **kw):
        """Execute select statement and returns result list"""

        return list(self.query(*a, **kw))

00188     def get_row(self, *a, **kw):
        """Execute select statement and return first row.
        
        If there are no result rows, raises `NoSuchRowError`. If there is more
        than one result row, raises `NoUniqueValueError`.
        """

        res = ResultSet(self._execute(*a, **kw))
        try:
            row = res.next()
        except StopIteration:
            raise NoSuchRowError()
        try:
            res.next()
        except StopIteration:
            # Fine, we only wanted one row
            pass
        else:
            # Finish the active SQL statement
            res.close()
            raise NoUniqueValueError()

        return row

00212     def last_rowid(self):
        """Return rowid most recently inserted in the current thread"""

        return self.conn.last_insert_rowid()

00217     def changes(self):
        """Return number of rows affected by most recent sql statement"""

        return self.conn.changes()


00223 class NoUniqueValueError(Exception):
    '''Raised if get_val or get_row was called with a query 
    that generated more than one result row.
    '''

    def __str__(self):
        return 'Query generated more than 1 result row'
    
    
00232 class NoSuchRowError(Exception):
    '''Raised if the query did not produce any result rows'''
    
    def __str__(self):
        return 'Query produced 0 result rows'
    

00239 class ResultSet(object):
    '''Iterator over the result of an SQL query
    
    This class automatically converts back from buffer() to bytes().'''

    def __init__(self, cur):
        self.cur = cur

    def __iter__(self):
        return self

    def next(self):
        return [ (col if not isinstance(col, buffer) else bytes(col))
                  for col in self.cur.next() ]

00254     def close(self):
        '''Finish query transaction'''
        self.cur.close()

    # Once the ResultSet goes out of scope, the cursor goes out of scope
    # too (because query() uses a fresh cursor), so we don't have to
    # take any special precautions to finish the active SQL statement.  

Generated by  Doxygen 1.6.0   Back to index