[sqlite] 转发:SQLITE3 JOURNAL MODE
-- 原始邮件 -- 发件人: 2718894394;2718894...@qq.com; 发送时间: 2015年2月10日(星期二) 下午5:31 收件人: drhd...@hwaci.com; 主题: SQLITE3 JOURNAL MODE HELLO, I HAVE SOME QUESTIONS ABOUT USING SQLITE3 '8.8.2' WHERE A POWER LOSS OR SIGNAL 'KILL -9' HAPPEND IN LINUX SYSTEM,HOW CAN I MAKE SURE THE DB FILE HAVE THE fewest LOSS! At the same time, THE kernel CPU period IN 30MS~50MS OR LESS. I HAVE TRIED TO USE WAL JOURNAL MODE, IN THIS MODE ,when POWER DOWN OR RESET, THE DB DATA IS OK ? THANKS ! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Repost: Having problems with Entity Framework code first db creation
We have not done this in the past. We are trying to create a SQLite database to use as a project file for software we're developing. The test project I linked to in the initial email is one we were able to first make work with MS SQL Compact, but it has limitations that we want to avoid. The test project steps are: 1) Create a new console app in VS2013 2) Manage NuGet packages and add the SQLite packages (which also install Entity Framework) 3) Copy in the schema class and main function (found in the link) 4) Run the program Walter Williams Senior Software Engineer Sawtooth Software, Inc. Do, or do not. There is no try. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Monday, February 09, 2015 4:01 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Repost: Having problems with Entity Framework code first db creation Walter Williams wrote: Nothing changed after installing it. What steps in the IDE are you taking to get to that point? Have these steps worked in the past? I'm asking these questions because I'm not completely sure that the System.Data.SQLite provider supports the various code first scenarios with the Entity Framework. Some errors we have seen in the past are: https://system.data.sqlite.org/index.html/tktview/f37ca75 and https://system.data.sqlite.org/index.html/tktview/6b5ef4f -- Joe Mistachkin ___ 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] 转发:SQLITE3 JOURNAL MODE
On 11 Feb 2015, at 2:59am, Keith Medcalf kmedc...@dessus.com wrote: 1) Keep it on the boot volume, not a network or external drive. The following things will make your journal less safe: 1) PRAGMA data_store_directory to anywhere other than the boot volume I presume you mean local filesystem not boot volume. Both are true. My logic is that if the boot volume stops working (or somehow gets unmounted) then the computer is going to stop working anyway. Whereas another volume on the same disk might somehow get dismounted and that wouldn't stop the computer from working. But I suspect that the OP doesn't care about this. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 转发:SQLITE3 JOURNAL MODE
1) Keep it on the boot volume, not a network or external drive. The following things will make your journal less safe: 1) PRAGMA data_store_directory to anywhere other than the boot volume I presume you mean local filesystem not boot volume. The problem is that remote filesystems (aka network filesystems) usually do not support locking semantics properly -- but most local filesystems do. So for example putting the database on a local NTFS or extx volume is fine, as is putting it on a local filesystem where the underlying block storage is remote (whether a few inches by the data cable or ten thousand miles by fibre) is also fine. Using a Network File System (NFS, CIFS, AFS, etc) is bad. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Tuesday, 10 February, 2015 08:41 To: General Discussion of SQLite Database Subject: Re: [sqlite] 转发:SQLITE3 JOURNAL MODE On 10 Feb 2015, at 9:32am, 博协软件-张涛 2718894...@qq.com wrote: WHERE A POWER LOSS OR SIGNAL 'KILL -9' HAPPEND IN LINUX SYSTEM,HOW CAN I MAKE SURE THE DB FILE HAVE THE fewest LOSS! At the same time, THE kernel CPU period IN 30MS~50MS OR LESS. I HAVE TRIED TO USE WAL JOURNAL MODE, IN THIS MODE ,when POWER DOWN OR RESET, THE DB DATA IS OK ? WAL mode is no more safe or less safe than the original journal made. To make your database more safe 1) Keep it on the boot volume, not a network or external drive. 2) Set a long busy_timeout, perhaps several minutes. (see PRAGMA busy_timeout) 3) PRAGMA synchronous = FULL The following things will make your journal less safe: 1) PRAGMA data_store_directory to anywhere other than the boot volume 2) PRAGMA ignore_check_constraints = YES 3) PRAGMA journal_mode = MEMORY 4) PRAGMA journal_mode = OFF 5) PRAGMA synchronous = OFF 6) PRAGMA temp_store_directory to anywhere other than the boot volume Do not do any of those things if you want your database to be as safe as possible. 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] Porting SQLite to another operating system (not supported out of the box)
On Feb 10, 2015, at 11:21 AM, Peter Aronson pbaron...@att.net wrote: You could add VFS creation if you ever do a revised edition (along with a virtual table example that actually used xBestIndex and xFilter…) Given that the book is over four years old and covers to the end of SQLIte3 3.6.x, there are a lot of things that would need to go into a revised edition… including a lot more examples of everything, according to reviews. We greatly underestimated the number of SQLite developers that were touching SQL for the first time, and I would have never guessed people would have considered yet another SQL lesson to be so important, given that there are a million books and a bazillion websites on learning SQL basics. You can literally find books on “SQL For Dummies” (Allen Taylor) to “SQL For Smarties” (Joe Celko), and everything in-between. That last book (or books, actually) is awesome, BTW, and the “Advanced SQL Programming” one should be on the shelf of every database programmer doing anything more advanced than an address book. Regardless, if we do a second edition (and at this point that’s an extremely, exceptionally big “if”), VFS is unlikely to make the cut. Consider that out of the thousands of SQLite applications and billions of installed databases, there are likely less than 100 production VFS modules in the whole world. Spending a lot of time and pages, driving up the cost of the book, covering an extremely advanced and obscure topic is a poor trade-off (every page averages about a day to write/edit/prep, and adds about $0.10 to the price of the book). If you need that level of integration and detail, working in the guts of the I/O and locking system, you should likely hand the task to a systems engineer that is familiar with the problem domain and isn’t afraid of looking through a few headers and examples to figure it all out. It’s advanced, custom stuff that is, almost by definition, not textbook work. It is the kind of work that requires digging through nitty-gritty code, documentation, and examples from both SQLite and your environment. This is the kind of thing that’s learned from years of experience, not by reading it in a book. That isn’t meant to be a criticism of the original poster— there is a huge difference between asking if anyone knows where to start looking, and asking for detailed step-by-step instructions. In fact, if we did decide to put some information about VFS modules in a book, it would likely be a discussion of how the structures and APIs fit together, what they’re used for, and the types of things that can be done with them— exactly the kind of info you need to get started, but not much beyond that. After all, what goes in those functions is going to be extremely dependent on the environment the VFS is trying to use. I might say similar things about the xBestIndex() and xFilter() functions. While the APIs and how they are used is a tad confusing, their purpose and function should be reasonably straight forward to someone comfortable with relational data management and design. While the book attempts to cover how the APIs are meant to perform their tasks (and has a six page discussion on their purpose and use), actually writing such a function is extremely dependent on understanding the virtual table being design— and the data in it. I feel it is something that just needs to be done by a skilled engineer, with a lot of detailed knowledge about the problem that’s trying to be solved. Again, there aren’t any real textbook examples here; yes, I could write a contrived example, but if they didn’t understand from a general description, a single specific example is unlikely to help anyone in their specific case. At the end of the day, both functions are an optimizations anyways. You can write a functional virtual table without them, it might just run a tad slower. If you really need that last bit of performance, you need an engineering that knows how to get detailed work done, and can verify the correctness of the system when they’re done— even if it involves a little of banging heads against walls, and cursing at the debugger. That’s where the magic happens anyways. -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] Porting SQLite to another operating system (not supported out of the box)
Firma: Capgemini Deutschland GmbH Aufsichtsratsvorsitzender: Antonio Schnieder • Geschäftsführer: Dr. Michael Schulte (Sprecher) • Jost Förster • Dr. Peter Lempp • Dr. Volkmar Varnhagen Amtsgericht Berlin-Charlottenburg, HRB 98814 This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)
On Tue, Feb 10, 2015 at 5:27 PM, Janke, Julian julian.ja...@capgemini.com wrote: So my question is, if there is any additional information, how to write a VFS? Or does anyone have a clue how to start best? This book: http://www.amazon.de/Using-SQLite-Jay-Kreibich-ebook/dp/B008IGK5QM/ resp. http://shop.oreilly.com/product/9780596521196.do covers VFS creation in detail with a step-by-step example. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Porting SQLite to another operating system (not supported out of the box)
Hello, we plan to port the SQLite library on an embedded system, which runs on a platform other than the ones that are supported out of the box (it’s an SMX based system). For the first step, we don’t need a working mutex subsystem, because we have no multithreaded environment. The required standard c library functions memset(), memcpy(), memcmp(), strcmp(), malloc(), free() and realloc() are available. In my opinion, this means, we must only write a VFS implementation for our target platform. So my question is, if there is any additional information, how to write a VFS? Or does anyone have a clue how to start best? I know there are two implementations for unix and windows systems in os_unix.c and os_win.c, but these are very large files, which are not very helpful for a first rough understanding. Regards, Julian Firma: Capgemini Deutschland GmbH Aufsichtsratsvorsitzender: Antonio Schnieder • Geschäftsführer: Dr. Michael Schulte (Sprecher) • Jost Förster • Dr. Peter Lempp • Dr. Volkmar Varnhagen Amtsgericht Berlin-Charlottenburg, HRB 98814 This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)
On 2/10/15, Janke, Julian julian.ja...@capgemini.com wrote: I know there are two implementations for unix and windows systems in os_unix.c and os_win.c, but these are very large files, which are not very helpful for a first rough understanding. See also: https://www.sqlite.org/src/artifact/0396f220561f -- 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] Porting SQLite to another operating system (not supported out of the box)
Janke, Julian wrote: In my opinion, this means, we must only write a VFS implementation for our target platform. What file API is there? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)
No, it does not. Using SQLite covers Virtual Tables in great detail, but not VFS systems. They’re somewhat unusual, after all. I assume you’ve read the VFS docs: http://www.sqlite.org/vfs.html From there, my suggestion is to look closely at the code for the VFS structure (http://www.sqlite.org/c3ref/vfs.html) and the IO Methods structure (http://www.sqlite.org/c3ref/io_methods.html). Both those structs contain a bunch of function pointers for which you need to provide code. -j On Feb 10, 2015, at 10:31 AM, Stephan Beal sgb...@googlemail.com wrote: On Tue, Feb 10, 2015 at 5:27 PM, Janke, Julian julian.ja...@capgemini.com wrote: So my question is, if there is any additional information, how to write a VFS? Or does anyone have a clue how to start best? This book: http://www.amazon.de/Using-SQLite-Jay-Kreibich-ebook/dp/B008IGK5QM/ resp. http://shop.oreilly.com/product/9780596521196.do covers VFS creation in detail with a step-by-step example. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- 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
Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)
On Tue, Feb 10, 2015 at 5:58 PM, Jay Kreibich j...@kreibi.ch wrote: No, it does not. Using SQLite covers Virtual Tables in great detail, but not VFS systems. They’re somewhat unusual, after all. My apologies - i mixed my terminology there! -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)
You could add VFS creation if you ever do a revised edition (along with a virtual table example that actually used xBestIndex and xFilter...). On Tuesday, February 10, 2015 9:58 AM, Jay Kreibich j...@kreibi.ch wrote: No, it does not. Using SQLite covers Virtual Tables in great detail, but not VFS systems. They’re somewhat unusual, after all. I assume you’ve read the VFS docs: http://www.sqlite.org/vfs.html From there, my suggestion is to look closely at the code for the VFS structure (http://www.sqlite.org/c3ref/vfs.html) and the IO Methods structure (http://www.sqlite.org/c3ref/io_methods.html). Both those structs contain a bunch of function pointers for which you need to provide code. -j On Feb 10, 2015, at 10:31 AM, Stephan Beal sgb...@googlemail.com wrote: On Tue, Feb 10, 2015 at 5:27 PM, Janke, Julian julian.ja...@capgemini.com wrote: So my question is, if there is any additional information, how to write a VFS? Or does anyone have a clue how to start best? This book: http://www.amazon.de/Using-SQLite-Jay-Kreibich-ebook/dp/B008IGK5QM/ resp. http://shop.oreilly.com/product/9780596521196.do covers VFS creation in detail with a step-by-step example. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)
On 10 Feb 2015, at 5:01pm, Clemens Ladisch clem...@ladisch.de wrote: Janke, Julian wrote: In my opinion, this means, we must only write a VFS implementation for our target platform. What file API is there? It looks like a standard POXIS implementation. My guess is that the best place to start would be https://www.sqlite.org/vfs.html including the examples linked in section 2.3. The OP should come back if he has more specific questions after that. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)
On 02/11/2015 12:31 AM, Simon Slavin wrote: On 10 Feb 2015, at 5:01pm, Clemens Ladisch clem...@ladisch.de wrote: Janke, Julian wrote: In my opinion, this means, we must only write a VFS implementation for our target platform. What file API is there? It looks like a standard POXIS implementation. My guess is that the best place to start would be https://www.sqlite.org/vfs.html including the examples linked in section 2.3. The OP should come back if he has more specific questions after that. A simple VFS for systems that support a basic set of posix primitives is here: http://www.sqlite.org/src/artifact/69b2085076654 The code is quite easy to follow too. Dan. 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] 转发:SQLITE3 JOURNAL MODE
On 10 Feb 2015, at 9:32am, 博协软件-张涛 2718894...@qq.com wrote: WHERE A POWER LOSS OR SIGNAL 'KILL -9' HAPPEND IN LINUX SYSTEM,HOW CAN I MAKE SURE THE DB FILE HAVE THE fewest LOSS! At the same time, THE kernel CPU period IN 30MS~50MS OR LESS. I HAVE TRIED TO USE WAL JOURNAL MODE, IN THIS MODE ,when POWER DOWN OR RESET, THE DB DATA IS OK ? WAL mode is no more safe or less safe than the original journal made. To make your database more safe 1) Keep it on the boot volume, not a network or external drive. 2) Set a long busy_timeout, perhaps several minutes. (see PRAGMA busy_timeout) 3) PRAGMA synchronous = FULL The following things will make your journal less safe: 1) PRAGMA data_store_directory to anywhere other than the boot volume 2) PRAGMA ignore_check_constraints = YES 3) PRAGMA journal_mode = MEMORY 4) PRAGMA journal_mode = OFF 5) PRAGMA synchronous = OFF 6) PRAGMA temp_store_directory to anywhere other than the boot volume Do not do any of those things if you want your database to be as safe as possible. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS5 compression
Hi, What kind of compression is used with the experimental FTS5? I¹d be interested to hear about real world comparisons in regards to compaction and performance. Ben ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users