Re: Is it just me, or is Sqlite3 goofy?
Fredrik Lundh wrote: ... dynamic typing != random typing. So true. To get a really good random typing going, you need a cryptographically strong random number generator to feed the application of type constructors to values during the execution of a program. Perhaps the best way to do this is to modify the the interpreter. We could call the new language Python?!, or actually use an interobang if Unicode has such a character. --Scott David Daniels [EMAIL PROTECTED] -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Paul Boddie wrote: Well, if the client is free not to bother signalling anything about erroneous value types, one has to wonder why there's so much of a specification. If you read it, I think you'll notice that the committee has managed to produce a lot of text without spending too much ink on error handling. I'm not completely up to date with the latest standards, but last time I looked, a lot of things that are essential parts of all serious implementations were missing from the standards. I'm pretty certain it would overwhelm the SQL standards committee if they had to specify how error conditions are handled. It would certainly be useful though. E.g. is PostgreSQL SQL compliant in forcing a rollback as soon as an operation fails. E.g. in Oracle, you can have a transaction with a loop where you try to insert values, and fall back to updating instead if you get a duplicate key on insert error. In PostgreSQL you need to set a savepoint before the the insert and rollback to that. Does the standard say whether this is kosher? I think not. -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Bryan Olson wrote: Ben Sizer wrote: It's not a crackpot theory. It's a completely reasonable theory. SQL is based on relational algebra, which provides a mathematical set of operators for grouping data that is stored in separate sets. That data is selected and projected according to its value, and nothing else. The concept of it having a 'type' has been overlaid on top of this, presumably to facilitate efficient implementation, which tends to require fixed-width rows (and hence columns). It's not necessary in any sense, and it's reasonable to argue that if it was trivial to implement variable width columns as efficiently as fixed width columns, that explicit data types might never have needed to exist. The mathematical definition of the relational model includes that data values are drawn from specific sets. Well, I did say relational algebra, which from what I understand predates the official 'relational model'. Implementing variable width columns has nothing to do with it. On a practical level, it has lots to do with it! Here's the reference: 1.3. A Relational View of Data The term relation is used here in its accepted mathematical sense. Given sets S1, S2, ···, Sn, (not necessarily distinct), R is a relation on these n sets if it is a set of n-tuples each of which has its first element from S1, its second element from S2, and so on [1]. We shall refer to Sj as the jth domain of R. Does it specify anywhere that sets S1...Sn cannot each be the universal set? To put it another way - although the spec implies the existence of limited set domains, and data types enforce limited domains, I don't think a requirement to allow limited domains is a requirement for static data types. -- Ben Sizer -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Ben Sizer wrote: Bryan Olson wrote: Ben Sizer wrote: It's not a crackpot theory. It's a completely reasonable theory. SQL is based on relational algebra, which provides a mathematical set of operators for grouping data that is stored in separate sets. That data is selected and projected according to its value, and nothing else. The concept of it having a 'type' has been overlaid on top of this, presumably to facilitate efficient implementation, which tends to require fixed-width rows (and hence columns). It's not necessary in any sense, and it's reasonable to argue that if it was trivial to implement variable width columns as efficiently as fixed width columns, that explicit data types might never have needed to exist. The mathematical definition of the relational model includes that data values are drawn from specific sets. Well, I did say relational algebra, which from what I understand predates the official 'relational model'. Relational algebra got into it when you said SQL is based on relational algebra. SQL is based on the relation model. Incidentally SQL's expressions are closer to relation calculus than to relational algebra. Furthermore, relation algebra does deal with types. One can, for example, infer the type of a result from the type of the operands. Implementing variable width columns has nothing to do with it. On a practical level, it has lots to do with it! There are subtler points on that, but it's not the issue here. Here's the reference: 1.3. A Relational View of Data The term relation is used here in its accepted mathematical sense. Given sets S1, S2, ···, Sn, (not necessarily distinct), R is a relation on these n sets if it is a set of n-tuples each of which has its first element from S1, its second element from S2, and so on [1]. We shall refer to Sj as the jth domain of R. Does it specify anywhere that sets S1...Sn cannot each be the universal set? No; it indicates that they can be different, and when they are, value not of the given set are not legal. To put it another way - although the spec implies the existence of limited set domains, and data types enforce limited domains, I don't think a requirement to allow limited domains is a requirement for static data types. One might argue that a one-atom-type-only DBMS can still be relational, but that's not what SQLite offers. As we've seen, one does declare the type of a column, and the DBMS seems to prefer the stated type in that it will sometimes convert values of other types. -- --Bryan -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Tim Chase [EMAIL PROTECTED] wrote: To: Steve Holden [EMAIL PROTECTED] But honestly, boss, I didn't write this code! It was my evil alter-ego that puts VARCHAR values containing Gilbert Sullivan lyrics into the Amount_Due CURRENCY fields! Hence the phrase Going for a song? I am the very model of a modern major database, For gigabytes of information gathered out in userspace. For banking applications to a website crackers will deface, You access me from console or spiffy user interface. My multi-threaded architecture offers you concurrency, And loads of RAM for caching things reduces query latency. The data is correctly typed, a fact that I will guarantee, Each datum has a data type, it's specified explicitly. is-it-friday-yet'ly yers, -tkc I love this group - thanks for that - it made my day ... - Hendrik -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
From: Steve Holden [EMAIL PROTECTED] wrote: snip These kids wi' their Oracle databases didn't know they were born. I can remember 'avin' to optimise programs by making sure that the next instruction were comin' under the heads of t' drum just as the last instruction were finishing. But yer tell these young folk nowadays and they just don't believe yer. snip Too true - one of my contemporaries made NCR CRAM units sing - one card dropping, one on the drum, and one flying back up into the stack - Hendrik -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
First of all, anyone with extensive experience in database systems understand that validating and cleaning input is an unavoidable task. Static typing can help identify some of the problems, but far from all, and there is often data processing done before the data enters the database, so it's often too late to do the cleaning at that time anyway. Once you are dealing with data within your system, it's a bug in the software if data doesn't fulfill the intended constraints. Such problems should be identified by unit tests. Mike Owens wrote: Next, as far as transferring you data, you most likely have to resort to some delimited format, or INSERT statements, which is no different than any other database. You can always write a Python script which reads from one database, cleans up the data and inserts into another, one row at a time. This isn't just a porting activity. I've written a Python app that moved data from a DB2 mainframe production database to DB2 testing database on Windows. It moved data from a hierarchy of tables, starting in a given table, and reading foreign keys from system tables (very slow in mainframe DB2!) to figure out what data to bring from which tables. Since the system used abstract keys and moved data to a populated database, it had to generate new keys and adjust the foreign keys in the dependent tables. It also had to work around bugs and quirks in Windows NT, DB2 and ADODBAPI etc. These things are relatively easy, but it's never trivial. There are always a lot of details to deal with. For big volumes, you typically need to use some kind of bulk loading facility. Inserts are generally much too slow. (I'm talking about general data porting woes here--in the case of SQLite it's not likely that you have many million rows of data.) Efficient bulk loading means that you have different data format for different database systems, and also that you need to validate your data before insertion, so the problems directly related to SQLite doesn't seem very odd. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] wrote: What was Richard Hipp's justification for slandering the writers of the SQL Language Specification? First of all, if you read the text you quoted and understand English, you should be able to see that the author of the text is clearly expressing an opinion, not stating a fact. Calling this lies or slander is just absurd. the authors of SQLite feel very strongly that this is a feature The authors argue that static typing is a bug in the SQL specification If you think that these sentences imply some kind objective truth that could be claimed to be a lie, then you are confused about English or logic or both. Is there anything more rude than describling the SQL Language Specification as a bug that needs to be fixed? Using waterheadretard in a posting subject perhaps? Seriously, this is the first time I ever heard anyone being religious about the SQL standard in that way. Also, while I actually find your statement about SQL being a bug rather funny and fitting, it's not what Hipp is saying. He's talking about static typing, which is really only a detail in SQL and has no bearing on the relational theories. Equality and inequality can certainly be defined in a strict way regardless on whether it is columns or values that have a distinct type. The use of NULL as a permitted value for all types mean that SQL has already relaxed the traditional type mechanism a bit. Python, Tcl, SQLite etc goes one step further. To cleanse you from this affliction, I suggest that you read Date and Darwen's A Guide to the SQL Standard. After all, these guys are seriously involved in the SQL standard development, and they are certainly not very religious about it. Among their claims you can find these pearls: SQL in particular is very far from ideal as a relational language, although there are well-established principles for the design of formal languages, there is little evidence that SQL was ever designed in accordance with any such principles, Standard SQL especially is additionally deficient in a number of respects. Many people have claimed through the years that SQL is broken, and that Quel was a much better language, and the only reason that SQL killed Quel and not vice versa was the IBM backing. Richard Hipp is hardly being controversial in this respect... -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Paul Boddie wrote: To be fair, that text originates in section 12.3, referring to input parameters to procedures. Meanwhile, the following text (subclause 13.8, insert statement) appears to be more pertinent: If the data type of the target identified by the i-th column name is an exact numeric type, then the data type of the i-th item of the insert statement shall be an exact numeric type. And SQLite behaves as expected if you provide a numeric value to a numeric column, right? In other words, it complies to that clause. Does the spec state how the database engine should behave if the client breaks the requirements for insert? I guess that this is implementation dependent. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Magnus Lycka wrote: Paul Boddie wrote: To be fair, that text originates in section 12.3, referring to input parameters to procedures. Meanwhile, the following text (subclause 13.8, insert statement) appears to be more pertinent: If the data type of the target identified by the i-th column name is an exact numeric type, then the data type of the i-th item of the insert statement shall be an exact numeric type. And SQLite behaves as expected if you provide a numeric value to a numeric column, right? In other words, it complies to that clause. My 30 seconds' worth of consideration suggests that you have to consider the matter the other way round: if the data type of some column is an exact numeric type, then you should be providing a value of such a type, not a string, for example. Does the spec state how the database engine should behave if the client breaks the requirements for insert? I guess that this is implementation dependent. Well, if the client is free not to bother signalling anything about erroneous value types, one has to wonder why there's so much of a specification. Paul -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Gabriel Genellina wrote: At Tuesday 5/9/2006 16:23, [EMAIL PROTECTED] wrote: I would be surprised if they had never used ANY database. A little thing like dynamic field typing will simply make it impossible to migrate your Sqlite data to a *real* database. Why not? Because it breaks the relational model rules? That's part of it. That model certainly was great 30 years ago, but now things are different. Different only in lite databases. (In fact, you didn't menction the word relational, but I presume you were thinking of that). Even what you call *real* databases have a lot of incompatibilities among them (e.g. ORACLE does not provide an autoincrement type, but has sequences, and so on...). But it was stated in the sqlite docs that ALL SQL databases use static types implying that sqlite will be incompatible with any heavy database should the need arise to migrate upwards. The issue is not that there will be compatibilty problems with any data migration but that the truth is exactly opposite of what's claimed in Section 13.13. I'm not saying sqlite can't be used, what I'm asking for is that the documentation lay the facts out and I'll decide whether I can make this work in my application. Lying about it makes you sound like Microsoft. Of course you could restrict yourself to, by example, SQL92 entry level and be a lot more compatible. But if I'm using a nice OO language like Python which lets me bind *any* object to *any* name, why should be wrong to bind *any* object to *any* database column? But SQL isn't OO, it's relational. That means JOINing tables together on a common field. In theory, due to the comparison hierarchy, it is impossible to do JOINs with dynamic typing since different types can never be equal. In practice, the type affinity kluge trys to work around this but can't do anything if the string doesn't look like an integer when a text field attempts to JOIN to an interger field. Looks a lot more pythonic for me. If all you have is a hammer, everything looks like a nail. Of course, a true object database (like ZODB) is better. Gabriel Genellina Softlab SRL __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Mike Owens wrote: And if you say SQLite misrepresents itself, then what do you say about MySQL, which until version 5 didn't have views or triggers? In fact, it didn't even have subselects until version 4. For a period of years, SQLite had more mainstream SQL features than MySQL. Yet you don't see people going around claiming that MySQL is not an SQL database -- that it's misrepresenting itself. Just to be fair... You do hear many people claiming exactly that, and the primary complaint is often exactly the same one that's being levelled against sqlite here (it's incredibly lax with types and does sometimes mystifying conversions rather than pointing out programmer errors--and yes that's intentionally loaded language that I don't necessarily agree with, it's a common argument though.). The lack of subselects was also a major sticking point for a lot of people, as are other major missing SQL features. Not having used sqlite I can't comment on it in particular. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] wrote: But it was stated in the sqlite docs that ALL SQL databases use static types implying that sqlite will be incompatible with any heavy database should the need arise to migrate upwards. The issue is not that there will be compatibilty problems with any data migration but that the truth is exactly opposite of what's claimed in Section 13.13. I'm not saying sqlite can't be used, what I'm asking for is that the documentation lay the facts out and I'll decide whether I can make this work in my application. Lying about it makes you sound like Microsoft. I thought your qualm was with the pysqlite docs, not the sqlite docs (which apparently do make it plain how the database handles typing)? Also, as others have mentioned, there are a number of ways to ensure type safety, as long as you know how the database works (which as I understand was your original point -- that it should be better documented how it works in the pysqlite docs; and I am inclined to agree -- at least a mention with link to the sqlite docs would be helpful). But given that type safety is not an issue if you use those ways of ensuring it, then the move to a fuller database _will_ be relatively easy. If you don't want to change anything in your database creation/update code ala check constraints, you can always explicitly validate from python, which can be done programatically (very simple example -- you could also use regexp patterns to validate; e.g., string fields not only must be type str, but must not match '^\d+$', c): rows = [ ['1', 'fred', '0051', '/home/fred'], ['2', 'bob', '0054', '/home/bob'], ['3', 'bork', '056', '/home/bork'] ] def validate(row): return [int(row[0]), str(row[1]), int(row[2]), str(row[3])] for i in xrange(len(rows)): rows[i] = validate(rows[i]) # - throws an exception on the third row # database stuff here... Regards, Jordan -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Oops! Sorry for the top-post! -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Marty wrote:
On 9/11/06, Mike Owens [EMAIL PROTECTED] wrote:
I coworker pointed me to this thread.
Joy for us.
snipped good information
In all seriousness, the information you present here is great, and
much appreciated. Your sarcastic, condescending tone kind of gets in
the way of the message, though.
What about jokes on waterheadretard then ?
--
bruno desthuilliers
python -c print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])
--
http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Mike Owens [EMAIL PROTECTED] writes: No it doesn't. If you don't like SQLite's design decisions, write your own embedded relational database, and stop yapping about something you didn't lift a finger to create, but are clearly trying to benefit from. That's silly. The sqlite developers are making all kinds of claims on their web site, in order to attract users and build mindshare and gain the benefits thereof. If the claims aren't valid, it's completely appropriate for others to call attention to it, whether or not they feel like doing anything to fix it. It's just like anything else. If you think your Congressperson is misleading the public about something or pursuing a bad policy, you should speak out about it. That doesn't mean you need to run for Congress yourself. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Mike Owens wrote: Crackpot? And now we get to why I took the flamebait -- wonderfully constructive comments such as this. I know SQLite's author. Besides being a nice and clearly very intelligent person, he also holds a master's degree in electrical engineering from Georgia Tech and a PhD in computer science from Duke University. His crackpot software is used by Sun, Apple, Symbian, Google, AOL, Philips, DLink, and I don't know how many other companies, not to mention countless open source projects such as Mozilla, PHP, and now Python. but is he a member of Mensa? /F -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Steve Holden wrote: Sure. But if you go back to the start of the thread you'll remember the OP was originally complaining that SQLite was being promoted in the Python docs as SQL compliant. It clearly isn't if its response to the insertion of a data value that conflicts with the declared column type is to store a value whose type is something else. the standard actually says If the value of any input parameter provided by the SQL-agent falls outside the set of allowed values of the data type of the parameter /.../ the effect is implementation-defined so that's perfectly SQL92 compliant. in fact, the phrases is implementation-defined and is implementation- dependent occurs hundreds of times in the SQL92 standard. it's far from obvious to me that SQL92 would rule out storing everything as strings (The physical representation of a value is implementation- dependent.) and leaving it to the language binding to map things back to the host language types in whatever way it wants (Each host language has its own data types, which are separate and distinct from SQL data types, the above quotation, and so on). looks like the real problem here is that some people think that implementation-defined means works as it did in that other database I'm using, and not specified by the implementor for each particular SQL-implementation. that's not how standards work; if something's explicitly left undefined, it's not something you can rely on. /F -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Paul Rubin wrote: Mike Owens [EMAIL PROTECTED] writes: No it doesn't. If you don't like SQLite's design decisions, write your own embedded relational database, and stop yapping about something you didn't lift a finger to create, but are clearly trying to benefit from. That's silly. The sqlite developers are making all kinds of claims on their web site, in order to attract users and build mindshare and gain the benefits thereof. If the claims aren't valid, it's completely appropriate for others to call attention to it, whether or not they feel like doing anything to fix it. It's just like anything else. If you think your Congressperson is misleading the public about something or pursuing a bad policy, you should speak out about it. That doesn't mean you need to run for Congress yourself. Though it might improve the country's politics if wanting to be a Congressperson was an absolute disqualification from the job. regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
On 12 Sep 2006 00:15:41 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Just to be fair... You do hear many people claiming exactly that, and the primary complaint is often exactly the same one that's being levelled against sqlite here (it's incredibly lax with types and does sometimes mystifying conversions rather than pointing out programmer errors--and yes that's intentionally loaded language that I don't necessarily agree with, it's a common argument though.). True enough. Yet not a single conversion is undocumented in SQLite, nor is its type affinity. It works exactly as advertised. And MySQL's chief type conversion gotchas -- primarily dates/times I think -- were also clearly addressed in the documentation, and where changed in later versions of MySQL. Then there is, as I think another person pointed out on this thread, Oracle's silent conversion of empty strings to NULLs in VARCHAR fields -- talk about mystifying. What about Oracle's native date format, which also has no relation to the standard? I have little experience with Oracle, but from what I can tell, to get it to display dates in standard (ISO) format, you have to set the NLS_DATE_FORMAT at the session or database level, which requires either additional SQL commands, or administrative intervention. More so-called non-SQL compliant behavior, although hardly a surprise (or even a problem) to someone who is experienced with Oracle. The bottom line: to use *any* database effectively, big or small, one has to read its documentation, not the SQL standard. -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Mike Owens wrote: The bottom line: to use *any* database effectively, big or small, one has to read its documentation, not the SQL standard. note that the SQL standard tells you to read the documentation for the database you're using, in at least 149 places (*). /F *) See Annex B. I only have a draft edition; the number of items in the final version may differ. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
On 11 Sep 2006 21:35:28 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Mike Owens wrote: On 11 Sep 2006 18:23:50 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Can you run your car on diesel fuel? Why not? Because your car's specification says to use gasoline? If your car has been designed to run on diesel, you shouldn't be saying it has gasoline engine. Duh. No but you can still call it a car with an engine, just as SQLite is a SQL database, with an SQL engine. Seperate the data from the engine and what have you got? Data with dynamic typing. Data that can't be migrated to a real SQL database because you'll get type mismatches when strings are inserted into numeric fields. The type affinity kluge won't help there, will it? Did you even read my original post? Or did you just completely miss the point? It's not the job of the System Test Engineer to design things. It's his job to find fault with everything. I just happen to be very good at finding faults with things. And apparently not very good at providing any constructive solutions. But no one appreciates my finding those faults. No one appreciates the tone in which you report these alleged faults, necessarily agrees with the faults that you find, nor elected you system test engineer of the SQLite project. It calls for other things that many databases don't implement or altogether violate as well, so what? Show me how both MS SQL's T-SQL and Oracle's PL/SQL procedure languages are so standards compliant that you can use the same procedure code in both databases. You can't -- precisely because they ignore or outright violate parts of the standard as well. What's your position on that? Do some Googling and you can easily find 18 ways that Oracle's PL/SQL deviates from the standard. And T-SQL is plainly nowhere close. And how many of those systems use dynamic typing? And how many conform to the standard? Name one where the documentation claims the SQL Language Specification is a bug. Name one that conforms to the standard. And a lot of people go to chiropractors. And chiropractors are nice intelligent people with degrees. And the therapy provided does good. Nevertheless, the theory on which it's based is quackery. To use your specious analogy, it represents another way of doing things, which you admit yourself works. That's your justification for calling Richard Hipp a crackpot? It's clear. You're just way too smart for SQLite. Did you see my solution to Rick Shepard's problem in the thread Parsing String, Dictionary Lookups, Writing to Database Table? The point being? -- you can write Python code and feel entitled to condescending and rude? -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Mike Owens [EMAIL PROTECTED] writes: It's not the job of the System Test Engineer to design things. It's his job to find fault with everything. I just happen to be very good at finding faults with things. And apparently not very good at providing any constructive solutions. As he says, it's not his job. But no one appreciates my finding those faults. No one appreciates the tone in which you report these alleged faults, Your tone is not so great either. necessarily agrees with the faults that you find, nor elected you system test engineer of the SQLite project. It's an open source project, as you like to say. Everyone is a test engineer. standard as well. What's your position on that? Do some Googling and you can easily find 18 ways that Oracle's PL/SQL deviates from the standard. And T-SQL is plainly nowhere close. And how many of those systems use dynamic typing? And how many conform to the standard? How many of those deviations are justified in their documentation by the responsible parties claiming, in effect, that they're smarter than the standard's designers? It seems obvious to me that there should, at minimum, be an option to turn this particular nonstandard behavior on and off. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
On 11 Sep 2006 23:29:28 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: But it was stated in the sqlite docs that ALL SQL databases use static types implying that sqlite will be incompatible with any heavy database should the need arise to migrate upwards. The issue is not that there will be compatibilty problems with any data migration but that the truth is exactly opposite of what's claimed in Section 13.13. Implying? There's a solid word. Migrating data from SQLite to other databases is no more difficult or easy than migrating data to any other database. Do you think this is ever trivial? It's as hard or as easy as you make it. No database can just take any schema and the data you put in it and just magically convert that schema/data to flawlessly work in any arbitrary database of your choosing. Some databases have tools to help with this, but they still are not perfect. I'm not saying sqlite can't be used, what I'm asking for is that the documentation lay the facts out and I'll decide whether I can make this work in my application. Lying about it makes you sound like Microsoft. Lying? Whose lying? Where on the website is there a lie about anything? From what I can tell, you've not taken the time to read the documentation or post anything to the mailing list. You've just posted jeremiads on the Python list. Don't like the documentation? Ever volunteered to help out? Ever posted any suggestions on the list or report a bug? Do you really think that open source projects exists to serve you and meet your standards? Do you think that free code and documentation just falls like manna from heaven? Do you honestly think the two core developers of SQLite have some secret agenda to deceive you or the world into using SQLite? Of course you could restrict yourself to, by example, SQL92 entry level and be a lot more compatible. But if I'm using a nice OO language like Python which lets me bind *any* object to *any* name, why should be wrong to bind *any* object to *any* database column? But SQL isn't OO, it's relational. That means JOINing tables together on a common field. In theory, due to the comparison hierarchy, it is impossible to do JOINs with dynamic typing since different types can never be equal. In practice, the type affinity kluge trys to work around this but can't do anything if the string doesn't look like an integer when a text field attempts to JOIN to an interger field. Unless you ensure that the correct types are put int the column to begin with, which is entirely possible with SQLite, as I've already demonstrated. And if that's just too much to bear, you can still do an inner join by explicitly casting the two columns in the join constraint to a common desired type. Want to know how? Read the documentation. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Fredrik Lundh wrote: Steve Holden wrote: Sure. But if you go back to the start of the thread you'll remember the OP was originally complaining that SQLite was being promoted in the Python docs as SQL compliant. It clearly isn't if its response to the insertion of a data value that conflicts with the declared column type is to store a value whose type is something else. the standard actually says If the value of any input parameter provided by the SQL-agent falls outside the set of allowed values of the data type of the parameter /.../ the effect is implementation-defined so that's perfectly SQL92 compliant. To be fair, that text originates in section 12.3, referring to input parameters to procedures. Meanwhile, the following text (subclause 13.8, insert statement) appears to be more pertinent: If the data type of the target identified by the i-th column name is an exact numeric type, then the data type of the i-th item of the insert statement shall be an exact numeric type. I have used SQLite (releases 2 and 3) to my satisfaction, aware of the common knowledge around the limitations (or features) of SQLite with respect to data types. I'd agree with the complainant that the behaviour of SQLite isn't what one would expect, although I started my relational database experience using an Oracle database system and admit that I may have been spoilt, thus saving Mr Holden - a self-confessed Yorkshireman, I believe - the effort involved in pointing out the relative luxury of my professional upbringing. ;-) Paul -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Mike Owens wrote: On 11 Sep 2006 21:35:28 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Mike Owens wrote: On 11 Sep 2006 18:23:50 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Can you run your car on diesel fuel? Why not? Because your car's specification says to use gasoline? If your car has been designed to run on diesel, you shouldn't be saying it has gasoline engine. Duh. No but you can still call it a car with an engine, just as SQLite is a SQL database, with an SQL engine. Seperate the data from the engine and what have you got? Data with dynamic typing. Data that can't be migrated to a real SQL database because you'll get type mismatches when strings are inserted into numeric fields. The type affinity kluge won't help there, will it? Did you even read my original post? Or did you just completely miss the point? It's not the job of the System Test Engineer to design things. It's his job to find fault with everything. I just happen to be very good at finding faults with things. And apparently not very good at providing any constructive solutions. But no one appreciates my finding those faults. No one appreciates the tone in which you report these alleged faults, necessarily agrees with the faults that you find, nor elected you system test engineer of the SQLite project. It calls for other things that many databases don't implement or altogether violate as well, so what? Show me how both MS SQL's T-SQL and Oracle's PL/SQL procedure languages are so standards compliant that you can use the same procedure code in both databases. You can't -- precisely because they ignore or outright violate parts of the standard as well. What's your position on that? Do some Googling and you can easily find 18 ways that Oracle's PL/SQL deviates from the standard. And T-SQL is plainly nowhere close. And how many of those systems use dynamic typing? And how many conform to the standard? Name one where the documentation claims the SQL Language Specification is a bug. Name one that conforms to the standard. And a lot of people go to chiropractors. And chiropractors are nice intelligent people with degrees. And the therapy provided does good. Nevertheless, the theory on which it's based is quackery. To use your specious analogy, it represents another way of doing things, which you admit yourself works. That's your justification for calling Richard Hipp a crackpot? What was Richard Hipp's justification for slandering the writers of the SQL Language Specification? It's clear. You're just way too smart for SQLite. Did you see my solution to Rick Shepard's problem in the thread Parsing String, Dictionary Lookups, Writing to Database Table? The point being? -- you can write Python code and feel entitled to condescending and rude? Is there anything more rude than describling the SQL Language Specification as a bug that needs to be fixed? -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
On 12 Sep 2006 08:29:34 -0700, Paul Rubin http://phr.cx@nospam.invalid wrote: But no one appreciates my finding those faults. No one appreciates the tone in which you report these alleged faults, Your tone is not so great either. And what would you expect after someone who has take little to no effort to 1. read the documentation 2. seek help in the appropriate places, or 3. raise concerns to the appropriate people unjustly and publicly vilifies an otherwise wonderful volunteer effort to provide a free, high-quality, open source software package? necessarily agrees with the faults that you find, nor elected you system test engineer of the SQLite project. It's an open source project, as you like to say. Everyone is a test engineer. Only real test engineers provide constructive feedback to the appropriate places. Posting ill-informed flames to the Python mailing list is hardly constructive feedback, or worthy of being called test engineering. standard as well. What's your position on that? Do some Googling and you can easily find 18 ways that Oracle's PL/SQL deviates from the standard. And T-SQL is plainly nowhere close. And how many of those systems use dynamic typing? And how many conform to the standard? How many of those deviations are justified in their documentation by the responsible parties claiming, in effect, that they're smarter than the standard's designers? No one said they are smarter than anyone else in effect or otherwise, but rather the SQLite project stated that it disagreed with static typing. When you write free software, you have the freedom to write it however you want, and that is the justification. This is the approach that SQLite took, they are free to do so, and in no way have they been deceptive about it. And because SQLite deviates in this way does not make it any more guilty of deviating or not conforming to the SQL standard than any other SQL database. It seems obvious to me that there should, at minimum, be an option to turn this particular nonstandard behavior on and off. Then the obvious means by which to accomplish this are like any other open source project: At a minimum, post this suggestion to the mailing list, write an RFE, or best of all, write the missing code yourself and supply a patch to the bug list. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Paul Boddie wrote: Fredrik Lundh wrote: Steve Holden wrote: Sure. But if you go back to the start of the thread you'll remember the OP was originally complaining that SQLite was being promoted in the Python docs as SQL compliant. It clearly isn't if its response to the insertion of a data value that conflicts with the declared column type is to store a value whose type is something else. the standard actually says If the value of any input parameter provided by the SQL-agent falls outside the set of allowed values of the data type of the parameter /.../ the effect is implementation-defined so that's perfectly SQL92 compliant. To be fair, that text originates in section 12.3, referring to input parameters to procedures. Meanwhile, the following text (subclause 13.8, insert statement) appears to be more pertinent: If the data type of the target identified by the i-th column name is an exact numeric type, then the data type of the i-th item of the insert statement shall be an exact numeric type. I have used SQLite (releases 2 and 3) to my satisfaction, aware of the common knowledge around the limitations (or features) of SQLite with respect to data types. I'd agree with the complainant that the behaviour of SQLite isn't what one would expect, although I started my relational database experience using an Oracle database system and admit that I may have been spoilt, thus saving Mr Holden - a self-confessed Yorkshireman, I believe - the effort involved in pointing out the relative luxury of my professional upbringing. ;-) Spoilt? I should say so. When I were a lad we 'ad to scrape around for half a bit here and half a bit there - you didn't find whole bits just lying around in them days. As fer integers, you 'ad to save up for a year just to get sixteen bits. We used ter dream abaht 'avin' 64 bits, but there were no chance unless yer Dad were rich. Most of our strings were put together from characters we'd dredged out of t' canal, you 'ad to try and ignore the mud. We'd 'ave given our eye teeth for a bit of UTF-8 on a Sunday. These kids wi' their Oracle databases didn't know they were born. I can remember 'avin' to optimise programs by making sure that the next instruction were comin' under the heads of t' drum just as the last instruction were finishing. But yer tell these young folk nowadays and they just don't believe yer. etc., etc. pythonistical-ly y'rs - steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Paul Boddie wrote: To be fair, that text originates in section 12.3, referring to input parameters to procedures. which is the section that section 4.1 (data types) refers to for more details on mappings between host data and SQL data. guess it depends on how you look at the different layers: if you're using SQLite via the DB- API, are you using SQL or an SQL Agent? /F -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
On 12 Sep 2006 09:31:54 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: To use your specious analogy, it represents another way of doing things, which you admit yourself works. That's your justification for calling Richard Hipp a crackpot? What was Richard Hipp's justification for slandering the writers of the SQL Language Specification? Slander? Richard wrote a free and open source relational database the way he wanted to, in a way that seems useful to him, and apparently thousands of other people. You are free to write your own open source relational database as well which has its own deviations from the standard. I say this because I'd love to see you write the worlds first SQL compliant database, and do it in under 30,000 lines of C, and make it portable across both operating systems and byte orders, capable of being run on cell phones and low power environments. The only slander I see are in your ill-informed posts on this mailing lists which should really be directed to the SQLite list. Apparently, you are too afraid to insult the SQLite community to its face, and seek refuge on the Python list. It's clear. You're just way too smart for SQLite. Did you see my solution to Rick Shepard's problem in the thread Parsing String, Dictionary Lookups, Writing to Database Table? The point being? -- you can write Python code and feel entitled to condescending and rude? Is there anything more rude than describling the SQL Language Specification as a bug that needs to be fixed? Yes, namely calling respectable people (who selflessly write free and open source software in their spare time) crackpots. Also, not lifting a finger to seek help or effect change, but taking every effort to insult. And finally, there is entertaining the notion that people live to serve your needs. Yes, there are things that are clearly more rude. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Mike Owens wrote: On 11 Sep 2006 23:29:28 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: But it was stated in the sqlite docs that ALL SQL databases use static types implying that sqlite will be incompatible with any heavy database should the need arise to migrate upwards. The issue is not that there will be compatibilty problems with any data migration but that the truth is exactly opposite of what's claimed in Section 13.13. Implying? There's a solid word. Migrating data from SQLite to other databases is no more difficult or easy than migrating data to any other database. Do you think this is ever trivial? It's as hard or as easy as you make it. No database can just take any schema and the data you put in it and just magically convert that schema/data to flawlessly work in any arbitrary database of your choosing. Some databases have tools to help with this, but they still are not perfect. So, knowing that, would you agree that quote Python Library Reference 13.13 If switching to a larger database such as PostgreSQL or Oracle is later necessary, the switch should be relatively easy. /quote is misleading if not outright untruthful? I'm not saying sqlite can't be used, what I'm asking for is that the documentation lay the facts out and I'll decide whether I can make this work in my application. Lying about it makes you sound like Microsoft. Lying? Whose lying? See above quote. And while you're at it, see the sqlite docs about how the SQL Language Specification of static typing is a bug. Where on the website is there a lie about anything? From what I can tell, you've not taken the time to read the documentation or post anything to the mailing list. You've just posted jeremiads on the Python list. Don't like the documentation? No, it's misleading and full of errors (this is the Python docs I'm referring to). Ever volunteered to help out? That's what this thread was about, testing the waters. No point making bug reports if I'm the one who's wrong. But it turns out I'm not wrong, sqlite IS goofy and this should be pointed out. Ever posted any suggestions on the list or report a bug? I'm still considering it. This thread has been very useful towards that. Do you really think that open source projects exists to serve you and meet your standards? Do you think that free code and documentation just falls like manna from heaven? But why does it have to be wrong? It's just as easy to get things right. Isn't that your complaint, that if I read the sqlite docs first, the riculous examples in the Python docs would have made more sense? Why didn't the guy writing the Python docs read the sqlite docs first? Do you honestly think the two core developers of SQLite have some secret agenda to deceive you or the world into using SQLite? Why do they claim that the SQL Language Specification of static typing is a bug? That's simply a lie. Why do they claim they've fixed it in a backwards compatible way? That's another lie. Why didn't they simply say they have an alternative to static typing? Because part of the deception is to make people think there is something wrong with static typing. Of course you could restrict yourself to, by example, SQL92 entry level and be a lot more compatible. But if I'm using a nice OO language like Python which lets me bind *any* object to *any* name, why should be wrong to bind *any* object to *any* database column? But SQL isn't OO, it's relational. That means JOINing tables together on a common field. In theory, due to the comparison hierarchy, it is impossible to do JOINs with dynamic typing since different types can never be equal. In practice, the type affinity kluge trys to work around this but can't do anything if the string doesn't look like an integer when a text field attempts to JOIN to an interger field. Unless you ensure that the correct types are put int the column to begin with, which is entirely possible with SQLite, as I've already demonstrated. And if that's just too much to bear, you can still do an inner join by explicitly casting the two columns in the join constraint to a common desired type. Want to know how? Read the documentation. And what do you get when you implement all these kluges? A database that effectively is static typed. Do you still think static typing is a bug? -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] wrote: So, knowing that, would you agree that quote Python Library Reference 13.13 If switching to a larger database such as PostgreSQL or Oracle is later necessary, the switch should be relatively easy. /quote is misleading if not outright untruthful? eh? if you've never migrated *from* SQLite to some other database, how can *you* possibly know *anything* about how hard or easy it is? /F -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Mike Owens wrote: On 12 Sep 2006 09:31:54 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: To use your specious analogy, it represents another way of doing things, which you admit yourself works. That's your justification for calling Richard Hipp a crackpot? What was Richard Hipp's justification for slandering the writers of the SQL Language Specification? Slander? Richard wrote a free and open source relational database the way he wanted to, in a way that seems useful to him, and apparently thousands of other people. You are free to write your own open source relational database as well which has its own deviations from the standard. I say this because I'd love to see you write the worlds first SQL compliant database, and do it in under 30,000 lines of C, and make it portable across both operating systems and byte orders, capable of being run on cell phones and low power environments. And when he was done, he made the ridiculous claim that the SQL Language Specification of static typing was a bug. The only slander I see are in your ill-informed posts on this mailing lists which should really be directed to the SQLite list. Why? I'm not requesting that dynamic typing be removed from sqlite. I'm not even requesting that the slander in the sqlite docs be removed. What I'm requesting is that these features of sqlite be better presented in the Python docs, hence, no posting to the sqlite list. Is that so hard to figure out? Apparently, you are too afraid to insult the SQLite community to its face, and seek refuge on the Python list. I'm sure the sqlite community could care less about what appears in the Python documentation. It's clear. You're just way too smart for SQLite. Did you see my solution to Rick Shepard's problem in the thread Parsing String, Dictionary Lookups, Writing to Database Table? The point being? -- you can write Python code and feel entitled to condescending and rude? Is there anything more rude than describling the SQL Language Specification as a bug that needs to be fixed? Yes, namely calling respectable people (who selflessly write free and open source software in their spare time) crackpots. Also, not lifting a finger to seek help or effect change, but taking every effort to insult. And finally, there is entertaining the notion that people live to serve your needs. Yes, there are things that are clearly more rude. So you admit that Richard Hipp's characterization of SQL was rude. And now that we've established what you are, we're just haggling over price. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Fredrik Lundh wrote: [EMAIL PROTECTED] wrote: So, knowing that, would you agree that quote Python Library Reference 13.13 If switching to a larger database such as PostgreSQL or Oracle is later necessary, the switch should be relatively easy. /quote is misleading if not outright untruthful? eh? if you've never migrated *from* SQLite to some other database, how can *you* possibly know *anything* about how hard or easy it is? Because I can extrapolate. I *know* before even trying it that if I export all my data from a sqlite db to a csv file and then try to import it into Access that there will be problems if the fields aren't static typed. That's one of the reasons why I was such a good test engineer. I could anticipate problems the design engineers didn't think of and I would deliberately provoke those problems during testing and crash their hardware/software. I wasn't very popular. /F -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
On Tue, 2006-09-12 at 13:01 +0200, Fredrik Lundh wrote: Mike Owens wrote: Crackpot? And now we get to why I took the flamebait -- wonderfully constructive comments such as this. I know SQLite's author. Besides being a nice and clearly very intelligent person, he also holds a master's degree in electrical engineering from Georgia Tech and a PhD in computer science from Duke University. His crackpot software is used by Sun, Apple, Symbian, Google, AOL, Philips, DLink, and I don't know how many other companies, not to mention countless open source projects such as Mozilla, PHP, and now Python. but is he a member of Mensa? Now *there's* a group of crackpots. Cliff -- -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
On 12 Sep 2006 10:24:00 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: So, knowing that, would you agree that quote Python Library Reference 13.13 If switching to a larger database such as PostgreSQL or Oracle is later necessary, the switch should be relatively easy. /quote is misleading if not outright untruthful? Not in the least. If you know what you are doing from a database perspective (not just a SQLite perspective), migrating data to another database is exactly that -- relatively easy. That means, you may have to recreate or modify your schema for the target database -- and this is true in ALL databases. Native datatypes vary from system to system, and some systems support user-defined data types, in which case your schema will definitely have to be modified. How would you migrate a CIDR type in PostgreSQL to a numeric field in Oracle? You have to work at it. Next, as far as transferring you data, you most likely have to resort to some delimited format, or INSERT statements, which is no different than any other database. So, I would call that relatively easy without a stretch, and certainly no different than migrating data with any other database. Really, how is this different than migrating data to/from any other database? whether I can make this work in my application. Lying about it makes you sound like Microsoft. Lying? Whose lying? See above quote. And while you're at it, see the sqlite docs about how the SQL Language Specification of static typing is a bug. Both of which have been addressed in detail. The above quote is not even stretching the truth, and the latter fact is a deviation that SQLite has every right to make because they, and not you, wrote the software. Furthermore, it is very clearly stated on the website. So how is that a lie? No, it's misleading and full of errors (this is the Python docs I'm referring to). I didn't join this thread because of Python's documentation, and I've made that clear. I am here because you are unjustly vilifying the SQLite project. Ever volunteered to help out? That's what this thread was about, testing the waters. No point making bug reports if I'm the one who's wrong. But it turns out I'm not wrong, sqlite IS goofy and this should be pointed out. Then be a man and point it out on the SQLite mailing list, where you can be called on it, rather than ranting about it here. But why does it have to be wrong? It's just as easy to get things right. Isn't that your complaint, that if I read the sqlite docs first, the riculous examples in the Python docs would have made more sense? Why didn't the guy writing the Python docs read the sqlite docs first? First, SQLite's approach is no more wrong than any other database's deviation from the standard. Second, as I've said, I'm not here for the Python issues. I think they'll get things sorted out in due time, and people on this list have been very receptive to your feedback. Do you honestly think the two core developers of SQLite have some secret agenda to deceive you or the world into using SQLite? Why do they claim that the SQL Language Specification of static typing is a bug? That's simply a lie. Why do they claim they've fixed it in a backwards compatible way? That's another lie. It's not a lie at all. Are you incapable of comprehending the context of that text? Do you not understand that it effectively says This is the way we do things. It's not in agreement with the SQL standard. We know that, we are doing it this way, and here's how it works, take it or leave it. And the whole bug in the SQL standard, if you can't tell, is called humor. Why didn't they simply say they have an alternative to static typing? They did. You couldn't understand that from the documentation? Because part of the deception is to make people think there is something wrong with static typing. Yes, it's really an underhanded conspiracy designed to deceive and mislead on a global scale. I can just see the developers sitting around plotting: Hey, let's write some free software. Yeah let's give the code away for free and not make a dime from it. Yeah, and then let's make up a bunch of lies to make people want to use it, so we can continue to not make a dime from it. And let's slander the SQL standard, and say all sorts nasty things about it. Yeah, that's how we'll spend our nights and weekends. You really need to find some fault that will stick at this point, don't you? They're really up to something. And what do you get when you implement all these kluges? A database that effectively is static typed. Only if you want one. Otherwise, you have the freedom of dynamic typing, which other databases don't afford. So, you in fact have more freedom than you do than with databases that only offer strict typing. Do you still think static typing is a bug? Did I say this, ever? I am not the SQLite website. I don't think either is a bug. Both are two different
Re: Is it just me, or is Sqlite3 goofy?
On 12 Sep 2006 10:47:22 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: So you admit that Richard Hipp's characterization of SQL was rude. And now that we've established what you are, we're just haggling over price. No, you've just managed to try and take the heat off of yourself. I never said anything about Richard. Good try. More sinned against than sinning doesn't help you at all. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] wrote: Fredrik Lundh wrote: [EMAIL PROTECTED] wrote: So, knowing that, would you agree that quote Python Library Reference 13.13 If switching to a larger database such as PostgreSQL or Oracle is later necessary, the switch should be relatively easy. /quote is misleading if not outright untruthful? eh? if you've never migrated *from* SQLite to some other database, how can *you* possibly know *anything* about how hard or easy it is? Because I can extrapolate. I *know* before even trying it that if I export all my data from a sqlite db to a csv file and then try to import it into Access that there will be problems if the fields aren't static typed. That's one of the reasons why I was such a good test engineer. I could anticipate problems the design engineers didn't think of and I would deliberately provoke those problems during testing and crash their hardware/software. I wasn't very popular. Strange, that, what with your excess of personal charm and great powers of diplomacy. People can be so touchy, can't they? regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] wrote: Because I can extrapolate. I *know* before even trying it that if I export all my data from a sqlite db to a csv file and then try to import it into Access that there will be problems if the fields aren't static typed. that's just the old C++/Java is better than Smalltalk/Python/Ruby crap. we've seen it before, and it's no more true when it comes from you than when it comes from some Java head. people who've actually used dynamic typing knows that it doesn't mean that all objects have random types all the time. That's one of the reasons why I was such a good test engineer. I could anticipate problems the design engineers didn't think of and I would deliberately provoke those problems during testing and crash their hardware/software. I wasn't very popular. no wonder, if you kept running around telling your colleagues that they were liars and crackpots and slanderers when things didn't work as you expected. what's your current line of work, btw? /F -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
On 12 Sep 2006 10:24:00 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: So, knowing that, would you agree that quote Python Library Reference 13.13 If switching to a larger database such as PostgreSQL or Oracle is later necessary, the switch should be relatively easy. /quote is misleading if not outright untruthful? As the original author of that sentence, I don't think it's either misleading or untruthful; 'relatively easy' gives me wiggle room. However, to fix your complaint, I've changed the paragraph to read: SQLite is a C library that provides a lightweight disk-based database that doesn't require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It's also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle. Of course, if you accept Fredrik's reading of the SQL standard, the word 'nonstandard' in the revised text is incorrect; SQLite is compliant with the standard but in an unusual way. (But most readers will interpret nonstandard as meaning not like most other SQL databases, so I'll let it stand.) --amk -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Fredrik Lundh wrote: [EMAIL PROTECTED] wrote: Because I can extrapolate. I *know* before even trying it that if I export all my data from a sqlite db to a csv file and then try to import it into Access that there will be problems if the fields aren't static typed. that's just the old C++/Java is better than Smalltalk/Python/Ruby crap. we've seen it before, and it's no more true when it comes from you than when it comes from some Java head. people who've actually used dynamic typing knows that it doesn't mean that all objects have random types all the time. No, it isn't the same old crap. When I define an Access field as Double, I cannot insert a value such as 200 or ND or Yes. I'm not saying static typing is better, just that migrating a dynamic types to static types may cause difficulties that wouldn't be present if it was static to static. And if you call the C++/Java is better than Smalltalk/Python/Ruby statement crap, why do you accept the statement that static typing is a bug in the SQL specification? Isn't that crap also? That's one of the reasons why I was such a good test engineer. I could anticipate problems the design engineers didn't think of and I would deliberately provoke those problems during testing and crash their hardware/software. I wasn't very popular. no wonder, if you kept running around telling your colleagues that they were liars and crackpots and slanderers when things didn't work as you expected. Nobody cared about that. What they cared about was my reporting to their boss that the latest version of the software was no better than the previous version who then had to figure out how to explain to the customer that the improvement he was promised didn't materialize and who then had to explain to his boss why the customer still hadn't signed off on the delivery and pay the bill. what's your current line of work, btw? Database manager for an a geotechnical consulting firm doing environmental remediation. /F -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] wrote: that's just the old C++/Java is better than Smalltalk/Python/Ruby crap. we've seen it before, and it's no more true when it comes from you than when it comes from some Java head. people who've actually used dynamic typing knows that it doesn't mean that all objects have random types all the time. No, it isn't the same old crap. When I define an Access field as Double, I cannot insert a value such as 200 or ND or Yes. I'm not saying static typing is better, just that migrating a dynamic types to static types may cause difficulties that wouldn't be present if it was static to static. dynamic typing != random typing. if your program is using the DB-API to add data to an SQLite database, who, exactly, is inserting the values? who's producing the data? under what circumstances would that code produce or insert arbitrarily typed data? /F -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
On 12 Sep 2006 10:47:22 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Why? I'm not requesting that dynamic typing be removed from sqlite. I'm not even requesting that the slander in the sqlite docs be removed. What I'm requesting is that these features of sqlite be better presented in the Python docs, hence, no posting to the sqlite list. Is that so hard to figure out? Apparently, you are too afraid to insult the SQLite community to its face, and seek refuge on the Python list. I'm sure the sqlite community could care less about what appears in the Python documentation. As a member of the SQLite community, your publicly denouncing SQLite as f***ing goofy, and it's creator as a crackpot, liar, and slanderer, concerns at least me. And I would say that these comments have no relation to what appears in the Python documentation. In any case, I am thankful for these comments, as they speak volumes about you. So much so, in fact, that I think neither you nor they pose any real danger of being taken seriously by anyone new to Python or SQLite. That being the case, I needn't continue with you. If you ever want to have a constructive discussion about SQLite or any of its features, or just need some general advice, please feel free to join the mailing list. There are a lot of friendly people who would be glad to help you out or consider to your suggestions. Cheers. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
A.M. Kuchling wrote: On 12 Sep 2006 10:24:00 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: So, knowing that, would you agree that quote Python Library Reference 13.13 If switching to a larger database such as PostgreSQL or Oracle is later necessary, the switch should be relatively easy. /quote is misleading if not outright untruthful? As the original author of that sentence, I don't think it's either misleading or untruthful; 'relatively easy' gives me wiggle room. Ok, I appologize for saying that. Got a little carried away by the flames. However, to fix your complaint, I've changed the paragraph to read: SQLite is a C library that provides a lightweight disk-based database that doesn't require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It's also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle. Of course, if you accept Fredrik's reading of the SQL standard, the word 'nonstandard' in the revised text is incorrect; SQLite is compliant with the standard but in an unusual way. (But most readers will interpret nonstandard as meaning not like most other SQL databases, so I'll let it stand.) And that was basically what I was originally asking for. That and fixing the broken examples. And maybe add a few more to illustrate SQLite manifest typing which is alien to a long time Access user. Should I start a new thread (sans polemics) to discuss the examples? --amk -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
dynamic typing != random typing. if your program is using the DB-API to add data to an SQLite database, who, exactly, is inserting the values? who's producing the data? under what circumstances would that code produce or insert arbitrarily typed data? Must be the code written by a Dr. Jekyll/Mr. Hyde personality... But honestly, boss, I didn't write this code! It was my evil alter-ego that puts VARCHAR values containing Gilbert Sullivan lyrics into the Amount_Due CURRENCY fields! :) -tkc -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Mike Owens wrote: On 12 Sep 2006 10:47:22 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Why? I'm not requesting that dynamic typing be removed from sqlite. I'm not even requesting that the slander in the sqlite docs be removed. What I'm requesting is that these features of sqlite be better presented in the Python docs, hence, no posting to the sqlite list. Is that so hard to figure out? Apparently, you are too afraid to insult the SQLite community to its face, and seek refuge on the Python list. I'm sure the sqlite community could care less about what appears in the Python documentation. As a member of the SQLite community, your publicly denouncing SQLite as f***ing goofy, and it's creator as a crackpot, liar, and slanderer, concerns at least me. quote And the whole bug in the SQL standard, if you can't tell, is called humor. /quote And I would say that these comments have no relation to what appears in the Python documentation. In any case, I am thankful for these comments, as they speak volumes about you. So much so, in fact, that I think neither you nor they pose any real danger of being taken seriously by anyone new to Python or SQLite. That being the case, I needn't continue with you. See you in another life. If you ever want to have a constructive discussion about SQLite or any of its features, or just need some general advice, please feel free to join the mailing list. There are a lot of friendly people who would be glad to help you out or consider to your suggestions. I'll use a different pseudonym. Cheers. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Tim Chase wrote: dynamic typing != random typing. if your program is using the DB-API to add data to an SQLite database, who, exactly, is inserting the values? who's producing the data? under what circumstances would that code produce or insert arbitrarily typed data? Must be the code written by a Dr. Jekyll/Mr. Hyde personality... But honestly, boss, I didn't write this code! It was my evil alter-ego that puts VARCHAR values containing Gilbert Sullivan lyrics into the Amount_Due CURRENCY fields! Hence the phrase Going for a song? groan-along-with-me-ly y'rs - steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] wrote: A.M. Kuchling wrote: On 12 Sep 2006 10:24:00 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: So, knowing that, would you agree that quote Python Library Reference 13.13 If switching to a larger database such as PostgreSQL or Oracle is later necessary, the switch should be relatively easy. /quote is misleading if not outright untruthful? As the original author of that sentence, I don't think it's either misleading or untruthful; 'relatively easy' gives me wiggle room. Ok, I appologize for saying that. Got a little carried away by the flames. However, to fix your complaint, I've changed the paragraph to read: SQLite is a C library that provides a lightweight disk-based database that doesn't require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It's also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle. Of course, if you accept Fredrik's reading of the SQL standard, the word 'nonstandard' in the revised text is incorrect; SQLite is compliant with the standard but in an unusual way. (But most readers will interpret nonstandard as meaning not like most other SQL databases, so I'll let it stand.) And that was basically what I was originally asking for. That and fixing the broken examples. And maybe add a few more to illustrate SQLite manifest typing which is alien to a long time Access user. Should I start a new thread (sans polemics) to discuss the examples? Why don't you? That would seem like a productive forward direction. regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
On 12 Sep 2006 13:03:09 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Ok, I appologize for saying that. Got a little carried away by the flames. Apology accepted; no problem. That and fixing the broken examples. That's also done. I fixed the executescript.py example, and tried running all the other examples as a check; that didn't turn up any more crashers. The development version of the docs, built nightly from the SVN trunk, are at http://docs.python.org/dev/. Note that the version number is now 2.6a0 on the trunk, but I can backport fixes to 2.5-maint as they're made. (Any new changes won't get in 2.5c2, which should be released tomorrow, but will get into 2.5final if the fixes are made by about the 17th.) Should I start a new thread (sans polemics) to discuss the examples? Certainly! --amk -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
On Tue, 12 Sep 2006 15:54:25 -0500, A.M. Kuchling [EMAIL PROTECTED] wrote: (Any new changes won't get in 2.5c2, which should be released tomorrow, but will get into 2.5final if the fixes are made by about the 17th.) And in fact the formatted development version no longer reflects what's in the trunk: I've just checked in a bunch of minor typo fixes and other edits to the trunk. So you may want to wait until tomorrow when these fixes show up... or you can look at the original LaTeX in the SVN browser at http://svn.python.org/view/python/trunk/Doc/lib/. --amk -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
But honestly, boss, I didn't write this code! It was my evil alter-ego that puts VARCHAR values containing Gilbert Sullivan lyrics into the Amount_Due CURRENCY fields! Hence the phrase Going for a song? I am the very model of a modern major database, For gigabytes of information gathered out in userspace. For banking applications to a website crackers will deface, You access me from console or spiffy user interface. My multi-threaded architecture offers you concurrency, And loads of RAM for caching things reduces query latency. The data is correctly typed, a fact that I will guarantee, Each datum has a data type, it's specified explicitly. is-it-friday-yet'ly yers, -tkc -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Fredrik Lundh wrote: Paul Boddie wrote: To be fair, that text originates in section 12.3, referring to input parameters to procedures. which is the section that section 4.1 (data types) refers to for more details on mappings between host data and SQL data. guess it depends on how you look at the different layers: if you're using SQLite via the DB- API, are you using SQL or an SQL Agent? Don't ask me! :-) I found it awkward enough scrolling up and down an n * 100 page plain text document formatted for a line printer in my Web browser, let alone spending time working out the cross-references throughout the text, all in the five to ten minutes I spent looking this up. Nevertheless, one could read the value of any input parameter provided by the SQL-agent falls outside the set of allowed values of the data type of the parameter as being something different from supplying a value of one particular data type in a place where another data type is expected. And I thought that the quote about valid values in insert statements provided a more categorical summary of the specification's position on such matters. Certainly, it's hard to read that and then to claim that a string is acceptable in places where a number is expected, regardless of what section 12.3 is trying to say. Paul -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Paul Boddie wrote: Don't ask me! :-) I found it awkward enough scrolling up and down an n * 100 page plain text document formatted for a line printer in my Web browser, let alone spending time working out the cross-references throughout the text, all in the five to ten minutes I spent looking this up. which proves my old theory: once a specification ends up having a bit more than 650 pages, it's certifiably evil. (for true greatness, the limit is 50 pages) /F -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Paul Boddie wrote: Don't ask me! :-) I found it awkward enough scrolling up and down an n * 100 page plain text document formatted for a line printer in my Web browser, let alone spending time working out the cross-references throughout the text, all in the five to ten minutes I spent looking this up. I remember that one of my very first Python scripts was to convert the SQL92 grammar -unusable otherwise- into an html cross-referenced version... Gabriel Genellina Softlab SRL __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Tim Chase wrote: But honestly, boss, I didn't write this code! It was my evil alter-ego that puts VARCHAR values containing Gilbert Sullivan lyrics into the Amount_Due CURRENCY fields! Hence the phrase Going for a song? I am the very model of a modern major database, For gigabytes of information gathered out in userspace. For banking applications to a website crackers will deface, You access me from console or spiffy user interface. My multi-threaded architecture offers you concurrency, And loads of RAM for caching things reduces query latency. The data is correctly typed, a fact that I will guarantee, Each datum has a data type, it's specified explicitly. Mensantor: When I was a lad I had to load Data to Access via import mode. Data came in as DBase III And had to be converted to csv. I so carefully converted the csv's That now I am the admin of big db's. Chorus: He so carefully converted the csv's That now he is the admin of big db's. is-it-friday-yet'ly yers, -tkc -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] wrote: I think an explanation of how Sqlite3 differs from SQL and a better set of examples is still warranted. In general, Python standard library modules that are wrappers for third party libraries are very thinly documented, and they should probably remain that way, because it's really too much of a burden on the Python developers to develop this documentation and keep it up to date. The idea is to document the wrapper, not the wrapped library. If I had a choice concerning these wrapper libraries, I'd much rather see more docs on the tkinter and xml libraries. There you need to guess a lot. There is no shortage of Tcl/Tk docs, but it doesn't look the same within Python. For the Python xml libraries, I've had to experiment a lot, and I have this nagging feeling that I don't do things the way I should. (From Python 2.5, we have ElementTree, which is much better from this perspective, but we've just started using 2.4 at work, and we try to keep the amount of third party libraries to a minimum here.) It seems to me that the sqlite3 module is fairly decent in this regard, particularly since it's not even included in a completed Python release yet. Concerning the API, I'm surprised to see magic method naming such as __conform__ introduced in a library like that. It seems to me that this is a violation of PEP 8. I'm sure there are further details that could be worth mentioning in the docs, but I think it's important that we don't try to duplicate the SQLite docs in the Python docs. -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote:
Bruno Desthuilliers wrote:
[EMAIL PROTECTED] wrote:
Probably just me. I've only been using Access and SQL Server
for 12 years, so I'm sure my opinions don't count for anything.
SQLite never pretended to be a full-blown RDBMS - just a lightweight
simple embedded database as SQL-compliant as possible.
Ah, *you* haven't read the documentation either!
I use SQLite and PgSQL (and, alas, MySQL) on a daily basis. So I've *of
course* read the doc (and frequently re-read it).
as SQL-compliant as possible?
Yes. And yes, I know about the typing issue. If you want to make sure
you can migrate your data to a real RDBMS, then it's up to you to take
appropriate precautions (FormEncode can be a real helper here...).
This (documented enough) issue apart, we have far less troubles with
SQLite than we have with MySQL, on both the dev and admin sides.
And FWIW, insulting peoples won't buy you much.
(snip useless rant again)
--
bruno desthuilliers
python -c print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])
--
http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Kay Schluehr wrote: [EMAIL PROTECTED] wrote: I wouldn't be at all surprised if the pysqlite author operated under that assumption. That the Python developers didn't pick up on the issue is not surprising. I'm not sure how many of them are (py)sqlite users, probably relatively few. Skip Who has reviewed sqlite/pysqlite after all? pysqlite was actually reviewed by several Python core developers before becoming a part of the Python standard library, most prominently Neil Norwitz and Anthony Baxter. Reading the passage in the sqlite FAQ I can hardly believe that passing errors silently and coercing everything to string when it fails to be coerced to INTEGER although INTEGER was an invariant declared in the create command is on par with Pythons design philosophy. [...] Unfortunately, third-party library authors don't first check with Python's design philosophy in case their software will be wrapped as a Python module ;-) I did my best to combine both SQLite's and Python's type system though, including a discussion with pysqlite 1.x users before the grand rewrite for version 2, which is the one that ended up in the Python standard library now. In other cases doctoral dissertations are written about whether a keyword or some punctuation shall be used for decorator syntax and in this case everything must be rushed into the code base of the standard library? There was no rush at all. -- Gerhard -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
I coworker pointed me to this thread.
and why it isn't SQL.
It isn't SQL simply because SQL won't let you insert text
into a numeric field.
Yup, I have to agree that's pretty crappy. (Makes mental note to limit
use of SQLite).
Ever heard of check constraints? That's another feature of this
crappy, non-SQL database. They are one of at least three different
approaches you can take to implement strict typing using SQLite's SQL
and C library facilities.
[EMAIL PROTECTED]:/tmp# ./sqlite3
SQLite version 3.3.7
Enter .help for instructions
sqlite create table stupid(x INTEGER check(typeof(x)=='integer'));
sqlite create table little(x REAL check(typeof(x)=='real'));
sqlite create table database(x INTEGER check(typeof(x)!='text'));
sqlite -- insert text
sqlite insert into stupid values('SQLite sucks!');
SQL error: constraint failed
sqlite insert into stupid values(1);
sqlite --whattayaknow
sqlite -- insert text
sqlite insert into little values('This isn''t SQL!');
SQL error: constraint failed
sqlite insert into little values(1.0);
sqlite --how about that
sqlite -- insert text
sqlite insert into database values('Dynamic typing just doesn''t work!');
SQL error: constraint failed
sqlite insert into database values(x'');
sqlite -- something's going on here
sqlite -- so what do we have in the tables?
sqlite .h on
sqlite .m col
sqlite select x, typeof(x) from stupid;
x typeof(x)
-- --
1 integer
sqlite -- integers only
sqlite select x, typeof(x) from little;
x typeof(x)
-- --
1.0 real
sqlite -- reals only
sqlite select x, typeof(x) from database;
x typeof(x)
-- --
blob
sqlite -- anything but text
That darned dynamic typing. It works like almost any other database if
you want it to. (Makes mental note to spend more time reading SQLite
documentation before bashing SQLite.)
--
http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Unfortunately, I don't think they are going to duplicate the 200 or so page O'Reilly SQLite book as part of the help system (even if that book is quite out-of-date; there is one skinny chapter near the end that explains what changes will appear in the version that has been available for Python for over a year now). -- Just to let you (and everyone else know) there is a new SQLite book out from APress that covers SQLite 3 http://www.apress.com/book/bookDisplay.html?bID=10130 It actually has a section that covers what a lot of these postings have been discussing, check constraints. You can actually implement type checking constraints in SQLite with very little additional code. That way it will give you an error message if you try to insert something of the wrong type. HTH, Preston -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
On 9/11/06, Mike Owens [EMAIL PROTECTED] wrote: I coworker pointed me to this thread. Joy for us. snipped good information In all seriousness, the information you present here is great, and much appreciated. Your sarcastic, condescending tone kind of gets in the way of the message, though. And here is the crux of the issue. Sqlite doesn't follow the standard for sql. The name certainly implies that it would. This doesn't make it a crappy product, but it is certainly misleading. I must admit, that after 10 years of oracle experience, I don't necessarily read all of the documentation for a new dbms I'm trying out, particularly a light weigth variety. I get in, and try things. Sometimes I get bitten, but I learn better that way. I would expect, however, for each product with 'sql' in the name, to, at least by default, adhere to the standard. And expectations are what set this conversation up. But, I don't expect that anything productive will come from the rest of this thread. Your post had the stink of zealotry all over it, and we all know what happens when a zealots favorite is questioned. Again, thanks for the info. It'll serve me well when I'm playing with sqlite later. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Mike Owens wrote: I coworker pointed me to this thread. and why it isn't SQL. It isn't SQL simply because SQL won't let you insert text into a numeric field. Yup, I have to agree that's pretty crappy. (Makes mental note to limit use of SQLite). Ever heard of check constraints? That's another feature of this crappy, non-SQL database. They are one of at least three different approaches you can take to implement strict typing using SQLite's SQL and C library facilities. Oh, right. So you're trying to defend SQLite (which, by the way, doesn't need it: it's a perfectly fine tool for limited purposes) by suggesting that column typing's failure to work isn't a problem because you can declare column types in check constraints? That seems like a hard position to defend to me. You might as well say it's OK to sell blunt knives because they can always be sharpened. [examples snipped] That darned dynamic typing. It works like almost any other database if you want it to. (Makes mental note to spend more time reading SQLite documentation before bashing SQLite.) Sure. But if you go back to the start of the thread you'll remember the OP was originally complaining that SQLite was being promoted in the Python docs as SQL compliant. It clearly isn't if its response to the insertion of a data value that conflicts with the declared column type is to store a value whose type is something else. You shouldn't need to add check constraints to verify that the value stored in an integer column is actually an integer. I don't think anyone is trying to suggest that SQLite isn't a prefectly good tool for many purposes: it's far more sophisticated than bsddb, for example, and I've used both it and Gadfly (which has similar deficiencies when compared to strict standards) with complete satisfaction. So climb down off that high horse :-) regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
On 9/11/06, Marty [EMAIL PROTECTED] wrote: In all seriousness, the information you present here is great, and much appreciated. Your sarcastic, condescending tone kind of gets in the way of the message, though. Sarcastic, perhaps. Condesceding, I think not. It is ridiculous that people can simply say whatever they want to about software they've taken little time to learn. I did not see one single post by mensanator to the SQLite mailing list, or by anyone else on this thread who criticized SQLite. Rather, it was SQLite is just crappy, or not a database, or not an SQL database, or other expletives. And while mensanator had other claims about Python's documentation, this general frustration then carelessly took a turn to SQLite. And as for the alleged problems in SQLite, there was (to my estimation) much less effort expended in finding a solution than there was in badmouthing an otherwise wonderful piece of software. If you are too impatient to read the documentation, fine. If you don't want to consult the experienced people on the SQLite mailing list (who are glad to help), fine. But DON'T remain willfully ignorant AND blame SQLite for not working the way your intuition demands. For years I've watched people badmouth SQLite whose claims are uninformed, unfounded, or downright unfair. Had a single accuser here posted this alleged problem with SQLite to the SQLite mailing list, I probably would have remained silent here (and answered it more politely there). And here is the crux of the issue. Sqlite doesn't follow the standard for sql. The name certainly implies that it would. This doesn't make it a crappy product, but it is certainly misleading. Newsflash: No database follows the complete SQL standard, not even Oracle. By the logic in this thread, there is no such thing as an SQL database. I must admit, that after 10 years of oracle experience, I don't necessarily read all of the documentation for a new dbms I'm trying out, particularly a light weigth variety. I get in, and try things. Sometimes I get bitten, but I learn better that way. I would hope then that when you don't read the documentation, and you get bitten, you know better than to blame the software. I would expect, however, for each product with 'sql' in the name, to, at least by default, adhere to the standard. And expectations are what set this conversation up. These expectations are simply unrealistic. If someone is simply too lazy to read the documentation or use the mailing list, then they can only blame themselves. And if you say SQLite misrepresents itself, then what do you say about MySQL, which until version 5 didn't have views or triggers? In fact, it didn't even have subselects until version 4. For a period of years, SQLite had more mainstream SQL features than MySQL. Yet you don't see people going around claiming that MySQL is not an SQL database -- that it's misrepresenting itself. So no, SQLite most certainly does not misrepresent itself. It is an open source, embedded, relational database that uses SQL as its query language. Plain and simple. Just because it may not implement part of the standard you or someone else likes does not strip it being an SQL database. But, I don't expect that anything productive will come from the rest of this thread. Your post had the stink of zealotry all over it, and we all know what happens when a zealots favorite is questioned. Expecting people to get the facts before badmouthing something is hardly zealotry. I am tired of seeing SQLite taking the blame when certain people choose simply to assume rather than read (or consult others). They see database-level locking, they ASSUME it's too slow for any kind of write concurrency applications. They see type affinity, they ASSUME it's just substandard or useless, and then by further soritical leaps, discount it as even an SQL database? Rather than reading, or testing, or asking people who know, they get frustrated and go straight to blaming it. That is completely unfair. The purpose of this post is to demonstrate that: 1. SQLite was not at fault here, nor insufficient for the purpose stated. 2. There are people who can easily provide the very help you need, provided you ask them, and try to keep your derogatory comments to a minimum. 3. There is no substitute for reading the documentation and learning the product. This is doubly true of relational databases. I'd love to see someone who uses MSSQL or Oracle try to install and use PostgreSQL or Firebird with nothing but instinct. I currently use three of these databases in production, and I couldn't survive without reading documentation. Again, thanks for the info. It'll serve me well when I'm playing with sqlite later. You are more than welcome. Glad to help. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
On 9/11/06, Steve Holden [EMAIL PROTECTED] wrote: Sure. But if you go back to the start of the thread you'll remember the OP was originally complaining that SQLite was being promoted in the Python docs as SQL compliant. Define SQL compliant. That's about as technically precise as saying that something tastes like chicken. Furthermore, I'm not responding to Python's representation of one thing or another. I am responding to some of the ridiculous and unfair criticisms directed at SQLite. Whatever Python did or didn't do, or whatever PySQLite does or doesn't do, SQLite doesn't deserve to be drug through the mud. You shouldn't need to add check constraints to verify that the value stored in an integer column is actually an integer. You should if your using SQLite, and this is clearly documented: http://www.sqlite.org/datatype3.html. I don't think anyone is trying to suggest that SQLite isn't a prefectly good tool for many purposes: it's far more sophisticated than bsddb, for example, and I've used both it and Gadfly (which has similar deficiencies when compared to strict standards) with complete satisfaction. Then what does calling it crappy and goofy suggest? -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Mike Owens wrote: On 9/11/06, Steve Holden [EMAIL PROTECTED] wrote: Sure. But if you go back to the start of the thread you'll remember the OP was originally complaining that SQLite was being promoted in the Python docs as SQL compliant. Define SQL compliant. That's about as technically precise as saying that something tastes like chicken. Furthermore, I'm not responding to Python's representation of one thing or another. I am responding to some of the ridiculous and unfair criticisms directed at SQLite. Whatever Python did or didn't do, or whatever PySQLite does or doesn't do, SQLite doesn't deserve to be drug through the mud. Which is precisely why I took pains to acknowledge that there were many purposes for which SQLite is entirely suitable. You shouldn't need to add check constraints to verify that the value stored in an integer column is actually an integer. You should if your using SQLite, and this is clearly documented: http://www.sqlite.org/datatype3.html. Right. In which case, why bother to define the types of the columns in your table declarations? I don't think anyone is trying to suggest that SQLite isn't a prefectly good tool for many purposes: it's far more sophisticated than bsddb, for example, and I've used both it and Gadfly (which has similar deficiencies when compared to strict standards) with complete satisfaction. Then what does calling it crappy and goofy suggest? That colloquial English expression is acceptable on this list. Strict affinity mode seems to represent a movement towards more rigorous type checking. So the designers of SQLIte accept that it wasn't perfect. So what? Please, don't take on so. It's only ones and zeroes. regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Mike Owens wrote: On 9/11/06, Steve Holden [EMAIL PROTECTED] wrote: Sure. But if you go back to the start of the thread you'll remember the OP was originally complaining that SQLite was being promoted in the Python docs as SQL compliant. Define SQL compliant. That's about as technically precise as saying that something tastes like chicken. Can you run your car on diesel fuel? Why not? Because your car's specification says to use gasoline? If your car has been designed to run on diesel, you shouldn't be saying it has gasoline engine. Duh. Furthermore, I'm not responding to Python's representation of one thing or another. I am responding to some of the ridiculous and unfair criticisms directed at SQLite. Whatever Python did or didn't do, or whatever PySQLite does or doesn't do, SQLite doesn't deserve to be drug through the mud. Sure it does. From SQLite Homepage documentation Available Documentation Distinctive Features quote *emphasis added* This page highlights some of the characteristics of SQLite that are unusual and which make SQLite different from many other SQL database engines. Manifest typing Most SQL database engines use static typing. A datatype is associated with each column in a table and only values of that particular datatype are allowed to be stored in that column. SQLite relaxes this restriction by using manifest typing. In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column. (There are some exceptions to this rule: An INTEGER PRIMARY KEY column may only store integers. And SQLite attempts to coerce values into the declared datatype of the column when it can.) *** * The SQL language specification calls for static typing. * *** So some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. ** * The authors argue that static typing is a bug in the * * SQL specification that SQLite has fixed in a backwards * * compatible way.* ** /quote A spcification cannot be a bug (unless it is inconsistent with other specifications). An implementation can be, but a specification cannot. You shouldn't need to add check constraints to verify that the value stored in an integer column is actually an integer. You should if your using SQLite, and this is clearly documented: http://www.sqlite.org/datatype3.html. I don't think anyone is trying to suggest that SQLite isn't a prefectly good tool for many purposes: it's far more sophisticated than bsddb, for example, and I've used both it and Gadfly (which has similar deficiencies when compared to strict standards) with complete satisfaction. Then what does calling it crappy and goofy suggest? It suggests that the SQLite author is a crackpot. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
On 11 Sep 2006 18:23:50 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Can you run your car on diesel fuel? Why not? Because your car's specification says to use gasoline? If your car has been designed to run on diesel, you shouldn't be saying it has gasoline engine. Duh. No but you can still call it a car with an engine, just as SQLite is a SQL database, with an SQL engine. Furthermore, I'm not responding to Python's representation of one thing or another. I am responding to some of the ridiculous and unfair criticisms directed at SQLite. Whatever Python did or didn't do, or whatever PySQLite does or doesn't do, SQLite doesn't deserve to be drug through the mud. Sure it does. No it doesn't. If you don't like SQLite's design decisions, write your own embedded relational database, and stop yapping about something you didn't lift a finger to create, but are clearly trying to benefit from. From SQLite Homepage documentation Available Documentation Distinctive Features In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column. (There are some exceptions to this rule: An INTEGER PRIMARY KEY column may only store integers. And SQLite attempts to coerce values into the declared datatype of the column when it can.) *** * The SQL language specification calls for static typing. * *** It calls for other things that many databases don't implement or altogether violate as well, so what? Show me how both MS SQL's T-SQL and Oracle's PL/SQL procedure languages are so standards compliant that you can use the same procedure code in both databases. You can't -- precisely because they ignore or outright violate parts of the standard as well. What's your position on that? Do some Googling and you can easily find 18 ways that Oracle's PL/SQL deviates from the standard. And T-SQL is plainly nowhere close. A spcification cannot be a bug (unless it is inconsistent with other specifications). An implementation can be, but a specification cannot. Then every database implementation, when held up to the SQL standard, is equally guilty of being buggy and/or incomplete. Name one database that fully conforms to SQL 2003. Can't name one? Then why single out SQLite? Then what does calling it crappy and goofy suggest? It suggests that the SQLite author is a crackpot. Crackpot? And now we get to why I took the flamebait -- wonderfully constructive comments such as this. I know SQLite's author. Besides being a nice and clearly very intelligent person, he also holds a master's degree in electrical engineering from Georgia Tech and a PhD in computer science from Duke University. His crackpot software is used by Sun, Apple, Symbian, Google, AOL, Philips, DLink, and I don't know how many other companies, not to mention countless open source projects such as Mozilla, PHP, and now Python. But I guess they must all be crackpots too. It's clear. You're just way too smart for SQLite. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
On 9/11/06, Steve Holden [EMAIL PROTECTED] wrote: Furthermore, I'm not responding to Python's representation of one thing or another. I am responding to some of the ridiculous and unfair criticisms directed at SQLite. Whatever Python did or didn't do, or whatever PySQLite does or doesn't do, SQLite doesn't deserve to be drug through the mud. Which is precisely why I took pains to acknowledge that there were many purposes for which SQLite is entirely suitable. Which was objective and diplomatic. Thanks. You shouldn't need to add check constraints to verify that the value stored in an integer column is actually an integer. You should if your using SQLite, and this is clearly documented: http://www.sqlite.org/datatype3.html. Right. In which case, why bother to define the types of the columns in your table declarations? Actually there is some use for this, so long as the declared types correspond to one of SQLite's storage classes -- INTEGER, REAL, TEXT, BLOB, or NONE. When SQLite sees one of these as the declared type, it will apply some affinity rules for updates/inserts which will attempt to coerce the input value to that type if possible. So while this does not give you true strict typing, it does provide you with something helpful if you code your application with this in mind. As already mentioned, you will need triggers or check constraints if you need true strict typing. Then what does calling it crappy and goofy suggest? That colloquial English expression is acceptable on this list. And what about some of the other colloquial expressions I saw but didn't repeat? Strict affinity mode seems to represent a movement towards more rigorous type checking. So the designers of SQLIte accept that it wasn't perfect. So what? Please, don't take on so. It's only ones and zeroes. This is an example of SQLite listening and responding to constructive feedback. When I first started using SQLite, it didn't have an autoincrement primary key. I made a case for it, asked nicely, and it was done in three days. Most people will agree that this approach tends to work better in most projects, as opposed to calling them goofy and crappy, or its author a crackpot. Mike regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Mike Owens wrote: On 9/11/06, Steve Holden [EMAIL PROTECTED] wrote: Furthermore, I'm not responding to Python's representation of one thing or another. I am responding to some of the ridiculous and unfair criticisms directed at SQLite. Whatever Python did or didn't do, or whatever PySQLite does or doesn't do, SQLite doesn't deserve to be drug through the mud. Which is precisely why I took pains to acknowledge that there were many purposes for which SQLite is entirely suitable. Which was objective and diplomatic. Thanks. Well known for my even temper and cool objective thinking. My modesty, too ;-) You shouldn't need to add check constraints to verify that the value stored in an integer column is actually an integer. You should if your using SQLite, and this is clearly documented: http://www.sqlite.org/datatype3.html. Right. In which case, why bother to define the types of the columns in your table declarations? Actually there is some use for this, so long as the declared types correspond to one of SQLite's storage classes -- INTEGER, REAL, TEXT, BLOB, or NONE. When SQLite sees one of these as the declared type, it will apply some affinity rules for updates/inserts which will attempt to coerce the input value to that type if possible. So while this does not give you true strict typing, it does provide you with something helpful if you code your application with this in mind. As already mentioned, you will need triggers or check constraints if you need true strict typing. Then what does calling it crappy and goofy suggest? That colloquial English expression is acceptable on this list. And what about some of the other colloquial expressions I saw but didn't repeat? Well, I'm not going to take responsibility for others' immoderate comments, am I? But remember that this is usenet, where generally you can count on somewhere between 20% and 80% of what you read being somewhere between ill-informed and downright wrong. This group seems to have a better record than most, but that just means less crap, not no crap ... Strict affinity mode seems to represent a movement towards more rigorous type checking. So the designers of SQLIte accept that it wasn't perfect. So what? Please, don't take on so. It's only ones and zeroes. This is an example of SQLite listening and responding to constructive feedback. When I first started using SQLite, it didn't have an autoincrement primary key. I made a case for it, asked nicely, and it was done in three days. Most people will agree that this approach tends to work better in most projects, as opposed to calling them goofy and crappy, or its author a crackpot. Works better for Python too. Anyway, you seem to be feeling better now, so I presume getting it off your chest helped :-) SQLite will clearly fare well in the future with a responsive development team like that. regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Mike Owens wrote: On 11 Sep 2006 18:23:50 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Can you run your car on diesel fuel? Why not? Because your car's specification says to use gasoline? If your car has been designed to run on diesel, you shouldn't be saying it has gasoline engine. Duh. No but you can still call it a car with an engine, just as SQLite is a SQL database, with an SQL engine. Seperate the data from the engine and what have you got? Data with dynamic typing. Data that can't be migrated to a real SQL database because you'll get type mismatches when strings are inserted into numeric fields. The type affinity kluge won't help there, will it? Furthermore, I'm not responding to Python's representation of one thing or another. I am responding to some of the ridiculous and unfair criticisms directed at SQLite. Whatever Python did or didn't do, or whatever PySQLite does or doesn't do, SQLite doesn't deserve to be drug through the mud. Sure it does. No it doesn't. If you don't like SQLite's design decisions, write your own embedded relational database, and stop yapping about something you didn't lift a finger to create, It's not the job of the System Test Engineer to design things. It's his job to find fault with everything. I just happen to be very good at finding faults with things. But no one appreciates my finding those faults. The Emperor is naked and I'm the only one who can see it. but are clearly trying to benefit from. From SQLite Homepage documentation Available Documentation Distinctive Features In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column. (There are some exceptions to this rule: An INTEGER PRIMARY KEY column may only store integers. And SQLite attempts to coerce values into the declared datatype of the column when it can.) *** * The SQL language specification calls for static typing. * *** It calls for other things that many databases don't implement or altogether violate as well, so what? Show me how both MS SQL's T-SQL and Oracle's PL/SQL procedure languages are so standards compliant that you can use the same procedure code in both databases. You can't -- precisely because they ignore or outright violate parts of the standard as well. What's your position on that? Do some Googling and you can easily find 18 ways that Oracle's PL/SQL deviates from the standard. And T-SQL is plainly nowhere close. And how many of those systems use dynamic typing? A spcification cannot be a bug (unless it is inconsistent with other specifications). An implementation can be, but a specification cannot. Then every database implementation, when held up to the SQL standard, is equally guilty of being buggy and/or incomplete. Name one database that fully conforms to SQL 2003. Can't name one? Then why single out SQLite? Name one where the documentation claims the SQL Language Specification is a bug. Then what does calling it crappy and goofy suggest? It suggests that the SQLite author is a crackpot. Crackpot? And now we get to why I took the flamebait -- wonderfully constructive comments such as this. I know SQLite's author. Besides being a nice and clearly very intelligent person, he also holds a master's degree in electrical engineering from Georgia Tech and a PhD in computer science from Duke University. His crackpot software is used by Sun, Apple, Symbian, Google, AOL, Philips, DLink, and I don't know how many other companies, not to mention countless open source projects such as Mozilla, PHP, and now Python. But I guess they must all be crackpots too. And a lot of people go to chiropractors. And chiropractors are nice intelligent people with degrees. And the therapy provided does good. Nevertheless, the theory on which it's based is quackery. It's clear. You're just way too smart for SQLite. Did you see my solution to Rick Shepard's problem in the thread Parsing String, Dictionary Lookups, Writing to Database Table? -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
At Tuesday 5/9/2006 16:23, [EMAIL PROTECTED] wrote: I would be surprised if they had never used ANY database. A little thing like dynamic field typing will simply make it impossible to migrate your Sqlite data to a *real* database. Why not? Because it breaks the relational model rules? That model certainly was great 30 years ago, but now things are different. (In fact, you didn't menction the word relational, but I presume you were thinking of that). Even what you call *real* databases have a lot of incompatibilities among them (e.g. ORACLE does not provide an autoincrement type, but has sequences, and so on...). Of course you could restrict yourself to, by example, SQL92 entry level and be a lot more compatible. But if I'm using a nice OO language like Python which lets me bind *any* object to *any* name, why should be wrong to bind *any* object to *any* database column? Looks a lot more pythonic for me. Of course, a true object database (like ZODB) is better. Gabriel Genellina Softlab SRL __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Dennis Lee Bieber wrote: Talking to myself again, I see... Not quite. ;-) [...] How interesting... With MySQL/MySQLdb I did NOT get exceptions or error results on inserting bad numeric data supplied as character string format (ie, as read from the CSV). Instead, MySQL SILENTLY converted them to ZEROS A price of Priceless becomes Decimal(0.00). The Customer number of 68 became 0L This kind of thing is classic MySQL behaviour. Which would one rather have to work with -- a database that copied invalid numerics as string literals (which, in my mind, makes it much easier to correct the data later, using update set field = correct where field = invalid) or a database that silently converts them all to 0 values. (Of course, I now expect to have a rejoinder about Using a REAL database instead of MySQL -- but unless said person wishes to start making the same comments about SQLite on at least as regular a basis, I believe the objection itself is invalid for this example). Given subsequent research into SQLite's affinity modes and their presumed status as future features, the solution in that database system's case is to apply validation in the driver/module or through driver extensions, and there is apparently some flexibility in the pysqlite2 modules for changing the way data types are handled, although a cursory inspection of the documentation doesn't appear to suggest a convenient, ready-made solution. As for MySQL, the situation is possibly more awkward: one expects the database system to complain about certain things, which it probably does from time to time, but it would seem wasteful to duplicate whatever validation the database system does do just to cover those situations where the system misbehaves. Paul -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Dennis Lee Bieber wrote: Guess I lied... On Sat, 09 Sep 2006 05:22:20 GMT, Dennis Lee Bieber [EMAIL PROTECTED] declaimed the following in comp.lang.python: Talking to myself again, I see... snip rs = cr.execute(insert into invoice_1 (CustNo, Title, Author, Year, Price) values (?,?,?,?,?), r) Whoops, r = rv, though the exceptions raised made it moot rs = cr.execute(insert into invoice_2 (CustNo, Title, Author, Year, Price) values (?,?,?,?,?), r) Same comment Out of curiousity, I converted to using MySQL(db) as a test. As expected, the pre-insert validation code worked with same results (well, the price was declared decimal, and Python 2.4 appears to handle that as a Decimal(value) on return G) Now, taking out the pre-validation and type conversion, supplying all data as it came from the CSV file: -=-=-=-=-=-=- Inserting: ['066', '101 Ways to Start A Fight', 'some Irish gentleman', '1919', '19.95'] ['032', 'A Sale of Two Titties', 'Charles Dikkens', '1855', '20.00'] ['001', 'Olsens Standard Book of British Birds (Expurgated)', 'Olsen', 'None', '99.95'] ['066', 'Ethel the Aardvark Goes Quantity Surveying', 'None', '1975', '9.99'] ['032', 'David Coperfield', 'Edmund Wells', '1955', '3.95'] ['68', 'Strawmen and Dorothy', '', '2006', '49.89'] ['033', The Emperor's Old Clothes, 'Grimm Hound', '1887', 'Priceless'] Select all from Invoice_1 (CustNo is CHARACTER) (1L, '066', '101 Ways to Start A Fight', 'some Irish gentleman', 1919L, Decimal(19.95)) (2L, '032', 'A Sale of Two Titties', 'Charles Dikkens', 1855L, Decimal(20.00)) (3L, '001', 'Olsens Standard Book of British Birds (Expurgated)', 'Olsen', 0L, Decimal(99.95)) (4L, '066', 'Ethel the Aardvark Goes Quantity Surveying', 'None', 1975L, Decimal(9.99)) (5L, '032', 'David Coperfield', 'Edmund Wells', 1955L, Decimal(3.95)) (6L, '68', 'Strawmen and Dorothy', '', 2006L, Decimal(49.89)) (7L, '033', The Emperor's Old Clothes, 'Grimm Hound', 1887L, Decimal(0.00)) Select all from Invoice_2 (CustNo is INTEGER) (1L, 66L, '101 Ways to Start A Fight', 'some Irish gentleman', 1919L, Decimal(19.95)) (2L, 32L, 'A Sale of Two Titties', 'Charles Dikkens', 1855L, Decimal(20.00)) (3L, 1L, 'Olsens Standard Book of British Birds (Expurgated)', 'Olsen', 0L, Decimal(99.95)) (4L, 66L, 'Ethel the Aardvark Goes Quantity Surveying', 'None', 1975L, Decimal(9.99)) (5L, 32L, 'David Coperfield', 'Edmund Wells', 1955L, Decimal(3.95)) (6L, 0L, 'Strawmen and Dorothy', '', 2006L, Decimal(49.89)) (7L, 33L, The Emperor's Old Clothes, 'Grimm Hound', 1887L, Decimal(0.00)) -=-=-=-=-=-=- How interesting... With MySQL/MySQLdb I did NOT get exceptions or error results on inserting bad numeric data supplied as character string format (ie, as read from the CSV). Instead, MySQL SILENTLY converted them to ZEROS A price of Priceless becomes Decimal(0.00). The Customer number of 68 became 0L Which would one rather have to work with -- a database that copied invalid numerics as string literals (which, in my mind, makes it much easier to correct the data later, using update set field = correct where field = invalid) or a database that silently converts them all to 0 values. (Of course, I now expect to have a rejoinder about Using a REAL database instead of MySQL -- but unless said person wishes to start making the same comments about SQLite on at least as regular a basis, I believe the objection itself is invalid for this example). (Apparently we have fallen afoul of this clause from the old O'Reilly/MySQL black/brown book: When asked to store a value in a numeric column that is outside the column type's allowable range, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead. -- seems character data clips to zero. Are you saying that MySQL is goofy? ;-) Based on these replies, I'm pulling back and retrenching. As I said before, I'm not entering 500,000 records by writing INSERT statements for each record, so reading csv files is a more realistic test. Nevertheless, I am still convinced that the documentation (or lack thereof) is mainly responsible for my confusion. I was, after all, mimicing the examples given (which still have errors). I think an explanation of how Sqlite3 differs from SQL and a better set of examples is still warranted. -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Paul McNett wrote:
[EMAIL PROTECTED] wrote:
Do you know what INNER JOIN means?
Do you know how important it is to a relational database?
Can you explain how an INNER JOIN can even work, in theory,
with dynamic data types?
Let's stop the pissing contest and just see how it works. After all,
this is Python and we can easily just try it out. Here's my example.
Please tell me how this causes unexpected results,
When we use a best case scenario, we get what we expect.
and why it isn't SQL.
It isn't SQL simply because SQL won't let you insert text
into a numeric field.
Please modify my example to get it to cause a catastrophe,
Make it worse case? Sure, I can do that.
and post it
here so we can see the errors of our ways and be properly humbled.
#-- Preliminaries:
## from pysqlite2 import dbapi2 as sqlite
import sqlite3 as sqlite
## con = sqlite.connect(test.db)
con = sqlite.connect(:memory:)
cur = con.cursor()
#-- Create 3 tables for a M:M relationship between customers
#-- and categories:
cur.execute(create table customers (id integer primary key
autoincrement, name char))
cur.execute(create table categories (id integer primary key
autoincrement, name char))
cur.execute(create table cust_cat (id integer primary key
autoincrement, cust_id integer, cat_id integer))
##cur.execute(create table cust_cat (id integer, cust_id,
cat_id))
##cur.execute(create table customers (id, name char))
##cur.execute(create table categories (id, name char))
##
### Ok, THIS fails. Because the JOINs were originally made against
fields
### that were cast as integers so the 'hinting' of sqlite must
operate in a JOIN
### allowing ints to JOIN against strings. Take away the casts and
the JOIN
### fails. Unfortunately, not every situation will involve JOINing
primary keys.
##
##[(1, u'Ziggy Marley'), (2, u'David Bowie')]
##[(1, u'Glam Rock'), (2, u'Nuevo Reggae'), (3, u'Male Singers')]
##[(None, 1, 3), (None, 1, u'2'), (None, u'2', u'1'), (None, u'2',
u'3')]
##
##[(1, u'Ziggy Marley', 3, u'Male Singers')]
##[]
##[]
##[]
##[]
##[(1, u'Ziggy Marley', 3, u'Male Singers')]
#-- Insert some test data into customer and categories:
cur.execute(insert into customers (name) values ('Ziggy Marley'))
cur.execute(insert into customers (name) values ('David Bowie'))
cur.execute(insert into categories (name) values ('Glam Rock'))
cur.execute(insert into categories (name) values ('Nuevo Reggae'))
cur.execute(insert into categories (name) values ('Male Singers'))
## # if int cast removed, manually insert ids
##cur.execute(insert into customers (id, name) values (1,'Ziggy
Marley'))
##cur.execute(insert into customers (id, name) values (2,'David
Bowie'))
##cur.execute(insert into categories (id, name) values (1,'Glam
Rock'))
##cur.execute(insert into categories (id, name) values (2,'Nuevo
Reggae'))
##cur.execute(insert into categories (id, name) values (3,'Male
Singers'))
cur.execute(select * from customers)
#-- Take a look at the data (and retrieve the pk's):
print cur.fetchall()
#[(1, u'Ziggy Marley'), (2, u'David Bowie')]
cur.execute(select * from categories)
print cur.fetchall()
#[(1, u'Glam Rock'), (2, u'Nuevo Reggae'), (3, u'Male Singers')]
#-- Relate some customers to some categories. Note how I send strings
#-- in some places and ints in others:
##cur.execute(insert into cust_cat (cust_id, cat_id) values (1, 3))
##cur.execute(insert into cust_cat (cust_id, cat_id) values (1, '2'))
##cur.execute(insert into cust_cat (cust_id, cat_id) values ('2',
'1'))
##cur.execute(insert into cust_cat (cust_id, cat_id) values ('2', 3))
##cc = [(1,3),(1,'2'),('2','1'),('2','3')]
cc = [(1,3),(1,'2'),('2','1'),('2','3')]
### And this also fails (differently). The 'hinting' of sqlite that
operates
### during a JOIN only works if the string looks like an integer.
And, no,
### the use of the '' is not a contrived example like 'fred'. I
often get a
### result like '200' in what is supposed to be a numeric field.
##
##[(1, u'Ziggy Marley'), (2, u'David Bowie')]
##[(1, u'Glam Rock'), (2, u'Nuevo Reggae'), (3, u'Male Singers')]
##[(1, 1, 3), (2, 1, u'2'), (3, u'2', u'1'), (4, u'2', u'3')]
##
##[(1, u'Ziggy Marley', 3, u'Male Singers')]
##[]
##[]
##[]
##[(1, u'Ziggy Marley', 3, u'Male Singers')]
##[(1, u'Ziggy Marley', 3, u'Male Singers')]
cur.executemany(insert into cust_cat (cust_id, cat_id) values
(?,?),cc)
cur.execute(select * from cust_cat)
print cur.fetchall()
print
#-- Run some queries:
cur.execute(
select customers.id as cust_id,
customers.name as cust_name,
categories.id as cat_id,
categories.name as cat_name
from customers
inner join cust_cat
on cust_cat.cust_id = customers.id
inner join categories
on categories.id = cust_cat.cat_id
order by 2,4)
print cur.fetchall()
#[(2, u'David Bowie', 1, u'Glam Rock'),
# (2, u'David Bowie', 3, u'Male
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] wrote: I wouldn't be at all surprised if the pysqlite author operated under that assumption. That the Python developers didn't pick up on the issue is not surprising. I'm not sure how many of them are (py)sqlite users, probably relatively few. Skip Who has reviewed sqlite/pysqlite after all? Reading the passage in the sqlite FAQ I can hardly believe that passing errors silently and coercing everything to string when it fails to be coerced to INTEGER although INTEGER was an invariant declared in the create command is on par with Pythons design philosophy. In other cases doctoral dissertations are written about whether a keyword or some punctuation shall be used for decorator syntax and in this case everything must be rushed into the code base of the standard library? -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Kay Schluehr wrote: [Quoting Marc 'BlackJack' Rintsch...] If you are so fond of static typing, why are you using Python in the first place? Just see it as consistency -- dynamically typed language → dynamically typed DB columns. ;-) I have to admit I find this bogus too. It has by no means anything to do with static typing but letting errors pass silently i.e. deactivating runtime type checks as well. If the questioner had been talking about Perl, most respondents would rightly have said that Perl's silent coercion or conversion of values is an irritating source of potential errors. Here, the behaviour of SQLite, whilst not as bad as Perl - values are not converted into something different which is then erroneous - does contradict the naive expectations of users who expect the data type declarations to mean something and for such constraints to be enforced. Of course, the criticism of the questioner should be more forgiving in this debate, since I imagine that most people with experience of SQLite know of its loose data typing model, and that many assume that everyone else is aware of this feature or limitation, even though that may not be the case. Thus, the priority should be on emphasizing this in the module documentation (in order to avoid unnecessary confusion), along with mentioning other possible strategies for emulating other database system behaviours: Other Affinity Modes in the SQLite documentation [1] may be helpful here, if the future tense can be replaced with the present tense whilst preserving the factual content of that section. Paul [1] http://www.sqlite.org/datatype3.html -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote: Paul McNett wrote: [EMAIL PROTECTED] wrote: Do you know what INNER JOIN means? Do you know how important it is to a relational database? Can you explain how an INNER JOIN can even work, in theory, with dynamic data types? Let's stop the pissing contest and just see how it works. After all, this is Python and we can easily just try it out. Here's my example. Please tell me how this causes unexpected results, When we use a best case scenario, we get what we expect. and why it isn't SQL. It isn't SQL simply because SQL won't let you insert text into a numeric field. Yup, I have to agree that's pretty crappy. (Makes mental note to limit use of SQLite). regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote: Bruno Desthuilliers wrote: SQLite never pretended to be a full-blown RDBMS - just a lightweight simple embedded database as SQL-compliant as possible. Ah, *you* haven't read the documentation either! as SQL-compliant as possible? ROTFLMAO! No need to be rude really. In this context as SQL-compliant as possible means, as SQL-compliant as it is possible to be within the project's restrictions, which presumably refer to code size and speed. It's a reasonable trade-off. ** * The authors argue that static typing is a bug in the * * SQL specification that SQLite has fixed in a backwards * * compatible way.* ** /quote Fixed? Up until now, I didn't think it was possible for crackpot theories to be implemented in computer science. This is absolutely the craziest thing I've ever heard. It's not a crackpot theory. It's a completely reasonable theory. SQL is based on relational algebra, which provides a mathematical set of operators for grouping data that is stored in separate sets. That data is selected and projected according to its value, and nothing else. The concept of it having a 'type' has been overlaid on top of this, presumably to facilitate efficient implementation, which tends to require fixed-width rows (and hence columns). It's not necessary in any sense, and it's reasonable to argue that if it was trivial to implement variable width columns as efficiently as fixed width columns, that explicit data types might never have needed to exist. So much for If switching to a larger database such as PostgreSQL or Oracle is later necessary, the switch should be relatively easy. If you rely too much on a language-enforced data type rather than the values of the underlying data, perhaps Python is not for you! Personally I've migrated from SQLite to MySQL a couple of times (on small projects, granted) and not found it to be a problem at all. Fixing the documentation is now becoming an enormous task. I don't think so... it doesn't take much to say that the module implements a subset of SQL but stores ignores data types. What are the chances that anything I send in as a bug report will simply be ignored? Kind of like the Emporer's New Clothes, eh? It would be an admission of ignorance and stupidity on the part of the Python Development Team, wouldn't it? Why get so bitter over this? I agree the docs need fixing but you make it sound like this was a deliberate attempt to make you waste your time. -- Ben Sizer -- http://mail.python.org/mailman/listinfo/python-list
Is it just me, or is Sqlite3 goofy?
While I can understand your frustration, I think it is important to think about the tone in our postings here. Hydrocephalus is one of the most common birth defects, and it's not terribly unlikely that someone who reads this has a family member or someone else in his proximity who suffers from this condition. [EMAIL PROTECTED] wrote: Fixed? Up until now, I didn't think it was possible for crackpot theories to be implemented in computer science. This is absolutely the craziest thing I've ever heard. Still, many people with lots of experience in databases use it, and prefer it for certain kinds of applications. All systems have limitations and deviations, and those limitations and deviations are stated more clearly for SQLite than for most commercial products at least. The market leader Oracle still can't store empty strings in VARCHAR fields for instance. They are silently converted to NULL. I'm pretty sure that has been in clear violation to the official spec since 1986 at least. As far as I understand, noone here is forcing you to use SQLite, and with your long experience of MS Access I'd expect you to be fairly used to almost SQL... It's some time since I used Jet/Access now, but I had much more problems with that than I've had with SQLite. SQLite is built in Tcl, by someone who appreciates the way Tcl works, with its weak typing. I don't think Tcl's type handling is nearly as clever as Python's, but I think it's a good thing that Python's standard lib finally has a DB-API compliant module, and while I would have preferred something that was closer to standard SQL, I don't know of a better candidate than SQLite. It's good that it's usable without a server setup, and that it's very light weight. A Jet engine is obviously not an option, and I would have preferred SQLite even if Jet was open source and worked on all platforms. (Well, if JET *was* open source, I suspect it would have been fixed by now.) It's possible that one could have used the embedded version of Firebird instead, but in my experience that's not nearly as lean or easy to deploy. With your long experience of Access and SQL Server I'm sure you know well that any attempt to build a working database application requires extensive knowledge of the backend to understand its peculiarities and limitations. The list of software projects where not quite competent developers built Access applications that worked ok in small scale tests and failed catastrophically in real life is looong... Of course, if you've stayed with one vendor for 15 years, I can imagine that you've forgotten how long it took you Having worked with half a dozen backends or so, I'm no longer surprised that SQL can be interpreted in so many ways... I agree that SQLite is unique in it's approach to typing, but if you are aware of this, it's really not a big problem. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
I've made the following edits:
Index: whatsnew25.tex
===
--- whatsnew25.tex (revision 51828)
+++ whatsnew25.tex (working copy)
@@ -2116,14 +2116,16 @@
SQLite embedded database, has been added to the standard library under
the package name \module{sqlite3}.
-SQLite is a C library that provides a SQL-language database that
-stores data in disk files without requiring a separate server process.
+SQLite is a C library that provides a lightweight disk-based database
+that doesn't require a separate server process and allows accessing
+the database using a nonstandard variant of the SQL query language.
+Some applications can use SQLite for internal data storage. It's also
+possible to prototype an application using SQLite and then port the
+code to a larger database such as PostgreSQL or Oracle.
+
pysqlite was written by Gerhard H\aring and provides a SQL interface
compliant with the DB-API 2.0 specification described by
-\pep{249}. This means that it should be possible to write the first
-version of your applications using SQLite for data storage. If
-switching to a larger database such as PostgreSQL or Oracle is
-later necessary, the switch should be relatively easy.
+\pep{249}.
If you're compiling the Python source yourself, note that the source
tree doesn't include the SQLite code, only the wrapper module.
--
http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Ben Sizer wrote: [EMAIL PROTECTED] wrote: Bruno Desthuilliers wrote: SQLite never pretended to be a full-blown RDBMS - just a lightweight simple embedded database as SQL-compliant as possible. Ah, *you* haven't read the documentation either! as SQL-compliant as possible? ROTFLMAO! No need to be rude really. In this context as SQL-compliant as possible means, as SQL-compliant as it is possible to be within the project's restrictions, which presumably refer to code size and speed. It's a reasonable trade-off. And I accept those restrictions. I haven't complained about SQLite being a subset of SQL. It's just that it's not SQL, so it can't even be a subset. ** * The authors argue that static typing is a bug in the * * SQL specification that SQLite has fixed in a backwards * * compatible way.* ** /quote Fixed? Up until now, I didn't think it was possible for crackpot theories to be implemented in computer science. This is absolutely the craziest thing I've ever heard. It's not a crackpot theory. It's a completely reasonable theory. SQL is based on relational algebra, which provides a mathematical set of operators for grouping data that is stored in separate sets. That data is selected and projected according to its value, and nothing else. Ok, it's not crackpot with respect to relational algebra. The concept of it having a 'type' has been overlaid on top of this, presumably to facilitate efficient implementation, which tends to require fixed-width rows (and hence columns). It's not necessary in any sense, and it's reasonable to argue that if it was trivial to implement variable width columns as efficiently as fixed width columns, that explicit data types might never have needed to exist But they do exist as far as the SQL Language Specification is concerned. Isn't this just like the parallel postualate in Euclidean Geometry? Sure, the parallel postulate isn't absolute truth in geometry theory, but once you make it an axiom, then calling it a bug and trying to fix it while claiming it's still Euclidean Geometry is crackpot math. If the SQLite author wants to make a new system based on a different set of relational algebra axioms, that's fine. But claiming the SQL Language Specification axioms are bugs that need to be fixed is crackpot. So much for If switching to a larger database such as PostgreSQL or Oracle is later necessary, the switch should be relatively easy. If you rely too much on a language-enforced data type rather than the values of the underlying data, perhaps Python is not for you! Suppose someone said this is a new implementation of Python. And all your programs crashed in it. Then, buried deep inside the documentation (so as not to affect sales), you find that the author has an issue with dynamic data types and in HIS version of Python, all data types are static. Wouldn't you argue that what he has isn't Python? Personally I've migrated from SQLite to MySQL a couple of times (on small projects, granted) and not found it to be a problem at all. And yet, I, knowing how SQL is supposed to work, had all kinds of problems. Why do you think I started this thread? Because the kind of things that work in SQL-compliant systems weren't working in SQLite. Fixing the documentation is now becoming an enormous task. I don't think so... it doesn't take much to say that the module implements a subset of SQL but stores ignores data types. So I can't just say WHERE qty=12 I have to say WHERE (qty=12) OR (qty=12) otherwise I can't guarantee that my query will return the records I want. Oh, and that will cause a Type mis-match error in MS-Access meaning I literally can't migrate this query from SQLite to MS-Access (or any other SQL-compliant database). Do you know what INNER JOIN means? Do you know how important it is to a relational database? Can you explain how an INNER JOIN can even work, in theory, with dynamic data types? The implications of violating the SQL Language Specification are much more serious than you think. What are the chances that anything I send in as a bug report will simply be ignored? Kind of like the Emporer's New Clothes, eh? It would be an admission of ignorance and stupidity on the part of the Python Development Team, wouldn't it? Why get so bitter over this? I agree the docs need fixing but you make it sound like this was a deliberate attempt to make you waste your time. I'm not bitter, just being a squeaky wheel. And I'd be more than happy to make PROPER corrections to the docs if I thought the effort wouldn't be wasted. But in looking over this thread, it certainly appears that there are very few who understand the issue. -- Ben Sizer -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote: Ben Sizer wrote: [EMAIL PROTECTED] wrote: [...] What are the chances that anything I send in as a bug report will simply be ignored? Kind of like the Emporer's New Clothes, eh? It would be an admission of ignorance and stupidity on the part of the Python Development Team, wouldn't it? Why get so bitter over this? I agree the docs need fixing but you make it sound like this was a deliberate attempt to make you waste your time. I'm not bitter, just being a squeaky wheel. Ain't that the truth :-) And I'd be more than happy to make PROPER corrections to the docs if I thought the effort wouldn't be wasted. But in looking over this thread, it certainly appears that there are very few who understand the issue. Well, as I've already suggested, sound corrections and/or additions to the documentation are *very* unlikely to be refused. I suspect you need to be a little more pragmatic with regard to SQLite. If it doesn't suit your needs, that's fine. If you can suggest changes that will avoid others experiencing the same disappointment you have, even better. regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Magnus Lycka wrote: While I can understand your frustration, I think it is important to think about the tone in our postings here. Hydrocephalus is one of the most common birth defects, and it's not terribly unlikely that someone who reads this has a family member or someone else in his proximity who suffers from this condition. Ok, I appologize. No more Larry the Cable Guy humor. I'll stick with Monty Python and Douglas Adams. [EMAIL PROTECTED] wrote: Fixed? Up until now, I didn't think it was possible for crackpot theories to be implemented in computer science. This is absolutely the craziest thing I've ever heard. Still, many people with lots of experience in databases use it, and prefer it for certain kinds of applications. All systems have limitations and deviations, and those limitations and deviations are stated more clearly for SQLite than for most commercial products at least. But they're not stated up front. The deviations from standard SQL are extreme enough to warrant mentioning in the Python docs. The market leader Oracle still can't store empty strings in VARCHAR fields for instance. They are silently converted to NULL. I'm pretty sure that has been in clear violation to the official spec since 1986 at least. But does Oracle claim the specification is a bug? As far as I understand, noone here is forcing you to use SQLite, As long as it's included in the standard library, I'm going to use it. There is nothing wrong with the idea of a lite database. It is very misleading, though, to claim it's SQL. and with your long experience of MS Access I'd expect you to be fairly used to almost SQL... It's some time since I used Jet/Access now, but I had much more problems with that than I've had with SQLite. SQLite is built in Tcl, by someone who appreciates the way Tcl works, with its weak typing. I don't think Tcl's type handling is nearly as clever as Python's, but I think it's a good thing that Python's standard lib finally has a DB-API compliant module, and while I would have preferred something that was closer to standard SQL, I don't know of a better candidate than SQLite. It's good that it's usable without a server setup, and that it's very light weight. A Jet engine is obviously not an option, and I would have preferred SQLite even if Jet was open source and worked on all platforms. (Well, if JET *was* open source, I suspect it would have been fixed by now.) It's possible that one could have used the embedded version of Firebird instead, but in my experience that's not nearly as lean or easy to deploy. With your long experience of Access and SQL Server I'm sure you know well that any attempt to build a working database application requires extensive knowledge of the backend to understand its peculiarities and limitations. The list of software projects where not quite competent developers built Access applications that worked ok in small scale tests and failed catastrophically in real life is looong... Of course, if you've stayed with one vendor for 15 years, I can imagine that you've forgotten how long it took you Having worked with half a dozen backends or so, I'm no longer surprised that SQL can be interpreted in so many ways... I agree that SQLite is unique in it's approach to typing, but if you are aware of this, it's really not a big problem. Ok, I'm now aware of it. I'm aware that all my WHERE clauses will have to be modified to allow for text that may show up in my numeric fields. I'm aware that all my ORDER BY clauses will have to be modified for the same reason. And I'm aware that INNER JOIN doesn't even work in theory with dynamic typed fields and can't even be worked around like the WHERE and ORDER BY clauses can. And given a typical query I use: SELECT tblLocations.SiteID, tblSites.SiteName, tblLocations.IEPALocationNumber, tblZones.Zone, tblSampleEvent.SampleEventCode, tblSampleAnalyses.SampleDate, tblSamples.SampleMatrixID, tblSamples.SampleNumber, tblRefQAQCTypes.QAQCType, tblResults.LabSampleNumber, tblRefParameterGroups.ParameterGroupCode, tblSampleAnalyses.AnalysisID, tblRefAnalyses.AnalysisTypeCode, tblRefParameters.ParameterReportLabel, tblRefParameters.CASNumber, tblResults.Result, tblResults.Qualifier, tblRefUnitOfMeasure.Unit, Val(Format$(IIf(([tblResults].[unitid]=5) Or ([tblResults].[unitid]=7), [result]/1000,[result]), 0.)) AS the_ppm_result, IIf([tblResults].[unitid]=7,mg/kg, IIf([tblResults].[unitid]=5,mg/L, [unit])) AS the_ppm_unit, Val(Format$(IIf(([tblResults].[unitid]=5) Or ([tblResults].[unitid]=7), [quantitationlimit]/1000, [quantitationlimit]),0.)) AS
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] [EMAIL PROTECTED] writes: As long as it's included in the standard library, I'm going to use it. There is nothing wrong with the idea of a lite database. It is very misleading, though, to claim it's SQL. Maybe it could be renamed by changing the t in lite to k. -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
In [EMAIL PROTECTED], [EMAIL PROTECTED] wrote: Ben Sizer wrote: I don't think so... it doesn't take much to say that the module implements a subset of SQL but stores ignores data types. So I can't just say WHERE qty=12 I have to say WHERE (qty=12) OR (qty=12) No you just write the first and don't stuff strings into that column. It's your choice after all. Do you know what INNER JOIN means? Do you know how important it is to a relational database? Can you explain how an INNER JOIN can even work, in theory, with dynamic data types? The same as with static types!? Where's the problem? table_a: idname - -- 42bob 'foo' methusalem '42' gaylord table_b: idage - -- 4223 'foo' 1000 0.5 None Join on `id`: idname age - -- 42bob23 'foo' methusalem 1000 The implications of violating the SQL Language Specification are much more serious than you think. Or not so serious than you think. Ciao, Marc 'BlackJack' Rintsch -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote:
Do you know what INNER JOIN means?
Do you know how important it is to a relational database?
Can you explain how an INNER JOIN can even work, in theory,
with dynamic data types?
Let's stop the pissing contest and just see how it works. After all,
this is Python and we can easily just try it out. Here's my example.
Please tell me how this causes unexpected results, and why it isn't SQL.
Please modify my example to get it to cause a catastrophe, and post it
here so we can see the errors of our ways and be properly humbled.
#-- Preliminaries:
from pysqlite2 import dbapi2 as sqlite
con = sqlite.connect(test.db)
cur = con.cursor()
#-- Create 3 tables for a M:M relationship between customers
#-- and categories:
cur.execute(create table customers (id integer primary key
autoincrement, name char))
cur.execute(create table categories (id integer primary key
autoincrement, name char))
cur.execute(create table cust_cat (id integer primary key
autoincrement, cust_id integer, cat_id integer))
#-- Insert some test data into customer and categories:
cur.execute(insert into customers (name) values ('Ziggy Marley'))
cur.execute(insert into customers (name) values ('David Bowie'))
cur.execute(insert into categories (name) values ('Glam Rock'))
cur.execute(insert into categories (name) values ('Nuevo Reggae'))
cur.execute(insert into categories (name) values ('Male Singers'))
cur.execute(select * from customers)
#-- Take a look at the data (and retrieve the pk's):
cur.fetchall()
[(1, u'Ziggy Marley'), (2, u'David Bowie')]
cur.execute(select * from categories)
cur.fetchall()
[(1, u'Glam Rock'), (2, u'Nuevo Reggae'), (3, u'Male Singers')]
#-- Relate some customers to some categories. Note how I send strings
#-- in some places and ints in others:
cur.execute(insert into cust_cat (cust_id, cat_id) values (1, 3))
cur.execute(insert into cust_cat (cust_id, cat_id) values (1, '2'))
cur.execute(insert into cust_cat (cust_id, cat_id) values ('2', '1'))
cur.execute(insert into cust_cat (cust_id, cat_id) values ('2', 3))
#-- Run some queries:
cur.execute(select customers.id as cust_id, customers.name as
cust_name, categories.id as cat_id, categories.name as cat_name from
customers inner join cust_cat on cust_cat.cust_id = customers.id inner
join categories on categories.id = cust_cat.cat_id order by 2,4)
cur.fetchall()
[(2, u'David Bowie', 1, u'Glam Rock'), (2, u'David Bowie', 3, u'Male
Singers'), (1, u'Ziggy Marley', 3, u'Male Singers'), (1, u'Ziggy
Marley', 2, u'Nuevo Reggae')]
cur.execute(select customers.id as cust_id, customers.name as
cust_name, categories.id as cat_id, categories.name as cat_name from
customers inner join cust_cat on cust_cat.cust_id = customers.id inner
join categories on categories.id = cust_cat.cat_id where categories.id =
1 order by 2,4)
cur.fetchall()
[(2, u'David Bowie', 1, u'Glam Rock')]
cur.execute(select customers.id as cust_id, customers.name as
cust_name, categories.id as cat_id, categories.name as cat_name from
customers inner join cust_cat on cust_cat.cust_id = customers.id inner
join categories on categories.id = cust_cat.cat_id where categories.id =
'1' order by 2,4)
cur.fetchall()
[(2, u'David Bowie', 1, u'Glam Rock')]
cur.execute(select customers.id as cust_id, customers.name as
cust_name, categories.id as cat_id, categories.name as cat_name from
customers inner join cust_cat on cust_cat.cust_id = customers.id inner
join categories on categories.id = cust_cat.cat_id where categories.id =
'2' order by 2,4)
cur.fetchall()
[(1, u'Ziggy Marley', 2, u'Nuevo Reggae')]
cur.execute(select customers.id as cust_id, customers.name as
cust_name, categories.id as cat_id, categories.name as cat_name from
customers inner join cust_cat on cust_cat.cust_id = customers.id inner
join categories on categories.id = cust_cat.cat_id where categories.id =
'3' order by 2,4)
cur.fetchall()
[(2, u'David Bowie', 3, u'Male Singers'), (1, u'Ziggy Marley', 3, u'Male
Singers')]
cur.execute(select customers.id as cust_id, customers.name as
cust_name, categories.id as cat_id, categories.name as cat_name from
customers inner join cust_cat on cust_cat.cust_id = customers.id inner
join categories on categories.id = cust_cat.cat_id where categories.id =
3 order by 2,4)
cur.fetchall()
[(2, u'David Bowie', 3, u'Male Singers'), (1, u'Ziggy Marley', 3, u'Male
Singers')]
If I have skipped the test case that will fail, please enlighten me.
--
Paul McNett
http://paulmcnett.com
http://dabodev.com
--
http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Marc 'BlackJack' Rintsch wrote: In [EMAIL PROTECTED], [EMAIL PROTECTED] wrote: Ben Sizer wrote: I don't think so... it doesn't take much to say that the module implements a subset of SQL but stores ignores data types. So I can't just say WHERE qty=12 I have to say WHERE (qty=12) OR (qty=12) No you just write the first and don't stuff strings into that column. It's your choice after all. Not when I don't control the source of the data. Despite the laboratory having been given a static type definition for data deliverables, I sporadically find text in my numeric fields. And surely you don't think I write INSERT statements for each of the over 500,000 records I have? The records are inserted programatically. Static types mean the exceptions...raise exceptions. How am I supposed to identify the exceptions if SQLite simply quietly converts the non-numeric data to text? Do you know what INNER JOIN means? Do you know how important it is to a relational database? Can you explain how an INNER JOIN can even work, in theory, with dynamic data types? The same as with static types!? Where's the problem? table_a: idname - -- 42bob 'foo' methusalem '42' gaylord table_b: idage - -- 4223 'foo' 1000 0.5 None Join on `id`: idname age - -- 42bob23 'foo' methusalem 1000 Numbers JOINing numbers and text JOINing text doesn't illustrate the problem. The problem is when SQLite allows bad data to be inserted. The problem isn't that fields JOIN when they are not supposed to, it's when they fail to JOIN when they are supposed to. The implications of violating the SQL Language Specification are much more serious than you think. Or not so serious than you think. Well, I can make up examples also. Bill for customer:Tom Smith -- 0003 Olsen's Standard Book of British Birds (Expurgated) $ 99.95 Bill for customer: Dick Smith -- 0002 A Sale of Two Titties$ 20.00 0005 David Coperfield $ 3.95 Bill for customer: Harry Smith -- 0001 101 Ways to Start A Fight$ 19.95 Hmm...looks like item 4 isn't being billed to anyone. That's no way to run a business. If I do a LEFT JOIN instead of an INNER JOIN: Bill for customer: None None -- 0004 Ethel the Aardvark Goes Quantity Surveying $ 9.99 Bill for customer:Tom Smith -- 0003 Olsen's Standard Book of British Birds (Expurgated) $ 99.95 Bill for customer: Dick Smith -- 0002 A Sale of Two Titties$ 20.00 0005 David Coperfield $ 3.95 Bill for customer: Harry Smith -- 0001 101 Ways to Start A Fight$ 19.95 I see the missing item, but still don't know who to bill it to. What if I dumped the invoice table? 4 54 Ethel the Aardvark Goes Quantity Surveying None 1975 9.99 3 001 Olsen's Standard Book of British Birds (Expurgated) Olsen None 99.95 2 032 A Sale of Two Titties Charles Dikkens 1855 20.0 5 032 David Coperfield Edmund Wells 1955 3.95 1 066 101 Ways to Start A Fight some Irish gentleman 1919 19.95 Aha, it was sold to customer 54, so now I just need to dump the customer table: 001 Tom Smith 42 032 Dick Smith 28 066 Harry Smith 66 only to discover there is no customer 54! How can that happen? invoices = [(1,'066','101 Ways to Start A Fight','some Irish gentleman',1919,19.95), \ (2,'032','A Sale of Two Titties','Charles Dikkens',1855,20.00), \ (3,'001',Olsen's Standard Book of British Birds (Expurgated),Olsen,None,99.95), \ (4,066,'Ethel the Aardvark Goes Quantity Surveying',None,1975,9.99), \ (5,'032','David Coperfield','Edmund Wells',1955,3.95)] Oops! Forgot the quotes around the customer id for item 4. But why didn't it become 66? Because the leading 0 made it octal! A little static typing would have helped here. Now, IF the same error is repeated in EVERY table that's JOINed, THEN the dynamic typing won't be a problem. But that never happens in practice. I populate one table and ensure it's correct. Someone else populates the other table and screws it up so the JOIN never works. Sure, errors happen with static typing. After all, the values still have to match. Dynamic typing allows for more potential errors and, thanks to Murpy's Law, I will have a much bigger problem with data integrity. Ciao, Marc 'BlackJack' Rintsch --
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Ben Sizer wrote: It's not a crackpot theory. It's a completely reasonable theory. SQL is based on relational algebra, which provides a mathematical set of operators for grouping data that is stored in separate sets. That data is selected and projected according to its value, and nothing else. The concept of it having a 'type' has been overlaid on top of this, presumably to facilitate efficient implementation, which tends to require fixed-width rows (and hence columns). It's not necessary in any sense, and it's reasonable to argue that if it was trivial to implement variable width columns as efficiently as fixed width columns, that explicit data types might never have needed to exist. The mathematical definition of the relational model includes that data values are drawn from specific sets. Implementing variable width columns has nothing to do with it. Here's the reference: 1.3. A Relational View of Data The term relation is used here in its accepted mathematical sense. Given sets S1, S2, ···, Sn, (not necessarily distinct), R is a relation on these n sets if it is a set of n-tuples each of which has its first element from S1, its second element from S2, and so on [1]. We shall refer to Sj as the jth domain of R. [Footnote 1:] More concisely, R is a subset of the Cartesian product S1* S2 * ... * Sn. -- E. F. Codd. A Relational Model of Data for Large Shared Data Banks. /Communications of the ACM/, Vol. 13, No. 6, June 1970, pp. 377-387. -- --Bryan -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote: Sure, errors happen with static typing. After all, the values still have to match. Dynamic typing allows for more potential errors and, thanks to Murpy's Law, I will have a much bigger problem with data integrity. If this was a java or c++ list, all this rant would be more understandable, but bashing dynamic typing in a dynamic language list seems pointless at best (as this has been beaten to death over and over again), flamebait at worst. It should be clear by now that there are two (at least) alternatives: 1. Validate the data in python before (or at the same time when) feeding the DB. 2. Forget sqlite and use a statically typed DBMS; it's not like there is a shortage of them. Massaging your SQL statements to make up for the lack of type checking (even if this is always possible) would be a bad idea for more than one reasons (complexity,portability,performance), so you'd better not go down this road. George -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
It's not a bug, it's a feature. And answered as third point in the FAQ: http://www.sqlite.org/faq.html#q3 I think your whole experience is based on it. Live with it or use a real RDBMS. If you are so fond of static typing, why are you using Python in the first place? Just see it as consistency -- dynamically typed language → dynamically typed DB columns. ;-) Ciao, Marc 'BlackJack' Rintsch I have to admit I find this bogus too. It has by no means anything to do with static typing but letting errors pass silently i.e. deactivating runtime type checks as well. The problem here is that fields are not dynamically type checked but completely untyped and only coercion hints are present. Using a clever coercion / type-affinity does not justify that there is no error case handling when the coercion fails. This might be handled by user code ( or better by the pysqlite wrapper ) but it appears to be redundant. -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
George Sakkis wrote: [EMAIL PROTECTED] wrote: Sure, errors happen with static typing. After all, the values still have to match. Dynamic typing allows for more potential errors and, thanks to Murpy's Law, I will have a much bigger problem with data integrity. If this was a java or c++ list, all this rant would be more understandable, but bashing dynamic typing in a dynamic language list seems pointless at best (as this has been beaten to death over and over again), flamebait at worst. But I'm not bashing Python's use of dynamic typing. But if the SQL Language Specification says static typing, then static typing it is. Period. It should be clear by now that there are two (at least) alternatives: 1. Validate the data in python before (or at the same time when) feeding the DB. Data integrity is an issue even with static typing. It's a bigger issue with dynamic typing. 2. Forget sqlite and use a statically typed DBMS; it's not like there is a shortage of them. I have no intention of forgetting sqlite simply because it's now part of the standard library. I have now qualms about using it *now* because I understand it better. But reaching that level of understanding was like pulling teeth. Documentation shouldn't talk down to the reader. It's always bad when you confuse the smart people. The ignorant are supposed to be confused. It's job of the documentation to educate the ignorant. Hiding the idiosynchrocies of Sqlite3 from the user who's already familiar with SQL is simply unacceptable. Massaging your SQL statements to make up for the lack of type checking (even if this is always possible) would be a bad idea for more than one reasons (complexity,portability,performance), so you'd better not go down this road. George -- http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Dennis Lee Bieber wrote: On 8 Sep 2006 16:46:03 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] declaimed the following in comp.lang.python: snip After a sequence of hypothetical results of occult SQL you show this... invoices = [(1,'066','101 Ways to Start A Fight','some Irish gentleman',1919,19.95), \ (2,'032','A Sale of Two Titties','Charles Dikkens',1855,20.00), \ (3,'001',Olsen's Standard Book of British Birds (Expurgated),Olsen,None,99.95), \ (4,066,'Ethel the Aardvark Goes Quantity Surveying',None,1975,9.99), \ (5,'032','David Coperfield','Edmund Wells',1955,3.95)] ... A Python list of tuples! Oops! Forgot the quotes around the customer id for item 4. But why didn't it become 66? Because the leading 0 made it octal! A little static typing would have helped here. Do you routinely populate your databases by editing python lists of tuples? I don't routinely do anything, as I've only been playing with it for 4 days. And if you do, why not show us the table definition and insert statements that go with the above data? I was simply following the examples in the Python manuals. If the examples are poor usage, maybe they shouldn't be in the manuals. Or do you get the data from some file... What format is that file? No file. Point is moot. Would you agree that the following is a clean representation of your example data, when considered as a CSV data source? (I left off the primary key -- we'll just let SQLite add that value). I DID, however, normalize the quoting by changing ' to . (please ignore the line wrap on the (Expurgated) -- it does not exist in the data file) invoice.csv -=-=-=-=-=- 066,101 Ways to Start A Fight,some Irish gentleman,1919,19.95 032,A Sale of Two Titties,Charles Dikkens,1855,20.00 001,Olsens Standard Book of British Birds (Expurgated),Olsen,None,99.95 066,Ethel the Aardvark Goes Quantity Surveying,None,1975,9.99 032,David Coperfield,Edmund Wells,1955,3.95 -=-=-=-=-=- Now, since you seem to believe that a customer number is a string data type (implied by the presence of quotes around all but the bad data record), I've so defined it in the table definition... Ah, but what the heck, let's create a table with it defined as an integer too... SQLiteTest.py -=-=-=-=-=-=-=- from pysqlite2 import dbapi2 as sql import csv TABLE_DEF_1 = create table invoice_1 ( ID integer primary key, CustNo char, Title char, Author char, Year integer, Price float ) TABLE_DEF_2 = create table invoice_2 ( ID integer primary key, CustNo integer, Title char, Author char, Year integer, Price float ) db = sql.connect(test.db) cr = db.cursor() try: rs = cr.execute(TABLE_DEF_1) db.commit() except: #I know, I should be explicit pass#assume table already exists try: rs = cr.execute(TABLE_DEF_2) db.commit() except: #I know, I should be explicit pass#assume table already exists fin = open(invoice.csv, rb) indata = csv.reader(fin) print \nInserting: for r in indata: print r if len(r) != 5: print ^^^Bad Record else: rs = cr.execute(insert into invoice_1 (CustNo, Title, Author, Year, Price) values (?,?,?,?,?), r) rs = cr.execute(insert into invoice_2 (CustNo, Title, Author, Year, Price) values (?,?,?,?,?), r) db.commit() fin.close() print \nSelect all from Invoice_1 (CustNo is CHARACTER) rs = cr.execute(select * from invoice_1) for r in cr: print r print \nSelect all from Invoice_2 (CustNo is INTEGER) rs = cr.execute(select * from invoice_2) for r in cr: print r db.close() -=-=-=-=-=-=-=- Now, let us run the above program, using the above data file! Again, watch out for line wrapping (my comments will be blocked off with ) E:\UserData\Dennis Lee Bieber\My Documents\Python Progspython SQLiteTest.py Inserting: ['066', '101 Ways to Start A Fight', 'some Irish gentleman', '1919', '19.95'] ['032', 'A Sale of Two Titties', 'Charles Dikkens', '1855', '20.00'] ['001', 'Olsens Standard Book of British Birds (Expurgated)', 'Olsen', 'None', '99.95'] ['066', 'Ethel the Aardvark Goes Quantity Surveying', 'None', '1975', '9.99'] ['032', 'David Coperfield', 'Edmund Wells', '1955', '3.95'] Well, look at that... ALL the data from the file is coming in as character strings... customer number, year, price, title, author... It's ALL character! The difference between quoted and unquoted numbers has been lost. How 'bout that? Maybe I should try harder to make a better example. Select all from Invoice_1 (CustNo is CHARACTER) (1, u'066', u'101 Ways to Start A
SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
Bruno Desthuilliers wrote:
[EMAIL PROTECTED] wrote:
Probably just me. I've only been using Access and SQL Server
for 12 years, so I'm sure my opinions don't count for anything.
SQLite never pretended to be a full-blown RDBMS - just a lightweight
simple embedded database as SQL-compliant as possible.
Ah, *you* haven't read the documentation either!
as SQL-compliant as possible?
ROTFLMAO!
From SQLite Homepage
documentation
Available Documentation
Distinctive Features
quote *emphasis added*
This page highlights some of the characteristics of SQLite
that are unusual and which make SQLite different from many
other SQL database engines.
Manifest typing
Most SQL database engines use static typing. A datatype is
associated with each column in a table and only values of
that particular datatype are allowed to be stored in that
column. SQLite relaxes this restriction by using manifest
typing.
In manifest typing, the datatype is a property of the
value itself, not of the column in which the value is
stored. SQLite thus allows the user to store any value
of any datatype into any column regardless of the declared
type of that column. (There are some exceptions to this
rule: An INTEGER PRIMARY KEY column may only store
integers. And SQLite attempts to coerce values into the
declared datatype of the column when it can.)
***
* The SQL language specification calls for static typing. *
***
So some people feel that the use of manifest typing is a
bug in SQLite. But the authors of SQLite feel very
strongly that this is a feature.
**
* The authors argue that static typing is a bug in the *
* SQL specification that SQLite has fixed in a backwards *
* compatible way.*
**
/quote
Fixed? Up until now, I didn't think it was possible for
crackpot theories to be implemented in computer science.
This is absolutely the craziest thing I've ever heard.
Manifest typing doesn't work *in theory*!! Sure, through
diligent data integrity management it can be made to work
in practice. But when that's applied, guess what? All your
fields end up having the same data type anyway. Duh.
And Python's inclusion of this into the standard library
is like the AMA endorsing homeopathy. It should have
been pointed out in the Python Manuals that SQLite3 is
NOT
SQL-compliant, never will be, and true SQL-compliant code
CANNOT
be made to work in this environment. So much for
If switching to a larger database such as PostgreSQL or Oracle
is later necessary, the switch should be relatively easy.
That does not, of course, mean I don't have a use for a light,
simple database that's part of the standard library. But I could
have saved myself a lot of wailing and gnashing of teeth if I
hadn't been lied to by the Python documentation that, like you,
has the mistaken belief that SQLite3 is SQL-compliant.
Fixing the documentation is now becoming an enormous task.
What are the chances that anything I send in as a bug report
will simply be ignored? Kind of like the Emporer's New Clothes, eh?
It would be an admission of ignorance and stupidity on the part
of the Python Development Team, wouldn't it?
In it's category,
it beats Access and MySQL hands down. Now if you want a real RDBMS,
you've just failed to choose the right tool. May I suggest PostgreSQL ?
(snip useless rant)
--
bruno desthuilliers
python -c print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])
--
http://mail.python.org/mailman/listinfo/python-list
Re: SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)
[EMAIL PROTECTED] wrote: Bruno Desthuilliers wrote: [EMAIL PROTECTED] wrote: Probably just me. I've only been using Access and SQL Server for 12 years, so I'm sure my opinions don't count for anything. SQLite never pretended to be a full-blown RDBMS - just a lightweight simple embedded database as SQL-compliant as possible. Ah, *you* haven't read the documentation either! as SQL-compliant as possible? ROTFLMAO! From SQLite Homepage documentation Available Documentation Distinctive Features quote *emphasis added* This page highlights some of the characteristics of SQLite that are unusual and which make SQLite different from many other SQL database engines. Manifest typing Most SQL database engines use static typing. A datatype is associated with each column in a table and only values of that particular datatype are allowed to be stored in that column. SQLite relaxes this restriction by using manifest typing. In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column. (There are some exceptions to this rule: An INTEGER PRIMARY KEY column may only store integers. And SQLite attempts to coerce values into the declared datatype of the column when it can.) *** * The SQL language specification calls for static typing. * *** So some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. ** * The authors argue that static typing is a bug in the * * SQL specification that SQLite has fixed in a backwards * * compatible way.* ** /quote Fixed? Up until now, I didn't think it was possible for crackpot theories to be implemented in computer science. This is absolutely the craziest thing I've ever heard. Manifest typing doesn't work *in theory*!! Sure, through diligent data integrity management it can be made to work in practice. But when that's applied, guess what? All your fields end up having the same data type anyway. Duh. And Python's inclusion of this into the standard library is like the AMA endorsing homeopathy. It should have been pointed out in the Python Manuals that SQLite3 is NOT SQL-compliant, never will be, and true SQL-compliant code CANNOT be made to work in this environment. So much for If switching to a larger database such as PostgreSQL or Oracle is later necessary, the switch should be relatively easy. Sorry. I have written code that started out on SQLite and migrated it to other databases. Sure, your milage may vary. But the wailing and gnashing of teeth is complete unnecessary. That does not, of course, mean I don't have a use for a light, simple database that's part of the standard library. But I could have saved myself a lot of wailing and gnashing of teeth if I hadn't been lied to by the Python documentation that, like you, has the mistaken belief that SQLite3 is SQL-compliant. Fixing the documentation is now becoming an enormous task. What are the chances that anything I send in as a bug report will simply be ignored? Kind of like the Emporer's New Clothes, eh? It would be an admission of ignorance and stupidity on the part of the Python Development Team, wouldn't it? No, when they need ignorance and stupidity they normally look to the user base ;-) I'm sure your doc patch will receive the same courteous consideration that my early suggestions for amendment did, and your name will join mine in the list of contributors. regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: I think your whole experience is based on it. But shouldn't a significant feature like that be explained in the Python manuals? Why should I go dig up Sqlite FAQs to learn what should have been in the manuals? I don't know, but I will take a stab at a plausible explanation. First, sqlite support has only been in Python for a month or three. Its first official unveiling will be when 2.5 is released. Although possibly too late for the final release, now would be a good time to straighten out the documentation. And you would be the best person to do it, since you're teh one this has bitten in the tender parts. Second, it's common when wrapping functionality into Python to rely on the documentation for the thing being wrapped. The thinner the wrapper, the more you tend to rely on the underlying documentation. Also, the more functionally rich the thing you've wrapped, the more you rely on the underlying documentation. I wouldn't be at all surprised if the pysqlite author operated under that assumption. Ok, that's certainly plausible. But it's not an excuse. The thinner the documentation, the greater the emphasis should be made to point the reader to a more adequate source. Simply listing the Sqlite home page at the bottom of the page is hardly good enough. It should be explicitly stated in bold letters that the reader should go read the Sqlite FAQ because it radically differs from *real* databases and provide a seperate link to it in the body of the documentation. Whoa, there! This isn't commercial software we are talking about. While I appreciate the need to continually better Python's documentation, the should implies a moral imperative that the (volunteer) developers are unikely to find compelling. That the Python developers didn't pick up on the issue is not surprising. I'm not sure how many of them are (py)sqlite users, probably relatively few. I would be surprised if they had never used ANY database. A little thing like dynamic field typing will simply make it impossible to migrate your Sqlite data to a *real* database. What I'll do is re-format my rant, suggest how *I* would do the documentation, fix the errors I found in the examples and send it off to the Python bug tracking as suggested in the manuals. How's that as a plan? That's the ticket. Great idea. Changes to the documentation can be suggested in plain ASCII, you don't have to grok the LaTeX markup. regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: I think your whole experience is based on it. But shouldn't a significant feature like that be explained in the Python manuals? Why should I go dig up Sqlite FAQs to learn what should have been in the manuals? I don't know, but I will take a stab at a plausible explanation. First, sqlite support has only been in Python for a month or three. Its first official unveiling will be when 2.5 is released. Although possibly too late for the final release, now would be a good time to straighten out the documentation. And you would be the best person to do it, since you're the one this has bitten in the tender parts. Second, it's common when wrapping functionality into Python to rely on the documentation for the thing being wrapped. The thinner the wrapper, the more you tend to rely on the underlying documentation. Also, the more functionally rich the thing you've wrapped, the more you rely on the underlying documentation. I wouldn't be at all surprised if the pysqlite author operated under that assumption. Ok, that's certainly plausible. But it's not an excuse. The thinner the documentation, the greater the emphasis should be made to point the reader to a more adequate source. Simply listing the Sqlite home page at the bottom of the page is hardly good enough. It should be explicitly stated in bold letters that the reader should go read the Sqlite FAQ because it radically differs from *real* databases and provide a seperate link to it in the body of the documentation. Whoa, there! This isn't commercial software we are talking about. While I appreciate the need to continually better Python's documentation, the should implies a moral imperative that the (volunteer) developers are unlikely to find compelling. That the Python developers didn't pick up on the issue is not surprising. I'm not sure how many of them are (py)sqlite users, probably relatively few. I would be surprised if they had never used ANY database. A little thing like dynamic field typing will simply make it impossible to migrate your Sqlite data to a *real* database. What I'll do is re-format my rant, suggest how *I* would do the documentation, fix the errors I found in the examples and send it off to the Python bug tracking as suggested in the manuals. How's that as a plan? That's the ticket. Great idea. Changes to the documentation can be suggested in plain ASCII, you don't have to grok the LaTeX markup. regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] wrote:
(snip)
But shouldn't a significant feature like that be explained
in the Python manuals?
Why should it ? It's a SQLite feature, not a Python one.
Why should I go dig up Sqlite
FAQs to learn what should have been in the manuals?
Why should you read the manuals at all then ?
Live with it or use a real RDBMS.
I don't mind living with it as long as it's documented.
It is. In SQLite manual. Or do you hope the Python manual to also fully
document PostgreSQL, MySQL, Oracle, Apache, Posix, Win32 etc ?
--
bruno desthuilliers
python -c print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])
--
http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Bruno Desthuilliers wrote: [EMAIL PROTECTED] wrote: I don't mind living with it as long as it's documented. It is. In SQLite manual. Or do you hope the Python manual to also fully document PostgreSQL, MySQL, Oracle, Apache, Posix, Win32 etc ? With those other applications, you have a separate download. With sqlite, you don't, on Windows at least. Surely all the 'included batteries' should have local documentation, especially with the type conversions. -- Ben Sizer -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
Bruno Desthuilliers [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: (snip) But shouldn't a significant feature like that be explained in the Python manuals? Why should it ? It's a SQLite feature, not a Python one. You have missed the key point that, as of Python 2.5, SQLite 3 is part of the Python 2.5 standard library. So, at this point, it IS a Python feature. I don't mind living with it as long as it's documented. It is. In SQLite manual. Or do you hope the Python manual to also fully document PostgreSQL, MySQL, Oracle, Apache, Posix, Win32 etc ? Warning: misplaced sarcasm detected -- - Tim Roberts, [EMAIL PROTECTED] Providenza Boekelheide, Inc. -- http://mail.python.org/mailman/listinfo/python-list
Re: Is it just me, or is Sqlite3 goofy?
[EMAIL PROTECTED] wrote: Probably just me. I've only been using Access and SQL Server for 12 years, so I'm sure my opinions don't count for anything. [...] Ok, next issue, what the fuck are [varchar] and [decimal]? [..] It's still fuckin' goofy. Language ... regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://holdenweb.blogspot.com Recent Ramblings http://del.icio.us/steve.holden -- http://mail.python.org/mailman/listinfo/python-list
