Recipe 4.5. Generating Unique Table Names


Problem

You need to create a table with a name that is guaranteed not to exist already.

Solution

If you can create a TEMPORARY table, it doesn't matter if the name exists already. Otherwise, try to generate a value that is unique to your client program and incorporate it into the table name.

Discussion

MySQL is a multiple-client database server, so if a given script that creates a transient table might be invoked by several clients simultaneously, you must take care to keep multiple invocations of the script from fighting over the same table name. If the script creates tables using CREATE TEMPORARY TABLE, there is no problem because different clients can create temporary tables having the same name without clashing.

If you do not want to use a TEMPORARY table, you should make sure that each invocation of the script creates a uniquely named table and that it drops that table when it is no longer needed. To accomplish this, incorporate into the name some value that is guaranteed to be unique per invocation. A timestamp won't work, because it's easily possible for two instances of a script to be invoked within the same second. A random number may be somewhat better. For example, in Java, you can use the java.util.Random⁠(⁠ ⁠ ⁠) class to create a table name like this:

import java.util.Random; import java.lang.Math; Random rand = new Random (); int n = rand.nextInt ();  // generate random number n = Math.abs (n);         // take absolute value String tblName = "tmp_tbl_" + n; 

Unfortunately, random numbers only reduce the possibility of name clashes, they do not eliminate it. Process ID (PID) values are a better source of unique values. PIDs are reused over time, but never for two processes at the same time, so a given PID is guaranteed to be unique among the set of currently executing processes. You can use this fact to create unique table names as follows:

Perl:

my $tbl_name = "tmp_tbl_$$";

Ruby:

tbl_name = "tmp_tbl_" + Process.pid.to_s

PHP:

$tbl_name = "tmp_tbl_" . posix_getpid ();

Python:

import os tbl_name = "tmp_tbl_%d" % os.getpid ()

Note that even if you create a table name using a value such as a PID that is guaranteed to be unique to a given script invocation, there may still be a chance that the table will already exist. This can happen if a previous invocation of the script with the same PID created a table with the same name, but crashed before removing the table. On the other hand, any such table cannot still be in use because it will have been created by a process that is no longer running. Under these circumstances, it's safe to remove the table if it does exist by issuing the following statement:

DROP TABLE IF EXISTS tbl_name             

Then you can go ahead and create the new table.

Connection identifiers are another source of unique values. The MySQL server reuses these numbers over time, but no two simultaneous connections to the server have the same ID. To get your connection ID, execute this statement, and retrieve the result:

SELECT CONNECTION_ID(); 

Some MySQL APIs expose the connection ID directly without requiring any statement to be executed. For example, in Perl DBI, use the mysql_thread_id attribute of your database handle:

my $tbl_name = "tmp_tbl_" . $dbh->{mysql_thread_id}; 

In Ruby DBI, do this:

tbl_name = "tmp_tbl_" + dbh.func(:thread_id).to_s 




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net