RE: [sqlite] moving from mySQL to SQLite - A guidance request
> -Original Message- > From: Darren Duncan [mailto:[EMAIL PROTECTED] > Sent: Friday, March 19, 2004 2:29 PM > To: SQLite > Subject: RE: [sqlite] moving from mySQL to SQLite - A guidance request > > > At 9:47 AM -0600 3/19/04, Fred Williams wrote: > >You have raised some vaild concerns for sure. But, the rest of the known > >SQL database universe (Oracle, SQL Server, MySQL, Advantage, > Access(?), and > >etc.) does it the other way round. The only reason I prefer the > change is > >to allow for greater cross platform migration. > > > >If there are those who obviously have a great deal invested in > the current > >implementation, then something like a sticky PRAGMA would be a very good > >solution. This would allow the existing code base be protected, > but future > >migration to be more "industry standard." In the old IBM days > the "c.foo" > >would require a "gray area" in the manual:-) > > If the main issue at hand is cross-database compatability, then there > is a good method that already exists as defined by the SQL standards > - "AS". If one were to use that for every returned column in the > select list then you would have the same result using this on any SQL > database. Moreover, using AS all the time allows a type of > uniformity on selects that contain arbitrary expressions in the > select list, which typically use AS anyway. And for people such as > myself who are inclined to generate our SQL from a data dictionary, > making it generate AS all the time is trivial. Those are some > thoughts. -- Darren Duncan > And I guess we could just use other databases that actually conform to a wide spread standard and just forget about SQLite. This little "feature" is the only thing keeping me from carrying SQLite implementations beyond "trivial." I guess I spent too many years having to avoid the grey areas in IBM manuals, to want to do that again. Lets see now, I have a multi thousand line SQL based implementation based in another database. I am considering switching platforms. To move to one I basically move the bare SQL over and go. With my other possible platform, I must track down, modify every occurrence of a returned column name to implement "AS", test and debug all this, and... Guess what I'm going to do... Fred - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] moving from mySQL to SQLite - A guidance request
> -Original Message- > From: Darren Duncan [mailto:[EMAIL PROTECTED] > Sent: Friday, March 19, 2004 12:39 AM > To: SQLite > Subject: RE: [sqlite] moving from mySQL to SQLite - A guidance request > > > On Fri, 19 Mar 2004, Fred Williams wrote: > > Yes, about that c.firstname thing... > > > > Do we need to turn in some kind'a formal "enhancement request?" > Seems the > > feeling of those responding was that a configurable option was > most desired. > > Perhaps a PRAGMA? > > > > So Richard, in all your spare time, what'd y'a think? > > I would say that if this were implemented then is should indeed be PRAGMA > triggered and not be the default option. A default of "leave it the way > you found it" as is done now seems best for a default. It's certainly > more descriptive. The "AS" directive in SQL for explicit renaming seems > the best way to rename for serious work. > > Also, an auto-rename as was asked for could have a side-effect of losing > retrieved data in some cases. For example, see the following: > > SELECT a.foo, b.foo ... > > If the "a." and "b." were stripped off, we would have two > returned columns > with identical names. Any application which actually references the > returned data by name and not by column number would only be able to see > one of them. And if the application layer puts a row in an associative > array (a "hash" in Perl talk) of field name/value, then only one column > value would be stored and the other lost. > > So you can see how the requested new behaviour would only be useful in > specialized cases and isn't good for a default. > > On the other hand, a good way around this problem is that the SQL parser > will throw an error when said behaviour is used, in situations where > the SQL writer does what I demonstrated, citing the fact that > multiple same-named return value columns aren't allowed, requiring the > application writer to use "AS" at least once to get around the error. You have raised some vaild concerns for sure. But, the rest of the known SQL database universe (Oracle, SQL Server, MySQL, Advantage, Access(?), and etc.) does it the other way round. The only reason I prefer the change is to allow for greater cross platform migration. If there are those who obviously have a great deal invested in the current implementation, then something like a sticky PRAGMA would be a very good solution. This would allow the existing code base be protected, but future migration to be more "industry standard." In the old IBM days the "c.foo" would require a "gray area" in the manual:-) We all hope, as users and developers, that SQLite will continue to evolve and gain acceptance, but realistically I don't see Oracle et all coming around to SQLite's way of thinking on this one! Fred - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] moving from mySQL to SQLite - A guidance request
At 3:12 PM -0500 3/18/04, rich coco wrote: I have a legacy app that uses mySQL as the embedded RDBMS. The app makes explicit invocations to mysql library functions. There is no ODBC layer being used at all. What about existing SQL statements (explicit arguments to mysql function calls) that use SQL keywords/contructs (eg, REGEXP) not supported by SQLite? Is there a best-practices way of doing this kind of transition from one DB to another? advice welcome. What programming language is your application written in? Depending on your answer, there may be libraries out there for said language that will translate the SQL and/or related functionality for you by emulating one database on top of another. (I'm also making one such thing in Perl right now, with other language versions to follow later.) -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
