"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 <g>. Olaf _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
