[sqlite] multilingual FTS5 stemmer

[Abilio Marques]

Past year I was following the progress of FTS5 with the idea of making a
multilingual stemmer extension. Due to being busy in other things I lost
track of it, until a couple of weeks ago, when I decided to upgrade the
code I had made back then.

It uses the Snowball generated libstemmer. It works pretty much as porter
stemmer (actually, Snowball is Mr. Porter's work). When you create the
table, you use tokenize = 'snowball language_here'.

This weekend I decided to publish it as is, in case someone found it useful
too. Not even 48 hours after that, I got questioned: how do I use multiple
languages? (I had the same question too).

I've been thinking about the right way to do it (aka, more close to perfect
results), and I guess the most obvious would be to get the language from a
field in the query each time the stemmer is called. But I don't know if
it's possible, and how to do it in a clean way.

Any suggestions on how to proceed?

PS: if anyone wants to use what I've got so far: https://github.com/
abiliojr/fts5-snowball
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Tcl copy command

[Andy Goth]

I'm trying to import databases from CSV into an in-memory database.
These CSV files contain quoted delimiters (a,"b,c",d is three columns),
plus they contain a header row.  These two issues are not handled by the
SQLite Tcl extension's copy command.  The SQLite shell's .import command
used to have the same limitations but was improved, yet the SQLite Tcl
extension copy command has not kept pace.

Is there any interest in updating the SQLite Tcl extension copy command
to match the capability of the SQLite shell's .import command?

It makes sense to me, but the details are challenging.  Adding CSV
support to the shell's .import command meant adding a nontrivial amount
of code, and I don't like code duplication, so I'd prefer that most of
the underlying implementation be shared.  Yet it's inappropriate for CSV
parsing to be part of the SQLite core, and I don't think there's a
common shell library at this point, so either one would have to be made,
or CSV would have to be spun off to be its own object file, linked into
both the shell and the Tcl extension.

Yet as complicated as all that sounds, there's more.  The shell's
.import command needs to directly call stdio functions like fopen(), but
a modern implementation of the Tcl extension copy command should use the
Tcl I/O subsystem to leverage Tcl channels.  The result would be
virtualization not only of the input file format ("ASCII" vs. CSV field
readers) but also the underlying I/O.  Thankfully, the only I/O function
called by the field readers is fgetc(), so it shouldn't be too much work
to replace that with a call to a function pointer inside the ImportCtx
structure, through which any reasonable VFS can be invoked.

There are more details to be discussed, e.g. backward compatibility and
what to do about zFile, but for now I'm trying to raise awareness, gauge
interest, and document my first impressions.

See also my post from 2005 which got no replies.  I've asked about this
basic issue before.
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg11195.html

Back to my immediate situation.  I could go off and try implementing the
above, but I could also get what I need by not having the database be
in-memory.  If it's on disk, I can alternate between [exec]'ing the
SQLite shell to import and loading it using the Tcl extension.  Far from
clean, but it would work right now.

And last, a question.  Are there any other functionalities common to the
SQLite shell and Tcl extension which could become common code?

-- 
Andy Goth | 



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_bind_text with empty sting....

[Mr Bob]

I have a table with a not null column...

 

Some of the values are empty string and some have more lengthy values :-)

 

I prepare a statement ("Select col2 from table where column = ?") and then
use bind to that parameter what are the parameters ?

 

From slq browser I can "Select col2 from table where column = "" and get the
empty string rows - so I know they are there sqlite3_bind_text(stmt, 1, "",
0, SQLITE_ STATIC) ?

 

sqlite3_bind_text(stmt, 1, "", -1, SQLITE_ STATIC) ?

 

I'm stuck

 

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

Re: [sqlite] What are the correct bind parameters for empty string

[Richard Hipp]

On 9/19/16, Robert E. Pappenhagen  wrote:
> I have a table with a not null column...
>
> Some of the values are empty string and some have more lengthy values :-)
>
> I prepare a statement ("Select col2 from table where column = ?") and then
> use bind to that parameter what are the parameters ?
>
> From slq browser I can "Select col2 from table where column = "" and get the
> empty string rows - so I know they are there
> sqlite3_bind_text(stmt, 1, "", 0, SQLITE_ STATIC) ?
>
> sqlite3_bind_text(stmt, 1, "", -1, SQLITE_ STATIC) ?

They should both work.  The first might be very slightly faster by
avoiding a call to strlen().

-- 
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] What are the correct bind parameters for empty string

[Robert E. Pappenhagen]

I have a table with a not null column...

Some of the values are empty string and some have more lengthy values :-)

I prepare a statement ("Select col2 from table where column = ?") and then use 
bind to that parameter what are the parameters ?

From slq browser I can "Select col2 from table where column = "" and get the 
empty string rows - so I know they are there
sqlite3_bind_text(stmt, 1, "", 0, SQLITE_ STATIC) ?

sqlite3_bind_text(stmt, 1, "", -1, SQLITE_ STATIC) ?

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

Re: [sqlite] Complicated join

[David Bicking]

Thanks.
Yeah, sometimes I hate SQL. Some of my queries for this project take 2 or 3 
pages of paper to print out. Unfortunately the only alternatives approved by 
the bosses are even worse.
I think I can add the Not exists clause to my query and that should do it.
Which means I need to load the 70,000+ records to M and the 200,000+ records to 
E and see how long it takes to run!
David


  From: David Raymond 
 To: SQLite mailing list  
 Sent: Monday, September 19, 2016 3:30 PM
 Subject: Re: [sqlite] Complicated join
   
Something that works, but is ugly so I hesitate to post it. Again, working with 
the results of a simpler query outside of SQL would be preferred. Just because 
you  make ASCII-art of the Mandelbrot set using SQL doesn't mean it's the 
best choice. (Would that now be UTF-8-art? Doesn't have quite the same ring to 
it)

Playing around does leave me with one question by the way: Do temp tables 
created via "with a as b" not have a rowid field? I tried referencing that, but 
kept getting issues.


But in any case:


create table E (CombinedKeyField text, EvtNbr int, primary key 
(CombinedKeyField, EvtNbr));
create table M (CombinedKeyField text, EvtNbr int, primary key 
(CombinedKeyField, EvtNbr));
insert into E values ('A', 1), ('A', 2), ('A', 3), ('A', 4), ('A', 5), ('A', 
6), ('A', 7),
('A', 8), ('A', 9), ('B', 1);
insert into M values ('A', 1), ('A', 5);

--explain query plan
with x as (
select CombinedKeyField, E.EvtNbr as EEvtNbr, M.EvtNbr as MEvtNbr, E.EvtNbr != 
M.EvtNbr as neq
from E inner join M using (CombinedKeyField))
--order by CombinedKeyField, EEvtNbr, neq, MEvtNbr)
--Use x instead of M for the outer join, and take only the first record (if 
any) that matches.
--Had the "order by" in there while trying to use the rowid in the later 
compare,
--just realized I could take it out since I gave up on using rowid.

select E.CombinedKeyField, E.EvtNbr as EEvtNbr, x.MEvtNbr
from E left outer join x
on E.CombinedKeyField = x.CombinedKeyField and E.EvtNbr = x.EEvtNbr

--The "take only the first one" part.
where not exists (
select * from x as xt
where xt.CombinedKeyField = E.CombinedKeyField and xt.EEvtNbr = E.EvtNbr
and (xt.neq < x.neq or (xt.neq = x.neq and xt.MEvtNbr < x.MEvtNbr)));


Output is:
CombinedKeyField|EEvtNbr|MEvtNbr
A|1|1
A|2|1
A|3|1
A|4|1
A|5|5
A|6|1
A|7|1
A|8|1
A|9|1
B|1|


explain query plan output:
selectid|order|from|detail
1|0|0|SCAN TABLE E
1|1|1|SEARCH TABLE M USING COVERING INDEX sqlite_autoindex_M_1 
(CombinedKeyField=?)
0|0|0|SCAN TABLE E
0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (EEvtNbr=? AND 
CombinedKeyField=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE E USING COVERING INDEX sqlite_autoindex_E_1 
(CombinedKeyField=? AND EvtNbr=?)
2|1|1|SEARCH TABLE M USING COVERING INDEX sqlite_autoindex_M_1 
(CombinedKeyField=?)


The compares and such are going to blow up in size when translated to your real 
version with the 8 fields, which is what makes me cringe.

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

INSERT INTO M (CombinedKeyField, EvtNbr)

VALUES ('A', 1),
('A', 5);

INSERT INTO E (CombineKeyField, EvtNbr)
VALUES ('A', 1)
, ('A', 2)
, ('A', 3)
, ('A', 4)
, ('A', 5)
, ('A', 6)
, ('A', 7)
, ('A', 8)
, ('A', 9)
, ('B', 1);

I'm ignoring the TransDate part for now.

This is what I want:

SELECT E.CombinedKeyField, E.EvtNbr, M.EvtNbr;
A  1  1
A  2  1
A  3  1
A  4  1
A  5  5  -- matches the (A,5) record in the M table.

A  6  1
A  7  1
A  8  1
A  9  1
B  1  NULL  -- no match found for CombinedKeyfield in M


This is the closest I have got

select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
from E left join M
on E.CombinedKeyField = M.CombinedKeyField
and (E.EvtNbr = M.EvtNbr
or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
                          WHERE M1.CombinedKeyField = E.CombinedKeyField 
              ) 
)

But it doubles up on A,5, matching both on A,1 and A,5 in M
And it doesn't return B,1 with no match even though it is a left join.

Hopefully that comes out readable, and my needs are clearer.

Thanks,
David

- Original Message -
From: James K. Lowden 
To: sqlite-users@mailinglists.sqlite.org
Sent: Monday, September 19, 2016 10:57 AM
Subject: Re: [sqlite] Complicated join

On Thu, 15 Sep 2016 15:53:10 + (UTC)



David Bicking  wrote:

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

I think this is what you describe: 

select E.CombinedKeyFields, max(M.EvtNbr) as EvtNbr, max(M.TransDate)
as TransDate from E left join M
on E.CombinedKeyFields = M.CombinedK

Re: [sqlite] Complicated join

[David Raymond]

Something that works, but is ugly so I hesitate to post it. Again, working with 
the results of a simpler query outside of SQL would be preferred. Just because 
you  make ASCII-art of the Mandelbrot set using SQL doesn't mean it's the 
best choice. (Would that now be UTF-8-art? Doesn't have quite the same ring to 
it)

Playing around does leave me with one question by the way: Do temp tables 
created via "with a as b" not have a rowid field? I tried referencing that, but 
kept getting issues.


But in any case:


create table E (CombinedKeyField text, EvtNbr int, primary key 
(CombinedKeyField, EvtNbr));
create table M (CombinedKeyField text, EvtNbr int, primary key 
(CombinedKeyField, EvtNbr));
insert into E values ('A', 1), ('A', 2), ('A', 3), ('A', 4), ('A', 5), ('A', 
6), ('A', 7),
('A', 8), ('A', 9), ('B', 1);
insert into M values ('A', 1), ('A', 5);

--explain query plan
with x as (
select CombinedKeyField, E.EvtNbr as EEvtNbr, M.EvtNbr as MEvtNbr, E.EvtNbr != 
M.EvtNbr as neq
from E inner join M using (CombinedKeyField))
--order by CombinedKeyField, EEvtNbr, neq, MEvtNbr)
--Use x instead of M for the outer join, and take only the first record (if 
any) that matches.
--Had the "order by" in there while trying to use the rowid in the later 
compare,
--just realized I could take it out since I gave up on using rowid.

select E.CombinedKeyField, E.EvtNbr as EEvtNbr, x.MEvtNbr
from E left outer join x
on E.CombinedKeyField = x.CombinedKeyField and E.EvtNbr = x.EEvtNbr

--The "take only the first one" part.
where not exists (
select * from x as xt
where xt.CombinedKeyField = E.CombinedKeyField and xt.EEvtNbr = E.EvtNbr
and (xt.neq < x.neq or (xt.neq = x.neq and xt.MEvtNbr < x.MEvtNbr)));


Output is:
CombinedKeyField|EEvtNbr|MEvtNbr
A|1|1
A|2|1
A|3|1
A|4|1
A|5|5
A|6|1
A|7|1
A|8|1
A|9|1
B|1|


explain query plan output:
selectid|order|from|detail
1|0|0|SCAN TABLE E
1|1|1|SEARCH TABLE M USING COVERING INDEX sqlite_autoindex_M_1 
(CombinedKeyField=?)
0|0|0|SCAN TABLE E
0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (EEvtNbr=? AND 
CombinedKeyField=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE E USING COVERING INDEX sqlite_autoindex_E_1 
(CombinedKeyField=? AND EvtNbr=?)
2|1|1|SEARCH TABLE M USING COVERING INDEX sqlite_autoindex_M_1 
(CombinedKeyField=?)


The compares and such are going to blow up in size when translated to your real 
version with the 8 fields, which is what makes me cringe.

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

INSERT INTO M (CombinedKeyField, EvtNbr)

VALUES ('A', 1),
('A', 5);

INSERT INTO E (CombineKeyField, EvtNbr)
VALUES ('A', 1)
, ('A', 2)
, ('A', 3)
, ('A', 4)
, ('A', 5)
, ('A', 6)
, ('A', 7)
, ('A', 8)
, ('A', 9)
, ('B', 1);

I'm ignoring the TransDate part for now.

This is what I want:

SELECT E.CombinedKeyField, E.EvtNbr, M.EvtNbr;
A   1   1
A   2   1
A   3   1
A   4   1
A   5   5  -- matches the (A,5) record in the M table.

A   6   1
A   7   1
A   8   1
A   9   1
B   1   NULL  -- no match found for CombinedKeyfield in M


This is the closest I have got

select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
from E left join M
on E.CombinedKeyField = M.CombinedKeyField
and (E.EvtNbr = M.EvtNbr
or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
   WHERE M1.CombinedKeyField = E.CombinedKeyField 
   ) 
)

But it doubles up on A,5, matching both on A,1 and A,5 in M
And it doesn't return B,1 with no match even though it is a left join.

Hopefully that comes out readable, and my needs are clearer.

Thanks,
David

- Original Message -
From: James K. Lowden 
To: sqlite-users@mailinglists.sqlite.org
Sent: Monday, September 19, 2016 10:57 AM
Subject: Re: [sqlite] Complicated join

On Thu, 15 Sep 2016 15:53:10 + (UTC)



David Bicking  wrote:

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

I think this is what you describe: 

select E.CombinedKeyFields, max(M.EvtNbr) as EvtNbr, max(M.TransDate)
as TransDate from E left join M
on E.CombinedKeyFields = M.CombinedKeyFields
and E.EvtNbr >= M.EvtNbr
and E.TransDate >= M.TransDate

--jkl
___
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
sq

Re: [sqlite] Complicated join

[David Bicking]

This is what I want:

SELECT E.CombinedKeyField, E.EvtNbr, M.EvtNbr;
A  1  1
A  2  1
A  3  1
A  4  1
A  5  5  -- matches the (A,5) record in the M table.
A  6  1
A  7  1
A  8  1
A  9  1
B  1  NULL  -- no match found for CombinedKeyfield in M

Did this part of my post not make it to your reader?
Your output is almost what I want, except that the A.5 line is matching 1 and 5 
in the M table, and I only want it to match the 5.

Now, can you suggest how I can get the query to return A,5,5 but not A,5,1?
Thanks,David
 From: Luuk  To: sqlite-users@mailinglists.sqlite.org 
 Sent: Monday, September 19, 2016 2:43 PM
 Subject: Re: [sqlite] Complicated join
   
On 19-09-16 19:33, David Bicking wrote:
> select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
> from E left join M
> on E.CombinedKeyField = M.CombinedKeyField
> and (E.EvtNbr = M.EvtNbr
> or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
>                            WHERE M1.CombinedKeyField = E.CombinedKeyField
>                )
> )

expected output is missing

now we need to guess at what you want the output to look like

> But it doubles up on A,5, matching both on A,1 and A,5 in M
which line is correct? (or are they both correct?)
> And it doesn't return B,1 with no match even though it is a left join.
In my output i do see 'B|1|' ..


sqlite> select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
    ...> from E left join M
    ...> on E.CombinedKeyField = M.CombinedKeyField
    ...> and (E.EvtNbr = M.EvtNbr
    ...> or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
    ...>                            WHERE M1.CombinedKeyField = 
E.CombinedKeyField
    ...>                )
    ...> );
A|1|1
A|2|1
A|3|1
A|4|1
A|5|1
A|5|5
A|6|1
A|7|1
A|8|1
A|9|1
B|1|
sqlite> .version
SQLite 3.11.1 2016-03-03 16:17:53 f047920ce16971e573bc6ec9a48b118c9de2b3a7
sqlite>



___
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] Complicated join

[David Bicking]

"CombinedKeyFields", is in fact about 7 or 8 fields in the natural key. If I 
mistype 17 letters, I'd hate to see how I mangle the whole thing. 


  From: Luuk 
 To: sqlite-users@mailinglists.sqlite.org 
 Sent: Monday, September 19, 2016 2:23 PM
 Subject: Re: [sqlite] Complicated join
   
On 19-09-16 19:33, David Bicking wrote:
> INSERT INTO M (CombinedKeyField, EvtNbr)
>
> VALUES ('A', 1),
> ('A', 5);
>
> INSERT INTO E (CombineKeyField, EvtNbr)
> VALUES ('A', 1)
> , ('A', 2)
> , ('A', 3)
> , ('A', 4)
> , ('A', 5)
> , ('A', 6)
> , ('A', 7)
> , ('A', 8)
> , ('A', 9)
> , ('B', 1);
>
>

What is the name of this field?

CombinedKeyField
CombineKeyField
CombinedKeyFields


And why is it not possible to keep this the SAME/UNCHANGED between posts?

___
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] Complicated join

[Luuk]

On 19-09-16 19:33, David Bicking wrote:

select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
from E left join M
on E.CombinedKeyField = M.CombinedKeyField
and (E.EvtNbr = M.EvtNbr
or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
WHERE M1.CombinedKeyField = E.CombinedKeyField
)
)


expected output is missing

now we need to guess at what you want the output to look like


But it doubles up on A,5, matching both on A,1 and A,5 in M

which line is correct? (or are they both correct?)

And it doesn't return B,1 with no match even though it is a left join.

In my output i do see 'B|1|' ..


sqlite> select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
   ...> from E left join M
   ...> on E.CombinedKeyField = M.CombinedKeyField
   ...> and (E.EvtNbr = M.EvtNbr
   ...> or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
   ...>WHERE M1.CombinedKeyField = 
E.CombinedKeyField

   ...>)
   ...> );
A|1|1
A|2|1
A|3|1
A|4|1
A|5|1
A|5|5
A|6|1
A|7|1
A|8|1
A|9|1
B|1|
sqlite> .version
SQLite 3.11.1 2016-03-03 16:17:53 f047920ce16971e573bc6ec9a48b118c9de2b3a7
sqlite>



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

Re: [sqlite] Complicated join

[Luuk]

On 19-09-16 19:33, David Bicking wrote:

INSERT INTO M (CombinedKeyField, EvtNbr)

VALUES ('A', 1),
('A', 5);

INSERT INTO E (CombineKeyField, EvtNbr)
VALUES ('A', 1)
, ('A', 2)
, ('A', 3)
, ('A', 4)
, ('A', 5)
, ('A', 6)
, ('A', 7)
, ('A', 8)
, ('A', 9)
, ('B', 1);




What is the name of this field?

CombinedKeyField
CombineKeyField
CombinedKeyFields


And why is it not possible to keep this the SAME/UNCHANGED between posts?

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

Re: [sqlite] Complicated join

[David Bicking]

INSERT INTO M (CombinedKeyField, EvtNbr)

VALUES ('A', 1),
('A', 5);

INSERT INTO E (CombineKeyField, EvtNbr)
VALUES ('A', 1)
, ('A', 2)
, ('A', 3)
, ('A', 4)
, ('A', 5)
, ('A', 6)
, ('A', 7)
, ('A', 8)
, ('A', 9)
, ('B', 1);

I'm ignoring the TransDate part for now.

This is what I want:

SELECT E.CombinedKeyField, E.EvtNbr, M.EvtNbr;
A   1   1
A   2   1
A   3   1
A   4   1
A   5   5  -- matches the (A,5) record in the M table.

A   6   1
A   7   1
A   8   1
A   9   1
B   1   NULL  -- no match found for CombinedKeyfield in M


This is the closest I have got

select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
from E left join M
on E.CombinedKeyField = M.CombinedKeyField
and (E.EvtNbr = M.EvtNbr
or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
   WHERE M1.CombinedKeyField = E.CombinedKeyField 
   ) 
)

But it doubles up on A,5, matching both on A,1 and A,5 in M
And it doesn't return B,1 with no match even though it is a left join.

Hopefully that comes out readable, and my needs are clearer.

Thanks,
David

- Original Message -
From: James K. Lowden 
To: sqlite-users@mailinglists.sqlite.org
Sent: Monday, September 19, 2016 10:57 AM
Subject: Re: [sqlite] Complicated join

On Thu, 15 Sep 2016 15:53:10 + (UTC)



David Bicking  wrote:

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

I think this is what you describe: 

select E.CombinedKeyFields, max(M.EvtNbr) as EvtNbr, max(M.TransDate)
as TransDate from E left join M
on E.CombinedKeyFields = M.CombinedKeyFields
and E.EvtNbr >= M.EvtNbr
and E.TransDate >= M.TransDate

--jkl
___
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] Slow Sqlite3 Select Query while Insert/Update/Delete has no issue

[Dominique Devienne]

On Mon, Sep 19, 2016 at 4:56 PM, James K. Lowden 
wrote:

> On Wed, 14 Sep 2016 16:27:37 +0530
>
> But I agree with Teg: SQLite is providing you with transactions you
> don't need, and puts an interpreted language exactly where you don't
> want it: in a performance-critical spot.  The C++ standard library has
> all the bits you need, and is almost as convenient to use.
>
> You have only one table, and probably just a few simple queries.
> std::set gives you lower_bound and upper_bound.  Hand those two
> iterators to std::accumulate, and you have GROUP BY.  Call that for 5
> prices.  Not very much code, and I bet 100x faster than SQL.  If more
> than one thread is updating the table, obviously protect your set with
> a mutex.
>

In addition to James' excellent answer, I'd add that using C++/STL
containers
doesn't preclude you from having SQL on top of them, thanks to SQLite's
virtual tables.

For example, FWIW, I extensively use Boost's Multi-Index containers,
which are like statically indexed tables (you decide what indexes you want,
at compile time), and layer read-only virtual tables on top, properly
exposing
the static indexes to SQLite's query planner (via xBestIndex). That way you
can use C++ when you want, but can still benefit for all the flexibility of
SQL,
which happens to read even faster than SQLite's in-memory DBs. --DD
___
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:50 PM, Richard Hipp  wrote:

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

On Mon, Sep 19, 2016 at 4:56 PM, James K. Lowden 
wrote:

> On Fri, 16 Sep 2016 16:59:17 +0200
> Dominique Devienne  wrote:
>
> > Is that <> SQL standard?
> No.
>

Thank you Richard and James. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Complicated join

[James K. Lowden]

On Thu, 15 Sep 2016 15:53:10 + (UTC)
David Bicking  wrote:

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

I think this is what you describe: 

select E.CombinedKeyFields, max(M.EvtNbr) as EvtNbr, max(M.TransDate)
as TransDate from E left join M
on E.CombinedKeyFields = M.CombinedKeyFields
and E.EvtNbr >= M.EvtNbr
and E.TransDate >= M.TransDate

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

Re: [sqlite] When is data committed on one connection seen on another?

[James K. Lowden]

On Wed, 14 Sep 2016 18:29:36 + (UTC)
Alex Ward  wrote:

> Perhaps our schema needs a rework, would one table with a million
> rows be better than 500 tables with 2000 rows each? 

500 tables isn't right or wrong, but *counting* tables is.  Table count
is not a design-quality metric.  

There is no rule of thumb except Boyce-Codd Normal Form.  

Table design reflects the entities you choose to represent your domain
of discourse.  You should strive, as Einstein advised, to make your
model as simple as possible, but no simpler.  

Sometimes we see schemas on this list with sets of identical tables,
each set dedicated to a different client or somesuch.  Those folks buy
themselves trouble, because otherwise identical SQL has to vary by
tablename, adding nothing but complexity to the application layer.  

At the opposite end of the spectrum is the classic
entity-attribute-value design error.  One table conquers all, including
the programmers when they discover how slow things are when SQL is used
before learned.  

Number of columns?  Many widely used and correctly normalized financial
market databases maintain thousands of rows on hundreds of columns.  

Complex models can easily have a hundred tables.  Before I would agree
a design has "too many" tables, I would want an affirmative answer to
one of two questions:

1.  Is there a process in place to dynamically extend the schema?  If
so, that indicates data -- some change over time -- has found its way
into the metadata (the table names).  

2.  Do you frequently find yourself looking in more than one place for
what -- to you -- substantially the same information.  That would
indicate the tables do not reflect your mental model of the problem
domain.  

There are forces that drive intelligent table design other than the
logical model.  Things like performance and convenience inevitably
cause tables that would otherwise pass textbook muster to be split and
combined.  Those things count, too.  One thing that doesn't count is
the count.  

Regards, 

--jkl


___
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

[James K. Lowden]

On Fri, 16 Sep 2016 07:29:28 -0400
Richard Hipp  wrote:

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

My immediate thought was that this is an optimization opportunity.  As
the OP alludes to, N is the sum of LIMIT and OFFSET. 

Would you have information on how these are typically used?  My guess
is that the LIMIT argument is typically small, less than 20, but that
OFFSET marches on, and grows to be a significant fraction of the
table.  

If LIMIT N is small and OFFSET is not used, a memory-efficient,
nonlocking parallel algorithm would reserve N slots for each thread,
and divide the table among the threads, each processing 1/threads
rows.  Then merge-sort their outputs.  

Humbly submitted, 

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

Re: [sqlite] Slow Sqlite3 Select Query while Insert/Update/Delete has no issue

[James K. Lowden]

On Wed, 14 Sep 2016 16:27:37 +0530
SinhaK  wrote:

> strlen(MyString.str().c_str())

BTW, as a matter of style, 

MyString.str().size()

gets you to the same place sooner.   

> MyString<<"select TokenNo,Price ,sum(QTY) from 'Stream0' where 
> TokenNo=?1 and Side=66 group by Price order by Price desc limit 5";

You should group by TokenNo, Price.  You should not have 

 'Stream0' 

in single quotes; that makes it a string.  

But I agree with Teg: SQLite is providing you with transactions you
don't need, and puts an interpreted language exactly where you don't
want it: in a performance-critical spot.  The C++ standard library has
all the bits you need, and is almost as convenient to use.  

You have only one table, and probably just a few simple queries.
std::set gives you lower_bound and upper_bound.  Hand those two
iterators to std::accumulate, and you have GROUP BY.  Call that for 5
prices.  Not very much code, and I bet 100x faster than SQL.  If more
than one thread is updating the table, obviously protect your set with
a mutex.  

--jkl


___
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

[James K. Lowden]

On Fri, 16 Sep 2016 16:59:17 +0200
Dominique Devienne  wrote:

> Is that <> SQL standard?

No.  

The two most frequently used pointless words in SQL are "select *".
The SELECT clause (not statement) chooses columns; in relational
algebra terms, it's a project operator.  If "all columns" is what you
need, what you don't need is projection.  Requiring "select *" makes as
much sense as requiring "WHERE TRUE" if there is no restriction.  


The strict select-from-where construct in SQL is an artifact of its
roots in IBM's 1970s-era "4th generation" languages.  That's why the
language looks so much like Cobol and so little like math.  

But it is what it is.  In SQL, a tablename is a parameter for FROM (and
nowadays, JOIN).  Predicates -- IN, EXISTS -- take SELECT.  

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

[sqlite] ANN: SQLite PHP Generator 16.9 released

[SQL Maestro Group]

Hi!

SQL Maestro Group announces the release of SQLite PHP Generator 16.9, a 
powerful GUI frontend for Windows that allows you to generate feature-rich 
CRUD web applications for your SQLite database.

http://www.sqlmaestro.com/products/sqlite/phpgenerator/

Online demos:
http://demo.sqlmaestro.com/feature_demo/
http://demo.sqlmaestro.com/nba/

Top 20 new features:


1. Column Filtering.
2. Custom Form Layouts.
3. Enhanced Master/Detail Views.
4. Record comparison.
5. Multi-Row Column Headers.
6. Breadcrumb navigation.
7. Automatic generation of index pages.
8. On-the-Fly Adding New Items to Lookup Controls.
9. Improved Filter Builder.
10. Quick Edit.
11. Adding multiple records from a single Insert form.
12. Geo Charts.
13. Permissions and RLS for all-level Details.
14. Flexible card views.
15. Enhanced JavaScript API.
16. OnGetCustomExportOptions event.
17. OnGetCustomUploadFileName event.
18. Built-in Lightbox component for external images.
19. Simple Data Grid Styling.
20. A number of GUI improvements.

Full press-release is available at:
http://www.sqlmaestro.com/news/company/php_generators_16_9_released/

Background information:
---
SQL Maestro Group offers complete database management and web development 
tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2, SQLite, SQL Anywhere, 
Firebird and MaxDB providing the highest performance, scalability and 
reliability to meet the requirements of today's database applications.


Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com 


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

[sqlite] Fwd: 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... -fPI

[sqlite] The importance of beta-testing. Was: Crash Report

[Richard Hipp]

On 9/18/16, Keith Medcalf  wrote:
>
> Somewhere between:
>
> 2016-09-03 16:23:42 672c21bcf09c5bfb67e061456a56be45409c4f34
>
> 2016-09-09 20:23:59 19e2e5950541f1a93eed994cc2b1eaf64b68e858
>
> where the former works and the later crashes (with a wunderful new fangled
> Windows 10 content free  error message) ((I do not have all versions from
> the fossil repository in my private repository)
>

Problem fixed on trunk.

Thank you for testing with recent, unreleased, trunk versions of
SQLite!  Without your beta-testing and this bug report, the obscure
but real problem you identified would have slipped through our
extensive testing regime and made it into the 3.15.0 release.  But
thanks to your report, the problem has now been fixed and test cases
have been added to ensure there is never a regression.

Everyone:  The latest unreleased trunk version of SQLite is available
as the "Prerelease Snapshot" on the download page
(https://www.sqlite.org/download.html).  Your assistance in testing
this and future Prerelease Snapshots is greatly appreciated.  Please
report any problems seen (crashes and/or performance regressions) to
this mailing list, or directly to me.

-- 
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] rowid changing....

[Dominique Devienne]

On Sun, Sep 18, 2016 at 4:19 PM, mikeegg1  wrote:

> Thanks everyone. I was mis-equating REPLACE with UPDATE. I’ll change my
> code to “INSERT or IGNORE” and add an UPDATE.
>

For info, that's a common gotcha with SQLite. See [1] from 2-months ago,
but I'm sure the archives are littered with similar examples :). --DD

[1]
http://sqlite.1065341.n5.nabble.com/insert-or-replace-on-PK-and-UNIQUE-INDEX-td90407.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users