A little shell script for hammering the dvdstore database on Postgres

make-load.sh

#!/usr/bin/env bash

if [ -z "$1" ]; then
   echo "Usage: $0 <iterations>"
   exit 1
fi

# Display product ID, category, title, price, num orders
# ordered by top products limit 10
function top_products(){
	cat <<-EOF
	select p.prod_id, p.category, p.title, p.price, sum(quantity) as qty
	from products as p
		inner join orderlines as o on(p.prod_id=o.prod_id)
	group by p.prod_id
	order by sum(quantity) desc
	limit 10 offset 0
	EOF
}

function top_actors(){
	cat <<-EOF
	select actor from products group by actor order by count(*) desc limit 10 offset 0
	EOF
}

function recent_orders(){
	cat <<-EOF
	select d.orderid, d.orderdate,
		(select c.firstname || ' ' || c.lastname
		from customers as c where c.customerid=d.customerid) as name,
		count(*) as items, sum(quantity) as qty
	from orders as d
		inner join orderlines as o on(o.orderid=d.orderid)
	group by d.orderid
	order by d.orderdate desc limit 10 offset 0
	EOF
}

function order_history(){
	cat <<-EOF
	select c.customerid, c.firstname || ' ' || c.lastname as name,
		d.orderid, d.orderdate
	from customers as c
		inner join cust_hist as h on h.customerid=c.customerid
		inner join orders as d on d.orderid=h.orderid
	where h.customerid = cast(random()*10000 as integer)
	order by d.orderdate desc 
	EOF
}

i=0
while [ "${i:=0}" -lt "$1" ]; do
	order_history | psql -h 127.0.0.1 >/dev/null &
	recent_orders | psql -h 127.0.0.1 >/dev/null &
	top_actors  | psql -h 127.0.0.1 >/dev/null &
	top_products | psql -h 127.0.0.1 >/dev/null &
	wait
	i=$(($i+1))
	echo $i
done