How would you enforce DRY (Don't Repeat Yourself) in a SQL script?

Question

Hi,

I'm changing a database (oracle) with a script containing a few updates looking like:

UPDATE customer
SET status = REPLACE(status,   'X_Y',   'xy')
WHERE status LIKE '%X_Y%'
 AND category_id IN
  (SELECT id
   FROM category
   WHERE code = 'ABC');

UPDATE customer
SET status = REPLACE(status,   'X_Z',   'xz')
WHERE status LIKE '%X_Z%'
 AND category_id IN
  (SELECT id
   FROM category
   WHERE code = 'ABC');

-- More updates looking the same...

In this case, how would you enforce DRY (Don't Repeat Yourself)?

I'd particularly interested in solving the two following recurring problems:

• Define a function, available from this script only, to extract the subquery SELECT id FROM category WHERE code = 'ABC'

• Create a set of replace rules (that could look like {"X_Y": "yx", "X_Z": "xz", ...} in a popular programming language) and then iterate a single update query on it.

Thanks!

Answer 1

Depending on how important the script is, I would:

1. Just copy and paste and modify, or
2. Write a script in another programming language that has better ways to resolve the duplication.

For the replace rules you could create a temporary table and fill it with these replace rules, and then join with this table.

If the subquery is always the same, you have solved the first problem also by using a join.

Answer 2

First of all, remember that scripting is not the same thing as programming, and you don't have to adhere to DRY principles. Scripts like this one are usually one-offs, not a program to be maintained over a long time.

But you could use PL/SQL to do this:

declare
   type str_tab is table of varchar2(30) index by binary_integer;
   from_tab str_tab;
   to_tab str_tab;
begin
   from_tab(1) := 'X_Y';
   from_tab(2) := 'X_Z';
   to_tab(1) := 'xy';
   to_tab(2) := 'xz';

   for i in 1..from_tab.count loop

      UPDATE customer
      SET status = REPLACE(status,   from_tab(i),   to_tab(i))
      WHERE status LIKE '%' || from_tab(i) || '%'
       AND category_id IN
        (SELECT id
         FROM category
         WHERE code = 'ABC');

   end loop;
end;

Answer 3

I've seen a few approaches to this:

1. Use string buffers to assemble the sql dynamically using PL/SQL or in your programming language.
2. Use a framework such as IBATIS which let's you reuse and extend fragments of SQL that are stored in XML files.
3. Using an ORM framework circumvents this issue by working with objects rather than directly with the SQL.

Depending on your language and problem at hand using a framework may be the best approach and then extending it to do what you want it to do.

Answer 4

I would reduce it to a single query:

UPDATE customer
SET status = REPLACE(REPLACE(status, 'X_Y', 'xy'), 'X_Z', 'xz')
WHERE status REGEXP_LIKE 'X_[YZ]'
 AND category_id IN
  (SELECT id
   FROM category
   WHERE code = 'ABC');

Answer 5

Write a script that takes parameters and call it multiple times. (I'm assuming you're using SQLPlus to run the script.)

replace_in_status.sql:

UPDATE customer
SET status = REPLACE(status,   UPPER('&1'),   '&2')
WHERE status LIKE '%' ||UPPER('&1')|| '%'
 AND category_id IN
  (SELECT id
   FROM category
   WHERE code = 'ABC');

Calling script:

@replace_in_status X_Y xy
@replace_in_status X_Z xz

Answer 6

Pretty straightforward, unless I'm missing something.

UPDATE customer
SET status = REPLACE(REPLACE(status,'X_Y','xy'),'X_Z','xz')
WHERE ( status LIKE '%X_Y%' Or status LIKE '%X_Z%')
  AND category_id IN
     (SELECT id
      FROM category
      WHERE code = 'ABC');

Answer 7

The solution suggested by soulmerge is the simplest, and therefore best one - you just need to nest the calls to "replace". I just want to add that the condition

status like '%tagada%'

is useless. replace() will change nothing to the status if the searched string is not found, therefore you can safely apply it to all rows. And since a condition where you search a string lost in the middle of another string cannot make any use of whatever index you have, it's useless as a filtering condition. Your only filtering condition is the one on category_id ... Which brings one point that justifies why soulmerge's solution is best: iterating on all the changes is a bad idea. Suppose that the filter on category_id is moderately selective, odds are that Oracle will choose to scan the table. Do you really want to scan the table each time when you can do all the changes in a single pass?

Answer 8

Okay, a shot from the hip here, take it easy on my syntax :-)

Would an approach like this help:

DECLARE
  v_sql1   VARCHAR2(1000);
  v_sql2   VARCHAR2(2000);
  TYPE T_Rules IS RECORD (srch  VARCHAR2(100),  repl(VARCHAR2(100));
  TYPE T_RuleTab IS TABLE OF T_Rules INDEX BY BINARY_INTEGER;
  v_rules T_RuleTab;

  FUNCTION get_subquery RETURN VARCHAR2 IS
  BEGIN
    RETURN '(SELECT id FROM category WHERE code = ''ABC'')';
  END;

BEGIN
  v_sql1 := 'UPDATE customer SET status = REPLACE('':1'','':2'') WHERE status LIKE ''%:1%'' AND category_id IN ';
  v_rules(1).srch := ('X_Y'); v_rules(1).repl := 'yx';
  v_rules(2).srch := ('X_Z'); v_rules(2).repl := 'xz';

  FOR i IN 1..v_rules.COUNT LOOP
    v_sql2 := v_sql1||get_subquery();
    EXECUTE IMMEDIATE v_sql2 USING v_rules(i).srch, v_rules(i).repl;
  END LOOP;
END;

You could replace the PL/SQL table with a real table and run a cursor over it, but this addresses your second requirement.

Obviously some work is left on get_subquery, your first requirement ;-)

EDIT

Dang! forgot to mention you need to be careful with that replace string in your WHERE clause - underscores are a single character matching wild card in Oracle...