How to generate code for multiple databases?

[costa100]

Hi,

Because I am stuck to .net 5 for now, I am using Facil 1.4.3. How do I handle multiple databases? According to the docs, I can have multiple yaml files. I created two yaml files facil_db1.yaml, facil_db2.yaml, but the build process complains:

Error No config file found. A minimal config file has been placed in the project directory (D:\TestDbAccess\facil.yaml). Re-build after editing the config. TestDbAccess D:\TestDbAccess\Facil 1

Also, it seems that there is a build hook that calls the facil generator, but I am not sure how I can override that command in case it supports specifying the yaml file name.

Thank you

Update: One option would be to create two library projects, one for each database and then reference these.

[cmeeren]

You can not have multiple YAML files. You have several config sections in the single file. To be specific, as you can see in the reference yaml file, rulesets is an array. Just add another array item to generate another file.

I will make the readme a bit clearer on this.

[costa100]

Yes, please. I read this:

See the full YAML config reference for details. Note that you can generate multiple source files with separate configs (e.g. to generate from multiple DBs). Here are some highlights of what you can configure.

I thought I need to specify different files.

Quick question, if I want to define two connection strings, do I need to specify this:

configs:
  - envVars: ""

rulesets:
  - connectionString: Data Source=server1;Initial Catalog=db1;Integrated Security=True

    namespaceOrModuleDeclaration: module Db1

    tableScripts:
      - include: dbo.Table1

        scripts:
          - type: insert
          - type: update
          - type: merge
          - type: getById

    tableDtos:
        - include: .*
          except: cdc\..*

  - connectionString: Data Source=server1;Initial Catalog=db2;Integrated Security=True

    filename: DbGenDb2.fs
    namespaceOrModuleDeclaration: namespace Db2

    tableScripts:
      - include: dbo.Table2
        scripts:
          - type: insert
          - type: update
          - type: merge
          - type: getById

    tableDtos:
        - include: .*
          except: cdc\..*  

I tried it, and the generation tool complained:

Severity    Code    Description Project File    Line    Suppression State
Error       System.Exception: Error getting table DTOs  TestDbAccess    D:\TestDbAccess\Facil   1   

[cmeeren]

That YAML looks right to me. Not sure what's wrong. Is that the only error message you get? The exception should have an inner exception; I would think that too should be printed.

[costa100]

I took a look with the sql profiler to see what happens and the tool stopped after running this:

      SELECT
        SCHEMA_NAME(sys.tables.schema_id) AS SchemaName,
        sys.tables.name AS TableName,
        sys.all_columns.name AS ColName,
        sys.all_columns.column_id,
        sys.all_columns.is_nullable,
        sys.all_columns.system_type_id,
        sys.all_columns.max_length,
        sys.all_columns.precision,
        sys.all_columns.scale,
        sys.all_columns.is_identity,
        sys.all_columns.is_computed,
        IsView = CAST(0 AS BIT)
      FROM
        sys.tables
      INNER JOIN
        sys.all_columns
          ON sys.all_columns.object_id = sys.tables.object_id

      UNION

      SELECT
        SCHEMA_NAME(sys.views.schema_id) AS SchemaName,
        sys.views.name AS TableName,
        sys.all_columns.name AS ColName,
        sys.all_columns.column_id,
        sys.all_columns.is_nullable,
        sys.all_columns.system_type_id,
        sys.all_columns.max_length,
        sys.all_columns.precision,
        sys.all_columns.scale,
        sys.all_columns.is_identity,
        sys.all_columns.is_computed,
        IsView = CAST(1 AS BIT)
      FROM
        sys.views
      INNER JOIN
        sys.all_columns
          ON sys.all_columns.object_id = sys.views.object_id

Btw, the union can be converted to an union ALL - it saves some time.

Yes, it fails with this: ---> System.Exception: Unsupported SQL type 'sql_variant' for column 'Value' in table 'MapPropertyValue' This is ok, it is a different issue. The tables that I have to update don't have sql_variant columns. So, it is all good for now, though it might be nice to support sql_variant columns in the future.

[cmeeren]

it might be nice to support sql_variant columns in the future.

That will both be non-trivial and go very much against the grain of Facil's strongly typed goals, since it will require Facil to provide a way to retrieve the column value as any type on a per-row basis, essentially wrapping SqlDataReader's numerous methods. I might consider it if someone posts a feature request with a well explained use-case and some guidance on the expected functionality, but no promises.

In any case, I have updated the readme now, so AFAIK there is nothing more outstanding in this issue. Feel free to correct me if I have missed anything.

[costa100]

Please note the generator fails with the exception above even when no tables that have sql_variant columns are included in tableDtos or tableScripts. I just tried it. You might consider fixing this though. For me this is a showstopper because I have tables with sql_variant columns and I cannot change them at this point. I haven't looked at the source code but maybe this check is done too early. Imo, it should be done only for tables that are being processed by the generator.

1>Facil : error : System.Exception: Error getting table DTOs
1> ---> System.Exception: Unsupported SQL type 'sql_variant' for column 'Value' in table 'ContractMapPropertyValue'
1>   at Facil.Db.typeInfo@743-10.Invoke(Unit unitVar0) in C:\projects\facil\src\Facil.Generator\Db.fs:line 743
1>   at Facil.Db.getTableDtos(RuleSet cfg, FSharpMap`2 sysTypeIdLookup, FSharpMap`2 primaryKeyColumnNamesByTable, SqlConnection conn) in C:\projects\facil\src\Facil.Generator\Db.fs:line 667

I agree with your comments. sql_variant columns are a bit painful to deal with in client code. You could map the value to object. Then the user of the code can cast them to the specific types they need.

[cmeeren]

Indeed Facil only checks that for tables that are being processed. If you look carefully at your YAML, I'm guessing that there is an include value for the table DTOs that covers the ContractMapPropertyValue table, and that if this rule has an except value, that value does not cover (i.e., does not exclude) that table.

[cmeeren]

Side-note (not relevant here, but nice to know): You can exclude specific columns, too, so if you need to work with a table that has sql_variant but you don't actually need the sql_variant column, then that is possible.

[costa100]

Indeed Facil only checks that for tables that are being processed. If you look carefully at your YAML, I'm guessing that there is an include value for the table DTOs that covers the ContractMapPropertyValue table, and that if this rule has an except value, that value does not cover (i.e., does not exclude) that table.

This is the content of yaml file for this database:

  - connectionString: Data Source=server1;Initial Catalog=db1;Integrated Security=True

    filename: DbDb1.fs
    namespaceOrModuleDeclaration: namespace db1

    #procedures:
      # Generate wrappers for all stored procedures with the default configuration.
    #  - include: .*
    tableScripts:
      - include: dbo.Table1
        #except: dbo\.SomeTable

        scripts:

            # Generates a script to insert a row.
          - type: insert
          - type: update
          - type: merge
          - type: getById

    tableDtos:
        - include: dbo\.Table2
          #except: cdc\..*     

Table1 & Table2 don't include sql_variant columns. That is why I thought the check is done early. Unless i am missing something.

You could try it by creating a table with a sql_variant field in your test database:

create table dbo.TableWithSqlVariant (SqlVariantField sql_variant NULL)

I just tried, and the generator fails even though the table is not selected in any way to be processed by the generator.

[cmeeren]

Thanks, I'll have a look at it.

[cmeeren]

I have confirmed the bug (which affects only table DTOs, not e.g. table scripts). Will fix tomorrow. However, it will of course require .NET 6.

As a workaround, you can add a rule for the problematic table that explicitly skips the sql_variant column. That should work on 1.x.

[costa100]

Thank you!!

The workaround you suggested works. I had to include the table and then exclude the sql_variant column:

        - include: dbo\.ContractMapPropertyValue    
          columns:
              Value:
                skip: true

I tried:

        - include: dbo\.ContractMapPropertyValue    
          except: dbo\.ContractMapPropertyValue

but it doesn't seem to do the trick.

[costa100]

Hi, Sorry, I discovered one more issue which I think it is a bit in the same vein as the other one:

Facil : Regenerating D:\TestDbAccess\DbDb2.fs
Facil : error : Table or view dbo.SomeTable_Backup has no primary keys and can not be used for a 'get' table script

Sometimes, I create backup tables before doing certain data massaging operations. Because this table is not specified in the yaml file, I don't think it should affect the generation process. As a principle, the generation tool should ignore and it should not validate any table that is not selected for generation.

I will get rid of it for now, and I know it is best practice to specify PKs for each table, but sometimes you deal with third-party databases (I have to deal with one third party database where none of the tables have PKs), or like in this case, the table is the byproduct of some data processing. Thanks

[cmeeren]

That error should only occur if dbo.SomeTable_Backup is included by your table script rules. I'm fairly sure about that, because I am testing Facil using a DB where there are tables without PKs, and it compiles fine for this DB. I recommend you comment out your table script rules and add back one at a time to see which one it is.

[costa100]

I will confirm it tomorrow, but I don't think it is included. Keep in mind that I am using version 1.4.3 while you are using 2.x. That could account for the difference.

[cmeeren]

Keep in mind that I am using version 1.4.3 while you are using 2.x. That could account for the difference.

I don't think anything relevant changed between these versions.

If you want, I can check if you send me the actual table script rules from your YAML file and the actual name of the table. If you don't want to post that publicly, I will wait for you to confirm yourself.