From the Orient

Recent Trackback template using just MovableType and the MTSQL plugin. (Did I tell you how much I liked the MTSQL plugin? Oh, I did, didn’t I.)

<ul>

<MTSQLPings query="select p.tbping_id
from mt_entry e, mt_trackback t, mt_tbping p
where e.entry_id = t.trackback_entry_id
and t.trackback_id = p.tbping_tb_id order by p.tbping_created_on limit 5">

<MTSQLEntries query="select DISTINCT e.entry_id
from mt_entry e, mt_trackback t, mt_tbping p
where e.entry_id = t.trackback_entry_id
and t.trackback_id = p.tbping_tb_id
and p.tbping_id = [MTPingID]">

<li><a href="<MTPingURL>"><MTPingTitle></a>
 on <a href="<MTEntryPermalink>"><MTEntryTitle></a>
</li>

</MTSQLEntries>
</MTSQLPings>
</ul>

Change the value after limit to however many trackbacks you want. In theory, you can use any MTEntries or MTPings tags you want in there

So, for inclusion with SimpleComments, this is my template.

<h4>Recent Comments</h4>
<ul class="recentcomments">
<MTSimpleComments lastn="15" sort_order="descend">
<MTSimpleCommentIfComment>
<li><$MTCommentAuthorLink show_email="0"$>
on <a href="<MTCommentEntry>
<$MTEntryPermalink$>
</MTCommentEntry>#comment<$MTCommentID$>"><MTCommentEntry><$MTEntryTitle$></MTCommentEntry>
</a></li>
</MTSimpleCommentIfComment>
<MTSimpleCommentIfTrackback>
<MTSQLEntries query="select DISTINCT e.entry_id
from mt_entry e, mt_trackback t, mt_tbping p
where e.entry_id = t.trackback_entry_id
and t.trackback_id = p.tbping_tb_id
and p.tbping_id = [MTPingID]">

<li>[Trackback] <a href="<MTPingURL>"><MTPingTitle></a>
 on <a href="<MTEntryPermalink>"><MTEntryTitle></a>
</li>

</MTSQLEntries>
</MTSimpleCommentIfTrackback>
</MTSimpleComments>
</ul>

I’ve switched the backend database from the built in Berkeley DB to mySQL and the conversion process and subsequent MT upgrade problem has taught me a little bit about what is a new database for me.

I come from a background of using Oracle, which is a heavy-weight database for serious use. I doubt I’ll ever need the power of Oracle for any personal project I do, it I will be interested to see how mySQL copes under strain given its more light-weight standing in the database world.

First thing that threw me was the differences in naming conventions. An Oracle database is the binary files existing in a specific Oracle Home. You can have multiple instances running per database, they all run in the same Oracle Home (you can have different versions of Oracle running in different Oracle Homes). Inside each instance, you have a schema for each user, containing tables, views and other RDBMS stuff. So, a CREATE DATABASE command will create a new instance that runs completely separately and has its own set of users, i.e. there will be a SYS and SYSTEM in each instance.

mySQL’s CREATE DATABASE command really creates the equivalent of a new schema. There is now new ROOT user for this database, and the ROOT user does all the granting of permissions for the user of the database (you can have multiple users for this database still; I’m not at the stage yet to know whether you can have a user with the power of ROOT just for this database). This hit me when I had the permission problem above. I tried to grant myself the correct alter permission, but it seemed only ROOT could do this.

I like the way mysqldump works, a plain and simple way of doing a backup. It actually creates a series of SQL scripts much like the EXP binary in Oracle, but this script that is created is text rather than a binary dump.

I’m looking forward to further playing with the database and producing some interesting things with mySQL and Python. One notes that Shelley is able to do a lot of interesting custom stuff over the top of MT using PHP. This leads me down an interesting thought path. Is it always better to have a mysql like database (e.g. Postgres) as the backend? Or does the advantage of a built-in database like BerkeleyDB outweigh the features you can create? Non extra install required. This is something I discussed with Stuart whilst he was throwing all his Vellum ideas at me. He preferred the idea that you could just install Vellum and you were ready to go, whilst I (being a DBA) wanted the database.

I did it the hard, long way, but I’ve switched to mySQL now. Exported from the old system, re-ran mt-load.cgi with the new database settings in place, and imported. Already I felt the power; instead of having to go through many web pages telling MT which template file to match to which template, I did it in a few update statements in mySQL instead. I’m going to enjoy this.