Exponential Moving Average Calculations in SQL Server

Financial systems use a large variety of different analysis functions to identify trends and provide other derivations from periodic price or volume data.  One of these is the Exponential Moving Average (EMA), which provides a price average where more recent periods are weighted more significantly than older periods.  I'm going to demonstrate a way this can be calculated in SQL Server's T-SQL in a set-based way.

The EMA has only one parameter, which is a number of periods from which the weighting value is calculated.

The calculation is:

weight = 2 / ( number of periods + 1 )  [which will yield a value between 0 and 1]

EMA = CurrentPrice * weight + PreviousPeriodEMA * (1 - weight)

    = p₀ * w + EMA₁ * (1 - w)

where p₀ is the price for the current period, EMA₁ is the EMA value for the previous period, and w is the weighting factor.

As each EMA is calculated from the previous EMA, most solutions calculate this iteratively, but it can be calculated in a set-based manner.

Row Generation in SQL Server

While writing complex queries for SQL Server, I have often needed to generate a specific number of records in order to solve a problem in a set-based manner.  At times like this I reach for a trusty Row Generator functions (also referred to as a "numbers table").

Here is a simple implementation of a row generator as a table-valued function:

Certificate Deep Dive - Part 2 - What's in the box?

Continuing our deep dive into certificates, in this part we'll look at the actual certificate files themselves and explore their structure in depth.

Contents

• What's in a Certificate File?
• What About Base64 Encoding?
• How Big is a Certificate?
• How Do I Identify a Certificate File?
• Why Do My Certificates All Start with "MII"?
• What Are OIDs?

Certificate Deep Dive - Part 1 - What's a Certificate?

[Nothing to do with the Operating System at the moment I'm afraid, but this is an article I have wanted to write for a while.]

A few years ago, if you had asked me what technical subjects felt I should know more about, I would have said Time Zones, Character Encoding, and most definitely Certificates, which we'll look at today.

Despite their widespread use and amazing utility, the lack of good information and the obscurity of the tools for working with them lead many software developers to never grasp the benefits of them.  Over the next few posts, we'll do a deep dive into Certificates and attempt to cover off as much information as possible to give a good grounding and reference of what certificates are, why you might want to use them, and much more besides.

So buckle up, everybody, this is going to be a long few articles just because there is a lot to cover.  This first one is broken down into a "Question & Answer" form where the questions should logically follow each other and each adds progressively more detail.  I have tried to ensure that everything in here is correct and isn't misleading, but I can't be held liable for anything you do with this and you're encouraged to do your own research as well.

[Speaking of character encoding, if you want to weep for the species about how little software developers understand the topic, this StackOverflow Question is a disturbing read.]

2017-04-17 AtomicityCodec Introduction

AtomicityCodec is an XML-based grammar designed for declaratively specifying the outline structure of a file.  I have been working recently to create it to fill a number of roles within my OS.  This is a long post to explain the what and why, but I'm leaving the actual technical details of the grammar for a future post.

The grammar is designed to achieve the following goals:

• Define a wide variety of file formats including filesystems.
• Provide a first-level parsing of files for an associated codec to then use a reference for further parsing.
• Provide a means of identifying a file based on its content.
• Serve as a basis for an "intelligent" hex viewer, which will have applications beyond AtomicityOS.
• Provide a means for basic file format or minor variant of an existing format to be added to the system without needing to write any code.
• Design a binary file parsing grammar that will have uses to a wider audience than just myself.
• Possibly have the format usable to save out files as well as load them.

Background

I have long been interested in file formats, and always wanted a means to easily define, modify, and play with files in an easy and responsive manner.  There are a few hex viewer tools on the market that allow a structure definition to be applied to the data to visualise or extract values, but these tend to be rather limited.  A number of groups have attempted to create a grammar to describe a binary file format, but these projects have either been abandoned, or commercial with a limited usefulness.  So I have defined my own grammar (as with everything else) which is still evolving (I'm considering adding iterative loops) but is sufficiently complete to describe a host of formats in a breadth-first manner.  This grammar allows me to make ad-hoc changes to how the structure of a given file is parsed and to see the results of those changes in real-time.

2016-10-10 Thread Synchronisation

I went back to an old kernel (the DriveAccess kernel from 2011) to review the code I created for the network stack there.  It wasn't anything fancy, it could send and receive UDP packets, and could respond to ICMP pings.  I had a function set up in the kernel so that it could download files from the TFTP server and display them, but the rest of the kernel wasn't complete enough to really take advantage of it.  For one thing, it was only a single thread with interrupts.

Having kernel threading now working, I set about researching the BSD Socket library and working out how best to fit an implementation into the kernel, particularly with a loopback interface so that I could create a server thread and a client thread and let them talk.

Unfortunately, I didn't get far down this road before I really needed some good old fashioned thread synchronisation.

The Basics

Interrupts can happen at any time, they don't care what you're doing, they will interrupt the currently running code and let some other code run for a while.  That's all well and good until you're half-way through updating some structure, you get interrupted, then the next task tries to read that data and finds it broken.  Bad things ensue.  In SQL Server, there are lot of fanciful things such as transaction isolation levels and snapshots and whatnot to assist you in not reading half-written data unless you really want to.

2016-09-25 Kernel Threads and VT100 Console

The Ring 3 test from yesterday is useful and proves the TSS, but the rest of my OS is not quite set up yet to handle everything from process space with system calls and protected memory and the like.  What I really need is a way to run multiple tasks that are within kernel space.  What I need is KERNEL THREADS!

Because I already have much of a window manager in place, I made a quick change to the kernel main function so that instead of just opening a window for the console, it opens two more windows.  My test case is then a couple of functions which write characters into those other windows with a delay of of a few thousand nops, which will then get set to run as separate threads.

The method signature for the function to create a kernel thread looks like this
int32 process_kernelThreadStart( void* pThreadStart, size_t pStackSize, int32_t pParamCount, ... );
It allocates a stack for the thread and copies the parameters from its own stack into the thread stack it just allocated.  It also writes the address of a termination function to the stack so that if the thread function ever exits, it "returns" to termination function is called which collects the return value and terminates that thread.