Metadata by Message-ID

[mikedibella]

I get a callback POST from SendGrid for delivery events. The data provided by SendGrid is incomplete notify the message originator, so I need a way to look up the message in hMailServer metadata. I do get the Message-ID of the message in the POST. I don't see a method in the API to lookup a message globally by Message-ID.

Could I get this data from the hMailServer database? How long after a message is delivered (as far as HMS is concerned) would this data persist in the HMS DB?

Another way I could handle this is with a script procedure to write records to a table keyed on Message-ID and consisting of the other metadata I need to generate an NDR (i.e. originator).

Any ideas?

[SorenR]

I get a callback POST from SendGrid for delivery events. The data provided by SendGrid is incomplete notify the message originator, so I need a way to look up the message in hMailServer metadata. I do get the Message-ID of the message in the POST. I don't see a method in the API to lookup a message globally by Message-ID.

Could I get this data from the hMailServer database? How long after a message is delivered (as far as HMS is concerned) would this data persist in the HMS DB?

Another way I could handle this is with a script procedure to write records to a table keyed on Message-ID and consisting of the other metadata I need to generate an NDR (i.e. originator).

Any ideas?

The metadata table only hold emails stored in IMAP folders so if you don't store sent mail it won't be there.

Making a sub to store outgoing metadata in a custom table is simple if you can accept a new table in the HM database.

Code: Select all

Function GetDatabaseObject()
    Dim oApp : Set oApp = CreateObject("hMailServer.Application")
    Call oApp.Authenticate(ADMIN, PASSWD)
    Set GetDatabaseObject = oApp.Database
    Set oApp = Nothing
End Function

Function MyMetaData(oMessage)
    Dim strSQL, oDB : Set oDB = GetDatabaseObject
    strSQL = "INSERT INTO my_metadata (timestamp,key,data1,data2) VALUES (NOW(),'bla', 'bla', 'bla') ON DUPLICATE KEY UPDATE timestamp=NOW();"
    Call oDB.ExecuteSQL(strSQL)
    Set oDB = Nothing
End Function

NB ! Using hMailServer DB API is WRITE-ONLY !

AND... you need to build the table to match your finished script

[palinka]

I get a callback POST from SendGrid for delivery events. The data provided by SendGrid is incomplete notify the message originator, so I need a way to look up the message in hMailServer metadata. I do get the Message-ID of the message in the POST. I don't see a method in the API to lookup a message globally by Message-ID.

Could I get this data from the hMailServer database? How long after a message is delivered (as far as HMS is concerned) would this data persist in the HMS DB?

Another way I could handle this is with a script procedure to write records to a table keyed on Message-ID and consisting of the other metadata I need to generate an NDR (i.e. originator).

Any ideas?

The metadata table only hold emails stored in IMAP folders so if you don't store sent mail it won't be there.

Making a sub to store outgoing metadata in a custom table is simple if you can accept a new table in the HM database.

Code: Select all

Function GetDatabaseObject()
    Dim oApp : Set oApp = CreateObject("hMailServer.Application")
    Call oApp.Authenticate(ADMIN, PASSWD)
    Set GetDatabaseObject = oApp.Database
    Set oApp = Nothing
End Function

Function MyMetaData(oMessage)
    Dim strSQL, oDB : Set oDB = GetDatabaseObject
    strSQL = "INSERT INTO my_metadata (timestamp,key,data1,data2) VALUES (NOW(),'bla', 'bla', 'bla') ON DUPLICATE KEY UPDATE timestamp=NOW();"
    Call oDB.ExecuteSQL(strSQL)
    Set oDB = Nothing
End Function

NB ! Using hMailServer DB API is WRITE-ONLY !

AND... you need to build the table to match your finished script

Why not just look up the metadata_from in hm_message_metadata?

Code: Select all

Sub LookupSender(ByVal m_ID, ByRef m_Sender)
	Dim oRecord, oConn : Set oConn = CreateObject("ADODB.Connection")
	oConn.Open "Driver={MariaDB ODBC 3.1 Driver}; Server=localhost; Database=hmailserver; User=hmailserver; Password=secretpassword;"

	If oConn.State <> 1 Then
		EventLog.Write( "Sub LookupSender - ERROR: Could not connect to database" )
		m_Sender = ""
		Exit Sub
	End If

	Set oRecord = oConn.Execute("SELECT metadata_from FROM hm_message_metadata WHERE metadata_messageid = '" & m_ID & "';")
	Do Until oRecord.EOF
		m_Sender = oRecord("metadata_from")
		oRecord.MoveNext
	Loop
	oConn.Close
	Set oRecord = Nothing
End Sub

[SorenR]

I get a callback POST from SendGrid for delivery events. The data provided by SendGrid is incomplete notify the message originator, so I need a way to look up the message in hMailServer metadata. I do get the Message-ID of the message in the POST. I don't see a method in the API to lookup a message globally by Message-ID.

Could I get this data from the hMailServer database? How long after a message is delivered (as far as HMS is concerned) would this data persist in the HMS DB?

Another way I could handle this is with a script procedure to write records to a table keyed on Message-ID and consisting of the other metadata I need to generate an NDR (i.e. originator).

Any ideas?

The metadata table only hold emails stored in IMAP folders so if you don't store sent mail it won't be there.

Making a sub to store outgoing metadata in a custom table is simple if you can accept a new table in the HM database.

Code: Select all

Function GetDatabaseObject()
    Dim oApp : Set oApp = CreateObject("hMailServer.Application")
    Call oApp.Authenticate(ADMIN, PASSWD)
    Set GetDatabaseObject = oApp.Database
    Set oApp = Nothing
End Function

Function MyMetaData(oMessage)
    Dim strSQL, oDB : Set oDB = GetDatabaseObject
    strSQL = "INSERT INTO my_metadata (timestamp,key,data1,data2) VALUES (NOW(),'bla', 'bla', 'bla') ON DUPLICATE KEY UPDATE timestamp=NOW();"
    Call oDB.ExecuteSQL(strSQL)
    Set oDB = Nothing
End Function

NB ! Using hMailServer DB API is WRITE-ONLY !

AND... you need to build the table to match your finished script

Why not just look up the metadata_from in hm_message_metadata?

Code: Select all

Sub LookupSender(ByVal m_ID, ByRef m_Sender)
	Dim oRecord, oConn : Set oConn = CreateObject("ADODB.Connection")
	oConn.Open "Driver={MariaDB ODBC 3.1 Driver}; Server=localhost; Database=hmailserver; User=hmailserver; Password=secretpassword;"

	If oConn.State <> 1 Then
		EventLog.Write( "Sub LookupSender - ERROR: Could not connect to database" )
		m_Sender = ""
		Exit Sub
	End If

	Set oRecord = oConn.Execute("SELECT metadata_from FROM hm_message_metadata WHERE metadata_messageid = '" & m_ID & "';")
	Do Until oRecord.EOF
		m_Sender = oRecord("metadata_from")
		oRecord.MoveNext
	Loop
	oConn.Close
	Set oRecord = Nothing
End Sub

[palinka]

The metadata table only hold emails stored in IMAP folders so if you don't store sent mail it won't be there.

I suppose this depends on the userbase. Do real people use POP3 anymore?

But I definitely get your point.

[SorenR]

The metadata table only hold emails stored in IMAP folders so if you don't store sent mail it won't be there.

I suppose this depends on the userbase. Do real people use POP3 anymore?

But I definitely get your point.

No you don't.

If you use SENDGRID for outbound messages then you are sending bulk. That makes a LOT of messages to save in your Send Mail folder

[mikedibella]

I only need ephemeral storage for those messages that don't get relayed by SendGrid on the first attempt due to transient failure. All other messages will be cleared immediately via either a delivered notification, or a bounce notification. Those that are simply delayed are the messages I want to notify the originator.

So I'm going to just use the registry:

Code: Select all

Sub NewPendingDelivery(oMessage)
	const REGKEY = "HKEY_LOCAL_MACHINE\SOFTWARE\hMailServer\Pending"
	set oShell = CreateObject("WScript.Shell")
	' new key
	sNewKey = REGKEY & "\" & oMessage.HeaderValue("Message-ID")
	oShell.RegWrite sNewKey, ""
	' write metadata to key
	oShell.RegWrite sNewKey & "\From", oMessage.HeaderValue("From"), "REG_SZ"
	oShell.RegWrite sNewKey & "\Date", oMessage.HeaderValue("Date"), "REG_SZ"
	oShell.RegWrite sNewKey & "\To", oMessage.HeaderValue("To"), "REG_SZ"
	oShell.RegWrite sNewKey & "\Subject", oMessage.HeaderValue("Subject"), "REG_SZ"
	set oShell = nothing
End Sub

In my POST handler, one of three actions happens for these temporary registry entries:

1. A delivery notification event deletes the key.
2. A bounce notification event deletes the key (SendGrid generates an NDR for these and HMS relays it to the originator).
3. A delayed notification event uses this metadata to create a delay notification to the originator and sends it via HMS.

[SorenR]

I only need ephemeral storage for those messages that don't get relayed by SendGrid on the first attempt due to transient failure. All other messages will be cleared immediately via either a delivered notification, or a bounce notification. Those that are simply delayed are the messages I want to notify the originator.

So I'm going to just use the registry:

Code: Select all

Sub NewPendingDelivery(oMessage)
	const REGKEY = "HKEY_LOCAL_MACHINE\SOFTWARE\hMailServer\Pending"
	set oShell = CreateObject("WScript.Shell")
	' new key
	sNewKey = REGKEY & "\" & oMessage.HeaderValue("Message-ID")
	oShell.RegWrite sNewKey, ""
	' write metadata to key
	oShell.RegWrite sNewKey & "\From", oMessage.HeaderValue("From"), "REG_SZ"
	oShell.RegWrite sNewKey & "\Date", oMessage.HeaderValue("Date"), "REG_SZ"
	oShell.RegWrite sNewKey & "\To", oMessage.HeaderValue("To"), "REG_SZ"
	oShell.RegWrite sNewKey & "\Subject", oMessage.HeaderValue("Subject"), "REG_SZ"
	set oShell = nothing
End Sub

In my POST handler, one of three actions happens for these temporary registry entries:

1. A delivery notification event deletes the key.
2. A bounce notification event deletes the key (SendGrid generates an NDR for these and HMS relays it to the originator).
3. A delayed notification event uses this metadata to create a delay notification to the originator and sends it via HMS.

[palinka]

The metadata table only hold emails stored in IMAP folders so if you don't store sent mail it won't be there.

I suppose this depends on the userbase. Do real people use POP3 anymore?

But I definitely get your point.

No you don't.

If you use SENDGRID for outbound messages then you are sending bulk. That makes a LOT of messages to save in your Send Mail folder

Good point.

However, an alternative could be to save the messages and then just clear them out sometime after the last delivery attempt time period. Many ways to skin this cat.

Actually, I think that would generate a different message ID now that I think about it, which would also obviate my first post above. Maybe not so many ways to skin this cat.

[palinka]

I only need ephemeral storage for those messages that don't get relayed by SendGrid on the first attempt due to transient failure. All other messages will be cleared immediately via either a delivered notification, or a bounce notification. Those that are simply delayed are the messages I want to notify the originator.

So I'm going to just use the registry:

Code: Select all

Sub NewPendingDelivery(oMessage)
	const REGKEY = "HKEY_LOCAL_MACHINE\SOFTWARE\hMailServer\Pending"
	set oShell = CreateObject("WScript.Shell")
	' new key
	sNewKey = REGKEY & "\" & oMessage.HeaderValue("Message-ID")
	oShell.RegWrite sNewKey, ""
	' write metadata to key
	oShell.RegWrite sNewKey & "\From", oMessage.HeaderValue("From"), "REG_SZ"
	oShell.RegWrite sNewKey & "\Date", oMessage.HeaderValue("Date"), "REG_SZ"
	oShell.RegWrite sNewKey & "\To", oMessage.HeaderValue("To"), "REG_SZ"
	oShell.RegWrite sNewKey & "\Subject", oMessage.HeaderValue("Subject"), "REG_SZ"
	set oShell = nothing
End Sub

In my POST handler, one of three actions happens for these temporary registry entries:

1. A delivery notification event deletes the key.
2. A bounce notification event deletes the key (SendGrid generates an NDR for these and HMS relays it to the originator).
3. A delayed notification event uses this metadata to create a delay notification to the originator and sends it via HMS.

This is an interesting concept. Is it faster/lighter than a database?