sysdiagram and related SPs are not excluded by default

[mauriciomagni]

Hello!

After adding support for diagrams in my DB, Facil included related types and SPs in the generated file. At a first glance this is not a problem by itself, but it produced an unexpected behavior, my code started raising a System.AccessViolationException.

The culprit for raising this exception are one or many of these: type sysdiagrams type sp_alterdiagram_Executable type sp_alterdiagram type sp_creatediagram_Executable type sp_creatediagram type sp_dropdiagram_Executable type sp_dropdiagram type sp_helpdiagramdefinition_Executable type sp_helpdiagramdefinition type sp_helpdiagrams_Executable type sp_helpdiagrams type sp_renamediagram_Executable type sp_renamediagram type sp_upgraddiagrams

As a workaround, we excluded these DB elements using "except" mechanism (facil.yaml) provided by Facil.

Kind regards, Mauricio

[cmeeren]

Thank you for letting me know. By design, Facil can include anything from any schema, so the solution, as you discovered, is to modify your include/except patterns.

Out of interest, what schema are these under, what what rule filter were you using that were picking these up? I usually only include stuff from a certain schema, e.g. dbo\..+. (Normally I'm even more restrictive in my patterns, but specifying the schema is a minimum.)

[mauriciomagni]

Hello cmeeren

It filter these out I have added: In procedures: .diagram. `procedures:

Generate wrappers for all stored procedures with the default configuration.

  - include: .*
    except: (dbo.GetGeoPoint|.*diagram.*)
    voptionIn: true
    voptionOut: true`

and in tableDtos: sysdiagrams `tableDtos:

• include: .* except: sysdiagrams voption: true`

this is the info about sysdiagrams it creates in TableDtos, I would have expected, that is related to

and this is a sample of one of the SPs:

The weird thing, is these changes end up raising a System.AccessViolationException when the app starts setting up functions in modules. So the execution, never reaches facil itself, it reaches just the code generated in DbGen, as long as I understand, this happens in the static constructor of modules.

this weekend I can investigate further and try to find which one of the SPs is the one generating this strange behavior.

Kind regards,

[cmeeren]

Do you have a stack trace for the exception? Can you show me the part of the code that throws the exception?

[mauriciomagni]

good morning

here is the exception raised:

I know for certain that this is not the actual code motivating the exception, given that removing the SPs for diagramas it just works.

In "CarrierInfoDal" there is a reference to DbGen:

In any case, any of the diagrams' SPs are called, they are just "defined" in DbGen... I will narrow the information during the weekend.

Regards, Mauricio

[cmeeren]

Thank you for supplying more details.

I will narrow the information during the weekend.

Great, I will await additional information from you. A minimal repro (given some DB schema that I can replicate) would be perfect.

[mauriciomagni]

hello cmeeren

I was able to narrow the code that raises the error in my project, but not able to create a minimal reproducible example.

[<EditorBrowsable(EditorBrowsableState.Never)>]
type ``sp_helpdiagramdefinition_Executable`` (connStr: string, conn: SqlConnection, configureConn: SqlConnection -> unit, userConfigureCmd: SqlCommand -> unit, getSqlParams: unit -> SqlParameter [], tempTableData: seq<TempTableData>, tran: SqlTransaction) =

      let configureCmd sqlParams (cmd: SqlCommand) =
        cmd.CommandType <- CommandType.StoredProcedure
        cmd.CommandText <- "dbo.sp_helpdiagramdefinition"
        cmd.Parameters.AddRange sqlParams
        userConfigureCmd cmd

      let mutable ``ordinal_version`` = 0
      let mutable ``ordinal_definition`` = 0

      let initOrdinals (reader: SqlDataReader) =
        ``ordinal_version`` <- reader.GetOrdinal "version"
        ``ordinal_definition`` <- reader.GetOrdinal "definition"

      let getItem (reader: SqlDataReader) =
        let ``version`` = if reader.IsDBNull ``ordinal_version`` then ValueNone else reader.GetInt32 ``ordinal_version`` |> ValueSome
        let ``definition`` = if reader.IsDBNull ``ordinal_definition`` then ValueNone else reader.GetBytes ``ordinal_definition`` |> ValueSome
        {|
          ``version`` = ``version``
          ``definition`` = ``definition``
        |}

      member _.ExecuteAsync(?cancellationToken) =
        let sqlParams = getSqlParams ()
        executeQueryEagerAsync connStr conn tran configureConn (configureCmd sqlParams) initOrdinals getItem tempTableData (defaultArg cancellationToken CancellationToken.None)

      member this.AsyncExecute() =
        async {
          let! ct = Async.CancellationToken
          return! this.ExecuteAsync(ct) |> Async.AwaitTask
        }

      member _.ExecuteAsyncWithSyncRead(?cancellationToken) =
        let sqlParams = getSqlParams ()
        executeQueryEagerAsyncWithSyncRead connStr conn tran configureConn (configureCmd sqlParams) initOrdinals getItem tempTableData (defaultArg cancellationToken CancellationToken.None)

      member this.AsyncExecuteWithSyncRead() =
        async {
          let! ct = Async.CancellationToken
          return! this.ExecuteAsyncWithSyncRead(ct) |> Async.AwaitTask
        }

      member _.Execute() =
        let sqlParams = getSqlParams ()
        executeQueryEager connStr conn tran configureConn (configureCmd sqlParams) initOrdinals getItem tempTableData

If I comment out the last method shown in the code above, it works:

member _.Execute() =
        let sqlParams = getSqlParams ()
        executeQueryEager connStr conn tran configureConn (configureCmd sqlParams) initOrdinals getItem tempTableData

I tried creating a simple project, but it does not fail, so there should be something else that ends up creating this unexpected behavior because, by itself, it does not make sense... Sadly I have not idea how to find it out.

Regards, Mauricio

[cmeeren]

Thank you for the details!

As I understand it, you would ideally like Facil to automatically exclude these stored procedures.

That is unfortunately problematic for two reasons:

• It is a breaking change, since it is possible that users may have defined their own stored procedures with these names.
• If users want to override this and include the procedures (whether they are diagram procedures or similarly named custom procedures), this would requires functionality to override the exception, which currently does not exist, and would also be stretching the include/exclude concepts a bit.

Since Facil is doing exactly as you tell it to by including these procedures when including e.g. dbo\.*, and since the solution is trivial (just exclude them manually, as you yourself discovered), and since no other users have reported this so far, I will keep the existing functionality.

[mauriciomagni]

Hello @cmeeren This message has the only purpose of providing you more info, there is no action expected. As our project grew and the DbGen file became larger we started experiencing the exception System.AccessViolationException more and more. It started showing up after trivial changes in our database and DbGen regeneration, for instance, once it appeared after changing an inner join by a left join, some times after adding a new simple table, etc. We have found a workaround: splitting our data access layer library, and so, producing smaller DbGen files. After we split the libraries and the dbgen files became smaller the issue went away. I have reported this to Microsoft but it is not clear if they will take any action. I wanted to keep you updated about this just in case somebody else experience this issue. Kind regards, Mauricio

[cmeeren]

Thank you for the info.

1. How big was your DbGen file when it didn't work?
2. Are you saying that the AccessViolationException was not caused by sysdiagrams, but was instead related to size? Or are there multiple issues that cause AccessViolationException?
3. For reference, could you link to the issue where you reported it to Microsoft, if it is publicly accessible?

[mauriciomagni]

hello @cmeeren

1. The file was almos 70k lines of code, now the biggest one is 44k lines.
2. Yes, I think this issue is related with the size of the file. Of course, this is just speculation, but my gut says it is because of the extensive use of generic type validation. It catches our attention the time it takes to compile the assembly.
3. Correct Sadly, I made the mistake of mention we were using Facil and they decided to blame the library. I have insisted it is not the library and gave them a more detailed info (copy below), but I have not heard back from Microsoft:

[cmeeren]

Thanks. If you believe this is a compiler issue, you should raise an issue at https://github.com/dotnet/fsharp. But I'm not sure it can be considered a compiler issue. 70k lines is a lot.