Database Scripts Naming Dialect

ServiceStack #Script
1

I am using a PostgreSQL database with OrmLiteNamingStrategy.
It also works fine. I want to use ValiteRequest with Database Script.

 [ValidateRequest(Condition = "!dbExistsSync('SELECT * FROM \"Region\" WHERE \"RegionCode\" = @RegionCode')")]

However, you get this error::
42703: column “regioncode” does not exist POSITION: 46

How to configure the Database Script to use a different Naming Strategy?

So far I have set it in AppHost Configure

 Plugins.Add(new SharpPagesFeature
            {
                ScriptMethods = { new DbScriptsAsync() },                
            });

Also in ConfigureDb:

 var defaultConnectionString = context.Configuration.GetConnectionString("DefaultConnection")
    ?? "Server=localhost;User Id=postgres;Password=XXXXX;Database=SkylerDB;Pooling=true;MinPoolSize=0;MaxPoolSize=200";
var secondConnectionString = context.Configuration.GetConnectionString("SecondConnection")
    ?? "Server=localhost;User Id=postgres;Password=XXXXX;Database=SecondSkylerDB;Pooling=true;MinPoolSize=0;MaxPoolSize=200";

PostgreSqlDialect.Provider.NamingStrategy = new OrmLiteNamingStrategyBase();
var dbFactory = new OrmLiteConnectionFactory(
    defaultConnectionString, PostgreSqlDialect.Provider);
services.AddSingleton<IDbConnectionFactory>(dbFactory);

services.AddDbContext<SkylerDbContext>(options =>
    options.UseNpgsql(defaultConnectionString, b => b.MigrationsAssembly(nameof(Skyler))));

dbFactory.RegisterConnection("Default", defaultConnectionString, PostgreSqlDialect.Provider);
dbFactory.RegisterConnection("Second", secondConnectionString, PostgreSqlDialect.Provider);

var scriptContext = new ScriptContext
{
    ScriptMethods = {
                    new DbScriptsAsync(),
                }
};
scriptContext.Container.AddSingleton<IDbConnectionFactory>(() => dbFactory);
scriptContext.Init();
2

The naming strategy is configured on the dialect provider, but it’s only used for generating table and columns from classes and properties, i.e. it’s not used for transforming raw SQL (which is always used as-is) as used in your attribute.

BTW your SQL includes an unknown @RegionCode param, you can get it from the Request dto like:

[ValidateRequest(Condition = 
    "!dbExistsSync('SELECT * FROM Table WHERE Id = @Id', { dto.Id })"]
3

Like that?

[ValidateRequest(Condition ="!dbExistsSync('SELECT * FROM Region WHERE Id = @Id', { CreateRegion.Id })")]

42P01: relation “region” does not exist POSITION: 15

4

No, the Request DTO is stored in dto, to access the Id request DTO property you’d use dto.Id.

Also you need to use the exact table/column names of your DB, i.e. if your Table name is quoted your SQL would also need to be quoted, e.g: "Region".

Try out the SQL in your DB UI/psql first to make sure you’re using the correct table/column names.

5

The quoted is ok, although I was hoping that you don’t have to put it in every SQL and there is a solution, but I accept it.

The dto, on the other hand, is interesting, I might still be misunderstanding something, sorry, but I already tried it and it didn’t work and it still doesn’t work now, what am I doing wrong?

[ValidateRequest(Condition = "!dbExistsSync('SELECT * FROM \"Region\" WHERE Id = @Id', { dto.Id })")]

Could not evaluate expression ‘:dto.:Id’

6

Does your Request DTO have an Id property?

7

Ok, fine! I used the same condition for create and update, but create does not contain the Id! Thanks!

8

Sorry, but last question this topic: Is the copied code correct for using named connection? (for Scriptcontext)
Where can I find a description, e.g. about dbexists arguments to set e.g. namedconnection?

9

We’ve got basic docs for db scripts is at: https://sharpscript.net/docs/db-scripts.

i.e. you can specify the named connection to use in { namedConnection:'...' }.

There’s nothing special about dbScripts, they’re just pre-registered Script Methods which you could also register yourself to call your own C# APIs. The source code for dbScripts is at DbScripts.cs