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_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
Re: [sqlite] Out of memory error for SELECT char();
On Sat, 8 Mar 2014 14:09:17 -0500 Richard Hipp d...@sqlite.org 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 emorr...@yahoo.es ___ 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 wrote: On Sat, 8 Mar 2014 14:09:17 -0500 Richard Hipp d...@sqlite.org 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
[sqlite] Making string changes in a table
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] Making string changes in a table
On 9 Mar 2014, at 10:05pm, Tim Streater t...@clothears.org.uk 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(): http://www.sqlite.org/lang_corefunc.html 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
Re: [sqlite] Making string changes in a table
On 3/9/14, Simon Slavin slav...@bigfraud.org 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
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
On 09 Mar 2014 at 22:17, Igor Tandetnik i...@tandetnik.org 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
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] Suggestion to add locate as a broader version of instr
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] Suggestion to add locate as a broader version of instr
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 kmedc...@dessus.com 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
On Sunday, 9 March, 2014 19:38, Stephen Chrzanowski pontia...@gmail.com 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 kmedc...@dessus.com 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
On 10 Mar 2014, at 1:38am, Stephen Chrzanowski pontia...@gmail.com 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
