Re: [sqlite] Will SQLite supports UnQL?
2011/8/2 Eric Scouten : > It falls apart badly in a highly distributed environment where ... > > ... May be a RDF storage is more reasonable for this. Operations with atomic facts can be highly distributed. And SPARQL is similar to SQL. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and Java
Yin, A Google search of "sqlite java api" gives several good hits. Click on the first http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers and scroll down to Java Martin Am 02.08.2011 06:01, schrieb yinlijie2011: > Dear, > I want use SQLite, but my program language is Java. And > thewww.sqlite.org not supply API for Java. What should I do? > Thank you! > > Yin Lijie > ___ > 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] 32-bit to 64-bit on Snow Leopard
Hi folks. Just wondering if the tables will remain if I configure SQLite to install the 64 bit version on Snow Leopard over my current 32 bit version. Cheers _ Rich in Toronto ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite and Java
Dear, I want use SQLite, but my program language is Java. And thewww.sqlite.org not supply API for Java. What should I do? Thank you! Yin Lijie ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)
On 2 Aug 2011, at 1:10am, Igor Sereda wrote: > To my humble knowledge, operations with NULL have well-defined semantics, > both in SQL-you-name-it standards and in SQLite. "A < B" may have three > results - TRUE, FALSE and NULL. It doesn't matter whether you can make any > sense of it - it's the spec ;) The spec for '<=' should say that comparing any number with NULL always gives a NULL result. If SQLite is doing anything apart from that, it's a bug. Okay, here it is: SQL92 8.2 (1) (a): "If XV or YV is the null value, then "X Y" is unknown." In this context, returning 'unknown' means returning NULL. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)
Simon, Michael - To my humble knowledge, operations with NULL have well-defined semantics, both in SQL-you-name-it standards and in SQLite. "A < B" may have three results - TRUE, FALSE and NULL. It doesn't matter whether you can make any sense of it - it's the spec ;) Therefore I'm trying to report a bug here according to guideline at http://www.sqlite.org/src/wiki?name=Bug+Reports - I would very much like to hear from SQLite developers whether this report makes sense or if additional information is needed. Cheers, Igor Simon Slavin-3 wrote: > > > On 1 Aug 2011, at 10:45pm, Black, Michael (IS) wrote: > >> If it's meaningless then shouldn't it be a syntax error? > > It's about as meaningless as > > X <= maxreal > > so it would take quite a lot of processing time to identify it as > meaningless. Not sure as if it's worth the processing time. Any decent > debugging effort should find the problem. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Virtual-Table-xBestIndex-and-NULL-Search-Conditions-%28Bug-%29-tp32172549p32174172.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] Will SQLite supports UnQL?
On Sun, Jul 31, 2011 at 12:58, Simon Slavin wrote: These two go together. Multi-master replication (one example of which is a > document store) is relatively easy. Datestamp every value (document) and > whichever one has the lastest date is the one you want. > This is perhaps an acceptable answer if everything is bottlenecked on a single centralized server or a cluster of central servers where the notion of time is tightly synchronized. It falls apart badly in a highly distributed environment where ... 1: The clocks on various devices (PCs, laptops, tablets, mobile) are often in error by minutes or even hours. (If, for example, that the system time zone is set incorrectly.) 2: (As Dustin pointed out earlier) You can't know that the user of a mobile device has actually seen the latest edits made elsewhere. (There might not be network connectivity between a particular device and the rest of the network, for example.) -Eric -- Eric Scouten :: software developer, photographer :: Poulsbo, WA (near Seattle) http://ericscouten.com :: click for Flickr, Facebook, Twitter, LinkedIn links ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)
On 1 Aug 2011, at 10:45pm, Black, Michael (IS) wrote: > If it's meaningless then shouldn't it be a syntax error? It's about as meaningless as X <= maxreal so it would take quite a lot of processing time to identify it as meaningless. Not sure as if it's worth the processing time. Any decent debugging effort should find the problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)
If it's meaningless then shouldn't it be a syntax error? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Monday, August 01, 2011 4:43 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?) On 1 Aug 2011, at 9:47pm, Igor Sereda wrote: > So - who else thinks it's a bug? The SQL standard says 'NULL' means 'I don't know' or 'value missing' or something of the kind. So using a comparison like X > NULL doesn't mean anything, since there can't be a well-ordering principle for a missing value since there's nothing to compare it to. By the way, the recommended way to do what the original line of code does seems to be to use 'typeof(X)'. But I don't know how a beginner SQLite user is expected to know that. Simon. ___ 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] Virtual Table xBestIndex and NULL Search Conditions (Bug?)
On 1 Aug 2011, at 9:47pm, Igor Sereda wrote: > So - who else thinks it's a bug? The SQL standard says 'NULL' means 'I don't know' or 'value missing' or something of the kind. So using a comparison like X > NULL doesn't mean anything, since there can't be a well-ordering principle for a missing value since there's nothing to compare it to. By the way, the recommended way to do what the original line of code does seems to be to use 'typeof(X)'. But I don't know how a beginner SQLite user is expected to know that. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)
Thanks Jay, That's a good hint about the origin of the problem. However, you refer to the sort order, but the problem is with WHERE statement. Since numeric comparison with NULL always evaluates to NULL (see section 4.0 of the link you gave me), a statement like "SELECT * FROM table WHERE value > NULL" would return an empty result set on any table -- *always* -- whereas "SELECT * FROM table WHERE value IS NOT NULL" would return all rows with non-null value. That works on the normal tables, but it's probably broken on the virtual tables in the latest version. I've checked what's happening on 3.7.4: when parsing "SELECT value FROM table WHERE value IS NOT NULL", the xBestIndex method receives no contraints, which, I believe, is the correct thing: pIdxInfo->nConstraint == 0 So - who else thinks it's a bug? Cheers Igor Jay A. Kreibich-2 wrote: > > On Mon, Aug 01, 2011 at 12:34:33PM -0700, Igor Sereda scratched on the > wall: >> >> Hello, >> >> I'm seeing strange input given into xBestIndex method of my virtual >> table. >> >> I'm maintaining sqlite4java wrapper and I'm trying to upgrade it from >> SQLite >> 3.7.4 to 3.7.7.1. A couple of failed tests uncovered that there's a >> problem >> when searching a simple virtual table with constraints that contain NULL. >> >> More specifically, the virtual table is declared as follows in xCreate >> method: >> >> CREATE TABLE x(value INTEGER) >> >> When the following SQL is executed: >> >> SELECT value FROM table WHERE value IS NOT NULL >> >> , xBestIndex receives the following parameters: >> >> pIdxInfo->nConstraint == 1 >> pIdxInfo->aConstraint[0].usable == 1 >> pIdxInfo->aConstraint[0].iColumn == 0 >> pIdxInfo->aConstraint[0].op == 4 (GT) >> >> So basically the search is going to be for condition "value > ?". >> >> When xFilter is called, the value passed is NULL. So instead of searching >> for "value IS NOT NULL" the module is instructed to search for "value > >> NULL" - which gives the opposite result. And when SQL executed is >> "SELECT >> value FROM table WHERE value > NULL", all the parameters are identical. > > All values in SQLite have a consistent sort order. As section 3.1 of > http://sqlite.org/datatype3.html#comparisons shows, NULL is considered > to be the "smallest" value. Hence, "value > NULL" is equivalent to > "value IS NOT NULL". > > It might not be the most obvious logic, but it is the logic used by > SQLite and the query optimizer, so it is the logic that needs to be > used by any virtual table. > >> This problem did not exist in SQLite 3.7.4. > > What did earlier versions do? > >> Do I miss something or is this a bug? > > I assume it is a change in the query optimizer. Since this is a > legit way to express an IS NOT NULL, it isn't exactly "wrong", just > different. > >-j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Virtual-Table-xBestIndex-and-NULL-Search-Conditions-%28Bug-%29-tp32172549p32173021.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] Will SQLite supports UnQL?
2011/8/1 Simon Slavin : > I'm sorry Alexey, I was trying to be funny and failed. Your question is very > important for this situation. Oh, I'm sorry! My english is bad by night :) -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)
On Mon, Aug 01, 2011 at 12:34:33PM -0700, Igor Sereda scratched on the wall: > > Hello, > > I'm seeing strange input given into xBestIndex method of my virtual table. > > I'm maintaining sqlite4java wrapper and I'm trying to upgrade it from SQLite > 3.7.4 to 3.7.7.1. A couple of failed tests uncovered that there's a problem > when searching a simple virtual table with constraints that contain NULL. > > More specifically, the virtual table is declared as follows in xCreate > method: > > CREATE TABLE x(value INTEGER) > > When the following SQL is executed: > > SELECT value FROM table WHERE value IS NOT NULL > > , xBestIndex receives the following parameters: > > pIdxInfo->nConstraint == 1 > pIdxInfo->aConstraint[0].usable == 1 > pIdxInfo->aConstraint[0].iColumn == 0 > pIdxInfo->aConstraint[0].op == 4 (GT) > > So basically the search is going to be for condition "value > ?". > > When xFilter is called, the value passed is NULL. So instead of searching > for "value IS NOT NULL" the module is instructed to search for "value > > NULL" - which gives the opposite result. And when SQL executed is "SELECT > value FROM table WHERE value > NULL", all the parameters are identical. All values in SQLite have a consistent sort order. As section 3.1 of http://sqlite.org/datatype3.html#comparisons shows, NULL is considered to be the "smallest" value. Hence, "value > NULL" is equivalent to "value IS NOT NULL". It might not be the most obvious logic, but it is the logic used by SQLite and the query optimizer, so it is the logic that needs to be used by any virtual table. > This problem did not exist in SQLite 3.7.4. What did earlier versions do? > Do I miss something or is this a bug? I assume it is a change in the query optimizer. Since this is a legit way to express an IS NOT NULL, it isn't exactly "wrong", just different. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)
Hello, I'm seeing strange input given into xBestIndex method of my virtual table. I'm maintaining sqlite4java wrapper and I'm trying to upgrade it from SQLite 3.7.4 to 3.7.7.1. A couple of failed tests uncovered that there's a problem when searching a simple virtual table with constraints that contain NULL. More specifically, the virtual table is declared as follows in xCreate method: CREATE TABLE x(value INTEGER) When the following SQL is executed: SELECT value FROM table WHERE value IS NOT NULL , xBestIndex receives the following parameters: pIdxInfo->nConstraint == 1 pIdxInfo->aConstraint[0].usable == 1 pIdxInfo->aConstraint[0].iColumn == 0 pIdxInfo->aConstraint[0].op == 4 (GT) So basically the search is going to be for condition "value > ?". When xFilter is called, the value passed is NULL. So instead of searching for "value IS NOT NULL" the module is instructed to search for "value > NULL" - which gives the opposite result. And when SQL executed is "SELECT value FROM table WHERE value > NULL", all the parameters are identical. This problem did not exist in SQLite 3.7.4. Do I miss something or is this a bug? Thanks for your help, Igor -- View this message in context: http://old.nabble.com/Virtual-Table-xBestIndex-and-NULL-Search-Conditions-%28Bug-%29-tp32172549p32172549.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] Will SQLite supports UnQL?
On 1 Aug 2011, at 7:20pm, Alexey Pechnikov wrote: > 2011/8/1 Simon Slavin : >> >> On 1 Aug 2011, at 6:56pm, Alexey Pechnikov wrote: >> >>> 2011/8/1 Black, Michael (IS) : This is a side-question to this thread...but has anybody every done row-level locking for edit? >>> >>> What problem are you solving? >> >> Please stop asking key questions. > > I don't understand the problem. I'm sorry Alexey, I was trying to be funny and failed. Your question is very important for this situation. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will SQLite supports UnQL?
2011/8/1 Simon Slavin : > > On 1 Aug 2011, at 6:56pm, Alexey Pechnikov wrote: > >> 2011/8/1 Black, Michael (IS) : >>> This is a side-question to this thread...but has anybody every done >>> row-level locking for edit? >> >> What problem are you solving? > > Please stop asking key questions. I don't understand the problem. IMHO Redis+SQLite is quick way to do it. Use Redis key for locking and incremented key as unique id generator. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will SQLite supports UnQL?
On 1 Aug 2011, at 6:56pm, Alexey Pechnikov wrote: > 2011/8/1 Black, Michael (IS) : >> This is a side-question to this thread...but has anybody every done >> row-level locking for edit? > > What problem are you solving? Please stop asking key questions. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will SQLite supports UnQL?
2011/8/1 Black, Michael (IS) : > This is a side-question to this thread...but has anybody every done row-level > locking for edit? What problem are you solving? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comments
On 1 Aug 2011, at 2:10pm, Richard Hipp wrote: > On Mon, Aug 1, 2011 at 8:53 AM, Marco Bambini wrote: > >> Why this valid statement: >> >> CREATE TABLE USER( >> id text, -- the id of the user >> nametext-- the name of the user >> ); >> >> gives me a syntax error with sqlite 3.7.6.3? > > Works for me. Works for me under 3.7.5. My guess is that you're including some non-printable character. What syntax error message are you getting ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comments
On Mon, Aug 1, 2011 at 8:53 AM, Marco Bambini wrote: > Why this valid statement: > > CREATE TABLE USER( >id text, -- the id of the user >nametext-- the name of the user > ); > > gives me a syntax error with sqlite 3.7.6.3? > Works for me. > > Thanks. > -- > Marco Bambini > http://www.sqlabs.com > > > > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite-Wal file location
I think database will go corrupt if the power goes off during the process of checkpoint. Database will remain intact even if wal file is lost due to power failure. For embedded devices its a balance between flash life, db performance and ram memory usage. Sent from BlackBerry® on Airtel -Original Message- From: Richard Hipp Sender: drhsql...@gmail.com Date: Mon, 1 Aug 2011 08:47:09 To: ; General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite-Wal file location On Mon, Aug 1, 2011 at 8:45 AM, wrote: > Hi, > > Thanks for the detailed explanation. > My original question was more in relation to the .db-wal file. I guess the > same discussion is applicable to the db-wal file also?. In my project, in > all probability one writer and multiple readers. Hence the chances of > corruption is greatly reduced. > One problem I have noticed is that I have to create (an empty) a file with > the same name as the database file in the tmpfs for correct working . > If the WAL file is in volatile storage and you lose power or reset, your database will likely go corrupt. > > > Sent from BlackBerry® on Airtel > > -Original Message- > From: Richard Hipp > Sender: sqlite-users-boun...@sqlite.org > Date: Mon, 1 Aug 2011 07:07:52 > To: General Discussion of SQLite Database > Reply-To: General Discussion of SQLite Database > Subject: Re: [sqlite] Sqlite-Wal file location > > On Sun, Jul 31, 2011 at 11:51 PM, Sreekumar TP >wrote: > > > Hello, > > > > Inorder to suit the needs of my embedded device, I have changed the > > location > > of the .db-wal file from the location of the db file to tmpfs. Does > sqlite > > make assumptions(persistence etc) based on the location of the file ? > > > > An early prototype of WAL did exactly what you describe. But we then > encountered database corruption during testing when two separate processes > tried to access the same database file where one of the processes was in a > chroot jail and the other was not. Both processes used the same name for > the -shm file, but because of the chroot jail, they in fact used two > separate -shm files. Fossil uses SQLite in WAL mode and it often runs in a > chroot jail, so this is not an uncommon scenario. > > The only way we have found to ensure that all processes use the same -shm > file is to put the -shm file in the same directory as the database file. > > For an embedded project, you can put the -shm file wherever you want as > long > as all processes agree to use the same file. If you mess up, and two or > more processes use different -shm files for the same database, database > corruption will result. You have been warned. > > Note that -shm does not have to be a file. We just need an area of shared > memory common to all processes accessing the database. On unix and > windows, > we found this most convenient to implement as a file using mmap(). But if > you have some other mechanism on your embedded device that would work > better, you are welcomed to use that. > > > > > >___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org >___ > 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 > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Comments
Why this valid statement: CREATE TABLE USER( id text, -- the id of the user nametext-- the name of the user ); gives me a syntax error with sqlite 3.7.6.3? Thanks. -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite-Wal file location
On Mon, Aug 1, 2011 at 8:45 AM, wrote: > Hi, > > Thanks for the detailed explanation. > My original question was more in relation to the .db-wal file. I guess the > same discussion is applicable to the db-wal file also?. In my project, in > all probability one writer and multiple readers. Hence the chances of > corruption is greatly reduced. > One problem I have noticed is that I have to create (an empty) a file with > the same name as the database file in the tmpfs for correct working . > If the WAL file is in volatile storage and you lose power or reset, your database will likely go corrupt. > > > Sent from BlackBerry® on Airtel > > -Original Message- > From: Richard Hipp > Sender: sqlite-users-boun...@sqlite.org > Date: Mon, 1 Aug 2011 07:07:52 > To: General Discussion of SQLite Database > Reply-To: General Discussion of SQLite Database > Subject: Re: [sqlite] Sqlite-Wal file location > > On Sun, Jul 31, 2011 at 11:51 PM, Sreekumar TP >wrote: > > > Hello, > > > > Inorder to suit the needs of my embedded device, I have changed the > > location > > of the .db-wal file from the location of the db file to tmpfs. Does > sqlite > > make assumptions(persistence etc) based on the location of the file ? > > > > An early prototype of WAL did exactly what you describe. But we then > encountered database corruption during testing when two separate processes > tried to access the same database file where one of the processes was in a > chroot jail and the other was not. Both processes used the same name for > the -shm file, but because of the chroot jail, they in fact used two > separate -shm files. Fossil uses SQLite in WAL mode and it often runs in a > chroot jail, so this is not an uncommon scenario. > > The only way we have found to ensure that all processes use the same -shm > file is to put the -shm file in the same directory as the database file. > > For an embedded project, you can put the -shm file wherever you want as > long > as all processes agree to use the same file. If you mess up, and two or > more processes use different -shm files for the same database, database > corruption will result. You have been warned. > > Note that -shm does not have to be a file. We just need an area of shared > memory common to all processes accessing the database. On unix and > windows, > we found this most convenient to implement as a file using mmap(). But if > you have some other mechanism on your embedded device that would work > better, you are welcomed to use that. > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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 > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite-Wal file location
Hi, Thanks for the detailed explanation. My original question was more in relation to the .db-wal file. I guess the same discussion is applicable to the db-wal file also?. In my project, in all probability one writer and multiple readers. Hence the chances of corruption is greatly reduced. One problem I have noticed is that I have to create (an empty) a file with the same name as the database file in the tmpfs for correct working . Sent from BlackBerry® on Airtel -Original Message- From: Richard Hipp Sender: sqlite-users-boun...@sqlite.org Date: Mon, 1 Aug 2011 07:07:52 To: General Discussion of SQLite Database Reply-To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite-Wal file location On Sun, Jul 31, 2011 at 11:51 PM, Sreekumar TP wrote: > Hello, > > Inorder to suit the needs of my embedded device, I have changed the > location > of the .db-wal file from the location of the db file to tmpfs. Does sqlite > make assumptions(persistence etc) based on the location of the file ? > An early prototype of WAL did exactly what you describe. But we then encountered database corruption during testing when two separate processes tried to access the same database file where one of the processes was in a chroot jail and the other was not. Both processes used the same name for the -shm file, but because of the chroot jail, they in fact used two separate -shm files. Fossil uses SQLite in WAL mode and it often runs in a chroot jail, so this is not an uncommon scenario. The only way we have found to ensure that all processes use the same -shm file is to put the -shm file in the same directory as the database file. For an embedded project, you can put the -shm file wherever you want as long as all processes agree to use the same file. If you mess up, and two or more processes use different -shm files for the same database, database corruption will result. You have been warned. Note that -shm does not have to be a file. We just need an area of shared memory common to all processes accessing the database. On unix and windows, we found this most convenient to implement as a file using mmap(). But if you have some other mechanism on your embedded device that would work better, you are welcomed to use that. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ 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] Will SQLite supports UnQL?
This is a side-question to this thread...but has anybody every done row-level locking for edit? I can see it: create table t(id int primary key,stuff text, lock l); insert into t values(1,'stuff1',0); select * from t where id=1 and lock=0; // or drop lock to get all and check lock!=0 to make "row is locked" message 1|stuff1|0 update t set lock=1 where id=1 and lock=0; // lock value would actually be the process id of your program, or in the case of multiple-machines add the machine name plus process id to guarantee uniqueness. select * from t where id=1 and lock=0; (no results -- so 2nd query returns nothing or drop lock=0 from query and check results to make "row is locked" message update t set stuff='stuff1a',lock=0 where id=1 and lock=1; Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Roger Binns [rog...@rogerbinns.com] Sent: Sunday, July 31, 2011 10:13 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Will SQLite supports UnQL? -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Any database system can be nonsensical. If you have multiple people editing prices for the same item even with a single instance database you can have the wrong final answer. But that is a procedural and business issue, not a technological one. Using a less highly charged example, how about a photos database? Using multi-master it is possible on one instance for someone to edit a photo's description while someone else on a different instance sets the location. Your conflict resolution can simply merge both changes as the fields are independent of each other. On the other hand someone setting the longitude on one system and latitude on the other is likely a genuine conflict that cannot be automatically resolved since the fields are dependent on each other. If you also wanted a view count then you would not have a single field labelled "view count" since there is no way to have a sane value with multi-master. Instead what you do is record the views themselves. This could be tracking every view in a log, or each server instance could use a key derived from its name. When you want to display the actual view count the value would have to be calculated from the pieces, and map/reduce is frequently used to do this. These kinds of databases are atomic but not transactional. Instead of recording final values as you do with transactional SQL, you instead record the transaction events themselves and calculate the final values from those. http://guide.couchdb.org/draft/recipes.html#banking http://stackoverflow.com/questions/299723/can-i-do-transactions-and-locks-in-couchdb Going back to the prices issue in this thread, the final price is made up of multiple parts (component prices, markup, taxes, profit margins, shipping and handling, discounting, competitive pressures etc). Those can be recorded and edited separately in a multi-master environment without conflicts. You do not have to use the "NoSQL" databases for everything and even the authors of the systems would tell you to use the regular databases we have decades of experience and best practises with for financial transactions. But there are also places where they are significantly more productive. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk41cQ8ACgkQmOOfHg372QTQuQCg4mmFFen351KbJH4elTU5NdTj uv0An2bJhP7GgExdp25uAesxiSbpEKsI =Co8y -END PGP SIGNATURE- ___ 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] AUTO: Alessandro Azzolini is out of the office (returning 22/08/2011)
I am out of the office until 22/08/2011. Note: This is an automated response to your message "Re: [sqlite] How to convert a database with FTS2 to FTS3/4?" sent on 01/08/2011 4.32.58. This is the only notification you will receive while this person is away. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite-Wal file location
On Sun, Jul 31, 2011 at 11:51 PM, Sreekumar TP wrote: > Hello, > > Inorder to suit the needs of my embedded device, I have changed the > location > of the .db-wal file from the location of the db file to tmpfs. Does sqlite > make assumptions(persistence etc) based on the location of the file ? > An early prototype of WAL did exactly what you describe. But we then encountered database corruption during testing when two separate processes tried to access the same database file where one of the processes was in a chroot jail and the other was not. Both processes used the same name for the -shm file, but because of the chroot jail, they in fact used two separate -shm files. Fossil uses SQLite in WAL mode and it often runs in a chroot jail, so this is not an uncommon scenario. The only way we have found to ensure that all processes use the same -shm file is to put the -shm file in the same directory as the database file. For an embedded project, you can put the -shm file wherever you want as long as all processes agree to use the same file. If you mess up, and two or more processes use different -shm files for the same database, database corruption will result. You have been warned. Note that -shm does not have to be a file. We just need an area of shared memory common to all processes accessing the database. On unix and windows, we found this most convenient to implement as a file using mmap(). But if you have some other mechanism on your embedded device that would work better, you are welcomed to use that. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_OMIT_UTF16
Where would I use the SQLITE_OMIT_* flags? On Sun, Jul 31, 2011 at 8:06 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 07/31/2011 09:14 AM, Baruch Burstein wrote: > > I can use Linux if it makes it easier, > > This is how I build which also works with grabbing the current development > snapshots. After extracting the relevant archive: > > make TOP=. -f Makefile.linux-gcc sqlite3.c && cp src/sqlite3ext.h . > > The resulting sqlite3.c is the amalgamation. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.11 (GNU/Linux) > > iEYEARECAAYFAk41i4wACgkQmOOfHg372QTCvACfQMdmjYwCc/9lLgR6l33gxCf2 > aQYAniU6LZqQDE+WQA0J84KT6mlB5EMF > =8ZW/ > -END PGP SIGNATURE- > ___ > 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
