Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

Synchronizing constants/enums in database and code

by seattlejohn (Deacon)
on Apr 11, 2004 at 08:53 UTC ( #344235=perlmeditation: print w/replies, xml ) Need Help??

Lately I've been scratching my head over how to most effectively keep constants in code and constants in a database table synchronized. Since I haven't found any relevant discussions via search, I thought I'd share my thinking here and see how other monks might approach this problem.

As a contrived example, imagine we have a database table PAYMENT_TYPES that contains the following:

              1 | VISA
              2 | MASTERCARD
              3 | AMERICAN EXPRESS

And somewhere in our code we have corresponding constants:

use constant PAYMENT_TYPE_VISA => 1; use constant PAYMENT_TYPE_MASTERCARD => 2; use constant PAYMENT_TYPE_AMERICAN_EXPRESS => 3;
or, more likely, the equivalent enum. Since this enumeration lives in two places, how do we ensure that they stay in sync? (Alternately, can we find a way to eliminate one or the other?)

Some approaches that come to mind are:

1. Keep code and data in sync manually.
Seems error-prone, though suitably designed automated testing could probably mitigate many oversights.

2. Eliminate the enumeration from the database and keep it in code only.
Violates the expectation that the database will be self-describing, and compromises the ability to establish a foreign-key constaint for tables that include a PAYMENT_TYPE_ID.

3. Eliminate the enumeration from the code and keep it in the database only.
Breaks down if the code needs to know what the enumeration means so that it can apply different logic to different cases. Possible workarounds below, though.

4. Keep the enumeration in the database and query it on program startup to define appropriate variables.
The enumeration in the code is no longer constant, and there's a potential performance hit if you have lots of enumerations to load and thus lots of queries to do every time you start the program. (Then again, no need to think about optimization prematurely...)

5. Keep the official enumeration in the database; query the database to generate code that contains appropriate constants/enums.
Have to make sure your code generation really occurs when the database changes. Could automate this as part of the build/test process.

6. Don't worry about it!
After all, if you are changing your constants/enums, you're probably changing fundamental functionality of the code (it's not as if adding a row for 4 | DINER'S CLUB somehow magically enables you to handle that case properly without touching code elsewhere).

I think I'm torn between #1 and #5 right now. #1 has the obvious virtue of simplicity, but I worry about maintainability -- my experience is that if it's possible to forgot to do something, someone (myself included) *will* forgot to do it sooner or later. Admittedly, since I'm pretty rigorous about test suites, it wouldn't be too hard to detect this before deployment. #5 seems a little bit more pure, because the enumeration really lives in only one place, but it does rely on code generation being trigged when the database changes (which is probably not a big deal since I do "builds" to my test environment with ANT anyway). Then there's the part of me that's convinced that the reasoning behind #6 makes this a moot point anyway.

Any other monks who have contemplated this issue? I'd be curious to hear what pros and cons you see in the various approaches (or some totally new alternatives)... and how you ultimately resolved it.

        $perlmonks{seattlejohn} = 'John Clyman';

Replies are listed 'Best First'.
Re: Synchronizing constants/enums in database and code
by liz (Monsignor) on Apr 11, 2004 at 11:35 UTC
    I would go for 5:

    5. Keep the official enumeration in the database; query the database to generate code that contains appropriate constants/enums. Have to make sure your code generation really occurs when the database changes. Could automate this as part of the build/test process.

    But please note that you don't need to do this as part of the build/test process, but you can also do this at compile time in a BEGIN block.

    BEGIN { use DBI; my $dbh = DBI->connect( dsn,user,password ); my $result = $dbh->some sort of query that generates name/value pa +irs; no strict 'refs'; # allow for using variables to make subs foreach (@$result) { *{$_->[0]} = eval "sub () { $_->[1] }"; # use constant on vari +ables } }
    That should ensure that your constants are up to date for each run, as opposed to each build/test cycle.


    As thor pointed out, this could be construed as going with #4, but I was more thinking along the lines of SHOW COLUMNS FROM table LIKE 'columnname' and associated magic as can e.g. be found on the MySQL site.

      The solution that you gave is one for case #4. Which is fine, as long as you don't have a lot of data to load at the start of the program. There is also the case where we're using other data structures from other languages (enums were mentioned). I'm no C wizard, but I don't know whether or not you can define an enum at run time.


Re: Synchronizing constants/enums in database and code
by cLive ;-) (Prior) on Apr 11, 2004 at 12:48 UTC
    Following up on what Liz said...

    I was in a similar situation. Here's what I did:

    • create the data table in the database
    • create a module that turns the data into something I can play with (in my instance, a hashref, but constants are just as good - if a little less flexible whenever you want to embed them in strings :)
    • use the module whenever I need to know any of the constants

    And because it's under mod_perl, the code that builds the hashref is in a that is called when Apache starts, so it's instantly available to my scripts, and DB calls are only made when the server starts.

    cLive ;-)

      I do the exact same thing, but with my security tree. I build all the possible access levels in memory, which saves up to 2000 database hits per page.

      We are the carpenters and bricklayers of the Information Age.

      Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

Re: Synchronizing constants/enums in database and code
by hardburn (Abbot) on Apr 11, 2004 at 13:35 UTC

    Enums are a hack for the fact that MySQL didn't have proper referential integrity until recently. In this case, the better answer is probably to put your column in a seperate table which is linked to with a foreign key. You can use a subselect (another thing MySQL only recently got) to get the data back out:

    CREATE TABLE payment_types ( id int primary key auto_increment, name blob not null unique ) type = InnoDB; -- Because InnoDB doesn't suck CREATE TABLE order ( -- or whatever you call it id int primary key auto_increment, pay_type int not null, -- other columns FOREIGN KEY (pay_type) REFERENCES payment_type (id) ON DELETE RESTRICT ) type = InnoDB; -- Example insert INSERT INTO order (pay_type) VALUES ( (SELECT id FROM payment_type WHERE name = 'VISA') )

    Yes, there is a performance hit. It's also a much cleaner implementation.

    Update: s/FOREGIN/FOREIGN/. Thanks to cLive ;-).

    : () { :|:& };:

    Note: All code is untested, unless otherwise stated

Re: Synchronizing constants/enums in database and code
by jplindstrom (Monsignor) on Apr 11, 2004 at 15:39 UTC
    As you have already figured out, there should only be one single representation of a fact in the system (The DRY principle from the Pragmatic Programmers).

    So the question is where the fact is stated. Some ideas:

    You can get it from the database as per your #4. But how does it get into the database in the first place? If you nuke the db, how do you rebuild it? I imagine that somewhere there are SQL scripts for default data.

    • If not, maybe there should be? Maybe those scripts could be used to determine the constants in the code?
    • If not, maybe the code could be responsible for setting up the default data in the database? It wouldn't have to do this all the time, but the information for doing it at scheduled upgrades could live in the code


      Indeed, the DRY principle was the underlying motivator for my asking this question -- why do I have this in two places, and how am I going make sure they stay synchronized?

      I do have scripts that (re-)construct and (re-)populate the database. I'm really glad you brought that up, because considering them as part of the constellation of things that needs to stay in sync is leading me toward an approach I think I'm comfortable with.

      I'm now inclined to the constants in the code and generating the database table based on those same constants that the production code relies on. Of course I'll back that up with tests to make sure that I don't do something dumb like change the constants without reflecting those changes in the database.

      Thinking about it that way removes a lot of the unease I was feeling about this data being spread around instead of having one source of Ultimate Truth.

      Thanks for the replies, everyone!

              $perlmonks{seattlejohn} = 'John Clyman';

Re: Synchronizing constants/enums in database and code
by tilly (Archbishop) on Apr 11, 2004 at 20:13 UTC
    Adding to the confusion, I'm perfectly fine with most of these strategies, but I'm also comfortable with #1 and a suitable unit test to check that they are in sync. As you note in #6, if you're changing constants/enums, you're probably changing fundamental functionality in the code. The testing strategy reflects your understanding that this change is not one that can be automated.
Re: Synchronizing constants/enums in database and code
by Anonymous Monk on Apr 11, 2004 at 16:31 UTC

    I'd go with #4, unless you're really processing so many requests at once that it is damaging to hit the database to get the constants. It's only one simple SELECT statement, that can't hurt anything much, can it?

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlmeditation [id://344235]
Approved by Old_Gray_Bear
Front-paged by calin
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (8)
As of 2023-02-01 13:40 GMT
Find Nodes?
    Voting Booth?
    I prefer not to run the latest version of Perl because:

    Results (5 votes). Check out past polls.