SQLite: add additional quote to table name only when it’s not quoted

[hieuk09]

Motivation / Background

In Rails 7.1, when using quoted table name in exec_insert for SQLite, the table name is quoted again, which raises error that the table does not exist. This breaks downstream gem like activerecord-import. This PR aims to fix the issue by avoid quoting when the table name is already quoted.

Detail

I added a test in activerecord/test/cases/adapters/sqlite3/sqlite3_adapter_test.rb to reproduce the issue. When running the below code without the fix, it will raise the below error

• Code:

@conn.exec_insert("insert into \"ex\" (number) VALUES (?)", "SQL", vals)

• Output

ActiveRecord::ConnectionAdapters::SQLite3AdapterTest#test_exec_insert_with_quote:
ActiveRecord::StatementInvalid: Could not find table '"ex"'
    /workspaces/rails/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb:460:in `table_structure'
    /workspaces/rails/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb:270:in `primary_keys'
    /workspaces/rails/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb:146:in `primary_key'
    /workspaces/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:641:in `sql_for_insert'
    /workspaces/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:152:in `exec_insert'
    /workspaces/rails/activerecord/test/cases/adapters/sqlite3/sqlite3_adapter_test.rb:102:in `block in test_exec_insert_with_quote'
    /workspaces/rails/activerecord/test/support/ddl_helper.rb:6:in `with_example_table'
    /workspaces/rails/activerecord/test/cases/adapters/sqlite3/sqlite3_adapter_test.rb:841:in `with_example_table'
    /workspaces/rails/activerecord/test/cases/adapters/sqlite3/sqlite3_adapter_test.rb:100:in `test_exec_insert_with_quote'

Checklist

Before submitting the PR make sure the following are checked:

• This Pull Request is related to one change. Changes that are unrelated should be opened in separate PRs.
• Commit message has a detailed description of what changed and why. If this PR fixes a related issue include it in the commit message. Ex: [Fix #issue-number]
• Tests are added or updated if you fix a bug or add a feature.
• CHANGELOG files are updated for the changed libraries if there is a behavior change or additional feature. Minor bug fixes and documentation changes should not be included.

[skipkayhil]

What do you think about trying to improve extract_table_ref_from_insert_sql to not include the quotes? That seems like a more appropriate place to make this fix

[hieuk09]

That may not work because table name can contain space, which will require the quote.

[byroot]

@hieuk09 Hartley is talking of the extraction method, not the quoting one. I think it's fair for the extraction method to canonicalize the table name, hence removing the quotes.

[hieuk09]

@byroot thanks for the clarification. I forgot that the table name will always be quoted when building the query, so having space in the name is no problem.

I updated the PR to remove quote in the table name, let me know if it works for you.

[byroot]

Thanks, backported to 7-1-stable.