Master-Xwalk-Duplicate SQL Pattern

December 6, 2011

This concise and powerful SQL statement returns out-of-sync duplicated data.  On one hand, we could say that the tables should be normalized and therefore the data would never be out-of-sync.  In this case, if it was normalized, 1425 records would contain unfixable errors.   I was fortunate to have some duplication.  The errors were introduced over 3 years using a correct business rule that we discovered (during an audit review) should now be different.

select x.inventory_id, p.caci_po,
(select acq_doc_num from inventory where inventory_id = x.inventory_id )
from inventory_po_xwalk x inner join purchase_orders p
on x.po_id = p.id
and p.caci_po != (select acq_doc_num from inventory where inventory_id = x.inventory_id)

There is a better way to obtain protection against business rules that change data: using a trigger that records each change in an audit table.  However, its much harder to extract the desired changes from an audit table than it is to use a set of 3 tables with the master-xwalk-duplicate pattern.

Leave a Comment » | database | Permalink
Posted by @downeycp @vaxcalc

Rails: A Spotlight on Design Flaws

August 9, 2011

From RailsGuides: Active Record and Referential Integrity

The Active Record way claims that intelligence belongs in your models, not in the database. As such, features such as triggers or foreign key constraints, which push some of that intelligence back into the database, are not heavily used.

That is a very different way of designing systems than I’m used to doing and is interesting to think about: have I been pushing too much intelligence into the database?  I think so.  My current (at work) system has many stored procedures that contain most of the business logic and, at best, can be tested only indirectly.  In the July 2011 Thoughtworks Technology Radar (p.10), they point out that:

Programming languages commonly used to implement stored procedures lack expressiveness, are difficult to test, and discourage clean modular design.

I like how Rails is forcing me to think of physical structure and logical design at the very start of a new system; for legacy systems, thinking about how to integrate Rails is bringing clarity to some fundamental design flaws.  The trick will be: how to gracefully move into an improved architecture by bringing in Rails and pushing out a thin Java DAO layer that really doesn’t do much except transfer data between the Flex client and the database?

Leave a Comment » | Rails | Permalink
Posted by @downeycp @vaxcalc

Learning Rails

August 8, 2011

To learn Rails, I’ve started working on integrating it with a production system.  Getting connected to SQL Server 2005 was a bit tricky – this guide helped move things along.  Getting the ODBC connection string correct was a pain.

Everything I’ve seen so far on learning Rails focuses on building new apps.  Its not clear to me how to integrate rails into an existing production system.  The first problem is how to handle migrations? If I skip a migration because the table exists, what is the impact of not having the Rails-created fields id, created_at, updated_at in the table?

Leave a Comment » | Rails | Permalink
Posted by @downeycp @vaxcalc

Flex 3 to Flex 4 Migration – 2

April 28, 2011

Uh-oh.  Big problem with charts going from Flex 3 to Flex 4: ColumnSeries displayName is not showing up.

Flex 3 (displayName showing)

Using the Flex 4.1 SDK, the labels don’t show:

Flex 4.1 - displayName doesn't show

I spent hours upon hours trying to see if something in my code was working differently, looking at the API documentation… maybe some small change was made and my code needs to work a bit differently?

In the shower this morning, an idea appeared, like a nagging little itch: what if the problem is a bug in the Flex SDK?  So, I installed Flex 4.5.0.19786, re-compiled and – problem solved!  Whew.  I’m relieved.  Not being able to show labels on the stacked columns would be a deal-killer and we’d have to move back to Flex 3.5.

Leave a Comment » | Flex 3 to Flex 4 migration | Permalink
Posted by @downeycp @vaxcalc

Flex 3 to Flex 4 Migration – 1

April 21, 2011

This is a technical diary, short entries tracking the migration of the Vaccine Ingredients Calculator from Flex 3 to Flex 4: a non-trivial effort that I’m approaching with both excitement and trepidation.

I’ve been meaning to start this project for the past 6 months, but there has been too many enhancements that I’ve wanted to roll into the VIC.  Then, a few days ago, I found a good article on migrating flex apps and, after deploying a new version of the VIC today, figured that the time was right to begin.

The VIC is hosted on Google Code and stored in svn.  So the first thing I did was to create a new branch called flex4 from within subclipse.  Next, I changed the VIC’s Flex SDK version to 4.1 (in Flex compiler properties).  The change generated 28 errors and 14 warnings.  The errors were all related to style attributes:

Initially, this made my stomach turn; I didn’t understand what it meant.  Its a result of a big change made in the Spark component architecture pertaining to how styling and skinning is done (see video tutorial “Introducing Flex components and controls” on this page for a good overview.)  Fortunately, these errors can be fixed with no coding changes by choosing the Halo theme:

All compile errors are gone, but I’m left with 66 warnings to work through.  I did a quick check: the VIC runs but there are some problems; however, all 459 of our FlexUnit tests run successfully.  This is enough for today.

Leave a Comment » | Flex 3 to Flex 4 migration | Permalink
Posted by @downeycp @vaxcalc

New VIC version (2.2) released

April 1, 2011

A big improvement is that the Vaccine Ingredients Calculator (VIC) can now handle ingredients with both known and unknown amounts. For example, one vaccine may include the amount of Polysorbate 80 while another will not provide the amount. In previous VIC versions, we could not show this. However, we now show it in the Ingredient Chart and in the Vaccination Plan.  Coding this was a big effort because the original design assumed that if vaccine A’s package insert specified an amount for Polysorbate 80, all vaccine package inserts would do the same; however, this was an inaccurate assumption that had to be rooted out.  It is important to know when a manufacturer is including an ingredient but not letting us know how much.  This is a graphical example of known and unknown amounts:

Ooooooops – still have some alignment work to do!

The Ingredient Chart is showing that, based upon the chosen vaccines, 100 mcg from Infanrix + an unknown amount of Polysorbate 80 from RotaTeq will be injected into the patient.  The Vaccination Plan also shows this combination as 100 mcg + 1 exposure:

We’ve also included a new (incomplete) beta feature called Chart Explain. There is a great deal of information packed into the ingredient charts (as well as important research into what is NOT known), some of which is not obvious. Chart Explain attempts to expose all assumptions and to lay out the entire meaning of the chart.  For example, here is Chart Explain for aluminum:

The next steps for the VIC include adding more ingredients, continuing to improve Chart Explain and migrating from Adobe Flex 3 to 4.

Leave a Comment » | Vaccine Ingredients Calculator | Permalink
Posted by @downeycp @vaxcalc

Recover Lost SQL Server Product Key

January 7, 2011

I’ve been in an uncomfortable fix for months. I need to create a stand-by server but can’t find our SQL Server product key. Desperate to move this important project forward (I’ve been having dreams of hard disk crashes), I dug online for hours.  Most of what I found was tantalizingly promising, but would fail – leaving me increasingly frustrated.  Finally, I found a small utility called ProduKey.  Skeptical, but willing to try almost anything, I scanned it for threats then, with some trepidation, ran it on our server.

It worked beautifully.  Microsoft product keys are stored in the registry in a format that you cannot use.  ProduKey found all of our Microsoft product keys (including for Windows server 2003) and translated them correctly.

I highly recommend this wonderful little utility and tip my hat to it’s skilled author, Nir Sofer.

Leave a Comment » | reviews | Permalink
Posted by @downeycp @vaxcalc

Are highly educated parents a threat to public health?

December 29, 2010

Paul Offit believes that they are.

In Philadelphia for example, the Hispanic population get vaccinated. This is because they trust their doctors and they trust doctors’ expertise. Not so with some within the upper class population. It is graduate school-educated, college-educated people who tend not to vaccinate, and that seems to be universally true.

Now – combine that with his view of parental health and medical decisions:

In the United States we balance the sovereign right of the parent to make a choice for the child against the right of the state to protect children. In some respects, we are far too slanted towards the rights of parents, and that is to the detriment of unvaccinated children and the larger community.

He isn’t saying that about all parents.  Its the highly educated parents who, by thinking for themselves and selectively vaccinating, have Offit deeply concerned.  He calls upon the government to compel full vaccination, by re-education if necessary:

I think the government should make it very tough to gain an exemption from a vaccination. To attempt to gain an exemption, a person should go through an educational program where they hear the information and the facts about vaccines and about the diseases they prevent.

Why does Paul Offit, a world-renowned expert on vaccines, believe that an unwilling public should be forced by government compulsion to vaccinate their children as directed by a faceless bureaucratic committee?  Jacques Barzun, historian of ideas and culture, wrote that “science is an all-pervasive energy, for it is at once a mode of thought, a source of  strong emotion, and a faith as fanatical as any in history.” [1]  Simply put, he believes in vaccines.  And because he is a world-renowned expert, you should trust him.

But we don’t think that way anymore.  An ever-growing number of people are

  • reading ingredient labels (for foods, household cleaners, soap, shampoo, even for vaccines)
  • concerned about excessive use of pharmaceutical products (including antibiotics and vaccines)
  • worried about toxins (like formaldehyde in school trailers or aluminum in vaccines)
  • doing their best to reduce overall chemical exposure at every opportunity

We are taking charge of our own health and the health of our children.  Medical doctors are, at best, consultants that offer one point of view for our consideration.

If public health officials want better compliance, then the first thing to do is to become, along with the pharmaceutical companies, more transparent and trustworthy.  One way would be to toss out the intelligence-insulting campaigns such as “The Flu Ends With You” and stop recommending that everybody get the flu shot.  Back in 1986, I had a terrible case of the flu and have been fine ever since – without a single flu shot.  When the CDC tells me that I need a shot, which I clearly do not, they lose credibility.

[1] Barzun, Jacques (1964). Science: The Glorious Entertainment.   p. 3

Leave a Comment » | observations, vaccines | Permalink
Posted by @downeycp @vaxcalc

Best Tweet I’ve Ever Seen

December 16, 2010

This is the visually most effective use of Twitter that I’ve ever encountered.  What else needs to be said?

The tweet was created by @brunodecock

BTW, I’m very disappointed in Amazon for dropping Wikileaks.  It looks like a panic decision with the intention of staying in the good graces of our gov’t.  I’d have more respect for Amazon if they exhibited some courage to wait out the storm.  We should not allow our government to tell us what we can and cannot read.

Leave a Comment » | observations | Tagged: | Permalink
Posted by @downeycp @vaxcalc

Comparing Vaccines, part 3

December 1, 2010

Hand-coding this HTML and calculating sizes of all the red dots was becoming tedious.  Here is a small section of it:

Partial look at Vaccine Quick View for Seasonal/H1N1 Influenza Vaccines

To alleviate the boredom and minimize data entry to just the actual ingredient amount, I figured out a fascinating way to have the circles be drawn automatically, based upon the data in the cell.  The JavaScript code reads each row of table data (<TD>), figures out the largest value and uses that as the master (or scale-basis).  Then it creates a canvas and draws circles on the canvas.  Each cirlce’s area is scaled to the master.  Here is the code that calculates each circle’s area and draws it:


function drawCircle(canvas, amount, scaleBasis) {
 var cxt;
 var radius;
 var area;

 /*
 * Need to plug in an image for the "?"
 */
 if (amount == 0 | amount == "?") return;

 amount = getAmount(amount);
 radius = canvas.height / 2;
 area = Math.PI * radius * radius;

 /*
 * Amount will never be greater than scaleBasis.
 * An assertion here would be fantastic!
 */
 areaScaleAdjustor = (amount / scaleBasis);

 /*
 * Scale area; it's always the same size as original area or a reduction
 */
 area = area * areaScaleAdjustor;

 /*
 * From the adjusted area, obtain the radius
 */
 radius = Math.sqrt(area / Math.PI);

 cxt = canvas.getContext("2d");
 cxt.fillStyle = "#FF0000";
 cxt.beginPath();

 try {
    cxt.arc( canvas.height / 2, canvas.height / 2,
             radius, 0, Math.PI * 2, true);
 } catch(e) {
    alert( "Please email cdowney@gmail.com that there is a " +
           "drawCircle error on id " +
           canvas.id + "\n" + e.message + "\n" + e.name );
 }

 cxt.closePath();
 cxt.fill();

}

I never thought that I’d code HTML by hand; but after trying a number of WYSIWYG editors, I could not get the complex table structure correct.  Its been a surprisingly positive experience.  I’ve always feared and despised web page coding, largely because it seems to unstructured, messy and chaotic.

Well – I’ve learned a great deal from the third edition of Designing with Web Standards.  I had no idea that web page coding could be so fun… and so structured, with its own version of the MVC pattern (HTML, JavaScript, CSS) that works really well when you approach it with discipline and care – and stick as close as possible to web standards.

Leave a Comment » | coding, Vaccine Quick View | Permalink
Posted by @downeycp @vaxcalc

« Previous Entries
Follow

Get every new post delivered to your Inbox.