Raku, pod, and literate programming

PostgreSQL Remote Administration

I want to use graphical programs (particularly pgAdmin III) to administer PostgreSQL on an external, remote server. The existing documentation I can find doesn't cover that situation but i finally found a solution by multiple queries and trial and error. The solution follows.

Assumptions:

• You have root access to the remote server and thus full control over it and your PostgreSQL installation. You will need assistance from your server administrator if you don't have root access.
• The server is running a Linux O/S. My server is Ubuntu 10.04 LTS but the procedures should work with most Linux PostgreSQL installations.

There is one main problem that needs to be addressed that requires two major steps to solve: allowing remote access to the server by the "postgres" user.

(To be continued if there is interest...)

C Heap Arrays and Libc Qsort

Background

Years ago I had a solution for the subject problem, but I deleted it when I started using C++ and the STL and didn't need to worry about it any more. However, I now help with a large Open Source project (BRL-CAD), the core of which is still programmed in C (and likely to remain so). Consequently, I recently needed to use qsort on a heap array of pointers and the nightmare cropped up again.

Once more I tried to find a good example that worked and couldn't. All the examples I found used arrays on the stack and they didn't use arrays of pointers. Eventually, I found the solution through some grunt testing and trial and error. I hope someone can use my example now.

The code

#include 
#include 
#include 

const int debug = 0;
/* Define an array of critters to sort. */

typedef struct crit
{
  const char* name;
  int         index;
} crit_t;

const crit_t dat[] =
{
  {"Gonzo",          3},
  {"Fozzie",         3},
  {"Kermit",         1},
  {"Piggy",          2},
  {"Sam",            3},

#if 0
  {"Robin",          6},
  {"Animal",         8},
  {"Camilla",        8},
  {"Sweetums",       9},
  {"Dr. Strpork",   10},
  {"Link Hogthrob", 11},
  {"Zoot",          12},
  {"Dr. Honeydew",  13},
  {"Beaker",        14},
  {"Swedish Chef",  15},
#endif

};
const int ncrits = sizeof(dat)/sizeof(crit_t);

// the sort functions MUST have this signature
int
crit_index_sort(const void* p1, const void* p2)
{
  // cast args into the desired type
  const crit_t* c1 = *(crit_t**)p1;
  const crit_t* c2 = *(crit_t**)p2;

  // now do comparisons
  if (c1->index < c2->index)
    return -1;
  else if (c1->index > c2->index)
    return +1;
  else
    return strcmp(c1->name, c2->name);
}

int
crit_name_sort(const void* p1, const void* p2)
{
  // cast args into the desired type
  const crit_t* c1 = *(crit_t**)p1;
  const crit_t* c2 = *(crit_t**)p2;

  // now do comparisons
  if (debug) {
    printf("DEBUG: comparing %s and %s\n", c1->name, c2->name);
  }
  int ncmp = strcmp(c1->name, c2->name);
  if (ncmp) {
    return ncmp;
  }
  else if (c1->index < c2->index)
    return -1;
  else if (c1->index > c2->index)
    return +1;
  else
    return 0;
}

int
main(int argc, char** argv)
{
  int i;
  // an array from the heap
  crit_t** muppets = (crit_t**)malloc(sizeof(crit_t*) * ncrits);

  // fill it
  for (i = 0; i < ncrits; ++i) {
    crit_t* c = (crit_t*)malloc(sizeof(crit_t));
    c->name = strdup(dat[i].name);
    c->index = dat[i].index;
    muppets[i] = c;
  }

  // show it
  printf("\nUnsorted array:\n");
  for (i = 0; i < ncrits; ++i) {
    printf("  %2d  %s\n", muppets[i]->index, muppets[i]->name);
  }

  // output
#if 0
Unsorted array:
   3  Gonzo
   3  Fozzie
   1  Kermit
   2  Piggy
   3  Sam
#endif

  // alpha sort
  qsort(muppets, ncrits, sizeof(crit_t*), crit_name_sort);

  // show it
  printf("\nSorted by name:\n");
  for (i = 0; i < ncrits; ++i) {
    printf("  %2d  %s\n", muppets[i]->index, muppets[i]->name);
  }

  // index sort
  qsort(muppets, ncrits, sizeof(crit_t*), crit_index_sort);

  // output
#if 0
Sorted by name:
   3  Fozzie
   3  Gonzo
   1  Kermit
   2  Piggy
   3  Sam
#endif

  // show it
  printf("\nSorted by index:\n");
  for (i = 0; i < ncrits; ++i) {
    printf("  %2d  %s\n", muppets[i]->index, muppets[i]->name);
  }

  // output
#if 0
Sorted by index:
   1  Kermit
   2  Piggy
   3  Fozzie
   3  Gonzo
   3  Sam
#endif

}

(to be continued)

A contacts and genealogy database

One of my long-term projects has been the desire for a centralized contacts database (DB) that can be used in multiple places. Over the years I've used PDAs, MS Access, etc.

My MS Access database is currently the repository for all my personal contacts, and I have a working process to produce a Christmas mailing list and mailing labels with it: csv dump of selected fields, take csv file to Linux box, use Perl program to generate PostScript files for labels, convert ps to pdf, print pdf file anywhere (if you print from Adobe, remember to select no scaling).

Recently I've started using my Google Mail contacts and have happily discovered that I can get it synched so I can access it from my iPhone, and I have written Perl programs to enable up- and downloading my Google contacts.

But I still don't have exactly what I want. First, I really want my central database completely under my control without Microsoft (or Google) in the way. Second, I realize the flat contact lists I have need to really be a single relational database to accommodate the relational aspects of my contact data as realized in a simple Christmas card list, e.g., children of friends and family grow up and start families of their own, thereby becoming a new entry for the mailing list. Third, I want my database to be available online to friends and family to use and update. And fourth, I want it to be the authoritative source for all my personal contacts and family information.

As I've studied the problem, I've decided that my database really needs to be designed to handle many aspects of a genealogy database, in fact, I think they could really be different views of the same. After reorienting my design plans, I've been searching for an existing, open source database solution and looked at a couple (using the site Database Answers as a helpful guide along the way):

• grubba, a general purpose DB
• GRAMPS, a genealogical DB

but none of them really seem to fit the bill (also I found a good argument that the standard genealogical database format is not ideal).

Then I stumbled upon what I think is the epitome of a good genealogy and contacts DB design, which happens to use PostgreSQL, my RDBMS of choice, in Leif Kristensen's Yggdrasil. Leif has done yeoman's work in designing a genealogy database, necessary functions, and a web interface access to it. His database has provided me practical insights into how to accomplish my goals, and I am indebted to him for showing the way.

To be continued...

Muliple Virtual Hosts on a Cloud Server

I have spent some time getting an inexpensive cloud server set up to host my hobby domains and have leapt enough hurdles to think I can document the process better than any single document I've found (but note that I've received lots of help from various open source project mailing lists, particularly from Mark Sapiro at Mailman-Users@python.org).  Follow me as I re-trace my steps.

The domains:

• mygnus.com (the "real" domain)
• usafa-1965.org (virtual: site for my college classmates)
• highlandsprings61.org (virtual: site for my high school classmates)

Domain registrar:

• 1 and 1 (http://1and1.com)

Cloud server hosting:

•  Rackspace Cloud (http://rackspacecloud.com)

The goals:

• use SSL/TLS encryption for all web access
• host multiple mailing lists for each virtual domain with one GNU Mailman instance
• use an RDBMS for Apache2 Digest authentication for access to user contact data (also on an RDBMS)
• I first used MySQL, but could not get the current Ubuntu Apache2 mod_dbd to work
• now I'm using the latest release of PostgreSQL (9.0.4), built from source, with which I am very satisfied

The cloud server:

• Ubuntu 10.04 LTS (64-bit), 256 Gb RAM, 10 Gb disk space, single CPU

The primary software needing configuration (all from Ubuntu packages):

• Apache2 (http://httpd.apache.org)
• MySQL (http://www.mysql.org)
• Postfix (http://postfix.org)
• GNU Mailman (http://www.list.org)
• SQLite3 (http://sqlite.org)

Other resources required:

• PostgreSQL (http://postgresql.org)
• a single IP, wild card, multi-domain SSL/TLS certificate
• I got an inexpensive but effective one from: StartSSL (http://startssl.com)

To be continued...

Parsing Apache Access Logs with Mixed Formats

I changed the LogFormat for my Apache server recently from the default common log format (CLF) to the "vhost_common" format and couldn't find an easy solution to handle access logs using multiple formats. The easiest Perl module I found to use is "Parse::AccessLogEntry" (from CPAN.org), but it only works with the common log format. However, with a simple hack I can now parse logs with either or both formats. The trick is that the "vhost_common" format has only one token extra compared to the CLF and that token is the first one on the log line. It is easily detected so it can be removed if present, and the rest of the line can then be handled normally since it will then be in CLF.

Following is a fragment of code from the log line parsing loop showing the hack:

# the incoming line may be in CLF or vhost_common format # split the line on space to tokenize it my @d = split(' ', $line); next if !defined $d[0]; my $vhost = $d[0]; # the vhost token is in format "servername:port" # and the next token (the first in the CLF format) is # the remote host address in format "xxx.xxx.xxx" so # the presence of the ':' tells us the type # of format we have my $idx = index $vhost, ':'; if ($idx >= 0) {   # we have detected the vhost info   # I remove the port info, you may want it   $vhost = substr $vhost, 0, $idx;   # remove the vhost token from the list   shift @d;   # reconstitute the log line into the CLF   $line = join(' ', @d); } else {   # we din't find vhost so set it to zero   $vhost = 0; } # parse the CLF my $href = $p->parse($line); if ($vhost) {   # add the vhost to the hash   $href->{vhost} = $vhost; }

I'm sure the code can be improved, but it does work as is.

SSL Certificates, Virtual Web Sites, Perl for Apache AuthDigest

Note that I have changed two of my favorite links: (1) Debian is now my Linux distribution of choice and (2) I had to fall back to the more powerful Apache HTTPD server because of features I could not find elsewhere.

I have just finished two Perl scripts to aid in Apache htdigest handling for large numbers of users (a database solution will be better but I don't have the time for that at the moment). The first uses the program *nix program pwgen() to yield reasonably secure clear text passwords for an input list of user names. The second takes an input list of pairs of user names with clear text passwords and produces an Apache htdigest file. I will post them if I get any interested responses.

I have been working part time on multiple virtual hosts on my single cloud httpd server and have learned much--with lots to go. The main sites are two for my college and high school graduating classes and one for my company (and I would be very interested in critiques of them):

• USAF Academy, CO, Class of 1965
• Highland Springs HS, VA, Class of 1961
• VALUE (a program sold by ManTech International Corporation)

In the process of building the web sites I found a source for a reasonably priced SSL certificate for multiple virtual hosts on a single server: StartSSL whom I highly recommend.