AuthRepo Schema

ServiceStack Authentication
1

Hey @mythz,

Was wondering if maybe SS could have a GitHub repo (new or included in an existing) of the different Auth DB schemas in sql code. Probably only needs the major db dialects (SqlServer, Postgres, etc.). This could probably be auto generated and only needs to be generated if something changes. If this already exists, let me know.

The reasoning is running the code to init an Auth DB are:

  1. It’s rather annoying, when all that is needed is the sql script.
  2. DBA’s don’t run .NET code.
  3. Tracking Auth DB schema changes as they happen. I think this has happened once or twice since SS v2 and caused some nuisances when upgrading b/c of the changes.
  4. For SS beginners, it’ll help them, as well as, include code for them to generate their own. Even tho it’s indicated in the docs.

Yes dialects could change conventions and therefore the schema, but I think have the basic default dialect schemas would be enough.

Thoughts?

2

I have no intentions of spending time on this never requested option which would require keeping it in sync indefinitely and supporting it as an alternative code-path for creating RDBMS schemas.

If you want to yourself you can register the BeforeExecFilter to access all generated SQL + params, e.g:

OrmLiteConfig.BeforeExecFilter = dbCmd => Console.WriteLine(dbCmd.GetDebugString());

Otherwise feel free to take the InitSchema() code used to create the RDBMS tables and run them wherever appropriate:

public override void InitSchema()
{
    hasInitSchema = true;

    EachDb(db =>
    {
        db.CreateTableIfNotExists<TUserAuth>();
        db.CreateTableIfNotExists<TUserAuthDetails>();
        db.CreateTableIfNotExists<UserAuthRole>();
    });
}

i.e. you literally just need to run CreateTableIfNotExists<T> on each POCO table.

As a goal I avoid making any structural RDBMS changes that would cause a software update to break when accessing an old schema. I can’t remember the last time this happened I know the Auth DB schemas never changed in v4 which is now more than 5 years ago, the last SS v2 release was likely more than 7 years ago. I can’t quickly tell offhand because this predates NuGet.

3

No problems. Trying to offload some stuff to the dba and make it easier to track changes.

In regards to the previous changes.

  1. The table name changed from user_o_auth_provider to user_auth_details
  2. There were additional fields like phone/address/invalid_login/etc added to both user_auth and user_auth_details table.

We made these changes in 2014, but could have waited a while to update SS to these changes.

Thanks again for your response.