Compalex

Database schema compare tool

tables views procedures functions indexes triggers  all changed

compalex_dev

localhost/compalex_dev

compalex_prod

localhost/compalex_prod

film_in_stock 9

  • BEGIN
  • SELECT inventory_id
  • FROM inventory
  • WHERE film_id = p_film_id
  • AND store_id = p_store_id
  • AND inventory_in_stock(inventory_id);
  • SELECT FOUND_ROWS() INTO p_film_count;
  • END

film_in_stock 10

  • BEGIN
  • SELECT inventory_id
  • FROM inventory
  • WHERE film_id = p_film_id
  • AND store_id = p_store_id
  • AND store_id = 2
  • AND inventory_in_stock(inventory_id);
  • SELECT FOUND_ROWS() INTO p_film_count;
  • END

film_not_in_stock 9

  • BEGIN
  • SELECT inventory_id
  • FROM inventory
  • WHERE film_id = p_film_id
  • AND store_id = p_store_id
  • AND NOT inventory_in_stock(inventory_id);
  • SELECT FOUND_ROWS() INTO p_film_count;
  • END

film_not_in_stock 9

  • BEGIN
  • SELECT inventory_id
  • FROM inventory
  • WHERE film_id = p_film_id
  • AND store_id = p_store_id
  • AND NOT inventory_in_stock(inventory_id);
  • SELECT FOUND_ROWS() INTO p_film_count;
  • END

rewards_report 28

  • proc: BEGIN
  • DECLARE last_month_start DATE;
  • DECLARE last_month_end DATE;
  • IF min_monthly_purchases = 0 THEN
  • SELECT 'Minimum monthly purchases parameter must be > 0';
  • LEAVE proc;
  • END IF;
  • IF min_dollar_amount_purchased = 0.00 THEN
  • SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
  • SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
  • SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
  • SET last_month_end = LAST_DAY(last_month_start);
  • CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
  • INSERT INTO tmpCustomer (customer_id)
  • SELECT p.customer_id
  • FROM payment AS p
  • WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
  • GROUP BY customer_id
  • HAVING SUM(p.amount) > min_dollar_amount_purchased
  • AND COUNT(customer_id) > min_monthly_purchases;
  • SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
  • SELECT c.*
  • FROM tmpCustomer AS t
  • INNER JOIN customer AS c ON t.customer_id = c.customer_id;
  • DROP TABLE tmpCustomer;
  • END

rewards_report 29

  • proc: BEGIN
  • DECLARE last_month_start DATE;
  • DECLARE last_month_end DATE;
  • IF min_monthly_purchases = 0 THEN
  • SELECT 'Minimum monthly purchases parameter must be > 0';
  • LEAVE proc;
  • END IF;
  • IF min_dollar_amount_purchased = 0.00 THEN
  • SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
  • SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
  • SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
  • SET last_month_end = LAST_DAY(last_month_start);
  • CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
  • INSERT INTO tmpCustomer (customer_id)
  • SELECT p.customer_id
  • FROM payment AS p
  • WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
  • GROUP BY customer_id
  • HAVING SUM(p.amount) > min_dollar_amount_purchased
  • AND COUNT(customer_id) > min_monthly_purchases;
  • SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
  • SELECT c.*
  • FROM tmpCustomer AS t
  • INNER JOIN customer AS c ON t.customer_id = c.customer_id;
  • DROP TABLE tmpCustomer;
  • END