In an attempt to clear my mind, and challenge myself, I asked Dave Olson if I could volunteer some time to help him blog an item or two. I was expecting to help write up about a local event around town, instead I was challenged a little bit more to my dismay.

Dave suggested to write a weekly blog post, about some of the work that I have been involved with. Of course he had answers to my protests;

I am sure somebody out there knows a better way than I, and I definitely don't know all there is to know on this subject. His answer, that is part of the reason one blogs, to get feedback from the community.

Should I be my blunt self, or put on a good company front, pasted with unicorns and butterflies? No, this is the new era of openness, one can be honest and open.

Who would be interested in what I have to say, aren't funny fluffy things what makes the web2.0 go around, who would want to know about my hacks? He sees more comments made on the posts that are more technical.

The past 2 projects working on database migration have been difficult for a number of reasons, including in my opinion inadequate details of the previous system and how the old features/data would interact with new proposed features, and the customer's implied understanding that old features (and thus data attached) already existing should "obviously" be implemented in the drupal project.

Techniques used so far rely heavily on one-off scripts using node_save() including saving cck fields including files and images (as imagefields).

I will leave information architecture to experts such as Gregory Heller. One thing though, the information architecture process becomes a whole lot more important when data import is involved.

Besides getting as much documentation on the current site, including database schema, file server, code if possible, I like to make a sample listing of tables, description and sample rows.

Listing of database tables

* Get a listing of tables from mysql database into tables.txt, one line script: # mysql -u {username} -p {databasename} -e "SHOW TABLES" | grep -v '^+' | grep -v 'Tables_in' > tables.txt

* Describe each table on mysql database, run this shell script, redirecting to tables-structure.txt #!/bin/sh
while read table
do
count=`mysql -u {username} -p{password} {database} -e "SELECT COUNT(*) FROM $table" | grep -v '^+' | grep -v 'COUNT('`;
echo "=== $table : $count rows ===";
mysql -u {username} -p{password} {database} -e "DESCRIBE $table";
echo;
done < tables.txt

* Show a random listing of 5 rows. Copy the shell script above, replacing "DESCRIBE $table" with "SELECT * FROM $table ORDER BY RAND() LIMIT 5". Run the shell script redirecting to tables-rows.txt

* Get a listing of tables from postgres database into tables.txt, one line script: # psql -U -c "SELECT tablename FROM pg_tables WHERE schemaname LIKE 'public' ORDER BY tablename" | grep -v 'tablename' | grep -v '^-' | grep -v '^(' > tables.txt

* Describe each table on postgres database, run this shell script, redirecting to tables-structure.txt #!/bin/sh
while read table
do
count=`psql -U postgres csm_080515 -c "SELECT COUNT(*) FROM person" | grep -v '^(' | grep -v '^-' | grep -v 'count'`;
echo "=== $table : $count ===";
psql -U postgres csm_080515 -c "\d $table"
echo;
done < tables.txt

* Show a random listing of 5 rows. Copy the shell script above, replacing "DESCRIBE $table" with "SELECT * FROM $table ORDER BY RANDOM() LIMIT 5". Run the shell script redirecting to tables-rows.txt