...and master of none

In the beginning, Codd proposed Third Normal Form , and it was good. Today, we need something better... Relational databases are great. They provide a robust and scalable representation of data in a way that people easily understand, and can manipulate with a minimum of training. However, the usefulness of that data is only as valuable as how the data is modeled. In this post, the "three keys pattern" for entity design will be presented. We will attempt to show how it allows relational databases to continue to scale horizontally and vertically for the internet age while retaining the simple query characteristics that are so appealing. Model Complete Entities, not Tables Much of the data we model is hierarchal in nature: parent-child, master-detail, and so forth... When this data is modeled in a database, the levels of the hierarchy are split, and the relationships described. Other data represents the edges between primary entities: uses of, instances of, etc... These...

In my last post , I demonstrated how to shape and bend XML from your existing tables in SQL Server. In this post I am going to go the other direction and shred the xml directly into SQL tables with a desired structure. I will also create a parent-child relationship between entities where no keys previously existed in the xml. This approach uses the older OPENXML mechanisms and is distinct from the value() and node() methods introduced later. I believe this approach to be superior and easier to construct than that demonstrated by Adam Machanic . I'm assuming here that you've loaded the xml into the @xmlDocument variable (as in my previous example) You need a document handle (@XmlDocumentHandle). As long as this handle remains open, the id's returned for each node do not change. Use the OPENXML function with the @XmlDocumentHandle and create an XPath query that finds the data for the you want. The value of 9 means to map attributes to your defined table, and handles r...

The XML functionality of SQL Server is vast, however, the best subset of that functionality that I've found may be demonstrated in the following example: Unfortunately, the example result is listed before the code. Join all the desired tables together Set the desired root element Set the path to an empty string to avoid an extra XML element at the root of each resulting row Provide an XPATH name with attributes for each of the desired columns (select * doesn't work well) Keep all the selected columns together by XPATH name or you'll split your xml attributes See my next blog entry for my best way to shred the xml

SQL Server 2016 has (finally) a built-in string split function: STRING_SPLIT ( string , separator ) That is great, however, if you are using an earlier version, many people end up writing something that loops through the string and wherever the separator shows up, the substring is copied to a table, which is returned later. Here's an alternative... replace the separator with XML element tags and then use the built-in XML functionality to return the delimited elements. In theory, this may be slower than a tight string loop, but in practice, the XML libraries are extremely efficient, and the REPLACE function is also highly optimized. It certainly takes less code than the other approach.

Continuous Integration  is a really good idea, especially when you are working with a team of developers. There are lots of tools out there that can help your team build better quality into your application. TeamCity by JetBrains is one such tool that has been around for awhile; specifically, it is the one that my company uses. Visual Studio has Database Projects   SQL Server Data Tools  to address some of the inconsistencies that developers face when using a database as a storage layer; Things like version control and consistent deployment. It also includes SQL unit tests , which fills a huge gap if any  of your logic is in the database. Many organizations moved away from logic in the database years ago because it was tricky (at best) to verify logic on the data tier, of course, they were also making changes directly to the database itself... but I digress. If you are using SQL Server Data Tools, and run your unit tests locally, it is a joy to behold. Your data...

The other day I was working on a little demonstration of using OWIN to serve local files, use dependency injection, and server Web API in a modern SPA (Single Page Application). Almost as an afterthought, I decided to use KnockoutJS and jQuery from a minimal HTML page. This was overheard by colleagues, and a firestorm erupted... "You should have used AngularJS instead; It would take less lines of code." So I pulled down AngularJS and changed my demo to placate the angry threats wishes of the religious zealots thoughtful developers at my desk. For the record, this is the code I wrote using KnockoutJS and jQuery: and here is the AngularJS version: Trying to provide equally readable formatting, the Knockout solution takes one less line of code even though you are loading two JavaScript libraries. The only fair way to count lines is to count semicolons. Both have three. That's a tie. What about the size of the libraries being loaded. Well, Angular includes...

...but everyone should learn to code. I have friends who are pharmacists, lawyers, police, firemen, military, mechanics, and musicians who also write code to help them out in their jobs. Software is a tool, a versatile and powerful tool, that can be applied to any occupation or industry. Learning to code is a 21st century skill that should be required in every middle school and high school in the country. I'm not alone in this belief. There are lots and lots and lots of organizations dedicated to helping people learn to code. Even the Prime Minister of Singapore posted a Sudoku Solver that he wrote while an electrical engineering student as encouragement and an example to the world that we need more people who can code. I think that code may have been more approachable when I was learning. The video games we had were blocky and you could understand how they could worked with a minimal amount of instruction or none at all; how many versions of breakout and lunar lander mu...

Say you have a stored procedure that does something like this: SELECT A.id, 10.0 * A.number / B.number AS Computed FROM Table1 A JOIN Table2 B on A.id = B.id What is the resulting type of the "Computed" column? Decimal? Float? The following SQL 2012 will tell you the data types of the columns from the first returned data set of a stored procedure: SELECT name, system_type_name FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID('myNamespace.mySPROC') NULL ); This will give a nice table that contains the column name and system data type name system_type_name --------------------- ---------------------- Id bigint Computed float

I am now the code manager for the public repository of a project that takes pull requests from several active forks and has tagged releases. The problem is, that I've not found any single resource that covers advanced code management using Git. My local repository has multiple upstream sources that I synchronize. All are hosted at github.com. This post is going to be a collection of little Git recipes. Many of them are collected from somewhere else, but others are more specific to the code management processes Configure git for certificate-based credentials for multiple logins Generate certificate I was able to get this working by following the instructions  here . However, I also needed to add some commands to my "~/.basrc" file to provide my SSH credentials to the git/bash shell window. Each ssh-add command adds one certificate to the identity store. #! /bin/bash eval `ssh-agent -s` ssh-add ~/.ssh/github_rsa ssh-add ~/.ssh/github_admin_rsa Make the local co...

Computing the z-score of individual values in SQL Server is not a built in function (although average and standard deviation are). The z-score tells us how far off from the average value the individual values are. The following function computes the z-score: for a table named [data] having an [objectId] and [score] fields. SELECT [D].[objectId], [D].[score], [E].[avg_score], [E].[stdev_score], ([D].[score] - [E].[avg_score]) / [E].[stdev_score] as zvalue FROM [data] D CROSS JOIN (SELECT Avg(CAST([score] as float)) as [avg_score], StDevP([score]) as [stdev_score] FROM [data] ) E ;

I love Bootstrap. Mostly because I'm not a very good graphic designer; but also because I'm lazy. I like the things it does for me that make my websites suck less. One of those things is the Bootstrap menu. One of the nice features of the Bootstrap menu is that you get a gradient highlight when the menu has a "selected" css class applied to it. I am looking at EmberJS . Ember has lots of nifty features as well. As a Single Page Application (SPA) framework, it likes to handle URL routing, and does something especially nifty with links that it manages: It applies a "selected" class to the current URL. Did the stars just align? Is this too good to be true? "Um, yea. Why?" Well, they almost align. It turns out that Bootstrap wants to have a "selected" css class applied to the list item that contains the anchor tag for the menu item while Ember's linkTo helper applies it directly to the anchor tag . Ouch. In other words, th...

When you create a set of interconnected web applications, or a mobile application that accesses a web service back-end, you have different service addresses that need to make their way into your JavaScript. Visual studio has XML transformations that apply to the app.config or web.config files, depending upon your build configuration, but that doesn't do you a lot of good if your client-side JavaScript is looking for the appropriate settings. You can isolate those settings in a single JavaScript file, but that file needs to change when you deploy to the web or the cloud. Here is a simple solution: Create an IHTTPHandler that reads all the application settings from your web.config file and transforms them into a constant object (named appSettings) containing all the application settings. Add the reference to this "dynamic" JavaScript in your master page template. As a HTTP Handler, it is fast, and because IsReusable is set, it is cached too. /// /// Summary de...

JavaScript is not Java As Brent so famously once said "JavaScript is not Java". My reply was "Dynamic, loosely typed, AND case sensitive...yea, that'll work". I tried to ignore JavaScript, hoping it would go away. jQuery helped a lot, but it still felt loose and dirty. Somebody out there heard my complaints. My biggest complaint with JavaScript has always been that it didn't have any concept of types, and you got very poor build-time support from development environments. Enter TypeScript I believe that Anders Hejlsberg would have been one of the great contributors to software developers productivity just for TurboPascal, Delphi, or C#, or .NET, but his work to modernize without breaking JavaScript through TypeScript , I predict will be far more reaching than any of the others. Add to that the fact that Microsoft has made TypeScript open source, and that it has IDE support in Eclipse and Visual Studio (among others), and you have the makings of a perfe...

The following applies to jQuery version 2.0.3 and ServiceStack version 3.9.59 My latest project is using ServiceStack.net  to create a REST-ful API for an existing data interchange format. There are a few gotchas that are important to understand: REST API DELETE Methods and jQuery The specification may be a bit vague, but most server frameworks (including ServiceStack) ignore any parameters passed to DELETE methods in the body. Only parameters passed on the URL are sent into the method. This behavior is similar to a HTTP GET. That is okay, but when you call an $.ajax method using jQuery, it doesn't serialize and attach the data to the URL, but instead puts it in the body...where the server promptly ignores it. In order to handle this apparent disconnect, and use the same semantics in $.ajax, you need to move the parameters from the body into the URL. The following ajax prefilter code does the trick: $.ajaxPrefilter(function (options, originalOptions, jqXHR) { if (options...

Here's a quickie. Like the title says, use powershell to recursively 'touch' every file in the current directory. Get-ChildItem . * -recurse | ForEach-Object{$_.LastWriteTime = get-date}

I got my new laptop. A Dell Precision M6700 with 256 GB SSD and two 750 GB spindle drives in raid 0 However, it shipped without the SSD, and of course that is the drive that I want to install windows on. If you try to install Windows 7/8 on this machine using UEFI from a USB drive you will get an error: “Windows is unable to install to the selected location. Error: 0x80300024.” USB installer tool doesn't work with it, so something needs to be done. What you need: 4 GB (minimum) USB Drive Windows 8 installation ISO Virtual Clone Drive (ISO mounter) or another way of retrieving files from an ISO image Let's pretend that my ISO is mounted under drive letter F: and my USB is X: Open a command prompt as administrator Format the USB drive using FAT32 format X: /Q /FS:FAT32 Make the USB drive bootable bootsect /nt60 X: Copy all the files from the ISO to the USB drive (this will take awhile) xcopy F:\* I:\* /E At this point, you should be able to boot ...

I was working on converting a Java application to a multi-threaded application today and wanted to use the simplest means possible. This involves the following steps: Make the class implement Runnable or descend from the Thread class Put the previously sequential code into threads Use the "synchronized" keyword on methods that should be critical sections Join the threads before working on the results Now, there are variations on this theme. Because each object in Java has an intrinsic lock, you can use any old Object as a mutex by calling synchronized(object){} around the critical section you want to protect. so this: private synchronized foo(){ // critical section stuff } is the same as this: private foo(){ synchronized(this){ // critical section stuff } } And when trying to synchronize multiple threads, both are wrong!! The problem is that each object has an intrinsic lock, not each class. This is easiest to see in the second case. Because each t...

I spent a little bit of time threadifying the classic mergesort. This sort works by using recursively dividing the array to be sorted down to some depth, and then eventually merging the sorted members of the array. This sort also has the desirable characteristic that it sorts in-place. There is no additional memory location required. This is a *good thing* when you have hundreds of thousands of elements in your array. In this particular implementation, an insertion sort is used when the recursive division has less than 100 elements. Multi-threading *could* occur at the first n levels of bifurcation, at the bottom level only, or at some intermediary number of levels. Limiting the number of threads and making them a small multiple of the number of CPU cores causes the threading overhead and context switching to be limited as well. The optimal multiplier appears to be quite low, only about 2 threads per CPU core. For these reasons, I choose to thread only at one depth level. Let's lo...

Yesterday in class, we were learning about the linux scheduler. How you can have several different algorithms for scheduling, etc. All very nice. The actual scheduler used in linux 2.6 has 140 FIFO queues; one for each possible priority. The scheduler takes the first job from the highest priority queue. The queues have a 140 bit bitmap. Because the bitmap gets scanned every single time there is a new quantum, the algorithm for scanning the bitmap needs to be very fast. Our professor asked how we would go about scanning the bitmap. As an example, he put up an algorithm that would compare a single bit, then shift the bits left and keep track of the number of shifts. Obviously room for improvement there. I suggested that the algorithm should be just an integer base 2 log function. To which the professor asked me how I would implement it. I wanted to say: "in hardware", but he was already moving forward. But really, the question of "what is the most significant bit?" ...

Working on a device driver for Linux 3.0.4. In class we are modifying some existing code: static struct file_operations simple_fops = { .read = simple_read, .write = simple_write, .ioctl = NULL, .open = simple_open, .release = simple_release, }; However, this doesn't compile with the latest kernel source. The error is: error: unknown field ‘ioctl’ specified in initializer The new structure, as defined in <source>/include/linux/fs.h is: struct file_operations { struct module *owner; loff_t (*llseek) (struct file *, loff_t, int); ssize_t (*read) (struct file *, char __user *, size_t, loff_t *); ssize_t (*write) (struct file *, const char __user *, size_t, loff_t *); ssize_t (*aio_read) (struct kiocb *, const struct iovec *, unsigned long, loff_t); ssize_t (*aio_write) (struct kiocb *, const struct iovec *, unsigned long, loff_t); int (*readdir) (struct file *, void *, fil...