Re: [sqlite] Does sqlite support stored procedure?

[John Zhang]

Hi everyone,
Now the discussion has deviated from my original question.  But the topic 
is interesting.

We also access SQLite from javascript (through MOZILLA's firefox codebase). 
 Mozilla has a set of XPCOM components that allow easy Javscript access to 
SQLite (firefox uses SQLite for its internal use).  We have a javascript 
wrapper on top of their interface to make them easy to use.  It was looking 
through the Mozilla components we noticed that there was no stored procedure at 
all, hence the original question.  Our product - Jaxer - is open source, and we 
are releasing 1.0 RC today or tomorrow.  If you are interested in how we access 
SQLite from javascript, you can download Jaxer free (from http://aptana.com/), 
and the SQLite APIs are wrapped in ServerFramework.js.

Thanks,
John

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton
Sent: Tuesday, July 29, 2008 4:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Does sqlite support stored procedure?

I wrote an Sqlite module which links into Spidermonkey and connects to
Sqlite using the Sqlite API.

Stephen Woodbridge wrote:
> John Stanton wrote:
>
>>Adding Javascript to Sqlite as a stored procedure language was a fairly
>>simple operation.  Try it if you need stored procedures.
>
>
> This sounds really interesting.
>
> How to you make access to the sqlite3 api in javascript? Would you need
> to? Seems like you would need to at least provide some interface to the
> DB functions so you can do things like INSERT, UPDATE, DELETE, SELECT,
> and step through the results, etc.
>
> -Steve
>
>
>>BareFeet wrote:
>>
>>>Hi John,
>>>
>>>
>>>
I would like to know if SQLite supports stored procedures.
>>>
>>>Technically, no it doesn't.
>>>
>>>For what purpose do you want to store procedures?
>>>
>>>You can store some procedures in triggers, if you want to have SQLite
>>>trigger a task when some data is changed.
>>>
>>>You can simply create a "Procedures" table like this:
>>>
>>>create table "Procedures" (Name, SQL);
>>>
>>>and populate it with SQL procedures. You can call those procedures
>>>later from within your program and sqlite3 command line and execute
>>>them.
>>>
>>>Tom
>>>BareFeet
>>>
>>>  --
>>>Comparison of SQLite GUI applications:
>>>http://www.tandb.com.au/sqlite/compare/
>>>
>>>___
>>>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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does sqlite support stored procedure?

[John Stanton]

I wrote an Sqlite module which links into Spidermonkey and connects to 
Sqlite using the Sqlite API.

Stephen Woodbridge wrote:
> John Stanton wrote:
> 
>>Adding Javascript to Sqlite as a stored procedure language was a fairly 
>>simple operation.  Try it if you need stored procedures.
> 
> 
> This sounds really interesting.
> 
> How to you make access to the sqlite3 api in javascript? Would you need 
> to? Seems like you would need to at least provide some interface to the 
> DB functions so you can do things like INSERT, UPDATE, DELETE, SELECT, 
> and step through the results, etc.
> 
> -Steve
> 
> 
>>BareFeet wrote:
>>
>>>Hi John,
>>>
>>>
>>>
I would like to know if SQLite supports stored procedures.
>>>
>>>Technically, no it doesn't.
>>>
>>>For what purpose do you want to store procedures?
>>>
>>>You can store some procedures in triggers, if you want to have SQLite  
>>>trigger a task when some data is changed.
>>>
>>>You can simply create a "Procedures" table like this:
>>>
>>>create table "Procedures" (Name, SQL);
>>>
>>>and populate it with SQL procedures. You can call those procedures  
>>>later from within your program and sqlite3 command line and execute  
>>>them.
>>>
>>>Tom
>>>BareFeet
>>>
>>>  --
>>>Comparison of SQLite GUI applications:
>>>http://www.tandb.com.au/sqlite/compare/
>>>
>>>___
>>>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] Does sqlite support stored procedure?

[John Stanton]

I just added an Sqlite interface into Spidermonkey.  I can send you the 
code if you are interested.  We used Javascript instead of PL/SQL 
because it is so well known and the backend code which executes in the 
DB matches the frontend stuff running in the browser and there is less 
learning involved.

We use it to store business and other rules in the DB.

Shawn Wilsher wrote:
> On Tue, Jul 29, 2008 at 11:39 AM, John Stanton <[EMAIL PROTECTED]> wrote:
> 
>>Adding Javascript to Sqlite as a stored procedure language was a fairly
>>simple operation.  Try it if you need stored procedures.
> 
> Woah - that sounds neat and something interesting to the Mozilla
> project.  Care to elaborate on this?
> 
> Cheers,
> 
> Shawn Wilsher
> Mozilla Developer
> ___
> 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] Does sqlite support stored procedure?

[Harold Wood & Meyuni Gani]

Can you send examples?
Thanks


Woody
from his pda

-Original Message-
From: John Stanton <[EMAIL PROTECTED]>
Sent: Tuesday, July 29, 2008 11:39 AM
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] Does sqlite support stored procedure?

Adding Javascript to Sqlite as a stored procedure language was a fairly 
simple operation.  Try it if you need stored procedures.

BareFeet wrote:
> Hi John,
> 
> 
>>I would like to know if SQLite supports stored procedures.
> 
> 
> Technically, no it doesn't.
> 
> For what purpose do you want to store procedures?
> 
> You can store some procedures in triggers, if you want to have SQLite  
> trigger a task when some data is changed.
> 
> You can simply create a "Procedures" table like this:
> 
> create table "Procedures" (Name, SQL);
> 
> and populate it with SQL procedures. You can call those procedures  
> later from within your program and sqlite3 command line and execute  
> them.
> 
> Tom
> BareFeet
> 
>   --
> Comparison of SQLite GUI applications:
> http://www.tandb.com.au/sqlite/compare/
> 
> ___
> 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] winDelete retry-on-failure functionality isn't working

[Jeremy Spiegel]

Hi,

winDelete in os_win.c has retry functionality to try multiple times to
delete a file if a virus scanner or indexing program has a handle open
on that file.  We've seen SQLite failures that have been tracked down to
other apps temporarily opening our db journal files, so we believe that
the retry behavior could be very valuable to us.

In order to check for failure, winDelete checks that DeleteFileW returns
zero and that GetFileAttributesW doesn't return INVALID_FILE_ATTRIBUTES.
However, when I try calling DeleteFileW on a file with a handle open on
that file, I see DeleteFileW returning 1 (success) and I see
GetFileAttributesW returning INVALID_FILE_ATTRIBUTES, I think because
even though the file still exists it is in a "delete pending" state.

The code below illustrates the problem.  Thanks for any help you can
provide!

:) Jeremy Spiegel


#include 
#include 
#include 

int _tmain(int argc, _TCHAR* argv[])
{
DWORD dwDesiredAccess = GENERIC_READ | GENERIC_WRITE;
DWORD dwShareMode = FILE_SHARE_READ | FILE_SHARE_WRITE |
FILE_SHARE_DELETE;
DWORD dwCreationDisposition = OPEN_ALWAYS;
DWORD dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY
| FILE_ATTRIBUTE_HIDDEN
| FILE_FLAG_DELETE_ON_CLOSE
| FILE_FLAG_RANDOM_ACCESS;

HANDLE h = CreateFileW(L"c:\\test.txt",
dwDesiredAccess,
dwShareMode,
NULL,
dwCreationDisposition,
dwFlagsAndAttributes,
NULL
);

if( h==INVALID_HANDLE_VALUE )
printf("error\n");

int rc = DeleteFileW( L"c:\\test.txt" );
if ( rc == 0 )
printf("DeleteFileW failed\n");

if ( GetFileAttributesW( L"c:\\test.txt" ) != 0x )
printf( "File still exists\n");

HANDLE h2 = CreateFileW(L"c:\\test.txt",
dwDesiredAccess,
dwShareMode,
NULL,
dwCreationDisposition,
dwFlagsAndAttributes,
NULL
);

if ( h2 == INVALID_HANDLE_VALUE )
printf( "Error: 0x%x", GetLastError() );

CloseHandle(h);
return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert the CURRENT_TIMESTAMP

[Joanne Pham]

Hi All,
I still have the problem to set the result of the below statement to variable 
so I can print out mulitple times without the executing the select statement 
over and over again.
If you have a solution/syntax to set variable please share with me.
Thank,
JP
 select '#device local time = ' ||
 (case strftime('%w', d) when '0' then 'SUN' when '1' then 'MON' when '2' then 
'TUE' when '3' then 'WED' when '4' then 'THUR' when '5' then 'FRI' when '6' 
then 'SAT' end)  || ' ' ||
(case strftime('%m', d) when '01' then 'JAN'  when '02' then 'FEB' when '03' 
then 'MAR' when '04' then 'APR' when '05' then 'MAY' when '06' then 'JUN' when 
'07' then 'JUL'  when '08' then 'AUG' when '09' then 'SEP' when '10' then 'OCT' 
when '11' then 'NOV'  when '12' then 'DEC' end) || ' ' ||
strftime('%d %H:%M:%S %Y', d,'localtime') || ', ' 
from (select CURRENT_TIMESTAMP as d)   ;




- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Monday, July 28, 2008 10:57:22 AM
Subject: Re: [sqlite] Convert the CURRENT_TIMESTAMP

Hi,
Thanks for the big help.
Finally I got it worked as expected and the sql statement below to return the 
format as: #device local time = MON JUL 28 10:57:30 2008.
Another question that I have is to set this select statement in the variable so 
I can repeated to print out the variable again and again in different section 
without repeat the long select statement
I would like to set the result of below statement to variable so I print it 
again without repeating the long select statement. Would you please help.
Thanks,
JP

 select '#device local time = ' ||
 (case strftime('%w', d) when '0' then 'SUN' when '1' then 'MON' when '2' then 
'TUE' when '3' then 'WED' when '4' then 'THUR' when '5' then 'FRI' when '6' 
then 'SAT' end)  || ' ' ||
(case strftime('%m', d) when '01' then 'JAN'  when '02' then 'FEB' when '03' 
then 'MAR' when '04' then 'APR' when '05' then 'MAY' when '06' then 'JUN' when 
'07' then 'JUL'  when '08' then 'AUG' when '09' then 'SEP' when '10' then 'OCT' 
when '11' then 'NOV'  when '12' then 'DEC' end) || ' ' ||
strftime('%d %H:%M:%S %Y', d,'localtime') || ', ' 
from (select CURRENT_TIMESTAMP as d)   ;



- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, July 25, 2008 9:21:26 PM
Subject: Re: [sqlite] Convert the CURRENT_TIMESTAMP

"Joanne Pham" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> Hi all ,
> I have the following statement to convert the CURRENT_TIMESTAMP to
> format as
> TUE JULY 25 23:11:13 2008 but I got the empty string. Can you help me
> why the empty string is returned.
> Below is sql statement:
> select
> (case strftime('%w', d) when 0 then 'SUN' when 1 then 'MON’ when 2
> then ‘TUE’ when 3 then ‘WED’ when 4 then ‘THUR’ when 5 then ‘FRI’
> when 6 then ‘SAT’ end) || ‘ ‘ ||
> (case strftime('%m', d) when 1 then 'JAN' when 2 then ‘FEB’ when 3
> then ‘MAR’ when 4 then ‘APR’ when 5 then ‘MAY’ when 6 then ‘JUN’ when
> 7 then ‘JUL’ when 8 then “AUG’ when 9 then ‘SEP’ when 10 then ‘OCT’
> when 11 then ‘NOV’ when 12 then 'DEC' end)
>>> ' ' ||
> strftime('%d %H:%M:%S %Y', d)
> from (select CURRENT_TIMESTAMP as d);

Change strftime('%w', d) to CAST(strftime('%w', d) as INTEGER), and 
similarly for strftime('%m', d). strftime returns a string, which 
doesn't match any condition in the CASE statement, so the statement 
produces NULL, and then the whole expression becomes NULL. To avoid 
this, the result of strftime needs to be converted to integer.

And fix smart quotes ‘’ to plain apostrophe '

Igor Tandetnik 


      
___
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] solaris 9 build instructions

[Bill Shurtleff]

Hello,

I found & extracted the source. Now are there any build instructions for 
Solaris 9 anywhere? I haven't been on UNIX in a long time. Also, if there are 
any dependencies is there a list somewhere?

Thanks,
Bill Shurtleff

Bill Shurtleff
Sr. Software Engineer
Petris Technology www.petris.com
Support: www.petris.com/support or e-mail [EMAIL 
PROTECTED]

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

Re: [sqlite] Does sqlite support stored procedure?

[Stephen Woodbridge]

John Stanton wrote:
> Adding Javascript to Sqlite as a stored procedure language was a fairly 
> simple operation.  Try it if you need stored procedures.

This sounds really interesting.

How to you make access to the sqlite3 api in javascript? Would you need 
to? Seems like you would need to at least provide some interface to the 
DB functions so you can do things like INSERT, UPDATE, DELETE, SELECT, 
and step through the results, etc.

-Steve

> BareFeet wrote:
>> Hi John,
>>
>>
>>> I would like to know if SQLite supports stored procedures.
>>
>> Technically, no it doesn't.
>>
>> For what purpose do you want to store procedures?
>>
>> You can store some procedures in triggers, if you want to have SQLite  
>> trigger a task when some data is changed.
>>
>> You can simply create a "Procedures" table like this:
>>
>> create table "Procedures" (Name, SQL);
>>
>> and populate it with SQL procedures. You can call those procedures  
>> later from within your program and sqlite3 command line and execute  
>> them.
>>
>> Tom
>> BareFeet
>>
>>   --
>> Comparison of SQLite GUI applications:
>> http://www.tandb.com.au/sqlite/compare/
>>
>> ___
>> 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] Does sqlite support stored procedure?

[Shawn Wilsher]

On Tue, Jul 29, 2008 at 11:39 AM, John Stanton <[EMAIL PROTECTED]> wrote:
> Adding Javascript to Sqlite as a stored procedure language was a fairly
> simple operation.  Try it if you need stored procedures.
Woah - that sounds neat and something interesting to the Mozilla
project.  Care to elaborate on this?

Cheers,

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

Re: [sqlite] Does sqlite support stored procedure?

[John Stanton]

Adding Javascript to Sqlite as a stored procedure language was a fairly 
simple operation.  Try it if you need stored procedures.

BareFeet wrote:
> Hi John,
> 
> 
>>I would like to know if SQLite supports stored procedures.
> 
> 
> Technically, no it doesn't.
> 
> For what purpose do you want to store procedures?
> 
> You can store some procedures in triggers, if you want to have SQLite  
> trigger a task when some data is changed.
> 
> You can simply create a "Procedures" table like this:
> 
> create table "Procedures" (Name, SQL);
> 
> and populate it with SQL procedures. You can call those procedures  
> later from within your program and sqlite3 command line and execute  
> them.
> 
> Tom
> BareFeet
> 
>   --
> Comparison of SQLite GUI applications:
> http://www.tandb.com.au/sqlite/compare/
> 
> ___
> 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] what is the best amalgamation for a build on Solaris 9?

[Bill Shurtleff]

Hi,

I want to build Sqlite on Solaris 9. What is the best amalgamation? Are there 
any build Makefiles for building on Solaris? Any input on this will be helpful.

Thanks,
Bill Shurtleff

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

Re: [sqlite] db vs shell

[Ken]

Using sqlite 3.5.9: 

My results: 
Piped shell
real 27.91
user 27.21
sys 0.78
DB :
real 29.59
user 28.57
sys 0.96

Perhaps this is an issue with 3.6.0 ? 
Piped:
real 28.44
user 27.85
sys 0.86

DB:
real 74.24
user 72.96
sys 0.94

I compliled sqlite 3.6.0 from the full source. When running both seem to 
consume approximately the same amount of ram.  

Ken

--- On Tue, 7/29/08, Stephen Woodbridge <[EMAIL PROTECTED]> wrote:
From: Stephen Woodbridge <[EMAIL PROTECTED]>
Subject: Re: [sqlite] db vs shell
To: "General Discussion of SQLite Database" 
Date: Tuesday, July 29, 2008, 10:35 AM

I'm seeing a similar speed different with the 3X performance difference:

[EMAIL PROTECTED]:~/work$ true && ( set -x
 > sqlite3 sample.db 'create table bar (foo text)'
 > seq -w 1 200 | sed 's/^/id/' > list.txt
 > sqlite3 sample.db '.imp "list.txt" "bar"'
 > time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort
| 
uniq | wc -l
 > time -p sqlite3 sample.db 'select count(distinct foo) from bar ;
'
 > )
+ sqlite3 sample.db 'create table bar (foo text)'
+ seq -w 1 200
+ sed 's/^/id/'
+ sqlite3 sample.db '.imp "list.txt" "bar"'
+ uniq
+ sort
+ uniq
+ sqlite3 sample.db 'select foo from bar ; '
+ wc -l
110
real 7.05
user 8.71
sys 0.36
+ sqlite3 sample.db 'select count(distinct foo) from bar ; '
110
real 21.53
user 21.03
sys 0.24

SQLite version 3.6.0

Linux carto 2.6.15-1-em64t-p4-smp #2 SMP Tue Mar 7 08:19:39 UTC 2006 
x86_64 GNU/Linux

-Steve

Robert Citek wrote:
> On Tue, Jul 29, 2008 at 2:35 AM,  <[EMAIL PROTECTED]> wrote:
>> On Tue, Jul 29, 2008 at 02:29:53AM -0500, Robert Citek wrote:
>>> $ sqlite3 -version
>>> 3.4.2
>> On 3.4.0 and 3.5.9 here, the pure-SQL version is -much- faster than
the shell
>> pipe. Could you tell us more about the contents of your database?
> 
> The column contains a list of text items.  This script demonstrates
> the phenomenon:
> 
> true && ( set -x
> sqlite3 sample.db 'create table bar (foo text)'
> seq -w 1 200 | sed 's/^/id/' > list.txt
> sqlite3 sample.db '.imp "list.txt" "bar"'
> time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort |
uniq | wc -l
> time -p sqlite3 sample.db 'select count(distinct foo) from bar ; '
> )
> 
> Output:
> 
> + sqlite3 sample.db 'create table bar (foo text)'
> + seq -w 1 200
> + sed 's/^/id/'
> + sqlite3 sample.db '.imp "list.txt" "bar"'
> + sqlite3 sample.db 'select foo from bar ; '
> + uniq
> + sort
> + uniq
> + wc -l
> 200
> real 3.25
> user 3.71
> sys 0.47
> + sqlite3 sample.db 'select count(distinct foo) from bar ; '
> 200
> real 22.48
> user 20.98
> sys 0.28
> 
> Regards,
> - Robert
> ___
> 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] db vs shell

[Dan]

Do things improve any if you increase the temporary cache size?
Compile with -DSQLITE_DEFAULT_TEMP_CACHE=100 or something?

How much memory does the [sort] process consume in the shell
version? What percentage of records are being trimmed by the
first [uniq] in the pipeline?

Dan.

On Jul 29, 2008, at 1:26 PM, Robert Citek wrote:

> Was doing some DB operations and felt they were going slower than they
> should.  So I did this quick test:
>
> $ time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort |
> uniq | wc -l
> 209
> real 5.64
> user 5.36
> sys 1.51
>
> $ time -p sqlite3 sample.db 'select count(distinct foo) from bar ; '
> 209
> real 29.71
> user 26.09
> sys 1.32
>
> Why the difference in time?
> What can I do to make the DB operate closer to the times within the  
> shell?
>
> Regards,
> - Robert
> ___
> 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] db vs shell

[Stephen Woodbridge]

I'm seeing a similar speed different with the 3X performance difference:

[EMAIL PROTECTED]:~/work$ true && ( set -x
 > sqlite3 sample.db 'create table bar (foo text)'
 > seq -w 1 200 | sed 's/^/id/' > list.txt
 > sqlite3 sample.db '.imp "list.txt" "bar"'
 > time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | 
uniq | wc -l
 > time -p sqlite3 sample.db 'select count(distinct foo) from bar ; '
 > )
+ sqlite3 sample.db 'create table bar (foo text)'
+ seq -w 1 200
+ sed 's/^/id/'
+ sqlite3 sample.db '.imp "list.txt" "bar"'
+ uniq
+ sort
+ uniq
+ sqlite3 sample.db 'select foo from bar ; '
+ wc -l
110
real 7.05
user 8.71
sys 0.36
+ sqlite3 sample.db 'select count(distinct foo) from bar ; '
110
real 21.53
user 21.03
sys 0.24

SQLite version 3.6.0

Linux carto 2.6.15-1-em64t-p4-smp #2 SMP Tue Mar 7 08:19:39 UTC 2006 
x86_64 GNU/Linux

-Steve

Robert Citek wrote:
> On Tue, Jul 29, 2008 at 2:35 AM,  <[EMAIL PROTECTED]> wrote:
>> On Tue, Jul 29, 2008 at 02:29:53AM -0500, Robert Citek wrote:
>>> $ sqlite3 -version
>>> 3.4.2
>> On 3.4.0 and 3.5.9 here, the pure-SQL version is -much- faster than the shell
>> pipe. Could you tell us more about the contents of your database?
> 
> The column contains a list of text items.  This script demonstrates
> the phenomenon:
> 
> true && ( set -x
> sqlite3 sample.db 'create table bar (foo text)'
> seq -w 1 200 | sed 's/^/id/' > list.txt
> sqlite3 sample.db '.imp "list.txt" "bar"'
> time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc 
> -l
> time -p sqlite3 sample.db 'select count(distinct foo) from bar ; '
> )
> 
> Output:
> 
> + sqlite3 sample.db 'create table bar (foo text)'
> + seq -w 1 200
> + sed 's/^/id/'
> + sqlite3 sample.db '.imp "list.txt" "bar"'
> + sqlite3 sample.db 'select foo from bar ; '
> + uniq
> + sort
> + uniq
> + wc -l
> 200
> real 3.25
> user 3.71
> sys 0.47
> + sqlite3 sample.db 'select count(distinct foo) from bar ; '
> 200
> real 22.48
> user 20.98
> sys 0.28
> 
> Regards,
> - Robert
> ___
> 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] FTS statistics and stemming

[Alexey Pechnikov]

Hello!

В сообщении от Tuesday 29 July 2008 00:18:40 Scott Hess написал(а):
> On Sat, Jul 26, 2008 at 1:28 PM, Stephen Woodbridge
>
> <[EMAIL PROTECTED]> wrote:
> > Alexey Pechnikov wrote:
> >> I'm know that ispell, myspell, hunspell and trigrams are used in
> >> PostgreSQL FTS. A lot of languages are supported this. And soundex
> >> function useful for morphology search if to write word by latin alphabet
> >> (transliteration by replace each symbol of national alphabet by one or
> >> more latin):
>
> 
>
> >> There is stemming in Apache Lucene, Sphinx (included morphology by
> >> soundex) and Xapian too.
> >>
> >> Are these futures planned to be in SQLIte FTS?
> >
> > Well, I will leave the question of plans to Scott Hess the FTS developer
> > to answer.
>
> Unfortunately, my interests don't really run towards implementing
> useful new stemmers.  I mean, I could, but I'm unlikely to do a good
> job unless I'm doing it because it scratches some engineering itch I
> have.  I tend to have more interest in infrastructure-y things, like
> how to safely encode/decode data structures.  I know this is an
> unsatisfactory answer :-).

Well, we can to implement our stemmers and waiting new high-performance data 
structures from you :-)

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

Re: [sqlite] db vs shell

[Robert Citek]

On Tue, Jul 29, 2008 at 4:25 AM,  <[EMAIL PROTECTED]> wrote:
> On Tue, Jul 29, 2008 at 03:27:20AM -0500, Robert Citek wrote:
>> real 3.25
> ..
>> real 22.48
>
> I'm seeing the second being twice as -fast- as the first one here, still.

I don't follow.  22/3 ~ 7.  Or do you mean when you run the same
script on your machine?

> How many CPU cores are in your testing machine? Parallel execution
> -might- explain the difference.

Tried this on two different machine, both dual cores.  Same sqlite3
version but with slightly different kernels (2.6.22 vs 2.6.24).
Similar results:

+ sqlite3 sample.db 'select foo from bar ; '
+ uniq
+ sort
+ uniq
+ wc -l
200
real 4.33
user 5.01
sys 0.66
+ sqlite3 sample.db 'select count(distinct foo) from bar ; '
200
real 29.67
user 29.12
sys 0.43

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

Re: [sqlite] threads and database lock

[Sébastien Escudier]

Quoting Igor Tandetnik <[EMAIL PROTECTED]>:
> How do you think this is applicable? It talks about multiple threads
> running statements on the same connection

Yes, and this is the first thing I tried, but in that case, the BEGIN EXCLUSIVE;
is useless for queries from other threads.

> With multiple connections - yes. See also sqlite3_busy_handler,
> sqlite3_busy_timeout.

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

Re: [sqlite] threads and database lock

[Igor Tandetnik]

"Sébastien Escudier"
<[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> now I tried with two different handle, one in each thread
>
> In these messages I read :
> "with SQLite 3.5, access to each
> database connection is serialized.  So even though the interface
> allows you to have 20 different threads all doing sqlite3_exec()
> on the same connection at the same time, the SQL statements are
> still being processed one by one"
>
> Do I misunderstand this ?

How do you think this is applicable? It talks about multiple threads 
running statements on the same connection, whereas you have two 
different connections.

> Do I still have to handle this myself with SQLITE_BUSY tests or
> mutexes ?

With multiple connections - yes. See also sqlite3_busy_handler, 
sqlite3_busy_timeout.

Igor Tandetnik



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

[sqlite] PRAGMA synchronous

[Alexey Pechnikov]

Hello!

Can I'm using "PRAGMA synchronous" on per-database basis? How to attach 
database with "PRAGMA synchronous = OFF;" mode to main database with "PRAGMA 
synchronous = ON;" mode?

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

Re: [sqlite] db vs shell

[peter]

On Tue, Jul 29, 2008 at 03:27:20AM -0500, Robert Citek wrote:
> real 3.25
..
> real 22.48

I'm seeing the second being twice as -fast- as the first one here, still.
How many CPU cores are in your testing machine? Parallel execution
-might- explain the difference.

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

Re: [sqlite] db vs shell

[Simon Davies]

2008/7/29 Robert Citek <[EMAIL PROTECTED]>:
> On Tue, Jul 29, 2008 at 2:35 AM,  <[EMAIL PROTECTED]> wrote:
>> On Tue, Jul 29, 2008 at 02:29:53AM -0500, Robert Citek wrote:
>>> $ sqlite3 -version
>>> 3.4.2
>>
>> On 3.4.0 and 3.5.9 here, the pure-SQL version is -much- faster than the shell
>> pipe. Could you tell us more about the contents of your database?
>
> The column contains a list of text items.  This script demonstrates
> the phenomenon:
>
> true && ( set -x
> sqlite3 sample.db 'create table bar (foo text)'
> seq -w 1 200 | sed 's/^/id/' > list.txt
> sqlite3 sample.db '.imp "list.txt" "bar"'
> time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc 
> -l
> time -p sqlite3 sample.db 'select count(distinct foo) from bar ; '
> )
>
> Output:
>
> + sqlite3 sample.db 'create table bar (foo text)'
> + seq -w 1 200
> + sed 's/^/id/'
> + sqlite3 sample.db '.imp "list.txt" "bar"'
> + sqlite3 sample.db 'select foo from bar ; '
> + uniq
> + sort
> + uniq
> + wc -l
> 200
> real 3.25
> user 3.71
> sys 0.47
> + sqlite3 sample.db 'select count(distinct foo) from bar ; '
> 200
> real 22.48
> user 20.98
> sys 0.28
>
> Regards,
> - Robert

Hi Robert,

on my XP machine I get:

> sqlite3 -version
3.6.0

> true && ( set -x
> sqlite3 sample.db 'create table bar (foo text)'
> seq -w 1 200 | sed 's/^/id/' > list.txt
> sqlite3 sample.db '.imp "list.txt" "bar"'
> time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc 
> -l
> time -p sqlite3 sample.db 'select count(distinct foo) from bar ; '
> )
+ sqlite3 sample.db 'create table bar (foo text)'
+ seq -w 1 200
+ sed 's/^/id/'
+ sqlite3 sample.db '.imp "list.txt" "bar"'
+ sqlite3 sample.db 'select foo from bar ; '
+ uniq
+ sort
+ uniq
+ wc -l
200
real 12.67
user 10.63
sys 3.91
+ sqlite3 sample.db 'select count(distinct foo) from bar ; '
200
real 13.59
user 0.01
sys 0.01

> alias sqlite3=sjd_old_sqlite3.exe
> sqlite3 -version
3.4.2
> time sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc -l
200

real0m12.883s
user0m10.870s
sys 0m3.856s

> time sqlite3 sample.db 'select count( distinct foo ) from bar ;'
200

real0m14.888s
user0m0.015s
sys 0m0.015s

-Nothing like the discrepancy you are seeing...
Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] db vs shell

[Robert Citek]

On Tue, Jul 29, 2008 at 2:35 AM,  <[EMAIL PROTECTED]> wrote:
> On Tue, Jul 29, 2008 at 02:29:53AM -0500, Robert Citek wrote:
>> $ sqlite3 -version
>> 3.4.2
>
> On 3.4.0 and 3.5.9 here, the pure-SQL version is -much- faster than the shell
> pipe. Could you tell us more about the contents of your database?

The column contains a list of text items.  This script demonstrates
the phenomenon:

true && ( set -x
sqlite3 sample.db 'create table bar (foo text)'
seq -w 1 200 | sed 's/^/id/' > list.txt
sqlite3 sample.db '.imp "list.txt" "bar"'
time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc -l
time -p sqlite3 sample.db 'select count(distinct foo) from bar ; '
)

Output:

+ sqlite3 sample.db 'create table bar (foo text)'
+ seq -w 1 200
+ sed 's/^/id/'
+ sqlite3 sample.db '.imp "list.txt" "bar"'
+ sqlite3 sample.db 'select foo from bar ; '
+ uniq
+ sort
+ uniq
+ wc -l
200
real 3.25
user 3.71
sys 0.47
+ sqlite3 sample.db 'select count(distinct foo) from bar ; '
200
real 22.48
user 20.98
sys 0.28

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

Re: [sqlite] db vs shell

[peter]

On Tue, Jul 29, 2008 at 02:29:53AM -0500, Robert Citek wrote:
> $ sqlite3 -version
> 3.4.2

On 3.4.0 and 3.5.9 here, the pure-SQL version is -much- faster than the shell
pipe. Could you tell us more about the contents of your database?

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

Re: [sqlite] db vs shell

[Robert Citek]

On Tue, Jul 29, 2008 at 2:23 AM,  <[EMAIL PROTECTED]> wrote:
> On Tue, Jul 29, 2008 at 02:15:54AM -0500, Robert Citek wrote:
>> Are you sure time ignores everything after the pipe?
>
> Seems to depend on shell version - when I tested it here it definitely
> ignored everything after. Yours seems to do the right thing, which makes
> your sqlite issue an interesting find indeed.

Some more info which may help:

$ sqlite3 -version
3.4.2

$ uname -a
Linux Ubuntu804 2.6.24-19-generic #1 SMP Fri Jul 11 23:41:49 UTC 2008
i686 GNU/Linux

$ echo $BASH_VERSION
3.2.39(1)-release

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

Re: [sqlite] threads and database lock

[Sébastien Escudier]

now I tried with two different handle, one in each thread (and each thread
execute a sqlite3_open), but the insertion fails with : 'database is locked'
error.
I thought this was handled by sqlite since 3.5 version.
http://www.mail-archive.com/sqlite-users@sqlite.org/msg28089.html
http://www.mail-archive.com/sqlite-users@sqlite.org/msg28099.html

In these messages I read :
"with SQLite 3.5, access to each
database connection is serialized.  So even though the interface
allows you to have 20 different threads all doing sqlite3_exec()
on the same connection at the same time, the SQL statements are
still being processed one by one"

Do I misunderstand this ?
Do I still have to handle this myself with SQLITE_BUSY tests or mutexes ?

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

Re: [sqlite] db vs shell

[peter]

On Tue, Jul 29, 2008 at 02:15:54AM -0500, Robert Citek wrote:
> Are you sure time ignores everything after the pipe?

Seems to depend on shell version - when I tested it here it definitely
ignored everything after. Yours seems to do the right thing, which makes
your sqlite issue an interesting find indeed.

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

Re: [sqlite] db vs shell

[Robert Citek]

On Tue, Jul 29, 2008 at 1:31 AM,  <[EMAIL PROTECTED]> wrote:
> On Tue, Jul 29, 2008 at 01:26:54AM -0500, Robert Citek wrote:
>> Why the difference in time?
>
> Your first test is only measuring how long sqlite needs to 'select foo from 
> bar';
> all the commands after the pipe are ignored by 'time'.

Are you sure time ignores everything after the pipe?

$ time -p echo | uniq | sort | uniq | wc -l
1
real 0.00
user 0.00
sys 0.00

 $ time -p echo | uniq | sort | uniq | wc -l | sleep 10
real 10.00
user 0.00
sys 0.00

> Try this: time -p sh -c "sqlite3 sample.db 'select foo from bar ; ' | uniq | 
> sort | uniq | wc -l"

$ time -p sh -c "sqlite3 refseq.db 'select foo from bar ; ' | uniq |
sort | uniq | wc -l"
209
real 5.76
user 5.35
sys 1.61

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