Upgrade Your Drupal Skills

We trained 1,000+ Drupal Developers over the last decade.

See Advanced Courses NAH, I know Enough
Jun 04 2013
Jun 04

The Date Picker widget, when using webforms, defaultly appears in American form (Month, Day, Year). For UK sites this isn't often desirable. Doing a quick google and I stumbled upon 's blog post on how to re-theme the element from earlier this year. I thought it was certainly one approach, but felt there must have been a neater alternative. So here is my approach:


/**
 * Implements hook_webform_component_render_alter().
 */
function THEMENAME_webform_component_render_alter(&$element, &$component) {
  if ($element['#type'] == 'date') {
    $element['#process'][] = 'THEMENAME_webform_expand_date';
  }
}

/**
 * Process function to re-order the elements in the date widget.
 */
function THEMENAME_webform_expand_date($element) {
  $element['day']['#weight'] = 0;
  $element['month']['#weight'] = 1;
  $element['year']['#weight'] = 2;
  return $element;
}

Obviously you can replace THEMENAME with what ever theme or module name you like; in my case I put this in the template.php file.

Aug 10 2012
Aug 10

It's usually considered best practice to run Drush under the same user account that the webserver runs on. Some modules, such as XML Sitemap, Advanced Aggregator and cTools, often try to alter or generate files. If you run Drush under your own account then these files may get generated as you (and therefore become unwritable by the webserver) or, if the files were generated by the webserver, are usually unwritable by your user which can mean you get errors and being unable to delete/unlink files.

The solution is to run Drush as your webuser, usually Apache. For example:

su -s /bin/sh apache -c "/usr/bin/drush @sites cron -y"

This method also helps get around the problem/error This account is currently not available by running drush under a shell executed as Apache. On most systems, the apache user is secured to not have a shell account associated with it.

Mar 09 2012
Mar 09

New Relic is a great bit of kit - nobody can deny that. I recently needed to configure it on a standalone box so it could be open on a large screen so my colleagues and I could see the current status of our sites. New Relic has a Kiosk Mode which strips out some of the navigation from the page an optimises it for "viewing only". Very handy.

I wanted a setting to force (or at least default) the page into Kiosk mode so that when I opened the bookmark on the browser, I didn't have to scroll to the bottom, click it, scroll back up and then refresh any other relevant tabs. It didn't look like there was a feature for this and the Kiosk Mode link was just a java function call; there was no URL.

So I turned to GreaseMonkey.

Enter GreaseMonkey

GreaseMonkey is a Firefox Addon which allows you to bind custom JS scripts to all (or specific) pages. There is a repository of over 74,000 scripts which let you customize all kinds of sites (Twitter, Gmail, Facebook, etc) including Drupal.

However there was nothing for New Relic.

Until now.

Using GreaseMonkey with New Relic

As it turns out, the script is very simple.

// ==UserScript==
// @name            New Relic Kiosk Mode
// @namespace       http://www.thingy-ma-jig.co.uk/
// @icon            http://www.thingy-ma-jig.co.uk/sites/thingy-ma-jig.co.uk/files/greasemonkey/NewRelic_inline_small.png
// @description     Force New Relic into Kiosk mode by setting the cookie on page load if ?kiosk is in the URL
// @include         https://rpm.newrelic.com/*?kiosk
// @updateURL       http://www.thingy-ma-jig.co.uk/sites/thingy-ma-jig.co.uk/files/greasemonkey/newrelic-kioskmode.user.js
// @version         1.0
// ==/UserScript==

unsafeWindow.RPM.kioskMode._setCookie();

Once the cookie is set, New Relic handles the rest. All you need to do is append "?kiosk" onto the URL (maybe in your bookmarks?) and the page loads in Kiosk mode.

You can install the script by clicking on this button.

Install Kiosk Mode Script

Find It On UserScripts.org

I have also added the script to UserScripts.org, for those that are interested.

Dec 15 2011
Dec 15

Have you ever needed or wanted to pull a remote Git Repository on Drupal.org down as a zip or tarball? You know, the way Github does? Most project releases have fairly recently built tarballs which is awesome - but Sandboxes do not (so it seems - please correct me if I'm wrong!).

The following snippet lets you "archive" a remote repository, pull it down as a tarball, and extract it in-place:

git archive --format=tar --prefix=PROJECT/ [email protected]:sandbox/USERNAME/123456789.git BRANCH | tar -xf -

Some important notes:

  • The prefix is very important - without it, tar extracts to the current folder.
  • The trailing slash on the prefix is equally important - without it all files have PROJECT at the beginning!
  • The number (next to .git) references the sandbox Node ID.

I have tried using the "zip" format, however the unzip bash command doesn't accept stdin as a source. It looks like funzip might hold some promise thought…

EDIT: - looks like you can only do this if your user has access to the repository.

Nov 14 2011
Nov 14

According to the Drupal Coding Standards for Documenting Hook Implementations, its considered a good practice to quickly chuck a comment before any function which implements a Drupal hook (eg, hook_menu). This helps someone reading your code quickly see that the function is actually linked with a hook in Drupal and isn't just a function in your module to be called directly.

But… Well… The thing is… Does anyone else get bored of writing the following over and over again? I know do…


/**
 * Implements hook_menu().
 */

Wouldn't it be nice if you could just type in "menu" and Vim could just fill it our for you? Here follows a little Vim script for inserting a "hook implements" comment at the current cursor position.


function! DrupalImplementsComment(hook)
  set paste

  exe "normal! i/**\"
  \          . " * Implements hook_" . a:hook . "()\"
  \          . " */\"

  set nopaste
endfunction

map  :call DrupalImplementsComment(input("Enter Hook name:"))

Wherever your cursor is, press Ctrl+C 3 times, you then get prompted to enter the hook name. When you press enter, a comment gets inserted. Hopefully this will save someone some time - its already saving me time!

To install the script, I just have it in a file called DrupalCommenting.vim inside my ~/.vim/ folder. Then, inside my ~/.vimrc file, I have a line which imports the source file, eg: so ~/.vim/DrupalCommenting.vim.

Any improvements very welcome!

Sep 14 2011
Sep 14

Ever needed to build a list which "sub-selects", say, 5 items from a given list of categories? This snippet should help.

Assume the following schema…


CREATE TABLE content (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL DEFAULT '',
  body LONGTEXT NOT NULL,
  status INT(11) NOT NULL DEFAULT '1',
  created INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id),
  KEY content_created (created)
);

CREATE TABLE tags(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
);

CREATE TABLE content_tags (
  tid INT(10) UNSIGNED NOT NULL,
  cid INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (tid, cid),
  KEY content_id (cid)
);

Now we can insert some dummy data…


TRUNCATE content;
INSERT INTO content (title, STATUS, created) VALUES
  ('Ut Secundum Modo',             1, UNIX_TIMESTAMP('2011-09-12 12:00:00')),
  ('Quidem Accumsan Facilisis',    1, UNIX_TIMESTAMP('2011-09-14 17:00:00')),
  ('Vel Ut Oppeto Interdico ',     1, UNIX_TIMESTAMP('2011-09-10 09:00:00')),
  ('Iustum Nimis Venio',           1, UNIX_TIMESTAMP('2011-09-11 12:30:00')),
  ('Consequat Defui Verto Macto',  1, UNIX_TIMESTAMP('2011-09-13 19:00:00')),
  ('Quae Natu Facilisis Ille Jus', 1, UNIX_TIMESTAMP('2011-09-09 21:15:00')),
  ('Abico Meus Ullamcorper',       0, UNIX_TIMESTAMP('2011-09-01 00:00:00')),
  ('Ulciscor Antehabeo Gravis',    1, UNIX_TIMESTAMP('2011-09-05 11:00:00'));

TRUNCATE tags;  
INSERT INTO tags (title) VALUES ('alpha'), ('beta'), ('gamma'), ('delta');

TRUNCATE content_tags;
INSERT INTO  content_tags(tid, cid) VALUES
  (1,1), (4,1),
  (2,2), (3,2), (4,2),
  (1,3), (3,3), (4,3),
  (3,4), (4,4),
  (2,5),
  (3,6), (4,6),
  (1,7), (4,7),
  (1,8);

The TRUNCATE's are only there to ensure these test tables are empty and that the auto incrementing ID's starts from 1.

Now if you run the following query, you will get a list of up to 3 of the most recent posts from each category.


SELECT tag_id, tag_name, content_id, content_title FROM (
  SELECT
    CASE
      WHEN @id != t.id THEN @row_num := 1
      ELSE @row_num := @row_num + 1
    END AS rownum,
    t.id tag_id,
    t.title tag_name,
    c.id content_id,
    c.title content_title,
    @id := t.id
  FROM tags t
  INNER JOIN content_tags ct ON ct.tid = t.id
  INNER JOIN content c ON c.id = ct.cid
  JOIN (SELECT @id := NULL, @row_num := 0) a
  WHERE c.status = 1
  ORDER BY t.id ASC, c.created DESC
) r
WHERE rownum < 4

This produces…

Note how the beta tag only has 2 items; this is due to the INSERT's above. tag_id tag_name content_id content_title 1 alpha 1 Ut Secundum Modo 1 alpha 3 Vel Ut Oppeto Interdico 1 alpha 8 Ulciscor Antehabeo Gravis 2 beta 2 Quidem Accumsan Facilisis 2 beta 5 Consequat Defui Verto Macto 3 gamma 2 Quidem Accumsan Facilisis 3 gamma 4 Iustum Nimis Venio 3 gamma 3 Vel Ut Oppeto Interdico 4 delta 2 Quidem Accumsan Facilisis 4 delta 1 Ut Secundum Modo 4 delta 3 Vel Ut Oppeto Interdico

How to use in Drupal

The above example could easily be adapted for a Drupal site; to list the 4 most recent items in all terms in a given vocabulary…


SELECT term_id, term_name, node_id, node_title FROM (
  SELECT
    CASE
      WHEN @id != td.tid THEN @row_num := 1
      ELSE @row_num := @row_num + 1
    END AS rownum,
    td.tid term_id,
    td.name term_name,
    n.nid node_id,
    n.title node_title,
    @id := td.tid
  FROM term_data td
  INNER JOIN term_node tn ON tn.tid = td.tid
  INNER JOIN node n ON n.vid = tn.vid
  JOIN (SELECT @id := NULL, @row_num := 0) a
  WHERE n.status = 1 AND td.vid = 1
  ORDER BY td.tid ASC, n.created DESC
) r
WHERE rownum < 5
May 10 2011
May 10

I've had several people ask me recently how I managed to get the nice date effect on my blog headers. It's quite simple really. All you need is:

  • An Image - A background image (a CSS Sprite) which contains the days, months and years.
  • Some HTML - A VERY basic HTML template.
  • Some CSS - To align the image sections.
  • A PHP Snippet - A Drupal preprocess function.

How to make a Nice Date block

The Image

The image I use here was knocked up in Photoshop. It's just a grid of "output". Everything must be lined up pixel perfect to make the CSS easier to generate.

Dates grid

As you can see, on the left we have the Months (Jan -> Dec), then the Days (01 -> 31) and finally the years. You can also make out the grid layout; months are "2 rows per day" and day is "2 rows per year".

Using CSS, we can specify which "section" of the image appears in the HTML template. This is known as "spriting". It's a technique for clipping bits of a single image. Without the spriting technique, we'd need 12 (months) + 31 (days) + 7 (years) = 50 images!

The HTML

Using the following simple HTML, we can apply CSS to style appropriately.



  {MONTH_LONG}
  {DAY_LONG}
  

{YEAR_LONG}

In the above, the values in curly braces are variable placeholders which should be replaced by appropriate data. SHORT implies a shortened date (eg, the year 2011 shortens to 11, the month January shortens to 01). LONG is the opposite of SHORT and is only there so that Search Engines and screen readers have some content to use (ie accessibility).

For example, 1st January 2011 would result in:


January

1st

2011

With CSS disabled (or to a Search Engine), this still reads January 1st 2011.

The CSS

The following CSS is used to align the sprite images into the HTML above.


.nice_date {
  float:right;
  position:relative;
  width:41px;
  height:40px;
}

.nice_date .month,
.nice_date .day,
.nice_date .year {
  position:absolute;
  text-indent:-1000em;
  background:transparent url(i/dates.png) no-repeat;
}
.nice_date .month { top:5px;  left:0;  width:25px; height:10px; }
.nice_date .day   { top:20px; left:0;  width:25px; height:20px; }
.nice_date .year  { bottom:0; right:0; width:15px; height:40px; }

.nice_date .m-01 { background-position:0     0; }
.nice_date .m-02 { background-position:0  -10px; }
.nice_date .m-03 { background-position:0  -20px; }
.nice_date .m-04 { background-position:0  -30px; }
.nice_date .m-05 { background-position:0  -40px; }
.nice_date .m-06 { background-position:0  -50px; }
.nice_date .m-07 { background-position:0  -60px; }
.nice_date .m-08 { background-position:0  -70px; }
.nice_date .m-09 { background-position:0  -80px; }
.nice_date .m-10 { background-position:0  -90px; }
.nice_date .m-11 { background-position:0 -100px; }
.nice_date .m-12 { background-position:0 -110px; }

.nice_date .d-01 { background-position:-25px      0; }
.nice_date .d-02 { background-position:-25px  -20px; }
.nice_date .d-03 { background-position:-25px  -40px; }
.nice_date .d-04 { background-position:-25px  -60px; }
.nice_date .d-05 { background-position:-25px  -80px; }
.nice_date .d-06 { background-position:-25px -100px; }
.nice_date .d-07 { background-position:-25px -120px; }
.nice_date .d-08 { background-position:-25px -140px; }
.nice_date .d-09 { background-position:-25px -160px; }
.nice_date .d-10 { background-position:-25px -180px; }
.nice_date .d-11 { background-position:-25px -200px; }
.nice_date .d-12 { background-position:-25px -220px; }
.nice_date .d-13 { background-position:-25px -240px; }
.nice_date .d-14 { background-position:-25px -260px; }
.nice_date .d-15 { background-position:-25px -280px; }
.nice_date .d-16 { background-position:-50px      0; }
.nice_date .d-17 { background-position:-50px  -20px; }
.nice_date .d-18 { background-position:-50px  -40px; }
.nice_date .d-19 { background-position:-50px  -60px; }
.nice_date .d-20 { background-position:-50px  -80px; }
.nice_date .d-21 { background-position:-50px -100px; }
.nice_date .d-22 { background-position:-50px -120px; }
.nice_date .d-23 { background-position:-50px -140px; }
.nice_date .d-24 { background-position:-50px -160px; }
.nice_date .d-25 { background-position:-50px -180px; }
.nice_date .d-26 { background-position:-50px -200px; }
.nice_date .d-27 { background-position:-50px -220px; }
.nice_date .d-28 { background-position:-50px -240px; }
.nice_date .d-29 { background-position:-50px -260px; }
.nice_date .d-30 { background-position:-50px -280px; }
.nice_date .d-31 { background-position:-50px -300px; }

.nice_date .y-06 { background-position:-75px      0; }
.nice_date .y-07 { background-position:-75px -040px; }
.nice_date .y-08 { background-position:-75px -080px; }
.nice_date .y-09 { background-position:-75px -120px; }
.nice_date .y-10 { background-position:-75px -160px; }
.nice_date .y-11 { background-position:-75px -200px; }
.nice_date .y-12 { background-position:-75px -240px; }
.nice_date .y-13 { background-position:-75px -280px; }

As you can see, the first part just sets up the element sizes and positions. The last chuck of code defines the background offsets for the sprite.

Note: You may need to adjust bits of this based on your own settings. For example, if you remake the Dates PNG Sprite, you will need to adjust ALL the background positions (unless you keep to the same grid).

The PHP

The following PHP is used to embed the HTML Template into a Node.


function THEMENAME_preprocess_node(&$vars) {
  $vars['nide_date'] = _THEMENAME_nice_date($vars['created']);
}
function _THEMENAME_nice_date($timestamp) {
  // Nice Date
  $ys = date('y', $timestamp);
  $yl = date('Y', $timestamp);
  $ms = date('m', $timestamp);
  $ml = date('M', $timestamp);
  $d  = date('d', $timestamp);

  return "

{$ml}

{$d}

{$yl}

"; }

You've probably guessed, but you should replace THEMENAME with the name of the theme (eg, this theme is currently called "tmj2"). You now have a variable, $nide_date to print into your node.tpl.php.

You can also use the same function in hook_preprocess_comment too, if your site has comments enabled.

Other Tips

  • You could bundle this up into a module to re-use across several sites. The preprocess hooks in Drupal 6+ are accessible from Modules.
  • You could alter the Sprite PNG to use a different font; I used Helvetica to fit with the site's clean/simple font design. Someone like Morten (the King of Denmark) might prefer to remake it using Bello to match his blogs header title.
  • Altering the layout is possible too; maybe you're prefer to the date along the top and the month on the side?

If you implement this on your site, please share your link below! (Note to spammers, my site uses No Follow and I check for link spam, so save us both some time ;-) hehe).

Feb 16 2011
Feb 16

Isn't it a pain when you have dozens of Views setup and they are all marked as "overridden" because you just pulled in an updated feature file from somewhere. Features doesn't always notice when the Views on your site aren't up to date.

The following snippet (which you should use with caution) will batch "delete" (or Revert, once the view is in code) all Views which are marked as Overridden. This took a few seconds to run on our development machine.


$views = views_get_all_views();

foreach ($views as $view) {
  if ($view->disabled) continue;

  if ($view->type == t('Overridden')) {
    $view->delete();
    views_object_cache_clear('view', $view->name);
  }
}

I ran this from the Devel PHP page (http://www.example.com/devel/php). It essentially does the same as the view module does when you individually revert views, but this does it without confirming on ALL VIEWS MARKED AS Overridden. I cannot stress enough - use at your own risk, and backup your database first!

Jan 16 2011
Jan 16

So, Drupal 7 is out. Have you heard? It was a bit of a quiet launch really. ;-)

When it came to upgrading by blog, I decided (eventually) that I needed to do a cleanup as the Database had been upgraded from 4.7, to 5 and then to 6 - with many modules added and removed in between. New year, new drupal, new blog (kinda)!

It's also a good excuse to test out that some of the modules I maintain actually work in Drupal 7 (such as Page Title and GlobalRedirect). It's also a kick up the arse to get Relevant Content ported to Drupal 7!

Theme:

The theme was ported over relatively easily - although it's now using the Boron base theme to make it HTML 5 (another new whizz-bang thing). Mostly it was a matter of remembering to change a lot of instances where a variable got printed out to use the new render() function instead.

Content:

The content was easily ported over using the following MySQL:

Nodes

INSERT INTO [NEW_DB].node
SELECT n.nid, n.vid, n.type, 'und', n.title, n.uid, n.status, n.created, n.changed, n.comment, n.promote, n.sticky, n.tnid, n.translate
FROM [OLD_DB].node n WHERE n.type IN ('blog', 'page');
INSERT INTO [NEW_DB].node_revision
SELECT nr.nid, nr.vid, nr.uid, nr.title, nr.log, nr.timestamp, n.status, n.comment, n.promote, n.sticky
FROM [OLD_DB].node_revisions nr
INNER JOIN [OLD_DB].node n ON n.vid = nr.vid
WHERE n.type IN ('page', 'blog')

Node body

INSERT INTO [NEW_DB].field_data_body
SELECT
  'node', n.type, 0, nr.nid, nr.vid, 'und', 0, nr.body, nr.teaser,
  CASE nr.format
    WHEN 3 THEN 'full_html'
    ELSE 'filtered_html'
  END AS format
FROM [OLD_DB].node_revisions nr
INNER JOIN [OLD_DB].node n ON n.vid = nr.vid
WHERE n.type IN ('page', 'blog')
INSERT INTO [NEW_DB].field_revision_body
SELECT
  'node', n.type, 0, nr.nid, nr.vid, 'und', 0, nr.body, nr.teaser,
  CASE nr.format
    WHEN 3 THEN 'full_html'
    ELSE 'filtered_html'
  END AS format
FROM [OLD_DB].node_revisions nr
INNER JOIN [OLD_DB].node n ON n.vid = nr.vid
WHERE n.type IN ('page', 'blog')

Comments

INSERT INTO [NEW_DB].comment
SELECT
  c.cid, c.pid, c.nid, c.uid, c.subject, c.hostname, c.timestamp, c.timestamp,
  CASE c.status WHEN 0 THEN 1 ELSE 0 END,
  c.thread, c.name, c.mail, c.homepage, 'und'
FROM [OLD_DB].comments c
LEFT JOIN [NEW_DB].comment c2 ON c2.cid = c.cid
INNER JOIN [OLD_DB].node n ON n.nid = c.nid
WHERE n.type IN ('page', 'blog') AND c2.cid IS NULL
INSERT INTO [NEW_DB].field_data_comment_body
SELECT
  'comment', CONCAT('comment_node_', n.type), 0, c.cid, c.cid, 'und', 0, c.comment,
  CASE c.format
    WHEN 3 THEN 'full_html'
    ELSE 'filtered_html'
  END
FROM [OLD_DB].comments c
LEFT JOIN [NEW_DB].field_data_comment_body c2 ON c2.entity_type = 'comment' AND c2.entity_id = c.cid
INNER JOIN [OLD_DB].node n ON n.nid = c.nid
WHERE n.type IN ('page', 'blog') AND c2.entity_type IS NULL
INSERT INTO [NEW_DB].field_revision_comment_body
SELECT
  'comment', CONCAT('comment_node_', n.type), 0, c.cid, c.cid, 'und', 0, c.comment,
  CASE c.format
    WHEN 3 THEN 'full_html'
    ELSE 'filtered_html'
  END
FROM [OLD_DB].comments c
LEFT JOIN [NEW_DB].field_revision_comment_body c2 ON c2.entity_type = 'comment' AND c2.entity_id = c.cid
INNER JOIN [OLD_DB].node n ON n.nid = c.nid
WHERE n.type IN ('page', 'blog') AND c2.entity_type IS NULL

URL Aliases

INSERT INTO [NEW_DB].url_alias
SELECT u.pid, u.src, u.dst, 'und'
FROM [OLD_DB].url_alias u
LEFT JOIN [OLD_DB].node n ON u.src = CONCAT('node/', CAST(n.nid AS CHAR))
WHERE (u.src LIKE 'node/%' OR u.src LIKE 'taxonomy/%') AND (n.type IS NULL OR n.type IN ('page', 'blog'))

Taxonomy

INSERT INTO [NEW_DB].taxonomy_term_data
SELECT t.tid, t.vid, t.name, t.description, 'full_html', 0
FROM [OLD_DB].term_data t
INSERT INTO [NEW_DB].taxonomy_term_hierarchy
SELECT t.tid, 0
FROM [OLD_DB].term_hierarchy t
INSERT INTO [NEW_DB].taxonomy_index
SELECT n.nid, t.tid, n.sticky, n.created
FROM [OLD_DB].node n
INNER JOIN [OLD_DB].term_node t ON t.nid = n.nid
WHERE n.type IN ('blog', 'page')

Node taxonomy

INSERT INTO [NEW_DB].field_data_field_tags
SELECT
  'node', i.type, 0 AS deleted, i.nid, i.vid, 'und' AS LANGUAGE,
  @delta := CASE WHEN @prevnid = i.nid THEN @delta:[email protected]+1 ELSE CASE WHEN @prevnid := i.nid THEN @delta := 0 ELSE @delta := 0 END END AS delta,
  i.tid
FROM (
  SELECT n.nid, n.vid, n.type, t.tid
  FROM [OLD_DB].node n
  INNER JOIN [OLD_DB].term_node t ON t.nid = n.nid
  WHERE n.type IN ('blog', 'page')
  ORDER BY n.nid ASC
) AS i
INSERT INTO [NEW_DB].field_revision_field_tags
SELECT
  'node', i.type, 0 AS deleted, i.nid, i.vid, 'und' AS LANGUAGE,
  @delta := CASE WHEN @prevnid = i.nid THEN @delta:[email protected]+1 ELSE CASE WHEN @prevnid := i.nid THEN @delta := 0 ELSE @delta := 0 END END AS delta,
  i.tid
FROM (
  SELECT n.nid, n.vid, n.type, t.tid
  FROM [OLD_DB].node n
  INNER JOIN [OLD_DB].term_node t ON t.nid = n.nid
  WHERE n.type IN ('blog', 'page')
  ORDER BY n.nid ASC
) AS i

Files

INSERT INTO [NEW_DB].file_managed
SELECT
  f.fid, f.uid,
  SUBSTRING_INDEX(f.filepath, '/', -1) AS `filename`,
  REPLACE(f.filepath, 'sites/thingy-ma-jig.co.uk/files/', 'public://') AS `uri`,
  f.filemime, f.filesize, f.status, f.timestamp
FROM [OLD_DB].files f
INSERT INTO [NEW_DB].field_revision_field_image
SELECT
  'node', n.type, 0 AS `deleted`, n.nid, n.vid, 'und' AS `language`, 0 AS `delta`, ctb.field_image_fid AS `fid`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(SUBSTR(ctb.field_image_data, LOCATE('alt', ctb.field_image_data)), 8), '"', 2), '"', -1) AS `alt`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(SUBSTR(ctb.field_image_data, LOCATE('title', ctb.field_image_data)), 8), '"', 2), '"', -1) AS `title`
FROM [OLD_DB].node n
INNER JOIN [OLD_DB].content_type_blog ctb ON ctb.vid = n.vid
WHERE n.type IN ('blog') AND ctb.field_image_fid IS NOT NULL

Page Title

INSERT INTO [NEW_DB].page_title
SELECT p.*
FROM [OLD_DB].page_title p
LEFT JOIN [NEW_DB].page_title p2 ON p.type = p2.type AND p.id = p2.id
WHERE p2.id IS NULL;

Meta Tags

Unfortunately, at the time of writing, the Metatags module is not available and Nodewords has not been updated. The current "hack" solution is to have two fields (field_meta_description and field_meta_keywords), add then to the node and term 'bundles' and just use SQL to get the content into them. Then, using a custom module, embed them into the header manually (using hook_html_head_alter).

Nodes

INSERT INTO [NEW_DB].field_data_field_meta_description
SELECT
  'node', n.type, 0, n.nid, n.vid, 'und', 0,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(SUBSTR(nw.content, LOCATE('value', nw.content)), 8), '"', 2), '"', -1),
  'plain_text'
FROM [OLD_DB].node n
INNER JOIN [OLD_DB].nodewords nw ON nw.type = 5 AND nw.id = n.nid AND nw.name = 'description'
WHERE n.type IN ('blog', 'page');
INSERT INTO [NEW_DB].field_revision_field_meta_description
SELECT
  'node', n.type, 0, n.nid, n.vid, 'und', 0,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(SUBSTR(nw.content, LOCATE('value', nw.content)), 8), '"', 2), '"', -1),
  'plain_text'
FROM [OLD_DB].node n
INNER JOIN [OLD_DB].nodewords nw ON nw.type = 5 AND nw.id = n.nid AND nw.name = 'description'
WHERE n.type IN ('blog', 'page');
INSERT INTO [NEW_DB].field_data_field_meta_keywords
SELECT
  'node', n.TYPE, 0, n.nid, n.vid, 'und', 0,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(SUBSTR(nw.content, LOCATE('value', nw.content)), 8), '"', 2), '"', -1),
  'plain_text'
FROM [OLD_DB].node n
INNER JOIN [OLD_DB].nodewords nw ON nw.type = 5 AND nw.id = n.nid AND nw.name = 'keywords'
WHERE n.type IN ('blog', 'page');
INSERT INTO [NEW_DB].field_revision_field_meta_keywords
SELECT
  'node', n.type, 0, n.nid, n.vid, 'und', 0,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(SUBSTR(nw.content, LOCATE('value', nw.content)), 8), '"', 2), '"', -1),
  'plain_text'
FROM [OLD_DB].node n
INNER JOIN [OLD_DB].nodewords nw ON nw.type = 5 AND nw.id = n.nid AND nw.name = 'keywords'
WHERE n.type IN ('blog', 'page');

Terms

INSERT INTO [NEW_DB].field_data_field_meta_description
SELECT
  'taxonomy_term', 'tags', 0, t.tid, t.tid, 'und', 0,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(SUBSTR(nw.content, LOCATE('value', nw.content)), 8), '"', 2), '"', -1),
  'plain_text'
FROM [OLD_DB].term_data t
INNER JOIN [OLD_DB].nodewords nw ON nw.type = 6 AND nw.id = t.tid AND nw.NAME = 'description';
INSERT INTO [NEW_DB].field_revision_field_meta_description
SELECT
  'taxonomy_term', 'tags', 0, t.tid, t.tid, 'und', 0,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(SUBSTR(nw.content, LOCATE('value', nw.content)), 8), '"', 2), '"', -1),
  'plain_text'
FROM [OLD_DB].term_data t
INNER JOIN [OLD_DB].nodewords nw ON nw.type = 6 AND nw.id = t.tid AND nw.NAME = 'description';
INSERT INTO [NEW_DB].field_data_field_meta_keywords
SELECT
  'taxonomy_term', 'tags', 0, t.tid, t.tid, 'und', 0,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(SUBSTR(nw.content, LOCATE('value', nw.content)), 8), '"', 2), '"', -1),
  'plain_text'
FROM [OLD_DB].term_data t
INNER JOIN [OLD_DB].nodewords nw ON nw.type = 6 AND nw.id = t.tid AND nw.NAME = 'keywords';
INSERT INTO [NEW_DB].field_revision_field_meta_keywords
SELECT
  'taxonomy_term', 'tags', 0, t.tid, t.tid, 'und', 0,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(SUBSTR(nw.content, LOCATE('value', nw.content)), 8), '"', 2), '"', -1),
  'plain_text'
FROM [OLD_DB].term_data t
INNER JOIN [OLD_DB].nodewords nw ON nw.type = 6 AND nw.id = t.tid AND nw.NAME = 'keywords';

Modules:

I also stripped back some functionality to make the site easier to maintain, but pretty much all of what I needed worked directly from checkout. I'm still using Gravatar, Flickr, Views and Panels (I still need to configure the panels).

Issues?

I found an odd issue with Drupal core. I didn't want my comments to "permalink" to comment URL's - I wanted them to anchor to their point on the page. So I just thought I'd alter the entity info and switch the callback used for the URI. This caused an error where the Comment module had not completely been updated to the new Drupal 7 API. See Issue 1027936.

Also, as mentioned above, Nodewords/Metatags are currently unavailable so I had to "hack" together my own module using hidden fields. Here is the code that enables me to get Meta description and keywords on nodes and terms + frontpage:

function MODULE_html_head_alter(&$head) {
  if (($obj = menu_get_object('node', 1)) || ($obj = menu_get_object('taxonomy_term', 2))) {
    $description = isset($obj->field_meta_description['und'][0]['safe_value']) ? $obj->field_meta_description['und'][0]['safe_value'] : '';
    $keywords    = isset($obj->field_meta_keywords['und'][0]['safe_value']) ? $obj->field_meta_keywords['und'][0]['safe_value'] : '';
  }
  elseif (drupal_is_front_page()) {
    $description = 'FRONTPAGE DESCRIPTION';
    $keywords = 'FRONTPAGE KEYWORDS';
  }

  if (!empty($description)) {
    $head['tmj_tweaks_description'] = array(
      '#type' => 'html_tag',
      '#tag' => 'meta',
      '#attributes' => array('name' => 'description', 'content' => $description),
    );
  }
  if (!empty($keywords)) {
    $head['tmj_tweaks_keywords'] = array(
      '#type' => 'html_tag',
      '#tag' => 'meta',
      '#attributes' => array('name' => 'keywords', 'content' => $keywords),
    );
  }
}

This just pulls the node or term object from the Menu API, grabs the values from the field (or the hardcoded frontpage values) and pushes them into the head as html tag elements.

During the upgrade I also submitted some patches to the GeSHi module (which powers the code highlighting on this site) and the Gravatar module (which powers the user profile icons on comments).

So far, I'm really liking Drupal 7 - it's shaping up to be a very nice release!

Dec 09 2010
Dec 09

Ever wondered if your Christmas Tree is really any good? Well last weekend I decided to make a website for this purpose. It's just a bit of fun really! You login using your Facebook credentials (using Facebook Connect) and can create Chrismas Tree posts by linking to Flickr or Facebook photo's. You can then vote on your favourite tree's in an "A vs B" type match. If you like the sound of this and fancy something a bit festive, please give it a go and let me know if you have any thoughts! Head over to Rate My Christmas Tree :-)

I've published one of the modules this site produced: Media: Facebook. This module allows you to embed a Facebook photo into an EmField by simply copying and pasting the page URL from Facebook. The module then usesthe Facebook API to lookup all available sizes. It is a beta and still needs work, but certainly covers the basics.

I've also written a custom module for comparing and voting between two nodes. I looked into the Voting API (which is AWESOME), btu it only allows you to store a score aginst a single node. I need a system which defined a "left" and "right" node and a "winner". I also looked into the Smackdown module, which looks great too - however that seems to force you to create a "competition" node for every competition. That could get out of handy very quickly (assuming the site gains any kind of traction). So I wrote a module called Compare (which I've not released yet). It will allow you to create "profiles" of votes. Each profile has it's own role-level access control and stores the User ID, Left, Right, Winner and Timestamp of each vote. The vote URL also uses Drupal's private key system to generate secure URL which is limited to only work for a certain period of time. The hash used to secure the URL also stops people changing the left, right or winning Node ID to anything other than that intended. I want to do more work on it before I release it though.

About Drupal Sun

Drupal Sun is an Evolving Web project. It allows you to:

  • Do full-text search on all the articles in Drupal Planet (thanks to Apache Solr)
  • Facet based on tags, author, or feed
  • Flip through articles quickly (with j/k or arrow keys) to find what you're interested in
  • View the entire article text inline, or in the context of the site where it was created

See the blog post at Evolving Web

Evolving Web