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: > > id name > ----- ---------- > 42 bob > 'foo' methusalem > '42' gaylord > > table_b: > > id age > ----- ------ > 42 23 > 'foo' 1000 > 0.5 None > > Join on `id`: > > id name age > ----- ---------- ---- > 42 bob 23 > '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 -- http://mail.python.org/mailman/listinfo/python-list