Make DBItest tests more consistent

[hadley]

So we're skipping the same core tests on all backends.

And gate behind standard env var so it's easy to turn off if desired.

[hadley]

Some initial exploration

by_database <- list(
  mysql = list(
    getting_started = "package_name",
    driver = "connect_format",
    connection = character(),
    result = c(
      "fetch_n_bad", # TODO
      "fetch_n_good_after_bad", # TODO
      "fetch_n_multi_row_inf", # TODO
      "fetch_no_return_value", # TODO
      "get_query_n_bad", # todo
      "get_query_good_after_bad_n", # todo
      "get_query_n_zero_rows", # todo
      "get_query_n_incomplete", # todo
      "get_query_n_multi_row_inf", # todo
      "fetch_no_return_value", # TODO
      "data_logical($|_.+)", # Not an error, PostgreSQL has a logical data type
      "data_raw.*", # cast(1 bytea) is not valid `cannot cast type integer to bytea`
      "^data_timestamp.*", # MySQL converts the timestamps from local times, so they roundtrip unexpectedly
      "data_character", # Strange MySQL Error only reproducible on travis
      "data_type_create_table", # 2023/6/23: Strange MySQL Error (warning/Result already cleared) only reproducible on GHA
      NULL
    ),
    sql = c(
      "quote_identifier_vectorized", # Can't implement until https://github.com/rstats-db/DBI/issues/71 is closed
      "roundtrip_logical", # Not an error, PostgreSQL has a logical data type
      "roundtrip_timestamp", # We explicitly want to set tzone to UTC regardless of input
      "roundtrip_time",
      "roundtrip_raw", # TODO
      "list_tables",
      ".*_table_name",
      "write_table_error", # TODO
      "roundtrip_character", # Strange MySQL Error only reproducible on travis
      "roundtrip_character_native", # Strange MySQL Error only reproducible on travis
      "roundtrip_factor", # Strange MySQL Error only reproducible on travis
      "unquote_identifier_vectorized",
      "unquote_identifier_roundtrip",
      "unquote_identifier_special",
      "create_table_error",
      "append_roundtrip_.*",
      "roundtrip_64_bit_roundtrip",
      "write_table_row_names_default",
      "remove_table_temporary_arg",
      "remove_table_missing_succeed",
      "remove_table_temporary",
      "list_objects",
      "list_objects_features",
      "list_fields_wrong_table",
      "list_fields_quoted",
      "list_fields_object",
      "list_tables_quote",
      "list_objects_quote",
      NULL
    ),
    meta = c(
      "rows_affected_query", # The MySQL Driver returns 1 affected row
      "rows_affected_statement",
      "row_count_statement",
      "column_info_consistent",
      "bind_.*",
      "has_completed_statement",
      "get_statement_statement",
      NULL
    ),
    transaction = NULL,
    compliance = c(
      "compliance", # We are defining additional subclasses for OdbcConnections
      "reexport",
      NULL
    )
  ),
  postgres = list(
    getting_started = c(
      "package_name", # Not an error
      NULL
    ),
    driver = "connect_format",
    connection = c(
      "clear_result_return_statement",
      "cannot_clear_result_twice_statement",
      NULL
    ),
    result = c(
      "fetch_n_bad", # TODO
      "fetch_n_good_after_bad", # TODO
      "fetch_no_return_value", # TODO
      "get_query_n_bad", # todo
      "get_query_good_after_bad_n", # todo
      "get_query_n_zero_rows", # todo
      "fetch_no_return_value", # TODO
      "data_raw.*", # cast(1 bytea) is not valid `cannot cast type integer to bytea`
      "^data_time$", "^data_time_.*", # `time()` function is not valid syntax
      "^data_timestamp.*", # We explicitly want to set tzone to UTC
      "data_64_bit_numeric_warning", # TODO
      "data_64_bit_lossless", # TODO
      "data_integer", # Fails, unreliably on Windows
      "send_query_syntax_error", # TODO
      "get_query_syntax_error", # TODO
      "send_query_params", # TODO
      "fetch_n_multi_row_inf", # TODO
      "get_query_n_multi_row_inf", # TODO
      "get_query_n_incomplete", # TODO
      "get_query_params", # TODO
      "send_statement_params", # TODO
      "execute_params", # TODO
      NULL
    ),
    sql = c(
      "quote_identifier_vectorized", # Can't implement until https://github.com/rstats-db/DBI/issues/71 is closed
      "quote_identifier_special", # TODO
      "roundtrip_timestamp", # We explicitly want to set tzone to UTC
      "roundtrip_time",
      "roundtrip_raw", # TODO
      "list_tables",
      ".*_table_name",
      "write_table_error", # TODO
      "unquote_identifier_vectorized", # TODO
      "create_table_overwrite", # TODO
      "create_table_error", # TODO
      "create_temporary_table", # TODO
      "append_table_.*", # TODO
      "append_roundtrip_.*", # TODO
      "append_table_.*", # TODO
      "roundtrip_64_bit_roundtrip", # TODO
      "roundtrip_character", # TODO
      "roundtrip_field_types", # TODO
      "write_table_append_incompatible",
      "write_table_row_names_default", # TODO
      "remove_table_temporary_arg", # TODO
      "remove_table_missing_succeed", # TODO
      "remove_table_temporary", # TODO
      "list_objects_features", # TODO
      "list_fields_wrong_table", # TODO
      "list_fields_quoted", # TODO
      "list_fields_object", # TODO
      NULL
    ),
    meta = c(
      "arrow_.*",
      "stream_bind.*",
      "bind_.*", # TODO
      "has_completed_statement",
      "get_statement_statement",
      "column_info_consistent", # TODO
      "row_count_statement", # TODO
      "rows_affected_statement", # TODO
      "rows_affected_query", # TODO
      "get_info_result", # TODO
      NULL
    ),
    transaction =c(
      NULL
    ),
    compliance = c(
      "compliance", # We are defining additional subclasses for OdbcConnections
      "reexport", # TODO
      NULL
    )
  ),
  
  mssql = list(
    getting_started = c(
      "package_name", # Not an error
      NULL
    ),
    driver = c(
      "connect_bigint_integer",
      "connect_bigint_character",
      "connect_bigint_integer64",
      NULL
    ),
    connection = c(
      NULL
    ),
    result = c(
      "get_query_n_zero_rows",
      "get_query_n_incomplete",
      "fetch_no_return_value", # TODO
      "clear_result_return_statement",
      "cannot_clear_result_twice_statement",
      "send_statement.*", # Invalid CTAS syntax
      "execute_atomic", # Invalid CTAS syntax
      "execute_immediate", # Invalid CTAS syntax
      "data_character", # I think the test is bad
      "data_64_bit_numeric_warning", # Test does not explicitly set 64 bit columns
      "data_64_bit_lossless", # Test does not explicitly set 64 bit columns
      "data_date.*", # Date not a builtin function name
      "data_raw.*", # cast(1 bytea) is not valid `cannot cast type integer to bytea`
      "^data_time$", "^data_time_.*", # time objects not supported
      "^data_timestamp.*", # syntax not supported
      NULL
    ),
    sql = c(
      "append_roundtrip_.*", # TODO
      "quote_string_na_is_null", # Invalid syntax
      "remove_table_missing_succeed",
      "roundtrip_character", # #10
      "roundtrip_character_native", # Possible false positive
      "roundtrip_factor", # #10
      "roundtrip_time", # TODO
      "roundtrip_timestamp", # We explicitly want to set tzone to UTC regardless of input
      "write_table_error", # TODO
      "quote_string_roundtrip",
      "quote_literal_roundtrip",
      "quote_literal_na_is_null",
      "quote_literal_na_is_null",
      "create_table_error",
      "create_temporary_table",
      "roundtrip_64_bit_roundtrip",
      "write_table_row_names_default",
      "list_fields_wrong_table",
      "list_fields_quoted",
      "list_fields_object",
      "list_objects_features",
      NULL
    ),
    meta = c(
      "column_info_consistent", # TODO
      "bind_empty",
      "rows_affected_query",
      "rows_affected_statement",
      "has_completed_statement",
      "get_statement_statement",
      "row_count_statement",
      NULL
    ),
    transaction = c(
      NULL
    ),
    compliance = c(
      "compliance", # We are defining additional subclasses for OdbcConnections
      "reexport",
      NULL
    )
  ),
  sqlite = list(
    getting_started = c(
      "package_name", # Not an error
      NULL
    ),
    driver = c(
      "connect_format",
      "connect_bigint_numeric",
      "connect_bigint_character",
      "connect_bigint_integer64",
      NULL
    ),
    connection = c(
      "data_type_connection",
      NULL
    ),
    result = c(
      "data_logical$", # Not an error
      "data_64_bit.*", # TODO
      "data_integer", # These tests are returned as strings by SQLite (bug?)
      "data_raw.*", # cast(1 bytea) is not valid `cannot cast type integer to bytea`
      "^data_time$", "^data_time_.*", # time objects not supported
      "^data_timestamp.*", # SQLite doesn't do timestamps
      "^data_date.*", # SQLite doesn't do dates
      "send_query_params", # TODO
      "fetch_n_bad", # TODO
      "fetch_n_good_after_bad", # TODO
      "fetch_no_return_value", # TODO
      "fetch_n_multi_row_inf", # TODO
      "get_query_n_bad", # TODO
      "get_query_good_after_bad_n", # TODO
      "get_query_n_multi_row_inf", # TODO
      "get_query_n_zero_rows", # TODO
      "get_query_n_incomplete", # TODO
      "get_query_params", # TODO
      "send_statement_params", # TODO
      "execute_params", # TODO
      "data_numeric", # TODO
      "clear_result_return_statement",
      "cannot_clear_result_twice_statement",
      NULL
    ),
    sql = c(
      "quote_identifier_special", # #7
      "roundtrip_timestamp.*", # SQLite doesn't do timestamps
      "roundtrip_date.*", # SQLite doesn't do timestamps
      "roundtrip_logical", # Not an error
      "read_table", # #7
  
      "exists_table_temporary",
      "list_tables_temporary",
      "list_objects_temporary",
      "list_fields_temporary",
  
      # These work locally but fail on travis due to an old SQLite version
      "roundtrip_integer",
      "roundtrip_numeric.*",
      "roundtrip_character",
      "roundtrip_factor",
      "roundtrip_raw",
      "quote_identifier_vectorized", # TODO
      "quote_identifier_string", # TODO
      "unquote_identifier_vectorized", # TODO
      "read_table_empty", # TODO
      "read_table_row_names_na_missing", # TODO
      "create_table_overwrite", # TODO
      "create_table_error", # TODO
      "create_temporary_table", # TODO
      "create_table_visible_in_other_connection", # TODO
      "append_table_missing", # TODO
      "append_table_append_incompatible", # TODO
      "append_roundtrip_.*", # TODO
      "append_table_name", # TODO
      "append_table_row_names_false", # TODO
      "write_table_error", # TODO
      "overwrite_table", # TODO
      "overwrite_table_missing", # TODO
      "append_table", # TODO
      "append_table_new", # TODO
      "temporary_table", # TODO
      "table_visible_in_other_connection", # TODO
      "roundtrip_64_bit_roundtrip", # TODO
      "roundtrip_time", # TODO
      "roundtrip_field_types", # TODO
      "write_table_row_names_.*", # TODO
      "list_tables", # TODO
      "exists_table", # TODO
      "remove_table_temporary_arg", # TODO
      "remove_table_missing_succeed", # TODO
      "remove_table_temporary", # TODO
      "list_objects", # TODO
      "list_objects_features", # TODO
      "list_fields", # TODO
      "list_fields_wrong_table", # TODO
      "list_fields_quoted", # TODO
      "list_fields_object", # TODO
      "exists_table_name",
      "read_table_name",
      "write_table_name",
      "remove_table_name",
      "write_table_append_incompatible",
      NULL
    ),
    meta = c(
      "arrow_.*",
      "stream_bind.*",
      "column_info_consistent", # TODO
      "row_count_statement", # TODO
      "rows_affected_statement", # TODO
      "rows_affected_query", # TODO
      "has_completed_statement",
      "get_statement_statement",
      "bind_.*", # TODO
      NULL
    ),
    transaction = c(
      "begin_write_disconnect",
      NULL
    ),
    compliance = c(
      "reexport", # TODO
      NULL
    )
  )
)

tests <- unique(unlist(lapply(by_database, names)))

by_test <- lapply(setNames(,tests), function(test) lapply(skips, function(x) x[[test]]))


common <- by_test |> lapply(\(x) Reduce(intersect, x))
common

lapply(by_database, \(db) lapply(setNames(, names(db)), \(test) setdiff(db[[test]], common[[test]])))

Random thoughts:

• compliance is package level; we don't need to run it for each driver
• driver is also really package level, and we can probably run it once centrally. Although we'll need to make to run it on a backend that supports int64s.
• There are bunch of SQLite tests that are skipped because version on Travis is too old; can probably remove now.