{tech}.Ben Buckman.net

This is a script I wrote in 2009 and updated a few months ago, to automate the creation of Bash scripts for synchronizing a database from a remote server to a local server via SSH. It's a single PHP file which presents a form, you fill in the form with the connection and DB info on both sides, and it creates a .sh file that you run to synchronize. (This script does not synchronize anything itself.) The original script used mysqldump and asked for database credentials; the new version uses Drush, so it's only for Drupal but doesn't need DB credentials anymore. (And if you use an SSH key it doesn't need SSH credentials either.) You will need Drush on both sides for it to work.

I'm not hosting this script because I don't want to be responsible for the security of your server information. Instead download the .tar file below and host it on your own server. Check out the code of the .sh script that it creates before you run it so you understand exactly what's going on.

(I'll be mentioning this in my Developer's Arsenal of Productivity Hacks presentation tomorrow at DrupalCampNYC, so stay tuned for that!)

I welcome any and all bug reports, fixes, or other feedback.

Update: Someone pointed out correctly in the comments that Drush already has sql-sync, so I realized I need to explain what this does better. sql-sync makes a remote connection to a database and transfers directly between them. Most of the servers I've worked with, however, do not allow remote login to their database server (except maybe from a separate web server). All the MySql security recommendations I've read say the same thing. So this script will dump the database on the remote server via SSH (run locally on that server), zip the file, transfer it down, and import it. It also backs up both databases which Drush doesn't.
(If Drush does in fact do this, please let me know!)

Update 2: I stand corrected! Thanks to the commenters for enlightening me, Drush does in fact do this. I'll take the script off of here, since it doesn't add anything (except the form maybe). The method is:
drush sql-sync SRC DEST --source-remote-host=XX --source-dump=YY --target-dump=ZZ (and some other options available at drush help sql-sync).

Someone wrote to me recently asking if I could help them set up a Drupal development environment for an outsourced dev team. I don't have time and declined the job, but continued a brief correspondence about the requested project requirements, which I thought were interesting:.

The requirements included (each a full installation of a 3rd party software package):

• Issue Tracking, Wiki, Forum
• Git repository
• Git repo server
• Git access control
• Hudson (Continuous Integration)
• Selenium
• SimpleTest
• JMeter
• Capistrano
• Drupal Mercury (includes EC2, Varnish)
• Image resizing software
• rsync

Whoa.

This is the list of tools that you'll find by googling "best practices development environment" or something like that. But it's massive tool creep. Several problems I see here:

• Toolsets need to grow organically. Dumping 10 enormous tools on a brand new team (outsourced/remote/global, let alone in the same office) is like throwing a 1000 page HR Handbook on a new employee's desk.
• Most of these can be [ironically] outsourced to 3rd party services. There are several excellent Git hosters for example; there's no need to host a repo yourself (unless you have some crazy security requirements).
• Tons of redundancy. Both SimpleTest and Selenium can do client-side unit testing. SimpleTest also does server-side; ditch Selenium.
• Each of these tools requires maintenance, upgrades, TLC. The team is hired to develop a product, not maintain other companies' software, why not focus on the essentials?
• Capistrano is great. I suggested they use Webistrano, however, and write a 5-line Ruby recipe that tells Drush to run SimpleTest before deploying. No need for Hudson anymore.
• Issue Tracking, Wiki, and Forum? I worked on a team with Jira and Basecamp and that was more than enough for critical details to get lost. "Did you see my message from last week?" - "Which tool did you put it in?" - "I think Basecamp, but the specs are in Jira, and the wireframes in DropBox, and the timesheet in Google Docs" ... recipe for confusion.
• This is a Drupal project. I asked several of the best Drupal developers I know if they've heard of Hudson; they hadn't. (I only have because I worked near a Java team that demo'd it for me. I didn't think it would be helpful for my workflow.) Having a list of tools like this on the job description for developers is likely to be a turnoff. Again, focus on essentials.

So I wish this project all the best. But I think they'd be much better off starting with some simple, well-known tools: Drush, SimpleTest, Webistrano OR Aegir (not both), Basecamp OR Jira OR Atrium. Better to over-use one really good tool than underuse half a dozen tools, in my opinion.

Thoughts, comments, rebuttals?

On a whim I decided to start learning AppleScript tonight. AppleScript is one of the great features of OSX, and allows scripts to communicate pretty much with any [decently built] application.

The first use case I could think of was a little toggle for quiet time, a "Do Not Disturb" toggle. It has 2 modes, Quiet and Noisy. In Quiet mode it puts Mailplane into its own "Do Not Disturb" mode, logs out of iChat, closes Skype, turns off Growl, and turns on Caffeine (assuming this quiet time also involves concentrated looking at the screen). Noisy mode is normal; flipping that switch logs back into iChat, and turns Mailplane and Growl back on. (It doesn't toggle all the same things; I don't always have Skype on and Caffeine shouldn't necessarily be turned off. More on that below.)

Here's the code:

-- figure out: how can it REMEMBER from one run to the next (e.g. were caffeine, ichat on?)
 
set appName to "DoNotDisturb Toggle"  -- for display later
 
-- register with Growl
tell application "GrowlHelperApp"
	set the allNotificationsList to {"toggle-announce"}
	copy allNotificationsList to enabledNotificationsList
	register as application ¬
		appName all notifications allNotificationsList ¬
		default notifications enabledNotificationsList
end tell
 
display dialog "Noisy or Quiet?" buttons {"Noisy", "Silent"}
set doMode to button returned of result
 
if doMode is "Noisy" then
	tell application "Mailplane" to set doNotDisturb to false
 
	tell application "GrowlHelperApp"
		launch
		notify with name "toggle-announce" title appName ¬
			description "Going back to Noisy mode" application name appName
	end tell
 
	tell application "iChat" to log in # what if it wasn't before?
 
else -- Quiet
	tell application "GrowlHelperApp"
		notify with name "toggle-announce" title appName ¬
			description "Going into Quiet mode" application name appName
		quit
	end tell
 
	tell application "Mailplane" to set doNotDisturb to true
 
	tell application "Caffeine" to turn on # (turn on for quiet but don't turn off on noisy)
 
	tell application "iChat" to log out
	tell application "Skype" to quit
 
end if

I turned it into an Application (File->Save As), so it's easy to run from the Dock.

What I need to figure out is how it can remember states from one run to the other: it should only log back into iChat, for instance, if it was logged in before going into Quiet mode. Does anyone know?

(This code will be part of my DrupalCamp NYC session on A Developer's Toolkit of Productivity Hacks.)

I had to do a brute-force deployment today, synchronizing functionality and content in 2 directions between a development and live site that split months ago. So I needed to see exactly what changed on each site's database to plan the deployment process. This scriptlet, run on the server with both sites, helped a lot.

(You'll want to do all this from a directory outside the webroot because you don't want the public to download SQL dumps.)

• Set up shortcuts to each site, as user 'admin', passing all prompts (not crucial for the rest but generally useful):

DEV="drush --root=/path/to/dev/site -u admin -y "
PROD="drush --root=/path/to/live/site -u admin -y "

• Make shortcuts to mysqldump each site's database, sorting by primary key and with full insert's - so each record is its own line and can be matched to the other. (You can use $DEV sql-connect and $PROD sql-connect, and copy/paste from there:

PRODDUMP="mysqldump --order-by-primary --skip-extended-insert -hHOST -uUSER -pPASS PRODDB "
DEVDUMP="mysqldump --order-by-primary --skip-extended-insert -hHOST -uUSER -pPASS DEVDB "

• Now get a list of all your tables. (This assumes one site has a more complete set):
$DEV sql-query "show tables" > tables.log

(Alternately, you could do this for both tables by combining them with >>, piping (|) to uniq and back to the file, for the full list from both sides.)

• Now get each table from development and production, and diff them. (The semicolons allow this to be consolidated to 1 line; I separate it here for readability.)

mkdir tables;
cat tables.log | while read TABLE; do 
  $DEVDUMP $TABLE > tables/${TABLE}-dev.sql;
  $PRODDUMP $TABLE > tables/${TABLE}-prod.sql; 
  diff -u tables/${TABLE}-prod.sql tables/${TABLE}-dev.sql > tables/${TABLE}.diff;
  echo "Done with $TABLE";
 done

• Now delete the files with no differences, they're just clutter. Those seemed to be 10 lines long, so I useD that as the magic number:

cat tables.log | while read TABLE; do 
  LCOUNT=`cat tables/${TABLE}.diff | wc -l`; 
  echo "$TABLE : $LCOUNT";
    if [[ $LCOUNT -lt 11 ]]; then 
      rm tables/${TABLE}.diff && rm tables/${TABLE}-dev.sql && rm tables/${TABLE}-prod.sql && echo "DELETED $TABLE"; 
    fi;  
 done

You can probably also ignore the cache and session tables. But this will give you a good view of what nodes, variables, users, and so on have changed.

If anyone wants to turn this into a Drush plugin (a la drush sql-diff PROD DEV --destination=tables), please do!

What kind of hacks, shortcuts, and tricks do you use in your development workflow? Let me know so I can include them in the session!

I learned a new Drupal API function today: views_embed_view() to programmatically display a view inside another template (for example). Previously I had a custom function that loaded, executed, and rendered a view, several more lines of code than this.
I'm using it to pull a "Other Articles by Author" block into the side of a node template (the author is an argument):
echo views_embed_view('other_articles', 'block_1', $node->uid);
(Could also do the same in a preprocessor, $vars['other_articles'] = ...

For those who prefer not to write any code (and I've been increasingly impressed lately at Drupal designers who build amazing sites without any custom code), the Views Attach module (different from Views attachments) can pull Views into nodes. But for the code-inclined like myself, it can't be simpler than one line.

Pressflow is a performance-tuned, MySql-specific distribution of Drupal maintained by Four Kitchens. Drush Make is a way of automatically compiling the files needed to build a big Drupal site.

I posted this issue on Pressflow's Launchpad queue (after raising the question with their team at D4D):

I'm using Drush Make to build my sites and I'd like Pressflow to be the default core. It's easy to put projects[] = drupal in the make file and it gets the latest 6.x from drupal.org. But how do I get the latest Pressflow? Currently I have:

projects[drupal][download][url] = "http://launchpad.net/pressflow/6.x/6.16.77/+download/pressflow-6.16.77.tar.gz"
projects[drupal][download][type] = "get"

For every Pressflow upgrade, I have to change the path there manually, which mostly defeats the purpose of Drush Make (and is a barrier to using Pressflow). It would be great if I could simply put in http://launchpad.net/pressflow/6.x/6.x-latest.tar.gz and it would always fetch the latest version. Or better yet, have projects[] = pressflow instead of "drupal," but I don't want to press my luck...

Thank you!

David Strauss (creator of Pressflow) replied:

Two options:

* Use Drush Make's support for Bazaar, and use lp:pressflow
* Use this URL to our build server: http://files.pressflow.org/pressflow-6-current.tar.gz

Nice!