I think you better explain more of the situation. Where do the table names come from? If they are part of hash, what are they associated with (filenames? connections?). What are you going to do with the table names? By hardcoded in the script, do you mean hardcoded in SQL statements? Sorry, there's not much to answer unless you can give us some clues. | [reply] |
My apologies, jZed, and thanks for replying.
Each key of the hash stores the table name and the name of column to be sorted (just in case sorting is required). The table names themselves are hardcoded and do not come from an external source. However, a table may be queried for its contents by supplying its name through an online form.
The hash I was talking about is as follows
%table =(
members => [ qw(members id) ],
emails => [ qw(emails member_id)]
};
So, instead of
SELECT * from members
It's
SELECT * from $table{'members'}[0]
Does hashing the table names make sense?
One use I could think of is verifying the table's existence when it's being queried (as in an web application). | [reply]
[d/l]
[select] |
Now that I see what you're doing, yes, I think some kind of a hash makes sense. You wrote:
a table may be queried for its contents by supplying its name through an online form.
In that case, you definitely want to check the name against the hash to make sure it's a table you want users to be able to access. You may also want something like this, depending on your needs:
my @tables = (
{name=>members,cols=>[qw(members id)]},
{name=>emails,cols=>[qw(emails member_id)]},
);
my $sql = "SELECT "
. join(',',@{$tables[0]->{cols}})
. " FROM "
. $tables[0]->{name}
;
Eventually you might consider Class::DBI which provides some ready-made shortcuts for this kind of thing. | [reply]
[d/l] |
Does it make sense to store sql table names in a hash
Not to me, it makes your SQL impossible to read without refering to your index hash.
... or hardcode the names in your script?
They have to be written somewhere. They ARE hardcoded into the DB schema.
In the beginning there was the schema. And the chief architect saw that it was good and said unto his minions, go forth and code this schema for it is inviolate and it will not changed. And though the minions wanted desperately to believe the chief architect they knew that the schema would change.....
As so after 40 days and 40 nights it became evident that column X was missing from table Y.....
I expect dealing with this sort of sitation is what you are really talking about. Some of the ways to deal with this eventuality include avoiding select * and always selecting specfic colums, same for updates and inserts. That way adding a column is less of an issue. Abstracting DB access to varying degrees also makes sense. At if simplest you might just put all the DB access code into its own module so at least you have one place to do most of the work in the event of changes to the schema.
| [reply] |