Skip to main content

Querying GitHub Issues from Google App Script

I'm currently exploring different integrations that can be done between Google App Script and Git. This is part of a bigger attempt to integrate Git data into my project management system. I'll post more on that later. GitHub supports a number of very nice sub-systems. One of them is the GitHub Issues ticket tracking system. It's fairly robust and comes with GitHub - which means it's highly integrated out-of-the-box. Integration with Google's App Script is done via the fetchURL command and JSON object manipulation.  After a good bit of experimentation, I've settled on querying GitHub Issues with an "on open spreadsheet event" to avoid issues with Google's quota system. Essentially, Google prevents you from calling the fetchURL command more than X times (see the quota limits ) per day. That's not great if you have people actively working and changing data in a spreadsheet. Some of my other App Script routines are running thousands of times per d

SMTP Mail and Indy (again)

Having spent a lot of time recently working on a ping scanner using Indy, I noticed that there's a lot of questions still on using SMTP with Indy. Let me first say that I am not an Indy expert. I get along with it successfully but find I still have to research things frequently.

With the disclaimer out of the way, we can get to the offering. A while back I wrote a handy little unit to simply send a mail message with or without attachments and with or without providing authentication. It even has support for OpenSSL. I use this unit in a number of applications and have seen it successfully send hundreds of e-mails without issue. I recently added support for threaded message sending to take advantage of the multi-core system I'm now running on.

This code has had a few additions (like the logger) that I've gleaned over time from various newsgroup postings, but I didn't record the authors so let me credit the anonymous Internet authors who support Indy. It's really amazing how helpful Google can be when you're stuck. At any rate, here's the code for the mailer in the hopes that it will help someone else who is stuck dealing with SMTP.



unit MailIt;

{requires indy10 - the LATER VERSIONS}

interface

uses Classes, SysUtils, IdSMTP, IdLogFile;

type
//this class encapsulates the "where to" and "what" portions of the message
//basically, it sends the BODY, including the files in the FILES list, using the SUBJECT to every person on the SendTo, etc. list
//SendTo, CCSendTo, and BccSendTo all take comma separated addresses (i.e, SendTo:='first@me.com,second@me.com';)
TDestinationPart = class
SendTo : string; //list of addresses
CCSendTo : string;
BccSendTo : string;
ReturnAddy: string; //if this is blank, there will be no return receipt

Subject : string;
Body : string;
Files : TStringList;

constructor Create(aSndTo, aCCSndTo, aBCCSndTo, aRtrnRcptAddy, aSubject, aBody:string; aFiles:TStringList);
destructor Free;
end;

//this class encapsulates the "how do i get it there" and "who from" portion of the message
//since this doesn't frequently change, you can use CreateFromFile and SaveToFile to create a template
// and save the typing every time you set it up
TOriginPart = class
AuthType : TIdSMTPAuthenticationType;
FromAddy : string;
UserName : string;
Password : string;
Server : string;
Port : integer;
Debug : boolean;
constructor Create(aAuthType:TIdSMTPAuthenticationType; aFromAddy, aUserName, aPassword, aServer: string; aPort:integer);
constructor CreateFromFile(fn:string);
procedure SaveToFile(fn:string);
end;

//this is the entire message
//you provide a DestinationPart and OriginPart and send the file using SendNow.
// Check SentOk afterwards to make sure it went.
// Any error message will be in SendMsg
TMailMessage = class
public
Destination : TDestinationPart;
Origin : TOriginPart;
SentOk : boolean;
SentMsg : string;
logFile : TIdLogFile;

constructor Create(Dest:TDestinationPart; Orig:TOriginPart);
procedure SendNow;
destructor Free;
end;

//this REPLACES TMailMessage with a Threaded version.
//You may want to use the FreeOnTerminate flag to create a "Fire and Forget" threaded solution
// NOTE: DO NOT GO CRAZY
// Pay attention to the documentation of the OS (try this for what I mean http://blogs.msdn.com/oldnewthing/archive/2005/07/29/444912.aspx)
// If you've got a really great quad-cpu quad-core rig, you may want to use a bigger number
TThreadMailMessage = class(TThread) //you get no error results or other
protected //information from this class, it just tries to send the message in the
MailMsg : TMailMessage; //background.make sure you use the debug function if you're having problems
procedure Execute; override;
public
constructor Create(aDestinationPart:TDestinationPart;
aOriginPart:TOriginPart);
end;


//NOTE: The following utility entries use CriticalSections. DO NOT HAMMER THEM.

//You can test this to see if all the TThreadedMailMessages have completed
function MailThreadsDone:boolean;
//A count of the number of TThreadedMailMessages are still out there
function ActiveMailThreadCount:integer;

implementation

uses IdGlobal, IdResourceStringsCore, IdGlobalProtocols, IdResourceStrings, IdExplicitTLSClientServerBase,
IDPOP3, IdMessage, IdEmailAddress, IdWinSock2, IdIOHandler, IdSSLOpenSSL, IdException,
IdAttachmentFile, IdText, IdSASL_CRAM_MD5, IdUserPassProvider, IniFiles,
IdIOHandlerStream, Dialogs, windows;

const
logFileName='mailit.log'; //the default log file name

var
ActiveMailThreads : integer=0;
MailThreadCSR : TRTLCriticalSection;

// ---------------------------------------------------------------------------

type
// this is a descendant of TidLogFile, it will create a plain text file with
// information about the transfer session
TlogFile = class(TidLogFile)
protected
procedure LogReceivedData(const AText, AData: string); override;
procedure LogSentData(const AText, AData: string); override;
procedure LogStatus(const AText: string); override;
public
procedure LogWriteString(const AText: string); override;
class function buildLogLine(data, prefix: string) : string;
end;

// this ensures the output of error and debug logs are in the same format, regardless of source
class function TlogFile.buildLogLine(data, prefix: string) : string;
begin

data := StringReplace(data, EOL, RSLogEOL, [rfReplaceAll]);
data := StringReplace(data, CR, RSLogCR, [rfReplaceAll]);
data := StringReplace(data, LF, RSLogLF, [rfReplaceAll]);

result := FormatDateTime('yy/mm/dd hh:nn:ss', now) + ' ';
if (prefix <> '') then
result := result + prefix + ' ';
result := result + data + EOL;
end;

// ---------------------------------------------------------------------------

procedure TlogFile.LogReceivedData(const AText, AData: string);
begin
// ignore AText as it contains the date/time
LogWriteString(buildLogLine(Adata, '<<'));
end;

// ---------------------------------------------------------------------------

procedure TlogFile.LogSentData(const AText, AData: string);
begin
// ignore AText as it contains the date/time
LogWriteString(buildLogLine(Adata, '>>'));
end;

// ---------------------------------------------------------------------------

procedure TlogFile.LogStatus(const AText: string);
begin
LogWriteString(buildLogLine(AText, '**'));
end;

// ---------------------------------------------------------------------------

procedure TlogFile.LogWriteString(const AText: string);
begin
// protected --> public
inherited;
end;

// ---------------------------------------------------------------------------



// ---------------------------------------------------------------------------
{TMailMessage}
// ---------------------------------------------------------------------------

constructor TMailMessage.Create(Dest:TDestinationPart; Orig:TOriginPart);
begin
Destination:=Dest;
Origin:=Orig;
//the class expects these, it will create them by default
if Destination=nil then Destination:=TDestinationPart.Create('','','','','','',TStringList.Create);
if Origin=nil then Origin:=TOriginPart.Create(satDefault,'','','','',0);
end;

// ---------------------------------------------------------------------------
procedure TMailMessage.SendNow;
var MsgSend:TIdMessage;
SMTP:TIdSMTP;
ix:integer;
SASLLogin:TIdSASLCramMD5;
UserPassProv:TIdUserPassProvider;
textPart:TIdText;
begin
logFile := nil; //init to no logging
MsgSend:=TIdMessage.Create; //create the message
with MsgSend do
begin
Body.Text:=Destination.Body;
if POS('<html>',Body.Text)<>0 then
if (Destination.Files<>nil) AND (Destination.Files.Count>0) then //if you are using attachments with html mail, you have do do it in sections
begin
Body.Text:=''; //clear the body
contentType:='multipart/alternative'; //setup the message parts
textPart:=TIdText.Create(MsgSend.MessageParts,nil); // this is for plain text
textPart.ContentType:='text/plain'; // set the content type
textPart.Body.Add('This message is formatted as HTML.');// just tell the user that the message is html - it would be nice to strip the html, but you'd never make it look right. probably should add support for a plain text and html text in the Destination class
textPart.Body.Add(Destination.Body); // add the plain text part which is really html formatted
textPart:=TIdText.Create(MsgSend.MessageParts,nil); // this is for html text (which is basically just plain text with special rules)
textPart.ContentType:='text/html'; // set the content type - this is the magic that kicks in the special rules
textPart.Body.Add(Destination.Body); // add the html formatted text
end
else
contentType:='text/html'; //you can just send it as an html message
From.Text := Origin.FromAddy; //setup the from address
Recipients.EMailAddresses := Destination.SendTo; //setup who it's going to
Subject := Destination.Subject; //set the subject
Priority := mpNormal; //set the priority (note that you could add to Destination to support different priorities)
CCList.EMailAddresses := Destination.CCSendTo; //set the CC list
BccList.EMailAddresses := Destination.BCCSendTo; //set the BCC list
ReceiptRecipient.Text := Destination.ReturnAddy; //set the return receipt address
end;

for ix:=0 to Destination.Files.Count-1 do //if we got a list of file names, add each file as an attachment
TIdAttachmentFile.Create(MsgSend.MessageParts, Destination.Files.Strings[ix]);

try
SMTP:=TIdSMTP.Create; //create the SMTP object

try
TIdSSLContext.Create.Free; //try to create a SSL context (immediately disposes of it) - need OpenSSL to support this, if it fails, it will fall into the exception
smtp.IOHandler := TIdSSLIOHandlerSocketOpenSSL.Create(smtp); //if we succeed, then we can create a SSL socket handler and assign it to the IOHandler
smtp.UseTLS := utUseExplicitTLS; //and we can mark the we can use tls
except
smtp.IOHandler := TIdIOHandler.MakeDefaultIOHandler(smtp); //the SSL failed to create, so make the default IO handler
smtp.UseTLS := utNoTLSSupport; //mark that we have no TLS support
end;
smtp.ManagedIOHandler := True; //either way, we now have a ManagedIOHandler

if Origin.Debug then //if we marked that we wanted debugging, we need to do some setup
begin
logFile := TLogFile.Create(nil); //create the log file
logFile.FileName:=logFileName; //set the default log file name - NOTE: you could get fancy with this, but I normally don't use it
logFile.Active:=true; //activate the log file (creates the file and intializes everything)
smtp.IOHandler.Intercept := logFile; //set the logFile to the intercept of the IOHandler to capture the events
smtp.IOHandler.OnStatus := smtp.OnStatus; // - not sure why we do this, copied code -
end;

try
SMTP.AuthType := Origin.AuthType; //setup the auth type : satNONE, satDEFAULT, satSASL
SMTP.UserName := Origin.UserName; //setup the user name
SMTP.Password := Origin.Password; //setup the password

userPassProv:=TIdUserPassProvider.Create; //create the user/pass provider - this handles the login functions for SASL
userPassProv.UserName:=SMTP.UserName; // setup user name
userPassProv.Password:=SMTP.Password; // & password

if SMTP.AuthType=satSASL then //if we are going to use SASL - basically, SASL just encrypts the login, everything else is still clear text
begin
SASLLogin:=TIdSASLCramMd5.Create; // create the CramMd5 provider - see http://en.wikipedia.org/wiki/CRAM-MD5 - this is the encryptor
SASLLogin.UserPassProvider:=userPassProv; // assign the user pass provider to it
SMTP.SASLMechanisms.Add.SASL:=SASLLogin; // add the SASL login back to the SMTP object
end;

{General setup}
SMTP.Host := Origin.Server; //setup the server name
SMTP.Port := Origin.Port; //setup the server port

{now we send the message}
SMTP.Connect; //connect to the server - this will automatically use TLS if configured and supported
if not SMTP.Authenticate then //authenticate with the server - this will automatically use SASL if configured and supported
begin
MessageDlg('An error occurred attempting to send your e-mail. The error was : Unable to authenticate.', mtError, [mbOK], 0);
exit;
end;
try
try
SMTP.Send(MsgSend); //send the message
SentOk:=true; //indicate success
finally
SMTP.Disconnect; //disconnect from the server
end;
except on E :Exception do //if we had a failure, say so
begin
MessageDlg('An error occurred attempting to send your e-mail. The error was : '+E.Message, mtError, [mbOK], 0);
SentOk:=false; //set failure
SentMsg:=E.Message; //and grab the error message
end;
end;
finally
SMTP.Free; //free the memory
end;
finally
if logFile<>nil then //if we had a log file, free that
logFile.Free;
MsgSend.Free; //free the message
end;
end;

// ---------------------------------------------------------------------------
destructor TMailMessage.Free;
begin
Destination.Free; //free the destination
Origin.Free; //free the origin
end;

// ---------------------------------------------------------------------------


// ---------------------------------------------------------------------------
{TDestinationPart}
// ---------------------------------------------------------------------------

constructor TDestinationPart.Create(aSndTo, aCCSndTo, aBCCSndTo, aRtrnRcptAddy, aSubject, aBody:string; aFiles:TStringList);
begin
SendTo := aSndTo; //send message to, comma separate list for multiple adddesses
CCSendTo := aCCSndTo; //CC message to
BccSendTo := aBCCSndTo; //BCC message to
ReturnAddy:= aRtrnRcptAddy; //leave blank for not return receipt. set if you want a return receipt (me@mydomain.com)

Subject := aSubject; //what's it about
Body := aBody; //the text, supports HTML or TEXT
Files := aFiles; //a list of files to upload - WATCH THIS - you are giving up control by passing in the list, this list is free'd in the destructor
if Files=nil then
Files:=TStringList.Create; //we must have a list, create if not provided
end;

// ---------------------------------------------------------------------------
destructor TDestinationPart.Free;
begin
Files.Free; //free space claimed in create - watch this, it takes control of the list passed in the create and will dispose of it, can cause A/Vs
end;

// ---------------------------------------------------------------------------

// ---------------------------------------------------------------------------
{TOriginPart}
// ---------------------------------------------------------------------------

constructor TOriginPart.Create(aAuthType:TIdSMTPAuthenticationType; aFromAddy, aUserName, aPassword, aServer: string; aPort:integer);
begin
AuthType := aAuthType; //options are : 0=satNONE, 1=satDEFAULT, 2=satSASL
FromAddy := aFromAddy; //me@mydomain.com
UserName := aUserName; //me
Password := aPassword; //mypassword
Server := aServer; //smtp.mydomain.com
Port := aPort; //SMTP connection port (25 is default)
Debug := false; //debugging off/on
end;

// ---------------------------------------------------------------------------
constructor TOriginPart.CreateFromFile(fn:string);
var INF:TIniFile;
i:integer;
begin
INF:=TIniFile.Create(fn);
i := INF.ReadInteger('SMTP','AuthType',ord(satDefault));
case i of
0 : AuthType := satNONE;
1 : AuthType := satDEFAULT;
2 : AuthType := satSASL;
end;
FromAddy := INF.ReadString ('SMTP', 'FromAddy', ''); //me@mydomain.com
UserName := INF.ReadString ('SMTP', 'UserName', ''); //me
Password := INF.ReadString ('SMTP', 'Password', ''); //mypassword
Server := INF.ReadString ('SMTP', 'Server', ''); //smtp.mydomain.com
Port := INF.ReadInteger('SMTP', 'Port', 25); //default SMTP is 25
Debug := INF.ReadBool ('SMTP', 'Debug', false); //we don't we debugging on
INF.Free;
end;

// ---------------------------------------------------------------------------
procedure TOriginPart.SaveToFile(fn:string);
var INF:TIniFile;
begin
INF:=TIniFile.Create(fn);
INF.WriteInteger('SMTP', 'AuthType', ord(AuthType)); //options are : 0=satNONE, 1=satDEFAULT, 2=satSASL
INF.WriteString ('SMTP', 'FromAddy', FromAddy); //me@mydomain.com
INF.WriteString ('SMTP', 'UserName', UserName); //me
INF.WriteString ('SMTP', 'Password', Password); //mypassword
INF.WriteString ('SMTP', 'Server', Server); //smtp.mydomain.com
INF.WriteInteger('SMTP', 'Port', Port); //SMTP connection port
INF.WriteBool ('SMTP', 'Debug', Debug); //debugging off/on
INF.Free;
end;

// ---------------------------------------------------------------------------


// ---------------------------------------------------------------------------
{ TThreadMailMessage & util functions for it}
// ---------------------------------------------------------------------------

procedure IncMailThreadCount;
begin
EnterCriticalSection(MailThreadCSR);
inc(ActiveMailThreads);
LeaveCriticalSection(MailThreadCSR);
end;

procedure DecMailThreadCount;
begin
EnterCriticalSection(MailThreadCSR);
dec(ActiveMailThreads);
LeaveCriticalSection(MailThreadCSR);
end;

function MailThreadsDone:boolean;
begin
result:=ActiveMailThreadCount=0;
end;

function ActiveMailThreadCount:integer;
begin
EnterCriticalSection(MailThreadCSR);
result:=ActiveMailThreads;
LeaveCriticalSection(MailThreadCSR);
end;


constructor TThreadMailMessage.Create(aDestinationPart: TDestinationPart;
aOriginPart: TOriginPart);
begin
inherited Create(false);
IncMailThreadCount;
if aDestinationPart=nil then raise Exception.Create('You must supply a destination');
if aOriginPart=nil then raise Exception.Create('You must supply an origin');
FreeOnTerminate:=true;

MailMsg:=TMailMessage.Create(aDestinationPart, aOriginPart);
end;

procedure TThreadMailMessage.Execute;
begin
try
MailMsg.SendNow;
finally
DecMailThreadCount;
end;
end;

initialization
InitializeCriticalSection(MailThreadCSR);

finalization
DeleteCriticalSection(MailThreadCSR);

end.

Comments

Chris Miller said…
Thanks Marshall, I was couldn't figure out how to configure logging with the TIDSmtp component. Your example was very helpful.
Marshall Fryman said…
Glad it was useful. I find that the log file applies uniformly to most of the Indy components. I used the exact same log file class for my HTTPS Post example.
Robert said…
this is great. but a question. my attachments are missing the header Content-Description: filename and are being received as noname... Do you have any thoughts on this? And again, great job on the code...
Marshall Fryman said…
Robert -

I've never seen a problem with it using the code I've provided. I have a few automated processes that send status info with attachments daily without the issue. What version of Indy are you using?

m
Marshall Fryman said…
Robert -

Since you haven't come back to add more information, I thought I'd offer another pointer. Arvid just posted a solution to exactly what you are describing. It actually appears to be an issue from the Indy version shipped with D2009, although I would assume you could also experience the problem if you picked up that snapshot.

HTH,
m
Anonymous said…
Thanks, I got stuck a bit with the satSASL, and found it to be atSASL, same for satDefault, satNone.

This is the most comprehensive piece of code I've seen thusfar! with regards to idSMTP

Regards

Adrian Wreyford
Anonymous said…
Hi, me again.

In D2009, your code compiles just fine.

In D2007, (and I'm still stuck there due to components I rely on that don't yet support D2009
) I cannot get it to compile.

First as noted earlier the satDefault is atdefault in D2007.
You need to remove the IDPOP3 or the case statement
case i of
0 : AuthType := satNONE;
1 : AuthType := satDEFAULT;
2 : AuthType := satSASL;
end; won't compile correctly.

Infortunately the descendant of
TlogFile = class(TidLogFile) wont compile either.

It appears the override is the problem, as the procedures differ, and then you cannot inherit the:
procedure TlogFile.LogWriteString(const AText: string);
begin // protected --> public
inherited;
end;

Well, I'm just hoping you see this, and perhaps you still have D2007, and could help out a bit.

Thanks
Adrian Wreyford
Marshall Fryman said…
Adrian -

I do have and use D2007. The code I've posted compiles in D2007 and D7. The trick is that you can't use the version of Indy that shipped with either product. Download the latest/later snapshots, rename the Indy DCU's in Dxxxx and use the new code. Indy is a bit odd in its release cycles. For some unfathomable reason, Indy hasn't released a new version since Indy 10 came out. Regardless of the "version number", there have been significant changes to the code base between the various versions of Indy 10.

Posted back if that doesn't do the trick. I'm personally using a late 2007 snapshot of Indy and I know that the code works in that version because I use it daily with D2007. I have seen a few odd things with the HTTP post, but overall it works well.

m
Anonymous said…
Thanks Marshall,

I downloaded the latest Indy builds, installed the Indy10 VersionX11 for CodeGear D2007, got rid of all the old BPL's, built the packages, and there you have it, compiling without errors.

I Suppose the D2009 had the latest builds.

Thanks a stack .. now to put it to test!

Adrian Wreyford
Unknown said…
Thank you very much for this unit. It works fine and is a very good example for object-oriented programming.

Best Regards Uli Becker
Marshall Fryman said…
Glad it worked out for you.
Sadegh said…
very usefull code.

but please write your code with right indent for look like.

thanks
Anonymous said…
Is it possible to post an example of how to use this?
Anonymous said…
I coun't get it to work on Win8.
I spend ages on finding out why...

It is IdHTTP's ReadTimeout. By default it is -1 and doesn't work with Windows 8. Set it to anything positive like 15000ms or 30000 will fix the problem.

code (just before SMTP.connect):
SMTP.ReadTimeout := 30000;

Popular posts from this blog

Detecting a virtualized environment

CubicDesign on delphi-talk.elists.org recently asked the question: "How do I know/detect if my software is running under Windows [or a virtual environment]?" Well, it turns out that it's a lot harder to tell than you would think. Apparently, the VM (VMware, Xen, Wine, etc.) doesn't really want you to be able to do this. At least not easily. For VMware, there is a decent little C routine called jerry.c that does the trick. Jerry actually uses a simple communication channel that VMware left open. It's not 100% foolproof since the admin can change the channel, but that's not likely going to happen unless the system is specifically designed to be a honeypot. If you're running on a honeypot and still have a legitimate reason for detection, you could look at the much more complex scoopy implementation which inspects how the system is actually virtualized using the SIDT CPU instruction instead of a communication channel. Another reference (red pill) is here . F

Vista UAC Manifest

Here's a couple of interesting little tidbits for those using Delphi 2007 and trying to create manifest files to require your program to run as Administrator. For those who don't know what this means, we're talking about running a program and receiving the wonderful little pop-up "A program is trying to take over your life"... well, something like that anyway. You will need your program to receive elevated privileges if you want to do certain things. In my particular instance, I wanted to write to the HKEY_CLASSES_ROOT registry entries. This isn't supposed to be a full reference to what a manifest is, how it works, etc., etc. Microsoft has documented the UAC in some depth and I recommend using their documentation if you want to really understand your options. First, the manifest structure is basically just an XML document that tells Vista how it should handle the program. For elevated privileges, it should look like: < assembly xmlns ="urn:schemas-mic

Copyright 2008-2022, Marshall Fryman