Support for CockroachDB temporal queries

[pierreis]

If you're having a generation problem please answer these questions before submitting your issue. Thanks!

What version of SQLBoiler are you using (sqlboiler --version)?

v4.5.0

What is your database and version (eg. Postgresql 10)

CockroachDB v20.2.9

If this happened at generation time what was the full SQLBoiler command you used to generate your models? (if not applicable leave blank)

If this happened at runtime what code produced the issue? (if not applicable leave blank)

What is the output of the command above with the -d flag added to it? (Provided you are comfortable sharing this, it contains a blueprint of your schema)

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

Further information. What did you do, what did you expect?

Currently, there seems to be no way to set temporal clauses of the form AS OF SYSTEM TIME. Reference for CockroachDB is as follows: https://www.cockroachlabs.com/docs/v20.2/as-of-system-time.html

Adding support for this would go a long way in supporting multi-region clusters.

[optiman]

Have you tried Query Mod System?

The query from CockroachDB docs

SELECT name, balance
    FROM accounts
         AS OF SYSTEM TIME '2016-10-03 12:45:00'
   WHERE name = 'Edna Barath';

would look like:

var acc models.Account
err := models.NewQuery(
    qm.Select(models.AccountColumns.Name, models.AccountColumns.Balance),
    qm.From(models.TableNames.Accounts + " AS OF SYSTEM TIME '2016-10-03 12:45:00'"),
        models.AccountWhere.Name.EQ("Edna Barath")
).Bind(context.Background(), db, &acc)

[pierreis]

Thanks for the suggestion. I have not tried this and will definitely do so.

I tend to think that this is not very idiomatic though -- seems a bit of an abuse of From, which works as long as we decide not to quote the table name for some reason.

Having a space in the table name, while arguably being a bad idea, is technically valid and would be a good reason to do so.

[aarondl]

@pierreis I don't love this workaround either. But it's an oddly specific cockroach db thing that we'd be adding.

Really I think we should just blow open the Query struct and allow people to write their own query mods.