How can I be DRY in columns names in this MySQL procedure?

Question

I'm referencig name, description and user_id columns of meta table. Twice, and maybe more (who knows?) in future. Those columns are used to compute the ETag of my meta resource.

Adding one column that contributes to compute ETag in the future will force me to change the code N times, and this is bad.

Is there any way to make it DRY and store these column names elsewhere? Because I'd like to use these column names also when INSERT on meta is performed.

IF only = true THEN
    -- Calculate ETag on meta fields only
    UPDATE meta
    SET etag = etag(CONCAT(name, description, user_id))
    WHERE id = meta_id;
ELSE
    -- Calculate Etag on meta fields and meta customers
    BEGIN
        DECLARE c_etags VARCHAR(32);

        -- Compute c_etags

        UPDATE meta
        SET etag = etag(CONCAT(etag(name, description, user_id), c_etags))
        WHERE id = meta_id;
    END;
END IF;

Disclaimer: this code is untested, I'm pretty new to MySQL stuff, apart for simple statements.

EDIT: etag is MD5 MySQL function. Maybe this is one option:

CREATE PROCEDURE set_meta_etag(IN meta_id INT, IN related TEXT)
    NOT DETERMINISTIC
BEGIN
    UPDATE meta
    SET etag = etag(CONCAT(name, description, user_id,
        IF(related IS NOT NULL, related, '')))
    WHERE id = meta_id;
END //

-- First call
CALL set_meta_etag(meta_id, NULL);

-- Second call
CALL set_meta_etag(meta_id, c_etags);

But it won't work for INSERT statement.

score 0 · Accepted Answer · edited May 23 '17 at 10:34

The obvious thing (foreach column, if it's the one I want, use it to help make the etag) doesn't work in SQL with any ease, because SQL doesn't, historically, contemplate column names stored in variables.

You could write a program in your favorite non-SQL programming language (Java, PHP, etc) to create and then define your procedure.

You could also use so-called "dynamic sql" to do this, if you were willing to do the work and take the slight performance hit. See

How To have Dynamic SQL in MySQL Stored Procedure

for information on how to PREPARE and EXECUTE statements in a stored procedure.

By the way, I have had good success building systems that have various kind of metadata stored in the column contents. For example, you could write code looking for the string '[etag]' in your column contents. The comments for columns are stored in

information_schema.COLUMNS.COLUMN_COMMENT

and are very easy to process when your program is starting up.

score 0 · Answer 2 · edited May 23 '17 at 10:34

0

If you know this is confined to one table, you could add a trigger. Using an AFTER trigger should allow your stored proc to work for both INSERT and UPDATE. See MySQL Fire Trigger for both Insert and Update.

edited May 23 '17 at 10:34

Community

1
1

answered Sep 05 '12 at 17:45

roufamatic

18,187
7
57
86

How can I be DRY in columns names in this MySQL procedure?

2 Answers2