sushidub/sqlite_query.sh

## sqlite_query.sh
#!/bin/bash
#
#   @note:    WIP!!
#   @desc:    Quickly perform a series of simple CRUD type actions i.e., query, update, insert, delete
#             against a locally owned sqlite database. Assumes one standard integer based 'id' column.
#             Optional SQLITE_DB, SQLITE_TBL, SQLITE_INDEX env variables replace the -db, -t, and -c flags
#   @why:     Because I use several sqlite db's and frequently query and/or update them. I finally got sick of
#             invoking the sqlite cli, typing sensitive slq syntax, and wanted to build or use something super
#             simple without relying on but Bash functionality.
#
#   @function slq
#   @param:   [[-db,--database] <pathname to local db> (defaults to the env variable: SQLITE_DB, name.ext considered inclusive)]
#   @param:   [[-t,--table] <table name> (defaults to the env variable: SQLITE_TBL)]
#   @param:   [[-c,--col,--column] <column/header> (defaults to the env variable: SQLITE_INDEX)]
#   @param:   [[-u,--update] <table name> (defaults to the env variable: SQLITE_TBL)]
#   @param:   [[-i,--insert] <table name> (defaults to the env variable: SQLITE_TBL)]
#   @param:   [[-q,--query] <query/keyword>]
#   @flag:    --debug (defaults to false)
#   @flag:    [-h,--help]
#
#   Tested:   GNU bash, version 5.1.8(1)-release (x86_64-apple-darwin19.6.0)
#
#   TODO:
#   - more tests using flag params (i.e., --col, --table, --database)
#   - implement multi-column lookup/query
#   - clean up debug statements and/or leverage Bash's builtin debug utility
#   - reduce overall variable clutter
#   - normalize variable syntax (is the 'local' option even needed?)
#   - implement DELETE
#   - portability tests
#   - confirm/validate current user and DB access permissions
#   - first run setup (env variables, permissions, etc)
#
#   Usage:
#   $ . sqlite_query.sh && slq --help
#
function slq() {
  # (1) initialize variables used during execution
  # the main array (associative) used for defaults, tracking, and updaing params throughout execution
  local -A ARR
  # column names passed in as arguments
  ARR[COLS]=""
  # default table name
  ARR[TABLE]="$SQLITE_TBL"
  # default path to sqlite db
  ARR[DB]="$SQLITE_DB"
  ARR[QUERY]=""
  ARR[UPDATE]=""
  ARR[INSERT]=""
  # default to false (if true, prints specific variable values during execution unrelated to final results)
  ARR[DEBUG]=0
  #
  # parameters array (indexed) used for kepping track where our pointer is during iteration
  local -a params
  #
  # ref variable used to store the type of requested CRUD action derived from the initial stdin params
  # can be one of insert, query (default), update
  local request="query"
  #
  # ------------help output--------------
  local -r usage="\n \
  Usage: slq [-c column] [-db database] [-i insert] [-q query] [-t table] [-u update] \n \
  Options:\n \
  \t -c, --col, --column \tColumn header to query against (defaults to env variable: SQLITE_INDEX)\n \
  \t -db, --database \tValid pathname to database (e.g., <path>/<database name.ext>, defaults to env variable: SQLITE_DB)\n \
  \t -i, --insert \tInsert a new entry\n \
  \t -q, --query \t\tQuery lookup/search keyword\n \
  \t -t, --table \t\tTable name to perform search against (defaults to env variable: SQLITE_TBL)\n \
  \t -u, --update \t\tUpdate an existing entry\n \
  \t --debug \t\tOutput passed params and log of the local array used to assemble the final SELECT statement\n\n"

  # (2) iterate over the arguments provided in the function call
  # passing each into a new indexed array giving us the convenience of an index -
  # this will also provide more flexiblity when and how the final SELECT statement gets assembled
  local _param
  for _param in "$@"
  do
    params+=("$_param")
    # and if a debug flag exists as a param, then set debug var to true
    if [[ $_param = "--debug" ]]; then
      ARR[DEBUG]=1
    elif [[ $_param = "-h" ]] || [[ $_param = "--help" ]] ; then
      printf "%b" "$usage"
    fi
  done

  # ------------debug--------------
  if [[ ${ARR[DEBUG]} -eq 1 ]]; then
    printf "\n\n-------PARAMS (%s)--------" "$#";
  fi

  # (3) iterate over previously created indexed array
  for _param in "${!params[@]}"
  do
    # p is the value located at the index (where index = the loops current pointer position) within the arguments array
    local p=${params[$_param]}

    # only set b if the pointer is at a position its previous position was 0 or greater
    if [[ $_param -gt 0 ]]; then
      # b is value located at the index (where index = the loops current pointer position - 1) within the arguments array
      local b=${params[$((_param - 1))]}
    fi

    # ------------debug--------------
    if [[ ${ARR[DEBUG]} -eq 1 ]]; then
      printf "\n%d: %s" "$((_param+1))" "$p"
    fi

    if [[ $p != [-]* ]]; then
      # current pointer matches an argument that is not the the main function call itself or a flag
      # it also comes directly after a previous pointer position that matches a flag argument
      if [[ ! $_param -eq 0 ]] && [[ $b =~ [-]* ]] && [[ $b != "--debug" ]]; then
        # populate the relevant index of the main array based on the flag
        # matching the argument string read at the previous pointer position
        case $b in
          -c|--col|--column)
            if [[ -z ${ARR[COLS]} ]]; then
              ARR[COLS]="$p"
            else
              ARR[COLS]+=",$p"
            fi
            ;;
          -t|--table) ARR[TABLE]="$p";;
          -db|--database) ARR[DB]="$p";;
          -q|--query) ARR[QUERY]="$p";;
          -i|--insert) ARR[INSERT]="$p" && ARR[QUERY]="$p";;
          -u|--update) ARR[UPDATE]="$p" && ARR[QUERY]="$p";;
        esac
      else
        ARR[QUERY]="$p"
      fi
    fi

    if [[ $p = [-]* ]] && [[ $p = @(--insert|-i|--query|-q|--update|-u) ]] && [[ $b != @(--insert|-i|--query|-q|--update|-u) ]]; then
      # current pointer matches a CRUD flag keyword
      # so set the CRUD keyword variable for later use
      case $p in
        --insert|-i) request=insert ;;
        --query|-q) request=query ;;
        --update|-u) request=update ;;
      esac
    fi
  done

  # (4)
  # if the list containing any passed in column (e.g., -c, --col, --column) parameter is empty
  # we need to at least provide the default column to use for the query (in the case of Indo.db the default header value is 'SQLITE_INDEX')
  if [[ -z ${ARR[COLS]} ]]; then
    ARR[COLS]="$SQLITE_INDEX";
  fi

  # (5)
  # now we can string together the final part of our SELECT statement (e.g., the WHERE clause)
  # and in case we have more than one column in our COLS list
  # we'll concatenate them together using OR conditionals
  # e.g., WHERE (column1 LIKE keyword OR column2 LIKE keyword OR...)
  local COL_QUERY
  function create_query_string() {
    local -a COLS
    local IFS
    IFS=,
    read -ra COLS <<< "${ARR[COLS]}"

    local col=
    for col in "${!COLS[@]}"
    do
      if [[ $col -eq 0 ]]; then
        COL_QUERY="${COLS[$col]} LIKE \"%${ARR[QUERY]}%\""
      else
        COL_QUERY+=" OR ${COLS[$col]} LIKE \"%${ARR[QUERY]}%\""
      fi
    done
  }
  local input=
  if [[ -n ${ARR[QUERY]} ]] && [[ ${ARR[QUERY]} != [-]* ]]; then
    if [[ ${#ARR[QUERY]} -lt 3 ]]; then
      while [[ -z $input ]] || [[ ${#input} -lt 3 ]]; do
        printf "\n\nYou need to provide a query string (minimum of 3 characters):\n> "
        read -r input
      done
      ARR[QUERY]="$input"
    fi
    create_query_string
  fi

  # (6)
  # declare utility functions prior to their invocations
  local -a _RESULT_IDS
  function query() {
    _RESULT_IDS=()
    readarray -t _RESULT_IDS < <(sqlite "${ARR[DB]}" "SELECT id FROM ${ARR[TABLE]} WHERE ($COL_QUERY)")
    exitStatus $?

    if [[ ${#_RESULT_IDS[*]} -eq 0 ]]; then
      printf "\n\nno results for %s\n\n" "${ARR[QUERY]}"
    elif [[ ${#_RESULT_IDS[*]} -gt 0 ]]; then
      printf "\n\nFound \e[38;5;220m%d$COLOR_RESET entries containing '%s'\n\n" "${#_RESULT_IDS[*]}" "${ARR[QUERY]^^}"
    fi
    return
  }

  function  read_valid_index_input() {
    input=
    while [[ -z $input ]] || [[ ${#input} -lt 3 ]]; do
      echo "Enter the value for the $col column. This value is required."
      read -r input
    done
    ARR[QUERY]="$input"
    ARR[INSERT]="$input"
    create_query_string && query
    return
  }

  function display_index_options() {
    echo "Choose an entry to update or skip to create a new entry..."
    local _i=1
    local -A ENTRIES
    for entry in "${_RESULT_IDS[@]}"; do
      name=$(sqlite "${ARR[DB]}" "SELECT $SQLITE_INDEX FROM ${ARR[TABLE]} WHERE id=$entry")
      exitStatus $?
      printf "\n[%d] %s" "$_i" "$name"
      ENTRIES[$_i]=$entry
      _i=$((_i+1))
    done
    printf "\n[Enter] Create a new entry\n"
    local OVERWRITE=
    local option_status="invalid"
    while [[ $option_status != "valid" ]]; do
      read -r OVERWRITE
      case $OVERWRITE in
        [1-$_i] ) printf "\n\n"
          ARR[UPDATE]=${ENTRIES[$OVERWRITE]}
          if [[ $request != "update" ]]; then
            request="update"
            updateDB
          else
            return
          fi
          option_status="valid";;
        "" ) printf "\n\n"
          if [[ $request = "update" ]]; then
            request="insert"
          fi
          ARR[INSERT]=
          option_status="valid";;
        * ) echo "invalid option"
      esac
    done
    return
  }

  function exitStatus() {
    if [[ ! $1 -eq 0 ]]; then
      echo "Something went wrong with the query."
      echo "Exiting with status $1" >&2; return 1
    else
      return 0
    fi
  }

  # (7)
  # get all the column names as they exist in the DB
  local DB_COL_NAMES_ARR
  readarray -t DB_COL_NAMES_ARR < <(sqlite "${ARR[DB]}" "SELECT name FROM PRAGMA_TABLE_INFO('${ARR[TABLE]}')")
  exitStatus $?

  # ------------debug--------------
  if [[ ${ARR[DEBUG]} -eq 1 ]]; then
    printf "\n\n---------DEBUG----------"
    printf "\n#ARR[@]: %s" "${#ARR[@]}"
    printf "\nARR[*]: %s" "${ARR[*]}"
    printf "\nARR[DB]: %s\nARR[TABLE]: %s\nARR[COLS]: %s\nARR[QUERY]: %s\nARR[INSERT]: %s\nARR[UPDATE]: %s\nARR[DEBUG]: %s" "${ARR[DB]}" "${ARR[TABLE]}" "${ARR[COLS]}" "${ARR[QUERY]}" "${ARR[INSERT]}" "${ARR[UPDATE]}" "${ARR[DEBUG]}"
    printf "\nCOLS: %s" "${COLS[*]}"
    printf "\nCOL_QUERY: %s" "$COL_QUERY"
    printf "\nRESULTS FROM QUERY: %d" "${#_RESULT_IDS[*]}"
    printf "\nDB_COL_NAMES_ARR: %s" "${DB_COL_NAMES_ARR[*]}"
    printf "\nrequest type: %s" "$request"
    printf "\n-------------------------\n\n"
  fi

  # (9)
  # the business
  # declared prior to being invoked by the case
  insertDB() {
    # ------------debug--------------
    if [[ ${ARR[DEBUG]} -eq 1 ]]; then
      printf "\n\n--------- fn: INSERT [%s]----------\n\n" "${ARR[INSERT]^^}"
    fi

    local -A INSERT_COL_VALS
    # if the insert subcommand contained a query argument
    # assign it to the default search column/index
    # then query the db for any existing entries that match the query string
    if [[ -n ${ARR[INSERT]} ]] && [[ ${#_RESULT_IDS[*]} -eq 0 ]]; then
      INSERT_COL_VALS["$SQLITE_INDEX"]="'${ARR[INSERT]^^}'"
    elif [[ -n ${ARR[INSERT]} ]] && [[ ${#_RESULT_IDS[*]} -gt 0 ]]; then
      display_index_options
      if [[ $request = "update" ]]; then return; fi
    fi

    # solicit and assign the rest of the entry values to its associated column name
    for col in "${DB_COL_NAMES_ARR[@]}";
    do
      if [[ $col != "id" ]]; then
        if [[ $col = "$SQLITE_INDEX" ]] && [[ -n ${INSERT_COL_VALS["$SQLITE_INDEX"]} ]]; then
          continue
        elif [[ $col = "$SQLITE_INDEX" ]] && [[ -z ${INSERT_COL_VALS["$SQLITE_INDEX"]} ]]; then
          while [[ -z ${INSERT_COL_VALS["$SQLITE_INDEX"]} ]]; do
            read_valid_index_input
            if [[ ${#_RESULT_IDS[*]} -eq 0 ]]; then
              INSERT_COL_VALS["$SQLITE_INDEX"]="'${ARR[INSERT]^^}'"
            elif [[ ${#_RESULT_IDS[*]} -gt 0 ]]; then
              display_index_options
            fi
          done
        else
          echo "Enter a value for the $col column or leave blank to skip..."
          read -r input
          if [[ -n "$input" ]]; then
            INSERT_COL_VALS[$col]="\"$input\""
          fi
        fi
      fi
    done

    # array syntax reference:
    # array keys - echo "${!INSERT_COL_VALS[@]}"
    # array values = echo "${INSERT_COL_VALS[@]}"
    # array length - echo "${#INSERT_COL_VALS[@]}"
    local input_keys=
    local input_values
    input_keys=$( ( IFS=,; echo "${!INSERT_COL_VALS[*]}" ) )
    input_values=$( (IFS=,; echo "${INSERT_COL_VALS[*]}" ) )

    if [[ ${#INSERT_COL_VALS[@]} -gt 0 ]]; then
      printf "key: %s\tval: %s\n" "$input_keys" "$input_values"
      if (sqlite "${ARR[DB]}" "INSERT INTO ${ARR[TABLE]} ($input_keys) VALUES($input_values)"); then
        printf "\nSuccessfully added %s to %s\n" "$input_values" "${ARR[TABLE]}"
      else
        printf "\nSomething went wrong with the query.\nexiting with status %d\n\n" "$?"
      fi
    else
      echo "Missing values in function call" >&2; return 1
    fi
  }
  queryDB() {
    # ------------debug--------------
    if [[ ${ARR[DEBUG]} -eq 1 ]]; then
      printf "\n\n--------- fn: QUERY [%s]----------\n\n" "${ARR[QUERY]}"
    fi

    local entry=
    local name=
    if [[ $request = "query" ]]; then
      if [[ -z ${ARR[QUERY]} ]]; then
        input=
        while [[ -z $input ]] || [[ ${#input} -lt 3 ]]; do
          echo "You need to provide a query string (minimum of 3 characters):"
          read -r input
        done
        ARR[QUERY]="$input"
        create_query_string && query
      fi
      for entry in "${_RESULT_IDS[@]}"; do
        name=$(sqlite -column "${ARR[DB]}" "SELECT * FROM ${ARR[TABLE]} WHERE id=$entry")
        exitStatus $?
        echo "$name"
      done
    fi

    return
  }
  updateDB() {
    # ------------debug--------------
    if [[ ${ARR[DEBUG]} -eq 1 ]]; then
      printf "\n\n--------- fn: UPDATE [%s]----------\n\n" "${ARR[UPDATE]}"
    fi

    local update_entry_arr=

    if [[ ${#_RESULT_IDS[*]} -gt 0 ]]; then
      display_index_options
    fi

    if [[ -n ${ARR[UPDATE]} ]]; then
      echo "${ARR[UPDATE]}"
      if [[ ${ARR[UPDATE]} < "a" ]]; then
        echo "${ARR[UPDATE]}"
        readarray -t -d '|' update_entry_arr < <(sqlite "${ARR[DB]}" "SELECT * FROM ${ARR[TABLE]} WHERE id=${ARR[UPDATE]}")
        exitStatus $?
      elif [[ ${ARR[UPDATE]} > "a" ]]; then
        echo "${ARR[UPDATE]}"
      fi
    elif [[ -z ${ARR[UPDATE]} ]]; then
      echo "You need to provide a query..."
      read -r input
      ARR[QUERY]="$input"
      create_query_string && query
    fi

    if [[ ${#update_entry_arr[*]} -eq 0 ]] && [[ ${#_RESULT_IDS[*]} -eq 0 ]]; then
      printf "\nCan't find an entry for %s\n" "${ARR[QUERY]}"
      printf "Would you like to create a new entry for %s\n" "${ARR[UPDATE]}"
      printf "[y]\n[n]\n\n"
      read -r input
      if [[ $input = "y" ]]; then
        ARR[INSERT]="${ARR[UPDATE]}"
        ARR[UPDATE]=
        request="insert"
        insertDB
        if [[ $request = "insert" ]]; then return; fi
      fi
    fi

    # ------------debug--------------
    if [[ ${ARR[DEBUG]} -eq 1 ]]; then
      printf "\nUpdate entry array: \n%s" "${update_entry_arr[*]}"
    fi

    local UPDATE_QUERY="SET "
    local UPDATING=true
    while $UPDATING; do
      echo "Select a column to update..."
      printf "\n[#] COLUMN:\tVALUE"
      printf "\n--- ------\t------\n"
      local idx=
      for idx in "${!DB_COL_NAMES_ARR[@]}"; do
        if [[ ${DB_COL_NAMES_ARR[$idx]} != "id" ]]; then
          printf "[%d] %s:\t%s\n" "$((idx+1))" "${DB_COL_NAMES_ARR[$idx]}" "${update_entry_arr[$idx]}"
        fi
      done
      printf "\n[Enter] to submit changes.\n\n"

      read -r UPDATE_COL_OPTION
      case $UPDATE_COL_OPTION in
        [1-$((${#DB_COL_NAMES_ARR[@]}-1))] ) printf "\n%s: " "${DB_COL_NAMES_ARR[$((UPDATE_COL_OPTION-1))]}"
          local UPDATE_COL_VALUE=
          read -r UPDATE_COL_VALUE
          if [[ ${DB_COL_NAMES_ARR[$((UPDATE_COL_OPTION-1))]} = "$SQLITE_INDEX" ]]; then
            echo "we should check for existing entries here..."
            ARR[UPDATE]="$UPDATE_COL_VALUE"
            ARR[QUERY]="$UPDATE_COL_VALUE"
            create_query_string && query
            if [[ ${#_RESULT_IDS[*]} -gt 0 ]]; then
              display_index_options
              if [[ $request = "insert" ]]; then
                insertDB
                UPDATING=false
              fi
            else
              UPDATE_COL_VALUE="${UPDATE_COL_VALUE^^}"
            fi
          fi
          UPDATE_QUERY+="${DB_COL_NAMES_ARR[$((UPDATE_COL_OPTION-1))]}='$UPDATE_COL_VALUE',"
          update_entry_arr[$((UPDATE_COL_OPTION-1))]="$UPDATE_COL_VALUE"
          ;;
        "" )
          echo "${UPDATE_QUERY%,}"
          local len=$((${#update_entry_arr[@]}-1))
          local update_db=
          update_db=$(sqlite "${ARR[DB]}" "UPDATE ${ARR[TABLE]} ${UPDATE_QUERY%,} WHERE id=${ARR[UPDATE]}")
          exitStatus $?
          echo "$update_db"
          printf "Updated entry id \e[38;5;220m%s : %s$COLOR_RESET\n" "${update_entry_arr[$len]/$'\n'/}" "${update_entry_arr[$SQLITE_INDEX]}"
          UPDATING=false
          ;;
        * )
          echo "Invalid option..."
          ;;
      esac
    done
  }

  if [[ -n $COL_QUERY ]]; then query; fi

  # (8)
  # query action router
  case $request in
    query) queryDB;;
    insert) insertDB;;
    update) updateDB;;
  esac

  return
}
	#!/bin/bash
	#
	# @note: WIP!!
	# @desc: Quickly perform a series of simple CRUD type actions i.e., query, update, insert, delete
	# against a locally owned sqlite database. Assumes one standard integer based 'id' column.
	# Optional SQLITE_DB, SQLITE_TBL, SQLITE_INDEX env variables replace the -db, -t, and -c flags
	# @why: Because I use several sqlite db's and frequently query and/or update them. I finally got sick of
	# invoking the sqlite cli, typing sensitive slq syntax, and wanted to build or use something super
	# simple without relying on but Bash functionality.
	#
	# @function slq
	# @param: [[-db,--database] <pathname to local db> (defaults to the env variable: SQLITE_DB, name.ext considered inclusive)]
	# @param: [[-t,--table] <table name> (defaults to the env variable: SQLITE_TBL)]
	# @param: [[-c,--col,--column] <column/header> (defaults to the env variable: SQLITE_INDEX)]
	# @param: [[-u,--update] <table name> (defaults to the env variable: SQLITE_TBL)]
	# @param: [[-i,--insert] <table name> (defaults to the env variable: SQLITE_TBL)]
	# @param: [[-q,--query] <query/keyword>]
	# @flag: --debug (defaults to false)
	# @flag: [-h,--help]
	#
	# Tested: GNU bash, version 5.1.8(1)-release (x86_64-apple-darwin19.6.0)
	#
	# TODO:
	# - more tests using flag params (i.e., --col, --table, --database)
	# - implement multi-column lookup/query
	# - clean up debug statements and/or leverage Bash's builtin debug utility
	# - reduce overall variable clutter
	# - normalize variable syntax (is the 'local' option even needed?)
	# - implement DELETE
	# - portability tests
	# - confirm/validate current user and DB access permissions
	# - first run setup (env variables, permissions, etc)
	#
	# Usage:
	# $ . sqlite_query.sh && slq --help
	#
	function slq() {
	# (1) initialize variables used during execution
	# the main array (associative) used for defaults, tracking, and updaing params throughout execution
	local -A ARR
	# column names passed in as arguments
	ARR[COLS]=""
	# default table name
	ARR[TABLE]="$SQLITE_TBL"
	# default path to sqlite db
	ARR[DB]="$SQLITE_DB"
	ARR[QUERY]=""
	ARR[UPDATE]=""
	ARR[INSERT]=""
	# default to false (if true, prints specific variable values during execution unrelated to final results)
	ARR[DEBUG]=0
	#
	# parameters array (indexed) used for kepping track where our pointer is during iteration
	local -a params
	#
	# ref variable used to store the type of requested CRUD action derived from the initial stdin params
	# can be one of insert, query (default), update
	local request="query"
	#
	# ------------help output--------------
	local -r usage="\n \
	Usage: slq [-c column] [-db database] [-i insert] [-q query] [-t table] [-u update] \n \
	Options:\n \
	\t -c, --col, --column \tColumn header to query against (defaults to env variable: SQLITE_INDEX)\n \
	\t -db, --database \tValid pathname to database (e.g., <path>/<database name.ext>, defaults to env variable: SQLITE_DB)\n \
	\t -i, --insert \tInsert a new entry\n \
	\t -q, --query \t\tQuery lookup/search keyword\n \
	\t -t, --table \t\tTable name to perform search against (defaults to env variable: SQLITE_TBL)\n \
	\t -u, --update \t\tUpdate an existing entry\n \
	\t --debug \t\tOutput passed params and log of the local array used to assemble the final SELECT statement\n\n"

	# (2) iterate over the arguments provided in the function call
	# passing each into a new indexed array giving us the convenience of an index -
	# this will also provide more flexiblity when and how the final SELECT statement gets assembled
	local _param
	for _param in "$@"
	do
	params+=("$_param")
	# and if a debug flag exists as a param, then set debug var to true
	if [[ $_param = "--debug" ]]; then
	ARR[DEBUG]=1
	elif [[ $_param = "-h" ]] \|\| [[ $_param = "--help" ]] ; then
	printf "%b" "$usage"
	fi
	done

	# ------------debug--------------
	if [[ ${ARR[DEBUG]} -eq 1 ]]; then
	printf "\n\n-------PARAMS (%s)--------" "$#";
	fi

	# (3) iterate over previously created indexed array
	for _param in "${!params[@]}"
	do
	# p is the value located at the index (where index = the loops current pointer position) within the arguments array
	local p=${params[$_param]}

	# only set b if the pointer is at a position its previous position was 0 or greater
	if [[ $_param -gt 0 ]]; then
	# b is value located at the index (where index = the loops current pointer position - 1) within the arguments array
	local b=${params[$((_param - 1))]}
	fi

	# ------------debug--------------
	if [[ ${ARR[DEBUG]} -eq 1 ]]; then
	printf "\n%d: %s" "$((_param+1))" "$p"
	fi

	if [[ $p != [-]* ]]; then
	# current pointer matches an argument that is not the the main function call itself or a flag
	# it also comes directly after a previous pointer position that matches a flag argument
	if [[ ! $_param -eq 0 ]] && [[ $b =~ [-]* ]] && [[ $b != "--debug" ]]; then
	# populate the relevant index of the main array based on the flag
	# matching the argument string read at the previous pointer position
	case $b in
	-c\|--col\|--column)
	if [[ -z ${ARR[COLS]} ]]; then
	ARR[COLS]="$p"
	else
	ARR[COLS]+=",$p"
	fi
	;;
	-t\|--table) ARR[TABLE]="$p";;
	-db\|--database) ARR[DB]="$p";;
	-q\|--query) ARR[QUERY]="$p";;
	-i\|--insert) ARR[INSERT]="$p" && ARR[QUERY]="$p";;
	-u\|--update) ARR[UPDATE]="$p" && ARR[QUERY]="$p";;
	esac
	else
	ARR[QUERY]="$p"
	fi
	fi

	if [[ $p = [-]* ]] && [[ $p = @(--insert\|-i\|--query\|-q\|--update\|-u) ]] && [[ $b != @(--insert\|-i\|--query\|-q\|--update\|-u) ]]; then
	# current pointer matches a CRUD flag keyword
	# so set the CRUD keyword variable for later use
	case $p in
	--insert\|-i) request=insert ;;
	--query\|-q) request=query ;;
	--update\|-u) request=update ;;
	esac
	fi
	done

	# (4)
	# if the list containing any passed in column (e.g., -c, --col, --column) parameter is empty
	# we need to at least provide the default column to use for the query (in the case of Indo.db the default header value is 'SQLITE_INDEX')
	if [[ -z ${ARR[COLS]} ]]; then
	ARR[COLS]="$SQLITE_INDEX";
	fi

	# (5)
	# now we can string together the final part of our SELECT statement (e.g., the WHERE clause)
	# and in case we have more than one column in our COLS list
	# we'll concatenate them together using OR conditionals
	# e.g., WHERE (column1 LIKE keyword OR column2 LIKE keyword OR...)
	local COL_QUERY
	function create_query_string() {
	local -a COLS
	local IFS
	IFS=,
	read -ra COLS <<< "${ARR[COLS]}"

	local col=
	for col in "${!COLS[@]}"
	do
	if [[ $col -eq 0 ]]; then
	COL_QUERY="${COLS[$col]} LIKE \"%${ARR[QUERY]}%\""
	else
	COL_QUERY+=" OR ${COLS[$col]} LIKE \"%${ARR[QUERY]}%\""
	fi
	done
	}
	local input=
	if [[ -n ${ARR[QUERY]} ]] && [[ ${ARR[QUERY]} != [-]* ]]; then
	if [[ ${#ARR[QUERY]} -lt 3 ]]; then
	while [[ -z $input ]] \|\| [[ ${#input} -lt 3 ]]; do
	printf "\n\nYou need to provide a query string (minimum of 3 characters):\n> "
	read -r input
	done
	ARR[QUERY]="$input"
	fi
	create_query_string
	fi

	# (6)
	# declare utility functions prior to their invocations
	local -a _RESULT_IDS
	function query() {
	_RESULT_IDS=()
	readarray -t _RESULT_IDS < <(sqlite "${ARR[DB]}" "SELECT id FROM ${ARR[TABLE]} WHERE ($COL_QUERY)")
	exitStatus $?

	if [[ ${#_RESULT_IDS[*]} -eq 0 ]]; then
	printf "\n\nno results for %s\n\n" "${ARR[QUERY]}"
	elif [[ ${#_RESULT_IDS[*]} -gt 0 ]]; then
	printf "\n\nFound \e[38;5;220m%d$COLOR_RESET entries containing '%s'\n\n" "${#_RESULT_IDS[*]}" "${ARR[QUERY]^^}"
	fi
	return
	}

	function read_valid_index_input() {
	input=
	while [[ -z $input ]] \|\| [[ ${#input} -lt 3 ]]; do
	echo "Enter the value for the $col column. This value is required."
	read -r input
	done
	ARR[QUERY]="$input"
	ARR[INSERT]="$input"
	create_query_string && query
	return
	}

	function display_index_options() {
	echo "Choose an entry to update or skip to create a new entry..."
	local _i=1
	local -A ENTRIES
	for entry in "${_RESULT_IDS[@]}"; do
	name=$(sqlite "${ARR[DB]}" "SELECT $SQLITE_INDEX FROM ${ARR[TABLE]} WHERE id=$entry")
	exitStatus $?
	printf "\n[%d] %s" "$_i" "$name"
	ENTRIES[$_i]=$entry
	_i=$((_i+1))
	done
	printf "\n[Enter] Create a new entry\n"
	local OVERWRITE=
	local option_status="invalid"
	while [[ $option_status != "valid" ]]; do
	read -r OVERWRITE
	case $OVERWRITE in
	[1-$_i] ) printf "\n\n"
	ARR[UPDATE]=${ENTRIES[$OVERWRITE]}
	if [[ $request != "update" ]]; then
	request="update"
	updateDB
	else
	return
	fi
	option_status="valid";;
	"" ) printf "\n\n"
	if [[ $request = "update" ]]; then
	request="insert"
	fi
	ARR[INSERT]=
	option_status="valid";;
	* ) echo "invalid option"
	esac
	done
	return
	}

	function exitStatus() {
	if [[ ! $1 -eq 0 ]]; then
	echo "Something went wrong with the query."
	echo "Exiting with status $1" >&2; return 1
	else
	return 0
	fi
	}

	# (7)
	# get all the column names as they exist in the DB
	local DB_COL_NAMES_ARR
	readarray -t DB_COL_NAMES_ARR < <(sqlite "${ARR[DB]}" "SELECT name FROM PRAGMA_TABLE_INFO('${ARR[TABLE]}')")
	exitStatus $?

	# ------------debug--------------
	if [[ ${ARR[DEBUG]} -eq 1 ]]; then
	printf "\n\n---------DEBUG----------"
	printf "\n#ARR[@]: %s" "${#ARR[@]}"
	printf "\nARR[]: %s" "${ARR[]}"
	printf "\nARR[DB]: %s\nARR[TABLE]: %s\nARR[COLS]: %s\nARR[QUERY]: %s\nARR[INSERT]: %s\nARR[UPDATE]: %s\nARR[DEBUG]: %s" "${ARR[DB]}" "${ARR[TABLE]}" "${ARR[COLS]}" "${ARR[QUERY]}" "${ARR[INSERT]}" "${ARR[UPDATE]}" "${ARR[DEBUG]}"
	printf "\nCOLS: %s" "${COLS[*]}"
	printf "\nCOL_QUERY: %s" "$COL_QUERY"
	printf "\nRESULTS FROM QUERY: %d" "${#_RESULT_IDS[*]}"
	printf "\nDB_COL_NAMES_ARR: %s" "${DB_COL_NAMES_ARR[*]}"
	printf "\nrequest type: %s" "$request"
	printf "\n-------------------------\n\n"
	fi

	# (9)
	# the business
	# declared prior to being invoked by the case
	insertDB() {
	# ------------debug--------------
	if [[ ${ARR[DEBUG]} -eq 1 ]]; then
	printf "\n\n--------- fn: INSERT [%s]----------\n\n" "${ARR[INSERT]^^}"
	fi

	local -A INSERT_COL_VALS
	# if the insert subcommand contained a query argument
	# assign it to the default search column/index
	# then query the db for any existing entries that match the query string
	if [[ -n ${ARR[INSERT]} ]] && [[ ${#_RESULT_IDS[*]} -eq 0 ]]; then
	INSERT_COL_VALS["$SQLITE_INDEX"]="'${ARR[INSERT]^^}'"
	elif [[ -n ${ARR[INSERT]} ]] && [[ ${#_RESULT_IDS[*]} -gt 0 ]]; then
	display_index_options
	if [[ $request = "update" ]]; then return; fi
	fi

	# solicit and assign the rest of the entry values to its associated column name
	for col in "${DB_COL_NAMES_ARR[@]}";
	do
	if [[ $col != "id" ]]; then
	if [[ $col = "$SQLITE_INDEX" ]] && [[ -n ${INSERT_COL_VALS["$SQLITE_INDEX"]} ]]; then
	continue
	elif [[ $col = "$SQLITE_INDEX" ]] && [[ -z ${INSERT_COL_VALS["$SQLITE_INDEX"]} ]]; then
	while [[ -z ${INSERT_COL_VALS["$SQLITE_INDEX"]} ]]; do
	read_valid_index_input
	if [[ ${#_RESULT_IDS[*]} -eq 0 ]]; then
	INSERT_COL_VALS["$SQLITE_INDEX"]="'${ARR[INSERT]^^}'"
	elif [[ ${#_RESULT_IDS[*]} -gt 0 ]]; then
	display_index_options
	fi
	done
	else
	echo "Enter a value for the $col column or leave blank to skip..."
	read -r input
	if [[ -n "$input" ]]; then
	INSERT_COL_VALS[$col]="\"$input\""
	fi
	fi
	fi
	done

	# array syntax reference:
	# array keys - echo "${!INSERT_COL_VALS[@]}"
	# array values = echo "${INSERT_COL_VALS[@]}"
	# array length - echo "${#INSERT_COL_VALS[@]}"
	local input_keys=
	local input_values
	input_keys=$( ( IFS=,; echo "${!INSERT_COL_VALS[*]}" ) )
	input_values=$( (IFS=,; echo "${INSERT_COL_VALS[*]}" ) )

	if [[ ${#INSERT_COL_VALS[@]} -gt 0 ]]; then
	printf "key: %s\tval: %s\n" "$input_keys" "$input_values"
	if (sqlite "${ARR[DB]}" "INSERT INTO ${ARR[TABLE]} ($input_keys) VALUES($input_values)"); then
	printf "\nSuccessfully added %s to %s\n" "$input_values" "${ARR[TABLE]}"
	else
	printf "\nSomething went wrong with the query.\nexiting with status %d\n\n" "$?"
	fi
	else
	echo "Missing values in function call" >&2; return 1
	fi
	}
	queryDB() {
	# ------------debug--------------
	if [[ ${ARR[DEBUG]} -eq 1 ]]; then
	printf "\n\n--------- fn: QUERY [%s]----------\n\n" "${ARR[QUERY]}"
	fi

	local entry=
	local name=
	if [[ $request = "query" ]]; then
	if [[ -z ${ARR[QUERY]} ]]; then
	input=
	while [[ -z $input ]] \|\| [[ ${#input} -lt 3 ]]; do
	echo "You need to provide a query string (minimum of 3 characters):"
	read -r input
	done
	ARR[QUERY]="$input"
	create_query_string && query
	fi
	for entry in "${_RESULT_IDS[@]}"; do
	name=$(sqlite -column "${ARR[DB]}" "SELECT * FROM ${ARR[TABLE]} WHERE id=$entry")
	exitStatus $?
	echo "$name"
	done
	fi

	return
	}
	updateDB() {
	# ------------debug--------------
	if [[ ${ARR[DEBUG]} -eq 1 ]]; then
	printf "\n\n--------- fn: UPDATE [%s]----------\n\n" "${ARR[UPDATE]}"
	fi

	local update_entry_arr=

	if [[ ${#_RESULT_IDS[*]} -gt 0 ]]; then
	display_index_options
	fi

	if [[ -n ${ARR[UPDATE]} ]]; then
	echo "${ARR[UPDATE]}"
	if [[ ${ARR[UPDATE]} < "a" ]]; then
	echo "${ARR[UPDATE]}"
	readarray -t -d '\|' update_entry_arr < <(sqlite "${ARR[DB]}" "SELECT * FROM ${ARR[TABLE]} WHERE id=${ARR[UPDATE]}")
	exitStatus $?
	elif [[ ${ARR[UPDATE]} > "a" ]]; then
	echo "${ARR[UPDATE]}"
	fi
	elif [[ -z ${ARR[UPDATE]} ]]; then
	echo "You need to provide a query..."
	read -r input
	ARR[QUERY]="$input"
	create_query_string && query
	fi

	if [[ ${#update_entry_arr[]} -eq 0 ]] && [[ ${#_RESULT_IDS[]} -eq 0 ]]; then
	printf "\nCan't find an entry for %s\n" "${ARR[QUERY]}"
	printf "Would you like to create a new entry for %s\n" "${ARR[UPDATE]}"
	printf "[y]\n[n]\n\n"
	read -r input
	if [[ $input = "y" ]]; then
	ARR[INSERT]="${ARR[UPDATE]}"
	ARR[UPDATE]=
	request="insert"
	insertDB
	if [[ $request = "insert" ]]; then return; fi
	fi
	fi

	# ------------debug--------------
	if [[ ${ARR[DEBUG]} -eq 1 ]]; then
	printf "\nUpdate entry array: \n%s" "${update_entry_arr[*]}"
	fi

	local UPDATE_QUERY="SET "
	local UPDATING=true
	while $UPDATING; do
	echo "Select a column to update..."
	printf "\n[#] COLUMN:\tVALUE"
	printf "\n--- ------\t------\n"
	local idx=
	for idx in "${!DB_COL_NAMES_ARR[@]}"; do
	if [[ ${DB_COL_NAMES_ARR[$idx]} != "id" ]]; then
	printf "[%d] %s:\t%s\n" "$((idx+1))" "${DB_COL_NAMES_ARR[$idx]}" "${update_entry_arr[$idx]}"
	fi
	done
	printf "\n[Enter] to submit changes.\n\n"

	read -r UPDATE_COL_OPTION
	case $UPDATE_COL_OPTION in
	[1-$((${#DB_COL_NAMES_ARR[@]}-1))] ) printf "\n%s: " "${DB_COL_NAMES_ARR[$((UPDATE_COL_OPTION-1))]}"
	local UPDATE_COL_VALUE=
	read -r UPDATE_COL_VALUE
	if [[ ${DB_COL_NAMES_ARR[$((UPDATE_COL_OPTION-1))]} = "$SQLITE_INDEX" ]]; then
	echo "we should check for existing entries here..."
	ARR[UPDATE]="$UPDATE_COL_VALUE"
	ARR[QUERY]="$UPDATE_COL_VALUE"
	create_query_string && query
	if [[ ${#_RESULT_IDS[*]} -gt 0 ]]; then
	display_index_options
	if [[ $request = "insert" ]]; then
	insertDB
	UPDATING=false
	fi
	else
	UPDATE_COL_VALUE="${UPDATE_COL_VALUE^^}"
	fi
	fi
	UPDATE_QUERY+="${DB_COL_NAMES_ARR[$((UPDATE_COL_OPTION-1))]}='$UPDATE_COL_VALUE',"
	update_entry_arr[$((UPDATE_COL_OPTION-1))]="$UPDATE_COL_VALUE"
	;;
	"" )
	echo "${UPDATE_QUERY%,}"
	local len=$((${#update_entry_arr[@]}-1))
	local update_db=
	update_db=$(sqlite "${ARR[DB]}" "UPDATE ${ARR[TABLE]} ${UPDATE_QUERY%,} WHERE id=${ARR[UPDATE]}")
	exitStatus $?
	echo "$update_db"
	printf "Updated entry id \e[38;5;220m%s : %s$COLOR_RESET\n" "${update_entry_arr[$len]/$'\n'/}" "${update_entry_arr[$SQLITE_INDEX]}"
	UPDATING=false
	;;
	* )
	echo "Invalid option..."
	;;
	esac
	done
	}

	if [[ -n $COL_QUERY ]]; then query; fi

	# (8)
	# query action router
	case $request in
	query) queryDB;;
	insert) insertDB;;
	update) updateDB;;
	esac

	return
	}