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
    }
}
[download]

Liz

Update:
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.

thor

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 startup.pl 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

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')
)
[download]

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

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

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

/J

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';

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?