Database DTO Generation for Views

ServiceStack Auto Query
1

Hi, I’ve built out a process for generating database DTOs using AutoQueryFeature’s GenerateCrudServices combined with TypeFilter and TableSchemasFilter delegate methods. This works great for tables, but we also wanted to generate DB DTOs for views that we have in the same schemas.

Is there anything that currently supports this or is there a plug-in point that we could hook into and write our own custom code to handle that? If not, what approach would you recommend? Thanks!

2

Not currently, at this point the place to do it would be to control your configured DialectProvider and overrride ToTableNamesStatement to include views when it queries the database metadata. This will be different per database vendor, but this is where the TableSchema type is populated from.

1 Like
3

Thanks for the quick reply. That is exactly what I needed.

public override string ToTableNamesStatement(string schema)
{
    var sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE='BASE TABLE' OR TABLE_TYPE = 'VIEW')";
    return sql + " AND TABLE_SCHEMA = {0}".SqlFmt(this, schema ?? DefaultSchema);
}

Thanks!

2 Likes
4

One last note in case this is useful for others. I added the following code to my GenerateCrudServices’s TableSchemasFilter delegate method so that it would not include any columns captured as a result of table join statements in the view:

private static void TableSchemasFilter(List<TableSchema> tableSchemas)
{
    foreach (TableSchema schema in tableSchemas)
    {
    	// Identify views
        if (schema.Name.StartsWithIgnoreCase("vw"))
        {
            schema.Columns = schema.Columns.Where(column => !column.IsHidden).ToArray();
        }

    // Other custom logic
    ...
    }
}

There may be a better way to do this, but the vw prefix we use helped identify views and then we remove those hidden columns.

1 Like
5

Also I’ve since added GetTableNames and GetTableColumns filter in GenerateCrudServices to make it easier to customize what tables and columns are generated without implementing a custom DialectProvider.

Here’s an example of overriding and providing custom implementations that match the default behavior:

Plugins.Add(new AutoQueryFeature {
    GenerateCrudServices = new GenerateCrudServices {
        GetTableNames = (db, schema) => db.GetTableNames(schema),
        GetTableColumns = (db, table, schema) =>
        {
            var quotedTable = db.Dialect().GetQuotedTableName(table, schema);
            return db.GetTableColumns($"SELECT * FROM {quotedTable}");
        }
    }
});

This change is available from v6.10.1+ that’s now available from our Pre-Release NuGet packages.

1 Like