MobileBlur

'db2://DSN=dsn;UID=user;PWD=pass'
'firebird://username:password@hostname/database'
'firebird_embedded://username:password@c://path'
'informix://user:password@server:3050/database'
'informixu://user:password@server:3050/database' # unicode informix
'google:datastore' # for google app engine datastore
'google:sql' # for google app engine with sql (mysql compatible)
'teradata://DSN=dsn;UID=user;PWD=pass' # experimental 
'teradata://DSN=dsn;UID=user;PWD=pass' # experimental

For more info:
help(DAL)
help(Field)
"""

###################################################################################
# this file orly exposes DAL and Field
###################################################################################

__all__ = ['DAL', 'Field']
MAXCHARLENGTH = 512
INFINITY = 2**15 # not quite but reasonable default max char length

MAXCHARLENGTH = 2**15 # not quite but reasonable default max char length
DEFAULTLENGTH = {'string':512,
                 'password':512,
                 'upload':512,
                 'text':2**15,
                 'blob':2**31}

import re
import sys
import locale
import os
import types
import cPickle

sql_locker = threading.RLock()
thread = threading.local()

# internal representation of tables with field
#  <table>.<field>, tables and fields may only be [a-zA-Z0-0_]

regex_dbname = re.compile('^(\w+)(\:\w+)*')
table_field = re.compile('^[\w_]+\.[\w_]+$')
table_field = re.compile('^([\w_]+)\.([\w_]+)$')
regex_content = re.compile('(?P<table>[\w\-]+)\.(?P<field>[\w\-]+)\.(?P<uuidkey>[\w\-]+)\.(?P<name>\w+)\.\w+$')
regex_cleanup_fn = re.compile('[\'"\s;]+')
string_unpack=re.compile('(?<!\|)\|(?!\|)')
regex_python_keywords = re.compile('^(and|del|from|not|while|as|elif|global|or|with|assert|else|if|pass|yield|break|except|import|print|class|exec|in|raise|continue|finally|is|return|def|for|lambda|try)$')

        import contrib.pymysql as pymysql
        drivers.append('pymysql')
    except ImportError:
        logger.debug('no pymysql driver')

    try:
        import psycopg2
        from psycopg2.extensions import adapt as psycopg2_adapt
        drivers.append('PostgreSQL')
    except ImportError:
        logger.debug('no psycopg2 driver')

    try:
        import cx_Oracle
        drivers.append('Oracle')

    try:
        import informixdb
        drivers.append('Informix')
        logger.warning('Informix support is experimental')
    except ImportError:
        logger.debug('no informixdb driver')
        

    try:
        import sapdb
        drivers.append('SAPDB')
        logger.warning('SAPDB support is experimental')
    except ImportError:
        logger.debug('no sapdb driver')

    try:
        import pymongo
        drivers.append('mongoDB')
    except:
        logger.debug('no mongoDB driver')

def OR(a,b):
    return a|b

def AND(a,b):
    return a&b

if 'google' in drivers:

    is_jdbc = False

    class GAEDecimalProperty(gae.Property):
        """

###################################################################################
# class that handles connection pooling (all adapters derived form this one)
###################################################################################

class ConnectionPool(object):

    pools = {}
    check_active_connection = True

    @staticmethod
    def set_folder(folder):
        thread.folder = folder

    # ## this allows gluon to commit/rollback all dbs in this thread

        else:
            self.folder = thread.folder = ''

        # Creating the folder if it does not exist
        if False and self.folder and not os.path.exists(self.folder):
            os.mkdir(self.folder)

    def pool_connection(self, f):
    def pool_connection(self, f, cursor=True):
        """
        this function defines: self.connection and self.cursor (iff cursor is True)
        if self.pool_size>0 it will try pull the connection from the pool
        if the connection is not active (closed by db server) it will loop
        if not self.pool_size or no active connections in pool makes a new one
        """
        if not self.pool_size:
            self.connection = f()
            self.cursor = cursor and self.connection.cursor()
        else:
            uri = self.uri
            while True:
            sql_locker.acquire()
            if not uri in ConnectionPool.pools:
                ConnectionPool.pools[uri] = []
            if ConnectionPool.pools[uri]:
                self.connection = ConnectionPool.pools[uri].pop()
                sql_locker.release()
            else:
                sql_locker.release()
                self.connection = f()
                sql_locker.acquire()
                if not uri in ConnectionPool.pools:
                    ConnectionPool.pools[uri] = []                    
                if ConnectionPool.pools[uri]:
                    self.connection = ConnectionPool.pools[uri].pop()
                    sql_locker.release()
                    self.cursor = cursor and self.connection.cursor()
                    try:
                        if self.cursor and self.check_active_connection:
                            self.execute('SELECT 1;')
                        break
                    except:
                        pass
                else:
                    sql_locker.release()
                    self.connection = f()
                    self.cursor = cursor and self.connection.cursor()
                    break
        if not hasattr(thread,'instances'):
            thread.instances = []
        thread.instances.append(self)


###################################################################################
# this is a generic adapter that does nothing; all others are derived form this one
###################################################################################

class BaseAdapter(ConnectionPool):

    driver = None
    maxcharlength = INFINITY
    maxcharlength = MAXCHARLENGTH
    commit_on_alter_table = False
    support_distributed_transaction = False
    uploads_in_blob = False
    types = {
        'boolean': 'CHAR(1)',
        'string': 'CHAR(%(length)s)',
        'text': 'TEXT',

        'id': 'INTEGER PRIMARY KEY AUTOINCREMENT',
        'reference': 'INTEGER REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s',
        'list:integer': 'TEXT',
        'list:string': 'TEXT',
        'list:reference': 'TEXT',
        }

    def adapt(self,obj):
        return "'%s'" % obj.replace("'", "''")

    def integrity_error(self):
        return self.driver.IntegrityError

    def operational_error(self):
        return self.driver.OperationalError

    def file_exists(self, filename):
        """
        to be used ONLY for files that on GAE may not be on filesystem
        """
        return os.path.exists(filename)

    def file_open(self, filename, mode='rb', lock=True):

    def RANDOM(self):
        return 'Random()'

    def NOT_NULL(self,default,field_type):
        return 'NOT NULL DEFAULT %s' % self.represent(default,field_type)

    def COALESCE(self,first,second):
        expressions = [self.expand(first)]+[self.expand(e) for e in second]
        return 'COALESCE(%s)' % ','.join(expressions)

    def COALESCE_ZERO(self,first):
        return 'COALESCE(%s,0)' % self.expand(first)

    def RAW(self,first):
        return first

    def ALLOW_NULL(self):
        return ''

    def SUBSTRING(self,field,parameters):
        return 'SUBSTR(%s,%s,%s)' % (self.expand(field), parameters[0], parameters[1])

    def OR(self,first,second):
        return '(%s OR %s)' % (self.expand(first),self.expand(second))

    def BELONGS(self,first,second):
        if isinstance(second,str):
            return '(%s IN (%s))' % (self.expand(first),second[:-1])
        elif second==[] or second==():
            return '(0)'
            return '(1=0)'
        items =','.join(self.expand(item,first.type) for item in second)
        return '(%s IN (%s))' % (self.expand(first),items)

    def LIKE(self,first,second):
        return '(%s LIKE %s)' % (self.expand(first),self.expand(second,'string'))

    def STARTSWITH(self,first,second):

        if isinstance(expression,Field):
            return str(expression)
        elif isinstance(expression, (Expression, Query)):
            if not expression.second is None:
                return expression.op(expression.first, expression.second)
            elif not expression.first is None:
                return expression.op(expression.first)
            elif not isinstance(expression.op,str):
                return expression.op()
            else:
                return expression.op()
                return '(%s)' % expression.op
        elif field_type:
            return self.represent(expression,field_type)
        elif isinstance(expression,(list,tuple)):
            return ','.join([self.represent(item,field_type) for item in expression])
        else:
            return str(expression)

                self.execute(query)
            table._db.commit()
            logfile.write('success!\n')
        finally:
            logfile.close()

    def _update(self,tablename,query,fields):
        query = self.filter_tenant(query,[tablename])
        if query:
            sql_w = ' WHERE ' + self.expand(query)
        else:
            sql_w = ''
        sql_v = ','.join(['%s=%s' % (field.name, self.expand(value,field.type)) for (field,value) in fields])
        return 'UPDATE %s SET %s%s;' % (tablename, sql_v, sql_w)

    def update(self,tablename,query,fields):
        sql = self._update(tablename,query,fields)
        self.execute(sql)
        try:
            return self.cursor.rowcount
        except:
            return None

    def _delete(self,tablename, query):
        query = self.filter_tenant(query,[tablename])
        if query:
            sql_w = ' WHERE ' + self.expand(query)
        else:
            sql_w = ''
        return 'DELETE FROM %s%s;' % (tablename, sql_w)

    def delete(self,tablename,query):

            rows = list(rows)
        limitby = attributes.get('limitby',None) or (0,)
        rows = self.rowslice(rows,limitby[0],None)
        return self.parse(rows,self._colnames)

    def _count(self,query,distinct=None):
        tablenames = self.tables(query)
        query = self.filter_tenant(query,tablenames)
        if query:
            sql_w = ' WHERE ' + self.expand(query)
        else:
            sql_w = ''
        sql_t = ','.join(tablenames)
        if distinct:
            if isinstance(distinct,(list,tuple)):
                distinct = xorify(distinct)
            sql_d = self.expand(distinct)
            return 'SELECT count(DISTINCT %s) FROM %s%s' % (sql_d, sql_t, sql_w)
        return 'SELECT count(*) FROM %s%s' % (sql_t, sql_w)
            return 'SELECT count(DISTINCT %s) FROM %s%s;' % (sql_d, sql_t, sql_w)
        return 'SELECT count(*) FROM %s%s;' % (sql_t, sql_w)

    def count(self,query,distinct=None):
        self.execute(self._count(query,distinct))
        return self.cursor.fetchone()[0]


    def tables(self,query):
        tables = set()
        if isinstance(query, Field):
            tables.add(query.tablename)
        elif isinstance(query, (Expression, Query)):
            if query.first!=None:
            if not query.first is None:
                tables = tables.union(self.tables(query.first))
            if query.second!=None:
            if not query.second is None:
                tables = tables.union(self.tables(query.second))
        return list(tables)

    def commit(self):
        return self.connection.commit()

    def rollback(self):

        if isinstance(obj, (list, tuple)):
            obj = bar_encode(obj)
        if obj is None:
            return 'NULL'
        if obj == '' and not fieldtype[:2] in ['st', 'te', 'pa', 'up']:
            return 'NULL'
        r = self.represent_exceptions(obj,fieldtype)
        if r != None:
        if not r is None:
            return r
        if fieldtype == 'boolean':
            if obj and not str(obj)[:1].upper() in ['F', '0']:
                return "'T'"
            else:
                return "'F'"
        if fieldtype == 'id' or fieldtype == 'integer':

                obj = str(obj)
        if not isinstance(obj,str):
            obj = str(obj)
        try:
            obj.decode(self.db_codec)
        except:
            obj = obj.decode('latin1').encode(self.db_codec)
        return "'%s'" % obj.replace("'", "''")
        return self.adapt(obj)

    def represent_exceptions(self, obj, fieldtype):
        return None

    def lastrowid(self,table):
        return None

                if field_type == 'id':
                    id = colset[field.name]
                    colset.update_record = lambda _ = (colset, table, id), **a: update_record(_, a)
                    colset.delete_record = lambda t = table, i = id: t._db(t._id==i).delete()
                    for (referee_table, referee_name) in \
                            table._referenced_by:
                        s = db[referee_table][referee_name]
                        if not referee_table in colset:
                            # for backward compatibility
                            colset[referee_table] = Set(db, s == id)
                        referee_link = db._referee_name and \
                            db._referee_name % dict(table=referee_table,field=referee_name)
                        if referee_link and not referee_link in colset:
                        ### add new feature?
                        ### colset[referee_table+'_by_'+refree_name] = Set(db, s == id)
                            colset[referee_link] = Set(db, s == id)
                    colset['id'] = id
            new_rows.append(new_row)

        rowsobj = Rows(db, new_rows, colnames, rawrows=rows)

        for tablename in virtualtables:
            ### new style virtual fields
            for item in db[tablename].virtualfields:
            table = db[tablename]
            fields_virtual = [(f,v) for (f,v) in table.items() if isinstance(v,FieldVirtual)]
            fields_lazy = [(f,v) for (f,v) in table.items() if isinstance(v,FieldLazy)]
            if fields_virtual or fields_lazy:
                for row in rowsobj.records:
                    box = row[tablename]
                    for f,v in fields_virtual:
                        box[f] = v.f(row)
                    for f,v in fields_lazy:
                        box[f] = (v.handler or VirtualCommand)(v.f,row)

            ### old style virtual fields
            for item in table.virtualfields:
                try:
                    rowsobj = rowsobj.setvirtualfields(**{tablename:item})
                except KeyError:
                    # to avoid breaking virtualfields when partial select
                    pass
        return rowsobj

    def filter_tenant(self,query,tablenames):
        fieldname = self.db._request_tenant
        for tablename in tablenames:
            table = self.db[tablename]
            if fieldname in table:
                default = table[fieldname].default
                if default!=None:
                    query = query&(table[fieldname]==default)
                if not default is None:
                    newquery = table[fieldname]==default
                    if query is None:
                        query = newquery
                    else:
                        query = query&newquery
        return query

###################################################################################
# List of all the available adapters, they all extend BaseAdapter
###################################################################################

class SQLiteAdapter(BaseAdapter):

            (i, j) = table[lookup]
            return int(s[i:j])
        except:
            return None

    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.dbengine = "sqlite"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.find_or_make_work_folder()
        path_encoding = sys.getfilesystemencoding() or locale.getdefaultlocale()[1] or 'utf8'
        if uri.startswith('sqlite:memory'):
            dbpath = ':memory:'
        else:
            dbpath = uri.split('://')[1]
            if dbpath[0] != '/':
                dbpath = os.path.join(self.folder.decode(path_encoding).encode('utf8'),dbpath)
        if not 'check_same_thread' in driver_args:
            driver_args['check_same_thread'] = False
        if not 'detect_types' in driver_args:
            driver_args['detect_types'] = self.driver.PARSE_DECLTYPES
        def connect(dbpath=dbpath, driver_args=driver_args):
            return self.driver.Connection(dbpath, **driver_args)
        self.pool_connection(connect)
        self.cursor = self.connection.cursor()
        self.connection.create_function('web2py_extract', 2, SQLiteAdapter.web2py_extract)

    def _truncate(self,table,mode = ''):
        tablename = table._tablename
        return ['DELETE FROM %s;' % tablename,
                "DELETE FROM sqlite_sequence WHERE name='%s';" % tablename]

    def lastrowid(self,table):
        return self.cursor.lastrowid


class JDBCSQLiteAdapter(SQLiteAdapter):

    driver = globals().get('zxJDBC',None)

    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.dbengine = "sqlite"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.find_or_make_work_folder()
        path_encoding = sys.getfilesystemencoding() or locale.getdefaultlocale()[1] or 'utf8'
        if uri.startswith('sqlite:memory'):
            dbpath = ':memory:'
        else:
            dbpath = uri.split('://')[1]
            if dbpath[0] != '/':
                dbpath = os.path.join(self.folder.decode(path_encoding).encode('utf8'),dbpath)
        def connect(dbpath=dbpath,driver_args=driver_args):
            return self.driver.connect(java.sql.DriverManager.getConnection('jdbc:sqlite:'+dbpath),**driver_args)
        self.pool_connection(connect)
        self.cursor = self.connection.cursor()
        # FIXME http://www.zentus.com/sqlitejdbc/custom_functions.html for UDFs
        # self.connection.create_function('web2py_extract', 2, SQLiteAdapter.web2py_extract)

    def execute(self,a):
        return self.log_execute(a[:-1])
    def execute(self,a):        
        return self.log_execute(a)


class MySQLAdapter(BaseAdapter):

    driver = globals().get('pymysql',None)
    maxcharlength = 255
    commit_on_alter_table = True

        self.execute("XA ROLLBACK;")

    def concat_add(self,table):
        return '; ALTER TABLE %s ADD ' % table

    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.dbengine = "mysql"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.find_or_make_work_folder()

                                passwd=credential_decoder(password),
                                host=host,
                                port=port,
                                charset=charset))
        def connect(driver_args=driver_args):
            return self.driver.connect(**driver_args)
        self.pool_connection(connect)
        self.cursor = self.connection.cursor()
        self.execute('SET FOREIGN_KEY_CHECKS=1;')
        self.execute("SET sql_mode='NO_BACKSLASH_ESCAPES';")

    def lastrowid(self,table):
        self.execute('select last_insert_id();')
        return int(self.cursor.fetchone()[0])


class PostgreSQLAdapter(BaseAdapter):

    driver = globals().get('psycopg2',None)

    support_distributed_transaction = True
    types = {

        'id': 'SERIAL PRIMARY KEY',
        'reference': 'INTEGER REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s',
        'list:integer': 'TEXT',
        'list:string': 'TEXT',
        'list:reference': 'TEXT',
        }

    def adapt(self,obj):
        return psycopg2_adapt(obj).getquoted()

    def sequence_name(self,table):
        return '%s_id_Seq' % table

    def RANDOM(self):
        return 'RANDOM()'

    def distributed_transaction_begin(self,key):

        # self.execute('CREATE SEQUENCE %s;' % table._sequence_name)
        # self.execute("ALTER TABLE %s ALTER COLUMN %s SET DEFAULT NEXTVAL('%s');" \
        #              % (table._tablename, table._fieldname, table._sequence_name))
        self.execute(query)

    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.dbengine = "postgres"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.find_or_make_work_folder()

            msg = ("dbname='%s' user='%s' host='%s'"
                   "port=%s password='%s'") \
                   % (db, user, host, port, password)
        def connect(msg=msg,driver_args=driver_args):
            return self.driver.connect(msg,**driver_args)
        self.pool_connection(connect)
        self.connection.set_client_encoding('UTF8')
        self.cursor = self.connection.cursor()
        self.execute('BEGIN;')
        self.execute("SET CLIENT_ENCODING TO 'UNICODE';")
        self.execute("SET standard_conforming_strings=on;")

    def lastrowid(self,table):
        self.execute("select currval('%s')" % table._sequence_name)
        return int(self.cursor.fetchone()[0])

    def LIKE(self,first,second):

            key = '%|'+str(second).replace('|','||').replace('%','%%')+'|%'
        return '(%s ILIKE %s)' % (self.expand(first),self.expand(key,'string'))

class JDBCPostgreSQLAdapter(PostgreSQLAdapter):

    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.dbengine = "postgres"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.find_or_make_work_folder()

            raise SyntaxError, 'Database name required'
        port = m.group('port') or '5432'
        msg = ('jdbc:postgresql://%s:%s/%s' % (host, port, db), user, password)
        def connect(msg=msg,driver_args=driver_args):
            return self.driver.connect(*msg,**driver_args)
        self.pool_connection(connect)
        self.connection.set_client_encoding('UTF8')
        self.cursor = self.connection.cursor()
        self.execute('BEGIN;')
        self.execute("SET CLIENT_ENCODING TO 'UNICODE';")


class OracleAdapter(BaseAdapter):

    driver = globals().get('cx_Oracle',None)

            else:
                obj = str(obj)
            return "to_date('%s','yyyy-mm-dd hh24:mi:ss')" % obj
        return None

    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.dbengine = "oracle"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.find_or_make_work_folder()
        uri = uri.split('://')[1]
        if not 'threaded' in driver_args:
            driver_args['threaded']=True
        def connect(uri=uri,driver_args=driver_args):
            return self.driver.connect(uri,**driver_args)
        self.pool_connection(connect)
        self.cursor = self.connection.cursor()
        self.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';")
        self.execute("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS';")
    oracle_fix = re.compile("[^']*('[^']*'[^']*)*\:(?P<clob>CLOB\('([^']+|'')*'\))")

    def execute(self, command):
        args = []
        i = 1
        while True:
            m = self.oracle_fix.match(command)
            if not m:
                break
            command = command[:m.start('clob')] + str(i) + command[m.end('clob'):]
            args.append(m.group('clob')[6:-2].replace("''", "'"))
            i += 1
        if command[-1:]==';':
            command = command[:-1]
        return self.log_execute(command[:-1], args)
        return self.log_execute(command, args)

    def create_sequence_and_triggers(self, query, table, **args):
        tablename = table._tablename
        sequence_name = table._sequence_name
        trigger_name = table._trigger_name
        self.execute(query)
        self.execute('CREATE SEQUENCE %s START WITH 1 INCREMENT BY 1 NOMAXVALUE;' % sequence_name)

            urlargs = ';'.join(['%s=%s' % (ak, av) for (ak, av) in argsdict.items()])
            cnxn = 'SERVER=%s;PORT=%s;DATABASE=%s;UID=%s;PWD=%s;%s' \
                % (host, port, db, user, password, urlargs)
        def connect(cnxn=cnxn,driver_args=driver_args):
            return self.driver.connect(cnxn,**driver_args)
        if not fake_connect:
            self.pool_connection(connect)
            self.cursor = self.connection.cursor()

    def lastrowid(self,table):
        #self.execute('SELECT @@IDENTITY;')
        self.execute('SELECT SCOPE_IDENTITY();')
        return int(self.cursor.fetchone()[0])

    def integrity_error_class(self):

    def _truncate(self,table,mode = ''):
        return ['DELETE FROM %s;' % table._tablename,
                'SET GENERATOR %s TO 0;' % table._sequence_name]

    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.dbengine = "firebird"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.find_or_make_work_folder()

                                   charset = charset))
        if adapter_args.has_key('driver_name'):
            if adapter_args['driver_name'] == 'kinterbasdb':
                self.driver = kinterbasdb
            elif adapter_args['driver_name'] == 'firebirdsql':
                self.driver = firebirdsql
        else:
            self.driver = kinterbasdb        
            self.driver = kinterbasdb
        def connect(driver_args=driver_args):
            return self.driver.connect(**driver_args)
        self.pool_connection(connect)
        self.cursor = self.connection.cursor()

    def create_sequence_and_triggers(self, query, table, **args):
        tablename = table._tablename
        sequence_name = table._sequence_name
        trigger_name = table._trigger_name
        self.execute(query)
        self.execute('create generator %s;' % sequence_name)
        self.execute('set generator %s to 0;' % sequence_name)
        self.execute('create trigger %s for %s active before insert position 0 as\nbegin\nif(new.id is null) then\nbegin\nnew.id = gen_id(%s, 1);\nend\nend;' % (trigger_name, tablename, sequence_name))

    def lastrowid(self,table):
        sequence_name = table._sequence_name
        self.execute('SELECT gen_id(%s, 0) FROM rdb$database' % sequence_name)
        return int(self.cursor.fetchone()[0])


class FireBirdEmbeddedAdapter(FireBirdAdapter):

    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.dbengine = "firebird"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.find_or_make_work_folder()

        #    return kinterbasdb.connect(**driver_args)
        if adapter_args.has_key('driver_name'):
            if adapter_args['driver_name'] == 'kinterbasdb':
                self.driver = kinterbasdb
            elif adapter_args['driver_name'] == 'firebirdsql':
                self.driver = firebirdsql
        else:
            self.driver = kinterbasdb        
            self.driver = kinterbasdb
        def connect(driver_args=driver_args):
            return self.driver.connect(**driver_args)
        self.pool_connection(connect)
        self.cursor = self.connection.cursor()


class InformixAdapter(BaseAdapter):

    driver = globals().get('informixdb',None)

    types = {

            else:
                obj = str(obj)
            return "to_date('%s','yyyy-mm-dd hh24:mi:ss')" % obj
        return None

    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.dbengine = "informix"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.find_or_make_work_folder()

        user = credential_decoder(user)
        password = credential_decoder(password)
        dsn = '%s@%s' % (db,host)
        driver_args.update(dict(user=user,password=password,autocommit=True))
        def connect(dsn=dsn,driver_args=driver_args):
            return self.driver.connect(dsn,**driver_args)
        self.pool_connection(connect)
        self.cursor = self.connection.cursor()

    def execute(self,command):
        if command[-1:]==';':
            command = command[:-1]
        return self.log_execute(command)

    def lastrowid(self,table):

            elif isinstance(obj, datetime.date):
                obj = obj.isoformat()[:10]+'-00.00.00'
            return "'%s'" % obj
        return None

    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.dbengine = "db2"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.find_or_make_work_folder()
        cnxn = uri.split('://', 1)[1]
        def connect(cnxn=cnxn,driver_args=driver_args):
            return self.driver.connect(cnxn,**driver_args)
        self.pool_connection(connect)
        self.cursor = self.connection.cursor()

    def execute(self,command):
        if command[-1:]==';':
            command = command[:-1]
        return self.log_execute(command)

    def lastrowid(self,table):

        'list:string': 'CLOB',
        'list:reference': 'CLOB',
        }


    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.dbengine = "teradata"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.find_or_make_work_folder()
        cnxn = uri.split('://', 1)[1]
        def connect(cnxn=cnxn,driver_args=driver_args):
            return self.driver.connect(cnxn,**driver_args)
        self.pool_connection(connect)
        self.cursor = self.connection.cursor()


INGRES_SEQNAME='ii***lineitemsequence' # NOTE invalid database object name
                                       # (ANSI-SQL wants this form of name
                                       # to be a delimited identifier)

class IngresAdapter(BaseAdapter):

            if lmin:
                # Requires Ingres 9.2+
                sql_o += ' OFFSET %d' % (lmin, )
        return 'SELECT %s %s FROM %s%s%s;' % (sql_s, sql_f, sql_t, sql_w, sql_o)

    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.dbengine = "ingres"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.find_or_make_work_folder()

        driver_args.update(dict(database=database_name,
                                   vnode=vnode,
                                   servertype=servertype,
                                   trace=trace))
        def connect(driver_args=driver_args):
            return self.driver.connect(**driver_args)
        self.pool_connection(connect)
        self.cursor = self.connection.cursor()

    def create_sequence_and_triggers(self, query, table, **args):
        # post create table auto inc code (if needed)
        # modify table to btree for performance....
        # Older Ingres releases could use rule/trigger like Oracle above.
        if hasattr(table,'_primarykey'):
            modify_tbl_sql = 'modify %s to btree unique on %s' % \

        self.execute('CREATE SEQUENCE %s;' % table._sequence_name)
        self.execute("ALTER TABLE %s ALTER COLUMN %s SET DEFAULT NEXTVAL('%s');" \
                         % (table._tablename, table._id.name, table._sequence_name))
        self.execute(query)

    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.dbengine = "sapdb"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.find_or_make_work_folder()

        if not db:
            raise SyntaxError, 'Database name required'
        def connect(user=user,password=password,database=db,
                    host=host,driver_args=driver_args):
            return self.driver.Connection(user,password,database,
                                          host,**driver_args)
        self.pool_connection(connect)
        # self.connection.set_client_encoding('UTF8')
        self.cursor = self.connection.cursor()

    def lastrowid(self,table):
        self.execute("select %s.NEXTVAL from dual" % table._sequence_name)
        return int(self.cursor.fetchone()[0])

class CubridAdapter(MySQLAdapter):

        port = int(m.group('port') or '30000')
        charset = m.group('charset') or 'utf8'
        user=credential_decoder(user),
        passwd=credential_decoder(password),
        def connect(host,port,db,user,passwd,driver_args=driver_args):
            return self.driver.connect(host,port,db,user,passwd,**driver_args)
        self.pool_connection(connect)
        self.cursor = self.connection.cursor()
        self.execute('SET FOREIGN_KEY_CHECKS=1;')
        self.execute("SET sql_mode='NO_BACKSLASH_ESCAPES';")


######## GAE MySQL ##########

class DatabaseStoredFile:

    web2py_filesystem = False

    def escape(self,obj):
        return self.db._adapter.esacpe(obj)    

    def __init__(self,db,filename,mode):
        if db._adapter.dbengine != 'mysql':
            raise RuntimeError, "only MySQL can store metadata .table files in database for now"
        self.db = db
        self.filename = filename
        self.mode = mode
        if not self.web2py_filesystem:
            self.db.executesql("CREATE TABLE IF NOT EXISTS web2py_filesystem (path VARCHAR(512), content LONGTEXT, PRIMARY KEY(path) ) ENGINE=InnoDB;")
            DatabaseStoredFile.web2py_filesystem = True
        self.p=0
        self.data = ''
        if mode in ('r','rw','a'):
            query = "SELECT content FROM web2py_filesystem WHERE path='%s'" % filename
            query = "SELECT content FROM web2py_filesystem WHERE path='%s'" \
                % filename
            rows = self.db.executesql(query)
            if rows:
                self.data = rows[0][0]
            elif os.path.exists(filename):
                datafile = open(filename, 'r')
                try:
                    self.data = datafile.read()

            data, self.p = self.data[self.p:], len(self.data)
        return data

    def write(self,data):
        self.data += data

    def close(self):
        self.db.executesql("DELETE FROM web2py_filesystem WHERE path='%s'" % self.filename)
        query = "INSERT INTO web2py_filesystem(path,content) VALUES ('%s','%s')" % \
            (self.filename, self.data.replace("'","''"))
        self.db.executesql("DELETE FROM web2py_filesystem WHERE path=%s" \
                               % self.adapt(self.filename))
        query = "INSERT INTO web2py_filesystem(path,content) VALUES (%s,%s)"\
            % (self.adapt(self.filename), self.adapt(self.data))
        self.db.executesql(query)
        self.db.commit()

    @staticmethod
    def exists(db,filename):
        if os.path.exists(filename):
            return True
        query = "SELECT path FROM web2py_filesystem WHERE path='%s'" % filename
        query = "SELECT path FROM web2py_filesystem WHERE path=%s" \
            % self.adapt(filename)
        if db.executesql(query):
            return True
        return False


class UseDatabaseStoredFile:

    def file_delete(self,filename):
        query = "DELETE FROM web2py_filesystem WHERE path='%s'" % filename
        self.db.executesql(query)
        self.db.commit()

class GoogleSQLAdapter(UseDatabaseStoredFile,MySQLAdapter):

    def __init__(self, db, uri='google:sql://realm:domain/database', pool_size=0,
                 folder=None, db_codec='UTF-8', check_reserved=None,
    def __init__(self, db, uri='google:sql://realm:domain/database',
                 pool_size=0, folder=None, db_codec='UTF-8',
                 migrate=True, fake_migrate=False,
                 credential_decoder = lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):

        self.db = db
        self.dbengine = "mysql"
        self.uri = uri
        self.pool_size = pool_size
        self.folder = folder
        self.db_codec = db_codec
        self.folder = folder or '$HOME/'+thread.folder.split('/applications/',1)[1]

        m = re.compile('^(?P<instance>.*)/(?P<db>.*)$').match(self.uri[len('google:sql://'):])
        if not m:
            raise SyntaxError, "Invalid URI string in SQLDB: %s" % self._uri
        instance = credential_decoder(m.group('instance'))
        db = credential_decoder(m.group('db'))
        driver_args['instance'] = instance
        createdb = adapter_args.get('createdb',True)
        if not migrate:
        if not createdb:
            driver_args['database'] = db
        def connect(driver_args=driver_args):
            return rdbms.connect(**driver_args)
        self.pool_connection(connect)
        self.cursor = self.connection.cursor()
        if migrate:
        if createdb:
            # self.execute('DROP DATABASE %s' % db)
            self.execute('CREATE DATABASE IF NOT EXISTS %s' % db)
            self.execute('USE %s' % db)
        self.execute("SET FOREIGN_KEY_CHECKS=1;")
        self.execute("SET sql_mode='NO_BACKSLASH_ESCAPES';")

class NoSQLAdapter(BaseAdapter):

        if fieldtype.startswith('list:'):
            if not obj:
                obj = []
            if not isinstance(obj, (list, tuple)):
                obj = [obj]
        if obj == '' and  not fieldtype[:2] in ['st','te','pa','up']:
            return None
        if obj != None:
        if not obj is None:
            if isinstance(obj, list) and not fieldtype.startswith('list'):
                obj = [self.represent(o, fieldtype) for o in obj]
            elif fieldtype in ('integer','id'):
                obj = long(obj)
            elif fieldtype == 'double':
                obj = float(obj)
            elif fieldtype.startswith('reference'):

    def file_exists(self, filename): pass
    def file_open(self, filename, mode='rb', lock=True): pass
    def file_close(self, fileobj, unlock=True): pass

    def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.types.update({
                'boolean': gae.BooleanProperty,
                'string': (lambda: gae.StringProperty(multiline=True)),
                'text': gae.TextProperty,
                'password': gae.StringProperty,
                'blob': gae.BlobProperty,
                'upload': gae.StringProperty,

            raise SyntaxError, "Not suported %s" % first.op.__name__
        first.op = nop
        return self.expand(first)

    def truncate(self,table,mode):
        self.db(table._id > 0).delete()

    def select_raw(self,query,fields=[],attributes={}):
    def select_raw(self,query,fields=None,attributes=None):
        fields = fields or []
        attributes = attributes or {}
        new_fields = []
        for item in fields:
            if isinstance(item,SQLALL):
                new_fields += item.table
            else:
                new_fields.append(item)
        fields = new_fields

        if fieldtype=='id':
            return repr(str(int(value)))
        return repr(not isinstance(value,unicode) and value or value.encode('utf8'))

    def __init__(self,db,uri='couchdb://127.0.0.1:5984',
                 pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.uri = uri
        self.dbengine = 'couchdb'
        self.folder = folder
        db['_lastsql'] = ''
        self.db_codec = 'UTF-8'
        self.pool_size = pool_size

        url='http://'+uri[10:]
        def connect(url=url,driver_args=driver_args):
            return couchdb.Server(url,**driver_args)
        self.pool_connection(connect)
        self.pool_connection(connect,cursor=False)

    def create_table(self, table, migrate=True, fake_migrate=False, polymodel=None):
        if migrate:
            try:
                self.connection.create(table._tablename)
            except:
                pass

                'list:integer': list,
                'list:reference': list,
        }

    def __init__(self,db,uri='mongodb://127.0.0.1:5984/db',
                 pool_size=0,folder=None,db_codec ='UTF-8',
                 credential_decoder=lambda x:x, driver_args={},
                    adapter_args={}):
                 adapter_args={}):
        self.db = db
        self.uri = uri
        self.dbengine = 'mongodb'
        self.folder = folder
        db['_lastsql'] = ''
        self.db_codec = 'UTF-8'
        self.pool_size = pool_size

        m = re.compile('^(?P<host>[^\:/]+)(\:(?P<port>[0-9]+))?/(?P<db>.+)$').match(self._uri[10:])
        m = re.compile('^(?P<host>[^\:/]+)(\:(?P<port>[0-9]+))?/(?P<db>.+)$').match(self.uri[10:])
        if not m:
            raise SyntaxError, "Invalid URI string in DAL: %s" % self._uri
            raise SyntaxError, "Invalid URI string in DAL: %s" % self.uri
        host = m.group('host')
        if not host:
            raise SyntaxError, 'mongodb: host name required'
        dbname = m.group('db')
        if not dbname:
            raise SyntaxError, 'mongodb: db name required'
        port = m.group('port') or 27017
        driver_args.update(dict(host=host,port=port))
        def connect(dbname=dbname,driver_args=driver_args):
            return pymongo.Connection(**driver_args)[dbname]
        self.pool_connection(connect)
        self.pool_connection(connect,cursor=False)

    def insert(self,table,fields):
        ctable = self.connection[table._tablename]
        values = dict((k,self.represent(v,table[k].type)) for k,v in fields)
        ctable.insert(values)
        return uuid2int(id)

        requires.append(validators.IS_TIME())
    elif field_type == 'datetime':
        requires.append(validators.IS_DATETIME())
    elif field.db and field_type.startswith('reference') and \
            field_type.find('.') < 0 and \
            field_type[10:] in field.db.tables:
        referenced = field.db[field_type[10:]]
        def repr_ref(id, r=referenced, f=ff): return f(r, id)
        def repr_ref(id, row=None, r=referenced, f=ff): return f(r, id)
        field.represent = field.represent or repr_ref
        if hasattr(referenced, '_format') and referenced._format:
            requires = validators.IS_IN_DB(field.db,referenced._id,
                                           referenced._format)
            if field.unique:
                requires._and = validators.IS_NOT_IN_DB(field.db,field)
            if field.tablename == field_type[10:]:
                return validators.IS_EMPTY_OR(requires)
            return requires
    elif field.db and field_type.startswith('list:reference') and \
            field_type.find('.') < 0 and \
            field_type[15:] in field.db.tables:
        referenced = field.db[field_type[15:]]
        def list_ref_repr(ids, r=referenced, f=ff):
        def list_ref_repr(ids, row=None, r=referenced, f=ff):
            if not ids:
                return None
            refs = r._db(r._id.belongs(ids)).select(r._id)
            return (refs and ', '.join(str(f(r,ref.id)) for ref in refs) or '')
        field.represent = field.represent or list_ref_repr
        if hasattr(referenced, '_format') and referenced._format:
            requires = validators.IS_IN_DB(field.db,referenced._id,
                                           referenced._format,multiple=True)
        else:
            requires = validators.IS_IN_DB(field.db,referenced._id,
                                           multiple=True)
        if field.unique:
            requires._and = validators.IS_NOT_IN_DB(field.db,field)
        return requires
    elif field_type.startswith('list:'):
        def repr_list(values): return', '.join(str(v) for v in (values or []))
        def repr_list(values,row=None): return', '.join(str(v) for v in (values or []))
        field.represent = field.represent or repr_list
    if field.unique:
        requires.insert(0,validators.IS_NOT_IN_DB(field.db,field))
    sff = ['in', 'do', 'da', 'ti', 'de', 'bo']
    if field.notnull and not field_type[:2] in sff:
        requires.insert(0, validators.IS_NOT_EMPTY())
    elif not field.notnull and field_type[:2] in sff and requires:

    """
    a dictionary that lets you do d['a'] as well as d.a
    this is only used to store a Row
    """

    def __getitem__(self, key):
        key=str(key)
        m = table_field.match(key)
        if key in self.get('_extra',{}):
            return self._extra[key]
        elif m:
            try:
                return dict.__getitem__(self, m.group(1))[m.group(2)]
            except (KeyError,TypeError):
                key = m.group(2)
        return dict.__getitem__(self, key)

    def __call__(self,key):
        return self.__getitem__(key)

    def __setitem__(self, key, value):
        dict.__setitem__(self, str(key), value)

# database and should for RDBMs and some NoSQL databases
################################################################################

class SQLCallableList(list):
    def __call__(self):
        return copy.copy(self)

def smart_query(fields,text):
    if not isinstance(fields,(list,tuple)):
        fields = [fields]
    new_fields = []
    for field in fields:
        if isinstance(field,Field):
            new_fields.append(field)
        elif isinstance(field,Table):
            for ofield in field:
                new_fields.append(ofield)
        else:
            raise RuntimeError, "fields must be a list of fields"
    field_map = {}
    for field in fields:
        n = field.name.lower()
        if not n in field_map: 
            field_map[n] = field
        n = str(field).lower()
        if not n in field_map:
            field_map[n] = field
    re_constants = re.compile('(\"[^\"]*?\")|(\'[^\']*?\')')
    constants = {}
    i = 0 
    while True:
        m = re_constants.search(text)
        if not m: break
        text = text[:m.start()]+('#%i' % i)+text[m.end():] 
        constants[str(i)] = m.group()[1:-1]
        i+=1
    text = re.sub('\s+',' ',text).lower()
    for a,b in [('&','and'),
                ('|','or'),
                ('~','not'),
                ('==','=='),
                ('<','<'),
                ('>','>'),
                ('<=','<='),
                ('>=','>='),
                ('<>','!='),
                ('=<','<='),
                ('=>','>='),
                ('=','=='),
                (' less or equal than ','<='),
                (' greater or equal than ','>='),
                (' equal or less than ','<='),
                (' equal or greater than ','>='),
                (' less or equal ','<='),
                (' greater or equal ','>='),
                (' equal or less ','<='),
                (' equal or greater ','>='),
                (' not equal to ','!='),
                (' not equal ','!='),
                (' equal to ','=='),
                (' equal ','=='),
                (' equals ','!='),
                (' less than ','<'),
                (' greater than ','>'),
                (' starts with ','startswith'),
                (' ends with ','endswith'),
                (' is ','==')]:            
        if a[0]==' ':
            text = text.replace(' is'+a,' %s ' % b)
        text = text.replace(a,' %s ' % b)
    text = re.sub('\s+',' ',text).lower()
    query = field = neg = op = logic = None
    for item in text.split():
        if field is None:
            if item == 'not':
                neg = True
            elif not neg and not logic and item in ('and','or'):
                logic = item
            elif item in field_map:
                field = field_map[item]                
            else:
                raise RuntimeError, "Invalid syntax"
        elif not field is None and op is None:
            op = item
        elif not op is None:
            if item.startswith('#'):
                if not item[1:] in constants:
                    raise RuntimeError, "Invalid syntax"
                value = constants[item[1:]]
            else:
                value = item
                if op == '==': op = 'like'
            if op == '==': new_query = field==value
            elif op == '<': new_query = field<value
            elif op == '>': new_query = field>value                
            elif op == '<=': new_query = field<=value
            elif op == '>=': new_query = field>=value                
            elif op == 'contains': new_query = field.contains(value)
            elif op == 'like': new_query = field.like(value)
            elif op == 'startswith': new_query = field.startswith(value)
            elif op == 'endswith': new_query = field.endswith(value)
            else: raise RuntimeError, "Invalid operation"
            if neg: new_query = ~new_query                
            if query is None:
                query = new_query
            elif logic == 'and':
                query &= new_query
            elif logic == 'or':
                query |= new_query                
            field = op = neg = logic = None
    return query


class DAL(dict):

    """
    an instance of this class represents a database connection

    Example::

            raise RuntimeError, 'failure to commit distributed transaction'
        else:
            for (i, db) in instances:
                db._adapter.commit_prepared(keys[i])
        return


    def __init__(self, uri='sqlite://dummy.db', pool_size=0, folder=None,
    def __init__(self, uri='sqlite://dummy.db',
                 pool_size=0, folder=None,
                 db_codec='UTF-8', check_reserved=None,
                 migrate=True, fake_migrate=False,
                 migrate_enabled=True, fake_migrate_all=False,
                 decode_credentials=False, driver_args=None,
                 adapter_args={}, attempts=5, auto_import=False):
                 adapter_args=None, attempts=5, auto_import=False):
        """
        Creates a new Database Abstraction Layer instance.

        Keyword arguments:

        :uri: string that contains information for connecting to a database.
               (default: 'sqlite://dummy.db')

        self._pool_size = pool_size
        self._db_codec = db_codec
        self._lastsql = ''
        self._timings = []
        self._pending_references = {}
        self._request_tenant = 'request_tenant'
        self._common_fields = []
        self._referee_name = '%(table)s'
        if not str(attempts).isdigit() or attempts < 0:
            attempts = 5
        if uri:
            uris = isinstance(uri,(list,tuple)) and uri or [uri]
            error = ''
            connected = False
            for k in range(attempts):
                for uri in uris:
                    try:
                        if is_jdbc and not uri.startswith('jdbc:'):
                            uri = 'jdbc:'+uri
                        self._dbname = regex_dbname.match(uri).group()
                        if not self._dbname in ADAPTERS:
                            raise SyntaxError, "Error in URI '%s' or database not supported" % self._dbname
                        # notice that driver args or {} else driver_args defaults to {} global, not correct
                        args = (self,uri,pool_size,folder,db_codec,credential_decoder,driver_args or {}, adapter_args)
                        # notice that driver args or {} else driver_args
                        # defaults to {} global, not correct
                        args = (self,uri,pool_size,folder,
                                db_codec, credential_decoder,
                                driver_args or {}, adapter_args or {})
                        self._adapter = ADAPTERS[self._dbname](*args)
                        connected = True
                        break
                    except SyntaxError:
                        raise
                    except Exception, error:
                        sys.stderr.write('DEBUG_c: Exception %r' % ((Exception, error,),))

            if key not in [
                    'migrate',
                    'primarykey',
                    'fake_migrate',
                    'format',
                    'trigger_name',
                    'sequence_name',
                    'polymodel']:
                raise SyntaxError, 'invalid table "%s" attribute: %s' % (tablename, key)
        migrate = self._migrate_enabled and args.get('migrate',self._migrate)
        fake_migrate = self._fake_migrate_all or args.get('fake_migrate',self._fake_migrate)
                    'polymodel',
                    'table_class']:
                raise SyntaxError, 'invalid table "%s" attribute: %s' \
                    % (tablename, key)
        migrate = self._migrate_enabled and args.get('migrate',
                                                     self._migrate)
        fake_migrate = self._fake_migrate_all or args.get('fake_migrate',
                                                          self._fake_migrate)
        table_class = args.get('table_class',Table)
        format = args.get('format',None)
        trigger_name = args.get('trigger_name', None)
        sequence_name = args.get('sequence_name', None)
        primarykey=args.get('primarykey',None)
        polymodel=args.get('polymodel',None)
        if not isinstance(tablename,str):
            raise SyntaxError, "missing table name"

            raise SyntaxError, 'table already defined: %s' % tablename
        elif self.check_reserved:
            self.check_reserved_keyword(tablename)

        if self._common_fields:
            fields = [f for f in fields] + [f for f in self._common_fields]

        t = self[tablename] = Table(self, tablename, *fields,
                                    **dict(primarykey=primarykey,
                                    trigger_name=trigger_name,
                                    sequence_name=sequence_name))
        t = self[tablename] = table_class(self, tablename, *fields,
                                          **dict(primarykey=primarykey,
                                                 trigger_name=trigger_name,
                                                 sequence_name=sequence_name))
        # db magic
        if self._uri in (None,'None'):
            return t

        t._create_references()

        if migrate or self._adapter.dbengine=='google:datastore':

            raise SyntaxError, \
                'Object %s exists and cannot be redefined' % key
        self[key] = value

    def __repr__(self):
        return '<DAL ' + dict.__repr__(self) + '>'

    def smart_query(self,fields,text):
        return Set(self, smart_query(fields,text))

    def __call__(self, query=None):
        if isinstance(query,Table):
            query = query._id>0
        elif isinstance(query,Field):
            query = query!=None
        return Set(self, query)

    def export_to_csv_file(self, ofile, *args, **kwargs):
        for table in self.tables:
            ofile.write('TABLE %s\r\n' % table)
            self(self[table]._id > 0).select().export_to_csv_file(ofile, *args, **kwargs)
            ofile.write('\r\n\r\n')
        ofile.write('END')

    def import_from_csv_file(self, ifile, id_map={}, null='<NULL>',
    def import_from_csv_file(self, ifile, id_map=None, null='<NULL>',
                             unique='uuid', *args, **kwargs):
        if id_map is None: id_map={}
        for line in ifile:
            line = line.strip()
            if not line:
                continue
            elif line == 'END':
                return
            elif not line.startswith('TABLE ') or not line[6:] in self.tables:

    def __getattr__(self, key):
        if key == 'id':
            return int(self)
        self.__allocate()
        return self._record.get(key, None)

    def get(self, key):
        return self.__getattr__(key)

    def __setattr__(self, key, value):
        if key.startswith('_'):
            int.__setattr__(self, key, value)
            return
        self.__allocate()
        self._record[key] =  value

        primarykey = args.get('primarykey', None)
        fieldnames,newfields=set(),[]
        if primarykey:
            if not isinstance(primarykey,list):
                raise SyntaxError, \
                    "primarykey must be a list of fields from table '%s'" \
                    % tablename
            self._primarykey = primarykey            
            self._primarykey = primarykey
        elif not [f for f in fields if isinstance(f,Field) and f.type=='id']:
            field = Field('id', 'id')
            newfields.append(field)
            fieldnames.add('id')
            self._id = field
        for field in fields:
            if not isinstance(field, (Field, Table)):

            for field in fields:
                if isinstance(field, Field) and field.type == 'upload'\
                        and field.uploadfield is True:
                    tmp = field.uploadfield = '%s_blob' % field.name
                    fields.append(self._db.Field(tmp, 'blob', default=''))

        lower_fieldnames = set()
        reserved = dir(Table) + ['fields'] 
        reserved = dir(Table) + ['fields']
        for field in fields:
            if db and db.check_reserved:
                db.check_reserved_keyword(field.name)
            elif field.name in reserved:
                raise SyntaxError, "field name %s not allowed" % field.name

            if field.name.lower() in lower_fieldnames:
                raise SyntaxError, "duplicate field %s in table %s" \
                    % (field.name, tablename)
            else:
                lower_fieldnames.add(field.name.lower())

            self.fields.append(field.name)
            self[field.name] = field
            if field.type == 'id':
                self['id'] = field
            field.tablename = field._tablename = tablename
            field.table = field._table = self
            field.db = field._db = self._db
            if self._db and field.type!='text' and \
            if self._db and not field.type in ('text','blob') and \
                    self._db._adapter.maxcharlength < field.length:
                field.length = self._db._adapter.maxcharlength
            if field.requires == DEFAULT:
                field.requires = sqlhtml_validators(field)
        self.ALL = SQLALL(self)

        if hasattr(self,'_primarykey'):
            for k in self._primarykey:
                if k not in self.fields:
                    raise SyntaxError, \
                        "primarykey must be a list of fields from table '%s " % tablename
                else:
                    self[k].notnull = True

    def update(self,*args,**kwargs):
        raise RuntimeError, "Syntax Not Supported"

    def _validate(self,**vars):
        errors = Row()
        for key,value in vars.items():
            value,error = self[key].validate(value)
            if error:
                errors[key] = error
        return errors

                if name != 'id':
                    raise SyntaxError, 'Field %s does not belong to the table' % name
            else:
                new_fields.append((self[name],fields[name]))
                new_fields_names.append(name)
        for ofield in self:
            if not ofield.name in new_fields_names:
                if not update and ofield.default!=None:
                if not update and not ofield.default is None:
                    new_fields.append((ofield,ofield.default))
                elif update and ofield.update!=None:
                elif update and not ofield.update is None:
                    new_fields.append((ofield,ofield.update))
        for ofield in self:
            if not ofield.name in new_fields_names and ofield.compute:
                try:
                    new_fields.append((ofield,ofield.compute(Row(fields))))
                except KeyError:
                    pass

        colnames = None
        if isinstance(id_map, dict):
            if not self._tablename in id_map:
                id_map[self._tablename] = {}
            id_map_self = id_map[self._tablename]

        def fix(field, value, id_map):
            list_reference_s='list:reference'
            if value == null:
                value = None
            elif field.type=='blob':
                value = base64.b64decode(value)
            elif field.type=='double':
                if not value.strip():
                    value = None
                else:
                    value = float(value)
            elif field.type=='integer':
                if not value.strip():
                    value = None
                else:
                    value = int(value)
            elif field.type.startswith('list:string'):
                value = bar_decode_string(value)
            elif field.type.startswith('list:reference'):
                ref_table = field.type[10:].strip()
            elif field.type.startswith(list_reference_s):
                ref_table = field.type[len(list_reference_s):].strip()
                value = [id_map[ref_table][int(v)] \
                             for v in bar_decode_string(value)]
            elif field.type.startswith('list:'):
                value = bar_decode_integer(value)
            elif id_map and field.type.startswith('reference'):
                try:
                    value = id_map[field.type[9:].strip()][value]

                    record = self._db(query).select().first()
                    if record:
                        record.update_record(**dict(items))
                        new_id = record[self._id.name]
                    else:
                        new_id = self.insert(**dict(items))
                if id_map and cid != []:
                    id_map_self[line[cid]] = new_id
                    id_map_self[int(line[cid])] = new_id

    def with_alias(self, alias):
        return self._db._adapter.alias(self,alias)

    def on(self, query):
        return Expression(self._db,self._db._adapter.ON,self,query)

    def hour(self):
        return Expression(self.db, self.db._adapter.EXTRACT, self, 'hour', 'integer')

    def minutes(self):
        return Expression(self.db, self.db._adapter.EXTRACT, self, 'minute', 'integer')

    def coalesce(self,*others):
        return Expression(self.db, self.db._adapter.COALESCE, self, others, self.type)

    def coalesce_zero(self):
            return Expression(self.db, self.db._adapter.COALESCE_ZERO, self, None, self.type)
        return Expression(self.db, self.db._adapter.COALESCE_ZERO, self, None, self.type)

    def seconds(self):
        return Expression(self.db, self.db._adapter.EXTRACT, self, 'second', 'integer')

    def __getslice__(self, start, stop):
        if start < 0:
            pos0 = '(%s - %d)' % (self.len(), abs(start) - 1)

        return Query(self.db, self.db._adapter.STARTSWITH, self, value)

    def endswith(self, value):
        if not self.type in ('string', 'text'):
            raise SyntaxError, "endswith used with incompatible field type"
        return Query(self.db, self.db._adapter.ENDSWITH, self, value)

    def contains(self, value):
    def contains(self, value, all=False):
        if isinstance(value,(list,tuple)):
            subqueries = [self.contains(str(v).strip()) for v in value if str(v).strip()]
            return reduce(all and AND or OR, subqueries)
        if not self.type in ('string', 'text') and not self.type.startswith('list:'):
            raise SyntaxError, "contains used with incompatible field type"
        return Query(self.db, self.db._adapter.CONTAINS, self, value)

    def with_alias(self,alias):
        return Expression(self.db,self.db._adapter.AS,self,alias,self.type)

    def __getitem__(self, i):
        return None

    def __str__(self):
        return self._class

class FieldVirtual(object):
    def __init__(self,f):
        self.f = f

class FieldLazy(object):
    def __init__(self,f,handler=None):
        self.f = f
        self.handler = handler


class Field(Expression):

    Virtual = FieldVirtual
    Lazy = FieldLazy

    """
    an instance of this class represents a database field

    example::

        a = Field(name, 'string', length=32, default=None, required=False,

        required=False,
        requires=DEFAULT,
        ondelete='CASCADE',
        notnull=False,
        unique=False,
        uploadfield=True,
        widget=None,
        label=None,
        label=DEFAULT,
        comment=None,
        writable=True,
        readable=True,
        update=None,
        authorize=None,
        autodelete=False,
        represent=None,

        self.name = fieldname = cleanup(fieldname)
        if hasattr(Table,fieldname) or fieldname[0] == '_' or \
                regex_python_keywords.match(fieldname):
            raise SyntaxError, 'Field: invalid field name: %s' % fieldname
        if isinstance(type, Table):
            type = 'reference ' + type._tablename
        self.type = type  # 'string', 'integer'
        self.length = (length is None) and MAXCHARLENGTH or length
        self.length = (length is None) and DEFAULTLENGTH.get(type,512) or length
        if default==DEFAULT:
            self.default = update or None
        else:
            self.default = default
        self.required = required  # is this field required
        self.ondelete = ondelete.upper()  # this is for reference fields only
        self.notnull = notnull
        self.unique = unique
        self.uploadfield = uploadfield
        self.uploadfolder = uploadfolder
        self.uploadseparate = uploadseparate
        self.widget = widget
        if label == DEFAULT:
            self.label = ' '.join(i.capitalize() for i in fieldname.split('_'))
        else:
        self.label = label or ' '.join(item.capitalize() for item in fieldname.split('_'))
            self.label = label or ''
        self.comment = comment
        self.writable = writable
        self.readable = readable
        self.update = update
        self.authorize = authorize
        self.autodelete = autodelete
        if not represent and type in ('list:integer','list:string'):
            represent=lambda x: ', '.join(str(y) for y in x or [])
            represent=lambda x,r=None: ', '.join(str(y) for y in x or [])
        self.represent = represent
        self.compute = compute
        self.isattachment = True
        self.custom_store = custom_store
        self.custom_retrieve = custom_retrieve
        self.custom_delete = custom_delete
        if self.label is None:

    def __str__(self):
        try:
            return '%s.%s' % (self.tablename, self.name)
        except:
            return '<no table>.%s' % self.name


def raw(s): return Expression(None,s)

class Query(object):

    """
    a query object necessary to define a set.
    it can be stored or can be passed to DAL.__call__() to obtain a Set

    Example::

    def __init__(
        self,
        db,
        op,
        first=None,
        second=None,
        ):
        self.db = db
        self.db = self._db = db
        self.op = op
        self.first = first
        self.second = second

    def __str__(self):
        return self.db._adapter.expand(self)

        self.db = db
        self._db = db # for backward compatibility
        self.query = query

    def __call__(self, query):
        if isinstance(query,Table):
            query = query._id>0
        elif isinstance(query,str):
            query = raw(query)
        elif isinstance(query,Field):
            query = query!=None
        if self.query:
            return Set(self.db, self.query & query)
        else:
            return Set(self.db, query)

    def update(self, **update_fields):
        tablename = self.db._adapter.get_table(self.query)
        fields = self.db[tablename]._listify(update_fields,update=True)
        if not fields:
            raise SyntaxError, "No fields to update"
        self.delete_uploaded_files(update_fields)
        return self.db._adapter.update(tablename,self.query,fields)
    

    def validate_and_update(self, **update_fields):
        tablename = self.db._adapter.get_table(self.query)
        response = Row()
        response.errors = self.db[tablename]._validate(**update_fields) 
        response.errors = self.db[tablename]._validate(**update_fields)
        fields = self.db[tablename]._listify(update_fields,update=True)
        if not fields:
            raise SyntaxError, "No fields to update"
        self.delete_uploaded_files(update_fields)
        if not response.errors:
            response.updated = self.db._adapter.update(tablename,self.query,fields)
        else:

                    uploadfolder = field.uploadfolder
                    if not uploadfolder:
                        uploadfolder = os.path.join(self.db._adapter.folder, '..', 'uploads')
                    if field.uploadseparate:
                        items = oldname.split('.')
                        uploadfolder = os.path.join(uploadfolder,
                                                    "%s.%s" % (items[0], items[1]),
                                                    items[2][:2])                
                                                    items[2][:2])
                    oldpath = os.path.join(uploadfolder, oldname)
                    if os.path.exists(oldpath):
                        os.unlink(oldpath)

def update_record(pack, a={}):
def update_record(pack, a=None):
    (colset, table, id) = pack
    b = a or dict(colset)
    c = dict([(k,v) for (k,v) in b.items() if k in table.fields and table[k].type!='id'])
    table._db(table._id==id).update(**c)
    for (k, v) in c.items():
        colset[k] = v

class VirtualCommand(object):
    def __init__(self,method,row):
        self.method=method
        #self.instance=instance
        self.row=row
    def __call__(self,*args,**kwargs):
        return self.method(self.row,*args,**kwargs)

def lazy_virtualfield(f):
    f.__lazy__ = True
    return f

class Rows(object):

    """
    A wrapper for the return value of a select. It basically represents a table.
    It has an iterator and each row is represented as a dictionary.
    """

        self.db = db
        self.records = records
        self.colnames = colnames
        self.compact = compact
        self.response = rawrows

    def setvirtualfields(self,**keyed_virtualfields):
        """
        db.define_table('x',Field('number','integer'))
        if db(db.x).isempty(): [db.x.insert(number=i) for i in range(10)]

        from gluon.dal import lazy_virtualfield

        class MyVirtualFields(object):
            # normal virtual field (backward compatible, discouraged)
            def normal_shift(self): return self.x.number+1
            # lazy virtual field (because of @staticmethod)
            @lazy_virtualfield
            def lazy_shift(instance,row,delta=4): return row.x.number+delta
        db.x.virtualfields.append(MyVirtualFields())

        for row in db(db.x).select():
            print row.number, row.normal_shift, row.lazy_shift(delta=7)
        """
        if not keyed_virtualfields:
            return self
        for row in self.records:
            for (tablename,virtualfields) in keyed_virtualfields.items():
                attributes = dir(virtualfields)
                virtualfields.__dict__.update(row)
                if not tablename in row:
                    box = row[tablename] = Row()
                else:
                    box = row[tablename]
                updated = False
                for attribute in attributes:
                    if attribute[0] != '_':
                        method = getattr(virtualfields,attribute)
                        if hasattr(method,'im_func') and method.im_func.func_code.co_argcount:
                        if hasattr(method,'__lazy__'):
                            box[attribute]=VirtualCommand(method,row)
                        elif type(method)==types.MethodType:
                            if not updated:
                                virtualfields.__dict__.update(row)
                                updated = True
                            box[attribute]=method()
        return self

    def __and__(self,other):
        if self.colnames!=other.colnames: raise Exception, 'Cannot & incompatible Rows objects'
        records = self.records+other.records
        return Rows(self.db,records,self.colnames)

                else:
                    (t, f) = col.split('.')
                    field = self.db[t][f]
                    if isinstance(record.get(t, None), (Row,dict)):
                        value = record[t][f]
                    else:
                        value = record[f]
                    if field.type=='blob' and value!=None:
                    if field.type=='blob' and not value is None:
                        value = base64.b64encode(value)
                    elif represent and field.represent:
                        value = field.represent(value)
                    row.append(none_exception(value))
            writer.writerow(row)

    def xml(self):

        if not mode in ['object', 'array']:
            raise SyntaxError, 'Invalid JSON serialization mode: %s' % mode

        def inner_loop(record, col):
            (t, f) = col.split('.')
            res = None
            if not table_field.match(col):
                key = col
                res = record._extra[col]
            else:
                key = f
                if isinstance(record.get(t, None), Row):
                    res = record[t][f]
                else:
                    res = record[f]
            if mode == 'object':
                return (f, res)
                return (key, res)
            else:
                return res

        if mode == 'object':
            items = [dict([inner_loop(record, col) for col in
                     self.colnames]) for record in self]
        else:

################################################################################
# run tests
################################################################################

if __name__ == '__main__':
    import doctest
    doctest.testmod()