# Stor family of commands

## Introduction

Originally when I wrote the `$db` PR, I went in knowing that other commands would be needed. The concept is to have an in-memory sqlite database available to you at any time to store anything you want. I just chose `$db` because it was very nushell-y and easily accessible.

An alternative that I came up with was to have a command named `stor` with subcommands. `stor` without and `e` because you don't get groceries from it. :) We can name it whatever, but for now, it's `stor`. The idea behind the `stor` family of commands is that they provide a way to interact with the in-memory database.

### Commands

Just spit balling here. I think we want the basic CRUD commands to start out with. Create, Read, Update, Delete.

1. `stor` - command to list the sub commands
2. `stor init` - Not sure we need an init command yet. This creates the in-memory database if we move the creation from main.
3. `stor create` or `stor create-table` - This command creates a table in the in-memory database. It should take a record that can pass in column names and datatype. I'm guessing we use nushell datatype names to specify sqlite datatypes and then just match over them to make the proper sql string like this one.

SQL:

    create table table_name (
        id integer not null primary key
        column_name1 sqlite_datatype
        column_name2 sqlite_datatype
        column_name3 sqlite_datatype
        ...
    )

Nushell Example:

    stor create --table-name table_name --columns {
        column_name1: int, 
        column_name2: datetime, 
        column_name3: float
    }

4. `stor open` - Not sure we'll need this. We may just retrofit the `open` command. It should open the particular nushell in-memory database and return the contents of the table specified.

SQL:

    select * from my_table_name

Nushell Example:

    stor open --table-name my_table_name

5. `stor insert` - This would translate to a sql insert statement. I'm thinking it would take a record.

SQL:

    insert into my_table_name (
        column_name1, 
        column_name2, 
        column_name3
    ) values (
        value1, 
        value2, 
        'value3'
    )

Nushell Example:

    stor insert --table-name my_table_name {
        column_name1: value1, 
        column_name2: value2, 
        column_name3: value3
    }

We'll have to map nushell datatypes to sqlite datatypes. In sqlite numbers and bools are not quoted, strings and dates are.

6. `stor update` - This would translate to a sql update statement. It maybe take two records. This one is kind of tricky.

SQL:

    update table_name 
    set column_name1 = value1, 
        column_name2 = value2, 
        column_name3 = 'value3'
    where condition

Nushell Example:

    stor update {
        column_name1: value1, 
        column_name2: value2, 
        column_name3: value3
    } --condition {
        column_name1 == nushell
    }

7. `stor delete` - This would delete rows or tables and maps to the sql delete statement.

SQL:
Delete table

    drop table table_name

Delete rows

    delete from table_name where condition

Nushell Example:
Delete a table

    stor delete --table-name table_name

Delete row(s)

    stor delete --table-name --rows {
        column_name1 == nushell 
    }

8. `stor reset` - This command brings you back to the original state without restarting nushell. It would essentially rung the `stor init` steps again.
9. Update `query db` to be able to point to the in-memory database and perhaps `schema` too if necessary.
10. `stor export` - This command persists the in-memory database in nuon or json or something else?
11. `stor import` - This command takes the input of the `stor save` output and restores the in-memory database to the state contained in the nuon/json file(s).

### Secret Sauce

Just to document my findings, this is really what makes a sqlite in-memory database work.

    // This is the real secret sauce to having an in-memory sqlite db. You must
    // start a connection to the memory database in main so it will exist for the
    // lifetime of the program. If it's created with how MEMORY_DB is defined
    // you'll be able to access this open connection from anywhere in the program
    // by using the identical connection string.
    let db = nu_protocol::sqlite_db::open_connection_in_memory_custom()?;

This is how `MEMORY_DB` is definied.

const MEMORY_DB: &str = "file:memdb1?mode=memory&cache=shared";

So, anything within the current process that tries to open a connection to `MEMORY_DB` will get a connection to the in-memory database that was created previously.

With that said, I'm not confident that the initial connection has to be made in `main()`. I have not tested yet but we can try to create that connection in other places, but it has to be in the nushell executable's memory space and not a plugin because plugins being and end.

Note that the `MEMORY_DB` string defines a file as `memdb1`. Technically, we can define as many in-memory databases that we want by naming them `memdb1`, `memdb2`, `memdb3`, etc. in the connection string. I'm sure that will work but I think it's better to just have one database and provide capabilities to create mutliple tables.