Re: [sqlite] Stored Procedures

[Warren Young]

On May 14, 2018, at 9:44 PM, Alek Paunov  wrote:
> 
> Practical example of the benefit is that e.g. same complex turnover report 
> which implementation includes bunch of intermediate calculations, could be 
> used with same SQL call from Python desktop application, directly as Excel 
> Data Source or on partial replica through SQL.js [1] in the browser, without 
> coding the same thing 3 times: on Python, VBA and JS.

How common is that for a DBMS that should never be used over a network?  That 
is, all three applications would have to be running on the same machine for 
proper data safety.

I get why a client-server DBMS would have a mix of strange applications in many 
languages accessing it, but SQLite?  Don’t you tend to just pick one 
implementation language appropriate to the delivery platform?

> How many list readers really missed the Stored procedures feature?

I’m with drh: stored procedures are a nasty hack needed only to minimize RTT in 
the high-latency LAN (or WAN!) environment.  The cost of this hack is 
proprietary lock-in, since AFAIK no DBMS supports another’s stored procedure 
dialect.

I’m basing that on a skim of 
https://en.wikipedia.org/wiki/Stored_procedure#Implementation  Apparently 
SQL/PSM is a “standard,” but poorly supported in the market, according to that 
article, with all other DBMSes going off in their own directions.

If you have multiple applications accessing a given SQLite DB on a single 
computer, chances are that they’re all coded in the same programming language, 
so you can just write your shared procedures in a library for that language.

If it happens that you really are using multiple languages on a single computer 
or the same DB has to be cross-compatible among disparate machines with 
different programs (e.g. desktop and mobile) then you can probably write your 
shared procedure library in a statically-compiled language that can export a 
C-compatible ABI, then link to it from any language you like, since most 
practical non-C family languages have C-compatible FFI mechanisms.

You need a way to link to SQLite in the first place, right?  In fact, why not 
just use C to extend SQLite, adding the functions you need?

> My humble opinion is that for us, the minority, the best move is to look for 
> approaches and collaboration on "incubating" valuable advanced features 
> initially outside of the SQLite core, then eventually apply result solutions 
> in our own projects and finally propose for inclusion in the core library 
> only small key components (which lead to inefficiencies or maintenance burden 
> when living outside).

That path leads to a non-“Lite” implementation.

I think you can add some flavor of stored procedures as an extension, allowing 
it to be compiled out of binaries that don’t require it, but it still feels 
like mission creep to me.

The only option I can think of that fits the philosophy of SQLite would be to 
allow writing stored procedures in Jim Tcl or TH1.  The code for both is 
already in the SQLite tree, though both are used for other things, so neither 
is currently built into a SQLite binary.

This document maps a pre-trodden path:

   https://www.tcl.tk/community/tcl2004/Papers/D.RichardHipp/drh.html

Scroll down to "SQLite Extensions Written In Tcl.”  All I’m proposing is that 
that be made more of a first-class citizen, so that you can choose to enable it 
from the SQLite source tree, not by starting first with Tcl and extending it 
with SQLite.

> That way, at some stage, SQLite could even have several procedural dialect 
> implementations - TSQL, MySQL, etc. :-)

Are any of those actually good languages?  Every time I’ve seen stored 
procedures, the syntax is horrid, but I lack the experience to discern among 
these alternatives.  All I know is that I’ve managed to avoid every one of 
these languages for my entire career, and I’d like to get through the rest of 
it while still avoiding stored procedures, if possible.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stored Procedures

[hwoody2wood]

I missed stored procedures till i coded my workaround. I had a table named 
sprocs with the sql statements coded into a text blob in each row with each row 
representing a different sproc. A linked table named params had a column with a 
tag representing a parameter for the associated sproc.
Pull the sproc text and the list of the parameters pass that to a prep routine 
that replaces the tag text with the relevant values and then execute the sql.
Works like a champ.


Sent from my T-Mobile 4G LTE Device
 Original message From: Alek Paunov <a...@declera.com> Date: 
5/14/18  8:44 PM  (GMT-08:00) To: SQLite mailing list 
<sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Stored Procedures 
On 2018-05-09 03:56, Richard Hipp wrote:

...

> 
> The other benefit of stored procedures is that it provides a way to
> code up a common operation once (correctly!) and store it in the
> database, rather than having multiple clients all have to work out the
> operating themselves (some of them perhaps incorrectly).  The usual
> way of handling that in SQLite is to store a script in a text column
> someplace, then execute them as needed.  SQLite began life as a TCL
> extension, and so naturally TCL scripts work very well for this kind
> of thing.
> 

To amplify the remark above, for me, the Stored procedures are mostly 
method to add methods :-) to the "cold" data objects encoded in tables, 
just like the OO style SQLite C API defines interfaces and behavior on 
top of "cold" C structures.

That way, schemes and non-trivial calculations on top of them can be 
encapsulated and reused as modules (or packages as named in some design 
tools).

Practical example of the benefit is that e.g. same complex turnover 
report which implementation includes bunch of intermediate calculations, 
could be used with same SQL call from Python desktop application, 
directly as Excel Data Source or on partial replica through SQL.js [1] 
in the browser, without coding the same thing 3 times: on Python, VBA 
and JS.

Having said that, Let's take the survey: How many list readers really 
missed the Stored procedures feature?

If there are enough interest, I would be happy to discuss an (possibly 
naive) idea (*) for implementation (of simple e.g. Sybase 12.5 level 
procedures) mostly in pure SQL with just tiny C runner as UDF TValued 
Function [2].

Kind Regards,
Alek

(*) On the sqlite mailing list, we see permanent conflict of interests: 
On the one side are 99% of the users, for which sqlite is just better 
(than e.g. compressed json) storage option (usually for small as size or 
simple as structure data).

On the other, are the advanced users - these with complex applications 
who [especially after the great advancements from the last years (CTEs, 
JSON and so on)] try to encapsulate as much as possible business logic 
into the DB.

My humble opinion is that for us, the minority, the best move is to look 
for approaches and collaboration on "incubating" valuable advanced 
features initially outside of the SQLite core, then eventually apply 
result solutions in our own projects and finally propose for inclusion 
in the core library only small key components (which lead to 
inefficiencies or maintenance burden when living outside).

For the Stored procedures, borrowing from the SQLite design, I think 
that it is possible the non SQL, procedural part of the code to be 
translated to very high level VM with just few instructions, which uses 
attached :memory: DB for stack/registers/variables and (tree) table for 
the code. [This can be seen as step further above the approach cited by 
Dr. Hipp SQLs kept in table and used by executed by TCL script.]

That way, at some stage, SQLite could even have several procedural 
dialect implementations - TSQL, MySQL, etc. :-)

[1] https://github.com/kripken/sql.js/
[2] https://www.sqlite.org/vtab.html#tabfunc2
___
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] Stored Procedures

[Alek Paunov]

On 2018-05-09 03:56, Richard Hipp wrote:

...



The other benefit of stored procedures is that it provides a way to
code up a common operation once (correctly!) and store it in the
database, rather than having multiple clients all have to work out the
operating themselves (some of them perhaps incorrectly).  The usual
way of handling that in SQLite is to store a script in a text column
someplace, then execute them as needed.  SQLite began life as a TCL
extension, and so naturally TCL scripts work very well for this kind
of thing.



To amplify the remark above, for me, the Stored procedures are mostly 
method to add methods :-) to the "cold" data objects encoded in tables, 
just like the OO style SQLite C API defines interfaces and behavior on 
top of "cold" C structures.


That way, schemes and non-trivial calculations on top of them can be 
encapsulated and reused as modules (or packages as named in some design 
tools).


Practical example of the benefit is that e.g. same complex turnover 
report which implementation includes bunch of intermediate calculations, 
could be used with same SQL call from Python desktop application, 
directly as Excel Data Source or on partial replica through SQL.js [1] 
in the browser, without coding the same thing 3 times: on Python, VBA 
and JS.


Having said that, Let's take the survey: How many list readers really 
missed the Stored procedures feature?


If there are enough interest, I would be happy to discuss an (possibly 
naive) idea (*) for implementation (of simple e.g. Sybase 12.5 level 
procedures) mostly in pure SQL with just tiny C runner as UDF TValued 
Function [2].


Kind Regards,
Alek

(*) On the sqlite mailing list, we see permanent conflict of interests: 
On the one side are 99% of the users, for which sqlite is just better 
(than e.g. compressed json) storage option (usually for small as size or 
simple as structure data).


On the other, are the advanced users - these with complex applications 
who [especially after the great advancements from the last years (CTEs, 
JSON and so on)] try to encapsulate as much as possible business logic 
into the DB.


My humble opinion is that for us, the minority, the best move is to look 
for approaches and collaboration on "incubating" valuable advanced 
features initially outside of the SQLite core, then eventually apply 
result solutions in our own projects and finally propose for inclusion 
in the core library only small key components (which lead to 
inefficiencies or maintenance burden when living outside).


For the Stored procedures, borrowing from the SQLite design, I think 
that it is possible the non SQL, procedural part of the code to be 
translated to very high level VM with just few instructions, which uses 
attached :memory: DB for stack/registers/variables and (tree) table for 
the code. [This can be seen as step further above the approach cited by 
Dr. Hipp SQLs kept in table and used by executed by TCL script.]


That way, at some stage, SQLite could even have several procedural 
dialect implementations - TSQL, MySQL, etc. :-)


[1] https://github.com/kripken/sql.js/
[2] https://www.sqlite.org/vtab.html#tabfunc2
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stored Procedures

[Richard Hipp]

On 5/14/18, Warren Young  wrote:
>
> Your benchmark doesn’t address the primary problems pointed out in the
> Mozilla article:

The point of my article is that many people assume *without measuring*
that reading and writing directly to the filesystem will be faster
than using a database.  That assumption is incorrect.

The Mozilla article does not state this assumption explicitly, but it
is there just beneath the surface.

There are indeed many issue to consider when designing a storage
system for an application.  My point is to show that a relational
database is often a much better choice than many people (including the
authors of the Mozilla article) believe.  Prejudices against
relational databases that were acquired due to bad experiences in the
previous millennium are still quite pervasive.  I aim to combat those
prejudices with actual measurements.

Where there any actual measurements in the Mozilla article?  It is
quite old and I haven't read it in years, but I don't recall there
being any.
-- 
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] Stored Procedures

[Warren Young]

On May 14, 2018, at 1:19 PM, Richard Hipp  wrote:
> 
> On 5/14/18, Warren Young  wrote:
>> 
>> https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature
> 
> That's an older article.  These days, it is generally faster to use
> SQLite than fopen().  See, for example,
> https://www.sqlite.org/fasterthanfs.html

Your benchmark doesn’t address the primary problems pointed out in the Mozilla 
article:

1. Filesystem fragmentation.  Mozilla is talking about real-world pathologies, 
whereas if you run your paper’s benchmark on top of a modern filesystem, it 
won’t have much fragmentation, if any.  Mozilla’s point here is that if you 
rewrite the data file each time, it’s always in a defragmented state with 
modern filesystems.

2. DB fragmentation.  Your benchmark doesn’t simulate months of semi-random 
mixed DELETEs, INSERTs, UPDATEs, etc.  You don’t have to VACUUM a flat file 
loaded into RAM on app start and rewritten on app exit.

3. Stale data expiration policies.  When INSERT is all but free, you tend not 
to think as much about how much your data footprint is ballooning as when 
you’ve chosen a simpler persistence mechanism.

4. Journals have consequences, particularly on mobile platforms.  You don’t 
need to pay the cost for some applications.

5. “Just index it” doesn’t work when dealing with data that doesn’t index well, 
like a column of URLs, particularly on the modern web where you run into many 
arm-length pseudorandom URLs that you will only see once.

6. A naive reliance on SQLite’s good data structures breaks down when you 
misdesign your schema, indexes, etc. so that you create pathological behavior.  
All the indexing and log(n) and btrees in the world can’t help you when your 
simplistic query forces a table scan.

Understand, I’m not repeating the title of that article as unqualified advice.  
I offer it as contrast.  You can’t know whether SQLite is the correct answer 
unless you know what your alternatives are and how they respectively stack up.

The Mozilla article is reacting to the “If all you have is a hammer…” syndrome. 
 You can just as well flip it around: “If all you have is fopen(), you won’t 
know when you need SQLite.”  (Or PostgreSQL, or Hadoop, or…)

Very little of what this Mozilla article discusses is a result of old 
technology.  These are perennial topics.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stored Procedures

[Richard Hipp]

On 5/14/18, Warren Young  wrote:
>
> https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature
>

That's an older article.  These days, it is generally faster to use
SQLite than fopen().  See, for example,
https://www.sqlite.org/fasterthanfs.html

-- 
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] Stored Procedures

[Warren Young]

On May 13, 2018, at 10:15 PM, Rowan Worth  wrote:
> 
> ...I wouldn't call the extra i/o imposed by sqlite "very very
> cheap" either - it doubles writes (once to the rollback journal, once to
> the DB), forces syncs, and likely results in a more seek heavy i/o pattern
> (this depends a bit on schema design and whether the app requires/fully
> takes advantage of relational operations).

fopen() can be simpler than SQLite when you’ve got a problem that isn’t taking 
advantage of SQLite’s power.

If you ignore the sizes of SQLite and libc, so that you only consider the size 
of end-user-written code, the same up-front cost gets you a fully 
ACID-compliant RDBMS on the one hand vs. flat C structures written linearly to 
disk on the other.  If you can express most or all of your operations on the 
data in terms of linear table scans or linearly-indexed accesses — e.g. data[i] 
is always at sizeof(data[0]) * i bytes into the file — then the flat file is 
probably faster and cheaper.  On top of that, the binary size of stdio is 
probably a lot smaller than that of SQLite.

The interesting cases happen when you write your “simple” C code using fopen() 
and then find yourself writing O(N) algorithms where faster algorithms would 
work if only you had indexed access or JOIN abilities, or…   Then you have to 
do a classic tradeoff evaluation: will the complexity of SQLite pay for itself?

I recommend reading “Avoid SQLite in Your Next Firefox Feature,” an article 
written by the Mozilla developers after they found many features creating 
SQLite DBs for tiny little things better handled another way:

   
https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature

Note that their recommendation to use plain-text JSON or lz4-compressed JSON 
instead of small amounts of data comes out of the fact that Mozilla had to have 
those mechanisms in Firefox anyway, so they’re “free,” and they already had to 
be heavily-optimized.  It isn’t great as general-purpose advice.

> My point is simply that it's unwise to think of any DB query as having "no
> latency" even when dealing with an sqlite DB.

Yes.

ObWarStory: I once wrote an application using SQLite that tripped a 20-second 
watchdog timer by having an unexpectedly high amount of real-world traffic: a 
mere hundreds of DB updates per second.  How?

It was running on a system with a 5400 RPM hard disk, which translates to a 
theoretical maximum of 45 transactions per second: 5400 RPM / 60 seconds per 
minute / 2 disk rotations per transaction = 45.  Since each DB update was done 
independently, as long as the update rate was over 45 TPS, the application 
would fall further and further behind real time until that watchdog timer 
tripped.

Immediate solution: wrap a couple of key “for” loops in SQLite transactions to 
batch the disk updates.

Future solution: stop using spinning rust. :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stored Procedures

[Abroży Nieprzełoży]

> That's fair, but I wouldn't call the extra i/o imposed by sqlite "very very
> cheap" either - it doubles writes (once to the rollback journal, once to
> the DB), forces syncs, and likely results in a more seek heavy i/o pattern
> (this depends a bit on schema design and whether the app requires/fully
> takes advantage of relational operations).

Use explicit transaction maybe?



2018-05-14 6:15 GMT+02:00, Rowan Worth :
> On 14 May 2018 at 01:08, Richard Damon  wrote:
>
>> On 5/13/18 12:55 PM, Rowan Worth wrote:
>> > On 9 May 2018 at 08:56, Richard Hipp  wrote:
>> >
>> >>   But with
>> >> SQLite, there is no round-trip latency.  A "round-trip" to and
>> >> database is just a function call, and is very very cheap.
>> >>
>> > I want to emphasise that Dr. Hipp's usage of "round-trip" only includes
>> the
>> > latency of _communication_ between the app and database in this
>> statement,
>> > and excludes any processing time required by the database.
>> >
>> > If you were to interpret "round-trip" from an app-centric perspective
>> > (as
>> > in "the time taken to retrieve/commit data") then the statement becomes
>> > misleading because handling the data involves i/o, possibly even
>> > synchronous i/o, which is not "very very cheap" by any standard I'm
>> > aware
>> > of :)
>> >
>> > -Rowan
>>
> Yes, if the request requires I/O, then that costs time, but then the
>> operation would likely use similar I/O in whatever way the application
>> needed to get that information, so that I/O shouldn't really be charged
>> to the use of a database, but to the information requested. One thing to
>> remember is SQLite is often compared as a better way to get information
>> then using simple disk i/o, so the 'cost' of using SQLite (compared to
>> the alternative) shouldn't include the base time to read the file, but
>> only any extra i/o above that.
>>
>
> That's fair, but I wouldn't call the extra i/o imposed by sqlite "very very
> cheap" either - it doubles writes (once to the rollback journal, once to
> the DB), forces syncs, and likely results in a more seek heavy i/o pattern
> (this depends a bit on schema design and whether the app requires/fully
> takes advantage of relational operations).
>
> To be clear, this is not a criticism of sqlite. These costs are paid for a
> reason (eg. durability) and I think sqlite does its job very efficiently.
> You're also right that an app implementing similar features without sqlite
> will have to pay similar costs.
>
> My point is simply that it's unwise to think of any DB query as having "no
> latency" even when dealing with an sqlite DB.
> -Rowan
> ___
> 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] Stored Procedures

[Rowan Worth]

On 14 May 2018 at 01:08, Richard Damon  wrote:

> On 5/13/18 12:55 PM, Rowan Worth wrote:
> > On 9 May 2018 at 08:56, Richard Hipp  wrote:
> >
> >>   But with
> >> SQLite, there is no round-trip latency.  A "round-trip" to and
> >> database is just a function call, and is very very cheap.
> >>
> > I want to emphasise that Dr. Hipp's usage of "round-trip" only includes
> the
> > latency of _communication_ between the app and database in this
> statement,
> > and excludes any processing time required by the database.
> >
> > If you were to interpret "round-trip" from an app-centric perspective (as
> > in "the time taken to retrieve/commit data") then the statement becomes
> > misleading because handling the data involves i/o, possibly even
> > synchronous i/o, which is not "very very cheap" by any standard I'm aware
> > of :)
> >
> > -Rowan
>
Yes, if the request requires I/O, then that costs time, but then the
> operation would likely use similar I/O in whatever way the application
> needed to get that information, so that I/O shouldn't really be charged
> to the use of a database, but to the information requested. One thing to
> remember is SQLite is often compared as a better way to get information
> then using simple disk i/o, so the 'cost' of using SQLite (compared to
> the alternative) shouldn't include the base time to read the file, but
> only any extra i/o above that.
>

That's fair, but I wouldn't call the extra i/o imposed by sqlite "very very
cheap" either - it doubles writes (once to the rollback journal, once to
the DB), forces syncs, and likely results in a more seek heavy i/o pattern
(this depends a bit on schema design and whether the app requires/fully
takes advantage of relational operations).

To be clear, this is not a criticism of sqlite. These costs are paid for a
reason (eg. durability) and I think sqlite does its job very efficiently.
You're also right that an app implementing similar features without sqlite
will have to pay similar costs.

My point is simply that it's unwise to think of any DB query as having "no
latency" even when dealing with an sqlite DB.
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stored Procedures

[Richard Damon]

On 5/13/18 12:55 PM, Rowan Worth wrote:
> On 9 May 2018 at 08:56, Richard Hipp  wrote:
>
>>   But with
>> SQLite, there is no round-trip latency.  A "round-trip" to and
>> database is just a function call, and is very very cheap.
>>
> I want to emphasise that Dr. Hipp's usage of "round-trip" only includes the
> latency of _communication_ between the app and database in this statement,
> and excludes any processing time required by the database.
>
> If you were to interpret "round-trip" from an app-centric perspective (as
> in "the time taken to retrieve/commit data") then the statement becomes
> misleading because handling the data involves i/o, possibly even
> synchronous i/o, which is not "very very cheap" by any standard I'm aware
> of :)
>
> -Rowan
Yes, if the request requires I/O, then that costs time, but then the
operation would likely use similar I/O in whatever way the application
needed to get that information, so that I/O shouldn't really be charged
to the use of a database, but to the information requested. One thing to
remember is SQLite is often compared as a better way to get information
then using simple disk i/o, so the 'cost' of using SQLite (compared to
the alternative) shouldn't include the base time to read the file, but
only any extra i/o above that.

-- 
Richard Damon

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

Re: [sqlite] Stored Procedures

[Rowan Worth]

On 9 May 2018 at 08:56, Richard Hipp  wrote:

>   But with
> SQLite, there is no round-trip latency.  A "round-trip" to and
> database is just a function call, and is very very cheap.
>

I want to emphasise that Dr. Hipp's usage of "round-trip" only includes the
latency of _communication_ between the app and database in this statement,
and excludes any processing time required by the database.

If you were to interpret "round-trip" from an app-centric perspective (as
in "the time taken to retrieve/commit data") then the statement becomes
misleading because handling the data involves i/o, possibly even
synchronous i/o, which is not "very very cheap" by any standard I'm aware
of :)

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

Re: [sqlite] Stored Procedures

[Craig H Maynard]

Very useful comments in this thread. I recommend adding this to the SQLite FAQ, 
if it exists.

--
Craig H Maynard
Rhode Island, USA
401-413-2376


> Date: Tue, 8 May 2018 20:56:45 -0400
> From: Richard Hipp <d...@sqlite.org>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Stored Procedures
> Message-ID:
>   

Re: [sqlite] Stored Procedures

[David Burgess]

> The usual way of handling that in SQLite is to store a script in a text 
> column someplace, then execute them as needed.
Is there a simple way to do this from SQLite shell?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stored Procedures

[Richard Hipp]

On 5/8/18, Jens Alfke  wrote:
>
>
>> On May 8, 2018, at 1:02 PM, Mike Clark  wrote:
>>
>> Has there been any thought of implementing some kind of stored procedure
>> feature for SQLite?
>
> That's more of a server thing. In an embedded database, a stored procedure
> is literally a function in your programming language, which runs a SQLite
> query.
>
Right.

To amplify Jens' remark:  In a traditional client/server database,
there is high latency in the round-trip message from client to server
and back again.  To work around this, client/server systems provide
stored procedures in which lots of little SQL statements can be
embedded, and then all run together in a single round-trip.  But with
SQLite, there is no round-trip latency.  A "round-trip" to and
database is just a function call, and is very very cheap.  This one
fact removes a lot of the motivation behind having stored procedures.

The other benefit of stored procedures is that it provides a way to
code up a common operation once (correctly!) and store it in the
database, rather than having multiple clients all have to work out the
operating themselves (some of them perhaps incorrectly).  The usual
way of handling that in SQLite is to store a script in a text column
someplace, then execute them as needed.  SQLite began life as a TCL
extension, and so naturally TCL scripts work very well for this kind
of thing.

-- 
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] Stored Procedures

[Jens Alfke]

> On May 8, 2018, at 1:02 PM, Mike Clark  wrote:
> 
> Has there been any thought of implementing some kind of stored procedure
> feature for SQLite?

That's more of a server thing. In an embedded database, a stored procedure is 
literally a function in your programming language, which runs a SQLite query.

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

Re: [sqlite] Stored Procedures

[J Decker]

maybe Virtual tables?
can register functions of various types...
mostly since the typical usage of sqlite is as a tightly coupled library, a
function in your application is a 'stored procedure'.

http://www.sqlite.org/c3ref/update_hook.html  There are hooks which would
trigger callbacks like triggers...

to fake an exec you'd have to use like 'select sp_myProc(...)' instead of
'execute'


On Tue, May 8, 2018 at 1:58 PM, Igor Tandetnik  wrote:

> On 5/8/2018 4:02 PM, Mike Clark wrote:
>
>> Has there been any thought of implementing some kind of stored procedure
>> feature for SQLite?
>>
>> Or does this feature exist in some other form, with another name?
>>
>
> Triggers are kind of like stored procedures. You can create a dedicated
> view and put INSTEAD OF trigger on it. To "call" the trigger, insert a row
> into the view - the trigger could use column values, available via
> new.columnName, as its parameters.
> --
> Igor Tandetnik
>
>
>
> ___
> 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] Stored Procedures

[Igor Tandetnik]

On 5/8/2018 4:02 PM, Mike Clark wrote:

Has there been any thought of implementing some kind of stored procedure
feature for SQLite?

Or does this feature exist in some other form, with another name?


Triggers are kind of like stored procedures. You can create a dedicated view and put 
INSTEAD OF trigger on it. To "call" the trigger, insert a row into the view - 
the trigger could use column values, available via new.columnName, as its parameters.
--
Igor Tandetnik


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

[sqlite] Stored Procedures

[Mike Clark]

Has there been any thought of implementing some kind of stored procedure
feature for SQLite?

Or does this feature exist in some other form, with another name?

-- 
Mike Clark
Twitter: @Cyberherbalist
Blog: Cyberherbalist's Blog 
-
"Free will, though it makes evil possible, is also the only thing that
makes possible any love or goodness or joy worth having."
*- C. S. Lewis*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stored Procedures

[Nico Williams]

You can't change the NEW "row" in trigger bodies.

Since you can't make "SELECT"s (or virtual tables) this way, all your
"stored procedure" can do is INSERT/UPDATE/DELETE anyways.  Using
coalesce(NEW.foo, "default value") works fine (and it's how you'd
default "SP arguments").

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

Re: [sqlite] Stored Procedures

[Mark Lawrence]

On Thu Oct 09, 2014 at 05:38:57PM -0500, Nico Williams wrote:
> I use triggers, particularly INSTEAD OF INSERT triggers on VIEWs.
> The values of the columns of the rows to be "inserted" are the
> "stored procedure's" arguments.

I would like to able to do this too, but INSTEAD OF INSERT on a view
does not support default values for arguments the same way that BEFORE
INSERT on a regular table does.

What would really be nice is if one could run the following inside a
BEFORE or INSTEAD OF trigger:

UPDATE
NEW
SET
NEW.name = COALESCE(NEW.name, new_value)
;

Could the SQLite team perhaps comment on how difficult this would be to
implement?

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

Re: [sqlite] Stored Procedures

[Nico Williams]

I use triggers, particularly INSTEAD OF INSERT triggers on VIEWs.  The
values of the columns of the rows to be "inserted" are the "stored
procedure's" arguments.  I use WHERE clauses judiciously to make up
for the lack of IFs.  It works well enough.

I've used this in combination with recursive triggers to implement
recursive CTEs before SQLite3 added support for them.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stored Procedures

[Omprakash Kolluri]

Thanks ALL. This has been very helpful

Om Kolluri

On Thu, Oct 9, 2014 at 1:19 PM, big stone  wrote:

> Hi,
>
> Here is an example of stored procedure made in Python for SQLite.
>
> https://pypi.python.org/pypi/sqlite_bro/0.8.7.4
>
> I Hope it will help you figure out quickly  if SQLite is ok enough for your
> use-case.
>
> Sheers,
> ___
> 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] Stored Procedures

[big stone]

Hi,

Here is an example of stored procedure made in Python for SQLite.

https://pypi.python.org/pypi/sqlite_bro/0.8.7.4

I Hope it will help you figure out quickly  if SQLite is ok enough for your
use-case.

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

Re: [sqlite] Stored Procedures

[Mark Lawrence]

On Thu Oct 09, 2014 at 11:29:49AM -0700, J Decker wrote:
> they can be implemented through registered extensions..(well no probably
> not how you're thinking)..
> 
> but apparently can't add syntax like 'EXEC"  ... but could make them be
> like "select * from (stored_proc)" as an alias for "exec (stored proc)"

I use BEFORE INSERT triggers that end with a SELECT RAISE(IGNORE). That
way I can use the following syntax to perform multiple operations
within a single statement:

INSERT INTO
my_function_name(
arg1,
arg2,
arg3
)
VALUES (
val1,
val2,
val3
)

Obviously you can't get a result from that, but it would of course be
possible to let the insert succeed or to insert a "result row"
somewhere. Unfortunately CTEs don't work inside triggers so complicated
logic is somewhat limited and/or must be spread across multiple
triggers.

> For lack of portability I haven't used them; what's a good use case
> for stored procedures?

I generally use them for storing entities that must be entered in many
tables, where it nicely presents an API to the caller that closely maps
to the single action they want to take. This provides:

Simplicity: a single statement can replace many individual
statements, putting more of your application inside the database
and can often remove the need for an explicit transaction.

Efficiency: for non-C languages there is less translation between
the language/SQLite boundary.

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

Re: [sqlite] Stored Procedures

[J Decker]

they can be implemented through registered extensions..(well no probably
not how you're thinking)..

I have a few functions to mimic MySQL functions like now(), curdate() 
pretty simple
learned you can sqlite3_create_module()  which is a virtual recordset...
but apparently can't add syntax like 'EXEC"  ... but could make them be
like "select * from (stored_proc)" as an alias for "exec (stored proc)"
 but it wouldn't be SQL defining the procedure well it could be, but it
would be a C routine that then issued the sql statements (maybe make
loadable modules like shaders?)

For lack of portability I haven't used them; what's a good use case for
stored procedures?

On Thu, Oct 9, 2014 at 10:12 AM, RSmith  wrote:

>
> On 2014/10/09 19:04, Omprakash Kolluri wrote:
>
>> Hi,
>>
>> I am new to SQLite. I am working on an app that I am developing and plan
>> to
>> use SQLite as an embedded database. My Question - Does SQLite support
>> stored procedures similar to those in MS SQL Server etc. Any suggestions
>> OR
>> pointers to information links woill be greatly appreciated. Thank you
>>
>
> Hi Om,
>
> Not directly since it is at the API level very integrated and integratable
> with any C or other common compiler languages, so doing anything procedural
> is usually an easy task, and as the name implies it is "Lite" which means
> some higher-CPU-Cycle-and-Memory-consumption additions are foregone
> specifically so it could work well on embedded systems, as you seem to be
> implementing.
>
> A good read in this regard would be here:
> http://www.sqlite.org/whentouse.html
>
> Check out the recent CTE additions which does allow a level of procedural
> querying here:
> http://www.sqlite.org/lang_with.html#rcex2
>
>
> ___
> 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] Stored Procedures

[RSmith]

On 2014/10/09 19:04, Omprakash Kolluri wrote:

Hi,

I am new to SQLite. I am working on an app that I am developing and plan to
use SQLite as an embedded database. My Question - Does SQLite support
stored procedures similar to those in MS SQL Server etc. Any suggestions OR
pointers to information links woill be greatly appreciated. Thank you


Hi Om,

Not directly since it is at the API level very integrated and integratable with any C or other common compiler languages, so doing 
anything procedural is usually an easy task, and as the name implies it is "Lite" which means some 
higher-CPU-Cycle-and-Memory-consumption additions are foregone specifically so it could work well on embedded systems, as you seem 
to be implementing.


A good read in this regard would be here:
http://www.sqlite.org/whentouse.html

Check out the recent CTE additions which does allow a level of procedural 
querying here:
http://www.sqlite.org/lang_with.html#rcex2

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

Re: [sqlite] Stored Procedures

[Stephen Chrzanowski]

Views, yes.  Stored Procedures, no.

On Thu, Oct 9, 2014 at 1:04 PM, Omprakash Kolluri 
wrote:

> Hi,
>
> I am new to SQLite. I am working on an app that I am developing and plan to
> use SQLite as an embedded database. My Question - Does SQLite support
> stored procedures similar to those in MS SQL Server etc. Any suggestions OR
> pointers to information links woill be greatly appreciated. Thank you
>
> Om Kolluri
> ___
> 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] Stored Procedures

[Omprakash Kolluri]

Hi,

I am new to SQLite. I am working on an app that I am developing and plan to
use SQLite as an embedded database. My Question - Does SQLite support
stored procedures similar to those in MS SQL Server etc. Any suggestions OR
pointers to information links woill be greatly appreciated. Thank you

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

Re: [sqlite] stored procedures implementation for SQLite

[Andy Gibbs]

On Wednesday, January 26, 2011 5:38 PM, Chris Wolf wrote:

> but if anyone is interested, I checked in my work on GitHub, including 
> pre-compiled
> binaries for MacOS and Linux.
>
> http://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/#extended
>

Yes, very interesting!

You approached the problem from a different angle to the way I did it.  I 
did it the other way round: created a procedural grammar that could be used 
like a standard statement, then found a way of storing them and then a way 
to call them.  As a result, I stored the procedures just inside 
sqlite_master, rather than having multiple additional tables.  I simply used 
wrappers around the sqlite3_bind_* functions for handling parameters to the 
stored procedures when they were called, rather than holding the parameter 
names in a table.  But I think your way is just as appropriate, and possibly 
better, in the long run.

Additional to calling a stored procedure through a standalone statement 
("EXEC fn(param)"), I also implemented calling through an SQL function (e.g. 
"SELECT exec(fn, param)"), which meant that the stored procedure could 
return a value that would be fed back into another statement/procedure.  Of 
course, that led to the possibility of recursive calls and other 
nightmares...

Anyway, I'll be interested to see how you progress.

Andy


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

Re: [sqlite] stored procedures implementation for SQLite

[Chris Wolf]

Thanks for bringing that to my attention - that sample was left over from
when I was trying to use APSW rather then sqlite2.  The actual test program,
sqlite-3.7.3/src/createproc_test.c, is correct.  I updated the blog page
to reflect the proper code.

   -Chris

On Jan 26, 2011, at 12:55 PM, Jim Wilcoxson wrote:

> It looks interesting.  Should your except stmt reference apsw?  -Jim
> --
> HashBackup: easy onsite and offsite Unix backup
> http://www.hashbackup.com
> 
> 
> On Wed, Jan 26, 2011 at 11:38 AM, Chris Wolf  wrote:
> 
>> 
>> 
>> I know this is an old thread, but shortly after I read it, I attempted to
>> implement
>> stored procedures in SQLite!  I only did it to see if I could, not because
>> I
>> necessarily think it's a good idea...  It's very experimental and not fully
>> implemented,
>> but if anyone is interested, I checked in my work on GitHub, including
>> pre-compiled
>> binaries for MacOS and Linux.
>> 
>> 
>> http://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/#extended
>> 
>> Regards,
>> 
>> Chris Wolf
>> 
>> BareFeetWare wrote:
>>> On 13/11/2010, at 11:14 AM, Scott Hess wrote:
>>> 
 On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare <
>> list@barefeetware.com> wrote:
> IMO, if you're implementing database logic (ie constraints and
>> triggers) in application code, then you're reinventing the wheel, making
>> your package unnecessarily complex and grossly inefficient. If you're just
>> using SQLite to store your data but doing all the logic outside of SQLite,
>> then you might as well just be saving your data to a CSV file or XML. See my
>> previous post for reasoning behind this.
 From http://www.sqlite.org/about.html :
> Think of SQLite not as a replacement for Oracle but as a replacement
>> for fopen()
>>> 
>>> The full paragraph from that page reads:
>>> 
> SQLite is an embedded SQL database engine. Unlike most other SQL
>> databases, SQLite does not have a separate server process. SQLite reads and
>> writes directly to ordinary disk files. A complete SQL database with
>> multiple tables, indices, triggers, and views, is contained in a single disk
>> file. The database file format is cross-platform - you can freely copy a
>> database between 32-bit and 64-bit systems or between big-endian and
>> little-endian architectures. These features make SQLite a popular choice as
>> an Application File Format. Think of SQLite not as a replacement for Oracle
>> but as a replacement for fopen()
>>> 
>>> So I think it's referring to how SQLite stores its data in a local file,
>> rather than on a remote server with which it communicates indirectly. ie
>> "SQLite does not have a separate server process". In that way, SQLite is
>> like fopen rather than Oracle. The same paragraphs mentions SQLite
>> "triggers, and views", freely copying a [self contained] SQLite database
>> between architectures, which allude to my point about putting the logic in
>> the database itself so you can move the whole database between
>> architectures.
>>> 
 So, yes, you might as well just be saving your data to a CSV or XML
>> file.  And I'm sure if you had a package to do that, someone would be
>> arguing about whether your XML should allow for embedded transforms.
>>> 
>>> What do you gain by implementing database logic in the application layer,
>> when it could be done far more efficiently and reliably in the SQL schema?
>> The only thing I can think of is avoiding the (shallow) learning curve. Why
>> re-invent and roll your own integrity checking etc when it's already
>> available and in a way much closer to the data than your application code
>> can get?
>>> 
>>> See my previous post for the contrary argument:
>>> 
>> http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html
>>> 
>>> Basically, database logic in the database itself is much faster, causes
>> less error, optimises queries, makes the database portable between
>> application environments or GUI apps. What's not to love?
>>> 
>>> Thanks,
>>> Tom
>>> BareFeetWare
>>> 
>>> --
>> 
>> ___
>> 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] stored procedures implementation for SQLite

[Jim Wilcoxson]

It looks interesting.  Should your except stmt reference apsw?  -Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com


On Wed, Jan 26, 2011 at 11:38 AM, Chris Wolf  wrote:

>
>
> I know this is an old thread, but shortly after I read it, I attempted to
> implement
> stored procedures in SQLite!  I only did it to see if I could, not because
> I
> necessarily think it's a good idea...  It's very experimental and not fully
> implemented,
> but if anyone is interested, I checked in my work on GitHub, including
> pre-compiled
> binaries for MacOS and Linux.
>
>
> http://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/#extended
>
> Regards,
>
> Chris Wolf
>
> BareFeetWare wrote:
> > On 13/11/2010, at 11:14 AM, Scott Hess wrote:
> >
> >> On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare <
> list@barefeetware.com> wrote:
> >>> IMO, if you're implementing database logic (ie constraints and
> triggers) in application code, then you're reinventing the wheel, making
> your package unnecessarily complex and grossly inefficient. If you're just
> using SQLite to store your data but doing all the logic outside of SQLite,
> then you might as well just be saving your data to a CSV file or XML. See my
> previous post for reasoning behind this.
> >> From http://www.sqlite.org/about.html :
> >>> Think of SQLite not as a replacement for Oracle but as a replacement
> for fopen()
> >
> > The full paragraph from that page reads:
> >
> >>> SQLite is an embedded SQL database engine. Unlike most other SQL
> databases, SQLite does not have a separate server process. SQLite reads and
> writes directly to ordinary disk files. A complete SQL database with
> multiple tables, indices, triggers, and views, is contained in a single disk
> file. The database file format is cross-platform - you can freely copy a
> database between 32-bit and 64-bit systems or between big-endian and
> little-endian architectures. These features make SQLite a popular choice as
> an Application File Format. Think of SQLite not as a replacement for Oracle
> but as a replacement for fopen()
> >
> > So I think it's referring to how SQLite stores its data in a local file,
> rather than on a remote server with which it communicates indirectly. ie
> "SQLite does not have a separate server process". In that way, SQLite is
> like fopen rather than Oracle. The same paragraphs mentions SQLite
> "triggers, and views", freely copying a [self contained] SQLite database
> between architectures, which allude to my point about putting the logic in
> the database itself so you can move the whole database between
> architectures.
> >
> >> So, yes, you might as well just be saving your data to a CSV or XML
> file.  And I'm sure if you had a package to do that, someone would be
> arguing about whether your XML should allow for embedded transforms.
> >
> > What do you gain by implementing database logic in the application layer,
> when it could be done far more efficiently and reliably in the SQL schema?
> The only thing I can think of is avoiding the (shallow) learning curve. Why
> re-invent and roll your own integrity checking etc when it's already
> available and in a way much closer to the data than your application code
> can get?
> >
> > See my previous post for the contrary argument:
> >
> http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html
> >
> > Basically, database logic in the database itself is much faster, causes
> less error, optimises queries, makes the database portable between
> application environments or GUI apps. What's not to love?
> >
> > Thanks,
> > Tom
> > BareFeetWare
> >
> > --
>
> ___
> 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] stored procedures implementation for SQLite

[Chris Wolf]

I know this is an old thread, but shortly after I read it, I attempted to 
implement
stored procedures in SQLite!  I only did it to see if I could, not because I 
necessarily think it's a good idea...  It's very experimental and not fully 
implemented,
but if anyone is interested, I checked in my work on GitHub, including 
pre-compiled
binaries for MacOS and Linux.

http://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/#extended

Regards,

Chris Wolf

BareFeetWare wrote:
> On 13/11/2010, at 11:14 AM, Scott Hess wrote:
> 
>> On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare  
>> wrote:
>>> IMO, if you're implementing database logic (ie constraints and triggers) in 
>>> application code, then you're reinventing the wheel, making your package 
>>> unnecessarily complex and grossly inefficient. If you're just using SQLite 
>>> to store your data but doing all the logic outside of SQLite, then you 
>>> might as well just be saving your data to a CSV file or XML. See my 
>>> previous post for reasoning behind this.
>> From http://www.sqlite.org/about.html :
>>> Think of SQLite not as a replacement for Oracle but as a replacement for 
>>> fopen()
> 
> The full paragraph from that page reads:
> 
>>> SQLite is an embedded SQL database engine. Unlike most other SQL databases, 
>>> SQLite does not have a separate server process. SQLite reads and writes 
>>> directly to ordinary disk files. A complete SQL database with multiple 
>>> tables, indices, triggers, and views, is contained in a single disk file. 
>>> The database file format is cross-platform - you can freely copy a database 
>>> between 32-bit and 64-bit systems or between big-endian and little-endian 
>>> architectures. These features make SQLite a popular choice as an 
>>> Application File Format. Think of SQLite not as a replacement for Oracle 
>>> but as a replacement for fopen()
> 
> So I think it's referring to how SQLite stores its data in a local file, 
> rather than on a remote server with which it communicates indirectly. ie 
> "SQLite does not have a separate server process". In that way, SQLite is like 
> fopen rather than Oracle. The same paragraphs mentions SQLite "triggers, and 
> views", freely copying a [self contained] SQLite database between 
> architectures, which allude to my point about putting the logic in the 
> database itself so you can move the whole database between architectures.
> 
>> So, yes, you might as well just be saving your data to a CSV or XML file.  
>> And I'm sure if you had a package to do that, someone would be arguing about 
>> whether your XML should allow for embedded transforms.
> 
> What do you gain by implementing database logic in the application layer, 
> when it could be done far more efficiently and reliably in the SQL schema? 
> The only thing I can think of is avoiding the (shallow) learning curve. Why 
> re-invent and roll your own integrity checking etc when it's already 
> available and in a way much closer to the data than your application code can 
> get?
> 
> See my previous post for the contrary argument:
> http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html
> 
> Basically, database logic in the database itself is much faster, causes less 
> error, optimises queries, makes the database portable between application 
> environments or GUI apps. What's not to love?
> 
> Thanks,
> Tom
> BareFeetWare
> 
> --

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

Re: [sqlite] Stored procedures

[Olaf Schmidt]

"BareFeetWare"   schrieb
> On 13/11/2010, at 10:33 AM, Olaf Schmidt wrote:

> >> If you have code (in either environment) that is
> >> looping or grabbing one result and sticking it in a
> >> second query, then your approach is probably flawed.
> >
> > As you say: "probably" ... because "it depends" ...
> > (maybe your application wants to show "details"
> > in a delayed fashion - e.g. when a Node in a
> > Treeview gets "expanded").
>
> Good example. At the point where the user requests expanded
> details (eg by clicking on a button), that obviously kicks
> off application code. As far as its interaction with the
> database, simplistically, it would have to do something
> like get the key values in the current row, check whether
> it exists in the details set, get the detail data. What I'm
> saying is that this should be done as one SQL call, which
> most likely doesn't require any branching logic within that call.
>
> So it should look something like this, with just one SQL call:
>
> array filteredDetailResults  = execQuery(
> select "Product Code", "Product Name", Quantity, Price
> from "Invoice Items"
> join "Invoice List" on "Invoice Items"."Invoice Number" = "Invoice
List"."Invoice Number"
> left join "Item List" on "Invoice Items"."Product Code" = "Item
List"."Product Code"
> where "GUI Selection Flag" = 1
> )
> show filteredDetailResults

Nah ... come on - Joins are common things - don't
know which DB-Application-developer (who worth
its salt) is not making use of it (instead of falling back
to loops).

And in my App I would have put the above SQL
into a View-Definition beforehand (that's another
common thing, most DB-Engines share and which
is well-portable).

Ending up with something like that...

SQL = "Select * From InvoiceDetailView Where CustomerID = " & _
CurrentNode.ID

' get a new Recordset-Instance, containing the set, defined above
Set Rs = Cnn.OpenRecordset(SQL)

' GUI-Refresh over DataBinding against the Rs-Instance
Set MyGUIWidget.DataSource = Rs

--
And in case I'd have put the above (already View-based)
SQL-Code into a CommandObject first, then I would only
need to set the Command-Parameter (typed, and without
fiddling around with String-Concats).

InvoicesCmd.SetInteger !CustomerID, CurrentNode.ID
Set MyGUIWidget.DataSource = InvoicesCmd.Execute


Olaf



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

Re: [sqlite] Stored procedures

[BareFeetWare]

On 13/11/2010, at 10:33 AM, Olaf Schmidt wrote:

 From: "Olaf Schmidt"
 Wednesday, November 10, 2010 9:07:19 AM
 
>>> There was a somewhat similar sounding post (from BareFeetWare,
>>> sent on 20.Oct to this list) who also encouraged, to include
>>> "more logic" into the SQLite-Files itself, to reach more "portability".
>> 
>> That post of mine is archived here:
>> 
> http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html
> 
> Yep, that was it.
> 
>> For the record, I (for BareFeetWare) was advocating implementing database 
>> logic in constraints and triggers, rather than in application code. I was 
>> not actually talking about stored procedures.

> After reading your posting again, it seems I had a "wrong image" in the back 
> of my mind, regarding your post, sorry.
> 
> Since you are talking about *database-logic* - our opinions do not differ 
> that much I'd say...

> ...use "DB-internal mechanisms" only in a range, which is common among 
> different DB-engines (Triggers for example, to ensure referential integrity 
> at least)."
> 
> And constraints, which I left out, belong into the same category, since they 
> are "common enough" too (among different DB-engines).

> Nothing wrong with Triggers and Constraints (the "database logic", as you 
> call it). Things which ensure (force) consistency on your Data belong into 
> the DB. They are usually well portable between different Engines (good 
> DB-Import-Tools can recognize and understand most of these - where importing 
> vendor-specific Stored-Procedures into another Backend can be adventurous at 
> least, if we talk about larger or complex procedures).

OK, we'll agree to... agree ;-) Database internal logic (integrity checks etc) 
are best handled in the database code (SQL), such as in constraints and 
triggers. I didn't know what you meant by "range".

> "business-logic" or "business layer", then that's something, what in my 
> opinion belongs into a shareable Component (usually a Dll), written
> in a normal language - but I think I already made my points, let's not repeat 
> them again - and it's after all only a personal opinion.

I guess we both agree that for business logic (as opposed to database logic, 
discussed above), a procedure of some kind is obviously required. In this 
situation, I advocate that as much as possible is done in SQL, by using "where" 
clauses and manipulating sets rather than using "if/then" branching structures 
and iterating through loops, respectively. This is more efficient and exploits 
SQL data storage optimisations. In fact I think that the availability of 
if/then and repeat loops in a database often entices people into bad habits of 
procedural programming where set programming should be used instead.

Do you have procedures that require branching structures? Can it not be better 
achieved by set logic?

If there's no other way than to have branching structures, then I think I see 
what you're saying: that it's better to take care of the branching structures 
in an external code layer than in an SQL procedural language (which SQLite 
doesn't have, currently). I think you're saying that SQL procedure branching 
structures aren't standard enough across SQL flavours to make it portable. In 
short, if you're more likely to change the choice of SQL flavor (eg SQLite to 
MySQL) than application code (eg perl vs python vs PHP vs Objective-C) then 
you're better off putting the branching structures in the application code. 
Correct? This choice would boil down to the likelihood of changing each (ie SQL 
flavor vs application code environment).

>> Why would we want to perform stored procedures?
> 
> Ehhmm, because a "bunch of well-organized and consistent data-records" is not 
> (yet) an Application? ;-)

LOL, yes, true. The application code has to drive the GUI or html input/output 
or whatever, but when the application code gets to the point of getting or 
setting data in the database, then it should ideally be done each time as one 
SQL call, rather than having a pile of application branches that makes several 
SQL calls.

>> If you have code (in either environment) that is looping or grabbing one 
>> result and sticking it in a second query, then your approach is probably 
>> flawed.

> As you say: "probably" ... because "it depends" ... (maybe your application 
> wants to show "details" in a delayed fashion - e.g. when a Node in a Treeview 
> gets "expanded").

Good example. At the point where the user requests expanded details (eg by 
clicking on a button), that obviously kicks off application code. As far as its 
interaction with the database, simplistically, it would have to do something 
like get the key values in the current row, check whether it exists in the 
details set, get the detail data. What I'm saying is that this should be done 
as one SQL call, which most likely doesn't require any branching logic within 
that call.

So it should look something like this, 

Re: [sqlite] Stored procedures (was: Question about SQLite features.)

[Scott Hess]

On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare  wrote:
> IMO, if you're implementing database logic (ie constraints and triggers) in
> application code, then you're reinventing the wheel, making your package
> unnecessarily complex and grossly inefficient. If you're just using SQLite
> to store your data but doing all the logic outside of SQLite, then you might
> as well just be saving your data to a CSV file or XML. See my previous
> post for reasoning behind this.

>From http://www.sqlite.org/about.html :
> Think of SQLite not as a replacement for Oracle but as a replacement
> for fopen()

So, yes, you might as well just be saving your data to a CSV or XML
file.  And I'm sure if you had a package to do that, someone would be
arguing about whether your XML should allow for embedded transforms.

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

Re: [sqlite] Stored procedures (was: Question about SQLite features.)

[Olaf Schmidt]

"BareFeetWare"  schrieb
> On 12/11/2010, at 6:30 AM, Olaf Schmidt wrote:
>
> > "jeff archer"  schrieb
> >> From: "Olaf Schmidt"
> >> Wednesday, November 10, 2010 9:07:19 AM
> >>
> >>> [Stored procedures in SQLite]
> >>>
> >>> IMO stored procedure-support only makes
> >>> sense in "Server-Instances" which run on their own...
> >
> >> I disagree. The overall design and structure of applications
> >> using SQLite and therefor SQLite itself would benefit
> >> from SQLite supporting stored procedures.
> >> This would allow all code necessary for enforcing
> >> the business rules of the data to be stored in the
> >> database itself. This is just a good basic design principal.
> >
> > There was a somewhat similar sounding post (from BareFeetWare,
> > sent on 20.Oct to this list) who also encouraged, to include
> > "more logic" into the SQLite-Files itself, to reach more "portability".
>
> That post of mine is archived here:
>
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html

Yep, that was it.

> For the record, I (for BareFeetWare) was advocating implementing
> database logic in constraints and triggers, rather than in
> application code. I was not actually talking about stored
> procedures.
> In order of preference, I would implement logic in:
>
> 1. Constraints
> 2. Triggers
> 9. Pure SQL (eg stored procedures)
> 10. Application code (ie an external language that
> calls SQL snippets)

After reading your posting again, it seems I had a
"wrong image" in the back of my mind, regarding
your post, sorry.

Since you are talking about *database-logic* -
our opinions do not differ that much I'd say...
>From my posting you were replying to ... I wrote:
  "...use "DB-internal mechanisms" only in a
   range, which is common among different
   DB-engines (Triggers for example,
   to ensure referential integrity at least)."

And constraints, which I left out, belong into the
same category, since they are "common enough"
too (among different DB-engines).

Maybe I also misunderstood Jeff Archer in this
regard, but I think he meant a different thing with:
  "...code necessary for enforcing the business rules..."

If he meant "business-logic" or "business layer", then
that's something, what in my opinion belongs into
a shareable Component (usually a Dll), written
in a normal language - but I think I already made
my points, let's not repeat them again - and it's
after all only a personal opinion.

> IMO, if you're implementing database logic (ie constraints
> and triggers) in application code, then you're reinventing
> the wheel, ...
As said, that was not what I was "complaining about".

[layout of a handmade "stored procedure mechanism for the poor"
 in SQLite ;-)]

> But my question is: why?
>
> Why would we want to perform stored procedures?
> Or why would we want to perform application code,
> if you're on that side of the "war"? ;-)

Ehhmm, because a "bunch of well-organized and consistent
data-records" is not (yet) an Application? ;-)

> In most cases, I suggest that you should be implementing your
> database logic in constraints and triggers, not in procedural
> code (ie not in SQL store procedures and not in application code).
Here you go again... ;-)
Nothing wrong with Triggers and Constraints (the
"database logic", as you call it). Things which ensure
(force) consistency on your Data belong into the DB.
They are usually well portable between different
Engines (good DB-Import-Tools can recognize and
understand most of these - where importing vendor-
specific Stored-Procedures into another Backend
can be adventurous at least, if we talk about larger
or complex procedures).

> If you have code (in either environment) that is looping or
> grabbing one result and sticking it in a second query,
> then your approach is probably flawed.
As you say: "probably" ... because "it depends" ...
(maybe your application wants to show "details"
 in a delayed fashion - e.g. when a Node in a
 Treeview gets "expanded").

Anyways, hope my position is more clear to you now,
and - (since you made this request) - you feel
"enlightened enough" in the meantime .

Olaf



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

[sqlite] Stored procedures (was: Question about SQLite features.)

[BareFeetWare]

On 12/11/2010, at 6:30 AM, Olaf Schmidt wrote:

> "jeff archer"  schrieb
>> From: "Olaf Schmidt"
>> Wednesday, November 10, 2010 9:07:19 AM
>> 
>>> [Stored procedures in SQLite]
>>> 
>>> IMO stored procedure-support only makes sense in "Server-Instances" which 
>>> run on their own...
> 
>> I disagree. The overall design and structure of applications using SQLite 
>> and therefor SQLite itself would benefit from SQLite supporting stored 
>> procedures. This would allow all code necessary for enforcing the business 
>> rules of the data to be stored in the database itself. This is just a good 
>> basic design principal.
> 
> There was a somewhat similar sounding post (from BareFeetWare, sent on 20.Oct 
> to this list) who also encouraged, to include "more logic" into the 
> SQLite-Files itself, to reach more "portability".

That post of mine is archived here:
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html

For the record, I (for BareFeetWare) was advocating implementing database logic 
in constraints and triggers, rather than in application code. I was not 
actually talking about stored procedures. In order of preference, I would 
implement logic in:

1. Constraints
2. Triggers
9. Pure SQL (eg stored procedures)
10. Application code (ie an external language that calls SQL snippets)

IMO, if you're implementing database logic (ie constraints and triggers) in 
application code, then you're reinventing the wheel, making your package 
unnecessarily complex and grossly inefficient. If you're just using SQLite to 
store your data but doing all the logic outside of SQLite, then you might as 
well just be saving your data to a CSV file or XML. See my previous post for 
reasoning behind this.

Now, as for stored procedures (which I wasn't discussing before), I will chime 
in on this new discussion:

It is fairly trivial to add your own table to your database for storing 
procedures, such as:

create table "Procedures"
(   ID integer primary key
,   Name text unique
,   Description text
,   "SQL" text
)
;

In this you can store and name SQL procedures, using whatever external 
application code to simply get the SQL of the desired Procedure by Name and 
execute it. It will run as one connection within SQLite, utilise the begin 
commit/rollback mechanism etc. You can combine, say, an update and select, to 
update one table based on results from another very efficiently totally within 
SQL, without reinjecting the results of one query into a second query via an 
application code layer. And so on.

Such an after market stored procedure mechanism isn't as good as could be 
achieved via a native stored procedure facility built into SQLite, but it 
fulfils many of the objectives. It does lack variables, procedural nesting etc, 
but those can usually be accomplished by a set theory approach (which is 
desirable anyway) and a similarly contrived "Variables" table. It also has to 
convert raw text SQL into object/machine code on each call.

But my question is: why?

Why would we want to perform stored procedures? Or why would we want to perform 
application code, if you're on that side of the "war"? ;-)

In most cases, I suggest that you should be implementing your database logic in 
constraints and triggers, not in procedural code (ie not in SQL store 
procedures and not in application code). If you have code (in either 
environment) that is looping or grabbing one result and sticking it in a second 
query, then your approach is probably flawed. You really should be approaching 
the data as sets and not something over which code should be iterated.

Having said that, there are some needs for procedural code that aren't just 
misplaced and inefficient attempts to implementing internal logic. For example, 
I have procedural scripts that:

1. Import data from one table to another (eg in a different database).
2. Periodically clean, flush or delete redundant or outdated entries from a 
logging table
3. Change the data in one table to match a data set proposed by best estimate 
calculations (eg budget forecasts or assignments based on most recent choices)

They do implement "business logic" but not what I would call "database logic". 
These procedures are actions initiated by the user or business schedule, such 
as importing a new sales catalog, end of cycle data cleansing, or a preliminary 
entry of "best guess" data before the data entry operator or bookkeeper manual 
enters hundreds of records. These procedures are triggered by an external 
event. By contrast, database logic implements data integrity and is initiated 
by an internal event such as an update, insert or delete.

If your experience differs to this, please enlighten me. Do you have or need 
procedures (either stored SQL or in application code) that aren't one of:

1. Enforce data integrity such as restricting entries or cascading changes, 
which could/should be instead accomplished through constraints and 

Re: [sqlite] "Stored procedures" performance issue

[Igor Tandetnik]

Darren Duncan wrote:
> Igor Tandetnik wrote:
>> Kristoffer Danielsson
>>  wrote:
>>> When I create my own "stored procedures" using
>>> sqlite3_create_function, I get horrible performance (which I
>>> expected) even though the column of interest is INDEXED.
>>>
>>> Consider this sample (it's stupid, but it shows my problem):
>>>
>>> SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate);
>>>
>>> IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside
>>> this function, if I encounter a date greater than my birthday, then
>>> I want to tell SQLite to stop searching, since the date is indexed.
>>
>> I'm not sure what you mean by "a date greater than my birthday".
>> Presumably, your birthday comes every year, so every date is either
>> your birthday or falls between two birthdays (except dates before
>> the date
>> you were actually born on, but those can't be greater than any of
>> your
>> birthday dates).
>
> Another meaning for birthday is the the day in history where one was
> born, and there is just one of these per person.  More often this is
> what people are
> talking about when they are dealing with dates having a year part.

With this interpretation, having a function IS_MY_BIRTHDAY(IndexedDate) 
doesn't make much sense. In any case, one part of my response deals with 
this possibility.

Igor Tandetnik



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

Re: [sqlite] "Stored procedures" performance issue

[Darren Duncan]

Igor Tandetnik wrote:
> Kristoffer Danielsson  wrote:
>> When I create my own "stored procedures" using
>> sqlite3_create_function, I get horrible performance (which I
>> expected) even though the column of interest is INDEXED.
>>
>> Consider this sample (it's stupid, but it shows my problem):
>>
>> SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate);
>>
>> IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside
>> this function, if I encounter a date greater than my birthday, then I
>> want to tell SQLite to stop searching, since the date is indexed.
> 
> I'm not sure what you mean by "a date greater than my birthday". 
> Presumably, your birthday comes every year, so every date is either your 
> birthday or falls between two birthdays (except dates before the date 
> you were actually born on, but those can't be greater than any of your 
> birthday dates).

Another meaning for birthday is the the day in history where one was born, and 
there is just one of these per person.  More often this is what people are 
talking about when they are dealing with dates having a year part. -- Darren 
Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Stored procedures" performance issue

[Kees Nuyt]

On Mon, 5 Oct 2009 18:01:46 +0200, Kristoffer Danielsson
 wrote:

> This makes sense. Though, I think the 
> documentation should cover this.

Much of this is implicitly or explicitly covered in
http://www.sqlite.org/optoverview.html .

And what Scott Hess said.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Stored procedures" performance issue

[Scott Hess]

Not to be flippant, but how could SQLite optimize for the output of a
custom function you define outside of SQLite?  It can't possibly know
anything about how IS_MY_BIRTHDAY() works.  Your function could return
1 for every single row, or for no rows at all, without regard to
whether you're passing the function an indexed column.

-scott


On Mon, Oct 5, 2009 at 9:01 AM, Kristoffer Danielsson
<kristoffer.daniels...@live.se> wrote:
>
> This makes sense. Though, I think the documentation should cover this.
>
>
>
> Thanks for your response.
>
>> From: paiva...@gmail.com
>> Date: Mon, 5 Oct 2009 09:31:10 -0400
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] "Stored procedures" performance issue
>>
>> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
>> > function, if I encounter a date greater than my birthday, then I want to 
>> > tell SQLite to stop searching, since the date is indexed.
>> >
>> > Is this possible? If so, how?
>>
>> Even if this was possible it would be useless because you cannot say
>> if SQLite traverses table via index or makes just full table scan. And
>> in your particular case SQLite will see that there's no benefit in
>> using any index and will just scan the whole table.
>> The general advice in this case is to add column to the table which
>> will store the result of your function, create an index on that
>> additional column and then use that column in the query. In this case
>> SQLite will understand that it's better to use index and will use it.
>>
>> Pavel
>>
>> On Mon, Oct 5, 2009 at 9:24 AM, Kristoffer Danielsson
>> <kristoffer.daniels...@live.se> wrote:
>> >
>> > When I create my own "stored procedures" using sqlite3_create_function, I 
>> > get horrible performance (which I expected) even though the column of 
>> > interest is INDEXED.
>> >
>> >
>> > Consider this sample (it's stupid, but it shows my problem):
>> >
>> > SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate);
>> >
>> >
>> >
>> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
>> > function, if I encounter a date greater than my birthday, then I want to 
>> > tell SQLite to stop searching, since the date is indexed.
>> >
>> >
>> >
>> > Is this possible? If so, how?
>> >
>> >
>> > Thanks.
>> >
>> > _
>> > Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och 
>> > Digg när de skickar e-post till dig.
>> > http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
>> > ___
>> > 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
>
> _
> Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg 
> när de skickar e-post till dig.
> http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
> ___
> 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] "Stored procedures" performance issue

[Kristoffer Danielsson]

This makes sense. Though, I think the documentation should cover this.

 

Thanks for your response.
 
> From: paiva...@gmail.com
> Date: Mon, 5 Oct 2009 09:31:10 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "Stored procedures" performance issue
> 
> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
> > function, if I encounter a date greater than my birthday, then I want to 
> > tell SQLite to stop searching, since the date is indexed.
> >
> > Is this possible? If so, how?
> 
> Even if this was possible it would be useless because you cannot say
> if SQLite traverses table via index or makes just full table scan. And
> in your particular case SQLite will see that there's no benefit in
> using any index and will just scan the whole table.
> The general advice in this case is to add column to the table which
> will store the result of your function, create an index on that
> additional column and then use that column in the query. In this case
> SQLite will understand that it's better to use index and will use it.
> 
> Pavel
> 
> On Mon, Oct 5, 2009 at 9:24 AM, Kristoffer Danielsson
> <kristoffer.daniels...@live.se> wrote:
> >
> > When I create my own "stored procedures" using sqlite3_create_function, I 
> > get horrible performance (which I expected) even though the column of 
> > interest is INDEXED.
> >
> >
> > Consider this sample (it's stupid, but it shows my problem):
> >
> > SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate);
> >
> >
> >
> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
> > function, if I encounter a date greater than my birthday, then I want to 
> > tell SQLite to stop searching, since the date is indexed.
> >
> >
> >
> > Is this possible? If so, how?
> >
> >
> > Thanks.
> >
> > _
> > Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg 
> > när de skickar e-post till dig.
> > http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
> > ___
> > 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
  
_
Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg när 
de skickar e-post till dig.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Stored procedures" performance issue

[Pavel Ivanov]

> IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
> function, if I encounter a date greater than my birthday, then I want to tell 
> SQLite to stop searching, since the date is indexed.
>
> Is this possible? If so, how?

Even if this was possible it would be useless because you cannot say
if SQLite traverses table via index or makes just full table scan. And
in your particular case SQLite will see that there's no benefit in
using any index and will just scan the whole table.
The general advice in this case is to add column to the table which
will store the result of your function, create an index on that
additional column and then use that column in the query. In this case
SQLite will understand that it's better to use index and will use it.

Pavel

On Mon, Oct 5, 2009 at 9:24 AM, Kristoffer Danielsson
 wrote:
>
> When I create my own "stored procedures" using sqlite3_create_function, I get 
> horrible performance (which I expected) even though the column of interest is 
> INDEXED.
>
>
> Consider this sample (it's stupid, but it shows my problem):
>
> SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate);
>
>
>
> IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
> function, if I encounter a date greater than my birthday, then I want to tell 
> SQLite to stop searching, since the date is indexed.
>
>
>
> Is this possible? If so, how?
>
>
> Thanks.
>
> _
> Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg 
> när de skickar e-post till dig.
> http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
> ___
> 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] "Stored procedures" performance issue

[Kristoffer Danielsson]

When I create my own "stored procedures" using sqlite3_create_function, I get 
horrible performance (which I expected) even though the column of interest is 
INDEXED.

 
Consider this sample (it's stupid, but it shows my problem):

SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate); 

 

IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
function, if I encounter a date greater than my birthday, then I want to tell 
SQLite to stop searching, since the date is indexed.

 

Is this possible? If so, how?


Thanks.
  
_
Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg när 
de skickar e-post till dig.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Most wanted features of SQLite?: Stored procedures

[BareFeet]

On 20/09/2009, at 6:57 AM, Simon Slavin wrote:
>
> Ah.  Okay, so in SQLite3 you can emulate stored procedures using  
> triggers.  Just define a trigger to operate on something that  
> doesn't matter to you.  For instance inserting a record in a table  
> that you never bother reading.  Every so often you delete all rows  
> in the table just to keep it from taking up pointless space.

Unfortunately triggers can't fill the need of stored procedures. A  
trigger can only perform certain SQL tasks, specifically update,  
insert, delete, select raise(error). For full procedures, we need the  
full SQL syntax, such as create temp table, pragma, create temp index,  
attach etc.

Currently, I store procedures as text in a "Procedures" table in my  
database. But to execute them, I have to use SQL to copy out the text  
to my code, reinject it into SQL, pull out any results back into my  
code, potentially reinject into another SQL statement etc. Proper  
procedures would facilitate one call to SQLite, syntax checking of the  
procedure, precompiled optimizations etc.

Tom
BareFeet

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

Re: [sqlite] Stored procedures

[John Stanton]

Sqlite supports user loaded functions and triggers but not stored 
procedures.  You can probably achieve the functionality you need with 
functions and/or triggers.


VIGNY Cecilia wrote:


Hi,

Does SQLite supports stored procedures ? If it does, what is the 
appropriated syntax ?


Thanks.


Ce message est protégé par les règles relatives au secret des 
correspondances. Il est donc établi à destination exclusive de son 
destinataire. Celui-ci peut donc contenir des informations 
confidentielles. La divulgation de ces informations est à ce titre 
rigoureusement interdite. Si vous avez reçu ce message par erreur, merci 
de le renvoyer à l'expéditeur dont l'adresse e-mail figure ci-dessus et 
de détruire le message ainsi que toute pièce jointe.


This message is protected by the secrecy of correspondence rules. 
Therefore, this message is intended solely for the attention of the 
addressee. This message may contain privileged or confidential 
information, as such the disclosure of these informations is strictly 
forbidden. If, by mistake, you have received this message, please return 
this message to the addressser whose e-mail address is written above and 
destroy this message and all files attached.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Stored procedures

[VIGNY Cecilia]

Hi,

Does SQLite supports stored procedures ? If it does, what is the 
appropriated syntax ?


Thanks.


Ce message est protégé par les règles relatives au secret des correspondances. 
Il est donc établi à destination exclusive de son destinataire. Celui-ci peut 
donc contenir des informations confidentielles. La divulgation de ces 
informations est à ce titre rigoureusement interdite. Si vous avez reçu ce 
message par erreur, merci de le renvoyer à l'expéditeur dont l'adresse e-mail 
figure ci-dessus et de détruire le message ainsi que toute pièce jointe.

This message is protected by the secrecy of correspondence rules. Therefore, 
this message is intended solely for the attention of the addressee. This 
message may contain privileged or confidential information, as such the 
disclosure of these informations is strictly forbidden. If, by mistake, you 
have received this message, please return this message to the addressser whose 
e-mail address is written above and destroy this message and all files attached.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Stored procedures in triggers

[Ralf Junker]

>Would it be useful to have recursive DELETE triggers
>even without recursive INSERT or UPDATE triggers? 

Recursive DELETE triggers would certainly be usefull and have in fact already 
been asked for on this list occasionally. They would allow to move referential 
integrity of hierarchical data out of the application and into the database.

My vote is a strong "Yes!" in favour of recursive DELETE triggers!

Ralf

Btw: Does the SQL standard say anything about recursive triggers? Is there a 
reserved word to make a trigger recursive or not? Could there be an 
(application defined) limit on INSERT and UPDATE recursions for to solve the 
endless loop / stack/memory overflow problem? 

RE: [sqlite] Stored procedures in triggers

[Cariotoglou Mike]

> 
> Thoughts?  Would making recursive triggers an error rather 
> than just silently ignoring them break anybody's code?

even if it does, it should. otherwise, people may assume that the
functionality exists,and rely on it.
 
> I'm also looking at making DELETE triggers recursive.  I can 
> do that because recursive DELETE triggers are guaranteed to 
> terminate (you will eventually run out of rows to delete.)  
> But INSERT or UPDATE triggers might go on forever.  There are 
> also technical issues that make recursive INSERT and UPDATE 
> triggers more difficult so that I would prefer to delay 
> implementing them.
> 
> Comments?  Would it be useful to have recursive DELETE 
> triggers even without recursive INSERT or UPDATE triggers?
not much IMHO

Re: [sqlite] Stored procedures in triggers

[drh]

"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
> Cascading triggers are supported [in SQLite], recursive triggers are not. 
> That is, if you have an insert trigger on table A which, say, inserts 
> into table B, and there's an insert trigger on table B, it will run. But 
> if this latter trigger turns around and inserts into table A, the A 
> trigger won't run again.
> 

I've been looking into this.  Right now, if you have a recursive
trigger, it just doesn't run.  There is no error.  I'm thinking of
perhaps changing that so that you do at least get an error message.

Thoughts?  Would making recursive triggers an error rather than
just silently ignoring them break anybody's code?

I'm also looking at making DELETE triggers recursive.  I can do that
because recursive DELETE triggers are guaranteed to terminate (you
will eventually run out of rows to delete.)  But INSERT or UPDATE 
triggers might go on forever.  There are also technical issues that
make recursive INSERT and UPDATE triggers more difficult so that I
would prefer to delay implementing them.

Comments?  Would it be useful to have recursive DELETE triggers
even without recursive INSERT or UPDATE triggers?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Stored procedures in triggers

[Ran]

See the email of Igor Tandetnik from 18-Dec-2005:

"Vishal Kashyap" wrote
> Is their any way we can write simple stored procedures or functions
> in sqlite. If yes please do guide me I need this functionality in one
> of my open source project.

Not in the usual sense, meaning some language that gets stored in the
database itself together with the data. The only thing that comes
somewhat close is a trigger. It is possible to create a poor man's
stored procedure like this:

create table sp_dosomething (param1 int, param2 char);
create trigger sp_dosomething_impl
instead of insert on sp_dosomething
begin
-- one or more sql statements possibly referring to
-- new.param1 and new.param2
end;

-- To invoke:
insert into sp_dosomething values(1, 'hello');


Note that triggers are rather limited in what they can do. They are just
a bunch of SQL statements, there is no control flow (loops, if then
else, goto) beyond what little you can implement in pure SQL. They
cannot return values, except indirectly by inserting or updating some
table. SQLite does not support cascading triggers, so if your "stored
procedure" manipulates some table to which regular triggers are attached
(perhaps ensuring data integrity), those triggers won't run.


SQLite supports custom functions - see sqlite3_create_function[16]. You
write them in C (or any other language that has bindings to SQLite API)
and you have to install them every time you open a DB handle with
sqlite3_open, before you can refer to them in your SQL statements. They
are not stored in the database file itself.

Finally, SQLite prepared statements (sqlite_prepare) can be thought of
as simple stored procedures defined in your program. Similar to custom
functions, you can prepare a statement right after opening the database,
then keep it around.

Igor Tandetnik



Ran


On 3/24/06, Chethana, Rao (IE10) <[EMAIL PROTECTED]> wrote:
>
>   Hi,
>
> Can you tell me how to create a stored procedure in an sqlite3 database
> and use the same in a trigger? Please provide an example (as complete as
> possible). In the stored procedure I need to execute few queries on some
> tables. Can you tell me how to do that also?
>
> Any help is deeply appreciated.
>
> Best Regards,
>
> Chethana
>

[sqlite] Stored procedures in triggers

[Chethana, Rao \(IE10\)]

 Hi, 

Can you tell me how to create a stored procedure
in an sqlite3 database and use the same in a trigger? Please provide an example
(as complete as possible). In the stored procedure I need to execute few
queries on some tables. Can you tell me how to do that also?

Any help is deeply appreciated.

Best Regards,

Chethana

Re: [sqlite] stored procedures

[Dan Kennedy]

One reason might be that SQLite does not usually include anything that 
can be just as well implemented externally. 

I could be missing something, because I'm not really sure of the advantages 
of stored procedures, but it seems to me that an implementation could be 
created without modifying SQLite itself. 

--- Lloyd Dupont <[EMAIL PROTECTED]> wrote:

> I'm not sure it's a real justification
> I believe that stored procedure are more than convenience to do avoid 
> multiple client-serveur call
> 
> For exemple lately I wanted to created to related table (kind of 
> MASTER_TABLE, PROPERTY_TABLE)
> property should be destroyed/created with master record.
> I used trigger for that.
> But I read once that TRIGGER are evil. And I do feel it, when I write my 
> INSERT in MASTER_TABLE, it's quite easy to forget that that a record is 
> created as well in PROPERTY_TABLE (with some link ID updated in both table).
> Whereas a stored Procedure would have enable me to clearly look at the whole 
> procedure as one single operation.
> 
> - Original Message - 
> From: "Jay Sprenkle" <[EMAIL PROTECTED]>
> To: <sqlite-users@sqlite.org>
> Sent: Wednesday, June 15, 2005 12:40 AM
> Subject: Re: [sqlite] stored procedures
> 
> 
> >> BTW I wonder why SQLite doesn't support Stored Procedure.
> >> Through Trigger it does already support some similar functionality.
> >> Certainly, while writing trigger code it won't have been that much code 
> >> to
> >> write stored procedure code as well.
> >> That kind of puzzle me.. is there any rationale for the lack of stored
> >> procedure?
> >
> > Stored procedures are code run by the server. There isn't a server with 
> > SQLite.
> > SQLite is a database file structure that uses SQL to access it.
> > 
> 
> 




__ 
Yahoo! Mail Mobile 
Take Yahoo! Mail with you! Check email on your mobile phone. 
http://mobile.yahoo.com/learn/mail