Metadata by Message-ID

Use this forum if you have problems with a hMailServer script, such as hMailServer WebAdmin or code in an event handler.
Post Reply
mikedibella
Senior user
Senior user
Posts: 391
Joined: 2016-12-08 02:21

Metadata by Message-ID

Post by mikedibella » 2021-01-04 20:14

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?

User avatar
SorenR
Senior user
Senior user
Posts: 4201
Joined: 2006-08-21 15:38
Location: Denmark

Re: Metadata by Message-ID

Post by SorenR » 2021-01-05 00:05

mikedibella wrote:
2021-01-04 20:14
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 ;-)
SørenR.

Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.

palinka
Senior user
Senior user
Posts: 2475
Joined: 2017-09-12 17:57

Re: Metadata by Message-ID

Post by palinka » 2021-01-05 05:15

SorenR wrote:
2021-01-05 00:05
mikedibella wrote:
2021-01-04 20:14
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

User avatar
SorenR
Senior user
Senior user
Posts: 4201
Joined: 2006-08-21 15:38
Location: Denmark

Re: Metadata by Message-ID

Post by SorenR » 2021-01-05 14:46

palinka wrote:
2021-01-05 05:15
SorenR wrote:
2021-01-05 00:05
mikedibella wrote:
2021-01-04 20:14
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
SørenR.

Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.

palinka
Senior user
Senior user
Posts: 2475
Joined: 2017-09-12 17:57

Re: Metadata by Message-ID

Post by palinka » 2021-01-05 15:59

SorenR wrote:
2021-01-05 14:46
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.

Image

User avatar
SorenR
Senior user
Senior user
Posts: 4201
Joined: 2006-08-21 15:38
Location: Denmark

Re: Metadata by Message-ID

Post by SorenR » 2021-01-05 20:02

palinka wrote:
2021-01-05 15:59
SorenR wrote:
2021-01-05 14:46
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 :mrgreen:
SørenR.

Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.

mikedibella
Senior user
Senior user
Posts: 391
Joined: 2016-12-08 02:21

Re: Metadata by Message-ID

Post by mikedibella » 2021-01-05 20:47

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.

User avatar
SorenR
Senior user
Senior user
Posts: 4201
Joined: 2006-08-21 15:38
Location: Denmark

Re: Metadata by Message-ID

Post by SorenR » 2021-01-05 20:52

mikedibella wrote:
2021-01-05 20:47
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.
:shock:
SørenR.

Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.

palinka
Senior user
Senior user
Posts: 2475
Joined: 2017-09-12 17:57

Re: Metadata by Message-ID

Post by palinka » 2021-01-05 23:18

SorenR wrote:
2021-01-05 20:02
palinka wrote:
2021-01-05 15:59
SorenR wrote:
2021-01-05 14:46
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 :mrgreen:
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. :wink:

palinka
Senior user
Senior user
Posts: 2475
Joined: 2017-09-12 17:57

Re: Metadata by Message-ID

Post by palinka » 2021-01-05 23:25

mikedibella wrote:
2021-01-05 20:47
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?

Post Reply