395
407
if False and self.folder and not os.path.exists(self.folder):
396
408
os.mkdir(self.folder)
398
def pool_connection(self, f):
410
def pool_connection(self, f, cursor=True):
412
this function defines: self.connection and self.cursor (iff cursor is True)
413
if self.pool_size>0 it will try pull the connection from the pool
414
if the connection is not active (closed by db server) it will loop
415
if not self.pool_size or no active connections in pool makes a new one
399
417
if not self.pool_size:
400
418
self.connection = f()
419
self.cursor = cursor and self.connection.cursor()
404
if not uri in ConnectionPool.pools:
405
ConnectionPool.pools[uri] = []
406
if ConnectionPool.pools[uri]:
407
self.connection = ConnectionPool.pools[uri].pop()
411
self.connection = f()
424
if not uri in ConnectionPool.pools:
425
ConnectionPool.pools[uri] = []
426
if ConnectionPool.pools[uri]:
427
self.connection = ConnectionPool.pools[uri].pop()
429
self.cursor = cursor and self.connection.cursor()
431
if self.cursor and self.check_active_connection:
432
self.execute('SELECT 1;')
438
self.connection = f()
439
self.cursor = cursor and self.connection.cursor()
412
441
if not hasattr(thread,'instances'):
413
442
thread.instances = []
414
443
thread.instances.append(self)
445
474
'list:reference': 'TEXT',
478
return "'%s'" % obj.replace("'", "''")
448
480
def integrity_error(self):
449
481
return self.driver.IntegrityError
483
def operational_error(self):
484
return self.driver.OperationalError
451
486
def file_exists(self, filename):
453
488
to be used ONLY for files that on GAE may not be on filesystem
798
833
def NOT_NULL(self,default,field_type):
799
834
return 'NOT NULL DEFAULT %s' % self.represent(default,field_type)
836
def COALESCE(self,first,second):
837
expressions = [self.expand(first)]+[self.expand(e) for e in second]
838
return 'COALESCE(%s)' % ','.join(expressions)
801
840
def COALESCE_ZERO(self,first):
802
841
return 'COALESCE(%s,0)' % self.expand(first)
804
846
def ALLOW_NULL(self):
945
987
return expression.op(expression.first, expression.second)
946
988
elif not expression.first is None:
947
989
return expression.op(expression.first)
990
elif not isinstance(expression.op,str):
991
return expression.op()
949
return expression.op()
993
return '(%s)' % expression.op
951
995
return self.represent(expression,field_type)
952
996
elif isinstance(expression,(list,tuple)):
1202
1249
if isinstance(distinct,(list,tuple)):
1203
1250
distinct = xorify(distinct)
1204
1251
sql_d = self.expand(distinct)
1205
return 'SELECT count(DISTINCT %s) FROM %s%s' % (sql_d, sql_t, sql_w)
1206
return 'SELECT count(*) FROM %s%s' % (sql_t, sql_w)
1252
return 'SELECT count(DISTINCT %s) FROM %s%s;' % (sql_d, sql_t, sql_w)
1253
return 'SELECT count(*) FROM %s%s;' % (sql_t, sql_w)
1208
1255
def count(self,query,distinct=None):
1209
1256
self.execute(self._count(query,distinct))
1450
1497
for (referee_table, referee_name) in \
1451
1498
table._referenced_by:
1452
1499
s = db[referee_table][referee_name]
1453
if not referee_table in colset:
1454
# for backward compatibility
1455
colset[referee_table] = Set(db, s == id)
1456
### add new feature?
1457
### colset[referee_table+'_by_'+refree_name] = Set(db, s == id)
1500
referee_link = db._referee_name and \
1501
db._referee_name % dict(table=referee_table,field=referee_name)
1502
if referee_link and not referee_link in colset:
1503
colset[referee_link] = Set(db, s == id)
1458
1504
colset['id'] = id
1459
1505
new_rows.append(new_row)
1460
1507
rowsobj = Rows(db, new_rows, colnames, rawrows=rows)
1461
1509
for tablename in virtualtables:
1462
for item in db[tablename].virtualfields:
1510
### new style virtual fields
1511
table = db[tablename]
1512
fields_virtual = [(f,v) for (f,v) in table.items() if isinstance(v,FieldVirtual)]
1513
fields_lazy = [(f,v) for (f,v) in table.items() if isinstance(v,FieldLazy)]
1514
if fields_virtual or fields_lazy:
1515
for row in rowsobj.records:
1516
box = row[tablename]
1517
for f,v in fields_virtual:
1519
for f,v in fields_lazy:
1520
box[f] = (v.handler or VirtualCommand)(v.f,row)
1522
### old style virtual fields
1523
for item in table.virtualfields:
1464
1525
rowsobj = rowsobj.setvirtualfields(**{tablename:item})
1465
1526
except KeyError:
1523
1588
dbpath = os.path.join(self.folder.decode(path_encoding).encode('utf8'),dbpath)
1524
1589
if not 'check_same_thread' in driver_args:
1525
1590
driver_args['check_same_thread'] = False
1591
if not 'detect_types' in driver_args:
1592
driver_args['detect_types'] = self.driver.PARSE_DECLTYPES
1526
1593
def connect(dbpath=dbpath, driver_args=driver_args):
1527
1594
return self.driver.Connection(dbpath, **driver_args)
1528
1595
self.pool_connection(connect)
1529
self.cursor = self.connection.cursor()
1530
1596
self.connection.create_function('web2py_extract', 2, SQLiteAdapter.web2py_extract)
1532
1598
def _truncate(self,table,mode = ''):
1561
1627
dbpath = os.path.join(self.folder.decode(path_encoding).encode('utf8'),dbpath)
1562
1628
def connect(dbpath=dbpath,driver_args=driver_args):
1563
1629
return self.driver.connect(java.sql.DriverManager.getConnection('jdbc:sqlite:'+dbpath),**driver_args)
1564
self.pool_connection(connect)
1565
self.cursor = self.connection.cursor()
1566
1630
# FIXME http://www.zentus.com/sqlitejdbc/custom_functions.html for UDFs
1567
1631
# self.connection.create_function('web2py_extract', 2, SQLiteAdapter.web2py_extract)
1569
def execute(self,a):
1570
return self.log_execute(a[:-1])
1633
def execute(self,a):
1634
return self.log_execute(a)
1573
1637
class MySQLAdapter(BaseAdapter):
1934
1994
command = command[:m.start('clob')] + str(i) + command[m.end('clob'):]
1935
1995
args.append(m.group('clob')[6:-2].replace("''", "'"))
1937
return self.log_execute(command[:-1], args)
1997
if command[-1:]==';':
1998
command = command[:-1]
1999
return self.log_execute(command, args)
1939
2001
def create_sequence_and_triggers(self, query, table, **args):
1940
2002
tablename = table._tablename
2208
2269
elif adapter_args['driver_name'] == 'firebirdsql':
2209
2270
self.driver = firebirdsql
2211
self.driver = kinterbasdb
2272
self.driver = kinterbasdb
2212
2273
def connect(driver_args=driver_args):
2213
2274
return self.driver.connect(**driver_args)
2214
2275
self.pool_connection(connect)
2215
self.cursor = self.connection.cursor()
2217
2277
def create_sequence_and_triggers(self, query, table, **args):
2218
2278
tablename = table._tablename
2272
2332
elif adapter_args['driver_name'] == 'firebirdsql':
2273
2333
self.driver = firebirdsql
2275
self.driver = kinterbasdb
2335
self.driver = kinterbasdb
2276
2336
def connect(driver_args=driver_args):
2277
2337
return self.driver.connect(**driver_args)
2278
2338
self.pool_connection(connect)
2279
self.cursor = self.connection.cursor()
2282
2341
class InformixAdapter(BaseAdapter):
2758
2811
def connect(host,port,db,user,passwd,driver_args=driver_args):
2759
2812
return self.driver.connect(host,port,db,user,passwd,**driver_args)
2760
2813
self.pool_connection(connect)
2761
self.cursor = self.connection.cursor()
2762
2814
self.execute('SET FOREIGN_KEY_CHECKS=1;')
2763
2815
self.execute("SET sql_mode='NO_BACKSLASH_ESCAPES';")
2811
2867
self.data += data
2813
2869
def close(self):
2814
self.db.executesql("DELETE FROM web2py_filesystem WHERE path='%s'" % self.filename)
2815
query = "INSERT INTO web2py_filesystem(path,content) VALUES ('%s','%s')" % \
2816
(self.filename, self.data.replace("'","''"))
2870
self.db.executesql("DELETE FROM web2py_filesystem WHERE path=%s" \
2871
% self.adapt(self.filename))
2872
query = "INSERT INTO web2py_filesystem(path,content) VALUES (%s,%s)"\
2873
% (self.adapt(self.filename), self.adapt(self.data))
2817
2874
self.db.executesql(query)
2818
2875
self.db.commit()
2846
2904
class GoogleSQLAdapter(UseDatabaseStoredFile,MySQLAdapter):
2848
def __init__(self, db, uri='google:sql://realm:domain/database', pool_size=0,
2849
folder=None, db_codec='UTF-8', check_reserved=None,
2850
migrate=True, fake_migrate=False,
2906
def __init__(self, db, uri='google:sql://realm:domain/database',
2907
pool_size=0, folder=None, db_codec='UTF-8',
2851
2908
credential_decoder = lambda x:x, driver_args={},
2855
2912
self.dbengine = "mysql"
2865
2922
instance = credential_decoder(m.group('instance'))
2866
2923
db = credential_decoder(m.group('db'))
2867
2924
driver_args['instance'] = instance
2925
createdb = adapter_args.get('createdb',True)
2869
2927
driver_args['database'] = db
2870
2928
def connect(driver_args=driver_args):
2871
2929
return rdbms.connect(**driver_args)
2872
2930
self.pool_connection(connect)
2873
self.cursor = self.connection.cursor()
2875
2932
# self.execute('DROP DATABASE %s' % db)
2876
2933
self.execute('CREATE DATABASE IF NOT EXISTS %s' % db)
2877
2934
self.execute('USE %s' % db)
3228
3285
def truncate(self,table,mode):
3229
3286
self.db(table._id > 0).delete()
3231
def select_raw(self,query,fields=[],attributes={}):
3288
def select_raw(self,query,fields=None,attributes=None):
3289
fields = fields or []
3290
attributes = attributes or {}
3232
3291
new_fields = []
3233
3292
for item in fields:
3234
3293
if isinstance(item,SQLALL):
3604
3663
driver_args.update(dict(host=host,port=port))
3605
3664
def connect(dbname=dbname,driver_args=driver_args):
3606
3665
return pymongo.Connection(**driver_args)[dbname]
3607
self.pool_connection(connect)
3666
self.pool_connection(connect,cursor=False)
3609
3668
def insert(self,table,fields):
3610
3669
ctable = self.connection[table._tablename]
3708
3767
field_type.find('.') < 0 and \
3709
3768
field_type[10:] in field.db.tables:
3710
3769
referenced = field.db[field_type[10:]]
3711
def repr_ref(id, r=referenced, f=ff): return f(r, id)
3770
def repr_ref(id, row=None, r=referenced, f=ff): return f(r, id)
3712
3771
field.represent = field.represent or repr_ref
3713
3772
if hasattr(referenced, '_format') and referenced._format:
3714
3773
requires = validators.IS_IN_DB(field.db,referenced._id,
3738
3797
requires._and = validators.IS_NOT_IN_DB(field.db,field)
3739
3798
return requires
3740
3799
elif field_type.startswith('list:'):
3741
def repr_list(values): return', '.join(str(v) for v in (values or []))
3800
def repr_list(values,row=None): return', '.join(str(v) for v in (values or []))
3742
3801
field.represent = field.represent or repr_list
3743
3802
if field.unique:
3744
3803
requires.insert(0,validators.IS_NOT_IN_DB(field.db,field))
3845
3910
def __call__(self):
3846
3911
return copy.copy(self)
3913
def smart_query(fields,text):
3914
if not isinstance(fields,(list,tuple)):
3917
for field in fields:
3918
if isinstance(field,Field):
3919
new_fields.append(field)
3920
elif isinstance(field,Table):
3921
for ofield in field:
3922
new_fields.append(ofield)
3924
raise RuntimeError, "fields must be a list of fields"
3926
for field in fields:
3927
n = field.name.lower()
3928
if not n in field_map:
3929
field_map[n] = field
3930
n = str(field).lower()
3931
if not n in field_map:
3932
field_map[n] = field
3933
re_constants = re.compile('(\"[^\"]*?\")|(\'[^\']*?\')')
3937
m = re_constants.search(text)
3939
text = text[:m.start()]+('#%i' % i)+text[m.end():]
3940
constants[str(i)] = m.group()[1:-1]
3942
text = re.sub('\s+',' ',text).lower()
3943
for a,b in [('&','and'),
3955
(' less or equal than ','<='),
3956
(' greater or equal than ','>='),
3957
(' equal or less than ','<='),
3958
(' equal or greater than ','>='),
3959
(' less or equal ','<='),
3960
(' greater or equal ','>='),
3961
(' equal or less ','<='),
3962
(' equal or greater ','>='),
3963
(' not equal to ','!='),
3964
(' not equal ','!='),
3965
(' equal to ','=='),
3968
(' less than ','<'),
3969
(' greater than ','>'),
3970
(' starts with ','startswith'),
3971
(' ends with ','endswith'),
3974
text = text.replace(' is'+a,' %s ' % b)
3975
text = text.replace(a,' %s ' % b)
3976
text = re.sub('\s+',' ',text).lower()
3977
query = field = neg = op = logic = None
3978
for item in text.split():
3982
elif not neg and not logic and item in ('and','or'):
3984
elif item in field_map:
3985
field = field_map[item]
3987
raise RuntimeError, "Invalid syntax"
3988
elif not field is None and op is None:
3990
elif not op is None:
3991
if item.startswith('#'):
3992
if not item[1:] in constants:
3993
raise RuntimeError, "Invalid syntax"
3994
value = constants[item[1:]]
3997
if op == '==': op = 'like'
3998
if op == '==': new_query = field==value
3999
elif op == '<': new_query = field<value
4000
elif op == '>': new_query = field>value
4001
elif op == '<=': new_query = field<=value
4002
elif op == '>=': new_query = field>=value
4003
elif op == 'contains': new_query = field.contains(value)
4004
elif op == 'like': new_query = field.like(value)
4005
elif op == 'startswith': new_query = field.startswith(value)
4006
elif op == 'endswith': new_query = field.endswith(value)
4007
else: raise RuntimeError, "Invalid operation"
4008
if neg: new_query = ~new_query
4011
elif logic == 'and':
4015
field = op = neg = logic = None
3849
4019
class DAL(dict):
3907
def __init__(self, uri='sqlite://dummy.db', pool_size=0, folder=None,
4077
def __init__(self, uri='sqlite://dummy.db',
4078
pool_size=0, folder=None,
3908
4079
db_codec='UTF-8', check_reserved=None,
3909
4080
migrate=True, fake_migrate=False,
3910
4081
migrate_enabled=True, fake_migrate_all=False,
3911
4082
decode_credentials=False, driver_args=None,
3912
adapter_args={}, attempts=5, auto_import=False):
4083
adapter_args=None, attempts=5, auto_import=False):
3914
4085
Creates a new Database Abstraction Layer instance.
3964
4136
self._dbname = regex_dbname.match(uri).group()
3965
4137
if not self._dbname in ADAPTERS:
3966
4138
raise SyntaxError, "Error in URI '%s' or database not supported" % self._dbname
3967
# notice that driver args or {} else driver_args defaults to {} global, not correct
3968
args = (self,uri,pool_size,folder,db_codec,credential_decoder,driver_args or {}, adapter_args)
4139
# notice that driver args or {} else driver_args
4140
# defaults to {} global, not correct
4141
args = (self,uri,pool_size,folder,
4142
db_codec, credential_decoder,
4143
driver_args or {}, adapter_args or {})
3969
4144
self._adapter = ADAPTERS[self._dbname](*args)
3970
4145
connected = True
4269
4444
'trigger_name',
4270
4445
'sequence_name',
4272
raise SyntaxError, 'invalid table "%s" attribute: %s' % (tablename, key)
4273
migrate = self._migrate_enabled and args.get('migrate',self._migrate)
4274
fake_migrate = self._fake_migrate_all or args.get('fake_migrate',self._fake_migrate)
4448
raise SyntaxError, 'invalid table "%s" attribute: %s' \
4450
migrate = self._migrate_enabled and args.get('migrate',
4452
fake_migrate = self._fake_migrate_all or args.get('fake_migrate',
4454
table_class = args.get('table_class',Table)
4275
4455
format = args.get('format',None)
4276
4456
trigger_name = args.get('trigger_name', None)
4277
4457
sequence_name = args.get('sequence_name', None)
4293
4473
if self._common_fields:
4294
4474
fields = [f for f in fields] + [f for f in self._common_fields]
4296
t = self[tablename] = Table(self, tablename, *fields,
4297
**dict(primarykey=primarykey,
4298
trigger_name=trigger_name,
4299
sequence_name=sequence_name))
4476
t = self[tablename] = table_class(self, tablename, *fields,
4477
**dict(primarykey=primarykey,
4478
trigger_name=trigger_name,
4479
sequence_name=sequence_name))
4301
4481
if self._uri in (None,'None'):
4765
4955
new_fields_names.append(name)
4766
4956
for ofield in self:
4767
4957
if not ofield.name in new_fields_names:
4768
if not update and ofield.default!=None:
4958
if not update and not ofield.default is None:
4769
4959
new_fields.append((ofield,ofield.default))
4770
elif update and ofield.update!=None:
4960
elif update and not ofield.update is None:
4771
4961
new_fields.append((ofield,ofield.update))
4772
4962
for ofield in self:
4773
4963
if not ofield.name in new_fields_names and ofield.compute:
4863
5054
value = int(value)
4864
5055
elif field.type.startswith('list:string'):
4865
5056
value = bar_decode_string(value)
4866
elif field.type.startswith('list:reference'):
4867
ref_table = field.type[10:].strip()
5057
elif field.type.startswith(list_reference_s):
5058
ref_table = field.type[len(list_reference_s):].strip()
4868
5059
value = [id_map[ref_table][int(v)] \
4869
5060
for v in bar_decode_string(value)]
4870
5061
elif field.type.startswith('list:'):
4976
5167
def minutes(self):
4977
5168
return Expression(self.db, self.db._adapter.EXTRACT, self, 'minute', 'integer')
5170
def coalesce(self,*others):
5171
return Expression(self.db, self.db._adapter.COALESCE, self, others, self.type)
4979
5173
def coalesce_zero(self):
4980
return Expression(self.db, self.db._adapter.COALESCE_ZERO, self, None, self.type)
5174
return Expression(self.db, self.db._adapter.COALESCE_ZERO, self, None, self.type)
4982
5176
def seconds(self):
4983
5177
return Expression(self.db, self.db._adapter.EXTRACT, self, 'second', 'integer')
5066
5260
raise SyntaxError, "endswith used with incompatible field type"
5067
5261
return Query(self.db, self.db._adapter.ENDSWITH, self, value)
5069
def contains(self, value):
5263
def contains(self, value, all=False):
5264
if isinstance(value,(list,tuple)):
5265
subqueries = [self.contains(str(v).strip()) for v in value if str(v).strip()]
5266
return reduce(all and AND or OR, subqueries)
5070
5267
if not self.type in ('string', 'text') and not self.type.startswith('list:'):
5071
5268
raise SyntaxError, "contains used with incompatible field type"
5072
5269
return Query(self.db, self.db._adapter.CONTAINS, self, value)
5492
5708
raise SyntaxError, "No fields to update"
5493
5709
self.delete_uploaded_files(update_fields)
5494
5710
return self.db._adapter.update(tablename,self.query,fields)
5496
5712
def validate_and_update(self, **update_fields):
5497
5713
tablename = self.db._adapter.get_table(self.query)
5498
5714
response = Row()
5499
response.errors = self.db[tablename]._validate(**update_fields)
5715
response.errors = self.db[tablename]._validate(**update_fields)
5500
5716
fields = self.db[tablename]._listify(update_fields,update=True)
5502
5718
raise SyntaxError, "No fields to update"
5537
5753
items = oldname.split('.')
5538
5754
uploadfolder = os.path.join(uploadfolder,
5539
5755
"%s.%s" % (items[0], items[1]),
5541
5757
oldpath = os.path.join(uploadfolder, oldname)
5542
5758
if os.path.exists(oldpath):
5543
5759
os.unlink(oldpath)
5545
def update_record(pack, a={}):
5761
def update_record(pack, a=None):
5546
5762
(colset, table, id) = pack
5547
5763
b = a or dict(colset)
5548
5764
c = dict([(k,v) for (k,v) in b.items() if k in table.fields and table[k].type!='id'])
5575
5802
self.response = rawrows
5577
5804
def setvirtualfields(self,**keyed_virtualfields):
5806
db.define_table('x',Field('number','integer'))
5807
if db(db.x).isempty(): [db.x.insert(number=i) for i in range(10)]
5809
from gluon.dal import lazy_virtualfield
5811
class MyVirtualFields(object):
5812
# normal virtual field (backward compatible, discouraged)
5813
def normal_shift(self): return self.x.number+1
5814
# lazy virtual field (because of @staticmethod)
5816
def lazy_shift(instance,row,delta=4): return row.x.number+delta
5817
db.x.virtualfields.append(MyVirtualFields())
5819
for row in db(db.x).select():
5820
print row.number, row.normal_shift, row.lazy_shift(delta=7)
5578
5822
if not keyed_virtualfields:
5580
5824
for row in self.records:
5581
5825
for (tablename,virtualfields) in keyed_virtualfields.items():
5582
5826
attributes = dir(virtualfields)
5583
virtualfields.__dict__.update(row)
5584
5827
if not tablename in row:
5585
5828
box = row[tablename] = Row()
5587
5830
box = row[tablename]
5588
5832
for attribute in attributes:
5589
5833
if attribute[0] != '_':
5590
5834
method = getattr(virtualfields,attribute)
5591
if hasattr(method,'im_func') and method.im_func.func_code.co_argcount:
5835
if hasattr(method,'__lazy__'):
5836
box[attribute]=VirtualCommand(method,row)
5837
elif type(method)==types.MethodType:
5839
virtualfields.__dict__.update(row)
5592
5841
box[attribute]=method()
added
removed
gluon/dal.py
