Add support for foreign keys

[CrCliff]

Support for foreign keys for nested structs would support SQL JOINs and more complicated/efficient querying. Foreign key support would be an alternative to dumping nested structs to a field as JSON.

Consider the following example:

use serde::{Deserialize, Serialize};
use turbosql::Turbosql;

#[derive(Turbosql, Default, Serialize, Deserialize)]
pub struct Department {
    pub rowid: Option<i64>,
    pub employees: Option<Vec<Employee>>,  // nested struct
    pub name: Option<String>,
}

#[derive(Turbosql, Default, Serialize, Deserialize)]
pub struct Employee {
    pub rowid: Option<i64>,
    pub departmentid: Option<i64>,  // matches the rowid property from Department struct
    pub first_name: Option<String>,
    pub last_name: Option<String>,
}

pub fn main() -> Result<i64, turbosql::Error> {
    let employee1 = Employee {
        departmentid: Some(0),
        first_name: Some("Bob".to_string()),
        last_name: Some("Johnson".to_string()),
        ..Default::default()
    };
    let employee2 = Employee {
        departmentid: Some(0),
        first_name: Some("Frank".to_string()),
        last_name: Some("Waltz".to_string()),
        ..Default::default()
    };

    let department = Department {
        employees: Some(vec![employee1, employee2]),
        name: Some("Sales".to_string()),
        ..Default::default()
    };

    department.insert()
}

Turbosql will generate the following SQL tables (with some extra stuff):

CREATE TABLE employee (
    rowid INTEGER PRIMARY KEY,
    departmentid INTEGER,
    first_name TEXT,
    last_name TEXT
);

CREATE TABLE department (
    rowid INTEGER PRIMARY KEY,
    name TEXT,
    address TEXT,
    employees TEXT  -- contains a JSON dump of the vector of employees
);

But since we're only inserting the department object, the employee table is not populated. Instead, a JSON string is dumped into the department.employees field:

sqlite> SELECT * FROM DEPARTMENT;
1|Sales||[{"rowid":null,"departmentid":0,"first_name":"Bob","last_name":"Johnson"},{"rowid":null,"departmentid":0,"first_name":"Frank","last_name":"Waltz"}]

This JSON dump is not queryable with SQL. To support foreign keys, we currently have to manually insert each nested object:

// This sucks... :(
fn put(department: &Department) -> Result<i64, crate::Error> {
    let mut dpt = department.clone();
    let dpt_employees = dpt.employees.clone();
    // Clear the employees so there's no JSON dump
    dpt.employees = None;
    let rowid = dpt.insert()?;

    match dpt_employees {
        Some(employees) => {
            // Manually set the department id on each employee and insert
            for emp in &employees{
                let mut e = emp.clone();
                e.departmentid = Some(rowid);
                e.insert()?;
            }
        },
        None => {},
    };
    Ok(rowid)
}

Instead, there should be some technique for signaling table relationships to Turbosql. This could be an attribute macro that signals to Turbosql a field represents a foreign key from another table. If this macro is present, Turbosql should generate a foreign key field that references the parent table, and remove the respective field in the parent table. Ideally, Turbosql would also automatically insert these nested structs into the correct table when .insert() is called on the parent struct.

For example:

#[derive(Turbosql, Default, Serialize, Deserialize)]
pub struct Employee {
    pub rowid: Option<i64>,
    #[foreign_key(Department)]  // new attribute macro signals to Turbosql this field represents a FOREIGN KEY
    pub departmentid: Option<i64>,
    pub first_name: Option<String>,
    pub last_name: Option<String>,
};

CREATE TABLE employee (
    rowid INTEGER PRIMARY KEY,
    FOREIGN KEY (departmentid) REFERENCES department(rowid),  -- uses a FOREIGN KEY from the parent table "department"
    first_name TEXT,
    last_name TEXT
);

CREATE TABLE department (   -- no longer has "employees" field
    rowid INTEGER PRIMARY KEY,
    name TEXT,
    address TEXT
);

This would enable JOINs in SQL for more advanced querying of related tables.

-- something like this...
SELECT *
FROM department
INNER JOIN employee
ON department.rowid = employee.departmentid;