TSqlFragment: common information lost in translation

[ghost]

hi,

i wonder why are common props (named startProps in build_lib.fsx) dropped in translation from original DacFx C# to F#, i'm interested particularly in StartOffset and FragmentLength, but all might be handy (full list https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.transactsql.scriptdom.tsqlfragment?view=sql-dacfx-150#properties )

one of my intentions is to parse some TSQL string and then manipulate that same TSQL string, so i don't loose original formatting

for example: from a script containing create scalar function statement, i would like to create:

• create function statement with same parameter list, without original implementation, just return NULL, no formatting requirements

• alter function statement with implementation, preserving formatting of original input (no re-rendeging, just substring the original text)

so let's say emulation of header/body separation in Oracle

i'm aware of possibility to unwrap parts of AST, replace its parts or append to another AST and let TSQL generator do its job (as in Util.renderCs(frag, opts)), but that breaks original formatting

is there some way how to achieve this with FsSqlDom?

thanks, joe

[isaksky]

Hi Joe,

It wasn't really needed for my use cases, but you are the second person who has asked about it, so I added a way to get that information these 2 commits:

3188e3a
f2baa12

After those commits, you should be able to pass in a Dictionary<obj, ScriptDom.TSqlFragment> to the .FromCs(...) methods, and then you can use it to map back to the C# version, which has that information.

cc @smoothdeveloper

[smoothdeveloper]

@isaksky thanks for going on implementing what we've been exchanging about a couple of times!

I don't remember what were the alternatives, I think we had one idea that would help preserve structural comparison integrity and would be more generic but the solution with external dictionary was a good approach too IIRC.

I'll try to pay a look, I've not had to update the library/generating script or the underlying transact SQL parser so it just slid from my attention.

[smoothdeveloper]

for the second part of the question, SQL Server supports "create or replace" IIRC.

Also, in many simple cases, using the underlying parser library with object expressions result in simpler code than using the library.

[ghost]

hi,

as to the second reply - just playing with tokens is not an option for me, it does not help with first point (make 'create function' without body), and in addition i'll need to create that for all sql modules

"create or alter" came in SQL server 2016 SP1, that's unfortunately a no-go for me

regarding the dictionary solution - it seems to me like two balls juggling, so yes, using object expressions with DacFx is probably better way to go for me

i didn't realize that adding all TSqlFragment props would break structural comparison - that's nice feature to have; would [<CustomComparison; StructuralEquality>] help?

thanks for all answers and for this library, it's inspiring even if i don't use it; btw i'm on Linux with .NET core, so i generate F# wrapper against Microsoft.SqlServer.TransactSql.ScriptDom.dll from SqlPackage for Linux

[smoothdeveloper]

@josef-marec

using object expressions with DacFx is probably better way to go for me

I've updated the readme in #7 it highlights this and also highlights when the DU approach yields lots of value.

i didn't realize that adding all TSqlFragment props would break structural comparison - that's nice feature to have; would [<CustomComparison; StructuralEquality>] help?

I've discussed a work around back then, having a custom type that holds the dictionary and making it comparable with adjustable semantics (maybe through a generic type parameter) PropsWrapper.

Such type could be a member of the DU cases, or the functions returning/taking a tuple, and it would still be structurally comparable, with potential to adjust semantics.

I too feel that keeping a state dictionary around is a bit of juggling, but it was an easy solution @isaksky and I discussed, I think your issue was his call to action to go ahead with it.

[isaksky]

Added my reasoning for the dict instead of embed here:

https://github.com/isaksky/FsSqlDom/blob/master/README.md#design-questions