Best Perlish way to test if a mysql table exists?

punch_card_don has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Best Perlish way to test if a mysql table exists? by Aristotle (Chancellor) on Jan 31, 2005 at 00:09 UTC
Well, you can use `SHOW TABLES LIKE SQL-glob`. Also, to my knowledge MySQL makes all the data about its database structure available in a system database (called `mysql` if I'm not mistaken), against which you could run queries. Makeshifts last the longest.	[reply]
Re^2: Best Perlish way to test if a mysql table exists? by trammell (Priest) on Jan 31, 2005 at 00:19 UTC
Nah, the `mysql` table contains primarily user access config data. Update: Whoops, that should read `mysql` database.	[reply]
Re: Best Perlish way to test if a mysql table exists? by trammell (Priest) on Jan 31, 2005 at 00:04 UTC
I'd suggest that your MySQL version is going to dictate exactly what features are supported. IIRC subselects aren't supported by MySQL versions before 4.1. One solution is to wrap all your queries in `eval` blocks to catch any errors. I'd suggest the solution you don't want--running a `SHOW TABLES` and rejecting any tables that aren't available. Why is that such a terrible solution? Is it too slow? Why is it not "clean and simple" enough for you?	[reply]
Re^2: Best Perlish way to test if a mysql table exists? by Aristotle (Chancellor) on Jan 31, 2005 at 00:36 UTC
eval is only necessary if DBI's `RaiseError` flag was set on connection to the database; otherwise `$dbh->err` must be checked. This should probably be done with a query such as `SELECT 1 FROM table WHERE 0` which would always succeed but would never return any results, provided the table exists. Makeshifts last the longest.	[reply]
Re^3: Best Perlish way to test if a mysql table exists? by punch_card_don (Curate) on Jan 31, 2005 at 01:14 UTC
Just read up on RaiseError - maybe a possibility. But that would create an error trapping nightmare for me having to re-write trappig code for every line of sql in the script.... Oh, for a "if table exists" function! Forget that fear of gravity, Get a little savagery in your life.	[reply]
Re^4: Best Perlish way to test if a mysql table exists? by Aristotle (Chancellor) on Jan 31, 2005 at 02:25 UTC
Re^4: Best Perlish way to test if a mysql table exists? by blokhead (Monsignor) on Jan 31, 2005 at 05:35 UTC
Re^5: Best Perlish way to test if a mysql table exists? by Aristotle (Chancellor) on Jan 31, 2005 at 06:24 UTC
Re^2: Best Perlish way to test if a mysql table exists? by punch_card_don (Curate) on Jan 31, 2005 at 00:28 UTC
'cause, like I said, there are tens of thousands of tables in the database, and perhaps hundreds of tables in the list - I don't want to bloat this with a lot of extra crunching. The more I think of it, the more a Perl solution that will trap the "table does not exist" error may be best. Forget that fear of gravity, Get a little savagery in your life.	[reply]
Re^3: Best Perlish way to test if a mysql table exists? by trammell (Priest) on Jan 31, 2005 at 02:17 UTC
I don't think it would be a lot of extra crunching. Maybe a single query to list the tables, which you cache in a local hash. Then check the values in the hash to see if the current table you're querying is in the database. Hardly bloated.	[reply]
Re: Best Perlish way to test if a mysql table exists? by gwhite (Friar) on Jan 31, 2005 at 02:47 UTC
How about: `@names = $dbh->tables( '', '', 'r6019', 'TABLE');` If you get something back you are good to go, if not skip it and go to the next table. I would also index either the misses or hits (lessor of the two) if you might hit the same table multiple times. g_White	[reply] [d/l]
Re: Best Perlish way to test if a mysql table exists? by Solo (Deacon) on Jan 31, 2005 at 16:31 UTC
...I don't want to do is use a SHOW TABLES and iterate through the results... SHOW TABLES accepts a LIKE clause, though it's not clear what versions support it. The more Perlish way might be to use one of the DBIx:: modules like DBIx::DBSchema to check if each table exists. --Solo -- You said you wanted to be around when I made a mistake; well, this could be it, sweetheart.	[reply]
Re^2: Best Perlish way to test if a mysql table exists? by Anonymous Monk on Mar 28, 2013 at 18:32 UTC
Short, sweet, not complicated, no additional questions, etc.., = what I was looking for. Thanks.	[reply]
Re: Best Perlish way to test if a mysql table exists? by dragonchild (Archbishop) on Jan 31, 2005 at 13:52 UTC
. . . I have a list of tables to query. Problem is, the list is supplied by someone else and may contain errors, meaning it may direct the script to query a table that does not exist. Why not pre-process the list of tables you are given? Then, your script only has to worry about good tables, because you've already removed the bad tables. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re: Best Perlish way to test if a mysql table exists? by digiryde (Pilgrim) on Jan 31, 2005 at 15:33 UTC
I know this could be done, but I do not know if it is the best way. All of the mysql tables are stored in a directory as tablen_name.file_extension. I am not sure if the file extension is the same for every table type or not (mine are .ISDm, .ISM and .frm). Something liek this (minimally tested) ## Code is to be considered untested. ## This assumes that each $i* has only one table name and no other ch +aracters in it $mysql_table_path = "Your path to your databases"; $dbname="The name of the database"; $db_extension="The file extension for your database type"; for $i (0 ..$#tables_to_query) { $filepath = "$mysql_table_path/$dbname/" . $tables_to_query[$i] ." +.$db_extension"; if (-e $filepath) { $sql = "select count(*) from ".$tables_to_query[$i].";"; $sth = $dbh->prepare($sql) or die("Could not prepare!" . $dbh +->errstr); $sth->execute() or die("Could not execute!" . $dbh->errstr); $total = $sth->fetchrow_array(); print "Total $i is $total\n"; } } [download] You can find the path to the db directory in the configuration file, either "/etc/mysql/my.cnf" or "/etc/my.cnf". If might be somewhere else, but I have always found it in one of these places. The line you want is the "datadir=" line. If all of your tables are the same database type, then they should all have the same extensions (as far as I know). `[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid` [download] It works for me on my system, but I do not have the number of tables that you have, and probably not the same setup. This is obviously black magic, as I know of no guarentee that the tables will always be exposed like this, but it it does work for me. Otherwise, your best bet (and the best solution) sounds like you need to trap the errors.	[reply] [d/l] [select]
Re: Best Perlish way to test if a mysql table exists? by stevecomrie (Beadle) on Feb 01, 2005 at 15:24 UTC
`my %tables; $tables{$_} = scalar( @{$dbh->selectcol_arrayref("SHOW TABLES LIKE +'$_'")} ) foreach ( @tables_to_query );` [download] will result in the %tables hash having either a 1 or 0 for whether or not each table in @tables_to_query exists.	[reply] [d/l]


Just another Perl shrine
	PerlMonks