jeffshumphreys / mysqlclrfunctions Goto Github PK

Not a bug per se, but if it keeps blowing memory because of it, then it's a bug.

Try SqlChar and alternate named functions like "Left{100}C" Ugh!!!!! bizarro function names!

Not SubString4K for heaven's sake.

And I want a variant whereby VARCHAR(100) is used. Maybe even the default, which is another breaking change, but is NVARCHAR really the standard? Over double the space for ASCII?
Now "FirstName" is NVARCHAR of course. That is also another reason to have hyper-specific naming.

https://nielsberglund.com/2017/07/23/sql-server-2017-sqlclr---whitelisting-assemblies/

https://nielsberglund.com/2017/07/02/sql-server-2017-sqlclr-and-permissions/

"Microsoft introduced an sp_configure option called clr strict security, which by default is set to 1 (on). When the setting is on, SQL Server treats all assemblies (SAFE, EXTERNAL_ACCESS, UNSAFE) as if they were marked UNSAFE."

sp_add_trusted_assembly
[ @hash = ] 'value'
[ , [ @description = ] 'description' ]

"The slightly interesting thing with this is that even when you mark an assembly as trusted, it still has to be created with the correct permission set."

"The security choices you now have for an assembly in SQL Server 2017 are:

The database is set to be TRUSTWORTHY, OR
The assembly is signed with a certificate that has a corresponding login with UNSAFE ASSEMBLY permission, OR
The assembly is signed with an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, OR
The assembly is marked as trustworthy by the sys.sp_add_trusted_assembly procedure."

https://nielsberglund.com/2017/07/01/sqlclr-and-certificates/

Describe the bug
They don't raise an error, but they make no sound. Console.Beep may be blocked somehow, especially under "runas"

Probably will switch to playing files.

Right now SQLCLR_Register.sql is manually maintained, which was necessary to learn all the variations. But this can be generated from a scan of the functions in the code with a [SqlFunction] attribute. Not sure how to do, though.

https://github.com/Humanizr/Humanizer

Massive functionality, and MIT as well.

"Humanizer meets all your .NET needs for manipulating and displaying strings, enums, dates, times, timespans, numbers and quantities. It is part of the .NET Foundation, and operates under their code of conduct. It is licensed under the MIT (an OSI approved license)."

ByteSize.Parse("1.55 gB");
var b = (10.505).Kilobytes();
(1024).Gigabytes().ToString(); // 1 TB
maxFileSize.LargestWholeNumberSymbol; // "KB"
b.Humanize("#.## B"); // 10757.12 B
(1000).Kilobytes().ToFullWords(); // 1000 kilobytes
(4.7).Terabytes();
3.Bits();
var fileSize = (10).Kilobytes();
1.ToRoman() => "I"
new DateTime(2015, 1, 1).ToOrdinalWords() => "January 1st, 2015"
new DateTime(2015, 2, 12).ToOrdinalWords() => "February 12th, 2015"
new DateTime(2015, 3, 22).ToOrdinalWords() => "March 22nd, 2015"
10.ToOrdinalWords(new CultureInfo("en-US")) => "tenth"
5.Ordinalize() => "5th"
121.ToOrdinalWords() => "hundred and twenty first"
3501.ToWords() => "three thousand five hundred and one"
3.Hundreds().Thousands() => 300000
1.25.Billions() => 1250000000
3.Hundreds().Thousands() => 300000
someDateTime.At(2, 20, 15)
On.November.The13th.In(2010).AtNoon + 5.Minutes()
In.One.Second // DateTime.UtcNow.AddSeconds(1);
In.TheYear(2010) // Returns the first of January of 2010
In.FebruaryOf(2009) // Returns 1st of February of 2009
In.Two.SecondsFrom(DateTime dateTime)
DateTime.Now + 2.Days() + 3.Hours() - 5.Minutes()
2.Hours() => TimeSpan.FromHours(2)
"SomeTitle".Underscore() => "some_title"
"some_title for something".Camelize() => "someTitleForSomething"
"case".ToQuantity(5, ShowQuantityAs.Words) => "five cases"
"case".ToQuantity(5, ShowQuantityAs.None) => "cases"
"man".ToQuantity(1) => "1 man"
"man".ToQuantity(2) => "2 men"
Vocabularies.Default.AddSingular("(vert|ind)ices$", "$1ex");
Vocabularies.Default.AddIrregular("person", "people");
Vocabularies.Default.AddUncountable("fish");
Vocabularies.Default.AddPlural("bus", "buses");
collection.Humanize() // "Specific String, Specific String, and Specific String"
collection.Humanize(sc => sc.SomeInt.Ordinalize(), "or") // "1st, 2nd, or 3rd"
TimeSpan.FromMilliseconds(1299630020).Humanize(3, toWords: true) => "two weeks, one day, one hour"
DateTime.UtcNow.AddHours(-30).Humanize() => "yesterday"
DateTime.UtcNow.AddHours(-2).Humanize() => "2 hours ago"
DateTime.UtcNow.AddHours(30).Humanize() => "tomorrow"
DateTime.UtcNow.AddHours(2).Humanize() => "2 hours from now"
DateTimeOffset.UtcNow.AddHours(1).Humanize() => "an hour from now"
"To be formatted -> {0}/{1}.".FormatWith(1, "A") => "To be formatted -> 1/A."
"Long text to truncate".Truncate(2, Truncator.FixedNumberOfWords) => "Long text…"
"Sentence casing".Transform(To.LowerCase) => "sentence casing"
"Sentence casing".Transform(To.SentenceCase) => "Sentence casing"
"Sentence casing".Transform(To.TitleCase) => "Sentence Casing"
"Sentence casing".Transform(To.UpperCase) => "SENTENCE CASING"
"Pascal case input string is turned into sentence".Dehumanize() => "PascalCaseInputStringIsTurnedIntoSentence"
"Underscored_input_String_is_turned_INTO_sentence".Humanize() => "Underscored input String is turned INTO sentence"

It's not framework, so I'll have to cut and paste.

So for instance, a newer function "Near" i suffixed "NearX" to make it abundantly clear that it takes a pattern and not a string.

Considering using "-Cs" for a list of characters.

Bug in this day an age. Expecting people to just "Trust" a lib.

This will break the assembly builder, and force a redeploy, so in that since it is breaking. But the names of the sql-side functions wouldn't change.

I blame the example I copied - all the more reason to make gooder examples.

I was missing the SqlFunction attribute TableDefinition. I fixed one, but I need to fix all of them.

So I like to save some SQL scripts with "?" on the end, because they are half-baked. They need investigation.
But you can't put ? in a file name, so instead, I put ❓ or ⁇ on the end, and it's beautiful.

Clone Database⁇.sql

Might be nice to pull as nuget.

Just a matter of walking through and creating at least one test.
Then flushing out negative tests, space tests, large string tests, empty string tests, nulls, negative 1s.

Maybe not the sweetheart, but over MSTest it 1) supports Regexp. Also methods are shorter, so NotNull instead of IsNotNull. Not sure what the means, but without Regexp I cannot survive in this life.

I write and I write. Fortunately I love and use this code, and I'm finally of the age where I'm willing to care long-term about something. So no one cares - who would? Am I special? Is my code? Am I looking to work with other people's dumb ideas? No.