base_SQL="SELECT title,author,journal,year,token,doi,arxiv,citeref,prauth,bibtex,url FROM bibliography"
base_SQL_noprauth="SELECT title,author,journal,year,token,doi,arxiv,citeref,bibtex,url FROM bibliography"

# add quotes
function SQL_addquotes {
  while read -r line; do
    # add quotes and escape string
    if [ -n "$line" ]; then
      line=$(escape_string "$line")
      line="${line/%=/= }"
      line="${line/ = /=\"}"
      line="${line/%/\"}"
    fi
    echo "$line"
  done
}

# SQL command for alphabetical ordering
function generate_SQL_alpha {
  echo -n "$base_SQL WHERE citeref='"

  # whether there is a citeref
  exists_citeref=0

  # sift through aux file
  grep -h "$aux_cmd" $aux | while read -r citerefs; do
    eval "citerefs=\${citerefs#$aux_cmd}"
    citerefs="${citerefs%\}}"

    # can be a comma separated list
    for citeref in $(echo -n "$citerefs" | tr ',' '\n'); do

      # replace the citeref with a ref_map if there is any
      if [ -n "$ref_map" ]; then
	citeref=$(map_citeref "$citeref")
      fi

      # Only query the db if there is no matching extra entry
      foundit=0
      if [ ${#extra} -gt 0 ]; then
	for entry in "${extra[@]}"; do
	  ref="${entry#*:}"
	  ref="${ref%%:*}"
	  if [ "$ref" = "$citeref" ]; then
	    foundit=1
	    break
	  fi
	done
      fi
      
      if [ "$foundit" = 0 ]; then
	echo -n "$citeref' OR citeref='"
	exists_citeref=1
      fi
    done
  done

  # if there are no citerefs close '
  [ $exists_citeref = 0 ] && echo -n "'"
}

# fetch entries by alphabetical order
function fetch_BBlog_entries_alpha {
  SQL="$(generate_SQL_alpha)"
  SQL="${SQL% OR citeref=\'} ORDER BY prauth,year"
  sqlite3 -line "$db" "$SQL" | SQL_addquotes
  # add an empty line
  echo ""
}

# fetch entries by order of appearance
function fetch_BBlog_entries_appearance {
  # keep track of citerefs to avoid repetitions
  citeref_list=""

  # sift through aux file
  grep -h "$aux_cmd" $aux | while read -r citerefs; do
    eval "citerefs=\${citerefs#$aux_cmd}"
    citerefs="${citerefs%\}}"

    # can be a comma separated list
    for citeref in $(echo -n "$citerefs" | tr ',' '\n'); do

      # replace the citeref with a ref_map if there is any
      if [ -n "$ref_map" ]; then
	citeref=$(map_citeref "$citeref")
      fi

      # check whether the reference was already cited
      citeref_list="$citeref_list;"
      if $(echo "$citeref_list" | grep -q ";$citeref;"); then
	# remove trailing ';'
	citeref_list="${citeref_list%;}"
      else
	citeref_list="$citeref_list$citeref"

	# Only query the db if there is no matching extra entry
	foundit=0
	if [ ${#extra} -gt 0 ]; then
	  for entry in "${extra[@]}"; do
	    ref="${entry#*:}"
	    ref="${ref%%:*}"
	    if [ "$ref" = "$citeref" ]; then
	      foundit=1
	      break
	    fi
	  done
	fi
	
	if [ "$foundit" = 0 ]; then
	  SQL="$base_SQL_noprauth WHERE citeref='$citeref'"
	  sqlite3 -line "$db" "$SQL" | SQL_addquotes
	else
	  echo "citeref=$citeref"
	fi

	# add an empty line
	echo ""
      fi
    done
  done
}