Re: [sqlite] 转发：SQLITE3 JOURNAL MODE

[Simon Slavin]

On 11 Feb 2015, at 2:59am, Keith Medcalf  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] Porting SQLite to another operating system (not supported out of the box)

[Jay Kreibich]

On Feb 10, 2015, at 11:21 AM, Peter Aronson  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

Re: [sqlite] 转发：SQLITE3 JOURNAL MODE

[Keith Medcalf]

>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 ext 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

[sqlite] FTS5 compression

[Stadin, Benjamin]

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

Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)

[Dan Kennedy]

On 02/11/2015 12:31 AM, Simon Slavin wrote:

On 10 Feb 2015, at 5:01pm, Clemens Ladisch  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



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] Porting SQLite to another operating system (not supported out of the box)

[Richard Hipp]

On 2/10/15, Janke, Julian  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)

[Simon Slavin]

On 10 Feb 2015, at 5:01pm, Clemens Ladisch  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



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)

[Peter Aronson]

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  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  wrote:
>
>> On Tue, Feb 10, 2015 at 5:27 PM, Janke, Julian 
>> 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)

[Clemens Ladisch]

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)

[Stephan Beal]

On Tue, Feb 10, 2015 at 5:58 PM, Jay Kreibich  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)

[Jay Kreibich]

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  wrote:

> On Tue, Feb 10, 2015 at 5:27 PM, Janke, Julian 
> 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)

[Stephan Beal]

On Tue, Feb 10, 2015 at 5:27 PM, Janke, Julian 
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)

[Janke, Julian]

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

[sqlite] Porting SQLite to another operating system (not supported out of the box)

[Janke, 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] 转发：SQLITE3 JOURNAL MODE

[Simon Slavin]

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

Re: [sqlite] Repost: Having problems with Entity Framework code first db creation

[Walter Williams]

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

[sqlite] 转发：SQLITE3 JOURNAL MODE

[博协软件-张涛]

-- 原始邮件 --
发件人: "2718894394";<2718894...@qq.com>;
发送时间: 2015年2月10日(星期二) 下午5:31
收件人: "drh"; 

主题: 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