Yuniql Versions Save

This is a hotfix to reported SNI-related errors when connecting to SqlServer

Error thrown when using installation from choco and downloaded exe file. This is because Microsoft.Data.SqlClient.SNI.dll is excluded when publishing self-contained application.

ERR 2022-05-24 20:55:24Z Failed to execute run function. The type initializer for 'Microsoft.Data.SqlClient.TdsParser' threw an exception. Diagnostics stack trace captured a System.TypeInitializationException: ---> System.TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.SNILoadHandle' threw an exception. ---> System.DllNotFoundException: Dll was not found. at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize(Boolean useSystemDefaultSecureProtocols, IntPtr pmo) at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize()

v1.3.15- See full release note here https://github.com/rdagumampan/yuniql/releases/tag/v1.3.15 v1.3.10- See full release note here https://github.com/rdagumampan/yuniql/releases/tag/v1.3.10

Full Changelog: https://github.com/rdagumampan/yuniql/commits/latest

This is release comes heavy on changes, the .NET 6 LTS upgrade and migration to Microsoft.Data.SqlClient driver for SqlServer. These changes promises better security and performance. And they are both contribution from our community! Enjoy this release. HTH.

Breaking changes

• Due to migration to the new Microsoft driver for SQL Sever, the Encrypted connection is now enabled by default. An error described below will ne thrown in your next migration run targeting SqlServer. To resolve this, you may need to install necessary client certificate. Alternatively, you may use self-signed certificate by adding TrustServerCertificate=True in the connection string or ultimately set Encrypt=False as it is before.

ERR 2022-05-08 12:28:35Z Failed to execute run function. A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) https://weblog.west-wind.com/posts/2021/Dec/07/Connection-Failures-with-MicrosoftDataSqlClient-4-and-later

Take control of script execution order with _sequence.ini

If you have hundreds of scripts in a directory and prefixed the files with a sequence number to control the sequence of execution, it will be very tedious to reorganize the scripts if you need to insert another script file in the middle. In this release, you can place _sequence.ini file in the version directories like v0.00, in the non-version directories like _erase or in the child directories of those directories like v0.00\setup-tables\. This enhancement works in any directories and including all subdirectories. See snipped below and details here #254:

...
INF   2022-03-06 19:41:08Z   Found 5 script files on <redacted>\yuniql_testdb_c7ff7be2\v0.00
  + test_v0_00_01.sql
  + test_v0_00_02.sql
  + test_v0_00_03.sql
  + test_v0_00_04.sql
  + test_v0_00_05.sql

INF   2022-03-06 19:41:08Z   A custom execution sequence manifest is detected. \
Scripts will run as listed in the content of _sequence.ini file. Any scripts not listed in the manifest will be skipped \
and will not be committed in the version where it is placed. Skipped scripts can only be executed \
by moving them to the next version. Expected sequence: 
  + test_v0_00_03.sql
  + test_v0_00_01.sql
  + test_v0_00_05.sql
  + test_v0_00_02.sql
  + test_v0_00_04.sql

Full featured container-based runner using docker run yuniql/cli

In this release, we also published new docker image yuniql/cli. This is a stand alone runner and you can run all known actions such as check, run, list, erase and all others. Using volume attachment, you don't need to build an image before you can run migration.

https://hub.docker.com/repository/docker/yuniql/cli https://hub.docker.com/repository/docker/yuniql/yuniql

docker run --rm -v c:/play/yuniql/samples/basic-postgresql-sample:/data \
yuniql/cli:linux-x64-1.3.10 \
run --platform postgresql -d  \
-c "<your-connection-string>"

docker run --rm yuniql/cli:linux-x64-1.3.10 list --platform postgresql -d  -c "<your-connection-string>"

docker run --rm -v c:/play/yuniql/samples/basic-postgresql-sample:/data \
yuniql/cli:linux-x64-1.3.10 \
erase --platform postgresql -d  \
-c "<your-connection-string>" --force

New features and enhancements

• Upgraded the solution to use .NET 6 LTS version from Net Core 3.1 #223
• Migrated SqlServer driver to use Microsoft.Data.SqlClient #224
• Supports integrated Azure AD authentication in SqlServer #229
• Supports docker run -v <work:space> yuniql/cli:latest #234
• One thing, currently script order is per File Name sort, sorting this way is not intuitive. #193
• Script naming file to specify the order instead of numerical numbering #254
• Display affected record count for every batch of sql statement #257
• Supports --trace-to-file to support negation #260
• Supports for SQL Server 2016 based on user feedback #265
• Created pipelines for continuous testing of docker images #271

Bugs fixes

• A fatal error was encountered. The library 'libhostpolicy.so' required to execute the application was not found in '/app/' #190
• Failed connecting to Azure Sql database #199
• Directory names like v0.00.1, v0.01.1, v0.02.1, v0.03.1 , It was working upto v0.02.1 but when I deployed v0.03.1 it was showing below error. #252
• 'yuniql run' command executed through Azure Pipelines task exposes connection string if it contains an ampersand #259
• A fatal error was encountered. The library 'libhostpolicy.so' required to execute the application was not found in '/app/'. #270

Credits

Special thanks to @pmelander for the migration to Microsoft.Data.SqlClient, @Jean-Paul76 for upgrading to .NET 6 LTS. These are significant contrubutions and certainly one hard to get through easily. Also for everyone who filed issue tickets and asked questions, thanks for all your feedback! HTH

v1.3.2

• New feature from issue #254. Script naming file to specify the order instead of numerical numbering.
• Hotfix for issue #252. Directory names like v0.00.1, v0.01.1, v0.02.1, v0.03.1 , It was working up to v0.02.1 but when I deployed v0.03.1 it was showing below error. If you are using {major}.{minor} version semantics, v1.2.25 should be fine for you.

Every release is special and this is not an exception. This pandemic period presents a rare opportunity to review more PRs, follow-up on reported issues, build prioritized features and fix major bugs. In fact, perhaps this is the release with the most bug-fixes! A testament of our growing usage and feedback from our community. We can only thank you all for your issue tickets and contributions. And here are the highlights:

Multi-tenant database support

The idea behind this popular request is to perform schema versioning on shared database where objects are segmented by schema. This would also enable developers and teams to get dedicated schema version and evolve independently while using single installation. This is more important when teams are not permitted to create and destroy databases during dev/test.

yuniql run -a --meta-schema dev01 --platform sqlserver
yuniql run -a --meta-schema dev02 --platform sqlserver
yuniql run -a --meta-schema dev03 --platform sqlserver

yuniql run -a --meta-schema sales --platform sqlserver
yuniql run -a --meta-schema inventory --platform sqlserver
yuniql run -a --meta-schema manufacturing --platform sqlserver

Support Oracle Enterprise & Autonomous Database

This release comes with previous support for Oracle. The platform tests has been executed using Oracle Enterprise 12C on docker container and Oracle Autonomous Database. You can explore this feature and get started here https://yuniql.io/docs/get-started-oracle/

docker run --rm -dit --name oracle  -p 1521:1521  store/oracle/database-enterprise:12.2.0.1-slim

SETX YUNIQL_PLATFORM "oracle"
SETX YUNIQL_CONNECTION_STRING "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLCDB.localdomain)));User Id=sys;Password=Oradoc_db1;DBA Privilege=SYSDBA;"
SETX YUNIQL_WORKSPACE "C:\temp\yuniql\samples\basic-oracle-sample"

yuniql run --debug
yuniql list --debug
yuniql erase --force --debug

Quick connectivity check with yuniql check

Can I resolve or ping the database server? Can I establish an Sql/Odbc connection to the master or catalog database? Will my connection string work if I run the migrations on this database server? Is my service account have permissions to establish a session? I hope this simple feature helps in diagnosing migration issues quickly.

$ yuniql check -c "Server=DESKTOP-ULR8GDO;Database=helloyuniql;Trusted_Connection=True;" -d --platform sqlserver

INF   2022-02-21 23:23:11Z   Verifying ping connectivity to server/cluster DESKTOP-ULR8GDO...
INF   2022-02-21 23:23:11Z   Ping connectivity to server/cluster DESKTOP-ULR8GDO - Successful
INF   2022-02-21 23:23:11Z   Verifying sql/odbc connectivity to master/catalog on DESKTOP-ULR8GDO...
INF   2022-02-21 23:23:11Z   Sql/odbc connectivity to master/catalog on DESKTOP-ULR8GDO - Successful
INF   2022-02-21 23:23:11Z   Verifying sql/odbc connectivity to database helloyuniql on DESKTOP-ULR8GDO...
INF   2022-02-21 23:23:11Z   Sql/odbc connectivity to database helloyuniql on DESKTOP-ULR8GDO - Successful

Drop your dev/test database with yuniql destroy

Warning: Needless to say, don't use this in production. Just don't. This feature enables testers to rapidly deploy test databases and destroy when the test execution completes. If yuniql erase is not sufficient, yuniql destroy might suffice. It's quite simple, you prepare the destroy scripts yourself and do the necessary handling and sequence to drop the database.

docker run --rm -dit -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=P@ssw0rd!" -p 1400:1433 -d mcr.microsoft.com/mssql/server:2017-latest

SETX YUNIQL_CONNECTION_STRING "Server=localhost,1400;Database=helloyuniql;User Id=SA;Password=P@ssw0rd!"
SETX YUNIQL_WORKSPACE "C:\temp\yuniql\samples\basic-sqlserver-sample"
SETX YUNIQL_PLATFORM "sqlserver" 

yuniql run -a --debug
yuniql list --debug
yuniql destroy --force --debug

Breaking changes

• None

New features and enhancements

• Support multi-tenant database
• Support Oracle platform
• yuniql check: Basic connectivity check to the target database.
• yuniql destroy: Drops database using custom prepared scripts.
• yuniql apply: A synonym for yuniql run to avoid conflict in docker run
• trace-silent: Console window only log and no log files produced.
• trace-sensitive-data: Print secrets and connection strings into console window and log files.
• trace-directory: Set the directory where log files are created.
• Many many bug fixes and code improvements

Bugs fixes

• Directory names like v10.0.0 and higher versions are not taking as latest #249
• Failed database updates should ideally fail with a non-zero exit code #198
• MSSQL/New version: SUSER_SNAME() throw an exception: String or binary data would be truncated. #201
• Providing table name for the schema version table cause error #202
• I am implementing Erease Yuniql and I get the following error This NpgsqlTransaction has completed; it is no longer usable, the process deletes my tables in PostgreSQL (HyperScale) but flag that error. #204
• Error when setting up multi-tenant on shared databases instance #207
• Environment-aware migration not working. #208
• -- environment argument does not work #214
• CSV import towards SQLServer with a uniqueidentifier column #239
• Fixed failing bulk import when destination tables uses Pascal case

Refactorings

• Revise all test scripts to CREATE TABLE instead of CREATE PROC #221
• Run platform tests in single database and not creating database per test case #220
• Why Core is linked to SqlServer ??? #241
• dynamically extract manifest data using reflection https://github.com/rdagumampan/yuniql/commit/74cba91c4ef954d0493052a68a6fd0e8db6441e6

Credits

Special thanks to @bobi591, @wahmedswl, @zwdOr20, @black-eagle17, @automada, @gfnavarro for your significatn contrubutions in this release. Also for everyone filing issue tickets and asking questions, thanks for all your feedback.

This release is very special as its packed with a back story of going through challenges of COVID-19 and support for two major cloud datawarehouse platforms Redshift and Snowflake. In this release, we're also able to support diverse transaction modes so we can run either in transaction for run session, for every version, or for every sql statement. The capability enables us to better track migration failures on databases that do not support full transactional DDL such as MySql and Snowflake.

Thank you and looking forward for continuous feedback and issue tickets. This tool only gets better with community of users sharing their issues and feature requests. Inside the box are…

Breaking changes

• Renamed version tracking table into __yuniql_schema_version. Automatic backup and upgrade of __yuniqldbversion existing tables on your next run.

New features & enhancements

• PREVIEW! Support for Snowflake Cloud DW
• PREVIEW! Support for AWS Redshift Cloud DW
• Sequence number in bulk CSV files ensure right order of CSV files
• Diverse transaction modes per session, per version and per statement
• Requires --require-cleared-draft option on yuniql run
• Richer schema history infromation with duration and error information
• Pretty print run configuration for better debugging
• Dedicated yuniql/yuniql Dockerhub image repository
• New CLI command yuniql platforms
• Major refactoring, code cleanup and better test coverage
• Many many bug fixes, prettier console and code improvements

Credits

Special thanks to @bymse, @TaurahSP, @wcontayon and @DavidHopkinsFbr for your significatn contrubutions in this release. Also for everyone filing issue tickets and asking questions, thanks for all your feedback.

Sneek Peek

Amazon Redshift rapid database deployment and schema versioning with yuniql - preview https://www.youtube.com/watch?v=TUJijZsXP7k

Latest nightly build for v1.1 from master

Key development:

• Support for Snowflake DW (preview) *** NEW!
• Support for AWS Redshift DW (preview) *** NEW!
• Sequence number in bulk csv files to address FK constraints
• Support transaction modes per session, per version, and per-statement
• Requires --require-cleared-draft option on yuniql run
• Push docker images to dedicated yuniql/yuniql Dockerhub account
• New CLI command yuniql platforms
• Major refactoring, code cleanup and better test coverage
• Many many bug fixes and code improvements

See yuniql 2021 roadmap here https://github.com/rdagumampan/yuniql/projects/3 HTH, Rodel

v1.0.1

This is a PRODUCTION RELEASE. After 8 months of active development and continuous engament with our early adapters, yuniql is finally graduating into version 1.0. The release captured general use cases for schema versioning and migration for SQL Server, PostgreSql, MySql and MariaDB. Azure DevOps tasks are also made available in this release for both Windows and Linux hosted agents.

I am very excited with this release and looking forward for continuous user feedback and issues reported. This tool only gets better with community of users sharing their issues and feature requests.

Breaking changes

• Renamed yuniql info into yuniql list #116
• Required --force flag when executing yuniql erase
• When using Azure DevOps Tasks, change all your tasks to use @1 . For example, user UseYUNIQLCLI@1 instead ofUseYUNIQLCLI@0.

New features & enhancements

• Supports Linux Azure DevOps build agents #53
• Supports MariaDB platform #114
• Prettier display applied versions in console #82
• Show the last failed script filename when migration failed #121
• Reduced log trace messages when --debug is off
• Supports --no-transaction flag to disable transactional ddl

Credits

Special thanks to @Jabestrada and @mguoth for significant contributions in this release. Their active contributions over several months drives many significant development in yuniql and other exciting features in vnext releases.

Also, this best build so far thanks for giving feedback and creating issue tickets:

• @kuromukira
• @djshaw01
• @larsoleruben
• @farazfaheem
• @gasystarttask
• @mikependon

Cheers, Rodel

v0.465.0

This is a BETA RELEASE. This is stable build and has so far captured and fixes many defects from early adopters of yuniql. Platform tests and unit tests coverage has been significantly increased in this build.

Breaking changes

• Rename --delimiter parameter into --bulk-separator #84

New features & Enhancements

• Support custom schema and table name of version tracking table #78
• Support for labeling versions (to be closer to semantic versioning) #75
• Remove global transaction for MySql platform #76
• Build more robust sql batch parser #88
• Support --continue-after-failure for MySql
• Support --bulk-batch-size parameter #83

Bug fixes

• Lack of correct exit code hangs Azure Pipelines when yuniql.exe fails #92
• Bulk insert with CSV fails when word NULL is used instead of empty #77
• Scripts _draft directory was skipped when db runs latest version #66
• Arguments filed is not working as expected in Azure Pipeline Extension #73

Credits

Special thanks to @mguoth for significant improvement in yuniql's MySql support. Also, this best build so far thanks for giving feedback and creating issue tickets:

• @kuromukira
• @djshaw01
• @larsoleruben
• @farazfaheem
• @gasystarttask
• @mikependon

Cheers, Rodel

This PREVIEW release fixes some of reported bugs in the metadata table.

Enhancement:

#64 Add ToolVersion in metadata table #63 Support "Verify YUNIQL" Az DevOps Task #62 Support "Erase YUNIQL" Az DevOps Task

Bug fixes:

#65 Missing data in pgsql tracking table bug #60 "Incorrect syntax near '-'" when database name contains