The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 21, 2019, 12:55:43 PM

Login with username, password and session length


Pages: [1] 2
  Print  
Author Topic: What can I do with stored procedures  (Read 8629 times)
dink
Expert
****
Offline Offline

Posts: 349


View Profile
« on: May 30, 2008, 11:04:27 PM »

I think I've got a handle on 'why stored procedures?'.

Now it's time for me to learn some more about what can/can't be done with SP's.

So, are we limited to only sql in the SP's?
Logged

[quote Nutballs]
the universe has a giant fist, and its got enough whoop ass for everyone.
[/quote]
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #1 on: May 31, 2008, 01:06:29 PM »

Dink - lots of words here, but I'm not in ATM - I'll respond to this tonight
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #2 on: June 01, 2008, 05:41:51 PM »

Dink - an excellent book to get your hands on would be Stored Procedures for MySQL (O'Reilly) - it is a great starter to show what all can be done.

But a quick wrapper is that, although the embedded SQL language is somewhat funky (I personally do not like it at all) it is reasonable well featured. Loops, control structures, functions... really not that bad. It is lacking horribly from an extensibility standpoint ie., if you wanted to write a scraper that worked purely from stored procedures and functions, you'd have a lot of work to do writing extensions to the language in C or Java.

But more importantly, the language limits you in exactly the way it should IMO.

There are a few different camps with opinions about Stored Procedures/Functions/Triggers etc... (I'll use SPs from now on) and how/when to use them.

There's the Never Use SPs camp: This group of folks hates logic fragmentation and using multiple languages for a single task - which is good in a way. Unfortunately, this group would also be more comfortable whacking a nail into a wall with a screwdriver than having to learn how to implement a hammer. They are the opposite of the Best Tool For The Job crowd. The "Stored Procedures are Evil" clown is in this category.

There's the Always Use SPs group - this is not exactly how it sounds. This group would not (or at least, should not - but there are many Oraclites that would try) write a complete web application in embedded code. They would advocate that all access to the database is via stored procedures for security ie., no direct SQL statements. This group, rightly so, says that it is more easy to lock down a database where no one has access to the tables, layout or even straight up SQL - they must go through Stored Procedures. Meaning that every call to a DB is checked on the way in and the logic is locked down. This makes SQLinjection attacks really tough - nigh virtually impossible unless the coders of the stored procedure library do some silly things. This group also, unfortunately, would forego using a power saw to cut wood, choosing a hand-held instead because of user security. Their paranoia is more often rooted in a lack of understanding of other components than real security. The only really logical exception to this is huge companies that have thousands of users - or banks, for example, that have a multitude of ways that people/entities can access/modify data and the modifications must be very tightly controlled.

Then there's this kind of hybrid, some stored procedures, some direct SQL, some local code Best Tool For The Job crowd, of which I am one. Ergo, this is clearly the right way to think. Wink My position is this: It is entirely possible to write absolutely horrible code with any tool, fantastically easy to expose vulnerabilities with any methodology and incredibly easy to screw the pooch with any language. Or not. My personal philosophy is hardening around the notion of appropriate logic handler ie., have the best suited processor/process handle the logic at hand. An example: I've been working through pages and pages of my own blueprints trying to identify my strategy for inventory management given my new overarching framework. Without going in too deep, there's a BUNCH of logic that wraps around the addition or subtraction of a physical piece of inventory (don't ask why, there just is). And ALL of it eventually results in changes in database entities. Ergo, it is most appropriate that I write the SP inv_deltaQ(itemID, delta) where itemID is the item to be modified and delta is the number of plus-or-minus units that must change the number. See even there is an important change... since I never know who's buying something, adding inventory or whatever, even the process must simply increase or decrease the number we have. (For those of you reading along and about to flame me with QQs about "hard inventory numbers" there's also a function inv_auditedQ() where I can hard set the number, so shut up). Here's my point: now, any PHP routine that needs to adjust inventory needs only deal with *its* modification, and not all of the logic that goes into sorting out all the derivative numbers around the Q. In a way, this is exactly like Object Oriented encapsulation - the calling routine does not need to understand all the intricacies about inventory numbers to simply "sell one."

Extrapolating this out a bit, you can see that there are lots of places that SPs might make sense, depending on the logic behind the action:
  • Many accounting functions
  • Many transaction functions, which may affect inventory, accounting etc
  • Customer profile stuff - adding a new address and making it the current one for example
  • Surfer tracking - particularly when one is watching spiders versus surfers
  • Customer list management - if a customer record changes, should they or should they not still be included in a particular list (like a eblasting list)

This goes on, but you get the idea. IMO, if a chunk of logic is explicitly and utterly about database machinations, particularly if it might be executed by more than one entity, I think it makes a good potential candidate for an SP.

Now, given you can get an idea of where all I have SPs going, you can perhaps imagine that the language is reasonably strong, or these sorts of things would be out of reach.

Hope that helps,

/p
« Last Edit: June 01, 2008, 08:06:02 PM by perkiset » Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nop_90
Global Moderator
Lifer
*****
Offline Offline

Posts: 2203


View Profile
« Reply #3 on: June 01, 2008, 06:28:08 PM »

@perks good description
As ussual i will play devil advocate Smiley
Just wondering if the SP has something to do with MYSQL starting to charge.
It seems to be just another trap to get people into being tied into using a specific database.

There seems to be little (if anything) that SPs offer that a database wrapper like sqlobject http://www.sqlobject.org/
does not already do, and to boot u are not tied to a specific database. (perl and ruby offer similar solutions not sure about PHP)
All this logic could be tied into the class which describes the database.
And u are not crippled by a crappy language.

The logic of subtracting/adding inventory for example can be done much more elegantly with sqlobject.
sql injection with sqlobject is not really possible since it takes a subset of python and then creates the sql queries from it.

The security arguement really does not hold water. (same arguement was used with buffer overflows and scripting languages etc)
Only arguement that might hold water is speed. But with processing power being cheaper then programming time .....

Logged
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #4 on: June 01, 2008, 08:17:09 PM »

As ussual i will play devil advocate Smiley
I would expect no less, my friend Smiley


Just wondering if the SP has something to do with MYSQL starting to charge.
It seems to be just another trap to get people into being tied into using a specific database.

There seems to be little (if anything) that SPs offer that a database wrapper like sqlobject http://www.sqlobject.org/
does not already do, and to boot u are not tied to a specific database. (perl and ruby offer similar solutions not sure about PHP)
All this logic could be tied into the class which describes the database.
And u are not crippled by a crappy language.
It is true, that the vast majority, and in fact virtually anything that can be done as an SP can be done as an outside process. Your example site is a strong example of how people try to encapsulate SQL statements and mechanics into "other forms of thinking" - in that case, an OO wrapper. But there is part of the point: it's another wrapper on a language that is outside of the database. In this case, SPs have the opportunity to have the SQL prepared and ready to go - and there are lots of benefits when it comes to caching as well - so that it is as quick and instantaneous as possible.

Another thought: regardless of the wrapper around a database connection, if you have two different places that use the wrapper, they can use it differently - or if you have a load of logic that you want done around every change in a particular column/row/whatever, then you MUST make sure that all the outside code does things exactly the same way, or you've got trouble. In the case of SPs, the outside-the-db programmer cannot avoid the logic tied to a trigger or an SP - it is absolute.

And so back to my notion, if a "data movement" is *specifically and utterly* about the database, then it makes good sense to put it there, so that no matter what language/process/entity accesses/changes the data, it will be forced through the same logic sieve. Regardless of the handiness or cleverness of a wrapper, it cannot enforce things the same way that a DB trigger can.

The security arguement really does not hold water. (same arguement was used with buffer overflows and scripting languages etc)
Only arguement that might hold water is speed. But with processing power being cheaper then programming time .....
Interestingly I think it does, although it is WAY over the top, even for paranoid ol' me. If you have a system where security is at a double-extra premium, then having yet another layer between the user and the data is a good thing... and if there is no way to execute standard old SQL against a database (ergo you could not even get a hold on the schema, table layout etc) then you push the bad guys further away. However as I mentioned I think the need for this sort of paranoia is way beyond the norm.

Thanks for the debate Wink
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #5 on: June 01, 2008, 08:24:36 PM »

...regardless of the wrapper around a database connection, if you have two different places that use the wrapper, they can use it differently - or if you have a load of logic that you want done around every change in a particular column/row/whatever, then you MUST make sure that all the outside code does things exactly the same way, or you've got trouble. In the case of SPs, the outside-the-db programmer cannot avoid the logic tied to a trigger or an SP - it is absolute.

I wanted to expand that point just a bit... consider: you have two coders, each touching a DB. One chooses Python, another PHP - the logic between the two (as regards a particular type of transaction) MUST be identical to preserve the integrity of the data, but the programmers can really do things in two different ways. My point here is that the way we talk to a database from the outside is different than enforcing integrity from the inside. Clearly, stored procedures are far from a perfect tool, but they make a way for us to enforce logic at the DB level regardless of how we talk to it.

Sorry if that's duplicate stuff, just had to clarify for myself Wink
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nop_90
Global Moderator
Lifer
*****
Offline Offline

Posts: 2203


View Profile
« Reply #6 on: June 01, 2008, 10:06:29 PM »

@perks
just to explain how sqlobject works.

so if u look at the little example on the first page

Code:
sqlhub.processConnection = connectionForURI('sqlite:/:memory:')
class Person(SQLObject):
...     fname = StringCol()
...     mi = StringCol(length=1, default=None)
...     lname = StringCol()
...
Person.createTable()

The class Person appears to be a normal class.
But SQLObject is actually a meta-class, metaclass is like a C++ template, except on steriods.

behind the scenes, the code for class Person is created at runtime.
depending on what database driver u use, will depend on what code will be generated. (Ian Bickering is brilliant, one of the best examples of how to use metaclass)
objects do not actually get created till u access the database.

for attributes like lname, behind the scenes when u try an access p.lname it actually calls a function like p.get_lname or p.set_lname (python stole that from delphi Smiley)
u can override those setters/getters, so u can make ur own triggers in python.

When it comes to selecting etc, the python class generates the proper SQL code.

Quote
I wanted to expand that point just a bit... consider: you have two coders, each touching a DB. One chooses Python, another PHP - the logic between the two (as regards a particular type of transaction) MUST be identical to preserve the integrity of the data, but the programmers can really do things in two different ways. My point here is that the way we talk to a database from the outside is different than enforcing integrity from the inside. Clearly, stored procedures are far from a perfect tool, but they make a way for us to enforce logic at the DB level regardless of how we talk to it.
The 2 different languages touching the same database, with same logic probably is the only compelling reason.

Logged
dink
Expert
****
Offline Offline

Posts: 349


View Profile
« Reply #7 on: June 02, 2008, 12:55:14 AM »

Wow.

I'll have to read this a few times to digest all of it.

Thanks.

Wow.
Logged

[quote Nutballs]
the universe has a giant fist, and its got enough whoop ass for everyone.
[/quote]
dink
Expert
****
Offline Offline

Posts: 349


View Profile
« Reply #8 on: June 02, 2008, 11:48:23 PM »


Extrapolating this out a bit, you can see that there are lots of places that SPs might make sense, depending on the logic behind the action:
  • Many accounting functions
  • Many transaction functions, which may affect inventory, accounting etc
  • Customer profile stuff - adding a new address and making it the current one for example
  • Surfer tracking - particularly when one is watching spiders versus surfers
  • Customer list management - if a customer record changes, should they or should they not still be included in a particular list (like a eblasting list)

This goes on, but you get the idea. IMO, if a chunk of logic is explicitly and utterly about database machinations, particularly if it might be executed by more than one entity, I think it makes a good potential candidate for an SP.

Thanks Perk.  It does, indeed, help.

My first candidate (the datafeed import script) would seem to fit all but the 'execution by more than one entity' criteria.

A second candidate, a membership site I'm planning, looks promising.  The members would have different access depending on which level(s) they are authorized to use.

Lots of planning coming up.   Grin
Logged

[quote Nutballs]
the universe has a giant fist, and its got enough whoop ass for everyone.
[/quote]
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #9 on: June 03, 2008, 06:42:02 AM »

I am not sure about the functionality of mySQL, but with MSsql, you can use it like a magic black box. Since msSQL can handle XMl natively, you can move A LOT of the XML retrievals out of your code, and instead handle XML just like a normal database recordset (once you get it all formatted correctly).

so techincally, you could do "select ASIN from amazon where keyword='car seat'"
pretty cool if you ask me. I don't actually do this, like this example, but it was the easiest I could think of off the top of my head.

A secondary reason this is nice is because again, it create a separation of data and logic. PHP=logic, SQL=data (even if its remote data not even in SQL).

Triggers, which are stored procedures tied to an event, are way cool for splitting up your tasks into bite size chunks. Kind of like a class/object, which you dont even need to call. Personally, I don't use triggers much anymore, because I instead call the other SPs from the one that was called. The main reason is logic, which you can follow. Triggers are like magic voodoo crazy shit, that automatigically run when come other event happens. I don't like external hidden processes, they make me usually feel like there gremlins in the system.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #10 on: June 03, 2008, 09:04:02 AM »

Triggers are like magic voodoo crazy shit, that automatigically run when come other event happens. I don't like external hidden processes, they make me usually feel like there gremlins in the system.

I am surprised at that, Nuts, because they follow an event driven model so well. This is like a day at home for me - I love little things happening at the moment of importance, rather than having to instantiate bigger jobs on a cron. Of course, the logic of a trigger (for me) is limited explicitly to the code required at the moment and nothing more so I don't get myself into exactly the situation you're hinting at.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #11 on: June 03, 2008, 10:12:11 AM »

Don't get me wrong. the idea of a trigger is great. "something happens, do something else". But when I used to use them, I found a disconnect between what I was doing, and what was actually happening, which tended to make my brain hurt.

That being said...

Now that I think triggers are pretty well hammered out in MySQL, I will probably start using them again if i see a use for them.

But in the end, there is generally no real need for triggers in most databases. especially if all the data handling in in SPs. Triggers made more sense when you had Single Codebase model. Since as the database owner, you could enable other actions when an external program did anything to the database. Like, edit a row, trigger to save what application just did that, so you know who to blame. But when all communication is through SPs, there seems to be diminishing returns on the need/use of triggers.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #12 on: June 03, 2008, 12:22:15 PM »

But if I read you correctly, then you are asserting that:
   rather than allowing triggers to be the event driver AND
   since I am calling a Stored Procedure from what is essentially a single-user app
      (a web app, regardless of surfers, I have a single "user" calling things against the database) AND
   I *know* when something is happening because I called the stored procedure,
I can reduce complexity by calling the logic I want to happen within my procedures, correct?

That is a viable argument and needs a think.





OK. That works IF you adhere to not using direct SQL statements against your database. If you restrict yourself to access to features of the DB via the stored procs ONLY then you are in control of the logic, because you've channelled the access. If you anticipate that you or others will access/modify the data via direct SQL then you've got the same problem where all (entities touching the DB) MUST implement the exact same logic or the DB can become corrupt (or at least outside of integrity guidelines). In this case, triggers help because the logic is unavoidable by anyone touching the database. But, at the expense of complexity, which you aptly point out.

Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
dink
Expert
****
Offline Offline

Posts: 349


View Profile
« Reply #13 on: June 03, 2008, 12:25:31 PM »

...
A secondary reason this is nice is because again, it create a separation of data and logic. PHP=logic, SQL=data (even if its remote data not even in SQL).
...

"even if its remote data not even in SQL"  ??

Are you saying that the 'data' could be something other than sql?  Like, maybe, a code fragment, snippet, or a full-blown procedure?

"Remote" == not necessarily on the same server where the SP's reside?

This is getting cooler by the day.   Grin
Logged

[quote Nutballs]
the universe has a giant fist, and its got enough whoop ass for everyone.
[/quote]
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #14 on: June 03, 2008, 02:11:44 PM »

Yeah Dink. I have a trigger somewhere in a keyword scraping system I developed eons ago. I think anyway. I want it to hit my database first. If my database comes up empty for [keyword], then I want it to scrape a few places for the data. etc. etc.
Logged

hai
Pages: [1] 2
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!