Dave Jarvis' Repositories

SET search_path = recipe, pg_catalog;

DROP FUNCTION is_existing_book(p_book_id text);

ALTER TABLE ingredient_name
	ALTER COLUMN label TYPE character varying(50) /* TYPE change - table: ingredient_name original: character varying(32) new: character varying(50) */;

CREATE OR REPLACE FUNCTION generate_search_recipe_xml(p_min real, p_max real, p_unit text, p_ingredient_name text) RETURNS xml
    LANGUAGE plpgsql STABLE COST 10000
    AS $_$
DECLARE
  v_result XML DEFAULT '<search/>';
  v_ingredient_name_id BIGINT;
  v_ingredient_unit_id BIGINT;
  v_sql TEXT DEFAULT '';
  v_where TEXT DEFAULT '';
BEGIN
  /**
   * Searches recipes given some ingredients.
   */

  -- Get the name of the ingredient that most closely matches the given name.
  v_ingredient_name_id = get_ingredient_name_id( p_ingredient_name );
  v_ingredient_unit_id = get_ingredient_unit_id( p_unit );

  IF p_min > 0 THEN
    v_where := v_where || ' AND ri.min_quantity = ' || p_min;
  END IF;

  IF p_max > p_min THEN
    v_where := v_where || ' AND ri.max_quantity = ' || p_max;
  END IF;

  IF v_ingredient_unit_id > 0 THEN
    v_where := v_where || ' AND ri.unit_id = ' || v_ingredient_unit_id;
  END IF;

  -- Get all the recipes that contain the given ingredient.
  v_sql := '
SELECT
    xmlconcat(
      (''<search>'' ||
      xmlagg(
        xmlelement( name "recipe",
          xmlattributes( rd.recipe_id AS id ),
          xmlelement( name "title", rd.title )
        ) ORDER BY rd.title
      ) ||
      ''</search>'')::xml
    )
FROM (
  SELECT
    rd.recipe_id, rd.title
  FROM
    recipe.ingredient ri,
    recipe.description rd,
    recipe.ingredient_group rig,
    recipe.direction_group rdg
  WHERE
    ri.ingredient_name_id = $1 AND
    rig.id = ri.ingredient_group_id AND
    rig.recipe_id = rd.recipe_id AND
    rdg.recipe_id = rd.recipe_id ' || v_where || '
  GROUP BY
    rd.recipe_id, rd.title
) rd';

  EXECUTE (v_sql)::xml INTO v_result USING v_ingredient_name_id;

  IF v_result IS NULL THEN
    v_result := '<search/>';
  END IF;

  RETURN v_result;

EXCEPTION
  WHEN others THEN
    PERFORM recipe.error_log_insert( SQLSTATE, SQLERRM, current_query() );
    RETURN v_result;
END;
$_$;

CREATE OR REPLACE FUNCTION generate_search_recipe_xml(p_comparator text, p_min real, p_max real, p_unit text, p_ingredient_name text) RETURNS xml
    LANGUAGE plpgsql STABLE COST 10000
    AS $_$
DECLARE
  v_result XML DEFAULT '<search/>';
  v_ingredient_name_id BIGINT;
  v_ingredient_unit_id BIGINT;
  v_sql TEXT DEFAULT '';
  v_where TEXT DEFAULT '';
  v_comparator TEXT DEFAULT '=';
BEGIN
  /**
   * Searches recipes given some ingredients.
   */

  -- Get the name of the ingredient that most closely matches the given name.
  v_ingredient_name_id = get_ingredient_name_id( p_ingredient_name );
  v_ingredient_unit_id = get_ingredient_unit_id( p_unit );

  -- Determine how to compare against quantities; requires unit conversions.
  IF p_comparator = 'gte' THEN
    v_comparator := '>';
  ELSIF p_comparator = 'lte' THEN
    v_comparator := '<';
  END IF;

  IF p_min > 0 THEN
    v_where := v_where || ' AND ri.min_quantity' || v_comparator || p_min;
  END IF;

  IF p_max > p_min THEN
    v_where := v_where || ' AND ri.max_quantity = ' || p_max;
  END IF;

  IF v_ingredient_unit_id > 0 THEN
    v_where := v_where || ' AND ri.unit_id = ' || v_ingredient_unit_id;
  END IF;

  -- Get all the recipes that contain the given ingredient.
  v_sql := '
SELECT
    xmlconcat(
      (''<search>'' ||
      xmlagg(
        xmlelement( name "recipe",
          xmlattributes( rd.recipe_id AS id ),
          xmlelement( name "title", rd.title )
        ) ORDER BY rd.title
      ) ||
      ''</search>'')::xml
    )
FROM (
  SELECT
    rd.recipe_id, rd.title
  FROM
    recipe.ingredient ri,
    recipe.description rd,
    recipe.ingredient_group rig,
    recipe.direction_group rdg
  WHERE
    ri.ingredient_name_id = $1 AND
    rig.id = ri.ingredient_group_id AND
    rig.recipe_id = rd.recipe_id AND
    rdg.recipe_id = rd.recipe_id ' || v_where || '
  GROUP BY
    rd.recipe_id, rd.title
) rd';

  EXECUTE (v_sql)::xml INTO v_result USING v_ingredient_name_id;

  IF v_result IS NULL THEN
    v_result := '<search/>';
  END IF;

  RETURN v_result;

EXCEPTION
  WHEN others THEN
    PERFORM recipe.error_log_insert( SQLSTATE, SQLERRM, current_query() );
    RETURN v_result;
END;
$_$;

CREATE OR REPLACE FUNCTION get_incomplete_recipe(p_account_id bigint) RETURNS bigint
    LANGUAGE plpgsql STABLE COST 2
    AS $$
DECLARE
  v_result BIGINT DEFAULT -1;
BEGIN
  /**
   * Find the first recipe without ingredient or instruction groups.
   * This logic (not necessarily function) is used to:
   * 1. prevent users from creating many different recipes; and
   * 2. determine whether the recipe is complete.
   */
  SELECT
    rar.recipe_id
  INTO
    v_result
  FROM
    recipe.account_recipe rar,
    recipe.ingredient_group rig,
    recipe.direction_group rdg
  WHERE
    rar.account_id = p_account_id AND
    rar.recipe_id = rig.recipe_id AND
    rar.recipe_id = rdg.recipe_id AND (
      rig.id NOT IN (
        SELECT ingredient_group_id FROM recipe.ingredient
      ) OR
      rdg.id NOT IN (
        SELECT direction_group_id FROM recipe.direction
      )
    )
  ORDER BY
    recipe_id
  LIMIT 1;

  IF NOT found THEN
    v_result := -1;
  END IF;

  RETURN v_result;

EXCEPTION
  WHEN OTHERS THEN
    PERFORM recipe.error_log_insert( SQLSTATE, SQLERRM, current_query() );
    RETURN v_result;
END;
$$;

CREATE OR REPLACE FUNCTION get_ingredient_unit_id(p_label text) RETURNS bigint
    LANGUAGE plpgsql IMMUTABLE COST 1
    AS $$
DECLARE
  v_ingredient_unit_id BIGINT DEFAULT 0;
BEGIN
  p_label := recipe.sanitize( p_label, 15 );

  /**
   * Returns the known unit name that most closely matches the given
   * unit ordered by similarity.
   */
  SELECT
    id
  INTO
    v_ingredient_unit_id
  FROM
    recipe.ingredient_unit riu
  WHERE
    unaccent( riu.label ) = unaccent( p_label ) OR
    unaccent( riu.abridge ) = unaccent( p_label );

  IF NOT found THEN
    v_ingredient_unit_id := 0;
  END IF;

  RETURN v_ingredient_unit_id;

EXCEPTION
  WHEN OTHERS THEN
    PERFORM recipe.error_log_insert( SQLSTATE, SQLERRM, current_query() );
    RETURN v_ingredient_unit_id;
END;
$$;

CREATE OR REPLACE FUNCTION get_recipe_equipment_list(bigint, text) RETURNS SETOF text_label
    LANGUAGE sql IMMUTABLE COST 1 ROWS 10
    AS $_$
SELECT
  re.id::bigint,
  ren.label::text
FROM
  recipe.equipment re,
  recipe.equipment_name ren,
  recipe.equipment_group reg
WHERE
  re.recipe_id = $1 AND -- p_recipe_id
  re.equipment_name_id = ren.id AND
  ren.equipment_group_id = reg.id AND
  reg.label = $2; -- p_equipment_group_label
$_$;

CREATE OR REPLACE FUNCTION is_existing_book(p_book_id bigint) RETURNS integer
    LANGUAGE plpgsql STABLE COST 1
    AS $$
DECLARE
  v_result INTEGER;
BEGIN
  /**
   * Does the book exist in the database?
   * Returns 1 for yes and 0 for no.
   */
  SELECT
    rbb.id
  INTO
    v_result
  FROM
    recipe_book.book rbb
  WHERE
    rbb.id = p_book_id;

  IF found THEN
    v_result := 1;
  ELSE
    v_result := 0;
  END IF;

  RETURN v_result;

EXCEPTION
  WHEN OTHERS THEN
    PERFORM recipe.error_log_insert( SQLSTATE, SQLERRM, current_query() );
END;
$$;

CREATE OR REPLACE FUNCTION unaccent_text(text) RETURNS text
    LANGUAGE sql IMMUTABLE COST 1
    AS $_$
  -- unaccent is STABLE, but the indexes must use IMMUTABLE functions.
  -- To avoid a failure with db_dump, use the IMMUTABLE musicbrainz
  -- alternative function.
  SELECT recipe.musicbrainz_unaccent($1);
$_$;