SQ: R6 Class For Managing Queries Stored in SQLite database
In eamoakohene/storedQry: Helper Class For Stored Queries In SQLite

SQ provides a convienient way to separate complex sql statements from R code. SQ class is a helper class and not intended to run on its own. SQ expects a table by name stored_queries to exist in sqlite database db.

The ddl statement of the stored_queries table is as follows

 CREATE TABLE stored_queries (
      id    INTEGER      PRIMARY KEY AUTOINCREMENT NOT NULL,
      qry_name           VARCHAR (50),
      qry_params         TEXT,
      qry_sql            TEXT,
      qry_default_values TEXT
      );

Using the procreate method of class SQ will run the above ddl and create the query table if does not already exist.

    
    db <- "path_to_sqlite_db"
    
    SQ$new( db )$table_procreate()  #create stored_queries table


    #set query name and parameters
    SQ$new( db, name , params)
    SQ$new( db, name )$set_params( params )
    SQ$new( db )$set_name( name )$set_params( params )

    #set query name, parameters and execute/add/update/delete/list/print
    SQ$new( db, name , params )$qry_exec()
    SQ$new( db, name , params )$qry_add()
    SQ$new( db, name , params )$qry_update()
    SQ$new( db, name , params )$qry_delete()
    SQ$new( db, name , params )$qry_list()
    SQ$new( db, name , params )$qry_print_sql()

    SQ$new( db )$qry_list()   # list names of all stored queries
    SQ$new( db )$table_list() # list tables in database db
    SQ$new( db )$table_count()

    #operations on tables stored in db
    SQ$new( db )$table_get_records(tname, top=TRUE, where=NULL, order_by = order_by )
    SQ$new( db )$table_head(tname, n = 10, where = NULL, order_by = order_by )
    SQ$new( db )$table_tail(tname, n = 10, where = NULL, order_by = order_by )
    SQ$new( db )$table_all(tname, where= NULL, order_by = order_by )
    SQ$new( db )$table_info(tname)
    SQ$new( db )$table_add(tname, df)
    SQ$new( db )$table_create(tname, df)
    SQ$new( db )$table_update(tname, set, where)
    SQ$new( db )$table_insert(tname, fields, values)
    SQ$new( db )$table_select(tname, fields, where,n )

`name`	name of the query to execute
`params`	list of query parameters. The parameter names are surrounded by backticks and are prepended by `@i_` for `numbers` or `@s_` for `strings` parameters. Example: list( `@s_name` = "Emmalyn", `@i_age` = 5, `@i_weight` = 2.5 )
`db`	name of the sqlite database. The queries are expected to be stored in table named `stored_queres` in database `db`
`tname`	name of the table
`top`	logical variable to indicate whether top or bottom records to be retrieved
`where`	sql where clase for subsetting records to be returned
`n`	number of records to return
`df`	dataframe to be stored or appended
`fields`	a vector containing the fields of the tables to be queries
`values`	a vector containing values to be added to the table

 ##Not run
  sq <- storedQry::SQ$new( 
         db = 'my_db', 
         name = 'my_qry_name', 
         params = list(
               `@s_field1` = "value1", 
               `@i_field2` = 2,
               ... 
        ) 
  )
  sq$qry_exec()

  #or
  storedQry::SQ$new( 
    'my_db' 
  )$set_name( 
    'my_qry_name' 
  )$set_params( 
    list(
        `@s_field1` = "value1", 
        `@i_field2` = 2, 
        ... 
    ) 
  )$qry_exec()

 #Typically you will create one SQ object and use for several queries
 
 sq <- storedQry::SQ$new('my_db')
 
 sq$set_name( 'my_qry_name1' )$set_params( my_params_list1 )$qry_exec()
 
 sq$set_name( 'my_qry_name2' )$set_params( my_params_list2 )$qry_exec()