Hi, I am interested in joining the sqlite-users mailing list. Kindly add my name. Thanks & Regards, Mahalakshmi
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Friday, February 08, 2008 8:22 AM To: sqlite-users@sqlite.org Subject: sqlite-users Digest, Vol 2, Issue 16 Send sqlite-users mailing list submissions to sqlite-users@sqlite.org To subscribe or unsubscribe via the World Wide Web, visit http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users or, via email, send a message with subject or body 'help' to [EMAIL PROTECTED] You can reach the person managing the list at [EMAIL PROTECTED] When replying, please edit your Subject line so it is more specific than "Re: Contents of sqlite-users digest..." Today's Topics: 1. Why attach databases? (Jason Tudor) 2. Re: Why attach databases? (David Baird) 3. Re: Why attach databases? (Samuel R. Neff) 4. Re: Why attach databases? (Nicolas Williams) 5. Re: Why attach databases? (Jason Tudor) 6. Re: Why attach databases? (Samuel R. Neff) 7. Re: Why attach databases? (Nicolas Williams) 8. Re: Why attach databases? (Martin Pelletier) 9. Re: How to select Strict Affinity or No Affinity modes? Attention: DRH (Lee Crain) 10. Re: Version 3.2.2 (Mike McGonagle) ---------------------------------------------------------------------- Message: 1 Date: Thu, 7 Feb 2008 14:21:06 -0800 From: "Jason Tudor" <[EMAIL PROTECTED]> Subject: [sqlite] Why attach databases? To: sqlite-users@sqlite.org Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset=ISO-8859-1 I have been playing around with attaching databases. I'm not sure what the point is. Assume that I have two databases with the same schema, say db1 and db2, and I attach them to one connection. In order to get the count from a particular table, I could do the following: SELECT COUNT id FROM main.table UNION SELECT COUNT id FROM db2.table I would then step through the result and add the two values to get a total count. If I have two separate connections, I could run two queries and add the results. Is there a speed difference? In general, what is the benefit of attaching databases verses maintaining multiple connections? TIA TUD ------------------------------ Message: 2 Date: Thu, 7 Feb 2008 15:33:36 -0700 From: "David Baird" <[EMAIL PROTECTED]> Subject: Re: [sqlite] Why attach databases? To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset=ISO-8859-1 On Feb 7, 2008 3:21 PM, Jason Tudor <[EMAIL PROTECTED]> wrote: > In general, what is the benefit of attaching databases verses maintaining > multiple connections? Well, let's say that you don't have the same schema in both databases. Attaching two databases allows you to do queries across multiple tables and would require much more effort if you maintained separate connections. e.g. SELECT People.name, Pets.name FROM People AS People other_database.Pets AS Pets WHERE People.id = Pets.owner_id; It would be less pleasant to do that query via multiple connections. -David ------------------------------ Message: 3 Date: Thu, 7 Feb 2008 17:34:54 -0500 From: "Samuel R. Neff" <[EMAIL PROTECTED]> Subject: Re: [sqlite] Why attach databases? To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org> Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset="us-ascii" It's particularly valuable when you want to run queries across databases. INSERT INTO main.table SELECT * FROM newdata.table; Also if it's possible for you to segment out your data to multiple databases but normally only work with one of them, then you can increase performance and concurrency for those times when you only need to work with one database, but then have the ability to attach databases when you need all the data together. Most if not all database engines have similar functionality, but syntax and functionality differs in each. SQLite syntax is easiest to use of the ones I've used (MSSQL, Access, Sybase ASE, Oracle). HTH, Sam ------------------------------------------- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jason Tudor Sent: Thursday, February 07, 2008 5:21 PM To: sqlite-users@sqlite.org Subject: [sqlite] Why attach databases? I have been playing around with attaching databases. I'm not sure what the point is. Assume that I have two databases with the same schema, say db1 and db2, and I attach them to one connection. In order to get the count from a particular table, I could do the following: SELECT COUNT id FROM main.table UNION SELECT COUNT id FROM db2.table I would then step through the result and add the two values to get a total count. If I have two separate connections, I could run two queries and add the results. Is there a speed difference? In general, what is the benefit of attaching databases verses maintaining multiple connections? TIA TUD _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ------------------------------ Message: 4 Date: Thu, 7 Feb 2008 16:39:29 -0600 From: Nicolas Williams <[EMAIL PROTECTED]> Subject: Re: [sqlite] Why attach databases? To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset=us-ascii Given the restrictions on views and triggers (they cannot make reference to tables from more than one database) I'd say "stay away from ATTACH where possible" -- don't create new uses of it without good reason. ------------------------------ Message: 5 Date: Thu, 7 Feb 2008 15:10:18 -0800 From: "Jason Tudor" <[EMAIL PROTECTED]> Subject: Re: [sqlite] Why attach databases? To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset=ISO-8859-1 Thanks for the quick feedback, you all have good points. The data transfer example that Sam provided is the most compelling to me. I was initially thinking that I could attach every database I had with the same schema and execute queries without specifying database names. It's good to know the limitations and uses. Thanks again TUD On Feb 7, 2008 2:39 PM, Nicolas Williams <[EMAIL PROTECTED]> wrote: > Given the restrictions on views and triggers (they cannot make reference > to tables from more than one database) I'd say "stay away from ATTACH > where possible" -- don't create new uses of it without good reason. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ------------------------------ Message: 6 Date: Thu, 7 Feb 2008 18:17:19 -0500 From: "Samuel R. Neff" <[EMAIL PROTECTED]> Subject: Re: [sqlite] Why attach databases? To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org> Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset="us-ascii" A blanket "stay away" is pretty extreme. I would rather be able to have triggers across attached databases, but without that will use them less, but still there are times when they're very useful and you have no need for triggers. Sam ------------------------------------------- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Nicolas Williams Sent: Thursday, February 07, 2008 5:39 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Why attach databases? Given the restrictions on views and triggers (they cannot make reference to tables from more than one database) I'd say "stay away from ATTACH where possible" -- don't create new uses of it without good reason. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ------------------------------ Message: 7 Date: Thu, 7 Feb 2008 17:22:38 -0600 From: Nicolas Williams <[EMAIL PROTECTED]> Subject: Re: [sqlite] Why attach databases? To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset=us-ascii On Thu, Feb 07, 2008 at 06:17:19PM -0500, Samuel R. Neff wrote: > A blanket "stay away" is pretty extreme. I would rather be able to have True. One use case discussed before would be to archive history data from one DB to another, then reset the history tables in the first one. ------------------------------ Message: 8 Date: Thu, 07 Feb 2008 18:30:12 -0500 From: Martin Pelletier <[EMAIL PROTECTED]> Subject: Re: [sqlite] Why attach databases? To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Jason Tudor wrote: > > In order to get the count from a particular table, I could do the following: > > SELECT COUNT id FROM main.table UNION > SELECT COUNT id FROM db2.table > > I would then step through the result and add the two values to get a total > count. That would not be the optimal use in this case, programmatically (speed-wise is another story). However, you can nest that in a larger query: SELECT sum(cid) FROM (SELECT COUNT(id) as cid FROM main.table UNION SELECT COUNT(id) as cid FROM db2.table) Which would yield one result only. My basic rule is if I need to put the two in relation together (join, union, etc), I attach them. If not, I make more connections. Martin ------------------------------ Message: 9 Date: Thu, 7 Feb 2008 17:50:27 -0700 (MST) From: "Lee Crain" <[EMAIL PROTECTED]> Subject: Re: [sqlite] How to select Strict Affinity or No Affinity modes? Attention: DRH To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org> Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset="us-ascii" DRH, ">> Can you explain why you think strict affinity mode >> might be beneficial to you? If somebody can provide a good >> enough rational to justify strict affinity mode, we might just put it >> in." My response to your request for an example of a benefit is that I have always been an adherent of strict datatyping as a means of trapping inadvertent software development errors. It's just one of the many aspects of my self-checking software that makes certain everything is done on purpose and not by chance. Lee Crain ________________________________________ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Chapman Sent: Wednesday, February 06, 2008 8:23 AM To: D. Richard Hipp Cc: sqlite-users@sqlite.org Subject: Re: [sqlite] How to select Strict Affinity or No Affinity modes? I take it that there's no way to work around this currently? Scott Scott Chapman wrote: > D. Richard Hipp wrote: > >> On Feb 2, 2008, at 7:57 PM, Scott Chapman wrote: >> >> >> >>> I've looked high and low and can't find a way to invoke the other 2 >>> affinity modes. Are they available? I'm on 3.5.4. >>> >>> >> The concept of "strict" affinity mode was briefly discussed years >> ago, but we never implemented it, having never seen any benefit >> for such a thing. Can you explain why you think strict affinity mode >> might be beneficial to you? If somebody can provide a good >> enough rational to justify strict affinity mode, we might just put it >> in. >> >> > I'm working on a Python adapter that goes on top of APSW. It will > enable you to use the column types NUMERIC, DATE, TIME, TIMESTAMP and > automatically convert these to and from Python's respective data types. > > The case I'm dealing with that is not working like I want is the case of > NUMERIC column type. In SQLite, this column type gets an affinity of > REAL. If I put in a value to the column as a string literal, say > '123.23', it's stored as a REAL even though I specified it as a string > in quotes. I want it to store it as a string. The only way I've found > to fix this is to use a column type of NUMERIC_TEXT. The presense of > "TEXT" in the column type changes the affinity to string. This is not > very elegant and I was looking for any other way to make this work > correctly. "No Affinity" would probably work, if I understand it > correctly. > > I want to avoid the use of REAL types in this case because they can lead > to rounding errors, which is the whole purpose of the NUMERIC type to > begin with, in my understanding. I also would like to be able to make > the column type just NUMERIC as that is compilant with the SQL standard. > > Strict Affinity and No Affinity are mentioned in the SQLite3 Datatypes > page. If there are no plans to implement these, please consider > removing them from the docs. > > Thanks! > Scott > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ------------------------------ Message: 10 Date: Thu, 7 Feb 2008 20:51:41 -0600 From: "Mike McGonagle" <[EMAIL PROTECTED]> Subject: Re: [sqlite] Version 3.2.2 To: [EMAIL PROTECTED], "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset=WINDOWS-1252 Trey, Could you site a reference on this? I have been looking for documentation on how SQL deals with quoted things for a while now, but have not found anything. Thanks, Mike On Feb 6, 2008 8:21 PM, Trey Mack <[EMAIL PROTECTED]> wrote: > > UPDATE listings SET buyer = 'Price' WHERE listnum = 12345 > > Double quotes are for column names. > > Walt wrote: > > I have a table 'listings' with fields 'listnum', 'price' and 'buyer' > etc. > > > > executing the following sql > > > > UPDATE listings SET buyer = "Price" WHERE listnum = 12345 > > > > results in the 'buyer' field being set to the contents of the 'price' > field instead of setting the field 'buyer' to "Price". > > > > Need HELP > > > > Walt Mc Whirter > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Peace may sound simple?one beautiful word? but it requires everything we have, every quality, every strength, every dream, every high ideal. ?Yehudi Menuhin (1916?1999), musician ------------------------------ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users End of sqlite-users Digest, Vol 2, Issue 16 ******************************************* _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
