Author: Tom

• Drupal/xcart to wordpress/Woocommerce
• Magento to WordPress/Woocommerce
• OSCommerce to WP/Woocommerce

Using database and text utilites for migration/development research

The best way to migrate a site from one platform to another is to use the tools and utilities designed for import export. Sometimes you get a situation where that won’t work any longer due to customizations, extreme legacy, or many other reasons. Or becuase there are no import/export tools available. Maybe the best prospect for getting the data is using wget recursively, then parse the html and do something with it.

wget –mirror https://example.com

for i in `find "pattern that matches your files"`; do php your-custom-script.php "$i"; done

The above would likely be tricky to get right.

One project I worked on involved migrating a site from Drupal to WordPress. It was a big site with an x-cart ecommerce site, and thousands of posts and images, along with tags and categories. Most development boils down to some variation of the same formula:

1. Define problem
2. Come up with a solution
3. Code it
4. Run it/test it
5. Debug it
6. Document it

The solution that we came up with was to loop over the source database, parse the posts out and insert them into the target database, retaining src_id in the target database. Keeping the legacy id allowed us to comb over the system as much as necessary to get all the relevant connections, like tags, categories, orders, customers.

Here is an important development technique for researching or reverse engineering how cms code writes to the database. Using the mysql console app, I investigated by running queries to show tables inside the database and to describe the individual tables. This process looks at table names and describes the details of that table:

This goes a long way, but it’s also nice to use grep. You can use mysqldump to get a text file of the database and use grep or vim to search that with regular expression. Two nice ways to use mysqldump for schema research are:

mysqldump database_name --skip-extended-insert > database_name-`date +%F`.sql

That command results in all the data from database_name dumping into a text file named database_name-2021-09-23.sql with a line for each record, rather than default of mysqldump fitting as many rows as it can into a 5k character long line. Be aware that skipping extended insert can take a while for a large dataset.

And:

mysqldump database_name --no-data > database_name-schema-`date +%F`.sql

Which will ignore the data and just write the table definitions to a similarly name schema file.

Once you have the the database in text format, you can use text utitlities like grep to search it. So if you have a post with a unique classname in it, to find where the system stores the classname, you can do ‘grep unique-class-name databasename-2021-09-23.sql’ and get the field name and table that contains it, then search code for where that field is updated.

You could also use the mysql information_schema database which might be more convenient, like this example query:

SELECT TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE FROM COLUMNS WHERE COLUMN_NAME LIKE '%field-search-string%';

Sql looks so shouty! It’s actually not case sensitive, you can type it i n lower case and it will work, but the convention is to do sql keywords uppercase for readability and follow the case of the database, table, and field as they were created.

Commandline Scripting

Migration scripting is a very effective use of the command line. This bypasses the webserver’s timeouts and memory limits, though it can effectively block the server, so know what you are doing before you do migration in production environemnt.

For your script, the top line should be this or something like it, so that the script only runs via command:

<?php if ( ! defined( 'PHP_SAPI' ) || 'cli' !== PHP_SAPI ) die( header( 'Location: /' ) );
ini_set( 'display_errors', 'On' );
ini_set( 'implicit_flush', 'On' );
// use this later to see how long script runs. Note that this is only fun when the script takes a long time.
$time_start = time();
include '../wp-load.php';

Commonly the scripts have to run from inside the web dirctory structure to take advantage of app functionality, but of course it is important for security that they are not accessible from the internet.

I like to grant permissions on the source_db to the webserv user of the target_db as set in wp-config.php. This configuration makes it convenient to pull data from the source_db and use whatever WordPress hooks and filters you need. The last line above includes the database connection, and since we’ve granted permission on the source_db to the target_db user, we can use that to run queries on both databases.

Your script should be harmless if you just run it witout options. I like to add a usage() function to show opts, then in the script parse the options and and do the right thing.

That part might look something like this:

// runs and returns current options as specified on command line and verified against allowed values 
function script_parse_args() {
	// each opt is a single character. The colon marks it as requiring a value. -h doesn't need an arg. -p does need an argument
	$short = 'p:hn';
	$long = array(
		'post_type:',
		'help',
		'no-action',
    );

	// get the cli params
	$raw = getopt( $short, $long );
	$out = array();

	// post_types
	$allowed = array( 'shop_order', 'wc_user_membership', 'wc_membership_plan', 'tribe_events', 'tribe_venue', 'page', 'product', 'order', 'user', 'video', 'teacher');
	
	if( empty( $raw ) or array_key_exists( 'h', $raw ) or array_key_exists( 'help', $raw ) ) {
		die( show_help() );
	} 
	
	if( array_key_exists( 'p', $raw ) || array_key_exists( 'post_type', $raw ) ) {
		// handle
		if( array_key_exists( 'p', $raw ) && in_array( $raw['p'], $allowed ) ) $out['post_type'] = $raw['p'];
		if( array_key_exists( 'post_type', $raw ) && in_array( $raw['post_type'], $allowed ) ) $out['post_type'] = $raw['post_type'];
	}

	if( array_key_exists( 'n', $raw ) || array_key_exists( 'no-action', $raw ) ) $out['test'] = 1;
	
	return $out;

}

The help/usage function that runs if there are no arguments or if -h –help is requested is this:

// Function shows the command line switches, cli switches
Function usage() {
	$msg =  "\tScript uses wp-cli for some features, media import in particular. 
	Imports posts of post_type from src database to the current database assuming script is called from within wp install
	Options: -n --no-action, -h --help, -p --post_type, 

	-h | --help show usage function
	-p | --post_type the wordpress post_type to be imported
	-n | --no-action Test mode, doesn't write anything to database
	
";
	echo $msg;
}

The basic plumbing of the script handled, we still need to do something with it. The main structure I settled on is to select a group of posts from the source_db and do a group of actions to them by loopiing over them. Like this pseudo-code:

// $condition could be anything -- id_field > 1000?  start_date < $certain_time?
$sql = "select * from src_table where 1 and $condition";

if( !empty( $sql ) ) {

	$posts = $wpdb->get_results( $sql );
	
	$i = 0;
	foreach( $posts as $post ) {
		$i++;

		do_post( $post ); 
		
		// don't need incrmental hundreds count on --no-action -n test
		if( !array_key_exists( 'test', $opts ) ) {
			if( $i % 100 == 0 ) echo "\n$i of $cnt \n";
		}
	}

	$time_end = time();
	$elapsed = $time_end - $time_start;
	if( $elapsed > 60 ) {
		$took = ( $elapsed / 60 ) . " minutes";
	}

	echo "\nTook: $elapsed seconds";
	echo !empty( $took ) ? ", $took\n": '';
	echo "\n\ntype: {$opts['post_type']} i: $i\n";
	echo "done\n";
}

So, it turns out to be quite easy, but … not especially well documented. So, if you are like me and this is your first Android, you’ll be pleased to find out that adding custom ringtones in linux (or any os, windows or mac) is fairly easy. So lets get on with it, shall we?

The problem: The default ringtones on my new google nexus samsung galaxy hspa+ i9250 suck.

The solution: add custom ringtones

They make this dead-easy for us, all we have to do is copy ogg files to the sd-card/Ringtones directory that already exists at:
/storage/sdcard0/Ringtones/

So, how do we convert mp3s to oggs? The way I did it was:
convert mp3 to wav with lame:
lame --decode file.mp3 file.wav

This results in a large file, 10x bigger than the mp3 was to start with. I used audacity to edit it down and get bite-sized chunks for my ringers. It was pretty easy, open your new wav file, then drag over the part of the file you want to get. It will be highlighted in the gui. Hit the play button, you can preview your selection. Once you have a good selection, go to file menu->export selection, and save it as file type, “Ogg Vorbis Files”.

And, I just found out that decoding w/ lame is not necessary. Audacity will open mp3 files just fine.

K, so once you get a file.ogg ringtone on your computer, you just copy it to your phone. I have a win7 laptop that does mtp nicely, so that’s how I got my file on the phone.

Ok, ok. So lets say you are using wordpress and want to find all the deprecated images in your theme’s image directory. Because I’m a madcap party animal, I’d say you could do something like this:

#!/bin/bash
for i in *.*; do
cd ..
RES=`grep -lIrc "$i" * | grep -v ":0" -`
if test -n "$RES"; then
echo "$i good, $RES";
else
echo "$i deprecated";
fi


#echo "move $i deprecated";
cd images/
done


fancy right? That’s how I get down for Saturday night.

Ok, so lets say just hypothetically that you had all the 80 episodes of the 3 seasons of Star Trek the Original Series listed in a text file named list.txt and you wanted to select one at random to simulate what you might’ve seen on channel 13 on your black and white tv.

Well, you could simply do this…

R=$[ $RANDOM % 80 + 1 ] && cat -n list.txt | grep $R

That is a pretty complex command, lets look at it piece by piece. The part before the double ampersand assigns a random number to the variable $R, based on the pseudo-random shell variable $RANDOM. We know our file is 80 lines long, so we choose a number between 0 and 80, then add one to make it inclusive of 1 and 80. The double ampersand tells bash to wait til that’s done, and if it is sucessful, then go on to the next thing. The next thing then cat’s the list (with line numbers via the -n switch), then greps the line containing your random number $R.

I do that, and it spits out something like this:
76 Season 3/Star Trek - 3x20 - The Way to Eden.avi

iPhoto is the default camera/photo application for Mac OS X computers. And it is not readily obvious how to get your images out of there, and into a folder for emailing to your web developer!–hence this post.

Permalink: http://www.kfdev.com/2010/02/05/iphoto-export/

0) Step zero is to create a new folder in your home directory. I called mine “web_uploads”.  Isn’t that a nice name?-all lower-case, no extended ascii characters, no punctuation, underscore instead of space. Boy, I just love it when I see a file name like that.

1) Crack open iphoto, and select the batch of photos you want to export. Regular Finder selection rules apply, command-click to select additional items, drag a lasso around multiple. Click one, then shift-click an arbitrary number of items forward, and all the items in the middle should be selected.

Note blue line around the selected items. I selected 5, but the same principle works for 500 or 5000.

2) From the File menu, select “Export”, or use the keyboard shortcut, Command-shift-e. That should bring up the export window:

I want the max width to be 800, so I’ve entered “800”, and iPhoto figured out that the max height should be 1067. Good going, iPhoto! I kept the default values for the other options. Format (original — my camera shoots jpegs, and that’s fine), and kept “Use extension” clicked.

Then, just click the “Export” button at the bottom.

3) Clicking the “Export” button pops up the familiar “open and save” dialog box. This has confounded more than one person, so don’t feel bad if you don’t know what to do. Or you may be saying to yourself, “hah. Babies in their cradles know how to work the open and save dialog box.” In either case, what you want to do is export the pictures into the folder you made starting out in step 0. How you do that is by choosing the folder you made, in this case, “web_uploads” Now, click “Ok”, and just watch while all the magick of exporting happens. Sweet.

I know, it should be harder, but it’s not. That’s all there is to it. 🙂

[nggallery id=1]