Subject: seeking input on developing mysql based driver (message store)

From: Bill Schoolfield <[email-masked]>
Date: Wed, 15 Jan 2003 13:01:46 -0600

Hi everyone,

I'm looking into the possibility of integrating UW's imapd server with
our Billing and Customer Care system known as BillMax. More
specifically, I want to replace our existing trouble ticket with imapd
using a MySQL database and our schema as the message store.

My thought is to:

1) Write a driver based on mbx that uses our database as the message
store.
2) Modify imapd to examine incoming email to do the lookups for the
purpose of determining where to store (in the schema) the message. In
the case of a new ticket, a new ticket number would be assigned and the
stored message header would be modified in some way (probably encoded in
the subject line) to contain this ticket number. A acknowledgment email
would then be sent to the originator of the incoming email. The ticket
number would be used thereafter to append the incoming correspondence to
the appropriate folder. There will be a one to one correspondence
between a particular ticket and a folder. Ticket/folders will belong to
a folder that's either the originators email address or the date the
ticket was received. Since the messages are stored in the database, the
parent folder is virtual (e.g. just a column's value in the table where
the messages are stored.)
3) In our application, when a ticket is closed, have some way of hiding
or un-subscribing the ticket/folder from the imap client so that the
clients view doesn't become unmanageable. How can this be modeled
w/existing imap protocol?
4) The trouble tickets would be managed in the call center using
standard imap clients. Status changes and the like would be done via our
application.

There's quite a bit more functionality but the above covers most of
what's needed in imapd. My questions are: Has anybody done this? (if so,
would you provide example code?) Can you see any pitfalls in this? What
about file locking, is that even a consideration for the new driver? How
extensive are the required changes?

Thanks in advance for you comments.

Bill

--

The iSpark Group, Inc.
dba BillMax Billing Solutions
Suite 210
6737 Brentwood Stair Rd.
Fort Worth TX 76112

817.446.7776 (Phone)
817.446.7773 (Fax)

-- 
------------------------------------------------------------------
 For information about this mailing list, and its archives, see: 
 http://www.washington.edu/imap/c-client-list.html
------------------------------------------------------------------

---

From: Mark Crispin <[email-masked]>
Date: Wed, 15 Jan 2003 18:54:01 -0800 (Pacific Standard Time)

A number of people have talking about using a MySQL database with imapd.
It's pretty easy to do a first order implementation, but more advanced
implementations (such as implementing a msgdata method) are harder.

File locking is an issue only if your driver makes it an issue.
Basically, you have to consider "what happens if some other process does
something to the mailbox while I am doing something with it?"  That, in
turn, leads you to if you need locking, and what type of locking you need.

As for hiding the ticket, I would recommend using some keyword flag, such
as "CLOSED", to mark the message as being closed.  This assumes that your
client knows about keyword flags.

-- Mark --

http://staff.washington.edu/mrc
Science does not emerge from voting, party politics, or public debate.