DEV Community: Peter Eysermans

Creating an iOS 14 widget showing health data

Peter Eysermans — Sun, 19 Jul 2020 10:40:46 +0000

With iOS 14 Apple introduced a neat new feature: widgets. I've been running the beta for some weeks now and love them. So let's see how hard it is to create one ourself.

Let's start with a little disclaimer: This article and the code with it was written using iOS 14 beta 2 and Xcode 12 beta 2. There is no guarantee that it will work in future versions.

Via the health app in iOS you can track your sleep with either your iPhone or your Apple Watch. I thought it would be neat to have a widget which shows the time you slept last night. Let's look at the end result:

The first thing you need to know when developing a widget: your widget won't work if the code you are using in your widget is not being called in the app itself as well. At first I struggled with this. If your widget contains code that is not called by the app, it won't work. With this in mind let's first develop the main app and once it's finished we can reuse part of its code to create a widget for it.

Configuring HealthKit

First some setup needs to be done. The app needs permission to query data from the iOS Health app. In the Project navigator, select the top most item in the list and open the Signing & Capabilities tab. Add a capability by clicking on the + Capability button on the top left. In the popup that follows select the HealthKit capability.

Next to this capability we also need to add two rows to the Info.plist file to explain to the user what will happen with the data. Open the file and add these two rows to it:

Privacy - Health Share Usage Description
Privacy - Health Update Usage Description

Give a description what your app will be doing with the data.

Fetching sleep data

Now the app is all set up to read the necessary data from the Health app. To fetch this data a helper class is created, this helper class will be reused when the widget is created. The code to retrieve the sleep data is based on code from this article: Using Sleep Analysis in HealthKit with Swift.

To interact with the Health app HealthKit is imported and the HealthKit HKHealthStore class is used. Before data can be read, the app needs authorisation to query data:

func retrieveSleepWithAuth(completion: @escaping (String) -> ()) {

        let typestoRead = Set([
            HKObjectType.categoryType(forIdentifier: HKCategoryTypeIdentifier.sleepAnalysis)!
        ])

        let typestoShare = Set([
            HKObjectType.categoryType(forIdentifier: HKCategoryTypeIdentifier.sleepAnalysis)!
        ])

        healthStore.requestAuthorization(toShare: typestoShare, read: typestoRead) { (success, error) -> Void in
            if success == false {
                NSLog(" Display not allowed")
            } else {
                self.retrieveSleep(completion: completion)
            }
        }
    }

This function doesn't need much explanation, the type of data the app will save and read are passed to the healthStore via the requestAuthorization function.

Once the authorisation has been approved, the sleep data can be fetched. Because the app only shows the data from last night, the data is filtered using a predicate.

let startDate = Calendar.current.date(byAdding: .day, value: -1, to: Date())
let endDate = Date()

let predicate = HKQuery.predicateForSamples(withStart: startDate, end: endDate, options: HKQueryOptions.strictEndDate)

As you can see the predicate filters all data from yesterday until today. Now that there is a predicate, we can query the healthStore to get the necessary data.

if let sleepType = HKObjectType.categoryType(forIdentifier: HKCategoryTypeIdentifier.sleepAnalysis) {
     let sortDescriptor = NSSortDescriptor(key: HKSampleSortIdentifierEndDate, ascending: false)

     let query = HKSampleQuery(sampleType: sleepType, predicate: predicate, limit: 200, sortDescriptors: [sortDescriptor]) { (query, tmpResult, error) -> Void in

     }

healthStore.execute(query)

A few things are happening in this block of code. The sleepType variable indicates the type of data that is fetched, the sortDescriptor describes how the data should be sorted. In this case the newest data is on top. At last a query object is created using the type, predicate and sort descriptor. It has a closure where the fetched data can be processed. This query is passed to the healthStore instance and that's it.

In the closure the number of seconds between the start and end of each sleep session is calculated and the total number of seconds is converted to a readable format: 7h 30m 4s. This is the query object including the closure for the sake of being complete:

let query = HKSampleQuery(sampleType: sleepType, predicate: predicate, limit: 200, sortDescriptors: [sortDescriptor]) { (query, tmpResult, error) -> Void in

      if error != nil {
          return
      }

      var totalSeconds : Double = 0

      if let result = tmpResult {
          for item in result {
              if let sample = item as? HKCategorySample {

                  let timeInterval = sample.endDate.timeIntervalSince(sample.startDate)

                  totalSeconds = totalSeconds + timeInterval
              }
          }
      }

      let result =
          String(Int(totalSeconds / 3600)) + "h " +
          String(Int(totalSeconds.truncatingRemainder(dividingBy: 3600) / 60)) + "m " +
          String(Int(totalSeconds.truncatingRemainder(dividingBy: 3600)
                      .truncatingRemainder(dividingBy: 60))) + "s"
    }

Showing sleep data

Now that the necessary data is fetched, it can be shown in the UI. This ObservableObject will be used to drive the UI. It has a mainText property in which the fetched sleep data is filled out.

class SleepModel : ObservableObject {
    @Published var mainText : String = "Getting your sleep time from yesterday"

    let sleepRetrieval = SleepRetrieval()

    func retrieveSleep() {
        sleepRetrieval.retrieveSleepWithAuth() { result -> Void in
            DispatchQueue.main.async {
                self.mainText = result
            }
        }
    }
}

And this is the SwiftUI view to show the data:

struct ContentView: View {
    @ObservedObject var viewModel : SleepModel

    var body: some View {

        VStack(alignment: .center, spacing: 0) {
            Text("Last night you slept: ")
            // show the text from the SleepModel in the UI
            Text(viewModel.mainText)
                .padding()
                // when the text appears call the SleepModel to fetch the necessary data
                .onAppear {
                    viewModel.retrieveSleep()
                }
        }
        .frame(minWidth: 0, maxWidth: .infinity, minHeight: 0, maxHeight: .infinity)
        .padding()
        .background(LinearGradient(gradient: Gradient(colors: [.white, .blue]), startPoint: .top, endPoint: .bottom))
        // stretch the gradient from top to bottom, ignoring the safe area
        .edgesIgnoringSafeArea(.all)


    }
}

Running the app, this is the result:

Widget

Now that the basic functionality has been implemented in the app, let's create a widget for it. This allows the user to see how much he has slept last night without having to open the app itself. To create a widget for an app, add a new target to the project in Xcode via File > New > Target.... In the popup select Widget extension.

Go to the next window, give the widget a name and uncheck the Include Configuration Intent. This checkbox is intended if the widget has certain properties than can be configured by the user and is out of scope for this little project. When finishing the wizard there should be a new folder in the Project navigator in Xcode with the name of the widget you have chosen. In there Xcode has generate several files to create the widget.

This widget has its own plist file. The first thing that needs to happen, in order to allow the widget to read data from the Health app, is to add the necessary rows to the Info.plist file of the widget similar to what has been done for the app itself.

The class where it all starts is the one annotated with the @main. That is the starting point for the widget.

@main
struct SleepTrackerWidget: Widget {
    private let kind: String = "SleepTrackerWidget"

    public var body: some WidgetConfiguration {
        StaticConfiguration(kind: kind, provider: SleepTimeline(), placeholder: PlaceholderView()) { entry in
            SleepTrackerWidgetEntryView(entry: entry)
        }
        .configurationDisplayName("Time slept")
        .description("This widget shows the time you slept last night.")
    }
}

The widget uses a StaticConfiguration because the widget has no user configurable options. To this instance a TimelineProvider, a PlaceholderView and a closure with an entry instance that has the necessary data for the widget to display are passed.

The configurationDisplayName and the description are shown when the user is configuring his homescreen.

Let's have a look at the TimelineProvider. The class provides two methods that need to be overriden. The snapshot method is called by WidgetKit whenever the widget is in a transient state. For example when it appears in the widget gallery. Some sample data can be provided to give the user an idea of what the widget does an how it looks.

The timeline method is where it happens. This will be called by WidgetKit every time the widget needs to be updated. The time the widget needs to be updated is controlled by the refreshDate that is passed to the Timeline instance. This code tells WidgetKit to update the widget every day. It does not guarantee that the widget will be updated at that moment. iOS still decides itself when it updates the widget and takes other parameters into account, for example if the widget is currently not on the screen it could decide to wait with updating the widget. The helper class to retrieve the sleep is used and in the closure an entry is created and passed to the completion closure.

struct SleepTimeline: TimelineProvider {
    public func snapshot(with context: Context, completion: @escaping (SleepEntry) -> ()) {
        let entry = SleepEntry(date: Date(), value: "")
        completion(entry)
    }

    public func timeline( with context: Context, completion: @escaping (Timeline<Entry>) -> ()) {
        let currentDate = Date()
        let refreshDate = Calendar.current.date(byAdding: .day, value: 1, to: currentDate)!

        SleepRetrieval().retrieveSleep { result in

            let entry = SleepEntry(date: currentDate, value: result)

            let timeline = Timeline(entries: [ entry ], policy: .after(refreshDate))
            completion(timeline)
        }
    }
}

To be able to use the helper class, don't forget to check the widget extension in the Target Membership in Xcode.

Another important notice is that compared to the app itself this code will not ask permission to access the data of the Health app. This is because the widget cannot show the permission dialog. The error below is thrown when the widget tries to ask permission. More information can be found in this thread on the Apple developer forums.

2020-07-19 10:02:27.315405+0200 SleepTrackerWidgetExtension[9097:2633778] [auth] FAILED prompting authorization request to share (
    HKCategoryTypeIdentifierSleepAnalysis
), read (
    HKCategoryTypeIdentifierSleepAnalysis
)
2020-07-19 10:02:27.315675+0200 SleepTrackerWidgetExtension[9097:2633778]  Display not allowed

The solution is to handle the permission in the app itself and just retrieve the necessary data in the widget.

The SleepEntry class is the ViewModel which is used to pass the data to the widget.

struct SleepEntry: TimelineEntry {
    public let date: Date
    public let value: String
}

The last thing that needs to be defined is the user interface. There are two classes that are responsible for how the widget looks. The PlaceholderView is the class that is used when the widget is shown for the first time. From the Apple documentation:

It's a generic visual representation with no specific content.

The SleepTrackerWidgetEntryView is the main class that provides the user interface. It has a Timeline.Entry property which is actually an instance of the SleepEntry class, it contains the necessary data for the widget to show.

struct SleepTrackerWidgetEntryView : View {
    var entry: SleepTimeline.Entry

    var body: some View {
        VStack(alignment: .center, spacing: 4) {
            Text("You slept:")
            Text(entry.value)
        }
        .frame(minWidth: 0, maxWidth: .infinity, minHeight: 0, maxHeight: .infinity)
        .padding()
        .background(LinearGradient(gradient: Gradient(colors: [.white, .blue]), startPoint: .top, endPoint: .bottom))
    }
}

And that's it, our app now has a neat widget. Let's look at the end result one more time.

If you need the full project, the code can be found on github.

Manipulating an Excel file with Python

Peter Eysermans — Fri, 15 May 2020 21:04:01 +0000

It's one of those things that sound easy but hardly are. There are a lot of libraries and solutions out there that enable you to manipulate Excel files via code. Some of them are expensive, other are open source.

A lot of languages have a library to help you manipulate Excel files. An obvious language to choose is C#. It has a lot of options, some open source, some paying. Instead of using C#, I opted for Python. Unlike C#, Python is very light weight to start with and does not need a big IDE like Visual Studio to create a small application.

In Python there are multiple options to use. Pandas and xlsxwriter can both write to an Excel file but they both have their limitations as well. Xlsxwriter can only create new Excel files and Pandas does not have the functionality to export a sheet of the Excel file to PDF. So I opted in using the Excel COM Object that ships with Excel.

This comes with some prerequisites, the code will only work on Windows with Excel installed. There is a package to use COM objects called pywin32. It's a bit confusing, there are actually two packages, pywin32 and pypiwin32. But pypiwin32 is abandoned an the pywin32 package is the way to go. It's installed via pip:

pip install pywin32

After installing the package, the code is fairly easy. First let's create an application, this is the main object which allows to manipulate the Excel file.

from win32com import client

excelApp = client.Dispatch("Excel.Application")

Next let's load an excel file to manipulate.

books = excelApp.Workbooks.Open(r"C:\\folder\\test.xlsx")

Writing a value to Excel

Now everything is in place to read or write to the Excel file, to discover the possibilities you can always check the COM object documentation. Let's write a value to the Excel file and save it.

from win32com import client

excelApp = client.Dispatch("Excel.Application")
book = excelApp.Workbooks.open(r"C:\\folder\\test.xlsx")

workSheet = book.Worksheets('Sheet1')
workSheet.Cells(1, 1).Value = "test"

book.Save()
book.Close()

This code will write the value test to the cell A1 in the Excel file. Notice that the Cells property has a 1-based index for both the row and the column.

Generating a PDF from the Excel file

There are not a lot of libraries out there that foresee in this functionality. Using Python and the Excel COM object is one of the easiest ways to generate a PDF from an Excel file. I've used this on big Excel files and achieved very good results with it.

from win32com import client

excelApp = client.Dispatch("Excel.Application")
book = excelApp.Workbooks.open(r"C:\\folder\\test.xlsx")

sheet = book.Worksheets('Sheet1')
sheet.ExportAsFixedFormat(0, r"C:\\folder\\test.pdf")

book.Close()

ExportAsFixedFormat is the method that does all the work. The first parameter is the type of format and is based on the XlFixedFormatType enum. The PDF will be created on the destination that is specified as second parameter.

Performance

When the use case gets more complex you often bump into performance problems. Here are a few tips that can help you. The first and most obvious tip is to get rid of all the unused functions in your Excel file, make your Excel file as simple as possible and make sure it has no unused formulas.

Disable display alerts

To avoid Excel being blocked by an alert that could be shown to the user we can disable display alerts. If the Excel manipulation happens in the background the alerts won't be visible anyway.

import win32com.client

excelApp = win32com.client.Dispatch("Excel.Application")
excelApp.DisplayAlerts = false

XLSB file type

Saving the Excel as the XSLB binary file type instead of the XLSX xml file type will give a small performance boost and make the file smaller.

Compress images

To make the Excel file as small as possible, the images that it contains can be compressed. Go to File > Save As and open the Compress Pictures option via Tools. Choosing a higher compression will result in a smaller file.

Reading and writing values using `Range`

Instead of using the Cells property to write values one by one, it's also possible to write an entire range to the Excel file. This will be more performant than using Cells. The value that is set to the Range is always an array of arrays. Let's start with a simple case

workSheet.Range('A1:A5').Value = [ [ 1 ], [ 2 ], [ 3 ], [ 4 ], [ 5 ] ]

This will write values to the range A1:A5.

To write to multiple columns a value can be added to the arrays for each column.

workSheet.Range('A1:C5').Value = [ [ 1, 10, 100 ], [ 2, 20, 200 ], [ 3, 30, 300 ], [ 4, 40, 400 ], [ 5, 50, 500 ] ]

The result:

Disable automatic calculation

If the Excel file you are using has a lot of formulas this might slow down the manipulation considerably. Because the formulas need to be recalculated every time a value changes in the Excel. This automatic recalculating can be disabled. This can be handy if you have a lot of values to fill out and if you are only interested in the end result. First you disable automatic recalculation, you fill out all the values and then you reenable automatic recalculation. This way the recalculation will only happen once even if a lot of values are filled out.

This code will set the calculation to manual, the possible values of the Calculation property can be found in the XlCalculation enumeration.

import win32com.client

excelApp = win32com.client.Dispatch("Excel.Application")
excelApp.Calculation = -4135

To reenable the calculation set the Calculation property to automatic:

excelApp.Calculation = -4105

Turn off screen updating

Turning off screen updating can also speed up the Excel manipulation.

import win32com.client

excelApp = win32com.client.Dispatch("Excel.Application")
excelApp.ScreenUpdating = false

Call rejected by callee

This error can pop up sometimes, it usually happens when Excel is being called consecutive times and it is not fast enough to respond to the calls. Then it will return the error Call rejected by callee. The solution is to retry the call whenever this error occurs, most of the time Excel will have finished with the current call and it will be able to handle the next one.

To easily implement this I use a ComWrapper class, the inspiration I got for this class comes from several Stackoverflow questions.

import time

from pywintypes import com_error

class ComWrapper:

    @staticmethod
    def wrap(func, *func_args, **kwargs):
        try:
            return func(*func_args, **kwargs)
        except com_error as e:

            if e.strerror == 'Call was rejected by callee.':
                print('com_error retrying ', e)
                time.sleep(0.5)
                self.wrap(func, *func_args, **kwargs)

            raise

This class will wrap an existing method and when the pywin com_error with the message Call was rejected by callee. occurs it will wait for half a second and retry the same function with the same arguments.

If we want to wrap writing a value to a cell, this is how it is done:

def writeValue(workSheet, rowNumber, columnNumber, value):
    workSheet.Cells(rowNumber, columnNumber).Value = value

ComWrapper.wrap(writeValue, workSheet, 1, 1, "test")

First the property to set a certain value is wrapped in the writeValue method. This way the method can be passed to the ComWrapper. The first argument for the wrapper class is the method itself. Note that we are not calling that method, we pass it to the wrapper which will call it. Then all arguments that are normally passed to the method itself are passed to wrapper as well. The wrapper will retry the method call as long as the Call was rejected by callee. error is thrown by Excel.

Installing a node.js application as a Windows service

Peter Eysermans — Sun, 12 Apr 2020 20:01:25 +0000

It wasn't my intention when I started the first article but this has become a series of how to run node applications in production with IIS on Windows. These are the previous articles on the topic:

In the last article a node web application was deployed on a Windows server. With the help of the HTTP platform handler, IIS manages the node process for us. When it receives a request it will start the node process and pass the web request for node to handle it.

In some cases however we don't want to expose the node.js web application via IIS. I have built an internal API in the past which should not be accessible from the outside for security reasons. The internal API is only consumed by other applications running on the same server. In this case, we can't rely on IIS to manage the node process for us as IIS would expose the web application to the internet.

We need an alternative to keep the node process running to make the internal API available on the server via localhost. PM2 can manage the node process and keep it up and running. Unfortunately I did not find a reliable way to start PM2 whenever the Windows Server restarts. Every time the server restarted, the internal API was down and had to be manually started.

Luckily there is a NPM package node-windows which can install a node application as a Windows service. This service can be automatically started when the server restarts.

This is the Hello World sample from the Express website, we will install it as a Windows service:

const express = require('express')
const app = express()
const port = 3000

app.get('/', (req, res) => res.send('Hello World!'))

app.listen(port, () => console.log(`Example app listening at http://localhost:${port}`))

The node-windows NPM package can do this for us. Run the following commands

npm install -g node-windows
npm link node-windows

Once the package is installed it can be used to install the application as a service with the following node script:

var Service = require('node-windows').Service;

// Create a new service object
var svc = new Service({
  name:'Node application as Windows Service',
  description: 'Node application as Windows Service',
  script: 'C:\\temp\\test.js'
});

// Listen for the "install" event, which indicates the
// process is available as a service.
svc.on('install',function(){
  svc.start();
});

svc.install();

Just run the script as any other node script:

node install-windows-service.js

If User Account Control (UAC) is enabled on Windows you will have to give permission a few times to complete the installation. Once this script has finished the service is installed and the application is running. You can find the service in the Services dialog. It will have the name that you have passed to the Service class in the node script.

If the service ever needs to be uninstalled, the Service class also has an uninstall method:

var Service = require('node-windows').Service;

// Create a new service object
var svc = new Service({
  name:'Node application as Windows Service',
  description: 'Node application as Windows Service',
  script: 'C:\\temp\\test.js'
});

// Listen for the "uninstall" event so we know when it's done.
svc.on('uninstall',function(){
  console.log('Uninstall complete.');
  console.log('The service exists: ',svc.exists);
});

// Uninstall the service.
svc.uninstall();

This can also be run as any other node script:

node uninstall-windows.service.js

Because the Windows service is set to start automatic, it will start every time the server is restarted. Exactly as expected. Happy deploying!

This is a cross post from my own blog.

Using HTTP platform handler to host a node.js application via IIS

Peter Eysermans — Fri, 13 Mar 2020 22:11:48 +0000

About a year ago I wrote about hosting a Node.js application via IIS. It uses IIS as a reversed proxy to route traffic from IIS to the node.js webserver. To manage the node.js process pm2 was used. Unfortunately I had some problems restarting the pm2 process when the server restarted. This meant downtime everytime the server was restarted until I manually resurrected pm2.

The original post got a lot of comments and in one of them DavidWhit mentioned that the HTTP Platform handler can be used to manage the node.js process. This IIS module will manage a given process and proxy requests to the process it manages. It acts as a reverse proxy and it manages the process, it's even better than the previous solution. It's not limited to node.js processes, it can manage any process. This also makes it a good solution to host Ruby or other platforms on Windows.

I am assuming you already have a node application running, if not please check the previous post to create a simple hello world example.

To install the module, download the installer and run it on your server. This module can only be configured using a web.config, there is no GUI element in the IIS Manager to configure it. Add a web.config in the root of the website if there isn't one yet and copy over this configuration.

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <system.webServer>
    <handlers>
      <add name="httppPlatformHandler" path="*" verb="*" modules="httpPlatformHandler" resourceType="Unspecified" />
    </handlers>
    <httpPlatform stdoutLogEnabled="true" stdoutLogFile=".\node.log" startupTimeLimit="20" processPath="C:\Program Files\nodejs\node.exe" arguments=".\app.js">
            <environmentVariables>
                <environmentVariable name="PORT" value="%HTTP_PLATFORM_PORT%" />
                <environmentVariable name="NODE_ENV" value="Production" />
            </environmentVariables>
        </httpPlatform>
  </system.webServer>
</configuration>

You should check if the paths in the configuration file are correct. Once the file has been saved, that's it. If you visit the the URL configured in the bindings in IIS it will now route the traffic to the node.js webserver which will be managed by IIS.

More info:

This is a cross post from my own blog.

Connecting to a Microsoft SQL Server database on macOS using Python

Peter Eysermans — Thu, 08 Aug 2019 10:45:09 +0000

There are always situations where I want to automate small tasks. I like using Python for these kind of things, you can quickly get something working without much hassle. I needed to perform some database changes in a Microsoft SQL Server database and wanted to connect to it using Python. On Windows this is usually pretty straight forward. But I use macOS as my main operating system and I had some hurdles along the way so here is a quick how to.

Preparing

I found an article with the same topic from 2017. If Homebrew isn't installed yet let's do that first. It's an excellent package manager for macOS. Paste the following command in the terminal to install it:

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Once finished run the following commands to install the Microsoft ODBC Driver 13.1 for SQL Server. This driver will be used to connect to the MS SQL database.

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql@13.1.9.2 mssql-tools@14.0.6.0

Python package: pyodbc or pypyodbc

With the driver installed, we'll be using a Python package that uses the driver to connect to the database. There are two frequently used packages to connect in Python: pyodbc and pypyodbc. pypyodbc is a pure Python re-implementation of pyodbc. The main thing I took a way was that pypyodbc is easier to set up on different platforms. I could not get pyodbc working, it simply wouldn't connect to the database.

Installing pypyodbc can be done using pip, the python package installer.

pip install pypyodbc

Code

Now that the necessary components have been installed, let's open our favorite code editor and write code. The first thing that needs to be done is importing pypyodbc. The script starts with this:

import pypyodbc

Then we need a connection to the database:

sqlConnection = pypyodbc.connect(
                "Driver={ODBC Driver 13 for SQL Server};"
        "Server=<server IP address>;"
        "Database=<database>;"
        "uid=<username>;pwd=<password>");

Note that you have to replace four values in this code: server IP address, database , username and password. The value for the driver is a hard coded value which indicates what driver to use to connect to the database, this value points to the driver that was installed earlier.

Now all what rests is use the connection and run a query.

cursor = sqlConnection.cursor()
cursor.execute("select * from Values")

The cursor now contains the result of our query, the property cursor.rowcount returns the number of rows the query returned. It's now possible to loop through the rows and access the different columns:

for row in cursor:
    print(cursor)
    # first column
    firstColumn = row[0]
    # ...

When we're done, we need to clean up the cursor and database connection by closing it.

cursor.close()
sqlConnection.close()

And that's it, save the file and use the python <filename>.py or python3 <filename.py> command, this depends on your configuration, to run. Here is the entire script:

import pypyodbc

sqlConnection = pypyodbc.connect(
                "Driver={ODBC Driver 13 for SQL Server};"
        "Server=<server IP address>;"
        "Database=<database>;"
        "uid=<username>;pwd=<password>");

cursor = sqlConnection.cursor()
cursor.execute("select * from Values")

for row in cursor:
    print(cursor)
    # first column
    firstColumn = row[0]
    # ...

cursor.close()
sqlConnection.close()

The with syntax can also be used to automatically close the cursor and the connection, this is another way of writing the same script:

import pypyodbc

with pypyodbc.connect(
        "Driver={ODBC Driver 13 for SQL Server};"
        "Server=<server IP address>;"
        "Database=<database>;"
        "uid=<username>;pwd=<password>") as sqlConnection:

    with sqlConnection.cursor() as cursor:

        cursor.execute("select * from Values")

        for row in cursor:
            print(cursor)
            # first column
            firstColumn = row[0]
            # ...

If you're looking for some more reading on the topic:

Python HOW: Connect to, and Manage a Database

Things I wish I knew before I got started with CosmosDB

Peter Eysermans — Wed, 31 Jul 2019 20:59:27 +0000

Lately I've been working on a project using Cosmos DB on Azure. I really like the simplicity of a document DB. However there were some things I learned while on the job which I wished somebody told me before starting with it.

Cosmos DB?

Cosmos DB is Microsoft's flavoured document DB, you could call it Microsoft's Mongo DB that runs on Azure. On contrary to a normal SQL database, a document DB stores documents in the database. There is no schema, so you don't need to define columns and data types. Instead you pass a document to the document database and it will store it in JSON for you. This also means that you can add and remove properties on a document without having to change other documents which store similar information. An example: if there are 2 documents in the database which represent a User, the first User can have an address and it's perfectly possible for the second User to not have an address.

First user:

{
    id: "41bb417e-c942-4df2-96f0-cd2c3e1b2f91",
    name: "User 1",
    street: "My street",
    number: 55,
    postalCode: 1234,
    city: "Antwerp"
}

Second user:

{
    id: "cc4f3a5b-5e1c-4cdf-8167-dbd363538e26",
    name: "User 2"
}

Now we got the basics, let's look into some operations.

Parameterized query

Let's start with the very basic. If you'd like to run a query using parameters with C#, the SqlQuerySpec class can be used and passed to the DocumentClient. Both classes can be found in the Microsoft.Azure.DocumentDB package.

var querySpec = new SqlQuerySpec {
    QueryText = "select * from c where c.id = @id",
    Parameters = new SqlParameterCollection {
        new SqlParameter { 
            Name = "@id",
            Value = userId
        }
    }
}

// These properties can be found in the Azure portal.
var documentDbUri = "cosmosdb-uri";
var databaseId = "cosmosdb-database";
var collectionId = "cosmosdb-collection";

var documentClient = new DocumentClient(new Uri(documentDbUri), documentDbKey);
var database = documentClient.CreateDatabaseQuery().FirstOrDefault(d => d.Id == databaseId);
var collection = documentClient.CreateDocumentCollectionQuery().FirstOrDefault(c => c.Id == collectionId);
var queryResult = documentClient.CreateQuery(collection.DocumentsLink, query);

ARRAY_CONTAINS

This function's name pretty much speaks for itself. If a document has a property which is an array, ARRAY_CONTAINS can be used in a query to check if the array contains a certain value.

An example, this is our document:

{
    id: "9476d2bb-6f19-483f-9a59-446ddc693050",
    name: "Jane Doe",
    roles: [
        "manager",
        "reporting"
    ]
}

To query all the users that have the manager role:

select * from c where ARRAY_CONTAINS(c.roles, "manager")

If the property is an array of objects, you can also pass an object to the ARRAY_CONTAINS function. More info can be found in this Stackoverflow question

SELECT  *
FROM    food as f
WHERE   ARRAY_CONTAINS(f.servings, {"description":"bar"}, true)

Another great use of this function is using it as an alternative for the traditional SQL in keyword. If you would want to check if an ID is in an array that is passed as a parameter you would write something like this:

SELECT *
FROM Orders
WHERE CustomerId in (@customerIds)

@customerIds would then be an array of ids which is passed from C#.

To do a similar query on a CosmosDB instance, ARRAY_CONTAINS can be used. The array is passed as a parameter and ARRAY_CONTAINS can be used to check if the id is in the given parameter.

var customerIds = new List<int> { 5, 6, 7, 8 };

var querySpec = new SqlQuerySpec {
    QueryText = "select * from c where ARRAY_CONTAINS(@customerIds, c.customerId)",
    Parameters = new SqlParameterCollection {
        new SqlParameter { 
            Name = "@customerIds",
            Value = customerIds
        }
    }
}

Count

If you want to know how many records are in the result of a given query, there are two ways of getting the result. Using the LINQ Count method.

// These properties can be found in the Azure portal.
var documentDbUri = "cosmosdb-uri";
var databaseId = "cosmosdb-database";
var collectionId = "cosmosdb-collection";

var documentClient = new DocumentClient(new Uri(documentDbUri), documentDbKey);
var database = documentClient.CreateDatabaseQuery().FirstOrDefault(d => d.Id == databaseId);
var collection = documentClient.CreateDocumentCollectionQuery().FirstOrDefault(c => c.Id == collectionId);

var count = documentClient.CreateDocumentQuery<User>(collection.DocumentsLink)
                          .Where(u => u.city == "Antwerp")
                          .Count();

Or you can use the COUNT keyword in a SQL query, this query can be ran using a parameterized query.

SELECT VALUE COUNT(d) FROM d where city = 'Antwerp'

Client

Although the Azure portal allows to do any kind of operation on the document database, it's a very clunky way to work. I'm a big fan of using a dedicated client. Although the Microsoft Azure Storage Explorer still has some rough edges, the Cosmos DB support is still in preview, it works well. And besides managing documents in a Cosmos DB on Azure you can also manage your storage account.

Another option is the open source Cosmos DB explorer but it has less options than the Storage Explorer.

Like keyword

In traditional SQL the like keyword can be used to filter a string field on a certain value. CosmosDB foresees in equivalents for the like keyword. Let's go over the different options.

Show all rows where a specific column contains a certain value

SQL:

select * from Table where Column like '%value%'

CosmosDB:

select * from d where CONTAINS(d.Column, 'value')

Show all rows where a specific column starts with a certain value

SQL:

select * from Table where Column like 'value%'

CosmosDB:

select * from d where STARTSWITH(d.Column, 'value')

Show all rows where a specific column ends with a certain value

SQL:

select * from Table where Column like '%value'

CosmosDB:

select * from d where ENDSWITH(d.Column, 'value')

Conclusion

As you can see if you compare CosmosDB with a relational SQL database there are some things that work quite different. I hope this gives you a head start with Cosmos DB. Feel free to ping me if you have any other tips or remarks.

Testing request permissions in an Android application

Peter Eysermans — Mon, 15 Jul 2019 11:12:02 +0000

Originally posted on the 15th of july 2019 on my own blog.

While working on an Android application I wanted to test permission requests. This application needed the location permission, typically this is code that is written once and when it is marked as done never looked at again. I wanted to create some UI tests to ensure that everything works and will still work in the future as expected.

Let's start this off with creating a sample application. It's written in Kotlin with API 23 as minimum API level and it also uses the androidx.* artifacts.

Following permissions are added in the AndroidManifest.xml file:

<uses-permission android:name="android.permission.ACCESS_FINE_LOCATION" />
<uses-permission android:name="android.permission.ACCESS_COARSE_LOCATION" />

When the activity is created, the app will check if the location permissions have been granted. If they haven't been granted yet, the app will request them. When the permissions are denied, the app will show Permissions denied to the user. When the permissions are granted, it will show Permissions granted. Pretty straightforward.

This is the overridden onCreate method:

override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        if (ContextCompat.checkSelfPermission(this, Manifest.permission.ACCESS_FINE_LOCATION) 
               != PackageManager.PERMISSION_GRANTED && 
            ContextCompat.checkSelfPermission(this, Manifest.permission.ACCESS_COARSE_LOCATION) 
               != PackageManager.PERMISSION_GRANTED) {

            requestPermissions(
                arrayOf(
                    Manifest.permission.ACCESS_FINE_LOCATION,
                    Manifest.permission.ACCESS_COARSE_LOCATION
                ), LocationPermissionRequestCode)
        }
    }

This code checks if the ACCESS_FINE_LOCATION and the ACCESS_COARSE_LOCATION permissions have been granted. If not they will be requested from the user. The LocationPermissionRequestCode variable is a constant to identify the request. In the sample application it is set to 1.

Next we need to handle the response of the user. To do this, there is a method which the activity provides that can be overridden. The onRequestPermissionsResult method is overridden and a switch statement determines for which request the response is received. Note that the same LocationPermissionRequestCode variable is used as above. According to the result of the request the text of the feedbackLabel is changed.

override fun onRequestPermissionsResult(requestCode: Int, permissions: Array<out String>, grantResults: IntArray) {

        when(requestCode) {
            LocationPermissionRequestCode -> {

                if((grantResults.isNotEmpty() && grantResults[0] == PackageManager.PERMISSION_GRANTED)) {
                    feedbackLabel.text = "Permissions granted"
                } else {
                    feedbackLabel.text = "Permissions denied"
                }
                return
            }
            else -> {

            }
        }
    }

Note: to access the feedbackLabel I'm using the Kotlin Android Extensions.

Now the sample app is working let's get to the testing part. Code like this is often written once, tested once and then never touched again. A developer will grant the permissions and go on with his life. If a bug creeps in it will be noticed too late or not at all. So let's write some UI tests to verify that the app behaves as expected. UI testing on Android is often done using Espresso and it works quite well. There even is an Espresso test recorder built into Android Studio to quickly record tests.

Let's record our first Espresso test, if the user denies the permission the feedbackLabel should read Permissions denied. In Android Studio select Run > Record Espresso Test from the menu.

The test recorder will open and a log of all actions that have happened on the device will appear. After clicking the Deny button assert that the feedbackLabel has the expected value.

If Espresso has not been added to the gradle file of the application, Android Studio will propose to do it for you.

Unfortunately Espresso can't be used for this kind of test. Espresso only works on the current package and the Allow and Deny buttons from the Permissions dialog are from another package. As you can see in the test record, the click on the Deny button is not recorded to the list of actions.

There is however a solution, next to Espresso there is also the UI Automator testing framework. This framework has no problem with accessing elements from another package. We can't use the test recorder so the test will have to be coded but this is pretty straightforward. This line in the gradle file will add UI Automator:

androidTestImplementation 'com.android.support.test.uiautomator:uiautomator-v18:2.1.3'

And the test becomes:

@RunWith(AndroidJUnit4::class)
class MainActivityTests {

    private var device : UiDevice? = null

    @get:Rule
    var mainActivityTestRule = ActivityTestRule(MainActivity::class.java)

    @Before
    fun setUp() {
        this.device = UiDevice.getInstance(InstrumentationRegistry.getInstrumentation())
    }

    @Test
    fun testFeedbackLocationPermissionDenied() {
        val denyButton = this.device?.findObject(UiSelector().text("DENY"))
        val permissionDeniedMessage = this.device?.findObject(UiSelector().text("Permission denied"))

        denyButton!!.click()

        assert(permissionDeniedMessage!!.exists())
    }

    @Test
    fun testFeedbackLocationPermissionAllowed() {
        val allowButton = this.device?.findObject(UiSelector().text("ALLOW"))
        var permissionAllowedMessage = this.device?.findObject(UiSelector().text("Permission allowed"))
        allowButton!!.click()
        assert(permissionAllowedMessage!!.exists())
    }
}

The testFeedbackLocationPermissionAllowed test will check the message when the permission is allowed, the testFeedbackLocationPermissionDenied will check the message when the permission is denied.

There is however a problem with these tests. Once the permission has been allowed, the tests will fail when they are run again. If you think about it, it's not surprising. Because the permission is already granted, the permission popup will no longer be shown the second, third, fourth, ... time the tests are run.

To fix this the granted permissions need to be cleared every time the tests are run. Clearing the permission can be done using pm revoke. These commands are added to the After method to clean up the permissions.

@After
fun tearDown() {
   InstrumentationRegistry.getInstrumentation().uiAutomation.
       executeShellCommand("pm revoke ${InstrumentationRegistry.getInstrumentation().targetContext.packageName} android.permission.ACCESS_COARSE_LOCATION")

   InstrumentationRegistry.getInstrumentation().uiAutomation.
       executeShellCommand("pm revoke ${InstrumentationRegistry.getInstrumentation().targetContext.packageName} android.permission.ACCESS_FINE_LOCATION")
    }
}

When the tearDown executes logcat logs the following:

2019-07-15 11:09:43.639 21138-21154/com.eysermans.permissionuitesting W/UiAutomation: UiAutomation.revokeRuntimePermission() is more robust and should be used instead of 'pm revoke'

So instead of using pm revoke we should use revokeRuntimePermission.

@After
fun tearDown() {
    InstrumentationRegistry.getInstrumentation().uiAutomation.revokeRuntimePermission(
        InstrumentationRegistry.getInstrumentation().targetContext.packageName,
        Manifest.permission.ACCESS_COARSE_LOCATION)

    InstrumentationRegistry.getInstrumentation().uiAutomation.revokeRuntimePermission(
        InstrumentationRegistry.getInstrumentation().targetContext.packageName,
        Manifest.permission.ACCESS_FINE_LOCATION)
}

Unfortunately this didn't work either. Every time the testFeedbackLocationPermissionAllowed test ran it failed stating that more information can be found in logcat. However I did not find any additional error logging in logcat. It just did not work.

Test failed to run to completion. Reason: 'Instrumentation run failed due to 'Process crashed.''. Check device logcat for details

Let's explore another option, using the Android Test Orchestrator. Add the dependency to the gradle file:

androidTestUtil 'androidx.test:orchestrator:1.2.0'

Then add the following line to the defaultConfig section of the gradle file:

testInstrumentationRunnerArguments clearPackageData: 'true

And add the testOptions section to the android section:

testOptions {
    execution 'ANDROIDX_TEST_ORCHESTRATOR'

    unitTests {
        includeAndroidResources = true
    }
}

The gradle file now looks like this:

apply plugin: 'com.android.application'

apply plugin: 'kotlin-android'

apply plugin: 'kotlin-android-extensions'

android {
    compileSdkVersion 28
    defaultConfig {
        applicationId "com.eysermans.permissionuitesting"
        minSdkVersion 23
        targetSdkVersion 28
        versionCode 1
        versionName "1.0"
        testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
        testInstrumentationRunnerArguments clearPackageData: 'true'
    }
    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
        }
    }

    testOptions {
        execution 'ANDROIDX_TEST_ORCHESTRATOR'

        unitTests {
            includeAndroidResources = true
        }
    }
}

dependencies {
    implementation fileTree(dir: 'libs', include: ['*.jar'])
    implementation"org.jetbrains.kotlin:kotlin-stdlib-jdk7:$kotlin_version"
    implementation 'androidx.appcompat:appcompat:1.0.2'
    implementation 'androidx.core:core-ktx:1.0.2'
    implementation 'androidx.constraintlayout:constraintlayout:1.1.3'
    testImplementation 'junit:junit:4.12'
    androidTestImplementation 'androidx.test:runner:1.2.0'
    androidTestImplementation 'androidx.test.espresso:espresso-core:3.2.0'
    androidTestImplementation 'androidx.test:rules:1.3.0-alpha01'
    androidTestImplementation 'com.android.support.test.uiautomator:uiautomator-v18:2.1.3'

    androidTestImplementation 'androidx.test.ext:junit:1.1.1'
    androidTestUtil 'androidx.test:orchestrator:1.2.0'
}

The tearDown method can now completely be removed and the testFeedbackLocationPermissionAllowed method can now be run over and over again.

It took some work but this solution works. The permissions are cleared before every test. I preferred using a tearDown method because it is more straightforward. But unfortunately it did not work as expected.

The sample application is available on GitHub.

Using the Digispark as a cheap USB Rubber Ducky

Peter Eysermans — Fri, 05 Apr 2019 20:40:44 +0000

Disclaimer: This post is for fun, learning and experimenting. Use these tools wisely, in no way am I encouraging malicious use of them.

Originally posted on the 5th of april 2019 on my own blog.

Last week I came across an interesting device, the USB Rubber Ducky. It looks like a normal USB stick but when you plug it into a computer, it emulates a keyboard. You can load a script onto the Rubber Ducky and it will execute them as keystrokes on the computer where the stick is plugged in. Because it executes more than 1000 words per minute, the possibilities are endless from running pentest task to malicious things like installing a backdoor.

I wanted to play around with it but unfortunately shipping the stick to Europe/Belgium costs almost as much as the stick. Luckily there is a post on Hackernoon on how to make your own USB Rubber Ducky with a Digispark. The Digispark is similar to the Arduino but it's cheaper, smaller and less powerful. The end result actually looks better because it is much smaller than the Rubber Ducky. One drawback, the Digispark does not have a micro SD slot so there is no disk space to use.

Here is it next to a pen. I'm still amazed how small it is.

Buying these low budget components can be challenging. I have the impression that they are much easier and cheaper to get in the States. In some cases there are alternatives on Amazon Germany. But the Digispark was too cheap and shipping costs were too high. Eventually I found them on a local webshop for 6,49 €. I needed some other things so I could reduce the shipping costs.

To get started there are a lot of resources on the internet. But most of the scripts and blog posts are targeted for Windows. However I found an excellent blog post with a hello world example on how to set up the Digispark as a Rubber Ducky on Mac OS X. This is the hello world script that will be used:

#include "DigiKeyboard.h"

boolean didRun = false;

void setup() {}

void loop() {
  while (didRun == false) {
    DigiKeyboard.sendKeyStroke(KEY_SPACE, MOD_GUI_LEFT);
    DigiKeyboard.delay(500);
    DigiKeyboard.println("terminal");
    DigiKeyboard.delay(500);
    DigiKeyboard.println("say Hello World");
    didRun = true;
  }
}

This script uses the default shortcut to activate Spotlight, types terminal and hits enter. This should open the terminal, after waiting half a second it types and executes the command say Hello World. When I tried uploading this script via the Arduino IDE, it could never find my Digispark.

I was using an Apple USB to USB-C adapter to connect the Digispark to my Macbook pro. After some googling I discovered that the middle pins in the USB connector of the Digispark are quite short and that in some cases it won't connect properly. After digging up a USB extension cable the Digispark connected and the script uploaded.

So if the device is now inserted in a target machine, the script will run. While testing the hello world script I realized there is quite a lot that can go wrong:

What if the Spotlight key shortcut has been changed?
On my laptop, when typing terminal in Spotlight another file was the top result. As a result, the script did absolutely nothing.
When testing the script in computer with an azerty keyboard layout, the characters were typed with a qwerty keyboard layout.

But in the end, it did work. The script ran within seconds from a device similar in size as a coin. Pretty cool. While doing some research, I found quite a lot of other resources and example scripts. Making a laptop saying Hello World is just the tip of the iceberg. Some more resources:

Hosting a Node.js application on Windows with IIS as reverse proxy

Peter Eysermans — Wed, 27 Mar 2019 08:07:59 +0000

Unfortunately a lot of companies are still stuck with Windows servers. Nobody ever got fired for choosing Microsoft, right. As a developer this can be frustrating because choosing a server technology is usually limited to ASP.Net. I have experimented with hosting Node.js applications on a Windows server by using iisnode. But it is a pain to get up and running, setting the correct permissions is a time consuming chore. Microsoft has taken control of the development of the project but I get the feeling it's not very active any more. There are several Stackoverflow questions where people just give up configuring it.

So I wanted to go another route. What if we could use the Node.js web server and use IIS as a reverse proxy to route traffic to the Node.js web server? We could ditch iisnode and hopefully have a more reliable solution for hosting Node.js web applications.

First we need a small test project, this hello world Node.js Express application will do:



var express = require('express');
var app = express();

app.get('/', function (req, res) {
  res.send('Hello World!');
});

app.listen(3000, function () {
  console.log('Example app listening on port 3000!');
});

To be able to run this, you need to install Node.js on your server. Once it's installed, you can run the test application by opening a command prompt and typing node app.js. If everything goes well you should now be able to access the test application via http://localhost:3000 on your local server.

To configure IIS as reverse proxy you need to install the URL Rewrite extension and the Application Request Routing extension. The URL Rewrite extension allows you to define rules to enable URLs that are easier for users to remember and for search engines to find. The Application Request Routing extension enables scalibility features: load balancing, rule-based routing and more.

Once these extensions are installed, you can begin configuring IIS. Open the Internet Information Services (IIS) Manager by opening the run window and typing the inetmgr command. Select the site for which you want to set up the reverse proxy and open the URL Rewrite extension.

Add a new rule and select the Reverse Proxy template.

Enable proxy functionality when you are prompted for it.

Add the address of your node.js website, don't forget to include the port, to the reverse proxy rules.

Once the rule has been added, the reverse proxy configuration works.

The last piece that's needed is a reliable way of running the Node.js application. Starting it via the command prompt and keeping the window open is not a durable solution. If someone logs on to the server and closes the window, the website goes down. pm2 is a Node.js process manager, it can be used to keep applications running. Installing pm2 is easy with npm:



npm install -g pm2

Once installed, we can use these commands to manage our processes:

pm2 start app.js: start our Node.js application
pm2 stop: stop a running process
pm2 restart: restart a running process
pm2 list: list all running processes

pm2 can do so much more, check out their website for more info. My own blog is currently running on this setup.

This is a cross post from my own blog.

DEV Community: Peter Eysermans

Creating an iOS 14 widget showing health data

Configuring HealthKit

Fetching sleep data

Showing sleep data

Widget

Manipulating an Excel file with Python

Writing a value to Excel

Generating a PDF from the Excel file

Performance

Disable display alerts

XLSB file type

Compress images

Reading and writing values using Range

Disable automatic calculation

Turn off screen updating

Call rejected by callee

Installing a node.js application as a Windows service

Using HTTP platform handler to host a node.js application via IIS

Connecting to a Microsoft SQL Server database on macOS using Python

Preparing

Python package: pyodbc or pypyodbc

Code

Things I wish I knew before I got started with CosmosDB

Cosmos DB?

Parameterized query

ARRAY_CONTAINS

Count

Client

Like keyword

Show all rows where a specific column contains a certain value

Show all rows where a specific column starts with a certain value

Show all rows where a specific column ends with a certain value

Conclusion

Testing request permissions in an Android application

Using the Digispark as a cheap USB Rubber Ducky

Hosting a Node.js application on Windows with IIS as reverse proxy

Reading and writing values using `Range`