Skip to Content

mvSQL

Overview

mvSQL is Vinyl's own dialect of SQL. It allows users to learn and use a single set of syntax and functions, while Vinyl translates that syntax to vendor appropriate expressions. mvSQL can be used to query relational database, storage, REST, file systems, or any other kind of data provider Vinyl supports.

mvSQL Versions

mvSQL versions supported by Vinyl are listed with the oldest supported version appearing first (Version 1).

Version 1

The oldest legacy version Vinyl supports, Version 1 mvSQL is even stricter than we allowed before refactoring the grammar and parser. Most legacy Vinyl apps should run using this version.

Version 2

Version 2 mvSQL improves on pass-thru syntax, supports the ability to close it and resume regular mvSQL expressions, and supports escaping characters.

Example

Version 1 would see this (in MS SQL Server) ${ [vendor syntax] } || 'my example' as ${ [vendor syntax] || 'my example' } rendering [vendor syntax] || 'my example'.

Version 2 would parse as it was meant to be, and render as [vendor syntax] + 'my example'. Note the concat being vendor appropriate).

Version 3

Version 3 mvSQL requires table prefixes to be correct. Up to this version, if the table prefix was incorrect, and Vinyl could infer it by checking the other columns, we would allow incorrect table prefixes.

Example

In previous versions of mvSQL, using a table alias that doesn't exist, or even one that does but that doesn't have the referenced column, would still work as long as that column name only showed up in one of the sources. This scenario presents an issue when that same column name is added to another source and the business rule stops working.

Version 4

In version 4 mvSQL, Runtime functions output a proper type, instead of always being a string, and null values are not coalesced into an empty string.

Example

Shared(ColumnName, numeric)

  • Prior to version 4, Vinyl would either output '0' if the column wasn't provided, or '1' if it was (note that is rendered as a string, assuming the shared value here is 1 of course)
  • Beginning in version 4, Vinyl now outputs null if it wasn't provided, and 1 (without the quotes), we also parametrized if the vendor supports it (@p0), as this can leverage better performance from the vendor.
  • This also means it's easier to coalesce the value, before if you wanted to change the value if it wasn't provided you would need something like IIF(Shared(ColumnName) = '', -1, Shared(ColumnName, numeric)), now you can use ISNULL(Shared(ColumnName, numeric), -1)
  • New rules are created using the highest available version (version 4 in Vinyl 3.3), but to avoid breaking legacy rules, we kept them as they were. If changes are made to a rule that won't be affected by this (mainly rules without runtime functions) Vinyl will auto update the mvSQL version.

Resources