Re: [PHP-DB] MySQL circular buffer

[OKi98]

> (i.e. stack_id > 500 && stack_id < 601 vs where stack_id = 500 limit 100)
stack_id between 501 and 600 (stack_id > 500 && stack_id < 601) is much 
better




What I would like to know is if anybody has experience
implementing this sort of data structure in MySQL (linked list?) or
any advice.
  

tables:
process_table:
   IDProcess PK

mail_table
   IDMail PK
   IDPrrocess FK references process_table.IDProcess ON DELETE SET NULL
   Mail TEXT
   From VCH(255)
   TO VCH(255)
   DateModified DATE ON UPDATE CURRENT_TIMESTAMP
   Mailed TINYINT DEFAULT 0;
--
code:
define('MaxProccessTimeMinutes', 30);
define('BatchCount', 100);

INSERT INTO process_table VALUES ();
$lnIdProcess = GetLastIDProcess();

label send_mail:
$ldDateExpired = time() - MaxProccessTimeMinutes * 60;
UPDATE mail_table
   SET IDProcess = $lnIdProcess
   WHERE
  Mailed = 0 AND
  (
   IDProcess IS NULL OR
  (
   IDProcess IS NOT NULL AND
   DateModified <= $ldDateExpired
  );
  )
   LIMIT BatchCount;
while ($result = SELECT IDMail, MailText, From, To FROM mail_table WHERE 
IDProcess = $lnIDProcess)

{
   if (send_mail())
   {
   UPDATE mail_table SET Mailed = 1 WHERE IDMail = $result['IDMail'];
   }
}
if there are other mails goto send_mail;
else DELETE FROM process_table WHERE IDProcess = $lnIDProcess;

this way more than one process can send mails, also if one process exits 
prematurely the other can send his emails later.

Time to time run cron:
   DELETE FROM mail_table WHERE Mailed = 1;
   rebuild indexes on mail_table;

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] MySQL circular buffer

[Andrew Martin]

Hello,

I'm looking at implementing a database-level stack for a (multi stack)
mail merge queue (able to queue up to 1 million messages per stack).
Current workflow:

server 1 (containing main db) transmits data sets (used to populate
mail merge template) to server 2
server 2 web-facing script (script 1) puts data sets onto stack (db)
server 2 mail process script (script 2) pulls single data block (say
100 rows) from front of stack (fifo), merges the data with the
template and sends data to smtp process
server 2 script 2 removes "processed" block of rows

The problems I am considering include keeping track of the value of
the primary key across multiple instances of script 2 (the script will
run from a cron), whether to select by limit or range (i.e. stack_id >
500 && stack_id < 601 vs where stack_id = 500 limit 100) and looping
the index back to zero while ensuring there is no data that hasn't
been deleted.

So - it seems easier to avoid these problems and implement a circular
buffer :) What I would like to know is if anybody has experience
implementing this sort of data structure in MySQL (linked list?) or
any advice.

There don't seem to be any current implementations so the last
question is - is there a good reason for that? Too many overheads? I
know this sort of structure is best kept in memory and not on disk,
but I am not sure of any other solution to a queue this size.

Any comments welcome. Many thanks,


Andy

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php