<commit_before><?xml version="1.0" encoding="UTF-8"?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Exporting a Drupal Database to an Atom/XHTML Bundle</title>
<meta name="author" content="<NAME>" />
<meta name="description" content="The Worst bash/sed/SQL/XSLT Combobulation You'll Ever See(tm): this article presents a nifty script to export a Drupal 5/6 MySQL database to a static .atom bundle containing XHTML atom:content nodes." />
<meta name="date" content="2012-11-18T11:49:00Z" />
<meta name="mtime" content="2012-11-18T11:49:00Z" />
<meta name="category" content="Articles" />
<meta name="unix:name" content="exporting-a-drupal-database-to-atom" />
</head>
<body>
<h1>Not Quite a One-Liner</h1>
<p>The other day I stumbled upon an old Drupal database that, miraculously, was still running. Since I've since lost all motivation to set up <del>'real'</del> <ins>'open-source'</ins> CMSs, especially on nginx without MySQL, I thought it'd be a lot nicer if I could just export the whole thing as an .atom file with basic XHTML files in the atom:content nodes. (I'll admit that this output format may seem a wee bit strange. Atom turned out to be a really nice container format for XML files, I'll be writing about serving those files soon.)</p>
<p>Now I thought someone else must've been crazy enough to do this before me, but I was wrong. Guess I just had to do it myself then. So I dug out an inglorious number of scripting languages, cobbled them all together and ended up producing something that might even be useful to others migrating from Drupal.</p>
<p>The result of this script is available on this website, as <a href="archives">the einit.org and kyuba.org archives</a>.</p>
<h1>The Script</h1>
<p>This is the script I came up with. All you need to do is adjust the variables in the header and then it should be good to run. It'll create a file called '(database).atom' with all the major content in the drupal database (excluding comments). You might also want to <a href="download/drupal-to-atom.sh">download this script instead of copying and pasting it into an editor</a>.</p>
<p>The script assumes that you're running it on the same host as the database and that the database is a MySQL database (not sure if Drupal would let you use anything else). It also assumes you have xsltproc (from libxslt) installed on that machine, that you have bash installed and that your sed accepts GNU extensions. So, nothing out of the ordinary on a typical LAMP machine. The database I wrote this with was for a Drupal 5 or Drupal 6 installation. Not sure if it's going to work with other versions, you might have to adjust it a bit.</p>
<p>The variables in the script are as follows:</p>
<ul>
<li>database: the name of the MySQL database</li>
<li>user: the name of the MySQL database user</li>
<li>prefix: the table prefix in the database. Not sure if Drupal supported this, but most LAMP-y designes do. Leave blank if you had a dedicated database for the drupal installation.</li>
<li>timezone: the timezone suffix for any dates taken from the database. Either use 'Z' or '+....' for whatever offset should be applied. There's no decent way to query this from a MySQL database and it sneakily applies timezone adjustments to Unix dates without telling you at times, so you'll have to provide this manually if you care enough. Otherwise just use 'Z'.</li>
<li>categoryprefix: this specifies a secondary category to put each of the Drupal nodes into. I'm using this to differentiate between different sources of content in my combined .atoms. You could just use the domain name here.</li>
<li>fileprefix: I'm adding 'unix:name' meta attributes to the XHTML portions, so that it should be easier to split up when post-processing. This is a prefix for these meta attributes.</li>
<li>tbase: this is the prefix for any temporary files the script generates. Sadly, xsltproc doesn't seem to be able to get a stylesheet piped into it, so I have to write those XSLT scripts to real files.</li>
<li>name: you don't really need this one, I'm just using it to make the script slightly easier to read.</li>
</ul>
<h2>The Worst bash/sed/SQL/XSLT Combobulation You'll Ever See</h2>
<pre><code><![CDATA[#!/bin/bash<commit_msg>minor adjustment to the description text<commit_after><?xml version="1.0" encoding="UTF-8"?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Exporting a Drupal Database to an Atom/XHTML Bundle</title>
<meta name="author" content="<NAME>" />
<meta name="description" content="The Worst bash/sed/SQL/XSLT Combobulation You'll Ever See(tm): this article presents a nifty script to export a Drupal 5/6 MySQL database to a static Atom bundle." />
<meta name="date" content="2012-11-18T11:49:00Z" />
<meta name="mtime" content="2012-11-18T11:49:00Z" />
<meta name="category" content="Articles" />
<meta name="unix:name" content="exporting-a-drupal-database-to-atom" />
</head>
<body>
<h1>Not Quite a One-Liner</h1>
<p>The other day I stumbled upon an old Drupal database that, miraculously, was still running. Since I've since lost all motivation to set up <del>'real'</del> <ins>'open-source'</ins> CMSs, especially on nginx without MySQL, I thought it'd be a lot nicer if I could just export the whole thing as an .atom file with basic XHTML files in the atom:content nodes. (I'll admit that this output format may seem a wee bit strange. Atom turned out to be a really nice container format for XML files, I'll be writing about serving those files soon.)</p>
<p>Now I thought someone else must've been crazy enough to do this before me, but I was wrong. Guess I just had to do it myself then. So I dug out an inglorious number of scripting languages, cobbled them all together and ended up producing something that might even be useful to others migrating from Drupal.</p>
<p>The result of this script is available on this website, as <a href="archives">the einit.org and kyuba.org archives</a>.</p>
<h1>The Script</h1>
<p>This is the script I came up with. All you need to do is adjust the variables in the header and then it should be good to run. It'll create a file called '(database).atom' with all the major content in the drupal database (excluding comments). You might also want to <a href="download/drupal-to-atom.sh">download this script instead of copying and pasting it into an editor</a>.</p>
<p>The script assumes that you're running it on the same host as the database and that the database is a MySQL database (not sure if Drupal would let you use anything else). It also assumes you have xsltproc (from libxslt) installed on that machine, that you have bash installed and that your sed accepts GNU extensions. So, nothing out of the ordinary on a typical LAMP machine. The database I wrote this with was for a Drupal 5 or Drupal 6 installation. Not sure if it's going to work with other versions, you might have to adjust it a bit.</p>
<p>The variables in the script are as follows:</p>
<ul>
<li>database: the name of the MySQL database</li>
<li>user: the name of the MySQL database user</li>
<li>prefix: the table prefix in the database. Not sure if Drupal supported this, but most LAMP-y designes do. Leave blank if you had a dedicated database for the drupal installation.</li>
<li>timezone: the timezone suffix for any dates taken from the database. Either use 'Z' or '+....' for whatever offset should be applied. There's no decent way to query this from a MySQL database and it sneakily applies timezone adjustments to Unix dates without telling you at times, so you'll have to provide this manually if you care enough. Otherwise just use 'Z'.</li>
<li>categoryprefix: this specifies a secondary category to put each of the Drupal nodes into. I'm using this to differentiate between different sources of content in my combined .atoms. You could just use the domain name here.</li>
<li>fileprefix: I'm adding 'unix:name' meta attributes to the XHTML portions, so that it should be easier to split up when post-processing. This is a prefix for these meta attributes.</li>
<li>tbase: this is the prefix for any temporary files the script generates. Sadly, xsltproc doesn't seem to be able to get a stylesheet piped into it, so I have to write those XSLT scripts to real files.</li>
<li>name: you don't really need this one, I'm just using it to make the script slightly easier to read.</li>
</ul>
<h2>The Worst bash/sed/SQL/XSLT Combobulation You'll Ever See</h2>
<pre><code><![CDATA[#!/bin/bash
{'fraction_non_alphanumeric': 0.08729595457771469, 'fraction_numerical': 0.010882422521883131, 'mean_word_length': 4.595632031767042, 'pattern_counts': {'":': 0, '<': 125, '<?xml version=': 2, '>': 123, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 4}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1202046', 'n_tokens_mistral': 2449, 'n_tokens_neox': 2320, 'n_words': 1282}