Dave Jarvis' Repositories

SET search_path = recipe, pg_catalog;

ALTER TABLE direction
	ALTER COLUMN min_time SET DEFAULT 0;

CREATE OR REPLACE FUNCTION count_ingredient_group_ingredients(p_ingredient_id bigint) RETURNS bigint
    LANGUAGE plpgsql STABLE COST 1
    AS $$
DECLARE
  v_result INTEGER DEFAULT 0;
BEGIN
  /**
   * Returns the number of ingredients in the group that the
   * given ingredient is associated.
   */
  SELECT
    count(ri2.ingredient_group_id)
  INTO
    v_result
  FROM
    recipe.ingredient ri,
    recipe.ingredient ri2
  WHERE
    ri.id = p_ingredient_id AND
    ri.ingredient_group_id = ri2.ingredient_group_id
  GROUP BY
    ri2.ingredient_group_id;

  RETURN v_result;

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

CREATE OR REPLACE FUNCTION ingredient_upsert(p_ingredient_group_id bigint, p_name text, p_unit_id integer, p_min real, p_max real, p_alias text, p_condition text, p_required boolean) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
  v_seq INTEGER;
  v_recipe_id BIGINT;
  v_ingredient_name_id BIGINT;
BEGIN
  p_name := recipe.sanitize( p_name, 30 );
  p_alias := recipe.sanitize( p_alias, 15 );
  p_condition := recipe.sanitize( p_condition, 30 );

  IF p_unit_id < 1 THEN
    p_unit_id := NULL;
  END IF;

  -- Assume all ingredients have 1 unit.
  IF p_min = NULL OR p_min <= 0 THEN
    p_min := 1;
  END IF;

  IF p_max <= p_min THEN
    p_max := NULL;
  END IF;

  v_ingredient_name_id := recipe.get_ingredient_name_id( p_name );

  IF v_ingredient_name_id > 0 THEN
    SELECT
      rig.recipe_id
    INTO
      v_recipe_id
    FROM
      recipe.ingredient_group rig
    WHERE
      rig.id = p_ingredient_group_id;

    -- Find the highest ingredient sequence number for this recipe,
    -- or retrieve the value of 1.
    SELECT
      COALESCE( max(ri.seq) + 1, 1 )
    INTO
      v_seq
    FROM
      recipe.ingredient ri,
      recipe.ingredient_group rig
    WHERE
      rig.id = ri.ingredient_group_id AND
      rig.recipe_id = v_recipe_id;

    INSERT INTO
      recipe.ingredient
      (min_quantity, max_quantity, condition, unit_id, ingredient_name_id, required, ingredient_group_id, seq)
    VALUES
      (p_min, p_max, p_condition, p_unit_id, v_ingredient_name_id, p_required, p_ingredient_group_id, v_seq);
  END IF;

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

CREATE OR REPLACE FUNCTION instruction_insert(p_recipe_id bigint, p_instruction text, p_action_name_id integer) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
  v_seq INTEGER;
  v_instruction_group_id BIGINT;
BEGIN
  SELECT
    rdg.id
  INTO
    v_instruction_group_id
  FROM
    recipe.direction_group rdg
  WHERE
    rdg.recipe_id = p_recipe_id
  ORDER BY
    seq DESC
  LIMIT 1;

  IF p_action_name_id IS NULL THEN
    p_action_name_id := 1;
  END IF;

  -- min_time = 0
  PERFORM recipe.instruction_upsert(
    p_recipe_id, -1, v_instruction_group_id, p_instruction, p_action_name_id, 0, null );

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

CREATE OR REPLACE FUNCTION instruction_upsert(p_recipe_id bigint, p_instruction_id bigint, p_instruction_group_id bigint, p_instruction text, p_action_name_id integer, p_min_time integer, p_max_time integer) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
  v_seq INTEGER;
BEGIN
  -- Instructions can be blank because you can "heat" for five minutes.
  p_instruction = recipe.sanitize( p_instruction, 100 );

  -- Assume instructions are instant (e.g., serve takes 0 minutes).
  IF p_min_time IS NULL OR p_min_time < 1 THEN
    p_min_time := 0;
  END IF;

  IF p_max_time < 1 THEN
    p_max_time := NULL;
  END IF;

  IF p_action_name_id IS NULL OR p_action_name_id < 1 THEN
    p_action_name_id := 1;
  END IF;

  IF p_instruction_id > 0 THEN
    SELECT
      rd.seq
    INTO
      v_seq
    FROM
      recipe.recipe r,
      recipe.direction rd,
      recipe.direction_group rdg
    WHERE
      r.id = p_recipe_id AND
      rdg.recipe_id = r.id AND
      rd.id = p_instruction_id AND
      rd.direction_group_id = rdg.id;

    -- If the instruction already exists, change it.
    IF found THEN
      UPDATE recipe.direction
      SET
        instruction = p_instruction,
        min_time = p_min_time,
        max_time = p_max_time,
        action_id = p_action_name_id
      WHERE
        id = p_instruction_id;       
    END IF;
  ELSIF p_instruction_group_id > 0 THEN
    SELECT
      COALESCE( max(rd.seq) + 1, 1 )
    INTO
      v_seq
    FROM
      recipe.direction rd,
      recipe.direction_group rdg
    WHERE
      rdg.recipe_id = p_recipe_id AND
      rdg.id = rd.direction_group_id;

    IF v_seq <= 50 THEN
      INSERT INTO recipe.direction
        (instruction, min_time, max_time, action_name_id, direction_group_id, seq)
      VALUES
        (p_instruction, p_min_time, p_max_time, p_action_name_id, p_instruction_group_id, v_seq);
    END IF;

    PERFORM recipe.instruction_resequence( p_recipe_id );
  END IF;

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

CREATE OR REPLACE FUNCTION is_account_role(p_account_id bigint, p_role_name text) RETURNS integer
    LANGUAGE plpgsql STABLE COST 1
    AS $$
DECLARE
  v_result BIGINT;
BEGIN
  -- Does the given account have the given role?
  SELECT
    rrn.id
  INTO
    v_result
  FROM
    recipe.account_role rar,
    recipe.role_name rrn
  WHERE
    rar.account_id = p_account_id AND
    rrn.code = p_role_name;

  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() );
    RETURN 0;
END;
$$;

CREATE OR REPLACE FUNCTION is_authorized(p_account_id bigint, p_auth_id bigint) RETURNS integer
    LANGUAGE plpgsql STABLE COST 1
    AS $$
DECLARE
  v_result INTEGER;
BEGIN
  /**
   * Can the given account token (from the client) issue edits for the authentication token?
   * This returns 1 for yes and 0 for no.
   */ 
  SELECT
    racc.id
  INTO
    v_result
  FROM
    recipe.authentication rauth,
    recipe.account racc
  WHERE
    rauth.id = racc.authentication_id AND
    racc.id = p_account_id AND
    rauth.id = p_auth_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 is_authorized_account(p_account_id bigint, p_auth_id bigint) RETURNS integer
    LANGUAGE plpgsql STABLE COST 1
    AS $$
DECLARE
  v_result INTEGER;
BEGIN
  /**
   * Can the given authentication token issue edits for the given account token?
   * This returns 1 for yes and 0 for no.
   */
  SELECT
    ra.id
  INTO
    v_result
  FROM
    recipe.account ra
  WHERE
    ra.id = p_account_id AND
    ra.authentication_id = p_auth_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 is_authorized_book(p_book_id bigint, p_auth_id bigint) RETURNS integer
    LANGUAGE plpgsql STABLE COST 1
    AS $$
BEGIN
  /**
   * Returns true if the given authentication ID can edit a given book.
   */
  RETURN 1;

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

CREATE OR REPLACE FUNCTION is_authorized_category(p_auth_id bigint) RETURNS integer
    LANGUAGE plpgsql STABLE COST 1
    AS $$
BEGIN
  -- Can the given authentication token issue edits for categories?
  RETURN recipe.is_account_role( recipe.get_account_id( p_auth_id ), 'ROLE_ADMIN' );

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

CREATE OR REPLACE FUNCTION is_authorized_dietary_preference(p_account_dietary_preference_id bigint, p_auth_id bigint) RETURNS integer
    LANGUAGE plpgsql STABLE COST 1
    AS $$
DECLARE
  v_result INTEGER;
BEGIN
  -- Can the given authentication token issue edits for the given dietary preference token?
  -- This returns 1 for yes and 0 for no.
  SELECT
    radp.id
  INTO
    v_result
  FROM
    recipe.account racct,
    recipe.account_dietary_preference radp
  WHERE
    racct.authentication_id = p_auth_id AND
    radp.account_id = racct.id AND
    radp.id = p_account_dietary_preference_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 is_authorized_recipe(p_recipe_id bigint, p_auth_id bigint) RETURNS integer
    LANGUAGE plpgsql STABLE COST 1
    AS $$
DECLARE
  v_result INTEGER;
BEGIN
  -- Can the given authentication token issue edits for the given recipe token?
  -- This returns 1 for yes and 0 for no.
  SELECT
    rar.recipe_id
  INTO
    v_result
  FROM
    recipe.account racct,
    recipe.account_recipe rar
  WHERE
    racct.authentication_id = p_auth_id AND
    rar.account_id = racct.id AND
    rar.recipe_id = p_recipe_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 is_existing_account(p_account_id bigint) RETURNS integer
    LANGUAGE plpgsql STABLE COST 1
    AS $$
DECLARE
  v_result INTEGER;
BEGIN
  -- This returns 1 for yes and 0 for no.
  SELECT
    ra.id
  INTO
    v_result
  FROM
    recipe.account ra
  WHERE
    ra.id = p_account_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 is_existing_book(p_book_id text) 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 is_existing_cookie(p_cookie text) RETURNS integer
    LANGUAGE plpgsql STABLE COST 1
    AS $$
DECLARE
  v_result INTEGER;
BEGIN
  /**
   * Does the cookie exist in the database?
   * Returns 1 for yes and 0 for no.
   */
  SELECT
    ra.id
  INTO
    v_result
  FROM
    recipe.authentication ra
  WHERE
    ra.cookie = p_cookie;

  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 is_existing_dietary_preference(p_account_dietary_preference_id bigint) RETURNS integer
    LANGUAGE plpgsql STABLE COST 1
    AS $$
DECLARE
  v_result INTEGER;
BEGIN
  -- Does the account dietary preference exist in the database?
  -- This returns 1 for yes and 0 for no.
  SELECT
    radp.id
  INTO
    v_result
  FROM
    recipe.account_dietary_preference radp
  WHERE
    radp.id = p_account_dietary_preference_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 is_existing_recipe(p_recipe_id bigint) RETURNS integer
    LANGUAGE plpgsql STABLE COST 1
    AS $$
DECLARE
  v_result INTEGER;
BEGIN
  -- Does the recipe exist in the database?
  -- This returns 1 for yes and 0 for no.
  SELECT
    rd.recipe_id
  INTO
    v_result
  FROM
    recipe.description rd
  WHERE
    rd.recipe_id = p_recipe_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 search_category(text) RETURNS SETOF text_label
    LANGUAGE sql STABLE COST 1 ROWS 100
    AS $_$
  SELECT
    ricn.id::bigint,
    ricn.label::text
  FROM
    recipe.ingredient_category_name_vw ricn
  WHERE
    recipe.unaccent_text(ricn.label) ~~ ('%' || $1 || '%')
  ORDER BY
    similarity(label, $1) DESC, label;
$_$;

CREATE OR REPLACE FUNCTION search_ingredient(text) RETURNS SETOF text_label
    LANGUAGE sql STABLE COST 1 ROWS 9
    AS $_$
  SELECT
    rinv.id::bigint,
    rinv.label::text
  FROM
    recipe.ingredient_name_vw rinv
  WHERE
    recipe.unaccent_text(rinv.label) ~~ ('%' || $1 || '%') OR
    recipe.unaccent_text(rinv.abridge) ILIKE ('%' || $1 || '%')
  ORDER BY
    similarity(label, $1) DESC, label
  LIMIT 9;
$_$;

CREATE OR REPLACE FUNCTION search_ingredient_category(text) RETURNS SETOF text_label
    LANGUAGE sql STABLE COST 10
    AS $_$
  /**
   * Returns text matching from the list of categories and
   * that matches from the list of ingredients.
   */
  SELECT
    DISTINCT ON (label)
    id::bigint,
    label::text
  FROM (
    SELECT
      id::bigint,
      label::text
    FROM
      recipe.search_category( $1 )
    UNION
    SELECT
      id::bigint,
      label::text
    FROM
      recipe.search_ingredient( $1 )
  ) t;
$_$;

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 musicbrainz_unaccent($1);
$_$;