Ætherhttp://www.atallison.com/aether
A blogTue, 26 Jul 2016 08:00:54 +0000en-UShourly1https://wordpress.org/?v=4.9.1Converting a Hash Map to a Tablehttp://www.atallison.com/aether/2016/07/26/converting-a-hash-map-to-a-table/
http://www.atallison.com/aether/2016/07/26/converting-a-hash-map-to-a-table/#respondTue, 26 Jul 2016 08:00:54 +0000http://www.atallison.com/aether/?p=607Several times I have run into a conversion where I have needed to take a table that stores key value pairs into a table that lists all values for a given record.

The table look something like this:

Item_ID

Field

Value

3

ITEM CLASSIFICATION

HOT DOG

3

ITEM DESCRIPTION

A TASTY CHICAGO STYLE DOG

3

UNIT OF MEASURE

LENGTH

3

HAS BUN

Y

4

ITEM CLASSIFICATION

CORNDOG

4

DIPPING SAUCE

MUSTARD

4

HAS BUN

N

5

ITEM CLASSIFICATION

HAMBURGER

5

ITEM DESCRIPTION

A HAMBURGER WITH NO CHEESE

5

UNIT OF MEASURE

POUND

5

HAS BUN

Y

What I really wanted was something like this:

Item_ID

Item Classification

Item Description

Unit Of Measure

Has Bun

Dipping Sauce

3

HOT DOG

A TASTY CHICAGO STYLE DOG

LENGTH

Y

4

CORNDOG

N

MUSTARD

5

HAMBURGER

A HAMBURGER WITH NO CHEESE

POUND

Y

This is kind of like a PIVOT function, but since there can be a varied number of distinct records in the FIELD column, a pivot won’t always work.

What we can do is create a dynamic sql statement that will scan the distinct FIELD column and create a table for us based on the elements in the column.

]]>http://www.atallison.com/aether/2016/07/26/converting-a-hash-map-to-a-table/feed/0Solving Sudoku in One SQL Query – Part 3 – Solving the Puzzlehttp://www.atallison.com/aether/2016/07/19/solving-sudoku-in-one-sql-query-part-3-solving-the-puzzle/
http://www.atallison.com/aether/2016/07/19/solving-sudoku-in-one-sql-query-part-3-solving-the-puzzle/#respondTue, 19 Jul 2016 08:00:35 +0000http://www.atallison.com/aether/?p=577Okay, now that we have the general framework we can start writing the pieces that deal with the values that were given to us in the puzzle. The first thing I’m going to do is create a table that contains each possible value for each cell based on the values that are already in the solution.

Possibilities
AS (
SELECT [g].[Row] -- first select just sets the value for the given locations.
,[g].[Col]
,[locations].[loc]
,[g].[Data]
FROM @given AS [g]
JOIN [locations]
ON [locations].[col] = [g].[Col]
AND [locations].[row] = [g].[Row]
UNION ALL
SELECT [possibility].[row]
,[possibility].[col]
,[possibility].[loc]
,[possibility].[val]
FROM (
SELECT [locations].*
,[possibleValue].[val]
FROM [locations] --
LEFT OUTER JOIN @given AS [g] -- I join to the given values to exclude these below
ON [g].[Col] = [locations].[col]
AND [g].[Row] = [locations].[row]
CROSS APPLY (
SELECT * FROM [Cnt] -- Join to all values 1-9
) AS [possibleValue]
WHERE [g].[Data] IS NULL --Exclude cells already defined in the given set
) AS [possibility]
WHERE [possibility].[val] NOT IN -- At this point, every cell not in the given set is
(
SELECT [existingCell].[Data]
FROM (
SELECT [locations].[loc]
,[g].[Data]
FROM @given AS [g]
JOIN [locations]
ON [locations].[row] = [g].[Row]
AND [locations].[col] = [g].[Col]
) AS [existingCell]
JOIN [groups] [existingCellGroup]
ON [existingCellGroup].[loc] = [existingCell].[loc]
JOIN [groups] [possibleValueGroup]
ON [existingCellGroup].[grouping] = [possibleValueGroup].[grouping]
AND [existingCellGroup].[loc] != [possibleValueGroup].[loc]
WHERE [possibleValueGroup].[loc] = [possibility].[loc]
)
),

First we get all the cells that are already given to us. These cells will only have one valid option. Next we need to union those with all the possible values for all the other cells. We could just include all values 1 thru 9 for each of these cells, but we already know that some values will be impossible because they already exist in a group that the cell belongs to. So to reduce our sample space, we do a check to see if the value already exists in any of the cells that are a member of a group that our cell is a member of.

]]>http://www.atallison.com/aether/2016/07/19/solving-sudoku-in-one-sql-query-part-3-solving-the-puzzle/feed/0Solving Sudoku in One SQL Query – Part 2 – Creating the Frameworkhttp://www.atallison.com/aether/2016/07/12/solving-sudoku-in-one-sql-query-part-2-creating-the-framework/
http://www.atallison.com/aether/2016/07/12/solving-sudoku-in-one-sql-query-part-2-creating-the-framework/#respondTue, 12 Jul 2016 08:00:57 +0000http://www.atallison.com/aether/?p=575In this post, I’ll be diving deep into the code that was presented in this post.

Recursive CTEs

My solution utilizes a lot of recursive common table expressions (CTEs). CTEs are a simple way of defining a temporary projection in T-SQL that can be referenced multiple times later on in a query. Their real power, however, lies in their ability to be self-referencing and thus recursive. In a recursive CTE, we define an initial projection and UNION ALL that with a projection that references the table currently being defined.

Since this puzzle is all about digits 1 through 9, I need to make something that will get me those values to work with.

;WITH Cnt AS
(
SELECT 1 AS [val]
UNION ALL
SELECT [val] + 1
FROM Cnt -- This is the table I'm creating right now, A SELF-REFERENCING DEFINITION!!!
WHERE [val] < 9
)

/*
val
1
2
...
8
9
*/

This is an easy way to get the digits we need without having to list them all out. The first statement in the expression gives the basis of the recursion (1), then we UNION ALL to the Cnt table, selecting the value in the table + 1. Initially Cnt has a single record with [val] = 1. Then we union that with a record that is [val] + 1 = 2. This continues until the constraint on the second selection is no longer true. Once [val] = 9, we cannot union it again and we end up with a table of values 1 through 9.

Assigning Each Cell

Next, I want to get a set of all the possible locations in the grid with values ranging from 1 to 81. I could do another CTE like the one I just did, but I already have values 1 to 9, I can utilize my Cnt table to create my location table.

Assigning Cells to Groups

In the game of Sudoku, each cell contains a digit that is unique within the row, column, and square that the cell is a member of. In order to facilitate checking that the value is unique, we will create a table that defines all the groups that each cell is a member of. The row and column groups are fairly easy to define, but the nine 3×3 boxes are a bit more difficult. In order to better set these groupings apart, I prefix the row groupings with a 10, the column groupings with a 20 and the box groupings with a 30.

This will allow us to cross-reference any locations to see if a particular value already exists. For instance, if I’m looking at the value in location 19, I’ll need to look at all the locations that are part of the groupings 103, 201, and 301 to see if the value I want to put in that location is already in any of the other locations in those groups.

The first two select clauses are retrieving the row and column groupings respectively, then the third grouping is creating groups for the nine 3×3 boxes in the grid. With these available to us, the job of building up a valid solution will be much easier.

Now I have all the framework I need to begin solving the sudoku puzzle. I have each of the 9 digits that I need; I have have given each cell in the grid a unique value; and I have made a listing of the groupings of cells that need to contain each of the 9 digits. In the next post I will walk through the code that takes the given values and builds out the correct solution.

]]>http://www.atallison.com/aether/2016/07/12/solving-sudoku-in-one-sql-query-part-2-creating-the-framework/feed/0Solving Sudoku in One SQL Query – Part 1http://www.atallison.com/aether/2016/07/05/solving-sudoku-in-one-sql-query-part-1/
http://www.atallison.com/aether/2016/07/05/solving-sudoku-in-one-sql-query-part-1/#commentsTue, 05 Jul 2016 19:03:34 +0000http://www.atallison.com/?p=549I’ve seen a couple examples of using SQL to solve sudoku puzzles, but most of the ones I’ve seen have used loops or cursors to cycle through and create a correct answer. I feel like if you make a solution that’s procedural like that, you might as well have done it in a language that’s designed to be procedural. The real trick is to write a query that really makes use of a relational database’s parallel projection capabilities to solve a sudoku challenge

We’ll start with the fairly simple sudoku puzzle below.

The Solution

For those looking for a tl;dr, here is the complete solution for solving a sudoku puzzle with a single query. The first two query simply define and populate the given values of a puzzle, then the last query evaluates and returns a solved sudoku grid. I’ll be posting a couple more times with in depth looks at various sections of the query.

]]>http://www.atallison.com/aether/2016/07/05/solving-sudoku-in-one-sql-query-part-1/feed/1Net Neutralityhttp://www.atallison.com/aether/2014/07/08/net-neutrality-2/
http://www.atallison.com/aether/2014/07/08/net-neutrality-2/#respondTue, 08 Jul 2014 21:46:23 +0000http://www.atallison.com/?p=510Imagine the internet as a highway. The actual “highway” part of the highway system is maintain by what are called Tier 1 providers. These are the companies that maintain the giant cables that cross oceans and provide the backbone of the internet. You as a consumer probably don’t deal with these providers very much. Instead, you deal with companies like Comcast or Time-Warner. In this analogy of an information superhighway, Comcast owns the on and off-ramps to your house. They connect the Tier 1 to your computer. Companies like Netflix, Ebay, or Amazon also have onramps and offramps, but depending on the volume they work with, these companies will either work directly with the Tier 1 providers or with other business facing internet providers to maintain their connections to the information superhighway.

When you click on a movie in Netflix, they send a “truck” from their onramp, down the highway, and it exits at your offramp and you watch a movie.

What Comcast is doing is saying “Hey, there’s a lot of these Netflix trucks coming down our offramps, let’s charge a toll to these trucks to make more money; or better yet, let’s just deny Netflix trucks from using our offramp so that our customers have to use our streaming services instead!”

Now sure, this wouldn’t be a problem if you had 3 or 4 different offramps to your house. You could easily say to Comcast, “Screw you guys, I’m going to use this other offramp where they aren’t blocking my Netflix truck from getting to me.” But the problem is that 2 out every every 3 Americans live in a place where there is only one choice of offramp. (In the unlikely event that someone from the FCC is reading this, that is what is called a monopoly. I know, it’s a word that you’ve been trained by your telecom lobbyists never to use.)

What the new FCC plan wants to do is allow Comcast to have two offramps, a tollway for fast traffic and another one for regular traffic. Essentially letting established companies with the financial backing to get a leg up over any competition that might not have the money to pay for a fast lane. It changes the dynamic from companies trying to provide better and more useful products to consumers into a dynamic of bribing the cable companies enough to ensure your content gets there faster.

In your city there is one electricity company. They are allowed to operate without competition because the people have decided that they would rather have one set of power lines in their neighborhoods than four or five. Electricity companies are therefore designated as “common carriers”. This means that they must provide service for a standard rate that cannot vary based on what it will be used for. So the electricity you use to power your refrigerator is billed at the same rate as what you use to turn on your TV.
Now suppose that your electricity company decides to start manufacturing microwaves. And then let’s say that they successfully convince your representatives that because they are providing this end use of the power, they are no longer common carriers and are not subject to regulation.
Now they are allowed to start charging you different rates depending on what you are using your power for. Don’t have one of their brand microwaves? Sorry, it costs twice as much to power it. Want to
power your TV? You’ll need to purchase the entertainment package. Unfortunately, since electricity requires infrastructure, they are the only provider in the town. Even though your tax dollars helped fund the development of their infrastructure, they now have no incentive to maintain it because you have no where else to go so you’ll just have to get used to it.
Or you could contact your representatives and tell them that you don’t want the electricity company to decide how you use your power. That they should again be treated as common carriers and should be required to charge a static rate regardless of what their power is used for.

Now just reread that replacing electricity with internet and appliances with streaming video or email and you will understand the current state of internet access in the United States.

]]>http://www.atallison.com/aether/2014/02/25/net-neutrality/feed/0SQL Cascade Delete without Cascadehttp://www.atallison.com/aether/2013/02/22/sql-cascade-delete-without-cascade/
http://www.atallison.com/aether/2013/02/22/sql-cascade-delete-without-cascade/#respondFri, 22 Feb 2013 22:45:36 +0000http://www.atallison.com/?p=489One of the things that I’ve commonly run into when I am working on a data conversion is an issue where I want to delete everything from a table, but there are foreign keys pointing to that table, and then there are foreign keys pointing to the tables with the foreign keys, etc. This is a stored procedure I created that will allow you to pass in the qualified name of a table (“[dbo].[MyTable]”) and it will recursively find all tables with foreign keys to it, then delete the data from all those tables in reverse order so that you don’t run into those annoying foreign key references.

]]>http://www.atallison.com/aether/2013/02/22/sql-cascade-delete-without-cascade/feed/0Vulnerabilityhttp://www.atallison.com/aether/2012/03/31/vulnerability/
http://www.atallison.com/aether/2012/03/31/vulnerability/#respondSat, 31 Mar 2012 17:56:31 +0000http://www.atallison.com/?p=437I’ve been on a journey the last few months that really started for me at an event called Men at the Cross (crossmg.org/matc.php) It’s been a predominant theme in a lot of my thoughts and interactions with people since then, and today a friend of mine posted a TED talk on facebook that just reinforces it all.

I’ve always been taught that true community only comes when people are open with one another. Light-hearted expressions like “nudity builds community” have been tossed out and hopes of “authentic community” have been expressed by participants of various groups in which I’ve been a part. But it wasn’t until the weekend at Men at the Cross where I really experienced how quickly and easily true community can be created when people are given a safe place where they can have the courage to be vulnerable.

Brown doesn’t go into a lot about where this shame and fear come from or why everyone experiences it. How sad that the scientific community is no longer allowed to use ideas like ‘sin’ because it, more than any other theory, completely explains the feeling of not “measuring up” that is universal to humankind.

The answer to this issue that Brown comes up with is for people to “believe” that they are worthy of love and connection. But she doesn’t really give any concrete evidence about why we should believe that. Naturalism doesn’t give any evidence for it, in fact it gives just the opposite: that eventually those people who fail to have this mentality actually aren’t worthy of it and will just get bred out of existence. But religion does give evidence for why we humans are worthy of love, and I believe Jesus gave the most succinct evidence when he said, For God so loved the world that He gave is only son. We are worthy of love because God loves us.

In fact, no other worldview fits the reality that we see around us as well as does the one that says we are all broken individuals who cannot experiencing the abundant life we were meant for until we recognize that we are worthy of it simply because God loves us.

A Role is a Role…

Roles play a huge part in the way that I interact with other people. I see every relationship I have in terms of the respective role that I play in that relationship. With my manager, I am the employee interacting with the boss. With my sister, I am the big brother. With my parents, I am the son and the eldest child.

And with each role, there is an nebulous set of rules that guide how I act in each of those relationships. As an employee, I do my best to do everything that my boss asks me to do, taking orders with a smile and providing feedback when asked for it. As a big brother I am supposed to be supportive and protective of my sister. I’m not at all theatrical unless I’m in the role of an actor.

It is what allows me to be totally mellow and go-with-the flow when I’m not in charge, and completely focused and driven when put into a leadership role. I am equally at ease being the center of attention on a theater stage and completely anonymous at a party. But all of these norms that allow me to thrive in a variety of situations is a double edged sword.

A Toll is a Toll…

These roles are how I have learned to function socially and any time those roles are mixed or confused, I don’t know how to respond. I’m out of the house now and all of a sudden my parents are also people who want me as a friend. There is the possibility that girls who I have been friends with for a while could become romantic interests. I feel like these things are a normal part of human experience, but for me, the lines don’t blur easily and the “rules of engagement” that development in my interactions hold me back from behaviors that don’t fit the roles I play in my relationships.

If it Don’t Take no Toll, Then I Don’t Have no Role…

I usually don’t make a post like this unless I have some idea-ribbon to tie it all together and make a neat little package, but this is something I’ve grown up with and some insightful comment isn’t going to suddenly change my perspective. And while it can cause me to feel trapped or alone at times, it is the only framework I have for interacting with people. But I guess knowing is half the battle and by recognizing occasions when my behavior norms are holding me back from something I can take chances to step out of those norms.

]]>http://www.atallison.com/aether/2011/10/18/the-principle-of-the-thing/feed/0Povertyhttp://www.atallison.com/aether/2011/09/30/poverty/
http://www.atallison.com/aether/2011/09/30/poverty/#respondFri, 30 Sep 2011 04:34:03 +0000http://www.atallison.com/?p=364There’s a power in poverty that breaks principalities;
that brings the authorities down to their knees.
There’s a brewing frustration and ageless temptation
to fight for control by some manipulation.But the God of the Kingdoms and God of the Nations,
the God of Creation sends this revelation
to the homeless and penniless: Jesus, the Son!The Poor will inherit the Kingdom to Come!Where will we turn when our world falls apart
and all of the treasures we’ve stored in our barns
can’t buy the Kingdom of God?And who will we praise when we’ve praised all our livesmen who build kingdoms and men who build fame
when Heaven does not know their names?And what will we fear when all that remains
is God on the throne with a child in His arms
And love in His eyes,
and the sound of His heartcries?

I’ve been re-re-reading Crazy Love and Chan mentions a question that one of his professors in college would ask: “What are you doing today that requires faith?” That question resonated with me because honestly, there isn’t anything going on in my life right now that requires me to have faith. I have a steady income, good health insurance, a grocery store down the street, and the biggest anxiety I face is if it’s going to be good hiking weather this weekend.

I live in a bubble where there isn’t room for faith. Faith is risky and it’s so much easier to settle into security, comfort, and status quo. We congratulate ourselves for not being like those silly Israelites who went around worshipping wood and gold statues immediately after witnessing God’s work in their midst, but how quickly do we lay our offerings at the feet of Retirement Plan and worship Flat Panel Screen?

What would it look like for an American Christian to truly live in a way that required faith? My mind goes first to money because we are filthy, stinking rich, but there are certainly other things that require faith of us. It takes faith to step into situations where you could be in trouble if God doesn’t come through. That could mean standing for justice at my job or speaking truth that might cause me to lose relationships. But mostly it’s about money, because that is what we have faith in in this country.