stor family of commands

store_commands.md

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. I 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 doug {
       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
   }

   And the sql would look something like this below. 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
   }

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.