Re: [sqlite] Multiple connection to in-memory database
tmpfs looks like the best approach for us since we then can use
concurrent transactions using multiple connections from different
threads and with good performances.
I didn't know /tmp was mounted as a tmpfs.
Thanks all for the replies.
Vincent
Brandon, Nicholas (UK) wrote:
>> I would like to use transactions from separate threads, each
>> thread having one connection to a single in-memory db.
>>
>>
>
> If your production environment is a modern linux distribution you may
> find the temporary directory ("/tmp") is already a memory drive using
> the tmpfs filesystem. If not it is very easy to create one. Search the
> internet for more information.
>
> You can then access the database by multiple processes/threads by
> referring to the file path.
>
> Nick
>
>
> This email and any attachments are confidential to the intended
> recipient and may also be privileged. If you are not the intended
> recipient please delete it from your system and notify the sender.
> You should not copy it or use it for any purpose nor disclose or
> distribute its contents to any other person.
>
>
> ___
> 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] Reducing SQLite Memory footprint(!)
Ok, I couldnt find the script but heres something started. On Fri, Aug 22, 2008 at 6:02 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote: > That script would be great :) > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Jeffrey Becker > Sent: Friday, August 22, 2008 1:31 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) > > At one point I wrote some vbscript to generate a table declaration and > insert statements for a csv. I might be able to dig it up if you dont > mind vbscript. > > On Fri, Aug 22, 2008 at 1:58 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote: >> I just ran Dennis's test databases through the test application and >> we're getting similar results: >>1k Pages (17.4 MB) used 18102 KB High 20416 KB >>4k Pages (12.2 MB) used 18102 KB, High 26416 KB (not sure why >> the high is higher?) >> My test database however with the same test application produces the >> following: >>1k Pages (7.46 MB) used 22735 KB, High 25138 KB. >> >> So it looks my issue could be data related if my test database going >> through the same app is coming out so large, Dennis's database is >> expanding to about 101.6% of its original size but mine is expanding > to >> 297.6% of its original size. This begs the question is the 3rd party >> tool (SQLite Analyzer) I'm using to import from an excel file causing >> this expansion with bad data type choices? And is there any other way >> to import table structure and contents from xls (or csv) to sqlite? >> >> -Original Message- >> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote >> Sent: Friday, August 22, 2008 7:10 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) >> >> Brown, Daniel wrote: >>> 2. And the other thing to try would be if anyone has a fairly meaty >> test >>> database they don't mind sharing that I could fling at my test >>> application to try and rule out the data? >>> >> >> Daniel, >> >> I can send you copies of the databases I am using for my testing, both >> the version with the 1K page size (17.4 MB) and the one with the 4K > page >> >> size (12.2 MB). >> >> Where would you like me to send them? The zipped versions are each > about >> >> 1.3 MB in size. >> >> Dennis Cote >> ___ >> 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-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Class FieldInfo Public Name private m_TextCount private m_BlobCount private m_IntCount private m_FloatCount private m_DateCount Public Property Get DataType dim t, c t = "NULL" c = 0 if(m_DateCount > c) then t = "Date" c = m_DateCount end if if(m_BlobCount > c) then t = "Binary" c = m_BlobCount end if if(m_FloatCount > c) then t = "Real" c = m_FloatCount end if if(m_IntCount > c) then t = "Integer" c = m_IntCount end if if(m_TextCount > c) then t = "Text" c = m_TextCount end if if count = 0 then t = "Text" end if DataType = t End Property Private Sub Class_Initialize m_TextCount = 0 m_BlobCount = 0 m_IntCount = 0 m_FloatCount = 0 m_DateCount = 0 End Sub Private Sub Class_Terminate End Sub Public Sub ScanForType(data) if IsDate(data) then m_DateCount = m_DateCount + 1 elseif(IsNumeric(data)) then if(instr(data,".") <> -1) then m_FloatCount = m_FloatCount + 1 else m_IntCount = m_IntCount+1 end if
Re: [sqlite] millisecond precision for unixepoch values
Igor Tandetnik wrote:
> You have two problems. First, 1219441430151/1000 is done as C-style
> integer truncating division, so you are losing your fractions right
> there. Try
>
> select 1219441430151/1000, 1219441430151/1000.0;
>
> and see the difference.
>
Ah, indeed:
sqlite> select 1219441430151/1000, 1219441430151/1000.0;
1219441430|1219441430.151
> Second, strftime('%s') is documented as printing the number of seconds.
> Even if its parameter is a fraction, its output is always an integer
> (or, to be precise, a string of digits convertible to an integer).
>
>
> You can convert "seconds since Unix epoch" to Julian day like this:
>
> J = U / 86400.0 + 2440587.5
>
> 86400 is the number of seconds in a day. 2440587.5 is the result of
> julianday('1970-01-01') - the difference between Unix epoch and Julian
> epoch.
>
Brilliant, thanks, that works great.
The presumed inverse |U = (J - 2440587.5) * 86400.0| also mostly works,
except that it doesn't return the original integer, and JavaScript's
Date constructor apparently converts fractional inputs to integers by
flooring (truncating) them, resulting in an off-by-one error for
1219441430151:
select (2454701.40544156 - 2440587.5) * 86400.0;
1219441430.15077
1219441430.15077 * 1000 -> 1219441430150.77 (in both SQLite and JS)
new Date(1219441430150.77).getTime() -> 1219441430150
I suppose I can fix that in most cases by rounding the value in either
SQLite or in JS before passing it to the Date constructor.
-myk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] millisecond precision for unixepoch values
Igor Tandetnik wrote: > Scott Baker <[EMAIL PROTECTED]> wrote: >> Did I do something wrong? >> >> SQLite version 3.5.9 >> Enter ".help" for instructions >> sqlite> select 1219441430151/1000, 1219441430151/1000.0; >> 1219441430| > > Works for me. Did you perhaps compile without floating point support, or > something like that? I'm running Windows pre-built binaries. I'm using the fedora packages: [EMAIL PROTECTED](~) :rpm -q sqlite sqlite-3.5.9-1.fc9.i386 It works for small numbers: sqlite> select 1.0 / 2.0; 0.5 -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] millisecond precision for unixepoch values
Scott Baker <[EMAIL PROTECTED]> wrote: > Did I do something wrong? > > SQLite version 3.5.9 > Enter ".help" for instructions > sqlite> select 1219441430151/1000, 1219441430151/1000.0; > 1219441430| Works for me. Did you perhaps compile without floating point support, or something like that? I'm running Windows pre-built binaries. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] millisecond precision for unixepoch values
Igor Tandetnik wrote:
> Myk Melez <[EMAIL PROTECTED]> wrote:
>> I noticed today that JavaScript Date values (the number of
>> milliseconds since the Unix epoch) degrade to second precision when
>> converted to Julian date values and back using naive SQLite date/time
>> function calls, for example:
>>
>> sqlite> SELECT strftime('%s', julianday(1219441430151/1000,
>> 'unixepoch')) * 1000;
>> 121944143
>>
>> I suppose this is because "Unix has no tradition of directly
>> representing non-integer Unix time numbers as binary fractions."
>
> No. You have two problems. First, 1219441430151/1000 is done as C-style
> integer truncating division, so you are losing your fractions right
> there. Try
>
> select 1219441430151/1000, 1219441430151/1000.0;
Did I do something wrong?
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> select 1219441430151/1000, 1219441430151/1000.0;
1219441430|
I don't get a result for the second select...
--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] millisecond precision for unixepoch values
Myk Melez <[EMAIL PROTECTED]> wrote:
> I noticed today that JavaScript Date values (the number of
> milliseconds since the Unix epoch) degrade to second precision when
> converted to Julian date values and back using naive SQLite date/time
> function calls, for example:
>
> sqlite> SELECT strftime('%s', julianday(1219441430151/1000,
> 'unixepoch')) * 1000;
> 121944143
>
> I suppose this is because "Unix has no tradition of directly
> representing non-integer Unix time numbers as binary fractions."
No. You have two problems. First, 1219441430151/1000 is done as C-style
integer truncating division, so you are losing your fractions right
there. Try
select 1219441430151/1000, 1219441430151/1000.0;
and see the difference.
Second, strftime('%s') is documented as printing the number of seconds.
Even if its parameter is a fraction, its output is always an integer
(or, to be precise, a string of digits convertible to an integer).
You can convert "seconds since Unix epoch" to Julian day like this:
J = U / 86400.0 + 2440587.5
86400 is the number of seconds in a day. 2440587.5 is the result of
julianday('1970-01-01') - the difference between Unix epoch and Julian
epoch.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] recommended declared type for Julian date columns
Is "REAL" the recommended declared type for a column storing only Julian date values that generally include a fractional time portion? -myk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] millisecond precision for unixepoch values
I noticed today that JavaScript Date values (the number of milliseconds
since the Unix epoch) degrade to second precision when converted to
Julian date values and back using naive SQLite date/time function calls,
for example:
sqlite> SELECT strftime('%s', julianday(1219441430151/1000,
'unixepoch')) * 1000;
121944143
I suppose this is because "Unix has no tradition of directly
representing non-integer Unix time numbers as binary fractions."
Is there another way to preserve this information besides taking the
cumbersome additional step of constructing an intermediate ISO 8601 time
string from the JavaScript Date value in JavaScript and then converting
that to the Julian date (and back the same way)?
-myk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
That script would be great :) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeffrey Becker Sent: Friday, August 22, 2008 1:31 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) At one point I wrote some vbscript to generate a table declaration and insert statements for a csv. I might be able to dig it up if you dont mind vbscript. On Fri, Aug 22, 2008 at 1:58 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote: > I just ran Dennis's test databases through the test application and > we're getting similar results: >1k Pages (17.4 MB) used 18102 KB High 20416 KB >4k Pages (12.2 MB) used 18102 KB, High 26416 KB (not sure why > the high is higher?) > My test database however with the same test application produces the > following: >1k Pages (7.46 MB) used 22735 KB, High 25138 KB. > > So it looks my issue could be data related if my test database going > through the same app is coming out so large, Dennis's database is > expanding to about 101.6% of its original size but mine is expanding to > 297.6% of its original size. This begs the question is the 3rd party > tool (SQLite Analyzer) I'm using to import from an excel file causing > this expansion with bad data type choices? And is there any other way > to import table structure and contents from xls (or csv) to sqlite? > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote > Sent: Friday, August 22, 2008 7:10 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) > > Brown, Daniel wrote: >> 2. And the other thing to try would be if anyone has a fairly meaty > test >> database they don't mind sharing that I could fling at my test >> application to try and rule out the data? >> > > Daniel, > > I can send you copies of the databases I am using for my testing, both > the version with the 1K page size (17.4 MB) and the one with the 4K page > > size (12.2 MB). > > Where would you like me to send them? The zipped versions are each about > > 1.3 MB in size. > > Dennis Cote > ___ > 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance Care
Bruno, I'm quite new to sqlite but I believe that concurrency may be an issue only for data changes which require db locking. As long as you go with SELECTs, there's not much to worry about. Tomas On Fri, Aug 22, 2008 at 7:47 PM, Bruno Moreira Guedes <[EMAIL PROTECTED]>wrote: > Hello, > > I have an application which uses SQLite. The program open a sqlite > database, and have about five instances running at the same time. For > each instance, it waits for user commands(where the 'user' is another > application), and for each command it executes some queries(about 10). > Currently, the user sends a little number of commands(I don't know the > numbers exactaly, but about 3 or less) per second, and it runs very > whell. But, it'll be used on a environment where the number of > commands per time will increase at least 10x!!! The program's answer > to command need to be very fast. > > So, do you think I need to care about performance?? Do you think > SQLite should be the best choice(or even a 'good choice')?? > I also need some idea about the best way to manage the high number of > access to the application. What's better to do to improve > performance?? Reducing the number of queries and increasing the number > of program instances should help(e.g. creating some views to reduce > the number of selects, reducing the number of sqlite3_exec calls)?? > > I haven't detailed yet, but sometimes(rarely) data writes should be > done(update/delete/insert). But it's too short data, and it's less > then 0.01% of the database access(it stores some access rules). > > When I researched about performance in SQLite, I found some > information about the concurrency, saying that SQLite is not the best > choice when I have concurrency. But I want to know if the concurrency > being only about selects change this idea(as I think the database > don't get locked when I do just SELECTs). > > Thank you all, > Bruno M Guedes > ___ > 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] Reducing SQLite Memory footprint(!)
At one point I wrote some vbscript to generate a table declaration and insert statements for a csv. I might be able to dig it up if you dont mind vbscript. On Fri, Aug 22, 2008 at 1:58 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote: > I just ran Dennis's test databases through the test application and > we're getting similar results: >1k Pages (17.4 MB) used 18102 KB High 20416 KB >4k Pages (12.2 MB) used 18102 KB, High 26416 KB (not sure why > the high is higher?) > My test database however with the same test application produces the > following: >1k Pages (7.46 MB) used 22735 KB, High 25138 KB. > > So it looks my issue could be data related if my test database going > through the same app is coming out so large, Dennis's database is > expanding to about 101.6% of its original size but mine is expanding to > 297.6% of its original size. This begs the question is the 3rd party > tool (SQLite Analyzer) I'm using to import from an excel file causing > this expansion with bad data type choices? And is there any other way > to import table structure and contents from xls (or csv) to sqlite? > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote > Sent: Friday, August 22, 2008 7:10 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) > > Brown, Daniel wrote: >> 2. And the other thing to try would be if anyone has a fairly meaty > test >> database they don't mind sharing that I could fling at my test >> application to try and rule out the data? >> > > Daniel, > > I can send you copies of the databases I am using for my testing, both > the version with the 1K page size (17.4 MB) and the one with the 4K page > > size (12.2 MB). > > Where would you like me to send them? The zipped versions are each about > > 1.3 MB in size. > > Dennis Cote > ___ > 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] Reducing SQLite Memory footprint(!)
You can try using the command line tool to import csv data: sqlite> .mode csv sqlite> .import yourdata.csv yourtablename however, if you have quoted strings with commas embedded it won't work. You can try using any delimiter with .separator command. RW sqlite>select level from sqlGuruOMeter where name="Ron Wilson"; 2 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel Sent: Friday, August 22, 2008 1:59 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) I just ran Dennis's test databases through the test application and we're getting similar results: 1k Pages (17.4 MB) used 18102 KB High 20416 KB 4k Pages (12.2 MB) used 18102 KB, High 26416 KB (not sure why the high is higher?) My test database however with the same test application produces the following: 1k Pages (7.46 MB) used 22735 KB, High 25138 KB. So it looks my issue could be data related if my test database going through the same app is coming out so large, Dennis's database is expanding to about 101.6% of its original size but mine is expanding to 297.6% of its original size. This begs the question is the 3rd party tool (SQLite Analyzer) I'm using to import from an excel file causing this expansion with bad data type choices? And is there any other way to import table structure and contents from xls (or csv) to sqlite? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Friday, August 22, 2008 7:10 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) Brown, Daniel wrote: > 2. And the other thing to try would be if anyone has a fairly meaty test > database they don't mind sharing that I could fling at my test > application to try and rule out the data? > Daniel, I can send you copies of the databases I am using for my testing, both the version with the 1K page size (17.4 MB) and the one with the 4K page size (12.2 MB). Where would you like me to send them? The zipped versions are each about 1.3 MB in size. Dennis Cote ___ 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] is there a way to escape the '-' character in an FTS3 search string?
Is there a way to escape the negatory syntax (the minus sign / dash) in FTS3 MATCH syntax? I found that if I enclose the search term in quotes (ie. "T-Bone"), FTS3 does not treat the minus sign as a exclusion from the search. I was just wondering if there is another way that does not require me to parse the search string into terms and quote the ones that have dashes in them. Jason Boehle [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
Brown, Daniel wrote: > I just ran Dennis's test databases through the test application and > we're getting similar results: > 1k Pages (17.4 MB) used 18102 KB High 20416 KB > 4k Pages (12.2 MB) used 18102 KB, High 26416 KB (not sure why > the high is higher?) > My test database however with the same test application produces the > following: > 1k Pages (7.46 MB) used 22735 KB, High 25138 KB. > Its good to see you are getting the same results as me using my databases. That rules out your build of sqlite and the build tolls. It does look like your issue has to do with your data. > So it looks my issue could be data related if my test database going > through the same app is coming out so large, Dennis's database is > expanding to about 101.6% of its original size but mine is expanding to > 297.6% of its original size. This begs the question is the 3rd party > tool (SQLite Analyzer) I'm using to import from an excel file causing > this expansion with bad data type choices? And is there any other way > to import table structure and contents from xls (or csv) to sqlite? > You could write one yourself in Python using the csv reader and the pysqlite modules to read CSV files saved from excel and save the data into an sqlite database. I doubt that will change your data much though. Can you publish the schema of your database, and some typical data? It may be a case of storing integers as text or something similar that is causing the unexpectedly large expansion. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite and åäö characters in file n ames
Hi, and never mind why the old work and the new one doesn't. Found myself into starting to compare sqlite code, compile and debug and I really don't have time for THAT. Curiosity can be a pain. Thanks all. /Ann ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
I just ran Dennis's test databases through the test application and we're getting similar results: 1k Pages (17.4 MB) used 18102 KB High 20416 KB 4k Pages (12.2 MB) used 18102 KB, High 26416 KB (not sure why the high is higher?) My test database however with the same test application produces the following: 1k Pages (7.46 MB) used 22735 KB, High 25138 KB. So it looks my issue could be data related if my test database going through the same app is coming out so large, Dennis's database is expanding to about 101.6% of its original size but mine is expanding to 297.6% of its original size. This begs the question is the 3rd party tool (SQLite Analyzer) I'm using to import from an excel file causing this expansion with bad data type choices? And is there any other way to import table structure and contents from xls (or csv) to sqlite? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Friday, August 22, 2008 7:10 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) Brown, Daniel wrote: > 2. And the other thing to try would be if anyone has a fairly meaty test > database they don't mind sharing that I could fling at my test > application to try and rule out the data? > Daniel, I can send you copies of the databases I am using for my testing, both the version with the 1K page size (17.4 MB) and the one with the 4K page size (12.2 MB). Where would you like me to send them? The zipped versions are each about 1.3 MB in size. Dennis Cote ___ 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] Performance Care
Hello, I have an application which uses SQLite. The program open a sqlite database, and have about five instances running at the same time. For each instance, it waits for user commands(where the 'user' is another application), and for each command it executes some queries(about 10). Currently, the user sends a little number of commands(I don't know the numbers exactaly, but about 3 or less) per second, and it runs very whell. But, it'll be used on a environment where the number of commands per time will increase at least 10x!!! The program's answer to command need to be very fast. So, do you think I need to care about performance?? Do you think SQLite should be the best choice(or even a 'good choice')?? I also need some idea about the best way to manage the high number of access to the application. What's better to do to improve performance?? Reducing the number of queries and increasing the number of program instances should help(e.g. creating some views to reduce the number of selects, reducing the number of sqlite3_exec calls)?? I haven't detailed yet, but sometimes(rarely) data writes should be done(update/delete/insert). But it's too short data, and it's less then 0.01% of the database access(it stores some access rules). When I researched about performance in SQLite, I found some information about the concurrency, saying that SQLite is not the best choice when I have concurrency. But I want to know if the concurrency being only about selects change this idea(as I think the database don't get locked when I do just SELECTs). Thank you all, Bruno M Guedes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Details of error messages. Was: "unable to open database file" on DROP
D. Richard Hipp wrote: > > Consider what happens if an interaction with the library contains two > or more errors. Only a single error message and error code can be > returned. Consequently, if the interaction contains error A we cannot > guarantee that the code and message returned will refer to A, since it > might instead refer to error B. We can guarantee that some kind of > error will be returned. We just cannot guarantee what the error code > and message content will be since that depends on what other errors > might be present. > While that may be a concern in the most general sense, I don't think it is a valid reason not to document the error that is returned when a specific rule is broken. Take this rule for example: H42334: The preparation of a CREATE TABLE statement shall fail with an error if the the databasename references a database that is not attached to the same database connection. This is a very specific rule. There should be a well documented error that will be returned if this rule is broken. With these definitions in place, a user could search through the documentation and find all the possible causes of that particular error (heck, you might even want to create an cross reference list to make this step easier). This would often help them to locate the cause of the problem or suggest possible causes that they might not otherwise consider. The fact that multiple rules might be broken by a statement isn't really an issue since you do not (and should not) specify the order that the rules are checked. If a statement has multiple problems they may need to be fixed one at a time until they are all resolved. Giving users the best possible guidance at each step is important and valuable. Furthermore, there are other, non-rule based issues, such as "out of memory" or "I/O error" that may cause an API function to fail. The mere existence of such possibilities should not be grounds to avoid documenting the errors that are returned when any of the many rule based errors conditions occur. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .import with .separator and quoted strings
Hmm. Ok I'll think about munging the data. If I find some time perhaps I'll submit some code to support quoted values in the sqlite3 command line tool. RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald Sent: Thursday, August 21, 2008 5:38 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] .import with .separator and quoted strings Hi Ron, I've encountered that as well. You're using the sqlite3 commandline interface program, I'm sure. I think it was intended as a test and demo utility, but it's found its way into a number of released products. I'm not aware of a way to make this work with the current utility. Since the source is available, you may want to modify it as needed. I know you prefer not to pre-process your input file, so enhancing the source may be your best option. In my case, *all* the fields were quoted in the input file, and so I replaced occurances of: ","(quote comma quote) with a vertical bar | and trimmed the quotes from the beginning and end of each line. You can even perform this using an sqlite3 script itself if you don't mind a bit of madness. -Set the separator to something very odd such as '@$%' -Import the original text to a temporary table with a single field to contain the entire row. -UPDATE each row, using REPLACE() to change "," to | (perhaps after first checking for any actual virgules in the original data). -Use SUBSTR() to remove the two remaining quotes at each end of the line. -Set the separator to | and export to a temp file. Delete the temporary table and .import the data into your real table. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P Sent: Thursday, August 21, 2008 4:37 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] .import with .separator and quoted strings Here is an easy way to reproduce the symptom. Given the following file as input for the .import command: ---csvtest.csv--- "1","wilson, ron" "2","momma, your" - Here is the sqlite output: SQLite version 3.5.9 Enter ".help" for instructions sqlite> create table names (id integer, name); .mode csv .import sqlite> csvtest.csv names csvtest.csv line 1: expected 2 columns of data but found 3 sqlite> .quit Clearly it is parsing the comma in the name column as a record delimiter. Is there a mode that causes the .import command to honor quoted entries? RW sqlite>select level from sqlGuruOMeter where name="Ron Wilson"; 2 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P Sent: Tuesday, August 19, 2008 4:49 PM To: sqlite-users@sqlite.org Subject: [sqlite] .import with .separator and quoted strings I'm trying to import a table using the command line tool. sqlite> .separator , sqlite> .import export.csv library export.csv line 1: expected 53 columns of data but found 77 sqlite> .mode csv sqlite> .import export.csv library export.csv line 1: expected 53 columns of data but found 77 All entries are quoted strings, but some of them have commas within the strings. It appears that SQLite is ignoring the string quoting and taking all commas literally. Is this intended? The same import works fine in Excel with 53 columns resulting. I have also tried tab delimited and apparently some of the strings in this dataset also contain tabs. sqlite> .mode tabs sqlite> .import export.txt library export.txt line 162: expected 53 columns of data but found 55 I don't control the data source, and I would really like to avoid pre-munging the data. RW sqlite>select level from sqlGuruOMeter where name="Ron Wilson"; 2 ___ 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 This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ 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] Any equivalent to MSSQL's UPDATE..FROM.. clause ?
Thanks, I didn't think REPLACE would work here but you're right, it does do exactly what I need. Best regards, Sam - We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in the Washington D.C. Contact [EMAIL PROTECTED] On Fri, Aug 22, 2008 at 3:13 AM, Francis GAYREL <[EMAIL PROTECTED]>wrote: > I met the same issue. REPLACE is the right way to override it. > Assuming C1 is the primary key or at least unique, > instead of > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close
Joanne Pham wrote:
> Hi Igor,
> I used SQLite versio n 3.5.9.
> I read the SQLite online document and the suggession that we need to finalize
> all the prepare statement associated with database connection before closing
> the connection as below
>
> while( (pStmt = sqlite3_next_stmt(pDb, 0))!=0 ){
> sqlite3_finalize(pStmt);
> }
> sqlSt= sqlite3_close(pDb);
> but the codes didn't return the syntax for sqlite3_next_stmt. Is
> sqlite3_next_stmt is valid command in SQLite 3.5.9
Yes, it is, see http://www.sqlite.org/c3ref/next_stmt.html for details.
It was introduced in 3.6.0 (see http://www.sqlite.org/changes.html), so
it was not present in 3.5.9.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
Brown, Daniel wrote: > Ok so after reading your feedback I tried: > 1. "PRAGMA cache_size =10" no change in memory usage. > 2. "PRAGMA page_size = 4096" no change in memory usage. > > I'm doing both those queries (in C++) after the 'sqlite3_open( > ":memory:", _pDataBase );' in my test but before the database file is > attached or anything is copied or created. The rebuilt database file is > 7.46 MB and the memory usage I'm seeing is now: 22.20 MB with a high > water of 24.55 MB as reported by the sqlite_memory_* functions. > I believe each database has its own cache. You need to set the cache size for the file database after it is attached. attach 'file.db' as file_db; pragma file_db.cache.size = 100; In my tests I am setting the cache size for both the memory database (right after the open call) and the file database. > I'm not using the amalgamation version of the pre-processed source; I'm > using the individual source files of 3.6.1 on Windows XP with Visual > Studio 2005. I'm afraid I can't give you a copy of the test database as > it's a drop from a live product, could it be using the third party > SQLite Analyzer application to import from excel be the issue? I doubt it, but anything is possible. > Are > there any other tools from importing from a .xls to a SQLite database > (converting each sheet to a table)? > I don't know. > I just tried a "vacuum" after I detach the database from file and that > didn't reduce the memory usage either but it did double the high water > mark which after reading the documentation sounds about right for making > a temporary copy. > > How do I rebuild a database file for another page size or did the pragma > do that already? > I used you test program to do that for my database. :-) I simply changed the filename of the output database from :memory: to my new filename in the open call, and then executed a "pragma page_size=4096" immediately after the open. The rest of your code copied all the tables in the test database out to the new database file with the new page size. This database doesn't use any named (i.e, non-automatic) indexes or triggers, so there was nothing else to be copied. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite and åäö characters in file n ames
On Aug 22, 2008, at 10:03 AM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> wrote: > What I wonder is why the old sqlite3.dll from 2004-10 works and the > newer ones don't. Older versions of SQLite contained bugs. They failed to convert UTF to the native code page before passing strings into windows APIs. Thus the older SQLite bugs and the bugs in your code cancelled each other out. When the bug in SQLite was fixed, the cancellation went away and the bug was expressed. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
Brown, Daniel wrote: > 2. And the other thing to try would be if anyone has a fairly meaty test > database they don't mind sharing that I could fling at my test > application to try and rule out the data? > Daniel, I can send you copies of the databases I am using for my testing, both the version with the 1K page size (17.4 MB) and the one with the 4K page size (12.2 MB). Where would you like me to send them? The zipped versions are each about 1.3 MB in size. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite and åäö characters in file n ames
Hi again, I haven't found yet where I can reply directly to posts, I guess an email should pop in when I find the correct button. Anyway ... that it was about the encoding I could have guessed and been more clear about. What I wonder is why the old sqlite3.dll from 2004-10 works and the newer ones don't. Sincerely /Ann ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Detecting other connections to DB?
Open the Sqlite DB file for exclusive access. If it fails another user has it open. Alexey Pechnikov wrote: > Hello! > > В сообщении от Thursday 21 August 2008 22:45:33 Doug Porter написал(а): > >>Our software uses SQLite to save our data and we want to warn a user >>who opens a file that is already opened by another user. I tried a >>homebrewed approach (keep a table of open connections manually), but >>ran into a couple places where that won't work. > > > You can create array for all connections and populate it by your application. > > Best regards, Alexey. > ___ > 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 and aao characters in file names
Igor Tandetnik wrote: "Mihai Limbasan" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] The sqlite3_open* family of functions expects the name arguments in UTF-8 encoding. Not quite. sqlite3_open16 et al expects UTF-16 encoding. Windows supports UTF-16 natively. True, I glossed over that :) Still, I'm fairly certain it's an UTF-8 issue since Ann is using MFC. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite and aao characters in file names
"Mihai Limbasan" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > The sqlite3_open* family of functions expects the name arguments in > UTF-8 encoding. Not quite. sqlite3_open16 et al expects UTF-16 encoding. Windows supports UTF-16 natively. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite and åäö characters in fi le names
[EMAIL PROTECTED] wrote: Hi, I'm completely new to this list so I will now see how these mailing lists works. We have been using sqlite 3.3.7 in a small Windows MFC (C++) application. The database is placed in the Document and Settings\\Application Data\-folder. This worked fine until a user with the username Administratör (Administrator in Swedish) showed up. After some research, it seems that sqllite3_open strips away åäö. I also tried the newer 3.6.1. Then I tried an old Demo program (CppSQLiteDemo) that uses a sqlite3.dll noticeable smaller than the others, 218 kB and the datestamp is from 2004-10-11. This one works just fine, and using this DLL in out MFC-program then works fine too. At least for open files with file/folder-names containing åäö, I haven't checked if something else has quit working. So any ideas what I should do? Use the old DLL and start checking if something else has quit, or is there another way to make use of the newer DLLs? Sincerely /Ann ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users The sqlite3_open* family of functions expects the name arguments in UTF-8 encoding. I'm guessing that you don't pass them as UTF-8 but encoded in your operating system's default code page, so sqlite3_open* receives a string containing high (128+) character codes, tries to interpret them as UTF-8 (which obviously fails) and does the right thing in ignoring the invalid characters, which is why you're seeing them as stripped off. Please make sure that the strings you are passing are encoded in UTF-8. -- Multumesc, Mihai Limbasan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite and åäö characters in file n ames
Hi, I'm completely new to this list so I will now see how these mailing lists works. We have been using sqlite 3.3.7 in a small Windows MFC (C++) application. The database is placed in the Document and Settings\\Application Data\-folder. This worked fine until a user with the username Administratör (Administrator in Swedish) showed up. After some research, it seems that sqllite3_open strips away åäö. I also tried the newer 3.6.1. Then I tried an old Demo program (CppSQLiteDemo) that uses a sqlite3.dll noticeable smaller than the others, 218 kB and the datestamp is from 2004-10-11. This one works just fine, and using this DLL in out MFC-program then works fine too. At least for open files with file/folder-names containing åäö, I haven't checked if something else has quit working. So any ideas what I should do? Use the old DLL and start checking if something else has quit, or is there another way to make use of the newer DLLs? Sincerely /Ann ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Details of error messages. Was: "unable to open database file" on DROP
On Aug 13, 2008, at 10:59 AM, Dennis Cote wrote: > D. Richard Hipp wrote: >> >> (2) Formal and detail requirements that define precisely what SQLite >> does. >> >> http://www.sqlite.org/draft/tokenreq.html >> http://www.sqlite.org/draft/syntax.html >> > > These look great. I noticed a few typos, but on the whole they are > very > good. > > I noticed that in many places you specify that sqlite will fail with > an > error, but you do not specify what the error will be (i.e. the error > code and or message isn't specified). Perhaps these details should > be added. Consider what happens if an interaction with the library contains two or more errors. Only a single error message and error code can be returned. Consequently, if the interaction contains error A we cannot guarantee that the code and message returned will refer to A, since it might instead refer to error B. We can guarantee that some kind of error will be returned. We just cannot guarantee what the error code and message content will be since that depends on what other errors might be present. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite freestanding.
Most people are using it the way you described. I assume they had success since it is the most widely deployed data base engine. On Thu, Aug 21, 2008 at 12:07 PM, Ricardo Hawerroth Wiggers - Terceiro < [EMAIL PROTECTED]> wrote: > Hello. > > Has anyone used sqlite in a freestanding embedded environment? If anyone > had success with it, how about the footprint? And storage medium, direct > flash access? > > Thanks, > Ricardo > ___ > 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] Recursive triggers - any news about their implementation?
Hello everyone! It's been almost two years since the last round of discussions about recursive trigger implementation (on this mailing list). Are there any news about their support in near future? (same with foreign keys) I wish there was a rough schedule for the unsupported features ( http://www.sqlite.org/omitted.html). Thanks, Tomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connection to in-memory database
This was the model that I was using. But I found out that I get a table
lock for dropping tables for no reason when you do interleave steps for
different prepares. Any body needs a proof I can create a test case for you.
No I did not forget to do finalize for the prior prepares.
The better way to do this is:
attach ':memory:' as db2
attach ':memory:' as db3
You can do 10 of these puppies and possibly increase it to 32 or 64
depending on your CPU. Your main database can be ':memory:' as well.
I am going to change my test script to see if the lock problem is solved.
Thanks,
-Alex
On Tue, Aug 19, 2008 at 9:17 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:
> You don't need to open a second connection. The sole connection can be used
> from any thread.
>
>
> On Tue, Aug 19, 2008 at 6:17 PM, vincent cridlig <[EMAIL PROTECTED]>wrote:
>
>> Hi,
>>
>> I would like to use transactions from separate threads, each thread having
>> one connection to a single in-memory db.
>>
>> I just read in the sqlite doc that in-memory sqlite databases (using
>> sqlite3_open(":memory:", ...)) are private to a single connection.
>> Is there a way to open a second connection to the same in-memory database
>> (for example from a second thread)? Has someone ever tried to do (or
>> implement) that?
>>
>> Any help appreciated.
>>
>> Thanks
>> Vincent
>>
>>
>>
>>
>> _
>> Envoyez avec Yahoo! Mail. Une boite mail plus intelligente
>> http://mail.yahoo.fr
>> ___
>> 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] how to check whether the database file opend or closed?
kriscbe wrote: thanks for eplay mihai i am asking is there any function to check the database is opened or not opened? in a single thread? thanks kris No, there is no such function because - you already know whether it's opened or not because you must store the database connection handle obtained from the sqlite3_open* family of functions - for example, initialize that variable to NULL before opening the connection and after closing it, so your question becomes a simple check against NULL on that variable - it makes no sense to have such a function - the database connection handle "belongs" to your application, not to the SQLite engine (that's why you have to pass the handle as the first parameter to almost every SQLite function) If you're asking whether there is a function to check whether the *file* which hosts the database structure and data is open or not, that depends on the operating system you are using, and the answer should be available in the operating system API documentation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to check whether the database file opend or closed?
thanks for eplay mihai i am asking is there any function to check the database is opened or not opened? in a single thread? thanks kris Mihai Limbasan wrote: > > kriscbe wrote: >> hi all, >> >> once the database file is opened is some other function how to check >> the >> db file is opened or not opened? >> >> is there any function like "isopen()" in sqlite??? >> >> thanks >> kris >> >> > No, there is no such function, it's the application's responsibility to > track the database connections it has opened. > I'm reproducing below part of a mail message sent to the list yesterday > which might clarify the situation: > > > Doug Porter wrote: > > > Is there a way to get a list of connections that are opened on a > > > particular SQLite database file? > > > > > > Our software uses SQLite to save our data and we want to warn a user > > > who opens a file that is already opened by another user. I tried a > > > homebrewed approach (keep a table of open connections manually), but > > > ran into a couple places where that won't work. > > > > > > Any help would be greatly appreciated! > > > > > > doug > > Mihai Limbasan wrote: > > The database connection object is handled by the SQLite database > engine and stored in memory allocated by the database engine. > > SQLite does not use a client-server architecture but is completely > contained in a library that you link with your application, thus > SQLite's functions run within your application process and the database > connection objects exist on a per-process basis. > > Your question seems to suggest that your users might run multiple > instances of your application, potentially on separate machines. If that > holds true, there is no way to get a list of connections from SQLite > since those connection objects belong to separate processes potentially > on separate machines. You will have to use an IPC mechanism of your > choice to handle communication between instances of your app. > > Hope this helps. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/how-to-check-whether-the-database-file-opend-or-closed--tp19101495p19103046.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to check whether the database file opend or closed?
kriscbe wrote: hi all, once the database file is opened is some other function how to check the db file is opened or not opened? is there any function like "isopen()" in sqlite??? thanks kris No, there is no such function, it's the application's responsibility to track the database connections it has opened. I'm reproducing below part of a mail message sent to the list yesterday which might clarify the situation: > Doug Porter wrote: > > Is there a way to get a list of connections that are opened on a > > particular SQLite database file? > > > > Our software uses SQLite to save our data and we want to warn a user > > who opens a file that is already opened by another user. I tried a > > homebrewed approach (keep a table of open connections manually), but > > ran into a couple places where that won't work. > > > > Any help would be greatly appreciated! > > > > doug Mihai Limbasan wrote: > The database connection object is handled by the SQLite database engine and stored in memory allocated by the database engine. > SQLite does not use a client-server architecture but is completely contained in a library that you link with your application, thus SQLite's functions run within your application process and the database connection objects exist on a per-process basis. > Your question seems to suggest that your users might run multiple instances of your application, potentially on separate machines. If that holds true, there is no way to get a list of connections from SQLite since those connection objects belong to separate processes potentially on separate machines. You will have to use an IPC mechanism of your choice to handle communication between instances of your app. Hope this helps. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any equivalent to MSSQL's UPDATE..FROM.. clause ?
I met the same issue. REPLACE is the right way to override it.
Assuming C1 is the primary key or at least unique,
instead of
UPDATE T1 INNER JOIN T2 ON T1.C1=T2.C1
SET T1.C2=T2.C2
WHERE T1.C2<>T2.C2;
you can write:
REPLACE INTO T1
SELECT T1.C1,T2.C2,C3,C4 FROM T1 INNER JOIN T2 ON T1.C1=T2.C1
WHERE T1.C2<>T2.C2;
Beware, SELECT shall include the full T1 column set
Samuel Neff a écrit :
> I'm trying to update records in one table based on joined data in another
> table. MSSQL has support for a "FROM" clause within an UPDATE statement
> which makes this type of thing very easy. Is there any equivalent in
> SQLite? The only way I've found to achive the same results is to use a
> subselect within the SET clause of the UPDATE statement, but that requires
> duplicating the WHERE clause within the subselect which is a lot of extra
> typing and I'm sure a lot of extra work for SQLite.
>
> MSSQL:
>
> UPDATE T1
> SET
> A = T2...,
> B = T2...,
> C = T2...,
> FROM T1 INNER JOIN T2 ON
>
> SQLite:
>
> UPDATE T1
> SET
> A = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
> T1.RowID),
> B = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
> T1.RowID),
> C = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
> T1.RowID),
> ...
>
> Here are samples of equivalent code in MSSQL and SQLite. Is there a way to
> simplify the UPDATE statement in the SQLite code?
>
> I'm not replacing the target row entirely, I don't think INSERT OR REPLACE
> will work in this scenario.
>
> Thanks,
>
> Sam
>
> --
> -- MSSQL
> --
>
> CREATE TABLE #T1(
> ID INTEGER PRIMARY KEY IDENTITY,
> A VARCHAR(100),
> B VARCHAR(100),
> C VARCHAR(100));
>
> CREATE TABLE #T2(
> ID INTEGER PRIMARY KEY IDENTITY,
> A VARCHAR(100),
> B VARCHAR(100),
> C VARCHAR(100));
>
> INSERT INTO #T1 VALUES ('a1', 'b1', 'c1');
> INSERT INTO #T1 VALUES (NULL, 'b2', 'c2');
> INSERT INTO #T1 VALUES ('a3', NULL, 'c3');
> INSERT INTO #T1 VALUES ('a4', 'b4', NULL);
>
> INSERT INTO #T2 VALUES ('A1', 'B1', 'C1');
> INSERT INTO #T2 VALUES ('A2', NULL, 'C2');
> INSERT INTO #T2 VALUES ('A3', 'B3', NULL);
> INSERT INTO #T2 VALUES (NULL, 'B4', 'C4');
>
> SELECT * FROM #T1;
> SELECT * FROM #T2;
>
> UPDATE#T1
> SETA = COALESCE(#T1.A, #T2.A),
> B = COALESCE(#T1.B, #T2.B),
> C = COALESCE(#T1.C, #T2.C)
> FROM#T1 INNER JOIN #T2 ON #T1.ID = #T2.ID;
>
> SELECT * FROM #T1;
>
> DROP TABLE #T1;
> DROP TABLE #T2;
>
> --
> -- SQLite
> --
>
> CREATE TEMP TABLE T1(
> ID INTEGER PRIMARY KEY AUTOINCREMENT,
> A TEXT,
> B TEXT,
> C TEXT);
>
> CREATE TEMP TABLE T2(
> ID INTEGER PRIMARY KEY AUTOINCREMENT,
> A TEXT,
> B TEXT,
> C TEXT);
>
> INSERT INTO T1 VALUES (NULL, 'a1', 'b1', 'c1');
> INSERT INTO T1 VALUES (NULL, NULL, 'b2', 'c2');
> INSERT INTO T1 VALUES (NULL, 'a3', NULL, 'c3');
> INSERT INTO T1 VALUES (NULL, 'a4', 'b4', NULL);
>
> INSERT INTO T2 VALUES (NULL, 'A1', 'B1', 'C1');
> INSERT INTO T2 VALUES (NULL, 'A2', NULL, 'C2');
> INSERT INTO T2 VALUES (NULL, 'A3', 'B3', NULL);
> INSERT INTO T2 VALUES (NULL, NULL, 'B4', 'C4');
>
> SELECT * FROM T1;
> SELECT * FROM T2;
>
>
> -- here's the ugly statement I'd like to simplfy
>
> UPDATET1
> SETA = (
> SELECT COALESCE(InnerT1.A, T2.A)
> FROM T1 InnerT1, T2
> WHERE InnerT1.ID = T1.ID
> AND T2.ID = T1.ID
> ),
>B = (
> SELECT COALESCE(InnerT1.B, T2.B)
> FROM T1 InnerT1, T2
> WHERE InnerT1.ID = T1.ID
> AND T2.ID = T1.ID
> ),
>C = (
> SELECT COALESCE(InnerT1.C, T2.B)
> FROM T1 InnerT1, T2
> WHERE InnerT1.ID = T1.ID
> AND T2.ID = T1.ID
> )
> ;
>
> SELECT * FROM T1;
>
> DROP TABLE T1;
> DROP TABLE T2;
>
>
> -
> We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
> the Washington D.C. Contact [EMAIL PROTECTED]
> ___
> 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] sqlite3_next_stmt in SQLite 3.5.9
Hi Igor,
I used SQLite versio n 3.5.9.
I read the SQLite online document and the suggession that we need to finalize
all the prepare statement associated with database connection before closing
the connection as below
while( (pStmt = sqlite3_next_stmt(pDb, 0))!=0 ){
sqlite3_finalize(pStmt);
}
sqlSt= sqlite3_close(pDb);
but the codes didn't return the syntax for sqlite3_next_stmt. Is
sqlite3_next_stmt is valid command in SQLite 3.5.9
Thanks
JP
___
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
