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]

Re: Perl DBI / SQL Question

Vance M. Allen wrote:
[color=blue]
> 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[/color]


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("DESCR IBE 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;


--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png

[Bill Karwin]

Re: Perl DBI / SQL Question

Vance M. Allen wrote:[color=blue]
> 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.[/color]

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]

Re: Perl DBI / SQL Question

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


"Bill Karwin" <bill@karwin.co m> wrote in message
news:dg9r0o01dq v@enews3.newsgu y.com...
Vance M. Allen wrote:[color=blue]
> 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.[/color]

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]

Re: Perl DBI / SQL Question

Vance M. Allen wrote:[color=blue]
> 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.[/color]

You need another table:

CREATE TABLE privilege (
access_group_id INTEGER NOT NULL
REFERENCES access_group(ac cess_group_id),
priv_type_id INTEGER NOT NULL
REFERENCES privelege_type( priv_type_id),
permitted BOOLEAN NOT NULL DEFAULT 0,
PRIMARY KEY (access_group_i d, 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_permit ted 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.perm itted, pt.default_perm itted) 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]

Re: Perl DBI / SQL Question

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

[Kevin Michael Vail]

Re: Perl DBI / SQL Question

In article <hX2We.67$4V2.3 443@news.uswest .net>,
"Vance M. Allen" <vma_nntp@vmacs .us> wrote:
[color=blue]
> I see where you're going with this, [...][/color]

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.
--
Kevin Michael Vail | a billion stars go spinning through the night,
kevin@vaildc.ne t | blazing high above your head.
. . . . . . . . . | But _in_ you is the presence that
. . . . . . . . | will be, when all the stars are dead.
. . . . . . . . . | (Rainer Maria Rilke)

[Bill Karwin]

Re: Perl DBI / SQL Question

Vance M. Allen wrote:[color=blue]
> "The additional Features for working with linked Tables have been
> deactivated."[/color]

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]

Re: Perl DBI / SQL Question

> You see where who is going with what? Please quote the relevant parts[color=blue]
> of the post you're responding to, which is long-standing Usenet practice.[/color]

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

[Gordon Burditt]

Re: Perl DBI / SQL Question

>I see where you're going with this, and I like it much better than what I[color=blue]
>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. "[/color]

As I recall, this type of message comes up for a number of reasons
relating to the database (often called 'phpmyadmin') used by
phpMyAdmin to store some of its own data. I think the problem is:

- It can't log into the database (or has the wrong name for
it) or doesn't have permissions on the tables it needs.
- You didn't create the database or tables for it.

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.

Gordon L. Burditt

[Vance M. Allen]

Re: Perl DBI / SQL Question

"Gordon Burditt" <gordonb.h57un@ burditt.org> wrote in message
news:11iho0m8rn 58a20@corp.supe rnews.com...[color=blue][color=green]
>>"The additional Features for working with linked Tables have been
>>deactivated ."[/color]
>
> 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.[/color]

"Bill Karwin" <bill@karwin.co m> wrote in message
news:dgag27021f i@enews3.newsgu y.com...[color=blue]
> http://www.psoft.net/HSdocumentation...ed_tables.html[/color]



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]

Re: Perl DBI / SQL Question

"Vance M. Allen" <vma_nntp@vmacs .us> wrote in
news:lV4We.72$4 V2.4109@news.us west.net:
[color=blue][color=green]
>> 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.[/color][/color]
....
[color=blue]
> I did not realize it was necessary to re-post the entire
> message.[/color]

It is not.

Sinan


--
A. Sinan Unur <1usa@llenroc.u de.invalid>
(reverse each component and remove .invalid for email address)

comp.lang.perl. misc guidelines on the WWW:

http://mail.augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html