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, 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
And not look like this (psuedo application code), with application code
reinjecting the results of separate SQL calls into each other:
int invoiceNumber = execQuery(
select "Invoice Number" from "Invoice List" where "GUI Selection Flag"
= 1;
)
array detailResults = execQuery(
select "Product Code", "Product Name", Quantity, Price from "Invoice
Items" where "Invoice Number" = ?;
arguments: invoiceNumber
)
for (detailResult in detailResults)
{
string productCode = value of column "Product Code" in detailResult
bool isAProduct = execQuery(
select "Is a Product" from "Item List" where "Product Code" = ?;
arguments: productCode
)
if (isAProduct)
append detailResult to filteredDetailResults
}
show filteredDetailResults
> Anyways, hope my position is more clear to you now
Yes, thank you O:-)
> and - (since you made this request) - you feel "enlightened enough" in the
> meantime <g>.
Yes, indeed, please enlighten me further, with some sample code of any
procedural code that you think is necessary to intermix application code with
multiple SQL calls, or any branching logic based on data returned that affects
a subsequennt database call.
Thanks,
Tom
BareFeetWare
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
