Crud operations only reference dbo schema

[jsobell]

I just spent three hours following the example at https://forums.servicestack.net/t/autogen-autocrud-services/8528 and trying to work out why I couldn't generate DTOs for some tables in my database, and I eventually realised that only the dbo schema on SQL Server is scanned by default. So if I want to generate client DTOs for my 'operations' schema I have to use a command like: x csharp http://localhost:5000 -path "/crud/all/csharp?ExcludeTypes=services&Schema=operations"

Given that many SQL Server instances use schemas as a way of logically partitioning operations, can the generator either default to all schemas (which is what the doco implies it's already doing) or at least allow multiple schema names? E.g. Schema=dbo,operations,history ?

[jsobell]

Also, the options at the top of the client files don't appear to allow the schema to be saved, so running x csharp to update the definitions after specifying the schema will reset the client contents to the default dbo again. Perhaps the options can either leave the UsePath: /crud/all/csharp unchanged so it can be set to UsePath: /crud/all/csharp?schema=operations, or a new option added:

//Schema: operations

or better still

//Schemas: operations,dbo

?

[Layoric]

Hi @jsobell , Schema isn't currently an option on the client generated DTOs which is why it isn't being persisted. See at the top of a generated DTO which options are supported. Are your CRUD services being generated and visible in /metadata? Could you provide a minimal reproduction of the issue.

Let us know which documentation was was implying all schemas are supported so we can make sure it is clearer to others?

Supporting multiple schemas can be done on the GenerateCrudServices option for the AutoQueryFeature as documented here. If you add the schemas you want to support for generated services, your x csharp should reflect what services are being hosted by the base URL server you point to.

[jsobell]

Sure, in the link I included it states:

Generating AutoQuery Types & Services

The development experience is essentially the same as Add ServiceStack Reference where you’ll need to run the .NET Core App in 1 terminal:

$ dotnet run Then use the x dotnet tool to download all the AutoQuery & Crud Services for all tables in the configured DB connection:

Schema is obviously an option for the DTO generation URL, but simply not respected in the utility. One issue is that this all seems to make x mix autodto unusable if there are multiple schema names in your SQL Server database. So yes, I found that doco, but it requires creating a standalone project and hand-coding schema names which is a lot more hassle than using the autodto feature.

[Layoric]

Schema is obviously an option for the DTO generation URL, but simply not respected in the utility

The utility takes the response directly from your ServiceStack instance and saves it to a file. The options for each language are defined in the generated file where which come from MetadataTypesConfig which each NativeTypes generator utilizes individually and persists in the Options based on if they were provided in the request, eg the CSharpGenerator here.

Using the x tool with a Sharp app, you can add the same CreateServices using the following app.settings.

AutoQueryFeature.GenerateCrudServices { CreateServices: [{ Schema: "myschema" }] }

It is an array so multiple can be used. Full example:

debug true
name Auto DTO
defaultRedirect /metadata
features AutoQueryFeature
AutoQueryFeature { MaxLimit: 100 }
AutoQueryFeature.GenerateCrudServices { CreateServices: [{ Schema: "custom" }] }

# Configure below. Supported dialects: sqlite, sqlserver, postgres, mysql
db sqlserver
db.connection Server=localhost;Database=Chinook;MultipleActiveResultSets=True;Integrated Security=SSPI;

I'll look at updating the documentation to be clearer regarding the use of default and configured schemas for the AutoQueryFeature and the specified DB connection.

[mythz]

What's returned are Add ServiceStack Reference generated DTOs, i.e. they're not bespoke for AutoGen and so does not include support for AutoGen schemas. Changing to use bespoke functionality would require a re-implementation requiring a lot more investment and would therefore require a high voted feature request for it to be prioritized above other work items.

Going to close this issue since it's working as intended and there's no actionable issues to resolve, we'll make the docs clearer that it only generates the default schema by default.

[jsobell]

OK, so as a workaround I've manually updated the app.settings and changed AutoQueryFeature.GenerateCrudServices { } to AutoQueryFeature.GenerateCrudServices { CreateServices: [{ Schema: "dbo" },{ Schema: "operations" }] }

This works, although it's very odd thing to have to add as most systems assume all schemas are in-scope by default.

[jsobell]

Ah, no, I spoke too soon, that doesn't work. There are no DTOs generated for any of the non-DBO schema tables.

If I run the following app.settings at the service end:

debug true
name Auto DTO
defaultRedirect /metadata
features AutoQueryFeature
AutoQueryFeature { MaxLimit: 100 }
AutoQueryFeature.GenerateCrudServices { CreateServices: [{ Schema: "operations" }] }

# Configure below. Supported dialects: sqlite, sqlserver, postgres, mysql
db sqlserver
db.connection Server=127.0.0.1;Database=DataStaging;user id=sa;password=****

Then use x csharp (with ExcludeTypes: services) it generates a new dtos.cs with (for example) a class such as

namespace Web.ServiceModel.Types
{

    [Alias("etlbox_loadprocess")]
    [DataContract]
    public class EtlboxLoadprocess
    {
        [DataMember(Order=1)]
        [AutoIncrement]
        public long Id { get; set; }

which is a dbo schema table, not operations.

But, if I get the URL http://localhost:5000/crud/all/csharp?Schema=operations I get the DTOs correctly returned:

namespace Web.ServiceModel.Types
{

    [Schema("operations")]
    [DataContract]
    public class Customer
    {
        [DataMember(Order=1)]
        [AutoIncrement]
        public int CustomerId { get; set; }

[mythz]

This works, although it's very odd thing to have to add as most systems assume all schemas are in-scope by default.

Which code gen tool generates models for all RDBMS schemas by default? Either way it should be explicitly opt-in as to which Schemas you want to generate APIs + data models for, which need to be uniquely named.

But on further investigation it looks like we can add optional query string params feature to the existing CodeGen impls which I've added in this commit.

This should now have the AutoGen options returned in /crud/all/csharp, e.g:

/* Options:
//...
//IncludeCrudOperations: 
Schema: custom
//NamedConnection: 
//IncludeTables: 
//ExcludeTables: 
*/

This change is available from the latest v5.13.3+ that's now available on MyGet.

[jsobell]

Which code gen tool generates models for all RDBMS schemas by default? Either way it should be explicitly opt-in as to which Schemas you want to generate APIs + data models for, which need to be uniquely named.

Isn't this what the main ones, EF6, EFCore, LLBLGen, etc. do by default? I don't have to specify individual schemas when I do a database-first project, just the database itself. Mind you, Rider and DataGrip always catch me out by assuming a single schema in their connection properties, but at least IU can click 'all' in the schema filter :)

Thanks for the quick fix! I'll try it out. I assume this means I can create dbo.dtos.cs, operations.dtos.cs, etc, one for each schema, and include them all in the project, each with its own schema in the options?

[mythz]

It should let you have multiple encapsulated code gen .cs files referencing different schemas, yes.

After they're generated then it's no longer an "AutoGen" feature, it just becomes normal AutoQuery RDBMS and AutoQuery CRUD DTOs and data models, so normal features/rules apply.