Re: [sqlite] what is the unit of SQLITE_STATUS_MEMORY_USED?
On 17 Dec 2011, at 5:02am, smallboat wrote: > Do you have or know some wiki or documents about this? I want to know more > about it. > > > It is the sum of the allocation > sizes as reported by the xSize method in sqlite3_mem_methods , based > onhttp://www.sqlite.org/c3ref/c_status_malloc_size.html. > Does it mean it depend son what the allocation size is? Each time SQLite calls malloc() it has to specify how many bytes of memory it wants. The value you're getting back from sqlite3_status() is the total number of bytes malloc() has delivered to SQLite. (Answer simplified because SQLite also releases memory while it's working.) Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what is the unit of SQLITE_STATUS_MEMORY_USED?
Hi, Dan: Do you have or know some wiki or documents about this? I want to know more about it. It is the sum of the allocation sizes as reported by the xSize method in sqlite3_mem_methods , based on http://www.sqlite.org/c3ref/c_status_malloc_size.html. Does it mean it depend son what the allocation size is? Thank you for your reply and help. Regards, Jun - Original Message - From: Dan Kennedy To: sqlite-users@sqlite.org Cc: Sent: Friday, December 16, 2011 8:56 PM Subject: Re: [sqlite] what is the unit of SQLITE_STATUS_MEMORY_USED? On 12/17/2011 06:16 AM, smallboat wrote: > Hi, > > > I am using sqlite_status = > sqlite3_status(SQLITE_STATUS_MEMORY_USED,&pCurrent,&pHighwater, resetFlag); > to get the memory used by sqlite. > > > What is the memory unit for pCurrent (the max sqlite memory used)? > > Is it in bytes, bit or something else? Bytes. ___ 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] what is the unit of SQLITE_STATUS_MEMORY_USED?
On 12/17/2011 06:16 AM, smallboat wrote: Hi, I am using sqlite_status = sqlite3_status(SQLITE_STATUS_MEMORY_USED,&pCurrent,&pHighwater, resetFlag); to get the memory used by sqlite. What is the memory unit for pCurrent (the max sqlite memory used)? Is it in bytes, bit or something else? Bytes. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Calculating MSO
David Bicking wrote: > I need to calculate Months Sales Outstanding. > > CREATE TABLE AR > Cust Text > AR Double > > CREATE TABLE Sales > Cust Text > Per Integer -- runs 1, 2, 3, etc > Sales Double > > > The calculation is that for each customer: > MSO = (Per + (AR-sum(Sales)/Sales)) > Where Per and Sales are for the lowest > Period where Sum(Sales) is greater than the AR Something like this: select Cust, (Per + (AR - SumSales) / Sales) MSO from ( select s.Cust Cust, AR, Per, Sales, (select sum(s2.Sales) from Sales s2 where s2.Cust = s.Cust and s2.Per <= s.Per) SumSales from AR join Sales s on (AR.Cust = s.Cust) where SumSales > AR and SumSales - s.Sales <= AR ); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FW: Which version of SQLite?
Hi dvn What can be done with ruby I/O? Rewrite the code in assembly? I work on Arm linux. Does Ruby support the kind of stuff? regards Nataraj On 12/16/11, Don V Nielsen wrote: > Ruby is awesome, especially when working with Sqlite...using ActiveRecord. > But its I/O is really slow, so propagating a database is not its thing. > > dvn > > On Wed, Dec 14, 2011 at 8:32 PM, Nataraj S Narayan > wrote: > >> Hi >> >> I am also an ex-clipper. I miss the old 'code blocks' days. I think >> Ruby comes closest to Clipper with its own code blocks. >> >> regards >> >> Nataraj >> >> On Thu, Dec 15, 2011 at 7:17 AM, Jeff Matthews wrote: >> > >> > >> > I want to use SQLite in a C# app I am developing. >> > >> > >> > >> > My database knowledge drops off around 1996, when I gave up Clipper >> > programming. I have since learned a little about some of the new >> methods >> > used by database gurus. But I remember Clipper like it was yesterday >> since >> > I did so much of it. >> > >> > >> > >> > Here is a short snippet of the logic I recall using back when: >> > >> > >> > >> > Select (0) // Provides allocation for a new file handle for opening a >> > database >> > >> > Use Customers // Will open Customers dbf, where in those days, each >> table >> > was its own file, and thus, we had multiple dbf files in an app to use >> > relational data. >> > >> > Set Index to Phone, LastName // The indexes were also stored in >> separate >> > files. >> > >> > Set Order to 2 // This would mean that our seeks would use the >> > LastName >> > index's sort order >> > >> > >> > >> > seek "MATTHEWS" // Try to find the first instance of search string in >> the >> > index and move record pointer to the row, or if not found, eof() >> > >> > >> > >> > numrecs=0 >> > >> > if found(); >> > >> >do while trim(upper(LastName))="MATTHEWS" .and. !eof() >> // >> > cycle through the records and stop if eof() is hit >> > >> >delete // delete the entire row >> > >> >numrecs=numrecs+1 >> > >> >skip // go to next record >> > >> >enddo >> > >> > endif >> > >> > showMessage(ltrim(str(numrecs,0))+" records deleted.") >> > >> > >> > >> > That's it. >> > >> > >> > >> > I am not wanting to have to deal with excitingly new ways to do all >> > this, >> > such as DataSets or Entities, unless someone says, "But, you must!" or >> > "You're crazy not to." >> > >> > >> > >> > So, which is the best SQLite download for me to use in C# using the >> closest >> > syntax and logic flow as set forth above? I am ready to download and >> start >> > hacking. >> > >> > >> > >> > Thanks. >> > >> > ___ >> > 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
[sqlite] what is the unit of SQLITE_STATUS_MEMORY_USED?
Hi, I am using sqlite_status = sqlite3_status(SQLITE_STATUS_MEMORY_USED, &pCurrent, &pHighwater, resetFlag); to get the memory used by sqlite. What is the memory unit for pCurrent (the max sqlite memory used)? Is it in bytes, bit or something else? Thanks Joe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Calculating MSO
Sorry I messed up the parens. the actual formula is more complicated, so I decided to hastily take the outer most layers away messing up the formula in the process. MSO = (Per + (AR-sum(Sales))/Sales) And yes, result should be 2.7 not 2.3. Stupid typo on my part. Thankfully I just got out of a meeting where I was told "it can't be done efficiently" is an acceptable answer at this time.I just need to make sure I'm not missing some obvious way to get the answer out of the data. David From: Igor Tandetnik To: sqlite-users@sqlite.org Sent: Friday, December 16, 2011 4:05 PM Subject: Re: [sqlite] Calculating MSO David Bicking wrote: > The calculation is that for each customer: > MSO = (Per + (AR-sum(Sales)/Sales)) > > Result > Cust MSO > 01 2.3 = (3+(100-120)/60)) You have more closing parentheses here than opening ones. If we ignore the rightmot closing paren, the grouping of actual numbers doesn't match the grouping of variables in the formula. What is being divided by Sales: sum(Sales) or (AR - sum(Sales)) ? Further, (3+(100-120)/60) == 2.7, not 2.3. Your example doesn't add up. > Hopefully I described that in a meaningful way. Is it possible to do that > efficiently or at all using SQL? I'd bet it is possible (I might take a stab at it once you clarify the problem statement), but I doubt it'd be efficient. -- Igor Tandetnik ___ 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] Calculating MSO
David Bicking wrote: > The calculation is that for each customer: > MSO = (Per + (AR-sum(Sales)/Sales)) > > Result > Cust MSO > 01 2.3 = (3+(100-120)/60)) You have more closing parentheses here than opening ones. If we ignore the rightmot closing paren, the grouping of actual numbers doesn't match the grouping of variables in the formula. What is being divided by Sales: sum(Sales) or (AR - sum(Sales)) ? Further, (3+(100-120)/60) == 2.7, not 2.3. Your example doesn't add up. > Hopefully I described that in a meaningful way. Is it possible to do that > efficiently or at all using SQL? I'd bet it is possible (I might take a stab at it once you clarify the problem statement), but I doubt it'd be efficient. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Calculating MSO
I need to calculate Months Sales Outstanding. CREATE TABLE AR Cust Text AR Double CREATE TABLE Sales Cust Text Per Integer -- runs 1, 2, 3, etc Sales Double The calculation is that for each customer: MSO = (Per + (AR-sum(Sales)/Sales)) Where Per and Sales are for the lowest Period where Sum(Sales) is greater than the AR So AR Cust AR 01 100 02 50 Sales Cust Per Sales 01 1 60 01 3 60 01 4 60 02 1 15 02 2 15 02 3 15 02 4 15 Result Cust MSO 01 2.3 = (3+(100-120)/60)) 02 3.3 = (4+(50-60)/15)) Hopefully I described that in a meaningful way. Is it possible to do that efficiently or at all using SQL? Thanks, David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to handle the locks with WAL mode?
Paxdo Presse wrote: >>> Are we to understand that the pending / exclusive locks function differently >>> and allow simultaneous shared locks? >> >> Yes. Note that, with WAL, writers never write to the same areas of files >> that readers may read from. > > This would mean that with wal mode, > Sqlite now some kind of lock on the record and not the entire database? :-) Would not. The documentation at http://sqlite.org/wal.html describes exactly how this mode works. You don't need to guess. > For example: > > Process A: > > - Begin immediate transaction > - UPDATE Table_A SET Column_a='..' WHERE id=1 > - End transaction > > If I have a process B that tries to read the record "id 2" > between the"UPDATE..." of process A, and "END..." of process A, > it can? Yes it can. > But if he tries to read the record "id 1", it can not, right? Wrong. It can. Any readers that started before the write transaction was committed, would see the original version of the record. Any readers that started after, would see the updated version. Neither would block. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to handle the locks with WAL mode?
Thank you Simon and Igor. >> Are we to understand that the pending / exclusive locks function differently >> and allow simultaneous shared locks? > > Yes. Note that, with WAL, writers never write to the same areas of files that > readers may read from. This would mean that with wal mode, Sqlite now some kind of lock on the record and not the entire database? :-) For example: Process A: - Begin immediate transaction - UPDATE Table_A SET Column_a='..' WHERE id=1 - End transaction If I have a process B that tries to read the record "id 2" between the"UPDATE..." of process A, and "END..." of process A, it can? But if he tries to read the record "id 1", it can not, right? Thank you olivier ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite with Vis. Studio C#
Can you attach some code samples showing usage in that way? Sent via BlackBerry from T-Mobile -Original Message- From: Don V Nielsen Sender: sqlite-users-boun...@sqlite.org Date: Fri, 16 Dec 2011 08:53:41 To: General Discussion of SQLite Database Reply-To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite with Vis. Studio C# My application never uses a DataSet. I push all db responsibility to sqlite. It returns DbDataReaders when I need to recurse through results. On Thu, Dec 15, 2011 at 1:40 PM, Jeff Matthews wrote: > Regarding my earlier question as to .NET SQlite syntax, I was wondering if > I > had to use DataAdapters and create and fill objects (DataSets), or not. > > Here is some sample code dealing with SQLite in C#: > http://www.codeproject.com/KB/cs/SQLiteCSharp.aspx > > It looks to me like he is loading the database into a DataSet and then, > doing his thing on the DataSet, rather than on the database directly. > > See how he is building his query as a string (surrounded by quotes) and > then, calling a function to conduct it? > > Is this necessary - i.e., is this the only way to query and manipulate > SQLite data from C#? > > I was thinking that we could just use sql statements literally, without > having to send them as a string to a function. > > Does my question make sense? > > Can it not be done just using the sql literally and skipping all the > DataAdapter, DataSet stuff? > > > > > > ___ > 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] How to handle the locks with WAL mode?
Paxdo Presse wrote: > In the doc, it is indicated that exclusive lock is required to write to the > database. > In this case, it can not be simultaneously other locks for reading (Shared..). > > And in wal mode? It can, obviously. > Are we to understand that the pending / exclusive locks function differently > and allow simultaneous shared locks? Yes. Note that, with WAL, writers never write to the same areas of files that readers may read from. > What locks/transactions should be used, to allow simultaneous readers and one > writer? The same that are used without WAL. A transaction simply doesn't get blocked in some cases where it used to be. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to handle the locks with WAL mode?
On 16 Dec 2011, at 2:56pm, Paxdo Presse wrote: > In the doc, it is indicated that exclusive lock is required to write to the > database. > In this case, it can not be simultaneously other locks for reading (Shared..). > > And in wal mode? > > Are we to understand that the pending / exclusive locks function differently > and allow simultaneous shared locks? > > What locks/transactions should be used, to allow simultaneous readers and one > writer? When using the SQLite library your application should not be doing any locking of the file or any special manipulation of TRANSACTIONs. All that is taken care of by the SQLite library. Simply use TRANSACTIONs however they relate to the data you're manipulating. It's up to SQLite to do The Right Thing depending on what journaling mode you're in. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to handle the locks with WAL mode?
In the doc, it is indicated that exclusive lock is required to write to the database. In this case, it can not be simultaneously other locks for reading (Shared..). And in wal mode? Are we to understand that the pending / exclusive locks function differently and allow simultaneous shared locks? What locks/transactions should be used, to allow simultaneous readers and one writer? Thank you Le 16 déc. 2011 à 15:40, Igor Tandetnik a écrit : > Paxdo Presse wrote: >> With WAL mode, >> there may be multiple readers and one writer, SIMULTANEOUSLY. >> >> is very different from the rollback mode. >> >> Should we handle locks and transactions differently? > > How do you handle them now? Is there a particular scenario you are concerned > about? > -- > Igor Tandetnik > > ___ > 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] FW: Which version of SQLite?
Ruby is awesome, especially when working with Sqlite...using ActiveRecord. But its I/O is really slow, so propagating a database is not its thing. dvn On Wed, Dec 14, 2011 at 8:32 PM, Nataraj S Narayan wrote: > Hi > > I am also an ex-clipper. I miss the old 'code blocks' days. I think > Ruby comes closest to Clipper with its own code blocks. > > regards > > Nataraj > > On Thu, Dec 15, 2011 at 7:17 AM, Jeff Matthews wrote: > > > > > > I want to use SQLite in a C# app I am developing. > > > > > > > > My database knowledge drops off around 1996, when I gave up Clipper > > programming. I have since learned a little about some of the new > methods > > used by database gurus. But I remember Clipper like it was yesterday > since > > I did so much of it. > > > > > > > > Here is a short snippet of the logic I recall using back when: > > > > > > > > Select (0) // Provides allocation for a new file handle for opening a > > database > > > > Use Customers // Will open Customers dbf, where in those days, each > table > > was its own file, and thus, we had multiple dbf files in an app to use > > relational data. > > > > Set Index to Phone, LastName // The indexes were also stored in > separate > > files. > > > > Set Order to 2 // This would mean that our seeks would use the LastName > > index's sort order > > > > > > > > seek "MATTHEWS" // Try to find the first instance of search string in > the > > index and move record pointer to the row, or if not found, eof() > > > > > > > > numrecs=0 > > > > if found(); > > > >do while trim(upper(LastName))="MATTHEWS" .and. !eof() > // > > cycle through the records and stop if eof() is hit > > > >delete // delete the entire row > > > >numrecs=numrecs+1 > > > >skip // go to next record > > > >enddo > > > > endif > > > > showMessage(ltrim(str(numrecs,0))+" records deleted.") > > > > > > > > That's it. > > > > > > > > I am not wanting to have to deal with excitingly new ways to do all this, > > such as DataSets or Entities, unless someone says, "But, you must!" or > > "You're crazy not to." > > > > > > > > So, which is the best SQLite download for me to use in C# using the > closest > > syntax and logic flow as set forth above? I am ready to download and > start > > hacking. > > > > > > > > Thanks. > > > > ___ > > 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 with Vis. Studio C#
My application never uses a DataSet. I push all db responsibility to sqlite. It returns DbDataReaders when I need to recurse through results. On Thu, Dec 15, 2011 at 1:40 PM, Jeff Matthews wrote: > Regarding my earlier question as to .NET SQlite syntax, I was wondering if > I > had to use DataAdapters and create and fill objects (DataSets), or not. > > Here is some sample code dealing with SQLite in C#: > http://www.codeproject.com/KB/cs/SQLiteCSharp.aspx > > It looks to me like he is loading the database into a DataSet and then, > doing his thing on the DataSet, rather than on the database directly. > > See how he is building his query as a string (surrounded by quotes) and > then, calling a function to conduct it? > > Is this necessary - i.e., is this the only way to query and manipulate > SQLite data from C#? > > I was thinking that we could just use sql statements literally, without > having to send them as a string to a function. > > Does my question make sense? > > Can it not be done just using the sql literally and skipping all the > DataAdapter, DataSet stuff? > > > > > > ___ > 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 reading old data
Yeah, I see the same two solutions. The first solution would also ensure currently encrypted databases will still be working. Whereas changing the encryption would make live a lot harder for currently deployed databases, which would need a conversion. For testing purposes I just changed the encryption algorithm in System.Data.SQLite to exclude the change counter, so the change counter remains plaintext. My tests with the 3 threads did run fine and without any problems for several runs I've executed. It would be great if there's a statement from the SQLite developers what the requirements for the encryptor are. And what their opinion is regarding this issue. > -Ursprüngliche Nachricht- > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] Im Auftrag von Michael Stephenson > Gesendet: Freitag, 16. Dezember 2011 15:27 > An: 'General Discussion of SQLite Database' > Betreff: Re: [sqlite] SQLite reading old data > > I think the only solutions would be: 1) SQLIte changes so that it does not > use > a direct file read/write for the change counter. 2) Have the page-level > encryptor specifically not encrypt certain parts of the database header, such > as the change counter, when saving page 1 to disk. > > I imagine that the direct reads/writes are to help ensure consistency and > recovery in the face of an application crash. > > You might try turning on WAL and see what happens. > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Fabrizio Steiner > Sent: Thursday, December 15, 2011 3:00 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite reading old data > > The answers to your questions : > 1)Yes I'm still using System.Data.SQLite but without any key. > 2)No I don't use WAL. > 3)Running only one thread didn't bring up the problem. > 4)Didn't have any time yet. > 5)Standard System.Data.SQLite with RC4 algorithm impleemnted there. > > I've taken a look into the the caching of SQLite during the afternoon because > I suspected some issues with caching. And I found a problem with the change > counter exactly as you thought. > > I took a look into the source code of the pager and the pager cache. I've > found the pager cache decides if the cache needs to be flushed whenever a > shared lock gets requested. In order to determine if a flush is needed or not, > the change counter of the db file is compared to the stored one in the pager > structure. The change counter is directly retrieved from the underlying OS > (sqlite3OsRead), so if the database is encrypted, the encrypted bytes will be > retrieved here (Sytem.Data.SQLite encrypts the entire database, including > page 1 and the SQLite header). > > So dbFileVers of the pager structure is always the encrypted value. I started > checking if there was a situation where it was used as the plaintext value. > > In the function pager_write_changecounter the change counter will get > incremented, but the encrypted value pPg->pPager->dbFileVer is used. > After incrementation the value will be put back into the page buffer. During > the write of the page 1, the is again encrypted (see, pager_write_pagelist). > After the page has been written the dbFileVers gets updated > (pager_write_pagelist Line 4049) with the encrypted value. > > So at least for incrementing the change counter the plaintext value should be > used. > > When RC4 with the full database encryption (as implemented in > System.Data.SQLite) is used the following happens. > RC4 basically XORs a plaintext byte with a pseudo random byte. Let's assume > the following change counter values, for simplicity consider only the last 4 > bits of it. As we've seen the encrypted change counter is incremented, > encrypted and stored back into the file. Let's consider the following > operations. > > DB Initial State > - Encrypted DB File counter: X Y Z > 0(The bits X, Y, Z are unknown, but the last bit is 0.) > > First update of Database: > - Update DB, Increment counter: X Y Z > 1(Adding one to the encrypted counter.) > - New encrypted Value in DB: (X XOR K1) (Y XOR K2) (Z > XOR K3) (1 XOR 1) = 0(Assuming the LSB of the pseudo byte is 1, the > probability is 1/2 for this if its purely random.) > > Let's update the database again: > - Update DB, Increment counter: (X XOR K1) (Y XOR K2) (Z > XOR K3) 1(Adding one to the encrypted counter.) > - New encrypted Value in DB:((X XOR K1) XOR K1) ((Y XOR K2) XOR K2) ((Z > XOR K3) XOR K3) 0(The same pseudo byte is again used for encryption.) >= X Y Z > 0 > > As a result after the second db update the encrypted change counter is the > same as before the updates occured. dbFileVers contains 12 more bytes but >
Re: [sqlite] How to handle the locks with WAL mode?
Paxdo Presse wrote: > With WAL mode, > there may be multiple readers and one writer, SIMULTANEOUSLY. > > is very different from the rollback mode. > > Should we handle locks and transactions differently? How do you handle them now? Is there a particular scenario you are concerned about? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How about the way put a database of SQLite 3 beside web folder?
On 15 Dec 2011, at 7:52pm, Apple wrote: > I just connected SQLite 3 with PHP. > The key point could be My database should put in same fold where as > info.php. > And set > extension = php_pdo.dll > extension = php_pdo_sqlite.dll > first. > > Know I have a question: > if I can put the database of SQLite 3 beside the folder of web, > and still can be accessed via php > Is there some way? The SQLite database file is accessed as a file by PHP. It must be accessible (both location and protection) by your web server process as a local file. In your 'open' command specify the full path to the database. I would not advise putting it in a folder that you are serving to the web since this would allow anyone browsing yourweb site to inspect the full contents of the file. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite reading old data
I think the only solutions would be: 1) SQLIte changes so that it does not use a direct file read/write for the change counter. 2) Have the page-level encryptor specifically not encrypt certain parts of the database header, such as the change counter, when saving page 1 to disk. I imagine that the direct reads/writes are to help ensure consistency and recovery in the face of an application crash. You might try turning on WAL and see what happens. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Fabrizio Steiner Sent: Thursday, December 15, 2011 3:00 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite reading old data The answers to your questions : 1)Yes I'm still using System.Data.SQLite but without any key. 2)No I don't use WAL. 3)Running only one thread didn't bring up the problem. 4)Didn't have any time yet. 5)Standard System.Data.SQLite with RC4 algorithm impleemnted there. I've taken a look into the the caching of SQLite during the afternoon because I suspected some issues with caching. And I found a problem with the change counter exactly as you thought. I took a look into the source code of the pager and the pager cache. I've found the pager cache decides if the cache needs to be flushed whenever a shared lock gets requested. In order to determine if a flush is needed or not, the change counter of the db file is compared to the stored one in the pager structure. The change counter is directly retrieved from the underlying OS (sqlite3OsRead), so if the database is encrypted, the encrypted bytes will be retrieved here (Sytem.Data.SQLite encrypts the entire database, including page 1 and the SQLite header). So dbFileVers of the pager structure is always the encrypted value. I started checking if there was a situation where it was used as the plaintext value. In the function pager_write_changecounter the change counter will get incremented, but the encrypted value pPg->pPager->dbFileVer is used. After incrementation the value will be put back into the page buffer. During the write of the page 1, the is again encrypted (see, pager_write_pagelist). After the page has been written the dbFileVers gets updated (pager_write_pagelist Line 4049) with the encrypted value. So at least for incrementing the change counter the plaintext value should be used. When RC4 with the full database encryption (as implemented in System.Data.SQLite) is used the following happens. RC4 basically XORs a plaintext byte with a pseudo random byte. Let's assume the following change counter values, for simplicity consider only the last 4 bits of it. As we've seen the encrypted change counter is incremented, encrypted and stored back into the file. Let's consider the following operations. DB Initial State - Encrypted DB File counter: X Y Z 0(The bits X, Y, Z are unknown, but the last bit is 0.) First update of Database: - Update DB, Increment counter: X Y Z 1(Adding one to the encrypted counter.) - New encrypted Value in DB: (X XOR K1) (Y XOR K2) (Z XOR K3) (1 XOR 1) = 0(Assuming the LSB of the pseudo byte is 1, the probability is 1/2 for this if its purely random.) Let's update the database again: - Update DB, Increment counter: (X XOR K1) (Y XOR K2) (Z XOR K3) 1(Adding one to the encrypted counter.) - New encrypted Value in DB:((X XOR K1) XOR K1) ((Y XOR K2) XOR K2) ((Z XOR K3) XOR K3) 0(The same pseudo byte is again used for encryption.) = X Y Z 0 As a result after the second db update the encrypted change counter is the same as before the updates occured. dbFileVers contains 12 more bytes but these represent the "db size in pages", the "page number of the first free page" and the "number of free pages". But these may be unchanged if no new pages were needed and there was no free page. => Therefore theres a good chance that a db change is undetected depending on the encryption algorithm. Is it allowed to encrypt the entire databse especially the header with the pagesize, change counter and so on? - If yes, then SQLite should make sure all data get's decrypted prior using these values. - If not, shouldn't SQLite make sure the crypt api never sees the header? I've found no documentation about implementing the crypting api. Kind Regards Fabrizio > -Ursprüngliche Nachricht- > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] Im Auftrag von Michael Stephenson > Gesendet: Donnerstag, 15. Dezember 2011 20:40 > An: 'General Discussion of SQLite Database' > Betreff: Re: [sqlite] SQLite reading old data > > A couple more questions: > > 1) Does "without encryption" mean still using System.Data.SQLite, > just without using a key? > 2) Are you using WAL? > 3) Do y
[sqlite] How to handle the locks with WAL mode?
Hi, With WAL mode, there may be multiple readers and one writer, SIMULTANEOUSLY. is very different from the rollback mode. Should we handle locks and transactions differently? Thank you, olivier ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How about the way put a database of SQLite 3 beside web folder?
Hi I just connected SQLite 3 with PHP. The key point could be My database should put in same fold where as info.php. And set extension = php_pdo.dll extension = php_pdo_sqlite.dll first. Know I have a question: if I can put the database of SQLite 3 beside the folder of web, and still can be accessed via php Is there some way? thanks! tangdao...@gmail.com 13989274151 skype: newmen512 qq:16929852 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using SQLite in C#
A big, public THANKS to Joe Mistachkin for going the extra mile to help me get installed and off and running. It's late, but that's because it's been fun doing theExecuteQuery()'s and ExecuteNonQuery()'s, along with filling selected data on a relation into a DataGridView and playing with the view's appearance. Now, I'm rolling right along. Good night, or more appropriately Good Morning! :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index usage when using UNION
On 16 Dec 2011, at 2:20am, Igor Tandetnik wrote: > Simon Slavin wrote: >> On 15 Dec 2011, at 7:19pm, Alexandr Němec wrote: >> >>> [UNION] >> >> Your 'ORDER BY' clause applies only to the second SELECT. > > Not true. Yeah, so I noticed from Richard's post. Sorry for posting incorrect information everyone. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
