Home Automation with Power Platform #2: Raspberry PI, Serverless and Power Platform

by Scott Muniz | Aug 23, 2020 | Uncategorized

This article is contributed. See the original author and article here.

In my previous post, we discussed how Raspberry PI can turn into a remote controller, using the LIRC module. Throughout this post, I’ll be literally remote controlling even outside the home network, using Power Platform.

• Turning Raspberry PI into Remote Controller
• Turning on/off Home Appliances Using Raspberry PI and Power Platform

The sample codes used in this post can be found at this GitHub repository.

node.js API App Server

There is an npm package, called node-lirc. With this package, we can easily build a node.js application to handle the LIRC command.

Remote Controller Function

Let’s write a simple remote.js module. Create a function, called remote(). It takes the device name and command (line #5-9). What it does is to just run the LIRC command, irsend SEND_ONCE <device> <command> (line #6). Then, create a command object of remoteControl that contains onSwitchOn and onSwitchOff functions.

    let lirc_node = require('lirc_node');

    lirc_node.init();
    
    const remote = (device, command) => {
        lirc_node.irsend.send_once(device, command, () => {
            console.log(command);
        });
    }
    
    const remoteControl = {
        onSwitchOn: (device) => {
            remote(device, "SWITCH_ON");
        },
    
        onSwitchOff: (device) => {
            remote(device, "SWITCH_OFF");
        }
    }
    
    module.exports = remoteControl;

API Server Application

We’ve got the remote controller module. This time, let’s build an API app that uses the remote controller module. If we use the express package, we can build the API app really quickly. Let’s have a look at the code below. it defines both endpoints for turning on and off appliances, /remote/switchon and /remote/switchoff respectively (line #6, 17). This example uses the GET verb, but strictly speaking, the POST verb is way closer. Finally, it allocates the port number of 4000 for this application (line #28).

    let express = require('express');
    let remote = require('./remote');
    
    let app = express();
    
    app.get('/remote/switchon', function(req, res) {
        let device = req.query.device;
        remote.onSwitchOn(device);
    
        let message = {
            "message": device + " turned on"
        }
    
        res.send(message);
    });
    
    app.get('/remote/switchoff', function(req, res) {
      let device = req.query.device;
      remote.onSwitchOff(device);
    
        let message = {
            "message": device + " turned off"
        }
    
        res.send(message);
    });
    
    app.listen(4000, () => {
      console.log("app started !!");
    });
    
    module.export = app;

Now we’ve got the API server app ready to run. Double check package.json so that the API can be run with the following command (line #4).

    {
        ...
        "scripts": {
            "start": "node app.js"
        },
        ...
    }

Run the command, npm run start to run the API app.

The API app is ready to receive requests. Open a web browser and send a request for the remote controller.

We’ve now got the API app up and running for the remote controller.

Secure Tunnelling to Home Network – ngrok

The API running on Raspberry PI resides within the home network. It means that, unless we open a secure channel, it can’t be accessible from the Internet because my home network uses the private IP address range like 172.30.xxx.xxx. There are many ways to sort out this issue, but I’m going to use ngrok to open a secure tunnel that allows only specified services. For more details, refer to their official documents. First of all, download the application and install it, then run the following command to open a secure tunnel.

    ./ngrok http 4000

The UI of ngrok looks like the following picture. As we use its free version, we can’t use a custom domain but are only allowed the randomly generated URL, every time we run it. The picture says it’s now https://48b6ff595189.ngrok.io, but the next time, it’ll be different.

With this ngrok URL, we can access to the home network from the Internet. Let’s have a look at the video:

Controlling Remote Controller with Azure Functions

The purpose of the secure tunnelling is to run the remote controller from Azure Functions on the Internet. We can quickly build an Azure Functions app with node.js. The following function code uses either query string parameters or request body payload to populate both device and power values (line #6-7). In addition to that, it gets the ngrok-generated URL from the environment variables (line #9-11). By combining them (line #14), the function app calls the remote controller on Raspberry PI (line #16).

    const axios = require('axios');

    module.exports = async function (context, req) {
        context.log('JavaScript HTTP trigger function processed a request.');
    
        let device = (req.query.device || (req.body && req.body.device));
        let power = (req.query.power || (req.body && req.body.power));
    
        let baseUri = process.env.REMOTE__BASE_URI;
        let switchOnEndpoint = process.env.REMOTE__ENDPOINTS__SWITCHON;
        let switchOffEndpoint = process.env.REMOTE__ENDPOINTS__SWITCHOFF;
        let endpoint = power == 'on' ? switchOnEndpoint : switchOffEndpoint;
    
        let requestUri = baseUri + endpoint + "?device=" + device;
    
        let response = await axios.get(requestUri);
    
        context.res = {
            // status: 200, /* Defaults to 200 */
            body: response.data
        };
    }

Let’s run the function app on my local machine and send a request to the remote controller through ngrok.

Deploy the function app to Azure and run it again. Everything goes alright.

Controlling Remote Controller through Power Platform

Azure Functions simply works as a proxy that calls the ngrok-secured remote controller API. It’s OK we stop right here, as long as we use a web browser. But let’s be fancier, using Power Platform. Power Automate handles the workflow of the remote controller by calling the Azure Functions app and Power Apps defines the behaviours how to “remote control” home appliances.

We can call Azure Functions directly from Power Apps, but the separation of concerns is also applied between Power Automate and Power Apps – control the workflow and control the behaviours. Let’s build the workflow first.

Power Automate Workflow

First of all, create a workflow instance at Power Automate. As Power App only triggers it, it sets the Power App as a trigger.

Once the instance is created, it opens the visual designer screen. Can you see the Power App trigger at the top?

As this workflow simply calls the Azure Functions app, search up the HTTP action.

We created the Azure Functions endpoint to accept both GET and POST verbs. When we test it on the browser, we had to send the request through the GET verb. In Power Automate, it’s better to use the POST verb to send requests to the function app.

Set the function app endpoint URL and header like below. You can see both HTTP_Body and HTTP_Body_1 at the bottom-right side corner. Both represent device and power, respectively, which will be explained later in this post.

Search up the HTTP response action to return the execution result from the function app endpoint to Power App.

Put the entire HTTP action response to the response action.

Click the Show advanced options button to define a JSON schema for Power Apps to recognise what the response might look like:

Click the Generate from sample button and paste the response payload taken from the previous example.

Then, it automatically generates the JSON schema.

We’ve now got the Power Automate workflow that calls the Azure Functions endpoints.

Power App Remote Controller for All Home Appliances

Let’s build the “real” remote controller with Power Apps. Add two buttons and one label controls. Set the name of “ON” to the first button and “OFF” to the second button.

Connect the Power Automate workflow of Home Appliances Remote Controller with the ON button. The HomeAppliancesRemoteController.Run() function represents the workflow that takes two parameters of tv and on. Do you remember both HTTP_Body and HTTP_Body_1 from the Power Automate workflow that takes device and power respectively? These tv and on are the ones.

Like the ON button, the OFF button calls the same workflow with different parameters – tv and off.

The text label shows the result of the call.

We just use only one appliance, that is TV. But if we add two extra buttons for an air-conditioning system, we can send parameters like winia and on, or winia and off.

Save and publish the Power App.

On our mobile phone, the Power App might look like this video:

If I run this app in front of the TV, it looks like the following video:

So far, we built an API app, opened a secure tunnel, and built an Azure Function app, Power Automate workflow and Power App. Through this Power App, we can now control our home appliances outside the home. As there are plenty of scenarios for Power Platform, I would really like you to run your idea. Are you fascinated? It’s your turn now.

This article was originally published on Dev Kimchi.

Create a table with the 1-click experience

by Scott Muniz | Aug 23, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Create a table easily and quickly using 1-click ingestion. 
One-click ingestion helps you ramp-up quickly to create database tables, mapping structures. Select data from different kinds of sources in different data formats to define the table schema. 

To access the wizard from the Azure Data Explorer web UI, right-click the database row in the left menu of the Azure Data Explorer web UI and select Create Table.

The service automatically generates schema, which you can then change. Add new mapping based on your data source.

Feel free to use this tool to explore at will, as you will be given the option to revert all changes.

Read more about one-click ingestion

SQL Server Extents, PFS, GAM, SGAM and IAM and related corruptions

by Scott Muniz | Aug 22, 2020 | Uncategorized

This article is contributed. See the original author and article here.

I’m going to start a series of posts talking about SQL Server allocation pages and corruption. Each post will have samples showing how SQL Server use these pages and scenarios of corruption.

The first post will talk about the Extents, PFS, GAM, SGAM and IAM and related corruptions, you can find all concepts from following two pages:

Pages and Extents Architecture Guide

Under the covers: GAM, SGAM, and PFS pages

As there are many concepts and samples, I’m going to discuss the topics in two or three posts.

1.Extents

Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.

SQL Server has two types of extents:

·         Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.

·         Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

Up to, and including, SQL Server 2014 (12.x), SQL Server does not allocate whole extents to tables with small amounts of data. A new table or index generally allocates pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents. However, starting with SQL Server 2016 (13.x), the default for all allocations in the database is uniform extents.

Sample 1: The first 8 pages are stored in mixed extent.

1)Create an empty database in SQL Server 2019 instance and enable the mixed extent allocation.

create database dbtest

go

alter database dbtest set MIXED_PAGE_ALLOCATION on

2)Create a heap table and insert one row.

create table heaptable1(c1 int, c2 varchar(8000))

insert heaptable1 values(1,REPLICATE(‘a’,1000))    

3)Let’s check how these rows and pages stored in SQL Server.

select ht1.c1,ht1.c2, p.file_id,p.page_id, is_mixed_page_allocation

From heaptable1 as ht1 CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) as p inner join sys.dm_db_database_page_allocations(db_id(),object_id(‘dbo.heaptable1′),null,null,’detailed’) as dddpa

on p.file_id=dddpa.allocated_page_file_id and

p.page_id=dddpa.allocated_page_page_id

Because there is only 1 row, in one page, it’s stored in mixed extent.

The page id is:(1:305).

2.PFS

Page Free Space (PFS) pages record the allocation status of each page, whether an individual page has been allocated, and the amount of free space on each page. The PFS has 1-byte for each page, recording whether the page is allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to 80 percent full, 81 to 95 percent full, or 96 to 100 percent full.

After an extent has been allocated to an object, the SQL Server Database Engine uses the PFS pages to record which pages in the extent are allocated or free. This information is used when the SQL Server Database Engine has to allocate a new page. The amount of free space in a page is only maintained for heap and Text/Image pages. It is used when the SQL Server Database Engine has to find a page with free space available to hold a newly inserted row. Indexes do not require that the page free space be tracked, because the point at which to insert a new row is set by the index key values.

A new PFS, GAM or SGAM page is added in the data file for every additional range that it keeps track of. Thus, there is a new PFS page 8,088 pages after the first PFS page, and additional PFS pages in subsequent 8,088 page intervals. To illustrate, page ID 1 is a PFS page, page ID 8088 is a PFS page, page ID 16176 is a PFS page, and so on. There is a new GAM page 64,000 extents after the first GAM page and it keeps track of the 64,000-extents following it; the sequence continues at 64,000-extent intervals. Similarly, there is a new SGAM page 64,000 extents after the first SGAM page and additional SGAM pages in subsequent 64,000 extent intervals. The following illustration shows the sequence of pages used by the SQL Server Database Engine to allocate and manage extents.

Sample 2: PFS page detail, how does SQL Server map the value ‘1’,’2’,’3’,’4’ to ’50 Percent full’,’80 Percent full’,’95 Percent full’ and ‘100 Percent full’.

1)The first PFS page is (1:1).

2)Run DBCC PAGE to display the content:

As the size of row is 1KB, the usage of the page is around 1000.0/8096=12%, so it’s marked as 50 percent full.

Let’s display the content again using the parameter 1 instead of 3.

The value is 61.

3)Let’s keep inserting another 4 rows.

insert heaptable1 values(2,REPLICATE(‘b’,1000))    

insert heaptable1 values(3,REPLICATE(‘c’,1000))    

insert heaptable1 values(4,REPLICATE(‘d’,1000))    

insert heaptable1 values(5,REPLICATE(‘e’,1000))  

4)The total size of 5 rows are around 5KB, the usage is around 5000.0/8096=61%, which is greater than 50% , but less than 80%, so it’s marked as 80 percent full.

Let’s display the content again using the parameter 1 instead of 3.

The value is 62.

5)Let’s keep inserting another 2 rows.

insert heaptable1 values(6,REPLICATE(‘f’,1000))    

insert heaptable1 values(7,REPLICATE(‘g’,1000))

6)The total size of 7 rows are around 7KB, the usage is around 7000.0/8096=86%, which is greater than 80% , but less than 85%, so it’s marked as 95 percent full.

Let’s display the content again using the parameter 1 instead of 3.

The value is 63.

Takeaway: Because SQL Server stop storing data in a page of heap when it’s 95%, you won’t see ‘100 percent full’ even if you continue inserting a row with size 1KB.

8)For 100 percent full, please see sample 9.

3.GAM

Global Allocation Map (GAM)
GAM pages record what extents have been allocated. Each GAM covers 64,000 extents, or almost 4 gigabytes (GB) of data. The GAM has 1-bit for each extent in the interval it covers. If the bit is 1, the extent is free; if the bit is 0, the extent is allocated, either in GAM or SGAM.

Sample 3: GAM explore

1)Let’s check the GAM.

2)What does the entry ‘(1:0)        – (1:304)      =     ALLOCATED’ stand for?

    (1:0)   is the first page of first extent in this range.

    (1:304) is the first page of last extent in this range. (1:311) is the last page of this extent.

     All the extents within the range, including the first extent and last extent, are allocated as mixed extent or uniformed extent. So do the page(1:0)~page(1:311) are in allocated.

Takeaway: By check GAM page, we can tell if the extents and pages are allocated, but we can’ tell if they are mixed extent or uniformed extent.

4.SGAM

Shared Global Allocation Map (SGAM)
SGAM pages record which extents are currently being used as mixed extents and also have at least one unused page. Each SGAM covers 64,000 extents, or almost 4-GB of data. The SGAM has 1-bit for each extent in the interval it covers. If the bit is 1, the extent is being used as a mixed extent and has a free page. If the bit is 0, the extent is not used as a mixed extent, or it is a mixed extent and all its pages are being used.

Sample 4: SGAM explore

Let’s check the GAM.

2)What does the first entry ‘(1:0)        – (1:296)      = NOT ALLOCATED’ stand for?

   (1:0)   is the first page of first extent in this range.

   (1:296) is the first page of last extent in this range. (1:303) is the last page in this extent.

   Please note: ‘NOT ALLOCATED’ is confusing, you see the ‘NOT ALLOCATED’ when the corresponding bit is 0: the extent is not used as a mixed extent, or it is a mixed extent and all its pages are being used. Please see the Sample 7 for more detail.

3)What does the second entry ‘(1:304)      –              =     ALLOCATED’ stand for?

  (1:304)   is the first page of first extent in this range.

  At least this extent is allocated as mixed extent. So do the page(1:304)~(1:311).

4)What does the second entry ‘(1:312)      – (1:1016)     = NOT ALLOCATED’ stand for?

    (1:304)   is the first page of first extent in this range.

    (1:1016) is the first page of last extent in this range.

    All the extents within the range, including the first extent and last extent, are not allocated as mixed extent. So do the page(1:304)~page(1:1023).

Takeaway:

1) SGAM only shows a part of mixed extents, not all of them. Please see the Sample 7 for more detail.

2)‘NOT ALLOCATED’ is confusing, you see the ‘NOT ALLOCATED’ when the corresponding bit is 0: the extent is not used as a mixed extent, or it is a mixed extent and all its pages are being used. Please see the Sample 7 for more detail.

Sample 5: GAM detail

Let’s review the GAM page from different perspective.

Run DBCC PAGE again with the parameter 1 instead of 3.

The 0000381f are reserved, the valid record starts from the fifth byte:00

Let’s talk a look at 00000000 80

It’s hex, Let me convert to binary:

If the bit is 1, the extent is free; if the bit is 0, the extent is allocated, either in GAM or SGAM.

Because the bit of the extent 0~38(totally 39 extents) are all 0, are allocated, either in GAM or SGAM. so does the page 0~311(304+7).

The bit extent 39 is 1, so extent 39 is not allocated. The related page (1:312) is not allocated at all. As the rest of bits/bytes are 1,(f stands for 1111)

It exactly matches the content of DBCC Page with parameter 3.

Sample 6: SGAM detail

Let’s review the SGAM page from a different perspective.

Run DBCC PAGE again , but with the parameter 1 instead 3.

The 0000381f are reserved, the really record starts from the fifth byte:00

Let’s talk a look at 00000000 80

It’s hex, now convert to binary:

Because the bit of the extent 0~37(totally 38 extents) are all 0, these extents are not used as a mixed extent, or it is a mixed extent and all its pages are being used.

The pages in extent 0~37 are (1:0)~(1:303).

The first page of extent 37 is (1:296).

The bit of extent 38 is 1, so the extent is used as a mixed extent, so does the page(1:304)~page(1:311)

It exactly matches the content of DBCC Page with parameter 3.

Takeaway: You can tell if the extent is allocated by checking the GAM and SGAM page, but you can’t tell if it’s mixed extent or uniformed extent. You need to combine the info with IAM page. I’m going to cover the topic in next post.

Sample 7: Insert more rows to make the heaptable1 has 8 pages.

1)Insert another 7 rows, the size of each row is around 8KB.

insert heaptable1 values(8,REPLICATE(‘i’,8000))      –page 2

insert heaptable1 values(9,REPLICATE(‘i’,8000))      –page 3

insert heaptable1 values(10,REPLICATE(‘j’,8000))     –page 4

insert heaptable1 values(11,REPLICATE(‘k’,8000))     –page 5

insert heaptable1 values(12,REPLICATE(‘l’,8000))     –page 6

insert heaptable1 values(13,REPLICATE(‘m’,8000))     –page 7

insert heaptable1 values(14,REPLICATE(‘n’,8000))     –page 8

2)There are 8 pages now.

3)Extent and page type

select allocated_page_file_id as [FileID],allocated_page_page_id as [PageID],page_type_desc,extent_page_id/8 as ExtentID, is_mixed_page_allocation,

extent_page_id as [First Page in Extent],extent_page_id+7 as [LastPage in Extent],is_allocated

From  sys.dm_db_database_page_allocations(db_id(),object_id(‘dbo.heaptable1′),null,null,’detailed’)  order by allocated_page_page_id

These pages belong to two different mixed extents, which are expected.

4)GAM also reflects the change.

5)Let’s check the SGAM

‘(1:320)      –              =     ALLOCATED’ is marked as mixed extent, which makes sense.

However, we already know that the page(1:304)~page(1:311) are in mixed extent, but it’s marked ‘NOT ALLOCATED’. (Please review the Sample 4)

Sample 8: 100 Percent full. (Please review sample 2)

Page 310,311,320,321,322,323 and 324 are almost full because every row in pages is more than 8KB.

Let’s check the PFS page.

Display again using the parameter 1 instead of 3.