Re: [sqlite] VFS memory leak : During lock / unlock operations
From: Murthy Girigowda (WT01 - TES-Access Networks) Sent: Friday, February 08, 2008 12:25 PM To: 'sqlite-users@sqlite.org' Subject: VFS memory leak : During lock / unlock operations Hi.. we are using sqlite3.3.4 with Integrity OS. we are facing a problem where in the VFS memory is getting exhausted due to large lock/unlock calls made by sqlite. Integrity support team said that, for each file lock call made by sqlite, a definite amount of memory is allocated, this memory is released only after the unlock or when the file is closed. And they are claiming that number of file unlock calls are not same as the number of file lock calls. they have put traces and identified that for 1000 lock calls there are only 950 unlock calls, which is a shortage of 50 unlock calls. This will leak considerable amount of memory if the system is left for long hours, with continuous sqlite operations being made. can any one throw some light on this problem, is there any known issue like this. Any information on this will be very helpful thanks murthy Please do not print this email unless it is absolutely necessary. Spread environmental awareness. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VFS memory leak : During lock / unlock operations
Hi.. we are using sqlite3.3.4 with Integrity OS. we are facing a problem where in the VFS memory is getting exhausted due to large lock/unlock calls made by sqlite. Integrity support team said that, for each file lock call made by sqlite, a definite amount of memory is allocated, this memory is released only after the unlock or when the file is closed. And they are claiming that number of file unlock calls are not same as the number of file lock calls. they have put traces and identified that for 1000 lock calls there are only 950 unlock calls, which is a shortage of 50 unlock calls. This will leak considerable amount of memory if the system is left for long hours, with continuous sqlite operations being made. can any one throw some light on this problem, is there any known issue like this. Any information on this will be very helpful thanks murthy Please do not print this email unless it is absolutely necessary. Spread environmental awareness. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting the result of a select. Is this possible?
"Trey Mack" beautifully wrote...
> Is this what you're after?
>
> create table LSOpenJobs (PSubClass);
> insert into lsopenjobs values ('DOC-Trans');
> insert into lsopenjobs values ('DTP');
> insert into lsopenjobs values ('PM');
> insert into lsopenjobs values ('Post-Proc');
> insert into lsopenjobs values ('Pre-Proc');
>
> select * from lsopenjobs
>order by case psubclass
>when 'Pre-Proc' then 1
>when 'Post-Proc' then 2
>when 'DOC-Trans' then 3
>when 'DTP' then 4
>when 'PM' then 5
>end;
Yes. Thanks.
>
>
> jose isaias cabrera wrote:
>> Greetings.
>>
>> I would like to have the results of a select be returned sorted in an
>> specific way. Let me show you what I mean:
>>
>> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190 GROUP BY
>> PSubClass;
>> DOC-Trans
>> DTP
>> PM
>> Post-Proc
>> Pre-Proc
>> sqlite>
>>
>> What I would like is to have the SELECT result be,
>>
>> Pre-Proc
>> Post-Proc
>> DOC-Trans
>> DTP
>> PM
>>
>> is this possible? Yes, I know I can sort it in the program, but how can
>> I
>> get this special sort from the DB?
>>
>> thanks,
>>
>> josé
>>
>>
>>
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this list available in *DIGEST* form??
On Feb 8, 2008, at 11:31 AM, Rob Sciuk wrote: > > How do I sign up for the digest rather than the regular feed?? > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Use the webpage linked above. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting the result of a select. Is this possible?
Is this what you're after?
create table LSOpenJobs (PSubClass);
insert into lsopenjobs values ('DOC-Trans');
insert into lsopenjobs values ('DTP');
insert into lsopenjobs values ('PM');
insert into lsopenjobs values ('Post-Proc');
insert into lsopenjobs values ('Pre-Proc');
select * from lsopenjobs
order by case psubclass
when 'Pre-Proc' then 1
when 'Post-Proc' then 2
when 'DOC-Trans' then 3
when 'DTP' then 4
when 'PM' then 5
end;
jose isaias cabrera wrote:
> Greetings.
>
> I would like to have the results of a select be returned sorted in an
> specific way. Let me show you what I mean:
>
> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190 GROUP BY
> PSubClass;
> DOC-Trans
> DTP
> PM
> Post-Proc
> Pre-Proc
> sqlite>
>
> What I would like is to have the SELECT result be,
>
> Pre-Proc
> Post-Proc
> DOC-Trans
> DTP
> PM
>
> is this possible? Yes, I know I can sort it in the program, but how can I
> get this special sort from the DB?
>
> thanks,
>
> josé
>
>
>
> ___
> 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
Re: [sqlite] Sorting the result of a select. Is this possible?
"P Kishor" asked... >I don't understand your question at all. I will answer it at the end. > On 2/7/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: >> >> Greetings. >> >> I would like to have the results of a select be returned sorted in an >> specific way. Let me show you what I mean: >> >> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190 GROUP BY > > But, first... why are you using GROUP BY above when you have no > aggregate function (Count, Max, Min, Sum, etc.) in your query? Because of this: sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190; Pre-Proc Post-Proc DOC-Trans DTP Pre-Proc Pre-Proc Pre-Proc Pre-Proc Pre-Proc Post-Proc Post-Proc Post-Proc Post-Proc Post-Proc DOC-Trans DOC-Trans DOC-Trans DOC-Trans DOC-Trans DTP DTP DTP DTP DTP PM PM PM PM PM PM sqlite> > >> PSubClass; >> DOC-Trans >> DTP >> PM >> Post-Proc >> Pre-Proc >> sqlite> >> >> What I would like is to have the SELECT result be, >> >> Pre-Proc >> Post-Proc >> DOC-Trans >> DTP >> PM >> >> is this possible? Yes, I know I can sort it in the program, but how can >> I >> get this special sort from the DB? >> > > What is the logic above? Without you telling us why Pre-Doc is before > Post-Proc is before DOC-Trans, and so on, how can we guess what your > logic is for the sort? In other words, please explain what makes the > above sort "special"? Aaah, ok. I am preparing an html file and in it I will list these PSubClasses in this special logical display sequence: 1. Pre-Proc 2. Post-Proc 3. DOC-Trans 4. DTP 5. PM I hope this explains it... thanks, josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this list available in *DIGEST* form??
Goto http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users "Would you like to receive list mail batched in a daily digest? No Yes" On Feb 8, 2008 5:31 AM, Rob Sciuk <[EMAIL PROTECTED]> wrote: > > How do I sign up for the digest rather than the regular feed?? > > Cheers, > Rob. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting the result of a select. Is this possible?
"jose isaias cabrera" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> I would like to have the results of a select be returned sorted in an
> specific way. Let me show you what I mean:
>
> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190 GROUP
> BY PSubClass;
> DOC-Trans
> DTP
> PM
> Post-Proc
> Pre-Proc
> sqlite>
>
> What I would like is to have the SELECT result be,
>
> Pre-Proc
> Post-Proc
> DOC-Trans
> DTP
> PM
>
> is this possible? Yes, I know I can sort it in the program, but how
> can I get this special sort from the DB?
Create a table specifying a desired sort order, like this:
create table SortOrder(PSubClass primary key, rank integer);
insert into SortOrder values('Pre-Proc', 1);
insert into SortOrder values(Post-Proc', 2);
...
SELECT distinct j.PSubClass
FROM LSOpenJobs j join SortOrder o on (j.PSubClass = o.PSubClass)
WHERE subProjID = 2190
order by rank;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select Strict Affinity or No Affinity modes?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Lee Crain wrote: > 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. There are also a whole school of people who believe that dynamic typing as used in SQLite is far more productive and results in less code. For example the Python programming language is typed in a similar way. http://en.wikipedia.org/wiki/Duck_typing There are many sites with many discussion threads containing opinions, anecdotes and evidence and code supporting all the positions on this issue. Feel free to discuss the merits there. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHq96dmOOfHg372QQRAnA/AJ9cHWhoprB0l1HAczzaQPiwNMgLZwCgr6tC aop4JoSVzKZp5UPwvr88WFs= =I8av -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting the result of a select. Is this possible?
I don't understand your question at all. On 2/7/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: > > Greetings. > > I would like to have the results of a select be returned sorted in an > specific way. Let me show you what I mean: > > sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190 GROUP BY But, first... why are you using GROUP BY above when you have no aggregate function (Count, Max, Min, Sum, etc.) in your query? > PSubClass; > DOC-Trans > DTP > PM > Post-Proc > Pre-Proc > sqlite> > > What I would like is to have the SELECT result be, > > Pre-Proc > Post-Proc > DOC-Trans > DTP > PM > > is this possible? Yes, I know I can sort it in the program, but how can I > get this special sort from the DB? > What is the logic above? Without you telling us why Pre-Doc is before Post-Proc is before DOC-Trans, and so on, how can we guess what your logic is for the sort? In other words, please explain what makes the above sort "special"? > josé > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sorting the result of a select. Is this possible?
Greetings. I would like to have the results of a select be returned sorted in an specific way. Let me show you what I mean: sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190 GROUP BY PSubClass; DOC-Trans DTP PM Post-Proc Pre-Proc sqlite> What I would like is to have the SELECT result be, Pre-Proc Post-Proc DOC-Trans DTP PM is this possible? Yes, I know I can sort it in the program, but how can I get this special sort from the DB? thanks, josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this list available in *DIGEST* form??
How do I sign up for the digest rather than the regular feed?? Cheers, Rob. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why attach databases?
On Feb 8, 2008, at 6:10 AM, Jason Tudor wrote: > Thanks for the quick feedback, you all have good points. The data > transfer > example that Sam provided is the most compelling to me. Another reason is that a transaction that writes to more than one attached database is still an atomic operation (either all the changes will succeed or none of them). You can't do that with two separate database handles. Dan. > 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 >> > ___ > 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
Re: [sqlite] sqlite-users Digest, Vol 2, Issue 16
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" 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'" 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 t
Re: [sqlite] Version 3.2.2
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
Re: [sqlite] How to select Strict Affinity or No Affinity modes? Attention: DRH
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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why attach databases?
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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why attach databases?
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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why attach databases?
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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why attach databases?
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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
Re: [sqlite] Why attach databases?
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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why attach databases?
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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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
Re: [sqlite] Version 3.2.2
Trey, Thank you for the prompt reply. Did not realize that double quote would denote column name. Will have to check for column names when building sql statement and insert single quotes around that part of data. Thanks again Walt Mc Whirter - Original Message - From: "Trey Mack" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Wednesday, February 06, 2008 7:21 PM Subject: Re: [sqlite] Version 3.2.2 > > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Collation Sequence Error
Am attempting to create a few reports from SQLite data that is collected in an application that uses SQLite as its data store. I'm using Visual Studio 2005 in this effort, creating a Windows Forms application. When I attempt to create an ORDER BY in my DataSet, I get an error as such: SQLite Error No such collation sequence:IUNICODE Any assistance would be appreciated. Bruce W. Wells ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Hello
___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UNSUBSCRIBE
___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mac binary Cannot Download
The same here: # wget http://sqlite.org/sqlite3-3.5.6-osx-x86.bin.gz --12:29:52-- http://sqlite.org/sqlite3-3.5.6-osx-x86.bin.gz => `sqlite3-3.5.6-osx-x86.bin.gz' Resolving sqlite.org... 67.18.92.124 Connecting to sqlite.org[67.18.92.124]:80... connected. HTTP request sent, awaiting response... End of file while parsing headers. Retrying. --12:30:10-- http://sqlite.org/sqlite3-3.5.6-osx-x86.bin.gz (try: 2) => `sqlite3-3.5.6-osx-x86.bin.gz' Connecting to sqlite.org[67.18.92.124]:80... connected. HTTP request sent, awaiting response... End of file while parsing headers. Retrying. PS. I am using sources for SQLite under Mac OS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Mac binary Cannot Download
Hi All, I cannot download the: http://sqlite.org/sqlite3-3.5.6-osx-x86.bin.gz No problems with other downloads Need help. Thanks Coen J Error: Safari can’t open the page “http://sqlite.org/sqlite3-3.5.6-osx- x86.bin.gz” because the server unexpectedly dropped the connection, which sometimes occurs when the server is busy. You might be able to open the page later. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
