C# - Support Sql CRL code in SAFE assembly

[mcenzato]

Antlr4 generated parse tree / listener runs as Sql CLR code (SQL Server CLR Integration) but only with PERMISSION_SET = UNSAFE (I've tested both Antlr4.Runtime.Standard 4.11.1 and Antlr4.Runtime 4.6.6).

As you can imagine it is not a good practice to load UNSAFE assemblies :)

To support SAFE assembly within SQL Server CLR integration both the runtime and the code generation require some changes, apparently quite simple.

This is an initial list of what I have discovered in my tests (I do not trust to make changes on the repository with relative PullRequest because I'm not familiar with the details of the code and risk of doing damage!).

• System.Console => UI not supported in SAFE assemblies, can't be used (#ifdef will be enought)
• static fields/properties must be declared readonly (for what I see and partially tested this is trivial)
• HashSet<T> is not supported (see MayLeakOnAbort ).
  As suggested in the post, normally it would be enought to change all HasSet<T> with a Dictionary<T, Boolean>
• syncronization/threading is not supported:
  1. lock() synchronization must be avoided.
     I don't know if there may be concurrency problems or not with the code executed within Sql Server, I guess not
  2. ConcurrentDictionary<T1,T2> is not supported, changing it to normal Dictionary<T1,T2> can be trivial only if not used in a static field/property

[ericvergnaud]

Hi,Thanks for this.Unfortunately some guesses below are incorrect:- locks are necessary for multi threading, which is critical for some apps - I believe some static fields cannot be readonlyI encourage you to fork the repo to make the changes required for your edge case.Kind regardsEnvoyé de mon iPhoneLe 2 nov. 2022 à 12:30, Marco Cenzato ***@***.***> a écrit : Antlr4 generated parse tree / listener runs as Sql CLR code (SQL Server CLR Integration) but only with PERMISSION_SET = UNSAFE (I've tested both Antlr4.Runtime.Standard 4.11.1 and Antlr4.Runtime 4.6.6). As you can imagine it is not a good practice to load UNSAFE assemblies :) To support SAFE assembly within SQL Server CLR integration both the runtime and the code generation require some changes, apparently quite simple. This is an initial list of what I have discovered in my tests (I do not trust to make changes on the repository with relative PullRequest because I'm not familiar with the details of the code and risk of doing damage!). System.Console => UI not supported in SAFE assemblies, can't be used (#ifdef will be enought) static fields/properties must be declared readonly (for what I see and partially tested this is trivial) HashSet<T> is not supported (see MayLeakOnAbort ). As suggested in the post, normally it would be enought to change all HasSet<T> with a Dictionary<T, Boolean> syncronization/threading is not supported: lock() synchronization must be avoided. I don't know if there may be concurrency problems or not with the code executed within Sql Server, I guess not ConcurrentDictionary<T1,T2> is not supported, changing it to normal Dictionary<T1,T2> can be trivial only if not used in a static field/property —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>