database | Eric London's Blog

Implementing a Subversion pre-commit hook to integrate with Drupal

Submitted by Eric.London on 5/8/2011

Tags:

Add new comment

In this article, I'll show an example of how to implement a Subversion pre-commit hook to integrate with Drupal. Pre-commit hooks can be used to execute any arbitrary code, such as deployment procedures, archiving databases, etc. For this example, I will show how to check for the creation of a subversion tag and archive the database.

To get started, I created a local subversion repository.

# create folder for subversion repositories
$ mkdir /var/subversion

# create the subversion repository
$ svnadmin create /var/subversion/project

Upon creating a new local svn repository, a hooks directory will be created. Example: /var/subversion/project/hooks

Inside this directory will be a bunch of sample scripts ending in ".tmpl" which contain example hook scripts. Here are the contents of the example pre-commit hook without comments:

$ cat pre-commit.tmpl | egrep -iv "(^#|^$)"
REPOS="$1"
TXN="$2"
SVNLOOK=/usr/bin/svnlook
$SVNLOOK log -t "$TXN" "$REPOS" | \
   grep "[a-zA-Z0-9]" > /dev/null || exit 1
commit-access-control.pl "$REPOS" "$TXN" commit-access-control.cfg || exit 1
exit 0

As noted in the pre-commit.tmpl file, there are 2 arguments being passed to the pre-commit script:

[1] REPOS-PATH   (the path to this repository)
[2] TXN-NAME     (the name of the txn about to be committed)

I created a new file called "pre-commit" and added the following contents:

#!/bin/bash

/var/www/vhosts/project.vm/scripts/svn-pre-commit.php "$1" "$2"

I then made the file executable.

$ chmod ug+w pre-commit

The above script simply passes the arguments to a PHP script contained with the Drupal project.

In my scripts folder (/var/www/vhosts/project.vm/scripts), I created the PHP script "svn-pre-commit.php" with the following contents:

#!/usr/bin/php
<?php

// get args
$repo = $argv[1];

// define path to mysql backups
$mysql_backups_path = '/var/www/vhosts/project.vm/database';

// define path to drupal docroot
$drupal_docroot_path = '/var/www/vhosts/project.vm/htdocs';

// get changed path
// example output:
// A   tags/20110503/
$svn_look = `svnlook changed $repo`;

// define pattern to break apart svnlook changed
$pattern = '/^\s*([A-Za-z])\s*(.*)$/';

// execute preg match
preg_match($pattern, $svn_look, $matches);
$svn_action = $matches[1];
$svn_changed_path = $matches[2];

// check if a tag is being created
if ($svn_action == 'A' && substr($svn_changed_path, 0, 5)=='tags/') {

  // get tag name
  $exploded = explode('/', $svn_changed_path);
  $tag_name = $exploded[1];
  
  // change dir to drupal docroot
  chdir($drupal_docroot_path);

  // backup mysql database using drush
  `/var/www/drush/drush sql-dump > {$mysql_backups_path}/tag_{$tag_name}.sql`;

}

I also made this script executable:

$ chmod ug+w svn-pre-commit.php

Now, assuming that my Drupal site is integrated with the subversion repository, and development is at a point to deploy/create a new tag, I executed the following command to create the subversion tag:

$ svn cp file:///var/subversion/project/trunk file:///var/subversion/project/tags/beta-0.1 -m "creating beta 0.1 tag"

To verify, I entered the directory containing my database dumps to checkout the result:

$ cd /var/www/vhosts/project.vm/database

$ ls -1
tag_beta-0.1.sql

Deploying a site with language translation hostnames and resolving hard coded hostname database records

Submitted by Eric.London on 11/19/2010

Tags:

Sometimes there's no escaping hard coded hostnames being stored in your Drupal database. If you have ever setup language translations (i18n) in production, and then deployed the site to a development environment (with a different hostname), you'll know exactly what I am talking about. The first thing you'll notice in your dev site is that all your urls are linking back to the production site, and you may even have a hard time logging in.

If you query the languages table, you'll see the following:

select language, domain
from {languages}

Results:

language	domain
de		http://de.ericlondon.com
en		http://www.ericlondon.com
es		http://es.ericlondon.com
fr		http://fr.ericlondon.com

At first I thought it would make sense to write a module or a hook_update_n() function to resolve this problem when deploying to another environment, but this method seemed rather clunky, repeatedly running the same revision of a module's update script. In the end, I decided to simply write a PHP script to run via Drush. The following code fetches your default language and all records in the language table, loops through them, and updates the hostname records to match your new hostname...

<?php
// ensure this script is being called from Drush ONLY
// ensure HTTP_HOST is being used
if (
  !is_array($_SERVER['argv'])
  || substr($_SERVER['argv'][0], -9) != 'drush.php'
  || substr($_SERVER['SCRIPT_FILENAME'], -9) != 'drush.php'
  || !strlen($_SERVER['HTTP_HOST'])
) {
  header('HTTP/1.1 403 Forbidden');
  die('Access Denied.');
}

$output = "";

// fetch hostname from globals
$http_host = $_SERVER['HTTP_HOST'];

// ensure http host exists
if (!$http_host || !is_string($http_host)) {
  die('Error occurred fetching http host.');
}

// set default http host
$default_http_host = 'www.' . $http_host;

// define default language
$default_language = 'en';

// define SQL to fetch default language
$sql = "
  select *
  from {languages}
  where language = '%s'
";

// execute SQL
$default_language_record = db_fetch_object(db_query($sql, $default_language));

// ensure record exists
if (!is_object($default_language_record)) {
  die('Error occurred fetching default language.');
}

// update default language record
$default_language_record->domain = 'http://' . $default_http_host;

// set new variable for "language_default"
variable_set('language_default', $default_language_record);
$output .= "Setting language_default variable to: " . $default_language_record->domain . "\n";

// fetch language records
$sql = "
  select *
  from {languages}
";
$resource = db_query($sql);
$language_records = array();
while ($row = db_fetch_object($resource)) {
  $language_records[] = $row;
}

// correct language records
foreach ($language_records as $key => $value) {

  // determine language prefix  
  switch ($value->language) {
    case $default_language:
      $prefix = 'www.';
      break;
    default:
      $prefix = $value->language . '.';
      break;
  }

  $new_domain = 'http://' . $prefix . $http_host;

  $sql = "
    update {languages}
    set domain = '%s'
    where language = '%s'
  ";
  db_query($sql, $new_domain, $value->language);

  $output .= "Setting language record for language: \"" . $value->language . "\" to: " . $new_domain . "\n";

}

drupal_flush_all_caches();

$output .= "Executed: drupal_flush_all_caches()\n";

$output .= "DONE.\n";

echo $output;
?>

I dropped this script (named: fix_env.php) in the root of my Drupal site, and then executed it via Drush using the following shell command:

drush --uri=http://tdb.erl.dev scr fix_env.php

The "--uri" flag allows you to specific a hostname for multi-site environments, and the "scr" flag allows you to specify a php script to execute.

NOTE: I'm in the habit of never using sites/default, because it's a real PITA if you ever try to move your site into a multi-site configuration ^_^

After running the script via Drush, my hostname database records have been corrected to match my development environment...

select language, domain
from {languages}

Results:

language	domain
de		http://de.tdb.erl.dev
en		http://tdb.erl.dev
es		http://es.tdb.erl.dev
fr		http://fr.tdb.erl.dev

Setting up a multi-site Drupal 6 installation with shared databases and single sign-on

Submitted by Eric.London on 9/7/2009

Tags:

12 comments

In this blog entry, I'll explain how I setup a multi-site Drupal 6 installation with shared databases and single sign-on. This will enable you to store your users (and other desired tables) in a separate database, and share them across multiple sites.

Before installing Drupal I created 3 databases, added 2 mysql users, and granted permissions. Both mysql users will have access to the shared database which will contain the shared tables:

$ mysql
# create new databases:
mysql> create database drupal_ms_1;
mysql> create database drupal_ms_2;
mysql> create database drupal_ms_shared;

# create new user "drupal_ms_1" and grant privileges to the databases:
mysql> grant all privileges on drupal_ms_1.* to 'drupal_ms_1'@'localhost' identified by 'drupal_ms_1';
mysql> grant all privileges on drupal_ms_shared.* to 'drupal_ms_1'@'localhost' identified by 'drupal_ms_1';

# create new user "drupal_ms_2" and grant privileges to the databases:
mysql> grant all privileges on drupal_ms_2.* to 'drupal_ms_2'@'localhost' identified by 'drupal_ms_2';
mysql> grant all privileges on drupal_ms_shared.* to 'drupal_ms_2'@'localhost' identified by 'drupal_ms_2';

For my setup I wanted to use a single Drupal filesystem, so I created a name-based Apache virtualhost to host both domain names (ms1.erl.dev & ms2.erl.dev):

NameVirtualHost *:80

<VirtualHost *:80>
ServerName ms1.erl.dev
ServerAlias ms2.erl.dev
DocumentRoot /var/www/vhosts/ms.erl.dev/httpdocs
ErrorLog logs/ms.erl.dev-error_log
CustomLog logs/ms.erl.dev-access_log common
</VirtualHost>

I downloaded and unpacked the Drupal installation file:

$ cd /var/www/vhosts
$ mkdir ms.erl.dev
$ cd ms.erl.dev
$ wget http://ftp.drupal.org/files/projects/drupal-6.13.tar.gz
$ tar -xzf drupal-6.13.tar.gz
$ mv drupal-6.13 httpdocs

I setup 2 new sites folders in the sites folder, and copied the default.settings.php file into my first site:

$ cd httpdocs/sites
$ mkdir ms1.erl.dev
$ mkdir ms2.erl.dev
$ cp default/default.settings.php ms1.erl.dev

At this point my databases and filesystem were ready to go so I installed Drupal for the first site (ms1.erl.dev). During installation, I entered "localhost" for the database host, drupal_ms_1 as the database, and drupal_ms_1 as the database user. I left the db_prefix setting blank (we'll change that later).

Now that Drupal was installed and the tables were created, it was time to modify the installation to enable single sign-on and shared databases.

You'll need to decide which tables to share across the sites. I decided on users, sessions, and authmap (but you could add more as desired, like roles, profile_*, etc). I used the following commands to transfer the desired tables from the drupal_ms_1 database into the shared database, remove the tables from drupal_ms_1, and then replicated drupal_ms_1 to drupal_ms_2:

$ mysqldump drupal_ms_1 users sessions authmap | mysql drupal_ms_shared
$ mysql drupal_ms_1
mysql> drop table authmap, sessions, users;
mysql> exit
$ mysqldump drupal_ms_1 | mysql drupal_ms_2

Next, I modified the settings.php for my first site (sites/ms1.erl.dev/settings.php) to enable the shared database and single sign-on configuration. The $db_prefix variable is used to specify a different database using the [DATABASE].[TABLE] syntax. You'll also need to add the $cookie_domain variable to ensure the cookie set by Drupal will work across both domain names.

<?php
// Replace:
$db_prefix = '';
// With:
$db_prefix = array(
  'default' => '',
  'users' => 'drupal_ms_shared.',
  'sessions' => 'drupal_ms_shared.',
  'authmap' => 'drupal_ms_shared.'
);
// Add:
$cookie_domain = '.erl.dev';
?>

I then copied the ms1.erl.dev settings.php file into my second sites folder. Edit the new ms2.erl.dev/settings.php $db_url variable to point to the drupal_ms_2 database (and update the user and password as well).

$ cp ms1.erl.dev/settings.php ms2.erl.dev

After you delete your domain cookies everything should be working! To test this out, log into your first site and then open a new window/tab and go to your second site. You should be logged in as the same user. If you log out from one site, you should also be logged out from the other site.

How to dump and compress all mysql databases on a server into separate files using PHP and PEAR

Submitted by Eric.London on 6/22/2009

Tags:

Add new comment

Here's a quick code snippet I just wrote to dump and compress (gzip) all mysql databases on a server into separate files using PHP and PEAR.

<?php
// include PEAR DB library
require_once('DB.php');

// define the DSN in an array
// NOTE: user must have access to all databases
$dsn = array(
  'phptype' => 'mysql',
  'username' => 'YOURUSER',
  'password' => 'YOURPASSWORD',
  'hostspec' => 'HOSTNAME', // localhost?
);

// instantiate a PEAR DB object
$DB =& DB::connect($dsn);

// check for an error
if (DB::isError($DB)) die($DB->getMessage());

// set the DB fetch mode to associative
$DB->setFetchMode(DB_FETCHMODE_ASSOC);

// define sql statement
$sql = "show databases";

// fetch sql result
$databases = $DB->getAll($sql);

// loop through results
foreach ($databases as $index => $result) {

  // define the mysqldump command
  $command = "mysqldump -u " . $dsn['username'] . " "
    . "-p" . $dsn['password'] . " "
    . "-h " . $dsn['hostspec'] . " "
    . $result['Database'] . " | gzip > "
    . $result['Database'] . ".sql.gz";

  // execute command
  `$command`;
    
}
?>

Taking a SQL statement and generating a table from the results

Submitted by Eric.London on 6/2/2009

Tags:

Add new comment

Functionality that I regularly create in applications is the ability to take a SQL statement and return a table of results for reports, debugging, etc. Here's a helper function I created to implement this functionality. I purposely left off pagination, sortable columns, and other features to focus on how the Drupal theme_table() function operates. This helper function will use the column names from the SQL as the table column headers, so make sure they are properly formatted.

<?php
// first, I defined this function in my module:
function _MYMODULE_sql_to_table($sql) {
    
  $html = "";
    
  // execute sql
  $resource = db_query($sql);
    
  // fetch database results in an array
  $results = array();
  while ($row = db_fetch_array($resource)) $results[] = $row;
    
  // ensure results exist
  if (!count($results)) {
    $html .= "Sorry, no results could be found.";
    return $html;    
  }

  // create an array to contain all table rows
  $rows = array();
    
  // get a list of column headers
  $columnNames = array_keys($results[0]);
    
  // loop through results and create table rows
  foreach ($results as $key => $data) {

    // create row data
    $row = array();
    
    // loop through column names
    foreach ($columnNames as $c) {
      $row[] = array(
        'data' => $data[$c],
        'class' => strtolower(str_replace(' ', '-', $c)),
      );
    }

    // add row to rows array
    $rows[] = $row;
        
  }
    
  // loop through column names and create headers
  $header = array();
  foreach ($columnNames as $c) {
    $header[] = array(
      'data' => $c,
      'class' => strtolower(str_replace(' ', '-', $c)),
    );
  }
    
  // generate table html
  $html .= theme('table', $header, $rows);
    
  return $html;
    
}

// then you can call it in your code...
function _MYMODULE_some_page_callback() {
  
  $html = "";

  $sql = "select * from {node}";

  $html .= _MYMODULE_sql_to_table($sql);

  return $html;
}
?>