Package gluon :: Module sql_sybase
[hide private]
[frames] | no frames]

Source Code for Module gluon.sql_sybase

   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
147 -def sqlhtml_validators(field_type,length):
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
163 -def sql_represent(object,fieldtype,dbname):
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 # I am not sure if these do what is expected, are we dealing with the database (and sql) now, or are we dealing with an (string?) object already from the db? 176 if dbname=='oracle': return "to_date('%s','yyyy-mm-dd')" % object 177 #if dbname=='sybase': return "stuff(stuff(convert(varchar, '%s', 102), 5, 1, '-'), 8, 1, '-')" % object 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 # I am not sure if these do what is expected, are we dealing with the database (and sql) now, or are we dealing with an (string?) object already from the db? 184 if dbname=='oracle': return "to_date('%s','yyyy-mm-dd hh24:mi:ss')" % object 185 #if dbname=='sybase': return "stuff(stuff(convert(varchar, '%s', 102), 5, 1, '-'), 8, 1, '-') + ' ' + convert(char(8), '%s', 108)" % object 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') ### escape 192
193 -def cleanup(text):
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
198 -def sqlite3_web2py_extract(lookup, s):
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
206 -class SQLStorage(dict):
207 """ 208 a dictionary that let you do d['a'] as well as d.a 209 """
210 - def __getattr__(self, key): return self[key]
211 - def __setattr__(self, key, value):
212 if self.has_key(key): 213 raise SyntaxError, 'Object exists and cannot be redefined' 214 self[key] = value
215 - def __repr__(self): return '<SQLStorage ' + dict.__repr__(self) + '>'
216
217 -class SQLCallableList(list):
218 - def __call__(self): return copy.copy(self)
219
220 -class static_method:
221 """ 222 now we can declare static methods in python! 223 """
224 - def __init__(self, anycallable): self.__call__ = anycallable
225
226 -class SQLDB(SQLStorage):
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 ### this allows gluon to comunite a folder for this thread 238 _folders={} 239 _instances={} 240 @static_method
241 - def _set_thread_folder(folder):
242 sql_locker.acquire() 243 SQLDB._folders[thread.get_ident()]=folder 244 sql_locker.release()
245 ### this allows gluon to commit/rollback all dbs in this thread 246 @static_method
247 - def close_all_instances(action):
248 #THIS IS NOT THREAD SAFE 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 ### check if there is a folder for this thread else use '' 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 ### now connect to database 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';") ### not completely sure but should work 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 # msg="dbname='%s' user='%s' host='%s' port=%s password='%s'" % (db,user,host,port,passwd) 349 self._connection=Sybase.connect(host,user,passwd,db, auto_commit = 1) 350 self._cursor=self._connection.cursor() 351 #self._execute=lambda *a, **b: self._cursor.execute(*a, **b) 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 ### register this instance of SQLDB 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
362 - def define_table(self,tablename,*fields,**args):
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
380 - def __call__(self,where=''):
381 return SQLSet(self,where)
382 - def commit(self):
383 self._connection.commit()
384 - def rollback(self):
385 self._connection.rollback()
386 - def executesql(self,query):
387 self['_lastsql']=query 388 self._execute(query) 389 return self._cursor.fetchall()
390
391 -class SQLALL:
392 - def __init__(self,table):
393 self.table=table
394 - def __str__(self):
395 s=['%s.%s'%(self.table._tablename,name) for name in self.table.fields] 396 return ', '.join(s) 397
398 -class SQLTable(SQLStorage):
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)
422 - def __str__(self):
423 return '<table %s>' % (self._tablename)
424 - def _create(self,migrate=True):
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: ### THIS IS OK 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 # 2 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 # 1 and 2 may have a problem with references in MySQL and Oracle, not sure 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()
526 - def create(self):
527 # nothing to do, here for backward compatility 528 pass
529 - def _drop(self):
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]
534 - def drop(self):
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')
546 - def _insert(self,**fields):
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)
570 - def insert(self,**fields):
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
592 - def import_from_csv_file(self,file):
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
608 -class SQLXorable:
609 - def __init__(self,name,type='string',db=None):
610 self.name,self.type,self._db=name,type,db
611 - def __str__(self):
612 return self.name
613 - def __or__(self,other): # for use in sortby
614 return SQLXorable(str(self)+', '+str(other),None,None)
615 - def __invert__(self):
616 return SQLXorable(str(self)+' DESC',None,None)
617 # for use in SQLQuery
618 - def __eq__(self,value): return SQLQuery(self,'==',value)
619 - def __ne__(self,value): return SQLQuery(self,'!=',value)
620 - def __lt__(self,value): return SQLQuery(self,'<',value)
621 - def __le__(self,value): return SQLQuery(self,'<=',value)
622 - def __gt__(self,value): return SQLQuery(self,'>',value)
623 - def __ge__(self,value): return SQLQuery(self,'>=',value)
624 - def like(self,value): return SQLQuery(self,'like',value)
625 - def belongs(self,other):
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 # for use in both SQLQuery and sortby
633 - def __add__(self,other): return SQLXorable(str(self)+'+'+str(other),'float',None)
634 - def __sub__(self,other): return SQLXorable(str(self)+'-'+str(other),'float',None)
635 - def __mul__(self,other): return SQLXorable(str(self)+'*'+str(other),'float',None)
636 - def __div__(self,other): return SQLXorable(str(self)+'/'+str(other),'float',None)
637
638 -class SQLField(SQLXorable):
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 # 'string', 'integer' 668 if type=='upload': length=64 669 self.length=length # the length of the string 670 self.default=default # default value for field 671 self.required=required # is this field required 672 self.ondelete=ondelete.upper() # this is for reference fields only 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 # list of validators
678 - def formatter(self,value):
679 if value is None or not self.requires: return value 680 if not isinstance(self.requires,(list,tuple)): requires=[self.requires] 681 else: requires=copy.copy(self.requires) 682 requires.reverse() 683 for item in requires: 684 if hasattr(item,'formatter'): value=item.formatter(value) 685 return value
686 - def lower(self):
687 s=self._db._translator["lower"] % dict(field=str(self)) 688 return SQLXorable(s,'string',self._db)
689 - def upper(self):
690 s=self._db._translator["upper"] % dict(field=str(self)) 691 return SQLXorable(s,'string',self._db)
692 - def year(self):
693 s=self._db._translator["extract"] % dict(name='year',field=str(self)) 694 return SQLXorable(s,'integer',self._db)
695 - def month(self):
696 s=self._db._translator["extract"] % dict(name='month',field=str(self)) 697 return SQLXorable(s,'integer',self._db)
698 - def day(self):
699 s=self._db._translator["extract"] % dict(name='day',field=str(self)) 700 return SQLXorable(s,'integer',self._db)
701 - def hour(self):
702 s=self._db._translator["extract"] % dict(name='hour',field=str(self)) 703 return SQLXorable(s,'integer',self._db)
704 - def minutes(self):
705 s=self._db._translator["extract"] % dict(name='minutes',field=str(self)) 706 return SQLXorable(s,'integer',self._db)
707 - def seconds(self):
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
712 -class SQLQuery:
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
724 - def __and__(self,other): return SQLQuery(self,'&',other)
725 - def __or__(self,other): return SQLQuery(self,'|',other)
726 - def __invert__(self): return SQLQuery(None,'~',self)
727 - def __str__(self):
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
753 -def parse_tablenames(text):
754 items=regex_tables.findall(re.sub("'[^']*'",'',text)) 755 tables={} 756 for item in items: tables[item]=True 757 return tables.keys()
758
759 -class SQLSet:
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 """
774 - def __init__(self,db,where=''):
775 self._db=db 776 self._tables=[] 777 # find out wchich tables are involved 778 self.sql_w=str(where) 779 #print self.sql_w 780 self._tables=parse_tablenames(self.sql_w)
781 #print self._tables
782 - def __call__(self,where):
783 return SQLSet(self._db,SQLQuery(self.sql_w)&where)
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 ### if not fields specified take them all from the requested tables 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)
825 - def _delete(self):
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)
832 - def delete(self):
833 query=self._delete() 834 self._db['_lastsql']=query 835 self._db._execute(query)
836 - def _update(self,**fields):
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)
846 - def update(self,**fields):
847 query=self._update(**fields) 848 self._db['_lastsql']=query 849 self._db._execute(query)
850
851 -def update_record(t,s,a):
852 s.update(**a) 853 for key,value in a.items(): t[str(key)]=value
854
855 -class SQLRows:
856 ### this class still needs some work to care for ID/OID 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
865 - def __len__(self):
866 return len(self.response)
867 - def __getitem__(self,i):
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 #row[tablename][fieldname]=SQLSet(self._db[referee].id==rid) 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
919 - def __iter__(self):
920 """ 921 iterator over records 922 """ 923 for i in range(len(self)): 924 yield self[i]
925 - def __str__(self):
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()
939 - def xml(self):
940 """ 941 serializes the table using sqlhtml.SQLTABLE (if present) 942 """ 943 import sqlhtml 944 return sqlhtml.SQLTABLE(self).xml()
945
946 -def test_all():
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