Re: [sqlite] WAL in SQLite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/29/2010 07:01 PM, Richard Hipp wrote: >> Good to know. Would moving to shm_open() help? The problem is that unless the networked filesystem provides exclusive locks that can be retained by one client only then there will always be race conditions in that client's view of the underlying file and the contents on the server. (And if the client's filesystem cache is segmented by user then only one segment will be able to hold the lock.) The API (mmap/shm_open) won't make any difference as this is all a property of the user space API the client application is using, the file system/vmm implementation in the client kernel, the network file system implementation in the client and what requests/semantics are available in the network protocol. One weak link in the chain and race conditions are possible. Windows and CIFS/SMB does have the exclusive locking while NFS does not. If the backend is Samba then Samba can't prevent concurrent access from Unix apps on the server and the CIFS clients. Basically if the shared memory file is networked then there will be hard to diagnose corruption problems. Note that shm_open (on Linux anyway) makes a file in /dev/shm (typically tmpfs) which means it is always on the local machine. I'd just go with saying that there are absolutely zero circumstances under which SQLite is supported using a networked filesystem, that there is no way to reliably diagnose when it is being misused that way, and that there will be corruption that will be hard to detect as it happens, and can't be fixed later. For people who must use a network, the solution is a networked database as they can reliably ensure data integrity. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwCAFcACgkQmOOfHg372QSvWQCgqTA5U0slEKLkSLjb/vb+dXTR 7dYAoM6Z5C22TNDSMhu6REanOebEfp9S =D1kf -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite turns 10 years old
Happy birthday and thanks for the memories. This has been a damn nice project. On Sat, May 29, 2010 at 6:57 AM, D. Richard Hipp wrote: > The first code check-in for SQLite occurred on 2000-05-29 14:26 UTC - > ten years ago today. > > http://www.sqlite.org/src/timeline?c=2000-05-29+14:26 > > Some of the code in SQLite (such as the Lemon parser generator and the > printf implementation) dates back to the late 1980s. But the core of > SQLite was not started until 10 years ago. Ten years is not that long > ago, though it has been long enough to amass 7114 check-ins - an > average of 2.1 check-ins per day. If you are overseeing such a > project, 10 years seems like forever. It has hard for me to remember > a time when I wasn't working on SQLite. > > In celebration of SQlite's 10th birthday, we are revamping the look of > the SQLite website. You can see a preview of the new look at > > http://www.sqlite.org/draft/index.html > > We won't push the new look out to the main website until we do the > next release which might not be until July or maybe even August. We > had hoped to have SQLite version 3.7.0 ready in time for the 10th > birthday celebration, but http://www.sqlite.org/draft/wal.html is > taking longer than planned. We want to make sure to get things right > so that SQLite lives to see its 20th and 30th birthdays! > > Thanks, everybody, for helping to make SQLite the most widely deployed > SQL database engine in the world. And Happy 10th Birthday to SQLite! > > D. Richard Hipp > d...@sqlite.org > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Jim "Jed" Dodgen j...@dodgen.us ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Insert Using Tcl Array?
If SQLite can return to me a Tcl array from SELECT, why does the feature not exist to INSERT, UPDATE, or DELETE using a properly formed Tcl array as an argument? Seems nice to have, but perhaps I'm not aware of possible dangers in such a feature. Thanks. -- Ross ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL in SQLite
On Sat, May 29, 2010 at 9:19 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 05/29/2010 02:01 PM, Richard Hipp wrote: > > Does anybody know if mmap() works for network files? > > It does. However there is no way to ensure that everyone seeing the file > will have a consistent view of it. This is especially the case when they > are on different machines. > > However there can even be consistency issues when all client processes are > on the same machine. This is because the client operating system should be > segmenting its cache by user - ie if my user and your user map the same > network file they should not share the client cache - as that is wonderful > way of doing cache poisoning attacks. > Good to know. Would moving to shm_open() help? I haven't do that yet because (I read) shm_open() requires -lrt when you link and I like keeping the number of dependencies to a minimum. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.10 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > iEYEARECAAYFAkwBvSkACgkQmOOfHg372QT4NwCgnxbBlQZVisfxJWqy+yPnvzon > gmAAnj8LnoZq4wIyYO3AcZTxbEDQP/Z3 > =uKqk > -END PGP SIGNATURE- > ___ > 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] WAL in SQLite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/29/2010 02:01 PM, Richard Hipp wrote: > Does anybody know if mmap() works for network files? It does. However there is no way to ensure that everyone seeing the file will have a consistent view of it. This is especially the case when they are on different machines. However there can even be consistency issues when all client processes are on the same machine. This is because the client operating system should be segmenting its cache by user - ie if my user and your user map the same network file they should not share the client cache - as that is wonderful way of doing cache poisoning attacks. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwBvSkACgkQmOOfHg372QT4NwCgnxbBlQZVisfxJWqy+yPnvzon gmAAnj8LnoZq4wIyYO3AcZTxbEDQP/Z3 =uKqk -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL in SQLite
Richard, thank you for responding to my questions about SQLite's WAL design. Richard Hipp wrote: > We've been working on an incremental checkpointing mechanism for about a > week. With incremental checkpointing, the checkpoint can be run more or > less continuously in a background thread or process. Writers never block > readers, readers never block writers, and checkpointers never block > anybody. There can still only be one writer at a time, though, so writers > still block other writers. I look forward to this, which should be a considerable improvement that shouldn't really increase complexity. I'm not going to advocate trying to support multiple concurrent writers, which is probably where most of any complexity would lie, or not any more so than SQLite currently does. Now, another item, which I forgot to state earlier ... 4. Quoth the raven: "3. Transactions that involve changes against multiple ATTACHed databases are atomic for each individual database, but are not atomic across all databases as a set." I greatly hope that this limitation could go away. I consider that SQLite's ability to make multiple databases subject to a common transaction is very powerful, and I would even argue, essential. I hope that some variation of the method used now with rollback journals can be applied to databases with WALs, so that the latter can take part in cross-database transactions. I don't see anything in the WAL design that this couldn't be done without much complexity. Thank you in advance. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL in SQLite
On Sat, May 29, 2010 at 3:49 PM, Darren Duncan wrote: > > 2. Quoth the raven: > > "The wal-index greatly improves the performance of readers, but the use > of > shared memory means that all readers must exist on the same machine. This > is > why the write-ahead log implementation will not work on a network > filesystem." > > I don't understand the need to sacrifice WAL on a network filesystem. My > naive > interpretation of the situation is that WAL itself would work on a network > filesystem, but just that processes using it that way would have slower > performance as they can't use the wal-index. > Correct. All clients that have the database open must be on the same SMP. The easiest way to enforce that is to just say "no network files". Note that the current implementation uses mmap() on an ordinary file to get shared memory. We may well change that to use shm_open() before the release. Does anybody know if mmap() works for network files? > 3. Quoth the raven: ... > > I don't understand the need for checkpointing to be an all-or-none affair. > We've been working on an incremental checkpointing mechanism for about a week. With incremental checkpointing, the checkpoint can be run more or less continuously in a background thread or process. Writers never block readers, readers never block writers, and checkpointers never block anybody. There can still only be one writer at a time, though, so writers still block other writers. And during recovery from a crash, everybody is blocked, but that's an unusual case. You can arrange for a fiendish sequence of overlapping reads and writes that prevent the checkpointer from making any progress and hence cause the WAL to grow without bound. But we do not anticipate that being a problem in the common case. If you set synchronous=NORMAL, then the checkpoint thread/process is the only thread/process that ever calls fsync() so you can do queries and updates in a GUI thread with much less risk of freezing the system due to a slow disk. You pay for this by giving up durability following a power-loss/hard-reset. Please note that SQLite databases with WAL will continue to feature fully automatic recovery from power-loss or hard-reset; applications do not have to do anything special to recover from a crash. Also, any client (reader, writer, or checkpointer) can take a SIGKILL at any instant and the system will automatically recover and continue operating. Code for the above will appear in the source tree soon, I hope - initially in a branch and then on the trunk as it stabilizes. -- - 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 turns 10 years old
> Thanks, everybody, for helping to make SQLite the most widely deployed > SQL database engine in the world. And Happy 10th Birthday to SQLite! No, *WE* have to thank *YOU* for this great piece of work, that you give to use for free! Amazing! Thank you! bye, Michael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL in SQLite
In regards to WAL (write-ahead-logging) support being added to SQLite for version 3.7.0, I have a few comments, questions, suggestions, etc. Most of them refer to parts of http://www.sqlite.org/draft/wal.html . 1. Congratulations on this move, which should greatly improve SQLite's utility relative to 3.6.x. I greatly look forward to the multi-versioning capabilities that this would provide. 2. Quoth the raven: "The wal-index greatly improves the performance of readers, but the use of shared memory means that all readers must exist on the same machine. This is why the write-ahead log implementation will not work on a network filesystem." I don't understand the need to sacrifice WAL on a network filesystem. My naive interpretation of the situation is that WAL itself would work on a network filesystem, but just that processes using it that way would have slower performance as they can't use the wal-index. I would think it better that WAL *is* supported on the network but with the caveat that its performance would be slower than use by a process on the same machine. Then even network users can still get the multi-versioning/etc benefits but just slower read performance. And concurrent processes on the same machine as the database would still use the wal-index at the same time. Is there more to this that I'm not seeing, or could SQLite's WAL be altered as I suggest above so that network users are supported but just more slowly? 3. Quoth the raven: "A checkpoint operation takes all the content from the WAL file and transfers it back into the original database file. As long as all readers are looking at the entire WAL file (in other words, as long as no reader is using an end mark that is earlier than the actual end of the WAL) then it will be OK for the checkpoint to run in parallel with the readers. [] Thus, a checkpoint will never block a reader and readers will not block a checkpoint as long as all readers are looking at the entire WAL. But, older readers that only see a prefix of the WAL will block a checkpoint. []" I don't understand the need for checkpointing to be an all-or-none affair. If not all readers are looking at the entire WAL file, then why can't just a subset of the WAL be checkpointed, that subset corresponding to what the reader sees that started the earliest so that then the WAL only has the corresponding subset from the earliest reader to the latest? -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite turns 10 years old
Congratulations on this milestone. I also just realized now that you're adding WAL to SQLite; I have more to say on this, but that will be in a new thread. -- Darren Duncan D. Richard Hipp wrote: > The first code check-in for SQLite occurred on 2000-05-29 14:26 UTC - > ten years ago today. > > http://www.sqlite.org/src/timeline?c=2000-05-29+14:26 > > Some of the code in SQLite (such as the Lemon parser generator and the > printf implementation) dates back to the late 1980s. But the core of > SQLite was not started until 10 years ago. Ten years is not that long > ago, though it has been long enough to amass 7114 check-ins - an > average of 2.1 check-ins per day. If you are overseeing such a > project, 10 years seems like forever. It has hard for me to remember > a time when I wasn't working on SQLite. > > In celebration of SQlite's 10th birthday, we are revamping the look of > the SQLite website. You can see a preview of the new look at > > http://www.sqlite.org/draft/index.html > > We won't push the new look out to the main website until we do the > next release which might not be until July or maybe even August. We > had hoped to have SQLite version 3.7.0 ready in time for the 10th > birthday celebration, but http://www.sqlite.org/draft/wal.html is > taking longer than planned. We want to make sure to get things right > so that SQLite lives to see its 20th and 30th birthdays! > > Thanks, everybody, for helping to make SQLite the most widely deployed > SQL database engine in the world. And Happy 10th Birthday to SQLite! > > 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] UTC/GMT conversion back and forth
On Fri, 28 May 2010 20:12:54 +0300 Andrejs Igumenovs wrote: Finally! A question that I can answer. For all practical (and most impractical) (Drum roll) UTC = GMT, or alternatively, GMT = UTC UTC is Coordinated Universal Time, which one would think would be abbreviated CUT, but one French-speaking country got its two cents in there, so we know it as UTC. -- __ 3.14159265358979323846264338327950 Let the spirit of pi 2884197169399375105820974944592307 spread all over the world! 8164062862089986280348253421170679 http://pi314.at PI VOBISCUM! == ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TEA not latest version?
Congratulations on the 10th birthday. On: http://www.sqlite.org/download.html The TEA download link is version 3_6_23, whereas everything else is 3.6.23.1. >From what I understand from the release notes, the changes from 3.6.23 to 3.6.23.1 affect the TEA version too. How do I get to the 3.6.23.1 TEA source? Or do I have to build it from the fossil sources? - Venkat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite turns 10 years old
Mr. D. Richard Hipp, 10-year anniversary a great news and real pleasure to see how your and supporters' skills turn this library into such big power. Sometimes it makes me sad I can't explain to a non-developer what is so great about sqlite, what makes it appearing in so many software packages. If I had power, I'd be glad to calculate and see how many sqlite-related reads and writes are taking place on the whole earth. I'm sure it's a big number :) Thank you for sqlite! Max Vlasov On Sat, May 29, 2010 at 5:57 PM, D. Richard Hipp wrote: > > Thanks, everybody, for helping to make SQLite the most widely deployed > SQL database engine in the world. And Happy 10th Birthday to SQLite! > > 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_WSD (3.6.23.1)
On May 29, 2010, at 10:19 AM, Albert Kim wrote: > > Hi Dan, > It doesn't matter that it will never be written to. Since the > variable is a > non-const static it will get mapped into the WSD portion of memory. Is a problem in practice? Or just messy? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite turns 10 years old
The first code check-in for SQLite occurred on 2000-05-29 14:26 UTC - ten years ago today. http://www.sqlite.org/src/timeline?c=2000-05-29+14:26 Some of the code in SQLite (such as the Lemon parser generator and the printf implementation) dates back to the late 1980s. But the core of SQLite was not started until 10 years ago. Ten years is not that long ago, though it has been long enough to amass 7114 check-ins - an average of 2.1 check-ins per day. If you are overseeing such a project, 10 years seems like forever. It has hard for me to remember a time when I wasn't working on SQLite. In celebration of SQlite's 10th birthday, we are revamping the look of the SQLite website. You can see a preview of the new look at http://www.sqlite.org/draft/index.html We won't push the new look out to the main website until we do the next release which might not be until July or maybe even August. We had hoped to have SQLite version 3.7.0 ready in time for the 10th birthday celebration, but http://www.sqlite.org/draft/wal.html is taking longer than planned. We want to make sure to get things right so that SQLite lives to see its 20th and 30th birthdays! Thanks, everybody, for helping to make SQLite the most widely deployed SQL database engine in the world. And Happy 10th Birthday to SQLite! 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] Getting a "rowid" from a view
On Fri, 28 May 2010 17:19:37 -0700, Jim Terman wrote: >SELECT rowid FROM phonebook > where last_name = "Smith" > and first_name = "John"; By the way, string literals (text) in SQL should be delimited by single quotes, not double quotes. Double quotes are used to allow reserved words as identifiers (table names, column names and the like). -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a "rowid" from a view
On Sat, 29 May 2010 02:35:22 +0100, Simon Slavin wrote: >On 29 May 2010, at 2:30am, Jim Terman wrote: > >> I want the 'rowid' of the view. In other words I'd like to now what row >> John Smith is in the view. I can do it with a view that is ordered by >> using count(*), but I wondered if there was a better way. > I believe that a view is just a window into an existing table: > a way of saving the terms of a 'SELECT' statement so you > don't have to keep restating it. Therefore it has no data > of its own, including no rowids of its own. That's correct. > Try doing 'SELECT rowid FROM phonebook_order' and see > whether you get any result at all, and whether the numbers are in order. I like the way you invite people to experiment themselves. It's the best way to acquire knowledge. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a "rowid" from a view
On Fri, 28 May 2010 17:19:37 -0700, Jim Terman
wrote:
>Say I have a table of phone numbers
>
>CREATE TABLE phonebook (first_name TEXT, last_name TEXT, phone_number TEXT);
>
>I want to sort this by name, so I create a view
>
>CREATE VIEW phonebook_order AS SELECT first_name, last_name,
>phone_number FROM phonebook ORDER BY last_name, first_name;
>
>Now on the table phonebook I can do a query:
>
>SELECT rowid FROM phonebook where last_name = "Smith" and first_name =
>"John";
>
>which will gave me the row number of John Smith.
>
>How do I do this for the view phonebook_order?
>
>Nearest I can determine would be to run the command
>
>SELECT COUNT(*) from phonebook_order WHERE last_name <= "Smith" AND
>first_name <= "John";
The query you propose:
SELECT COUNT(*) FROM phonebook_order
WHERE last_name <= "Smith"
AND first_name <= "John";
will not reliably return the row number of John Smith.
Run this example:
CREATE TABLE phonebook (
first_name TEXT,
last_name TEXT,
phone_number TEXT
);
CREATE VIEW phonebook_order AS
SELECT first_name, last_name, phone_number
FROM phonebook
ORDER BY last_name, first_name;
INSERT INTO phonebook (first_name, last_name, phone_number)
VALUES ('Jim','Terman','101004');
INSERT INTO phonebook (first_name, last_name, phone_number)
VALUES ('John','Brown','102001');
INSERT INTO phonebook (first_name, last_name, phone_number)
VALUES ('John','Smith','103003');
INSERT INTO phonebook (first_name, last_name, phone_number)
VALUES ('Simon','Slavin','104002');
.headers on
SELECT rowid
FROM phonebook
WHERE last_name = 'Smith'
AND first_name = 'John';
SELECT * FROM phonebook_order;
SELECT COUNT(*) AS roworder FROM phonebook_order
WHERE last_name <= 'Smith'
AND first_name <= 'John';
>Is there an easier way?
Use a counter in your host language.
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UTC/GMT conversion back and forth
sqlite> create table t (d integer);
sqlite> insert into t values (datetime('now'));
sqlite> select * from t;
2010-05-29 11:19:20 << you'll note this should be in GMT
sqlite> select datetime(d,'localtime') from t;
2010-05-29 06:19:20
You can, of course, get your time in whatever format you want.
http://www.sqlite.org/lang_datefunc.html
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of Andrejs Igumenovs
Sent: Fri 5/28/2010 12:12 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] UTC/GMT conversion back and forth
Hi,
I've got dates and times stored in GMT.
When I perform my calculations using date() function for ex., i need
to use those dates and times normalized to the current location I'm
now in (this is variable).
This thing makes me lost a bit of how to implement.. Any ideas ?
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] UTC/GMT conversion back and forth
Hi, I've got dates and times stored in GMT. When I perform my calculations using date() function for ex., i need to use those dates and times normalized to the current location I'm now in (this is variable). This thing makes me lost a bit of how to implement.. Any ideas ? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No conflict clause in foreign key clause?
On May 28, 2010, at 10:54 PM, Robert Nickel wrote: > I notice that the foreign key clause > (http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause) does > not > include a conflict clause > (http://www.sqlite.org/syntaxdiagrams.html#conflict-clause). I always > specify "ON CONFLICT ROLLBACK" with my other constraints, but what > will > happen when a foreign key violation occurs within a transaction? > Will we > see the default "ABORT" behavior as with other constraints? Yes. A failed immediate FK constraint is like an ABORT (statement rollback only). > How do I get > it to rollback the entire transaction in such a case? Can't be done. You'll have to use triggers instead of foreign keys if you need this. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
