Silly question time -- with answer (long)

This is a multi-part message in MIME format. --------------2F223D0C75A5 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I hope you can read the attachment. If not, I'll resubmit this message. Yours, Jonathan Leffler (jleffler@earthlink.net) #include <many-aliases.h> --------------2F223D0C75A5 Content-Type: text/plain; charset=iso-8859-1; name="TABID.txt" Content-Transfer-Encoding: 8bit Content-Disposition: inline; filename="TABID.txt" Silly question time: how do you determine the value of TabID for a given table in an Informix database? The basic answer is as simple as the question: SELECT TabID FROM SysTables WHERE TabName = 'tablename';So, what's Leffler thinking about when he asks such a simple question with such an obvious answer? And how on earth does he manage to write such a long message when he's already given the answer? There are several complicating factors: ? Consider a MODE ANSI database. ? Consider DELIMIDENT (no, on second thoughts, try not to consider DELIMIDENT). ? Consider owner names with quotes; consider owner names without quotes;. ? Consider remote databases. ? Consider whether the components of the name were typed in all lower case, all upper case, or in some mixture of cases. I'm trying to add the INFO statement to my general purpose SQL command in- terpreter, SQLCMD, and that forces me into wondering exactly how to handle all the following table names, in both MODE ANSI and non-ANSI databases: ? INFO INDEXES FOR tablename ? INFO INDEXES FOR TableName ? INFO INDEXES FOR TABLENAME ? INFO INDEXES FOR owner.tablename ? INFO INDEXES FOR OWNER.TABLENAME ? INFO INDEXES FOR 'owner'.tablename ? INFO INDEXES FOR 'owner'.tablename ? INFO INDEXES FOR database:tablename ? INFO INDEXES FOR database@server:tablename ? INFO INDEXES FOR database{x}@{y}server{z}:{p}'owner'{q}.{r}.TABLENAME The context means that this is an ESQL/C program, and the table name as a whole has been read into a character string. The various components of the table name are also available in separate string variables. If there are quotes around the owner name, they have been preserved. The comments in the last example have been duly ignored, as have any spaces, tabs, and newlines anywhere in the commands. The remote database part is much the easiest to handle; you have to prefix the SysTables part of the SELECT statement with whatever database name (and optional server name) is provided in the table name that you are processing. You cannot have a server name specified without a database name too. The only place where the notation '@server' is allowed is in a CONNECT statement, and it does not specify a table name. Both MODE ANSI and non-ANSI database allow you to specify the owner of tables. MODE ANSI databases require you to do so when you don't own the table. Con- sequently, it is best to write either 'informix'.SysTables or 'infor- mix'.SysTables in place of SysTables in the original solution. If you consider DELIMIDENT, it is best to use the double-quote notation. The user ID should be a delimited identifier rather than a string (and if you don't understand that, just remember to use double quotes around the owner name for absolute safety). Note that the capitalization of SysTables doesn't matter; it's a Lefflerian idiosyncrasy to capitalize the system catalogue names that way. Handling the various ways of capitalizing the tablename is easy enough. The name should be case-converted to all lower case, unless DELIMIDENT is in effect and the table name is enclosed in double quotes, whereupon it must be left ex- actly as it is. Ignoring the issues of table ownership, we're left with code that looks somewhat like: if (database and server specified) then prefix = 'database@server:'; else if (database specified) then prefix = 'database:'; else prefix = ''; systab = prefix || '''informix''.SysTables'; tablename = LOWER(tablename); query = 'SELECT TabID FROM ' || systab || ' WHERE TabName = '' || tablename || '''; This will yield the correct answer in a non-ANSI database if the table exists and the owner was not specified. In a MODE ANSI database, it is only correct if the user owns the table, so for a MODE ANSI database, the query should be: query = 'SELECT TabID FROM ' || systab || ' WHERE TabName = '' || tablename || '' AND Owner = USER; Table ownership is the big complicating factor. When the owner name is specified in (single or double) quotes, the query is simple. To avoid problems in the pres- ence of DELIMIDENT, the code strips the quotes from the quoted owner name and embeds the result in single quotes in the query: Owner = stripquotes(owner); query = 'SELECT TabID FROM ' || prefix || '''informix''.SysTables WHERE TabName = '' || tablename || '' AND Owner = '' || owner || '''; Even that wasn't too complex, but unquoted owner names are murder. In a MODE ANSI database, the unquoted name is case-converted to upper case; in a non-ANSI database, the unquoted name is case-converted to lower case. In a MODE ANSI database, there could be two tables with the same basic table name, but with different owner names, one in upper case and one in lower case: CREATE TABLE 'user1'.tablename ('); CREATE TABLE 'USER1'.tablename ('); In a MODE ANSI database, if the user writes user1.tablename, then the relevant table is the second of the two. By contrast, in a non-ANSI database, you could not create the second table, but even if you could, the first one would be the relevant one. A MODE ANSI database could also contain other tables with the same basic table name but with mixed case owner names, but those owner names must be specified in quotes. Given all these complexities, is there a reasonably simple algorithm that can determine the TabID for an arbitrary table name? Yes, but it is not one that can be written sensibly in pure SQL, so there isn't a single SQL statement which will do the trick. The algorithm that follows does derive the TabID in no more than two SQL statements. The examples that follow ignore the database and server information as they add minimal extra complexity. 1. Validate whether the table exists by preparing 'SELECT * FROM fulltablename'. If this works, the table exists and can be located using just the information in fulltablename. This avoids some problems with MODE ANSI databases when no owner name is given. 2. If the owner name is specified in quotes, then the follow-on query is quite simple. In fact, if the owner name is given in quotes, you do not have to do the first query, but it is better to do so as the error condition gives the correct missing table name regardless of the type of database. SELECT TabID FROM 'informix'.SysTables WHERE TabName = 'tablename' AND Owner = 'owner'; 3. If the owner name was specified without quotes, the first row returned by the following query is the relevant TabID: SELECT TabID, Owner FROM 'informix'.SysTables WHERE TabName = 'tablename' AND Owner IN ('owner', 'OWNER') ORDER BY Owner; If it is a MODE ANSI database and both the 'OWNER'.tablename and 'owner'.tablename tables exist, then the 'OWNER' entry will appear before the 'owne