Changeset 9
- Timestamp:
- 02/13/07 06:17:19
- Files:
-
- trunk/geniusql/__init__.py (modified) (3 diffs)
- trunk/geniusql/providers/ado.py (modified) (2 diffs)
- trunk/geniusql/providers/mysql.py (modified) (1 diff)
- trunk/geniusql/providers/psycopg.py (modified) (1 diff)
- trunk/geniusql/providers/pypgsql.py (modified) (1 diff)
- trunk/geniusql/select.py (modified) (2 diffs)
- trunk/geniusql/test/zoo_fixture.py (modified) (2 diffs)
Legend:
- Unmodified
- Added
- Removed
- Modified
- Copied
- Moved
trunk/geniusql/__init__.py
r8 r9 139 139 imperfect_type: if True, signals that we are deliberately using a 140 140 database type other than the default (usually in order to handle 141 irregular values, such as huge numbers). 141 irregular values, such as huge numbers). When comparing database 142 values with constant values in SQL, such columns must have an 143 explicit adaptertosql.cast_dbtype_to_pytype method to cast 144 the column value to one which can successfully be compared 145 with the constant. If there is no matching cast_* method, 146 then the query will be marked imperfect. 142 147 autoincrement: if True, uses the database's built-in sequencing. 143 148 sequence_name: for databases that use separate statements to create and … … 347 352 # ---------------------------- OLTP/CRUD ---------------------------- # 348 353 354 def whereclause(self, **inputs): 355 """Return an SQL WHERE clause for the given input fields. 356 357 If the given clause is imperfect, a ValueError is raised. 358 """ 359 tpair = [(self.qname, self)] 360 decom = self.db.decompiler(tpair, logic.filter(**inputs), 361 self.db.adaptertosql) 362 code = decom.code() 363 if decom.imperfect: 364 raise ValueError("The given inputs could not safely be translated " 365 "to SQL.", inputs, code) 366 return code 367 349 368 def id_clause(self, **inputs): 350 369 """Return an SQL expression for the identifiers of the given table.""" 351 coerce = self.db.adaptertosql.coerce 352 pairs = [] 353 for key, col in self.iteritems(): 354 if col.key: 355 val = coerce(inputs[key], col.dbtype) 356 pairs.append("%s = %s" % (col.qname, val)) 357 return " AND ".join(pairs) 370 for key in inputs.keys(): 371 if not self[key].key: 372 inputs.pop(key) 373 return self.whereclause(**inputs) 358 374 359 375 def insert(self, **inputs): … … 412 428 (self.qname, ", ".join(parms), self.id_clause(**inputs))) 413 429 self.db.execute(sql, self.db.get_transaction()) 430 431 use_asterisk_to_delete_all = False 432 433 def delete(self, **inputs): 434 """Delete all rows matching the given identifier inputs.""" 435 if self.use_asterisk_to_delete_all: 436 star = " *" 437 else: 438 star = "" 439 self.db.execute('DELETE%s FROM %s WHERE %s;' % 440 (star, self.qname, self.id_clause(**inputs)), 441 self.db.get_transaction()) 442 443 def delete_all(self, **inputs): 444 """Delete all rows matching the given inputs.""" 445 if self.use_asterisk_to_delete_all: 446 star = " *" 447 else: 448 star = "" 449 self.db.execute('DELETE%s FROM %s WHERE %s;' % 450 (star, self.qname, self.whereclause(**inputs)), 451 self.db.get_transaction()) 414 452 415 453 def select_all(self, restriction=None, **kwargs): trunk/geniusql/providers/ado.py
r8 r9 265 265 else: 266 266 try: 267 op1, op2 = self._compare_ imperfect(op1, op2)267 op1, op2 = self._compare_constants(op1, op2) 268 268 except TypeError: 269 269 self.stack.append(geniusql.cannot_represent) … … 1048 1048 class MSAccessTable(ADOTable): 1049 1049 1050 use_asterisk_to_delete_all = True 1051 1050 1052 def _grab_new_ids(self, idkeys, conn): 1051 1053 data, _ = self.db.fetch("SELECT @@IDENTITY;", conn) trunk/geniusql/providers/mysql.py
r8 r9 419 419 "to a Python type." % dbtype) 420 420 421 def isrelatedtype(self, pytype1, pytype2): 422 if pytype1 is float and pytype2 is float: 423 # MySQL provides no reliable method to compare floats in SQL. 424 # Setting this to False will set col.imperfect_type to True, 425 # which will tell the SQL decompiler to mark float comparisons 426 # as imperfect. 427 return False 428 return geniusql.Database.isrelatedtype(self, pytype1, pytype2) 429 421 430 def quote(self, name): 422 431 """Return name, quoted for use in an SQL statement.""" trunk/geniusql/providers/psycopg.py
r8 r9 69 69 value = escape_oct.sub(replace_oct, value) 70 70 return "'" + value + "'" 71 72 def coerce_float_to_REAL(self, value): 73 # Use quotes to restrict the value to single precision, so that 74 # comparisons work between existing values and supplied constants. 75 # See http://archives.postgresql.org/pgsql-bugs/2004-02/msg00062.php 76 return "'%r'" % value 77 coerce_float_to_FLOAT4 = coerce_float_to_REAL 71 78 72 79 trunk/geniusql/providers/pypgsql.py
r8 r9 65 65 value = escape_oct.sub(replace_oct, value) 66 66 return "'" + value + "'" 67 68 def coerce_float_to_REAL(self, value): 69 # Use quotes to restrict the value to single precision, so that 70 # comparisons work between existing values and supplied constants. 71 # See http://archives.postgresql.org/pgsql-bugs/2004-02/msg00062.php 72 return "'%r'" % value 73 coerce_float_to_FLOAT4 = coerce_float_to_REAL 67 74 68 75 trunk/geniusql/select.py
r8 r9 266 266 else: 267 267 try: 268 op1, op2 = self._compare_ imperfect(op1, op2)268 op1, op2 = self._compare_constants(op1, op2) 269 269 except TypeError: 270 270 self.stack.append(cannot_represent) … … 274 274 self.stack.append(op1 + " " + self.sql_cmp_op[op] + " " + op2) 275 275 276 def _compare_imperfect(self, op1, op2): 277 """Cast imperfect column types (or mark imperfect).""" 278 cast = self.adapter.cast 276 def _compare_constants(self, op1, op2): 277 """Coerce/cast compared types (or mark imperfect).""" 279 278 col = getattr(op1, "col", None) 280 279 if col: 281 if col.imperfect_type and isinstance(op2, ConstWrapper): 282 # Try to cast the column to op2's type 283 op1 = cast(op1, col.dbtype, type(op2.basevalue)) 280 if isinstance(op2, ConstWrapper): 281 if col.imperfect_type: 282 # Try to cast the column to op2's type 283 op1 = self.adapter.cast(op1, col.dbtype, 284 type(op2.basevalue)) 285 else: 286 # Try to coerce op2 to the column's type 287 op2 = self.adapter.coerce(op2.basevalue, col.dbtype) 284 288 else: 285 289 col = getattr(op2, "col", None) 286 if col :287 if col.imperfect_type and isinstance(op1, ConstWrapper):290 if col and isinstance(op1, ConstWrapper): 291 if col.imperfect_type: 288 292 # Try to cast the column to op1's type 289 op2 = cast(op2, col.dbtype, type(op1.basevalue)) 293 op2 = self.adapter.cast(op2, col.dbtype, 294 type(op1.basevalue)) 295 else: 296 # Try to coerce op1 to the column's type 297 op1 = self.adapter.coerce(op1.basevalue, col.dbtype) 290 298 return op1, op2 291 299 trunk/geniusql/test/zoo_fixture.py
r8 r9 391 391 # it doesn't preclude running the other tests. 392 392 self.assertEqual(matches(lambda x: "_" in x.Name, 'Zoo'), 1) 393 394 # I noticed this failed on PostgreSQL when testing Table.delete_all. 395 # Granted, not all float comparisons should work perfectly 396 # (and we should mark more of them imperfect), but a straight 397 # comparison with a known INSERTed value should probably work. 398 self.assertEqual(matches(lambda x: x.Lifespan == 103.2), 1) 393 399 394 400 ## def test_5_Aggregates(self): … … 611 617 ## finally: 612 618 ## box.flush_all() 613 ## 614 ## def test_9_arena_views(self): 615 ## # views 616 ## legs = [x[0] for x in arena.view(Animal, ['Legs'])] 617 ## legs.sort() 618 ## self.assertEqual(legs, [1, 2, 2, 2, 2, 2, 4, 4, 4, 4, 100, 1000000]) 619 ## 620 ## expected = {'Leopard': 73.5, 621 ## 'Slug': .75, 622 ## 'Tiger': None, 623 ## 'Lion': None, 624 ## 'Bear': None, 625 ## 'Ostrich': 103.2, 626 ## 'Centipede': None, 627 ## 'Emperor Penguin': None, 628 ## 'Adelie Penguin': None, 629 ## 'Millipede': None, 630 ## 'Ape': None, 631 ## } 632 ## for species, lifespan in arena.view(Animal, ['Species', 'Lifespan']): 633 ## if expected[species] is None: 634 ## self.assertEqual(lifespan, None) 635 ## else: 636 ## self.assertAlmostEqual(expected[species], lifespan, places=5) 637 ## 638 ## expected = [u'Montr\xe9al Biod\xf4me', 'Wild Animal Park'] 639 ## e = (lambda x: x.Founded != None 640 ## and x.Founded <= dejavu.today() 641 ## and x.Founded >= datetime.date(1990, 1, 1)) 642 ## values = [val[0] for val in arena.view(Zoo, ['Name'], e)] 643 ## for name in expected: 644 ## self.assert_(name in values) 645 ## 646 ## # distinct 647 ## legs = arena.distinct(Animal, ['Legs']) 648 ## legs.sort() 649 ## self.assertEqual(legs, [1, 2, 4, 100, 1000000]) 650 ## 651 ## # This may raise a warning on some DB's. 652 ## f = (lambda x: x.Species == 'Lion') 653 ## escapees = arena.distinct(Animal, ['Legs'], f) 654 ## self.assertEqual(escapees, [4]) 655 ## 656 ## # range should return a sorted list 657 ## legs = arena.range(Animal, 'Legs', lambda x: x.Legs <= 100) 658 ## self.assertEqual(legs, range(1, 101)) 659 ## topics = arena.range(Exhibit, 'Name') 660 ## self.assertEqual(topics, ['The Penguin Encounter', 'Tiger River']) 661 ## vets = arena.range(Vet, 'Name') 662 ## self.assertEqual(vets, ['Charles Schroeder', 'Jim McBain']) 663 ## 664 ## def test_Iteration(self): 665 ## box = arena.new_sandbox() 666 ## try: 667 ## # Test box.unit inside of xrecall 668 ## for visit in box.xrecall(Visit, VetID=1): 669 ## firstvisit = box.unit(Visit, VetID=1, Date=Jan_1_2001) 670 ## self.assertEqual(firstvisit.VetID, 1) 671 ## self.assertEqual(visit.VetID, 1) 672 ## 673 ## # Test recall inside of xrecall 674 ## for visit in box.xrecall(Visit, VetID=1): 675 ## f = (lambda x: x.VetID == 1 and x.ID != visit.ID) 676 ## othervisits = box.recall(Visit, f) 677 ## self.assertEqual(len(othervisits), len(every13days) - 1) 678 ## 679 ## # Test far associations inside of xrecall 680 ## for visit in box.xrecall(Visit, VetID=1): 681 ## # visit.Vet is a ToOne association, so will return a unit or None. 682 ## vet = visit.Vet() 683 ## self.assertEqual(vet.ID, 1) 684 ## finally: 685 ## box.flush_all() 686 ## 687 ## def test_Subclassing(self): 688 ## box = arena.new_sandbox() 689 ## try: 690 ## box.memorize(Visit(VetID=21, ZooID=1, AnimalID=1)) 691 ## box.memorize(Visit(VetID=21, ZooID=1, AnimalID=2)) 692 ## box.memorize(Visit(VetID=32, ZooID=1, AnimalID=3)) 693 ## box.memorize(Lecture(VetID=21, ZooID=1, Topic='Cage Cleaning')) 694 ## box.memorize(Lecture(VetID=21, ZooID=1, Topic='Ape Mating Habits')) 695 ## box.memorize(Lecture(VetID=32, ZooID=3, Topic='Your Tiger and Steroids')) 696 ## 697 ## visits = box.recall(Visit, inherit=True, ZooID=1) 698 ## self.assertEqual(len(visits), 5) 699 ## 700 ## box.flush_all() 701 ## 702 ## box = arena.new_sandbox() 703 ## visits = box.recall(Visit, inherit=True, VetID=21) 704 ## self.assertEqual(len(visits), 4) 705 ## cc = [x for x in visits 706 ## if getattr(x, "Topic", None) == "Cage Cleaning"] 707 ## self.assertEqual(len(cc), 1) 708 ## 709 ## # Checking for non-existent attributes in/from subclasses 710 ## # isn't supported yet. 711 ## ## f = logic.filter(AnimalID=2) 712 ## ## self.assertEqual(len(box.recall(Visit, f)), 1) 713 ## ## self.assertEqual(len(box.recall(Lecture, f)), 0) 714 ## finally: 715 ## box.flush_all() 716 ## 717 ## def test_DB_Introspection(self): 718 ## s = arena.stores.values()[0] 719 ## if not hasattr(s, "db"): 720 ## print "not a db (skipped) ", 721 ## return 722 ## 723 ## zootable = s.db['Zoo'] 724 ## cols = zootable 725 ## self.assertEqual(len(cols), 6) 726 ## idcol = cols['ID'] 727 ## self.assertEqual(s.db.python_type(idcol.dbtype), int) 728 ## for prop in Zoo.properties: 729 ## self.assertEqual(cols[prop].key, 730 ## prop in Zoo.identifiers) 731 ## 619 620 def test_9_delete(self): 621 ostrich = db['Animal'].select_one(Species='Ostrich') 622 self.assert_(ostrich is not None) 623 624 db['Animal'].delete(**ostrich) 625 626 ostrich = db['Animal'].select_one(Species='Ostrich') 627 self.assertEqual(ostrich, None) 628 629 # Re-create the ostrich and try deleting it with a non-ID kwarg. 630 db['Animal'].insert(Species='Ostrich', Legs=2, PreviousZoos=[], 631 Lifespan=103.2) 632 ostrich = db['Animal'].select_one(Species='Ostrich') 633 self.assert_(ostrich is not None) 634 635 db['Animal'].delete_all(Species='Ostrich') 636 637 ostrich = db['Animal'].select_one(Species='Ostrich') 638 self.assertEqual(ostrich, None) 639 732 640 ## def test_zzz_Schema_Upgrade(self): 733 641 ## # Must run last.
