HierarchyID is not supported

[isaacabraham]

Using the standard AdventureWorks sample DB (I think I got it from here), trying to run Facil gives the following error:

   ---> System.Exception: Unsupported SQL system type ID '240' for column 'OrganizationNode' in table 'Employee'
     at Facil.Db.typeInfo@736-9.Invoke(Unit unitVar0) in C:\projects\facil\src\Facil.Generator\Db.fs:line 736
     at Facil.Db.getTableDtos(RuleSet cfg, FSharpMap`2 sysTypeIdLookup, FSharpMap`2 primaryKeyColumnNamesByTable, SqlConnection conn) in C:\projects\facil\src\Facil.Generator\Db.fs:line 730
     --- End of inner exception stack trace ---
     at Facil.Db.getTableDtos(RuleSet cfg, FSharpMap`2 sysTypeIdLookup, FSharpMap`2 primaryKeyColumnNamesByTable, SqlConnection conn) in C:\projects\facil\src\Facil.Generator\Db.fs:line 799
     at Facil.Db.getEverything(RuleSet cfg, String fullYamlPath, FSharpList`1 scriptsWithoutParamsOrResultSetsOrTempTables, SqlConnection conn) in C:\projects\facil\src\Facil.Generator\Db.fs:line 856
     at Facil.Program.regenerate@108(String yamlFilePath, RuleSet cfg, FSharpList`1 scriptsWithoutParamsOrResultSetsOrTempTables, String hash, String outFile, Unit unitVar0) in C:\projects\facil\src\Facil.Generator\Program.fs:line 117
     at Facil.Program.main(String[] argv) in C:\projects\facil\src\Facil.Generator\Program.fs:line 170

This appears to be a SQL type called HierarchyId.

Perhaps from a practical point of view, it would be best to have Facil simply warn and not generate tables that contains types that it does not support, but continue with the remaining ones? I'm thinking people would still benefit even if e.g. 90% of their DB could be generated and 10% had to be hand-rolled.

[isaacabraham]

Also the geography type has the same issue.

[cmeeren]

These types are deliberately not supported because AFAIK they have no simple .NET representation. You can easily skip these columns in the config file and work with the rest as if the skipped columns didn't exist. Here's part of the config reference for procedures. You can skip columns in procedures, scripts, table DTOs, and table scripts.

https://github.com/cmeeren/Facil/blob/bf8a2597d428e43b313f6337f339b9d89f726320/facil_reference.yaml#L140-L149

[isaacabraham]

I believe that there is a Geography type in some of the MS SQL libraries - I believe it's this one. I think HierarchyId is also included in that.

Of course, that could potentially mean a dependency on that library for Facil, which you may not wish to do.

[cmeeren]

Thanks for the notice.

I'll leave these types unsupported for now until someone actually needs them.