Archive

Archive for October, 2020

Deleting a large number of records from a database without locking it (for ages) #SQL

If you have a large database, with millions of rows, and you need to run an batch operation on it, like deleting lots of old records, then you’ll quickly find that the operation often will lock the table, and prevent users get at their data – as well as bloating the transaction log table.

So, the trick is to break it down into manageable chunks. Let’s say the operation I want to run is this;

delete from log where success=0

If the “log” table has millions of rows, then this will undoubtedly lock the log table, as the statement is running. Preventing new logs from being written.

If I then write something like:

delete top (100) from log where success=0

This completes in a jiffy, and deletes 100 rows from the log table. The actual 100 rows that get deleted are beyond your control, it’s just the first 100 that it finds. But that isn’t really a problem.

So, let’s write this as a loop as follows;

declare @FirstPass Bit
select @FirstPass = 1;
while @@ROWCOUNT = 100 or @FirstPass = 1
begin
set @FirstPass = 0;
delete top (100) from log where success=0
end

This just means that it will run this command over and over again until it no longer has rows that it can delete.

Now, to improve upon this a bit more, I’m going to add the lines;

  WAITFOR DELAY  '00:00:01';  
  RAISERROR('ok',0,1) WITH NOWAIT;

Which will put a 1 second delay in the loop, to make sure we’re not hogging all the Database’s processing power, and the “RaiseError” just flushes the output to the messages window, so we can see that everything is running smoothly.

Categories: Uncategorized

#APNS using HTTP/2 in C# using #AWS Lambda

By March 31st 2021, all users using the Apple Push Notification Service (APNS) must update to Apple’s new API, which is based on HTTP/2. If you don’t update, then it’s likely that your push notifications won’t get through.

If you are using C#, then you can use the dotAPNS NUGet package, which, from the client perspective is a tiny change, however, it does require .NET Core to run, which if your project is in .NET standard, then this could cause problems.

So, in this case, I decided to create a stand-alone .NET Core app using AWS Lambda and AWS API Gateway, which would be a middleware API between my main app and the APNS API.

My App -> AWS LAMBDA -> APNS

So, I created an AWS Lambda App in Visual Studio, and made sure I could publish to AWS Lambda. Which also gives you an API Gateway URL, in the format;

<ID>.execute-api.<Region>.amazonaws.com/Prod

I also needed to update the serverless.template file as follows

         "Events": {
           "RootGet": {
             "Type": "Api",
             "Properties": {
               "Path": "/",
               "Method": "GET"
             }
           },
           "RootPost": {
             "Type": "Api",
             "Properties": {
               "Path": "/",
               "Method": "POST"
             }
           }

And changed the Handler to

ApnsHttp2Serverless::ApnsHttp2Serverless.Functions::HttpRequest

(Where ApnsHttp2Serverless was the name of my project)

Now, my code to send the push notification is as follows;

private ApnsResponse Send(
             byte[] certificate, 
             string certPassword, 
             string message, 
             string destination)
 {
     var x509 = new X509Certificate2(certificate, certPassword);
     var applePushNotificationService = ApnsClient.CreateUsingCert(x509);
     var push = new ApplePush(ApplePushType.Alert)
     .AddAlert(message)
     .AddToken(destination);
     return applePushNotificationService.Send(push).Result;
 }

Which is called from the lambda as follows;

public APIGatewayProxyResponse HttpRequest(APIGatewayProxyRequest request, ILambdaContext context)
 {
     context.Logger.LogLine(request.Body);
     var jRequest = JObject.Parse(request.Body);
     var certificate = jRequest["certificate"].ToObject();
     var certPassword = jRequest["certPassword"].ToObject();
     var message = jRequest["message"].ToObject();
     var destination = jRequest["destination"].ToObject();
     var sendResult = Send(certificate, certPassword, message, destination);
     var response = new APIGatewayProxyResponse
     {
     StatusCode = (int)HttpStatusCode.OK,
     Body = JsonConvert.SerializeObject(sendResult,Formatting.Indented),
     Headers = new Dictionary { { "Content-Type", "application/json" } }
     };
     return response;
 }

So, I pushed this code to AWS Lambda, and wrote the client as follows:

public static string Push(string destination, string message, string certFile, string certPassword)
 {
     var certificate = File.ReadAllBytes(certFile);
     var oPayload = new
     {
         destination,
         message,
         certificate,
         certPassword
     };
     var strPayload = JsonConvert.SerializeObject(oPayload, Formatting.Indented);
     var wc = new WebClient();
     var response = wc.UploadString("https://....execute-api.eu-west-1.amazonaws.com/Prod", strPayload);
     return response;
 }

So, Assuming you have the destination ID, and your certs set up correctly, this just works!

For anyone who is interested, here is the GitHub Repo of the AWS Lambda function:

https://github.com/infiniteloopltd/ApnsHttp2

And for those who happen to use PHP as the trigger to send push notifications, here is the client code in PHP:

<?php
$data        = file_get_contents('cert.p12');
$cert64      = base64_encode($data);
$destination = "-apns-token-";
$password    = "-cert-password-";
$url         = "https://xxxxxx.execute-api.eu-west-1.amazonaws.com/Prod";
$ch          = curl_init($url);
$oPayload    = array(
    'destination' => $destination,
    'message' => 'hello there!',
    'certificate' => $cert64,
    'certPassword' => $password
);
$payload     = json_encode($oPayload);
curl_setopt($ch, CURLOPT_POSTFIELDS, $payload);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$content = curl_exec($ch);
if ($content === false) {
    echo 'Curl error: ' . curl_error($ch);
}
curl_close($ch);
echo $content;
?>

Categories: Uncategorized

Overriding the Date in a WebClient Request in C#

The WebClient object in C# greatly simplifies the actions of making HTTP GET and POST requests, however, when you need to edit an “Under the hood” property, then you find that WebClient has hidden this functionality from you.

In this example, I want to override the Date property of the HTTP request, effectively sending a request to the server, but saying it was sent at a different time. This is not a property you often want to mess with, but you can do it as follows;

private class SpecializedWebClient : WebClient
{
    public DateTime Date { get; set; }
    protected override WebRequest GetWebRequest(Uri address)
    {
	var request = base.GetWebRequest(address) as HttpWebRequest;
	if (request != null) request.Date = Date;
	return request;
    }
}

I hope someone finds this useful!

Categories: Uncategorized
%d bloggers like this: