Perl DBI / SQL Question

[Vance M. Allen]

Sorry if the cross-posting wasn't appropriate, but I need help with this and
am not sure if it's more appropriate to post under MySQL or Perl...I want to
be sure that I can get help from the best source.

My question is probably a simple answer, but I am not sure what I have to do
and the books I have here are either not answering the question, or I'm not
finding the answer.

I need to know how to retrieve through Perl DBI a listing of possible ENUM
elements from a field for processing under a CGI script. If all I need for
this is some form of SELECT statement, please provide a code snippet of this
so I can do it.

I want to make my code so I'm not having to edit hard-coded Perl CGI scripts
if/when I add new elements to the ENUM field. Any help you can provide
would be greatly appreciated.

Thanks!

Vance

 

[Brian Wakem]

Sorry if the cross-posting wasn't appropriate, but I need help with this
and am not sure if it's more appropriate to post under MySQL or Perl...I
want to be sure that I can get help from the best source.

My question is probably a simple answer, but I am not sure what I have to
do and the books I have here are either not answering the question, or I'm
not finding the answer.

I need to know how to retrieve through Perl DBI a listing of possible ENUM
elements from a field for processing under a CGI script. If all I need
for this is some form of SELECT statement, please provide a code snippet
of this so I can do it.

I want to make my code so I'm not having to edit hard-coded Perl CGI
scripts
if/when I add new elements to the ENUM field. Any help you can provide
would be greatly appreciated.

Thanks!

Vance

I don't know if there's a built-in may of doing this, but it got me
thinking. Here's a dirty was of doing it.

my $sth = $dbh->prepare("DESCRIBE tablename fieldname");
$sth->execute();
my @row = $sth->fetchrow_array();
my $enum = $row[1];
$enum =~ s!^enum\(!!;
$enum =~ s!\)$!!;
my @enum = split/,/,$enum;
foreach(@enum) {
s!^'!!;
s!'$!!;
}

print "$_\n" foreach @enum;

 

[Bill Karwin]

I need to know how to retrieve through Perl DBI a listing of possible ENUM
elements from a field for processing under a CGI script.

It's for this reason that I avoid using ENUM datatypes.
It's much easier to get the list of values if you define the set of
values in a lookup table, and just reference the lookup table in your
field definition.

CREATE TABLE foo_lookup (
foo VARCHAR(32) PRIMARY KEY
) TYPE=InnoDB;
CREATE TABLE t (
foo VARCHAR(32) REFERENCES foo_lookup
) TYPE=InnoDB;

Then you can get the list of values simply: "SELECT foo FROM
foo_lookup". Note that you must use InnoDB tables if you want these
references to be enforced by the DBMS.

This method makes it easier to change the set of values, too.
Otherwise you have to change the field definition when you change the
values in the ENUM.

I find ENUM is best used for cases when you know the set of values will
be unchanging. Like True/False (if your DBMS doesn't support a boolean
type), or Male/Female, or Approved/Denied, etc.

Regards,
Bill K.

 

[Vance M. Allen]

Good point. My situation is this.. I basically have a Security table, and
currently the ENUM is listing the different actions that are available, and
the table has a simple Y/N for whether each of the access groups is
permitted for the action. If you have any good suggestions or examples for
how to handle this, it would be greatly appreciated.

Thanks,

Vance

I need to know how to retrieve through Perl DBI a listing of possible ENUM
elements from a field for processing under a CGI script.

It's for this reason that I avoid using ENUM datatypes.
It's much easier to get the list of values if you define the set of
values in a lookup table, and just reference the lookup table in your
field definition.

CREATE TABLE foo_lookup (
foo VARCHAR(32) PRIMARY KEY
) TYPE=InnoDB;
CREATE TABLE t (
foo VARCHAR(32) REFERENCES foo_lookup
) TYPE=InnoDB;

Then you can get the list of values simply: "SELECT foo FROM
foo_lookup". Note that you must use InnoDB tables if you want these
references to be enforced by the DBMS.

This method makes it easier to change the set of values, too.
Otherwise you have to change the field definition when you change the
values in the ENUM.

I find ENUM is best used for cases when you know the set of values will
be unchanging. Like True/False (if your DBMS doesn't support a boolean
type), or Male/Female, or Approved/Denied, etc.

Regards,
Bill K.

 

[Bill Karwin]

Good point. My situation is this.. I basically have a Security table, and
currently the ENUM is listing the different actions that are available, and
the table has a simple Y/N for whether each of the access groups is
permitted for the action. If you have any good suggestions or examples for
how to handle this, it would be greatly appreciated.

You need another table:

CREATE TABLE privilege (
access_group_id INTEGER NOT NULL
REFERENCES access_group(access_group_id),
priv_type_id INTEGER NOT NULL
REFERENCES privelege_type(priv_type_id),
permitted BOOLEAN NOT NULL DEFAULT 0,
PRIMARY KEY (access_group_id, priv_type_id)
);

CREATE TABLE privilege_type (
priv_type_id INTEGER NOT NULL PRIMARY KEY,
priv_type_name VARCHAR(64)
);

Now you take all the priveleges info out of your access_group table, and
rely on the rows in the privelege table.

This allows you to add new types of privileges without changing your
schema, and also allows you to look up the list of currently valid
privilege types by "SELECT * FROM privelege_type".

Absence of a row in the privilege table for a given priv type could be
equivalent to not having that privilege. Or if you want to get fancy,
you can make certain privileges have different defaults. Add a
"default_permitted BOOLEAN" to the privilege_type table, and use a query
like the following to get all privileges for all access groups:

SELECT g.id, pt.priv_type_id,
COALESCE(p.permitted, pt.default_permitted) AS permitted
FROM access_group AS g
INNER JOIN privilege_type AS pt
LEFT OUTER JOIN privilege AS p ON pt.priv_type_id = p.priv_type_id;

(note the rare appropriate use of a Cartesian product!)

Regards,
Bill K.

 

[Vance M. Allen]

I see where you're going with this, and I like it much better than what I
have. I have a question about it though. I currently have phpMyAdmin set
up to do hard edits to the data in my database when needed, and every time
I'm on the database structure page, I receive a message:

"The additional Features for working with linked Tables have been
deactivated."

Am I going to be able to use "REFERENCES" in the table definition with this
being the case? If not, do you know what I need to do in order to correct
this problem?

Thanks,

Vance

 

[Bill Karwin]

"The additional Features for working with linked Tables have been
deactivated."

This is a new one for me. Fortunately google helps with all things:

http://www.psoft.net/HSdocumentation/sysadmin/phpmyadmin_linked_tables.html

Regards,
Bill K.

 

[Vance M. Allen]

You see where who is going with what? Please quote the relevant parts

of the post you're responding to, which is long-standing Usenet practice.

I saw where Bill was going with the entire message he wrote. As I wrote it
as a reply to his message, causing it to be part of the same thread, I did
not realize it was necessary to re-post the entire message. I will try
harder to meet Usenet etiquette. Apologies to anyone affected by my failure
to do so.

Vance

 

[Vance M. Allen]

I think the features that have been deactivated refer to making
diagrams of the relationships between tables and such, not using
references, which is a MySQL, not phpMyAdmin, feature.

http://www.psoft.net/HSdocumentation/sysadmin/phpmyadmin_linked_tables.html

Thank you Gordon. I did the steps on the page Bill provided for me, and it
resolved the message in phpMyAdmin, but I didn't really see anything in
phpMyAdmin afterward that seemed relevant to my issue. I'm going to try his
SQL and we'll see if it works.

Thanks for everyone's help.

Vance

 

[A. Sinan Unur]

....

I did not realize it was necessary to re-post the entire
message.

It is not.

Sinan