#!/usr/bin/python2.5
import re
import MySQLdb 

connection = MySQLdb.connect("server(probably localhost)","user","password","your_database")
logFile = open('/path/to/asterisk/messages','r') #probably /var/log/

#dictionary to convert the month in the log file to something MySQL will understand
month_to_number = {'jan':'01','feb':'02','mar':'03','apr':'04','may':'05','jun':'06','jul':'07','aug':'08','sep':'09','oct':'10','nov':'11','dec':'12'}

for line in logFile.readlines():

  #our log file had some lines that had 'Input:' or '^' and that was it. This is hacky but should get the job done for ignoring those lines.
  if(len(line)>10): 
    #get the month of the message
    pattern = re.compile('\[?[A-Z][a-z][a-z]')
    month_pattern = pattern.match(line)
    month = month_to_number[month_pattern.string[month_pattern.start():month_pattern.end()].replace(' ','').replace('[','').lower()]

    #get the date of the message
    pattern = re.compile('\s\s?\d\d?')
    date_pattern = pattern.search(line)
    date = date_pattern.string[date_pattern.start():date_pattern.end()].replace(' ','')

    #get the time of the message
    pattern = re.compile('\d\d\:\d\d\:\d\d')
    time_pattern = pattern.search(line)
    time = time_pattern.string[time_pattern.start():time_pattern.end()]
  
    #get the message type e.g. ERROR, WARNING, NOTICE, ect
    pattern = re.compile('[A-Z]+\[')
    message_type_pattern = pattern.search(line)
    message_type = message_type_pattern.string[message_type_pattern.start():message_type_pattern.end()].replace('[','')

    #get the file the message is from
    pattern = re.compile('\w+\.(c|fl|y)')
    file_pattern = pattern.search(line)
    file_name = ''

    #I don't think I need this line anymore as I think the regex above will catch all the different file extensions now
    """
    if (file_pattern):
      file_name = file_pattern.string[file_pattern.start():file_pattern.end()]
    else:
      pattern = re.compile('\w+\.fl')
      file_pattern = pattern.search(line)
      file_name = file_pattern.string[file_pattern.start():file_pattern.end()]
    """

    #get the message
    pattern = re.compile('\.(c|fl|y):\s.+$')
    message_pattern = pattern.search(line)
    message = message_pattern.string[message_pattern.start():message_pattern.end()].replace('.(c|fl|y): ','').replace('?','').replace('!','')

    pattern = re.compile('Registration.+timed\sout')
    registration_pattern = pattern.search(line)

    #this variable will only really be used when our connection to our voip provider was lost, otherwise insert and empty string.
    attempt_number=''

    if(registration_pattern):
      #get the attempt number for registration to Digital Voice
      pattern = re.compile('#\d+')
      attempt_pattern = pattern.search(line)
      attempt_number = attempt_pattern.string[attempt_pattern.start():attempt_pattern.end()].replace('#','')

    #for some reason Asterisk does not log the year of the message, so we have to do this by hand...
    datetime = '2009-%s-%s %s' % (month,date,time) #construct our MySQL datetime string

    #print statement for debugging. I recommend using this before you actually insert into the database.
    #print ("date:%s message_type:%s file:%s message:%s attempt:%s") % (datetime,message_type,file_name,message,attempt_number)

    cursor = connection.cursor()
    cursor.execute("""INSERT INTO message (date,message_type,file,message,attempt_number)
                   VALUES (%s, %s, %s, %s, %s)""",
                   [datetime,message_type,file_name,message,attempt_number] )

#close our connection to the database
logFile.close()