Support for structure.sql seems to be broken

[gucki]

activerecord-sqlserver-adapter (6.1.0.0)
tiny_tds (2.1.5)

Compile-time settings (established with the "configure" script)
Version: freetds v1.2.18
freetds.conf directory: /etc
MS db-lib source compatibility: yes
Sybase binary compatibility: no
Thread safety: yes
iconv library: yes
TDS version: auto
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: no
OpenSSL: yes
GnuTLS: no
MARS: yes

The structure.sql (config.active_record.schema_format = :sql) generated can't be imported (rails db:setup) again. Bugs noticed so far:

• Column names are not quoted. For example the column "key" of "ar_internal_metadata" causes: ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near the keyword 'key'.
• Views are not supported. Import fails with: ActiveRecord::StatementInvalid: TinyTds::Error: 'CREATE VIEW' must be the first statement in a query batch.

[aidanharan]

The "Column names are not quoted" issue is caused by defncopy from FreeTDS and is already opened as FreeTDS/freetds#276

To demonstrate the issue do the following. Create a table with column name key (which Rails does in the ar_internal_metadata table).

CREATE TABLE dbo.table_with_column_named_key
( 
  [key]        nvarchar(4000)  NOT NULL
)

When you run "rails db:migrate" TinyTDS calls defncopy with a command similar to:

defncopy -S localhost -D [DATABASE NAME] -U [USERNAME] -P [PASSWORD] -o structure.sql table_with_column_named_key

If you open structure.sql it will contain:

CREATE TABLE dbo.table_with_column_named_key
	( key nvarchar(8000)  NOT NULL
	)
GO

If you run this manually in SQL Server you get the "Incorrect syntax near the keyword 'key'." error message. This issue would be fixed if "key" was wrapped in square brackets like "[key]" as "key" is a keyword in SQL Server.

[aidanharan]

I'm unable to recreate the "Views are not supported" issue. Could you provide steps on how to recreate the issue?

[Michoels]

Still having this issue in 2024.
FreeTDS/freetds#276 was never merged.

Is there any workaround for using structure.sql?

[jonahgeorge]

Still having this issue in 2024. FreeTDS/freetds#276 was never merged.

Is there any workaround for using structure.sql?

@Michoels my comment here is where I last left my investigation-- unfortunately I won't have anytime soon to continue looking into this

[freddy77]

Fixed in FreeTDS 1.4.12 and master branch

[Michoels]

@aidanharan will a new release of activerecord-sqlserver-adapter be required to support FreeTDS 1.4.12?
Or do I just need to upgrade my local FreeTDS and the current version will work OK?

And thanks so much to @freddy77, I'm looking forward to testing this!

[aidanharan]

I don't think a new release of the adapter is required, however I do not have the time to confirm this. Could you perform some testing and let us know your results?

[Michoels]

We're now using FreeTDS 1.4.12 in production with the current release of activerecord-sqlserver-adapter.

It's working well with no issues 🥳

Need to confirm if the OP issue is now fixed.

[jonahgeorge]

It would seem for databases that have views rails db:schema:load still fails:

activerecord-sqlserver-adapter/lib/active_record/tasks/sqlserver_database_tasks.rb

Lines 72 to 73 in f38562b

	view_args = lease_connection.views.map { |v| Shellwords.escape(v) }
	command.concat(view_args)

ActiveRecord::StatementInvalid: TinyTds::Error: 'CREATE VIEW' must be the first statement in a query batch. (ActiveRecord::StatementInvalid)

Relates to:

• support views in structure_load #956