query.sh

#!/usr/bin/env bash


# TO DO: come up with good queries (consider runtime/exponential paths)

set -e  # Exit on error
BASE_DIR="$(pwd)"
OUT_DIR="$BASE_DIR/$TAG/query_results"
TAG="$1"
QUERY_FILE="$BASE_DIR/query_list.csv"

# Prompt automatically and securely
if sudo -v; then
  echo "Sudo access granted. Continuing script..."
else
  echo "Sudo access denied."
  exit 1
fi

#Refresh Sudo access every 5 mins
( while true; do sudo -n true; sleep 300; done ) &
SUDO_REFRESH_PID=$!

sudo /usr/bin/neo4j-admin server stop

for project_csv_path in $BASE_DIR/project_csvs/$TAG/*/; do

  PROJECT_NAME=$(basename "$project_csv_path")
  import_dir="$OUT_DIR/$TAG/import_logs/${PROJECT_NAME}_import.log"
  if [[ -f  "$import_dir" ]]; then
	echo "[SKIPPED] $PROJECT_NAME already processed, skipping."
	continue;
  fi
  echo "[INFO] Processing directory: $PROJECT_NAME"
  cd $project_csv_path

  echo "[INFO] Importing $PROJECT_NAME files into Neo4j database"
  mkdir -p "$(dirname "$import_dir")" && touch "$import_dir"
  sudo /usr/bin/neo4j-admin database import full \
    --nodes=${PROJECT_NAME}_sbom_nodes_${TAG}.csv \
    --nodes=${PROJECT_NAME}_sca_version_nodes_${TAG}.csv \
    --nodes=${PROJECT_NAME}_sca_package_nodes_${TAG}.csv \
    --nodes=${PROJECT_NAME}_sca_vulnerability_nodes_${TAG}.csv \
    --relationships=${PROJECT_NAME}_sbom_relations_${TAG}.csv \
    --relationships=${PROJECT_NAME}_sca_relations_${TAG}.csv \
    --multiline-fields=true \
    --overwrite-destination \
    > $import_dir 2>&1

  echo "[INFO] Successful $PROJECT_NAME import. Starting Neo4j server..." 
  sudo /usr/bin/neo4j-admin server start

  sleep 1

  echo "[INFO] $PROJECT_NAME server started, running queries..." 
  cypher-shell -u neo4j -p 'neo4jpassword' --format plain --non-interactive <<< "CREATE CONSTRAINT UniquePackageId FOR (p:package) REQUIRE p.ID IS UNIQUE;
  CREATE CONSTRAINT UniqueRootId FOR (r:root) REQUIRE r.ID IS UNIQUE;
  CREATE CONSTRAINT UniqueVulnerabilityId FOR (v:vulnerability) REQUIRE v.ID IS UNIQUE;
  CREATE CONSTRAINT UniquePackageVersionId FOR (pv:package_version) REQUIRE pv.ID IS UNIQUE;"
  
  echo "[INFO] Running queries from CSV..."
  while IFS=',' read -r query_name query_text; do
    echo "[INFO] Running query: $query_name on $PROJECT_NAME"

    [[ -z "$query_name" || -z "$query_text" ]] && continue  # Skip empty lines
    query_text=$(echo "$query_text" | tr -d '\r' | sed 's/^"//' | sed 's/"$//' | sed 's/""/"/g')

    # Prepare file paths
    result_file="$OUT_DIR/$TAG/results/${query_name}.csv"
    runtime_file="$OUT_DIR/$TAG/runtimes/${query_name}_runtime.csv"
    
    # If file doesn't exist, write the query as the header
    if [ ! -f "$result_file" ]; then
      mkdir -p "$(dirname "$result_file")" && touch "$result_file"
      echo "$query_text" >> "$result_file"
    fi

    if [ ! -f "$runtime_file" ]; then
      mkdir -p "$(dirname "$runtime_file")" && touch "$runtime_file"
      echo "$query_text" >> "$runtime_file"
    fi

    # Start time (in milliseconds)
    start_time=$(date +%s%3N)

    # Run the query
    raw_output=$(cypher-shell -u neo4j -p 'neo4jpassword' --format plain --non-interactive <<< "$query_text")

    # End time
    end_time=$(date +%s%3N)
    runtime=$((end_time - start_time))

    # Format results
    results=$(echo "$raw_output" | tail -n +2)
    result_line=$(echo "$results" | paste -sd, -)

    # Append results and runtime
    echo "$PROJECT_NAME,$result_line" >> "$result_file"
    echo "$PROJECT_NAME,$runtime" >> "$runtime_file"

  done < "$QUERY_FILE"


  echo "[INFO] $PROJECT_NAME Results saved. Stopping $PROJECT_NAME Neo4j server..." 
  sudo /usr/bin/neo4j-admin server stop

  cd ..
  echo "[INFO] Finished processing directory: $PROJECT_NAME" 

done

echo "All directories processed."
trap "kill $SUDO_REFRESH_PID" EXIT