1"""Requirements specific to SQLAlchemy's own unit tests. 2 3 4""" 5 6import sys 7 8from sqlalchemy import exc 9from sqlalchemy.sql import text 10from sqlalchemy.testing import exclusions 11from sqlalchemy.testing.exclusions import against 12from sqlalchemy.testing.exclusions import fails_if 13from sqlalchemy.testing.exclusions import fails_on 14from sqlalchemy.testing.exclusions import fails_on_everything_except 15from sqlalchemy.testing.exclusions import LambdaPredicate 16from sqlalchemy.testing.exclusions import NotPredicate 17from sqlalchemy.testing.exclusions import only_if 18from sqlalchemy.testing.exclusions import only_on 19from sqlalchemy.testing.exclusions import skip_if 20from sqlalchemy.testing.exclusions import SpecPredicate 21from sqlalchemy.testing.exclusions import succeeds_if 22from sqlalchemy.testing.requirements import SuiteRequirements 23 24 25def no_support(db, reason): 26 return SpecPredicate(db, description=reason) 27 28 29def exclude(db, op, spec, description=None): 30 return SpecPredicate(db, op, spec, description=description) 31 32 33class DefaultRequirements(SuiteRequirements): 34 @property 35 def deferrable_or_no_constraints(self): 36 """Target database must support deferrable constraints.""" 37 38 return skip_if( 39 [ 40 no_support("firebird", "not supported by database"), 41 no_support("mysql", "not supported by database"), 42 no_support("mariadb", "not supported by database"), 43 no_support("mssql", "not supported by database"), 44 ] 45 ) 46 47 @property 48 def check_constraints(self): 49 """Target database must support check constraints.""" 50 51 return exclusions.open() 52 53 @property 54 def enforces_check_constraints(self): 55 """Target database must also enforce check constraints.""" 56 57 return self.check_constraints + fails_on( 58 self._mysql_check_constraints_dont_exist, 59 "check constraints don't enforce on MySQL, MariaDB<10.2", 60 ) 61 62 @property 63 def named_constraints(self): 64 """target database must support names for constraints.""" 65 66 return exclusions.open() 67 68 @property 69 def implicitly_named_constraints(self): 70 """target database must apply names to unnamed constraints.""" 71 72 return skip_if([no_support("sqlite", "not supported by database")]) 73 74 @property 75 def foreign_keys(self): 76 """Target database must support foreign keys.""" 77 78 return skip_if(no_support("sqlite", "not supported by database")) 79 80 @property 81 def foreign_key_constraint_name_reflection(self): 82 return fails_if( 83 lambda config: against(config, ["mysql", "mariadb"]) 84 and not self._mysql_80(config) 85 and not self._mariadb_105(config) 86 ) 87 88 @property 89 def table_ddl_if_exists(self): 90 """target platform supports IF NOT EXISTS / IF EXISTS for tables.""" 91 92 return only_on(["postgresql", "mysql", "mariadb", "sqlite"]) 93 94 @property 95 def index_ddl_if_exists(self): 96 """target platform supports IF NOT EXISTS / IF EXISTS for indexes.""" 97 98 # mariadb but not mysql, tested up to mysql 8 99 return only_on(["postgresql", "mariadb", "sqlite"]) 100 101 @property 102 def on_update_cascade(self): 103 """target database must support ON UPDATE..CASCADE behavior in 104 foreign keys.""" 105 106 return skip_if( 107 ["sqlite", "oracle"], 108 "target backend %(doesnt_support)s ON UPDATE CASCADE", 109 ) 110 111 @property 112 def non_updating_cascade(self): 113 """target database must *not* support ON UPDATE..CASCADE behavior in 114 foreign keys.""" 115 116 return fails_on_everything_except("sqlite", "oracle") + skip_if( 117 "mssql" 118 ) 119 120 @property 121 def recursive_fk_cascade(self): 122 """target database must support ON DELETE CASCADE on a self-referential 123 foreign key""" 124 125 return skip_if(["mssql"]) 126 127 @property 128 def deferrable_fks(self): 129 """target database must support deferrable fks""" 130 131 return only_on(["oracle", "postgresql"]) 132 133 @property 134 def foreign_key_constraint_option_reflection_ondelete(self): 135 return only_on( 136 ["postgresql", "mysql", "mariadb", "sqlite", "oracle", "mssql"] 137 ) 138 139 @property 140 def fk_constraint_option_reflection_ondelete_restrict(self): 141 return only_on(["postgresql", "sqlite", self._mysql_80]) 142 143 @property 144 def fk_constraint_option_reflection_ondelete_noaction(self): 145 return only_on(["postgresql", "mysql", "mariadb", "sqlite", "mssql"]) 146 147 @property 148 def foreign_key_constraint_option_reflection_onupdate(self): 149 return only_on(["postgresql", "mysql", "mariadb", "sqlite", "mssql"]) 150 151 @property 152 def fk_constraint_option_reflection_onupdate_restrict(self): 153 return only_on(["postgresql", "sqlite", self._mysql_80]) 154 155 @property 156 def comment_reflection(self): 157 return only_on(["postgresql", "mysql", "mariadb", "oracle"]) 158 159 @property 160 def unbounded_varchar(self): 161 """Target database must support VARCHAR with no length""" 162 163 return skip_if( 164 ["firebird", "oracle", "mysql", "mariadb"], 165 "not supported by database", 166 ) 167 168 @property 169 def boolean_col_expressions(self): 170 """Target database must support boolean expressions as columns""" 171 return skip_if( 172 [ 173 no_support("firebird", "not supported by database"), 174 no_support("oracle", "not supported by database"), 175 no_support("mssql", "not supported by database"), 176 no_support("sybase", "not supported by database"), 177 ] 178 ) 179 180 @property 181 def non_native_boolean_unconstrained(self): 182 """target database is not native boolean and allows arbitrary integers 183 in it's "bool" column""" 184 185 return skip_if( 186 [ 187 LambdaPredicate( 188 lambda config: against(config, "mssql"), 189 "SQL Server drivers / odbc seem to change " 190 "their mind on this", 191 ), 192 LambdaPredicate( 193 lambda config: config.db.dialect.supports_native_boolean, 194 "native boolean dialect", 195 ), 196 ] 197 ) 198 199 @property 200 def standalone_binds(self): 201 """target database/driver supports bound parameters as column expressions 202 without being in the context of a typed column. 203 204 """ 205 return skip_if(["firebird", "mssql+mxodbc"], "not supported by driver") 206 207 @property 208 def qmark_paramstyle(self): 209 return only_on( 210 [ 211 "firebird", 212 "sqlite", 213 "+pyodbc", 214 "+mxodbc", 215 "mysql+oursql", 216 "mariadb+oursql", 217 ] 218 ) 219 220 @property 221 def named_paramstyle(self): 222 return only_on(["sqlite", "oracle+cx_oracle"]) 223 224 @property 225 def format_paramstyle(self): 226 return only_on( 227 [ 228 "mysql+mysqldb", 229 "mysql+pymysql", 230 "mysql+cymysql", 231 "mysql+mysqlconnector", 232 "mariadb+mysqldb", 233 "mariadb+pymysql", 234 "mariadb+cymysql", 235 "mariadb+mysqlconnector", 236 "postgresql+pg8000", 237 ] 238 ) 239 240 @property 241 def pyformat_paramstyle(self): 242 return only_on( 243 [ 244 "postgresql+psycopg2", 245 "postgresql+psycopg2cffi", 246 "postgresql+pypostgresql", 247 "postgresql+pygresql", 248 "mysql+mysqlconnector", 249 "mysql+pymysql", 250 "mysql+cymysql", 251 "mariadb+mysqlconnector", 252 "mariadb+pymysql", 253 "mariadb+cymysql", 254 "mssql+pymssql", 255 ] 256 ) 257 258 @property 259 def no_quoting_special_bind_names(self): 260 """Target database will quote bound parameter names, doesn't support 261 EXPANDING""" 262 263 return skip_if(["oracle"]) 264 265 @property 266 def temporary_tables(self): 267 """target database supports temporary tables""" 268 return skip_if(["firebird", self._sqlite_file_db], "not supported (?)") 269 270 @property 271 def temp_table_reflection(self): 272 return self.temporary_tables 273 274 @property 275 def temp_table_reflect_indexes(self): 276 return skip_if( 277 ["mssql", "firebird", self._sqlite_file_db], "not supported (?)" 278 ) 279 280 @property 281 def reflectable_autoincrement(self): 282 """Target database must support tables that can automatically generate 283 PKs assuming they were reflected. 284 285 this is essentially all the DBs in "identity" plus PostgreSQL, which 286 has SERIAL support. FB and Oracle (and sybase?) require the Sequence 287 to be explicitly added, including if the table was reflected. 288 """ 289 return skip_if( 290 ["firebird", "oracle", "sybase"], "not supported by database" 291 ) 292 293 @property 294 def insert_from_select(self): 295 return skip_if(["firebird"], "crashes for unknown reason") 296 297 @property 298 def fetch_rows_post_commit(self): 299 return skip_if(["firebird"], "not supported") 300 301 @property 302 def non_broken_binary(self): 303 """target DBAPI must work fully with binary values""" 304 305 # see https://github.com/pymssql/pymssql/issues/504 306 return skip_if(["mssql+pymssql"]) 307 308 @property 309 def binary_comparisons(self): 310 """target database/driver can allow BLOB/BINARY fields to be compared 311 against a bound parameter value. 312 """ 313 return skip_if(["oracle", "mssql"], "not supported by database/driver") 314 315 @property 316 def binary_literals(self): 317 """target backend supports simple binary literals, e.g. an 318 expression like:: 319 320 SELECT CAST('foo' AS BINARY) 321 322 Where ``BINARY`` is the type emitted from :class:`.LargeBinary`, 323 e.g. it could be ``BLOB`` or similar. 324 325 Basically fails on Oracle. 326 327 """ 328 # adding mssql here since it doesn't support comparisons either, 329 # have observed generally bad behavior with binary / mssql. 330 331 return skip_if(["oracle", "mssql"], "not supported by database/driver") 332 333 @property 334 def tuple_in(self): 335 def _sqlite_tuple_in(config): 336 return against( 337 config, "sqlite" 338 ) and config.db.dialect.dbapi.sqlite_version_info >= (3, 15, 0) 339 340 return only_on( 341 ["mysql", "mariadb", "postgresql", _sqlite_tuple_in, "oracle"] 342 ) 343 344 @property 345 def tuple_in_w_empty(self): 346 return self.tuple_in + skip_if(["oracle"]) 347 348 @property 349 def independent_cursors(self): 350 """Target must support simultaneous, independent database cursors 351 on a single connection.""" 352 353 return skip_if(["mssql", "mysql", "mariadb"], "no driver support") 354 355 @property 356 def cursor_works_post_rollback(self): 357 """Driver quirk where the cursor.fetchall() will work even if 358 the connection has been rolled back. 359 360 This generally refers to buffered cursors but also seems to work 361 with cx_oracle, for example. 362 363 """ 364 365 return skip_if(["+pyodbc"], "no driver support") 366 367 @property 368 def independent_connections(self): 369 """ 370 Target must support simultaneous, independent database connections. 371 """ 372 373 # This is also true of some configurations of UnixODBC and probably 374 # win32 ODBC as well. 375 return skip_if( 376 [ 377 no_support( 378 "sqlite", 379 "independent connections disabled " 380 "when :memory: connections are used", 381 ), 382 exclude( 383 "mssql", 384 "<", 385 (9, 0, 0), 386 "SQL Server 2005+ is required for " 387 "independent connections", 388 ), 389 ] 390 ) 391 392 @property 393 def memory_process_intensive(self): 394 """Driver is able to handle the memory tests which run in a subprocess 395 and iterate through hundreds of connections 396 397 """ 398 return skip_if( 399 [ 400 no_support("oracle", "Oracle XE usually can't handle these"), 401 no_support("mssql+pyodbc", "MS ODBC drivers struggle"), 402 self._running_on_windows(), 403 ] 404 ) 405 406 @property 407 def updateable_autoincrement_pks(self): 408 """Target must support UPDATE on autoincrement/integer primary key.""" 409 410 return skip_if( 411 ["mssql", "sybase"], "IDENTITY columns can't be updated" 412 ) 413 414 @property 415 def isolation_level(self): 416 return only_on( 417 ("postgresql", "sqlite", "mysql", "mariadb", "mssql", "oracle"), 418 "DBAPI has no isolation level support", 419 ) + fails_on( 420 "postgresql+pypostgresql", 421 "pypostgresql bombs on multiple isolation level calls", 422 ) 423 424 @property 425 def legacy_isolation_level(self): 426 # refers to the engine isolation_level setting 427 return only_on( 428 ("postgresql", "sqlite", "mysql", "mariadb", "mssql"), 429 "DBAPI has no isolation level support", 430 ) + fails_on( 431 "postgresql+pypostgresql", 432 "pypostgresql bombs on multiple isolation level calls", 433 ) 434 435 def get_isolation_levels(self, config): 436 levels = set(config.db.dialect._isolation_lookup) 437 438 if against(config, "sqlite"): 439 default = "SERIALIZABLE" 440 levels.add("AUTOCOMMIT") 441 elif against(config, "postgresql"): 442 default = "READ COMMITTED" 443 levels.add("AUTOCOMMIT") 444 elif against(config, "mysql"): 445 default = "REPEATABLE READ" 446 levels.add("AUTOCOMMIT") 447 elif against(config, "mariadb"): 448 default = "REPEATABLE READ" 449 levels.add("AUTOCOMMIT") 450 elif against(config, "mssql"): 451 default = "READ COMMITTED" 452 levels.add("AUTOCOMMIT") 453 elif against(config, "oracle"): 454 default = "READ COMMITTED" 455 levels.add("AUTOCOMMIT") 456 else: 457 raise NotImplementedError() 458 459 return {"default": default, "supported": levels} 460 461 @property 462 def autocommit(self): 463 """target dialect supports 'AUTOCOMMIT' as an isolation_level""" 464 465 return self.isolation_level + only_if( 466 lambda config: "AUTOCOMMIT" 467 in self.get_isolation_levels(config)["supported"] 468 ) 469 470 @property 471 def row_triggers(self): 472 """Target must support standard statement-running EACH ROW triggers.""" 473 474 return skip_if( 475 [ 476 # no access to same table 477 no_support("mysql", "requires SUPER priv"), 478 no_support("mariadb", "requires SUPER priv"), 479 exclude("mysql", "<", (5, 0, 10), "not supported by database"), 480 ] 481 ) 482 483 @property 484 def sequences_as_server_defaults(self): 485 """Target database must support SEQUENCE as a server side default.""" 486 487 return self.sequences + only_on( 488 ["postgresql", "mariadb", "oracle >= 18"], 489 "doesn't support sequences as a server side default.", 490 ) 491 492 @property 493 def sql_expressions_inserted_as_primary_key(self): 494 return only_if([self.returning, self.sqlite]) 495 496 @property 497 def computed_columns_on_update_returning(self): 498 return self.computed_columns + skip_if("oracle") 499 500 @property 501 def correlated_outer_joins(self): 502 """Target must support an outer join to a subquery which 503 correlates to the parent.""" 504 505 return skip_if( 506 "oracle", 507 'Raises "ORA-01799: a column may not be ' 508 'outer-joined to a subquery"', 509 ) 510 511 @property 512 def multi_table_update(self): 513 return only_on(["mysql", "mariadb"], "Multi table update") 514 515 @property 516 def update_from(self): 517 """Target must support UPDATE..FROM syntax""" 518 519 return only_on( 520 ["postgresql", "mssql", "mysql", "mariadb"], 521 "Backend does not support UPDATE..FROM", 522 ) 523 524 @property 525 def delete_from(self): 526 """Target must support DELETE FROM..FROM or DELETE..USING syntax""" 527 return only_on( 528 ["postgresql", "mssql", "mysql", "mariadb", "sybase"], 529 "Backend does not support DELETE..FROM", 530 ) 531 532 @property 533 def update_where_target_in_subquery(self): 534 """Target must support UPDATE (or DELETE) where the same table is 535 present in a subquery in the WHERE clause. 536 537 This is an ANSI-standard syntax that apparently MySQL can't handle, 538 such as:: 539 540 UPDATE documents SET flag=1 WHERE documents.title IN 541 (SELECT max(documents.title) AS title 542 FROM documents GROUP BY documents.user_id 543 ) 544 545 """ 546 return fails_if( 547 self._mysql_not_mariadb_103, 548 'MySQL error 1093 "Cant specify target table ' 549 'for update in FROM clause", resolved by MariaDB 10.3', 550 ) 551 552 @property 553 def savepoints(self): 554 """Target database must support savepoints.""" 555 556 return skip_if( 557 ["sqlite", "sybase", ("mysql", "<", (5, 0, 3))], 558 "savepoints not supported", 559 ) 560 561 @property 562 def savepoints_w_release(self): 563 return self.savepoints + skip_if( 564 ["oracle", "mssql"], 565 "database doesn't support release of savepoint", 566 ) 567 568 @property 569 def schemas(self): 570 """Target database must support external schemas, and have one 571 named 'test_schema'.""" 572 573 return exclusions.open() 574 575 @property 576 def cross_schema_fk_reflection(self): 577 """target system must support reflection of inter-schema foreign 578 keys""" 579 return only_on(["postgresql", "mysql", "mariadb", "mssql"]) 580 581 @property 582 def implicit_default_schema(self): 583 """target system has a strong concept of 'default' schema that can 584 be referred to implicitly. 585 586 basically, PostgreSQL. 587 588 """ 589 return only_on(["postgresql"]) 590 591 @property 592 def default_schema_name_switch(self): 593 return only_on(["postgresql", "oracle"]) 594 595 @property 596 def unique_constraint_reflection(self): 597 return fails_on_everything_except( 598 "postgresql", "mysql", "mariadb", "sqlite", "oracle" 599 ) 600 601 @property 602 def unique_constraint_reflection_no_index_overlap(self): 603 return ( 604 self.unique_constraint_reflection 605 + skip_if("mysql") 606 + skip_if("mariadb") 607 + skip_if("oracle") 608 ) 609 610 @property 611 def check_constraint_reflection(self): 612 return fails_on_everything_except( 613 "postgresql", 614 "sqlite", 615 "oracle", 616 self._mysql_and_check_constraints_exist, 617 ) 618 619 @property 620 def indexes_with_expressions(self): 621 return only_on(["postgresql", "sqlite>=3.9.0"]) 622 623 @property 624 def temp_table_names(self): 625 """target dialect supports listing of temporary table names""" 626 627 return only_on(["sqlite", "oracle"]) + skip_if(self._sqlite_file_db) 628 629 @property 630 def temporary_views(self): 631 """target database supports temporary views""" 632 return only_on(["sqlite", "postgresql"]) + skip_if( 633 self._sqlite_file_db 634 ) 635 636 @property 637 def table_value_constructor(self): 638 return only_on(["postgresql", "mssql"]) 639 640 @property 641 def update_nowait(self): 642 """Target database must support SELECT...FOR UPDATE NOWAIT""" 643 return skip_if( 644 ["firebird", "mssql", "mysql", "mariadb<10.3", "sqlite", "sybase"], 645 "no FOR UPDATE NOWAIT support", 646 ) 647 648 @property 649 def subqueries(self): 650 """Target database must support subqueries.""" 651 return exclusions.open() 652 653 @property 654 def ctes(self): 655 """Target database supports CTEs""" 656 return only_on( 657 [ 658 lambda config: against(config, "mysql") 659 and ( 660 ( 661 config.db.dialect._is_mariadb 662 and config.db.dialect._mariadb_normalized_version_info 663 >= (10, 2) 664 ) 665 or ( 666 not config.db.dialect._is_mariadb 667 and config.db.dialect.server_version_info >= (8,) 668 ) 669 ), 670 "mariadb>10.2", 671 "postgresql", 672 "mssql", 673 "oracle", 674 "sqlite>=3.8.3", 675 ] 676 ) 677 678 @property 679 def ctes_with_update_delete(self): 680 """target database supports CTES that ride on top of a normal UPDATE 681 or DELETE statement which refers to the CTE in a correlated subquery. 682 683 """ 684 return only_on( 685 [ 686 "postgresql", 687 "mssql", 688 # "oracle" - oracle can do this but SQLAlchemy doesn't support 689 # their syntax yet 690 ] 691 ) 692 693 @property 694 def ctes_on_dml(self): 695 """target database supports CTES which consist of INSERT, UPDATE 696 or DELETE *within* the CTE, e.g. WITH x AS (UPDATE....)""" 697 698 return only_if(["postgresql"]) 699 700 @property 701 def mod_operator_as_percent_sign(self): 702 """target database must use a plain percent '%' as the 'modulus' 703 operator.""" 704 705 return only_if( 706 ["mysql", "mariadb", "sqlite", "postgresql+psycopg2", "mssql"] 707 ) 708 709 @property 710 def intersect(self): 711 """Target database must support INTERSECT or equivalent.""" 712 713 return fails_if( 714 ["firebird", self._mysql_not_mariadb_103, "sybase"], 715 "no support for INTERSECT", 716 ) 717 718 @property 719 def except_(self): 720 """Target database must support EXCEPT or equivalent (i.e. MINUS).""" 721 return fails_if( 722 ["firebird", self._mysql_not_mariadb_103, "sybase"], 723 "no support for EXCEPT", 724 ) 725 726 @property 727 def dupe_order_by_ok(self): 728 """target db wont choke if ORDER BY specifies the same expression 729 more than once 730 731 """ 732 733 return skip_if("mssql") 734 735 @property 736 def order_by_col_from_union(self): 737 """target database supports ordering by a column from a SELECT 738 inside of a UNION 739 740 E.g. (SELECT id, ...) UNION (SELECT id, ...) ORDER BY id 741 742 Fails on SQL Server 743 744 """ 745 return fails_if("mssql") 746 747 @property 748 def parens_in_union_contained_select_w_limit_offset(self): 749 """Target database must support parenthesized SELECT in UNION 750 when LIMIT/OFFSET is specifically present. 751 752 E.g. (SELECT ... LIMIT ..) UNION (SELECT .. OFFSET ..) 753 754 This is known to fail on SQLite. 755 756 """ 757 return fails_if("sqlite") 758 759 @property 760 def parens_in_union_contained_select_wo_limit_offset(self): 761 """Target database must support parenthesized SELECT in UNION 762 when OFFSET/LIMIT is specifically not present. 763 764 E.g. (SELECT ...) UNION (SELECT ..) 765 766 This is known to fail on SQLite. It also fails on Oracle 767 because without LIMIT/OFFSET, there is currently no step that 768 creates an additional subquery. 769 770 """ 771 return fails_if(["sqlite", "oracle"]) 772 773 @property 774 def offset(self): 775 """Target database must support some method of adding OFFSET or 776 equivalent to a result set.""" 777 return fails_if(["sybase"], "no support for OFFSET or equivalent") 778 779 @property 780 def sql_expression_limit_offset(self): 781 return ( 782 fails_if( 783 ["mysql", "mariadb"], 784 "Target backend can't accommodate full expressions in " 785 "OFFSET or LIMIT", 786 ) 787 + self.offset 788 ) 789 790 @property 791 def window_functions(self): 792 return only_if( 793 [ 794 "postgresql>=8.4", 795 "mssql", 796 "oracle", 797 "sqlite>=3.25.0", 798 "mysql>=8", 799 "mariadb>=10.2", 800 ], 801 "Backend does not support window functions", 802 ) 803 804 @property 805 def two_phase_transactions(self): 806 """Target database must support two-phase transactions.""" 807 808 def pg_prepared_transaction(config): 809 if not against(config, "postgresql"): 810 return True 811 812 with config.db.connect() as conn: 813 try: 814 num = conn.scalar( 815 text( 816 "select cast(setting AS integer) from pg_settings " 817 "where name = 'max_prepared_transactions'" 818 ) 819 ) 820 except exc.OperationalError: 821 return False 822 else: 823 return num > 0 824 825 return skip_if( 826 [ 827 no_support("firebird", "no SA implementation"), 828 no_support("mssql", "two-phase xact not supported by drivers"), 829 no_support( 830 "sqlite", "two-phase xact not supported by database" 831 ), 832 no_support( 833 "sybase", "two-phase xact not supported by drivers/SQLA" 834 ), 835 # in Ia3cbbf56d4882fcc7980f90519412f1711fae74d 836 # we are evaluating which modern MySQL / MariaDB versions 837 # can handle two-phase testing without too many problems 838 # no_support( 839 # "mysql", 840 # "recent MySQL communiity editions have too many issues " 841 # "(late 2016), disabling for now", 842 # ), 843 NotPredicate( 844 LambdaPredicate( 845 pg_prepared_transaction, 846 "max_prepared_transactions not available or zero", 847 ) 848 ), 849 ] 850 ) 851 852 @property 853 def two_phase_recovery(self): 854 return self.two_phase_transactions + ( 855 skip_if( 856 ["mysql", "mariadb"], 857 "still can't get recover to work w/ MariaDB / MySQL", 858 ) 859 + skip_if("oracle", "recovery not functional") 860 ) 861 862 @property 863 def views(self): 864 """Target database must support VIEWs.""" 865 866 return skip_if("drizzle", "no VIEW support") 867 868 @property 869 def empty_strings_varchar(self): 870 """ 871 target database can persist/return an empty string with a varchar. 872 """ 873 874 return fails_if( 875 ["oracle"], "oracle converts empty strings to a blank space" 876 ) 877 878 @property 879 def empty_strings_text(self): 880 """target database can persist/return an empty string with an 881 unbounded text.""" 882 883 return fails_if( 884 ["oracle"], "oracle converts empty strings to a blank space" 885 ) 886 887 @property 888 def empty_inserts_executemany(self): 889 # waiting on https://jira.mariadb.org/browse/CONPY-152 890 return skip_if(["mariadb+mariadbconnector"]) + self.empty_inserts 891 892 @property 893 def expressions_against_unbounded_text(self): 894 """target database supports use of an unbounded textual field in a 895 WHERE clause.""" 896 897 return fails_if( 898 ["oracle"], 899 "ORA-00932: inconsistent datatypes: expected - got CLOB", 900 ) 901 902 @property 903 def unicode_data(self): 904 """target drive must support unicode data stored in columns.""" 905 return skip_if([no_support("sybase", "no unicode driver support")]) 906 907 @property 908 def unicode_connections(self): 909 """ 910 Target driver must support some encoding of Unicode across the wire. 911 912 """ 913 return exclusions.open() 914 915 @property 916 def unicode_ddl(self): 917 """Target driver must support some degree of non-ascii symbol names.""" 918 919 return skip_if( 920 [ 921 no_support("sybase", "FIXME: guessing, needs confirmation"), 922 no_support("mssql+pymssql", "no FreeTDS support"), 923 ] 924 ) 925 926 @property 927 def symbol_names_w_double_quote(self): 928 """Target driver can create tables with a name like 'some " table'""" 929 930 return skip_if( 931 [no_support("oracle", "ORA-03001: unimplemented feature")] 932 ) 933 934 @property 935 def emulated_lastrowid(self): 936 """ "target dialect retrieves cursor.lastrowid or an equivalent 937 after an insert() construct executes. 938 """ 939 return fails_on_everything_except( 940 "mysql", 941 "mariadb", 942 "sqlite+aiosqlite", 943 "sqlite+pysqlite", 944 "sqlite+pysqlcipher", 945 "sybase", 946 "mssql", 947 ) 948 949 @property 950 def emulated_lastrowid_even_with_sequences(self): 951 """ "target dialect retrieves cursor.lastrowid or an equivalent 952 after an insert() construct executes, even if the table has a 953 Sequence on it. 954 """ 955 return fails_on_everything_except( 956 "mysql", 957 "mariadb", 958 "sqlite+pysqlite", 959 "sqlite+pysqlcipher", 960 "sybase", 961 ) 962 963 @property 964 def implements_get_lastrowid(self): 965 return skip_if([no_support("sybase", "not supported by database")]) 966 967 @property 968 def dbapi_lastrowid(self): 969 """ "target backend includes a 'lastrowid' accessor on the DBAPI 970 cursor object. 971 972 """ 973 return skip_if("mssql+pymssql", "crashes on pymssql") + only_on( 974 [ 975 "mysql", 976 "mariadb", 977 "sqlite+pysqlite", 978 "sqlite+aiosqlite", 979 "sqlite+pysqlcipher", 980 "mssql", 981 ] 982 ) 983 984 @property 985 def nullsordering(self): 986 """Target backends that support nulls ordering.""" 987 return fails_on_everything_except( 988 "postgresql", "oracle", "firebird", "sqlite >= 3.30.0" 989 ) 990 991 @property 992 def reflects_pk_names(self): 993 """Target driver reflects the name of primary key constraints.""" 994 995 return fails_on_everything_except( 996 "postgresql", "oracle", "mssql", "sybase", "sqlite" 997 ) 998 999 @property 1000 def nested_aggregates(self): 1001 """target database can select an aggregate from a subquery that's 1002 also using an aggregate""" 1003 1004 return skip_if(["mssql", "sqlite"]) 1005 1006 @property 1007 def tuple_valued_builtin_functions(self): 1008 return only_on( 1009 lambda config: self._sqlite_json(config) 1010 or against(config, "postgresql") 1011 ) 1012 1013 @property 1014 def array_type(self): 1015 return only_on( 1016 [ 1017 lambda config: against(config, "postgresql") 1018 and not against(config, "+pg8000") 1019 ] 1020 ) 1021 1022 @property 1023 def json_type(self): 1024 return only_on( 1025 [ 1026 lambda config: against(config, "mysql") 1027 and ( 1028 ( 1029 not config.db.dialect._is_mariadb 1030 and against(config, "mysql >= 5.7") 1031 ) 1032 or ( 1033 config.db.dialect._mariadb_normalized_version_info 1034 >= (10, 2, 7) 1035 ) 1036 ), 1037 "mariadb>=10.2.7", 1038 "postgresql >= 9.3", 1039 self._sqlite_json, 1040 "mssql", 1041 ] 1042 ) 1043 1044 @property 1045 def json_index_supplementary_unicode_element(self): 1046 # for sqlite see https://bugs.python.org/issue38749 1047 return skip_if( 1048 [ 1049 lambda config: against(config, "mysql") 1050 and config.db.dialect._is_mariadb, 1051 "mariadb", 1052 "sqlite", 1053 ] 1054 ) 1055 1056 @property 1057 def legacy_unconditional_json_extract(self): 1058 """Backend has a JSON_EXTRACT or similar function that returns a 1059 valid JSON string in all cases. 1060 1061 Used to test a legacy feature and is not needed. 1062 1063 """ 1064 return self.json_type + only_on( 1065 ["postgresql", "mysql", "mariadb", "sqlite"] 1066 ) 1067 1068 def _sqlite_file_db(self, config): 1069 return against(config, "sqlite") and config.db.dialect._is_url_file_db( 1070 config.db.url 1071 ) 1072 1073 def _sqlite_memory_db(self, config): 1074 return against( 1075 config, "sqlite" 1076 ) and not config.db.dialect._is_url_file_db(config.db.url) 1077 1078 def _sqlite_json(self, config): 1079 if not against(config, "sqlite >= 3.9"): 1080 return False 1081 else: 1082 with config.db.connect() as conn: 1083 try: 1084 return ( 1085 conn.exec_driver_sql( 1086 """select json_extract('{"foo": "bar"}', """ 1087 """'$."foo"')""" 1088 ).scalar() 1089 == "bar" 1090 ) 1091 except exc.DBAPIError: 1092 return False 1093 1094 @property 1095 def sqlite_memory(self): 1096 return only_on(self._sqlite_memory_db) 1097 1098 @property 1099 def reflects_json_type(self): 1100 return only_on( 1101 [ 1102 lambda config: against(config, "mysql >= 5.7") 1103 and not config.db.dialect._is_mariadb, 1104 "postgresql >= 9.3", 1105 "sqlite >= 3.9", 1106 ] 1107 ) 1108 1109 @property 1110 def json_array_indexes(self): 1111 return self.json_type 1112 1113 @property 1114 def datetime_literals(self): 1115 """target dialect supports rendering of a date, time, or datetime as a 1116 literal string, e.g. via the TypeEngine.literal_processor() method. 1117 1118 """ 1119 1120 return fails_on_everything_except("sqlite") 1121 1122 @property 1123 def datetime(self): 1124 """target dialect supports representation of Python 1125 datetime.datetime() objects.""" 1126 1127 return exclusions.open() 1128 1129 @property 1130 def datetime_microseconds(self): 1131 """target dialect supports representation of Python 1132 datetime.datetime() with microsecond objects.""" 1133 1134 return skip_if( 1135 ["mssql", "mysql", "mariadb", "firebird", "oracle", "sybase"] 1136 ) 1137 1138 @property 1139 def timestamp_microseconds(self): 1140 """target dialect supports representation of Python 1141 datetime.datetime() with microsecond objects but only 1142 if TIMESTAMP is used.""" 1143 1144 return only_on(["oracle"]) 1145 1146 @property 1147 def datetime_historic(self): 1148 """target dialect supports representation of Python 1149 datetime.datetime() objects with historic (pre 1900) values.""" 1150 1151 return succeeds_if(["sqlite", "postgresql", "firebird"]) 1152 1153 @property 1154 def date(self): 1155 """target dialect supports representation of Python 1156 datetime.date() objects.""" 1157 1158 return exclusions.open() 1159 1160 @property 1161 def date_coerces_from_datetime(self): 1162 """target dialect accepts a datetime object as the target 1163 of a date column.""" 1164 1165 # does not work as of pyodbc 4.0.22 1166 return fails_on("mysql+mysqlconnector") + skip_if("mssql+pyodbc") 1167 1168 @property 1169 def date_historic(self): 1170 """target dialect supports representation of Python 1171 datetime.datetime() objects with historic (pre 1900) values.""" 1172 1173 return succeeds_if(["sqlite", "postgresql", "firebird"]) 1174 1175 @property 1176 def time(self): 1177 """target dialect supports representation of Python 1178 datetime.time() objects.""" 1179 1180 return skip_if(["oracle"]) 1181 1182 @property 1183 def time_microseconds(self): 1184 """target dialect supports representation of Python 1185 datetime.time() with microsecond objects.""" 1186 1187 return skip_if( 1188 ["mssql", "mysql", "mariadb", "firebird", "oracle", "sybase"] 1189 ) 1190 1191 @property 1192 def precision_numerics_general(self): 1193 """target backend has general support for moderately high-precision 1194 numerics.""" 1195 return exclusions.open() 1196 1197 @property 1198 def precision_numerics_enotation_small(self): 1199 """target backend supports Decimal() objects using E notation 1200 to represent very small values.""" 1201 # NOTE: this exclusion isn't used in current tests. 1202 return exclusions.open() 1203 1204 @property 1205 def precision_numerics_enotation_large(self): 1206 """target backend supports Decimal() objects using E notation 1207 to represent very large values.""" 1208 1209 return fails_if( 1210 [ 1211 ( 1212 "sybase+pyodbc", 1213 None, 1214 None, 1215 "Don't know how do get these values through " 1216 "FreeTDS + Sybase", 1217 ), 1218 ("firebird", None, None, "Precision must be from 1 to 18"), 1219 ] 1220 ) 1221 1222 @property 1223 def precision_numerics_many_significant_digits(self): 1224 """target backend supports values with many digits on both sides, 1225 such as 319438950232418390.273596, 87673.594069654243 1226 1227 """ 1228 1229 def broken_cx_oracle(config): 1230 return ( 1231 against(config, "oracle+cx_oracle") 1232 and config.db.dialect.cx_oracle_ver <= (6, 0, 2) 1233 and config.db.dialect.cx_oracle_ver > (6,) 1234 ) 1235 1236 return fails_if( 1237 [ 1238 ("sqlite", None, None, "TODO"), 1239 ("firebird", None, None, "Precision must be from 1 to 18"), 1240 ("sybase+pysybase", None, None, "TODO"), 1241 ] 1242 ) 1243 1244 @property 1245 def cast_precision_numerics_many_significant_digits(self): 1246 """same as precision_numerics_many_significant_digits but within the 1247 context of a CAST statement (hello MySQL) 1248 1249 """ 1250 return self.precision_numerics_many_significant_digits + fails_if( 1251 "mysql" 1252 ) 1253 1254 @property 1255 def precision_numerics_retains_significant_digits(self): 1256 """A precision numeric type will return empty significant digits, 1257 i.e. a value such as 10.000 will come back in Decimal form with 1258 the .000 maintained.""" 1259 1260 return fails_if( 1261 [ 1262 ("oracle", None, None, "driver doesn't do this automatically"), 1263 ( 1264 "firebird", 1265 None, 1266 None, 1267 "database and/or driver truncates decimal places.", 1268 ), 1269 ] 1270 ) 1271 1272 @property 1273 def infinity_floats(self): 1274 return fails_on_everything_except( 1275 "sqlite", "postgresql+psycopg2", "postgresql+asyncpg" 1276 ) + skip_if( 1277 "postgresql+pg8000", "seems to work on pg14 only, not earlier?" 1278 ) 1279 1280 @property 1281 def precision_generic_float_type(self): 1282 """target backend will return native floating point numbers with at 1283 least seven decimal places when using the generic Float type.""" 1284 1285 return fails_if( 1286 [ 1287 ( 1288 "mysql", 1289 None, 1290 None, 1291 "mysql FLOAT type only returns 4 decimals", 1292 ), 1293 ( 1294 "mariadb", 1295 None, 1296 None, 1297 "mysql FLOAT type only returns 4 decimals", 1298 ), 1299 ( 1300 "firebird", 1301 None, 1302 None, 1303 "firebird FLOAT type isn't high precision", 1304 ), 1305 ] 1306 ) 1307 1308 @property 1309 def floats_to_four_decimals(self): 1310 return fails_if( 1311 [ 1312 ("mysql+oursql", None, None, "Floating point error"), 1313 ("mariadb+oursql", None, None, "Floating point error"), 1314 ( 1315 "firebird", 1316 None, 1317 None, 1318 "Firebird still has FP inaccuracy even " 1319 "with only four decimal places", 1320 ), 1321 ] 1322 ) 1323 1324 @property 1325 def implicit_decimal_binds(self): 1326 """target backend will return a selected Decimal as a Decimal, not 1327 a string. 1328 1329 e.g.:: 1330 1331 expr = decimal.Decimal("15.7563") 1332 1333 value = e.scalar( 1334 select(literal(expr)) 1335 ) 1336 1337 assert value == expr 1338 1339 See :ticket:`4036` 1340 1341 """ 1342 1343 return exclusions.open() 1344 1345 @property 1346 def fetch_null_from_numeric(self): 1347 return skip_if(("mssql+pyodbc", None, None, "crashes due to bug #351")) 1348 1349 @property 1350 def duplicate_key_raises_integrity_error(self): 1351 return exclusions.open() 1352 1353 def _has_pg_extension(self, name): 1354 def check(config): 1355 if not against(config, "postgresql"): 1356 return False 1357 count = ( 1358 config.db.connect(close_with_result=True) 1359 .exec_driver_sql( 1360 "SELECT count(*) FROM pg_extension " 1361 "WHERE extname='%s'" % name 1362 ) 1363 .scalar() 1364 ) 1365 return bool(count) 1366 1367 return only_if(check, "needs %s extension" % name) 1368 1369 @property 1370 def hstore(self): 1371 return self._has_pg_extension("hstore") 1372 1373 @property 1374 def btree_gist(self): 1375 return self._has_pg_extension("btree_gist") 1376 1377 @property 1378 def range_types(self): 1379 def check_range_types(config): 1380 if not against( 1381 config, ["postgresql+psycopg2", "postgresql+psycopg2cffi"] 1382 ): 1383 return False 1384 try: 1385 config.db.connect(close_with_result=True).exec_driver_sql( 1386 "select '[1,2)'::int4range;" 1387 ).scalar() 1388 return True 1389 except Exception: 1390 return False 1391 1392 return only_if(check_range_types) 1393 1394 @property 1395 def async_dialect(self): 1396 """dialect makes use of await_() to invoke operations on the DBAPI.""" 1397 1398 return only_on( 1399 LambdaPredicate( 1400 lambda config: config.db.dialect.is_async, 1401 "Async dialect required", 1402 ) 1403 ) 1404 1405 @property 1406 def oracle_test_dblink(self): 1407 return skip_if( 1408 lambda config: not config.file_config.has_option( 1409 "sqla_testing", "oracle_db_link" 1410 ), 1411 "oracle_db_link option not specified in config", 1412 ) 1413 1414 @property 1415 def postgresql_test_dblink(self): 1416 return skip_if( 1417 lambda config: not config.file_config.has_option( 1418 "sqla_testing", "postgres_test_db_link" 1419 ), 1420 "postgres_test_db_link option not specified in config", 1421 ) 1422 1423 @property 1424 def postgresql_jsonb(self): 1425 return only_on("postgresql >= 9.4") + skip_if( 1426 lambda config: config.db.dialect.driver == "pg8000" 1427 and config.db.dialect._dbapi_version <= (1, 10, 1) 1428 ) 1429 1430 @property 1431 def psycopg2_native_hstore(self): 1432 return self.psycopg2_compatibility 1433 1434 @property 1435 def psycopg2_compatibility(self): 1436 return only_on(["postgresql+psycopg2", "postgresql+psycopg2cffi"]) 1437 1438 @property 1439 def psycopg2_or_pg8000_compatibility(self): 1440 return only_on( 1441 [ 1442 "postgresql+psycopg2", 1443 "postgresql+psycopg2cffi", 1444 "postgresql+pg8000", 1445 ] 1446 ) 1447 1448 @property 1449 def percent_schema_names(self): 1450 return skip_if( 1451 ["mysql+aiomysql", "mariadb+aiomysql"], 1452 "see pr https://github.com/aio-libs/aiomysql/pull/545", 1453 ) 1454 1455 @property 1456 def order_by_label_with_expression(self): 1457 return fails_if( 1458 [ 1459 ( 1460 "firebird", 1461 None, 1462 None, 1463 "kinterbasdb doesn't send full type information", 1464 ), 1465 ("postgresql", None, None, "only simple labels allowed"), 1466 ("sybase", None, None, "only simple labels allowed"), 1467 ("mssql", None, None, "only simple labels allowed"), 1468 ] 1469 ) 1470 1471 def get_order_by_collation(self, config): 1472 lookup = { 1473 # will raise without quoting 1474 "postgresql": "POSIX", 1475 # note MySQL databases need to be created w/ utf8mb4 charset 1476 # for the test suite 1477 "mysql": "utf8mb4_bin", 1478 "mariadb": "utf8mb4_bin", 1479 "sqlite": "NOCASE", 1480 # will raise *with* quoting 1481 "mssql": "Latin1_General_CI_AS", 1482 } 1483 try: 1484 return lookup[config.db.name] 1485 except KeyError: 1486 raise NotImplementedError() 1487 1488 @property 1489 def skip_mysql_on_windows(self): 1490 """Catchall for a large variety of MySQL on Windows failures""" 1491 1492 return skip_if( 1493 self._has_mysql_on_windows, "Not supported on MySQL + Windows" 1494 ) 1495 1496 @property 1497 def mssql_freetds(self): 1498 return only_on(["mssql+pymssql"]) 1499 1500 @property 1501 def legacy_engine(self): 1502 return exclusions.skip_if(lambda config: config.db._is_future) 1503 1504 @property 1505 def ad_hoc_engines(self): 1506 return skip_if(self._sqlite_file_db) 1507 1508 @property 1509 def no_asyncio(self): 1510 def go(config): 1511 return config.db.dialect.is_async 1512 1513 return skip_if(go) 1514 1515 @property 1516 def no_mssql_freetds(self): 1517 return self.mssql_freetds.not_() 1518 1519 @property 1520 def pyodbc_fast_executemany(self): 1521 def has_fastexecutemany(config): 1522 if not against(config, "mssql+pyodbc"): 1523 return False 1524 if config.db.dialect._dbapi_version() < (4, 0, 19): 1525 return False 1526 with config.db.connect() as conn: 1527 drivername = conn.connection.connection.getinfo( 1528 config.db.dialect.dbapi.SQL_DRIVER_NAME 1529 ) 1530 # on linux this is something like 'libmsodbcsql-13.1.so.9.2'. 1531 # on Windows this is something like 'msodbcsql17.dll'. 1532 return "msodbc" in drivername 1533 1534 return only_if( 1535 has_fastexecutemany, "only on pyodbc > 4.0.19 w/ msodbc driver" 1536 ) 1537 1538 @property 1539 def python_fixed_issue_8743(self): 1540 return exclusions.skip_if( 1541 lambda: sys.version_info < (2, 7, 8), 1542 "Python issue 8743 fixed in Python 2.7.8", 1543 ) 1544 1545 @property 1546 def granular_timezone(self): 1547 """the datetime.timezone class, or SQLAlchemy's port, supports 1548 seconds and microseconds. 1549 1550 SQLAlchemy ported the Python 3.7 version for Python 2, so 1551 it passes on that. For Python 3.6 and earlier, it is not supported. 1552 1553 """ 1554 return exclusions.skip_if( 1555 lambda: sys.version_info >= (3,) and sys.version_info < (3, 7) 1556 ) 1557 1558 @property 1559 def selectone(self): 1560 """target driver must support the literal statement 'select 1'""" 1561 return skip_if( 1562 ["oracle", "firebird"], "non-standard SELECT scalar syntax" 1563 ) 1564 1565 @property 1566 def mysql_for_update(self): 1567 return skip_if( 1568 "mysql+mysqlconnector", 1569 "lock-sensitive operations crash on mysqlconnector", 1570 ) 1571 1572 @property 1573 def mysql_fsp(self): 1574 return only_if(["mysql >= 5.6.4", "mariadb"]) 1575 1576 @property 1577 def mysql_fully_case_sensitive(self): 1578 return only_if(self._has_mysql_fully_case_sensitive) 1579 1580 @property 1581 def mysql_zero_date(self): 1582 def check(config): 1583 if not against(config, "mysql"): 1584 return False 1585 1586 row = ( 1587 config.db.connect(close_with_result=True) 1588 .exec_driver_sql("show variables like 'sql_mode'") 1589 .first() 1590 ) 1591 return not row or "NO_ZERO_DATE" not in row[1] 1592 1593 return only_if(check) 1594 1595 @property 1596 def mysql_non_strict(self): 1597 def check(config): 1598 if not against(config, "mysql"): 1599 return False 1600 1601 row = ( 1602 config.db.connect(close_with_result=True) 1603 .exec_driver_sql("show variables like 'sql_mode'") 1604 .first() 1605 ) 1606 return not row or "STRICT_TRANS_TABLES" not in row[1] 1607 1608 return only_if(check) 1609 1610 @property 1611 def mysql_ngram_fulltext(self): 1612 def check(config): 1613 return ( 1614 against(config, "mysql") 1615 and not config.db.dialect._is_mariadb 1616 and config.db.dialect.server_version_info >= (5, 7) 1617 ) 1618 1619 return only_if(check) 1620 1621 def _mysql_80(self, config): 1622 return ( 1623 against(config, "mysql") 1624 and config.db.dialect._is_mysql 1625 and config.db.dialect.server_version_info >= (8,) 1626 ) 1627 1628 def _mariadb_102(self, config): 1629 return ( 1630 against(config, ["mysql", "mariadb"]) 1631 and config.db.dialect._is_mariadb 1632 and config.db.dialect._mariadb_normalized_version_info >= (10, 2) 1633 ) 1634 1635 def _mariadb_105(self, config): 1636 return ( 1637 against(config, ["mysql", "mariadb"]) 1638 and config.db.dialect._is_mariadb 1639 and config.db.dialect._mariadb_normalized_version_info >= (10, 5) 1640 ) 1641 1642 def _mysql_and_check_constraints_exist(self, config): 1643 # 1. we have mysql / mariadb and 1644 # 2. it enforces check constraints 1645 if exclusions.against(config, ["mysql", "mariadb"]): 1646 if config.db.dialect._is_mariadb: 1647 norm_version_info = ( 1648 config.db.dialect._mariadb_normalized_version_info 1649 ) 1650 return norm_version_info >= (10, 2) 1651 else: 1652 norm_version_info = config.db.dialect.server_version_info 1653 return norm_version_info >= (8, 0, 16) 1654 else: 1655 return False 1656 1657 def _mysql_check_constraints_exist(self, config): 1658 # 1. we dont have mysql / mariadb or 1659 # 2. we have mysql / mariadb that enforces check constraints 1660 return not exclusions.against( 1661 config, ["mysql", "mariadb"] 1662 ) or self._mysql_and_check_constraints_exist(config) 1663 1664 def _mysql_check_constraints_dont_exist(self, config): 1665 # 1. we have mysql / mariadb and 1666 # 2. they dont enforce check constraints 1667 return not self._mysql_check_constraints_exist(config) 1668 1669 def _mysql_not_mariadb_102(self, config): 1670 return (against(config, ["mysql", "mariadb"])) and ( 1671 not config.db.dialect._is_mariadb 1672 or config.db.dialect._mariadb_normalized_version_info < (10, 2) 1673 ) 1674 1675 def _mysql_not_mariadb_103(self, config): 1676 return (against(config, ["mysql", "mariadb"])) and ( 1677 not config.db.dialect._is_mariadb 1678 or config.db.dialect._mariadb_normalized_version_info < (10, 3) 1679 ) 1680 1681 def _mysql_not_mariadb_104(self, config): 1682 return (against(config, ["mysql", "mariadb"])) and ( 1683 not config.db.dialect._is_mariadb 1684 or config.db.dialect._mariadb_normalized_version_info < (10, 4) 1685 ) 1686 1687 def _has_mysql_on_windows(self, config): 1688 with config.db.connect() as conn: 1689 return ( 1690 against(config, ["mysql", "mariadb"]) 1691 ) and config.db.dialect._detect_casing(conn) == 1 1692 1693 def _has_mysql_fully_case_sensitive(self, config): 1694 with config.db.connect() as conn: 1695 return ( 1696 against(config, "mysql") 1697 and config.db.dialect._detect_casing(conn) == 0 1698 ) 1699 1700 @property 1701 def postgresql_utf8_server_encoding(self): 1702 def go(config): 1703 if not against(config, "postgresql"): 1704 return False 1705 1706 with config.db.connect() as conn: 1707 enc = conn.exec_driver_sql("show server_encoding").scalar() 1708 return enc.lower() == "utf8" 1709 1710 return only_if(go) 1711 1712 @property 1713 def cxoracle6_or_greater(self): 1714 return only_if( 1715 lambda config: against(config, "oracle+cx_oracle") 1716 and config.db.dialect.cx_oracle_ver >= (6,) 1717 ) 1718 1719 @property 1720 def oracle5x(self): 1721 return only_if( 1722 lambda config: against(config, "oracle+cx_oracle") 1723 and config.db.dialect.cx_oracle_ver < (6,) 1724 ) 1725 1726 @property 1727 def computed_columns(self): 1728 return skip_if(["postgresql < 12", "sqlite < 3.31", "mysql < 5.7"]) 1729 1730 @property 1731 def python_profiling_backend(self): 1732 return only_on([self._sqlite_memory_db]) 1733 1734 @property 1735 def computed_columns_stored(self): 1736 return self.computed_columns + skip_if(["oracle", "firebird"]) 1737 1738 @property 1739 def computed_columns_virtual(self): 1740 return self.computed_columns + skip_if(["postgresql", "firebird"]) 1741 1742 @property 1743 def computed_columns_default_persisted(self): 1744 return self.computed_columns + only_if("postgresql") 1745 1746 @property 1747 def computed_columns_reflect_persisted(self): 1748 return self.computed_columns + skip_if("oracle") 1749 1750 @property 1751 def regexp_match(self): 1752 return only_on(["postgresql", "mysql", "mariadb", "oracle", "sqlite"]) 1753 1754 @property 1755 def regexp_replace(self): 1756 return only_on(["postgresql", "mysql>=8", "mariadb", "oracle"]) 1757 1758 @property 1759 def supports_distinct_on(self): 1760 """If a backend supports the DISTINCT ON in a select""" 1761 return only_if(["postgresql"]) 1762 1763 @property 1764 def supports_for_update_of(self): 1765 return only_if(lambda config: config.db.dialect.supports_for_update_of) 1766 1767 @property 1768 def sequences_in_other_clauses(self): 1769 """sequences allowed in WHERE, GROUP BY, HAVING, etc.""" 1770 return skip_if(["mssql", "oracle"]) 1771 1772 @property 1773 def supports_lastrowid_for_expressions(self): 1774 """cursor.lastrowid works if an explicit SQL expression was used.""" 1775 return only_on(["sqlite", "mysql", "mariadb"]) 1776 1777 @property 1778 def supports_sequence_for_autoincrement_column(self): 1779 """for mssql, autoincrement means IDENTITY, not sequence""" 1780 return skip_if("mssql") 1781 1782 @property 1783 def identity_columns(self): 1784 return only_if(["postgresql >= 10", "oracle >= 12", "mssql"]) 1785 1786 @property 1787 def identity_columns_standard(self): 1788 return self.identity_columns + skip_if("mssql") 1789 1790 @property 1791 def index_reflects_included_columns(self): 1792 return only_on(["postgresql >= 11", "mssql"]) 1793 1794 # mssql>= 11 -> >= MS_2012_VERSION 1795 1796 @property 1797 def fetch_first(self): 1798 return only_on( 1799 ["postgresql", "mssql >= 11", "oracle >= 12", "mariadb >= 10.6"] 1800 ) 1801 1802 @property 1803 def fetch_percent(self): 1804 return only_on(["mssql >= 11", "oracle >= 12"]) 1805 1806 @property 1807 def fetch_ties(self): 1808 return only_on( 1809 [ 1810 "postgresql >= 13", 1811 "mssql >= 11", 1812 "oracle >= 12", 1813 "mariadb >= 10.6", 1814 ] 1815 ) 1816 1817 @property 1818 def fetch_no_order_by(self): 1819 return only_on(["postgresql", "oracle >= 12", "mariadb >= 10.6"]) 1820 1821 @property 1822 def fetch_offset_with_options(self): 1823 # use together with fetch_first 1824 return skip_if("mssql") 1825 1826 @property 1827 def fetch_expression(self): 1828 # use together with fetch_first 1829 return skip_if("mariadb") 1830 1831 @property 1832 def autoincrement_without_sequence(self): 1833 return skip_if("oracle") 1834 1835 @property 1836 def reflect_tables_no_columns(self): 1837 # so far sqlite, mariadb, mysql don't support this 1838 return only_on(["postgresql"]) 1839