I have no experience of SQL CLR restrictions, to be honest. If we had a 
complete list of them *and* some automated way of telling whether or not we 
comply with them, it might be feasible... but I don't know how easy that would 
be to do.

To get rid of the BclDateTimeZoneProvider, you could use the PCL version 
instead (or just build with the PCL symbol set). But that will still try to use 
TimeZoneInfo.Local in methods to find the system default time zone. I don't 
know whether it will work if you just don't call those methods.

Any idea how many people would be likely to use this support, if we went ahead 
with it? Is SQLCLR use widespread?

(Don't get me wrong, it's a fun idea to play around with - I'm just dubious 
about the value for the effort of properly supporting it.)

I agree: I don't think we're going to get very far without TimeZoneInfo at all.

(I do note that there's no reason the BCL provider needs to be eager, though, 
but I'll do that separately.)

I know its usage is normally discouraged, but I wonder if the old TimeZone 
class could be of use here?  I checked and it appears that 
TimeZone.CurrentTimeZone.StandardName is identical to TimeZoneInfo.Local.Id.  
It doesn't appear that it changes based on culture or ui culture.  At least for 
the TZDB, when we're calling DateTimeZoneProviders.Tzdb.GetSystemDefault() to 
use for CLDR lookup, it could avoid TimeZoneInfo class.  I'll prototype on some 
of this locally with SQLCLR and see if I get anywhere.  If it works, I'll check 
in to my fork for you to review.

With regards to who might actually use this - I do read a lot of S.O. questions 
about time zone support in databases.  In particular, Oracle and MySql have 
built-in time zone support using TZDB.  SQL Server has nothing - not even 
support for Windows time zones.  I do know people that count on SQLCLR on a 
regular basis.  This would be a great item to have in their toolchest.

I've seen more than a few cases of people storing timezone tables in their 
databases and writing complex logic to work with them - just to find that they 
never update it.  Ever.  They usually oversimplify the problem also, so their 
tables aren't very accurate.  It would be a killer solution to simply use 
NodaTime directly in the database for adding TZDB support to SQL Server.

Probably having some simple SQLCLR UDFs defined in a separate assembly would 
make things even more useful.  So a developer unfamiliar with SQLCLR could 
simply add the assemblies through the SQL Management Studio and get access to a 
variety of useful conversion functions they can start using right away.  
Assuming I can get the basic stuff working, I'd be willing to publish this as a 
separate project.

Okay, it sounds like it's at least worth investigating further. Note that 
StandardName may not be the same as Id in all situations - but we already have 
a mapping for situations where it's not, as Id isn't available on the PCL. I'd 
expect TimeZone.StandardName to be the same as TimeZoneInfo.StandardName.

I'd still want to use TimeZoneInfo.Id in the "normal" build though, I think.

I wonder how feasible it is to run the unit tests within SQL Server, as a good 
way of proving that everything works...

Quite easy actually, thanks to SSDT w/ VS2012:

http://blogs.msdn.com/b/ssdt/archive/2012/12/07/getting-started-with-sql-server-
database-unit-testing-in-ssdt.aspx

Like I said, if we can get over some of the basics of the NodaTime assembly 
working in SQL-CLR (as a "Safe" assembly), then I'll be glad to spin up a 
NodaSQL project separately, unit tests and all.  (NodaSql? NodaTime.SqlServer?  
hmmm...)

Well, the good news is that I was able to get NodaTime working in SQLCLR.

It works as-is when loaded into SQL as an "unsafe" assembly.  But I think we 
can do better than that.

As a "safe" assembly, it gets stuck on the following areas:

- Static fields that should be marked readonly.  (This should probably be done 
anyway):
  - NodaTime.Calendars.IslamicCalendarSystem.Calendars
  - NodaTime.Text.ParseResult.TimeSeparatorMismatch
  - NodaTime.Text.ParseResult.DateSeparatorMismatch
  - NodaTime.Text.ParseResult.MissingNumber
  - NodaTime.Text.ParseResult.UnexpectedNegative

- Assignment of value to static field
  - NodaTime.DateTimeZoneProviders.xmlSerializationProvider
  - NodaTime.TimeZones.BclDateTimeZone.systemDefault

- lock statements

If I remove the xml stuff, skip caching the default system zone, and comment 
out all of the lock statements, then it loads as a "safe" assembly. AND IT 
WORKS!

Of course, that seems a bit scary.  Especially removing the locks.  I'm sure 
they're there for good reason.  But using a lock statement causes an exception 
because it invokes HostProtection with ExternalThreading and Synchronization - 
which only work with fulltrust/unsafe.

Some background:
http://www.codeproject.com/Articles/17855/SQL-Server-CLR-Integration-Part-1-Secu
rity

There's also this:
http://stackoverflow.com/q/1284628/634824

The hack mentioned there to use a [CompilerGenerated] attribute didn't work.  I 
think MS have plugged that hole.

The lock exception messages look like this:

System.Security.HostProtectionException: Attempted to perform an operation that 
was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: Synchronization, ExternalThreading

I suppose I can start looking through the locks one at a time to see if they 
are really necessary or if there are workarounds.

Also, I didn't try accessing the local time zone.  The lazy-load that Malcolm 
put it worked so that TimeZoneInfo didn't load.  I think we could write 
something like BclDateTimeZone.FromTimeZone instead of .FromTimeZoneInfo.

I marked the static fields as readonly in r4cf86a9ff27e, which was easy enough.

However, the "assignment of value to static field" check seems fairly 
unreasonable:
- for xmlSerializationProvider, it's true that this is mutable static state, 
but there isn't a reasonable way to avoid that given XML serialisation.
- for BclDateTimeZone.systemDefault, it's a mutable cache as an optimisation. 
I'm not sure why this would be unreasonable.

Likewise, I don't see that we can realistically avoid some of the lock 
statements.


Backing up a bit: how useful is NodaTime as an unsafe assembly?  Would that in 
practice make it less usable, or would it just be better if we could mark it as 
safe? (Sorry, it's been a while since I've done anything with SQLCLR, and it's 
probably changed since then anyway.)

This is my first venture into SQLCLR also, so I'm probably not the best one to 
talk about best practices are.  But the number of hoops you have to jump 
through to use an unsafe assembly are discouraging to say the least.  And the 
documentation at every step along the way tries to talk you out of it.  So 
while it might be functional, I don't think it's a good idea for a 3rd party 
library like NodaTime to run unsafe.  Certainly, it will get much more 
real-world use if it can run as a safe assembly.

I'm going to proceed cautiously, with a separate build configuration and a 
SQLCLR conditional compilation flag (like we have the PCL build).  We can 
always undo it later if I uncover any optimizations that make sense outside of 
SQLCLR.

I'm a bit unsure of what the xml stuff is used for anyway, and why it needs to 
be mutable.  Can I just skip this for the SQLCLR build?

Regarding BclDateTimeZone.systemDefault - It would seem that SQLCLR doesn't 
want you to do any of your own internal caching.  I can bypass the cache for 
the SQLCLR build.  But I wonder if there is some other mechanism that it would 
prefer instead.  I'll have to do some more research.

It appears most of the lock statements we have are also part of caching logic.  
Surely there is some recommended way to handle caching safely in SQLCLR.  If I 
can figure out what mechanism they prefer, I can probably work around the 
existing locks.

On a hunch, I checked to see if ConcurrentDictionary is allowed - but no, it 
has the same problems as a lock.

http://stackoverflow.com/q/16303557/634824

Okay, I *think* I'd like to support this as another build target of Noda Time 
if possible - while forking would obviously work, it would cause more work in 
the long run. It sounds like we may even want two different build 
configurations - an "unsafe" one which still does caching, and a "safe" one 
which is slower due to not caching at all.

Most of the caching should be relatively easy to opt out of. One problem I can 
foresee is DateTimeZoneCache. The obvious fix here is to make that *eagerly* 
load all the time zones, before construction completes. That way we never need 
to lock, but we still get all the caching behaviour.

How important is performance in SQLCLR likely to be? The cache can make a huge 
difference in time zone calculations. For the Gregorian calendar up-front cache 
for 1900-2100 (year and month starts) probably means the BasicCalendar cache 
becomes a lot less relevant.

Are you doing this for work (where it's presumably relatively urgent) or would 
it be okay to defer this for now, and then take it up seriously post-1.2? (We 
could put it on the roadmap either before, after or coincident with the CLDR 
work.)

As you may have seen by the responses to my S.O. question, there doesn't appear 
to be a "safe" way to do thread-safe caching in a SQLCLR object.  It seems to 
want to be completely single-threaded.

Since the normal build works fine in unsafe mode as-is, I don't think we need a 
build target for that.  It would just be the one new target that doesn't do any 
caching, and therefore need no locks.  I also like your idea about eagerly 
loading the zone data.  It would have to go into a readonly static field.  I 
think this would be just fine for something like SQL Server.

It's not super important for work - no.  It can wait.  I'm thinking more about 
usefulness to others than to myself.  I'd love to see NodaTime everywhere.  I'm 
more into RavenDB than SQL Server these days anyway. :)

I'd love to help though.  I can certainly at least do all of the SQL specific 
parts.  The goal being to have ready-to-use functions that make sense to a SQL 
developer.

Sorry if it is a separate issue, but I stumbled upon this ticket searching for 
why DateTimeZoneProviders.Tzdb.GetSystemDefault on PCL build throws 
DateTimeZoneNotFoundException when the display language (this is on WP8) is set 
to non-English. Digging into the source, I found that on PCL, the library uses 
TimeZoneInfo.StandardName instead of normally TimeZoneInfo.Id for looking up 
the mapping. This apparently, is a localised name 
(http://msdn.microsoft.com/en-GB/library/system.timezoneinfo.standardname(v=vs.9
5).aspx) so this would not work properly. Eg. "Westeuropäische Zeit" instead 
of "GMT Standard Time"  Or is this a known issue?

@wiyono.aten: Ah, humbug. That's a real pain. Could you file that as a separate 
bug? (It's not really related to this one.)

I'll have to see what I can do about it - I'm sure I've seen other times where 
changing culture *doesn't* change the result of StandardName, so it's quite 
possible that it's dependent on *exactly* how the culture is changed. I can see 
this being a pain to fix, but I'll do what I can.

Yes it is unfortunately, as we just found this out quite late in our 
development cycle at the moment :) I have filed a separate issue now 
https://code.google.com/p/noda-time/issues/detail?id=221. I can see the fix can 
be real pain, so thanks!

Just an update, I may be able to complete this for the 1.2 milestone.  I've 
learned a lot about SQLCLR and have most of it done (locally).  I'll be 
publishing to my fork soon for review.

The only real strangeness is a few of the existing lock statements now look 
like this:

#if !SQL
lock (foo)
#endif
{
  //...
}

While this looks a bit scary (intentionally ignoring a lock), it turns out that 
SQLCLR calls are always single-threaded anyway.  So the lock is extraneous.

Of course we'll want to do some concurrency testing just to make sure all holds 
up.  But I think it will be good.

I think it will be a very nice story to say that Noda Time 1.2 works with SQL 
Server.  I also want to prove that it will work with EF/NH/L2S so people can 
start using Noda Time types in their data models.

I'd rather not make this change before 1.2, to be honest - it sounds like a 
large change to put in *just* before a release, especially as we've still got 
some build/packaging changes. I'd be very happy to punt the future releases so 
that 1.3 could be purely SQLCLR, so that it doesn't need to be too far out. 
Does that sound reasonable?

(I'm surprised about the threading part - do you have any links so I could read 
more about that?)

That sounds just fine.  That gives more time for testing and documentation as 
well.

Regarding the threading model, I'll see if I can dig up some links.  I've 
already forgotten where I read that.

Duly set as milestone 1.3 :)

Will update the roadmap later. Depending on how much you need to do and how far 
I get in my thinking, we could try to fit the "out of bounds" handling into 
that release too, or punt the latter to 1.4 (with CLDR).

This presentation was one of the most informative I could find:
http://www.sqlbits.com/Sessions/Event7/SQL_CLR_Demystified
A few things relevant to threading at:
- Slide 6, Video at 05:26
- Slide 54, Video at 45:27

Some MSDN docs here:
http://msdn.microsoft.com/en-us/library/ms131047.aspx

I'm not sure "single threaded" is the right terminology.  It does have the 
ability to work with multiple threads, although any thread management is 
specifically prohibited in "SAFE" assemblies.  The docs talk about how SQL's 
threading model is significantly different than the CLR, so any threading 
should be avoided.

I guess what I'm most interested here is in whether SQL queues up concurrent 
calls to CLR objects or if they might run in parallel and potentially cause a 
collision on our caches.  I have more research and testing to do.

Great, thanks - will have to look all of that up. The locking in 1.2 has moved 
around a bit. If you can do without text processing, that would get rid of a 
lot of it :)

I think I've worked out a way of avoiding the lock for YearMonthDayCalculator, 
by cunningly storing all the relevant information in a single int. It'll take a 
bit of work to implement it and then compare the performance, but I have some 
hopes that going lock-free could be good...

Dear All,

do you have any schedule/plan on when 1.3 with sqlclr support will ship? (and 
if you've actually decided to include SQLCLR support in 1.3?)

@Ivan: I believe Matt no longer has personal use of the SQLCLR stuff, so may 
not have worked on it for a while. I'm hoping to ship v1.3 "soonish" with a 
collection of minor features. If the SQLCLR code is nearly ready to merge in, 
I'd be happy for it to go in. Over to Matt :)

Looks like I have some work to do. :)

It will probably be a few weeks, but I think it is possible.

I do have some working prototype code from last year, but it needs to be 
refreshed before it's ready for consumption.

@John, @Matt, thanks for your quick replies. @Matt, please, let me know if 
there's anything I can help you with.