Re: [sqlite] Version 3.0.0 ALPHA now available
Any chance of getting Correlated Subqueries, including the EXISTS / NOT EXISTS clause ? And since it is open season, could we have user defined expression evaluation functionality (other than user defined compares that 3.0 already has). For example arithmetic, substring etc ? "D. Richard Hipp" <[EMAIL PROTECTED]> To 18/06/2004 13:33 [EMAIL PROTECTED] cc Subject [sqlite] Version 3.0.0 ALPHA now available Version 3.0.0 (alpha) of SQLite is now available on the website. http://www.sqlite.org/ http://www.sqlite.org/version3.html http://www.sqlite.org/capi3.html This is an alpha release intended for testing and evaluation only. Version 3 is not yet ready for production use. A file format and API freeze is scheduled for July 1. Users are encouraged to evaluate this release and make suggestions on how to improve the interface prior to that date. Anonymous CVS access has been reenabled. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] SQLite version 3 design question: '500'=500?
Not only comparison, but all expressions - arithmetic etc. [EMAIL PROTECTED] com To 13/05/2004 16:33 [EMAIL PROTECTED], [EMAIL PROTECTED] cc [EMAIL PROTECTED] Subject RE: [sqlite] SQLite version 3 design question: '500'=500? I think the point the SQL assumes is that everything is a string unless you tell it otherwise. How you tell it otherwise can be explicitly done or implicitly done. SQLite seems to be making implicit data conversions when in fact the user may mean something else. Therefore each implicit data conversion should also have an explicit data conversion to tell SQLite EXACTLY how to interpret this data type. Most relational systems have an enforced schema that dictates each allowable data type and how these data types can be converted from one type to another. SQLite which is basically type less, should allow us to add functions to overload the comparison operators so that we could convert/compare explicitly for each of our custom data types since we know exactly what we want . This should be optional and would probably impact performance but at least we would get the right answer whenever we queried for the custom data types that we have defined. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, May 13, 2004 11:18 AM To: Fred Williams Cc: [EMAIL PROTECTED] Subject: RE: [sqlite] SQLite version 3 design question: '500'=500? Not terribly interested in pursuing this, but that is precisely the point - letting the engine handle the 'technical' things means it needs to know the types of data in it. If the engine doesn't do it, the user has to do it, adding complexity and producing less reliable systems. "Fred Williams" <[EMAIL PROTECTED] on.net>To <[EMAIL PROTECTED]> 13/05/2004 15:12 cc <[EMAIL PROTECTED]> Subject RE: [sqlite] SQLite version 3 design question: '500'=500? I guess you read different books than I. The intent was to have the language engine do the required type conversions freeing the user from knowledge of such "technical" things. Like all languages it evolved, and has not remained "simple." Fred > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 13, 2004 8:51 AM > To: Fred Williams > Cc: [EMAIL PROTECTED] > Subject: RE: [sqlite] SQLite version 3 design question: '500'=500? > > > > > > > > Funny, I thought SQL was a more down to earth version of E. F. Codd's > mathematical model 'Relational Algebra', with some concepts taken from > Relational Calculus. > > To say types and strong typing weren't part of the original SQL concept > isn't right. Typing gives data semantics. > > Vijay > ... - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation
RE: [sqlite] SQLite version 3 design question: '500'=500?
Ditto that. [EMAIL PROTECTED] com To 13/05/2004 16:33 [EMAIL PROTECTED], [EMAIL PROTECTED] cc [EMAIL PROTECTED] Subject RE: [sqlite] SQLite version 3 design question: '500'=500? I think the point the SQL assumes is that everything is a string unless you tell it otherwise. How you tell it otherwise can be explicitly done or implicitly done. SQLite seems to be making implicit data conversions when in fact the user may mean something else. Therefore each implicit data conversion should also have an explicit data conversion to tell SQLite EXACTLY how to interpret this data type. Most relational systems have an enforced schema that dictates each allowable data type and how these data types can be converted from one type to another. SQLite which is basically type less, should allow us to add functions to overload the comparison operators so that we could convert/compare explicitly for each of our custom data types since we know exactly what we want . This should be optional and would probably impact performance but at least we would get the right answer whenever we queried for the custom data types that we have defined. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, May 13, 2004 11:18 AM To: Fred Williams Cc: [EMAIL PROTECTED] Subject: RE: [sqlite] SQLite version 3 design question: '500'=500? Not terribly interested in pursuing this, but that is precisely the point - letting the engine handle the 'technical' things means it needs to know the types of data in it. If the engine doesn't do it, the user has to do it, adding complexity and producing less reliable systems. "Fred Williams" <[EMAIL PROTECTED] on.net>To <[EMAIL PROTECTED]> 13/05/2004 15:12 cc <[EMAIL PROTECTED]> Subject RE: [sqlite] SQLite version 3 design question: '500'=500? I guess you read different books than I. The intent was to have the language engine do the required type conversions freeing the user from knowledge of such "technical" things. Like all languages it evolved, and has not remained "simple." Fred > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 13, 2004 8:51 AM > To: Fred Williams > Cc: [EMAIL PROTECTED] > Subject: RE: [sqlite] SQLite version 3 design question: '500'=500? > > > > > > > > Funny, I thought SQL was a more down to earth version of E. F. Codd's > mathematical model 'Relational Algebra', with some concepts taken from > Relational Calculus. > > To say types and strong typing weren't part of the original SQL concept > isn't right. Typing gives data semantics. > > Vijay > ... - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements
RE: [sqlite] SQLite version 3 design question: '500'=500?
Funny, I thought SQL was a more down to earth version of E. F. Codd's mathematical model 'Relational Algebra', with some concepts taken from Relational Calculus. To say types and strong typing weren't part of the original SQL concept isn't right. Typing gives data semantics. Vijay "Fred Williams" <[EMAIL PROTECTED] on.net>To "Darren Duncan" 13/05/2004 14:23 <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> cc Subject RE: [sqlite] SQLite version 3 design question: '500'=500? I seem to remember many, many moons ago when studying SQL for the first time, learning that SQL syntax treats everything as character data no matter what the DB column data type is. Also, I think this was to make the SQL syntax more "natural English language" like. I believe the original intent of SQL syntax was to be simple enough even management could understand it. Naturally that didn't work :-) Fred > -Original Message- > From: Darren Duncan [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 13, 2004 3:39 AM > To: [EMAIL PROTECTED] > Subject: Re: [sqlite] SQLite version 3 design question: '500'=500? > > > At 8:19 PM -0400 5/12/04, D. Richard Hipp wrote: > >The development team is making progress on SQLite version 3.0. > >But we've run across an interesting puzzle. What should be > >returned by this: > > > > SELECT '500'=500; > > > >Is the result "0" or "1"? In other words, what happens when > >you compare a number to a string that looks like that number. > >Are they equal or not? > > In my experience, strings are implicitely cast as numbers when used > in a numerical context, which includes comparisons with either > literal numbers or numerical columns. So I vote for the above two > items having the result "TRUE". > > Take this for example, pretending that "bar" is a numerical column > that does not have a uniqueness constraint. > > INSERT INTO foo (bar) VALUES (5); > INSERT INTO foo (bar) VALUES ('5'); > > SELECT bar FROM foo WHERE bar = 5; > SELECT bar FROM foo WHERE bar = '5'; > > With most database engines that I'm aware of, both of the insert > statements will work whether the column is a string or a number, and > both select statements will return 2 rows (assuming table empty > before we started). That just seems normal. > > Also, such behaviour will benefit lazy programmers that generate SQL > from code because they can just put '' around all literal values > regardless of the data type, and it will just work. > > As for what the SQL standard says ... > > For reference: > SQL-2003 Foundation, 6.12 "", p201 > SQL-2003 Foundation, 8.2 "", p375 > ... - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] suggestion
Don't know about ALTER TABLE, but having native corelated subqueries support would save on code and possibly speed up getting at the end result greatly. Vijay - Forwarded by Vijay S Tenneti/Europe/Group 1 Software Europe Ltd on 21/04/2004 18:46 - Puneet Kishor <[EMAIL PROTECTED] org> To [EMAIL PROTECTED] 21/04/2004 16:23 cc [EMAIL PROTECTED], [EMAIL PROTECTED] Subject Re: [sqlite] suggestion On Apr 21, 2004, at 8:37 AM, [EMAIL PROTECTED] wrote: > > Puneet Kishor wrote: > >> my guess is because it can be done other ways (see the docs on this >> specifically), and the idea is to keep SQLite as simple as possible. >> The >> more "conveniences" that are added to it, the more complicated it will >> become. > >> Usually, once the database is set, there is little reason to ALTER >> it... >> it all boils down to ROI... > > I beg to differ concerning the last point. Adding MORE sql commands is > not a > convenience Basil, I am simply speculating. Why really ALTER TABLE doesn't exist only Richard can definitively answer. That said, in the spirit of discussion, this thread could possibly influence Richard (or not). So, here is my take -- > but rather can save hundreds to thousands of lines of code!!! I have > been > programming in I can't visualize a scenario where an ALTER TABLE command would save 100s-1000s of lines of code, but that could be my failing. According to the docs (and from my basic SQL knowledge) it seems I would save about 5 lines of code if I had an ALTER table command. If I am using a scripting language, it would be a suitable function that I could call at will. That said, would I welcome an ALTER TABLE command? Absolutely. I use databases primarily as data stores for web apps, and I am constantly modifying the tables because I am a firm adherent to the "design by the seat of my pants" school. I find MS-Access to be a particularly wonderful database for web app development. It offers easy and convenient modification to tables, a diagramming view where I can make or break relationships, quick querying, etc. Once everything is done, I can always migrate the db to SQL Server or Oracle or MySQL. I sincerely wish there were something like that on my Mac. The new version of Filemaker offers a really nice relationship builder interface, its other shortcomings notwithstanding. SQL Server's Enterprise Manager is nice, but not quite. And Oracle's Java tools, at least the ones I have used... well, they defy the definition of good taste and sensibility. Suffice it to say that I would not have survived without TOAD. The thing here is... all these products take a long time to develop. They are expensive -- even Access and FM Pro are $300+. Richard's product is free. I am not quite sure how he makes money doing this, but I am very glad for his gift to us all. SQLite is wonderful, but given what I assume are few resources, my sense is that he is applying it toward developing the basic capabilities of the database, especially if certain "convenient" tasks can be done via not so cumbersome alternatives. Btw, I have a question that I can't find an answer to -- Is Richard the only developer of SQLite? There are many very knowledgeable sounding C and db type folks on this list, but I would be curious to know more about the "core developers" of SQLite, if there are any besides him. I would like to know this for my own gratification, for knowing who to thank in my prayers, and also as background knowledge on SQLite when trying to justify it for a project. Regards. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Timeframe for 3.0 ?
Hi, We're considering the use of SQLite in the next version of our Doc1 product. SQLite does seem the ideal embedded database for us to use. However, there seem to a couple of thorny issues, and can someone please throw some light on they could possibly be resolved: 1. Our product is Unicode (in fact we recently ported the code over from DBCS). I see from the ongoing discussions that supporting Unicode is something that is planned for SQLite 3.0. So what is the timeframe for 3.0 ? How easy / difficult may it turn out to be to change the code ourselves ? We may need to start the work very soon. 2. Our product allows users to write logic by dragging and dropping visual elements on the front end - and as part of that, we have our own datatypes (for example Integer, Fixed Point Number (proprietary format), String, Date, Time). So the fact that SQLite is typeless may turn out to be an advatage - but we would need to 'override', for example, arithmetic and comparison functions to use our own code. How easy or difficult may that turn out to be ? I probably should mention that our runtime engine (virtual machine) runs on a multitude of platforms from Windows to several flavours of Unix to MVS. Thanks a lot, Vijay - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Exists keyword and corelated subqueries
Hi, Can anyone please tell me if 'EXISTS' is supported (for subqueries in the WHERE clause) and also if corelated subqueries are supported ? One more thing: on the 'features not supported' page, the second point mentions variables not being supported in queries: are these 'corelation variables' (also called range variables), or are they host variables ? Thanks a lot, Vijay - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]