Re: [sqlite] Question about SQLite features.
>From: "Kees Nuyt" >Thursday, November 11, 2010 10:34:51 AM > >Stored procedures don't enforce business rules by >themselves. Constraints and triggers do. >To enforce business rules stored as procedures in the >database, one would need an access system which prevents >direct modification of table data. >As an embedded database SQLite definately is in an other >niche of the DBMS market. >Stored procedures can often be replaced by INSTEAD OF >triggers on views, that's quite powerful. OK, they can't truly enforce business rules but nothing enforces good design on my C++ code either. What it does is allow the business rules to be packaged as a complete unit with the data and I claim that would be better design and more maintainable over the life of the project. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
"Chris Wolf" schrieb
[Nested Recordsets/Resultsets as an alternative to Joins,
to "shape-off" redundancy in "hierarchical requests"...
as a native DB-Feature usable over Sybase StoredProcs...
...and the ADO-ShapeProvider as an example for an
alternative to use "these things" in a DBEngine-independent
way (at least on the Win-Platform)]
> > If you request the construction of such a nested
> > Recordset at the serverside (over the Shape-
> > Provider), then there are no network-roundtrips
> > involved, in case the DB-Server and the
> > AppServer do run on the same machine.
> >
> > But we digress ... ;-)
> >
> In your scenario, here, even though the middle-tier
> ("business-layer-code") is collocated with
> the database, it looks to me like ADO is still a client-server
> technology (I'm relatively unfamiliar with it) you still need
> a "Connection" object to connect with the database, ...
You're basically right - ADO-Resultsets are the "storage-
container" (with an integrated Cursor-Engine), that
"looks always the same" to your Application-Code -
and the "ADO-Connection-Object" connects (involving
the OLEDB-driver) to the DBEngine in question.
And in case the DBengine runs on the same machine as
the OLEDB-Driver, then faster IPC-mechanisms
(than sockets) are usually involved.
> ...so I assume there's still a protocol stack, through which,
> the client-based "SHAPE" mechanism must make multiple
> (local) round trips.
The "protocoll-stack" is thin in this (local) case - but if
you want to put it this way, then yes - the SHAPE-Provider
will probably perform "multiple Requests" - how efficient
it does this (compared with "native Sybase") would need
to be tested.
At least I could imagine some Optimizations (working
over Joins, which include only the Parents Primary Key
in the Joined result, to avoid overboarding redundancy,
before the final Resultset is constructed and delivered
into the "hierarchical ADO-Recordset-container".
> Even though the ADO Connection is not as heavy-weight
> as HTTP, or even TCP/IP (I'm assuming for local connections
> it may be via named pipes) ...
...or Shared Memory...
> There's still serialization/deserialization of the client-server
> protocol stack.
No, would not think so - since most OLEDB-providers
(as well as the ADO-Recordsets) also support so
called "serverside cursors" - the "looping over incoming
records" can be done efficiently "in place", building the
hierarchy "on the fly" then (no larger buffer-conversion
or -swapping going on IMO).
> With stored procedures, the multiple open cursors to different
> tables are right there, running in the same process/thread
> space of that stored proc - no connection, no protocol
> stack, so it's going to be "much" faster.
I would not bet on your "much"-attribute that much... ;-)
After all there is an overhead of course - but the DB-engine
itself will need a larger amount of time internally, to perform
the "joining" (the key-lookups on the dependent tables).
How much faster native Sybase is, compared with the more
generic "ADO-Shaper" ... only a test can tell (in case you're
running on windows, this should be relative easy to do).
But the general point you made is understood I think -
Stored procedures, which do make use of builtin
specialities can (or better: usually) offer a better performance -
the question is, "how badly" an application is in need
of that advantage (or if it is large enough, to be noticable
at all).
Olaf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
On Nov 11, 2010, at 9:38 PM, Ian Hardingham wrote: > Haha! Sqlite is embedded by others. It NEVER embeds. SQLite's tagline of the week :P ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
I hope this doesn't get posted twice. I don't think it will since I sent form wrong account first time. Sorry. >From: "Kees Nuyt" >Thursday, November 11, 2010 10:34:51 AM > >Stored procedures don't enforce business rules by >themselves. Constraints and triggers do. >To enforce business rules stored as procedures in the >database, one would need an access system which prevents >direct modification of table data. >As an embedded database SQLite definately is in an other >niche of the DBMS market. >Stored procedures can often be replaced by INSTEAD OF >triggers on views, that's quite powerful. OK, they can't truly enforce business rules but nothing enforces good design on my C++ code either. What it does is allow the business rules to be packaged as a complete unit with the data and I claim that would be better design and more maintainable over the life of the project. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
Olaf Schmidt wrote:
> "Chris Wolf" schrieb
>
>
>> I can't resist adding my little opinion to yet another
>> "business logic in stored procs vs. app layer" holy war...
>>
>
> ... yeah, seems this thread is evolving nicely in this
> regard ...
>
>
>> I usually prefer keeping the business logic in the application
>> layer and leaving the DB tier to be just a data provider.
>> In actual practice, this is not always practical.
>>
>> Let's say you implement a service where the client can
>> retrieve a set of top-level records,
>> each has an id; then for each id, you get additional
>> detail records from numerous detail/line-item tables.
>> If you implement this as a collection of fine-grained services,
>> i.e. each piece is a round trip from client, through web
>> services layer, through to db layer; and for each top-level id
>> in the result set - the performance will be abysmal.
>>
>> With Sybase stored procs, you can stack multiple result
>> sets in one call, so in the above scenario, you invoke the
>> lookup proc for each top-level id and the proc performs
>> all the secondary detail queries and stacks it all together
>> in a multiple-results result-set, such that there's only one
>> round-trip through the tiers for each top-level id in the set.
>>
>
> But that is a common problem, which can be solved
> directly in the business-layer-code at the serverside
> as well, also achieving a "stacked serialisation
> of resultsets" in one roundtrip.
> One can either code such a thing (a stacked serialization)
> "by hand" (based on XML for example, which is well-nestable) -
> or on a given platform (e.g. on Windows) one can avoid
> these hand-coded parts by making use of one of the
> already mentioned "DB-abstraction-helpers".
> E.g. ADO does support so called "shaped, hierarchical
> Recordsets" for a long time now ... over the "DataShape-
> Provider" which is part of ADO (and plays together with
> a lot of OLEDB-providers from different vendors...
> not sure if the Sybase-Provider is one of those, which is
> "Shape-Provider-capable").
> http://support.microsoft.com/kb/189657
>
> If you request the construction of such a nested
> Recordset at the serverside (over the Shape-
> Provider), then there are no network-roundtrips
> involved, in case the DB-Server and the
> AppServer do run on the same machine.
>
> But we digress ... ;-)
>
>
In your scenario, here, even though the middle-tier
("business-layer-code") is collocated with
the database, it looks to me like ADO is still a client-server
technology (I'm relatively
unfamiliar with it) you still need a "Connection" object to connect with
the database,
so I assume there's still a protocol stack, through which, the
client-based "SHAPE" mechanism must make
multiple (local) round trips. Even though the ADO Connection is not as
heavy-weight
as HTTP, or even TCP/IP (I'm assuming for local connections it may be
via named pipes)
There's still serialization/deserialization of the client-server
protocol stack.
With stored procedures, the multiple open cursors to different tables
are right there,
running in the same process/thread space of that stored proc - no
connection, no protocol
stack, so it's going to be "much" faster.
-Chris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
"Chris Wolf" schrieb > I can't resist adding my little opinion to yet another > "business logic in stored procs vs. app layer" holy war... ... yeah, seems this thread is evolving nicely in this regard ... > I usually prefer keeping the business logic in the application > layer and leaving the DB tier to be just a data provider. > In actual practice, this is not always practical. > > Let's say you implement a service where the client can > retrieve a set of top-level records, > each has an id; then for each id, you get additional > detail records from numerous detail/line-item tables. > If you implement this as a collection of fine-grained services, > i.e. each piece is a round trip from client, through web > services layer, through to db layer; and for each top-level id > in the result set - the performance will be abysmal. > > With Sybase stored procs, you can stack multiple result > sets in one call, so in the above scenario, you invoke the > lookup proc for each top-level id and the proc performs > all the secondary detail queries and stacks it all together > in a multiple-results result-set, such that there's only one > round-trip through the tiers for each top-level id in the set. But that is a common problem, which can be solved directly in the business-layer-code at the serverside as well, also achieving a "stacked serialisation of resultsets" in one roundtrip. One can either code such a thing (a stacked serialization) "by hand" (based on XML for example, which is well-nestable) - or on a given platform (e.g. on Windows) one can avoid these hand-coded parts by making use of one of the already mentioned "DB-abstraction-helpers". E.g. ADO does support so called "shaped, hierarchical Recordsets" for a long time now ... over the "DataShape- Provider" which is part of ADO (and plays together with a lot of OLEDB-providers from different vendors... not sure if the Sybase-Provider is one of those, which is "Shape-Provider-capable"). http://support.microsoft.com/kb/189657 If you request the construction of such a nested Recordset at the serverside (over the Shape- Provider), then there are no network-roundtrips involved, in case the DB-Server and the AppServer do run on the same machine. But we digress ... ;-) > I don't see how this is pertinent to SQLite, since it's > generally not used in multi-user client-server configurations. I'm using it here in (smaller to midsized) multi-user scenarios - my COM-wrapper (Dll) contains fast serialization-mechanisms and a built-in AppServer for that "mode". And I know that some Readers of this list also use SQLite behind Servers (mostly over http then) (the Fossil-repository is just another example). These solutions are certainly not "large enterprise-stuff" - but reliable and fast enough for ones own "special applications". Olaf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
Haha! Sqlite is embedded by others. It NEVER embeds. - Original message - > > On Nov 10, 2010, at 11:05 AM, Andy Gibbs wrote: > > > > That's I don't know SQLite have stored procedure support? > > > > > > > How're your C skills? > > Or perhaps SQLite should embed Lua [1] as its powerful, fast, > lightweight, scripting language and be done with it :) > > [1] http://www.lua.org/about.html > > ___ > 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] Question about SQLite features.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/11/2010 06:26 AM, jeff archer wrote: > The overall design and structure of applications using SQLite and > therefor SQLite itself would benefit from SQLite supporting stored > procedures. SQLite includes mechanisms to implement almost anything, without forcing particular policies. For example it is relatively trivial to embed in almost any programming language. It is very easy to add your own functions, collations and other forms of extension. The code is public domain so it means there is no restriction on you or anyone else making whatever changes you want and distributing them however you want. (The only restriction is you can't call the result SQLite.) In this example you can use the auto_extension mechanism plus storing script code in the database to get a lot of the way towards your goal. Also consider that currently SQLite database files are safe - they are just data. You can have no fear of taking random SQLite data files off the net and loading them into any program. Adding some form of stored procedures means that code can now run, and that code could be malicious (eg causing the allocation of lots of memory, infinite loops to consume all cpu and not return control back to the caller). SQLite takes compatibility very seriously. A program written and linking against SQLite 3.0.0 will work today without recompilation against a dll compiled with the latest SQLite code. (A recompilation will also succeed.) Anything added to SQLite is a commitment to update it, maintain it, test it etc till SQLite 3 is end of lifed. There are often requests to add things to the SQLite core, but the Lite is there for a reason and the main job of the developers is to say "no" - as in "small, fast, reliable - pick any three". So why don't you add stored procedures to SQLite to demonstrate their utility? And if you aren't a coder then convince coders to help you. The pool of people collaborating and using them would be a good argument towards including the functionality in the core. On the other hand if what you are really arguing is that the SQLite developers should do this work for you, then I'm sure they'd be happy to give you a quote. Start here: http://www.hwaci.com/sw/sqlite/prosupport.html#mods Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzc4usACgkQmOOfHg372QTcLACfQvu4sLjtf7ufoEHAVqUb+Y0D cVYAniNn1OLI+uHcDFhVowDCDOXtAg8K =NEhw -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
Olaf Schmidt wrote: > "Petite Abeille" schrieb > >> On Nov 11, 2010, at 8:30 PM, Olaf Schmidt wrote: >> >> >>> If such an "encapsulation of business-rules" is sitting in the >>> DB itself - written in a proprietary "DB-dialect", then you >>> cannot call such a thing a "business-layer" anymore. >>> >> Nonsense :)) >> > > Of course... ;-) > > Nah, seriously... you know, how I meant that and what > the context was ... *if* somebody decides to handle > DB-Interaction (and his "set of business-rules") *not* > directly "in the client" (or alternatively "in the DB-Server") - > then he obviously does so, to decouple the Client-Application > from the DB(-Backend) - allowing then (if done right), > to "connect" this intermediate layer to different clientside > Implementations (GUIs) - as well as different DB-Backends. > > Encapsulated in a Dll with the right interfaces, one can > use it either serverside (e.g. behind a WebServer, to > talk to Browser-Clients - delivering JSON- or XML- > serialized Resultset-Content) - or behind a "real AppServer", > to talk to "Fat Clients" (delivering Resultset-Content > in a serialized "Object-Container" - as for example > disconnected ADO-Recordsets on Windows, which > are then understood by a large Set of languages, easily > bindable with mostly only one line of code to a > DataGrid or whatever GUI-Widget). > Heck, you can put such a layer-Dll even at the clientside, > to support a standalone App or alternatively a more > Client/Server-like approach, capable to work against > a different set of DB-engines even then (in the standalone > App for example, against SQLite). > > > ["Helsinki Declaration(s)"...] > Cannot disagree more with these articles, sorry. > >From my experience his main-assumption is just > not true, that it is more difficult to develop such > a layer with "modern languages or environments" - > compared with a proprietary DB-dialect and > some DB-specific enhancements or features. > > Perhaps you should give an example of a certain > stored procedure (not too complex, to keep > things more simple), describe what it does - and > then compare it with the implementaion-code, > done in a "normal language", which does use > ODBC/JDBC/ADO or whatever and is using > only "common SQL-statements", to achieve > the same thing in a DB-engine-independent way? > > > Olaf > > > I can't resist adding my little opinion to yet another "business logic in stored procs vs. app layer" holy war... I usually prefer keeping the business logic in the application layer and leaving the DB tier to be just a data provider. In actual practice, this is not always practical. Let's say you implement a service where the client can retrieve a set of top-level records, each has an id; then for each id, you get additional detail records from numerous detail/line-item tables. If you implement this as a collection of fine-grained services, i.e. each piece is a round trip from client, through web services layer, through to db layer; and for each top-level id in the result set - the performance will be abysmal. With Sybase stored procs, you can stack multiple result sets in one call, so in the above scenario, you invoke the lookup proc for each top-level id and the proc performs all the secondary detail queries and stacks it all together in a multiple-results result-set, such that there's only one round-trip through the tiers for each top-level id in the set. I don't see how this is pertinent to SQLite, since it's generally not used in multi-user client-server configurations. -Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
"Petite Abeille" schrieb > > On Nov 11, 2010, at 8:30 PM, Olaf Schmidt wrote: > > > If such an "encapsulation of business-rules" is sitting in the > > DB itself - written in a proprietary "DB-dialect", then you > > cannot call such a thing a "business-layer" anymore. > > Nonsense :)) Of course... ;-) Nah, seriously... you know, how I meant that and what the context was ... *if* somebody decides to handle DB-Interaction (and his "set of business-rules") *not* directly "in the client" (or alternatively "in the DB-Server") - then he obviously does so, to decouple the Client-Application from the DB(-Backend) - allowing then (if done right), to "connect" this intermediate layer to different clientside Implementations (GUIs) - as well as different DB-Backends. Encapsulated in a Dll with the right interfaces, one can use it either serverside (e.g. behind a WebServer, to talk to Browser-Clients - delivering JSON- or XML- serialized Resultset-Content) - or behind a "real AppServer", to talk to "Fat Clients" (delivering Resultset-Content in a serialized "Object-Container" - as for example disconnected ADO-Recordsets on Windows, which are then understood by a large Set of languages, easily bindable with mostly only one line of code to a DataGrid or whatever GUI-Widget). Heck, you can put such a layer-Dll even at the clientside, to support a standalone App or alternatively a more Client/Server-like approach, capable to work against a different set of DB-engines even then (in the standalone App for example, against SQLite). ["Helsinki Declaration(s)"...] Cannot disagree more with these articles, sorry. >From my experience his main-assumption is just not true, that it is more difficult to develop such a layer with "modern languages or environments" - compared with a proprietary DB-dialect and some DB-specific enhancements or features. Perhaps you should give an example of a certain stored procedure (not too complex, to keep things more simple), describe what it does - and then compare it with the implementaion-code, done in a "normal language", which does use ODBC/JDBC/ADO or whatever and is using only "common SQL-statements", to achieve the same thing in a DB-engine-independent way? Olaf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
On Thu, Nov 11, 2010 at 5:36 PM, Petite Abeille wrote: > > On Nov 12, 2010, at 12:31 AM, Jay A. Kreibich wrote: > >> There have been many proposals to do just this, and in specific, >> with Lua. Outside of some moderate technical issues, the >> big problem is the license. Something like that would *never* >> be part of the SQLite core because the Lua license is not >> compatible with SQLite's license-- i.e. it is not in the public >> domain. > > hmmm... not wanting to go down lawyer lane, but... isn't Lua's MIT license as > liberal as it gets? Just curious. > Public domain is "as liberal as it gets." Nothing else is. -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
On Nov 12, 2010, at 12:31 AM, Jay A. Kreibich wrote: > There have been many proposals to do just this, and in specific, > with Lua. Outside of some moderate technical issues, the > big problem is the license. Something like that would *never* > be part of the SQLite core because the Lua license is not > compatible with SQLite's license-- i.e. it is not in the public > domain. hmmm... not wanting to go down lawyer lane, but... isn't Lua's MIT license as liberal as it gets? Just curious. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
On Thu, Nov 11, 2010 at 09:05:15PM +0100, Petite Abeille scratched on the wall: > Or perhaps SQLite should embed Lua [1] as its powerful, fast, > lightweight, scripting language and be done with it :) There have been many proposals to do just this, and in specific, with Lua. Outside of some moderate technical issues, the big problem is the license. Something like that would *never* be part of the SQLite core because the Lua license is not compatible with SQLite's license-- i.e. it is not in the public domain. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
On Nov 10, 2010, at 11:05 AM, Andy Gibbs wrote: >> That's I don't know SQLite have stored procedure support? >> > > How're your C skills? Or perhaps SQLite should embed Lua [1] as its powerful, fast, lightweight, scripting language and be done with it :) [1] http://www.lua.org/about.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
On Nov 11, 2010, at 8:30 PM, Olaf Schmidt wrote: > If such an "encapsulation of business-rules" is sitting in the > DB itself - written in a proprietary "DB-dialect", then you > cannot call such a thing a "business-layer" anymore. Nonsense :)) In any case, for these of us who do want to be closer to their data, please refer back to Toon Koppelaars' "The Helsinki declaration" series: [health warning: oracle centric] http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declaration-observation-1.html http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declaration-observation-2.html http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declaration-observation-3.html http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declaration-observation-4.html http://thehelsinkideclaration.blogspot.com/2009/04/helsinki-code-layers-in-dbms.html http://thehelsinkideclaration.blogspot.com/2009/06/continuing-with-part-2-of-helsinki.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
"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". All nice and well, but the problem is two-fold IMO. Portability with regards to Application-Code (exchangeability of "The DB" among different languages) and on the other hand, portability of the Data (the DB, the "Backend"). >From my experience one should use "DB-internal mechanisms" only in a range, which is common among different DB-engines (Triggers for example, to ensure referential integrity at least). But a "Stored-procedure-language" which is more or less proprietary to a certain DB, does not work out that well, if your Application (or your Business-Layer with your "business-rules") needs to be able, to talk to a different backend (since your new customer "just made the wish"...;-) Then backend-portability is required - and the less voluminous and complex your "special code" in your current DB-backend is, the faster will be your switch to a new backend (using the App-language for the business-rules then, supported by some sort of "DB-Abstraction-Helper" of course - as e.g. JDBC, ODBC - or ADO/OLEDB in the Windows-world). And if you want to ensure (aside from easy backend- portability), that your "business-rules" (your business- layer) survives "App-language-switches" as well, then you should encapsulate it in a component (a Dll for example) - containing a public reachable API, which is usable/callable from different languages then. If such an "encapsulation of business-rules" is sitting in the DB itself - written in a proprietary "DB-dialect", then you cannot call such a thing a "business-layer" anymore. A layer is a thing, sitting "in-between" (to abstract from two sides, in this case the App-language(s) *and* the Storage-Engine(s)) ... and not a thing "sitting inside somewhere". Just my 2cts... Olaf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
On Thu, 11 Nov 2010 06:26:31 -0800 (PST), jeff archer wrote: >>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. Stored procedures don't enforce business rules by themselves. Constraints and triggers do. To enforce business rules stored as procedures in the database, one would need an access system which prevents direct modification of table data. As an embedded database SQLite definately is in an other niche of the DBMS market. Stored procedures can often be replaced by INSTEAD OF triggers on views, that's quite powerful. >Please don't anyone take this comment wrongly. I really like SQLite and I am >very committed to its use in my projects. Many thanks to all who make SQLite >possible. It is an excellent product. And the support available through this >mailing list is truly top notch. I have paid (sometimes dearly) for a >lot less >competent and complete support. I second this, of course! -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
>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. Please don't anyone take this comment wrongly. I really like SQLite and I am very committed to its use in my projects. Many thanks to all who make SQLite possible. It is an excellent product. And the support available through this mailing list is truly top notch. I have paid (sometimes dearly) for a lot less competent and complete support. Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com <330>819.4615 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
Hey "Andy Gibbs". Why your code isn't added into sqlite? - "Andy Gibbs" escreveu: > On Tuesday, November 09, 2010 8:29 AM, Tran Van Hoc wrote: > > > Dear all. > > > > I'm using SQLite and many thanks for your supports. > > > > I have problem about SQLite features. > > > > That's I don't know SQLite have stored procedure support? > > > > How're your C skills? If you are comfortable with the idea, then it really > isn't too hard to add such support to sqlite, but you need to have a good > level of C programming skills and also an understanding of the lemon grammar > to do it. Speaking from experience, adding a procedural language into > sqlite is not particularly hard (I did it in under 2 weeks which included > the appropriate additions to the test scripts). I have to say, sqlite is > probably the best-written bit of open-source code I know of, in terms of how > easy it is to understand the original programmer's intention and to extend > it. > > I wish you all the best, if you try it! > > Andy > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- -- Atenciosamente/Regards, Israel Lins Albuquerque Desenvolvimento/Development Polibrás Brasil Software Ltda. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
"Tran Van Hoc" schrieb im Newsbeitrag news:43a0ef604a674b3fb80d1447b41f8...@isbvietnam.com... [Stored procedures in SQLite] IMO stored procedure-support only makes sense in "Server-Instances" which run on their own (and communicate over different IPC-mechanisms, mainly sockets, with their "Clients"). But SQLite is not such "a Server" - it's a *library* and as such it offers its API directly to the hosting Process. If you choose, to implement a small Server- Host (talking over sockets with "DB-Clients"), then you can write your stored Procedures in any language you want - either "in a static way" which would be implemented in the language you choose to write the Server-Host-Application with (in case this language has no scripting-capabilities). But if you choose TCL/Perl/Ruby/Lua/Python/etc. as your scripting-language, you're free to do so too - then your scripts could even be stored within "normal DB-Tables" in your serverside SQLite-DB and executed dynamically on request. No need to learn "special Stored-Procedure-syntax" - just talk in your scripting-language of choice with SQLite - using everything the SQLite-API has to offer (depending a bit on the wrapper-bindings of the scripting-language in question). If you do not want to implement such a hosting Server-instance yourself, you could always choose a WebServer-environment as "the Server-layer". These Hosts can "talk http" - and on the serverside you have support for all sorts of scripting-languages, as the ones just listed above. Olaf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
On Tuesday, November 09, 2010 8:29 AM, Tran Van Hoc wrote: > Dear all. > > I'm using SQLite and many thanks for your supports. > > I have problem about SQLite features. > > That's I don't know SQLite have stored procedure support? > How're your C skills? If you are comfortable with the idea, then it really isn't too hard to add such support to sqlite, but you need to have a good level of C programming skills and also an understanding of the lemon grammar to do it. Speaking from experience, adding a procedural language into sqlite is not particularly hard (I did it in under 2 weeks which included the appropriate additions to the test scripts). I have to say, sqlite is probably the best-written bit of open-source code I know of, in terms of how easy it is to understand the original programmer's intention and to extend it. I wish you all the best, if you try it! Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
> That's I don't know SQLite have stored procedure support? No. There's only limited support of triggers, i.e. triggers don't have some full-featured programming language, they are just a set of selects, updates, deletes or inserts Pavel On Tue, Nov 9, 2010 at 2:29 AM, Tran Van Hoc wrote: > Dear all. > > I'm using SQLite and many thanks for your supports. > > I have problem about SQLite features. > > That's I don't know SQLite have stored procedure support? > > > > Expect your respond. > > Thanks you very much. > > > > ___ > 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] Question about SQLite features.
Dear all. I'm using SQLite and many thanks for your supports. I have problem about SQLite features. That's I don't know SQLite have stored procedure support? Expect your respond. Thanks you very much. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
