Re: [sqlite] light weight write barriers

[Nico Williams]

On Wed, Oct 24, 2012 at 8:04 PM,   wrote:
> On Wed, 24 Oct 2012, Nico Williams wrote:
>> COW is "copy on write", which is actually a bit of a misnomer -- all
>> COW means is that blocks aren't over-written, instead new blocks are
>> written.  In particular this means that inodes, indirect blocks, data
>> blocks, and so on, that are changed are actually written to new
>> locations, and the on-disk format needs to handle this indirection.
>
> so how can you do this, and keep the writes in order (especially between two
> files) without being the filesystem?

By trusting fsync().  And if you don't care about immediate Durability
you can run the fsync() in a background thread and mark the associated
transaction as completed in the next transaction to be written after
the fsync() completes.

>> As for fsyn() and background threads... fsync() is synchronous, but in
>> this scheme we want it to happen asynchronously and then we want to
>> update each transaction with a pointer to the last transaction that is
>> known stable given an fsync()'s return.
>
> If you could specify ordering between two writes, I could see a process
> along the lines of
>
> [...]

fsync() deals with just one file.  fsync()s of different files are
another story.  That said, as long as the format of the two files is
COW then you can still compose transactions involving two files.  The
key is the file contents itself must be COW-structured.

Incidentally, here's a single-file, bag of b-trees that uses a COW
format: MDB, which can be found in
git://git.openldap.org/openldap.git, in the mdb.master branch.

> Or, as I type this, it occurs to me that you may be saying that every time
> you want to do an ordering guarantee, spawn a new thread to do the fsync and
> then just keep processing. The fsync will happen at some point, and the
> writes will not be re-ordered across the fsync, but you can keep going,
> writing more data while the fsync's are pending.

Yes, but only if the file's format is COWish.

The point is that COW saves the day.  A file-based DB needs to be COW.
 And the filesystem needs to be as well.

Note that write ahead logging approximates COW well enough most of the time.

> Then if you have a filesystem and I/O subsystem that can consolodate the
> fwyncs from all the different threads together into one I/O operation
> without having to flush the entire I/O queue for each one, you can get
> acceptable performance, with ordering. If the system crashes, data that
> hasn't had it's fsync() complete will be the only thing that is lost.

With the above caveat, yes.

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

Re: [sqlite] light weight write barriers

[Nico Williams]

On Wed, Oct 24, 2012 at 7:17 PM, Simon Slavin  wrote:
> A) fsync() doesn't work the way it's meant to on the majority of user 
> platforms.  It effectively does nothing.  Here are typical notes for Windows 
> Server and FreeBSD:

Many systems lie, that's true.  For example: Virtual Box by default
lies about cache flushes.  And consumer hardware typically does as
well.  The systems I'm familiar with implement fsync() correctly as
long as the hardware doesn't lie.  (Nothing much can be done about
lying hardware, especially if the lies go beyond merely not flushing
caches.  Though if cache flushing is the only thing the hardware lies
about then the OS/filesystem can implement a technique for recovery
like the one I described.  Indeed, ZFS does just that.)

But the point is that Richard asked for a light-weight barrier API and
it exists as I described.  Any API explicitly designed for this
purpose could still be implemented incorrectly, or just lie through
its teeth.  SQLite can't help this.  SQLite *can* use available APIs:
when the OS/FS/HW don't lie using these APIs is way better than not
using them, and if the OS/FS/HW lie, well, that's not SQLite's
problem.  At best SQLite could mitigate the lies by... doing what I
suggested: keep around N non-garbage-collected most recent
transactions so the the most recent transaction that can be validated
-meaning its writes hit disk- is taken as the current state of the DB.

Nico

PS: Typically OSes implement fsync(), and all filesystem system calls
via a VFS switch, so the actual implementation of fsync() generally
depends on the actual filesystem in addition to the OS and the
hardware.  A filesystem like a traditional UFS might correctly flush
caches and so on and yet fail to implement fsync() as a Durability
guarantee on account of not having a COW structure on disk, such that
a power failure in the middle of subsequent writes can leave the
filesystem inconsistent.  A filesystem like ZFS doesn't have this
problem.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to select " char in sqlite

[YAN HONG YE]

char bh1[320];
memset(bh1,0,320);
strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
id,partnumber,substr(\'\',1,180) as 
img,pcs from engine where id>7;\" >> n.html");
system(bh1);  //here couldn't work
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] light weight write barriers

[Nico Williams]

On Wed, Oct 24, 2012 at 5:03 PM,   wrote:
> I'm doing some work with rsyslog and it's disk-baded queues and there is a
> similar issue there. The good news is that we can have a version that is
> linux specific (rsyslog is used on other OSs, but there is an existing queue
> implementation that they can use, if the faster one is linux-only, but is
> significantly faster, that's just a win for Linux)
>
> Like what is being described for sqlite, loosing the tail end of the
> messages is not a big problem under normal conditions. But there is a need
> to be sure that what is there is complete up to the point where it's lost.
>
> this is similar in concept to write-ahead-logs done for databases (without
> the absolute durability requirement)
>
> [...]
>
> I am not fully understanding how what you are describing (COW, separate
> fsync threads, etc) would be implemented on top of existing filesystems.
> Most of what you are describing seems like it requires access to the
> underlying storage to implement.
>
> could you give a more detailed explination?

COW is "copy on write", which is actually a bit of a misnomer -- all
COW means is that blocks aren't over-written, instead new blocks are
written.  In particular this means that inodes, indirect blocks, data
blocks, and so on, that are changed are actually written to new
locations, and the on-disk format needs to handle this indirection.

As for fsyn() and background threads... fsync() is synchronous, but in
this scheme we want it to happen asynchronously and then we want to
update each transaction with a pointer to the last transaction that is
known stable given an fsync()'s return.

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

Re: [sqlite] light weight write barriers

[Simon Slavin]

On 24 Oct 2012, at 10:17pm, Nico Williams  wrote:

> That [cache flushing] is not what's being asked for here.  Just a
> light-weight barrier.  My proposal works without having to add new
> system calls: a) use a COW format, b) have background threads doing
> fsync()s, c) in each transaction's root block note the last
> known-committed (from a completed fsync()) transaction's root block,

Nico,

A) fsync() doesn't work the way it's meant to on the majority of user 
platforms.  It effectively does nothing.  Here are typical notes for Windows 
Server and FreeBSD:



"fsync shouldn't be a noop on windows"



"The fsync appears to be a noop."

I'm not knocking any particular OS, they're all like that.  Because actually 
implementing fsync() causes massive slow-downs on all disk writes, and makes 
the computer feel unresponsive to users.

B) Your hard disk lies.  Unless it's a server-level (i.e. expensive) hard disk 
sold especially for server use, it does not enforce in-order writing, either at 
the firmware level or the driver level.  Protocols like SCSI have ways to do 
this correctly, but the overwhelming majority of IDE controllers out there will 
just completely ignore it.

Again, I'm not knocking a particular model, they're all like that.  Because 
write-back caching is so much faster and implementing write-through caching as 
well takes additional programming for something most users will never use.

To propose a new utility which rides above the operating system, both of the 
above have to be remedied.  Your call depends on the OS not lying, and the OS 
depends on the hardware not lying.  You cannot fix this at the level you 
propose.

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

Re: [sqlite] Permalink to latest amalgamation

[Richard Hipp]

On Wed, Oct 24, 2012 at 7:43 PM, Kees Nuyt  wrote:

> >
> >I don't remember the exact path offhand, but fossil web ui has a URL that
> >return the requested checkin as a tarball, no need for a fossil client.
>
> True, but the name of the tarball/zip is not fixed but derived from the
> artefact ID.
>

The tarball/zip *can* be an artifact ID.  But it can also be a symbolic
name like "release" or "trunk" or "version-3.7.14.1".  Ex:

http://www.sqlite.org/src/tarball/sqlite3.tar.gz?uuid=trunk

The link above will always return a tarball of the latest trunk check-in.
Change "trunk" to "release" and you will always get the latest release.
Change "trunk" to "tip" and you will always get the most recent check-in,
regardless of the branch.


>
> The list of releases can be fetched with this URL:
> http://www.sqlite.org/cgi/src/timeline?t=release
>
> The first link leads to the checkin of the most recent release, with
> tarball and zip hyperlinks.
>
> The html is not that hard to parse, the [CDATA[...]] blocks are handy.
>
> There may be easier ways though...
>
> --
> Groet, Cordialement, Pozdrawiam, Regards,
>
> Kees Nuyt
>
> ___
> 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] Permalink to latest amalgamation

[Kees Nuyt]

On Thu, 25 Oct 2012 00:52:56 +0200, Baruch Burstein
 wrote:

>On Thu, Oct 25, 2012 at 12:42 AM, Darren Duncan wrote:
>
>> Kees Nuyt wrote:
>>
>>> On Thu, 25 Oct 2012 00:12:16 +0200, Baruch Burstein
>>>  wrote:
>>>
>>>  Is there a permanent link I can use that will always point to the latest
 amalgamation (or .zip containing it)? I would like to automate a make
 script that will use the latest sqlite.
 I know I can use a link to the latest release in the repo, but that
 means I
 would need to build the amalgamation as part of this make step.

>>>
>>> You make it sound very difficult to build the amalgamation source,
>>> but actually it's just "make sqlite3.c".
>>>
>>> So the easiest way really is to:
>>> * clone the fossil repo (once),
>>> * pull in updates periodically,
>>> * ./configure with the proper options/defines/omits
>>> for whatever your project needs,
>>> * make sqlite3.c, * make your project.
>>>
>>
>> I don't think that answer is appropriate for some common use cases, which
>> may include the original requestor.  Say for example and end user of the
>> DBD::SQLite Perl module that wants to pull in the latest SQLite version to
>> build it against, without having to specify a version.  We shouldn't expect
>> such a user to have a fossil client, they should just be able to pull the
>> amalgamation tarball over the web. -- Darren Duncan
>>
>
>I don't remember the exact path offhand, but fossil web ui has a URL that
>return the requested checkin as a tarball, no need for a fossil client.

True, but the name of the tarball/zip is not fixed but derived from the
artefact ID.

The list of releases can be fetched with this URL:
http://www.sqlite.org/cgi/src/timeline?t=release

The first link leads to the checkin of the most recent release, with
tarball and zip hyperlinks.

The html is not that hard to parse, the [CDATA[...]] blocks are handy.

There may be easier ways though...

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] Permalink to latest amalgamation

[Richard Hipp]

On Wed, Oct 24, 2012 at 6:12 PM, Baruch Burstein wrote:

> Is there a permanent link I can use that will always point to the latest
> amalgamation (or .zip containing it)? I would like to automate a make
> script that will use the latest sqlite.
>

"Latest sqlite" is ambiguous.  Do you mean (1) the most recent check-in,
(2) the most recent check-in on trunk, or (3) the most recent official
release?


> I know I can use a link to the latest release in the repo, but that means I
> would need to build the amalgamation as part of this make step.
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> ___
> 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] Permalink to latest amalgamation

[Baruch Burstein]

On Thu, Oct 25, 2012 at 12:42 AM, Darren Duncan wrote:

> Kees Nuyt wrote:
>
>> On Thu, 25 Oct 2012 00:12:16 +0200, Baruch Burstein
>>  wrote:
>>
>>  Is there a permanent link I can use that will always point to the latest
>>> amalgamation (or .zip containing it)? I would like to automate a make
>>> script that will use the latest sqlite.
>>> I know I can use a link to the latest release in the repo, but that
>>> means I
>>> would need to build the amalgamation as part of this make step.
>>>
>>
>> You make it sound very difficult to build the amalgamation source,
>> but actually it's just "make sqlite3.c".
>>
>> So the easiest way really is to:
>> * clone the fossil repo (once),
>> * pull in updates periodically,
>> * ./configure with the proper options/defines/omits
>> for whatever your project needs,
>> * make sqlite3.c, * make your project.
>>
>
> I don't think that answer is appropriate for some common use cases, which
> may include the original requestor.  Say for example and end user of the
> DBD::SQLite Perl module that wants to pull in the latest SQLite version to
> build it against, without having to specify a version.  We shouldn't expect
> such a user to have a fossil client, they should just be able to pull the
> amalgamation tarball over the web. -- Darren Duncan
>
I don't remember the exact path offhand, but fossil web ui has a URL that
return the requested checkin as a tarball, no need for a fossil client.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Permalink to latest amalgamation

[Darren Duncan]

Kees Nuyt wrote:

On Thu, 25 Oct 2012 00:12:16 +0200, Baruch Burstein
 wrote:


Is there a permanent link I can use that will always point to the latest
amalgamation (or .zip containing it)? I would like to automate a make
script that will use the latest sqlite.
I know I can use a link to the latest release in the repo, but that means I
would need to build the amalgamation as part of this make step.


You make it sound very difficult to build the amalgamation source,
but actually it's just "make sqlite3.c".

So the easiest way really is to:
* clone the fossil repo (once),
* pull in updates periodically,
* ./configure with the proper options/defines/omits
for whatever your project needs,
* make sqlite3.c, 
* make your project.


I don't think that answer is appropriate for some common use cases, which may 
include the original requestor.  Say for example and end user of the DBD::SQLite 
Perl module that wants to pull in the latest SQLite version to build it against, 
without having to specify a version.  We shouldn't expect such a user to have a 
fossil client, they should just be able to pull the amalgamation tarball over 
the web. -- Darren Duncan

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

Re: [sqlite] Permalink to latest amalgamation

[Kees Nuyt]

On Thu, 25 Oct 2012 00:12:16 +0200, Baruch Burstein
 wrote:

> Is there a permanent link I can use that will always point to the latest
> amalgamation (or .zip containing it)? I would like to automate a make
> script that will use the latest sqlite.
> I know I can use a link to the latest release in the repo, but that means I
> would need to build the amalgamation as part of this make step.

You make it sound very difficult to build the amalgamation source,
but actually it's just "make sqlite3.c".

So the easiest way really is to:
* clone the fossil repo (once),
* pull in updates periodically,
* ./configure with the proper options/defines/omits
for whatever your project needs,
* make sqlite3.c, 
* make your project.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

[sqlite] Permalink to latest amalgamation

[Baruch Burstein]

Is there a permanent link I can use that will always point to the latest
amalgamation (or .zip containing it)? I would like to automate a make
script that will use the latest sqlite.
I know I can use a link to the latest release in the repo, but that means I
would need to build the amalgamation as part of this make step.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] light weight write barriers

[Nico Williams]

On Tue, Oct 23, 2012 at 2:53 PM, Vladislav Bolkhovitin
<...@gmail.com> wrote:
>> As most of the time the order we need do not involve too many blocks
>> (certainly a lot less than all the cached blocks in the system or in
>> the disk's cache), that topological order isn't likely to be very
>> complicated, and I image it could be implemented efficiently in a
>> modern device, which already has complicated caching/garbage
>> collection/whatever going on internally. Particularly, it seems not
>> too hard to be implemented on top of SCSI's ordered/simple task mode?

If you have multiple layers involved (e.g., SQLite then the
filesystem, and if the filesystem is spread over multiple storage
devices), and if transactions are not bounded, and on top of that if
there are other concurrent writers to the same filesystem (even if not
the same files) then the set of blocks to write and internal ordering
can get complex.  In practice filesystems try to break these up into
large self-consistent chunks and write those -- ZFS does this, for
example -- and this is aided by the lack of transactional semantics in
the filesystem.

For SQLite with a VFS that talks [i]SCSI directly then things could be
much more manageable as there's only one write transaction in progress
at any given time.  But that's not realistic, except, perhaps, in some
embedded systems.

> Yes, SCSI has full support for ordered/simple commands designed exactly for
> that task: [...]
>
> [...]
>
> But historically for some reason Linux storage developers were stuck with
> "barriers" concept, which is obviously not the same as ORDERED commands,
> hence had a lot troubles with their ambiguous semantic. As far as I can tell
> the reason of that was some lack of sufficiently deep SCSI understanding
> (how to handle errors, believe that ACA is something legacy from parallel
> SCSI times, etc.).

Barriers are a very simple abstraction, so there's that.

> Hopefully, eventually the storage developers will realize the value behind
> ordered commands and learn corresponding SCSI facilities to deal with them.
> It's quite easy to demonstrate this value, if you know where to look at and
> not blindly refusing such possibility. I have already tried to explain it a
> couple of times, but was not successful.

Exposing ordering of lower-layer operations to filesystem applications
is a non-starter.  About the only reasonable thing to do with a
filesystem is add barrier operations.  I know, you're talking about
lower layer capabilities, and SQLite could talk to that layer
directly, but let's face it: it's not likely to.

> Before that happens, people will keep returning again and again with those
> simple questions: why the queue must be flushed for any ordered operation?
> Isn't is an obvious overkill?

That [cache flushing] is not what's being asked for here.  Just a
light-weight barrier.  My proposal works without having to add new
system calls: a) use a COW format, b) have background threads doing
fsync()s, c) in each transaction's root block note the last
known-committed (from a completed fsync()) transaction's root block,
d) have an array of well-known ubberblocks large enough to accommodate
as many transactions as possible without having to wait for any one
fsync() to complete, d) do not reclaim space from any one past
transaction until at least one subsequent transaction is fully
committed.  This obtains ACI- transaction semantics (survives power
failures but without durability for the last N transactions at
power-failure time) without requiring changes to the OS at all, and
with support for delayed D (durability) notification.

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

Re: [sqlite] VTab & xRename

[gwenn]

Thanks for your suggestion Jay.

static sqlite3_module csvModule = {
  0,/* iVersion */
  csvCreate,/* xCreate - create a table */
  csvConnect,   /* xConnect - connect to an existing table */
  csvBestIndex, /* xBestIndex - Determine search strategy */
  csvDisconnect,/* xDisconnect - Disconnect from a table */
  csvDestroy,   /* xDestroy - Drop a table */
  csvOpen,  /* xOpen - open a cursor */
  csvClose, /* xClose - close a cursor */
  csvFilter,/* xFilter - configure scan constraints */
  csvNext,  /* xNext - advance a cursor */
  csvEof,   /* xEof */
  csvColumn,/* xColumn - read data */
  csvRowid, /* xRowid - read data */
  0,/* xUpdate - write data */
  0,/* xBegin - begin transaction */
  0,/* xSync - sync transaction */
  0,/* xCommit - commit transaction */
  0,/* xRollback - rollback transaction */
  0,/* xFindFunction - function overloading */
  0 /* xRename - rename the table */
};

sqlite> .load ./csv.sqlext
sqlite> create virtual table test using csv(test1.csv, ',', USE_HEADER_ROW);
sqlite> select * from test;
1|2|3
a|b|c
a|b|c
a|b|c .. z
a|b|c,d
sqlite> alter table test rename to test1;
sqlite> select * from test;
Error: no such table: test
sqlite> select * from test1;
1|2|3
a|b|c
a|b|c
a|b|c .. z
a|b|c,d
sqlite>

So it's seems that SQLite properly handles virtual table rename even
when xRename is not specified by the module.
Regards.

On Tue, Oct 23, 2012 at 10:50 PM, Jay A. Kreibich  wrote:
> On Tue, Oct 23, 2012 at 10:16:07PM +0200, gwenn scratched on the wall:
>> Hello,
>>
>> The documentation says the xRename function is mandatory:
>> http://sqlite.org/vtab.html#xrename
>> "The xRename method is required for every virtual table implementation."
>>
>> But it seems possible to not specify it:
>>   static const sqlite3_module fts3aux_module = {
>> ...
>>  0,   /* xRename   */
>> ...
>>   };
>
>
>   And when you attempt to rename the table, what happens?
>
>
>   The virtual table interface is advanced, in the sense that there are
>   very few safety nets or double-checks.  It is designed to be used by
>   an intelligent programmer that knows their stuff.  You need to do what
>   the docs say, exactly, or something bad can happen.  That's not to
>   say something bad will happen right away.  The fact that you can assign
>   a NULL function pointer to the xRename() function only means the system
>   is not double-checking your work when you pass in the structure... it
>   does not mean that passing a NULL is allowed.  I strongly suspect that
>   if you do not provide a xRename() function, and someone attempts to
>   rename the table, the whole application will simply crash.  Your fault.
>
>-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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS returns out of memory when use NEAR and OR

[Dan Kennedy]

On 10/24/2012 11:07 PM, Vlad Seryakov wrote:

Hello

For some time already i noticed that when i use NEAR/1 and OR in one
query like SELECT * FROM search WHERE search MATCH 'tom NEAR/1 hanks
or tom hanks'


Are you able to share the database file that you use to reproduce
this? Thanks.

Dan.





i get out of memory error. Running this on 16Gb laptop cannot be
memory issue and the database only has several thousands of records.
Investigating the code i found one place where in fts3EvalNearTest
where it happens:

line 129689 i version 3.7.14.1

nTmp += p->pPhrase->doclist.nList; aTmp = sqlite3_malloc(nTmp*2); if(
!aTmp ){

Adding the check before the malloc solved the problem if (nTmp<= 0)
return res;

Thanks

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



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

Re: [sqlite] SQL logic error or missing database

[Gert Van Assche]

Kees, thanks. I reopened the connection and all is OK now.

thanks for your help,

gert


2012/10/24 Kees Nuyt 

> On Wed, 24 Oct 2012 15:53:39 +0200, Gert Van Assche
>  wrote:
>
> >All, hoping you can help me.
> >
> >I bumped into an "SQL logic error or missing database" error and I don't
> >have a clue why this happens.
> >It happens on the first action I take in a series of all the same actions
> >(updates of a table).
> >I removed the triggers, changed the indexes, checked integrity... still
> >this error.
> >
> >the strange thing is that when I copy the command that triggers this error
> >message to the clipboard and execute it at that time on the DB, the error
> >does not occur.
> >
> > Does anyone have an idea what might cause this
>
> Hard to tell without looking at the actual code. Perhaps the
> path/filename in your _open() statement is not correct (does not point
> to the same file as the command line does), or the open flags/URI
> arguments conflict with the databasefile.
>
> > or how I can detect why this
> > is happening?
>
> Check the return values of all API statements, like _open_*(),
> _prepare_*(), _bind_*(), _step(), 
> to pinpoint where an error first occurs.
>
> And as usual: Reduce your code to the minimum needed to reproduce the
> problem and post it here.
>
> >
> >thanks
> >
> >gert
>
> --
> Groet, Cordialement, Pozdrawiam, Regards,
>
> Kees Nuyt
>
> ___
> 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] FTS returns out of memory when use NEAR and OR

[Vlad Seryakov]

Hello

For some time already i noticed that when i use NEAR/1 and OR in one query like
SELECT * FROM search WHERE search MATCH 'tom NEAR/1 hanks or tom hanks'

i get out of memory error. Running this on 16Gb laptop cannot be memory issue 
and the database only has several thousands of records. Investigating the code 
i found one place where in fts3EvalNearTest where it happens:

line 129689 i version 3.7.14.1

nTmp += p->pPhrase->doclist.nList; 
aTmp = sqlite3_malloc(nTmp*2);
if( !aTmp ){ 

Adding the check before the malloc solved the problem
if (nTmp <= 0) return res;

Thanks

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

Re: [sqlite] how to select " char in sqlite

[Black, Michael (IS)]

If Windows get FART (find and replace text) from here:
http://blog.secaserver.com/2011/07/windows-find-and-replace-text-command-line-utility/
If Unix learn sed:
http://www.thegeekstuff.com/2009/09/unix-sed-tutorial-replace-text-inside-a-file-using-substitute-command/

Then
sqlite3 test.db
create table engine(id,partnumber,pic);
insert into engine values(1,11,'1.jpg');
insert into engine values(2,22,'2.jpg');
insert into engine values(3,33,'3.jpg');
D:\SQLite>sqlite3 -html test.db "select id,partnumber,'XXLTimg 
src='||\"XXQUOTE\"||pic||\"XXQUOTE\"||' height=220XXGT' from eng
ine;"
fart test.html XXGT ">"
fart test.html XXLT "<"
fart test.html XXQUOTE ''"

And you end up with:
1
11


2
22


3
33




Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of YAN HONG YE [yanhong...@mpsa.com]
Sent: Wednesday, October 24, 2012 4:00 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] FW: how to select " char in sqlite

sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\" as img,pcs 
from engine where id>7;" >> n.html
here   \"abc.jpg\" couldn't work.

sqlite3 -html -header t9_engine.db "select id,partnumber,'' as img,pcs from engine where id>7; ">> n.html
Same problem.

strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
id,partnumber,substr(\'\',1,180) as 
img,pcs from engine where id>7;\" >> n.html");
Same problem.


___
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] SQL logic error or missing database

[Simon Slavin]

On 24 Oct 2012, at 3:42pm, Gert Van Assche  wrote:

> I have permissions and I'm using the short string of the
> full path.

Just for testing, try specifying a full path and see what happens.

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

Re: [sqlite] SQL logic error or missing database

[Gert Van Assche]

Simon,

I'm using LUA. I have permissions and I'm using the short string of the
full path.
I'm now investigating something Kees mentioned: "Perhaps the
path/filename in your _open() statement is not correct (does not point
to the same file as the command line does), or the open flags/URI
arguments conflict with the databasefile."

This tool is opening the db via a lua socket but for some actions I have to
use the command line exe (like import & export data -- I don't know how to
do this in the luasqlite framework -- or it is a lot of work to parse the
tables).

I'm not an educated developer, just learned through experience. No doubt I
will learn a lot from this strange error.



2012/10/24 Simon Slavin 

>
> On 24 Oct 2012, at 2:53pm, Gert Van Assche  wrote:
>
> > I bumped into an "SQL logic error or missing database" error and I don't
> > have a clue why this happens.
>
> What language/API are you programming in ?
>
> Are you opening the file with permissions which allow it to be created if
> it doesn't already exist ?
>
> Are you specifying the full file path for the file or are you assuming it
> will be in some default folder ?
>
> 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] how to select "

[Jay A. Kreibich]

On Wed, Oct 24, 2012 at 07:27:57AM +, YAN HONG YE scratched on the wall:
> sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\"
>as img,pcs from engine where id>7;" >> n.html
> here   \"abc.jpg\" couldn't work.

  SQL string literals use single quotes.

   -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] SQL logic error or missing database

[Kees Nuyt]

On Wed, 24 Oct 2012 15:53:39 +0200, Gert Van Assche
 wrote:

>All, hoping you can help me.
>
>I bumped into an "SQL logic error or missing database" error and I don't
>have a clue why this happens.
>It happens on the first action I take in a series of all the same actions
>(updates of a table).
>I removed the triggers, changed the indexes, checked integrity... still
>this error.
>
>the strange thing is that when I copy the command that triggers this error
>message to the clipboard and execute it at that time on the DB, the error
>does not occur.
>
> Does anyone have an idea what might cause this

Hard to tell without looking at the actual code. Perhaps the
path/filename in your _open() statement is not correct (does not point
to the same file as the command line does), or the open flags/URI
arguments conflict with the databasefile.

> or how I can detect why this
> is happening?

Check the return values of all API statements, like _open_*(),
_prepare_*(), _bind_*(), _step(), 
to pinpoint where an error first occurs.

And as usual: Reduce your code to the minimum needed to reproduce the
problem and post it here.

>
>thanks
>
>gert

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] SQLite could not open db in WinRT issue

[Pavel Ivanov]

SQLite expects all paths to be in UTF-8 (non-standard for Win but the
same for all platforms). Is your path in UTF-8 or in some other system
encoding?

Pavel

On Tue, Oct 23, 2012 at 6:28 AM, Václav Jirovský
 wrote:
> Hello,
>
> I tried to use SQLite 3.7.14.1 with sqlite-net 1.0.5 (both official from
> visual studio gallery or NuGet with no modifications) for WinRT.
>
> here is my code to get error:
>
> [Table("Users")]
> public class User
> {
> [PrimaryKey, Unique]
> public int Id { get; set; }
> public string LastName { get; set; }
> public string FirstName { get; set; }
> public int Age { get; set; }
> }
>
> .
>
>  string DbRootPath = ApplicationData.Current.LocalFolder.Path;
>  string path = Path.Combine(DbRootPath, "users.db");
>  var db = new SQLiteAsyncConnection(path);
> *
> *
> *and when I call *
>
>   await db.CreateTableAsync();
>
> *I recieve SQLiteException **Could not open database file:
> C:\Users\Václav\AppData\Local\Packages\xxx\LocalState\users.db
> (CannotOpen)*
> *
> *
> I think it's because Unicode char (á) in my Path, but from WinRT app I am
> allowed to write to folders in this user path only. In Czech is this char
> in name typical and most of people has username their name.
>
> Has anyone seen this?
>
> Thanks,
>
> Vaclav
> --
> ___
> 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] light weight write barriers

[Vladislav Bolkhovitin]

杨苏立 Yang Su Li, on 10/11/2012 12:32 PM wrote:

I am not quite whether I should ask this question here, but in terms
of light weight barrier/fsync, could anyone tell me why the device
driver / OS provide the barrier interface other than some other
abstractions anyway? I am sorry if this sounds like a stupid questions
or it has been discussed before

I mean, most of the time, we only need some ordering in writes; not
complete order, but partial,very simple topological order. And a
barrier seems to be a heavy weighted solution to achieve this anyway:
you have to finish all writes before the barrier, then start all
writes issued after the barrier. That is some ordering which is much
stronger than what we need, isn't it?

As most of the time the order we need do not involve too many blocks
(certainly a lot less than all the cached blocks in the system or in
the disk's cache), that topological order isn't likely to be very
complicated, and I image it could be implemented efficiently in a
modern device, which already has complicated caching/garbage
collection/whatever going on internally. Particularly, it seems not
too hard to be implemented on top of SCSI's ordered/simple task mode?


Yes, SCSI has full support for ordered/simple commands designed exactly for that 
task: to have steady flow of commands even in case when some of them are ordered. 
It also has necessary facilities to handle commands errors without unexpected 
reorders of their subsequent commands (ACA, etc.). Those allow to get full storage 
performance by fully "fill the pipe", using networking terms. I can easily imaging 
real life configs, where it can bring 2+ times more performance, than with queue 
flushing.


In fact, AFAIK, AIX requires from storage to support ordered commands and ACA.

Implementation should be relatively easy as well, because all transports naturally 
have link as the point of serialization, so all you need in multithreaded 
environment is to pass some SN from the point when each ORDERED command created to 
the point when it sent to the link and make sure that no SIMPLE commands can ever 
cross ORDERED commands. You can see how it is implemented in SCST in an elegant 
and lockless manner (for SIMPLE commands).


But historically for some reason Linux storage developers were stuck with 
"barriers" concept, which is obviously not the same as ORDERED commands, hence had 
a lot troubles with their ambiguous semantic. As far as I can tell the reason of 
that was some lack of sufficiently deep SCSI understanding (how to handle errors, 
believe that ACA is something legacy from parallel SCSI times, etc.).


Hopefully, eventually the storage developers will realize the value behind ordered 
commands and learn corresponding SCSI facilities to deal with them. It's quite 
easy to demonstrate this value, if you know where to look at and not blindly 
refusing such possibility. I have already tried to explain it a couple of times, 
but was not successful.


Before that happens, people will keep returning again and again with those simple 
questions: why the queue must be flushed for any ordered operation? Isn't is an 
obvious overkill?


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

Re: [sqlite] FW: how to select " char in sqlite

[Kees Nuyt]

On Wed, 24 Oct 2012 09:00:31 +, YAN HONG YE 
wrote:

>
>sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\" as 
>img,pcs from engine where id>7;" >> n.html
>here   \"abc.jpg\" couldn't work.

Please be more specific about "couldn't work". 
What error message did you get? 

By the way, in SQL, delimiters for string literals are single quotes, 
so you need to code 'abc.jpg'.


>sqlite3 -html -header t9_engine.db "select id,partnumber,''||pic||' \" height=220/>' as img,pcs from engine where id>7; ">> n.html
>Same problem.

Same comment: wat error message do you get?
It works for me (Ubuntu, bash shell) :

$ sqlite3 t9_engine.db
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table engine (id integer,partnumber integer,pic text,pcs
integer);
sqlite> insert into engine values (8,9,'abc',2);
sqlite> .q

$ sqlite3 -html -header t9_engine.db "select id,partnumner,'' as img,pcs from engine where id>7; "
id
partnumner
img
pcs

8
9
img src=  abc  height=220/
2

$ 


>strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
>id,partnumber,substr(\'\',1,180) as 
>img,pcs from engine where id>7;\" >> n.html");
>Same problem.

This just produces a text string (if the code is correct). 
It is bad practice to fork to the sqlite3 commandline tool from within a
program. You are supposed to use the API, either the C-API
http://sqlite.org/c3ref/intro.html or some API provided by a "wrapper"
for the computer language your application is coded in.
By the way, I don't think the substr() is at the right place.

Hope this helps.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] how to select " char in sqlite

[Clemens Ladisch]

Igor Tandetnik wrote:
> YAN HONG YE  wrote:
>> sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\" as 
>> img,pcs from engine where id>7;" >> n.html
>> here   \"abc.jpg\" couldn't work.
>>
>> sqlite3 -html -header t9_engine.db "select id,partnumber,'> '||pic||' \" height=220/>' as img,pcs from engine where
>> id>7; ">> n.html Same problem.
>
> Define "couldn't work". What outcome do you observe, what outcome do you 
> expect, and how do the two differ?

I'd guess the purpose of these queries is to generate an HTML table with images.

sqlite> .mode html
sqlite> select '';
img src=something.jpg


But the sqlite shell always escapes special characters and thus cannot
generate HTML tags.

I'd recommend to export the data in some other format and then reformat
it to HTML.


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

Re: [sqlite] how to select " char in sqlite

[Igor Tandetnik]

YAN HONG YE  wrote:
> sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\" as 
> img,pcs from engine where id>7;" >> n.html
> here   \"abc.jpg\" couldn't work.
> 
> sqlite3 -html -header t9_engine.db "select id,partnumber,' '||pic||' \" height=220/>' as img,pcs from engine where
> id>7; ">> n.html Same problem.

Define "couldn't work". What outcome do you observe, what outcome do you 
expect, and how do the two differ?
-- 
Igor Tandetnik

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

Re: [sqlite] Problem: Index not used with ORDER BY on view

[Clemens Ladisch]

(quoting fixed)
Scholz Maik (CM-AI/PJ-CF42) wrote:
> Richard Hipp wrote:
> > Perhaps we could add a new optimization:
> >
> > IF:
> >(1) both inner and outer queries have an ORDER BY clause, and
> >(2) the inner query omits both LIMIT and OFFSET
> > THEN:
> >drop the ORDER BY from the inner query
> >
> > Such an optimization would cause the index to be used in the case above and
> > I cannot think of an instance where that optimization would lead to
> > incorrect results.
>
> My expectation was, that the actual used ORDER term is something like merge
> from outer to inner orders.

If the inner and outer clauses are on different fields, and if one
relies on the (undocumented!) fact that SQLite's sorting is stable,
then the order of the result indeed is a combination of both.

However, this is guaranteed neither by the SQL standards nor by SQLite,
and many other database engines can use non-stable sorting algorithms.

This new optimization could change some queries, but only in allowed
ways, and the recent covering-index optimizations introduced similar
changes anyway.


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

Re: [sqlite] Problem: Index not used with ORDER BY on view

[Scholz Maik (CM-AI/PJ-CF42)]

Hi,

My expectation was, that the actual used ORDER term is something like merge
from outer to inner orders.
But, this is wrong.

>Perhaps we could add a new optimization:

>IF:
>   (1) both inner and outer queries have an ORDER BY clause, and
>   (2) the inner query omits both LIMIT and OFFSET
>THEN:
>   drop the ORDER BY from the inner query

>Such an optimization would cause the index to be used in the case above and
>I cannot think of an instance where that optimization would lead to
>incorrect results.  On the other hand, I might be overlooking some
>pathological case where this proposed optimization leads to an incorrect
>result.
Sounds not so bad.

Can I add this "new optimization" or is this a bigger change?

Mit freundlichen Grüßen / Best regards

Maik Scholz
CM-AI/PJ-CF42

Tel. +49 5121 49 5391
PC-Fax +49 711 811 505 5391

BeQIK


-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Richard Hipp
Gesendet: Mittwoch, 24. Oktober 2012 13:27
An: General Discussion of SQLite Database
Cc: Klein Thomas (CM-AI/PJ-CF23); EXTERNAL Zeise Ralf (TCP, CM-AI/PJ-CF42); 
Erben Peter (CM-AI/PJ-CF42)
Betreff: Re: [sqlite] Problem: Index not used with ORDER BY on view

On Wed, Oct 24, 2012 at 4:59 AM, Scholz Maik (CM-AI/PJ-CF42) <
maik.sch...@de.bosch.com> wrote:

> Hi,
> I have some strange behavior with the query optimizer.
>
> SQLite version 3.7.7.1 2011-06-28 17:39:05
>
> sqlite> create table t1 (a,b);
> sqlite> insert into t1 (a,b) values (1,2);
> sqlite> insert into t1 (a,b) values (3,4);
>
> sqlite> select * from t1;
> 1|2
> 3|4
>
> sqlite> create index i1 on t1(a);
>
> sqlite> create view v1 as select a,b,a+b as f1 from t1;
> sqlite> create view v2 as select a,b,a+b as f1 from t1 order by (a+b);
> sqlite> create view v3 as select a,b,a+b as f1 from t1 order by f1;
> sqlite> explain query plan select a,b,f1 from v2 where a=3 order by (f1);
> 1|0|0|SCAN TABLE t1 (~100 rows)
> 1|0|0|USE TEMP B-TREE FOR ORDER BY
> 0|0|0|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (a=?) (~7 rows)
> 0|0|0|USE TEMP B-TREE FOR ORDER BY
> =>  Why is index i1 not used?
>

The query-flattener does not run because both the inner and outer query
contain ORDER BY clauses.  See
http://www.sqlite.org/src/artifact/9b759521f8?ln=2724 and the surrounding
context for additional information on the query flattener, its
implementation, and the specific rule that is causing your problems.

Perhaps we could add a new optimization:

IF:
   (1) both inner and outer queries have an ORDER BY clause, and
   (2) the inner query omits both LIMIT and OFFSET
THEN:
   drop the ORDER BY from the inner query

Such an optimization would cause the index to be used in the case above and
I cannot think of an instance where that optimization would lead to
incorrect results.  On the other hand, I might be overlooking some
pathological case where this proposed optimization leads to an incorrect
result.



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

Re: [sqlite] Problem: Index not used with ORDER BY on view

[Richard Hipp]

On Wed, Oct 24, 2012 at 4:59 AM, Scholz Maik (CM-AI/PJ-CF42) <
maik.sch...@de.bosch.com> wrote:

> Hi,
> I have some strange behavior with the query optimizer.
>
> SQLite version 3.7.7.1 2011-06-28 17:39:05
>
> sqlite> create table t1 (a,b);
> sqlite> insert into t1 (a,b) values (1,2);
> sqlite> insert into t1 (a,b) values (3,4);
>
> sqlite> select * from t1;
> 1|2
> 3|4
>
> sqlite> create index i1 on t1(a);
>
> sqlite> create view v1 as select a,b,a+b as f1 from t1;
> sqlite> create view v2 as select a,b,a+b as f1 from t1 order by (a+b);
> sqlite> create view v3 as select a,b,a+b as f1 from t1 order by f1;
> sqlite> explain query plan select a,b,f1 from v2 where a=3 order by (f1);
> 1|0|0|SCAN TABLE t1 (~100 rows)
> 1|0|0|USE TEMP B-TREE FOR ORDER BY
> 0|0|0|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (a=?) (~7 rows)
> 0|0|0|USE TEMP B-TREE FOR ORDER BY
> =>  Why is index i1 not used?
>

The query-flattener does not run because both the inner and outer query
contain ORDER BY clauses.  See
http://www.sqlite.org/src/artifact/9b759521f8?ln=2724 and the surrounding
context for additional information on the query flattener, its
implementation, and the specific rule that is causing your problems.

Perhaps we could add a new optimization:

IF:
   (1) both inner and outer queries have an ORDER BY clause, and
   (2) the inner query omits both LIMIT and OFFSET
THEN:
   drop the ORDER BY from the inner query

Such an optimization would cause the index to be used in the case above and
I cannot think of an instance where that optimization would lead to
incorrect results.  On the other hand, I might be overlooking some
pathological case where this proposed optimization leads to an incorrect
result.



-- 
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] Problem: Index not used with ORDER BY on view

[Simon Slavin]

On 24 Oct 2012, at 9:59am, "Scholz Maik (CM-AI/PJ-CF42)" 
 wrote:

> sqlite> explain query plan select a,b,f1 from v1 where a=3 order by (f1);
> 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)
> 0|0|0|USE TEMP B-TREE FOR ORDER BY
> =>  Why is index i1 not used?

It /does/ use index i1 for the SEARCH step, as it explains in the first line.  
However, you then ask for the answers to be sorted into f1 order, which it 
can't do using that index.  If you ask for them to be sorted into a order ...

sqlite> explain query plan select a,b,f1 from v1 where a=3 order by a;
0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)

it correctly deduces it doesn't need to use a TEMP B-TREE

There's no way to do anything like

CREATE INDEX i2 on t1(a,a+b);

And you cannot index a view.  So there's no way to create an ideal index for 
your select on a view

select a,b,f1 from v1 where a=3 order by (f1);

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

Re: [sqlite] Mac development question

[Simon Slavin]

On 24 Oct 2012, at 5:29am, Igor Korot  wrote:

> It looks like you are "Apple person" that I got suggeted to talk to. ;-)
> The situation is as follows: I am developing an application that will
> utilize not just SQLite but some other library.
> According to the "development standards" on *nix-like systems programs
> should use dynamic linking (use .so), and
> not static linking (not use .a). Since Apple Mac OS X follows *nix,
> this will be the approach.
> According to the same guidelines all those libraries should go to
> /usr/lib (or /usr/local/lib).

Well, for SQLite the recommendation is that you just compile the .h and .c 
SQLite files into your application.  Doesn't need a library.  And it ensures 
that you know which version of SQLite your app is using.

> Now how else I can place everything in the proper place without
> installer/distribution package? On Mac they have
> dmg extension IIRC.

For other libraries you want to install, do what I previously described and 
include a copy of the library in your application.  When the application is 
run, it looks for a copy of the library in the right place, and if it doesn't 
find one it copies its own copy from its bundle into there.  In other words, 
your application does its own installation of any support files (including 
libraries) that it needs, if they don't already exist.  Then, if your 
programming language makes it necessary, it restarts itself so it can use them.

My /usr/lib folder does have files in.  They have '.dylib' extensions, not .so, 
except for the PAM stuff which is '.so.2'.  I don't know what that means.

> Now AFAICS, those distribution packages (or dmg) files can be called
> installation packages.

dmg is a disk image.  They can be compressed which makes for smaller downloads, 
which is why they're popular.  You might distribute your application, 
documentation, sample files, etc. all in one disk image.  That's standard.  But 
application installers as separate apps or packages are frowned on in the Mac 
community.  They're used only when you have a large suite of applications all 
of which share common components, e.g. Microsoft Office, where building support 
installation logic into each individual application would be wasteful.

> This will be one copy for all users.
> The database will be populated originally by me and will be supplied
> alone with the application bundle and those additional library.

You explained it clearly now.  Your application can include a copy of that file 
with starter information inside its own bundle.  The writable version belongs 
in the shared application support folder, which on an English-based standalone 
locally-mounted Mac is

/Library/Application Support/

However, there is a system call which use should use to get that path, and the 
system call takes into account what your user has named your application, what 
language they are using, and whether they have a non-standard setup which keeps 
their support files on a network drive or something.  So rather than hardwire 
this path into your app you should use the system call

URLsForDirectory:inDomains:

and feed it

NSApplicationSupportDirectory

as described in the 'Locating Items in the Standard Directories' section of



Listing 2-1 on that page is a pre-written function you can use to find exactly 
that directory we've been discussing.

Hope this helps.  We have drifted far away from matters to do with SQLite, so 
if you want to pursue the more Maccish side of this, you might email me 
directly.

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

[sqlite] FW: how to select " char in sqlite

[YAN HONG YE]

sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\" as img,pcs 
from engine where id>7;" >> n.html
here   \"abc.jpg\" couldn't work.

sqlite3 -html -header t9_engine.db "select id,partnumber,'' as img,pcs from engine where id>7; ">> n.html
Same problem.

strcpy(bh1,"sqlite3 -html -header t9_engine.db \"select 
id,partnumber,substr(\'\',1,180) as 
img,pcs from engine where id>7;\" >> n.html");
Same problem.


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

[sqlite] Problem: Index not used with ORDER BY on view

[Scholz Maik (CM-AI/PJ-CF42)]

Hi,
I have some strange behavior with the query optimizer.

SQLite version 3.7.7.1 2011-06-28 17:39:05

sqlite> create table t1 (a,b);
sqlite> insert into t1 (a,b) values (1,2);
sqlite> insert into t1 (a,b) values (3,4);

sqlite> select * from t1;
1|2
3|4

sqlite> create index i1 on t1(a);

sqlite> create view v1 as select a,b,a+b as f1 from t1;
sqlite> create view v2 as select a,b,a+b as f1 from t1 order by (a+b);
sqlite> create view v3 as select a,b,a+b as f1 from t1 order by f1;

sqlite> explain query plan select a,b,f1 from v1 where a=3;
0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)
=>  Perfect, index i1 is used!

sqlite> explain query plan select a,b,f1 from v1 where a=3 order by (f1);
0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
=>  Why is index i1 not used?

sqlite> explain query plan select a,b,f1 from v2 where a=3;
0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
=>  Perfect, index i1 is used!

sqlite> explain query plan select a,b,f1 from v2 where a=3 order by (f1);
1|0|0|SCAN TABLE t1 (~100 rows)
1|0|0|USE TEMP B-TREE FOR ORDER BY
0|0|0|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (a=?) (~7 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
=>  Why is index i1 not used?

sqlite> explain query plan select a,b,f1 from v3 where a=3;
0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
=>  Perfect, index i1 is used!

sqlite> explain query plan select a,b,f1 from v3 where a=3 order by (f1);
1|0|0|SCAN TABLE t1 (~100 rows)
1|0|0|USE TEMP B-TREE FOR ORDER BY
0|0|0|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (a=?) (~7 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
=>  Why is index i1 not used?

Why do I see I different query plan, when a query on a view is done with "ORDER 
BY".
How can I force using the index i1?

Thanks in advance.

Maik


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

[sqlite] how to select "

[YAN HONG YE]

sqlite3 -html -header t9_engine.db "select id,partnumber,\"abc.jpg\" as img,pcs 
from engine where id>7;" >> n.html
here   \"abc.jpg\" couldn't work.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users