Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

2017-04-15 Thread Simon Slavin 

On 15 Apr 2017, at 9:14pm, petern  wrote:

> Yes, please include it in the FAQ

It’s not a FAQ.  Not on this list, at least.  I would argue against it.

I do agree that DRH’s explanation of why it’s not as important in SQLite as in 
client/server engines is well written.  We can point to it when we need it.

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

Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

2017-04-15 Thread petern 
Yes, please include it in the FAQ along with a description of the SQLite
stored procedure pattern syntax which is never disclosed in these replies:

CREATE TRIGGER  my_sproc INSTEAD OF INSERT on my_sproc_caller_view
BEGIN
--My procedural code to be prepared and stored in the database.
END;
--Called by the following syntax with specific VALUES():
INSERT INTO my_sproc_caller_view VALUES();
--And results returned in the requisite my_sproc_work_table upon which
my_sproc_caller_view is created.

As for the chattiness of client/server DB's, there is a more frequent cause
of that inefficient pattern.  In particular, there is often a ham fisted
design which keeps vital model data in application code or in tables which
are not SQL joined to get the final result.  Often the vendor will
encourage the same incompetent programmers to recast their inefficient
queries within the bodies of stored procedures as proof that the firm needs
to buy much larger and more expensive hardware and requisite software
licenses.  [Filed under vendor contracts for $435 hammers and $600 toilet
seats.]






On Sat, Apr 15, 2017 at 11:33 AM, Christian Werner <
christian.wer...@t-online.de> wrote:

> On 04/15/2017 06:18 PM, Richard Hipp wrote:
>
>> On 4/15/17, Manoj Sengottuvel  wrote:
>>
>>> Hi Richard,
>>>
>>> Is it possible to create the Stored Procedure (SP) in Sqlite?
>>>
>>> if not , is there any alternate way for SP?
>>>
>>
>> Short answer:  No.
>>
>> Longer answer:  With SQLite, your application is the stored procedure.
>> In a traditional client/server database like PostgreSQL or Oracle or
>> SQL Server, every SQL statement involves a round-trip to the server.
>> So there is a lot of latency with each command.  The way applications
>> overcome this latency is to put many queries into a stored procedure,
>> so that only the stored procedure invocation needs to travel over the
>> wire and latency is reduced to a single server round-trip.
>>
>> But with SQLite, each statement is just a procedure call.  There is no
>> network traffic, not IPC, and hence very little latency.  Applications
>> that use SQLite can be very "chatty" with the database and that is not
>> a problem.  For example, the SQLite website is backed by SQLite (duh!)
>> and a typical page request involves 200 to 300 separate queries.  That
>> would be a performance killer with a client/server database, but with
>> SQLite it is not a problem and the pages render in about 5
>> milliseconds.
>>
>
> May I vote this conversation to be included in the SQLite FAQ.
>
> Best,
> Christian
>
> ___
> 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] Threadsafe and _config() question

2017-04-15 Thread Simon Slavin 

On 15 Apr 2017, at 8:10pm, Richard Hipp  wrote:

> On 4/15/17, Simon Slavin  wrote:
>> Suppose I compile a copy of SQLite3 with SQLITE_THREADSAFE = 0.
>> 
>> Then suppose I execute
>> 
>>  sqlite3_config(SQLITE_CONFIG_SERIALIZED)
>> 
>> Would I get an error back ?
> 
> Yes.  Did you try it?

Thanks for the confirmation.  I tried it using macOS, and I got an error as I’d 
hoped, but I wasn’t sure whether it would apply to all possibly setups or I was 
just lucky.  I appreciate you telling me that someone throught through this 
problem.

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

Re: [sqlite] Threadsafe and _config() question

2017-04-15 Thread Richard Hipp 
On 4/15/17, Simon Slavin  wrote:
> Suppose I compile a copy of SQLite3 with SQLITE_THREADSAFE = 0.
>
> Then suppose I execute
>
>   sqlite3_config(SQLITE_CONFIG_SERIALIZED)
>
> Would I get an error back ?

Yes.  Did you try it?

-- 
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] Is it possible to create the Stored Procedure (SP) in Sqlite?

2017-04-15 Thread Christian Werner 

On 04/15/2017 06:18 PM, Richard Hipp wrote:

On 4/15/17, Manoj Sengottuvel  wrote:

Hi Richard,

Is it possible to create the Stored Procedure (SP) in Sqlite?

if not , is there any alternate way for SP?


Short answer:  No.

Longer answer:  With SQLite, your application is the stored procedure.
In a traditional client/server database like PostgreSQL or Oracle or
SQL Server, every SQL statement involves a round-trip to the server.
So there is a lot of latency with each command.  The way applications
overcome this latency is to put many queries into a stored procedure,
so that only the stored procedure invocation needs to travel over the
wire and latency is reduced to a single server round-trip.

But with SQLite, each statement is just a procedure call.  There is no
network traffic, not IPC, and hence very little latency.  Applications
that use SQLite can be very "chatty" with the database and that is not
a problem.  For example, the SQLite website is backed by SQLite (duh!)
and a typical page request involves 200 to 300 separate queries.  That
would be a performance killer with a client/server database, but with
SQLite it is not a problem and the pages render in about 5
milliseconds.


May I vote this conversation to be included in the SQLite FAQ.

Best,
Christian

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

Re: [sqlite] Malformed databases and multithreading

2017-04-15 Thread Simon Slavin 

On 14 Apr 2017, at 11:37pm, Simon Slavin  wrote:

> Have each program using SQLite execute
> 
>   sqlite3_config(SQLITE_CONFIG_MULTITHREAD)

That should have been

Have each program using SQLite execute

sqlite3_config(SQLITE_CONFIG_MULTITHREAD)

Apologies.

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

[sqlite] Threadsafe and _config() question

2017-04-15 Thread Simon Slavin 
Suppose I compile a copy of SQLite3 with SQLITE_THREADSAFE = 0.

Then suppose I execute

sqlite3_config(SQLITE_CONFIG_SERIALIZED)

Would I get an error back ?
If not, can this be considered a bug and fixed ?
If not, can the problem be documented ?

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

Re: [sqlite] Malformed databases and multithreading

2017-04-15 Thread Jens Alfke 

> On Apr 14, 2017, at 2:53 PM, Paul Egli  wrote:
> 
> On the "how to corrupt" page ( http://sqlite.org/howtocorrupt.html 
>  ) i
> do not see any mention of using SQLite in an incorrect way with
> respect to thread safety.

Huh, you’re right. That seems like an omission. This is covered elsewhere in 
the docs, but it should be on that page too.

> Is there really no way that, for example,
> using the same connection on multiple threads at the same time could
> either (a) corrupt the database, or (b) lead to a false reports of
> corruption?

Oh, it’s totally possible, if the connection doesn’t use the Serialized 
threading mode. (In fact it’s the most common cause of corruption that I’ve 
seen.)

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

Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

2017-04-15 Thread Richard Hipp 
On 4/15/17, Manoj Sengottuvel  wrote:
> Hi Richard,
>
> Is it possible to create the Stored Procedure (SP) in Sqlite?
>
> if not , is there any alternate way for SP?

Short answer:  No.

Longer answer:  With SQLite, your application is the stored procedure.
In a traditional client/server database like PostgreSQL or Oracle or
SQL Server, every SQL statement involves a round-trip to the server.
So there is a lot of latency with each command.  The way applications
overcome this latency is to put many queries into a stored procedure,
so that only the stored procedure invocation needs to travel over the
wire and latency is reduced to a single server round-trip.

But with SQLite, each statement is just a procedure call.  There is no
network traffic, not IPC, and hence very little latency.  Applications
that use SQLite can be very "chatty" with the database and that is not
a problem.  For example, the SQLite website is backed by SQLite (duh!)
and a typical page request involves 200 to 300 separate queries.  That
would be a performance killer with a client/server database, but with
SQLite it is not a problem and the pages render in about 5
milliseconds.
-- 
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] Is it possible to create the Stored Procedure (SP) in Sqlite?

2017-04-15 Thread Manoj Sengottuvel 
Hi Richard,

Is it possible to create the Stored Procedure (SP) in Sqlite?

if not , is there any alternate way for SP?


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

Re: [sqlite] Is this a foreign key bug ?

2017-04-15 Thread Domingo Alvarez Duarte 

Hello Dan !

Thank you for the reply !

It was my fault of knowledge here, thanks !


On 15/04/17 10:11, Dan Kennedy wrote:

On 04/15/2017 07:16 PM, Domingo Alvarez Duarte wrote:

Hello !

I'm getting an error that seems to be a bug in sqlite3 or a change of 
behavior.


===

CREATE TABLE a(
keyval varchar(25)  NOT NULL,
testid integer  NOT NULL,
PRIMARY KEY ("keyval","testid")
);

CREATE TABLE b(
sampleid integer  NOT NULL,
prodspeckey varchar(25)  NOT NULL,
PRIMARY KEY ("sampleid"),
CONSTRAINT "qasamples_ibfk_1" FOREIGN KEY ("prodspeckey") 
REFERENCES "a" ("keyval")

);

PRAGMA foreign_key_check;
===

output

===

sqlite3 < bug-fk.sql
Error: near line 14: foreign key mismatch - "b" referencing "a"


It's not a bug. There is no unique constraint on the parent key 
"a.keyval". "a.keyval" is part of a PRIMARY KEY, but is not by itself 
guaranteed to be unique. Hence the error.


This is the same case as the last example ("CREATE TABLE child7...") 
in the first block of code here:


  http://sqlite.org/foreignkeys.html#fk_indexes

Dan.


___
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] Is this a foreign key bug ?

2017-04-15 Thread Dan Kennedy 

On 04/15/2017 07:16 PM, Domingo Alvarez Duarte wrote:

Hello !

I'm getting an error that seems to be a bug in sqlite3 or a change of 
behavior.


===

CREATE TABLE a(
keyval varchar(25)  NOT NULL,
testid integer  NOT NULL,
PRIMARY KEY ("keyval","testid")
);

CREATE TABLE b(
sampleid integer  NOT NULL,
prodspeckey varchar(25)  NOT NULL,
PRIMARY KEY ("sampleid"),
CONSTRAINT "qasamples_ibfk_1" FOREIGN KEY ("prodspeckey") 
REFERENCES "a" ("keyval")

);

PRAGMA foreign_key_check;
===

output

===

sqlite3 < bug-fk.sql
Error: near line 14: foreign key mismatch - "b" referencing "a"


It's not a bug. There is no unique constraint on the parent key 
"a.keyval". "a.keyval" is part of a PRIMARY KEY, but is not by itself 
guaranteed to be unique. Hence the error.


This is the same case as the last example ("CREATE TABLE child7...") in 
the first block of code here:


  http://sqlite.org/foreignkeys.html#fk_indexes

Dan.


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

[sqlite] Is this a foreign key bug ?

2017-04-15 Thread Domingo Alvarez Duarte 

Hello !

I'm getting an error that seems to be a bug in sqlite3 or a change of 
behavior.


===

CREATE TABLE a(
keyval varchar(25)  NOT NULL,
testid integer  NOT NULL,
PRIMARY KEY ("keyval","testid")
);

CREATE TABLE b(
sampleid integer  NOT NULL,
prodspeckey varchar(25)  NOT NULL,
PRIMARY KEY ("sampleid"),
CONSTRAINT "qasamples_ibfk_1" FOREIGN KEY ("prodspeckey") 
REFERENCES "a" ("keyval")

);

PRAGMA foreign_key_check;
===

output

===

sqlite3 < bug-fk.sql
Error: near line 14: foreign key mismatch - "b" referencing "a"

===

Cheers !

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

[sqlite] strange behaviour on sqlite shell output…

2017-04-15 Thread aotto 

Hi…i have written a VTAB extension and using SHELL to view the results…

+ valgrind --leak-check=full --quiet gen/cltdb -column -echo -header 
gen/.my1 'select Reporter, Partner, Year, PartnerCode from [EXEC-BtH0] 
where Reporter = '\''AGO'\'' and
select Reporter, Partner, Year, PartnerCode from [EXEC-BtH0] where 
Reporter = 'AGO' and Partner = 'AUT'

ReporterPartner YearPartnerCode
--  --  --  
AGO AUT 2000EO EU WE €O $H
AGO AUT 2001EO EU WE €O $H
AGO AUT 2002EO EU WE €O $H
AGO AUT 2003EO EU WE €O $H
AGO AUT 2004EO EU WE €O $H
AGO AUT 2005EO EU WE €O $H
AGO AUT 2006EO EU WE €O $H
AGO AUT 2007EO EU WE €O $H
AGO AUT 2008EO EU WE €O $H
AGO AUT 2009EO EU WE €O $H
AGO AUT 2010EO EU WE €O $H
AGO AUT 2011EO EU WE €O $H
AGO AUT 2012EO EU WE €O $H
AGO AUT 2013EO EU WE €O $H
AGO AUT 2014EO EU WE €O $H
AGO AUT 2015EO EU WE €O $H

→ I have problem with the "PartnerCode" column… with the € sign… for 
DETAIL "where"
(where Reporter = '\''AGO'\'' and Partner = '\''AUT'\''') everything 
looks fine… "EO EU WE €O $H"


with LESS DETAIL "where"

+ valgrind --leak-check=full --quiet gen/cltdb -column -echo -header 
gen/.my1 'select Reporter, Partner, Year, PartnerCode from [EXEC-BtH0] 
where Reporter = '\''AGO'\'''
select Reporter, Partner, Year, PartnerCode from [EXEC-BtH0] where 
Reporter = 'AGO'

ReporterPartner YearPartnerCode
--  --  --  ---
AGO ABW 2000CA $H
AGO ABW 2001CA $H
AGO ABW 2002CA $H
...
AGO AUS 2009OC FR E5 $H
AGO AUS 2010OC FR E5 $H
AGO AUS 2011OC FR E5 $H
AGO AUS 2012OC FR E5 $H
AGO AUS 2013OC FR E5 $H
AGO AUS 2014OC FR E5 $H
AGO AUS 2015OC FR E5 $H
AGO AUT 2000EO EU WE 342202
AGO AUT 2001EO EU WE 342202
AGO AUT 2002EO EU WE 342202
AGO AUT 2003EO EU WE 342202
AGO AUT 2004EO EU WE 342202
AGO AUT 2005EO EU WE 342202
AGO AUT 2006EO EU WE 342202
AGO AUT 2007EO EU WE 342202
AGO AUT 2008EO EU WE 342202
AGO AUT 2009EO EU WE 342202
AGO AUT 2010EO EU WE 342202
AGO AUT 2011EO EU WE 342202
AGO AUT 2012EO EU WE 342202
AGO AUT 2013EO EU WE 342202
AGO AUT 2014EO EU WE 342202
AGO AUT 2015EO EU WE 342202
AGO AZE 2000AS ZW $h
AGO AZE 2001AS ZW $h

> i get strange numbers…"342202"

→ valgrind leakcheck says…everything is fine.

my compile command is…

ccache gcc -DNUM2 -c -o gen/shell.o -ggdb -Wall -Wextra -Isqlite 
-Ilibmsgque -Igen -I../libclt/build -D_HAVE_SQLITE_CONFIG_H -DDEBUG=1 
-DHAVE_READLINE=1 \
-Wno-extra -Wno-unused-value -Wno-unused-but-set-variable 
-Wno-unused-function \

shell.c
bin/Header.bash cltraw.c 1>gen/cltraw.h
ccache gcc -DNUM1 -c -o gen/cltdb.o -ggdb -Wall -Wextra -Isqlite 
-Ilibmsgque -Igen -I../libclt/build -D_HAVE_SQLITE_CONFIG_H -DDEBUG=1 
-Wno-unused-function cltdb.c
ccache gcc -DNUM1 -c -o gen/cltraw.o -ggdb -Wall -Wextra -Isqlite 
-Ilibmsgque -Igen -I../libclt/build -D_HAVE_SQLITE_CONFIG_H -DDEBUG=1 
-Wno-unused-function cltraw.c
ccache gcc -o gen/cltdb -ggdb -lreadline -lncurses gen/shell.o 
gen/cltdb.o gen/cltraw.o ../libclt/build/libclt.a 
libmsgque/.libs/libmsgque.a
ccache gcc -DNUM3 -std=gnu99 -c -o gen/abrain.o -ggdb -Wall -Wextra 
-Isqlite -Ilibmsgque -Igen -I../libclt/build -D_HAVE_SQLITE_CONFIG_H  
-DDEBUG=1 -DMQ_IGNORE_EXTERN \

-Wno-ignored-qualifiers -Wno-unused-parameter \
abrain.c
ccache gcc -o gen/msqdb -ggdb gen/abrain.o gen/cltdb.o gen/cltraw.o 
../libclt/build/libclt.a libmsgque/.libs/libmsgque.a

make[1]: Leaving directory '/home/dev1usr/WorldBank/clt/db'


thanks for your support !!



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