Re: [sqlite] [System.Data.SQLite] Using sqlite3.dll instead of Interop.dll - what's the trade-off?

[Joe Mistachkin]

John Reynolds wrote:
>
> My question is: what are the trade-offs of not using
> SQLite.Interop.dll? 
> 

There are various compile-options and extensions baked into the
"SQLite.Interop.dll" that are not enabled and/or included by
default with "sqlite3.dll".

One that is somewhat important, is the "vtshim" extension.  It
is required if you want to implement a virtual table in managed
code.  It's also fairly tightly integrated into the resulting
"SQLite.Interop.dll", by necessity.

It is possible to compile the "SQLite.Interop.dll" for Linux,
Mac OS X, and probably other POSIX compliant systems, using
the following build script:

https://urn.to/r/7C

--
Joe Mistachkin @ https://urn.to/r/mistachkin

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

[sqlite] [System.Data.SQLite] Using sqlite3.dll instead of Interop.dll - what's the trade-off?

[John Reynolds]

Hi,

(Sorry if this becomes a double post - I first tried posting from Nabble)

I've compiled System.Data.SQLite.dll with the
"/property:UseSqliteStandard=true" option, so that it will load
sqlite3.dll (or it's Linux equivalent) instead of SQLite.Interop.dll.

The advantage of this is that you can run the exact same code on Windows
and Mono+Linux without resorting to using the rarely updated
Mono.Data.SQLite package (about 1.5 years old right now, and with
unfixed bugs critical to my application).

My question is: what are the trade-offs of not using SQLite.Interop.dll?

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

Re: [sqlite] WHERE col IN tab

[Richard Hipp]

On 9/16/16, Dominique Devienne  wrote:
>
> Is that <> SQL standard?

That feature was added to SQLite on 2004-01-15
(http://sqlite.org/src/timeline?c=01874d25).  I do not recall why I
added it.

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

Re: [sqlite] WHERE col IN tab

[Dominique Devienne]

On Fri, Sep 16, 2016 at 6:00 PM, J Decker  wrote:

> but probably what you mean is...
>

I didn't mean anything. I asked a question about an unusual syntax.


> SELECT * FROM t1 join T2 on x=y;
> SELECT * FROM t1 join (select y from t2) on x=y
>

A join works too, but that's beside the point.

Logically I don't see any difference with <> and a join
when not accessing columns from the "joined" on column in the select list.
A good query planner will use as good a plan in both situation. but I'm not
a SQL expert.

select * from table where colName in (1,2,3,4)  /// woud return rows where
> some column has a value of 1,2,3 or 4
>

And? if those values are rows in a single-column table, as I already
demonstrated
in the original post, that's exactly the same as a literal list (or a join)
logically.

The question was about that <> syntax SQLite allows. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how is "pragma threads = 4" working

[J Decker]

that is a single sql statement though ; it doesn't spread itself amongst
threads, the compile option says 'limits number of threads' so if you had
an application (not sqlite shell) that had threads it could have a limit?

On Fri, Sep 16, 2016 at 5:13 AM, Adam Devita  wrote:

> Have you proven that the cpu is the bottleneck? Sorting a million rows
> seems like a lot, but even older single core cpus may be capable of 2
> billion ops per second. [I apologize if this has been sorted out
> already I've got about 2 days of history on the thread]
>
> regards,
> Adam DeVita
>
> On Fri, Sep 16, 2016 at 7:56 AM, Stephen Chrzanowski 
> wrote:
>
> > Although programmatically easily done, from the SQLite point of view,
> what
> > if that query, sans LIMIT, were a subquery, and the limit was put out on
> > the outside?  Would the inner query execute, use all the threads, then
> > return just one row on the outer?
> >
> > On Fri, Sep 16, 2016 at 7:29 AM, Richard Hipp  wrote:
> >
> > > On 8/14/16, Венцислав Русев  wrote:
> > > > My computer has 4 cores. I have compile sqlite like this "gcc
> > > > -DSQLITE_MAX_WORKER_THREADS=4 -DSQLITE_DEFAULT_WORKER_THREADS=4
> > shell.c
> > > > sqlite3.c -lpthread -ldl -o sqlite3". I made some tests and found
> that
> > > > "pragma threads = 4" doesn't decrease runtime of the query that
> sorts 1
> > > > milion records.
> > > >
> > > > SQLite version 3.8.8
> > > > sqlite> pragma threads;
> > > > 4
> > > > sqlite> CREATE TABLE event (
> > > >  ID INTEGER PRIMARY KEY NOT NULL,
> > > >  date   INTEGER NOT NULL,
> > > >  value  INTEGER NOT NULL );
> > > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM event ORDER BY date LIMIT
> 1;
> > >
> > > The algorithm used for "ORDER BY ... LIMIT N" uses much less memory
> > > than a full-up "ORDER BY" because is only keeps track of the top N
> > > entries seen so far, discarding the rest.  But it also only uses a
> > > single thread.  If you want multiple threads to be used, you'll need
> > > to drop the LIMIT, though I imagine that would defeat your purpose,
> > > no?
> > >
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> --
> VerifEye Technologies Inc.
> 151 Whitehall Dr. Unit 2
> Markham, ON
> L3R 9T1
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WHERE col IN tab

[J Decker]

IN is for sets, not another table.  I'm surprised sqlite didn't thrown an
error but probably what you mean is...

SELECT * FROM t1 join T2 on x=y;

SELECT * FROM t1 join (select y from t2) on x=y


select * from table where colName in (1,2,3,4)  /// woud return rows where
some column has a value of 1,2,3 or 4


On Fri, Sep 16, 2016 at 7:59 AM, Dominique Devienne 
wrote:

> Reading https://www.sqlite.org/src/tktview/0eab1ac7591f,
> (from a very recent thread) I was surprised to read that syntax.
>
> So I tried it in SQLite, and it works as shown in the ticket:
>
> C:\Users\DDevienne>sqlite3
> SQLite version 3.10.2 2016-01-20 15:27:19
> sqlite> CREATE TABLE t1(x INTEGER PRIMARY KEY NOT NULL, a,b,c,d);
> sqlite> CREATE TABLE t2(y INT);
> sqlite> insert into t1 values (1, 1,1,1,1), (2, 2,2,2,2);
> sqlite> insert into t2 values (2);
> sqlite> SELECT * FROM t1 WHERE x IN t2;
> 2|2|2|2|2
> sqlite> SELECT * FROM t1 WHERE x IN (select y from t2);
> 2|2|2|2|2
>
> But when I try the same thing with Oracle in SQL Dev OTOH, it fails:
>
> create table t (x number primary key, a number)
> create table tt (y number)
> SELECT * FROM t WHERE x IN tt
> ORA-00904: "TT": invalid identifier
> SELECT * FROM t WHERE x IN (select y from tt)
> OK (no rows)
>
> Is that <> SQL standard?
> Or an SQLite specific extension? Available in other RDBMs?
>
> Just curious. Thanks, --DD
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WHERE col IN tab

[Dominique Devienne]

Reading https://www.sqlite.org/src/tktview/0eab1ac7591f,
(from a very recent thread) I was surprised to read that syntax.

So I tried it in SQLite, and it works as shown in the ticket:

C:\Users\DDevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
sqlite> CREATE TABLE t1(x INTEGER PRIMARY KEY NOT NULL, a,b,c,d);
sqlite> CREATE TABLE t2(y INT);
sqlite> insert into t1 values (1, 1,1,1,1), (2, 2,2,2,2);
sqlite> insert into t2 values (2);
sqlite> SELECT * FROM t1 WHERE x IN t2;
2|2|2|2|2
sqlite> SELECT * FROM t1 WHERE x IN (select y from t2);
2|2|2|2|2

But when I try the same thing with Oracle in SQL Dev OTOH, it fails:

create table t (x number primary key, a number)
create table tt (y number)
SELECT * FROM t WHERE x IN tt
ORA-00904: "TT": invalid identifier
SELECT * FROM t WHERE x IN (select y from tt)
OK (no rows)

Is that <> SQL standard?
Or an SQLite specific extension? Available in other RDBMs?

Just curious. Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 issue report: sqlite3 hung-up on DELETE

[Richard Hipp]

Your best work-around is to fix your schema.  Do *not* say

UNIQUE PRIMARY KEY

That is redundant.  PRIMARY KEYs are always UNIQUE.  Just say PRIMARY
KEY and omit the UNIQUE.

Of course, SQLite should be able to deal with this redundancy without
a dramatic slowdown.  That problem will be fixed in the 3.15.0
release.  We will also add test cases to try to prevent a recurrence
of this or similar problems.

But in the meantime, just remove the UNIQUE from all your PRIMARY KEYs
and your performance issues should go away.  For all versions of
SQLite.

On 9/16/16, Richard Hipp  wrote:
> On 9/16/16, Takasumi Iwamoto  wrote:
>> Hello SQLite devs,
>>
>> We've found a hung-up issue in the current sqlite3.
>> Could you please read the below issue report?
>
> Thanks for the bug report.  The ticket for this problem is here:
> https://www.sqlite.org/src/tktview/0eab1ac7591f
>
> --
> D. Richard Hipp
> d...@sqlite.org
>


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

Re: [sqlite] sqlite3 issue report: sqlite3 hung-up on DELETE

[Richard Hipp]

On 9/16/16, Takasumi Iwamoto  wrote:
> Hello SQLite devs,
>
> We've found a hung-up issue in the current sqlite3.
> Could you please read the below issue report?

Thanks for the bug report.  The ticket for this problem is here:
https://www.sqlite.org/src/tktview/0eab1ac7591f

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

[sqlite] Session documentation: Invalid link for sqlite3_changegroup

[Ralf Junker]

The invalid link is in red font. Reference:

https://www.sqlite.org/sessionintro.html#extended_functionality

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

Re: [sqlite] how is "pragma threads = 4" working

[Adam Devita]

Have you proven that the cpu is the bottleneck? Sorting a million rows
seems like a lot, but even older single core cpus may be capable of 2
billion ops per second. [I apologize if this has been sorted out
already I've got about 2 days of history on the thread]

regards,
Adam DeVita

On Fri, Sep 16, 2016 at 7:56 AM, Stephen Chrzanowski 
wrote:

> Although programmatically easily done, from the SQLite point of view, what
> if that query, sans LIMIT, were a subquery, and the limit was put out on
> the outside?  Would the inner query execute, use all the threads, then
> return just one row on the outer?
>
> On Fri, Sep 16, 2016 at 7:29 AM, Richard Hipp  wrote:
>
> > On 8/14/16, Венцислав Русев  wrote:
> > > My computer has 4 cores. I have compile sqlite like this "gcc
> > > -DSQLITE_MAX_WORKER_THREADS=4 -DSQLITE_DEFAULT_WORKER_THREADS=4
> shell.c
> > > sqlite3.c -lpthread -ldl -o sqlite3". I made some tests and found that
> > > "pragma threads = 4" doesn't decrease runtime of the query that sorts 1
> > > milion records.
> > >
> > > SQLite version 3.8.8
> > > sqlite> pragma threads;
> > > 4
> > > sqlite> CREATE TABLE event (
> > >  ID INTEGER PRIMARY KEY NOT NULL,
> > >  date   INTEGER NOT NULL,
> > >  value  INTEGER NOT NULL );
> > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM event ORDER BY date LIMIT 1;
> >
> > The algorithm used for "ORDER BY ... LIMIT N" uses much less memory
> > than a full-up "ORDER BY" because is only keeps track of the top N
> > entries seen so far, discarding the rest.  But it also only uses a
> > single thread.  If you want multiple threads to be used, you'll need
> > to drop the LIMIT, though I imagine that would defeat your purpose,
> > no?
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how is "pragma threads = 4" working

[Stephen Chrzanowski]

Although programmatically easily done, from the SQLite point of view, what
if that query, sans LIMIT, were a subquery, and the limit was put out on
the outside?  Would the inner query execute, use all the threads, then
return just one row on the outer?

On Fri, Sep 16, 2016 at 7:29 AM, Richard Hipp  wrote:

> On 8/14/16, Венцислав Русев  wrote:
> > My computer has 4 cores. I have compile sqlite like this "gcc
> > -DSQLITE_MAX_WORKER_THREADS=4 -DSQLITE_DEFAULT_WORKER_THREADS=4 shell.c
> > sqlite3.c -lpthread -ldl -o sqlite3". I made some tests and found that
> > "pragma threads = 4" doesn't decrease runtime of the query that sorts 1
> > milion records.
> >
> > SQLite version 3.8.8
> > sqlite> pragma threads;
> > 4
> > sqlite> CREATE TABLE event (
> >  ID INTEGER PRIMARY KEY NOT NULL,
> >  date   INTEGER NOT NULL,
> >  value  INTEGER NOT NULL );
> > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM event ORDER BY date LIMIT 1;
>
> The algorithm used for "ORDER BY ... LIMIT N" uses much less memory
> than a full-up "ORDER BY" because is only keeps track of the top N
> entries seen so far, discarding the rest.  But it also only uses a
> single thread.  If you want multiple threads to be used, you'll need
> to drop the LIMIT, though I imagine that would defeat your purpose,
> no?
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3 issue report: sqlite3 hung-up on DELETE

[Takasumi Iwamoto]

Hello SQLite devs,

We've found a hung-up issue in the current sqlite3.
Could you please read the below issue report?


=== Summary of the issue ===
For the specified DB, sqlite3 hung-up after invoking the specified  
DELETE query.

Then CPU usage keeps 100% until killing the process.


=== Affected versions ===
3.12.0
3.12.1
3.12.2
3.13.0
3.14.0
3.14.1
3.14.2

We've confirmed that 3.11.1 is OK and 3.12.0 is NG.
It seems that the issue is introduced from 3.12.0.


=== How to reproduce ===
1. Prepare Ubuntu 16.04.1 LTS.
Note: You may be able to confirm the issue on any your environments  
since this issue also occurs on armv7 embedded device.


In the Ubuntu terminal,

2. Build sqlite3:
$ wget https://www.sqlite.org/2016/sqlite-autoconf-3140200.tar.gz
$ tar -xf sqlite-autoconf-3140200.tar.gz
$ cd sqlite-autoconf-3140200
$ ./configure; make
$ cd ../

3. Get the DB and query text file from the following URL using your web  
browser:

https://goo.gl/J2lyef
Download hungup.db.xz and query_posing_hungup.txt.
Places the downloaded files on the same directory as  
sqlite-autoconf-3140200.tar.gz.

Note: You can download the above files without creating dropbox account.

4. Reproduce the issue:
$ xz -d ./hungup.db.xz
$ cat ./query_posing_hungup.txt | ./sqlite-autoconf-3140200/sqlite3  
./hungup.db

  -> reproduced!


Thank you in advance.

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

Re: [sqlite] how is "pragma threads = 4" working

[Richard Hipp]

On 8/14/16, Венцислав Русев  wrote:
> My computer has 4 cores. I have compile sqlite like this "gcc
> -DSQLITE_MAX_WORKER_THREADS=4 -DSQLITE_DEFAULT_WORKER_THREADS=4 shell.c
> sqlite3.c -lpthread -ldl -o sqlite3". I made some tests and found that
> "pragma threads = 4" doesn't decrease runtime of the query that sorts 1
> milion records.
>
> SQLite version 3.8.8
> sqlite> pragma threads;
> 4
> sqlite> CREATE TABLE event (
>  ID INTEGER PRIMARY KEY NOT NULL,
>  date   INTEGER NOT NULL,
>  value  INTEGER NOT NULL );
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM event ORDER BY date LIMIT 1;

The algorithm used for "ORDER BY ... LIMIT N" uses much less memory
than a full-up "ORDER BY" because is only keeps track of the top N
entries seen so far, discarding the rest.  But it also only uses a
single thread.  If you want multiple threads to be used, you'll need
to drop the LIMIT, though I imagine that would defeat your purpose,
no?

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

[sqlite] SQLite 3.14.2 autoconf (shell) doesn't build with SQLITE_OMIT_AUTHORIZATION, undefined reference

[Jose Arroyo]

Hello everyone,

I couldn't find this in the existing bug list so I decided to send this
email.

I downloaded the latest SQLite autoconf version from
https://www.sqlite.org/2016/sqlite-autoconf-3140200.tar.gz and tried
building it using this compile option, doing

CFLAGS="-DSQLITE_OMIT_AUTHORIZATION" ./configure ; make

Make failed, with the following error:

sqlite3-shell.o: In function `do_meta_command':
shell.c:(.text+0x6c91): undefined reference to `sqlite3_set_authorizer'
shell.c:(.text+0x6cb2): undefined reference to `sqlite3_set_authorizer'
collect2: error: ld returned 1 exit status
make: *** [sqlite3] Error 1

Which makes sense, because according to the SQLite doc if
SQLITE_OMIT_AUTHORIZATION is defined, then "The sqlite3_set_authorizer()
 API function is not
present in the library"

The following code was added  in shell.c (compared to version 3.12.2):
In do_meta_command:
...
  if( c=='a' && strncmp(azArg[0], "auth", n)==0 ){
if( nArg!=2 ){
  raw_printf(stderr, "Usage: .auth ON|OFF\n");
  rc = 1;
  goto meta_command_exit;
}
open_db(p, 0);
if( booleanValue(azArg[1]) ){
  sqlite3_set_authorizer(p->db, shellAuth, p);
}else{
  sqlite3_set_authorizer(p->db, 0, 0);
}
  }else
...
Which doesn't seem to be protected against SQLITE_OMIT_AUTHORIZATION being
defined.

Building directly with ./configure ; make   finished correctly.

If it makes I difference, I'm running Ubuntu 14.04, gcc 4.8.4, libtool
2.4.2 (hehe). The whole build output is the following:

z$ CFLAGS="-DSQLITE_OMIT_AUTHORIZATION" ./configure ;
make
[16-09-16 9:43]
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking whether make supports nested variables... yes
checking for style of include used by make... GNU
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking whether gcc understands -c and -o together... yes
checking dependency style of gcc... gcc3
checking for special C compiler options needed for large files... no
checking for _FILE_OFFSET_BITS value needed for large files... no
checking for gcc... (cached) gcc
checking whether we are using the GNU C compiler... (cached) yes
checking whether gcc accepts -g... (cached) yes
checking for gcc option to accept ISO C89... (cached) none needed
checking whether gcc understands -c and -o together... (cached) yes
checking dependency style of gcc... (cached) gcc3
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking how to print strings... printf
checking for a sed that does not truncate output... /bin/sed
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for fgrep... /bin/grep -F
checking for ld used by gcc... /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld... yes
checking for BSD- or MS-compatible name lister (nm)... /usr/bin/nm -B
checking the name lister (/usr/bin/nm -B) interface... BSD nm
checking whether ln -s works... yes
checking the maximum length of command line arguments... 1572864
checking how to convert x86_64-unknown-linux-gnu file names to
x86_64-unknown-linux-gnu format... func_convert_file_noop
checking how to convert x86_64-unknown-linux-gnu file names to toolchain
format... func_convert_file_noop
checking for /usr/bin/ld option to reload object files... -r
checking for objdump... objdump
checking how to recognize dependent libraries... pass_all
checking for dlltool... no
checking how to associate runtime and link libraries... printf %s\n
checking for ar... ar
checking for archiver @FILE support... @
checking for strip... strip
checking for ranlib... ranlib
checking command to parse /usr/bin/nm -B output from gcc object... ok
checking for sysroot... no
checking for a working dd... /bin/dd
checking how to truncate binary pipes... /bin/dd bs=4096 count=1
checking for mt... mt
checking if mt is a manifest tool... no
checking how to run the C preprocessor... gcc -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking for dlfcn.h... yes
checking for objdir... .libs
checking if gcc supports -fno-rtti -fno-exceptions... no
checking for gcc option to produce PIC... 

Re: [sqlite] how is "pragma threads = 4" working

[Simon Slavin]

On 15 Aug 2016, at 1:02am, Венцислав Русев  wrote:

> sqlite doesn't use these "auxiliary threads" that sqlite docs talks about and 
> the runtime of that query is the same with or without "pragma threads = 4".

I cannot solve your problem, but this information may help the person who does.

Which OS (include rough version number) are you running ?

What does "PRAGMA compile_options" say about THREADSAFE ?

Let's suppose that SQLite does actually launch those threads but they make no 
change in the time the command takes for some reason.  Does your OS have a 
process tracker which can show you how many threads are in use ?  For instance, 
in macOS I'd look at the "Activity Monitor.app" main window.  In Windows I'd 
use Task Manager, select the process and click "Properties".  You may have to 
increase the table size to make it take long enough to monitor.

I'm just looking for some confirmation apart from the time taken.

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

[sqlite] how is "pragma threads = 4" working

[Венцислав Русев]

My computer has 4 cores. I have compile sqlite like this "gcc 
-DSQLITE_MAX_WORKER_THREADS=4 -DSQLITE_DEFAULT_WORKER_THREADS=4 shell.c 
sqlite3.c -lpthread -ldl -o sqlite3". I made some tests and found that 
"pragma threads = 4" doesn't decrease runtime of the query that sorts 1 
milion records.


SQLite version 3.8.8
sqlite> pragma threads;
4
sqlite> CREATE TABLE event (
ID INTEGER PRIMARY KEY NOT NULL,
date   INTEGER NOT NULL,
value  INTEGER NOT NULL );
sqlite> EXPLAIN QUERY PLAN SELECT ID FROM event ORDER BY date LIMIT 1;
0|0|0|SCAN TABLE event
0|0|0|USE TEMP B-TREE FOR ORDER BY
sqlite> SELECT ID FROM event ORDER BY date LIMIT 1;
4101021
Run Time: real 2.493 user 2.426000 sys 0.049000
sqlite> pragma threads = 0;
0
sqlite> SELECT ID FROM event ORDER BY date LIMIT 1;
4101021
Run Time: real 2.484 user 2.421000 sys 0.044000

To sum it up I have:
  - multi core cpu;
  - sqlite that is compiled to use "auxiliary threads";
  - a table with 1 million records;
  - a query that scans through the table, sorts all records and outputs 
the first ID.


sqlite doesn't use these "auxiliary threads" that sqlite docs talks 
about and the runtime of that query is the same with or without "pragma 
threads = 4".


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

Re: [sqlite] Complicated join

[Deon Brewis]

OVER PARTITION BY ...


One can dream...

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Raymond
Sent: Thursday, September 15, 2016 1:47 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Complicated join

Can it be done in SQL? Yes.

In any sort of pretty or efficient manner? Ehhh, maybe?

I came up with something that seems to work with the small sample cases that I 
came up with to try and cover your requirements there, but it's got a couple 
levels of CTE's with long "where not exists..." clauses etc, and I've probably 
missed something. If you could provide a sample set of insert statements to 
paste in along with "here's what I hope to see at the end from this" that would 
help out.

Also, when you ask "Can this be done in SQL?" are you asking...
-in a single statement?
-in only SQL, but multiple statements are ok (such as using intermediate temp 
tables)?
-with an initial SQL query, but then the ability to muck about with the 
returned results in the language of your choice thereafter?
-something else?

Thanks,

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Bicking
Sent: Thursday, September 15, 2016 11:53 AM
To: SQLite Mailing List
Subject: [sqlite] Complicated join

I have two tables:
CREATE TABLE M ( CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY 
KEY(CombinedKeyFields, EvtNbr, TransDate));CREATE TABLE E  ( CombineKeyFields, 
EvtNbr, TransDate, OtherFields, PRIMARY KEY(CombinedKeyFields, EvtNbr, 
TransDate)); "CombinedKeyFields" is shorthand for a combination of about a half 
dozen fields in the primary key."TransDate" is an integer storing a proprietary 
date sequence number, where an older date is always less than a newer date Now, 
I want to do E LEFT JOIN M
(1) The CombinedKeyFields must always match in each table

(2) Match using the EvtNbr, but if no match, use the lowest M.EvtNbr that 
matches the CombinedKeyFields

(3) Match using the TransDate but if no exact match, match on the M.TransDate 
that is less than the E.TransDate but greater than the prior E.TransDate For 
M.TransDate = 94E.TransDate = 96 will match to 94but E.TransDate = 98 will have 
no match because 94 is less than the prior trans at 96..The idea is to find the 
closest date that matches that couldn't be matched to another record.
All this data is coming from upstream data, so this is the data we have on 
hand, though the schema for this reporting package can still be changed, but I 
would have to justify the change by saying the report can only be done with the 
change...
Can this join be done in SQL?
If this were an inner join, I believe I could use CASE statements in the WHERE 
clause, but I'm not sure that would work moving it to the JOIN... ON clause.
Any advice or help is much appreciated.
Thanks,David
Saying a prayer that the email gods won't wrap all these lines together in to 
an unreadable mess like the last time I asked for advice here...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users