#!/bin/bash

cd '/folder/to/store/your/backups/'
for x in db-name-a db-name-b db-name-c
do
   mysqldump --user=username --password=pass $x --lock-tables --add-drop-table > $x`date +%u`.sql;
done

So what’s up with this little snippet? The first line with the ‘cd’ commad, we’re just changing into the directory where all the sql dumps will be stored. I also store this little script in there (backup.sh).

The next line is a basic loop. we list all of the local database names we want to backup, right after the “for x in” phrase, separated by a space.  The indented line is what actually dumps, names, and saves the file. On my local development computer, I just pass in the root username and pass, so it will work for each database. Each backup file then follows the “db-name” + “day of week” naming convention. For example, db-name-a1.sql would be Monday’s backup, db-name-a2.sql would be Tuesday’s backup, etc. A week’s worth of backups is fine for me, though you can customize this to your liking.

And the command line, you can test if this is working like so (assuming you are in the same directory as the file):

$ sh db-backup.sh

Not Working?

If it’s not working, a couple things may be happening. You can alter the script with more conditionals to output some general errors, but OS X will actually mail you a detailed copy of the error. Getting at it can be a little cryptic though. At the prompt, type mail:

$ mail

If you see a message like “You have mail in /var/mail/your-username” then you should check it out. You can use the terminal to check it (man mail to check the manual entry for mail). Or, what I tend to do is just open the entire mail file in my text editor, which is TextMate - using the command line tool:

$ mate /var/mail/your-username

Some juicy debugging info may be in there. You can delete everything in the file and save it if  you like. This will get rid of the “You have mail in…” message whenever you start up terminal.

Automating It

I have this script run everyday at 3pm - I do that with a cron job, using Cronnix as a graphical interface:

One problem you may have is that your crontab’s path is not necessarily the same as your user account’s shell path. For example, if you are seeing an error like “mysqldump - command not found” - simply go back to the terminal, and enter “which mysqldump”:

$ which mysqldump

It will output the full path to this command. Copy that path and paste the full path into your script. So for example, the line following the “do” command may look like:

/Applications/MAMP/Library/bin/mysqldump --user=user --password=pass $x --lock-tables --add-drop-table > $x`date +%u`.sql;

I use the MAMP version of mysql dump, so I need to tell the script exactly what version to use.

From here, I use SVN to keep this folder under version control as well, which makes switching from the desktop to the laptop much easier when developing. Cheers!

Let’s say your Drupal core is located at my-drupal-core.com. Install Drupal there, this will be your master copy.

Within your ’sites’ folder, create a folder for each domain you will want to run off this core installation. Let’s say we’ll make a Drupal site, based on this core, which will be drupal-slave.com. So, within your sites folder, create a drupal-slave.com folder. I prefer using a different database for each site. So create the database for the slave site, and then copy the settings.php file from your sites/default folder, alter it for your slave database, and put it into the drupal-slave.com folder. While you’re in there, create a files folder, you may need to change its permissions to 777.

So, your drupal-slave.com folder should look like this:

sites
   -- drupal-slave.com [folder]
      -- settings.php
      -- files [folder]
      -- modules [modules specific to this domain]
      -- themes [themes specific to this domain]

Now, on my server, every domain has a vhost.conf file, located inside a domains conf directory - this is at least how Red Hat does it. If you have a conf folder, but no vhost.conf file, go ahead and create it - you’ll likely need to login as root to do this.

Now, within your vhost.conf file (for drupal-slave.com, in this scenario), you’ll need to do something like this (alter for your own needs):

DocumentRoot /httpd/vhosts/my-drupal-core.com/httpdocs<Directory /httpd/vhosts/my-drupal-core.com/httpdocs>
  php_admin_value open_basedir /tmp:/httpd/vhosts/my-drupal-core.com/httpdocs
  AllowOverride All
</Directory>

Then, (gracefully) restart Apache - viola! Works for me at least. By the way, the AllowOverride All is to allow the my-drupal-core.com’s .htaccess file to work on all slave sites. The /tmp directory allows the slave site to write to the tmp directory for file uploads. Hope this helps someone.

Security

Note that with a multi-site setup, you really need to be careful of who can use the PHP filter (preferably, nobody). Reason being you can use PHP to read/output any domain’s settings.php file and gain access to the database username and password for any domain sharing the Drupal installation, including the master domain. Careful! This alone makes me think I won’t be using a multisite setup for high profile sites, or any site I’m not directly managing.

What do to?

Note: This page assumes some familiarity with how Drupal works.

The answer is the following sequence:

1. Alongside the regular data query, query the user database for a username using a LIKE query, and if one is found, grab the user id too (uid).
2. Perform a search for published content that matches that user id.
3. Count the total pieces of content found.
4. Spit out a link on the search result page that says something like, “Looking for posts written by Joe Author? (# found)”

I was hoping I could use the Views module for this, but I couldn’t figure out how to do a “LIKE” query with it. Besides, rather than loading a huge view object, why not to write a couple simple functions to do this? They can be efficient, and still take advantage of Drupal’s nifty (and secure) database functionality. The finished search page will look something like this:

Step 1: Build the query function

First things first - open up your theme’s template.php file. This is the place we store functions that we want to make available to the theme you are using. We’re going to add the following function to it - I’ve commented it pretty heavily:

/**
  * This function will search the database for an
  * aurhor's name, if it finds a match, will
  * count how many published stories and forum topics
  * they have
  * @param $name - the user name being searched for
  */
function get_user_content_total($name) {
  // this is the placeholder for the output link we're building
  $output = '';

  // build a user name search query with a string placeholder
  $sql = "SELECT name, uid FROM {users} WHERE name LIKE '%%%s%%'";
  $result = db_query(db_rewrite_sql($sql), $name);

  // build a count query with a digit placeholder for the user_id (uid from last query)
  $sql_count = "SELECT COUNT(node.uid) as count
  FROM {node} WHERE ({node}.status <> 0) AND ({node}.type in ('forum', 'story')) AND ({node}.uid = %d)";

  // iterate over the results
  while ($data = db_fetch_object($result)) {
    $result_count = db_query(db_rewrite_sql($sql_count), $data->uid);
    $data_count = db_fetch_object($result_count);

    // if we found any stories or forum topics, generate the text and link
    if ($data_count->count > 0) {
      $link_text = t('» Looking for content written by %author (%count)?', array('%author' => $data->name, '%count' => $data_count->count));
      $link = l($link_text, 'user/' . $data->uid . '/recent', array('html' => TRUE));
      $output .= '<p>' . $link . '</p>' . "\n";
    }
  }
  return $output;
}

You might be wondering about my link. Basically, I have a view already setup to fetch content written by a specific user. More on that over here. Ok, now we have some data to work with, so on to displaying it.

Step 2: Override the Search Results Template

The search module has a template file name search-results.tpl.php - we’re going to override that template file simply by making a file of the same name in our theme’s directory. So, your theme’s directory, create a file called search-results.tpl.php and put this into it:

<?php
print get_user_content_total(arg(2));
?>
<dl class="search-results <?php print $type; ?>-results">
  <?php print $search_results; ?>
</dl>
<?php print $pager; ?>

Now, the only difference between the search module’s search-results.tpl.php and ours is that ours calls our function at the top - the rest is exactly the same. And the nice thing about overriding it in this manner is that the next time we upgrade Drupal, our custom template here won’t get overridden because we’re not touching any core files.

After you create this file, you’ll need to visit admin/build/modules, which will rebuild the template files and let Drupal know that we are overriding something.

The last thing you may be asking is what’s with the arg(2) thing. That is just Drupal’s way of fetching things from the current URL. When you search with the search module, the URL is something like:

http://www.your-drupal.com/search/node/search words

The counting starts at 0, and does not include the domain. So ’search’ is 0, ‘node’ is 1, and ’search words’ is 2. Since this URL is always structured this way for a search result page, we can just pull the search query words directly from it, and rely on Drupal’s database functions to clean them up for safe querying.

This may not be the best way to do this - so I’m all ears if there’s a better way, cheers!

So, here’s a handy little function. Just plop it in your template.php file for your theme of choice, and it will be available where you need it on your site (as long as you’re using that template!):

function getUserName($id) {
  $sql = "SELECT name FROM {users} WHERE uid = '$id'";
  $result = db_query(db_rewrite_sql($sql));
  $username = db_fetch_object($result);
  return $username->name;
}

To use it for output, just do something like:

<?php print getUserName(1234); ?>

Using This With the Views Module

I often use this with the Views module. For example, I have a View for displaying all content created by a given user. The URL looks something like this:

http://www.some-drupal-site.com/user/1234/recent

The “1234″ is the user id argument getting passed to the view. The view will then go in and fetch content created by this user id. Oftentimes I want to create a nice header in the View with dynamic output - but Views doesn’t really allow that sort of thing in the header/footer areas under Basic Settings. So I use this function to get around it. In the view’s header, I do this to fetch a username:

<?php
  $view=views_get_current_view();
  $user_name = getUserName($view->args[0]);
  print '<h1>Content created by  ' . $user_name . '</h1>';
?>

*Note that to get the PHP input type working, you need to allow the PHP Filter in the modules section, and then select PHP as the input type for this view’s header.

Note that after you click the Update button, the Views module may give you an error message - that’s OK. It just doesn’t know what to do with this php code outside of the actual page you’re going to use it on.Below is a screenshot example.

Hope this helps someone! And if you know a better way to do this, speak up! Cheers.

Turns out, the Bbcode module does just the trick. Even if you don’t normally use it, you can add this input filter to your standard HTML filters, and it will work. The first step is to download and install the module.

Next up, enable the module, and add it to your input filters (located at /admin/settings/filters):

As you can see,  you don’t even need to assign it any roles. However, the Filtered HTML and Full HTML filters can use it, just set them up like so:

Next, go in and and configure the Bbcode module to encode email addresses:

Now, to use it - just use the standard bbcode to encode any email address:

[email]someone@domain.com[/email]

And when the page is rendered, the underlying HTML will all be JavaScript gobbly-gook, but to the end user, it will be a normal link.

Modifying the DNS and MX records was very easy (note if you use Postini, you will need to use different records) - they provide clear directions on this. However, make sure you install SPF records, this gives Google/Postini permission to send email on behalf of your domain. This will ensure you make it through today’s highly sensitive spam filters.

The only part of the transfer that made me a little nervous, and gave me a little trouble, was migrating my existing IMAP email over to the GAFYD servers. It’s really just a matter of knowing what IMAP client your current email server uses, and then entering the correct settings. Google provides a handy tool for doing this, but only supports certain IMAP servers, so check that yours is on the list. As of this writing, the supported IMAP servers are: Microsoft Exchange Server 2003, Cyrus IMAP Server, Courier-IMAP, and Dovecot.

Here are the settings I used:

143 is the default IMAP port. The thing that was tripping me up was the “IMAP Path Prefix.” Is this the path to where the email is stored on the server? A folder prefix name? It seemed no matter what I put in there, the only email folder it could find was “Inbox” - none of my subfolders where showing up.

Finally, I just left it blank, and whammo! All my subfolders were found. However, the “exclude” option did not work for me, which is fine - I can clean that up later.

After verifying that everything looked OK, I started the transfer. When I got back from lunch, it was finished! Cool.

Folders to Labels

This feature didn’t quite work as I expected it to. All my IMAP folders were indeed correctly labeled, but they were labeled with the full folder path. For example, if I had “folder-b” inside “folder-a” which was inside my Inbox, the label would be: “Inbox.folder-a.folder-b” - which is actually a smart way to do it because it takes into account that 2 folders may have the same name, but be in different locations. After the import, all I had to do was tweak my label names.

Am I Glad I Did It?

Absolutely. I love it. Being a huge fan of keyboard shortcuts, I can fly around Gmail like no other email program. And the spam filtering (along with Postini - a GAFYD free add-on), is the best. It just works, even better than Spam Assasin. Postini sends me a daily email with whatever emails it has quarantined, so I don’t have to constantly log in and check to see if a legitimate message was caught. I also enjoy the full suite of GAFYD apps - in particular the Calendar. And if you’re on a Mac, you’ll want to check out MailPlane - an excellent way to work in Gmail.

Cheers.

In the following example, my current location is my Desktop, and I’m looking for a newsletter folder called “goodie69.” Assuming it’s found, open it in TextMate:

[ ~/Desktop ]$ find . -type d -name 'goodie69' -exec mate {} \;

Note that you need the TextMate command line tool installed and in your path to do this.

So, first I’m issuing the find command, the single dot specifies start in the directory I’m currently in, the “-type d” means I’m only looking for directory names, and then I’m taking the results and executing the “mate” command. You need to close out the exec command with the brackets, backslash and semi-colon.

If you use BBEdit, and have the bbedit command line tool installed, you can replace “mate” with “bbedit”.

You can also specify any directory to start looking in (by default, this command will look in subdirectories). For example, if you have a folder called “clients” inside your home folder, and want to look in there for a particular folder, you can do something like this:

[ ~/Desktop ]$ find ~/clients -type d -name 'goodie69' -exec mate {} \;

And if you want to search for a file and open it in TextMate:

[ ~/Desktop ]$ find ~/clients -type f -name 'i-like-muffins.txt' -exec mate {} \;

Or,  you can just use Quicksilver ;)

To do this in Quicksilver - you just start typing the name of the folder, then tab over and select “open with” as the next option (or just start typing “with”), then tab over start typing “TextMate” until TextMate shows up. Then Enter. Whamo. If you don’t see the “open with” option, refer to the article above.

However, there’s a feature that will allow you to FTP your SQL file to an upload directory of your choosing, and then instruct phpMyAdmin to run the query file. Here’s how you do it.

1. Open up your FTP program, and navigate to the phpmyadmin folder on your server. Inside that folder is a file called config.inc.php - open this up in your trusty text editor.
2. While you’re still in your FTP program - create a directory inside the phpmyadmin directory called upload:
   
3. Towards the bottom of the config file, you’ll notice a potential setting for $cfg['UploadDir']. Change it so that it reads the following:
   
   Don’t forget to add the “./” before “upload” - this just tells the program to look in the existing directory (phpmyadmin) for a folder called “upload” - there’s no need to change file permissions or anything.
4. Save it, and then go back to your ftp program, and upload a sql file to that directory.
5. When you go to the Import tab in phpMyAdmin, you’ll now see a drop-down list of available files within your upload directory. Pick the one you want to run.
   
6. Sit back, have some coffee.

Some Notes

• You can compress the file, which will certainly make your FTP upload time shorter. I found that gzip works best for that. Sometimes a zip file would give me an error.
• Note that you can still run into trouble if your query is HUGE. If your php execution time and/or timeout settings are not big enough in your php.ini file, executing your query can be problematic. However, you can always chop up your data inserts into numerous files, although that can be a pain. (Or, if you have SSH access, you can execute the query file that way.) I encountered this problem while trying to import a big Drupal database. I solved it be emptying (NOT dropping) all the search-related database tables, and then re-indexing the site once all the other data was inserted.

That’s it - I was pleased to discover this feature and hope it helps someone else out too!

I’m looking for a band in Chicago called Sugar Beat. So, I start my search like this:

Hmmm… Even though I’m not looking for anything about cooking or food, I do need to trim the fat here.

By placing the words “sugar beat” in quotes, I tell Google that I am looking for this phrase - in other words, do not show me a page that contains sugar AND/OR beat - only return pages that have the words “sugar beat” right next to each other and in that order. Furthermore, let’s get rid of the word “sweets” - we do this by simply ending our query with “-sweets” - that’s a minus sign, and the word sweets (no space in between). This will help disassociate our search from pages related to food/desert:

This still isn’t perfect, but I think you’re getting the idea. One thing I want to point out here is that Google ignores punctuation - notice in the above example, that “… sugar. Beat…” turns up. So, if you are writing for search engines and need to use certain keyword phrases, keep in mind that you can use punctuation within your keyword phrase without risk.

Also, note that you can subtract more that one word from your query like this:

"sugar beat" chicago -sweets -cookies

So, if we can remove words from our queries, can we remove phrases? Yes, just put a phrase in quotes:

"sugar beat" chicago -"sweet tasty goodies"

And if we can subtract, surely we can add - right? You betcha - this next query will only return pages that have the phrase “sugar beat” along with the word music, but that do not have the word sweets:

Now we’re getting closer!  That first site listing looks interesting… Can I limit a search to just one website? Sure can:

By simply finishing our query with site:www.songkick.com - we restrict our search to that site.

So, by simply altering our search query in some intuitive ways, you can find what you are looking for faster by either filtering out, or forcibly including, keywords & phrases into your search. I do this so much it’s become second nature - hope you find it useful too!

Drupal uses a style of commenting that I haven’t used much before, called Doxygen:

/**
 * Your comment text
 */

I like this quite a bit - not only is it nice and clean looking, but software has an easy time picking up on it (such as for generating documentation). It can be used to comment in both CSS and PHP.

In the following screenshot/example, you can see how Textmate picks up on this, and adds the comment text to the symbol menu in the lower right corner:

So now, not only can I access every CSS rule via this symbol pop-up menu, but I can access my sections as well. For those of us that don’t like to switch to the mouse, you can bring up the go to symbol pop-up box with the Shift+Apple+T keyboard shortcut, then start typing into the Filter box what you’re looking for. In this example I started typing “right” and since my Right Oriented Section starts with an asterisk, it jumps right to the top:

Then I just press Enter, and it jumps to that point in the file for me, with the cursor right there too.

Anyways - so there you have it!