web2py.gluon.dal

Source Code for Module web2py.gluon.dal

1 #!/bin/env python 2 # -*- coding: utf-8 -*- 3 4 """ 5 This file is part of the web2py Web Framework 6 Copyrighted by Massimo Di Pierro <mdipierro@cs.depaul.edu> 7 License: LGPLv3 (http://www.gnu.org/licenses/lgpl.html) 8 9 Thanks to 10 * Niall Sweeny <niall.sweeny@fonjax.com> for MS SQL support 11 * Marcel Leuthi <mluethi@mlsystems.ch> for Oracle support 12 * Denes 13 * Chris Clark 14 * clach05 15 * Denes Lengyel 16 * and many others who have contributed to current and previous versions 17 18 This file contains the DAL support for many relational databases, 19 including: 20 - SQLite & SpatiaLite 21 - MySQL 22 - Postgres 23 - Firebird 24 - Oracle 25 - MS SQL 26 - DB2 27 - Interbase 28 - Ingres 29 - Informix (9+ and SE) 30 - SapDB (experimental) 31 - Cubrid (experimental) 32 - CouchDB (experimental) 33 - MongoDB (in progress) 34 - Google:nosql 35 - Google:sql 36 - Teradata 37 - IMAP (experimental) 38 39 Example of usage: 40 41 >>> # from dal import DAL, Field 42 43 ### create DAL connection (and create DB if it doesn't exist) 44 >>> db = DAL(('sqlite://storage.sqlite','mysql://a:b@localhost/x'), 45 ... folder=None) 46 47 ### define a table 'person' (create/alter as necessary) 48 >>> person = db.define_table('person',Field('name','string')) 49 50 ### insert a record 51 >>> id = person.insert(name='James') 52 53 ### retrieve it by id 54 >>> james = person(id) 55 56 ### retrieve it by name 57 >>> james = person(name='James') 58 59 ### retrieve it by arbitrary query 60 >>> query = (person.name=='James') & (person.name.startswith('J')) 61 >>> james = db(query).select(person.ALL)[0] 62 63 ### update one record 64 >>> james.update_record(name='Jim') 65 <Row {'id': 1, 'name': 'Jim'}> 66 67 ### update multiple records by query 68 >>> db(person.name.like('J%')).update(name='James') 69 1 70 71 ### delete records by query 72 >>> db(person.name.lower() == 'jim').delete() 73 0 74 75 ### retrieve multiple records (rows) 76 >>> people = db(person).select(orderby=person.name, 77 ... groupby=person.name, limitby=(0,100)) 78 79 ### further filter them 80 >>> james = people.find(lambda row: row.name == 'James').first() 81 >>> print james.id, james.name 82 1 James 83 84 ### check aggregates 85 >>> counter = person.id.count() 86 >>> print db(person).select(counter).first()(counter) 87 1 88 89 ### delete one record 90 >>> james.delete_record() 91 1 92 93 ### delete (drop) entire database table 94 >>> person.drop() 95 96 Supported field types: 97 id string text boolean integer double decimal password upload 98 blob time date datetime 99 100 Supported DAL URI strings: 101 'sqlite://test.db' 102 'spatialite://test.db' 103 'sqlite:memory' 104 'spatialite:memory' 105 'jdbc:sqlite://test.db' 106 'mysql://root:none@localhost/test' 107 'postgres://mdipierro:password@localhost/test' 108 'postgres:psycopg2://mdipierro:password@localhost/test' 109 'postgres:pg8000://mdipierro:password@localhost/test' 110 'jdbc:postgres://mdipierro:none@localhost/test' 111 'mssql://web2py:none@A64X2/web2py_test' 112 'mssql2://web2py:none@A64X2/web2py_test' # alternate mappings 113 'oracle://username:password@database' 114 'firebird://user:password@server:3050/database' 115 'db2://DSN=dsn;UID=user;PWD=pass' 116 'firebird://username:password@hostname/database' 117 'firebird_embedded://username:password@c://path' 118 'informix://user:password@server:3050/database' 119 'informixu://user:password@server:3050/database' # unicode informix 120 'google:datastore' # for google app engine datastore 121 'google:sql' # for google app engine with sql (mysql compatible) 122 'teradata://DSN=dsn;UID=user;PWD=pass; DATABASE=database' # experimental 123 'imap://user:password@server:port' # experimental 124 125 For more info: 126 help(DAL) 127 help(Field) 128 """ 129 130 ################################################################################### 131 # this file only exposes DAL and Field 132 ################################################################################### 133 134 __all__ = ['DAL', 'Field'] 135 136 MAXCHARLENGTH = 2**15 # not quite but reasonable default max char length 137 DEFAULTLENGTH = {'string':512, 138 'password':512, 139 'upload':512, 140 'text':2**15, 141 'blob':2**31} 142 TIMINGSSIZE = 100 143 SPATIALLIBS = { 144 'Windows':'libspatialite', 145 'Linux':'libspatialite.so', 146 'Darwin':'libspatialite.dylib' 147 } 148 149 import re 150 import sys 151 import locale 152 import os 153 import types 154 import datetime 155 import threading 156 import time 157 import csv 158 import cgi 159 import copy 160 import socket 161 import logging 162 import base64 163 import shutil 164 import marshal 165 import decimal 166 import struct 167 import urllib 168 import hashlib 169 import uuid 170 import glob 171 import traceback 172 import platform 173 174 PYTHON_VERSION = sys.version_info[0] 175 if PYTHON_VERSION == 2: 176 import cPickle as pickle 177 import cStringIO as StringIO 178 import copy_reg as copyreg 179 hashlib_md5 = hashlib.md5 180 bytes, unicode = str, unicode 181 else: 182 import pickle 183 from io import StringIO as StringIO 184 import copyreg 185 long = int 186 hashlib_md5 = lambda s: hashlib.md5(bytes(s,'utf8')) 187 bytes, unicode = bytes, str 188 189 CALLABLETYPES = (types.LambdaType, types.FunctionType, 190 types.BuiltinFunctionType, 191 types.MethodType, types.BuiltinMethodType) 192 193 TABLE_ARGS = set( 194 ('migrate','primarykey','fake_migrate','format','redefine', 195 'singular','plural','trigger_name','sequence_name', 196 'common_filter','polymodel','table_class','on_define',)) 197 198 SELECT_ARGS = set( 199 ('orderby', 'groupby', 'limitby','required', 'cache', 'left', 200 'distinct', 'having', 'join','for_update', 'processor','cacheable')) 201 202 ogetattr = object.__getattribute__ 203 osetattr = object.__setattr__ 204 exists = os.path.exists 205 pjoin = os.path.join 206 207 ################################################################################### 208 # following checks allow the use of dal without web2py, as a standalone module 209 ################################################################################### 210 try: 211 from utils import web2py_uuid 212 except (ImportError, SystemError): 213 import uuid

214 - def web2py_uuid(): return str(uuid.uuid4())

215 216 try: 217 import portalocker 218 have_portalocker = True 219 except ImportError: 220 have_portalocker = False 221 222 try: 223 import serializers 224 have_serializers = True 225 except ImportError: 226 have_serializers = False 227 228 try: 229 import validators 230 have_validators = True 231 except (ImportError, SyntaxError): 232 have_validators = False 233 234 LOGGER = logging.getLogger("web2py.dal") 235 DEFAULT = lambda:0 236 237 GLOBAL_LOCKER = threading.RLock() 238 THREAD_LOCAL = threading.local() 239 240 # internal representation of tables with field 241 # <table>.<field>, tables and fields may only be [a-zA-Z0-9_] 242 243 REGEX_TYPE = re.compile('^([\w\_\:]+)') 244 REGEX_DBNAME = re.compile('^(\w+)(\:\w+)*') 245 REGEX_W = re.compile('^\w+$') 246 REGEX_TABLE_DOT_FIELD = re.compile('^(\w+)\.(\w+)$') 247 REGEX_UPLOAD_PATTERN = re.compile('(?P<table>[\w\-]+)\.(?P<field>[\w\-]+)\.(?P<uuidkey>[\w\-]+)\.(?P<name>\w+)\.\w+$') 248 REGEX_CLEANUP_FN = re.compile('[\'"\s;]+') 249 REGEX_UNPACK = re.compile('(?<!\|)\|(?!\|)') 250 REGEX_PYTHON_KEYWORDS = re.compile('^(and|del|from|not|while|as|elif|global|or|with|assert|else|if|pass|yield|break|except|import|print|class|exec|in|raise|continue|finally|is|return|def|for|lambda|try)$') 251 REGEX_SELECT_AS_PARSER = re.compile("\s+AS\s+(\S+)") 252 REGEX_CONST_STRING = re.compile('(\"[^\"]*?\")|(\'[^\']*?\')') 253 REGEX_SEARCH_PATTERN = re.compile('^{[^\.]+\.[^\.]+(\.(lt|gt|le|ge|eq|ne|contains|startswith|year|month|day|hour|minute|second))?(\.not)?}$') 254 REGEX_SQUARE_BRACKETS = re.compile('^.+\[.+\]$') 255 REGEX_STORE_PATTERN = re.compile('\.(?P<e>\w{1,5})$') 256 REGEX_QUOTES = re.compile("'[^']*'") 257 REGEX_ALPHANUMERIC = re.compile('^[0-9a-zA-Z]\w*$') 258 REGEX_PASSWORD = re.compile('\://([^:@]*)\:') 259 REGEX_NOPASSWD = re.compile('(?<=\:)([^:@/]+)(?=@.+)') 260 261 # list of drivers will be built on the fly 262 # and lists only what is available 263 DRIVERS = [] 264 265 try: 266 from new import classobj 267 from google.appengine.ext import db as gae 268 from google.appengine.api import namespace_manager, rdbms 269 from google.appengine.api.datastore_types import Key ### for belongs on ID 270 from google.appengine.ext.db.polymodel import PolyModel 271 DRIVERS.append('google') 272 except ImportError: 273 pass 274 275 if not 'google' in DRIVERS: 276 277 try: 278 from pysqlite2 import dbapi2 as sqlite2 279 DRIVERS.append('SQLite(sqlite2)') 280 except ImportError: 281 LOGGER.debug('no SQLite drivers pysqlite2.dbapi2') 282 283 try: 284 from sqlite3 import dbapi2 as sqlite3 285 DRIVERS.append('SQLite(sqlite3)') 286 except ImportError: 287 LOGGER.debug('no SQLite drivers sqlite3') 288 289 try: 290 # first try contrib driver, then from site-packages (if installed) 291 try: 292 import contrib.pymysql as pymysql 293 # monkeypatch pymysql because they havent fixed the bug: 294 # https://github.com/petehunt/PyMySQL/issues/86 295 pymysql.ESCAPE_REGEX = re.compile("'") 296 pymysql.ESCAPE_MAP = {"'": "''"} 297 # end monkeypatch 298 except ImportError: 299 import pymysql 300 DRIVERS.append('MySQL(pymysql)') 301 except ImportError: 302 LOGGER.debug('no MySQL driver pymysql') 303 304 try: 305 import MySQLdb 306 DRIVERS.append('MySQL(MySQLdb)') 307 except ImportError: 308 LOGGER.debug('no MySQL driver MySQLDB') 309 310 311 try: 312 import psycopg2 313 from psycopg2.extensions import adapt as psycopg2_adapt 314 DRIVERS.append('PostgreSQL(psycopg2)') 315 except ImportError: 316 LOGGER.debug('no PostgreSQL driver psycopg2') 317 318 try: 319 # first try contrib driver, then from site-packages (if installed) 320 try: 321 import contrib.pg8000.dbapi as pg8000 322 except ImportError: 323 import pg8000.dbapi as pg8000 324 DRIVERS.append('PostgreSQL(pg8000)') 325 except ImportError: 326 LOGGER.debug('no PostgreSQL driver pg8000') 327 328 try: 329 import cx_Oracle 330 DRIVERS.append('Oracle(cx_Oracle)') 331 except ImportError: 332 LOGGER.debug('no Oracle driver cx_Oracle') 333 334 try: 335 import pyodbc 336 DRIVERS.append('MSSQL(pyodbc)') 337 DRIVERS.append('DB2(pyodbc)') 338 DRIVERS.append('Teradata(pyodbc)') 339 except ImportError: 340 LOGGER.debug('no MSSQL/DB2/Teradata driver pyodbc') 341 342 try: 343 import Sybase 344 DRIVERS.append('Sybase(Sybase)') 345 except ImportError: 346 LOGGER.debug('no Sybase driver') 347 348 try: 349 import kinterbasdb 350 DRIVERS.append('Interbase(kinterbasdb)') 351 DRIVERS.append('Firebird(kinterbasdb)') 352 except ImportError: 353 LOGGER.debug('no Firebird/Interbase driver kinterbasdb') 354 355 try: 356 import fdb 357 DRIVERS.append('Firbird(fdb)') 358 except ImportError: 359 LOGGER.debug('no Firebird driver fdb') 360 ##### 361 try: 362 import firebirdsql 363 DRIVERS.append('Firebird(firebirdsql)') 364 except ImportError: 365 LOGGER.debug('no Firebird driver firebirdsql') 366 367 try: 368 import informixdb 369 DRIVERS.append('Informix(informixdb)') 370 LOGGER.warning('Informix support is experimental') 371 except ImportError: 372 LOGGER.debug('no Informix driver informixdb') 373 374 try: 375 import sapdb 376 DRIVERS.append('SQL(sapdb)') 377 LOGGER.warning('SAPDB support is experimental') 378 except ImportError: 379 LOGGER.debug('no SAP driver sapdb') 380 381 try: 382 import cubriddb 383 DRIVERS.append('Cubrid(cubriddb)') 384 LOGGER.warning('Cubrid support is experimental') 385 except ImportError: 386 LOGGER.debug('no Cubrid driver cubriddb') 387 388 try: 389 from com.ziclix.python.sql import zxJDBC 390 import java.sql 391 # Try sqlite jdbc driver from http://www.zentus.com/sqlitejdbc/ 392 from org.sqlite import JDBC # required by java.sql; ensure we have it 393 zxJDBC_sqlite = java.sql.DriverManager 394 DRIVERS.append('PostgreSQL(zxJDBC)') 395 DRIVERS.append('SQLite(zxJDBC)') 396 LOGGER.warning('zxJDBC support is experimental') 397 is_jdbc = True 398 except ImportError: 399 LOGGER.debug('no SQLite/PostgreSQL driver zxJDBC') 400 is_jdbc = False 401 402 try: 403 import ingresdbi 404 DRIVERS.append('Ingres(ingresdbi)') 405 except ImportError: 406 LOGGER.debug('no Ingres driver ingresdbi') 407 # NOTE could try JDBC....... 408 409 try: 410 import couchdb 411 DRIVERS.append('CouchDB(couchdb)') 412 except ImportError: 413 LOGGER.debug('no Couchdb driver couchdb') 414 415 try: 416 import pymongo 417 DRIVERS.append('MongoDB(pymongo)') 418 except: 419 LOGGER.debug('no MongoDB driver pymongo') 420 421 try: 422 import imaplib 423 DRIVERS.append('IMAP(imaplib)') 424 except: 425 LOGGER.debug('no IMAP driver imaplib') 426 427 PLURALIZE_RULES = [ 428 (re.compile('child$'), re.compile('child$'), 'children'), 429 (re.compile('oot$'), re.compile('oot$'), 'eet'), 430 (re.compile('ooth$'), re.compile('ooth$'), 'eeth'), 431 (re.compile('l[eo]af$'), re.compile('l([eo])af$'), 'l\\1aves'), 432 (re.compile('sis$'), re.compile('sis$'), 'ses'), 433 (re.compile('man$'), re.compile('man$'), 'men'), 434 (re.compile('ife$'), re.compile('ife$'), 'ives'), 435 (re.compile('eau$'), re.compile('eau$'), 'eaux'), 436 (re.compile('lf$'), re.compile('lf$'), 'lves'), 437 (re.compile('[sxz]$'), re.compile('$'), 'es'), 438 (re.compile('[^aeioudgkprt]h$'), re.compile('$'), 'es'), 439 (re.compile('(qu|[^aeiou])y$'), re.compile('y$'), 'ies'), 440 (re.compile('$'), re.compile('$'), 's'), 441 ] 442

443 -def pluralize(singular, rules=PLURALIZE_RULES):

444 for line in rules: 445 re_search, re_sub, replace = line 446 plural = re_search.search(singular) and re_sub.sub(replace, singular) 447 if plural: return plural

448

449 -def hide_password(uri):

450 return REGEX_PASSWORD.sub('://******:',uri)

451

452 -def OR(a,b):

453 return a|b

454

455 -def AND(a,b):

456 return a&b

457

458 -def IDENTITY(x): return x

459

460 -def varquote_aux(name,quotestr='%s'):

461 return name if REGEX_W.match(name) else quotestr % name

462 463 if 'google' in DRIVERS: 464 465 is_jdbc = False 466

467 - class GAEDecimalProperty(gae.Property):

468 """ 469 GAE decimal implementation 470 """ 471 data_type = decimal.Decimal 472

473 - def __init__(self, precision, scale, **kwargs):

474 super(GAEDecimalProperty, self).__init__(self, **kwargs) 475 d = '1.' 476 for x in range(scale): 477 d += '0' 478 self.round = decimal.Decimal(d)

479

480 - def get_value_for_datastore(self, model_instance):

481 value = super(GAEDecimalProperty, self)\ 482 .get_value_for_datastore(model_instance) 483 if value is None or value == '': 484 return None 485 else: 486 return str(value)

487

488 - def make_value_from_datastore(self, value):

489 if value is None or value == '': 490 return None 491 else: 492 return decimal.Decimal(value).quantize(self.round)

493

494 - def validate(self, value):

495 value = super(GAEDecimalProperty, self).validate(value) 496 if value is None or isinstance(value, decimal.Decimal): 497 return value 498 elif isinstance(value, basestring): 499 return decimal.Decimal(value) 500 raise gae.BadValueError("Property %s must be a Decimal or string."\ 501 % self.name)

502 503 ################################################################################### 504 # class that handles connection pooling (all adapters are derived from this one) 505 ################################################################################### 506

507 -class ConnectionPool(object):

508 509 POOLS = {} 510 check_active_connection = True 511 512 @staticmethod

513 - def set_folder(folder):

514 THREAD_LOCAL.folder = folder

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

518 - def close(self,action='commit',really=True):

519 if action: 520 if callable(action): 521 action(self) 522 else: 523 getattr(self, action)() 524 # ## if you want pools, recycle this connection 525 if self.pool_size: 526 GLOBAL_LOCKER.acquire() 527 pool = ConnectionPool.POOLS[self.uri] 528 if len(pool) < self.pool_size: 529 pool.append(self.connection) 530 really = False 531 GLOBAL_LOCKER.release() 532 if really: 533 self.close_connection() 534 self.connection = None

535 536 @staticmethod

537 - def close_all_instances(action):

538 """ to close cleanly databases in a multithreaded environment """ 539 dbs = getattr(THREAD_LOCAL,'db_instances',{}).items() 540 for db_uid, db_group in dbs: 541 for db in db_group: 542 if hasattr(db,'_adapter'): 543 db._adapter.close(action) 544 getattr(THREAD_LOCAL,'db_instances',{}).clear() 545 getattr(THREAD_LOCAL,'db_instances_zombie',{}).clear() 546 if callable(action): 547 action(None) 548 return

549

550 - def find_or_make_work_folder(self):

551 """ this actually does not make the folder. it has to be there """ 552 self.folder = getattr(THREAD_LOCAL,'folder','') 553 554 # Creating the folder if it does not exist 555 if False and self.folder and not exists(self.folder): 556 os.mkdir(self.folder)

557

558 - def after_connection(self):

559 """ this it is suppoed to be overloaded by adtapters""" 560 pass

561

562 - def reconnect(self, f=None, cursor=True):

563 """ 564 this function defines: self.connection and self.cursor 565 (iff cursor is True) 566 if self.pool_size>0 it will try pull the connection from the pool 567 if the connection is not active (closed by db server) it will loop 568 if not self.pool_size or no active connections in pool makes a new one 569 """ 570 if getattr(self,'connection',None) != None: 571 return 572 if f is None: 573 f = self.connector 574 575 if not self.pool_size: 576 self.connection = f() 577 self.cursor = cursor and self.connection.cursor() 578 else: 579 uri = self.uri 580 POOLS = ConnectionPool.POOLS 581 while True: 582 GLOBAL_LOCKER.acquire() 583 if not uri in POOLS: 584 POOLS[uri] = [] 585 if POOLS[uri]: 586 self.connection = POOLS[uri].pop() 587 GLOBAL_LOCKER.release() 588 self.cursor = cursor and self.connection.cursor() 589 try: 590 if self.cursor and self.check_active_connection: 591 self.execute('SELECT 1;') 592 break 593 except: 594 pass 595 else: 596 GLOBAL_LOCKER.release() 597 self.connection = f() 598 self.cursor = cursor and self.connection.cursor() 599 break 600 self.after_connection()

601 602 603 ################################################################################### 604 # this is a generic adapter that does nothing; all others are derived from this one 605 ################################################################################### 606

607 -class BaseAdapter(ConnectionPool):

608 driver = None 609 driver_name = None 610 drivers = () # list of drivers from which to pick 611 connection = None 612 maxcharlength = MAXCHARLENGTH 613 commit_on_alter_table = False 614 support_distributed_transaction = False 615 uploads_in_blob = False 616 can_select_for_update = True 617 618 TRUE = 'T' 619 FALSE = 'F' 620 types = { 621 'boolean': 'CHAR(1)', 622 'string': 'CHAR(%(length)s)', 623 'text': 'TEXT', 624 'password': 'CHAR(%(length)s)', 625 'blob': 'BLOB', 626 'upload': 'CHAR(%(length)s)', 627 'integer': 'INTEGER', 628 'bigint': 'INTEGER', 629 'float':'DOUBLE', 630 'double': 'DOUBLE', 631 'decimal': 'DOUBLE', 632 'date': 'DATE', 633 'time': 'TIME', 634 'datetime': 'TIMESTAMP', 635 'id': 'INTEGER PRIMARY KEY AUTOINCREMENT', 636 'reference': 'INTEGER REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s', 637 'list:integer': 'TEXT', 638 'list:string': 'TEXT', 639 'list:reference': 'TEXT', 640 # the two below are only used when DAL(...bigint_id=True) and replace 'id','reference' 641 'big-id': 'BIGINT PRIMARY KEY AUTOINCREMENT', 642 'big-reference': 'BIGINT REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s', 643 } 644

645 - def id_query(self, table):

646 return table._id != None

647

648 - def adapt(self, obj):

649 return "'%s'" % obj.replace("'", "''")

650

651 - def smart_adapt(self, obj):

652 if isinstance(obj,(int,float)): 653 return str(obj) 654 return self.adapt(str(obj))

655

656 - def integrity_error(self):

657 return self.driver.IntegrityError

658

659 - def operational_error(self):

660 return self.driver.OperationalError

661

662 - def file_exists(self, filename):

663 """ 664 to be used ONLY for files that on GAE may not be on filesystem 665 """ 666 return exists(filename)

667

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

669 """ 670 to be used ONLY for files that on GAE may not be on filesystem 671 """ 672 if have_portalocker and lock: 673 fileobj = portalocker.LockedFile(filename,mode) 674 else: 675 fileobj = open(filename,mode) 676 return fileobj

677

678 - def file_close(self, fileobj):

679 """ 680 to be used ONLY for files that on GAE may not be on filesystem 681 """ 682 if fileobj: 683 fileobj.close()

684

685 - def file_delete(self, filename):

686 os.unlink(filename)

687

688 - def find_driver(self,adapter_args,uri=None):

689 if getattr(self,'driver',None) != None: 690 return 691 drivers_available = [driver for driver in self.drivers 692 if driver in globals()] 693 if uri: 694 items = uri.split('://',1)[0].split(':') 695 request_driver = items[1] if len(items)>1 else None 696 else: 697 request_driver = None 698 request_driver = request_driver or adapter_args.get('driver') 699 if request_driver: 700 if request_driver in drivers_available: 701 self.driver_name = request_driver 702 self.driver = globals().get(request_driver) 703 else: 704 raise RuntimeError("driver %s not available" % request_driver) 705 elif drivers_available: 706 self.driver_name = drivers_available[0] 707 self.driver = globals().get(self.driver_name) 708 else: 709 raise RuntimeError("no driver available %s" % str(self.drivers))

710 711

712 - def __init__(self, db,uri,pool_size=0, folder=None, db_codec='UTF-8', 713 credential_decoder=IDENTITY, driver_args={}, 714 adapter_args={},do_connect=True):

715 self.db = db 716 self.dbengine = "None" 717 self.uri = uri 718 self.pool_size = pool_size 719 self.folder = folder 720 self.db_codec = db_codec 721 class Dummy(object): 722 lastrowid = 1 723 def __getattr__(self, value): 724 return lambda *a, **b: []

725 self.connection = Dummy() 726 self.cursor = Dummy() 727

728 - def sequence_name(self,tablename):

729 return '%s_sequence' % tablename

730

731 - def trigger_name(self,tablename):

732 return '%s_sequence' % tablename

733

734 - def varquote(self,name):

735 return name

736

737 - def create_table(self, table, 738 migrate=True, 739 fake_migrate=False, 740 polymodel=None):

741 db = table._db 742 fields = [] 743 # PostGIS geo fields are added after the table has been created 744 postcreation_fields = [] 745 sql_fields = {} 746 sql_fields_aux = {} 747 TFK = {} 748 tablename = table._tablename 749 sortable = 0 750 types = self.types 751 for field in table: 752 sortable += 1 753 field_name = field.name 754 field_type = field.type 755 if isinstance(field_type,SQLCustomType): 756 ftype = field_type.native or field_type.type 757 elif field_type.startswith('reference'): 758 referenced = field_type[10:].strip() 759 if referenced == '.': 760 referenced = tablename 761 constraint_name = self.constraint_name(tablename, field_name) 762 if not '.' in referenced \ 763 and referenced != tablename \ 764 and hasattr(table,'_primarykey'): 765 ftype = types['integer'] 766 else: 767 if hasattr(table,'_primarykey'): 768 rtablename,rfieldname = referenced.split('.') 769 rtable = db[rtablename] 770 rfield = rtable[rfieldname] 771 # must be PK reference or unique 772 if rfieldname in hasattr(rtable,'_primarykey') or \ 773 rfield.unique: 774 ftype = types[rfield.type[:9]] % \ 775 dict(length=rfield.length) 776 # multicolumn primary key reference? 777 if not rfield.unique and len(rtable._primarykey)>1: 778 # then it has to be a table level FK 779 if rtablename not in TFK: 780 TFK[rtablename] = {} 781 TFK[rtablename][rfieldname] = field_name 782 else: 783 ftype = ftype + \ 784 types['reference FK'] % dict( 785 constraint_name = constraint_name, # should be quoted 786 foreign_key = '%s (%s)' % (rtablename, 787 rfieldname), 788 on_delete_action=field.ondelete) 789 else: 790 # make a guess here for circular references 791 if referenced in db: 792 id_fieldname = db[referenced]._id.name 793 elif referenced == tablename: 794 id_fieldname = table._id.name 795 else: #make a guess 796 id_fieldname = 'id' 797 ftype = types[field_type[:9]] % dict( 798 index_name = field_name+'__idx', 799 field_name = field_name, 800 constraint_name = constraint_name, 801 foreign_key = '%s (%s)' % (referenced, 802 id_fieldname), 803 on_delete_action=field.ondelete) 804 elif field_type.startswith('list:reference'): 805 ftype = types[field_type[:14]] 806 elif field_type.startswith('decimal'): 807 precision, scale = map(int,field_type[8:-1].split(',')) 808 ftype = types[field_type[:7]] % \ 809 dict(precision=precision,scale=scale) 810 elif field_type.startswith('geo'): 811 srid = self.srid 812 geotype, parms = field_type[:-1].split('(') 813 if not geotype in types: 814 raise SyntaxError( 815 'Field: unknown field type: %s for %s' \ 816 % (field_type, field_name)) 817 ftype = types[geotype] 818 if self.dbengine == 'postgres' and geotype == 'geometry': 819 # parameters: schema, srid, dimension 820 dimension = 2 # GIS.dimension ??? 821 parms = parms.split(',') 822 if len(parms) == 3: 823 schema, srid, dimension = parms 824 elif len(parms) == 2: 825 schema, srid = parms 826 else: 827 schema = parms[0] 828 ftype = "SELECT AddGeometryColumn ('%%(schema)s', '%%(tablename)s', '%%(fieldname)s', %%(srid)s, '%s', %%(dimension)s);" % types[geotype] 829 ftype = ftype % dict(schema=schema, 830 tablename=tablename, 831 fieldname=field_name, srid=srid, 832 dimension=dimension) 833 postcreation_fields.append(ftype) 834 elif not field_type in types: 835 raise SyntaxError('Field: unknown field type: %s for %s' % \ 836 (field_type, field_name)) 837 else: 838 ftype = types[field_type]\ 839 % dict(length=field.length) 840 if not field_type.startswith('id') and \ 841 not field_type.startswith('reference'): 842 if field.notnull: 843 ftype += ' NOT NULL' 844 else: 845 ftype += self.ALLOW_NULL() 846 if field.unique: 847 ftype += ' UNIQUE' 848 if field.custom_qualifier: 849 ftype += ' %s' % field.custom_qualifier 850 851 # add to list of fields 852 sql_fields[field_name] = dict( 853 length=field.length, 854 unique=field.unique, 855 notnull=field.notnull, 856 sortable=sortable, 857 type=str(field_type), 858 sql=ftype) 859 860 if isinstance(field.default,(str,int,float)): 861 # Caveat: sql_fields and sql_fields_aux 862 # differ for default values. 863 # sql_fields is used to trigger migrations and sql_fields_aux 864 # is used for create tables. 865 # The reason is that we do not want to trigger 866 # a migration simply because a default value changes. 867 not_null = self.NOT_NULL(field.default, field_type) 868 ftype = ftype.replace('NOT NULL', not_null) 869 sql_fields_aux[field_name] = dict(sql=ftype) 870 # Postgres - PostGIS: 871 # geometry fields are added after the table has been created, not now 872 if not (self.dbengine == 'postgres' and \ 873 field_type.startswith('geom')): 874 fields.append('%s %s' % (field_name, ftype)) 875 other = ';' 876 877 # backend-specific extensions to fields 878 if self.dbengine == 'mysql': 879 if not hasattr(table, "_primarykey"): 880 fields.append('PRIMARY KEY(%s)' % table._id.name) 881 other = ' ENGINE=InnoDB CHARACTER SET utf8;' 882 883 fields = ',\n '.join(fields) 884 for rtablename in TFK: 885 rfields = TFK[rtablename] 886 pkeys = db[rtablename]._primarykey 887 fkeys = [ rfields[k] for k in pkeys ] 888 fields = fields + ',\n ' + \ 889 types['reference TFK'] % dict( 890 table_name = tablename, 891 field_name=', '.join(fkeys), 892 foreign_table = rtablename, 893 foreign_key = ', '.join(pkeys), 894 on_delete_action = field.ondelete) 895 896 if hasattr(table,'_primarykey'): 897 query = "CREATE TABLE %s(\n %s,\n %s) %s" % \ 898 (tablename, fields, 899 self.PRIMARY_KEY(', '.join(table._primarykey)),other) 900 else: 901 query = "CREATE TABLE %s(\n %s\n)%s" % \ 902 (tablename, fields, other) 903 904 if self.uri.startswith('sqlite:///') \ 905 or self.uri.startswith('spatialite:///'): 906 path_encoding = sys.getfilesystemencoding() \ 907 or locale.getdefaultlocale()[1] or 'utf8' 908 dbpath = self.uri[9:self.uri.rfind('/')]\ 909 .decode('utf8').encode(path_encoding) 910 else: 911 dbpath = self.folder 912 913 if not migrate: 914 return query 915 elif self.uri.startswith('sqlite:memory')\ 916 or self.uri.startswith('spatialite:memory'): 917 table._dbt = None 918 elif isinstance(migrate, str): 919 table._dbt = pjoin(dbpath, migrate) 920 else: 921 table._dbt = pjoin( 922 dbpath, '%s_%s.table' % (table._db._uri_hash, tablename)) 923 924 if table._dbt: 925 table._loggername = pjoin(dbpath, 'sql.log') 926 logfile = self.file_open(table._loggername, 'a') 927 else: 928 logfile = None 929 if not table._dbt or not self.file_exists(table._dbt): 930 if table._dbt: 931 logfile.write('timestamp: %s\n' 932 % datetime.datetime.today().isoformat()) 933 logfile.write(query + '\n') 934 if not fake_migrate: 935 self.create_sequence_and_triggers(query,table) 936 table._db.commit() 937 # Postgres geom fields are added now, 938 # after the table has been created 939 for query in postcreation_fields: 940 self.execute(query) 941 table._db.commit() 942 if table._dbt: 943 tfile = self.file_open(table._dbt, 'w') 944 pickle.dump(sql_fields, tfile) 945 self.file_close(tfile) 946 if fake_migrate: 947 logfile.write('faked!\n') 948 else: 949 logfile.write('success!\n') 950 else: 951 tfile = self.file_open(table._dbt, 'r') 952 try: 953 sql_fields_old = pickle.load(tfile) 954 except EOFError: 955 self.file_close(tfile) 956 self.file_close(logfile) 957 raise RuntimeError('File %s appears corrupted' % table._dbt) 958 self.file_close(tfile) 959 if sql_fields != sql_fields_old: 960 self.migrate_table(table, 961 sql_fields, sql_fields_old, 962 sql_fields_aux, logfile, 963 fake_migrate=fake_migrate) 964 self.file_close(logfile) 965 return query

966

967 - def migrate_table( 968 self, 969 table, 970 sql_fields, 971 sql_fields_old, 972 sql_fields_aux, 973 logfile, 974 fake_migrate=False, 975 ):

976 db = table._db 977 db._migrated.append(table._tablename) 978 tablename = table._tablename 979 def fix(item): 980 k,v=item 981 if not isinstance(v,dict): 982 v=dict(type='unkown',sql=v) 983 return k.lower(),v

984 # make sure all field names are lower case to avoid 985 # migrations because of case cahnge 986 sql_fields = dict(map(fix,sql_fields.iteritems())) 987 sql_fields_old = dict(map(fix,sql_fields_old.iteritems())) 988 sql_fields_aux = dict(map(fix,sql_fields_aux.iteritems())) 989 if db._debug: 990 logging.debug('migrating %s to %s' % (sql_fields_old,sql_fields)) 991 992 keys = sql_fields.keys() 993 for key in sql_fields_old: 994 if not key in keys: 995 keys.append(key) 996 if self.dbengine == 'mssql': 997 new_add = '; ALTER TABLE %s ADD ' % tablename 998 else: 999 new_add = ', ADD ' 1000 1001 metadata_change = False 1002 sql_fields_current = copy.copy(sql_fields_old) 1003 for key in keys: 1004 query = None 1005 if not key in sql_fields_old: 1006 sql_fields_current[key] = sql_fields[key] 1007 if self.dbengine in ('postgres',) and \ 1008 sql_fields[key]['type'].startswith('geometry'): 1009 # 'sql' == ftype in sql 1010 query = [ sql_fields[key]['sql'] ] 1011 else: 1012 query = ['ALTER TABLE %s ADD %s %s;' % \ 1013 (tablename, key, 1014 sql_fields_aux[key]['sql'].replace(', ', new_add))] 1015 metadata_change = True 1016 elif self.dbengine in ('sqlite', 'spatialite'): 1017 if key in sql_fields: 1018 sql_fields_current[key] = sql_fields[key] 1019 metadata_change = True 1020 elif not key in sql_fields: 1021 del sql_fields_current[key] 1022 ftype = sql_fields_old[key]['type'] 1023 if self.dbengine in ('postgres',) \ 1024 and ftype.startswith('geometry'): 1025 geotype, parms = ftype[:-1].split('(') 1026 schema = parms.split(',')[0] 1027 query = [ "SELECT DropGeometryColumn ('%(schema)s', '%(table)s', '%(field)s');" % dict(schema=schema, table=tablename, field=key,) ] 1028 elif not self.dbengine in ('firebird',): 1029 query = ['ALTER TABLE %s DROP COLUMN %s;' 1030 % (tablename, key)] 1031 else: 1032 query = ['ALTER TABLE %s DROP %s;' % (tablename, key)] 1033 metadata_change = True 1034 elif sql_fields[key]['sql'] != sql_fields_old[key]['sql'] \ 1035 and not (key in table.fields and 1036 isinstance(table[key].type, SQLCustomType)) \ 1037 and not sql_fields[key]['type'].startswith('reference')\ 1038 and not sql_fields[key]['type'].startswith('double')\ 1039 and not sql_fields[key]['type'].startswith('id'): 1040 sql_fields_current[key] = sql_fields[key] 1041 t = tablename 1042 tt = sql_fields_aux[key]['sql'].replace(', ', new_add) 1043 if not self.dbengine in ('firebird',): 1044 query = ['ALTER TABLE %s ADD %s__tmp %s;' % (t, key, tt), 1045 'UPDATE %s SET %s__tmp=%s;' % (t, key, key), 1046 'ALTER TABLE %s DROP COLUMN %s;' % (t, key), 1047 'ALTER TABLE %s ADD %s %s;' % (t, key, tt), 1048 'UPDATE %s SET %s=%s__tmp;' % (t, key, key), 1049 'ALTER TABLE %s DROP COLUMN %s__tmp;' % (t, key)] 1050 else: 1051 query = ['ALTER TABLE %s ADD %s__tmp %s;' % (t, key, tt), 1052 'UPDATE %s SET %s__tmp=%s;' % (t, key, key), 1053 'ALTER TABLE %s DROP %s;' % (t, key), 1054 'ALTER TABLE %s ADD %s %s;' % (t, key, tt), 1055 'UPDATE %s SET %s=%s__tmp;' % (t, key, key), 1056 'ALTER TABLE %s DROP %s__tmp;' % (t, key)] 1057 metadata_change = True 1058 elif sql_fields[key]['type'] != sql_fields_old[key]['type']: 1059 sql_fields_current[key] = sql_fields[key] 1060 metadata_change = True 1061 1062 if query: 1063 logfile.write('timestamp: %s\n' 1064 % datetime.datetime.today().isoformat()) 1065 db['_lastsql'] = '\n'.join(query) 1066 for sub_query in query: 1067 logfile.write(sub_query + '\n') 1068 if not fake_migrate: 1069 self.execute(sub_query) 1070 # Caveat: mysql, oracle and firebird do not allow multiple alter table 1071 # in one transaction so we must commit partial transactions and 1072 # update table._dbt after alter table. 1073 if db._adapter.commit_on_alter_table: 1074 db.commit() 1075 tfile = self.file_open(table._dbt, 'w') 1076 pickle.dump(sql_fields_current, tfile) 1077 self.file_close(tfile) 1078 logfile.write('success!\n') 1079 else: 1080 logfile.write('faked!\n') 1081 elif metadata_change: 1082 tfile = self.file_open(table._dbt, 'w') 1083 pickle.dump(sql_fields_current, tfile) 1084 self.file_close(tfile) 1085 1086 if metadata_change and \ 1087 not (query and self.dbengine in ('mysql','oracle','firebird')): 1088 db.commit() 1089 tfile = self.file_open(table._dbt, 'w') 1090 pickle.dump(sql_fields_current, tfile) 1091 self.file_close(tfile) 1092

1093 - def LOWER(self, first):

1094 return 'LOWER(%s)' % self.expand(first)

1095

1096 - def UPPER(self, first):

1097 return 'UPPER(%s)' % self.expand(first)

1098

1099 - def COUNT(self, first, distinct=None):

1100 return ('COUNT(%s)' if not distinct else 'COUNT(DISTINCT %s)') \ 1101 % self.expand(first)

1102

1103 - def EXTRACT(self, first, what):

1104 return "EXTRACT(%s FROM %s)" % (what, self.expand(first))

1105

1106 - def EPOCH(self, first):

1107 return self.EXTRACT(first, 'epoch')

1108

1109 - def AGGREGATE(self, first, what):

1110 return "%s(%s)" % (what, self.expand(first))

1111

1112 - def JOIN(self):

1113 return 'JOIN'

1114

1115 - def LEFT_JOIN(self):

1116 return 'LEFT JOIN'

1117

1118 - def RANDOM(self):

1119 return 'Random()'

1120

1121 - def NOT_NULL(self, default, field_type):

1122 return 'NOT NULL DEFAULT %s' % self.represent(default,field_type)

1123

1124 - def COALESCE(self, first, second):

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

1127

1128 - def COALESCE_ZERO(self, first):

1129 return 'COALESCE(%s,0)' % self.expand(first)

1130

1131 - def RAW(self, first):

1132 return first

1133

1134 - def ALLOW_NULL(self):

1135 return ''

1136

1137 - def SUBSTRING(self, field, parameters):

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

1139

1140 - def PRIMARY_KEY(self, key):

1141 return 'PRIMARY KEY(%s)' % key

1142

1143 - def _drop(self, table, mode):

1144 return ['DROP TABLE %s;' % table]

1145

1146 - def drop(self, table, mode=''):

1147 db = table._db 1148 if table._dbt: 1149 logfile = self.file_open(table._loggername, 'a') 1150 queries = self._drop(table, mode) 1151 for query in queries: 1152 if table._dbt: 1153 logfile.write(query + '\n') 1154 self.execute(query) 1155 db.commit() 1156 del db[table._tablename] 1157 del db.tables[db.tables.index(table._tablename)] 1158 db._remove_references_to(table) 1159 if table._dbt: 1160 self.file_delete(table._dbt) 1161 logfile.write('success!\n')

1162

1163 - def _insert(self, table, fields):

1164 keys = ','.join(f.name for f,v in fields) 1165 values = ','.join(self.expand(v,f.type) for f,v in fields) 1166 return 'INSERT INTO %s(%s) VALUES (%s);' % (table, keys, values)

1167

1168 - def insert(self, table, fields):

1169 query = self._insert(table,fields) 1170 try: 1171 self.execute(query) 1172 except Exception: 1173 e = sys.exc_info()[1] 1174 if isinstance(e,self.integrity_error_class()): 1175 return None 1176 raise e 1177 if hasattr(table,'_primarykey'): 1178 return dict([(k[0].name, k[1]) for k in fields \ 1179 if k[0].name in table._primarykey]) 1180 id = self.lastrowid(table) 1181 if not isinstance(id,int): 1182 return id 1183 rid = Reference(id) 1184 (rid._table, rid._record) = (table, None) 1185 return rid

1186

1187 - def bulk_insert(self, table, items):

1188 return [self.insert(table,item) for item in items]

1189

1190 - def NOT(self, first):

1191 return '(NOT %s)' % self.expand(first)

1192

1193 - def AND(self, first, second):

1194 return '(%s AND %s)' % (self.expand(first), self.expand(second))

1195

1196 - def OR(self, first, second):

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

1198

1199 - def BELONGS(self, first, second):

1200 if isinstance(second, str): 1201 return '(%s IN (%s))' % (self.expand(first), second[:-1]) 1202 elif not second: 1203 return '(1=0)' 1204 items = ','.join(self.expand(item, first.type) for item in second) 1205 return '(%s IN (%s))' % (self.expand(first), items)

1206

1207 - def REGEXP(self, first, second):

1208 "regular expression operator" 1209 raise NotImplementedError

1210

1211 - def LIKE(self, first, second):

1212 "case sensitive like operator" 1213 raise NotImplementedError

1214

1215 - def ILIKE(self, first, second):

1216 "case in-sensitive like operator" 1217 return '(%s LIKE %s)' % (self.expand(first), 1218 self.expand(second, 'string'))

1219

1220 - def STARTSWITH(self, first, second):

1221 return '(%s LIKE %s)' % (self.expand(first), 1222 self.expand(second+'%', 'string'))

1223

1224 - def ENDSWITH(self, first, second):

1225 return '(%s LIKE %s)' % (self.expand(first), 1226 self.expand('%'+second, 'string'))

1227

1228 - def CONTAINS(self, first, second):

1229 if first.type in ('string', 'text'): 1230 key = '%'+str(second).replace('%','%%')+'%' 1231 elif first.type.startswith('list:'): 1232 key = '%|'+str(second).replace('|','||').replace('%','%%')+'|%' 1233 return '(%s LIKE %s)' % (self.expand(first),self.expand(key,'string'))

1234

1235 - def EQ(self, first, second=None):

1236 if second is None: 1237 return '(%s IS NULL)' % self.expand(first) 1238 return '(%s = %s)' % (self.expand(first), 1239 self.expand(second, first.type))

1240

1241 - def NE(self, first, second=None):

1242 if second is None: 1243 return '(%s IS NOT NULL)' % self.expand(first) 1244 return '(%s <> %s)' % (self.expand(first), 1245 self.expand(second, first.type))

1246

1247 - def LT(self,first,second=None):

1248 if second is None: 1249 raise RuntimeError("Cannot compare %s < None" % first) 1250 return '(%s < %s)' % (self.expand(first), 1251 self.expand(second,first.type))

1252

1253 - def LE(self,first,second=None):

1254 if second is None: 1255 raise RuntimeError("Cannot compare %s <= None" % first) 1256 return '(%s <= %s)' % (self.expand(first), 1257 self.expand(second,first.type))

1258

1259 - def GT(self,first,second=None):

1260 if second is None: 1261 raise RuntimeError("Cannot compare %s > None" % first) 1262 return '(%s > %s)' % (self.expand(first), 1263 self.expand(second,first.type))

1264

1265 - def GE(self,first,second=None):

1266 if second is None: 1267 raise RuntimeError("Cannot compare %s >= None" % first) 1268 return '(%s >= %s)' % (self.expand(first), 1269 self.expand(second,first.type))

1270

1271 - def ADD(self, first, second):

1272 return '(%s + %s)' % (self.expand(first), 1273 self.expand(second, first.type))

1274

1275 - def SUB(self, first, second):

1276 return '(%s - %s)' % (self.expand(first), 1277 self.expand(second, first.type))

1278

1279 - def MUL(self, first, second):

1280 return '(%s * %s)' % (self.expand(first), 1281 self.expand(second, first.type))

1282

1283 - def DIV(self, first, second):

1284 return '(%s / %s)' % (self.expand(first), 1285 self.expand(second, first.type))

1286

1287 - def MOD(self, first, second):

1288 return '(%s %% %s)' % (self.expand(first), 1289 self.expand(second, first.type))

1290

1291 - def AS(self, first, second):

1292 return '%s AS %s' % (self.expand(first), second)

1293

1294 - def ON(self, first, second):

1295 return '%s ON %s' % (self.expand(first), self.expand(second))

1296

1297 - def INVERT(self, first):

1298 return '%s DESC' % self.expand(first)

1299

1300 - def COMMA(self, first, second):

1301 return '%s, %s' % (self.expand(first), self.expand(second))

1302

1303 - def expand(self, expression, field_type=None):

1304 if isinstance(expression, Field): 1305 return '%s.%s' % (expression.tablename, expression.name) 1306 elif isinstance(expression, (Expression, Query)): 1307 first = expression.first 1308 second = expression.second 1309 op = expression.op 1310 if not second is None: 1311 return op(first, second) 1312 elif not first is None: 1313 return op(first) 1314 elif isinstance(op, str): 1315 if op.endswith(';'): 1316 op=op[:-1] 1317 return '(%s)' % op 1318 else: 1319 return op() 1320 elif field_type: 1321 return str(self.represent(expression,field_type)) 1322 elif isinstance(expression,(list,tuple)): 1323 return ','.join(self.represent(item,field_type) \ 1324 for item in expression) 1325 elif isinstance(expression, bool): 1326 return '1' if expression else '0' 1327 else: 1328 return str(expression)

1329

1330 - def alias(self, table, alias):

1331 """ 1332 Given a table object, makes a new table object 1333 with alias name. 1334 """ 1335 other = copy.copy(table) 1336 other['_ot'] = other._tablename 1337 other['ALL'] = SQLALL(other) 1338 other['_tablename'] = alias 1339 for fieldname in other.fields: 1340 other[fieldname] = copy.copy(other[fieldname]) 1341 other[fieldname]._tablename = alias 1342 other[fieldname].tablename = alias 1343 other[fieldname].table = other 1344 table._db[alias] = other 1345 return other

1346

1347 - def _truncate(self, table, mode=''):

1348 tablename = table._tablename 1349 return ['TRUNCATE TABLE %s %s;' % (tablename, mode or '')]

1350

1351 - def truncate(self, table, mode= ' '):

1352 # Prepare functions "write_to_logfile" and "close_logfile" 1353 if table._dbt: 1354 logfile = self.file_open(table._loggername, 'a') 1355 else: 1356 class Logfile(object): 1357 def write(self, value): 1358 pass

1359 def close(self): 1360 pass 1361 logfile = Logfile() 1362 1363 try: 1364 queries = table._db._adapter._truncate(table, mode) 1365 for query in queries: 1366 logfile.write(query + '\n') 1367 self.execute(query) 1368 table._db.commit() 1369 logfile.write('success!\n') 1370 finally: 1371 logfile.close() 1372

1373 - def _update(self, tablename, query, fields):

1374 if query: 1375 if use_common_filters(query): 1376 query = self.common_filter(query, [tablename]) 1377 sql_w = ' WHERE ' + self.expand(query) 1378 else: 1379 sql_w = '' 1380 sql_v = ','.join(['%s=%s' % (field.name, 1381 self.expand(value, field.type)) \ 1382 for (field, value) in fields]) 1383 return 'UPDATE %s SET %s%s;' % (tablename, sql_v, sql_w)

1384

1385 - def update(self, tablename, query, fields):

1386 sql = self._update(tablename, query, fields) 1387 self.execute(sql) 1388 try: 1389 return self.cursor.rowcount 1390 except: 1391 return None

1392

1393 - def _delete(self, tablename, query):

1394 if query: 1395 if use_common_filters(query): 1396 query = self.common_filter(query, [tablename]) 1397 sql_w = ' WHERE ' + self.expand(query) 1398 else: 1399 sql_w = '' 1400 return 'DELETE FROM %s%s;' % (tablename, sql_w)

1401

1402 - def delete(self, tablename, query):

1403 sql = self._delete(tablename, query) 1404 ### special code to handle CASCADE in SQLite & SpatiaLite 1405 db = self.db 1406 table = db[tablename] 1407 if self.dbengine in ('sqlite', 'spatialite') and table._referenced_by: 1408 deleted = [x[table._id.name] for x in db(query).select(table._id)] 1409 ### end special code to handle CASCADE in SQLite & SpatiaLite 1410 self.execute(sql) 1411 try: 1412 counter = self.cursor.rowcount 1413 except: 1414 counter = None 1415 ### special code to handle CASCADE in SQLite & SpatiaLite 1416 if self.dbengine in ('sqlite', 'spatialite') and counter: 1417 for field in table._referenced_by: 1418 if field.type=='reference '+table._tablename \ 1419 and field.ondelete=='CASCADE': 1420 db(field.belongs(deleted)).delete() 1421 ### end special code to handle CASCADE in SQLite & SpatiaLite 1422 return counter

1423

1424 - def get_table(self, query):

1425 tablenames = self.tables(query) 1426 if len(tablenames)==1: 1427 return tablenames[0] 1428 elif len(tablenames)<1: 1429 raise RuntimeError("No table selected") 1430 else: 1431 raise RuntimeError("Too many tables selected")

1432

1433 - def expand_all(self, fields, tablenames):

1434 db = self.db 1435 new_fields = [] 1436 append = new_fields.append 1437 for item in fields: 1438 if isinstance(item,SQLALL): 1439 new_fields += item._table 1440 elif isinstance(item,str): 1441 if REGEX_TABLE_DOT_FIELD.match(item): 1442 tablename,fieldname = item.split('.') 1443 append(db[tablename][fieldname]) 1444 else: 1445 append(Expression(db,lambda:item)) 1446 else: 1447 append(item) 1448 # ## if no fields specified take them all from the requested tables 1449 if not new_fields: 1450 for table in tablenames: 1451 for field in db[table]: 1452 append(field) 1453 return new_fields

1454

1455 - def _select(self, query, fields, attributes):

1456 tables = self.tables 1457 for key in set(attributes.keys())-SELECT_ARGS: 1458 raise SyntaxError('invalid select attribute: %s' % key) 1459 args_get = attributes.get 1460 tablenames = tables(query) 1461 for field in fields: 1462 if isinstance(field, basestring) \ 1463 and REGEX_TABLE_DOT_FIELD.match(field): 1464 tn,fn = field.split('.') 1465 field = self.db[tn][fn] 1466 for tablename in tables(field): 1467 if not tablename in tablenames: 1468 tablenames.append(tablename) 1469 1470 if use_common_filters(query): 1471 query = self.common_filter(query,tablenames) 1472 1473 if len(tablenames) < 1: 1474 raise SyntaxError('Set: no tables selected') 1475 sql_f = ', '.join(map(self.expand, fields)) 1476 self._colnames = [c.strip() for c in sql_f.split(', ')] 1477 if query: 1478 sql_w = ' WHERE ' + self.expand(query) 1479 else: 1480 sql_w = '' 1481 sql_o = '' 1482 sql_s = '' 1483 left = args_get('left', False) 1484 inner_join = args_get('join', False) 1485 distinct = args_get('distinct', False) 1486 groupby = args_get('groupby', False) 1487 orderby = args_get('orderby', False) 1488 having = args_get('having', False) 1489 limitby = args_get('limitby', False) 1490 for_update = args_get('for_update', False) 1491 if self.can_select_for_update is False and for_update is True: 1492 raise SyntaxError('invalid select attribute: for_update') 1493 if distinct is True: 1494 sql_s += 'DISTINCT' 1495 elif distinct: 1496 sql_s += 'DISTINCT ON (%s)' % distinct 1497 if inner_join: 1498 icommand = self.JOIN() 1499 if not isinstance(inner_join, (tuple, list)): 1500 inner_join = [inner_join] 1501 ijoint = [t._tablename for t in inner_join 1502 if not isinstance(t,Expression)] 1503 ijoinon = [t for t in inner_join if isinstance(t, Expression)] 1504 itables_to_merge={} #issue 490 1505 [itables_to_merge.update( 1506 dict.fromkeys(tables(t))) for t in ijoinon] 1507 ijoinont = [t.first._tablename for t in ijoinon] 1508 [itables_to_merge.pop(t) for t in ijoinont 1509 if t in itables_to_merge] #issue 490 1510 iimportant_tablenames = ijoint + ijoinont + itables_to_merge.keys() 1511 iexcluded = [t for t in tablenames 1512 if not t in iimportant_tablenames] 1513 if left: 1514 join = attributes['left'] 1515 command = self.LEFT_JOIN() 1516 if not isinstance(join, (tuple, list)): 1517 join = [join] 1518 joint = [t._tablename for t in join 1519 if not isinstance(t, Expression)] 1520 joinon = [t for t in join if isinstance(t, Expression)] 1521 #patch join+left patch (solves problem with ordering in left joins) 1522 tables_to_merge={} 1523 [tables_to_merge.update( 1524 dict.fromkeys(tables(t))) for t in joinon] 1525 joinont = [t.first._tablename for t in joinon] 1526 [tables_to_merge.pop(t) for t in joinont if t in tables_to_merge] 1527 important_tablenames = joint + joinont + tables_to_merge.keys() 1528 excluded = [t for t in tablenames 1529 if not t in important_tablenames ] 1530 def alias(t): 1531 return str(self.db[t])

1532 if inner_join and not left: 1533 sql_t = ', '.join([alias(t) for t in iexcluded + \ 1534 itables_to_merge.keys()]) 1535 for t in ijoinon: 1536 sql_t += ' %s %s' % (icommand, str(t)) 1537 elif not inner_join and left: 1538 sql_t = ', '.join([alias(t) for t in excluded + \ 1539 tables_to_merge.keys()]) 1540 if joint: 1541 sql_t += ' %s %s' % (command, ','.join([t for t in joint])) 1542 for t in joinon: 1543 sql_t += ' %s %s' % (command, str(t)) 1544 elif inner_join and left: 1545 all_tables_in_query = set(important_tablenames + \ 1546 iimportant_tablenames + \ 1547 tablenames) 1548 tables_in_joinon = set(joinont + ijoinont) 1549 tables_not_in_joinon = \ 1550 all_tables_in_query.difference(tables_in_joinon) 1551 sql_t = ','.join([alias(t) for t in tables_not_in_joinon]) 1552 for t in ijoinon: 1553 sql_t += ' %s %s' % (icommand, str(t)) 1554 if joint: 1555 sql_t += ' %s %s' % (command, ','.join([t for t in joint])) 1556 for t in joinon: 1557 sql_t += ' %s %s' % (command, str(t)) 1558 else: 1559 sql_t = ', '.join(alias(t) for t in tablenames) 1560 if groupby: 1561 if isinstance(groupby, (list, tuple)): 1562 groupby = xorify(groupby) 1563 sql_o += ' GROUP BY %s' % self.expand(groupby) 1564 if having: 1565 sql_o += ' HAVING %s' % attributes['having'] 1566 if orderby: 1567 if isinstance(orderby, (list, tuple)): 1568 orderby = xorify(orderby) 1569 if str(orderby) == '<random>': 1570 sql_o += ' ORDER BY %s' % self.RANDOM() 1571 else: 1572 sql_o += ' ORDER BY %s' % self.expand(orderby) 1573 if limitby: 1574 if not orderby and tablenames: 1575 sql_o += ' ORDER BY %s' % ', '.join(['%s.%s'%(t,x) for t in tablenames for x in (hasattr(self.db[t],'_primarykey') and self.db[t]._primarykey or [self.db[t]._id.name])]) 1576 # oracle does not support limitby 1577 sql = self.select_limitby(sql_s, sql_f, sql_t, sql_w, sql_o, limitby) 1578 if for_update and self.can_select_for_update is True: 1579 sql = sql.rstrip(';') + ' FOR UPDATE;' 1580 return sql 1581

1582 - def select_limitby(self, sql_s, sql_f, sql_t, sql_w, sql_o, limitby):

1583 if limitby: 1584 (lmin, lmax) = limitby 1585 sql_o += ' LIMIT %i OFFSET %i' % (lmax - lmin, lmin) 1586 return 'SELECT %s %s FROM %s%s%s;' % \ 1587 (sql_s, sql_f, sql_t, sql_w, sql_o)

1588

1589 - def _fetchall(self):

1590 return self.cursor.fetchall()

1591

1592 - def _select_aux(self,sql,fields,attributes):

1593 args_get = attributes.get 1594 cache = args_get('cache',None) 1595 if not cache: 1596 self.execute(sql) 1597 rows = self._fetchall() 1598 else: 1599 (cache_model, time_expire) = cache 1600 key = self.uri + '/' + sql + '/rows' 1601 if len(key)>200: key = hashlib_md5(key).hexdigest() 1602 def _select_aux2(): 1603 self.execute(sql) 1604 return self._fetchall()

1605 rows = cache_model(key,_select_aux2,time_expire) 1606 if isinstance(rows,tuple): 1607 rows = list(rows) 1608 limitby = args_get('limitby', None) or (0,) 1609 rows = self.rowslice(rows,limitby[0],None) 1610 processor = args_get('processor',self.parse) 1611 cacheable = args_get('cacheable',False) 1612 return processor(rows,fields,self._colnames,cacheable=cacheable) 1613

1614 - def select(self, query, fields, attributes):

1615 """ 1616 Always returns a Rows object, possibly empty. 1617 """ 1618 sql = self._select(query, fields, attributes) 1619 cache = attributes.get('cache', None) 1620 if cache and attributes.get('cacheable',False): 1621 del attributes['cache'] 1622 (cache_model, time_expire) = cache 1623 key = self.uri + '/' + sql 1624 if len(key)>200: key = hashlib_md5(key).hexdigest() 1625 args = (sql,fields,attributes) 1626 return cache_model( 1627 key, 1628 lambda self=self,args=args:self._select_aux(*args), 1629 time_expire) 1630 else: 1631 return self._select_aux(sql,fields,attributes)

1632

1633 - def _count(self, query, distinct=None):

1634 tablenames = self.tables(query) 1635 if query: 1636 if use_common_filters(query): 1637 query = self.common_filter(query, tablenames) 1638 sql_w = ' WHERE ' + self.expand(query) 1639 else: 1640 sql_w = '' 1641 sql_t = ','.join(tablenames) 1642 if distinct: 1643 if isinstance(distinct,(list, tuple)): 1644 distinct = xorify(distinct) 1645 sql_d = self.expand(distinct) 1646 return 'SELECT count(DISTINCT %s) FROM %s%s;' % \ 1647 (sql_d, sql_t, sql_w) 1648 return 'SELECT count(*) FROM %s%s;' % (sql_t, sql_w)

1649

1650 - def count(self, query, distinct=None):

1651 self.execute(self._count(query, distinct)) 1652 return self.cursor.fetchone()[0]

1653

1654 - def tables(self, *queries):

1655 tables = set() 1656 for query in queries: 1657 if isinstance(query, Field): 1658 tables.add(query.tablename) 1659 elif isinstance(query, (Expression, Query)): 1660 if not query.first is None: 1661 tables = tables.union(self.tables(query.first)) 1662 if not query.second is None: 1663 tables = tables.union(self.tables(query.second)) 1664 return list(tables)

1665

1666 - def commit(self):

1667 if self.connection: return self.connection.commit()

1668

1669 - def rollback(self):

1670 if self.connection: return self.connection.rollback()

1671

1672 - def close_connection(self):

1673 if self.connection: return self.connection.close()

1674

1675 - def distributed_transaction_begin(self, key):

1676 return

1677

1678 - def prepare(self, key):

1679 if self.connection: self.connection.prepare()

1680

1681 - def commit_prepared(self, key):

1682 if self.connection: self.connection.commit()

1683

1684 - def rollback_prepared(self, key):

1685 if self.connection: self.connection.rollback()

1686

1687 - def concat_add(self, table):

1688 return ', ADD '

1689

1690 - def constraint_name(self, table, fieldname):

1691 return '%s_%s__constraint' % (table,fieldname)

1692

1693 - def create_sequence_and_triggers(self, query, table, **args):

1694 self.execute(query)

1695

1696 - def log_execute(self, *a, **b):

1697 if not self.connection: return None 1698 command = a[0] 1699 if self.db._debug: 1700 LOGGER.debug('SQL: %s' % command) 1701 self.db._lastsql = command 1702 t0 = time.time() 1703 ret = self.cursor.execute(*a, **b) 1704 self.db._timings.append((command,time.time()-t0)) 1705 del self.db._timings[:-TIMINGSSIZE] 1706 return ret

1707

1708 - def execute(self, *a, **b):

1709 return self.log_execute(*a, **b)

1710

1711 - def represent(self, obj, fieldtype):

1712 field_is_type = fieldtype.startswith 1713 if isinstance(obj, CALLABLETYPES): 1714 obj = obj() 1715 if isinstance(fieldtype, SQLCustomType): 1716 value = fieldtype.encoder(obj) 1717 if fieldtype.type in ('string','text'): 1718 return self.adapt(value) 1719 return value 1720 if isinstance(obj, (Expression, Field)): 1721 return str(obj) 1722 if field_is_type('list:'): 1723 if not obj: 1724 obj = [] 1725 elif not isinstance(obj, (list, tuple)): 1726 obj = [obj] 1727 if field_is_type('list:string'): 1728 obj = map(str,obj) 1729 else: 1730 obj = map(int,obj) 1731 if isinstance(obj, (list, tuple)): 1732 obj = bar_encode(obj) 1733 if obj is None: 1734 return 'NULL' 1735 if obj == '' and not fieldtype[:2] in ['st', 'te', 'pa', 'up']: 1736 return 'NULL' 1737 r = self.represent_exceptions(obj, fieldtype) 1738 if not r is None: 1739 return r 1740 if fieldtype == 'boolean': 1741 if obj and not str(obj)[:1].upper() in '0F': 1742 return self.smart_adapt(self.TRUE) 1743 else: 1744 return self.smart_adapt(self.FALSE) 1745 if fieldtype == 'id' or fieldtype == 'integer': 1746 return str(int(obj)) 1747 if field_is_type('decimal'): 1748 return str(obj) 1749 elif field_is_type('reference'): # reference 1750 if fieldtype.find('.')>0: 1751 return repr(obj) 1752 elif isinstance(obj, (Row, Reference)): 1753 return str(obj['id']) 1754 return str(int(obj)) 1755 elif fieldtype == 'double': 1756 return repr(float(obj)) 1757 if isinstance(obj, unicode): 1758 obj = obj.encode(self.db_codec) 1759 if fieldtype == 'blob': 1760 obj = base64.b64encode(str(obj)) 1761 elif fieldtype == 'date': 1762 if isinstance(obj, (datetime.date, datetime.datetime)): 1763 obj = obj.isoformat()[:10] 1764 else: 1765 obj = str(obj) 1766 elif fieldtype == 'datetime': 1767 if isinstance(obj, datetime.datetime): 1768 obj = obj.isoformat()[:19].replace('T',' ') 1769 elif isinstance(obj, datetime.date): 1770 obj = obj.isoformat()[:10]+' 00:00:00' 1771 else: 1772 obj = str(obj) 1773 elif fieldtype == 'time': 1774 if isinstance(obj, datetime.time): 1775 obj = obj.isoformat()[:10] 1776 else: 1777 obj = str(obj) 1778 if not isinstance(obj,bytes): 1779 obj = bytes(obj) 1780 try: 1781 obj.decode(self.db_codec) 1782 except: 1783 obj = obj.decode('latin1').encode(self.db_codec) 1784 return self.adapt(obj)

1785

1786 - def represent_exceptions(self, obj, fieldtype):

1787 return None

1788

1789 - def lastrowid(self, table):

1790 return None

1791

1792 - def integrity_error_class(self):

1793 return type(None)

1794

1795 - def rowslice(self, rows, minimum=0, maximum=None):

1796 """ 1797 By default this function does nothing; 1798 overload when db does not do slicing. 1799 """ 1800 return rows

1801

1802 - def parse_value(self, value, field_type, blob_decode=True):

1803 if field_type != 'blob' and isinstance(value, str): 1804 try: 1805 value = value.decode(self.db._db_codec) 1806 except Exception: 1807 pass 1808 if isinstance(value, unicode): 1809 value = value.encode('utf-8') 1810 if isinstance(field_type, SQLCustomType): 1811 value = field_type.decoder(value) 1812 if not isinstance(field_type, str) or value is None: 1813 return value 1814 elif field_type in ('string', 'text', 'password', 'upload', 'dict'): 1815 return value 1816 elif field_type.startswith('geo'): 1817 return value 1818 elif field_type == 'blob' and not blob_decode: 1819 return value 1820 else: 1821 key = REGEX_TYPE.match(field_type).group(0) 1822 return self.parsemap[key](value,field_type)

1823

1824 - def parse_reference(self, value, field_type):

1825 referee = field_type[10:].strip() 1826 if not '.' in referee: 1827 value = Reference(value) 1828 value._table, value._record = self.db[referee], None 1829 return value

1830

1831 - def parse_boolean(self, value, field_type):

1832 return value == True or str(value)[:1].lower() == 't'

1833

1834 - def parse_date(self, value, field_type):

1835 if not isinstance(value, (datetime.date,datetime.datetime)): 1836 (y, m, d) = map(int, str(value)[:10].strip().split('-')) 1837 value = datetime.date(y, m, d) 1838 return value

1839

1840 - def parse_time(self, value, field_type):

1841 if not isinstance(value, datetime.time): 1842 time_items = map(int,str(value)[:8].strip().split(':')[:3]) 1843 if len(time_items) == 3: 1844 (h, mi, s) = time_items 1845 else: 1846 (h, mi, s) = time_items + [0] 1847 value = datetime.time(h, mi, s) 1848 return value

1849

1850 - def parse_datetime(self, value, field_type):

1851 if not isinstance(value, datetime.datetime): 1852 value = str(value) 1853 date_part,time_part,timezone = value[:10],value[11:19],value[19:] 1854 if '+' in timezone: 1855 ms,tz = timezone.split('+') 1856 h,m = tz.split(':') 1857 dt = datetime.timedelta(seconds=3600*int(h)+60*int(m)) 1858 elif '-' in timezone: 1859 ms,tz = timezone.split('-') 1860 h,m = tz.split(':') 1861 dt = -datetime.timedelta(seconds=3600*int(h)+60*int(m)) 1862 else: 1863 dt = None 1864 (y, m, d) = map(int,date_part.split('-')) 1865 time_parts = time_part and time_part.split(':')[:3] or (0,0,0) 1866 while len(time_parts)<3: time_parts.append(0) 1867 time_items = map(int,time_parts) 1868 (h, mi, s) = time_items 1869 value = datetime.datetime(y, m, d, h, mi, s) 1870 if dt: 1871 value = value + dt 1872 return value

1873

1874 - def parse_blob(self, value, field_type):

1875 return base64.b64decode(str(value))

1876

1877 - def parse_decimal(self, value, field_type):

1878 decimals = int(field_type[8:-1].split(',')[-1]) 1879 if self.dbengine in ('sqlite', 'spatialite'): 1880 value = ('%.' + str(decimals) + 'f') % value 1881 if not isinstance(value, decimal.Decimal): 1882 value = decimal.Decimal(str(value)) 1883 return value

1884

1885 - def parse_list_integers(self, value, field_type):

1886 if not self.dbengine=='google:datastore': 1887 value = bar_decode_integer(value) 1888 return value

1889

1890 - def parse_list_references(self, value, field_type):

1891 if not self.dbengine=='google:datastore': 1892 value = bar_decode_integer(value) 1893 return [self.parse_reference(r, field_type[5:]) for r in value]

1894

1895 - def parse_list_strings(self, value, field_type):

1896 if not self.dbengine=='google:datastore': 1897 value = bar_decode_string(value) 1898 return value

1899

1900 - def parse_id(self, value, field_type):

1901 return int(value)

1902

1903 - def parse_integer(self, value, field_type):

1904 return int(value)

1905

1906 - def parse_double(self, value, field_type):

1907 return float(value)

1908

1909 - def build_parsemap(self):

1910 self.parsemap = { 1911 'id':self.parse_id, 1912 'integer':self.parse_integer, 1913 'bigint':self.parse_integer, 1914 'float':self.parse_double, 1915 'double':self.parse_double, 1916 'reference':self.parse_reference, 1917 'boolean':self.parse_boolean, 1918 'date':self.parse_date, 1919 'time':self.parse_time, 1920 'datetime':self.parse_datetime, 1921 'blob':self.parse_blob, 1922 'decimal':self.parse_decimal, 1923 'list:integer':self.parse_list_integers, 1924 'list:reference':self.parse_list_references, 1925 'list:string':self.parse_list_strings, 1926 }

1927

1928 - def parse(self, rows, fields, colnames, blob_decode=True, 1929 cacheable = False):

1930 self.build_parsemap() 1931 db = self.db 1932 virtualtables = [] 1933 new_rows = [] 1934 tmps = [] 1935 for colname in colnames: 1936 if not REGEX_TABLE_DOT_FIELD.match(colname): 1937 tmps.append(None) 1938 else: 1939 (tablename, fieldname) = colname.split('.') 1940 table = db[tablename] 1941 field = table[fieldname] 1942 ft = field.type 1943 tmps.append((tablename,fieldname,table,field,ft)) 1944 for (i,row) in enumerate(rows): 1945 new_row = Row() 1946 for (j,colname) in enumerate(colnames): 1947 value = row[j] 1948 tmp = tmps[j] 1949 if tmp: 1950 (tablename,fieldname,table,field,ft) = tmp 1951 if tablename in new_row: 1952 colset = new_row[tablename] 1953 else: 1954 colset = new_row[tablename] = Row() 1955 if tablename not in virtualtables: 1956 virtualtables.append(tablename) 1957 value = self.parse_value(value,ft,blob_decode) 1958 if field.filter_out: 1959 value = field.filter_out(value) 1960 colset[fieldname] = value 1961 1962 # for backward compatibility 1963 if ft=='id' and fieldname!='id' and \ 1964 not 'id' in table.fields: 1965 colset['id'] = value 1966 1967 if ft == 'id' and not cacheable: 1968 # temporary hack to deal with 1969 # GoogleDatastoreAdapter 1970 # references 1971 if isinstance(self, GoogleDatastoreAdapter): 1972 id = value.key().id_or_name() 1973 colset[fieldname] = id 1974 colset.gae_item = value 1975 else: 1976 id = value 1977 colset.update_record = RecordUpdater(colset,table,id) 1978 colset.delete_record = RecordDeleter(table,id) 1979 for rfield in table._referenced_by: 1980 referee_link = db._referee_name and \ 1981 db._referee_name % dict( 1982 table=rfield.tablename,field=rfield.name) 1983 if referee_link and not referee_link in colset: 1984 colset[referee_link] = LazySet(rfield,id) 1985 else: 1986 if not '_extra' in new_row: 1987 new_row['_extra'] = Row() 1988 new_row['_extra'][colname] = \ 1989 self.parse_value(value, 1990 fields[j].type,blob_decode) 1991 new_column_name = \ 1992 REGEX_SELECT_AS_PARSER.search(colname) 1993 if not new_column_name is None: 1994 column_name = new_column_name.groups(0) 1995 setattr(new_row,column_name[0],value) 1996 new_rows.append(new_row) 1997 rowsobj = Rows(db, new_rows, colnames, rawrows=rows) 1998 1999 for tablename in virtualtables: 2000 ### new style virtual fields 2001 table = db[tablename] 2002 fields_virtual = [(f,v) for (f,v) in table.iteritems() 2003 if isinstance(v,FieldVirtual)] 2004 fields_lazy = [(f,v) for (f,v) in table.iteritems() 2005 if isinstance(v,FieldMethod)] 2006 if fields_virtual or fields_lazy: 2007 for row in rowsobj.records: 2008 box = row[tablename] 2009 for f,v in fields_virtual: 2010 box[f] = v.f(row) 2011 for f,v in fields_lazy: 2012 box[f] = (v.handler or VirtualCommand)(v.f,row) 2013 2014 ### old style virtual fields 2015 for item in table.virtualfields: 2016 try: 2017 rowsobj = rowsobj.setvirtualfields(**{tablename:item}) 2018 except (KeyError, AttributeError): 2019 # to avoid breaking virtualfields when partial select 2020 pass 2021 return rowsobj

2022

2023 - def common_filter(self, query, tablenames):

2024 tenant_fieldname = self.db._request_tenant 2025 2026 for tablename in tablenames: 2027 table = self.db[tablename] 2028 2029 # deal with user provided filters 2030 if table._common_filter != None: 2031 query = query & table._common_filter(query) 2032 2033 # deal with multi_tenant filters 2034 if tenant_fieldname in table: 2035 default = table[tenant_fieldname].default 2036 if not default is None: 2037 newquery = table[tenant_fieldname] == default 2038 if query is None: 2039 query = newquery 2040 else: 2041 query = query & newquery 2042 return query

2043

2044 - def CASE(self,query,t,f):

2045 def represent(x): 2046 types = {type(True):'boolean',type(0):'integer',type(1.0):'double'} 2047 if x is None: return 'NULL' 2048 elif isinstance(x,Expression): return str(x) 2049 else: return self.represent(x,types.get(type(x),'string'))

2050 return Expression(self.db,'CASE WHEN %s THEN %s ELSE %s END' % \ 2051 (self.expand(query),represent(t),represent(f))) 2052 2053 ################################################################################### 2054 # List of all the available adapters; they all extend BaseAdapter. 2055 ################################################################################### 2056

2057 -class SQLiteAdapter(BaseAdapter):

2058 drivers = ('sqlite2','sqlite3') 2059 2060 can_select_for_update = None # support ourselves with BEGIN TRANSACTION 2061

2062 - def EXTRACT(self,field,what):

2063 return "web2py_extract('%s',%s)" % (what, self.expand(field))

2064 2065 @staticmethod

2066 - def web2py_extract(lookup, s):

2067 table = { 2068 'year': (0, 4), 2069 'month': (5, 7), 2070 'day': (8, 10), 2071 'hour': (11, 13), 2072 'minute': (14, 16), 2073 'second': (17, 19), 2074 } 2075 try: 2076 if lookup != 'epoch': 2077 (i, j) = table[lookup] 2078 return int(s[i:j]) 2079 else: 2080 return time.mktime(datetime.datetime.strptime(s, '%Y-%m-%d %H:%M:%S').timetuple()) 2081 except: 2082 return None

2083 2084 @staticmethod

2085 - def web2py_regexp(expression, item):

2086 return re.compile(expression).search(item) is not None

2087

2088 - def __init__(self, db, uri, pool_size=0, folder=None, db_codec ='UTF-8', 2089 credential_decoder=IDENTITY, driver_args={}, 2090 adapter_args={}, do_connect=True):

2091 self.db = db 2092 self.dbengine = "sqlite" 2093 self.uri = uri 2094 if do_connect: self.find_driver(adapter_args) 2095 self.pool_size = 0 2096 self.folder = folder 2097 self.db_codec = db_codec 2098 self.find_or_make_work_folder() 2099 path_encoding = sys.getfilesystemencoding() \ 2100 or locale.getdefaultlocale()[1] or 'utf8' 2101 if uri.startswith('sqlite:memory'): 2102 dbpath = ':memory:' 2103 else: 2104 dbpath = uri.split('://',1)[1] 2105 if dbpath[0] != '/': 2106 if PYTHON_VERSION == 2: 2107 dbpath = pjoin( 2108 self.folder.decode(path_encoding).encode('utf8'), dbpath) 2109 else: 2110 dbpath = pjoin(self.folder, dbpath) 2111 if not 'check_same_thread' in driver_args: 2112 driver_args['check_same_thread'] = False 2113 if not 'detect_types' in driver_args: 2114 driver_args['detect_types'] = self.driver.PARSE_DECLTYPES 2115 def connector(dbpath=dbpath, driver_args=driver_args): 2116 return self.driver.Connection(dbpath, **driver_args)

2117 self.connector = connector 2118 if do_connect: self.reconnect()

2119

2120 - def after_connection(self):

2121 self.connection.create_function('web2py_extract', 2, 2122 SQLiteAdapter.web2py_extract) 2123 self.connection.create_function("REGEXP", 2, 2124 SQLiteAdapter.web2py_regexp)

2125

2126 - def _truncate(self, table, mode=''):

2127 tablename = table._tablename 2128 return ['DELETE FROM %s;' % tablename, 2129 "DELETE FROM sqlite_sequence WHERE name='%s';" % tablename]

2130

2131 - def lastrowid(self, table):

2132 return self.cursor.lastrowid

2133

2134 - def REGEXP(self,first,second):

2135 return '(%s REGEXP %s)' % (self.expand(first), 2136 self.expand(second,'string'))

2137

2138 - def select(self, query, fields, attributes):

2139 """ 2140 Simulate SELECT ... FOR UPDATE with BEGIN IMMEDIATE TRANSACTION. 2141 Note that the entire database, rather than one record, is locked 2142 (it will be locked eventually anyway by the following UPDATE). 2143 """ 2144 if attributes.get('for_update', False) and not 'cache' in attributes: 2145 self.execute('BEGIN IMMEDIATE TRANSACTION;') 2146 return super(SQLiteAdapter, self).select(query, fields, attributes)

2147

2148 -class SpatiaLiteAdapter(SQLiteAdapter):

2149 drivers = ('sqlite3','sqlite2') 2150 2151 types = copy.copy(BaseAdapter.types) 2152 types.update(geometry='GEOMETRY') 2153

2154 - def __init__(self, db, uri, pool_size=0, folder=None, db_codec ='UTF-8', 2155 credential_decoder=IDENTITY, driver_args={}, 2156 adapter_args={}, do_connect=True, srid=4326):

2157 self.db = db 2158 self.dbengine = "spatialite" 2159 self.uri = uri 2160 if do_connect: self.find_driver(adapter_args) 2161 self.pool_size = 0 2162 self.folder = folder 2163 self.db_codec = db_codec 2164 self.find_or_make_work_folder() 2165 self.srid = srid 2166 path_encoding = sys.getfilesystemencoding() \ 2167 or locale.getdefaultlocale()[1] or 'utf8' 2168 if uri.startswith('spatialite:memory'): 2169 dbpath = ':memory:' 2170 else: 2171 dbpath = uri.split('://',1)[1] 2172 if dbpath[0] != '/': 2173 dbpath = pjoin( 2174 self.folder.decode(path_encoding).encode('utf8'), dbpath) 2175 if not 'check_same_thread' in driver_args: 2176 driver_args['check_same_thread'] = False 2177 if not 'detect_types' in driver_args: 2178 driver_args['detect_types'] = self.driver.PARSE_DECLTYPES 2179 def connector(dbpath=dbpath, driver_args=driver_args): 2180 return self.driver.Connection(dbpath, **driver_args)

2181 self.connector = connector 2182 if do_connect: self.reconnect()

2183

2184 - def after_connection(self):

2185 self.connection.enable_load_extension(True) 2186 # for Windows, rename libspatialite-2.dll to libspatialite.dll 2187 # Linux uses libspatialite.so 2188 # Mac OS X uses libspatialite.dylib 2189 libspatialite = SPATIALLIBS[platform.system()] 2190 self.execute(r'SELECT load_extension("%s");') % libspatialite 2191 2192 self.connection.create_function('web2py_extract', 2, 2193 SQLiteAdapter.web2py_extract) 2194 self.connection.create_function("REGEXP", 2, 2195 SQLiteAdapter.web2py_regexp)

2196 2197 # GIS functions 2198

2199 - def ST_ASGEOJSON(self, first, second):

2200 return 'AsGeoJSON(%s,%s,%s)' %(self.expand(first), 2201 second['precision'], second['options'])

2202

2203 - def ST_ASTEXT(self, first):

2204 return 'AsText(%s)' %(self.expand(first))

2205

2206 - def ST_CONTAINS(self, first, second):

2207 return 'Contains(%s,%s)' %(self.expand(first), 2208 self.expand(second, first.type))

2209

2210 - def ST_DISTANCE(self, first, second):

2211 return 'Distance(%s,%s)' %(self.expand(first), 2212 self.expand(second, first.type))

2213

2214 - def ST_EQUALS(self, first, second):

2215 return 'Equals(%s,%s)' %(self.expand(first), 2216 self.expand(second, first.type))

2217

2218 - def ST_INTERSECTS(self, first, second):

2219 return 'Intersects(%s,%s)' %(self.expand(first), 2220 self.expand(second, first.type))

2221

2222 - def ST_OVERLAPS(self, first, second):

2223 return 'Overlaps(%s,%s)' %(self.expand(first), 2224 self.expand(second, first.type))

2225

2226 - def ST_SIMPLIFY(self, first, second):

2227 return 'Simplify(%s,%s)' %(self.expand(first), 2228 self.expand(second, 'double'))

2229

2230 - def ST_TOUCHES(self, first, second):

2231 return 'Touches(%s,%s)' %(self.expand(first), 2232 self.expand(second, first.type))

2233

2234 - def ST_WITHIN(self, first, second):

2235 return 'Within(%s,%s)' %(self.expand(first), 2236 self.expand(second, first.type))

2237

2238 - def represent(self, obj, fieldtype):

2239 field_is_type = fieldtype.startswith 2240 if field_is_type('geo'): 2241 srid = 4326 # Spatialite default srid for geometry 2242 geotype, parms = fieldtype[:-1].split('(') 2243 parms = parms.split(',') 2244 if len(parms) >= 2: 2245 schema, srid = parms[:2] 2246 # if field_is_type('geometry'): 2247 value = "ST_GeomFromText('%s',%s)" %(obj, srid) 2248 # elif field_is_type('geography'): 2249 # value = "ST_GeogFromText('SRID=%s;%s')" %(srid, obj) 2250 # else: 2251 # raise SyntaxError, 'Invalid field type %s' %fieldtype 2252 return value 2253 return BaseAdapter.represent(self, obj, fieldtype)

2254 2255

2256 -class JDBCSQLiteAdapter(SQLiteAdapter):

2257 drivers = ('zxJDBC_sqlite',) 2258

2259 - def __init__(self, db, uri, pool_size=0, folder=None, db_codec='UTF-8', 2260 credential_decoder=IDENTITY, driver_args={}, 2261 adapter_args={}, do_connect=True):

2262 self.db = db 2263 self.dbengine = "sqlite" 2264 self.uri = uri 2265 if do_connect: self.find_driver(adapter_args) 2266 self.pool_size = pool_size 2267 self.folder = folder 2268 self.db_codec = db_codec 2269 self.find_or_make_work_folder() 2270 path_encoding = sys.getfilesystemencoding() \ 2271 or locale.getdefaultlocale()[1] or 'utf8' 2272 if uri.startswith('sqlite:memory'): 2273 dbpath = ':memory:' 2274 else: 2275 dbpath = uri.split('://',1)[1] 2276 if dbpath[0] != '/': 2277 dbpath = pjoin( 2278 self.folder.decode(path_encoding).encode('utf8'), dbpath) 2279 def connector(dbpath=dbpath,driver_args=driver_args): 2280 return self.driver.connect( 2281 self.driver.getConnection('jdbc:sqlite:'+dbpath), 2282 **driver_args)

2283 self.connector = connector 2284 if do_connect: self.reconnect()

2285

2286 - def after_connection(self):

2287 # FIXME http://www.zentus.com/sqlitejdbc/custom_functions.html for UDFs 2288 self.connection.create_function('web2py_extract', 2, 2289 SQLiteAdapter.web2py_extract)

2290

2291 - def execute(self, a):

2292 return self.log_execute(a)

2293 2294

2295 -class MySQLAdapter(BaseAdapter):

2296 drivers = ('MySQLdb','pymysql') 2297 2298 maxcharlength = 255 2299 commit_on_alter_table = True 2300 support_distributed_transaction = True 2301 types = { 2302 'boolean': 'CHAR(1)', 2303 'string': 'VARCHAR(%(length)s)', 2304 'text': 'LONGTEXT', 2305 'password': 'VARCHAR(%(length)s)', 2306 'blob': 'LONGBLOB', 2307 'upload': 'VARCHAR(%(length)s)', 2308 'integer': 'INT', 2309 'bigint': 'BIGINT', 2310 'float': 'FLOAT', 2311 'double': 'DOUBLE', 2312 'decimal': 'NUMERIC(%(precision)s,%(scale)s)', 2313 'date': 'DATE', 2314 'time': 'TIME', 2315 'datetime': 'DATETIME', 2316 'id': 'INT AUTO_INCREMENT NOT NULL', 2317 'reference': 'INT, INDEX %(index_name)s (%(field_name)s), FOREIGN KEY (%(field_name)s) REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s', 2318 'list:integer': 'LONGTEXT', 2319 'list:string': 'LONGTEXT', 2320 'list:reference': 'LONGTEXT', 2321 'big-id': 'BIGINT AUTO_INCREMENT NOT NULL', 2322 'big-reference': 'BIGINT, INDEX %(index_name)s (%(field_name)s), FOREIGN KEY (%(field_name)s) REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s', 2323 } 2324

2325 - def varquote(self,name):

2326 return varquote_aux(name,'`%s`')

2327

2328 - def RANDOM(self):

2329 return 'RAND()'

2330

2331 - def SUBSTRING(self,field,parameters):

2332 return 'SUBSTRING(%s,%s,%s)' % (self.expand(field), 2333 parameters[0], parameters[1])

2334

2335 - def EPOCH(self, first):

2336 return "UNIX_TIMESTAMP(%s)" % self.expand(first)

2337

2338 - def _drop(self,table,mode):

2339 # breaks db integrity but without this mysql does not drop table 2340 return ['SET FOREIGN_KEY_CHECKS=0;','DROP TABLE %s;' % table, 2341 'SET FOREIGN_KEY_CHECKS=1;']

2342

2343 - def distributed_transaction_begin(self,key):

2344 self.execute('XA START;')

2345

2346 - def prepare(self,key):

2347 self.execute("XA END;") 2348 self.execute("XA PREPARE;")

2349

2350 - def commit_prepared(self,ley):

2351 self.execute("XA COMMIT;")

2352

2353 - def rollback_prepared(self,key):

2354 self.execute("XA ROLLBACK;")

2355

2356 - def concat_add(self,table):

2357 return '; ALTER TABLE %s ADD ' % table

2358 2359 REGEX_URI = re.compile('^(?P<user>[^:@]+)(\:(?P<password>[^@]*))?@(?P<host>[^\:/]+)(\:(?P<port>[0-9]+))?/(?P<db>[^?]+)(\?set_encoding=(?P<charset>\w+))?$') 2360

2361 - def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8', 2362 credential_decoder=IDENTITY, driver_args={}, 2363 adapter_args={}, do_connect=True):

2364 self.db = db 2365 self.dbengine = "mysql" 2366 self.uri = uri 2367 if do_connect: self.find_driver(adapter_args,uri) 2368 self.pool_size = pool_size 2369 self.folder = folder 2370 self.db_codec = db_codec 2371 self.find_or_make_work_folder() 2372 ruri = uri.split('://',1)[1] 2373 m = self.REGEX_URI.match(ruri) 2374 if not m: 2375 raise SyntaxError( 2376 "Invalid URI string in DAL: %s" % self.uri) 2377 user = credential_decoder(m.group('user')) 2378 if not user: 2379 raise SyntaxError('User required') 2380 password = credential_decoder(m.group('password')) 2381 if not password: 2382 password = '' 2383 host = m.group('host') 2384 if not host: 2385 raise SyntaxError('Host name required') 2386 db = m.group('db') 2387 if not db: 2388 raise SyntaxError('Database name required') 2389 port = int(m.group('port') or '3306') 2390 charset = m.group('charset') or 'utf8' 2391 driver_args.update(db=db, 2392 user=credential_decoder(user), 2393 passwd=credential_decoder(password), 2394 host=host, 2395 port=port, 2396 charset=charset) 2397 2398 2399 def connector(driver_args=driver_args): 2400 return self.driver.connect(**driver_args)

2401 self.connector = connector 2402 if do_connect: self.reconnect()

2403

2404 - def after_connection(self):

2405 self.execute('SET FOREIGN_KEY_CHECKS=1;') 2406 self.execute("SET sql_mode='NO_BACKSLASH_ESCAPES';")

2407

2408 - def lastrowid(self,table):

2409 self.execute('select last_insert_id();') 2410 return int(self.cursor.fetchone()[0])

2411

2412 -class PostgreSQLAdapter(BaseAdapter):

2413 drivers =