date:20140912

On Saturday, September 13, 2014 01:10:37 AM Kees Nuyt wrote:
> On Sat, 13 Sep 2014 00:55:23 +0200, Kees Nuyt 
> 
> wrote:
> > It works better on your local filesystem.
> 
> Perhaps you can configure (a partition on) the Seagate GoFlex as
> an iSCSI target and configure an iSCSI initiator on the PC ?
> A quick websearch on "seagate goflex iscsi" suggests that's
> possible.
> If so, locking should work.
> (warning: untested)

all things bing equal I will just use the NAS to store this particular db and 
as a backup for my 
development system.  The db works perfectly well on the local system (one is 
ubuntu, the 
others are Linux Mint 17) connecting to the NAS via CIFS (I'd prefer NFS, but 
seagate/govlex doesn't support that).

Thanks for the tips people.

Mark

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] presentation about ordering and atomicity of filesystems


On 13 Sep 2014, at 1:21am, Richard Hipp  wrote:

> On Fri, Sep 12, 2014 at 8:07 PM, Simon Slavin  wrote:
> 
>> one thing that annoys me about SQLite is that it needs to make a
>> journal file which isn't part of the database file.  Why ?  Why can't it
>> just write the journal to the database file it already has open ?  This
>> would reduce the problems where the OS prevents an application from
>> creating a new file because of permissions or sandboxing.
> 
> Where in the database does the journal information get stored?

Good points, all of them.  I don't doubt that the dev team has considered all 
these things carefully and chosen the best solution for the circumstances.

> There are also performance reasons for separating the temporary tables and
> indexes.  Because temporary tables do not have to be preserved across a
> system crash, SQLite is able to take lots of short-cuts when writing
> temporary tables (for example: omitting fsync() calls) which make them run
> must faster.

I never thought of either of those points.  Just goes to show.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] presentation about ordering and atomicity of filesystems

On Fri, Sep 12, 2014 at 8:07 PM, Simon Slavin  wrote:

>
>   one thing that annoys me about SQLite is that it needs to make a
> journal file which isn't part of the database file.  Why ?  Why can't it
> just write the journal to the database file it already has open ?  This
> would reduce the problems where the OS prevents an application from
> creating a new file because of permissions or sandboxing.
>

Where in the database does the journal information get stored?  At the
end?  What happens then if the transaction is an INSERT and the size of the
content has to grow?  Does that leave a big hole in the middle of the file
when the journal is removed?  During recovery after a crash, where does the
recovery process go to look for the journal information?   If the journal
is at some arbitrary point in the file, where does it look.  Note that we
cannot write the journal location in the file header because the header
cannot be (safely) changed without first journaling it but we cannot
journal the header without first writing the journal location into the
header.

One idea that might work is to interleave the journal information with the
content.  So for each page in the database, there is a corresponding page
of journal content.  The downside there is that you double the size of the
database file without increasing its storage capacity.

>
> Similarly, temporary indexes and temporary tables (I think) also go in
> external files.  I don't see why, if they're part of 'main', they can't go
> in the main file.
>
>
Temporary tables and indexes are only suppose to be visible to the one
database connection that created them, not to all database connections.  So
they cannot be put into the main database file where they would be visible
to everybody.

There are also performance reasons for separating the temporary tables and
indexes.  Because temporary tables do not have to be preserved across a
system crash, SQLite is able to take lots of short-cuts when writing
temporary tables (for example: omitting fsync() calls) which make them run
must faster.
-- 
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] presentation about ordering and atomicity of filesystems

On 13 Sep 2014, at 12:47am, James K. Lowden  wrote:

> I asked your question: why not add transactions to FFS?  
> 
> His answer: that's the province of a database.  

A file system is a database.  It accepts data from a user.  It stores it away 
for later retrieval.  It allows it to be searched in various ways more 
convenient than just reading it from beginning to end every time.

We're just not used to file systems being very clever.  New ones like ZFS and 
ext4 are getting very clever but the creators still seem to have missed out a 
feature I consider should have been implemented long before the fancy things I 
see now.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] presentation about ordering and atomicity of filesystems

On 13 Sep 2014, at 12:47am, James K. Lowden  wrote:

> The filesystem guys I've talked to all consider transactions to be a
> high-level construct hardly ever needed by most applications.  They're
> interested in raw speed and a consistent file structure (metadata)
> after a crash.  They feel that just making one disk look like another
> is hard enough.  
> 
> On the evidence, they're right.

Programmers don't expect file services to support transactions because file 
services have never supported transactions.  We're not used to specifying, when 
we write to one or many files, that a bunch of changes all go together.  But 
when it becomes possible everyone will rave about it.  I come from a banking 
background and I can imagine the transports of delight the financial techies 
will go through when they can assure their users that a crash in software will 
never lead to an inconsistent state: if one account was debited, the other will 
definitely be credited, and the transaction will definitely be in the ledger.  
No need to run consistency checks every time even the simplest application 
crashes.

> SQLite, for example, makes precious
> little use of the filesystem, insofar as the whole database is one
> file.

Except ... one thing that annoys me about SQLite is that it needs to make a 
journal file which isn't part of the database file.  Why ?  Why can't it just 
write the journal to the database file it already has open ?  This would reduce 
the problems where the OS prevents an application from creating a new file 
because of permissions or sandboxing.

Similarly, temporary indexes and temporary tables (I think) also go in external 
files.  I don't see why, if they're part of 'main', they can't go in the main 
file.

> Maybe we should go back to the future. You remember when DBMSs didn't
> use the filesystem, but acted on (the kernel's abstraction of) the
> device directly.  Implement a block-transaction store on the device
> itself: no inodes, no directories, just writeable blocks managed in
> transactions.  Build your DBMS on that.  Use the DBMS to build a
> user-space filesystem. With a little cleverness, they could be mutually
> intelligible, such that tables looked like files and find(1) would
> locate data in the database.  

That would be ... erm ... perhaps a new disk volume format.  Where the blocks 
of the volume were pages of a SQLite database.  One in which VACUUM could never 
shrink the file (but perhaps could be subverted to do something like 
defragmentation).

It would remove one level of abstraction between the OS and the bits on the 
disk surface.  And that's always good.  It would still rely on the disk driver 
correctly supporting flush and all other flush-like operations the OS 
implemented.  Most of them will when the jumpers on the drive are set correctly 
and the driver mounts the volume correctly.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] presentation about ordering and atomicity of filesystems

2014-09-12 Thread James K. Lowden

On Fri, 12 Sep 2014 19:38:53 +0100
Simon Slavin  wrote:

> I don't think it can be done by trying to build it on top of an
> existing file system.  I think we need a file system (volume format,
> drivers, etc.) built from the ground up with
> atomicity/ACID/transactions in mind.  Since the greatest of these is
> transactions, I want a transactional file system.

Funny you should mention that.  About 6 years ago Mike McKusick gave a
presentation on then-recent updates to FFS in FreeBSD, including the
birthdate.  Among other things, I remember he explored using a tree
instead of an array for a directory file, but found that because the
vast majority of directories hold a small number of names, the overall
performance is better with a simple array.  

I asked your question: why not add transactions to FFS?  

His answer: that's the province of a database.  

The filesystem guys I've talked to all consider transactions to be a
high-level construct hardly ever needed by most applications.  They're
interested in raw speed and a consistent file structure (metadata)
after a crash.  They feel that just making one disk look like another
is hard enough.  

On the evidence, they're right.  SQLite, for example, makes precious
little use of the filesystem, insofar as the whole database is one
file.  Within the database, there are no directories, no filesystem
metadata, no inodes, nothing for the OS virtualize/arbitrate between
users.  Apart from anciliary files, about the only thing the FS
supplies is extent management for blocks, and user convenience.  I
don't have to explain to you why even such extent management as it
provides is suboptimal from the point of view of the DBMS.  

Maybe we should go back to the future. You remember when DBMSs didn't
use the filesystem, but acted on (the kernel's abstraction of) the
device directly.  Implement a block-transaction store on the device
itself: no inodes, no directories, just writeable blocks managed in
transactions.  Build your DBMS on that.  Use the DBMS to build a
user-space filesystem. With a little cleverness, they could be mutually
intelligible, such that tables looked like files and find(1) would
locate data in the database.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite db is locked on network drive

On Sat, 13 Sep 2014 00:55:23 +0200, Kees Nuyt 
wrote:

> It works better on your local filesystem.

Perhaps you can configure (a partition on) the Seagate GoFlex as
an iSCSI target and configure an iSCSI initiator on the PC ?
A quick websearch on "seagate goflex iscsi" suggests that's
possible.
If so, locking should work.
(warning: untested)

-- 
Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug and fix in lemon.c

On Fri, Sep 12, 2014 at 7:23 AM, Benjamin Franksen <
benjamin.frank...@helmholtz-berlin.de> wrote:

> Hi Folks
>
> I am using the lemon parser generator for a different project. It
> appears that an attempt to pro-actively avoid a 64-bit problem in fact
> created one in the first place. I am referring to the following change:
>
>
> http://www3.sqlite.org/cgi/src/fdiff?sbs=1=445f18999b700d83b83a5d9be00c596546c21052=90f46af31c92b940fec25b491f39409fd95dcdfa
>
> which says in its comment: "Fix a typecast problem in lemon that could
> cause problems on 64-bit machines."
>
> One of my users noticed that on Windows 8.1 x64 with Visual Studio 2013
> the version of lemon I bundle with my project crashed. With a debugger
> he found that the problem is caused exactly by the line the above patch
> changes and found that using '(unsigned long)' in the cast solves the
> problem. I propose to revert this change. I tested this on Linux (32 and
> 64 Bit).
>

I think the code is better as it stands.  And I am unable to recreate the
problem.

Why do you think (unsigned long) is better?



>
> Cheers
> Ben
>
> P.S: I am not subscribed to the list.
> --
> "Make it so they have to reboot after every typo." â€• Scott Adams
>
>
> 
>
> Helmholtz-Zentrum Berlin für Materialien und Energie GmbH
>
> Mitglied der Hermann von Helmholtz-Gemeinschaft Deutscher
> Forschungszentren e.V.
>
> Aufsichtsrat: Vorsitzender Prof. Dr. Dr. h.c. mult. Joachim Treusch, stv.
> Vorsitzende Dr. Beatrix Vierkorn-Rudolph
> Geschäftsführung: Prof. Dr. Anke Rita Kaysser-Pyzalla, Thomas Frederking
>
> Sitz Berlin, AG Charlottenburg, 89 HRB 5583
>
> Postadresse:
> Hahn-Meitner-Platz 1
> D-14109 Berlin
>
> http://www.helmholtz-berlin.de
> ___
> 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 db is locked on network drive

On Fri, 12 Sep 2014 18:33:46 -0400, Mark Halegua
 wrote:

>On Friday, September 12, 2014 09:43:39 PM Simon Slavin wrote:
>> On 12 Sep 2014, at 9:15pm, Mark Halegua  wrote:
>> > On Friday, September 12, 2014 06:57:21 PM Simon Slavin wrote:
>> >> On 12 Sep 2014, at 5:28pm, Mark Halegua  wrote:
>> >>> The db file is stored on a seagate goflex device as my kinda file
>> >>> server.
>> >>> on that device I can open the db to read but not to write to.
>> >> 
>> >> Do you get an error message ?  Or do your changes just disappear ?
>> >> 
>> >> If it's an error message, what step generates it and what does it say ?
>> > 
>> > when I try an insert I get this:
>> > 
>> > Error: database is locked
>> 
>> Put a text file on you goflex device.
>> 
>> Then access your text file with a text editor and try to make a change in
>> it.  Will it let you save the change you make ?
>> 
>> Simon.
>
>Yes.  The only time I have a problem with files is the sqlite db
>
>Mark

The Seagate goflex appears to be a NAS (Network Attached
Storage, you probably access it using CIFS/SMB/SAMBA (the
protocol microsoft uses for file and printer sharing). 
If the NAS doesn't implement the protocol 100% perfect, locking
issues will occur, which can lead to corruption.
See also:

http://sqlite.org/lockingv3.html#how_to_corrupt

"... On Windows it uses the LockFile(), LockFileEx(), and
UnlockFile() system calls. SQLite assumes that these system
calls all work as advertised. If that is not the case, then
database corruption can result. One should note that POSIX
advisory locking is known to be buggy or even unimplemented on
many NFS implementations (including recent versions of Mac OS X)
and that there are reports of locking problems for network
filesystems under Windows. Your best defense is to not use
SQLite for files on a network filesystem. "

It works better on your local filesystem.

-- 
Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reinstalling My Build Environment

On Fri, 12 Sep 2014 23:23:39 +0100, Simon Slavin
 wrote:

>
>On 12 Sep 2014, at 10:33pm, Kees Nuyt  wrote:
>
>> Sure, it's all available at
>> https://system.data.sqlite.org/
>> 
>> I think the material there covers most of the questions.
>
>So the appropriate thing to do is post a pointer to
>
>
>
>?  That answers the questions about what to install, where to find it and how 
>to install it ?

Yes, and also
https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Actually, a pointer to https://system.data.sqlite.org/ will
usually suffice, they will find their way in the menu.

People asking about developing with, and deploying
System.Data.SQLite should be familiar with the wording, which is
quite specific for MS Windows .NET development and the Visual
Studio IDE.
Problem is, they sometimes aren't (and neither am I, so those
questions usually don't trigger me).

-- 
Groet,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reinstalling My Build Environment

2014-09-12 Thread Joe Mistachkin


Simon Slavin wrote:
> 
> So the appropriate thing to do is post a pointer to
> 
>  
> 
> ?  That answers the questions about what to install, where to find it and
how to install it ?
> 

The FAQ (your link) and the download page (link below) are both good places
for people to look.


https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite db is locked on network drive

On Friday, September 12, 2014 09:43:39 PM Simon Slavin wrote:
> On 12 Sep 2014, at 9:15pm, Mark Halegua  wrote:
> > On Friday, September 12, 2014 06:57:21 PM Simon Slavin wrote:
> >> On 12 Sep 2014, at 5:28pm, Mark Halegua  wrote:
> >>> The db file is stored on a seagate goflex device as my kinda file
> >>> server.
> >>> on that device I can open the db to read but not to write to.
> >> 
> >> Do you get an error message ?  Or do your changes just disappear ?
> >> 
> >> If it's an error message, what step generates it and what does it say ?
> > 
> > when I try an insert I get this:
> > 
> > Error: database is locked
> 
> Put a text file on you goflex device.
> 
> Then access your text file with a text editor and try to make a change in
> it.  Will it let you save the change you make ?
> 
> Simon.

Yes.  The only time I have a problem with files is the sqlite db

Mark


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reinstalling My Build Environment

On 12 Sep 2014, at 10:33pm, Kees Nuyt  wrote:

> Sure, it's all available at
> https://system.data.sqlite.org/
> 
> I think the material there covers most of the questions.

So the appropriate thing to do is post a pointer to

?  That answers the questions about what to install, where to find it and how 
to install it ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] write internal blob vs external file

On Fri, 12 Sep 2014 17:48:27 -0400, Eric Rubin-Smith
 wrote:

>Looking at the sqlite web site and mailing lists shows that the SQLite team
>has taken a stab at answering the question, "is it faster to read a blob
>out of sqlite or out of a file?".  See the links below.
>
>Does the team have analogous guidance regarding write speeds?

Good question, but I don't have an immediate answer.
Perhaps you can set up a quick benchmark with sqlar:
http://www.sqlite.org/sqlar
Use the -n option to disable compression.

> I'm also interested in the delete path.  It seems like SQLite doesn't have
> much hope of competing with a native filesystem unlink(2) call to delete a
> file that is many gigabytes long, for example.  Is that right?

sqlar yields a sqlite3 database, so you can benchmark DELETE
performance using SQL. 

Optimization opportunity:

printf "PRAGMA page_size=bytes; VACUUM;\n" \
| sqlite3 benchmark.sqlar 

>Eric
>
>References:
>
>http://sqlite.1065341.n5.nabble.com/Internal-v-External-BLOBs-td15515.html
>http://www.sqlite.org/intern-v-extern-blob.html

-- 
Groet,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] write internal blob vs external file

On 12 Sep 2014, at 10:48pm, Eric Rubin-Smith  wrote:

> Looking at the sqlite web site and mailing lists shows that the SQLite team
> has taken a stab at answering the question, "is it faster to read a blob
> out of sqlite or out of a file?".  See the links below.
> 
> Does the team have analogous guidance regarding write speeds?

I suspect that this would be very sensitive to the file system and storage 
drivers used.  You might get results with one setup which differ radically from 
those with another setup.  You might also get results which change depending 
how many files/rows there are when you update one file/row.

> I'm also interested in the delete path.  It seems like SQLite doesn't have
> much hope of competing with a native filesystem unlink(2) call to delete a
> file that is many gigabytes long, for example.  Is that right?

unlink(2) does a bit of checking to see that the user has permission to delete 
that file specifically and also to delete files from the folder that the file 
is in.  And once the file is deleted it has to add blocks to the volume's free 
list.  All SQLite has to do is change some bytes in flies it already has open.

I couldn't hope guess how this will come out and I will be interested in the 
result.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical \'AND\'

2014-09-12 Thread David Woodhouse

On Fri, 2014-09-12 at 15:35 -0600, Keith Medcalf wrote:
> What happens if you phrase it like this?
> 
>SELECT DISTINCT summary.uid, summary.vcard 
>  FROM folder_id AS summary
> LEFT JOIN 'folder_id_email_list' AS email_list
>ON email_list.uid = summary.uid
>   AND email_list.value like 'foo%'
> WHERE email_list.value like 'foo%'
>OR summary.full_name like 'foo%';
> 
> Which of course makes the left join only include records already satisfying 
> the condition?

0|0|0|SCAN TABLE folder_id AS summary USING INDEX sqlite_autoindex_folder_id_1
0|1|1|SEARCH TABLE folder_id_email_list AS email_list USING INDEX 
UID_INDEX_email_folder_id (uid=?)
0|0|0|USE TEMP B-TREE FOR DISTINCT

That still takes about a second and a half on my data set. Compared with
5-10ms for the UNION version:

1|0|1|SEARCH TABLE folder_id_email_list AS email_list USING INDEX 
INDEX_email_folder_id (value>? AND value? AND full_name___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] write internal blob vs external file

On Fri, Sep 12, 2014 at 5:48 PM, Eric Rubin-Smith  wrote:

> Looking at the sqlite web site and mailing lists shows that the SQLite team
> has taken a stab at answering the question, "is it faster to read a blob
> out of sqlite or out of a file?".  See the links below.
>
> Does the team have analogous guidance regarding write speeds?
>
> I'm also interested in the delete path.  It seems like SQLite doesn't have
> much hope of competing with a native filesystem unlink(2) call to delete a
> file that is many gigabytes long, for example.  Is that right?
>

The fact that SQLite is faster at reading was originally discovered by the
Adobe Lightroom developers and was surprising to the team.  We have not
looked into write performance.  Why don't you do the measurements and
report back?

-- 
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] SQLite Development Team support of System.Data.SQLite (was RE: Reinstalling My Build Environment)

2014-09-12 Thread Joe Mistachkin

Kees Nuyt wrote:
> 
> Well, if it doesn't ring the bell on your front door, why
> answer? 
> 

This is an excellent point.  I can assure you that this *IS* the most
appropriate venue to ask questions about System.Data.SQLite.

TO EVERYONE: PLEASE DO NOT MAKE ANY STATEMENTS TO THE CONTRARY ON THIS
 MAILING LIST.

Sorry to shout; however, I've now seen this happen so many times that
I must intervene.

> 
> Sure, it's all available at
> https://system.data.sqlite.org/ 
> 

Yes, that is the official web site for System.Data.SQLite, which *IS*
fully supported and *IS* maintained by the SQLite Development Team.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.Sqlite.dll v93 performance issues...

2014-09-12 Thread Joe Mistachkin

Andreas Hofmann wrote:
>
> 2)  Where can I create a bug for this? 
> 

These issues should be fixed in the pre-release 1.0.94.0 build, available
from here:

https://system.data.sqlite.org/index.html/doc/preRelease/www/downloads.wiki

Please let us know if this solves the issue in your environment.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] write internal blob vs external file

2014-09-12 Thread Eric Rubin-Smith

Looking at the sqlite web site and mailing lists shows that the SQLite team
has taken a stab at answering the question, "is it faster to read a blob
out of sqlite or out of a file?".  See the links below.

Does the team have analogous guidance regarding write speeds?

I'm also interested in the delete path.  It seems like SQLite doesn't have
much hope of competing with a native filesystem unlink(2) call to delete a
file that is many gigabytes long, for example.  Is that right?

Eric

References:

http://sqlite.1065341.n5.nabble.com/Internal-v-External-BLOBs-td15515.html
http://www.sqlite.org/intern-v-extern-blob.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical \'AND\'

2014-09-12 Thread Keith Medcalf


What happens if you phrase it like this?

   SELECT DISTINCT summary.uid, summary.vcard 
 FROM folder_id AS summary
LEFT JOIN 'folder_id_email_list' AS email_list
   ON email_list.uid = summary.uid
  AND email_list.value like 'foo%'
WHERE email_list.value like 'foo%'
   OR summary.full_name like 'foo%';

Which of course makes the left join only include records already satisfying the 
condition?




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reinstalling My Build Environment


On Fri, 12 Sep 2014 16:41:40 +0100, Simon Slavin
 wrote:

> On 12 Sep 2014, at 3:44pm, 
> Drago, William @ MWG - NARDAEAST 
> wrote:

> > It seems like a lot of people are unaware of
> > System.Data.Sqlite. This is understandable since most people on
> > this list are C programmers not .NET/C#/VB developers, but this
> > is the support list for System.Data.Sqlite as well as SQLite,
> > isn't it?

> All true.

> > Shouldn't we all be aware that anyone asking for help with
> > Visual Studio and/or EF6 and/or referencing file names like
> > sqlite-netFx451-setup-bundle-x86-2013-1.0.92.0.exe, is asking
> > about System.Data.Sqlite?

> How would I know that ?  I've never programmed for Visual Studio
> or for .NET.  I don't really know what they are (and don't need
> to).  I don't know what names like 'System.Data.Something' and
> 'netFx451' indicate.  For all I know they're something about
> Android.

Well, if it doesn't ring the bell on your front door, why
answer? 

Currently SQLite has created a very diverse landscape of
solutions, nobody expects we can cover them all by ourselves.
 
> There seems to be a confusing multiplicity of possible setups
> including things like Visual Studio and Entity Framework.  And
> they don't all end up needing the same SQLite
> package/library/executable/bundle from the same download site.
> While numerous people on this list can advise on PRAGMAs or a
> good way to phrase something in SQL, to answer questions like
> that you really need someone who actually uses that platform,
> and sometimes even someone who uses that version of that
> platform.
> 
> So people post questions like this, but their questions don't
> get answered as quickly as other questions posted on the same
> day.  And, reasonably, they think they're being ignored or their
> question didn't get posted properly or something.

They'll get used to the turn-around time of mailing lists soon
enough, if they are bothered to invest some time themselves.
If they really need immediate answers, they can buy a support
contract.

Eventually, when nobody else in the mailing list provides a
valid answer, Joe Mistachkin (member of the SQLite development
team) usually picks it up.
http://sqlite.org/crew.html

> Would it be possible to write a document for people looking for
> the right thing to download and install for their version of
> .NET or the Visual IDE or whatever it is ?  Perhaps with a text-
> based decision table to let readers figure out which one they
> want ?  And an explanation like the one I keep seeing about
> whether you just need runtime support or whether you also want
> setup/viewing integrated into the IDE.  Then if a question
> includes the words 'Visual Studio' or .NET, anyone can respond
> with a pointer to that document as a first approximation to an
> answer, whether they understand the question or not.
> 
> If such document already exists, could someone post a pointer ?

Sure, it's all available at
https://system.data.sqlite.org/

I think the material there covers most of the questions.

-- 
Groet,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite db is locked on network drive

2014-09-12 Thread FarSight Data Systems

when I try an insert I get this:

Error: database is locked

Mark

On Friday, September 12, 2014 06:57:21 PM Simon Slavin wrote:
> On 12 Sep 2014, at 5:28pm, Mark Halegua  wrote:
> > The db file is stored on a seagate goflex device as my kinda file server. 
> > on that device I can open the db to read but not to write to.
> 
> Do you get an error message ?  Or do your changes just disappear ?
> 
> If it's an error message, what step generates it and what does it say ?
> 
> Simon.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 

Mark S. Halegua
718-360-1712
917-686-8794
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical \'AND\'

2014-09-12 Thread David Woodhouse


On Wed, 27 Nov 2013 at 21:21:43 -0800, Igor Tandetnik wrote
> Why are you using outer joins when your WHERE clause discards
> unmatched records anyway? If you replace LEFT OUTER with INNER, the
> end result would be exactly the same.

Not for all queries.

Consider the query
 (or (beginswith "full_name" "foo") (beginswith "email" "foo"))

The full_name field is in the main table, since each vcard only has
*one* full_name field. There can be multiple email addresses, so those
are in an auxiliary table.

The SQL query becomes:

 SELECT DISTINCT summary.uid, summary.vcard from 'folder_id' AS summary
 LEFT OUTER JOIN 'folder_id_email_list' AS email_list
 ON email_list.uid = summary.uid
 WHERE (email_list.value like 'foo%')
OR (summary.full_name like 'foo%');

If that *isn't* a LEFT OUTER JOIN, and there's a record in the main
'folder_id' table which doesn't have *any* email addresses, then it will
be incorrectly omitted from the results.

As it happens, the performance on the LEFT OUTER JOIN sucks somewhat,
since it doesn't use any indices on folder_id(full_name):

0|0|0|SCAN TABLE folder_id AS summary USING INDEX sqlite_autoindex_folder_id_1
0|1|1|SEARCH TABLE folder_id_email_list AS email_list USING INDEX 
UID_INDEX_email_folder_id (uid=?)
0|0|0|USE TEMP B-TREE FOR DISTINCT

There's an optimisation there which maybe it would be nice if sqlite
would see for itself. It works a *whole* lot faster if we express it
thus:

 SELECT DISTINCT summary.uid, summary.vcard from 'folder_id' as summary
 JOIN 'folder_id_email_list' as email_list
 ON +email_list.uid = summary.uid
  WHERE (email_list.value like 'foo%')
UNION
 SELECT summary.uid, summary.vcard from 'folder_id' as summary
  WHERE (summary.full_name like 'foo%');

(Note that this time it *can* be an INNER JOIN.) 

1|0|1|SEARCH TABLE folder_id_email_list AS email_list USING INDEX 
INDEX_email_folder_id (value>? AND value? AND full_name___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Delays on multiple access to the database through shared network.

2014-09-12 Thread Motonari CHINO

Hello.
I am connecting SQLite database through shared folder from application
written with C# using “System.Data.SQLite.dll”
When doing partial match searching from 300,000 records with just one PC
connected to the network, it takes less than one second for the query
result to be returned.
But when connecting to the same database with another PCs, it takes about
12 seconds to have query result to return.
If index search (fully matched, prefix or the beginning match) is used,
multiple connections will have no problem with its speed.  We have tested
and confirmed that result.
The speed will somewhat improve if all PC is disconnected once for each
query, but since there is another problem that it takes too much time to
cache the index on the first query, I’d like to have it keep connected
until the application is finishes.
Are there any ways to avoid having speed declining when non-index search
are done while connected to database with multiple connection from the
network?

I gave SQLite Connection the following connect string:
Data Source="test.db";Pooling=false;FailIfMissing=false;Journal
Mode=Default;Synchronous=Normal

Motonari
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug and fix in lemon.c

2014-09-12 Thread Benjamin Franksen

Hi Folks

I am using the lemon parser generator for a different project. It
appears that an attempt to pro-actively avoid a 64-bit problem in fact
created one in the first place. I am referring to the following change:

http://www3.sqlite.org/cgi/src/fdiff?sbs=1=445f18999b700d83b83a5d9be00c596546c21052=90f46af31c92b940fec25b491f39409fd95dcdfa

which says in its comment: "Fix a typecast problem in lemon that could
cause problems on 64-bit machines."

One of my users noticed that on Windows 8.1 x64 with Visual Studio 2013
the version of lemon I bundle with my project crashed. With a debugger
he found that the problem is caused exactly by the line the above patch
changes and found that using '(unsigned long)' in the cast solves the
problem. I propose to revert this change. I tested this on Linux (32 and
64 Bit).

Cheers
Ben

P.S: I am not subscribed to the list.
--
"Make it so they have to reboot after every typo." â Scott Adams

Helmholtz-Zentrum Berlin für Materialien und Energie GmbH

Mitglied der Hermann von Helmholtz-Gemeinschaft Deutscher Forschungszentren e.V.

Aufsichtsrat: Vorsitzender Prof. Dr. Dr. h.c. mult. Joachim Treusch, stv.
Vorsitzende Dr. Beatrix Vierkorn-Rudolph
Geschäftsführung: Prof. Dr. Anke Rita Kaysser-Pyzalla, Thomas Frederking

Sitz Berlin, AG Charlottenburg, 89 HRB 5583

Postadresse:
Hahn-Meitner-Platz 1
D-14109 Berlin

http://www.helmholtz-berlin.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] presentation about ordering and atomicity of filesystems

2014-09-12 Thread Remzi H. Arpaci-Dusseau

hi!

Thanks for the note, and the interest.

I'm cc'ing Thanu into this discussion -- he and a few other grad students
did all the heavy lifting and can share the details of what they've found.

As I recall, he has already spoken with some of the SQLite gang a bit
about the purported "problem" (and perhaps we even disagree as to
whether it is or not). It may be that our reading of what the documentation
guarantees does not match what you think it does. Thanu?

As for SQLite "coming out on top" -- well, we're trying very hard not
to view our results as a comparison of one system vs. another,
because frankly we are only looking at one type of issue and
not overall robustness.

That said, we've been very impressed with SQLite -- it seems like
one of the few systems that has been very carefully thought through,
and we've learned much from it as well as what we've seen of its
testing tools.

We do have an upcoming paper on said topic (at OSDI '14),
in case you'd like to read more.

Thanks again!
Remzi









On Thu, Sep 11, 2014 at 9:07 PM, Richard Hipp  wrote:

>
>
> On Thu, Sep 11, 2014 at 5:49 PM, Kees Nuyt  wrote:
>
>>
>> Hi all,
>>
>> Today I bumped into a presentation about ordering and atomicity
>> of filesystems that might interest you.
>>
>> https://www.youtube.com/watch?v=YvchhB1-Aws
>>
>> The Application/Storage Interface: After All These Years, We're
>> Still Doing It Wrong
>> Remzi Arpaci-Dusseau, University of Wisconsin--Madison
>>
>> Talk at usenix 2014 Published on Sep 4, 2014 by USENIX
>> Association Videos
>>
>> Somewhat related to the article drh recently wrote about using
>> sqlite as an application data store.
>>
>>
> Thanks for the link, Kees!
>
> I just finished watching the video.  Remzi Arpaci-Dusseau talks about
> research (done by he and his graduate students) into how well application
> data survives system crashes.  Remzi observes that filesystem developers
> have worked very hard for many years ensuring that filesystem metadata is
> preserved in a crash, but they seem less concerned about protecting
> application data.
>
> Remzi developed tools (BOB and ALICE) to study various workloads to see
> how vulnerable they were to system crashes.  He looked at various
> "applications".  His definition of "application" includes standalone
> programs like Git and Hg, and database servers like PostgreSQL, and
> libraries like SQLite and LevelDB.  At one point he shows a chart that
> counts the number of unwarranted assumptions that the applications make
> about filesystem behavior.  Such unwarranted assumptions can lead to
> corruption following a system crash (or power loss).
>
> SQLite and PostgreSQL came out on top, with just one vulnerability each.
> Hg and Git each had many vulnerabilities.  In fairness, Remzi points out
> that these vulnerabilities assume a "worst case" filesystem and that many
> of them might not exist on a modern filesystem like EXT4.
>
> Remzi:  I would very much like to learn more about that one unwarranted
> durability assumption that you contend SQLite is making.
>
> That SQLite does well in an analysis using ALICE and BOB is not really
> surprising.  It turns out that we SQLite developers have our own ALICE and
> BOB like tools that we have implemented using custom VFSes.  We have three
> of them, actually, implemented at different times, by both me and Dan.
> (Only two are BOB- and ALICE-like crash simulators - the third tool is an
> invariant checker that helps us to prove crashes are recoverable.)  We run
> many cycles of all three prior to every release, looking for crash
> vulnerabilities.  If SQLite really is making an unwarranted durability
> assumption, as Remzi contends, then that points to a deficiency in our
> three crash analyzers, which is something we would like to fix.
>
> Remzi also talks about the idea of a new system call that he refers to as
> "osync()" that causes I/O operations to be ordered.  I've been saying much
> the same thing, for years, to anybody who would listen, though I've been
> calling the system call a "write barrier".  The idea is that if you could
> replace fsync() with the write barrier, you would lose durability (which
> few people really care about) but gain a lot more performance.  Remzi shows
> a test case using SQLite where osync() instead of fsync() results in a
> ten-fold performance improvement.
>
> --
> 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] System.Data.Sqlite.dll v93 performance issues...

2014-09-12 Thread Andreas Hofmann

Guys,

 

I do not know if this is the right forum, but we found a reproducible perf
issue with System.Data.Sqlite.dll. A simple query like this:

 

Select * from Dxlog  where  ContestNR = 0  order by contestnr, TS   limit 24
offset 143098

 

Takes about 60ms to fetch the first record from the reader, and then only
0.5ms for the rest of the 24 rows.  When I go back to version 92, it takes
only 2ms for the first fetch.

 

I can provivide more details if needed, but for now, I need to know these
two things:

1)  Where can I find the older builds, especially the statically linked
one for v92.

2)  Where can I create a bug for this?

 

Thanks

Andy

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite db is locked on network drive

On 12 Sep 2014, at 9:15pm, Mark Halegua  wrote:

> On Friday, September 12, 2014 06:57:21 PM Simon Slavin wrote:
>> On 12 Sep 2014, at 5:28pm, Mark Halegua  wrote:
>>> The db file is stored on a seagate goflex device as my kinda file server. 
>>> on that device I can open the db to read but not to write to.
>> 
>> Do you get an error message ?  Or do your changes just disappear ?
>> 
>> If it's an error message, what step generates it and what does it say ?
> 
> when I try an insert I get this:
> 
> Error: database is locked

Put a text file on you goflex device.

Then access your text file with a text editor and try to make a change in it.  
Will it let you save the change you make ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite db is locked on network drive

2014-09-12 Thread Keith Medcalf

>when I try an insert I get this:

>Error: database is locked

Is that a "goflex" USB device or the network device?  If the network device 
this likely means that you are attempting to use the device in a manner for 
which it was not designed.





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite db is locked on network drive

On Friday, September 12, 2014 06:57:21 PM Simon Slavin wrote:
> On 12 Sep 2014, at 5:28pm, Mark Halegua  wrote:
> > The db file is stored on a seagate goflex device as my kinda file server. 
> > on that device I can open the db to read but not to write to.
> 
> Do you get an error message ?  Or do your changes just disappear ?
> 
> If it's an error message, what step generates it and what does it say ?
> 
> Simon.

when I try an insert I get this:

Error: database is locked

Mark


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pls dont send so many message - once a month is enough

2014-09-12 Thread Larry Brasfield

Ketil Froyn writes:
> Clicking the mailto works, but it's not a proper reply-to. These two
> messages have appeared as a separate thread in my mailbox, probably 
because
> Larry's message didn't add the proper message-id reference. So if you 
reply

> like this on a high volume list, people following the thread might miss
> your post.

Actually, the mailto: URL has the proper In-Reply-To header to make 
threading work.

Unfortunately, the Thunderbird email client no longer knows what to do 
with it.
This is a regression introduced in version 24.* and which appears to 
remain in the

present version 31.1.1 .

So, whether clicking that mailto: link will work (correctly) depends on 
the setup.
With Thunderbird as the handler, it will mess up threading, at least 
today.  I had
to manually set the In-Reply-To: header (using the value from that URL) 
to get

this message to thread-link correctly (assuming it does!).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] presentation about ordering and atomicity of filesystems

2014-09-12 Thread Nico Williams

On Fri, Sep 12, 2014 at 1:18 PM, Howard Chu  wrote:
> Nico Williams wrote:
>> On ZFS datasets with sync disabled fsync() functions as osync(), as a
>> write
>> barrier without durability and without the associated penalty.  The
>> obvious
>> problem is that really do need osync() and fsync(); just one or the other
>> is not a reasonable compromise.
>
> Write barriers have been debated in Linux ad nauseum. I agree that osync()
> would be great to have, but it's still a die roll - the OS can flush blocks
> to the storage device in order, but without waiting for the storage device's
> buffer to empty, can't make any further ordering promises from there. You
> need device-level ordering support too. - which prompted my suggestion here

For ZFS there's no problem if this happens: you might lose whole
transactions, but the filesystem will remain consistent.  That's the
no-durability-guarantee part of a write barrier.

On recovery ZFS wants you to note and approve of any transactions lost
beyond the last one that was in process of being flushed.  If the last
one didn't complete that may be because of a power outage before the
cache flush could complete.  If more than one transaction didn't
completely reach stable storage ZFS figures it must have been that the
HW lied about cache flushes.

Of course, one might actually want the filesystem to never issue cache
flushes except in response to sync()/fsync()/fdatasync() calls.  IIRC
there's no way to configure such behavior in ZFS -- one can only make
sync/fsync/fdatasync not wait for a cache flush.  For some uses one
might really want such behavior though: a few lost transactions not
closed because of a sync/fsync/fdatasync syscall may be tolerable.
But then, ZFS can keep transactions open for a fairly long time if
sync I/O is not requested by any apps...

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] presentation about ordering and atomicity of filesystems

On 12 Sep 2014, at 7:08pm, Nico Williams  wrote:

> On ZFS datasets with sync disabled fsync() functions as osync(), as a write
> barrier without durability and without the associated penalty.  The obvious
> problem is that really do need osync() and fsync(); just one or the other
> is not a reasonable compromise.

Yep.  However ...

If you do it right, and you don't have an extra layer of hardware doing your 
write barrier, you sacrifice speed.  No exceptions.  Today's computers -- the 
desktop or laptop every one of us has -- are tensed to the eyebrows with 
time-optimization dodges.  Many of those defeat any opportunity for 
in-order-writing.  I don't think it can be done by trying to build it on top of 
an existing file system.  I think we need a file system (volume format, 
drivers, etc.) built from the ground up with atomicity/ACID/transactions in 
mind.  Since the greatest of these is transactions, I want a transactional file 
system.

Some years ago I ordered a very fast server-class Wintel computer with all 
components specced for use with safety-critical server use.  Having set jumpers 
on the motherboard and hard disk for "do it properly, don't acknowledge a write 
until the data is on the disk" I installed a standard copy of Windows (I think) 
and Microsoft Office on it.  

It was unusably slow.  Something like 5 minutes to boot and another 5 minutes 
to get a blank document showing in Microsoft Word.   I'm not talking about the 
initial setup boot, I'm talking 5 minutes for every boot, and 5 minutes to wait 
every time you start Word.  Typing into Word got about 3 characters a second 
which lags behind my standard typing speed quite a bit.  It made for a very 
convincing demonstration.

This is not a gripe at Microsoft.  It wasn't the software's fault.  It's what 
you get when you defeat all the speed optimizations we expect these days.  Or 
ten years ago when I did the experiment.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] presentation about ordering and atomicity of filesystems

2014-09-12 Thread Howard Chu


Nico Williams wrote:

On ZFS datasets with sync disabled fsync() functions as osync(), as a write
barrier without durability and without the associated penalty.  The obvious
problem is that really do need osync() and fsync(); just one or the other
is not a reasonable compromise.


Write barriers have been debated in Linux ad nauseum. I agree that osync() 
would be great to have, but it's still a die roll - the OS can flush blocks to 
the storage device in order, but without waiting for the storage device's 
buffer to empty, can't make any further ordering promises from there. You need 
device-level ordering support too. - which prompted my suggestion here


http://www.spinics.net/lists/linux-fsdevel/msg70047.html

--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] presentation about ordering and atomicity of filesystems

2014-09-12 Thread Nico Williams

On ZFS datasets with sync disabled fsync() functions as osync(), as a write
barrier without durability and without the associated penalty.  The obvious
problem is that really do need osync() and fsync(); just one or the other
is not a reasonable compromise.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite db is locked on network drive

On 12 Sep 2014, at 5:28pm, Mark Halegua  wrote:

> The db file is stored on a seagate goflex device as my kinda file server.  on 
> that device I can open the db to read but not to write to.

Do you get an error message ?  Or do your changes just disappear ?

If it's an error message, what step generates it and what does it say ?

Simon.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite db is locked on network drive

I'm developing an application, for single users, using python and wxpyton with 
pysqlite.  I often go into the database via sqlite3 to modify stuff or add 
records to tables for testing.

The db file is stored on a seagate goflex device as my kinda file server.  on 
that device I can open the db to read but not to write to.  I need to 
copy/move the db to the local drive and modify it there, then copy the db back 
to the network drive.

I'd like to be able to open and write to the db without going through all 
that.  There must be some sort of network file locking I don't see.

So, how can I unlock the file for writing to the network file?

Thanks, 

Mark
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] SQLite Vdbe Opcode MakeRecord

2014-09-12 Thread Keith Medcalf


sqlite-users is probably more appropriate than the development list so this 
should be carried on there.  Please use that list to continue this discussion 
as more people are likely to see it, participate, and benefit from the 
discussions.

Have you actually built some tables in SQLite and had it dump the VDBE code 
that it will execute to perform the query?  This will probably help enormously 
in understanding how SQLite works.

As an aside, SQLite does not join three tables by joining tables one and two 
into a temporary table, then joining in table three.  It plots the most 
efficient nested loop (navigational) solution and executes the resulting row 
generator which yields each result row as it is found.

See:  http://www.sqlite.org/optoverview.html

sqlite3_step() equates to:

def stepper():
open table1
open table2
open table3
rewind table1
for each row in table1 matching the query constraints on table1
rewind table2
for each row in table2 matching the constraints in the query 
and provided by table1
rewind table3
for each row in table3 matching the constraints in the 
query and provided by table1 and table2
yield a result row
close table3
close table2
close table1

where stepper is a generator.  the first call executes the code up until the 
yield whereupon the result is returned to the caller.  Subsequent calls carry 
on from the yield thus returning each result row one at a time.  when all the 
loops run out and there is nothing more to yield the generator terminates and 
returns an no more rows indication to the caller.

sqlite3_prepare prepares the stepper program by compiling the sql query that 
you have asked to be executed.

SQLite version 3.8.7 2014-09-12 04:28:33
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table employee(name, stuff);
sqlite> create table location(name, stuff);
sqlite> create table company(name, stuff);
sqlite> .explain
sqlite> explain select * from employee, company, location where employee.name = 
company.name and company.name = location.name;
SELECT item[0] = {0:0} AS name
   item[1] = {0:1} AS stuff
   item[2] = {1:0} AS name
   item[3] = {1:1} AS stuff
   item[4] = {2:0} AS name
   item[5] = {2:1} AS stuff
FROM {0,*} = employee
 {1,*} = company
 {2,*} = location
WHERE AND(EQ({0:0},{1:0}),EQ({1:0},{2:0}))
END
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 51000  Start at 51
1 OpenRead   0 2 0 2  00  root=2 iDb=0; employee
2 OpenRead   1 4 0 2  00  root=4 iDb=0; company
3 OpenRead   2 3 0 2  00  root=3 iDb=0; location
4 Noop   0 0 000  Begin WHERE-loop0: 
employee
5 Rewind 0 48000
6   Once   0 15000
7   OpenAutoindex  3 3 0 k(3,nil,nil,nil)  00  nColumn=3; for 
company
8   Rewind 1 15000
9 Column 1 0 200  r[2]=company.name
10Column 1 1 300  r[3]=company.stuff
11Rowid  1 4 000  r[4]=rowid
12MakeRecord 2 3 100  
r[1]=mkrec(r[2..4])
13IdxInsert  3 1 010  key=r[1]
14  Next   1 9 003
15  Noop   0 0 000  Begin WHERE-loop1: 
company
16  Column 0 0 500  r[5]=employee.name
17  IsNull 5 46000  if r[5]==NULL goto 
46
18  SeekGE 3 465 1  00  key=r[5]
19IdxGT  3 465 1  00  key=r[5]
20Once   1 29000
21OpenAutoindex  4 3 0 k(3,nil,nil,nil)  00  nColumn=3; for 
location
22Rewind 2 29000
23  Column 2 0 200  
r[2]=location.name
24  Column 2 1 300  
r[3]=location.stuff
25  Rowid  2 4 000  r[4]=rowid
26  MakeRecord 2 3 100  
r[1]=mkrec(r[2..4])
27  IdxInsert  4 1 010  key=r[1]
28Next   2 23003
29Noop   0 0 000  Begin 
WHERE-loop2: location

Re: [sqlite] Reinstalling My Build Environment

On 12 Sep 2014, at 3:44pm, Drago, William @ MWG - NARDAEAST 
 wrote:

> It seems like a lot of people are unaware of System.Data.Sqlite. This is 
> understandable since most people on this list are C programmers not 
> .NET/C#/VB developers, but this is the support list for System.Data.Sqlite as 
> well as SQLite, isn't it?

All true.

>  Shouldn't we all be aware that anyone asking for help with Visual Studio 
> and/or EF6 and/or referencing file names like 
> sqlite-netFx451-setup-bundle-x86-2013-1.0.92.0.exe, is asking about 
> System.Data.Sqlite?

How would I know that ?  I've never programmed for Visual Studio or for .NET.  
I don't really know what they are (and don't need to).  I don't know what names 
like 'System.Data.Something' and 'netFx451' indicate.  For all I know they're 
something about Android.

There seems to be a confusing multiplicity of possible setups including things 
like Visual Studio and Entity Framework.  And they don't all end up needing the 
same SQLite package/library/executable/bundle from the same download site.  
While numerous people on this list can advise on PRAGMAs or a good way to 
phrase something in SQL, to answer questions like that you really need someone 
who actually uses that platform, and sometimes even someone who uses that 
version of that platform.

So people post questions like this, but their questions don't get answered as 
quickly as other questions posted on the same day.  And, reasonably, they think 
they're being ignored or their question didn't get posted properly or something.

Would it be possible to write a document for people looking for the right thing 
to download and install for their version of .NET or the Visual IDE or whatever 
it is ?  Perhaps with a text-based decision table to let readers figure out 
which one they want ?  And an explanation like the one I keep seeing about 
whether you just need runtime support or whether you also want setup/viewing 
integrated into the IDE.  Then if a question includes the words 'Visual Studio' 
or .NET, anyone can respond with a pointer to that document as a first 
approximation to an answer, whether they understand the question or not.

If such document already exists, could someone post a pointer ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reinstalling My Build Environment

2014-09-12 Thread Drago, William @ MWG - NARDAEAST

That was an excellent response, Joe. I think Paul Bainter gave up way too 
quickly and his conclusion that System.Data.SQLite is unsupported is 
unwarranted, but I can understand if he felt alienated by comments like:

"You may have found a library built on SQLite...Whatever you found isn't 
SQLite."

And:

"Whatever package you are attempting to install...SQLite has no direct 
responsibilities to your IDE."

It seems like a lot of people are unaware of System.Data.Sqlite. This is 
understandable since most people on this list are C programmers not .NET/C#/VB 
developers, but this is the support list for System.Data.Sqlite as well as 
SQLite, isn't it? Shouldn't we all be aware that anyone asking for help with 
Visual Studio and/or EF6 and/or referencing file names like 
sqlite-netFx451-setup-bundle-x86-2013-1.0.92.0.exe, is asking about 
System.Data.Sqlite?

Regards,
--
Bill Drago
Senior Engineer
L3 Communications / Narda Microwave East
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Joe Mistachkin
> Sent: Thursday, September 11, 2014 11:19 PM
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] Reinstalling My Build Environment
>
>
> I assume that you did not see the response I sent earlier?
>
>   http://www.mail-archive.com/sqlite-
> users%40sqlite.org/msg85999.html
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pls dont send so many message - once a month is enough

2014-09-12 Thread Ketil Froyn

Clicking the mailto works, but it's not a proper reply-to. These two
messages have appeared as a separate thread in my mailbox, probably because
Larry's message didn't add the proper message-id reference. So if you reply
like this on a high volume list, people following the thread might miss
your post.

Cheers, Ketil
On 12 Sep 2014 13:35, "John McKown"  wrote:

> On Fri, Sep 12, 2014 at 1:16 AM, Larry Brasfield
>  wrote:
> > John McKown writes:
> >
> >>  The first option is to totally disable getting any emails at all. This
> >>  is often called NOMAIL mode. If you decide to do that, then you can,
> >>  at your convenience, go to this page:
> >>  http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/ and
> >>  review the archives. Before you can browse them, you will need to put
> >>  in your email address and password. This option is really nice for
> >>  reading threads because the software can put the thread together for
> >>  you in a logical manner. Unfortunately, you cannot use the archive
> >>  page to reply to a message, or to originate a message.
> >
> > John, your kind reply is an inspiration.
> >
> > You, and the OP, may be pleased to know that under the right
> circumstances,
> > you can use the archive page to reply to a message.  On every message
> > displayed by the web interface to the archive, the 2nd line had an
> > underlined,
> > "mailto:; link.  For systems/accounts which are setup to handle that
> > "protocol",
> > clicking the link in most modern browsers will invoke the setup email
> > client,
> > with the correct addressee to reach the list.  (I have used that mailto:
> > link for
> > this message, in fact.)
> >
> > Best regards,
> > --
> > Larry Brasfield
>
> Ah. I see it, now. That cataract surgery is paying off! I didn't
> notice that the email address was highlighted and could be clicked
> upon.
>
> --
> There is nothing more pleasant than traveling and meeting new people!
> Genghis Khan
>
> Maranatha! <><
> John McKown
> ___
> 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] PRAGMA cache_size;

2014-09-12 Thread Paul



> On Fri, Sep 12, 2014 at 5:00 AM, Paul  wrote:
> 
> >
> > I have a few questions regarding 'cache_size' PRAGMA:
> >
> > (Q1) Does this PRAGMA affects all attached databases or should I set it
> > for each
> > attached database individually like 'PRAGMA attached.cache_size =
> > 100'?
> >
> 
> Set all cache sizes individually.
> 
> 
> >
> > (Q2) If it affects all databasess and, for example, I set it to 100, will
> > it mean every
> > database (attached or main) have its own separate cache of size 100 *
> > page_size
> > or is it single, shared cache?
> >
> 
> Each database has its own cache.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org

Thank you!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pls dont send so many message - once a month is enough

2014-09-12 Thread John McKown

On Fri, Sep 12, 2014 at 1:16 AM, Larry Brasfield
 wrote:
> John McKown writes:
>
>>  The first option is to totally disable getting any emails at all. This
>>  is often called NOMAIL mode. If you decide to do that, then you can,
>>  at your convenience, go to this page:
>>  http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/ and
>>  review the archives. Before you can browse them, you will need to put
>>  in your email address and password. This option is really nice for
>>  reading threads because the software can put the thread together for
>>  you in a logical manner. Unfortunately, you cannot use the archive
>>  page to reply to a message, or to originate a message.
>
> John, your kind reply is an inspiration.
>
> You, and the OP, may be pleased to know that under the right circumstances,
> you can use the archive page to reply to a message.  On every message
> displayed by the web interface to the archive, the 2nd line had an
> underlined,
> "mailto:; link.  For systems/accounts which are setup to handle that
> "protocol",
> clicking the link in most modern browsers will invoke the setup email
> client,
> with the correct addressee to reach the list.  (I have used that mailto:
> link for
> this message, in fact.)
>
> Best regards,
> --
> Larry Brasfield

Ah. I see it, now. That cataract surgery is paying off! I didn't
notice that the email address was highlighted and could be clicked
upon.

-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA cache_size;