How a programmer sees a spreadsheet

Summary

Recently a non-programmer co-worker was giving me a hard time about not liking spreadsheets (Microsoft Excel or Google Sheets). He thinks it's the funniest thing that:
I HATE SPREADSHEETS!

If it's not obvious this is a rant with lots of explanation, there is much to be learned for new programmers and hopefully a few users can understand spreadsheets a little better.

There are no curse words but the tone is negative and/or cynical.

Background

First a little background, I've been a professional business application programmer for 20 years. In that time I've worked with a lot spreadsheets, everything from helping users create formula's to maintaining a VBA spreadsheet that runs 100's of queries against a MySQL database, then applies formulas to the results. I've also created a Google Sheet that does a mail merge and is used to send over 5,000 (non-SPAM) emails per day.

I've seen data loss, data corruption and tried to explain to users why these things happened. I understand the limits of a spreadsheet and have pushed those limits, it's not pure hate for spreadsheets it's about using them to solve the right problem.

How a programmer sees a spreadsheet

The most common request I receive is to "Import a spreadsheet into a database". This seems simple and shouldn't be hard because every program has this feature and the user has worked really hard on the spreadsheet and it has all the right fields and data for their task and it already works.

Process Questions:

• One time or scheduled import?
• Expected number of records?
• How many people edit the file?
• What is the source of the data?
• How do they change the data from the original?

Even once these questions are answered, it opens up so many more questions I've found it's easier to just ask for a copy of the spreadsheet so I can look at it myself.

Data Quality Questions:

• What is the actual file format? XLS, XLSX, CSV, Other?
• How many columns, rows?
• How many sheets?
• Are there column headers?
• Same number of column headers as columns?
• Does the data format look consistent?
• How should errors be handled?

With these questions answered I can usually answer yes or no, if I want to take on the task. If I have any questions about the task at this point the default answer is NO. Why? If my program fails because the user changed the spreadsheet it's my program that looks bad, and I lose trust with the users.

User enters bad data = Program Crashes = Decreased trust of users

Real Situations I've seen regarding spreadsheets:

• User made the spreadsheet prettier, and broke almost all of the code, because the code depended on certain data in certain places. The user actually performed the task manually for months before reporting the problem.
• 15+ digit numbers are truncated and get a zero at the end (Known Issue)
• Everything more than 65,536 Rows is deleted in XLS (Known Issue)

More Technical examples:

• I've received a CSV file with an XML formatted SOAP response, that was then parsed with Excel sub-string formulas that worked only on test data. It broke quickly when used on real data.
• CSV files that don't quote strings properly so using quote marks doesn't work ex. "60" TV" similar situation with peoples names with an apostrophe like O'Reilly.

Databases aka Structured Data

tl;dr Database data is HIGHLY structured, which improves data quality. 

Databases are a big topic and the only real thing to understand for this post is that they provide a very structured way to store data. Including tables, rows, columns, data types, as well as rules regarding unique values and the relationships between sheets/tables.

For example MySQL has 5 different data types to hold a whole number depending on the minimum and/or maximum value that will be stored. Whole numbers don't have any decimal places and can be positive or negative. There are other datatypes to handle everything else.

It sounds easy, simply choose the largest size, but choosing the min/max size of data can improve the quality of the data in your database. 

For example to store a persons age you shouldn't allow a negative number, but allowing a value greater than 1,000 is not valid. So an unsigned "TINYINT" can hold any value from 0 to 255, still a little big but it's the best match. 

Now lets look at a spreadsheet like a programmer

I've recreated the spreadsheet my co-worker wanted me to import with some dummy data.

Any programmers following along are already crying, here is why...

• 3 sets of column headings in the first 20 rows, note mixed case F3 and F11
• This suggests that it's manually maintained 
• Columns could have misspellings that are fixed in some places and not others
• How does my program know that row 11 is another column header and not another row of data?
• Black background rows may hide data from users, but doesn't hide it from a programmer.
• Colors wont be included in a CSV export.
• The City Name is 8 cells merged into 1. What row/column would it be in a CSV?
• Request Field is also merged
• I'd have to convert the x columns into a True/False column
• Column 6 appears to be a phone number
• Will it always be formatted as a raw number?
• How many different ways can a phone number be formatted?
• Should I store it as a number or a string?
• Column 7 is a name with both first and last in the same cell
• Do I store this as one or two database columns?
• What about middle names? Middle initials?
• Duplicates?
• F5 and F20 are identical, normal or typo?
• Duplicates in the email address, normal or typo?
• The "extra" heading in Column H has both a text name and a date
• Where should I store the extra header data? Same table? Another table?
• In the original spreadsheet some cells had comments. Ignore? Same table? Another table?

This spreadsheet didn't have any number examples where sometimes there is a money symbol (USD, EUR, etc), or a degree symbol or sometimes decimal sometimes a fraction. Basically things that a person can interpret easily while a program would have to test for each of these special cases.

This is the time to mention that any cell can truly accept any type of data. While there may be "conventions" used in the spreadsheet, if the user doesn't follow them the spreadsheet doesn't care. Many spreadsheets can actually have images inside of cells. 

In addition to everything noted here was the Tab names, I can't make this any worse by making it up, edited minimally for privacy: ch, ACS_Import, FY, January, February, March, April, May, June, July, July Special Circumstance, August, September, October, November, December, Sheet 15. This is only to mention that the tab names are not consistent and the ones selected for import would have to be specified in the import program.

As expected the monthly tabs were very similar to the example above, while the other tabs all had different formats even from each other.

MySQL Enable Remote Connections

Introduction

    This is something I've struggled with for about 5-6 hours now, all of the "helpful" websites have magic incantation command lines that are supposed to fix the problem, if they don't work you don't actually have any more knowledge to help you, so you just go looking for more magic to try.  Here I will attempt to improve this with a troubleshooting guide with some more details behind the commands used.

Possible Problems with Connecting to MySQL from another computer

• Network Hardware Failure
• MySQL Bind to an IP Address
• MySQL User Permissions
• Network Firewall (Not Complete)

I'm going to assume that the Network Hardware is working, which means that you can open a terminal from another computer. 

My configuration is an ODROID HC1 running Linux Ubuntu and MariaDB, I want to connect from my  Win10 Desktop with HeidiSQL. I'm using Putty to open a bash terminal to the HC1. Even if you are a Windows user I have to assume some knowledge with a navigating with a command line, primarily the 'ls' to list a directories contents and 'cd' to change to another directory, these commands should be enough. Optionally the 'mv' may be used to either move or rename a file. 

My main error is "Can't connect to MySQL server on <IP ADDRESS> (10061) 

I'm also assuming the MySQL (MariaDB) is already installed, but you can't connect remotely. You may of tried a bunch of things and have a very messed up environment and that is OK.

Troubleshooting tools to install

telnet client on both Ubuntu and Windows

• Ubuntu
• apt-get install telnet
• Windows 10
• Start button
• Type "Turn Windows" (Features On or Off)
• Check the box next to "Telnet Client"
• OK
• Restart

First steps are to make sure that it works on the Linux computer.

• Login to the remote Linux server with any terminal program (Win10 use Putty)
• >>mysql -u root -p
• password is blank by default, but if you set one use it.
• The default "host" is 'localhost' which is a unix socket
• SQL>select user, host, password from mysql.user;
• Don't forget the semi-colon at the end
• The password is hashed/encrypted but you can tell if it's set and if it's the same as another user.
• SQL>quit

At this point we have proven that the mysql server is installed and functioning and has at least a root user with localhost access. Now here is a tricky part mysql actually uses a unix socket to connect the client to the server, so there isn't any networking actually involved. 

• If any of the above steps failed maybe the mysql installation needs to be done/re-done

The easiest way to test if MySQL can be accessed outside of the local computer is with the telnet command. The following commands can be re-run at any stage of this article.

• telnet localhost 3306
• This uses the Unix Socket so it will work but doesn't prove anything. Although it does show you what you should see when you run the following command
• telnet <ip address> 3306
• If you see the same kind of thing you saw from the localhost version above, you can move on to the Network Firewall issues, or MySQL user permission issues

The next steps are to determine the configuration loaded by MySQL and where the values actually come from.

• >>mysqld --print-defaults
• One of issues was the bind-address was duplicated (see bold settings) the last one that is set is the value that is used. These all look like command line parameters, but they may come from a command line or from internal default values or from one of multiple configuration files. The challenge is figuring out which one.
• --bind-address=0.0.0.0 
• --character-set-server=utf8mb4 
• --collation-server=utf8mb4_unicode_ci 
• --character-set_server=utf8mb4 
• --collation_server=utf8mb4_unicode_ci 
• --user=mysql 
• --pid-file=/var/run/mysqld/mysqld.pid 
• --socket=/var/run/mysqld/mysqld.sock 
• --port=3306 
• --basedir=/usr 
• --datadir=/var/lib/mysql 
• --tmpdir=/tmp 
• --lc-messages-dir=/usr/share/mysql 
• --skip-external-locking 
• --bind-address=127.0.0.1 
• --key_buffer_size=16M 
• --max_allowed_packet=16M 
• --thread_stack=192K 
• --thread_cache_size=8 
• --myisam-recover=BACKUP 
• --query_cache_limit=1M 
• --query_cache_size=16M 
• --log_error=/var/log/mysql/error.log 
• --expire_logs_days=10 
• --max_binlog_size=100M 
• --character-set-server=utf8mb4 
• --collation-server=utf8mb4_general_ci
• >>ps -ef | grep 'mysql'
• This command will show the values that were actually passed on the command line.
  
• >>mysqld --verbose --help | grep -A 1 "Default options"
• This will list the location of all configuration files that were read when mysql started.
• NOTE: In many cases (all?) one of the config files may "includedir" configuration files from other locations.
• The configuration files for my system were in this order
• /etc/my.cnf
• /etc/mysql/my.cnf
• /etc/mysql/mariadb.cnf
• /etc/mysql/conf.d/*.cnf
• /etc/mysql/mariadb.conf.d/*.cnf
• ~/.my.cnf

Now review the list from print-defaults and figure out where each value comes from. Reminder: use 'sudo' to elevate your permissions in order to edit these text files, 'vi' is a good editor if you are familiar with it. If you are usually a Windows user or otherwise aren't sure 'nano' is a little easier to use. 

Examples:

• sudo vi /etc/my.cnf
• Look up a cheatsheet if you don't know the commands
• OR
• sudo nano /etc/my.cnf
• Ctrl+O and <Enter> to save the change
• Ctrl+X to exit nano

Here are my results:

/etc/my.cnf --bind-address=0.0.0.0 

/etc/mysql/conf.d/my.cnf --character-set-server=utf8mb4 

/etc/mysql/conf.d/my.cnf --collation-server=utf8mb4_unicode_ci 

/etc/mysql/conf.d/my.cnf --character-set_server=utf8mb4 

/etc/mysql/conf.d/my.cnf --collation_server=utf8mb4_unicode_ci 

command line --user=mysql 

command line --pid-file=/var/run/mysqld/mysqld.pid 
command line --socket=/var/run/mysqld/mysqld.sock 
command line --port=3306 
command line --basedir=/usr 
command line --datadir=/var/lib/mysql 

/etc/mysql/mariadb.conf.d/50-server.cnf --tmpdir=/tmp 

/etc/mysql/mariadb.conf.d/50-server.cnf --lc-messages-dir=/usr/share/mysql 

/etc/mysql/mariadb.conf.d/50-server.cnf --skip-external-locking 

/etc/mysql/mariadb.conf.d/50-server.cnf --bind-address=127.0.0.1 

/etc/mysql/mariadb.conf.d/50-server.cnf --key_buffer_size=16M 

/etc/mysql/mariadb.conf.d/50-server.cnf --max_allowed_packet=16M 

/etc/mysql/mariadb.conf.d/50-server.cnf --thread_stack=192K 

/etc/mysql/mariadb.conf.d/50-server.cnf --thread_cache_size=8 

/etc/mysql/mariadb.conf.d/50-server.cnf --myisam-recover=BACKUP 

/etc/mysql/mariadb.conf.d/50-server.cnf --query_cache_limit=1M 

/etc/mysql/mariadb.conf.d/50-server.cnf --query_cache_size=16M 

/etc/mysql/mariadb.conf.d/50-server.cnf --log_error=/var/log/mysql/error.log 

/etc/mysql/mariadb.conf.d/50-server.cnf --expire_logs_days=10 

/etc/mysql/mariadb.conf.d/50-server.cnf --max_binlog_size=100M 

/etc/mysql/conf.d/my.cnf --character-set-server=utf8mb4 

/etc/mysql/conf.d/my.cnf --collation-server=utf8mb4_general_ci

If you determine you need to edit any of these files use the editor of your choice from above and if you aren't sure about the change you are making I suggest commenting out the line with a '#' as the first character and retyping the line that you want.

Once you modify a configuration files that you think might fix your problem do the following:

• >>sudo service mysql restart
• I'm using Ubuntu so your syntax for this may be different
• >>telnet <ip address> 3306
• Error or some text that includes "Trying" and the database name "MariaDB"
• >>mysql -u root -h <ip address> -p
• I used the blank password that I was using for localhost previously and now this command changed to a new error message for me:
• Access denied for user 'root'@'<ip address>' (using password: NO)
• if root has a password assigned for '%' (see the query we ran near the beginning) use that password to try and connect.

NOTE: I was able to connect remotely at this point, so the following isn't as well tested. 

At this point we have a "permission" error. If you already set a password for root for remote access you can use that. Although best practice would be to create a new mysql user with remote access permissions. 

• >>mysql -u root -p
• Connect to the mysql server locally to add a new user
• SQL>create user '<username>'@'%' identified by '<password>';
• Even better is to use an IP address or range instead of the % character above
• SQL>GRANT ALL PRIVILEGES ON *.* TO '<username>'@'%';
• Again if you specified an IP address or range use that instead of the %
• OPTIONAL SQL>grant grant option on *.* to '<username>'@'%';
• This is so that the user can grant other permissions to users like 'root'
• SQL>flush privileges;

I don't know enough about firewall's to really guide someone and my problem is solved and I feel that this improves the available information on this error.

I hope your day is better because of this post.

Windows IoT - Another UI "Hat"

I'm going to start by saying I really like "The Pi Hut", they do not pay me or send me free product or anything. I like the products and service. I live in the western US and their packages arrive from the UK in about a week via USPS, which is the same amount of time a certain brown carrier promises for 3 times the price and half the distance. I also got tired of watching my packages, sit at a brown distro center for a few days.  Much of the delivery kudos go to the USPS, which is my preferred carrier in general.

This time I'm going to share about the "ZeroSeg".

The ZeroSeg hat is a kit that does require soldering, it's all through hole and goes pretty quick but read the directions since there are couple of places that need to be done correctly. 

The basics for this hat are:

• 8 qty 7-Segment characters (good for numbers and a few standard letters)
• A few built in Characters are: 
• H - High
• L - Low
• E - Error
• P - Programming?
• Characters can also be managed in software
• 8 decimal dots
• 2 buttons (that are on good pins and work great)

I probably will use this board on my outdoor robot, while it may be a little hard to read in bright sunlight, adding a little "sun visor" over it will help.

The Windows IoT perspective on this hat was a bit different, my first use of SPI communication. Also the first time I was required to use the async and await keywords. To be clear I've been a professional business application developer with .NET for 13 years now, I've used BackgroundWorkers, and Threads and on a few occasions Application.DoEvents() to keep my UI responsive. In concept the async and await are a nice easy way to send a request and wait for the response. In practice I was very frustrated first by the constraints of the concept and then by the fact that I couldn't do standard synchronous SPI communication. 

To be clear I'm still learning async & await so there are probably solutions to my complaints. In my experience I couldn't use this concept inside a property body or in a class constructor. Sure I would probably agree that those are the wrong places to use it, but as I said I was forced to use async/await because there isn't a synchronous api available for creating an SpiDevice.

My plan was to create a wrapper class to abstract away some of the "bit-twiddling" and "register-stuffing" involved in using the MAX7219. The first step was to setup the Spi device in the class constructor. Well constructors can't use the async keyword. So, I made another method "ConnectSPI" that has to be called after the constructor before you can use the class. The next problem was ConnectSPI was returning to the main code before it had completed. Well the next lines were the configuration lines I used to setup the display how I wanted it vs the default configuration. These lines would then fail because they needed to communicate over the SPI connection which wasn't ready. I feel like it's a hack but I created an IsReady property that gets set when the ConnectSPI method completes, and my main code has a while statement that spins until it's Ready. Overall not my proudest code, but it works.

The simplest usage of my class looks like this:

            ZeroSeg seg = new ZeroSeg();

            seg.ConnectSPI();

            while (!seg.IsReady){}

            seg.SetText("01234567");

There is a lot more opportunity to create a full 7-Segment Character set, but I decided to stop at simple. Feel free to modify the code to suit your needs. 

Source Code 
This work is licensed under a Creative Commons Attribution 4.0 International License.

Windows IoT - Status Board

Windows IoT - Status Board

I found a really cool "Hat" for the Raspberry Pi on https://thepihut.com called "Status Board" There is a 5 line version for Pi2/3 and a 3 line version for the Pi Zero. It's fairly simple, uses just the GPIO so I thought it would be a good place to write some code for Windows IoT.

The expectation is that most users will just use the Python library that is available, so there isn't much technical documentation. On the back side of the board the GPIO are listed by function "Breakout", "Button" and "LED" that's literally it. For example it doesn't specify which pin for which line or red/green. Also no mention of Hi/Lo Pullup/Pulldown etc. This doesn't make the job impossible, but it would of been nice.

Nuances\Gotchas:

• Pin 14, 15 are reserved in Windows IoT but are used so line 1 and 3 buttons don't work.
• Pin 4, 17 doesn't work like I expected, so line one LED's don't work (red or green)
• Pin 19 doesn't work like I expected, so line 2 button doesn't work. 

To be fair the issues on Pins 4, 17, 19 could be something wrong with my solder work or an issue on the Pi2 that I'm using, so I left the appropriate code for these functions, it just doesn't do anything for me.

Result was a little disappointing:

Line one: Nothing works

Line two: LED's work, No button

Line three: LED's work, No button

Line four: LED's work, Button works

Line five: LED's work, Button works

Personally I probably won't use these boards for my robot project, the LED's are "indoor" bright but my robot is intended for outdoor use.

I've attached my source code below which may be useful for someone to learn from.

Here is an example of how to use it:

            PiStatus piStatus = new PiStatus("Testing", 5);

            piStatus.Buttons[1].ValueChanged += StartupTask_ValueChanged; // Doesn't work for me

            piStatus.Buttons[3].ValueChanged += StartupTask_ValueChanged;

            piStatus.Buttons[4].ValueChanged += StartupTask_ValueChanged;

            piStatus.SetLED(0, LEDColor.Green); // Doesn't work for me

            piStatus.SetLED(1, LEDColor.Green);

            piStatus.SetLED(2, LEDColor.Green);

            piStatus.SetLED(3, LEDColor.Green);

            piStatus.SetLED(4, LEDColor.Green);

            piStatus.SetLED(0, LEDColor.Off);

            piStatus.SetLED(1, LEDColor.Off);

            piStatus.SetLED(2, LEDColor.Off);

            piStatus.SetLED(3, LEDColor.Off);

            piStatus.SetLED(4, LEDColor.Off);

            piStatus.SetLED(0, LEDColor.Red); // Doesn't work for me

            piStatus.SetLED(1, LEDColor.Red);

            piStatus.SetLED(2, LEDColor.Red);

            piStatus.SetLED(3, LEDColor.Red);

            piStatus.SetLED(4, LEDColor.Red);

Source Code 
This work is licensed under a Creative Commons Attribution 4.0 International License.

Windows IoT on Raspberry Pi - GPIO

Understanding which GPIO are available.

Reference Doc for this post: Windows IoT - Raspberry Pi I/O

The above link has the basic of what it takes to write an a program that uses GPIO. In my case I wanted to reverse engineer some simple add-on boards aka hats. A quick reminder that Windows IoT actually runs on multiple hardware platforms so there are some features that don't work on the Raspberry Pi. So even if you are using a completely different board than the Raspberry Pi this post will be helpful.

The code for this post will exist in the main class file and I don't want this to be a screenshot step by step how to, I'll cover some of the differences and why.

New "Background Application (IoT) - Visual C#". If this is a personal project always pick the latest for both Min and Max target version. If you were making a commercial product and had to support existing installations the Min version would be useful.

I'll provide the code as snippets inline and the full code sample at the end of this post.

The first piece to understand is that Windows IoT is a multitasking operating system and have your code do a classic for(;;){} loop would not be playing nice with other programs. With out the following lines of code, your program will run and when the steps are done, it will close. Adding these lines tell it to remain running and it will then continue to use event handlers to respond to events.

private BackgroundTaskDeferral deferral;

public void Run(IBackgroundTaskInstance taskInstance)
{
deferral = taskInstance.GetDeferral();
}

The next thing we need is to learn about the pins that we have available, unlike other platforms were everything is in the datasheet in this environment we can loop through the I/O and query it for attributes.
        private void GpioConfiguration()
        {
            gpio = GpioController.GetDefault();
            int[] pins = new int[30];

            for (int idx = 0; idx < 30; idx++)
            {
                pins[idx] = idx;
            }

            pins[28] = 35; // RPi2 Only
            pins[29] = 47; // RPi2 Only

            foreach (var p in pins)
            {
                GpioOpenStatus openStatus;
                List<GpioPinDriveMode> supported = new List<GpioPinDriveMode>();
                if (gpio.TryOpenPin(p, GpioSharingMode.SharedReadOnly, out pin, out openStatus))
                {
                    foreach (GpioPinDriveMode driveMode in Enum.GetValues(typeof(GpioPinDriveMode)))
                    {
                        // test driveMode for Pin Details
                        if (pin.IsDriveModeSupported(driveMode))
                        {
                            supported.Add(driveMode);
                        }
                    }
                }
                
                Debug.WriteLine("GPIO: {0} Status: {1}   Supported Drive Modes: {2}", p, openStatus, (supported.Count == 0)? "0" : string.Join(",", supported));
            }

        }

Adding a few class variable declarations (see full code at the bottom). Then calling this method right after our "GetDeferral();" code will give us the overview on the I/O pins by using the debugger to view the 'results' variable.

Not to spoil anything but the highlights are:

• 0, 1 are PinUnavailable
• 14,15 are PinUnavailable
• All pins support the following drive modes
• Input (Hi-Z)
• Output
• InputPullUp
• InputPullDown

The Details: 

GPIO: 0 Status: PinUnavailable   Supported Drive Modes: 0

GPIO: 1 Status: PinUnavailable   Supported Drive Modes: 0

GPIO: 2 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 3 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 4 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 5 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 6 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 7 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 8 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 9 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 10 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 11 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 12 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 13 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 14 Status: PinUnavailable   Supported Drive Modes: 0

GPIO: 15 Status: PinUnavailable   Supported Drive Modes: 0

GPIO: 16 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 17 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 18 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 19 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 20 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 21 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 22 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 23 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 24 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 25 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 26 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 27 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 35 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

GPIO: 47 Status: PinOpened   Supported Drive Modes: Input,Output,InputPullUp,InputPullDown

Other testing has shown the following pins not working as expected 4, 17, 19. At this time I'm not sure why, it could easily be a solder short or other damage to those pins on my RPi2.

Complete Source Code

Windows IoT on Raspberry Pi

As time moves on so do my interests. 

The Raspberry Pi is a very popular platform, but Python isn't the right language for me. I write most of my personal projects in C#, for the productivity and debugging capabilities. The question is do the benefits of C# translate over to the Raspberry Pi?

To be clear the following experience is mostly with a Raspberry Pi 2, Visual Studio 2017, Target Version "Windows 10 Fall Creators Update (10.0; Build 16299"), wired network connection.

If this project works out I'll use the Raspberry Pi with Windows IoT as a robot controller. If it doesn't work out I'll probably use a different hardware platform to control the robot, since I don't want to write and "debug" that much Python code.

The best reference for me has been this Microsoft Windows IoT Raspberry Pi page.

 It seems there are lots of random "gotchas" most aren't bad, in fact I haven't found a "deal breaker" yet, the first ones I ran into:

• - Windows IoT doesn't run on the Raspberry Pi Zero or Zero W
• Zero is Arm6 and WinIoT is compiled for Arm7 and above
• - Several GPIO are unavailable (0, 1, 14, 15)
• 0, 1 are EEPROM Id pins and shouldn't really be "re-used".
• 14,15 are a dedicated USART and can't be "re-used".

Good news is that the Raspberry Pi 2 (RPi2) works and Raspberry Pi 3 (RPi3) support seems good.

• - To setup Wireless for the RPi3, requires running on a PC that is connected to that Wireless network, i.e. not a wired desktop. I haven't seen a manual configuration option.

C# Language and Debugging on Windows IoT / Rasperry Pi

C# programming has some differences, it's an embedded platform so there are new Namespaces to support new features. There are also new concepts like the IBackgroundTask that take some getting used too. I haven't found any missing features, in fact I found a use for a Tuple and it worked as expected, basically I used it as a "lazy class" I wanted to collect Pin Data in a structured way and didn't have to make a 3 property struct/class to store the data.

I'm very happy that even the Resharper Visual Studio Add-In is working great in these UWP projects.

Debugging has a little more of an up and down story. When it's good it's very good, when it's bad your reminded that the Raspberry Pi is the same size as a baseball and would be very satisfying to destroy.

The good part of the debug experience is the variable expansion, setting the next line to execute (forward or backward). I'm a little bummed that Edit and Continue doesn't work.

The bad part of the debug experience is the Raspberry Pi seems to go to sleep or fall of the network after some period of time. Once this happens it's trial and error to get the debugger connected again. I think I have some reliable steps but I'll test a few more times before publishing.

More to come!

New Atmel board for ASF development!

I started off really excited when I first found the new SAMD10 X-Mini development board. http://www.atmel.com/tools/atsamd10-xmini.aspx

The Good News 

• It includes a Debugger built-in! It uses the meDBG protocol, through an ATMega32U4. Which could be hacked and used as a USB device and restore the firmware if you have a stand-alone hardware programmer. 
• Supports the ASF
• It's a 32-bit ARM M0+ based board
• Has pins in the right place to be Arduino Shield compatible!
• 16 kB FLASH, 4 kB SRAM
• 48 MHz operating speed.
• It has a QTouch button
• It's less than $10
• It's less than $10 (Yes it needs to be said twice!)
• + More http://www.atmel.com/Images/Atmel-42242-SAM-D10_Datasheet.pdf

The main reason for my excitement was the Arduino shield compatiblity w/ ASF support. I've made no secret that I like the readability of the ASF source code, it's great because the source code migrates well between all of the supported Atmel microcontrollers. The Arduino eco-system has so many good shields for prototyping designs that grabbing this SAMD10 XMini board and a shield is a great way to implement the code for a component like a Real-Time Clock or motion sensor IC's, even a classic character LCD. Write some code get it running then add that to your personal code library for larger projects. When you don't have to create the prototype board it's a big savings in time and money. Hint: This is what I'll be working on, I plan to share on Github anything that even kind of works.

Neutral News

A few details that are pretty neutral for me but should be clear.

• Only supports 3.3V I/O
• Doesn't include the header pins to attach a shield.
• Requires the use of Atmel Studio vs. the Arduino IDE.
• The SAMD10 doesn't have a crystal installed although there is an external clock line from the ATMega32U4 into PA08.
• The following SAM D10 pins are schematically shared with "Arduino" pins.
• PA07 w/ QTouch button
• PA09 w/ LED and w/ SPI Header
• PA10, PA11 w/ Serial on ATMega32U4 which gets routed to USB connector
• PA22, PA24 w/ SPI Header
• PA25 Mechanical button
• PA30, PA31 Debugger on ATMega32U4
• The ATMega32U4 isn't routed for much besides debugging, for hacking there is:
• The USB Device Lines to USB connector
• An LED
• 4 Pins on a tiny 10-pin header intended for JTAG use, but could be re-purposed.
• UART lines to the SAM D10
• Power Enable for the SAM D10

The Bad News

1. Digital Pin 8 and 9 are Not Connected (N/C) 
1. This is because of the limited number of pins available.
2. There is an issue between Atmel Studio and the documentation 
• The board silk screen and the schematic show PA27
• Atmel Studio says PA27 is NOT available with a compiler error.

I was able to address #1 with a couple of small wires and moving pins PA14 and PA15 to the N/C pins. This probably isn't a big deal if you are using Arduino shields that are pre 1.0 shield and many shields made after 1.0 don't use the extra I2C pins.

Issue #2 just needs to be tested it's a nuisance but more disappointing. 

I'm still looking forward to the Arduino Zero which according to the schematic does have all of the shield pins routed. Compared to this boards $10 price tag and "close enough" features against the Zero's approx $50 price, I'll probably order and use the SAM D10 XMini for most of my little projects. 

To be cont'd as I experiment more with the board!

P.S.

If you really like the classic ATMega328 that is in the Arduino UNO I suggest the ATMega328 XMini same chip, same Arduino shield pinout, except it's less than $10 and includes a real debugger. It does use Atmel Studio instead of the Arduino IDE, so you trade more power for a higher learning curve.
http://www.atmel.com/tools/mega328p-xmini.aspx

Notice the controller schema details

I received an email related to a game controller product that I designed previously and they asked a great question. It ended up being a pretty long exchange of questions and answers that I thought would be valuable to a wider audience.

"I'm putting some resources into game development and want to be aware of any steps I need to take to enable controller support on all platforms [especially] iOS." 

Their primary development platform is iOS so the answer would seem obvious "iOS Controller Support". It's been available since iOS7 and now with iOS 9 being released some 90+% of devices have the support built-in.

This easy answer becomes a little more complicated even before leaving the Apple realm since there are two different layouts, then if you want to support multiple platforms with the same code base it gets even more complicated.

Let's talk about controller compatibility, there are a lot of options. In addition to the iOS Controller support for Apple devices, there is the Android Phone/Tablet market. The most popular game controller on Android is the controller that Sony created for their PlayStation 3. So lets compare the main competitors for portable gaming controllers. Originally I was going to use a Logitech Controller but I couldn't find a clear description on their site of which inputs are analog and digital so I used mine sorry for the "bias".

iOS Standard Layout	iOS Extended Layout	Sony PS3	Evolution Controllers Drone
A,B,X,Y Analog D-Pad Analog L,R Analog Pause Digital	A,B,X,Y Analog D-Pad Analog L,R Analog L,R, Joysticks Analog L,R Triggers Analog Pause Digital	Square, Circle, Triangle, X Analog (i.e. A,B,X,Y) D-Pad Analog L1, R1 Analog L,R Joysticks Analog L2, R2 Triggers Analog Start, Select, PS, L3, R3 Digital	A,B,X,Y Digital D-Pad Digital L,R Joystick Analog LB,RB, Digital LT,RT, Digital Start, Select Digital

Reminder all of these controllers are used for mobile gaming, the iOS ones are a defined standard from Apple that hardware companies can make. The PS3 controller was a familiar layout with a standard Bluetooth connection, Sony of course was only trying to create a standard for their own platform, although I doubt they are disappointed by the additional sales. The Drone was initially created for use with classic console emulators on an Android phone, but has been very popular with PC gamers for extended play sessions.

Even between these 4 controllers there is a lot of variation in the hardware and the game programming side gets more complex too. All of the game controllers have the following buttons:

• A,B,X,Y
• D-Pad
• L,R
• Start/Pause 

Most of them have analog versions of those buttons but I forgot to mention that PC gamers don't usually get analog buttons, their buttons are actually very comparable to the Drone. So if your game is coming to a PC near you, remember that most buttons are digital. Analog buttons may have limited use since an average player might create about 4 different levels on an analog button, a good gamer might get 16 different levels. 

Best compatibility with the most platforms/controllers:

Essentially the iOS Standard Layout with digital buttons. If this is a good controller scheme for your game consider the iCade specification it's based on a keyboard input device and does work on iOS. http://www.ionaudio.com/downloads/ION%20Arcade%20Dev%20Resource%20v1.5.pdf.

• A, B, X, Y
• D-PAD (Up, Down, Left, Right)
• L, R
• Start/Pause

Best experience/compatibility combination:

Nice compromise for most games is analog joysticks but mostly digital buttons. (Not compatible with iOS Standard Layout) This is a great layout for PC games also consider using it for testing your game, using a simulator or emulator with a PC game controller may be quicker than using the actual hardware.

• Left Joystick (Analog)
• Right Joystick (Analog)
• Left Trigger Analog (or Digital for increased compatibility)
• Left Trigger Analog (or Digital for increased compatibility)
• Buttons (Digital)
• Start and/or Select
• A, B, X, Y
• 8-Way D-Pad (Up, Down, Left, Right, + combinations)
• Left/Right Bumper 

Best experience 

Same buttons and inputs as the above combination with the addition of any of the buttons in analog mode.
(Not compatible with iOS Standard Layout, or Drone)

• Left Joystick (Analog)
• Right Joystick (Analog)
• Left Trigger (Analog)
• Left Trigger (Analog)
• Buttons (Analog)
• Start/Pause
• A, B, X, Y
• 8-Way D-Pad (Up, Down, Left, Right, + combinations)
• Left/Right Bumper 

That is my "long winded" answer to a great question. 

Current project uses an ATtiny

My current project doesn't need the features of even the lowest featured ATXmega, it actually doesn't even need an ATMega. This one is happy to run on an ATtiny 8+ I/O pins, a couple of timers for PWM an ADC and I'm done, right?

What I forgot about the ATtiny is that it doesn't use the ASF, it's considered too memory constrained. More likely it would overly complicate (conditional compilation) the ASF source code.

The result is I'm really getting familiar with the actual registers. I can almost imagine the TTL chips it would take to perform the exact task I'm configuring. The good part is understanding a feature at the register level is great for troubleshooting when your abstraction leaks. It also makes you appreciate the amount of "heavy lifting" the ASF is doing for us.

XMega - ASF Performance

ASF Performance

The other day I noticed in my news feed that Sparkfun had a great post talking about "Performance" of the Arduino. While the post itself was a great start, the comments really added to the conversation. The short version is they tested toggling a pin using the Arduino digitalWrite command. If a user simply reads the specs of the ATMega328P (Arduino microcontroller) it's easy to take the 16 MHz clock speed and the estimate of 1 instruction per clock cycle and think the pin should toggle close to 8 MHz.

There are several reasons this number isn't what you would think.

• The C compiler adds additional instructions that may not be needed.
• Multiple checks and other actions are performed before the pin state is set.
• Instructions for managing the loop

The reality in their tests result was 117 kHz or 1/130th the speed expected. This suggests that there were approximately 130 assembly language instructions executed for each on/off cycle of the pin.

Before we get much further I'd like to clarify that this isn't a real world use case. For the simple action of toggling a pin at any speed the job is generally given to a built-in timer. In the case of the XMega we previously covered the ability to output the actual oscillator speed on a couple of supported pins (depends on XMega model how many and which ones). 

The real point here is to examine the Atmel Software Framework (ASF ) on the XMega and compare the overhead to the Arduino. Also we can explore some techniques to improve this situation.

First step is a "New Project .." "GCC C ASF Board Project", etc. For these tests we are going to set the clock speed to match the Arduino aka 16 MHz. See this post and based on your hardware use either the external crystal or the internal 32 MHz clock, both will require the use of PLL to achieve 16 MHz.

I'm going to compromise a little on the ASF code location standards and put everything in 'main.c' this is to be clear about the code being tested. The clock code remains where it is supposed to because that wont be changing over the course of these tests. My code in 'main.c' now looks like this:

#include

int main (void)
{
#define TOGGLE_PIN IOPORT_CREATE_PIN(PORTD, 6)
#define FREQ_OUT IOPORT_CREATE_PIN(PORTD, 7)

ioport_set_pin_dir  (TOGGLE_PIN, IOPORT_DIR_OUTPUT);
ioport_set_pin_dir  (FREQ_OUT, IOPORT_DIR_OUTPUT);

PORTCFG.CLKEVOUT = PORTCFG_CLKOUT_PD7_gc;
sysclk_init();

for (;;)
{
ioport_set_pin_level(TOGGLE_PIN, true);
ioport_set_pin_level(TOGGLE_PIN, false);
}
}

In this code you can see the extra 3 lines of initialization that I use to output the actual clock frequency out on Pin D7. This is for a quick validation of the operating frequency and should have no impact on the performance test in the for loop.

Actual clock frequency output is 15,999,883 or 16 MHz
The Frequency Counter shows the toggle speed on Port D6 is 2.6 MHz. Oscilloscope showed 3 MHz.

This is a great result but not exactly what I was expecting. My expectation was probably 1 MHz or less. So now lets work on some theories as to why.

The two pieces of test equipment show different values, this isn't usually a good sign. In this case we can assume that they use different methods for counting cycles.

Let talk about what the signal on the pin should look like. In it's simplest form the code is:

Loop -> High -> Low->Loop->High->Low
The Loop statement is really a low not knowing how many instructions are being executed it's still reasonable to say that the pin will be Low for twice as long as it's High. So this isn't really the type of wave that a frequency counter is good at counting.

Since the results weren't what I was expecting I changed the for loop to see what would happen.

for (;;)
{
ioport_toggle_pin_level(TOGGLE_PIN);
}

This code should give us equal High and Low times. Now both the O-Scope and the Frequency Counter show 2 MHz even. So less code, provides a lower toggle speed? 

This is only a theory, I'm sure someone smarter than me can confirm it or explain it better. When the two toggle instructions occur inside the loop the counting algorithms count the time between the high's since the wave isn't sinusoidal it leans toward the smaller of the two values. 

Improve Performance

Originally I was going to play with the code and see if it could be optimized to toggle the pin any faster. While researching the unexpected behavior I opened the '\Output\*.lss' file which contains the assembly source code with c source code as comments. What I found was well optimized code, 4 assembly instructions for the 2 lines toggle and 3 assembly instructions for the single line toggle code. In both cases one of the instructions is a jump which is defined to take 2 instruction cycles. I don't know if it could be done better, I couldn't do any better manually assigning registers and the loss of code readability is too valuable for me to mess with it. 

Conclusion

As a reminder toggling a pin manually with code, is about the worst way to implement functionality. By using the Clock out or a timer, better and more accurate results can be attained. Also remember if the processor is running at full speed toggling a pin, it can't go to sleep. This will impact battery life, and any other function you are trying to accomplish on the same microcontroller.