Compalex

Database schema compare tool

tables views procedures functions indexes triggers  all changed

compalex_dev

localhost/compalex_dev

compalex_prod

localhost/compalex_prod

get_customer_balance 21

  • BEGIN
  • DECLARE v_rentfees DECIMAL(5,2);
  • DECLARE v_overfees INTEGER;
  • DECLARE v_payments DECIMAL(5,2);
  • SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
  • FROM film, inventory, rental
  • WHERE film.film_id = inventory.film_id
  • AND inventory.inventory_id = rental.inventory_id
  • AND rental.rental_date <= p_effective_date
  • AND rental.customer_id = p_customer_id;
  • SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
  • ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
  • FROM rental, inventory, film
  • SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
  • FROM payment
  • WHERE payment.payment_date <= p_effective_date
  • AND payment.customer_id = p_customer_id;
  • RETURN v_rentfees + v_overfees - v_payments;
  • END

get_customer_balance 21

  • BEGIN
  • DECLARE v_rentfees DECIMAL(5,2);
  • DECLARE v_overfees INTEGER;
  • DECLARE v_payments DECIMAL(5,2);
  • SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
  • FROM film, inventory, rental
  • WHERE film.film_id = inventory.film_id
  • AND inventory.inventory_id = rental.inventory_id
  • AND rental.rental_date <= p_effective_date
  • AND rental.customer_id = p_customer_id;
  • SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
  • ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
  • FROM rental, inventory, film
  • SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
  • FROM payment
  • WHERE payment.payment_date <= p_effective_date
  • AND payment.customer_id = p_customer_id;
  • RETURN v_rentfees + v_overfees - v_payments;
  • END

inventory_held_by_customer 10

  • BEGIN
  • DECLARE v_customer_id INT;
  • DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
  • SELECT customer_id INTO v_customer_id
  • FROM rental
  • WHERE return_date IS NULL
  • AND inventory_id = p_inventory_id;
  • RETURN v_customer_id;
  • END

inventory_held_by_customer 10

  • BEGIN
  • DECLARE v_customer_id INT;
  • DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
  • SELECT customer_id INTO v_customer_id
  • FROM rental
  • WHERE return_date IS NULL
  • AND inventory_id = p_inventory_id;
  • RETURN v_customer_id;
  • END

inventory_in_stock 19

  • BEGIN
  • DECLARE v_rentals INT;
  • DECLARE v_out INT;
  • SELECT COUNT(*) INTO v_rentals
  • FROM rental
  • WHERE inventory_id = p_inventory_id;
  • IF v_rentals = 0 THEN
  • RETURN TRUE;
  • END IF;
  • SELECT COUNT(rental_id) INTO v_out
  • FROM inventory LEFT JOIN rental USING(inventory_id)
  • WHERE inventory.inventory_id = p_inventory_id
  • AND rental.return_date IS NULL;
  • IF v_out > 0 THEN
  • RETURN FALSE;
  • ELSE
  • END

inventory_in_stock 19

  • BEGIN
  • DECLARE v_rentals INT;
  • DECLARE v_out INT;
  • SELECT COUNT(*) INTO v_rentals
  • FROM rental
  • WHERE inventory_id = p_inventory_id;
  • IF v_rentals = 0 THEN
  • RETURN TRUE;
  • END IF;
  • SELECT COUNT(rental_id) INTO v_out
  • FROM inventory LEFT JOIN rental USING(inventory_id)
  • WHERE inventory.inventory_id = p_inventory_id
  • AND rental.return_date IS NULL;
  • IF v_out > 0 THEN
  • RETURN FALSE;
  • ELSE
  • END