Re: Tables in a database, mode ANSI

Jonathan Leffler wrote: > > On Fri, 5 Sep 1997, Peter Lancashire wrote: > > Nils Myklebust wrote: > > > Mark Fisher <fisherm@britannic.co.uk> wrote: > > > > > > :Is it possible to have 2 tables of the same name but owned by > > > :2 different users on 1 database? > > > > > > You can in a mode ansi database, but not in a regular Informix one. > > > I wouldn't ever set up a database as mode ansi. The problems are too > > > many. > > > > I used to use mode ANSI but I was forced to abandon it at 7.10. The > > benefit of allowing separate user schemas, which you need, comes with a > > heavy price. > > > > 1. 4GL 6.02 will not compile some forms. > > Can you give an example? The version on SCO I had would not. I took it up with technical support. The bug seemed to have existed in some versions for some platforms. The symptom was a core dump with no obvious common factor. > > > 2. User names are stored in upper case. > > Only if you don't treat them correctly. ANSI mandates the upper-case > treatment, but there's a get-out clause -- if the owner name is in quotes > (single quotes, please), then it is treated exactly as written. It isn't > an accident that programs such as dbexport use the notation: > > create table 'username'.tablename ... > > (And if it uses double quotes, make sure you never have DELIMIDENT set when > you run the dbimport command!) If only it were this simple! Tables are not the only objects. What about constraints? These are named automatically in upper case. I presume you can get around this with the CREATE SCHEMA statement but I have not tried it. > > > No problem, you might think, but USER is in lower case on Unix so all > > your user-selected views fail... This is the killer. Sorry, not quite what I meant. On Unix, USER is lower case and will not compare equal with automatically stored usernames. Getting around automatic storage is difficult. The reverse solution is not readily available because of the lack of an UPPER function to do UPPER(USER). OK, I know there is a SPL function on IIUG but it's slow and should have been done by Informix. > > Only if you write: > > SELECT * FROM username.table> > If you treat owner names carefully, then you write: > > SELECT * FROM 'username'.table> > and everyuthing works fine. If your forms do not use the correct table > alias scheme (as documented in the manuals): > > TABLES > table = 'username'.table > > then it won't compile forms, you're quite correct. But if you do use the > correct alias notation, then there shouldn't be any problems. > Yes, I read the manuals and did all this but still ran up against the bug. A core dump is not ANSI standard, I think. > > I understand Informix themselves have problems with mode ANSI. > > Not that I know of. It works fine as long as you understand what it means. > I don't recommend that people use MODE ANSI databases if they do not have a > very disciplined approach to handling databases, but they work perfectly > well and provide some useful features. > Perhaps you should tell your technical support people that you have no problems with mode ANSI? It was they who told me. My previous 4.0 databases religiously used owner naming and I found it a useful feature. It was deliberately broken in version 7.1 with no easy workaround. I do not consider a total rewrite, quoting everything to be an easy workaround. > > Strange how the manuals show no examples using it. > > The manuals use the stores database for most examples, and the default > stores database is not even logged, let alone MODE ANSI. > Time for a change so stores can show more features in action? > > I suspect it is a tick-list feature. > > Only in part. It is crucial for things like TPC benchmarks too. And for > people who require standards conformance, and for people who want to be > sure that their database runs as securely as possible. > > > Why can't Informix let us have the bits of mode ANSI separately? > > The product is quite complicated enough without adding phased MODE ANSI > compliance. > Maybe, but from the outside, some of these features seem to have little in common, so I presume the internal code is also well structured. In that case, separating the contents of the rag-bag should not be too difficult. :-) > > At the moment they are a rag-bag of things thought to be needed for ANSI > > compliance. > > I disagree with the terms 'rag-bag' and 'thought'. The products have been > tested for ANSI compliance at SQL-89 and SQL-92 entry-level. The features > are needed to attain that level of compliance. > > > There are a vast number of other things needed, such as real schemas and > > catalogs and tons of extra syntax and semantics... > > I think you're referring to the higher levels of SQL-92. I agree that they > would be desirable, but none of the other major database vendors has full > compliance to the top SQL-92 level as yet. I don't think that's a > particularly good reason for Informix not to get there -- in fact, it would > be a useful tick box item to have full compliance, and it would probably > force other people to match us -- but that is separate from the issue of > the MODE ANSI database behaviours. > > Yours, > Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> See my comments above. In summary, I agree that it is probably possible to make mode ANSI work. However, there are benefits to the various parts of it which are denied to customers who do not want the whole inconvenient lot. The useful and uncomplicated owner naming in version 4 mode ANSI has been broken by the new case shifting behaviour. The business of SQL92 compliance is complex. Informix has implemented entry level. They have also implemented some very useful parts of the other two levels. Strangely, they have implemented some parts in a non-standard way, eg constraint names and datetimes. The selection of omissions (such as UPPER) is baffling. Peter -- Peter Lancashire Mail: Peter.Lancashire.PL1@bayer.co.uk Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 All opinions are my own and not those of Bayer plc.