Re: [sqlite] User function's alias

[Jens Alfke]

> On Nov 10, 2017, at 9:51 AM, korablev  wrote:
> 
> I have noticed strange behaviour of user functions. Consider following
> example:

In reports like this, it really helps if you can clearly state the situation at 
the start, instead of dumping hundreds of lines of code and output, and 
expecting the reader to figure out what’s going on.

Down at the end:
> So, change_global_var is called 3 times for -- one for inserting and two for
> comparison. I expected that it would call function one time, save result and
> use saved result for comparisons and insertion.

This is what should have been at the top :)

First off, you didn’t register the function as deterministic, so SQLite has to 
assume it can return a different result every time it’s called, even with the 
same arguments. That immediately prevents the kind of optimization you wanted.

As for deterministic functions, I asked a similar question a month or two ago, 
about factoring multiple calls out of a query. You can find list archives and 
read the thread. It doesn’t sound likely to happen.

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

Re: [sqlite] Simple Search using LIKE or something else

[Jens Alfke]

> On Nov 10, 2017, at 6:50 AM, Peter Da Silva  
> wrote:
> 
>> I'm using the amalgamation by default, so I don't think FTS is in the DLL.  
>> I can recompile, but that now introduces complications on machines that may 
>> not have this exact DLL.  (A couple people in my company use this app)
> 
> The same problem would presumably apply to the regex extension... not all 
> users would have that extension.

What platform is this for? Apple platforms ship with SQLite libraries that 
include FTS (not sure about regex, but it’d be easy to check.)

On other platforms, you can just statically link SQLite into your application 
and not have to worry what version or configuration might be on the user’s 
machine.

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

[sqlite] User function's alias

[korablev]

I have noticed strange behaviour of user functions. Consider following
example:

#include 
#include 

#include "sqlite3.h"

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i0
AND y<100;", callback, 0, );
sqlite3_close(db);
return 0;
}

It prints: 
x = 1
y = 3

x = 2
y = 6

x = 3
y = 9

However, I expect y = 1, 2, 3, which seems to be reasonable. Let's look at
vdbe opcodes:

addr = 0
opcode = Init
p1 = 0
p2 = 13
p3 = 0
p4 = 
p5 = 00
comment = NULL

addr = 1
opcode = OpenRead
p1 = 0
p2 = 2
p3 = 0
p4 = 1
p5 = 00
comment = NULL

addr = 2
opcode = Rewind
p1 = 0
p2 = 11
p3 = 0
p4 = 
p5 = 00
comment = NULL

addr = 3
opcode = Function0
p1 = 0
p2 = 0
p3 = 1
p4 = change_global_var(0)
p5 = 00
comment = NULL

addr = 4
opcode = Le
p1 = 2
p2 = 10
p3 = 1
p4 = 
p5 = 51
comment = NULL

addr = 5
opcode = Function0
p1 = 0
p2 = 0
p3 = 1
p4 = change_global_var(0)
p5 = 00
comment = NULL

addr = 6
opcode = Ge
p1 = 3
p2 = 10
p3 = 1
p4 = 
p5 = 51
comment = NULL

addr = 7
opcode = Column
p1 = 0
p2 = 0
p3 = 4
p4 = 
p5 = 00
comment = NULL

addr = 8
opcode = Function0
p1 = 0
p2 = 0
p3 = 5
p4 = change_global_var(0)
p5 = 00
comment = NULL

addr = 9
opcode = ResultRow
p1 = 4
p2 = 2
p3 = 0
p4 = 
p5 = 00
comment = NULL

...
 
So, change_global_var is called 3 times for -- one for inserting and two for
comparison. I expected that it would call function one time, save result and
use saved result for comparisons and insertion. Such behaviour is very
confusing and should be documented or fixed.

Another kind of useful optimization is calling deterministic function
without arguments only once for entire table. Currently, it is called for
each row. It is unlikely to be important optimization, but can be
implemented.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Peter Da Silva]

Looking back through the thread, from the original post:

> I'm using the amalgamation by default, so I don't think FTS is in the DLL.  I 
> can recompile, but that now introduces complications on machines that may not 
> have this exact DLL.  (A couple people in my company use this app)

The same problem would presumably apply to the regex extension... not all users 
would have that extension.


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

Re: [sqlite] Simple Search using LIKE or something else

[Richard Hipp]

On 11/10/17, Stephen Chrzanowski  wrote:
> I'd take regular expressions, sure, but, how do you get SQLite syntax
> to take that on?

Load the regexp extension here
(https://www.sqlite.org/src/artifact/a68d25c659bd2d89) or one of the
other 3rd-party regexp extensions that you can find on the internet.
Then use the

 column REGEXP $pattern

operator.

-- 
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] Simple Search using LIKE or something else

[Don V Nielsen]

A good StackOverflow post on adapting in C# for use in Linq queries:
https://stackoverflow.com/questions/24229785/sqlite-net-sqlitefunction-not-working-in-linq-to-sql/26155359#26155359


On Fri, Nov 10, 2017 at 8:31 AM, Don V Nielsen 
wrote:

> Brent Ashley has a nice blog post on integrating your languages regular
> express processing into sqlite: http://www.ashleyit.
> com/blogs/brentashley/2013/11/27/using-regular-expressions-with-sqlite/
>
>
> On Fri, Nov 10, 2017 at 8:15 AM, Stephen Chrzanowski 
> wrote:
>
>> I'd take regular expressions, sure, but, how do you get SQLite syntax
>> to take that on?
>>
>> On Fri, Nov 10, 2017 at 8:45 AM, Don V Nielsen 
>> wrote:
>> > Assuming you are looking for "Abc" or "Def" anywhere in the argument,
>> how
>> > about regular expression? (Abc|Def)
>> >
>> > On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
>> > peter.dasi...@flightaware.com> wrote:
>> >
>> >> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
>> >> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
>> >> pontia...@gmail.com> wrote:
>> >> > I've added a simple filter function that uses SQLites LIKE operator,
>> but
>> >> I'd like something a little bit more advanced.  Right now, the SQL
>> code is
>> >> like:
>> >> >
>> >> > select * from Events where Title like '%Abc%Def%'
>> >> >
>> >> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
>> >> >
>> >> > I've thought about doing some kind of delimiter, then have the code
>> >> generate the SQL code by just looping through the keywords and
>> generate the
>> >> "or Title like '%keyword%'" statement (With appropriate escaping), but
>> that
>> >> just smells bad to me.
>> >>
>> >> The best way to do it is not to use complex keys, but if you have to
>> (say
>> >> because the input is free form) then generating
>> >>
>> >> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
>> >>
>> >> is probably the only option.
>> >>
>> >>
>> >> ___
>> >> 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
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Don V Nielsen]

Brent Ashley has a nice blog post on integrating your languages regular
express processing into sqlite:
http://www.ashleyit.com/blogs/brentashley/2013/11/27/using-regular-expressions-with-sqlite/


On Fri, Nov 10, 2017 at 8:15 AM, Stephen Chrzanowski 
wrote:

> I'd take regular expressions, sure, but, how do you get SQLite syntax
> to take that on?
>
> On Fri, Nov 10, 2017 at 8:45 AM, Don V Nielsen 
> wrote:
> > Assuming you are looking for "Abc" or "Def" anywhere in the argument, how
> > about regular expression? (Abc|Def)
> >
> > On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
> > peter.dasi...@flightaware.com> wrote:
> >
> >> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
> >> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> >> pontia...@gmail.com> wrote:
> >> > I've added a simple filter function that uses SQLites LIKE operator,
> but
> >> I'd like something a little bit more advanced.  Right now, the SQL code
> is
> >> like:
> >> >
> >> > select * from Events where Title like '%Abc%Def%'
> >> >
> >> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
> >> >
> >> > I've thought about doing some kind of delimiter, then have the code
> >> generate the SQL code by just looping through the keywords and generate
> the
> >> "or Title like '%keyword%'" statement (With appropriate escaping), but
> that
> >> just smells bad to me.
> >>
> >> The best way to do it is not to use complex keys, but if you have to
> (say
> >> because the input is free form) then generating
> >>
> >> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
> >>
> >> is probably the only option.
> >>
> >>
> >> ___
> >> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Search using LIKE or something else

[Stephen Chrzanowski]

I'd take regular expressions, sure, but, how do you get SQLite syntax
to take that on?

On Fri, Nov 10, 2017 at 8:45 AM, Don V Nielsen  wrote:
> Assuming you are looking for "Abc" or "Def" anywhere in the argument, how
> about regular expression? (Abc|Def)
>
> On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
> peter.dasi...@flightaware.com> wrote:
>
>> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
>> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
>> pontia...@gmail.com> wrote:
>> > I've added a simple filter function that uses SQLites LIKE operator, but
>> I'd like something a little bit more advanced.  Right now, the SQL code is
>> like:
>> >
>> > select * from Events where Title like '%Abc%Def%'
>> >
>> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
>> >
>> > I've thought about doing some kind of delimiter, then have the code
>> generate the SQL code by just looping through the keywords and generate the
>> "or Title like '%keyword%'" statement (With appropriate escaping), but that
>> just smells bad to me.
>>
>> The best way to do it is not to use complex keys, but if you have to (say
>> because the input is free form) then generating
>>
>> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
>>
>> is probably the only option.
>>
>>
>> ___
>> 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

Re: [sqlite] Simple Search using LIKE or something else

[Don V Nielsen]

Assuming you are looking for "Abc" or "Def" anywhere in the argument, how
about regular expression? (Abc|Def)

On Thu, Nov 9, 2017 at 3:00 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> On 11/9/17, 2:51 PM, "sqlite-users on behalf of Stephen Chrzanowski" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> pontia...@gmail.com> wrote:
> > I've added a simple filter function that uses SQLites LIKE operator, but
> I'd like something a little bit more advanced.  Right now, the SQL code is
> like:
> >
> > select * from Events where Title like '%Abc%Def%'
> >
> >  This works if the Title is AbcRfeDef, but would fail with FedRfeAbc.
> >
> > I've thought about doing some kind of delimiter, then have the code
> generate the SQL code by just looping through the keywords and generate the
> "or Title like '%keyword%'" statement (With appropriate escaping), but that
> just smells bad to me.
>
> The best way to do it is not to use complex keys, but if you have to (say
> because the input is free form) then generating
>
> SELECT * FROM EVENTS WHERE title LIKE ‘%Abc%’ AND title LIKE ‘%Def%’;
>
> is probably the only option.
>
>
> ___
> 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] Does wal-file support MMAP？

[Simon Slavin]

On 10 Nov 2017, at 8:49am, advancenOO  wrote:

> Purely for speed :)
> I hope to improve the performance inside sqlite in order to optimize the
> performance of the upper applications. As there are so many different scenes
> in APPs.

hAve you optimised your column orders ?

Have you created ideal indexes for the WHERE and ORDER BY clauses you use ?

Have you made use of covering indexes ?

Both those things speed up SQLite many times more than using memory mapping.

Have you tried SQLite just as it is, without putting special effort into 
PRAMGAs ?  SQLite is extremely fast without those things.  There’s a good 
chance it’s fast enough and you’re just wasting your time.

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

Re: [sqlite] [EXTERNAL] Re: Does wal-file support MMAP？

[Hick Gunter]

I take it you have already fine tuned your database schema and statements to 
utilize optimum query plans, which may cut orders of magnitude from execution 
times and found the performance to still be lacking.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von advancenOO
Gesendet: Freitag, 10. November 2017 09:49
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Does wal-file support MMAP？


Purely for speed :)
I hope to improve the performance inside sqlite in order to optimize the 
performance of the upper applications. As there are so many different scenes in 
APPs.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does wal-file support MMAP？

[advancenOO]

Purely for speed :)
I hope to improve the performance inside sqlite in order to optimize the
performance of the upper applications. As there are so many different scenes
in APPs.
 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users