Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[Simon Slavin]

On 10 Mar 2014, at 1:38am, Stephen Chrzanowski  wrote:

> Apologies for the interruption and sort of off topic, but, is .timer part
> of the CLI only or is it part of the SQL language?  Can I get the result of
> a timer from a call, or do I have to put a wrapper on my wrapper?

Commands which start with a dot are part of the Shell Tool, not built into the 
SQLite API.

Furthermore there are no rules about which order SQLite would execute something 
like

SELECT timerBefore(), somethingComplicated(), timerAfter() FROM myTable

in.  Sorry.

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

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[Keith Medcalf]

On Sunday, 9 March, 2014 19:38, Stephen Chrzanowski  
inquired:

>Apologies for the interruption and sort of off topic, but, is .timer part
>of the CLI only or is it part of the SQL language?  Can I get the result
>of a timer from a call, or do I have to put a wrapper on my wrapper?

.timer is a shell command specific to the sqlite shell and not part of the SQL 
language.  

You would have to put your own wrapper to collect timing data.

>On Sun, Mar 9, 2014 at 8:17 PM, Keith Medcalf 
>wrote:
>
>>
>> sqlite> create virtual table n using wholenumber;
>> sqlite> .timer on
>> sqlite> select sum(sqrt(value)) from n where value between 1 and 1000;
>> 21097.4558874807
>> Run Time: real 0.001 user 0.00 sys 0.00
>> sqlite> select sum(sqrt(value)) from n where value between 1 and
>100;
>> 67166.458841
>> Run Time: real 0.160 user 0.156250 sys 0.00
>> sqlite> select sum(sqrt(value)) from n where value between 1 and
>> 10;
>> 21081851083598.4
>> Run Time: real 151.021 user 151.031250 sys 0.00
>> sqlite> select sum(value) from n where value between 1 and 10;
>> 55
>> Run Time: real 89.341 user 89.343750 sys 0.00
>>
>> A native sqrt takes about 60 ns per operation.
>>
>> >-Original Message-
>> >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> >boun...@sqlite.org] On Behalf Of big stone
>> >Sent: Sunday, 9 March, 2014 03:35
>> >To: sqlite-users@sqlite.org
>> >Subject: Re: [sqlite] Suggestion to add "locate" as a broader version
>of
>> >"instr"
>> >
>> >Hello Max,
>> >
>> >Your link is pretty interesting. It looks that :
>> >- method1 should be easily implemented with SQLite "floating point"
>> >representation,
>> >- and with a very very small code size.
>> >
>> >Here is the benchmarking of the two available methods :
>> >
>>
>>https://raw.github.com/stonebig/ztest_donotuse/master/square_rooting_ben
>c
>> >hmark.GIF
>> >
>> >
>> >So :
>> >- your method is only 3 times slower than the python sqrt(),
>> >- if SQLite team accepts to sacrifice a few bytes to implement sqrt(),
>we
>> >may benefit a  389% speed-up (300/27*.652/1.86) at least.
>> >(300/27*.652/1.86)
>> >
>> >sqrt() is very interesting for statistics on-the-go over sql datas.
>> >
>> >Regards,
>> >___
>> >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-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] Suggestion to add "locate" as a broader version of "instr"

[Stephen Chrzanowski]

Apologies for the interruption and sort of off topic, but, is .timer part
of the CLI only or is it part of the SQL language?  Can I get the result of
a timer from a call, or do I have to put a wrapper on my wrapper?


On Sun, Mar 9, 2014 at 8:17 PM, Keith Medcalf  wrote:

>
> sqlite> create virtual table n using wholenumber;
> sqlite> .timer on
> sqlite> select sum(sqrt(value)) from n where value between 1 and 1000;
> 21097.4558874807
> Run Time: real 0.001 user 0.00 sys 0.00
> sqlite> select sum(sqrt(value)) from n where value between 1 and 100;
> 67166.458841
> Run Time: real 0.160 user 0.156250 sys 0.00
> sqlite> select sum(sqrt(value)) from n where value between 1 and
> 10;
> 21081851083598.4
> Run Time: real 151.021 user 151.031250 sys 0.00
> sqlite> select sum(value) from n where value between 1 and 10;
> 55
> Run Time: real 89.341 user 89.343750 sys 0.00
>
> A native sqrt takes about 60 ns per operation.
>
> >-Original Message-
> >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> >boun...@sqlite.org] On Behalf Of big stone
> >Sent: Sunday, 9 March, 2014 03:35
> >To: sqlite-users@sqlite.org
> >Subject: Re: [sqlite] Suggestion to add "locate" as a broader version of
> >"instr"
> >
> >Hello Max,
> >
> >Your link is pretty interesting. It looks that :
> >- method1 should be easily implemented with SQLite "floating point"
> >representation,
> >- and with a very very small code size.
> >
> >Here is the benchmarking of the two available methods :
> >
> >https://raw.github.com/stonebig/ztest_donotuse/master/square_rooting_benc
> >hmark.GIF
> >
> >
> >So :
> >- your method is only 3 times slower than the python sqrt(),
> >- if SQLite team accepts to sacrifice a few bytes to implement sqrt(), we
> >may benefit a  389% speed-up (300/27*.652/1.86) at least.
> >(300/27*.652/1.86)
> >
> >sqrt() is very interesting for statistics on-the-go over sql datas.
> >
> >Regards,
> >___
> >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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[Keith Medcalf]

sqlite> create virtual table n using wholenumber;
sqlite> .timer on
sqlite> select sum(sqrt(value)) from n where value between 1 and 1000;
21097.4558874807
Run Time: real 0.001 user 0.00 sys 0.00
sqlite> select sum(sqrt(value)) from n where value between 1 and 100;
67166.458841
Run Time: real 0.160 user 0.156250 sys 0.00
sqlite> select sum(sqrt(value)) from n where value between 1 and 10;
21081851083598.4
Run Time: real 151.021 user 151.031250 sys 0.00
sqlite> select sum(value) from n where value between 1 and 10;
55
Run Time: real 89.341 user 89.343750 sys 0.00

A native sqrt takes about 60 ns per operation.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of big stone
>Sent: Sunday, 9 March, 2014 03:35
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Suggestion to add "locate" as a broader version of
>"instr"
>
>Hello Max,
>
>Your link is pretty interesting. It looks that :
>- method1 should be easily implemented with SQLite "floating point"
>representation,
>- and with a very very small code size.
>
>Here is the benchmarking of the two available methods :
>
>https://raw.github.com/stonebig/ztest_donotuse/master/square_rooting_benc
>hmark.GIF
>
>
>So :
>- your method is only 3 times slower than the python sqrt(),
>- if SQLite team accepts to sacrifice a few bytes to implement sqrt(), we
>may benefit a  389% speed-up (300/27*.652/1.86) at least.
>(300/27*.652/1.86)
>
>sqrt() is very interesting for statistics on-the-go over sql datas.
>
>Regards,
>___
>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] Making string changes in a table

[Igor Tandetnik]

On 3/9/2014 6:37 PM, Tim Streater wrote:

Dammit, I looked up and down for 'strlen' and passed over 'length'! I had been 
thinking about:

   update mytable set path='/path/from/' || substr(path, length('/path/to/') + 
1)
   where path like '/path/to/%';

that way I anchor to the start of the path.


Be careful about '/path/to/' itself containing percent signs or underscores.
--
Igor Tandetnik

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

Re: [sqlite] Making string changes in a table

[Tim Streater]

On 09 Mar 2014 at 22:17, Igor Tandetnik  wrote: 

> On 3/9/2014 6:05 PM, Tim Streater wrote:
>> I have a table with one column containing file paths, such as /path/to/file
>> and /path/to/my/otherfile. Now I want to change all entries where the path
>> starts as /path/to/ to /path/from/. Getting a candidate list is easy, and I
>> can then make the changes in PHP and rewrite the rows, but I wondered if
>> there was a clever way to do it all in SQLite in, essentially, one statement.
>> A quick look persuades me there are not enough functions built into SQLite
>> for that, but confirmation would be handy.
>
> update mytable set path='/path/from/' || substr(path,
> length('/path/to/') + 1)
> where substr(path, 1, length('/path/to/')) = '/path/to/';

Dammit, I looked up and down for 'strlen' and passed over 'length'! I had been 
thinking about:

  update mytable set path='/path/from/' || substr(path, length('/path/to/') + 1)
  where path like '/path/to/%';

that way I anchor to the start of the path.



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

Re: [sqlite] Making string changes in a table

[Igor Tandetnik]

On 3/9/2014 6:05 PM, Tim Streater wrote:

I have a table with one column containing file paths, such as /path/to/file and 
/path/to/my/otherfile. Now I want to change all entries where the path starts 
as /path/to/ to /path/from/. Getting a candidate list is easy, and I can then 
make the changes in PHP and rewrite the rows, but I wondered if there was a 
clever way to do it all in SQLite in, essentially, one statement. A quick look 
persuades me there are not enough functions built into SQLite for that, but 
confirmation would be handy.


update mytable set path='/path/from/' || substr(path, 
length('/path/to/') + 1)

where substr(path, 1, length('/path/to/')) = '/path/to/';

--
Igor Tandetnik

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

Re: [sqlite] Making string changes in a table

[Zsbán Ambrus]

On 3/9/14, Simon Slavin  wrote:
> Check out REPLACE():
>
> Technically speaking this might mess up if the string '/path/to/' occurs in
> the middle of the string as well as at its beginning,

For that reason, I think it would be better to use the substr function.

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

Re: [sqlite] Making string changes in a table

[Simon Slavin]

On 9 Mar 2014, at 10:05pm, Tim Streater  wrote:

> I have a table with one column containing file paths, such as /path/to/file 
> and /path/to/my/otherfile. Now I want to change all entries where the path 
> starts as /path/to/ to /path/from/. Getting a candidate list is easy, and I 
> can then make the changes in PHP and rewrite the rows, but I wondered if 
> there was a clever way to do it all in SQLite in, essentially, one statement.

Check out REPLACE():



Technically speaking this might mess up if the string '/path/to/' occurs in the 
middle of the string as well as at its beginning, so you might do some paranoid 
testing if you think this may occur.

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

[sqlite] Making string changes in a table

[Tim Streater]

I have a table with one column containing file paths, such as /path/to/file and 
/path/to/my/otherfile. Now I want to change all entries where the path starts 
as /path/to/ to /path/from/. Getting a candidate list is easy, and I can then 
make the changes in PHP and rewrite the rows, but I wondered if there was a 
clever way to do it all in SQLite in, essentially, one statement. A quick look 
persuades me there are not enough functions built into SQLite for that, but 
confirmation would be handy.

Thanks,


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

Re: [sqlite] Out of memory error for SELECT char();

[Yuriy Kaminskiy]

Eduardo Morras wrote:
> On Sat, 8 Mar 2014 14:09:17 -0500
> Richard Hipp  wrote:
>> It isn't really running out of memory
>>
>> The implementation of char() allocates 4 bytes of output buffer for
>> each input character, which is sufficient to hold any valid unicode
>> codepoint. But with zero input characters, that means it tries to
>> allocate a zero-byte output buffer.  sqlite3_malloc() returns NULL
>> when asked to allocate zero bytes, at which point the char()
>> implementation thinks that the malloc() failed and reports the
>> output-of-memory error.
> 
> It's OS dependant. From malloc FreeBSD man page [...]

malloc() behavior wrt 0-byte allocation is OS-dependent.
sqlite3_malloc() is not: it will return NULL on any OS, regardless of malloc()
implementation.

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

Re: [sqlite] Out of memory error for SELECT char();

[Eduardo Morras]

On Sat, 8 Mar 2014 14:09:17 -0500
Richard Hipp  wrote:


> It isn't really running out of memory
> 
> The implementation of char() allocates 4 bytes of output buffer for
> each input character, which is sufficient to hold any valid unicode
> codepoint. But with zero input characters, that means it tries to
> allocate a zero-byte output buffer.  sqlite3_malloc() returns NULL
> when asked to allocate zero bytes, at which point the char()
> implementation thinks that the malloc() failed and reports the
> output-of-memory error.

It's OS dependant. From malloc FreeBSD man page, malloc.conf/_malloc_options, V 
option means:

 V   Attempting to allocate zero bytes will return a NULL pointer
 instead of a valid pointer.  (The default behavior is to make a
 minimal allocation and return a pointer to it.)  This option is
 provided for System V compatibility.  This option is incompatible
 with the ``X'' option.

> 
> The fix is to allocate 4*N+1 bytes instead of 4*N bytes.  Dan is
> checking in the fix even as I type this reply.
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org

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

Re: [sqlite] Suggestion to add "locate" as a broader version of "instr"

[big stone]

Hello Max,

Your link is pretty interesting. It looks that :
- method1 should be easily implemented with SQLite "floating point"
representation,
- and with a very very small code size.

Here is the benchmarking of the two available methods :

https://raw.github.com/stonebig/ztest_donotuse/master/square_rooting_benchmark.GIF


So :
- your method is only 3 times slower than the python sqrt(),
- if SQLite team accepts to sacrifice a few bytes to implement sqrt(), we
may benefit a  389% speed-up (300/27*.652/1.86) at least.
(300/27*.652/1.86)

sqrt() is very interesting for statistics on-the-go over sql datas.

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