Provide a more robust "real-world" example

[brandonros]

This is incomplete but it might be nice for people to see a "real world example" (I know Rust language is big fan of repos having examples/ directory):

use crate::formatted_structs::ParsedScrapeResult;
use crate::types::Result;
use crate::utilities::read_file_to_string;
pub struct Database;

impl Database {
    pub async fn migrate() -> Result<()> {
        let migrations = vec![
            read_file_to_string("./schema/001-scrape_results.sql").await?,
            read_file_to_string("./schema/002-formatted_options.sql").await?,            
        ];
        let connection = sqlite::open("./db.sqlite3")?;
        for migration in migrations {
            connection.execute(migration)?;
        }
        Ok(())
    }

    async fn transaction() -> Result<()> {
        todo!()
    }

    pub async fn insert(scrape_result: &ParsedScrapeResult) -> Result<()> {
        // open connection
        let connection = sqlite::open("./db.sqlite3")?;

        // begin transaction
        connection.execute("BEGIN")?;

        // insert into scrape_results
        let mut scrape_stmt = connection.prepare("
            INSERT INTO scrape_results (
                scraped_at,
                last_extended_hours_trade_price,
                last_non_reg_trade_price,
                last_trade_price
            ) VALUES (
                :scraped_at, 
                :last_extended_hours_trade_price, 
                :last_non_reg_trade_price, 
                :last_trade_price
            ) RETURNING rowid
        ")?;
        scrape_stmt.bind((":scraped_at", scrape_result.scraped_at as i64))?;
        scrape_stmt.bind((":last_extended_hours_trade_price", scrape_result.last_extended_hours_trade_price))?;
        scrape_stmt.bind((":last_non_reg_trade_price", scrape_result.last_non_reg_trade_price))?;
        scrape_stmt.bind((":last_trade_price", scrape_result.last_trade_price))?;
        let scrape_result_row = scrape_stmt.iter().next().ok_or("expected row")??;
        let scrape_result_id = scrape_result_row.try_read::<i64, _>("id")?;
        // finalize
        drop(scrape_stmt);

        // insert formatted options
        let mut option_stmt = connection.prepare("
            INSERT INTO formatted_options (
                scrape_result_id,
                expiration_date,
                instrument_type,
                strike_price,
                adjusted_mark_price,
                adjusted_mark_price_round_down,
                ask_price,
                ask_size,
                bid_price,
                bid_size,
                break_even_price,
                chance_of_profit_long,
                chance_of_profit_short,
                delta,
                gamma,
                high_fill_rate_buy_price,
                high_fill_rate_sell_price,
                high_price,
                implied_volatility,
                last_trade_price,
                last_trade_size,
                low_fill_rate_buy_price,
                low_fill_rate_sell_price,
                low_price,
                mark_price,
                occ_symbol,
                open_interest,
                previous_close_date,
                previous_close_price,
                pricing_model,
                rho,
                theta,
                updated_at,
                vega,
                volume
            ) VALUES (
                :scrape_result_id,
                :expiration_date,
                :instrument_type,
                :strike_price,
                :adjusted_mark_price,
                :adjusted_mark_price_round_down,
                :ask_price,
                :ask_size,
                :bid_price,
                :bid_size,
                :break_even_price,
                :chance_of_profit_long,
                :chance_of_profit_short,
                :delta,
                :gamma,
                :high_fill_rate_buy_price,
                :high_fill_rate_sell_price,
                :high_price,
                :implied_volatility,
                :last_trade_price,
                :last_trade_size,
                :low_fill_rate_buy_price,
                :low_fill_rate_sell_price,
                :low_price,
                :mark_price,
                :occ_symbol,
                :open_interest,
                :previous_close_date,
                :previous_close_price,
                :pricing_model,
                :rho,
                :theta,
                :updated_at,
                :vega,
                :volume
            )
        ")?;

        for formatted_option in &scrape_result.formatted_options {
            option_stmt.bind((":scrape_result_id", scrape_result_id))?;
            option_stmt.bind((":expiration_date", formatted_option.expiration_date.as_str()))?;
            option_stmt.bind((":instrument_type", formatted_option.instrument_type.as_str()))?;
            option_stmt.bind((":strike_price", formatted_option.strike_price))?;
            option_stmt.bind((":adjusted_mark_price", formatted_option.adjusted_mark_price))?;
            option_stmt.bind((":adjusted_mark_price_round_down", formatted_option.adjusted_mark_price_round_down))?;
            option_stmt.bind((":ask_price", formatted_option.ask_price))?;
            option_stmt.bind((":ask_size", formatted_option.ask_size))?;
            option_stmt.bind((":bid_price", formatted_option.bid_price))?;
            option_stmt.bind((":bid_size", formatted_option.bid_size))?;
            option_stmt.bind((":break_even_price", formatted_option.break_even_price))?;
            option_stmt.bind((":chance_of_profit_long", formatted_option.chance_of_profit_long))?;
            option_stmt.bind((":chance_of_profit_short", formatted_option.chance_of_profit_short))?;
            option_stmt.bind((":delta", formatted_option.delta))?;
            option_stmt.bind((":gamma", formatted_option.gamma))?;
            option_stmt.bind((":high_fill_rate_buy_price", formatted_option.high_fill_rate_buy_price))?;
            option_stmt.bind((":high_fill_rate_sell_price", formatted_option.high_fill_rate_sell_price))?;
            option_stmt.bind((":high_price", formatted_option.high_price))?;
            option_stmt.bind((":implied_volatility", formatted_option.implied_volatility))?;
            option_stmt.bind((":last_trade_price", formatted_option.last_trade_price))?;
            option_stmt.bind((":last_trade_size", formatted_option.last_trade_size))?;
            option_stmt.bind((":low_fill_rate_buy_price", formatted_option.low_fill_rate_buy_price))?;
            option_stmt.bind((":low_fill_rate_sell_price", formatted_option.low_fill_rate_sell_price))?;
            option_stmt.bind((":low_price", formatted_option.low_price))?;
            option_stmt.bind((":mark_price", formatted_option.mark_price))?;
            option_stmt.bind((":occ_symbol", formatted_option.occ_symbol.as_str()))?;
            option_stmt.bind((":open_interest", formatted_option.open_interest))?;
            option_stmt.bind((":previous_close_date", formatted_option.previous_close_date.as_str()))?;
            option_stmt.bind((":previous_close_price", formatted_option.previous_close_price))?;
            option_stmt.bind((":pricing_model", formatted_option.pricing_model.as_str()))?;
            option_stmt.bind((":rho", formatted_option.rho))?;
            option_stmt.bind((":theta", formatted_option.theta))?;
            option_stmt.bind((":updated_at", formatted_option.updated_at.as_str()))?;
            option_stmt.bind((":vega", formatted_option.vega))?;
            option_stmt.bind((":volume", formatted_option.volume))?;
            // execute the statement
            option_stmt.next()?;

            // reset
            option_stmt.reset()?;
        }

        // finalize
        drop(option_stmt);

        // finalize
        connection.execute("COMMIT")?;

        Ok(())
    }
}

I'm going to guess a lot of people will probably prefer to use https://github.com/rusqlite/rusqlite instead to avoid having to do manual bindings/auto-inherit ease of life things like transactions / automatic serialization into queries I think