search

rails-sqlserver / activerecord-sqlserver-adapter

SQL Server Adapter For Rails
MIT License
972 stars 559 forks source link

Replace TinyTDS/FreeTDS `defncopy` bin with Cross Platform SqlPackage.exe #437

Open metaskills opened 8 years ago

metaskills commented 8 years ago

Today, we have a tool called sqlpackage.exe which is a .NET command line app that’s built over a .NET library called DacFX and runs on Windows. This tool supports SQL Server on-premise, SQL Server hosted in a VM and also Azure SQL Database in exactly the same way. More info:

Our current thinking was to make sqlpackage.exe (and DacFX) work on coreclr and corefx so they could be used in Ruby on Rails in scenarios such as dumping schema and/or dumping schema + data for a SQL Server database. Cross Platform!

metaskills commented 8 years ago

cc @TigerMint as my point of contact on this.

metaskills commented 7 years ago

Linking https://github.com/Microsoft/mssql-docker/issues/8

metaskills commented 7 years ago

Some notes on playing with the latest docker image which has /opt/mssql/bin/sqlpackage installed. The below commands will eventually use sqlcmd vs sqsh.

Structure Dump

We need an empt database to Script against so we drop the test DB and use that. In reality we would use the database task here too.

$ sqsh -U sa -P $SA_PASSWORD -S localhost -C "DROP DATABASE [myapp_test];"
$ sqsh -U sa -P $SA_PASSWORD -S localhost -C "CREATE DATABASE [myapp_test];"

Now we run the sqlpackage commands. Unfortunately this requires being able to run from the host machine.

$ sqlpackage /a:Extract /q:True \
  /ssn:tcp:127.0.0.1 /sdn:myapp /su:rails \
  /of:True /tf:/opt/mssql/data/structure.dacpac

$ sqlpackage /a:Script \
  /sf:/opt/mssql/data/structure.dacpac \
  /tsn:tcp:127.0.0.1 /tdn:myapp_test /tu:rails \
  /of:True /op:/opt/mssql/data/structure.sql

From here we would need to search and replace the structure file and replace the test database name with the name of the source extract.

-:setvar DatabaseName "myapp_test"
+:setvar DatabaseName "myapp"

Structure Clone/Load

$ sqsh -U sa -P $SA_PASSWORD -S localhost -C "DROP DATABASE [myapp_test];"
$ sqsh -U sa -P $SA_PASSWORD -S localhost -C "CREATE DATABASE [myapp_test];"

$ sqlpackage /a:Extract /q:True \
  /ssn:tcp:127.0.0.1 /sdn:myapp /su:rails \
  /of:True /tf:/opt/mssql/data/structure.dacpac

$ sqlpackage /a:Publish /q:True \
  /sf:/opt/mssql/data/structure.dacpac \
  /tsn:tcp:127.0.0.1 /tdn:myapp_test /tu:rails
metaskills commented 7 years ago

Mac: https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/03/sql-server-command-line-tools-for-mac-preview-now-available/

Linux: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools

metaskills commented 7 years ago

Use new structure_dump_flags and structure_load_flags seen here: https://github.com/rails/rails/blob/5-1-stable/activerecord/lib/active_record/tasks/database_tasks.rb

vkalach commented 1 year ago

@metaskills what is the status of this feature? do you need any help with it? current defncopy does dump not in a really good way if we talk about syntax