Resistance to starting a semi-large project for fun

I have 2 projects floating around in my head, and 1 deployed but incomplete.

I’m on NYE break.. another 5 days or so of vacation. This would be a perfect time to work on these projects. If that is what I want to do.

Yet, I find I am reluctant to start. I get more enjoyment out of thinking what I could do, rather than actually doing it?

Its probably similar to the idea of not-running while on vacation. If I’m going to do it, it needs to be part of normal-life, I think, not trying to beat it into submission with the vacation-card.

Idea #1 – “Burndown”

I’ve had this for several years. It’s a cross between smartsheet, and Rally, and “always remembering historical scope and left” to generate burndown graphs for any node in the tree. Its meant to be a multi-person hierarchical task-for-a-project list. In advanced mode, with templates and stuff.

The trick to make this one work is to have the UX be as easy to get things done as possible.

Idea #2 – “Muddy Sky”

Except, I want it so that I could write a multiplayer version of Endless Sky or even Elite, but with Mud-like conventions on the server side .. your spaceship is “floating” within a “room”, and interacts with the other objects in that room, and all the client/server stuff is handled over Signal/R, probably using javascript fro the client.

The solution in my head currently consists of:

javascript Canvas on the client side, with translation for rotating ship graphics
signal/r for payload transfer from server to client
property transfer dictionary of object / key / value for transfering game state from server to client

the “player” object on the server side remembers what was sent before.
the player object on the server gets called to collect what it thinks its client is interested in
some code to compare that against what was sent before so that only changed values get sent down in a client update

different kinds of messaging

“the world has changed” server->client (described above) to provide client with the latest that it needs for rendering
“event” server->client – this would be stuff like “something blew up”, or “somebody said something”.

on the server side, would broadcast to a room or to a particular player object

“command” client->server – this is stuff like thrust, turn left, turn right, etc.

I can already tell that will likely need to do “thrust for 1 sec” etc because I’m not sure how time sync would work.

Challenges:

latency – I don’t know that signal/r will be fast enough to get the latest updates from server down to client. I’m also not sure that the code for reducing payload size is worth it. And I haven’t written any code yet!!!!
time tracking – client has to deal with different FPS, server has to deal with different tick-per-sec.

I was thinking that all commands / events etc are stuff like “at tick XXXXX, thrust at 50% for 3 ticks”
server deals with things in desired-tick order, possibly one tick at a time (or set server interval. 100ms?)
start off with fixed time intervals, and then auto-tune from there

auto-tune: how fast can the client update its screen – no need to get updates from the server any faster than that. Let the server know that.
auto-tune: how much has changed, does the server need to send an update to this client yet? Note that stuff that changes, can stay in the buffer to be sent, doesn’t have to be re-detected.

Gameplay challenges:

I want it to be like endless sky…

It’s a 2D world
things overlap each other, (mostly) no collision
basic operations are thrust, rotate
there are ship computers for dealing with more complicated things

except that, kindof in order:

no speed limit. thrust one way, thrust the other way

this is why you want an AI to guide you to someplace

don’t fly over a star, you’ll overheat and explode
there COULD gravity

I’ll suspend belief, and have the planets/sun/etc not do the gravity simulation bit, but I want the ships and torpedos to be affected by gravity.
However, then there’s a discrepancy .. so maybe planets do orbit via gravity.
There would have to be something like “I want a planet of mass M=x to orbit that star with a Aphelion,Perihilion=y,z” and it calculates the starting x,y and velocity or something. And then just calculate where the planet will be at a given time T and what its velocity will be.

If we go with Gravity, then there will be bigger distances between planets
HYPERSPACE is a different space that your ship can enter

masses exert anti-gravity. Ie, you get pushed away from planets, suns, etc. r^3 instead of r^2?
this will probably cause some interesting “focus” points where everything gets pushed towards.

I wonder how those focus points would move as things orbit each other in normal space?

I want velocity in hyperspace to be exponential, like it is in FSD in Elite. Ie, you control your acceleration up/down. You can then get to insane levels of acceleration .. in a certain direction.

You will really need a computer to guide you in this world.

“Interdiction” ala Elite would be done by firing interdiction missiles through hyperspace at your target.. possibly homing?

See, there are so many places I want this to go. Hence, I want a nice development system, in C#, similar to how we used to run LP-Mud’s.. where I can play with these different mechanics (server side).. and provide all the hooks that others might need to make the game interesting.

Based on the amount I’ve typed, I know that the game is the one I want to work on the most.

But I also know that vacation time is not enough to get this to where I want it to be. Sure I might get some stuff knocked out, but it won’t be the juicy stuff. So, I’m loathe to start.

Maybe I’ll go mine some more asteroids in Elite.

Or maybe I’ll set up mini-simulations of what the game play would be like, and just do that rather than build the game.

Maybe I need to start visiting the game-development groups in Louisville on a more regular basis.

Excellent Software Experience

I recently upgrade my mom’s store to use The Uniform Solution – it’s a point-of-sale + inventory + EDI ordering software designed specifically for stores that deal with uniforms and scrubs and stuff like that. And speaking as a software developer, the experience was awesome.

I want to call them out on some of the stuff they did well:

Appropriate System Design

They don’t do anything overly fancy. They have a SQL Express database; they have a client that interfaces to the database. The “main computer” has a share, other clients map to the share and run the same executable. It’s a little unnerving – the share is wide open – however, if you’re enabling WIFI at your store direct to your internal network, you’ve got other problems going on. And, I’m sure it could be locked down; their target audience usually doesn’t have an IT department, so they did the right thing for their market.

For a web server, we’re not using it (yet), but it looks like a stand-alone executable that hosts the website. Well done.

Excellent Installation

Server install – Single executable – run – it detects if its upgrading or installing. It downloads SQLExpress 2012 and totally auto-scripts the install of it (much to my chagrin, I could not easily hack into their database and look at tables directly.. I wanted to see what their schema looked like).

They stick everything in C:\USW.

Client install – Map a network drive to the server share; run an executable – it makes sure that firewall settings are set up correctly, sets up an icon, done.

Once their software comes up, it has a failsafe option – if it can’t connect, you can configure it to help it find its SQL Server. Well done! as in, they didn’t assume that that part will work all the time.

The running software automatically keeps track of who is logged in concurrently. I suspect they use some network broadcast type stuff. I’m reminded of the 1990’s “net send” command, for some reason.

Backup and Restore

They provide their own backup and restore functionality – Everything gets dumped into a single file (extension .bsql – I think it’s a zip of schema + data). They automatically take a backup first thing in the morning (first time a person logs in to the system in a day). I found their auto-backup directory, and I added a script to it to sync the latest N files to dropbox as an off-site solution (different blog post). I did do one restore; I need to do another with the offsite stuff.

Side note: I tried to use an azure VM to do the DRP restore session, but their software had a hard time installing SQLEXPRESS on Windows Server 2012 and then finding it later. Will do the restore on my surface at some point just to make sure its possible. Which is actually perfect – my surface was used as a client for a while – we hooked a usb barcode scanner to it and mom walked through her shop importing inventory with it (we were waiting for the bluetooth wireless scanner to arrive. Prime is only so fast.) Ie, just like in the real world, if the server dies, we’ll probably promote client #1 to be the server – so test restoring on that laptop would be the ideal DRP test.

Excellent Technical Customer Service

I had to call them a few times. Freaking awesome – they people I talked to, not just some help desk lackey. These were highly competent technical people (I can tell the difference). At the first sign of miscommunication, they immediately went into “let me remote in and look at your screen” mode and took it from there.

I could also tell that these people are a team. They trust each other. They like what they do. They don’t want to grow big and take over the world, they want to be excellent and do excellent and completely solve the one corner of the universe that they have adopted.

Well-Iterated UX

After using the software for a bit – especially on their point of sale screen – I came to appreciate the love and care they have put into their product. Little things, like “type in stuff in the text box – if its decimal and less than 1, it’s a %off; if its integer between 1 and 99, it’s a quantity” etc – made the system very efficient to use. I came home and used the same idea on our fridge-inventory-scanner app at work. (different blog post)

Some of the Finer Points of their System Design

“Catalogs” = people out there in the world that they maintain catalogs for. If they maintain a catalog for it, it means they know all the bar codes for everything in that catalog.
“Inventory” = things that you might or might not have in your store. Breaks down to Vendor, Product, Size, Color.

You can add your own Vendor, Product, Size, Color, etc to inventory – you just don’t get the pre-loaded data from the catalog.
Mom does carry other stuff in her store that they don’t have in their catalog – notably, SAS shoes.
Everything that gets added to inventory gets its own unique custom bar code number as well. Think “SKU” from BestBuy.
You get to set the prices for inventory (there are standard formulas for per-vendor markups that you can tweak)
USW (that’s what they call it) can and will print out a shop-specific bar code + price to stick on the item.

Hardware wise, the app knows how to talk with:

Barcode scanners (basically, they work as USB keyboard-entry-devices; when you scan something, it types the stuff in and presses enter)
Pole Displays (we didn’t get one)
Receipt Printers (just another printer, but with a possibility of controlling a cash drawer)
Cash Drawers (it tells the receipt printer “open the drawer now”, and there’s a cable between the cash drawer and the printer which sends that signal across)
Zebra Thermal Label printers (for printing out cute little labels OMG so cute)

At the checkout, you can scan:

the original bar code that came with the item (if its something in a known catalog)
OR the shop-specific price tag + bar-code
OR search inventory to find the thing
OR just type in “*9.95” and get a miscellaneous item for $9.95.

But then inventory doesn’t get tracked correctly if the item was actually in inventory.

You can set up customer tracking

Special customers, for example, don’t get charged sales tax (governmental things)
Or, maybe you just want a list of customers to keep track of what they ordered.
typing in 3 letters of customer’s name triggers a customer search in the sales entry screen. (nice touch)

There are special modes for dealing with “sucking inventory in the first time”
There are special modes for placing orders, and then printing tags specific to the order that was placed.
There are user permission levels (initially turned off) to allow for managers vs clerks, etc.
There’s a built-in time-card check in/out thing.
There’s cash drawer check in / out things.
There’s end of day reconciliation reports and all kinds of other reporting.

With a Print Preview for seeing it on screen
and everything has a Print to PDF option
and some of the reports are aware of if you are printing to a receipt printer vs a regular page; and self-format accordingly.

They chose to integrate with X-Charge as a credit card processing system – it was a little bit involved, but in the end, how it works:

The XCharge guys installed the xcharge server + client on the machine(s). It can run as a service, but in our case it sits in the system tray. It deals with talking over the internet, running cards, that kind of thing.
USW just knows a username/password to log into the local xcharge thing and posts things that way.
USW doesn’t hang on to any credit card information. That’s all XCharge’s thing.

Their pricing model is simple: Monthly, $250/month, +$25/mo for each additional concurrent user. Simple, straight forward, and definitely worth the less-than-$10-per-day that its costing my mom. (Well, one-time cost on the computer hardware)
They provide 250 minutes per month of technical support as part of this. And looking at the emails I get, the service they provide is impeccable:

The person you talk to very quickly drops down to “here let me show you how this works” mode and remotes in to look at your screen.
After the support session, everybody gets an email saying “here’s what I helped you with”
And they clearly mark how many minutes of support were used, and how many are left, for the month.

Did I mention Video Training on topics of all sorts? (It needs a little update, but overall, pretty good).

All in all, a big huge THUMBS UP for these dudes. If I ever get into providing a specific solution to a specific market, I’m totally going to try to live up to these folk’s standards.

The only thing that I would suggest to them – because they have such a wide-ranging system – is perhaps a “day in the life of” video series for prospective customers. Ie, show a real life scenario of “here’s how a clerk uses the system” vs “here’s how an owner users the system”, etc. Especially if its people who like using the system, who can show off their favorite shortcuts, etc.

Sleeping at the Office

Based on vacation hours, I could either have worked the day after Christmas, or, thanks to flexible hours, I could try to get all my hours in before Christmas Eve.

How

I took a blanket to the office, and stayed there till the deed was done. I slept when I was tired, took time out for walking and eating and other minor things, but mostly worked. Out of curiosity, I logged what I did in Excel:

Analysis

Below is the distribution of sleep, work, and other (life) that I endured. This is NOT STACKED though it looks like it.

See the flat line of blue from 1pm to 9pm? My wife picked me up, we went to look at a house, had dinner, then I went home, slept, and showered, then returned to work.

Comparatively, if I had done normal hours: (sleep midight to 7; work 9-12 and 1-4):

Thoughts

I converted “Other” time into “Work”. Sleep was more or less identical. Theoretically, this gave me more “Other” time on Friday.
It was quite stressful. I should probably include the day it took for me to return to normal. (Stress as in “my body was under stress”)

If I ever do this again, I’m taking blood pressure and other measurements.

Interesting that a segment of work translates to a segment of sleep shortly after. This was certainly not planned, but it worked out that way. You cannot escape needed sleep.
Assuming the ratio stays about the same, I could do a regular week of work from Monday 8am to Thursday 8am? Assuming no meetings and scope changes, of course. (hah!)

Tips

If I were to do this again, what would I do the same or differently?

Invest in a cot or air-mattress to make sleep more effective.
When sleeping, sleep FULLY, not “just enough”. You’re going to sleep the same amount in the end.
Nap after every good bit of work; but look ahead (preplan the next bit of code) before napping so subconscious can work on it.
Invest in fatty snacks rather than carby snacks. Especially good for me this time was Dinty Moore Beef Stew.
Have minor things you can do for yourself in the middle so you don’t feel neglected, like going for a walk, taking care of an errand, etc.

Process with States

I implemented this again today, and realized it was a pattern that I fall back on regularly.

When dealing with a system with several states, and actions to be performed at each state, I usually break things up into a state diagram. Inevitably, I end up with a “State” field, the the question becomes what to put in the state field.

Take an example of sending an invite to a user to come in and use an app.

What I do not do:

Alter Table Invitation 
   add state int not null default(1)
 
enum State { 
  Start = 1,
  Email = 2,
  Consumed=3, 
  Expired=4
}

What I do (approximately):

alter table Invitation add
	state varchar(25) null, 
	stateError bit(1) default(0) null, 
	stateTimestamp DateTime default(getdate()) not null

public static class InvitationState { 
   public const string SendEmail="SendEmail";
   public const string WaitInbound="WaitInbound"; 
   public const string InviteConsumed="InviteConsumed"; 
   public const string InviteExpired="InviteExpired";
}

Why?

It is my experience that for any flow, a future spec will come up with will add new states in the middle of that flow. If you go with an int value, you end up with the flow being 1->9->5->6. By going with a varchar, its much more obvious when doing a select from the db, and it allows for the addition of new states.

A previous employer loved using CHAR(4) and state names like “STRT”,”MAIL”,”CONS”,”EXPR” because Oracle stored the Char(4) like an int. Very similar to Apple’s Resource Fork’s from back in the day.
I’m not so worried about the speed difference it makes. I feel that the flexibility it gives makes up for any performance loss.

Either we have an action state, named with a “Verb Noun”, or we have and end state, named with “Noun Verb”.
The state names are based on what IS happening or SHOULD happen to the row, not what has already happened, except for the end states.
For any action state, if the attempt at performing that action fails, I set the stateError bit to 1. This lets me know I need to do something to fix that; when its fixed, I can change the stateError back to 0.

In large batch systems, I write the console based action-taker with an override switch: to work on a specific Id, even if the errorState=1; and to do it in a transaction but not commit the transaction.
This lets me do test-things with rows that failed, while the main batch system processes all the other non-errored rows. Once I get it to working, I tell it to go ahead and commit, and the system continues on its merry way.

The system only deals with the states that it knows. This lets the technical OPS team (whoever that is) use the state field for its out-of band processes, stuff like setting the state to ‘Ken’, for example. Because Ken is the man who knows what is up with those rows.
In really large critical systems, there’s an audit table and a trigger that auto-inserts any changes to the audit table.

The result of this is, I get Christmas cards in the mail from former clients with phrases like “Still amazed by your code!!!”. 🙂

Hope this helps.

Day at the Office: Burndown on Ever Increasing Scope

When given a client who has a lot of work that needs to be done.. and as the work progresses, other ideas form, older ideas lose priority, etc – how does one show progress?

The specific statement that spurred this was “You guys were supposed to be done N time ago”. True, for the scope from N-1, the date agreed to was N. How can I show you how things have changed since then? How can we agree on when things will actually get done?

A Solution: Add Months to the Burndown

What I started to do is keep track of work by when it was introduced into the queue. Each month got its own mini-burndown. In the picture above, pay attention to the spaces between the lines, not the lines themselves.

I like that this graph shows:

That work is being done! You can see work getting nibbled away from the top, showing up at the bottom.
That I can calculate a velocity (slope of the green line) — in fact, i can calculate a “slowest” and a “fastest” velocity. Reality was somewhere in the middle.

An additional thing was to somehow show time taken by production issues – unplanned, un-estimated things. Overall, quite slight – less than 10% of the project?

Improvements

I tried to show work in progress on this graph; it was not useful. I would not do that again; it would be better to have a mini graph showing ins and outs of things entering and leaving development. If that was even needed.
I would probably put unplanned stuff not-stacked, so that changes in it could be seen easier.
If there was some way i could have the month retain its color on the “done” side, that would be awesome. Then we would also be able to see “estimate vs actual” (the bottom part is all based on time reported, whereas the top stuff was based on estimates).

Before I let this post, I need to show it to the client to ensure that I’m not divulging too much information.

Day at the Office: I’m Optimizing UX with SPEECH

Note: Say the title of this post in the same way as you would “She Blinded Me With Science”

The Problem

The customer has a screen, which is used for data entry. The source of the entry is various pieces of papers with scribbles on them. On observing the person doing the data entry, what I saw was:

Look at paper to see WHO it was for
Look at screen to find the same. Usually its the next one in order
Switch to Mouse
Click once to select the who
Click to select the thingies inside the who
Click the button to start the data entry for the who
Look at paper
Find the number
Switch to keyboard
Type in the number
Look at screen
Verify number
Press Enter to save
Look at paper
Move Hands to Paper
Mark Paper as done.
Move Paper Aside
Look at next paper.

Ouch. We could do better.

Our (Final) Proposed Solution

I think its pretty cool, I don’t know yet if the user(s) will adopt it, but this is what we came up with.

User can keep right hand on numeric keypad, and arrow up to the first entry. (they sort paper and computer entries to match beforehand)
Numeric / (on the keypad) starts entering for the currently highlighted row (even if it not yet selected)
As the data entry screen opens, the computer “speaks” the relevant info about what is being entered. This is so the user can keep eyes on paper, while listening to verify that the right row was selected
Still keeping eyes on paper, user can type in the numbers on the keypad, and then press numeric + (on the keypad) to “speak” the numbers that were entered, for verification, if they want.
In case the user wants to look at the computer screen, we also made the window bigger, text larger, and used color to highlight the various corroborating pieces of information.
If all is good, user hits enter on the numeric keypad to save, and now the next row is highlighted.

Blocked

This is well and good, except, this is what happened in testing:

/ 34.5 + <enter> / 45 <enter>
When the user got to the second /, the hotkey did not fire.

I spent many hours trying to figure this one out. Its complicated, I still don’t know the full answer, but it has to do with a DockedWindow, a control suite, a Busy indicator, a Ribbon suite, sub-ribbons, and document panels, and when the modal popup went away, focus did not return to the correct window.

Bottom Line: all my attempts to set focus were ignored. Seriously. I even bound a hotkey at the Shell level (the whole application) “F10” to fire code that should set the focus back to the correct window.. and.. nothing.

This problem also extended to any window. Any window, when it first came up, did not have focus, so any hotkeys bound inside that window, would not fire. Till it was clicked in. Then it was okay.

Hacked Solution

I could still capture hotkeys via the Shell, so this is what I did:

Shell catches PreviewKeyDown
Shell looks at current active docked window (since it hosts the dock control, this is easy)
If current active window implements IReallyWantKeyDownStuff, then call the YoHeresAKeyForYou(). (Names changed because i don’t have intellisense writing at home, but you get the idea).
My window receives this, and if it was Numeric/, then it fires the button that starts the data entry process

I used the same code to also wire some other hotkeys for some other screens.

Office Visit Still Needed

I hope to (soon) visit my client’s office in person, and sit down with the folks who would use this “enhancement”, and show them what I had thought. As someone once told me, I have to be careful how many strings I have attached to this: I have to be okay with them taking my awesome idea and putting it in the trash. No strings attached. So I’m blogging about its coolness now, in case its a flop, because right now, I think its pretty cool.

Recent Coding Travels

I haven’t posted much code stuff lately. Nothing in particular seems blog-worthy, but hey! after CodePaLousa, I’m making an effort to write about what might be mundane.. in a relatively anonymous way.

Story #1: Performance Improvement

Problem: I had 5 people, all doing the same web service call(s), which were slow – sometimes up to 20, 30 seconds to complete. Except that the system was taking 2 minutes to bring the information back.

Diagnosis: Ants Performance Profiler showed some interesting stuff.

Initial call to get the list of A1,B2,C1,D2 … took 5 seconds
But a background thread to populate A1a, B2a, C1a, D2a .. took another 30 seconds
But there’s a thing, which for any given A, needed the sum of A1a, A2a, A3a even if A2 and A3 were not in the initial result set. THAT one .. for large screens.. going at a speed of about 6 per second.. took up to 2 minutes.

Solution:

Introduce a service that preemptively did the web service calls.. cached results for up to 5 minutes. Service runs every 4.5 minutes. Everybody became much faster, and 5 minutes did not affect their business decisions. Instead of 2-20 seconds, now those calls finished in 7ms .. IF the cache was filled. Which the service ensures that it is. If the service is down, any client can fill the cache as well – they all use the same class.
Removed the background thread that got all the extra A stuff… waited until the user clicked a little expando button that showed that information. THEN go and get it.

Problem introduced by the solution:

Now, if you click “Expand All” on a large screen, you get 200 threads all trying to expand at the same time. It succeeds, but it also thrashes while attempting to resolve. However, that’s a <1% use case, so we can live with that.

If my client is okay with it, I’d love to post some before and after graphs to show how things changed.

Story #2: Idle Crash

Not on mine or the product manager’s machine, but certain machines on the floor, while using the app – leaving it sitting in the background – it would crash.

Diagnosis:

Event log entry #1: .Net blew up, Yo.
Event log entry #2: System.AggregateException()

Research:

in .Net 4.0, if a Task.Factory.StartNew(()=>{}) background thread generates an exception, and nobody reads the exception, … nothing happens.

Until the GC runs. Then the app blows up.

Doesn’t happen in .Net 4.5, which is why my machine ran fine.

Solution:

It was blowing up in the code that I changed in Story #1.
Added a handler for UnobservedTaskException – to log any other things that might happen with a full stack trace so they could be debugged.
It hasn’t shown back since.

Story #3: Iterating on Requirements

Problem:

While working on my normal client, I have another client for whom I (and another guy) are estimating a project. However, this is a large client, and the scope of the project seemed.. fuzzy. Huge. Integrated with a bunch of systems that had not yet been written.

Solution:

Break the project up into Phases. The client came up with the same idea! Yes!
Make the first Phase the smallest it could be. Thus, success on that phase would also clear up architecture problems, and provide a target for other systems to code against. It would also not be dependent on any other system to come online first.
Collect everything into a single document:

Questions
Decisions
Design
Features

Almost Everything started off as questions.

As stuff gets clarified, it moves over to “Decisions”.

“Design” is where larger things (like schemas, interfaces, etc) can be added.

“Features” was an interesting idea:

It is a one-line-at a time, “if the system has all of these things, then it is complete” list, bulleted and indented.
When you first start working with the project, you won’t know what most of that stuff is.
But after you’ve caught up to the “groupthink” on the project, you Will. It’s a checklist, without content.
This is the only part of the document that needs to be “complete”; everything else is just talking points, things in flux.
This is what the estimate actually gets built on.

I can’t say this is my idea. I picked it up from a speaker who talked about his daily process.

So, yep, that’s my software engineering excitement of probably the last 3-4 weeks. If I were asking questions on this blog, I’d ask, what have you been up to?

Tutoring? Growing? Developers

I volunteered my way into a meeting today with a pretty cool guy: Rider Rodriguez Jr of www.kentuckianaworks.org. We were discussing how to make Louisville a better city as far as Software Developers – there’s a shortage, actually nationwide, and the gap is likely to grown in the future. (I have no references, but I figure I’d trust him on that one).

It was a great meeting! We talked about what actually works, what doesn’t work. As people in the trenches who actually do the work – very often biggity-bigs who don’t do the work, have opinions of how people should learn and are looking for some way to leverage that for dollars. Not efficient. We just love doing it, and we know what it takes to do the work in our industry.

Then he presented to us the plan that he’s shooting for (applying for a grant, etc). I’m not going to try to detail it here, as I’ll doubtlessly get it wrong, but it does involve classroom availability, labs / peer sessions with people in the industry, building a portfolio in skills and abilities that companies actually want, and internships.

Much discussion later, and some white board drawing by yours truly, it seemed like we were all on the same page.

The Whiteboard. Someone noted that it looked like a person’s face. The face.. of the anonymous software developer.. dum dum dum!

I hope this works.. and yes, if anybody *wants* to learn *anything* that I know (including! how to make Instant Mashed Potatoes. I practiced that tonight. My wife thinks I did it wrong. She thinks that by adding liquid to powder, it would be lumpy. She might be right, it was rubbery), I’d love to help them learn it.. just to see the joy in their lives as they figure it out. I am the son of a teacher, after all. (10th from the top)

Database Change Scripts

At CodePaLousa Day #1, I attended a session, Tools and Adoption Patterns for Automating Database Deployment, about doing automated change in databases. That’s more than I need to do right now, however, it gave me a +1 on how I’m currently managing change, which is my default method if the customer doesn’t provide one.

What I do:

For every change, I start or append to a script file.
When a change gets to prod or “moves on” to branch, I start a different script file.
Every script file must be re-runnable.
Every script file must have a precondition check – something that is true of the database before it can run against it.
For SqlServer, if the precondition check fails, I turn noexec on to prevent the rest of the script from running.
For SqlServer, DDL statements are wrapped in exec() so that I don’t get compilation errors.

Here’s a sample, with names changed to protect the client:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- This is something from the most recent previous round of changes, which should already exist. 
PRINT 'Precondition Check'
if (not exists (select 1 from information_Schema.COLUMNS where TABLE_NAME='Quadrant' and COLUMN_NAME='ImperialCode')) 
begin
    PRINT '  Failed -- skipping rest of script'
    set noexec on
end else begin
    PRINT '  Passed'
end
GO 

-- In support of TA26 (revision 1683): 

PRINT 'Table NoRebelsAllowedRule'
if (not exists (select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME='NoRebelsAllowedRule')) 
begin

    CREATE TABLE [dbo].[NoRebelsAllowedRule](
        [NoRebelsAllowedRuleID] [int] IDENTITY(1,1) NOT NULL,
        [CatalogID] [int] NOT NULL,
        [DeletedOn] [datetime] NULL,
        [DeletedByUserID] [int] NULL,
        [CreatedOn] [datetime] NOT NULL,
        [CreatedByUserID] [int] NOT NULL,
        [UpdatedOn] [datetime] NULL,
        [UpdatedByUserID] [int] NULL,
     CONSTRAINT [PK_NoRebelsAllowedRule] PRIMARY KEY CLUSTERED 
    (
        [NoRebelsAllowedRuleID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    PRINT '  Created'
end else begin
    PRINT '  Already Exists'
end
GO

PRINT 'Foreign Key FK_NoRebelsAllowedRule_Catalog'
if (not exists ( select 1 from information_schema.REFERENTIAL_CONSTRAINTS where constraint_name='FK_NoRebelsAllowedRule_Catalog' ) ) 
begin 
    ALTER TABLE [dbo].[NoRebelsAllowedRule]  WITH CHECK ADD  CONSTRAINT [FK_NoRebelsAllowedRule_Catalog] FOREIGN KEY([CatalogID])
    REFERENCES [dbo].[Catalog] ([CatalogID])
    PRINT '  Created'
end else begin
    PRINT '  Already Exists'
end
GO

PRINT '04/23/13 Replacing Catalog.CannotFireBlasters with BlasterBehaviorID'
if (not exists ( select 1 from information_schema.COLUMNS where table_name='Catalog' and column_name='BlasterBehaviorID' ) ) 
begin
    alter table [dbo].[Catalog] add BlasterBehavior INT NOT NULL DEFAULT '0'; 
    PRINT '  Added BlasterBehaviorID'
end else begin
    PRINT '  BlasterBehaviorID Already Exists'
end
if (exists (select 1 from information_Schema.COLUMNS where table_name='Catalog' and column_name='CannotFireBlasters' ) ) 
begin
    exec (N'update [dbo].[Catalog] set BlasterBehaviorID=0 where CannotFireBlasters=0');
    exec (N'update [dbo].[Catalog] set BlasterBehaviorID=3 where CannotFireBlasters=1');
    PRINT '  Migrated Data from CannotFireBlasters'
    alter table [dbo].[Catalog] drop column CannotFireBlasters
    PRINT '  Dropped old column CannotFireBlasters'
end else begin
    PRINT '  Old column CannotFireBlasters does not exist'
end
GO

set noexec off
GO

Since the above sample, I’ve started adding dates and work item numbers into the script as well, to make things a bit more traceable. I figured as long as I had a sequence going, I’d append to the same script, though in an environment with multiple developers, you may want to partition more.

Why I didn’t..

RedGate Sql Compare tools: I don’t own them.
Sql Server Project in Visual Studio: When you actually get around to using them against real environments, there are a ton of problems around “Server” projects verses “Schema” projects (or some such) which are worse in 2012 than they used to be.
EF Migrations: Haven’t spent the time to learn them, desire to be productive, sticking with what I already know and can do quickly.

Excel C# Interop

I made a mistake.

I had a project in mind – to pull information from basecamp classic (todo items, with metdata embedded), massage it in Excel, and then upload it back to basecamp.

I have always used Excel (or some equivalent spreadsheet) for project tracking – I never know which columns I need to add, when I need to highlight things in different colors, what kinds of charts I need to pull. So the decision to bring the information down to Excel was an easy one.

That cost me 1.5 days of pain.

In retrospect, it would have been much easier to use EF Code First against .\SQLEXPRESS and use SSMS to do the bulk of my editing. But, not having the time to re-tool, I pushed forward, and I did get it to work well enough to help me next week as I start work with that project…

Anyway, this is what I learned along the way:

Workbook.Save();
Workbook.Close(false);
Application.Quit();

The recipe for closing down the instance of Excel that had been open. Without getting asked if I want to save.

Marshal.ReleaseComObject(sheets);

You have to do this for pretty much ever object you dink with, else the COM Excel wrapper stays open in the background. Reference: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects). I haven’t fixed this yet.

Sheet.Cells[1,1].Value
Sheet.UsedRange.Cells[1,1].Value

Cells start at index 1, not 0. UsedRange is faster than Cells.

var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";

You can use ACE to query spreadsheet data, however, type-coercion is sketchy. As in, if you have no data, that long field will come back as string. I ended up ditching that and reading/writing my lists of stuff directly from the worksheet so I could have direct control over the types:

Worksheet sheet1 = b.Worksheets["Tasks"];
t2 = sheet1.ReadListFromSheet<MyTask>();
sheet1.UpdateListToSheet(t2, "TodoItemId");

        public static List<T> ReadListFromSheet<T>(this Worksheet sheet) where T:new()
        {
            var nameToCol = GetNameToColumnDictionary(sheet);
            var colToSetter = GetColToSetterDictionary<T>(nameToCol);

            var result = new List<T>();
            var rowCount = sheet.UsedRange.Rows.Count; 

            for (int row = 2; row<=rowCount; row++)
            {
                var item = new T();
                foreach (var e in colToSetter)
                {
                    var col = e.Key;
                    var setter = e.Value;
                    var o = sheet.Cells[row, col].Value;
                    if (o == null) continue;
                    Type source = o.GetType();
                    var target = setter.GetParameters()[0].ParameterType;
                    try
                    {
                        object o2 = null;
                        if (target == typeof(bool?))
                        {
                            o2 = Convert.ChangeType(o, typeof(bool));  // bypass the null stuff
                        } else if (target == typeof(int?))
                        {
                            o2 = Convert.ChangeType(o, typeof (int)); 
                        } else if (target == typeof(DateTime?))
                        {
                            o2 = Convert.ChangeType(o, typeof (DateTime));
                        }
                        else
                        {
                            // this generic conversion seems to catch most everything
                            o2 = Convert.ChangeType(o, target);
                        }
                        setter.Invoke(item, new object[] {o2});

                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Need to handle from type: {0}, value:{1} to setter type: {2}", source.FullName, o.ToString(), target.FullName);
                    }
                }
                result.Add(item);
            }
            return result; 
        }

        public static void UpdateListToSheet<T>(this Worksheet sheet, IEnumerable<T> lists, string pkColumnName)
        {
            pkColumnName = pkColumnName.Trim().ToLower();

            // Grab the list of columns
            var nameToCol = GetNameToColumnDictionary(sheet);

            // get a mapping from list member to column number / vice versa
            var colToGetter = GetColToGetterDictionary<T>(nameToCol);


            // grab the mapping of pk to row number
            if (!nameToCol.ContainsKey(pkColumnName)) throw new Exception("Could not locate primary key " + pkColumnName + " on sheet " + sheet.Name);
            var pkColumnNumber = nameToCol[pkColumnName];
            var pkToRow = new Dictionary<string, int>();
            var emptyRows = new List<int>();
            for (var r = 2; r <= sheet.UsedRange.Rows.Count; r++)
            {
                var pkValue = sheet.Cells[r, nameToCol[pkColumnName]].Value;

                if (pkValue == null)
                {
                    emptyRows.Add(r);
                }

                var test = pkValue.ToString();
                if (test == String.Empty) continue;
                pkToRow[test] = r;
            }

            // get a direct line to the getter of the pk
            if (!colToGetter.ContainsKey(pkColumnNumber)) throw new Exception("Could not locate primary column in DTO????");
            MethodInfo pkGetter = colToGetter[pkColumnNumber];

            // write the list contents into matching column names in the spreadsheet, updating as we go
            foreach (var item in lists)
            {
                var pkVal = pkGetter.Invoke(item, null).ToString();
                if (String.IsNullOrEmpty(pkVal)) continue; // inbound item has no PK

                // figure out which row to write to
                int rowToUpdate = -1;
                if (pkToRow.ContainsKey(pkVal))
                {
                    // row already exists .. update it
                    rowToUpdate = pkToRow[pkVal];
                }
                else
                {
                    // row does not exist .. append it
                    if (emptyRows.Count > 0)
                    {
                        rowToUpdate = emptyRows[0];
                        emptyRows.RemoveAt(0);
                    }
                    else
                    {
                        rowToUpdate = sheet.UsedRange.Rows.Count + 1;
                    }
                }

                // write it out
                foreach (var e in colToGetter)
                {
                    int col = e.Key;
                    MethodInfo getter = e.Value;
                    var o = getter.Invoke(item, null);
                    // any translations from .Net to Excel would happen here - none so far
                    sheet.Cells[rowToUpdate, col] = o;
                }
            }
        }

        #region Private

        private static Dictionary<string, int> GetNameToColumnDictionary(Worksheet sheet)
        {
            var nameToCol = new Dictionary<string, int>();
            for (var c = 1; c <= sheet.UsedRange.Columns.Count; c++)
            {
                var columnName = sheet.Cells[1, c].Value;
                if (!(columnName is string)) continue;
                if (String.IsNullOrEmpty(columnName)) continue;
                columnName = columnName.Trim().ToLower();
                nameToCol[columnName] = c;
            }
            return nameToCol;
        }

        private static Dictionary<int, MethodInfo> GetColToGetterDictionary<T>(Dictionary<string, int> nameToCol)
        {
            var colToGetter = new Dictionary<int, MethodInfo>();
            var properties = typeof (T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (var p in properties)
            {
                if (!p.CanWrite || !p.CanRead) continue;
                MethodInfo mget = p.GetGetMethod(false);
                if (mget == null) continue;
                var lowerPropName = p.Name.ToLower();
                if (!nameToCol.ContainsKey(lowerPropName)) continue; // not present in target xls
                colToGetter[nameToCol[lowerPropName]] = mget;
            }
            return colToGetter;
        }

        private static Dictionary<int, MethodInfo> GetColToSetterDictionary<T>(Dictionary<string, int> nameToCol)
        {
            var colToGetter = new Dictionary<int, MethodInfo>();
            var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (var p in properties)
            {
                if (!p.CanWrite || !p.CanRead) continue;
                MethodInfo mset = p.GetSetMethod(false);
                if (mset == null) continue;
                var lowerPropName = p.Name.ToLower();
                if (!nameToCol.ContainsKey(lowerPropName)) continue; // not present in target xls
                colToGetter[nameToCol[lowerPropName]] = mset;
            }
            return colToGetter;
        }

Not yet memory-leak proof
Can only use a long as an id
Does not handle nullable columns gracefully

I’ll post more later on what I was trying to do. I did finally get it to work. Sketchy as it all is. I love that word Sketchy… thank you Rutledge Wood!

Now I have to go pick up my race bib for a 10 mile race tomorrow.