Question: support for range types

[Milias]

Hello,

I have been testing this crate for the past several days and after some searching in the documentation and in the code I cannot find any references to range support. I have only used Sea Query with a postgres database, together with the postgres crate, which does support ranges. Additionally I used this crate.

Are there any plans to support range types? Could you point me to where would it be the best way to start supporting them in the code?

Thank you.

[tyt2y3]

Hi,

Thank you for reaching out.

Yes we can add support to it. But how'd you intend to use it?

Can you make some snippets on the schema & queries for intended use?

With that being done, I will be able to set out an implementation plan.

[Milias]

Hello, thank you for answering so quickly!

The use case I have in mind for ranges is for scheduling applications, that is, representing a period of time. Postgres already has a few built-in range types for dates and times (tsrange, tstzrange, daterange: see here). In my case I need to use a custom data type exactly like the example given in section 8.17.8 of that link, that is:

CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;

CREATE TYPE timerange AS RANGE (
    subtype = time,
    subtype_diff = time_subtype_diff
);

SELECT '[11:10, 23:00]'::timerange;

Then, if you have a table "events" with a column "range" with this data type you could insert new row as

insert into "events" ( "range" ) values ('[10:30, 11:00]');

Return all events happening at a given time:

select * from "events" where "range" @> '8:45'::time;

Or an exclusion constraint so that two events cannot overlap:

alter table events add constraint no_overlapping_events exclude using gist ("range" with &&);

These are simplified examples that I used in developing this toy application. Mainly, the range support that I would really use in Sea Query would be regarding insert and select queries, both in generating the queries themselves as well as converting their results back to rust types.

On the rust side this is a simplified example code to illustrate the usecase:

#[derive(Iden)]
enum Events {
    Table,
    Id,
    Range,
}

#[derive(Debug)]
pub struct Event {
    pub id: i64,
    range: Range<chrono::NaiveTime>, // <- defined in postgres_range.
}

pub fn new_event(
    conn: &mut Client,
    start: chrono::NaiveTime,
    end: chrono::NaiveTime,
) -> Result<Event, Box<dyn std::error::Error>> {
    let range = postgres_range::range!('(' start, end; ')'); // <- this is on postgres' side.

    let (sql, values) = Query::insert()
        .into_table((Data::Table, Events::Table))
        .columns(vec![
            Events::Range,
        ])
        .values_panic(vec![
            range.into(), // <- issue here: trait sea_query::Value is not implemented for postgres_range::Range<chrono::NaiveTime> (duh!).
        ])
        .returning(
            Query::select()
                .columns(vec![
                    Events::Id,
                    Events::Range,
                ])
                .take(),
        )
        .build(PostgresQueryBuilder);

        Ok(conn.query_one(sql.as_str(), &values.as_params())?.into())
}

I hope this helps!

As for the schema, I think with the above you can get an idea but to be specific:

CREATE TABLE "data".events (
    id int8 NOT NULL DEFAULT nextval('data.events_id_seq'::regclass),
    "range" "data".timerange NOT NULL,
    CONSTRAINT events_pk PRIMARY KEY (id),
    CONSTRAINT no_overlapping_events EXCLUDE USING gist (range WITH &&)
);
CREATE INDEX no_overlapping_events ON data.events USING gist (range);

Excuse any mistakes, I adapted my current implementation by removing some irrelevant details, but I might have removed either too much or too little!

[tyt2y3]

So I think this task can be broken down as:

1. Schema: create range type (this is the biggest task see #38 #19, but even without this, we can still create this in raw SQL and use a custom type in create table
2. operator @> (we can still use cust_with_values)
3. postgres_range::Range support in Value #35 #67

Question: How should a range look like when binding? if it looks exactly like '[10:30, 11:00]' then it is simply a string.

So I think solving 3 would be the minimum for a basic support.

[Milias]

Hello, thank you for your answer.

Sounds indeed like point 3 is the clear first step that would be sufficient to deal with insert and select queries.

As for binding ranges, I would expect [10:30, 11:00] to work (or other combination of [, ], (, )). I will confirm this later today.

[Milias]

I checked and indeed

insert into events ("range") values ($1);

with $1 being '[10:30, 11:00]' works perfectly.

I'm looking now at issues #35 and #67. Is in your plan to support ranges through the crate postgres_range? In which case I suppose adding a new build flag with-range makes the most sense?

[tyt2y3]

Yes you are correct. I would prefer it being named with-postgres_range though

[JohnnyMarconi]

Is it worth expecting in the near future?

[tyt2y3]

Hi there, thank you for expressing interest. I think it depends on if someone is willing to take on where we left off?

[bobogei81123]

Hi, I might be able to contribute this. The first step will be add a new range variant behind a feature (say with-postgres_range) here https://github.com/SeaQL/sea-query/blob/f3b895456061b2493a49f7576222a1862333a821/src/value.rs#L39-L44 Is this correct?

I think postgres is the only DB that supports date range so only sqlx-postgres driver will be able to use this feature. Should I use sqlx::DateRange directly? Or should I create a new Range struct in sea-orm (std::ops::Range might also be a good choice)?

[tyt2y3]

Sure, I think the support of a Range type can more or less be copied from IpNetwork (just grep from the repo). We cannot leak sqlx types, but using std or a standalone third-party crate is good. Otherwise we have to define our own DateRange type and add a bit of conversion facilities.

Thank you!