Toql is an ORM for async databases that features

• Translation between Rust structs and database tables.
• Can load and modify nested structs.
• A unique dead simple query language, suitable for web clients.
• Different table aliases from long and readable to tiny and fast.
• Prepared statements against SQL injection.
• Support for raw SQL for full database power.
• Support for role based access.
• Highly customizable through user defined parameters, query functions, field handlers, etc.
• Compile time safety for queries, fields and path names.
• No unsafe Rust code.
• Tested on real world scenario.

This guide will explain you how to use Toql in your own project.

Toql is free and open source software, distributed under a dual license of MIT and Apache. The code is available on Github. Check out the API for technical details.

• Toql is pronounced to-cue-ell with o as in object.
• Toql stands for Transfer Object Query Language and refers to the query language that is unique to this ORM. In a sense though it's a missleading name, because Toql together with Serde effectively avoid the need for data transfer objects (DTO): you pass your model directly.
• The project's mascot is a bollard, because Toql pronounced in allemanic / swiss german sounds like Toggel: A funny word that can colloquially be used for bollards.

Toql is a ORM that aims to boost your developer comfort and speed when working with databases.

To use it you must derive Toql for all structs that represent a table in your database:

• A field in those structs represents either a columns, an SQL expression or a relationship to one or many tables.
• The field also determines the field name or in case of a relationship the path name in the Toql query

A struct may map only some columns of a table and also multiple structs may refer to the same table. Structs are rather 'views' to a table.

A derived struct can then be inserted, updated, deleted and loaded from your database. To do that you must call the Toql API functions with a query string or a list of field names or paths.

Here the typical flow in a web environment:

1. A web client sends a Toql query to the REST Server.
2. The server uses Toql to parse the query and to create SQL statements.
3. Toql sends the SQL to the database
4. then deserializes the resulting rows into Rust structs.
5. The server sends these structs to the client.

There is full featured REST server based on Rocket, Toql and MySQL. It can be used as a playground or starting point for own projects.

Toql relies on backends to handle database differences. These backends implement the ToqlApi trait which serves as an entry point for any high level function. The backends internally then use the Toql library to do their job.

This chapter explains how to use the ToqlApi trait. Notice that you must derive your structs before you can load or modify them with the ToqlApi. See the derive chapter for details.

The common ToqlApi trait makes it also possible to write database independend code. This is described here.

To use the ToqlApi functions you need a Toql backend for your database.

Currently the following backends are available

For MySQL add this to your cargo.toml:

[dependency]
toql = "0.4"
toql_mysql_async = "0.4"

You must add toql together with the backend crate. The backend crate then depends on a suitable version of the driver crate. Normally there is no need to access the driver crate. However I you really must, the backend crate re-exports the driver crate. For toql_mysql_async the driver crate can be accessed through toql_mysql_async::mysql_async.

With these two dependencies you can get the backend in your code. Notice that the backend takes a database connection and a cache object to hold the database mapping.

# #![allow(unused_variables)]

#fn main() {
use toql::prelude::Cache;
use toql::mock_db::MockDb;

let cache = Cache::new();
let mut toql = MockDb::from(&cache);

// For MySQL
// use toql_mysql_async::{prelude::MySqlAsync, mysql_async};
//
// let database_url = "mysql://USER:PASS@localhost:3306/DATABASE";
// let pool = mysql_async::Pool::new(database_url);
// let mut conn = pool.get_conn().await.unwrap();
// let toql = MySqlAsync::from(&mut conn, &cache);


#}

In a bigger project you may want to feed configuration or authentication values into your SQL. This is done through so called auxiliary parameters (aux params).

There are three ways to feed in aux params:

• You can put them in the context and they will be available as long as the backend object lives
• You can also ship them with a query and they will be available only for that query
• You can map aux params to a field. Used to configure field handlers.

Here how to put them in the context:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{Cache, ContextBuilder};
use toql::mock_db::MockDb;
use std::collections::HashMap;

let mut p = HashMap::new();
p.insert("page_limit".into(), 200.into());

let context = ContextBuilder::new().with_aux_params(p).build();
let cache = Cache::new();
let mut toql = MockDb::with_context(&cache, context);

// For MySQL
// use toql_mysql_async::{prelude::MySqlAsync, mysql_async};
//
// let database_url = "mysql://USER:PASS@localhost:3306/DATABASE";
// let pool = mysql_async::Pool::new(database_url);
// let mut conn = pool.get_conn().await.unwrap();
// let toql = MySqlAsync::with_context(&mut conn, &cache, context);

#}

Beside aux params ContextBuilder allows you

• to choose an alias format (user.id, us1.id, t1.id, ...)
• set the roles for access control

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{ContextBuilder, AliasFormat};
use std::collections::HashSet;

let mut roles = HashSet::new();
roles.insert("teacher".to_string());
roles.insert("admin".to_string());

 let context = ContextBuilder::new()
   .with_alias_format(AliasFormat::TinyIndex)
   .with_roles(roles)
   .build();
#}

There are three loading functions: load_one, load_many and load_page.

All loading functions take a Toql query and will translate it into SQL SELECT statement(s). The resulting rows will be deserialized and returned.

If your Toql structs contain a Vec of other structs, the load functions issue multiple SELECT statements and merge the results.

If you expect exactly one result, use load_one.

    use toql::prelude::{query, ToqlApi, Cache, Toql, ToqlError};
    use toql::mock_db::MockDb;
  
    #[derive(Toql)]
    struct User {
      #[toql(key)]
      id: u64,
      title: Option<String>
    }

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
    let cache = Cache::default();
    let mut toql = MockDb::from(&cache);
   
    let query = query!(User, "*, id eq 1");
    let users = toql.load_one(query).await;

    assert_eq!(users.err().unwrap(), ToqlError::NotFound);
#    }

The function will return ToqlError::NotFound if no row matched the query filter or ToqlError::NotUnique if more than one row matched. To load zero or one row use load_page, see below.

Similarly, if you need to load multiple rows use load_many, that returns a Vec with deserialized rows. The Vec will be empty, if no row matched the filter criteria.

load_page allows you to select a page with a starting point and a certain length. It returns a Vec and count information.

The count information is either None for an uncounted page or contains count statistics that is needed for typical pagers in web apps, see below. (After all Toql was initially created to serve web pages.)

To load the first 10 -or less- rows do this:

    use toql::prelude::{query, ToqlApi, Cache, Toql, Page};
    use toql::mock_db::MockDb;

     #[derive(Toql)]
    struct User {
      #[toql(key)]
      id: u64,
      title: Option<String>
    }

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
    let cache = Cache::default();
    let mut toql = MockDb::from(&cache);
   
    let query = query!(User, "*");
    let (users, count_info) = toql.load_page(query, Page::Uncounted(0, 10)).await.unwrap();

    assert!(users.is_empty());
    assert!(count_info.is_none());
# }

To serve a webpage, you may also want to include count informations, call the above with

    let (u, c) = toql.load_page(q, Page::Counted(0, 10)).await.unwrap();

The code is almost the same, but the different page argument will issue two more select statements to return the filtered page length and the total page length. Let's see what those are:

Suppose you have a table with books. The books have an id, a title and an author_id.

Let's assume we have a webpage that contains a pager with page size 2 and a pager filter. The author wants to see all books that contain the word world. What will he get?

• The first two rows (id 1, id 2).
• The filtered page count of 3, because 3 rows match the filter criteria. The pager can now calculate the number of pages: ceil(3 / 2) = 2
• The total page count of 4. The author knows now that with a different filter query, he could get at most 4 rows back.

In practice the total page count is not so straight forward to select: Toql needs to decide, which filters from the query to consider or ignore when building the count SQL statement. For the total page count only filters are used on fields that are listed in the special count selection and predicates that are marked as count filters.

All load functions need a query, but how is this build?

The recommended way is to use the query! macro.

Alternatives are

• to create a new Query<T> object and use its builder methods
• or to parse a string

This chapter does not explain the Toql query language itself, see here to learn about that.

The query! macro will compile the provided string into Rust code. Any syntax mistakes, wrong path or field names show up as compiler errors!

Queries are typesafe, so query! takes a type and a query expression. See here:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{query, Toql};

#[derive(Toql)]
struct User {
    #[toql(key)]
    id: u64,
    name: String
}

let user_id = 5;
let q = query!(User, "*, id eq ?",  user_id);
#}

To include query parameters just insert a question mark in the query string and provide the parameter after the string.

In the example above it would also be possible to put the number 5 directly into the query string, since it's a constant. The resulting SQL would be the same as Toql extracts the parameter in either case to prevent SQL injections.

The Toql query only works with numbers and strings, see SqlArg. However this is not be a problem: Since database columns have a type, the database is able convert a string or number into its column type.

It's also possible to include other queries into a query. Consider this:

# #![allow(unused_variables)]
#fn main() {
#   use toql::prelude::Toql;
#   #[derive(Toql)]
#   struct User {
#       #[toql(key)]
#       id: u64,
#       name: String
#   }
use toql::prelude::query;
let q1 = query!(User, "id eq ?", 5);
let q = query!(User, "*, {}", q1);
#}

Here we include the query q1 into q. Since queries are typesafe, so you can only include queries of the same type.

When entities have composite keys or you want to write generic code it's easier to work with keys. Key structs are automatically derived from the Toql derive and are located where the struct is. Keys contain all fields from the struct that are marked with #[toql(key)].

With a single key this is possible

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{query, Query};
# use toql::prelude::Toql;
#   #[derive(Toql)]
#   struct User {
#       #[toql(key)]
#       id: u64,
#       name: String
#   }

let k = UserKey::from(5); // Easier than UserKey{id:5};
let q1 = query!(User, "id eq ?", &k);
let q2 = query!(User, "*, {}", Query::from(&k));
let q3 = query!(User, "*, {}", k);
#}

With multiple keys you can do this:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{query, Query};
#   use toql::prelude::Toql;
#   #[derive(Toql)]
#   struct User {
#       #[toql(key)]
#       id: u64,
#       name: String
#   }

let ks = vec![UserKey::from(1), UserKey::from(2)];

let q4 = query!(User, "*, id in ?", &ks);

let qk = ks.iter().collect::<Query<_>>();
let q5 = query!(User, "*, {}", qk);
#}

The query q4 only works for a simple key, not a composite key, whereas qk works for any type of key.

If you deal with entities you can get their keys from them (notice the Keyed trait):

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{query, Keyed, Query};
#   use toql::prelude::Toql;
#   #[derive(Toql)]
#   struct User {
#       #[toql(key)]
#       id: u64,
#       name: String
#   }
let e = User{id:1, name: "User 1".to_string()};

let q5 =  query!(User, "{}", e.key());
let q6 =  Query::from(e.key());
#}

Both q5 andq6 end up the same.

Or with mutiple entities:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{query, MapKey, Query};
#   use toql::prelude::Toql;
#   #[derive(Toql)]
#   struct User {
#       #[toql(key)]
#       id: u64,
#       name: String
#   }

let es = vec![ User{ id:1, name:"User 1".to_string()}, 
                User{ id:2, name: "User 2".to_string()}];

let qk = es.iter().map_key().collect::<Query<_>>();
let q7 = query!(User, "*, {}", qk);
#}

Do you like the collect style? There is a nifty implementation detail: If you collect keys, they will always be concatenated with OR, queries however will be concatenated with AND.

Compare q8 and q10 here:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{query, Query};
#   use toql::prelude::Toql;
#   #[derive(Toql)]
#   struct User {
#       #[toql(key)]
#       id: u64,
#       name: String
#   }

let ks = vec![UserKey{id:5}, UserKey{id:6}];
let q8 :Query<User> = ks.into_iter().collect(); 
assert_eq!(q8.to_string(), "(id EQ 5;id EQ 6)");

let q9 = query!(User, "name");
let q10 :Query<User> = vec![q9, q8].into_iter().collect(); 
assert_eq!(q10.to_string(), "name,(id EQ 5;id EQ 6)");

#}

In the example above the query q3 is build with a UserKey. This is possible because UserKey implements Into<Query<User>>. You can also implement this trait for you own types. Let's assume a book category.

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{query, Query, Toql};

#[derive(Toql)]
struct Book {
    #[toql(key)]
    id: u64,
    category: Option<String>
}

enum BookCategory {
    Novel,
    Cartoon
}
impl Into<Query<Book>> for BookCategory {
    fn into(self) -> Query<Book> {
       query!(Book, "category EQ ?", 
       match self {
        Novel => "NOVEL",
        Cartoon => "CARTOON"    
       })
    }
}

// Now use it like so
let q = query!(Book, "*, {}", BookCategory::Novel);
assert_eq!(q.to_string(), "*,category EQ 'NOVEL'");
#}

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{QueryWith, Query, Field, QueryFields, Toql};
   
#[derive(Toql)]
struct Book {
    #[toql(key)]
    id: u64,
    category: Option<String>,
    #[toql(join)]
    author: Option<User>
}
#[derive(Toql)]
struct User {
    #[toql(key)]
    id: u64,
    name: Option<String>,
}

struct Auth {
    user_id: u64
}
impl Into<Query<Book>> for Auth {
    fn into(self) -> Query<Book> {

        // This time with the builder methods for educational reasons
        // In production do `query!(User, "author_id eq ?", self.user_id)`
        Query::from(Book::fields().author().id().eq(self.user_id))
    }
}
#}

You may want trade typesafety for more flexibility. See the example above again, this time with the Field type.

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{Query, Field};

struct Auth {
    author_id: u64
}

impl<T> Into<Query<T>> for Auth {
    fn into(self) -> Query<T>{
        Query::from(Field::from("authorId").eq(self.author_id))
    }
}
#}

Wrong field names in Field::from do not show up at compile time, but at runtime.

You can use both examples like so:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::query;
#   use toql::prelude::{Query, Field, Toql};
#   enum BookCategory {
#       Novel,
#       Cartoon
#   }
#   #[derive(Toql)]
#   struct Book {
#       #[toql(key)]
#       id: u64,
#       category: Option<String>
#   }
#   struct Auth {
#       author_id: u64
#   }
#   impl<T> Into<Query<T>> for Auth {
#       fn into(self) -> Query<T>{
#           Query::from(Field::from("authorId").eq(self.author_id))
#       }
#   }

let auth = Auth {author_id: 5};
let q = query!(Book, "*, {}", auth);
assert_eq!(q.to_string(), "*,authorId EQ 5");
#}

The query! macro produces a Query type and can further be altered using all methods from that type. One interesting method is with. It takes a QueryWith trait that can be implemented for any custom type to enhance the query. This is more powerful than Into<Query> because you can also access auxiliary parameters.

Aux params can be used in SQL expressions. See here more information.

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{QueryWith, Query};

struct Config {
    limit_pages: u64
}
impl<T> QueryWith<T> for Config {
    fn with(&self, query: Query<T>) -> Query<T> {
        query.aux_param("limit_pages", self.limit_pages)
    }
}
#}

This can now be used like so:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{query, SqlArg};
#   use toql::prelude::{Toql, Query, QueryWith};
#   #[derive(Toql)]
#   struct User {
#       #[toql(key)]
#       id: u64,
#       name: String
#   }
#   struct Config {
#       limit_pages: u64
#   }
#   impl<T> QueryWith<T> for Config {
#       fn with(&self, query: Query<T>) -> Query<T> {
#           query.aux_param("limit_pages", self.limit_pages)
#       }
#   }

let config = Config {limit_pages: 200};
let k = UserKey::from(5);
let q = query!(User, "*, {}", k).with(config);
assert_eq!(q.to_string(), "*,id EQ 5");
assert_eq!(q.aux_params.get("limit_pages"), Some(&SqlArg::U64(200)));
#}

Use the query parser to turn a string into a Query type. Only syntax errors will returns as errors, wrong field names or paths will be rejected later when using the query.

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::QueryParser;
#   use toql::prelude::Toql;
#   #[derive(Toql)]
#   struct User {
#       #[toql(key)]
#       id: u64,
#       name: String
#   }
let s = "*, id eq 5";

let q = QueryParser::parse::<User>(s).unwrap();
assert_eq!(q.to_string(), "*,id EQ 5");
#}

There are two insert functions: insert_one, and insert_many.

The are used like so:

#   #[tokio::main(flavor="current_thread")]
#   async fn main(){
use toql::prelude::{Cache, Toql, ToqlApi, paths};
use toql::mock_db::MockDb;

#[derive(Toql)]
#[toql(auto_key)]
struct User{
    #[toql(key)]
    id: u64,
    name: String,
    address: Option<String>
}

let cache = Cache::new();
let mut toql = MockDb::from(&cache);

let mut u = User { 
                id:0, 
                name: "Joe".to_string(), 
                address: None
            };
toql.insert_one(&mut u, paths!(top)).await.unwrap();
assert_eq!(toql.take_unsafe_sql(), "INSERT INTO User (name, address) VALUES ('Joe', DEFAULT)");
assert_eq!(u.id, 100);

toql.insert_one(&mut u, paths!(User, "")).await.unwrap();
toql.insert_many::<User,_>(&mut [&mut u], paths!(top)).await.unwrap();
#   }

In the example above the first insert_one will insert u into the database, load back the generated id and sets it on u. The second insert_one makes a copy of u and again refreshes its id field.

Optional fields that are None will insert the default value of the database. See the mapping for details.

The paths! macro compiles a path list. Any invalid path will show up at compile time. The insert functions insert all referenced joins and merges from such a path list.

Let's assume a struct User that has a Vec of books with each book joining a publisher. A path list like paths!(User, "books_publisher") will

• insert all fields from User.
• if the id of User is marked as auto key, Toql will load the generated id from the database and update User and the composite key in books.
• insert all fields from the joined publisher.
• handle the id of publisher like the one in books.
• insert the merged books with the correct composite keys.

Cool stuff!

If you only want to insert a publisher, then you must call insert on a publisher object.

The paths in the list may overlap. In the path list paths!(User, "books_publisher, books_printedBy") book appears twice. No problem it will only be inserted once.

When inserting a struct some fields are excluded:

• Fields that map to an SQL expression
• Fields or structs with the atribute skip_mut
• Joins with custom ON predicate
• Merges with custom sql_join

It is possible to split up a table into multiple tables sharing the same primary key. See here.

If a path in the path list refers to a struct that contains joins marked as partial table then these joins will also be inserted. There is no need to mention these dependencies in the path list.

The order of SQL execution is based on key dependencies between structs.

There are two update functions: update_one and update_many.

They are used like so:

#   #[tokio::main(flavor="current_thread")]
#   async fn main(){
use toql::prelude::{Cache, Toql, ToqlApi, fields};
use toql::mock_db::MockDb;

#[derive(Toql)]
#[toql(auto_key)]
struct User{
    #[toql(key)]
    id: u64,
    name: String,
    address: Option<String>
}

let cache = Cache::new();
let mut toql = MockDb::from(&cache);

let mut user = User {id:27, name: "Susan".to_string(), address: None};

toql.update_one(&mut user, fields!(top)).await.unwrap();
assert_eq!(toql.take_unsafe_sql(), "UPDATE User SET name = \'Susan\' WHERE id = 27");

toql.update_one(&mut user, fields!(User, "*")).await.unwrap();

toql.update_many::<User, _>(&mut [&mut user], fields!(top)).await.unwrap();
toql.update_many(&mut [user], fields!(top)).await.unwrap();
#   }

In the example above all four statements do the same.

The fields! macro compiles a list of fields. Any invalid path or field name shows up at compile time.

The update function will consider all fields from the field list to update. Optional fields will only be updated if they contain some value. See the mapping for details.

You can update only the foreign key of a join or field from the join. Consider this field list:

let f = fields!(User, "*, address, address_*, address_id");

With * we consider all fields from User for updating, address will update the foreign key to Address in the User table, address_* will update all simple fields in table Address and finally address_id is ignored, since keys cannot be updated.

Notice in the example above address is actually a duplicate, because foreign keys are included in *. It's just mentioned explicitly for the purpose of learning.

Updates can either

• update existing structs in a Vec
• or insert new structs in the Vec and delete removed structs.

Consider this field list:

let f = fields!(User, "*, books, books_*");

• With * we consider all simple fields from User for updating (this excludes merges),
• books resizes the Vec: It deletes all books that are linked to the user but are not found in the books vector and it inserts new book (toghether with possible partial joins).
• books_* will update all simple fields in the existing books.

#   #[tokio::main(flavor="current_thread")]
#   async fn main(){
    use toql::prelude::{Cache, Toql, ToqlApi, fields};
    use toql::mock_db::MockDb;

    #[derive(Debug, PartialEq, Toql)]
    struct Book {
        #[toql(key)]
        id: u64,
        #[toql(key)]
        user_id: u64,
        title: Option<String>
    }

    #[derive(Debug, PartialEq, Toql)]
     #[toql(auto_key)]
    struct User {
        #[toql(key)]
        id: u64,
        name: Option<String>,
        #[toql(merge())]
        books : Option<Vec<Book>>
    }
    
    let cache = Cache::new();
    let mut toql = MockDb::from(&cache);

    let mut user = User {
        id: 27,
        name: Some("Joe".to_string()),
        books: Some(vec![
            Book{
                id: 100,
                user_id: 0,  // Invalid key
                title: Some("King Kong".to_string())
            },
            Book{
                id: 200,
                user_id: 27,
                title: Some("Batman".to_string())
            }
        ])
    };

    toql.update_one(&mut user, fields!(User, "*, books, books_*")).await.unwrap();
    assert_eq!(toql.take_unsafe_sqls(), 
   ["UPDATE Book SET title = 'Batman' WHERE id = 200 AND user_id = 27", 
    "UPDATE User SET name = 'Joe' WHERE id = 27",
    "DELETE user_books FROM Book user_books \
        JOIN User user ON user.id = user_books.user_id \
        WHERE user.id = 27 AND NOT (user_books.id = 200 AND user_books.user_id = 27)", 
    "INSERT INTO Book (id, user_id, title) VALUES (100, 27, 'King Kong')"]
    );

#   }    

To mark new books, add them with an invalid key. A value of 0 or an empty string '' is considered invalid. Normally databases start counting indexes from 1 and some databases consider an empty string like null, which is also forbidden as primary key. So this idea of invalid key should normally work, however check with you database.

In rare cases where this does not work. Insert and delete your Vec manually, using the ToqlApi functions.

In the example above the first book has an invalid composite key (id, user_id), because user_id is 0. Toql will notice that and insert a new book (with the correct user_id of 27). From the second book with id 200 the field title will be updated.

There are two delete functions: delete_one, and delete_many.

Both functions take a predicate and delete all rows that match that predicate.

delete_one takes a key. It will build a filter predicate from that and delete the row that coresponds to the key.

delete_many builds a predicate from the filters of the Query argument. Field filters and predicates and considered, whereas field selects and selections in the query are ignored. See the query language for details.

#   #[tokio::main(flavor="current_thread")]
#   async fn main(){
use toql::prelude::{ToqlApi, Cache, Toql, query, Keyed};
use toql::mock_db::MockDb;

#[derive(Toql)]
#[toql(auto_key)]
struct User{
    #[toql(key)]
    id: u64,
    name: String,
}

let cache = Cache::new();
let mut toql = MockDb::from(&cache);

toql.delete_one(UserKey::from(5)).await.unwrap();
assert_eq!(toql.take_unsafe_sql(), "DELETE user FROM User user WHERE user.id = 5");

let u = User {id: 5, name: "John".to_string()};
toql.delete_one(u.key()).await.unwrap();
assert_eq!(toql.take_unsafe_sql(), "DELETE user FROM User user WHERE user.id = 5");

toql.delete_many(query!(User, "id eq 5")).await.unwrap();
assert_eq!(toql.take_unsafe_sql(), "DELETE user FROM User user WHERE user.id = 5");
# }

delete does not do any cascading by itself. It just deletes rows from a single table. To cascade your deletes you must configure your database relations and tell the database what to do with your joined rows: Delete them too or just set the foreign key to NULL.

Check the manual for

• MySQL
• Postgres

TODO This chapter needs to be written

In bigger projects you need to structure your code with functions.

There as two common ways, each with different tradeoffs

• Pass the database driver to the function
• Pass ToqlApi to the function

If you decide to pass the database you give up on database independence, but less trait bounds are needed:

For MySQL this looks like this:

use toql::prelude::ToqlApi;
use toql_mysql_async::prelude::{MySqlAsync, Queryable};
fn do_stuff<C>(toql: &mut MySqlAsync<'_,C>) 
where C:Queryable -> Result
{
    let q = query!(...)
    let users = toql.load_many(&q).await?;
    toql.insert_many(users, paths!(top)).await?;
    toql.update_many(users, fields!(top)).await?;
    toql.delete_many(q).await?;
}

The Queryable trait makes the MySqlAsync work with a connection or a transaction.

It's also possible to pass a struct that implements ToqlApi. However this requires more trait bounds to satisfy the bounds on ToqlApi. Unfortunately rust Rust compiler has a problem with associated type bounds, so it looks more complicated than it had to be.

A struct must derive Toql. Only on a derived struct any function from the ToqlApi can be called.

This derive builds a lot of code. This includes

• Mapping of Toql fields to struct fields and database columns or expressions.
• Creating field methods for the query builder.
• Handling relationships through joins and merges.
• Creating Key structs.

With this simple code

# #![allow(unused_variables)]
#fn main() {
   use toql::prelude::Toql;
   #[derive(Toql)]
   struct User {
       #[toql(key)]
       id: u32,
       name: Option<String>
}
#}

We can now do the following

    use toql::prelude::{query, ToqlApi, Cache, Toql, Page, fields};
    use toql::mock_db::MockDb;
    use toql::row;

#    #[derive(Toql)]
#    struct User {
#      #[toql(key)]
#      id: u64,
#      age: Option<u8>
#    }

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
    
    let cache = Cache::default();
    let mut toql = MockDb::from(&cache);
    toql.mock_rows("SELECT user.id, user.age FROM User user WHERE user.id = 5", vec![row!(5u64, 27u64)]);
  
    let q = query!(User, "id eq 5, age"); 
    let mut user = toql.load_one(&q).await.unwrap(); 

    user.age = Some(16);
    toql.update_one(&mut user, fields!(top)).await.unwrap(); 
# }

Struct fields are mapped to Toql query fields and database columns by default in a predictable way:

1. Table names are UpperCamelCase.
2. Column names are snake_case.
3. Toql query fields are lowerCamelCase.
4. Toql query paths are lowerCamelCase, separated with an underscore.

To adjust the default naming to an existing database scheme use the attributes tables and columns for a renaming scheme or table and column for explicit name.

Supported renaming schemes are

• CamelCase
• snake_case
• SHOUTY_SNAKE_CASE
• mixedCase

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
use toql::prelude::{Toql, ToqlApi, query, Cache};
use toql::mock_db::MockDb;

#[derive(Toql)]
#[toql(tables="SHOUTY_SNAKE_CASE", columns="CamelCase")]
struct User {
    #[toql(key)]
    user_id: u32,
    full_name: String,
}

let cache = Cache::default();
let mut toql = MockDb::from(&cache);
let q = query!(User, "*"); 
let mut _users = toql.load_many(&q).await.unwrap(); 
assert_eq!(toql.take_unsafe_sql(), "SELECT user.UserId, user.FullName FROM USER user");
# }

Use table on the struct and column on fields to set a explicit name.

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
use toql::prelude::{Toql, ToqlApi, query, Cache};
use toql::mock_db::MockDb;
 
#[derive(Toql)]
#[toql(table="User")]
struct UserRef {

    #[toql(key, column="id")]
    user_id: u32,
    full_name: String,
} 
 
let cache = Cache::default();
let mut toql = MockDb::from(&cache);

let q = query!(UserRef, "*"); 
let mut _users = toql.load_many(&q).await.unwrap(); 
assert_eq!(toql.take_unsafe_sql(), "SELECT user.id, user.full_name FROM User user"); 

# }

Use column also when mapping a field that is a SQL keyword. Notice the back ticks:

    #[toql(column="`order`")]
    order: u32

Toql query fields on a struct are always mixed case, while dependencies are separated with an underscore.

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
use toql::prelude::{Toql, ToqlApi, query, Cache};
use toql::mock_db::MockDb;

#[derive(Toql)]
#[toql(table="User")]
struct UserRef {

    #[toql(key, column="id")]
    id: u32,

    full_name: String,

    #[toql(join)]
    country: Country
}
#[derive(Toql)]
struct Country {

    #[toql(key)]
    id: u32,
    name: String,
}

let cache = Cache::default();
let mut toql = MockDb::from(&cache);

let q = query!(UserRef, "id, fullName, country_id"); 
let mut users = toql.load_many(&q).await.unwrap(); 
assert_eq!(toql.take_unsafe_sql(), 
        "SELECT user.id, user.full_name, user_country.id, user_country.name \
            FROM User user \
            JOIN (Country user_country) \
            ON (user.country_id = user_country.id)");

# }

Fields can be excluded in several ways

• skip excludes a field completely from the table, use for non-db fields.
• skip_mut ensures a field is never updated, automatically added for keys and SQL expressions.
• skip_wildcard removes a field from default wildcard selection, use for expensive SQL expressions or soft hiding.

Notice that for skip_wildcard the field must be selectable (Option<T>). Otherwise Toql must always load a value to satisfy the deserializer.

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
use toql::prelude::{Toql, ToqlApi, query, Cache};
use toql::mock_db::MockDb;

#[derive(Toql)]
#[toql(table="User")]
struct UserRef {
    
    #[toql(key, column="id")]
    id: u32,

    full_name: String,

    #[toql(skip_wildcard)]
    middle_name: Option<String>,

    #[toql(skip)]
    value: Option<String>,

}
    let cache = Cache::default();
    let mut toql = MockDb::from(&cache);

    let q = query!(UserRef, "*"); 
    let mut users = toql.load_many(&q).await.unwrap(); 
    assert_eq!(toql.take_unsafe_sql(), "SELECT user.id, user.full_name FROM User user");

#  }

A Toql query can select individual fields from a struct. However fields must be Option for this, otherwise they will always be selected in the SQL statement, regardless of the query.

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
use toql::prelude::{Toql, ToqlApi, query, Cache};
use toql::mock_db::MockDb;
  #[derive(Toql)]
    struct User {

        #[toql(key)]
        id: u32,			// Always selected in SQL (keys must not be optional)

        age: u8,			// Always selected in SQL

        firstname: Option<String>,	// Selectable field of non nullable column
        middlename: Option<Option<String>>,// Selectable field of nullable column

        #[toql(preselect)]	
        lastname: Option<String>	// Always selected in SQL, nullable column
  }
    let cache = Cache::default();
    let mut toql = MockDb::from(&cache);
  
    // Load preselected fields
    let q = query!(User, "id"); 
    let mut _users = toql.load_many(&q).await.unwrap(); 
    assert_eq!(toql.take_unsafe_sql(), 
            "SELECT user.id, user.age, user.lastname FROM User user");

#  }

You noticed it: Nullable columns that should always be selected must be annotated with preselect.

Preselected fields on joined structs are selected, if

• A join itself is preselected
• or at least one field on that join is selected

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
use toql::prelude::{Toql, ToqlApi, query, Cache};
use toql::mock_db::MockDb;
  #[derive(Toql)]
    struct User {

        #[toql(key)]
        id: u32,

        #[toql(join())]
        native_language: Language,	// Preselected inner join

        #[toql(join())]
        foreign_language: Option<Option<Language>>,
    }

    #[derive(Toql)]
    struct Language {
            #[toql(key)]
            id: u32,

            code: Option<String>
    }
    let cache = Cache::default();
    let mut toql = MockDb::from(&cache);
  
    // Load preselected fields
    let q = query!(User, "id"); 
    let mut _users = toql.load_many(&q).await.unwrap(); 
    assert_eq!(toql.take_unsafe_sql(), 
            "SELECT user.id, user_nativeLanguage.id \
            FROM User user \
            JOIN (Language user_nativeLanguage) \
            ON (user.native_language_id = user_nativeLanguage.id)");

#  }

Above id in User is always selected, because it's not Option. As native_language is a preselected (inner) join, its id will also always be selected. But on the contrary foreign_language is a selectable (left) join. id will only be selected if the query requests any other field from that join. For example with foreignLanguage_code.

One more thing: If a field on a related struct is selected, all preselected fields from the path line will be selected too.

Lets assume we have a user that has an address, which contains country information.

The query

address_country_code

would therefore

• select code from the table Country
• select all preseleted fields from table Country
• select all preseleted fields from table Address
• select all preseleted fields from table User

Toql is an SQL friendly ORM. Instead of mapping a struct field to a column you can also map it to a raw SQL expression. There are small syntax enhancements to work with aliases and auxiliary parameters.

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
    use toql::prelude::{Toql, ToqlApi, query, Cache};
    use toql::mock_db::MockDb;

    #[derive(Toql)]
    struct User {
        #[toql(key)]
        id: u64,

        #[toql(sql="(SELECT COUNT(*) FROM Books b WHERE b.author_id = ..id)")]
        number_of_book:u64
    }

    let cache = Cache::default();
    let mut toql = MockDb::from(&cache);

    let q = query!(User, "id"); 
    let mut _users = toql.load_many(&q).await.unwrap(); 
    assert_eq!(toql.take_unsafe_sql(), 
            "SELECT user.id, (SELECT COUNT(*) FROM Books b WHERE b.author_id = user.id) \
            FROM User user");
#   }

Notice the .. ! This special alias will be replaced with the alias created for User.

To use aux params in a SQL query use the <param_name> syntax.

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
    use toql::prelude::{Toql, ToqlApi, query, Cache, ContextBuilder};
    use toql::mock_db::MockDb;
    use std::collections::HashMap;

    #[derive(Toql)]
    struct User {
        #[toql(key)]
        id: u64,

        #[toql(sql="(SELECT <page_limit>)")]
        page_limit:u64,

        #[toql(sql="(SELECT COUNT(*) FROM Films f WHERE f.age >= <age>)")]
        age_rated_films:u64
    }
 
    let mut p = HashMap::new();
    p.insert("page_limit".into(), 200.into());
    p.insert("age".into(), 57.into());

    let context = ContextBuilder::new().with_aux_params(p).build();
    let cache = Cache::new();
    let mut toql = MockDb::with_context(&cache, context);

    let q = query!(User, "id"); 
    let mut _users = toql.load_many(&q).await.unwrap(); 
    assert_eq!(toql.take_unsafe_sql(), 
            "SELECT user.id, (SELECT 200), (SELECT COUNT(*) FROM Films f WHERE f.age >= 57) FROM User user");
#   }

In the example page_limit might come from a server configuration. It would typically be put in the context and can be used in SQL expressions.

The aux param age might be taken from the authorisation token and put as an aux param into the context or query. Here it restricts the number of films.

There are other places you can use raw SQL:

• Predicates
• Custom Merge

It's possible to write an own field handler. Do it, because

• You want to build an SQL expression with a function.
• You want to support a database function through FN
• You want to abuild a filter condition with a function

Let's support a length function LLE , so that we can filter on maximum word length

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
   use toql::prelude::{ToqlApi, DefaultFieldHandler, FieldHandler, 
               SqlExpr, SqlBuilderError, FieldFilter, Cache, Toql,
               ParameterMap, sql_expr, query};
   use toql::mock_db::MockDb;

   struct LengthFieldHandler{
       // The default field handler gives us default filters 
       // such as `EQ`, `NE`, ...
       default_handler: DefaultFieldHandler, 
   };

   impl FieldHandler for LengthFieldHandler
   {
       fn build_filter(
           &self,
           select: SqlExpr,            // Column or SQL expression
           filter: &FieldFilter,       // The filter called with this field
           aux_params: &ParameterMap,  // All aux params available
       ) -> Result<Option<SqlExpr>, SqlBuilderError> {
           match filter {
               // Support our custom LLE filter that maps 
               // to the MySQL LENGTH function
               FieldFilter::Fn(name, args) => match name.as_str() {
                   "LLE" => {
                       if args.len() != 1 {
                           return Err(SqlBuilderError::FilterInvalid( 
                               "filter `FN LLE` expects exactly 1 argument".to_string()));
                       }
                       Ok(Some(sql_expr!("LENGTH ({}) <= ?", select, &args[0])))
                   }
                   name @ _ => Err(SqlBuilderError::FilterInvalid(name.to_string())),
               },
               _ => self.default_handler.build_filter(select, filter, aux_params),
           }
       }

   }

   // Getter method for mapper
   pub fn length_field_handler() -> impl FieldHandler {
       LengthFieldHandler{
           default_handler: DefaultFieldHandler::new(), 
       }
   }

   #[derive(Toql)]
   struct User {
       #[toql(key)]
       id: u64,

       #[toql(handler="length_field_handler")]
       name: Option<String>,
   }

   let cache = Cache::new();
   let mut toql = MockDb::from(&cache);

   let q = query!(User, "name FN LLE 5"); 
   let mut _users = toql.load_many(&q).await.unwrap(); 
   assert_eq!(toql.take_unsafe_sql(), 
           "SELECT user.id, user.name \
           FROM User user \
           WHERE LENGTH (user.name) <= 5");
#   }

For a bigger example, check out our permission handler.

If you want to use the same field handler in different places it mightly come handy to give the field handler some local context.

This can be achieved with local aux_params:

    #[toql(sql="", 
        field_handler="smart_name_handler", 
        aux_param(name="strategy", value="quick"))]
    smart_name: String

The aux param strategy is only available in the smart_name_handler. Only strings values are supported.

A struct can refer to another struct. This is done with a SQL join.

Joins are added to the SQL statement when

• they are requested in the query, like so: phone1_id.
• they are preselected.

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;

#[derive(Toql)]
struct Phone {
    #[toql(key)]
    country: u8,
    #[toql(key)]
    number: u64,
}

#[derive(Toql)]
struct User {

    #[toql(key)]
     id: u32,	

     name: Option<String>,

     #[toql(join)]  
     phone1 : Phone, 		// Always selected inner join

     #[toql(join)]  
     phone2 : Option<Phone>, // Selectable inner join

     #[toql(join)]  
     phone3 : Option<Option<Phone>>, // Selectable left join

     #[toql(join, preselect)]  
     phone4 : Option<Phone>, // Always selected left join
}
#}

Notice how Option makes the difference between an inner join and a left join.

By default foreign keys are calulated by the primary columns of the join and the field name of the join. For the above it would be phone1_id, phone2_id, phone3_id and phone4_id.

If your naming scheme differs from that default behaviour, use the columns attribute:

# #![allow(unused_variables)]
#fn main() {
#   use toql::prelude::Toql;
#   #[derive(Toql)]
#   struct Phone {
#   	#[toql(key)]
#   	country: u8,
#   	#[toql(key)]
#   	number: u64,
#   }
#   #[derive(Toql)]
#   struct User {
#   
#   	#[toql(key)]
#   	 id: u32,	
#   
    #[toql(join(columns(self="mobile1_id", other="id")))]  
    phone1 : Phone 
#   }
#}

For a composite key use columns multiple times.

It's possible to restrict the join with a ON SQL predicate.

Here an example of a translated country name.

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
    use toql::prelude::{Toql, ToqlApi,Cache, query, ContextBuilder};
    use toql::mock_db::MockDb;
    use std::collections::HashMap;

    #[derive(Toql)]
    struct User {
        #[toql(key)]
        id: u32,	

        #[toql(join)]
        country: Option<Country>
    }
 
    #[derive(Toql)]
    struct Country {

        #[toql(key)]
        id: u32,	

        #[toql(join(
                columns(self = "id", other = "id"), 
                on_sql = "...language_id=<interface_language_id>"))]
        pub translation: Option<CountryTranslation>
    }

    #[derive(Toql)]
    pub struct CountryTranslation {

        #[toql(key)]
        pub id: String,
        
        pub title: String,
    }

  
    let mut p = HashMap::new();
    p.insert("interface_language_id".into(), "en".into());
    
    let context = ContextBuilder::new().with_aux_params(p).build();
    let cache = Cache::new();
    let mut toql = MockDb::with_context(&cache, context);

    let q = query!(User, "country_translation_title");

    let mut _users = toql.load_many(&q).await.unwrap(); 
    assert_eq!(toql.take_unsafe_sql(), 
            "SELECT user.id, user_country.id, user_country_translation.id, user_country_translation.title \
                FROM User user \
                JOIN (Country user_country \
                    JOIN (CountryTranslation user_country_translation) \
                    ON (user_country.id = user_country_translation.id \
                        AND user_country_translation.language_id='en')) \
                ON (user.country_id = user_country.id)"); 
#   }

You can use any raw SQL in the ON predicate. Did you spot the ... alias? This will resolve to the alias of the joined struct (CountryTranslation).

Apart from ON predicates the ... alias can also be used in custom merge predicates.

It is also possible to use the regular .. alias to refer to the joining struct (Country), but we don't need it here.

You can use auxiliary parameters (here <interface_language_id>) in ON expression. Aux params usually come from a context, query.

However for ON there is a third source : Aux params may also come from query predicates.

This allows some nifty joining, see here:

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
    use toql::prelude::{Toql, ToqlApi,Cache, query, ContextBuilder};
    use toql::mock_db::MockDb;


    #[derive(Toql)]
    #[toql( predicate(	
                name = "lang", 
                sql = "EXISTS(SELECT 1 FROM Country c \
                    JOIN Language l ON (c.id = l.id)) WHERE l.id = ?)", 
                on_aux_param(name="language_id", index = 0)
            ))]
    struct Country {

        #[toql(key)]
        id: u32,	

        #[toql(join(on_sql = "...code = <language_id>"))]
        pub language: Option<Language>
    }

    #[derive(Toql)]
    pub struct Language {

        #[toql(key)]
        pub code: String,
        
        pub title: String,
    }
    let cache = Cache::new();
    let mut toql = MockDb::from(&cache);

    let q = query!(Country, "@lang 'fr'");

    let mut _users = toql.load_many(&q).await.unwrap(); 
    assert_eq!(toql.take_unsafe_sql(), 
            "SELECT country.id FROM \
                Country country \
                WHERE EXISTS(\
                    SELECT 1 FROM Country c \
                    JOIN Language l ON (c.id = l.id)) WHERE l.id = 'fr')"); 
#   }

Above we add a predicate that allows to filter all countries by a language. There can be multiple countries that speak the same language.

The predicate takes the one argument (?) and adds it to the aux_params for custom joins (on_param).

When the predicate is used in a Toql query, lets say *, @lang 'fr' the SQL will return countries that speak french. In addition it will add fr to the aux_params when doing the custom join.

So each country will contain the language field with information about french.

It's somehow hacky, but it works and is useful in 1-n situations when you want 1-1 .

Toql can insert joins with renamed columns and no custom ON expression, because key propagation is done internally through common column names. Joins with custom ON expressions can't be inserted or updated, they are read only.

Joining directly another struct is not ergonomic when you want to update the struct. Thats why the Join enum exists. It can either take a struct value or just its key.

Consider this

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;
#   #[derive(Toql)]
#   struct Phone {
#   	#[toql(key)]
#   	country: u8,
#   	#[toql(key)]
#   	number: u64,
#   }

#[derive(Toql)]
struct User {

    #[toql(key)]
     id: u32,	

    #[toql(join)]
     phone: Phone
}
#}

Here when we want to set a new Phone for the user, we need to provide a full Phone struct even tough we only want to set a new value for the foreign key phone_id in User. This feels clumsy and toql::prelude::Join comes to our rescue:

#   #[tokio::main(flavor="current_thread")]
async fn main() -> Result<(), toql::prelude::ToqlError> {
use toql::prelude::{Toql, Join, ToqlApi,Cache, query, 
                    fields, rval,join, rval_join};
use toql::mock_db::MockDb;
use toql::row;

#[derive(Toql)]
struct Phone {

    #[toql(key)]
    number: String,

    prepaid: Option<bool>,
}

#[derive(Toql)]
struct User {

    #[toql(key)]
     id: u32,	

    #[toql(join)]
     phone: Option<Join<Phone>>,

}

let cache = Cache::new();
let mut toql = MockDb::from(&cache);
let select = "SELECT user.id, user_phone.number, user_phone.prepaid \
                FROM User user \
                JOIN (Phone user_phone) \
                ON (user.phone_number = user_phone.number) \
                WHERE user.id = 1";

toql.mock_rows(select, vec![row!(1u64, "1 123 45 67", true)]);

let q = query!(User, "phone_prepaid, id eq ?", 1);

let mut user = toql.load_one(&q).await.unwrap(); 
assert_eq!(toql.take_unsafe_sql(), select);

// 4 ways to get the phone 
// Note that the macros `rval!`, `join!`, `rval_join!` 
// raise an error and return early, 
// if they can't get a value

// 1. `rval_join!` macro -> recommended
let phone1 = rval_join!(user.phone);

// 2. `join!` macro -> just for educational purpose
let phone2 = join!(rval!(user.phone));

// 3. `Join::entity()` method + `rval!` macro -> educational
let temp3 = &user.phone;
let temp31 = temp3.as_ref().unwrap().entity();
let phone3 = rval!(temp31);

// 4. No macros 
let temp4 = &user.phone;
let phone4 = temp4.as_ref().unwrap().entity().unwrap(); 

assert_eq!(phone1.number, "1 123 45 67");
assert_eq!(phone2.number, "1 123 45 67");
assert_eq!(phone3.number, "1 123 45 67");
assert_eq!(phone4.number, "1 123 45 67");

// Update phone number (foreign key only)
user.phone = Some(Join::with_key(PhoneKey::from("0300 123 45 67".to_string())));

toql.update_one(&mut user, fields!(User, "phone, phone_prepaid")).await.unwrap();
assert_eq!(toql.take_unsafe_sql(), 
    "UPDATE User SET phone_number = '0300 123 45 67' WHERE id = 1"); 

// Update phone (foreign key + entity)
let phone = Phone{number: "0300 123 45 67".to_string(), prepaid: Some(true)};
user.phone= Some(Join::with_entity(phone));

toql.update_one(&mut user, fields!(User, "phone, phone_prepaid")).await.unwrap();
assert_eq!(toql.take_unsafe_sqls(), [
    "UPDATE Phone SET prepaid = TRUE WHERE number = '0300 123 45 67'", 
    "UPDATE User SET phone_number = '0300 123 45 67' WHERE id = 1"]);

Ok(())
}

This has the following advantages:

• Loads as normal, Join will always hold a full value.
• Updating the phone_id column in User requires only a PhoneKey. This key can be always be taken out from a Join.
• Web clients can send keys or full entities. Join will deserialize into whatever is possible.

If you watch the generated SQL joins, you will notice that JOIN statements look slightly more complicated from Toql than you may expect.

This is because Toql builds correctly nested JOIN statements that reflect the dependencies among the joined structs. Any SQL builder that simply concatenates inner joins and left joins may accidentally turn left joins into inner joins. This database behaviour is not well known and usually surprises users - Toql avoids this.

When doing a join, it's possible do write a custom handler. The handler can only build a custom ON predicate.

Why would you do it? I don't know :)

In all cases I can think of you get your ON predicate cheaper with on_sql.

However it's still possible:

# #![allow(unused_variables)]

#fn main() {
use toql::prelude::{JoinHandler, SqlExpr, SqlArg, ParameterMap, SqlBuilderError};

pub(crate) struct MyJoinHandler;

impl JoinHandler for MyJoinHandler {
    fn build_on_predicate(&self, on_predicate: SqlExpr, aux_params: &ParameterMap,)
     ->Result<SqlExpr, SqlBuilderError> {
        // Modify on_predicate based on aux_params
        Ok(on_predicate)
    }
}

// Getter function
pub fn my_join_handler() -> impl JoinHandler {
    MyJoinHandler {}
}
#}

Now map the getter function with

#[toql(join(), handler="my_join_handler")]
address: Address

And any join on Address will now call the join handler.

A database table may split into several tables sharing the same primary key. This is done because

• the original table got too many columns
• A group of columns in the table is optional
• You want to control access with Toql roles.

Partial tables are supported with joins.

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;

#[derive(Toql)]
#[toql(auto_key)]
struct Question {
    #[toql(key)]
    id: u64,

    text: String,

    #[toql(join(columns(self="id", other="question_id"), partial_table))]
    details: Option<QuestionDetails>
}

#[derive(Toql)]
struct QuestionDetails {
    #[toql(key)]
    question_id: u64,
    
    font: String
}
#}

In the example above Question and QuestionDetails share the same values for primary keys.This is what patial_table says. So for a question with id = 42 there is a question_details with question_id = 42.

Inserts will always insert all partial tables too, whenever a path list asks to insert the base table (Question).

Also it will avoid to insert a non existing foreign key: If QuestionDetails was regular join (without partial_table) insert would try to set a (non existing) column details_id with the value of the primary key of Questiondetails. This would be correct for regular joins, but fails on partial tables.

Updates have the same behaviour when inserting new merges and for loading partial_table has no effect.

A struct can contain a Vec of other structs. Because this can't be loaded directly in SQL, Toql will execute multiple queries and merge the results.

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;

#[derive(Toql)]
#[toql(auto_key)]
struct User {

    #[toql(key)]
     id: u32,

     name: Option<String>,

     #[toql(merge())]  
     mobile_phones : Vec<Phone>
}

#[derive(Toql)]
struct Phone {

    #[toql(key)]
    number: u64,

    prepaid : Option<bool>
}
#}

Selecting all fields from above with *, mobilePhones_* will run 2 SELECT statements and merge the resulting Vec<Phone> into Vec<User> by the common value of User.id and Phone.user_id.

By default the merge column names follow the pattern above. However it's possible to explicitly specify the column names:

#[toql(merge(columns(self="id", other="user_id")))]  
phones : Vec<Phone>

Often in a 1-n-1 situation the association table (n) does not contain any other columns apart from the composite key. In those situations it's often desirable to skip it.

Let's go with an example:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;

#[derive(Toql)]
#[toql(auto_key)]
struct User {

    #[toql(key)]
    id: u32,

    name: Option<String>,

    #[toql(merge)] // Default merging on User.id = UserCountry.user_id
    countries1 : Vec<UserCountry>,

     #[toql(merge(
        join_sql = "JOIN UserCountry uc ON (...id = uc.country_id)",
        columns(self = "id", other = "uc.user_id")
    ))]  
     countries2 : Vec<Country>
}

#[derive(Toql)]
struct UserCountry {

    #[toql(key)]
     user_id: u32,

    #[toql(key, join)] // Default joining on UserCountry.country_id = Country.id
     country: Country
     
}
#[derive(Toql)]
struct Country {

    #[toql(key)]
     id: String,

     name: Option<String>
}
#}

Wow, a lot going on here:

• countries1 merges on default column names (User.id = UserCountry.user_id). Here the Vec contains UserCountry, which does not contain any interesting data and is unconvenient when accessing Country.

• countries2 skips the association table with a custom SQL join. Let's look at join_sql first: The special other alias ... refers - as always- to the merged struct (Country here), so Country will be joined with UserCountry on Country.id = uc.country_id. After the select Toql merges the countries into the users on common column values of User.id and uc.user_id column value. Because the later column is already aliased with uc no alias will be added.

• In UserCountry, notice the nice example of a composite key made up with a join :)

In the example above Country knows nothing about the User, so we must merge with join_sql.

However sometimes the merged struct does have a suitable foreign key and we can apply a different pattern:

In the example below we don't have a classic association table. Still we merge normally on User.id = Todo.user_id, but Todo.user_id is not part of a composite key, as it would be in a asscociation table. Instead it is just a normal foreign key.

This is not a problem when loading the merge. But when doing inserts, Toql wishes to update Todo.user_id to ensure the foreign key contains the right value. If Todo.user_id was part of the primary key this would work out of the box. But since it's not, we have to mark it with #[foreign_key]. This tells to consider this column too when setting keys.

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;

#[derive(Toql)]
#[toql(auto_key)]
struct User {
    #[toql(key)]
    id: u64,

    #[toql(merge)]
    todos: Vec<Todo>
}


#[derive(Toql)]
#[toql(auto_key)]
struct Todo {
    #[toql(key)]
    id: u64,

    #[toql(foreign_key)]
    user_id: u64,
    
    what: String
}
#}

Toql requires you to add the attribute key to the field that correspond to the primary key in your database.

For composite keys mark multiple fields with the key attribute.

For internal reasons keys must always be

• the first fields in a struct
• and must not be optional.

This means that keys are preselected and therefore always loaded when a Toql query selects fields from the struct.

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;

#[derive(Toql)]
struct User {
  #[toql(key)]
    id: u64,
    name: Option<String>
}
#}

If your primary key is generated in your database you can tell this with [#toql(auto_key)].

Inserts will then ignore the primary key in the struct and instead load the newly generated key from the database. The new key is then stored in the struct field.

Notice this can't be used with joined keys.

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
use toql::prelude::{Toql, ToqlApi,Cache, query, ContextBuilder, paths};
use toql::mock_db::MockDb;

#[derive(Toql)]
#[toql(auto_key)]
struct User {
  #[toql(key)]
  id: u64,
  name: Option<String>
}

let cache = Cache::new();
let mut toql = MockDb::from(&cache);
let mut user = User {id:0, name: Some("Mary".to_string())};

toql.insert_one(&mut user, paths!(top)).await.unwrap(); 

assert_eq!(toql.take_unsafe_sql(), "INSERT INTO User (name) VALUES ('Mary')");
assert_eq!(user.id, 100);
#   }

Inner joins can also have the key attribute. This is useful for association tables.

For a join used as a key the SQL builder takes the primary key of the joined struct to guess the foreign key columns.

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;

#[derive(Toql)]
struct Language {

  #[toql(key)] 
  code: String,

  name: String
}

#[derive(Toql)]
struct UserLanguage {

  #[toql(key)] 
  user_id: u64,

  #[toql(key, join)]  
  language: Language 
}
#}

In the example above Toql assumes that the database table UserLanguage has a composite key made up of the two columns user_id and language_code. You can change this assumption, see here.

The Toql derive creates for every struct a corresponding key struct. The key struct contains only the fields marked as key form the derived stuct.

Keys are useful to

• delete a value with delete_one.
• build a query.
• update a join.

Keys can be serialized and deserialized with serde, if the serde feature is enabled. This allows web clients to send either a full entity or just the key of it, if they want to update some foreign key.

#   #[tokio::main(flavor="current_thread")]
#   async fn main() {
#   use toql::prelude::{ToqlApi,Cache, query, ContextBuilder};
#   use toql::mock_db::MockDb;
  use toql::prelude::Toql;

   #[derive(Toql)]
   struct User {
     #[toql(key)]
        id: u64,
        name: Option<String>
   }

#   let cache = Cache::new();
#   let mut toql = MockDb::from(&cache);

let key = UserKey::from(10);
toql.delete_one(key).await.unwrap();
# }

Only columns and inner joins can be used as keys. Merged fields (Vec<T>) and fields that map to an SQL expression (#[toql(sql="..")) cannot be used as keys.

When you insert a struct, all fields, including foreign keys of joins, will be inserted.

Check the API for details.

For selectable fields in a struct that are None the default value will be inserted. If you have not defined a default value in your database you must ensure that the field in the struct can't be None. This can be done through prior validation.

The insert behaviour depends on the type and mapping of a field:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;
#   #[derive(Toql)]
#   struct Phone {
#   
#   	#[toql(key)]
#   	number: String,
#   	prepaid : Option<bool>
#   }
#   
#   #[derive(Toql)]
#   struct Address {
#   	#[toql(key)]
#   	id: u64,
#   	street: Option<String>
#   }
  
#[derive(Toql)]
struct User {
    #[toql(key)]
    id: u64,                    
    
    username: String,		// Value
    realname: Option<String>,	// Default or value
    address: Option<Option<String>>,// Nullable column: Default, value or NULL

    #[toql(preselect)]
    info: Option<String>, 	// Nullable column: Value or NULL

    #[toql(join)]
    address1: Option<Address>, 	// Selectable inner Join: Foreign key is inserted or default

    #[toql(join)]
    address2: Option<Option<Address>>,// Selectable left join: Default, value or NULL

    #[toql(join)]
    address3: Address, 		// Inner Join: Foreign key or default

    #[toql(join, preselect)]
    address4: Option<Address>,	// Selectable inner join: Foreign key or default

    #[toql(merge)]
    phones1: Vec<Phone>,		// No change on table 'User'

    #[toql(merge)]
    phones2: Option<Vec<Phone>> // No change on table 'User'
}
#}

When the path list requires to insert a dependency too, left joins and optional merges will only be inserted, if they contains a value.

The update functions from the API will update a field,

• if the field name is in the field list
• and a selectable field in the struct contains a value.

If we want to update all fields of the struct below with a field list of *, the behaviour would be

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;

#   #[derive(Toql)]
#   struct Phone {
#   
#   	#[toql(key)]
#   	number: String,
#   	prepaid : Option<bool>
#   }
#
#   #[derive(Toql)]
#   struct Address {
#   	#[toql(key)]
#   	id: u64,
#   	street: Option<String>
#   }

#[derive(Toql)]
struct User {
    #[toql(key)]
    id: u64,			// Keys are never updated	

    username: String,		// Update
    realname: Option<String>, 	// Updated , if Some
    address: Option<Option<String>>, // Update NULL or String, if Some

    #[toql(preselect)]
    info: Option<String>, 	//Update NULL or String

    #[toql(join)]
    address1: Option<Address>, // Update foreign_key, if Some 

    #[toql(join)]
    address2: Option<Option<Address>>,//  Update foreign_key or NULL, if Some 

    #[toql(join)]
    address3: Address, 		// Update foreign_key

    #[toql(join, preselect)]
    address4: Option<Address>,	// Update foreign_key or NULL

    #[toql(merge)]
    phones1: Vec<Phone>,	// No effect for *

    #[toql(merge)]
    phones2: Option<Vec<Phone>> // No effect for *, 
}
#}

Notice that foreign keys of joins are included (User.address1_id, User.address2_id, ..) with the * in the field list. However merges must be explicitly mentioned.

To update all fields from User and to resize the Vec of phones1 (insert new phones + delete old phones ) the field list would be *, phones1.

Selections are a list of fields and can be defined on a struct. A Toql query can then select the selection instead of all the individual fields.

For more information about selections, see also the chapter in the query language.

The builtin $mut selection selects all fields that are mutable. This can be used for debugging purposes. The fields of this selection cannot be user defined.

The $all selects all fields from a struct. This includes fields that are marked with skip_wildcard. Joins and merges are not selected. The fields of this selection cannot be user defined.

The standart selection $std behaves like any other user defined selection. However Toql supports this standart selection with the special short alias $ in Toql queries.

If the derive doesn't map the $std selection a query asking for it will fail. This is the same behaviour as for any other user defined selection.

To build count queries Toql considers filters on fields that are part of the $cnt selection.

Predicates that should also be considered when building a count selection must be marked with the count_filter flag. See the next chapter on predicates.

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;
 
#[derive(Toql)]
#[toql(selection(name="std", fields="*, address_street"))]
#[toql(selection(name="tiny", fields="id, name"))]
struct User {

 #[toql(key)]
 id: u64,

 name: Option<String>,

 #[toql(join)]
 address: Option<Address>
}
 
#[derive(Toql)]
struct Address {

 #[toql(key)]
 id: u64,

 street: Option<String>
}
#}

The selections above can now be used in a query. Instead of asking for name, address_street it is possible to ask for $std or just $.

All normal filtering is based on fields, see here. However sometimes you may have a completely different filter criteria that can't be mapped on fields.

An example is the MySQL full text search. Let's do it:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;
#[derive(Toql)]
#[toql(predicate(name = "search", 
            sql = "MATCH (..firstname, ..lastname) AGAINST (?  IN BOOLEAN MODE)"),
      predicate(name = "street", 
            sql = "EXISTS( SELECT 1 FROM User u \
                JOIN Address a ON (u.address_id = a.id) \
                WHERE a.street = ? AND u.id = ..id)"))]
struct User {

 #[toql(key)]
 id: u64,

 firstname: String,
 lastname: String,
}
#}

With the two predicates above you can seach for users that have a certain name with @search 'peter' and retrieve all users from a certain street with @street 'elmstreet'.

The question marks in the predicate are replaced by the arguments provided. Arguments can also be used to build an ON predicate in a join. See on aux param.

Predicate names are made up with letters, digits and underscores. They must begin with a letter.

While you can use any case in the derive definition, the derive will transform the name into mixedCase to follow the naming scheme of the struct fields. So a predicate name such as My_Address_contains corresponds in the Toql query to @myAddressContains and the builder method wqould be called with .my_address_contains().

Also names must be different than the fields names in a struct. Otherwise a duplicate definition error will occur. This is currently a limitation in the library.

The full predicate syntax is

    predicate(
      name="..",
      sql="..",
      handler="..",
      on_aux_param(name"..", index = ..),
      count_filter

where

• name is the name of the predicate. It can be called in a Toql query with @name ... If a predicate is defined on a joined struct, that predicate can be called with a path @path_name ... See predicates in the query for more details.
• sql is a raw SQL expression. Use ? to insert a predicate param in the SQL, .. for the table alias and <aux_param> for an aux param value.
• handler allows a custom predicate handler (build SQL with a function). Provide a function name without parenthesis that return a struct that implement toql::prelude::PredicateHandler
• on_aux_param sets an aux param to the value of a predicate argument. Index refers to the argument in the SQL expression with 0 being the first ?. This aux param is only available when building ON conditions for joins. and can only be used when the predicate takes exactly one argument. See example.
• count_filter makes Toql to use that a predicate for count queries.

It's also possible to write an own predicate handler. Let's write a handler that concatenates all argument passed to the predicate and puts those arguments into the SQL predicate.

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{Toql,PredicateHandler, SqlExpr, SqlArg,
        ParameterMap, SqlBuilderError, Resolver};
use std::collections::HashMap;

#[derive(Toql)]
#[toql(predicate(name="names", 
                 sql="EXISTS (Select 1 FROM User u JOIN Todo t ON (u.id = t.user_id) \
                AND u.name IN <args>)", handler="my_handler"))]
struct Todo {

    #[toql(key)]
    id: u64,

    what: String,
}




pub(crate) struct MyPredicateHandler;
impl PredicateHandler for MyPredicateHandler {
    fn build_predicate(
        &self,
        predicate: SqlExpr, 		// SQL from predicate
        predicate_args: &[SqlArg],	// Arguments from the query
        aux_params: &ParameterMap,	// Aux params
    ) -> Result<Option<SqlExpr>, SqlBuilderError>  // Return None if no filtering should take place
    {
        if predicate_args.is_empty() {
            return Err(SqlBuilderError::FilterInvalid(
                "at least 1 argument expected".to_string(),
            ));
        }
        let mut args_expr = SqlExpr::new();
        predicate_args.iter().for_each(|a| { 
            args_expr.push_arg(a.to_owned());
            args_expr.push_literal(", ");
        });
        args_expr.pop(); // remove trailing ', '

        let mut replace = HashMap::new();
        replace.insert("args".to_string(), args_expr);
        let predicate = Resolver::replace_aux_params(predicate, &replace); // Replace  aux params with SQL expressions
        
        Ok(Some(predicate))
    }
}

// Getter function
pub fn my_handler() -> impl PredicateHandler {
    MyPredicateHandler {}
}

#}

Use it in a Toql query with @names 'Peter' 'Sandy' 'Bob'

It's possible to restrict access to fields and structs with boolean role expressions.

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;

#[derive(Toql)]
#[toql(roles(insert="poweruser", delete="poweruser"))]
struct Book {

    #[toql(key)]
    id : u64,

    #[toql(roles(load="superuser;poweruser", update="poweruser"))]
    rating: u64
}
#}

The role expressions are similar to the Toql query syntax:

• OR is expressed with ;
• AND is expressed with ,
• NOT is expressed with !
• brackets are allowed

An valid role expression would be (teacher;student), !lazy meaning A teacher OR student AND NOT lazy.

Roles are provided with the context:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::ContextBuilder;
use std::collections::HashSet;

let mut r = HashSet::new();
r.insert("teacher".to_string());

let context = ContextBuilder::new()
        .with_roles(r)
        .build();
#}

See here for how to get a backend.

Notice that roles can restrict access to columns but not to rows. For row access control, check out the chapter in the appendix.

It's possible to restrict loading, filtering and ordering of a struct or individual fields, joins and merges.

Let's assume a struct Book:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{Toql, Join};
#   #[derive(Toql)]
#   #[toql(auto_key)]
#   struct User {
#   	#[toql(key)]
#   	 id: u32,
#   }
#   
#   #[derive(Toql)]
#   struct Edition {
#   	#[toql(key)]
#   	id: u64,
#   }

#[derive(Toql)]
#[toql(roles(load ="book_role"))]
struct Book {
    #[toql(key)]
    id : u64,

    #[toql(roles(load="author_role"))]
    title: Option<String>,

    #[toql(join, roles(load="author_role"))]
    author: Option<Join<User>>,

    #[toql(merge, roles(load="edition_role"))]
    editions: Option<Vec<Edition>>,
}
#}

To load anything from the book, the user requires the book_role. So to load the book's title the user requires the roles book_role and author_role.

Notice that restricting preselected fields is like restricting the entire struct. See here:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;

#[derive(Toql)]
#[toql(roles(load ="book_role"))]
struct Book {
    #[toql(key)]
    id : u64,

    #[toql(roles(load="author_role"))]
    title: String
}
#}

Here Toql needs to load the title field in order to deserialize the struct. Because title is role restricted an error is raised for a missing autor_role.

If all fields from Book are selected with a wildcard * fields that do not match the role restriction are simply skipped. However, if such a field is selected explictly in the query then an error is raised.

To restrict updating a struct or individual fields:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::{Toql, Join};
#   #[derive(Toql)]
#   #[toql(auto_key)]
#   struct User {
#   	#[toql(key)]
#   	 id: u32,
#   }
#   
#   #[derive(Toql)]
#   struct Edition {
#   	#[toql(key)]
#   	id: u64,
#   }

#[derive(Toql)]
#[toql(roles(update="book_role"))]
struct Book {

    #[toql(key)]
    id : u64,

    #[toql(roles(update="author_role"))]
    title: Option<String>,

    #[toql(join, roles(update="author_role"))]
    author: Option<Join<User>>,

    #[toql(merge, roles(update="edition_role"))]
    editions: Option<Vec<Edition>>,
}
#}

To update anything on the book, the user requires the book_role. So to update the book's title the user requires the roles book_role and author_role.

The role restriction on the author join applies only to the foreign key author_id in table Book, not to the joined User. To restrict the joined User put a role restriction #[toql(roles(update="user_role")) on top of the struct User

Update restrictions on partials joins are not allowed, since partial joins have no foreign key: Partial joins share the same primary key.

Likewise the role restriction on the editions merge applies only to resizing the Vec not to the fields on the edition table. See the chapter on updating for details.

Fields that have an invalid role expression are skipped for the field list *. However if the field name is explicitly mentioned in the field list, then an error occurs. This behaviour is similar to loading.

To restrict insertion or deltetion of a struct, attribute the struct like so:

use toql::prelude::{Toql, Join};

#[derive(Toql)] {
#[toql(roles(insert="book_role", delete="book_role"))
struct Book {

    #[toql(key)]
    id : u64

    title Option<String>>,

    #[toql(join)]
    author: Option<Join<User>>,

    #[toql(merge)]
    editions: Option<Vec<Edition>>,
}

The derive provides struct level attributes and field level attributes. Here a list of all available attributes:

The Toql query language is a normal string that list all query fields, that should be retrieved from a database.

Besides selection, query fields can also be filtered and ordered.

They are separated either by comma or semicolon. If a filter is applied a comma will join the filters with AND, a semicolon with OR.

id, +name, age gt 18 is translated into SELECT t1.id, t1.name, t1.age FROM ... t1 WHERE t1.age > 18 ORDER BY t1.name ASC

id, .age eq 12; .age eq 15is translated intoSELECT t1.id From ... t1 WHERE t1.age = 12 OR t1.age = 15`

Fields are selected if they are mentioned in the query.

• Names without underscore represent typically columns or SQL expressions from the table the query is run against. id, name, fullName, emailAddress

• Fields with underscores are called fields with a path. They are mapped to a joined or a merged dependency. For a join relationship, the join will be added to the SQL statement if the field is selected. For a merge relationship a second SQL query is run and the results are merged. Such a query might look like this book_id, book_title, book_createdBy_id, sellers_city

id, book_id

is translated into (assume a struct User that joins to a struct Book)

SELECT a.id, b.id FROM User a JOIN Book b ON (a.book_id = b.id)

There are two wildcards to select multiple fields. They can neither be filtered nor ordered.

• * selects all fields from the top level.

• path_* selects all fields from path.

Fields can be excluded from the wildcard by setting them to skip_wildcard.

So a query *, book_* would select all fields from user and book.

Fields can require roles to be loaded. An error is raised, if a query selects a field by name that it's not allowed to. However if the query selects with a wildcard, the disallowed field will just be ignored.

Fields can be ordered in ascending + or descending - way.

+id, -title

is translated into

--snip-- ORDER BY id ASC, title DESC

Use numbers to express ordering priority.

• Lower numbers have higher priority.
• If two fields have the same number the first field in the query has more importance.

-2id, -1title, -2age

is translated into

--snip-- ORDER BY title DESC, id DESC, age DESC

Fields can be filtered by adding a filter to the field name.

• Filters are case insensitiv.
• Arguments are separated by whitespace.
• Strings and enum arguments are enclosed with single quotes.
• Boolean arguments are expressed with numbers 0 and 1.

To use a field only for filtering, but not for selection, hide it with a dot: .age gt 50, .book_reviewed eq 1

id, book_published eq 1, .age gt 50

is typically translated into (using canonical aliases)

SELECT user.id, user_book.id, user_book.published FROM User user JOIN Book user_book ON (user.book_id = user_book.id) WHERE user_book.published = 1 AND user.age > 50

Custom functions are applied through the FN filter. They must be handled by a Field Handler. See API for details.

A field can be filtered multiple times by adding multiple the filter expressions in the query.

To build complex filter expressions join filters by comma to express logical AND or semicolon for logical OR. Keep in mind that logical AND has higher precendence than logical OR.

Use parens if required:

age eq 12, animal eq 'chicken'; animal eq 'cow

is the same as

(age eq 12, animal eq 'chicken'); animal eq 'cow

but different than

age eq 12, (animal eq 'chicken'; animal eq 'cow)

Use the dot notation if you only want to filter a field without selecting it:

age eq 12, .animal eq 'chicken'; .animal eq 'cow'

Toql onyl knows integers, floats and strings. Use the following table to express more types:

A typical query selects a lot of fields. Instead of writing out a long list of fields, predefined field lists can be mapped.

The list can then be selected with a $ followed by the selection name.

$mySelection, $otherSelection

There is a set of predefined selections:

Selections on a dependency can be used with a path.

To load the standart selection from a dependency address use

$address_std

Selection names with 3 or less characters are reserved for internal purposes. User defined selection names must contain at least 4 characters.

Normal query filters are applied to fields. Predicates overcome this limitation and can filter on any raw SQL predicate.

The behaviour of predicates must be mapped, then they can be called with a @, the predicate name and zero or more arguments.

@search 'peter', @updated, @tags 'island' 'fun'

Predicates can refer to a dependency, using a path.

To search a dependency address use

@address_search 'peter'

The appendix is a wild collection of issues around Toql in no particular order.

To map enums between a column and a struct field

• some boilerplate code for Toql is required.
• the enum must implement the ToString and FromStr traits.

For the first there exists the ToqlEnum derive and for the later several crates exit. Here an example with strum:

With this in Cargo.toml

[dependencies]
strum = "0.22"
strum_macros = "0.22"

you can attribute your enums:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::ToqlEnum;
use strum_macros::{Display, EnumString};

#[derive(PartialEq, EnumString, Display, ToqlEnum)]
enum Mood {
   Happy,
   Sad
} 
#}

Now Mood can be used:

# #![allow(unused_variables)]
#fn main() {
use toql::prelude::Toql;
#   use toql::prelude::{ToqlEnum};
#   use strum_macros::{Display, EnumString};
#   #[derive(PartialEq, EnumString, Display, ToqlEnum)]
#    enum Mood {
#       Happy,
#       Sad
#   } 

#[derive (Toql)]
struct User {
    #[toql(key)]
    id : u64,
    name: Option<String>,
    mood: Option<Mood>
}
#}

Toql comes with role based access. Roles can only restrict access to columns but not to rows. For a full security model you also need restricted access to rows.

Row access control should always be done in databases.

Reality is however that many databases (MySQL) provide little support for that.

So if you really need to do it in Toql, here is a way to go:

Let's assume a table Todo:

and a Permission table:

To ensure that a user with id 5 can only see his own rows 1 + 2 set up a permission field and build a custom field handler. Like so

# #![allow(unused_variables)]

#fn main() {
use toql::prelude::{Toql, FieldHandler, DefaultFieldHandler, 
    SqlExpr, ParameterMap, SqlBuilderError, SqlArg, sql_expr, FieldFilter};

#[derive(Toql)]
#[toql(auto_key)]
struct Todo {
    #[toql(key)]
    id: u64,

    what: String, 

    #[toql(sql="", handler="permission_handler",  
            aux_param(name = "entity", value = "TODO"))]
    permission: String
}

// Here comes our permission field handler
// We also want a custom filter function SC 
// so that we can filter for a specific permission.
//
// This allows the following toql queries
// Todos with any permissions -> `*, permission ne ''` 
// Todos with UPDATE permission -> `*, permission fn sc 'UPDATE'` 
struct PermissionFieldHandler{
     default_handler: DefaultFieldHandler, // The default field handler gives us default filters, such as `ne`
};
    
impl FieldHandler for PermissionFieldHandler
{
    fn build_select(
        &self,
        sql: SqlExpr,
        aux_params: &ParameterMap,
    ) -> Result<Option<SqlExpr>, SqlBuilderError> {
        
        // Get user_id from aux params (typically from web auth token)
        let user_id = aux_params.get("user_id").unwrap_or(&SqlArg::Null);

        // Get entity from aux params (locally provided with permission handler)
        let entity = aux_params.get("entity").unwrap_or(&SqlArg::Null).to_string();

        // Build subselect
        // Notice our special .. alias, it will be resolved later by the query builder
        // Build a string list with all permissions that we have as owners
        let sql = sql_expr!("(SELECT GROUP_CONCAT( p.action) FROM Permission p \
                WHERE p.entity = ? AND \
                (p.owner_only = false OR ..owner_id = ?))", entity, user_id);
        Ok(Some(sql))
    }
     fn build_filter(
        &self,
        select: SqlExpr,
        filter: &FieldFilter,
        aux_params: &ParameterMap,
    ) -> Result<Option<SqlExpr>, SqlBuilderError> {
        match filter {
            // Support our custom SC filter that maps to the MySQL FIND_IN_FIELD function
            FieldFilter::Fn(name, args) => match name.as_str() {
                "SC" => {
                    filter_sc(name, select, args)
                },
                name @ _ => Err(SqlBuilderError::FilterInvalid(name.to_string())),
            },
            _ => self.default_handler.build_filter(select, filter, aux_params),
        }
    }
}

pub fn filter_sc(
    name: &str,
    select: SqlExpr,
    args: &[SqlArg]
) -> Result<Option<SqlExpr>, SqlBuilderError> {
    if args.len() != 1 {
        return Err(SqlBuilderError::FilterInvalid(
            format!("filter `{}` expects exactly 1 argument",name)));
    }
            
    Ok(Some(sql_expr!("FIND_IN_SET (? , {})", &args[0], select)))
    
}

// Getter fucntion for the mapper
pub fn permission_handler() -> impl FieldHandler {
    PermissionFieldHandler {
         default_handler: DefaultFieldHandler::new(), 
    }
}

#}

Toql structs usually have a lot of Option types to make fields selectable with a query. Let's look how to attribute them with serde for smooth interaction.

This requires the feature serde for Toql:

[dependencies]
toql = {version = "0.4", features=["serde"]}

It's nice to omit unselected fields. This can easily achieved with #[serde(skip_serializing_if = "Option::is_none")]

# #![allow(unused_variables)]
#fn main() {
    use toql::prelude::{Toql, Join};
    use serde::Serialize;
#   #[derive(Toql, Serialize)]
#   struct Address {
#       #[toql(key)]
#       id: u64
#   }

#[derive(Toql, Serialize)]
struct User {
    #[toql(key)]
    id: u64,

    #[serde(skip_serializing_if = "Option::is_none")]
    age: Option<u8>,
    
    #[serde(skip_serializing_if = "Option::is_none")]
    #[toql(join)]
    address: Option<Option<Join<Address>>> // Selectable left join
}
#}

Your server needs deserializing either

• when creating a new item
• or when updating an existing item

    use toql::prelude::Toql;
    #[derive(Toql)]
    #[toql(auto_key)]
    struct User {
    
        // Serde `default` allows missing field `id` in Json
        //  Needed typically for insert and auto key
        #[serde(default)] 
        #[toql(key)]
        id: u64
        
        // No Serde attribute
        // Field must always be present in Json, but may be `null` -> `Option::None`
        // Fields that are `None` wont be updated.
        name: Option<String>

        // Never deserialize expressions
        #[serde(skip_deserializing)]  
        #[toql(sql = "(SELECT COUNT(*) From Book b WHERE b.author_id = ..id)")]
        pub number_of_books: Option<u64>,
    
        // See comment below
        #[serde(default, deserialize_with="des_double_option")]
        address: Option<Option<Join<Address>>> 
    }

Notice the double Option on the selectable left join address. When deserializing from JSON the following mapping works:

To make this happen you need a custom deserialization function:

# #![allow(unused_variables)]
#fn main() {
use serde::{Deserializer, Deserialize};

pub fn des_double_option<'de, T, D>(de: D) -> Result<Option<Option<T>>, D::Error>
where
    T: Deserialize<'de>,
    D: Deserializer<'de>,
{
    Deserialize::deserialize(de).map(Some)
}
#}

Now you get the following:

• If you omit address in your JSON #[serde(default)] kicks in and you get None.
• If you send "addess": null, you get Some(None).
• If you send "address: {"id": 5}", you get Some(Some(Join::Key(AddressKey{id:5}))).
• If you send "address: {"id": 5, ...}", you get Some(Some(Join::Entity(Address{id:5, ...}))).

Toql update will now work as expected.

Toql generates a lot of code. Mostly from the Toql derive, but also from various macros, such as query!.

To debug Toql generated code, follow these steps:

1. If you have a lot of modules move the affected mod at the end of the mod list. (So generated code will appear in the terminal last).
2. Run cargo with the logger enabled and a single job: RUST_LOG=DEBUG cargo check --jobs=1
3. Copy all the logged code from the derived struct and paste it into the source file.
4. Remove the log headers by regex replacing \[2.* with an empty string. There should be 13 occurences.
5. Copy your derived struct.
6. Comment out your derived struct.
7. On the copied struct remove all references to Toql.
8. Format your document and debug!

If you have issues with Toql you can post them on GitHub.