MDBC Documentation


[Top] [Synopsis] [Description] [Parsemail] [CGI Scripts] [Database]

Synopsis:

This program consists of three major parts;


[Top] [Synopsis] [Description] [Parsemail] [CGI Scripts] [Database]

Description:

The purpose of this application is to provide a simple interface from which a user can search for, search through, sort, view, and otherwise manipulate email messages that have been thoroughly parsed, and inserted into a database.

Included are features such as:

  • Document Searching and Sorting by a number of fields:

  • (See mdbc.cgi for further information).
    • Assigned User

    • Search for all messages assigned to
      a specific user or order by user.
    • Assigned Status

    • Search for all messages assigned
      a specific status or order by status.
    • Read/Unread Messages

    • Search only unread messages vs.
      search all messages.
    • Parent/Child of thread

    • Search/List only messages that start
      a thread vs. all messages.
    • Ticket Number

    • Search for a specific ticket number
      thread, or order by ticket number.
    • Time Received

    • Search mail based on time received,
      or order by time received.
    • Time Opened

    • Search mail based on time opened,
      or order by time opened.
    • Message Number

    • Search for a specific messages by
      message number, or order by message
      number.
    • Sender

    • Search for all mail from a specific
      address, or order by address.
    • Subject

    • Search through the subject lines
      of messages. Only the parent will
      have the original subject, all replies
      will have the ticket number as subject.
      Also order by subject (alphabetically).
    • Body of Message

    • Search through the entire body
      of the message for desired keywords.
    • Full Mail Header

    • Search the full mail header for
      keywords, date/time stamps, etc.
  • Message Threading
    • View only messages that start a thread,

    • or only responses. Each message is listed with
      a link to view the rest of the thread.
  • User Assignment by Thread
  • Status Assignment by Thread
  • Detailed and Condensed Listings
    • Detailed listing provides full functionality

    • including; button to delete the current message,
      a report of how long the current message has been
      open, subject, date, ID, ticket number,
      view/reply button, status button, assign user
      button, children (thread) button, and registration
      info button. Each message listing is two lines plus
      buttons.
    • Condensed listing is used for significantly faster

    • and more efficient listings. It includes links (not
      buttons) for reply, ticket (thread), status,
      registration, and user, and also includes subject
      and date fields. Each message listing is one line.
  • Serial Number Verification
  • Manual and Automated Customer Registration
    • The program determines whether the sender

    • of each message listed is registered or not, and
      displays the registration info button/link as
      'Registered', or 'Unregistered'. Then 'Registered'
      link displays registration information, the
      'Unregistered' link displays a manual registration
      form.
    • The tech may also click the 'Check Serial Number'

    • button from the main form and verify an address or
      serial number, and/or manually register a user from there.
  • Full Monthly Backup/Restore/Delete Functionality
  • Logging of all significant database and mailing evens
  • Custom SQL Query via HTML Interface
    • Quick and easy access for directly querying the

    • database to get specific information that may not
      be directly accessible by the program itself.
      Note: There are no sanity checks on this function,
      please use with caution.

[Top] [Synopsis] [Description] [Parsemail] [CGI Scripts] [Database]

Parsemail:

The parsemail script is the one of the major components of the application. It's kicked off from cron(1) at a short interval (1 min by default) and checks for an existing mail spool for the support account. If none is found, it simply terminates. If the mail spool does exist it's moved to a temporary file in $PATH, then parsed as follows:
  1. The header and body are separated and assigned to variables
  2. Various fields from the header are also separated out
  3. The $subject is checked for a 7+ digit number, if one exists, it's assigned to $ticket, and the subject is set as "Ticket $ticket".
  4. If there is no number in $subject, one is extracted from the 'current.ticket' file, assigned to a variable, incremented, then written back to the file.
  5. It is determined whether the mail is bound for sales or support, and if it's to sales and doesn't have the subject "Registration Form" it's disregarded.
  6. If the subject is "Registration Form" all the registration info is parsed from the body of the message and entered into the reginfo table in the database. During this step, if it is determined that the serial number is bad, or is already registered, an alert email is sent out to the user specified in the mdbc.config file.
  7. If it's determined to be a new thread (no ticket in $subject) then an auto reply is issued. Registration status is also checked at this step, and if the sender isn't registered, they're notified of that fact in the generated response.
  8. If the message is not registration (and is addressed to support) some values are assigned depending on the circumstance of the message. If it's not the first in a thread the parent message is determined and status and user values are inherited from said parent.
  9. The message is then put through a routine to escape all illegal characters.
More detailed information can be found in the source of this script. A list of variables and their purpose (excluding those sourced in from mdbc.conf, which are listed in the CGI section, and simple counter variables - i.e., $i, $j, etc.) is as follows:
  • SPOOL is the file handle associated with the mail spool that is currently being parsed.
  • $dbh is the database handle object used to talk to mysql.
  • $ishead is true while parsing the header. A "\n" by itself on a line signifies the end of header, and the start of body. When "\n" is found $ishead is set to 0.
  • $isnew is used to determine if an email is the start of a thread, and whether an auto reply should be generated. If a ticket number is found in the $subject, $isnew is set to 0.
  • $_, for the non-perl-inclined, signifies the current line of input, or default pattern to match.
  • @header is the array which is assigned each line of the header.
  • $to is who the message was addressed to. This is used to check whether the message in question came in from the sales alias, or support, and if the former, to disregard unless $subject is "Registration Form".
  • $subject holds the original subject line if the message is new, or "Ticket $ticket", if a ticket number is found.
  • $ticket is either taken from the "Subject: " line, or is generated (imported from a file) if none is present.
  • $child is used to signify if a message is the start of a thread or not. In the latter part of the script, if $child is true, a query will be made to find and assign it's parent.
  • $date is taken from the "Date: " line, and is used for searching, and backup/restore/delete.
  • $sender is the address (only the user@host.com part) from the "From: " line. This is the address used in the auto reply.
  • @body contains the full message body text. If the message is determined to be a registration form, this array is parsed through a foreach, and all registration fields are assigned to variables.
  • $regmail tells us if the mail is registration or support, which determines which table it's inserted into, whether the auto reply is generated, etc.
  • $product, $sernum, $company, $first, $last, $add1, $add2, $city, $state, $postcode, $country, $tel, $email, $resell, $company2, $function, $howhear, $prods, $os, $birth, $notify, and $date, are all assigned from the registration form, and entered into the reginfo table in the database.
  • $checkresult holds the result of the serial number validation, which is used to make sure a valid serial number is being registered, and also that it is not duplicate.
  • $sth holds the results of a database query using the $dbh object. It is used and re-used for several queries.
  • $dupcheck holds the results of the duplicate registration check. The database is queried with the serial number in question, if calling fetchrow() on $dupcheck returns null, then the serial number was not found, and is original.
  • MAILER is the handle for the sendmail process we open to write our duplicate or invalid serial number messages. It is also used for the auto reply.
  • $is_regd is the result of a query made on each support mail before the auto reply is sent. If it comes up as false, the extra 'you're not registered' lines are added to the auto reply.
  • $user and $status are given default values, then, if the current message is determined to be a child in a thread, values for $user and $status are determined by their parent message.
  • $childof holds the message ID of the parent ticket, if the current ticket's value of $ischild is true.
  • $usr and stat are temporary variables for database queries looking for $user and $status.
$sender, $subject, @header, @body, $user, $status, $ticket, $msg_id, $date, $child and $childof, and several default values and timestamps, are inserted into the mail database.

[Top] [Synopsis] [Description] [Parsemail] [CGI Scripts] [Database]

CGI Scripts:

The CGI scripts provide all the actions that take place upon the data that has been parsed into the database. Descriptions of the purpose(s) and function(s) of each follow:

backup.cgi

This CGI has 3 input fields. The first is a drop box determining what sort of action to take. Valid choices are; Backup, Restore, and Delete. The second and third fields indicate the month and year to perform the action on. backup.cgi is called by mdbc.cgi.

Backup will create a backup file called supmail.- in mysql's home dir (by default, /var/lib/mysql/supmail). This location can be easily changed by adding a path in front of the string "\'supmail.$month-$year\'" currently on line 34 of backup.cgi, or adding a variable (such as $BAKPATH), then adding and defining said variable in the mdbc.config file. The backup function will create a file even if no records for said month/year are found. Once a backup file is written it will not overwrite, the file must be deleted by hand. Backing up deletes all entries that are backed up.

Restore will restore any existing backup file specified by the month and year input fields. It currently does not overwrite existing entries. Trying to restore a file that does not exist will result in a mysql error. When a file is restored the backup file does not change or get deleted.

Delete is used to remove restored data from the database. Since restoring doesn't delete the backup file, and it cannot be overwritten, The best option is to restore, then delete. This is also useful if, for whatever reason, you simply want to clear large amounts of data from the database without backing it up.

del.cgi

This script is called by the 'Delete' button on the extended output listing. It's only function is to delete the current message where the button appears. del.cgi is called by sqlquery.cgi.

moduser.cgi

This script adds and removes users from the user table in the database. This table holds all users that can be assigned to a message (thread).

Make note that when searching by user only users that are in the user table AND are currently assigned to a message will be listed. Reload the page to update this list. moduser.cgi is called from mdbc.cgi.

reginfo.cgi

This is the script that is called when you click on the 'Registered' link, or button. It accesses the mail database to verify sender, then accesses the reginfo database and collects all registration information associated with said sender and displays it nicely in a little pop-up window. reginfo.cgi is called by sqlquery.cgi.

reply.cgi

This script is called by clicking on the 'View/Reply' button on the extended listing, or the email address (first) link on the condensed listing. It brings up a new window containing the message and basic header information, and give the option to reply to the message.

It also updates the 'repliedto' (a bit of a misnomer) value of the message so that it no longer appears in a 'unread only' search. Setting this value when the message is actually replied to would be tricky at best, since the reply button calls the netscape mailer and control is passed on to it.

Quoting of the message is done in the parsemail script, not here. This script does, however do some url encoding magic in order to pass the entire message body through to mailto: tag. reply.cgi is called by sqlquery.cgi.

scheck.cgi

This script is called by the 'Check Serial Number' box on the main page, and also by clicking the 'Unregistered' link or button from the list page. It takes a serial number, validates it, and if it's valid, prompts the user to input registration information. That information is then inserted into the reginfo table just as if it had been received via email registration form. scheck.cgi is called by sqlquery.cgi.

sqlquery.cgi

This script takes a series of values submitted from the form on the main page and generates an sql query from that criteria. The better part of the first half of this script is devoted to the sql query generating engine. The second half takes the results of that query and parses and formats the results into the list pages for either standard, or condensed. The script calls itself with a specific flag in order to generate view the 'children' threaded listing.

The links and buttons generated by this script are responsible for calling most of the small scripts in the program to perform actions on the selected message(s). For easier debugging and reference, a copy of the full generated sql query is placed in the comments of the source html code generated from this script. sqlquery.cgi is called from mdbc.cgi.

status.cgi

This script is called from the message listing status button or link, and simply takes the ticket id of the current ticket, prompts the user with a dialog box, then changes the status of the current thread (not ticket) to the user specified status. status.cgi is called by sqlquery.cgi

user.cgi

This script is called by the 'assign user' button, or the user link. It prompts the user to select a user from the current user table (modifiable with moduser.cgi) and assigns it to the current thread (not ticket). Only users that have been assigned by this mechanism will be selectable from the main form's search 'by user' drop box.

Also note that by assigning a user the status of the current thread (not ticket) is changed to 'open'. user.cgi is called by sqlquery.cgi

mdbc.cgi

This script generates the main selection form. This form is responsible for collecting all the values needed to create an appropriate sql query. For an 'open query', just hit submit with no option selected. This will retrieve all messages from the database (with the exception that the default 'view' setting is 'unread' - set this to 'all messages' for a full listing).

The 'custom query' box is used mostly for debugging, but can also prove useful for very specific quick lookups that aren't easily accomplished by the standard interface (select header from mail where ...., for instance, can show you full header information that is not available from the predefined screens.

The 'View', 'Search by Status', Select User to Search on' and 'Order by' drop boxes should be fairly self explanatory. The next set of entry fields, "Search by:", "and...", "and...", are simply there to help if you're trying to find a specific message or set of messages. For instance, if the tech notices a trend in a fairly specific bug in one set of graphics cards, they could select search by 'message body', then, in the 'for' dialog, enter the name of said graphics card, and the engine would then display all messages containing that string in the message body. The second and third fields are available to narrow the search even further by adding more criteria. For instance, search by 'message body' for 'Rage 3D', and 'subject' for 'lock up', and 'misc. header' for 'Oct.' (or, for the latter, 'time received' will also work, but is stored in timestamp format, and could, in theory, have expressions run against it, the 'misc. header' field contains the full mail header for searching).

mdbc.cgi is the initiating script of the program and could be accessed from a link on any html page if desired, or called directly.

mdbc.config

This is the program's main (global) configuration file. Any values in any one of the scripts (including parsemail) can be added here and are sourced into each script at runtime. See file for descriptions of the current configurable settings.

[Top] [Synopsis] [Description] [Parsemail] [CGI Scripts] [Database]

The Database:

The database used by MDBC contains 3 tables.

Mail

Database: supmail  Table: mail
+-----------+--------------+
| Field     | Type         |
+-----------+--------------+
| sender    | varchar(200) | "From: " address.
| Subject   | varchar(200) | "Subject: " line.
| header    | blob         | Full text of header.
| body      | blob         | Full text of body.
| status    | varchar(6)   | Status of message.
| user      | varchar(20)  | Currently assigned User.
| otime     | datetime     | Time Message was Ppened.
| id        | int(6)       | Individual Message ID.
| child     | int(1)       | False if message is parent.
| childof   | int(6)       | If child == 1, ID of parent.
| ticket    | int(8)       | Ticket Number.
| stime     | datetime     | Time mail was inserted into db.
| ctime     | datetime     | Time of last Change (unused).
| mtime     | varchar(50)  | Time grepped from "Date: ". 
| repliedto | int(11)      | True if message has been read.
| reg       | varchar(20)  | Registration (unused).
+-----------+--------------+

Users

Database: supmail  Table: users
+--------+----------+
| Field  | Type     |
+--------+----------+
| people | char(20) | Names of users available to assign to
+--------+----------+ messages.

RegInfo*

Database: supmail  Table: reginfo
+-----------+--------------+
| Field     | Type         |
+-----------+--------------+
| product   | varchar(20)  | Registered Product
| sernum    | varchar(22)  | Serial Number
| company   | varchar(100) | Company
| first     | varchar(20)  | First Name
| last      | varchar(20)  | Last Name
| add1      | varchar(50)  | Address Line 1
| add2      | varchar(50)  | Address Line 2
| city      | varchar(30)  | City
| state     | varchar(30)  | State
| postecode | varchar(20)  | Post/Zipcode
| country   | varchar(50)  | Country
| tel       | varchar(20)  | Phone
| email     | varchar(100) | Registered Email Address
| resell    | varchar(100) | Reseller Purchased From
| company2  | varchar(60)  | Type of Company
| function  | varchar(100) | Job Function
| howhear   | varchar(100) | Became Aware of Product How
| prods     | varchar(100) | (no idea)
| os        | varchar(30)  | OS Product was Purchased For
| notify    | char(3)      | Notify of Updates/New Products
| birth     | varchar(20)  | Place of Birth
| date      | varchar(20)  | Current Date
+-----------+--------------+
* Most field names were taken verbatim from the cgi 
registration form. I can't gaurentee the accuracy of 
each field's definition since I didn't write said form.

 

[Top] [Synopsis] [Description] [Parsemail] [CGI Scripts] [Database]
Support by R01.us