1 """
2 This file is part of web2py Web Framework (Copyrighted, 2007)
3 Developed by Massimo Di Pierro <mdipierro@cs.depaul.edu>
4 License: GPL v2
5 """
6
7 __all__=['SQLDB','SQLField']
8
9 import re, sys, os, types, cPickle, datetime, thread, cStringIO, csv, copy
10
11 try:
12 import hashlib
13 - def hash5(txt): return hashlib.md5(txt).hexdigest()
14 except:
15 import md5
16 - def hash5(txt): return md5.new(txt).hexdigest()
17
18 try: import sqlite3
19 except:
20 try:
21 from pysqlite2 import dbapi2 as sqlite3
22 sys.stderr.write('warning: importing mysqlite3.dbapi2 as sqlite3\n')
23 except:
24 sys.stderr.write('warning: no sqlite3 or dbapi2 driver\n')
25 try: import MySQLdb
26 except: sys.stderr.write('warning: no MySQLdb driver\n')
27 try: import psycopg2
28 except: sys.stderr.write('warning: no psycopg2 driver\n')
29 try:
30 import cx_Oracle
31 sys.stderr.write('warning: support for Oracle is experimental\n')
32 except: sys.stderr.write('warning: no cx_Oracle driver\n')
33 try:
34 import Sybase
35 sys.stderr.write('warning: support for Sybase is very experimental\n')
36 except: sys.stderr.write('warning: no Sybase driver\n')
37
38 import portalocker
39 import validators
40
41 sql_locker=thread.allocate_lock()
42 """
43 notes on concurrency....
44 Accessing SQLDB._folders, SQLDB._instances and SQLDB .table files
45 is not thread safe so they are locked with sql_locker
46 Moreover .table files are locked with portalocker to account for multiple
47 parallel processes.
48 """
49
50 """
51 date, time and datetime must be in ISO8601 format: yyyy-mm-dd hh:mm:ss
52 """
53
54
55 SQL_DIALECTS={'sqlite':{'boolean':'CHAR(1)',
56 'string':'CHAR(%(length)s)',
57 'text':'TEXT',
58 'password':'CHAR(%(length)s)',
59 'blob':'BLOB',
60 'upload':'CHAR(64)',
61 'integer':'INTEGER',
62 'double':'DOUBLE',
63 'date':'DATE',
64 'time':'TIME',
65 'datetime':'TIMESTAMP',
66 'id':'INTEGER PRIMARY KEY AUTOINCREMENT',
67 'reference':'REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s',
68 'lower':'LOWER(%(field)s)',
69 'upper':'UPPER(%(field)s)',
70 'is null':'IS NULL',
71 'is not null':'IS NOT NULL',
72 'extract':"web2py_extract('%(name)s',%(field)s)"},
73 'mysql':{'boolean':'CHAR(1)',
74 'string':'VARCHAR(%(length)s)',
75 'text':'TEXT',
76 'password':'VARCHAR(%(length)s)',
77 'blob':'BLOB',
78 'upload':'VARCHAR(64)',
79 'integer':'INT',
80 'double':'DOUBLE',
81 'date':'DATE',
82 'time':'TIME',
83 'datetime':'TIMESTAMP',
84 'id':'INT AUTO_INCREMENT NOT NULL',
85 'reference':'INT NOT NULL, INDEX %(field_name)s__idx (%(field_name)s), FOREIGN KEY (%(field_name)s) REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s',
86 'lower':'LOWER(%(field)s)',
87 'upper':'UPPER(%(field)s)',
88 'is null':'IS NULL',
89 'is not null':'IS NOT NULL',
90 'extract':'EXTRACT(%(name)s FROM %(field)s)'},
91 'postgres':{'boolean':'CHAR(1)',
92 'string':'VARCHAR(%(length)s)',
93 'text':'TEXT',
94 'password':'VARCHAR(%(length)s)',
95 'blob':'BYTEA',
96 'upload':'VARCHAR(64)',
97 'integer':'INTEGER',
98 'double':'FLOAT8',
99 'date':'DATE',
100 'time':'TIME',
101 'datetime':'TIMESTAMP',
102 'id':'SERIAL PRIMARY KEY',
103 'reference':'INTEGER REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s',
104 'lower':'LOWER(%(field)s)',
105 'upper':'UPPER(%(field)s)',
106 'is null':'IS NULL',
107 'is not null':'IS NOT NULL',
108 'extract':'EXTRACT(%(name)s FROM %(field)s)'},
109 'oracle':{'boolean':'CHAR(1)',
110 'string':'VARCHAR2(%(length)s)',
111 'text':'CLOB',
112 'password':'VARCHAR2(%(length)s)',
113 'blob':'BLOB',
114 'upload':'VARCHAR2(64)',
115 'integer':'INT',
116 'double':'FLOAT',
117 'date':'DATE',
118 'time':'CHAR(8)',
119 'datetime':'DATE',
120 'id':'NUMBER PRIMARY KEY',
121 'reference':'NUMBER, CONSTRAINT %(field_name)s FOREIGN KEY (%(field_name)s) REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s',
122 'lower':'LOWER(%(field)s)',
123 'upper':'UPPER(%(field)s)',
124 'is null':'IS NULL',
125 'is not null':'IS NOT NULL',
126 'extract':'EXTRACT(%(name)s FROM %(field)s)'},
127 'sybase':{'boolean':'CHAR(1)',
128 'string':'VARCHAR(%(length)s)',
129 'text':'TEXT',
130 'password':'VARCHAR(%(length)s)',
131 'blob':'BLOB',
132 'upload':'VARCHAR2(64)',
133 'integer':'INT',
134 'double':'FLOAT',
135 'date':'DATETIME',
136 'time':'CHAR(8)',
137 'datetime':'DATETIME',
138 'id':'NUMERIC(8,0) IDENTITY PRIMARY KEY',
139 'reference':'NUMERIC(8,0) REFERENCES %(foreign_key)s',
140 'lower':'LOWER(%(field)s)',
141 'upper':'UPPER(%(field)s)',
142 'is null':'IS NULL',
143 'is not null':'IS NOT NULL',
144 'extract':'DATEPART(%(name)s , %(field)s)'}
145 }
146
148 v={'boolean':[],
149 'string':validators.IS_LENGTH(length),
150 'text':[],
151 'password':validators.IS_LENGTH(length),
152 'blob':[],
153 'upload':[],
154 'double':validators.IS_FLOAT_IN_RANGE(-1e100,1e100),
155 'integer':validators.IS_INT_IN_RANGE(-1e100,1e100),
156 'date':validators.IS_DATE(),
157 'time':validators.IS_TIME(),
158 'datetime':validators.IS_DATETIME(),
159 'reference':validators.IS_INT_IN_RANGE(0,1e100)}
160 try: return v[field_type[:9]]
161 except KeyError: return []
162
164 if object is None: return 'NULL'
165 if fieldtype=='boolean':
166 if object and not str(object)[0].upper()=='F': return "'T'"
167 else: return "'F'"
168 if fieldtype[0]=='i': return str(int(object))
169 elif fieldtype[0]=='r': return str(int(object))
170 elif fieldtype=='double': return str(float(object))
171 if isinstance(object,unicode): object=object.encode('utf-8')
172 if fieldtype=='date':
173 if isinstance(object,(datetime.date,datetime.datetime)): object=object.strftime('%Y-%m-%d')
174 else: object=str(object)
175
176 if dbname=='oracle': return "to_date('%s','yyyy-mm-dd')" % object
177
178 if dbname=='sybase': return "DATE '%s'" % object
179 elif fieldtype=='datetime':
180 if isinstance(object,datetime.datetime): object=object.strftime('%Y-%m-%d %H:%M:%S')
181 elif isinstance(object,datetime.date): object=object.strftime('%Y-%m-%d 00:00:00')
182 else: object=str(object)
183
184 if dbname=='oracle': return "to_date('%s','yyyy-mm-dd hh24:mi:ss')" % object
185
186 if dbname=='sybase': return "datetime '%s'" % object
187 elif fieldtype=='time':
188 if isinstance(object,datetime.time): object=object.strftime('%H:%M:%S')
189 else: object=str(object)
190 else: object=str(object)
191 return "'%s'" % object.replace("'","''").replace('\0','\\0')
192
194 if re.compile('[^0-9a-zA-Z_]').findall(text):
195 raise Exception, 'only [0-9a-zA-Z_] allowed in table and field names'
196 return text
197
199 table={'year':(0,4),'month':(5,7),'day':(8,10),
200 'hour':(11,13),'minutes':(14,16),'seconds':(17,19)}
201 try:
202 i,j=table[lookup]
203 return int(s[i:j])
204 except: return None
205
207 """
208 a dictionary that let you do d['a'] as well as d.a
209 """
212 if self.has_key(key):
213 raise SyntaxError, 'Object exists and cannot be redefined'
214 self[key] = value
216
218 - def __call__(self): return copy.copy(self)
219
221 """
222 now we can declare static methods in python!
223 """
225
227 """
228 an instance of this class represents a database connection
229
230 Example:
231
232 db=SQLDB('sqlite://test.db')
233 db.define_table('tablename',SQLField('fieldname1'),
234 SQLField('fieldname2'))
235
236 """
237
238 _folders={}
239 _instances={}
240 @static_method
245
246 @static_method
248
249 """ to close cleanly databases in a multithreaded environment """
250 sql_locker.acquire()
251 pid=thread.get_ident()
252 if SQLDB._folders.has_key(pid):
253 del SQLDB._folders[pid]
254 if SQLDB._instances.has_key(pid):
255 instances=SQLDB._instances[pid]
256 while instances:
257 instance=instances.pop()
258 action(instance)
259 instance._connection.close()
260 del SQLDB._instances[pid]
261 sql_locker.release()
262 return
263 - def __init__(self,uri='sqlite://dummy.db'):
264 self._uri=uri
265 self['_lastsql']=''
266 self.tables=SQLCallableList()
267 pid=thread.get_ident()
268
269 sql_locker.acquire()
270 if self._folders.has_key(pid): self._folder=self._folders[pid]
271 else: self._folder=self._folders[pid]=''
272 sql_locker.release()
273
274 if self._uri[:9]=='sqlite://':
275 self._dbname='sqlite'
276 if uri[9]!='/':
277 dbpath=os.path.join(self._folder,uri[9:])
278 self._connection=sqlite3.Connection(dbpath)
279 else:
280 self._connection=sqlite3.Connection(uri[9:])
281 self._connection.create_function("web2py_extract",2,
282 sqlite3_web2py_extract)
283 self._cursor=self._connection.cursor()
284 self._execute=lambda *a,**b: self._cursor.execute(*a,**b)
285 elif self._uri[:8]=='mysql://':
286 self._dbname='mysql'
287 m=re.compile('^(?P<user>[^:@]+)(\:(?P<passwd>[^@]*))?@(?P<host>[^\:/]+)(\:(?P<port>[0-9]+))?/(?P<db>.+)$').match(self._uri[8:])
288 user=m.group('user')
289 if not user: raise SyntaxError, "User required"
290 passwd=m.group('passwd')
291 if not passwd: passwd=''
292 host=m.group('host')
293 if not host: raise SyntaxError, "Host name required"
294 db=m.group('db')
295 if not db: raise SyntaxError, "Database name required"
296 port=m.group('port')
297 if not port: port='3306'
298 self._connection=MySQLdb.Connection(db=db,
299 user=user,
300 passwd=passwd,
301 host=host,
302 port=int(port),
303 charset='utf8')
304 self._cursor=self._connection.cursor()
305 self._execute=lambda *a,**b: self._cursor.execute(*a,**b)
306 self._execute('SET FOREIGN_KEY_CHECKS=0;')
307 elif self._uri[:11]=='postgres://':
308 self._dbname='postgres'
309 m=re.compile('^(?P<user>[^:@]+)(\:(?P<passwd>[^@]*))?@(?P<host>[^\:/]+)(\:(?P<port>[0-9]+))?/(?P<db>.+)$').match(self._uri[11:])
310 user=m.group('user')
311 if not user: raise SyntaxError, "User required"
312 passwd=m.group('passwd')
313 if not passwd: passwd=''
314 host=m.group('host')
315 if not host: raise SyntaxError, "Host name required"
316 db=m.group('db')
317 if not db: raise SyntaxError, "Database name required"
318 port=m.group('port')
319 if not port: port='5432'
320 msg="dbname='%s' user='%s' host='%s' port=%s password='%s'" % (db,user,host,port,passwd)
321 self._connection=psycopg2.connect(msg)
322 self._cursor=self._connection.cursor()
323 self._execute=lambda *a,**b: self._cursor.execute(*a,**b)
324 query='BEGIN;'
325 self['_lastsql']=query
326 self._execute(query)
327 self._execute("SET CLIENT_ENCODING TO 'UNICODE';")
328 elif self._uri[:9]=='oracle://':
329 self._dbname='oracle'
330 self._connection=cx_Oracle.connect(self._uri[9:])
331 self._cursor=self._connection.cursor()
332 self._execute=lambda a: self._cursor.execute(a[:-1])
333 self._execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';")
334 self._execute("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS';")
335 elif self._uri[:9]=='sybase://':
336 self._dbname='sybase'
337 m=re.compile('^(?P<user>[^:@]+)(\:(?P<passwd>[^@]*))?@(?P<host>[^\:/]+)(\:(?P<port>[0-9]+))?/(?P<db>.+)$').match(self._uri[9:])
338 user=m.group('user')
339 if not user: raise SyntaxError, "User required"
340 passwd=m.group('passwd')
341 if not passwd: passwd=''
342 host=m.group('host')
343 if not host: raise SyntaxError, "Host name required"
344 db=m.group('db')
345 if not db: raise SyntaxError, "Database name required"
346 port=m.group('port')
347 if not port: port='5000'
348
349 self._connection=Sybase.connect(host,user,passwd,db, auto_commit = 1)
350 self._cursor=self._connection.cursor()
351
352 self._execute=lambda a: self._cursor.execute(a[:-1])
353 else:
354 raise SyntaxError, 'database type not supported'
355 self._translator=SQL_DIALECTS[self._dbname]
356
357 sql_locker.acquire()
358 if self._instances.has_key(pid): self._instances[pid].append(self)
359 else: self._instances[pid]=[self]
360 sql_locker.release()
361 pass
363 if not args.has_key('migrate'): args['migrate']=True
364 if args.keys()!=['migrate']:
365 raise SyntaxError, 'invalid table attribute'
366 tablename=cleanup(tablename)
367 if tablename in dir(self) or tablename[0]=='_':
368 raise SyntaxError, 'invalid table name'
369 if not tablename in self.tables: self.tables.append(tablename)
370 else: raise SyntaxError, "table already defined"
371 t=self[tablename]=SQLTable(self,tablename,*fields)
372 sql_locker.acquire()
373 try:
374 query = t._create(migrate=args['migrate'])
375 except Exception, e:
376 sql_locker.release()
377 raise e
378 sql_locker.release()
379 return t
387 self['_lastsql']=query
388 self._execute(query)
389 return self._cursor.fetchall()
390
395 s=['%s.%s'%(self.table._tablename,name) for name in self.table.fields]
396 return ', '.join(s)
397
399 """
400 an instance of this class represents a database table
401 Example:
402
403 db=SQLDB(...)
404 db.define_table('users',SQLField('name'))
405 db.users.insert(name='me') # print db.users._insert(...) to see SQL
406 db.users.drop()
407 """
408 - def __init__(self,db,tablename,*fields):
409 self._db=db
410 self._tablename=tablename
411 self.fields=SQLCallableList()
412 self._referenced_by=[]
413 fields=list(fields)
414 fields.insert(0,SQLField('id','id'))
415 for field in fields:
416 self.fields.append(field.name)
417 self[field.name]=field
418 field._tablename=self._tablename
419 field._table=self
420 field._db=self._db
421 self.ALL=SQLALL(self)
423 return '<table %s>' % (self._tablename)
425 fields=[]
426 sql_fields={}
427 for k in self.fields:
428 field=self[k]
429 if field.type[:9]=='reference':
430 referenced=field.type[10:].strip()
431 if not referenced:
432 raise SyntaxError, 'SQLTable: reference to nothing!'
433 if not self._db.has_key(referenced):
434 raise SyntaxError, 'SQLTable: table does not exist'
435 referee=self._db[referenced]
436 ftype=self._db._translator[field.type[:9]] % dict(field_name=field.name,foreign_key=referenced+'(id)',on_delete_action=field.ondelete)
437 if self._tablename in referee.fields:
438 raise SyntaxError, 'SQLField: table name has same name as a field in referenced table'
439 self._db[referenced]._referenced_by.append((self._tablename,field.name))
440 elif not self._db._translator.has_key(field.type):
441 raise SyntaxError, 'SQLField: unkown field type'
442 else:
443 ftype=self._db._translator[field.type] % dict(length=field.length)
444 if not field.type[:9] in ['id','reference']:
445 if field.notnull: ftype+=' NOT NULL'
446 if field.unique: ftype+=' UNIQUE'
447 sql_fields[field.name]=ftype
448 fields.append('%s %s' % (field.name,ftype))
449 other=';'
450 if self._db._dbname=='mysql':
451 fields.append('PRIMARY KEY(id)')
452 other=' ENGINE=InnoDB CHARACTER SET utf8;'
453 fields=',\n\t'.join(fields)
454 query='CREATE TABLE %s(\n\t%s\n)%s' % (self._tablename,fields,other)
455 if not migrate:
456 return query
457 elif isinstance(migrate,str):
458 self._dbt=os.path.join(self._db._folder,migrate)
459 else:
460 self._dbt=os.path.join(self._db._folder,\
461 '%s_%s.table' % (hash5(self._db._uri),self._tablename))
462 logfilename=os.path.join(self._db._folder,'sql.log')
463 logfile=open(logfilename,'a')
464 if not os.access(self._dbt,os.R_OK):
465 logfile.write('timestamp: %s\n' % \
466 datetime.datetime.today().isoformat())
467 logfile.write(query+'\n')
468 self._db['_lastsql']=query
469 self._db._execute(query)
470 if self._db._dbname=='oracle':
471 t=self._tablename
472 self._db._execute('CREATE SEQUENCE %s_sequence START WITH 1 INCREMENT BY 1 NOMAXVALUE;' % t)
473 self._db._execute('CREATE OR REPLACE TRIGGER %s_trigger BEFORE INSERT ON %s FOR EACH ROW BEGIN SELECT %s_sequence.nextval INTO :NEW.id FROM DUAL; END;\n' % (t,t,t))
474 self._db.commit()
475 file=open(self._dbt,'w')
476 portalocker.lock(file, portalocker.LOCK_EX)
477 cPickle.dump(sql_fields,file)
478 file.close()
479 logfile.write('success!\n')
480 else:
481 file=open(self._dbt,'r')
482 portalocker.lock(file, portalocker.LOCK_SH)
483 sql_fields_old=cPickle.load(file)
484 file.close()
485 if sql_fields!=sql_fields_old:
486 self._migrate(sql_fields,sql_fields_old,logfile)
487 return query
488 - def _migrate(self,sql_fields,sql_fields_old,logfile):
489 keys=sql_fields.keys()
490 for key in sql_fields_old.keys():
491 if not key in keys: keys.append(key)
492 for key in keys:
493 if not sql_fields_old.has_key(key):
494 query='ALTER TABLE %s ADD COLUMN %s %s;' % \
495 (self._tablename, key, \
496 sql_fields[key].replace(', ',', ADD '))
497 elif self._db._dbname=='sqlite': query=None
498 elif not sql_fields.has_key(key):
499 query='ALTER TABLE %s DROP COLUMN %s;' % \
500 (self._tablename, key)
501 elif sql_fields[key]!=sql_fields_old[key]:
502
503 t=self._tablename
504 tt=sql_fields[key].replace(', ',', ADD ')
505 query='ALTER TABLE %s ADD %s__tmp %s;\n' % (t,key,tt) +\
506 'UPDATE %s SET %s__tmp=%s;\n' % (t,key,key) +\
507 'ALTER TABLE %s DROP COLUMN %s;\n' % (t,key) +\
508 'ALTER TABLE %s ADD %s %s;\n' % (t,key,tt) +\
509 'UPDATE %s SET %s=%s__tmp;\n' % (t,key,key) +\
510 'ALTER TABLE %s DROP COLUMN %s__tmp;'%(t,key)
511
512 else: query=None
513 if query:
514 logfile.write('timestamp: %s\n' % \
515 datetime.datetime.today().isoformat())
516 logfile.write(query+'\n')
517 self._db['_lastsql']=query
518 self._db._execute(query)
519 if sql_fields.has_key(key): sql_fields_old[key]=sql_fields[key]
520 else: del sql_fields_old[key]
521 logfile.write('success!\n')
522 file=open(self._dbt,'w')
523 portalocker.lock(file, portalocker.LOCK_EX)
524 cPickle.dump(sql_fields_old,file)
525 file.close()
530 t=self._tablename
531 if self._db._dbname=='oracle':
532 return ['DROP TABLE %s;' % t,'DROP SEQUENCE %s_sequence;' % t]
533 return ['DROP TABLE %s;' % t]
535 logfile=open(os.path.join(self._db._folder,'sql.log'),'a')
536 queries=self._drop()
537 self._db['_lastsql']='\n'.join(queries)
538 for query in queries:
539 logfile.write(query+'\n')
540 self._db._execute(query)
541 del self._db[self._tablename]
542 del self._db.tables[self._db.tables.index(self._tablename)]
543 self._db.commit()
544 os.unlink(self._dbt)
545 logfile.write('success!\n')
547 fs,vs=[],[]
548 if [key for key in fields.keys() if not key in self.fields]:
549 raise SyntaxError, 'invalid field name'
550 for fieldname in self.fields:
551 if fieldname=='id': continue
552 field=self[fieldname]
553 ft,fd=field.type,field._db._dbname
554 if fields.has_key(fieldname):
555 fs.append(fieldname)
556 value=fields[fieldname]
557 if field.type[:9] in ['integer','reference']: value=int(value)
558 if field.type=='double': value=float(value)
559 try: vs.append(sql_represent(value.id,ft,fd))
560 except: vs.append(sql_represent(value,ft,fd))
561 elif field.default!=None:
562 fs.append(fieldname)
563 vs.append(sql_represent(field.default,ft,fd))
564 elif field.required is True:
565 raise SyntaxError, 'SQLTable: missing required field'
566 sql_f=', '.join(fs)
567 sql_v=', '.join(vs)
568 sql_t=self._tablename
569 return 'INSERT INTO %s(%s) VALUES (%s);' % (sql_t,sql_f,sql_v)
571 query=self._insert(**fields)
572 self._db['_lastsql']=query
573 self._db._execute(query)
574 if self._db._dbname=='sqlite':
575 id=self._db._cursor.lastrowid
576 elif self._db._dbname=='postgres':
577 self._db._execute("select currval('%s_id_Seq')" % self._tablename)
578 id=int(self._db._cursor.fetchone()[0])
579 elif self._db._dbname=='mysql':
580 self._db._execute("select last_insert_id();")
581 id=int(self._db._cursor.fetchone()[0])
582 elif self._db._dbname=='oracle':
583 t=self._tablename
584 self._db._execute('SELECT %s_sequence.currval FROM dual;' %t)
585 id=int(self._db._cursor.fetchone()[0])
586 elif self._db._dbname=='sybase':
587 self._db._execute("SELECT @@IDENTITY")
588 id=int(self._db._cursor.fetchone()[0])
589 else:
590 id=None
591 return id
593 """
594 import records from csv file. Column headers must have same names as
595 table fields. field 'id' is ignored. If column names read 'table.file'
596 the 'table.' prefix is ignored.
597 """
598 reader = csv.reader(file)
599 colnames=None
600 for line in reader:
601 if not colnames:
602 colnames=[x[x.find('.')+1:] for x in line]
603 c=[i for i in range(len(line)) if colnames[i]!='id']
604 else:
605 items=[(colnames[i],line[i]) for i in c]
606 self.insert(**dict(items))
607
609 - def __init__(self,name,type='string',db=None):
610 self.name,self.type,self._db=name,type,db
612 return self.name
614 return SQLXorable(str(self)+', '+str(other),None,None)
616 return SQLXorable(str(self)+' DESC',None,None)
617
626 if isinstance(other,str):
627 return SQLXorable(str(self)+' IN (%s)'%other[:-1],None,None)
628 elif hasattr(other,'__iter__'):
629 r=','.join([sql_represent(item,self.type,self._db) for item in other])
630 return SQLXorable(str(self)+' IN (%s)'%r,None,None)
631 else: raise SyntaxError, 'do not know what to do'
632
637
639 """
640 an instance of this class represents a database field
641
642 example:
643
644 a=SQLField(name,'string',length=32,required=False,default=None,requires=IS_NOT_EMPTY(),notnull=False,unique=False)
645
646 to be used as argument of SQLDB.define_table
647
648 allowed field types:
649 string, boolean, integer, double, text, blob,
650 date, time, datetime, upload, password
651
652 strings must have a length or 32 by default.
653 fields should have a default or they will be required in SQLFORMs
654 the requires argument are used to validate the field input in SQLFORMs
655
656 """
657 - def __init__(self,fieldname,type='string',
658 length=32,default=None,required=False,
659 requires=sqlhtml_validators,ondelete='CASCADE',
660 notnull=False,unique=False):
661 self.name=cleanup(fieldname)
662 if fieldname in dir(SQLTable) or fieldname[0]=='_':
663 raise SyntaxError, 'SQLField: invalid field name'
664 if isinstance(type,SQLTable): type='reference '+type._tablename
665 if not length and type=='string': type='text'
666 elif not length and type=='password': length=32
667 self.type=type
668 if type=='upload': length=64
669 self.length=length
670 self.default=default
671 self.required=required
672 self.ondelete=ondelete.upper()
673 self.notnull=notnull
674 self.unique=unique
675 if requires==sqlhtml_validators: requires=sqlhtml_validators(type,length)
676 elif requires is None: requires=[]
677 self.requires=requires
687 s=self._db._translator["lower"] % dict(field=str(self))
688 return SQLXorable(s,'string',self._db)
690 s=self._db._translator["upper"] % dict(field=str(self))
691 return SQLXorable(s,'string',self._db)
693 s=self._db._translator["extract"] % dict(name='year',field=str(self))
694 return SQLXorable(s,'integer',self._db)
696 s=self._db._translator["extract"] % dict(name='month',field=str(self))
697 return SQLXorable(s,'integer',self._db)
699 s=self._db._translator["extract"] % dict(name='day',field=str(self))
700 return SQLXorable(s,'integer',self._db)
702 s=self._db._translator["extract"] % dict(name='hour',field=str(self))
703 return SQLXorable(s,'integer',self._db)
705 s=self._db._translator["extract"] % dict(name='minutes',field=str(self))
706 return SQLXorable(s,'integer',self._db)
708 s=self._db._translator["extract"] % dict(name='seconds',field=str(self))
709 return SQLXorable(s,'integer',self._db)
710 - def __str__(self): return '%s.%s' % (self._tablename,self.name)
711
713 """
714 a query object necessary to define a set.
715 t can be stored or can be passed to SQLDB.__call__() to obtain a SQLSet
716
717 Example:
718 query=db.users.name=='Max'
719 set=db(query)
720 records=set.select()
721 """
722 - def __init__(self,left,op=None,right=None):
723 self.left,self.op,self.right=left,op,right
728 if self.op is None and self.right is None: return self.left
729 if self.op=='~': return 'NOT %s' % str(self.right)
730 if self.right is None:
731 if self.op=='==':
732 return '%s %s' % (str(self.left),
733 self.left._db._translator['is null'])
734 if self.op=='!=':
735 return '%s %s' % (str(self.left),
736 self.left._db._translator['is not null'])
737 if type(self.right)==types.InstanceType:
738 lr=(str(self.left),str(self.right))
739 else:
740 lr=(str(self.left),sql_represent(self.right,self.left.type,self.left._db._dbname))
741 if self.op=='&': return '(%s AND %s)' % lr
742 if self.op=='|': return '(%s OR %s)' % lr
743 if self.op=='==': return '%s=%s' % lr
744 if self.op=='!=': return '%s<>%s' % lr
745 if self.op=='<': return '%s<%s' % lr
746 if self.op=='<=': return '%s<=%s' % lr
747 if self.op=='>': return '%s>%s' % lr
748 if self.op=='>=': return '%s>=%s' % lr
749 if self.op=='like': return '%s LIKE %s' % lr
750
751 regex_tables=re.compile('(?P<table>[a-zA-Z]\w*)\.')
752
754 items=regex_tables.findall(re.sub("'[^']*'",'',text))
755 tables={}
756 for item in items: tables[item]=True
757 return tables.keys()
758
760 """
761 sn SQLSet represents a set of records in the database,
762 the records are identified by the where=SQLQuery(...) object.
763 normally the SQLSet is generated by SQLDB.__call__(SQLQuery(...))
764
765 given a set, for example
766 set=db(db.users.name=='Max')
767 you can:
768 set.update(db.users.name='Massimo')
769 set.delete() # all elements in the set
770 set.select(orderby=db.users.id,groupby=db.users.name,limitby=(0,10))
771 and take subsets:
772 subset=set(db.users.id<5)
773 """
775 self._db=db
776 self._tables=[]
777
778 self.sql_w=str(where)
779
780 self._tables=parse_tablenames(self.sql_w)
781
784 - def _select(self,*fields,**attributes):
785 valid_attributes=['orderby','groupby','limitby','required',
786 'default','requires']
787 if [key for key in attributes.keys() if not key in valid_attributes]:
788 raise SyntaxError, 'invalid select attribute'
789
790 if not fields: fields=[self._db[table].ALL for table in self._tables]
791 sql_f=', '.join([str(f) for f in fields])
792 tablenames=parse_tablenames(self.sql_w+' '+sql_f)
793 if len(tablenames)<1: raise SyntaxError, 'SQLSet: no tables selected'
794 sql_t=', '.join(tablenames)
795 self.colnames=[c.strip() for c in sql_f.split(', ')]
796 if self.sql_w: sql_w=' WHERE '+self.sql_w
797 else: sql_w=''
798 sql_o=''
799 if attributes.has_key('groupby'):
800 sql_o+=' GROUP BY '+str(attributes['groupby'])
801 if attributes.has_key('orderby'):
802 sql_o=' ORDER BY '+str(attributes['orderby'])
803 if attributes.has_key('limitby'):
804 lmin,lmax=attributes['limitby']
805 sql_o+=' LIMIT %i OFFSET %i' % (lmax-lmin,lmin)
806 return 'SELECT %s FROM %s%s%s;'%(sql_f,sql_t,sql_w,sql_o)
807 - def select(self,*fields,**attributes):
808 """
809 Always returns a SQLRows object, even if it may be empty
810 """
811 def response(query):
812 self._db['_lastsql']=query
813 self._db._execute(query)
814 return self._db._cursor.fetchall()
815 if not attributes.has_key('cache'):
816 query=self._select(*fields,**attributes)
817 r=response(query)
818 else:
819 cache_model,time_expire=attributes['cache']
820 del attributes['cache']
821 query=self._select(*fields,**attributes)
822 key=self._db._uri+'/'+query
823 r=cache_model(key,lambda:response(query),time_expire)
824 return SQLRows(self._db,r,*self.colnames)
826 if len(self._tables)!=1:
827 raise SyntaxError, 'SQLSet: unable to determine what to delete'
828 tablename=self._tables[0]
829 if self.sql_w: sql_w=' WHERE '+self.sql_w
830 else: sql_w=''
831 return 'DELETE FROM %s%s;' % (tablename,sql_w)
833 query=self._delete()
834 self._db['_lastsql']=query
835 self._db._execute(query)
837 tablenames=self._tables
838 if len(tablenames)!=1:
839 raise SyntaxError, 'SQLSet: unable to determine what to do'
840 tt,fd=self._db[tablenames[0]],self._db._dbname
841 sql_v='SET '+', '.join(['%s=%s' % (field,sql_represent(value,tt[field].type,fd)) for field,value in fields.items()])
842 sql_t=tablenames[0]
843 if self.sql_w: sql_w=' WHERE '+self.sql_w
844 else: sql_w=''
845 return 'UPDATE %s %s%s;' % (sql_t,sql_v,sql_w)
847 query=self._update(**fields)
848 self._db['_lastsql']=query
849 self._db._execute(query)
850
852 s.update(**a)
853 for key,value in a.items(): t[str(key)]=value
854
856
857 """
858 A wrapper for the retun value of a select. It basically represents a table.
859 It has an iterator and each row is represented as a dictionary.
860 """
861 - def __init__(self,db,response,*colnames):
862 self._db=db
863 self.colnames=colnames
864 self.response=response
866 return len(self.response)
868 if i>=len(self.response) or i<0:
869 raise SyntaxError, 'SQLRows: no such row'
870 if len(self.response[0])!=len(self.colnames):
871 raise SyntaxError, 'SQLRows: internal error'
872 row=SQLStorage()
873 for j in range(len(self.colnames)):
874 value=self.response[i][j]
875 if isinstance(value,unicode): value=value.encode('utf-8')
876 packed=self.colnames[j].split('.')
877 try: tablename,fieldname=packed
878 except:
879 if not row.has_key('_extra'): row['_extra']=SQLStorage()
880 row['_extra'][self.colnames[j]]=value
881 continue
882 table=self._db[tablename]
883 field=table[fieldname]
884 if not row.has_key(tablename):
885 row[tablename]=SQLStorage()
886 if field.type[:9]=='reference':
887 referee=field.type[10:].strip()
888 rid=value
889 row[tablename][fieldname]=rid
890
891 elif field.type=='boolean' and value!=None:
892 if value=='T': row[tablename][fieldname]=True
893 else: row[tablename][fieldname]=False
894 elif field.type=='date' and value!=None and not isinstance(value,datetime.date):
895 y,m,d=[int(x) for x in str(value).strip().split('-')]
896 row[tablename][fieldname]=datetime.date(y,m,d)
897 elif field.type=='time' and value!=None and not isinstance(value,datetime.time):
898 time_items=[int(x) for x in str(value).strip().split(':')[:3]]
899 if len(time_items)==3: h,mi,s=time_items
900 else: h,mi,s=time_items+[0]
901 row[tablename][fieldname]=datetime.time(h,mi,s)
902 elif field.type=='datetime' and value!=None and not isinstance(value,datetime.datetime):
903 y,m,d=[int(x) for x in str(value)[:10].strip().split('-')]
904 time_items=[int(x) for x in str(value)[11:].strip().split(':')[:3]]
905 if len(time_items)==3: h,mi,s=time_items
906 else: h,mi,s=time_items+[0]
907 row[tablename][fieldname]=datetime.datetime(y,m,d,h,mi,s)
908 else:
909 row[tablename][fieldname]=value
910 if fieldname=='id':
911 id=row[tablename].id
912 row[tablename].update_record=lambda t=row[tablename], \
913 s=self._db(table.id==id),**a: update_record(t,s,a)
914 for referee_table,referee_name in table._referenced_by:
915 s=self._db[referee_table][referee_name]
916 row[tablename][referee_table]=SQLSet(self._db,s==id)
917 if len(row.keys())==1: return row[row.keys()[0]]
918 return row
920 """
921 iterator over records
922 """
923 for i in range(len(self)):
924 yield self[i]
926 """
927 serializes the table into a csv file
928 """
929 s=cStringIO.StringIO()
930 writer = csv.writer(s)
931 writer.writerow(self.colnames)
932 c=range(len(self.colnames))
933 for i in range(len(self)):
934 row=[self.response[i][j] for j in c]
935 for k in range(len(row)):
936 if isinstance(row[k],unicode): row[k]=row[k].encode('utf-8')
937 writer.writerow(row)
938 return s.getvalue()
940 """
941 serializes the table using sqlhtml.SQLTABLE (if present)
942 """
943 import sqlhtml
944 return sqlhtml.SQLTABLE(self).xml()
945
947 """
948
949 Create a table with all possible field types
950 'sqlite://test.db'
951 'mysql://root:none@localhost/test'
952 'postgres://mdipierro:none@localhost/test'
953
954 >>> if len(sys.argv)<2: db=SQLDB("sqlite://test.db")
955 >>> if len(sys.argv)>1: db=SQLDB(sys.argv[1])
956 >>> tmp=db.define_table('users',\
957 SQLField('stringf','string',length=32,required=True),\
958 SQLField('booleanf','boolean',default=False),\
959 SQLField('passwordf','password',notnull=True),\
960 SQLField('blobf','blob'),\
961 SQLField('uploadf','upload'),\
962 SQLField('integerf','integer',unique=True),\
963 SQLField('doublef','double',unique=True,notnull=True),\
964 SQLField('datef','date',default=datetime.date.today()),\
965 SQLField('timef','time'),\
966 SQLField('datetimef','datetime'),\
967 migrate='test_user.table')
968
969 Insert a field
970
971 >>> db.users.insert(stringf='a',booleanf=True,passwordf='p',blobf='0A',\
972 uploadf=None, integerf=5,doublef=3.14,\
973 datef=datetime.date(2001,1,1),\
974 timef=datetime.time(12,30,15),\
975 datetimef=datetime.datetime(2002,2,2,12,30,15))
976 1
977
978 Drop the table
979
980 >>> db.users.drop()
981
982 Examples of insert, select, update, delete
983
984 >>> tmp=db.define_table('person',\
985 SQLField('name'), \
986 SQLField('birth','date'),\
987 migrate='test_person.table')
988 >>> person_id=db.person.insert(name="Marco",birth='2005-06-22')
989 >>> person_id=db.person.insert(name="Massimo",birth='1971-12-21')
990 >>> len(db().select(db.person.ALL))
991 2
992 >>> me=db(db.person.id==person_id).select()[0] # test select
993 >>> me.name
994 'Massimo'
995 >>> db(db.person.name=='Massimo').update(name='massimo') # test update
996 >>> db(db.person.name=='Marco').delete() # test delete
997
998 Update a single record
999
1000 >>> me.update_record(name="Max")
1001 >>> me.name
1002 'Max'
1003
1004 Examples of complex search conditions
1005
1006 >>> len(db((db.person.name=='Max')&(db.person.birth<'2003-01-01')).select())
1007 1
1008 >>> len(db((db.person.name=='Max')&(db.person.birth<datetime.date(2003,01,01))).select())
1009 1
1010 >>> len(db((db.person.name=='Max')|(db.person.birth<'2003-01-01')).select())
1011 1
1012 >>> me=db(db.person.id==person_id).select(db.person.name)[0]
1013 >>> me.name
1014 'Max'
1015
1016 Examples of search conditions using extract from date/datetime/time
1017
1018 >>> len(db(db.person.birth.month()==12).select())
1019 1
1020 >>> len(db(db.person.birth.year()>1900).select())
1021 1
1022
1023 Example of usage of NULL
1024
1025 >>> len(db(db.person.birth==None).select()) ### test NULL
1026 0
1027 >>> len(db(db.person.birth!=None).select()) ### test NULL
1028 1
1029
1030 Examples of search consitions using lower, upper, and like
1031
1032 >>> len(db(db.person.name.upper()=='MAX').select())
1033 1
1034 >>> len(db(db.person.name.like('%ax')).select())
1035 1
1036 >>> len(db(db.person.name.upper().like('%AX')).select())
1037 1
1038 >>> len(db(~db.person.name.upper().like('%AX')).select())
1039 0
1040
1041 orderby, groupby and limitby
1042
1043 >>> people=db().select(db.person.name,orderby=db.person.name)
1044 >>> order=db.person.name|~db.person.birth
1045 >>> people=db().select(db.person.name,orderby=order)
1046 >>> people=db().select(db.person.name,orderby=order,groupby=db.person.name)
1047 >>> people=db().select(db.person.name,orderby=order,limitby=(0,100))
1048
1049 Example of one 2 many relation
1050
1051 >>> tmp=db.define_table('dog', \
1052 SQLField('name'), \
1053 SQLField('birth','date'), \
1054 SQLField('owner',db.person),\
1055 migrate='test_dog.table')
1056 >>> db.dog.insert(name='Snoopy',birth=None,owner=person_id)
1057 1
1058
1059 A simple JOIN
1060
1061 >>> len(db(db.dog.owner==db.person.id).select())
1062 1
1063
1064 Drop tables
1065
1066 >>> db.dog.drop()
1067 >>> db.person.drop()
1068
1069 Example of many 2 many relation and SQLSet
1070
1071 >>> tmp=db.define_table('author',SQLField('name'),\
1072 migrate='test_author.table')
1073 >>> tmp=db.define_table('paper',SQLField('title'),\
1074 migrate='test_paper.table')
1075 >>> tmp=db.define_table('authorship',\
1076 SQLField('author_id',db.author),\
1077 SQLField('paper_id',db.paper),\
1078 migrate='test_authorship.table')
1079 >>> aid=db.author.insert(name='Massimo')
1080 >>> pid=db.paper.insert(title='QCD')
1081 >>> tmp=db.authorship.insert(author_id=aid,paper_id=pid)
1082
1083 Define a SQLSet
1084
1085 >>> authored_papers=db((db.author.id==db.authorship.author_id)&\
1086 (db.paper.id==db.authorship.paper_id))
1087 >>> rows=authored_papers.select(db.author.name,db.paper.title)
1088 >>> for row in rows: print row.author.name, row.paper.title
1089 Massimo QCD
1090
1091 Example of search condition using belongs
1092
1093 >>> set=(1,2,3)
1094 >>> rows=db(db.paper.id.belongs(set)).select(db.paper.ALL)
1095 >>> print rows[0].title
1096 QCD
1097
1098 Example of search condition using nested select
1099
1100 >>> nested_select=db()._select(db.authorship.paper_id)
1101 >>> rows=db(db.paper.id.belongs(nested_select)).select(db.paper.ALL)
1102 >>> print rows[0].title
1103 QCD
1104
1105 Output in csv
1106
1107 >>> str(authored_papers.select(db.author.name,db.paper.title))
1108 'author.name,paper.title\\r\\nMassimo,QCD\\r\\n'
1109
1110 Delete all leftover tables
1111
1112 >>> db.authorship.drop()
1113 >>> db.author.drop()
1114 >>> db.paper.drop()
1115 """
1116
1117 if __name__=='__main__':
1118 import doctest
1119 doctest.testmod()
1120