Support query variant

[Virgiel]

It is common to have SQL queries with different filters, currently I write each variant by hand:

--! raw_page_fitness: ProRawRow
select ... from pro ORDER BY fitness ASC LIMIT 51 OFFSET :offset;
--! raw_page_created: ProRawRow
select ... from pro ORDER BY created DESC LIMIT 51 OFFSET :offset;
--! raw_page_name: ProRawRow
select ... from pro ORDER BY lname ASC LIMIT 51 OFFSET :offset;

I see two ways to handle these cases:

Runtime query

We should allow the user to replace the statement query with their own runtime generated queries, this should be advertised as a very advanced feature as it will cause runtime panic if the columns and parameters do not match.

pub struct Stmt {
    query: &'static str,
    cached: Option<Statement>,
}

Would become:

pub struct Stmt<'a> {
    query: &'a str,
    cached: Option<Statement>,
}

impl <'a> Stmt<'a> {
    pub fn runtime_query(&mut self, query: &'a) {
        self.query = query;
    }
}

Pattern query

The user declares a pattern query and all possible variants that can complete the pattern. This way we check all variants at compile time! The runtime query solution could still be proposed for case we do not support.

--@ RawOrder
--: None 
--: Fitness : ORDER BY fitness ASC
--: Created : ORDER BY created DESC
--: Name : ORDER BY lname ASC

--! raw_page: ProRawRow
select ... from pro @RawOrder LIMIT 51 OFFSET :offset;

Would generate :

pub fn raw_page(raw_order: RawOrder) -> RawPageStmt {
    let query = match raw_order {...};
    RawPageStmt(cornucopia_async::private::Stmt::new(query))
}

[LouisGariepy]

Yeah this is an oft-requested feature! Personally, I favor the pattern/templating approach. It's more structured and safe, which are definitely some goals I had with Cornucopia. If we can make the templating general/powerful enough, it even enables us to tackle issues like #101.

I don't see the appeal of runtime queries Vs. using postgres queries directly, but maybe missing something.

[ricardodarocha]

I agree with @LouisGariepy That increases complexibility. I suggest a layer to you generate sql files from templates. For instance, using jinja etc.

First you define templates and rules for that templates Then you make a cli to process the template, generating the sql you want

template select * from pro ORDER BY {{ FILTER }} ASC LIMIT 51 OFFSET :offset;

args.toml FILTER = [fitness ASC , created DESC , lname ASC]

your custom cli $ generate_sql template.txt args.toml -d \sql

You can easily create a cli like this with clap

[jacobsvante]

Pattern query

The user declares a pattern query and all possible variants that can complete the pattern. This way we check all variants at compile time! The runtime query solution could still be proposed for case we do not support.

--@ RawOrder
--: None 
--: Fitness : ORDER BY fitness ASC
--: Created : ORDER BY created DESC
--: Name : ORDER BY lname ASC

--! raw_page: ProRawRow
select ... from pro @RawOrder LIMIT 51 OFFSET :offset;

This seems like a very good start. Builds on cornucopia's main strength - code generation time query verification! (I would call what sqlx does "compile time query verification", which wastes a lot more time on CPU / my time).