search

rails-sqlserver / activerecord-sqlserver-adapter

SQL Server Adapter For Rails
MIT License
968 stars 558 forks source link

Support for structure.sql seems to be broken #944

Open gucki opened 2 years ago

gucki commented 2 years ago

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:

aidanharan commented 2 years ago

The "Column names are not quoted" issue is caused by defncopy from FreeTDS and is already opened as https://github.com/FreeTDS/freetds/pull/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 commented 2 years ago

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

Michoels commented 3 months ago

Still having this issue in 2024. https://github.com/FreeTDS/freetds/pull/276 was never merged.

Is there any workaround for using structure.sql?

jonahgeorge commented 3 months ago

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 commented 3 months ago

Fixed in FreeTDS 1.4.12 and master branch

Michoels commented 3 months ago

@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 commented 3 months ago

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 commented 2 months ago

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 commented 1 month ago

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

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/f38562bbf85dfcff5c6c7cb32ecb7a80251efbe7/lib/active_record/tasks/sqlserver_database_tasks.rb#L72-L73

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

Relates to: