Skip to content

Instantly share code, notes, and snippets.

@cfsamson
Last active March 23, 2019 10:54
Show Gist options
  • Save cfsamson/d1232f11d6809a40c4b9bf772dd8d14c to your computer and use it in GitHub Desktop.
Save cfsamson/d1232f11d6809a40c4b9bf772dd8d14c to your computer and use it in GitHub Desktop.
//! Database-related logic, such as creating connections, CRUD on data
use futures::*;
use futures_state_stream::StateStream;
use tiberius::{BoxableIo, query::QueryRow, SqlConnection};
// I just uncomment so RLS is happy
// use crate::proto::rolemanagement::v1::*;
// lets return a future here instead that we can then chain future futures onto
pub fn connect() -> Box<Future<Item = SqlConnection<Box<BoxableIo>>, Error = Error> + Send> {
let conn_str = "server=tcp:localhost,1433;integratedSecurity=false;username=sa;password=password;".to_owned();
// when you want to chain futures, you'll want to chain on a "future" of a connection, not the connection itself.
SqlConnection::connect(conn_str.as_str())
}
pub struct RoleDatabase;
// let me just use this for now so
#[derive(Default)]
struct Role {
id: i32,
role_id: String,
role_name: String,
role_desc: String,
org_id: String,
is_active: bool
}
struct FilterCriteria;
// --- a trait that I can then implemet fns over
pub trait DataManager {
// this looks correct yes, but see comment below if you want to pass these futures
// to another framework that will drive them to finish.
type RoleResult = Box<Future<Item=Role, Error=tiberius::Error>>;
type RoleResults = Box<Future<Item=Vec<Role>, Error=tiberius::Error>>;
// this part looks good, but my first issue when using Tiberius and passing the futures
// from Tiberius to another framework is that I have issues with the error types.
// So what I have to do is to map tiberius::Error to an error type the framework
// will accept.
// I'm don't think Tiberius implements best practices here for error types and therefore doesn't
// impl std::error::Error or std::fmt::Display for the error types. But it does implement Debug.
// as an example - i have to do this when passing the future to Acitx Web, you should probably uncomment the code sections here
// but keep the comments to make it easier to read.
// ```
// type SqlFuture<T> = Box<dyn Future<Item = Vec<T>, Error = actix_web::Error>>;
// pub fn get_sale_pr_month(
// &self,
// start_date: NaiveDate,
// end_date: NaiveDate,
// department: String,
// ) -> SqlFuture<SaleMonth> {
// let sql = include_str!("sql/ms_sale_by_month.sql");
// let from = format!("{}", utility::get_first_date_in_month(start_date));
// let to = format!("{}", utility::get_last_date_in_month(end_date));
// let buff = SqlConnection::connect(&self.conn)
// .and_then(move |conn| {
// conn.query(sql, &[&from.as_str(), &to.as_str(), &department.as_str()])
// .map(move |row| {
// let month: i32 = row.get("month");
// let sale: f64 = row.get("sale");
// let sale: u64 = sale as u64;
// let year: i32 = row.get("year");
// let sale_month = SaleMonth {
// month,
// sale,
// department: department.clone().to_string(),
// year,
// };
// Ok(sale_month)
// })
// .map(|mapped_row: Result<SaleMonth, tiberius::Error>| {
// result_handler(mapped_row)
// })
// .collect()
// })
// .and_then(|(mapped_rows, conn)| Ok(mapped_rows))
//
// // NOTICE THIS PART
// // you could `match` on the tiberius::Error type and map it to your own error type or to
// // another type your framework will accept. I just return a BadRequest with an error message since that
// // was ok for my use case. Just returning the tiberius::Error will most likely not work.
// .map_err(|e| actix_web::error::ErrorBadRequest(format!("DB error: {:?}", e)));
// Box::new(buff)
// }
// This is probably not best practice but it's what I used in the code for debugging so I just add it here instead of
// rewriting. It's a way to inspect the error and return a default type if there was an query error. You don't really need to do exactly this, but I
// left it in since it was in my original code, but again since the Error here is a tiberius::Error (at least if my memory is correct)
// you'll probably want to map it here to your own error type or something that implements std::error::Error and std::fmt::Display.
// The alternative would be to defer that to later in the process and match on the tiberius::Error enum later. However, since it's a
// pain to work with I like to return errors that implement std::error::Error so I don't need to pull in Tiberius everywhere and
// rather do all the mapping here either to my own type or to something of the type of the library I'm using (I could map to an actix-web
// error type here as well for example if thats the only library that will use the data). Most likely creating your own error type
// that confirms to best practice and implementing a From<tiberius::Error> for it will be the "correct" solution if you want to make this
// robust. It's not difficult and [this blog post is pretty much all you need](https://blog.burntsushi.net/rust-error-handling/#standard-library-traits-used-for-error-handling)
// fn result_handler<T: Default, E: Debug>(res: Result<T, E>) -> T {
// match res {
// Ok(r) => r,
// Err(e) => {
// println!("SaleMonth error: {:?}", e);
// T::default()
// }
// }
//}
// ```
fn create_role(&self, role: Role) -> Self::RoleResult;
// --- fn update_role(&self, role: Role) -> Self::RoleResult;
// --- fn get_role(&self, id: i32) -> Self::RoleResult;
// --- fn disable_role(&self, id: i32) -> Self::RoleResult;
// --- fn get_roles(&self, criteria: FilterCriteria) -> Self::RoleResults;
}
impl DataManager for RoleDatabase {
// see my comment above
type RoleResult = Box<Future<Item=Role, Error=tiberius::Error>>;
type RoleResults = Box<Future<Item=Vec<Role>, Error=tiberius::Error>>;
fn create_role(&self, role: Role) -> Self::RoleResult {
// connect() is now returning a future we can chain onto with `and_then`
Box::new(future::result(connect().and_then(|conn| {
conn.query("INSERT INTO roleservice.role (
role_id, role_name, role_desc, org_id, is_active
) values (@P1, @P2, @P3, @P4, @P5)",
&[&role.role_id, &role.role_name, &role.role_desc, &role.org_id, &role.is_active.to_string()]
// I added to_string() to the boolean paramteter
// tiberius doesnt seem tom implement ToSql for bool. You might have to parse this to something
// that MSSQL understands, but I _think_ its `to_string()` representation is "true" or "false" which should
// work.
)
// you don't need into_future here, this should work but see my comments below
.map(|_| {
// se my example above for mapping
Role { ..Default::default() }
})
})))
}
}
// now create_role() returns a future that you can drive to completion BUT do notice what I wrote about
// the tiberius error types. They made me stuck for hours in the start complicating things a lot. You'll probably
// have to map the error type to something else
/// Now onto two more thing that I thought I'd mention that I used hours on to figure out:
///
/// # Using streams
///
/// If you ever need to call a method that returns a ResultSetStream [https://docs.rs/tiberius/0.3.1/tiberius/struct.SqlConnection.html#method.simple_exec]( https://docs.rs/tiberius/0.3.1/tiberius/struct.SqlConnection.html#method.simple_exec)
/// you'll need to call into_stream() twice to actually access the result sets
///
/// Note: I see Tiberius has had a release since last time I used this so they _might_ have changed that. However
/// nice to know since it's not very easy to figure out reading the docs
/// ```
/// let future = SqlConnection::connect(conn_str).and_then(
/// |conn: SqlConnection<Box<BoxableIo>>| {
/// conn.simple_exec(&sql)
/// .into_stream()
/// .into_stream()
/// .for_each(|resp| {
/// resp.and_then(|i| {
/// print!("{}", i);
/// Ok(())
/// })
/// })
/// },
/// );
/// ```
///
/// # Reusing the connection
/// It seems like all methods on SqlConnection consumes the connection, but when you get a future in return
/// calling conn.and_then(...) you actually get a tuple in return where the second value is the future of the
/// connection which you then can chain more operations onto. I haven't actually gotten around to using this a
/// lot but I noticed some time back and thought I'd mention it. Don't hang me if this somehow doesn't work
/// as expected and you need to create a new connection for every operation.
///
/// let fut1 = conn.and_then(|conn| {
/// conn.exec("DELETE FROM somtable", &[])
/// });
///
/// fut1.and_then(|(_,conn)| {
/// let conn: SqlConnection<Box<BoxableIo>> = conn;
/// conn.exec(
/// "INSERT INTO () VALUES ()",
/// &[]);
/// Ok(())
/// });
///
impl RoleDatabase {
// TODO some impls here
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment