Spreadsheet Regrets

I can't emphasize this enough.

Some people, when confronted with a problem, think
“I know, I'll use a spreadsheet.”   Now they have two problems.

(This was originally about regular expressions. And AWK. See http://regex.info/blog/2006-09-15/247)

Fiction writer F. L. Stevens got a list of literary agents from AAR Online. This became a spreadsheet driving queries for representation. After a bunch of rejections, another query against AAR Online provided a second list of agents.

Apple's Numbers product will readily translate the AAR Online HTML table into a usable spreadsheet table. But after initial success the spreadsheet as tool of choice collapses into a pile of rubble. The spreadsheet data model is hopelessly ineffective for the problem domain.

What is the problem domain?

There are two user stories:

1. Author needs to deduplicate agents and agencies. It's considered poor form to badger agents with repeated queries for the same title. It's also bad form to query two agents at the same agency. You have to get rejected by one before contacting the other. 
2. Author needs to track activities at the Agent and Agency level to optimize querying. This mostly involves sending queries and tracking rejections. Ideally, an agent acceptance should lead to notification to other agents that the manuscript is being withdrawn. This is so rare as to not require much automation.

Agents come and go. Periodically, an agent will be closed to queries for some period of time, and then reopen. Their interests vary with the whims of the marketplace they're trying to serve. Traditional fiction publishing is quite complex; agents are the gatekeepers.

To an extent, we can decompose the processing like this. 

1. Sourcing. There are several sources: AAR Online and Agent Query are two big sources. These sites have usable query engines and the HTML can be scraped to get a list of currently active agents with a uniform representation. This is elegant Python and Beautiful Soup. 

2. Deduplication. Agency and Agent deduplication is central. Query results may involve state changes to an agent (open to queries, interested in new genres.) Query results may involve simple duplicates, which have to be discarded to avoid repeated queries. It's a huge pain when attempted with a spreadsheet. The simplistic string equality test for name matching is defeated by whitespace variations, for example. This is elegant Python, however. 

3. Agent web site checks. These have to be done manually. Agency web pages are often art projects, larded up with javascript that produces elegant rolling animations of books, authors, agents, background art, and text. These sites aren't really set up to help authors. It's impossible to automate a check to confirm the source query results. This has to be done manually: F. L. is required to click and update status. 

4. State Changes. Queries and Rejections are the important state changes. Open and Closed to queries is also part of the state that needs to be tracked. Additionally, there's a multiple agent per agency check that makes this more complex. The state changes are painful to track in a simple spreadsheet-like data structure: a rejection by one agent can free up another agent at the same agency. This multi-row state change is simply horrible to deal with.

Bonus confusion! Time-to-Live rules: a query over 60 days old is more-or-less a de facto rejection. This means that periodic scans of the data are required to close a query to one agent in an agency, freeing up subsequent agents in the same agency.

Manuscript Wish Lists (MSWLs) are a source for agents actively searching for manuscripts. This is more-or-less a Twitter query. Using the various aggregating web sites seems slightly easier than using Twitter directly. However, additional Twitter lookups are required to locate agent details, so this is interesting web-scraping.

Of course F. L. Stevens has a legacy spreadsheet with at least four "similar" (but not really identical) tabs filled with agencies, agents, and query status.

I don't have an implementation to share -- yet. I'm working on it slowly.

I think it will be an interesting tutorial in cleaning up semi-structured data.

Continuous Data Migration

See http://slott-softwarearchitect.blogspot.com/2013/07/database-conversion-or-schema-migration.html

People talk about CI/CD (Continuous Integration/Continuous Deployment).

They also need to talk about CM (Continuous Migration).

"Wait, what?" you ask.

When we roll out a new version of the software (CD) there are three common situations.

1. The new software uses the existing data model with no changes. This is a "minor version change": from v3.2 to v3.3.
2. The new software requires a tweak to the schema, but it's backward compatible. This, too, is a minor version change. In a SQL context, we might have used an ALTER TABLE to add a nullable column. If there are no SELECT * statements in the code, this change is essentially transparent to legacy code.
3. The new software involves a new schema that's not backwards compatible. This is a major version change. From v3.2 to v4.0. This is difficult. Really difficult.

Clearly, the first two can be done with the data in place. New software is installed, the servers are restarted, and away we go. In a big environment, there may be a rolling deployment. There may be a canary release that will get converted first, then others will be brought online.

A change of the Second Kind does involve the one-time database transformation script. This may lead to some down-time. Or it may lead to a feature toggle so that the new software can work with the old database until the script is run.

In a NoSQL context, a change of the Second Kind doesn't require the one-time script. The new documents have new fields that old documents don't have. NoSQL apps -- in general -- must be able to cope with data model variations.

A change of the Third Kind is trouble.

Big trouble.

We have two schema: the v3 schema and the v4 schema. We have two sets of software: the v3.2 release and the v4.0 release. We'd like to have just one valid set of data. How do we deal with this?

How can we do schema migration badly?

We can't easily have a single software release that includes one set of data in both schema. It's technically possible Anything that doesn't involve time travel, anti-gravity or perpetual motion is technically possible. But it rapidly becomes so complex that we have to set this uber-version idea aside.

We have to do more deployment work to have both v3.2 and v4.0 installed in parallel. v3.2 will use data in the v3 schema, v4.0 will use data in the new schema.

How do we migrate the data from the old schema to the new schema?

This can be tricky. There are proven bad ideas out there. Really epically bad ideas.

One Very Bad Idea (VBI™) is the one-time-only data migration. Back in the olden days, we couldn't afford enough storage to have two copies of the database. Seriously. When a company owned exactly one computer (before PC's -- a Very Long Time Ago) the conversion had to be done by making special backups and restoring the backups into the new schema.

This VBI is still with us today.  Lots of places want to do one-time-only data migrations because it's the traditional approach. If they can't done a one-time conversion (over a long weekend) they complain. Loudly.

BTW. This never worked well. The one-time-only conversion software was never tested carefully, and therefore rarely worked the one time it was needed. Also, data profiling was never done, so edge and corner cases were found during conversion. These often called the new software's features into question, leading to larger and larger problems.

Continuous Migration

The ideas behind continuous migration are these.

1. We're always going to be migrating the data. Always.
2. Storage is cheaper than labor. When in doubt, buy more storage.
3. Data outside the database (in CSV files or YAML documents) is smaller than data inside the database. Don't be afraid to export.
4. Data outside the database is inaccessible. Be cautious of the implied down-time during exports and imports.
5. ABP. Always Be Profiling. If you don't have a data profiler in place right now, that's the first thing to build. There are schema definition tools and schema checking tools. Look at JSON-Schema.org. Write schema definitions and use a data profiler to examine all rows and check all rules. All. Seriously. All. In a SQL DB, actually check the foreign keys to be sure the referenced row exists; you'll be surprised.
6. We're moving forward. We're not milling around; we're not supporting the old version except for the purposes of a parallel test or a fall-back in the event the next version doesn't work. There's no long-term coexistence strategy. Preserve the data; upgrade the software.

Here's the central data migration requirement:

Be able to migrate to the new schema as many times as needed.

I'll repeat that. As. Many. Times. As. Needed.

Migration is not a one-time thing. You do it all the time.

1. Migrating (and possibly sanitizing or subsetting) production data into the development environment.
2. Migrating production data for QA testing.
3. Migrating production data for integration testing.
4. Migrating production data for performance testing.
5. Migration production data for the production upgrade.

These are all the same activity. 

I'll repeat that. The. Same. Activity. Sometimes with mappings. Sometimes with filters. 

Since you'll do many, many migrations, your data migration programming is as important as your application programming. Perhaps more important than the application code because it's what preserves the data, and the data is the only thing of value. Applications come and go. Data is forever.

Having real data available permits seamless, silent, and automatic parallel testing. We can easily do a parallel test with v3.2 and v4.0 release candidates by simply running the migration (or migration with subset filter) to gather some data for the parallel test. If the release candidate has problems, we can fix v4.0 to create the next release candidate, re-migrate the data, and try the parallel test again.

At some point the v4.0 release is final, and we need to migrate all of the data. This (usually) involves some feature toggles to put v3.2 into a special "end-of-life" mode where the keys for records which change are logged separately. After turning off v3.2 and turning on v4.0, a second phase of migration will process these end-of-life rows through the migration mill.

Software and Schema Design Consequences

This has an important consequence.

Your software must be explicitly bound to a specific schema by major version number.

Explicitly bound. In a SQL context, you can use the "schema" construct an include the version number in the schema name. "myapp_v3" vs. "myapp_v4". This becomes a ubiquitous qualifier on all table names. SELECT col FROM myapp_v4.some_table AS st.

Yes. Do this Everywhere. Do it Now. 

If you're using mybatis or SQLAlchemy to get the SQL out of your application, then this kind of thing is a trivial change. If you have SQL in your application code, well, you have two problems to solve. First, get the SQL out of your application. Then make the schema version explicit.

In a NoSQL context, you can include the schema version as part of a collection name. "collection_v3" or "collection_v4".

This should be present everywhere.

Then, you'll need data validation apps and data migration apps. The validation apps will use your favorite schema definition and schema validation framework. Start running this as soon as you think you might need to make a major version change.

Finally, you'll need the data migration tool set. This will involve filter rules and sanitizing rules. These are not sophisticated "rules engine" kind of things with unbounded complexity. They're usually if statements and simple computations. But they come and go pretty freely, so design the software in a way that makes the filter and sanitizing code obvious.

Now you can -- trivially -- migrate data between schema versions inside the same database. You can have v3.2 and v4.0 running side-by-side. You can migrate the data early and often. You can profile and validate the data. You have a formal schema for the data validation. 

Omni Outliner, XML Processing, and Recursive Generators

First, and most important, Omni Outliner is a super-flexible tool. Crazy levels of flexibility. It's very much a generic-all-singing-all-dancing information management tool.

It has a broad spectrum of file export alternative formats. Most of which are fine for import into some kind of word processor.

But what if the data is more suitable for a spreadsheet or some more structured environment? What if it was a detailed log or a project outline decorated with a column of budget numbers?

We have two approaches, one is workable, but not great, the other has numerous advantages.

In the previous post, "Omni Outliner and Content Conversion", we read an export in tab-delimited format. It was workable but icky.

Here's the alternative. This uses a recursive generator function to flatten out the hierarchy. There's a trick to recursion with generator functions.

Answer 2: Look Under the Hood

At the Mac OS X level, an Omni Outline is a "package". A directory that appears to be a single file icon to the user. If we open that directory, however, we can see that there's an XML file inside the package that has the information we want.

Here's how we can process that file.

import xml.etree.ElementTree as xml
import os
import gzip

packagename= "{0}.oo3".format(filename)
assert 'contents.xml' in os.listdir(packagename)
with gzip.GzipFile(packagename+"/contents.xml", 'rb' ) as source:
   self.doc= xml.parse(source)

This assumes it's compressed on disk. The outlines don't have to be compressed. It's an easy try/except block to attempt the parsing without unzipping. We'll leave that as an exercise for the reader.

And here's how we can get the column headings: they're easy to find in the XML structure.

self.heading = []
for c in self.doc.findall(
        "{http://www.omnigroup.com/namespace/OmniOutliner/v3}columns"
        "/{http://www.omnigroup.com/namespace/OmniOutliner/v3}column"):
    # print( c.tag, c.attrib, c.text )
    if c.attrib.get('is-note-column','no') == "yes":
        pass
    else:
        # is-outline-column == "yes"? May be named "Topic".
        # other columns don't have a special role
        title= c.find("{http://www.omnigroup.com/namespace/OmniOutliner/v3}title")
        name= "".join( title.itertext() )
        self.heading.append( name )

Now that we have the columns titles, we're able to walk the outline hierarchy, emitting normalized data. The indentation depth number is provided to distinguish the meaning of the data.

This involves a recursive tree-walk. Here's the top-level method function.

def __iter__( self ):
    """Find  for outline itself. Each item has values and children.
    Recursive walk from root of outline down through the structure.
    """
    root= self.doc.find("{http://www.omnigroup.com/namespace/OmniOutliner/v3}root")
    for item in root.findall("{http://www.omnigroup.com/namespace/OmniOutliner/v3}item"):
        for row in self._tree_walk(item):
            yield row

Here's the internal method function that does the real work.

    def _tree_walk( self, node, depth=0 ):
        """Iterator through item structure; descends recursively.
        """
        note= node.find( '{http://www.omnigroup.com/namespace/OmniOutliner/v3}note' )
        if note is not None:
            note_text= "".join( note.itertext() )
        else:
            note_text= None
        data= []
        values= node.find( '{http://www.omnigroup.com/namespace/OmniOutliner/v3}values' )
        if values is not None:
            for c in values:
                if c.tag == "{http://www.omnigroup.com/namespace/OmniOutliner/v3}text":
                    text= "".join( c.itertext() )
                    data.append( text )
                elif c.tag == "{http://www.omnigroup.com/namespace/OmniOutliner/v3}null":
                    data.append( None )
                else:
                    raise Exception( c.tag )
        yield depth, note_text, data
        children= node.find( '{http://www.omnigroup.com/namespace/OmniOutliner/v3}children' )
        if children is not None:
            for child in children.findall( '{http://www.omnigroup.com/namespace/OmniOutliner/v3}item' ):
                for row in self._tree_walk( child, depth+1 ):
                    yield row

This gets us the data in a form that doesn't require a lot of external schema information.

Each row has the indentation depth number, the note text, and the various columns of data. The only thing we need to know is which of the data columns has the indented outline.

The Trick

Here's the tricky bit.

When we recurse using a generator function, we have to explicitly iterate through the recursive result set. This is different from recursion in simple (non-generator) functions. In a simple function, we it looks like this.

def function( args ):
    if base case: return value
    else:
        return calculation on function( other args )
     
When there's a generator involved, we have to do this instead.

def function_iter( args ):
    if base case: yield value
    else:
        for x in function_iter( other args )
            yield x

Columnizing a Hierarchy

The depth number makes our data look like this.

0, "2009"
1, "November"
2, "Item In Outline"
3, "Subitem in Outline"
1, "December"
2, "Another Item"
3, "More Details"

We can normalize this into columns. We can take the depth number as a column number. When the depth numbers are increasing, we're building a row. When the depth number decreases, we've finished a row and are starting the next row.

"2009", "November", "Item in Outline", "Subitem in Outline"
"2009", "December", "Another Item", "More Details"

The algorithm works like this.

row, depth_prev = [], -1
for depth, text in source:
    while len(row) <= depth+1: row.append(None)
    if depth <= depth_prev: yield row
    row[depth:]= [text]+(len(row)-depth-1)*[None]
    depth_prev= depth
yield row


The yield will have to also handle the non-outline columns that may also be part of the Omni Outliner extract.

Omni Outliner and Content Conversion

First, and most important, Omni Outliner is a super-flexible tool. Crazy levels of flexibility. It's very much a generic-all-singing-all-dancing information management tool.

It has a broad spectrum of file export alternative formats. Most of which are fine for import into some kind of word processor.

But what if the data is more suitable for a spreadsheet or some more structured environment? What if it was a detailed log or a project outline decorated with a column of budget numbers?

We have two approaches, one is workable, but not great, the other has numerous advantages.

Answer 1: Workable

Sure, you say, that's easy. Export into a Plain Text with Tabs (or HTML or OPML) and then parse the resulting tab-delimited file.

In Python. Piece of cake.

import csv

class Tab_Delim(csv.Dialect):
    delimiter='\t'
    doublequote=False
    escapechar='\\'
    lineterminator='\n'
    quotechar=''
    quoting=csv.QUOTE_NONE
    skipinitialspace=True
    
rdr= csv.reader( source, dialect=Tab_Delim )
column_names= next(rdr)
for row in rdr:
   # Boom. There it is.    

That gets us started. But.

Each row is variable length. The number of columns varies with the level of indentation. The good news is that the level of indentation is consistent. Very consistent. Year, Month, Topic, Details in this case.

[When an outline is super consistent, one wonders why a spreadsheet wasn't used.]

Each outline node in the export is prefaced with "- ".

It looks pretty when printed. But it doesn't parse cleanly, since the data moves around.

Further, it turns out that "notes" (blocks of text attached to an outline node, but not part of the outline hierarchy) show up in the last column along with the data items that properly belong in the last column.

Sigh.

The good news is that notes seem to appear on a line by themselves, where the data elements seem to be properly attached to outline nodes. It's still possible to have a "blank" outline node with data in the columns, but that's unlikely.

We have to do some cleanup

Answer 1A: Cleanup In Column 1 

We want to transform indented data into proper first-normal form schema with a consistent number of fixed columns. Step 1 is to know the deepest indent. Step 2 is to then fill each row with enough empty columns to normalize the rows.

Each specific outline has a kind of schema that defines the layout of the export file. One of the tab-delimimted columns will be the "outline" column: it will have tabs and leading "-" to show the outline hierarchy. The other columns will be non-outline columns. There may be a notes column and there will be the interesting data columns which are non-notes and non-outline.

In our tab-delimited export, the outline ("Topic") is first. Followed by two data columns. The minimal row size, then will be three columns. As the topics are indented more and more, then the number of columns will appear to grow. To normalize, then, we need to pad, pushing the last two columns of data to the right.

That leads to a multi-part cleanup pipeline. First, figure out how many columns there are.

    rows= list( rdr )
    width_max= max( len(r) for r in rows )+1

This allows us the following two generator functions to fill each row and strip "-".

def filled( source, width, data_count ):
    """Iterable with each row filled to given width.
    Rightmost {data_count} columns are pushed right to preserve
    their position.
    """
    for r_in in source:
        yield r_in[:-data_count] + ['']*(width-len(r_in)) + r_in[-data_count:]

def cleaned( source ):
    """Iterable with each column cleaned of leading "- "
    """
    def strip_dash( c ):
        return c[2:] if c.startswith('- ') else c

    for row in source:
        yield list( strip_dash(c) for c in row )

That gets us to the following main loop in a conversion function.

    for row in cleaned( filled( rows, width_max, len(columns) ) ):
        # Last column may have either a note or column data.
        # If all previous columns empty, it's probably a note, not numeric value.
        if all( len(c)==0 for c in row[:-1] ):
            row[4]= row[-1]
            row[-1]= ''
        yield row

Now we can do some real work with properly normalized data. With overheads, we have an 80-line module that lets us process the outline extract in a simple, civilized CSV-style loop.

The Ick Factor

What's unpleasant about this is that it requires a fair amount of configuration.

The conversion from tab-delim outline to normalized data requires some schema information that's difficult to parameterize.

1. Which column has the outline.
2. Are there going to be notes on lines by themselves.

We can deduce how many columns of ancillary data are present, but the order of the columns is a separate piece of logical schema that we can't deduce from the export itself.

Database Conversion or Schema Migration

I was told a horror story about a bad database schema migration. Let's call the author of the horror story HPL.

HPL related a tale of woeful conversion problems from a mismanaged schema migration.

While I could feel HPL's pain, the reasons given for their pain were wrong. They didn't quite get the lessons they had learned. Consequently, HPL sounded like someone doomed to repeat the mistake, or—worse—unlearning their lessons.

Here's HPL's most distressing comment.

"we can't migrate over the weekend and be done w/ it."

Apparently, the horror of a weekend migration was somehow desirable to HPL. Who wants a lost weekend? And who wants to put all of the eggs in a single basket?

Anyone who's done more than one "lost weekend migration"—and who's also honest—knows that they don't go well. There are always subsets of data that (a) don't get converted properly and (b) have to get swept under the carpet in order to claim to meet the schedule.

It's a standard situation to have less than 100% of the data successfully converted and still call the effort a success. If 100% was not required, why lose a weekend over it?

Good Plans and Bad Plans

From far wiser people than me, I learned an important lesson in schema migration.

These Wiser Heads ran a "conversion" business. They moved data and applications from platform to platform. They knew a lot about database schema migrations. A lot.

Their standard plan was to build a schema migration script (usually a sequence of apps) that could be run to convert the database (or files or whatever) from old to new schema as often as was necessary.

I'll repeat that.

As often as was necessary.

They debugged the script to get to an acceptable level of conversion. The data conversion (or schema migration) was perfectly repeatable. Of course, they longed for 100% conversion; but pragmatically, the legacy software had bad data. So some fraction would not convert. And once that fraction was found, the schema migration applications could be modified to treat the non-convertable data in some intelligent way.

Their stated goal was to convert data and run parallel testing with that converted data as often as necessary to create confidence that the new data was as perfect a conversion as was possible. At some point, the confidence became certainty and the parallel testing was deemed complete. Since they were parallel testing with live data, the decision amounted to a formalized "commissioning" of the new application. And by then, the new application was already being used.

There are bad ways to do schema migration, of course. HPL listed many.

Horrible Mistakes

The horror story from HPL included this:

"For the migrated tables, create views in the old system and create instead of triggers on those views to ship data to the new system."

It appears that they used views and triggers to create a new system "facade" over the legacy system. Apparently, they wanted both suites of application software to coexist. Not a good approach to schema migration. It appeared that they were trying to share one database with two application schema.

This seems like it's doomed. Unless they're all geniuses.

Wiser Heads would have strongly suggested that the new system use a extract of the old system's data.

HPL goes on to complain,

"Sometimes we can take over a column or 2 and sometimes we can only take over some of the data in the table".  

HPL emphasizes this point with "This is not that far fetched". I'm not sure why the emphasis was needed.

This is not "far fetched". It doesn't need emphasis. It's not really much of a problem, either. It's a standard part of schema migration. Extracting a copy of the data makes this quite easy. Triggers and views to create some kind of active SQL-based Facade is what created the complexity. Not the number of columns involved.

HPL summarizes,

"So you end up w/ [many] tables/views triggers all moving data back and forth and faking stuff out"

Back and forth. A fundamental mistake. A copy can be much easier to manage. One way data movement: Legacy to New.

HPL concludes with a litany of errors of various types: performance, change management, file system issues, error logging and auditing. Blah blah blah. Yes, it was a nightmare. I feel their pain.

What About Coexistence? 

It appears that HPL was involved in a project where the entire old and new applications were supposed to somehow coexist during the conversion.

It appeared that they failed to do any kind of partitioning.

Coexistence is not a trivial exercise. Nor is it a monolith where the entire legacy application suite must coexist with the entire new schema and the entire new application suite.

Pragmatically, coexistence usually means that some portion of the legacy must be kept running while some other portion is modernized. This means the coexistence requires that the application portfolio be partitioned.

Step 1: Some suite of functionality is migrated. That means data from the legacy database/file system is copied to new. That also means some data from new is copied back into the legacy database file/system. Copied.

Step 2: Some other suite of functionality is migrated. As functionality is moved, less and less data is copied back to the legacy.

At some point, this copying back is of no value and can be discontinued.

What About Timing?

This copying clearly requires some coordination. It's not done haphazardly.

Does it require "real time" data movement? i.e. triggers and views?

Rarely is real time movement required. This is the point behind partitioning wisely. Partitioning includes timing considerations as well as data quality and functionality considerations.

It's remotely possible that timing and partitioning are so pathological that data is required in both legacy and new applications concurrently. This is no reason to throw the baby out with the bathwater. This is nothing more than an indication that the data is being copied back to the legacy application close to real time.

This also means performance must be part of the test plan. As well as error handling and diagnostic logging. None of this is particularly difficult. It simply requires care.

Lessons Learned

HPL appeared to make the claim that schema migration is super hard. Or maybe that coexistence is really hard.

Worse, HPL's horror story may be designed to indicate that a horrifying lost weekend is the only way to do schema migration.

Any or all of these are the wrong lessons to learn.

I think there are several more valuable lessons here.

1. Schema migration can and should be done incrementally. It's ideally tackled as an Agile project using Scrum techniques. It's okay to have release cycles that are just days apart as each phase of the conversion is run in parallel and tested to the user's satisfaction.
2. Coexistence requires partitioning to copy any data back to unconverted legacy components. Triggers and views and coexistence of entire suites of software make a difficult problem harder.
3. The conversion script is just another first-class application. The same quality features apply to the conversion as to every other component of the app suite.
4. The conversion must be trivially repeatable. It must be the kind of thing that can be run as often as necessary to move legacy data to the new schema. 

Almost a good idea

Appleworks (formerly Clarisworks) is software that's been dead meat since 2007.

See http://en.wikipedia.org/wiki/AppleWorks#End_of_Appleworks

Which is fine unless you have an old computer with old applications that still works. For example, a 2002-vintage iMac G4 http://www.imachistory.com/2002/ still works. Slowly.

When someone jumps 11 years to a new iMac, they find that their 2002 iMac with 2007 apps has files which are essentially unreadable by modern applications.

How can someone jump a decade and preserve their content?

1.  iWork Pages is cheap. Really. $19.99.  I could have used it to convert their files to their new iMac and then told them to ignore the app. Pages can be hard to learn. For someone jumping from 2007-vintage apps, it's probably too much. However, they can use TextEdit once the files are converted to RTF format.
2.  iWork for iCloud may be a better idea. But they have to wait a while for it to come out. And they want their files now.
3.  Try to write a data extractor.

Here are some places to start.

• https://github.com/teacurran/appleworks-parser
• http://fossies.org/linux/misc/abiword-2.9.4.tar.gz/dox/ie__imp__ClarisWorks_8cpp_source.html This appears to have a known bug in chaining through the ETBL resources.
• https://github.com/joshenders/appleworks_format This project is more notes and examples than useful code.

Documentation on the Appleworks file format does not seem to exist. It's a very weird void, utterly bereft of information.

In the long run $19.99 for a throw-away copy of Pages is probably the smartest solution.

However, if you're perhaps deranged, you can track down the content through a simple brute-force analysis of the file. This is Python3 code to scrape the content out of a .CWK file.

import argparse
import struct
import sys
import os
from io import open

class CWK:
    """Analyzes a .CWK file; must be given a file opened in "rb" mode.
    """
    DSET = b"DSET"
    BOBO = b"BOBO"
    ETBL = b"ETBL"

    def __init__( self, open_file ):
        self.the_file= open_file
        self.data= open_file.read()

    def header( self ):
        self.version= self.data[0:4]
        #print( self.version[:3] )
        bobo= self.data[4:8]
        assert bobo == self.BOBO
        version_prev= self.data[8:12]
        #print( version_prev[:3] )
        return self.version

    def margins( self ):
        self.height_page= struct.unpack( ">h", self.data[30:32] )
        self.width_page= struct.unpack( ">h", self.data[32:34] )
        self.margin_1= struct.unpack( ">h", self.data[34:36] )
        self.margin_2= struct.unpack( ">h", self.data[36:38] )
        self.margin_3= struct.unpack( ">h", self.data[38:40] )
        self.margin_4= struct.unpack( ">h", self.data[40:42] )
        self.margin_5= struct.unpack( ">h", self.data[42:44] )
        self.margin_6= struct.unpack( ">h", self.data[44:46] )
        self.height_page_inner= struct.unpack( ">h", self.data[46:48] )
        self.width_page_inner= struct.unpack( ">h", self.data[48:50] )

    def dset_iter( self ):
        """First DSET appears to have content.

        This DSET parsing may not be completely correct. 

        But it finds the first DSET, which includes all
        of the content except for headers and footers.

        It seems wrong to simply search for DSET; some part of the
        resource directory should point to this or provide an offset to it.
        """
        for i in range(len(self.data)-4):
            if self.data[i:i+4] == self.DSET:
                    #print( "DSET", i, hex(i) )
                    pos= i+4
                    for b in range(5): # Really? Always 5?
                        size, count= struct.unpack( ">Ih", self.data[pos:pos+6] )
                        pos += size+4
                    #print( self.data[i:pos] )
                    yield pos
    def content_iter( self, position ):
        """A given DSET may have multiple contiguous blocks of text."""
        done= False
        while not done:
            size= struct.unpack( ">I", self.data[position:position+4] )[0]
            content= self.data[position+4:position+4+size].decode("MacRoman")
            #print( "ENDING", repr(self.data[position+4+size-1]) )
            if self.data[position+4+size-1] == 0:
                yield content[:-1]
                done= True
                break
            else:
                yield content
                position += size+4

The function invoked from the command line is this.

def convert( *file_list ):
    for f in file_list:
        base, ext = os.path.splitext( f )
        new_file= base+".txt"
        print( '"Converting {0} to {1}"'.format(f,new_file) )
        with open(f,'rb') as source:
            cwk= CWK( source )
            cwk.header()
            with open(new_file,'w',encoding="MacRoman") as target:
                position = next( cwk.dset_iter() )
                for content in cwk.content_iter(position):
                    # print( content.encode("ASCII",errors="backslashreplace") )
                    target.write( content )
        atime, mtime = os.path.getatime(f), os.path.getmtime(f)
        os.utime( new_file, (atime,mtime) )

This is brute-force. But. It seemed to work. Buying Pages would have been less work and probably produced better results.

This does have the advantage of producing files with the original date stamps.  Other than that, it seems an exercise in futility because there's so little documentation.

What's potentially cool about this is the sane way that Python3 handles bytes as input. Particularly pleasant is the way we can transform the file-system sequence of bytes into proper Python strings with a very simple bytes.decode().

Automated Code Modernization: Don't Pave the Cowpaths

After talking about some experience with legacy modernization (or migration), I received information from Blue Phoenix about their approach to modernization.

Before talking about modernization, it's important to think about the following issue from two points of view.

Modernization can amount to nothing more than Paving the Cowpaths.

From a user viewpoint, "paving the cowpaths" means that the legacy usability issues have now been modernized without being fixed. The issues remain. A dumb business process is now implemented in a modern programming language. It's still a dumb business process. The modernization was strictly technical with no user-focused "value-add".

From a technical viewpoint, "paving the cowpaths" means that bad legacy design, bad legacy implementation and legacy platform quirks have now been modernized. A poorly-designed application in a legacy language has been modernized into a poorly-designed application in yet another language. Because of language differences, it may go from poorly-designed to really-poorly-designed.

The real underlying issue is how to avoid low-value modernization. How to avoid merely converting bad design and bad UX from one language to another.

Consider that it's possible to actually reduce the value of a legacy application through poorly-planned modernization. Converting quirks and bad design from one language to another will not magically make a legacy application "better". Converting quirky code to Java will merely canonize the quirks, obscuring the essential business value that was also encoded in the quirky legacy code.

Focus on Value

The fundamental modernization question is "Where's the Value?" Or, more specifically, "What part of this legacy is worth preserving?"

In some cases, it's not even completely clear what the legacy software really is. Old COBOL mainframe systems may contain hundreds (or thousands) of application programs, each of which does some very small thing.

While "Focus on Value" is essential, it's not clear how one achieves this. Here's a process I've used.

Step 1. Create a code and data inventory. 

This is essential for determine what parts of the legacy system have value. Blue Phoenix has "Legacy Indexing" for determine the current state of the application portfolio. Bravo. This is important.

I've done this analysis with Python. It's not difficult. Many organizations can provide a ZIP file with all of the legacy source and and all of the legacy JCL (Z/OS shell scripts). A few days of scanning can produce inventory summaries showing programs, files, inputs and outputs.

A suite of tools would probably be simpler than writing a JCL parser in Python

A large commercial operation will have all kinds of source checked into the repository. Some will be inexplicable. Some will have never been used. In some cases, there will be executable code that was not actually built from the source in the master source repository.

A recreational project (like HamCalc) reveals the same patterns of confusion as large multi-million dollar efforts. There are mystery programs which are probably never used; the code is available, but they don't appear in shell scripts or interactive menus. There are programs which have clear bugs and (apparently) never worked. There are programs with quirks; programs that work because of an undocumented "feature" of the language or platform.

Step 2. Capture the Data.

In most cases, the data is central: the legacy files or databases need to be preserved. The application code is often secondary. In most cases, the application code is almost worthless, and only the data matters. The application programs serve only as a definition of how to interpret and decode the data.

Blue Phoenix has Transition Bridge Services. Bravo. You'll be moving data from legacy to new (and the reverse, also.) We'll return to this "Build Bridges" below.

Regarding the data vs. application programming distinction, I need to repeat my observation: Legacy Code Is Largely Worthless. Some folks are married to legacy application code. The legacy code does stuff to the legacy files. It must be important, right?

"That's simple logic, you idiot," they say to me. "It's only logical that we need to preserve all the code to process all the data."

That's actually false. It's not simple logic. It's just wishful thinking.

When you actually read legacy code, you find that a significant fraction (something like 30%) is trivial recapitulation of SQL's "set" operations: SQL DML statements have an implied loop that operates on a set of data. Large amounts of legacy code merely recapitulates the implied loop. This is trivially true of legacy SQL applications with embedded SQL; explicit FETCH loops are very wordy. There's no sense in preserving this overhead if it can be avoided.

Programs which work with flat files always have long stretches of code that models SQL loops or Map-Reduce loops. There's no value in the loop management parts of these programs.

Another significant fraction is "utility" code that is not application-specific in any way. It's an application program that merely does a "CREATE TABLE XYZ(...) AS SELECT ....": a single line of SQL. There's no sense in preserving this through an "automated" tool, since it doesn't really do anything of value.

Also. The legacy code has usability issues. It doesn't precisely fit the business use cases. (Indeed, it probably hasn't fit the business use cases for decades.) Some parts of the legacy code base are more liability than asset and should be discarded in order to simplify, streamline or improve operations.

What's left?

The high value processing.

Step 3. Extract the Business Rules.

Once we've disposed of overheads, utility code, quirks, bad design, and wrong use cases, what's left are a the real brass tacks. A few lines of code here and there will decode a one-character flag or indicator and determine the processing. This code is of value.

Note that this code will be disappointingly small compared to the total inventory. It will often be widely scattered. Bad copy-and-paste programming will lead to exact copies as well as near-miss copies. It may be opaque.

IF FLAG-2 IS "B" THEN MOVE "R" TO FLAG-BC.

Seriously. What does this mean? This may turn out to be the secret behind paying bonus commissions to highly-valued sales associates. If this isn't preserved, the good folks will all quit en masse.

This is the "Business Rules" layer of a modern application design. These are the nuggets of high-value coding that we need to preserve.

These are things that must be redesigned when moving from the old database (or flat files) to the new database. These one character flag fields should not simply be preserved as a single character. They need to be understood.

The business rules should never be subject to automated translation. These bits of business-specific processing must always be reviewed by the users (or business owners) to be absolutely sure that it's (a) relevant and (b) has a complete suite of unit test cases.

The unique processing rules need to have modern, formal documentation. Minimally, the documentation must be in the form of unit test cases; English as a backup can be helpful.

Step 4. Build Bridges.

A modernization project is not a once-and-done operation.

I've been told that the IT department goal is to pick a long weekend, preferably a federal Monday holiday weekend (Labor Day is always popular), and do a massive one-time-only conversion on that weekend.

This is a terrible plan. It is doomed to failure.

A better plan is a phased coexistence. If a vendor (like Blue Phoenix) offers bridge services, then it's smarter and less risky to convert back and forth between legacy and new over and over again.

The policy is to convert early and convert often.

A good plan is the following.

1. Modernize some set of features in the legacy quagmire of code. This should be a simple rewrite from scratch using the legacy code as a specification and the legacy files (or database) as an interface.
2. Run in parallel to be sure the modern version works. Do frequent data conversions from old to new as part of this parallel test.
3. At some point, simply stop converting from old to new and start using the new because it passes all the tests. Often, the new will have additional features or remove old bugs, so the users will be clamoring for it.

For particularly large and gnarly systems, all features cannot be modernized at once. There will be features that have not yet been modernized. This means that some portion of new data will be converted back to the legacy for processing.

The feature sets are prioritized by value. What's most important to the users? As each feature set is modernized, the remaining bits become less and less valuable. As some point, you get to the situation where you have a portfolio of unconverted code but no missing features. Since there are no more desirable legacy features to convert, the remaining code is -- by definition -- worthless.

The unconverted code is a net cost savings.

Automated Translation

Note that there is very little emphasis on automated translation of legacy code. The important work is uncovering the data and the processing rules that make the data usable. The important tools are inventory tools and data bridging tools.

Language survey tools will be helpful. Tools to look for file operations. Tools to look for places where a particular field of a record is used.

Automated translation will tend to pave all the cowpaths: good, bad and indifferent. Once the good features are located, a manual rewrite is just as efficient as automated translation.

Automated translation cannot capture meaning, identify use cases or write unit test cases. Thoughtful manual analysis of meaning, usability and unit tests is how the value of legacy code and data is preserved.

COBOL Rework

See this article: "The COBOL Brain Drain" in ComputerWorld.  This article is very, very well written and repeats a number of common fallacies.

The fallacies lead to expensive, labor-intensive in-house software development and maintenance.  Either there's a lot of effort poking at the COBOL code.  Or there's a lot of extra code to "wrap" the COBOL code so that it's never touched.

"Migrating large-scale systems built in Cobol is costly and risky."  A popular position.  But the risks are actually quite small; possibly non-existent.  The risks of not converting are usually higher than the risk of conversion.

The perception of the COBOL code is that it's filled with decades of tricky, nuanced legacy details that are hard to fully understand.  This is only partially true.

A great deal of the tricky code is simply redundant.  COBOL is often written with copy-and-paste programming and blocks of code are simply repeated.  It's also important to note that some of the code is no longer exercised in the first place.

Mythical Risk

The "risk" comes from the perceived opacity of  the tricky, nuanced legacy details.  It doesn't appear to be clear what they mean.  How can a project be started when the requirements aren't fully understood?

What appears to be the case in reality is is that this tricky code isn't very interesting.  Most COBOL programs don't do much.  They can often be summarized in a few sentences and bullet points.

Detailed analysis (641,000 lines of code, 933 programs) reveals that COBOL programs often contain several commingled feature sets.

• The actual business rules.  These are often easy to find in the code and can also be articulated by key users.  The real work is usually quite simple.  
• A bunch of hackarounds.  These are hacks to work around bugs that occur elsewhere in the processing.  Sometimes a hackaround introduces additional problems which require yet more hackarounds.  All of this can be ignored.  
• Solutions to COBOL data representation issues.  Most of these seem to be "subtype" issues: a flag or indicator is introduced to distinguish between subtypes.  Often, these are extensions.  A field that has a defined range of values ("A", "C" or "D") has a few instances of "*" to indicated another subclass that was introduced with a non-standard code for peculiar historical reasons.

Once we separate the real code from the hackarounds and the representation issues, we find that most COBOL programs are trivial.  About 46% of the lines of code (74% of the distinct programs) involves programs that read one to four files to write a report.  In effect, these programs do a simple "relational join" or query.  These programs have single-sentence summaries.  

The hackaround problem is profound.  When looking at COBOL code, there may be endless copy-and-paste IF-statements to handle some issue.  There may be whole suites of programs designed to work around some issue with a third-party package.  There may be suites of programs to deal with odd product offerings or special customer relationships.  

The remaining 26% of the non-trivial programs follow a normal distribution of 1/4 simple, 1/2 moderately complex, and 1/4 regrettably and stupidly complex.  That final 5% of the programs will also be a whopping 20% of the lines of code.  They are the few big programs that really matter.  

Risk Mitigation

The risk mitigation strategy involves several parts.  

1. Data Profiling.  The COBOL data may have considerable value.  The processing is what we're trying to rewrite into a modern language.  A profile of every field of every file will reveal that (a) most of the data is usable and (b) the unusable portion of the data isn't very valuable.   
2. Triage.  We can summarize 80% of the code in simple sentences.  46% of the code is single-sentence summaries.  34% of the code has multiple sentence summaries.  The remaining 20% requires in-depth analysis because the programs are large; they average of 2400 lines of code each.
3. Test-Driven Reverse Engineering.  Since a 5% of the programs do the real updates, it's important to collect the inputs and outputs of these few programs.  This forms a core test suite.
4. Agile Methods.  Find the user stories which really matter.  Find the COBOL programs that implement those user stories.

The most important risk reduction strategy is to take an Agile approach.  We need to prioritize based on value creation.  All COBOL programs are not created equal.  Some fraction can simply be ignored.  Another fraction produces relatively low value, and can be converted last.

The second most important risk mitigation is to build conversions from legacy COBOL files to the new, preferred databases.  The data is preserved.  

There's almost no risk in rewriting the 46% of low-complexity COBOL lines of code.  This code is trivial.  Note that some of this code actually has no business value.  Simply ignoring the no-value code also reduces risk.  Since we're using live files to drive testing, we can easily prove that our new programs work.

It's not risky to focus on the 20% of high-value COBOL lines of code.  This contains most (or all) of the processing that the business needs to have preserved.  They can articulate the user stories; it's easy to confirm that the COBOL does what the business needs.  It's easy to use live data to drive the reverse engineering.

The remaining 34% of the code base may actually involve a small amount of overlooked complexity.  There may be a nuance here that really matters.    

This overlooked "nuance" is something that the users didn't articulate, but it showed up in our unit testing.  We couldn't reproduce an expected output because we didn't correctly locate all the processing steps.  It wasn't in our summary of the 80% of moderate-to-low complexity programs.  It wasn't in our detailed analysis of the 20% subset of hyper-complex, large programs.  

We've reduced the risk by reducing the volume of code that needs to be examined under the microscope. We've reduced the risk by using live files for parallel testing.  We've reduced the risk by collecting user stories.

The remaining risks are ordinary project risks, unrelated to COBOL or legacy data.

The Other Great Lie

Another popular fallacy is this:

"The business wants us to make investments in programming that buys them new revenue. Rewriting an application doesn't buy them any value-add".

The value-add is to create a nimble business.  Purging the COBOL has a lot of advantages.

• It reduces the total number of lines of code.  Reducing costs.  Improving time-to-market for an IT solution to a business problem.
• It reduces the number of technologies competing for mind-share.  Less thinking about the legacy applications is less time wasted solving problems.
• It reduces the architectural complexity.  If the architecture is a spaghetti-bowl of interconnections between Web and Legacy COBOL and Desktop, then following the spaghetti-like connections is simply a kind of intellectual friction.

The COBOL does not need to be purged all at once through a magical "big-bang" replacement.  It needs to be phased out.  

Agile techniques need to be applied.  A simple backlog of high-value COBOL-based user stories is the place to start.  The prioritization of these stories needs to then be clustered around the data files. 

Ideally all of the programs which create or update a given file (or related group of files) can be rewritten in a tidy package.  The old files can be used for Test-Driven Reverse Engineering.  Once the programs have been rewritten, the remaining COBOL legacy applications can continue to operate, using a file created by a non-COBOL application.

Each file (and related cluster of programs) is replaced from high-value down to low-value.  Each step creates a more nimble organization.

Legacy Data Preservation

Extracting legacy data can be really, really hard. However, it's of central importance because data lives forever. Application "logic" and "business rules" come and go.

Today's case study is a dusty old Dell Inspiron Laptop running Windows 98 with Chartview software.

Problem 1. Chartview. No extract or export capability, except to a GPS via a serial port. I guess I could solder up a PC serial connector to a serial-USB interface so my Mac could read the stream of NMEA 0183 messages that contain routes and waypoints. But that seems complex for a one-time transfer.

Problem 2. Windows 98. Won't mount any USB device I own. No solid-state disk, no rotating disk. Nothing. I have the original install CD with all the extra drivers. Didn't help.

Problem 3. Dell Laptop. DVD player, floppy disk drive, and a USB port that Windows 98 doesn't seem to know what to do with. No ethernet, only a modem connection.

How do we preserve the waypoints and routes on this ancient Dell so that we can replace it with a nice, new Standard Horizon CP 300i and MacBook Pro running GPSNavX?

Raw Data

To get the raw data, I pulled the disk drive, mounted it in an IDE-USB enclosure and pulled the relevant routes and waypoints files. Now we have something we can work with.

The file formats are undocumented, but the data's not complex, making it easy to explore. Also, we can look at the old Chartview GUI to see the data and compare it with the raw bytes on the file.

Modern software is more properly normalized, simplifying the conversion. The legacy Chartview route data included each waypoint -- unrelated to the master list of waypoints -- along with bearing and range information, as well as compass deviation and projected speed. Really. A modern GPX file as used by GPSNavX or iNavX only needs the waypoints. Nothing else. New software will correctly calculate range and bearing to next waypoint as well as lookup the magnetic deviation from standard tables. So we don't need to preserve all of the data.

Pass 1

The first pass is to write simple "hex dump" utility in Python to see what's even in the files.

Something like this seems to allow enough flexibility to get a good view of the record sizes and field contents in the file.

def hex_print( bytes, offset=0 ):
for section in xrange(0,len(bytes)+31,32):
    block= bytes[section:section+32]
    print( '    ', ' '.join( '{0:3d}'.format(x+section) for x in xrange(32) ) )
    print( "{0:4d}".format(offset+section), ' '.join( '{0:3d}'.format(ord(x)) for x in block ) )
    print( '    ', ' '.join(  "  {0}".format(x) if 32 <= ord(x) < 128 else '   ' for x in block ) )
    print()

def hex_dump( file, size=32 ):
offset= 0
with open(file,'rb') as data:
    print( '    ', ' '.join( '{0:3d}'.format(x) for x in xrange(size) ) )
    block= data.read(size)
    while block:
        hex_print( block, offset )
        block= data.read(size)
        offset += size

Once we have a sense of what's going on, we can use the Python struct module to get the real data.

Pass 2

In the case of Chartview marks, we have a complex, but manageable structure definition. Some of the field sizes are conjectures. It's possible that all those filler bytes are some kind of word or x386 paragraph alignment; it's also possible that I've misinterpreted some of the less relevant numeric fields. The two double-precision values, however, are rock solid.

mark_structure =   "=b 4x 6s 45x 502s   d   d 10x h h h 10x 8s x 6s x 8s x 6s 31x  f  f 32x"
Mark= namedtuple( 'Mark',
 "record, name, text, lat, lon, display_name, enable_mark, anchor_mark,"
 "dt1, tm1, dt2, tm2, arrival_radius, max_xte" )

Given this structure and the associated named tuple, we can write a pleasant (and highly reusable) generator function.

def gen_items( file, structure, record_class ):
 size= struct.calcsize( structure )
 with open(file,'rb') as data:
     block= data.read(size)
     while len(block) == size:
         raw= record_class( *struct.unpack(structure,block) )
         yield raw
         block= data.read(size)

This makes for a simple application to extract the marks. An application can reformat them into GPX or CSV format.

Something like this is a good starting point.

def print_marks( file ):
  for mark in gen_items( file, mark_structure, Mark ):
      print( strip(mark.name), mark.lat, mark.lon )

We can easily write a version which includes the formatting required to get the latitudes and longitudes into a format acceptable by GPSNavX or X-Traverse.

Routes

Routes are more complex than marks because they have a header, followed by the details as a sequence of individual waypoints. Since Chartview doesn't normalize these things, each route can have duplicate waypoints, making it very difficult to get them loaded into an application that normalizes properly.

How many WP1's can you have? The answer should be "one". Each additional WP1 is a problem. But it's a small problem. For the WPx points, we simply disambiguate them a route number. There seemed to have been a limit of 15 routes, so we can just expand WPx to WPx-rr, where rr is the route number.

Bottom Line

Data is preserved. Legacy PC and GPS can be chucked (or sold on eBay to a collector).

It's important to note that data outlives application software. This is a universal truth -- data lasts forever, applications come and go. Highly optimized data structures (like the legacy Chartview files) are a bad policy. Highly usable data structures (like GPX files) are more valuable.

Python does a marvelous job of making a potentially horrifying data conversion into something like a few evenings trying to find the key pieces of data in the legacy files. Perhaps the hardest part was tracking down single and double-precision floating-point numbers. But once they were found -- matching known latitudes and longitudes -- it was clear sailing.