next »

[thedarkness]

Just finished these (I think), thought they might make reasonable examples for those brave souls who follow.

Code:

# newclick
# CALL newclick( @nextID );
# SELECT @nextID;

DROP procedure if exists newclick;
delimiter //

CREATE PROCEDURE newclick(OUT nextID INT(11))
BEGIN
INSERT INTO click (destinationID,keywordID) VALUES (0,0);
SELECT Last_insert_id() INTO nextID;
END //
delimiter ;

# storeclickandkw
# CALL storeclickandkw( @campaignID, @destinationID, @clickID, @keyword );

DROP procedure if exists storeclickandkw;
delimiter //

CREATE PROCEDURE storeclickandkw(IN i_campaignID INT(11), IN i_destinationID INT(11), IN i_clickID INT(11),
                                 IN i_keyword VARCHAR(255))
BEGIN
  DECLARE _keyword_exists, _keyword_id int;

  SELECT COUNT(keywordID), keywordID INTO _keyword_exists, _keyword_id
      FROM keyword WHERE keyword = i_keyword AND campaignID = i_campaignID GROUP BY keywordID LIMIT 1;
  IF _keyword_exists = 0 OR _keyword_exists IS NULL THEN
    INSERT INTO keyword (campaignID, keyword) VALUES (i_campaignID,i_keyword);
    SELECT last_insert_id() INTO _keyword_id;
  END IF;

  # Update details for the click now that we have them (click created and clickID allocated in
  # main() accept loop)
  UPDATE click SET destinationID = i_destinationID, keywordID = _keyword_id WHERE clickID = i_clickID;

END //
delimiter ;

Cheers,
td

[thedarkness]

Hahahaha, I just realised the server where this stuff has to run is MySQl 4.x and doesn't support stored procedures... LOL..... my life sucks!.... it's just a big pile of shit!

Time to translate those SPs back to functions within my program.

meh :-P

Cheers,
td

[perkiset]

Bummage.

Couple thoughts on your SPs in the future with phpMyIDE -

• You don't need to DROP IF EXISTS - I do all that for you with the way that I store procs/functions/triggers etc
• You don't need to DELIMITER - I do all that for you as well. You can just look at embedded code as if it is a standalone thang
• I notice that you specify IN on params for storeclickandkw - that's the default for all params, so you don't have to do it

But all that said, thanks much for giving time in a fruitless effort to make MySQL 4.0 do what it can't 

[perkiset]

I think that your effort here is a good thing TD - since many have not even seen SP code or what it looks like, some simple examples here are probably a really good thing.

Here is a script that is called once a minute via a cron job that clears session records from a sessions table into my site statistics table. (I keep sessions in the database with a custom session handler rather than the default PHP session handlers). This is literally a cut and paste from the IDE. See how we can make the code look much more familiar and friendly, which to a non-SQL coder this is pretty durn important:

PROCEDURE expire_Sessions()
BEGIN

   declare thisID varchar(64);
   declare thisSiteID integer;
   declare thisDuration integer;
   declare thisPageCount integer;
   declare keepGoing integer;
   declare cutoff char(18);
   declare theDate char(10);
   declare dummy integer;
   declare sites cursor for select id from list_sites where active=1;
   declare continue handler for not found set keepGoing = 0;
   declare continue handler for 1062 set dummy = 1;

   set theDate = now();

   -- get this out of the way: inserting will only work if 
   -- it does not already exist because of the PK on the table:
   set keepGoing = 1;
   open sites;
   while keepGoing = 1 do
      fetch sites into thisID;
      insert into track_sitestats(site_id, datestamp) values(thisID, theDate);
   end while;
   close sites;
   

   -- note that cutoff is only a char(18), so the value created
   -- here will be only the date, a space and the hour (the hourBlock)
   set cutoff = date_sub(now(), interval 30 minute);


   set keepGoing = 1;
   select id, siteid, duration, pagecount into thisID, thisSiteID, thisDuration, thisPageCount 
   from sessions where lasttouch < cutoff limit 1;


   -- I won't even start here if the last query found nothing
   while keepGoing = 1 do
      update track_sitestats set 
         total_visitors=total_visitors+1, 
         total_pages=total_pages+thisPageCount, 
         total_duration=total_duration+thisDuration
      where site_id=thisSiteID and datestamp=theDate;

      delete from sessions where id=thisID;

      select id, siteid, duration, pagecount into thisID, thisSiteID, thisDuration, thisPageCount 
      from sessions where lasttouch < cutoff limit 1;
   end while;


   -- Now that all sessions have been expired, recalculate
   -- the averages for each site in the list_sites table
   set keepGoing = 1;
   open sites;
   while keepGoing = 1 do
      fetch sites into thisID;
      update track_sitestats set
         lupdate=now(),
         average_pages=(total_pages DIV total_visitors),
         average_duration=(total_duration DIV total_visitors)
         where site_id=thisID and datestamp=theDate;
   end while;
   close sites;

END

[thedarkness]

Your example is much bigger than mine  Wicked as usual perk.

Bummage.

You ain't just whistling Dixie buster.

Couple thoughts on your SPs in the future with phpMyIDE -

• You don't need to DROP IF EXISTS - I do all that for you with the way that I store procs/functions/triggers etc
• You don't need to DELIMITER - I do all that for you as well. You can just look at embedded code as if it is a standalone thang
• I notice that you specify IN on params for storeclickandkw - that's the default for all params, so you don't have to do it

Message received and understood. You caught me out, these were of course crafted outside of the IDE sue to some problems I'm having which are prolly trivial but i don't have time to address at the mo. Will advise on this.

But all that said, thanks much for giving time in a fruitless effort to make MySQL 4.0 do what it can't 

Tilting at windmills is my main gig  Should be upgrading that box soon so I will get my SPs eventually.

Cheers,
td

[thedarkness]

perk,

Can you explain the use of "keepGoing" in your SP? It's messing with my head man! Your use of cursors is making me think to, maybe you could describe them in lay terms. I grok them though....... I think??

Take care of that enormous brain of yours.

Cheers,
td

[perkiset]

No worries man.

BEAR IN MIND that I am pretty novice still to the SPs and such... in fact, that's the 3rd one I wrote. I built phpMyIDE so that I could learn how to do the things. The honest truth is that my understanding is purely theoretical, me having obtained such theories from hanging around the likes of truly smart people like the DBDog.

OK stop laughing ya bastard.

Anyhoo, there's a problem with the way that the SQL language handles errors - it's *kind of like* an exception handler, but not really. What you do is declare a continue handler in the beginning of the block - you can see them here:

   declare continue handler for not found set keepGoing = 0;
   declare continue handler for 1062 set dummy = 1;

These two are arguable the most popular - 1062 is a Duplicate Key error and "not found" is, well, when a select does not find either one or anymore of <what you are selecting>. Essentially you are saying "execute this single statement when an error of (x) type occurs.

So the way that I use it is to handle my while loop. I set keepGoing to 1, then do a query - then do a while loop while keepGoing is equal to 1... meaning that if I didn't find one on the first query I'll never enter the loop. Then the last statement of the loop is to do the select again, which will either produce a record or set keepGoing to 0, in which case I bomb out. Here again:

   set keepGoing = 1;
   select id, siteid, duration, pagecount into thisID, thisSiteID, thisDuration, thisPageCount 
   from sessions where lasttouch < cutoff limit 1;

   -- I won't even start here if the last query found nothing
   while keepGoing = 1 do
      update track_sitestats set 
         total_visitors=total_visitors+1, 
         total_pages=total_pages+thisPageCount, 
         total_duration=total_duration+thisDuration
      where site_id=thisSiteID and datestamp=theDate;

      delete from sessions where id=thisID;

      select id, siteid, duration, pagecount into thisID, thisSiteID, thisDuration, thisPageCount 
      from sessions where lasttouch < cutoff limit 1;
   end while;

Set dummy=0 is my novice way of saying "ignore the error" - dummy has no role at all except to get set when I see a duplicate key. When that happens, I set dummy to 1 which does... wait for it... nothing. Essentially I'm blindly inserting a record where I know that it's either going to work or bomb on a duplicate PKey error - I don't really care, since I just go on in the next statement and get the ID of the record I'm looking for.

Hope that helps... it's not particularly pretty, that much is certain. However, I now have a bunch of stored procedures working in my new framework and I am REALLY REALLY REALLY pleased about the stability and performance of everything. I also have a bunch of views that I used from the command line and I'm starting to seriously exercise the IDE. I think that time learning this stuff and re-thinking the overall architecture of what a lot of us do is a good idea. An *awful* lot of what we do is data-bound or data-only mechanics - it seems only logical to go right to the source and perform logic.

Cheers,
/p

[dink]

Damn.  Almost makes me feel smart just hanging around here.

[thedarkness]

Set dummy=0 is my novice way of saying "ignore the error" - dummy has no role at all except to get set when I see a duplicate key. When that happens, I set dummy to 1 which does... wait for it... nothing. Essentially I'm blindly inserting a record where I know that it's either going to work or bomb on a duplicate PKey error - I don't really care, since I just go on in the next statement and get the ID of the record I'm looking for.

You could use replace or ignore to accomplish the same thing right, although prolly not with the same level of control?

Hope that helps... it's not particularly pretty, that much is certain. However, I now have a bunch of stored procedures working in my new framework and I am REALLY REALLY REALLY pleased about the stability and performance of everything. I also have a bunch of views that I used from the command line and I'm starting to seriously exercise the IDE. I think that time learning this stuff and re-thinking the overall architecture of what a lot of us do is a good idea. An *awful* lot of what we do is data-bound or data-only mechanics - it seems only logical to go right to the source and perform logic.

Agree 100%, only reason I'm hanging around here 

Cheers,
td