A New Website, Part Nine - Mailing List Follies

One of the problems with starting a series like this is that it creates the expectation that it will be continued and even eventually finished. So when the task it describes becomes Byzantine, with numerous twists and turns, keeping up the documentation can become a chore in itself.

Views

Over the weekend I installed the Views module and experimented with it. Repeat after me: "A view is a list."

Happily this module does exactly what I want it to do: it allows me to describe a list of records I would like displayed on a page or a block, and it then displays them for me. In this it corresponds with my old 'keyword' command, that I would insert into a web page.

I didn't get a chance to do a lot of experimentation, but basically the idea is that you select what type of content you would like to display, what fields from that content you would like to display, what you want to filter on (eg., 'Content type = author') and how many records you want to show at a time.

In my system, I would feed each record into a template. This does not appear to be quite so flexible, and I haven't really found a way to style the output yet, but I'm sure there is one.

The Front Page

I decided I wanted to create a nice clean front page, displaying my records and news and stuff inside the website.

This was a disaster, and I ended up messing up the administration screens.

Without going into details (there's no point, and I can't remember it all), here are the lessons I learned:

- first, the administration screen needs the columns, so don't wipe them out in your template

- second, keep your content in blocks, not pages

Data, Revisited

This week I have been looking again at how to convert my old data to the new system, with more success.

Using the CCK module, I have determined that I can create new data types that emulate the tables in my old system, including even the links between records that I use.

I have experimented with the 'Authors' table first. In my system, when I describe a web resource, I enter the name of the author. So when I save a post, I also save a record for the author (if it's a new author) or find the name (if it's an existing author) and link it to the post record.

Now CCK isn't going to support anything so sophisticated (I will have to write some input script) but it will support the record types and linking.

In my system the author table has the following fields:

author_id
author_link
author_name
author_description
author_crdate

I would have added more but I never got around to finishing off this part of my system.

I defined a new content type, 'Author', in CCK, with the following fields: Link, ID, Description and Crdate. I let the Title of the record be Name. I then created a test record, just to see what CCK would do.

CCK creates a bunck of new tables and records to track the new content type. The actual data for a record is stored in three separate tables: node, node_content_author, and node_revisions. The first and third are standard Drupal tables, and the second was created when I created the content type.

My next step, therefore, is to look at each of these three tables and create a crosswalk -- that is, a mapping from my table to the Drupal table. Here's how I set it up (I have no idea why Blogger puts a big gap there, there's only one line and no markup in my text):

node

Drupal Field	My field or default value
nid	(Auto Increment)
vid	(from node_revision)
type	content_author
title	author_name
uid	1
status	1
created	author_crdate
changed	author_crdate
comment	2
promote	1
moderate	0
sticky	0

node_content_author

Drupal Field	My field or default value
uid	1
nid	(from Node)
field_link_value	author_link
field_id_value	author_id
field_description_value	author_description
field_description_format	1
field_crdate_value	author_crdate

node_revisions

Drupal Field	My field or default value
nid	(from Node)
vid	(Auto)
uid	1
title	author_name
body	(not used)
teaser	(not used)
log	(not used)
timestamp	author_crdate
format	0

So this was all pretty nice, it seemed clear I would be able to recreate my table just fine.

So I went into phpMyAdmin running on my current website and, navigating to the 'author' table, selected 'Export' and saved the data as an SQL file. I then copied the contents of this file (it was only a little more than 2000 lines) into the 'SQL' window in the phpMyAdmin running on the new site. Running the SQL created an 'author' table on the new site that was an exact duplicate of the one on my old site.

Then I created a script that would access this new table and copy it into the Drupal table. As I write I haven't finished this script, but here is what I have at the moment.

#!/usr/bin/perl
use DBI;


print "Content-type: text/html\n\n";
print "Test
";

my $dbh = &db_open("DBI:mysql:xxxxxx:localhost","xxxxxx","xxxxxx") or print "Database connect error: $!";


print "Database is open
";

my $sth = $dbh->prepare("SELECT * FROM author");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {
 #print ($ref->{author_name});
 #print " -
";
}

print "Testing insert
";

$vars->{author_crdate} = time;
$vars->{author_name} = "Test";
$vars->{idfield} = &db_insert($dbh,"author",$vars);
print "Inserted record into record number ",$vars->{idfield},"
";

if ($dbh) { $dbh->disconnect; }


exit;


sub db_open {

my ($dsn,$user,$password) = @_;
my $dbh = DBI->connect($dsn, $user, $password)
 or die "Database connect error: $! \n";
#  if ($dbh) { $dbh->trace(2,"dberror.txt"); }
return $dbh;
}

sub db_insert {  # Inserts record into table from hash

my $dbh = shift || die "Database handler not initiated";
my $table = shift || die "Table not specified on insert";
my $input = shift || die "No data provided on insert";
# die "Unsupported data type specified to insert" unless (ref $input eq 'HASH' || ref $input eq 'Link' || ref $input eq 'Feed');

my $data= &db_prepare_input($dbh,$table,$input);

my $sql   = "INSERT INTO $table ";
my(@sqlf, @sqlv, @sqlq) = ();

for my $k (sort keys %$data) {
 push @sqlf, $k;
 push @sqlq, '?';
 push @sqlv, $data->{$k};
}
$sql .= '(' . join(', ', @sqlf) .') VALUES ('. join(', ', @sqlq) .')';
#print "Content-type: text/html\n\n $sql -- ",@sqlv,"
";
my $sth = $dbh->prepare($sql);
$sth->execute(@sqlv);

return $dbh->{'mysql_insertid'};

# Adapted from SQL::Abstract by Nathan Wiger
}


sub db_prepare_input { # Filters input hash to contain only columns in given table

my ($dbh,$table,$input) = @_;
my $data = ();

my @columns = &db_columns($dbh,$table);    # Get a list of columns safeguard data input
foreach my $ikeys (keys %$input) {    # Clean input for save
 next unless ($input->{$ikeys});     #   - no blank fields
 next if ($ikeys =~ /_id$/i);     #   - do not change ID
 next unless (&index_of($ikeys,\@columns) >= 0);   #   - input column must exist
 $data->{$ikeys} = $input->{$ikeys};   # Transfer to input hash
 #$data->{$ikeys} = &demoronise($data->{$ikeys});  # Fix non-standard character input
}

return $data;

}

sub db_columns {

my ($dbh,$table) = @_;
my @columns = ();
my $showstmt = "SHOW COLUMNS FROM $table";
my $sth = $dbh -> prepare($showstmt);
$sth -> execute();
while (my $showref = $sth -> fetchrow_hashref()) { push @columns,$showref->{Field}; }
die "Can't find any columns for $table" unless (@columns);
return @columns;
}

sub index_of {

# Get item and array from input
my ($item,$array) = @_;

# Initialize counter
my $index_count = 0;
# For each item in the array
foreach my $i (@$array) {
 # Return the counter value if it matches item
 if ($item eq $i) { return $index_count; }

 # Increment the counter
 $index_count++;
}

# Return -1 if no match is returned
return "-1";
}

As you can see, what it does right now is print a list of all the items in the new 'author' table I created, as well as add some test data into the new database. To finish this script I need to create some hashes to implement the crosswalk, then run an INSERT command for each of the three Drupal tables using the crosswalk.

This is a small job, but I don't want to finish and run the script just yet. My website, after all, is a working website. Every time I add a new link, I could be adding a new author. So I don't want to run this until I'm ready to commit to the changeover.

Mailing Lists

The other major thing I need to do is to set up a mailing list. After all, most of my readers still receive OLDaily by email (three or four times as many as by RSS, in fact). So I can't make the changeover without setting up the mailing lists.

I don't want to use my current script. There's a simple reason: it's not very good. Oh sure, it will send the emails, but it takes a long time and ties up system resources. It makes much more sense to use a proper mailing list program.

CSoft supports a mailing list manager called exmlm (easy Mailing List Manager). Here is all the documentation CSoft provides:

By invoking ezmlm-make from your shell. We do not allow mailing lists which accept posts from non-subscribed addresses, so make sure to always pass the -g or -m flag to ezmlm-make.

The command to create a "foo@domain.com" mailing list, would look like this -

$ ezmlm-make -g ~/foo ~/.qmail-domain:com-foo foo domain.com

A moderated list is created using the -m flag, and by subscribing moderators like so -
$ ezmlm-make -m ~/foo ~/.qmail-domain:com-foo foo domain.com
$ ezmlm-sub ~/foo/mod moderator@domain.com

Honestly, that's pretty inadequate (the link is to the unix man page for ezmlm, but this is unfortunately not written in English). Moreover, following those instructions to the letter doesn't work; the email simply bounces (of course - I could be sending it to the wrong address). There is a nice web interface for it, but that doesn't appear to be supported by CSoft and because the developers created a Perl module (which means you need admin provileges to use it) I can't use it (which is exactly why I am moving away from Perl - the strategy of requiring perl modules you need superuser privileges to install is borked).

OK, maybe Drupal supports mailing lists.

I tried a search (why oh why do irrelevant Drupal discussions always rise to the top of Google searches of the Drupal website, and not things like, say, documentation?). After getting a bunch of useless results, I combined 'Drupal' and 'exmlm' in my search and got this page, the Drupal ezmlm module.

However, it says, "Note that this module, ezmlm, will no longer be maintained after release 4.7 because its functionality has been superceded by the Mailing List Manager (mlm) module."

OK, so I went to the Mailing List Manager module, downloaded the module and installed it as per usual. Only to be greeted by a slew of database errors. Huh? What?

Well, according to the bug reports, the Mailing List manager module installation routine simply failed to install the necessary tables in the database. This is just me, but wouldn't this be a really big problem? You'd think - but I see no activity on the module for months.

This then is probably another one of those abandoned Drupal modules. This, it seems to me, is a real problem for Drupal. If the modules are basically abandoned, they should be removed from the site. From what I can tell, the Drupal site is littered with half-built and semi-working modules. How about a little quality control here. Or at least, some requirement that you finish coding the thing before it gets listed.

I saw through another link something called 'og2list' - of course, what the search turned up was this screencast page and then this totally irrelevant page (the bad search results is a huge problem for me using Drupal - and it's probably because there's no %$%$ links to the documentation for Google to seize on, so Google has no way of knowing which Drupal website pages are important).

I eventually found the module here. But this is way more than I want - it is basically a set of modules that turns a Drupal installation into something like Yahoo groups. Nice, but not what I'm after with my website. Also, it requires all kinds of admin privileges to install, so once again I'm at sea here.

So far as I can tell, Drupal does not support a functioning mailing list module. So I wrote a note to my host technical support asking for either (a) help setting up my mailing lists, or (b) documentation.

So, another day. Not sure what I'm going to do. At least Perl works on my site, so if worst comes to worst I can just use my existing script. But I think that the CSoft people wouldn't like that very much.