Early implementation of Tiberius as Diesel backend

[delsehi]

I've started working on Tiberius/SQL Server as a backend for Diesel in this repository.

The current status is that I can select, insert and join from existing tables with simple data types. I discover more things that I need to implement as I go about adding more tests.

If anyone's interested in taking a look and giving some feedback this early on that'd be welcome! 😊

Does anyone know if it's possible to plug a custom backend into the CLI in any way? I'd really like to inspect an existing database to generate schema-files.

Is it possible to handle using several schemas in SQL Server somehow? That is, a table [mySchema].[myTable], rather than using the default schema.

[weiznich]

These are great news 🎉

Thanks for working on this and thanks for making this work public ❤️

---

Does anyone know if it's possible to plug a custom backend into the CLI in any way? I'd really like to inspect an existing database to generate schema-files.

That's currently not possible, as we did not have any need for that yet. In the back of my mind is the idea to have some sort of extension functionality for this, similar to what cargo does. Maybe by just allowing to register extension command line tools via diesel.toml + then querying them on startup for their capabilities. One of these could be to provide custom backend support, which in turn would require providing an interface for an yet to be defined json IPC API. That's all just ideas now, and would need further implementation on our side. On the other hand it should hopefully not be that hard to figure out an API for that.

Is it possible to handle using several schemas in SQL Server somehow? That is, a table [mySchema].[myTable], rather than using the default schema.

The table! macro just supports this. You can just write

table! {
    mySchema.myTable {
        id -> Integer
    }
}

and it will just work as expected.

[delsehi]

I've made some more progress. I've started stealing tests from diesel_tests one by one and making them compile.

There are still many bugs left and unimplemented features.

I'm having extra difficulties with the traits FromSql and ToSql. At first I used the raw value-struct (ColumnData) from Tiberius as the type for my backend and the RawBytesBindCollector. This did not work for dates though. Turns out all parameters got bound as VARBINARY when using this approach and SQL Server couldn't convert this to the DATETIME2-type, so I made a wrapper struct for the types and this solved the issue. Instead I got a new bug with nullable values. If you take a look at impl FromSql<sql_types::Text, Mssql> for String in src/mssql/types/primitives.rs, I have to match for NULL values in the from_sql implementation. I currently return an empty string to make my other test cases crash less. So this SQL:

create table slime (
external_id NVARCHAR(100) NOT NULL PRIMARY KEY,
name NVARCHAR(MAX) NULL
);

INSERT INTO slime (external_id) VALUES ('id-34');

When I load that row from that table in Diesel, I don't get None for the name column, but rather Some("").

Why is the from_sql fn used for values that are null from the database?

Any guidance on how to resolve this would be appreciated! 😄

[weiznich]

Thanks for the update.

Instead I got a new bug with nullable values. If you take a look at impl FromSql<sql_types::Text, Mssql> for String in src/mssql/types/primitives.rs, I have to match for NULL values in the from_sql implementation. I currently return an empty string to make my other test cases crash less. So this SQL:

I think the issue here is that you always return Some(value) here: https://github.com/delsehi/diesel-mssql/blob/5b15484b77ee5c2601d8a24580cb39d28452bbf8/src/mssql/connection/row.rs#L20

Diesel expects you to return None for null values there. It seems like you would need to match on ColumnData and see if the relevant entry is None internally.

I also noticed that you use the Iterator::nth() method here: https://github.com/delsehi/diesel-mssql/blob/5b15484b77ee5c2601d8a24580cb39d28452bbf8/src/mssql/connection/row.rs#L67 which might not be efficient.

I think you can implement the tiberius::FromSql trait for a new type wrapper to directly extract that value via get()

Something like:

struct FromSqlHelper<'a>(&'a ColumnData<'static>);

impl<'a> tiberius::FromSql<'a> for FromSqlHelper<'a> {
    fn from_sql(value: &'a ColumnData<'static>) -> Result<Option<Self>> {
       Ok(Some(Self(value)))
    }
}

Then you could just write row.try_get::<FromSqlHelper, _>(idx) there.

It also should be possible to skip the clone while constructing the MssqlField there by storing the value reference in the MssqlField struct instead.

Hopefully that helps, otherwise just let me know.

[delsehi]

Thanks a lot! Instant unblocker 😄

[delsehi]

T-SQL is a bit weird when it comes to OFFSET/LIMIT-related things.

-- LimitOffsetClause<LimitClause<L>, NoOffsetClause>
DECLARE @P1 INT = 5;
SELECT 
    TOP(@P1) -- limiting is done here
      FROM mytable;
     -- instead of here


-- LimitOffsetClause<LimitClause<L>, OffsetClause<O>
SELECT * FROM myTable 
ORDER BY 1 -- this is more like postgres, but order, offset and limit are all required now. 
OFFSET 10 ROWS 
FETCH NEXT 10 ROWS ONLY

So, limiting the result set is possible without any offset or ordering.
You can also offset the result set, but then you also need ordering and limiting and the syntax is different.

I'm not quite sure how to best implement this in Diesel.

I currently implement my own SelectStatement syntax just like the Firebird implementation to allow moving the "TOP()" to just after the DISTINCT clause.

    fn walk_ast<'b>(
        &'b self,
        mut out: diesel::query_builder::AstPass<'_, 'b, Mssql>,
    ) -> diesel::QueryResult<()> {
        out.push_sql("SELECT ");
        self.distinct.walk_ast(out.reborrow())?;
        self.limit_offset.walk_ast(out.reborrow())?; // This is after WHERE I think in postgres/mysql/sqlite
        self.select.walk_ast(out.reborrow())?;
        self.from.walk_ast(out.reborrow())?;
        self.where_clause.walk_ast(out.reborrow())?;
        self.group_by.walk_ast(out.reborrow())?;
        self.having.walk_ast(out.reborrow())?;
        self.order.walk_ast(out.reborrow())?;
        Ok(())
    }

But this ordering does not work with the FETCH/NEXT syntax in TSQL when OFFSET is needed. I'm not sure if I can check whether it's just a LIMIT-clause and no OFFSET and in that case use TOP otherwise if OFFSET/LIMIT/ORDER are present in that case use FETCH/NEXT.

I had another idea of using FETCH/NEXT in SelectStatements and not using TOP() at all. To allow for just limiting I'd instead find a way to extend the QueryDSL with a custom function so you could do something like:

users.select((name, hair_color))
  .top(5) -- This looks more like TSQL! 
  .load(connection)
  .unwrap();

But SelectStatement isn't open for extending, so I guess I'd have to reimplement the entire SELECT thing and do something like the Pagination-example in the Extending Diesel-guide.

I realize this is a confusing question, but I'm a bit confused as how to go about this in a wise way. I'd prefer making as few custom implementations as possible so it doesn't become too hard to maintain, but I also think it's a good idea to make the querybuilder go nicely with tsql.

[weiznich]

That's not a confusing question. There is already something like that in diesel for requiring at least that offset is always used with limit for the mysql backend. That's the reason why limit+offset is stored in the same struct.

The relevant code for that is here:

diesel/diesel/src/mysql/query_builder/limit_offset.rs

Lines 1 to 104 in 8939fce

	use crate::mysql::Mysql;
	use crate::query_builder::limit_clause::{LimitClause, NoLimitClause};
	use crate::query_builder::limit_offset_clause::{BoxedLimitOffsetClause, LimitOffsetClause};
	use crate::query_builder::offset_clause::{NoOffsetClause, OffsetClause};
	use crate::query_builder::{AstPass, IntoBoxedClause, QueryFragment};
	use crate::result::QueryResult;
	impl QueryFragment<Mysql> for LimitOffsetClause<NoLimitClause, NoOffsetClause> {
	fn walk_ast<'b>(&'b self, _out: AstPass<'_, 'b, Mysql>) -> QueryResult<()> {
	Ok(())
	}
	}
	impl<L> QueryFragment<Mysql> for LimitOffsetClause<LimitClause<L>, NoOffsetClause>
	where
	LimitClause<L>: QueryFragment<Mysql>,
	{
	fn walk_ast<'b>(&'b self, out: AstPass<'_, 'b, Mysql>) -> QueryResult<()> {
	self.limit_clause.walk_ast(out)?;
	Ok(())
	}
	}
	impl<L, O> QueryFragment<Mysql> for LimitOffsetClause<LimitClause<L>, OffsetClause<O>>
	where
	LimitClause<L>: QueryFragment<Mysql>,
	OffsetClause<O>: QueryFragment<Mysql>,
	{
	fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, Mysql>) -> QueryResult<()> {
	self.limit_clause.walk_ast(out.reborrow())?;
	self.offset_clause.walk_ast(out.reborrow())?;
	Ok(())
	}
	}
	impl<'a> QueryFragment<Mysql> for BoxedLimitOffsetClause<'a, Mysql> {
	fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, Mysql>) -> QueryResult<()> {
	match (self.limit.as_ref(), self.offset.as_ref()) {
	(Some(limit), Some(offset)) => {
	limit.walk_ast(out.reborrow())?;
	offset.walk_ast(out.reborrow())?;
	}
	(Some(limit), None) => {
	limit.walk_ast(out.reborrow())?;
	}
	(None, Some(offset)) => {
	// Mysql requires a limit clause in front of any offset clause
	// The documentation proposes the following:
	// > To retrieve all rows from a certain offset up to the end of the
	// > result set, you can use some large number for the second parameter.
	// https://dev.mysql.com/doc/refman/8.0/en/select.html
	// Therefore we just use u64::MAX as limit here
	// That does not result in any limitations because mysql only supports
	// up to 64TB of data per table. Assuming 1 bit per row this means
	// 1024 * 1024 * 1024 * 1024 * 8 = 562.949.953.421.312 rows which is smaller
	// than 2^64 = 18.446.744.073.709.551.615
	out.push_sql(" LIMIT 18446744073709551615 ");
	offset.walk_ast(out.reborrow())?;
	}
	(None, None) => {}
	}
	Ok(())
	}
	}
	impl<'a> IntoBoxedClause<'a, Mysql> for LimitOffsetClause<NoLimitClause, NoOffsetClause> {
	type BoxedClause = BoxedLimitOffsetClause<'a, Mysql>;
	fn into_boxed(self) -> Self::BoxedClause {
	BoxedLimitOffsetClause {
	limit: None,
	offset: None,
	}
	}
	}
	impl<'a, L> IntoBoxedClause<'a, Mysql> for LimitOffsetClause<LimitClause<L>, NoOffsetClause>
	where
	L: QueryFragment<Mysql> + Send + 'a,
	{
	type BoxedClause = BoxedLimitOffsetClause<'a, Mysql>;
	fn into_boxed(self) -> Self::BoxedClause {
	BoxedLimitOffsetClause {
	limit: Some(Box::new(self.limit_clause)),
	offset: None,
	}
	}
	}
	impl<'a, L, O> IntoBoxedClause<'a, Mysql> for LimitOffsetClause<LimitClause<L>, OffsetClause<O>>
	where
	L: QueryFragment<Mysql> + Send + 'a,
	O: QueryFragment<Mysql> + Send + 'a,
	{
	type BoxedClause = BoxedLimitOffsetClause<'a, Mysql>;
	fn into_boxed(self) -> Self::BoxedClause {
	BoxedLimitOffsetClause {
	limit: Some(Box::new(self.limit_clause)),
	offset: Some(Box::new(self.offset_clause)),
	}
	}
	}

To also include the order clause I see two possibilities:

1. We change diesel to use this pattern for the other clause as well, we can easily implement it in such a way that we don't require order for all calls to limit for the other backends
2. You introduce a custom QueryFragment helper trait in diesel-mssql

I think for now the second variant would be quicker as that's something that works right now. You would need to do something like that (not tested, just written down):

trait MssqlOrderLimitOffsetHelper<Order, LimitOffset> {
    fn walk_ast<'b>(
        &'b  self,
        mut out: diesel::query_builder::AstPass<'_, 'b, Mssql>,
    ) -> diesel::QueryResult<()>;
}

// basically have a separate variant for each of the allowed variants here
// this one is only for order
impl<F, S, D, W, G, H, O> MssqlOrderLimitOffsetHelper<OrderClause<O>, LimitOffsetClause<NoLimitClause, NoOffsetClause>>
    for SelectStatement<F, S, D, W, OrderClause<O>, LimitOffsetClause<NoLimitClause, NoOffsetClause>, G, H, LC>
where
    F: QueryFragment<Mssql>,
    S: QueryFragment<Mssql>,
    D: QueryFragment<Mssql>,
    W: QueryFragment<Mssql>,
    OrderClause<O>: QueryFragment<Mssql>,
    LimitOffsetClause<NoLimitClause, NoOffsetClause>: QueryFragment<Mssql>,
    G: QueryFragment<Mssql>,
    H: QueryFragment<Mssql>,
    LC: QueryFragment<Mssql> {
    fn walk_ast<'b>(
        &'b  self,
        mut out: diesel::query_builder::AstPass<'_, 'b, Mssql>,
    ) -> diesel::QueryResult<()> {
        // might be possible to factor out that in yet another generic function that's called from all three similar variants
        // that likely could just be a free standing function that
        // accepts a selectstatement with generic parameters + an astpas as argument
        out.push_sql("SELECT ");
        self.distinct.walk_ast(out.reborrow())?;
        self.select.walk_ast(out.reborrow())?;
        self.from.walk_ast(out.reborrow())?;
        self.where_clause.walk_ast(out.reborrow())?;
        self.group_by.walk_ast(out.reborrow())?;
        self.having.walk_ast(out.reborrow())?;
        self.order.walk_ast(out.reborrow())?;
        self.limit_offset.walk_ast(out.reborrow())?;
        Ok(())
    }
}

// this one is for order + limit
impl<F, S, D, W, G, H, L, O> MssqlOrderLimitOffsetHelper<OrderClause<O>, LimitOffsetClause<LimitClause<L>, NoOffsetClause>>
    for SelectStatement<F, S, D, W, OrderClause<O>, LimitOffsetClause<LimitClause<L>, NoOffsetClause>, G, H, LC>
where
    F: QueryFragment<Mssql>,
    S: QueryFragment<Mssql>,
    D: QueryFragment<Mssql>,
    W: QueryFragment<Mssql>,
    OrderClause<O>: QueryFragment<Mssql>,
    LimitOffsetClause<LimitClause<L>, NoOffsetClause>: QueryFragment<Mssql>,
    G: QueryFragment<Mssql>,
    H: QueryFragment<Mssql>,
    LC: QueryFragment<Mssql> {
    fn walk_ast<'b>(
        &'b  self,
        mut out: diesel::query_builder::AstPass<'_, 'b, Mssql>,
    ) -> diesel::QueryResult<()> {
        out.push_sql("SELECT ");
        self.distinct.walk_ast(out.reborrow())?;
        self.select.walk_ast(out.reborrow())?;
        self.from.walk_ast(out.reborrow())?;
        self.where_clause.walk_ast(out.reborrow())?;
        self.group_by.walk_ast(out.reborrow())?;
        self.having.walk_ast(out.reborrow())?;
        self.order.walk_ast(out.reborrow())?;
        self.limit_offset.walk_ast(out.reborrow())?;
        Ok(())
    }
}

// this one is for order + limit + offset
impl<F, S, D, W, G, H, L, O, Of> MssqlOrderLimitOffsetHelper<OrderClause<O>, LimitOffsetClause<LimitClause<L>, OffsetClause<Of>>>
    for SelectStatement<F, S, D, W, OrderClause<O>, LimitOffsetClause<LimitClause<L>, OffsetClause<Of>>, G, H, LC>
where
    F: QueryFragment<Mssql>,
    S: QueryFragment<Mssql>,
    D: QueryFragment<Mssql>,
    W: QueryFragment<Mssql>,
    OrderClause<O>: QueryFragment<Mssql>,
    LimitOffsetClause<LimitClause<L>, OffsetClause<Of>>: QueryFragment<Mssql>,
    G: QueryFragment<Mssql>,
    H: QueryFragment<Mssql>,
    LC: QueryFragment<Mssql> {
    fn walk_ast<'b>(
        &'b  self,
        mut out: diesel::query_builder::AstPass<'_, 'b, Mssql>,
    ) -> diesel::QueryResult<()> {
        out.push_sql("SELECT ");
        self.distinct.walk_ast(out.reborrow())?;
        self.select.walk_ast(out.reborrow())?;
        self.from.walk_ast(out.reborrow())?;
        self.where_clause.walk_ast(out.reborrow())?;
        self.group_by.walk_ast(out.reborrow())?;
        self.having.walk_ast(out.reborrow())?;
        self.order.walk_ast(out.reborrow())?;
        self.limit_offset.walk_ast(out.reborrow())?;
        Ok(())
    }
}

// this one is only for limit using the top syntax
impl<F, S, D, W, G, H, L> MssqlOrderLimitOffsetHelper<NoOrder, LimitOffsetClause<LimitClause<L>, NoOffsetClause>>
    for SelectStatement<F, S, D, W, NoOrder, LimitOffsetClause<LimitClause<L>, NoOffsetClause>, G, H, LC>
where
    F: QueryFragment<Mssql>,
    S: QueryFragment<Mssql>,
    D: QueryFragment<Mssql>,
    W: QueryFragment<Mssql>,
    NoOrder: QueryFragment<Mssql>,
    LimitOffsetClause<LimitClause<L>, NoOffset>: WalkAsTop,
    G: QueryFragment<Mssql>,
    H: QueryFragment<Mssql>,
    LC: QueryFragment<Mssql> {
    fn walk_ast<'b>(
        &'b  self,
        mut out: diesel::query_builder::AstPass<'_, 'b, Mssql>,
    ) -> diesel::QueryResult<()> {
        out.push_sql("SELECT ");
        self.distinct.walk_ast(out.reborrow())?;
        self.limit_offset.walk_ast_as_top(out.reborrow())?;
        self.select.walk_ast(out.reborrow())?;
        self.from.walk_ast(out.reborrow())?;
        self.where_clause.walk_ast(out.reborrow())?;
        self.group_by.walk_ast(out.reborrow())?;
        self.having.walk_ast(out.reborrow())?;
        self.order.walk_ast(out.reborrow())?;
        self.limit_offset.walk_ast(out.reborrow())?;
        Ok(())
    }
}

// yet another helper trait
pub trait WalkAsTop {
    fn walk_ast_as_top<'b>(
        &'b  self,
        mut out: diesel::query_builder::AstPass<'_, 'b, Mssql>,
    ) -> diesel::QueryResult<()>;
}

impl<L> WalkAsTop for LimitOffsetClause<LimitClause<L>, NoOffsetClause>
where
    L: QueryFragment<Mssql>,
{
    fn walk_ast_as_top<'b>(
        &'b self,
        mut out: diesel::query_builder::AstPass<'_, 'b, Mssql>,
    ) -> diesel::QueryResult<()> {
        out.push_sql(" TOP(");
        self.limit_clause.0.walk_ast(out.reborrow())?;
        out.push_sql(") ");
        Ok(())
    }
}

// finally the "real" queryfragment impl that just forwards to the other impls above
// This will reject invalid combinations at compile time now, so offset without limit and order won't compile
impl<F, S, D, W, O, LOf, G, H, LC> QueryFragment<Mssql, MssqlSelectSyntax>
    for SelectStatement<F, S, D, W, O, LOf, G, H, LC>
where
    Self: MssqlOrderLimitOffsetHelper<O, LOf>
{
    fn walk_ast<'b>(
        &'b self,
        out: diesel::query_builder::AstPass<'_, 'b, Mssql>,
    ) -> diesel::QueryResult<()> {
        <Self as MssqlOrderLimitOffsetHelper<O, LOf>>::walk_ast(self, out)
    }
}

That should fix the unboxed select implementation, while

In addition there is the boxed variant, that likely needs some special care as we cannot detect at compile time there if all clauses are there. I'm not sure if there is a variant that would allow to emulate meaningful behaviour if one of the clauses is missing. For example for Mysql we just use a absurdly large limit in case that limit is missing if someone tries to use offset, although I'm not sure how that would work with order? Maybe just order by 1 to order by the first field in the query?

[delsehi]

Hi again.

I might be mistaken, but is it possible that OrderClause, NoOrder and NoOffset would have to be made public under "i-implement-a-third-party-backend-and-opt-into-breaking-changes" for this to work?

[weiznich]

That's correct and that is something which needs to change in diesel. Mind opening a PR for that? Otherwise I will try to address this later this week.

[delsehi]

Your solution worked fine! I'm struggling with the boxed select as expected though. I feels like a usable solution is so close but still so far away 😅

I'm a bit too novice to figure it out I think. It looks like I could make the boxed select work similarly to the limit-offset for MySQL, but wouldn't I need to have access to Order to match correctly in there? When reading the code, it looks like that could solve my problem because I could solve it with "order by 1" like you say or probably even better "ORDER BY (SELECT NULL)". I'm guessing it's not possible to solve it as elegantly like you did with WalkAsTop for the boxed version?
I also think a runtime error is quite acceptable if you're doing something that's not really possible in mssql, but maybe that's not in the spirit of Diesel.

I'm working on this in my free time so I'm considering just dropping support for boxed select statements. If it's possible to map raw sql queries to structs like it is for postgres and mysql this is still a huge win in my opinion over just using raw Tiberius where that is very cumbersome and boilerplatey.

If I'd make a PR adding order to boxed offset-limit, would you consider merging that?

[weiznich]

Thanks for the update. I've opened delsehi/diesel-mssql#1 for the boxed select, turns out that it should just work. (I've not tested this as I've no mssql installed, but it should generate reasonable queries in all cases).

If I'd make a PR adding order to boxed offset-limit, would you consider merging that?

Sure, that's the reason because of which most of that stuff is behind the unstable feature flag. More general speaking: I'm open to change almost anything in these internal structures if it makes implementing a custom backend like this easier. The current implementation is essentially just the result of the requirements from any existing backend implementations. For each new backend implementation we make certain structures more configurable, so this wouldn't be unexpected.

Especially for order/limit/offset it might be reasonable to change that to be one struct instead of a struct + a separate field. It shouldn't make things harder for other backends and I can see how it would simplify the code in diesel-mssql