PGTS PGTS Pty. Ltd.   ACN: 007 008 568               Mobile Version Coming Soon

point Site Navigation

Valid HTML 4.01!

   Give Windows The Boot!
   And Say Goodbye To Viruses!


   If you own a netbook/laptop~
   Download Ubuntu Netbook!

Generating a List of Links with Postgres and Perl

By Gerry Patterson

Links with other sites are one of the best ways to raise a site's visibility. If you store your links using a database such as postgres, it would be far easier to generate your links page rather than maintaining this list by hand. This document describes a simple perl script that generates a Links page from a postgres table. Scripts such as these can be used in the automation of numerous maintenance procedures.

The importance of being linked.

Dynamic links are an important feature of the Internet and also seem to influence the way that a site rates in search engine rankings, some of which is outlined in the article, Getting Listed in The Search Engines. However it is important that the links be relevant and that they enhance site content. A good feature to have on a site is a facility that allows visitors to add their own link if they think that they have relevant content on their site.

The PGTS links page contains a link which allows visitors to submit their own links to the links page This form posts to a CGI script which copies the data into a postgres table. It then sends an acknowledgement email to the requester and another email to the local site webmaster. The table that the CGI script populates has a structure similar to the following:

  Attribute  |           Type  | Constraint       Description
-------------+-----------------+---------   ---------------------------------
 linkid      | integer         | not null   default nextval('linkid_s'::text)
 link        | varchar2(128)   | not null   Link URL (with http:// stripped)
 email       | varchar2(80)    |            Email address
 name        | varchar2(40)    | not null   Link name
 category    | varchar2(60)    | not null   Link category
 description | varchar2(1024)  |            Link description
 valid       | boolean         |            Valid flag
 create_date | timestamp       | not null   Date Created
 update_date | timestamp       | not null   Date last updated

 Unique Index: weblink_link_key
The CGI script which does this will be documented here soon, so watch this space.

After the link has been approved by the webmaster it can be copied to the local site's links page. However it would be much easier to generate the page again from the links database. This document describes a perl script that generates the links page again. This means that the process of adding the link to the links page and emailing the linkee has been automated. The mlink script, which performs these functions, can be found in the PGTS download area.

This script uses the following variables:
$connect  The connect string for the database. This depends on the way that postgres has been setup.
$SID The database name.
$PSQL The command line that is used to call the terminal-based front-end to Postgres. The options depend on the system setup. Postgres version 7.1.2 for Linux does not seem to like '\t'. Hence there is a physical TAB character ('\011') enclosed in single quotes as the delimiter for the -F option. This may not show up in all text editors. In fact the delimiter can be altered to any character. (Make sure it's sensible and don't forget to split() with the same delimiter)
$sqlout Temporary file that holds the output from the postgres query. Clean them up with cron.
$sqlerr Temporary file that holds the errors from the postgres query. Should also be cleaned up with cron.

The procedure run_sql() expects one string as an input parameter, which it treats as an SQL query. This query is run against the database and the results are stored in the temporary file $sqlout. This is achieved with the '\o' command (open an output channel). The core section of the code has been labeled with the comment "Main". (Old habits die hard). The Main section does some simple validation. It expects a single parameter on input. The single parameter must be a HTML file that is readable by the process. It looks in the HTML for a comment, "<!-- Useful Links -->" and replaces any text it finds up until the comment, "<!-- End Useful Links -->". The output is sent to stdout. Each link in the database is printed as a table entry. You can see the results by looking at the source code for the PGTS Links Page. In order to create a file the output needs to be re-directed to a new file If it looks ok, then the target can be overwritten. Eventually this script will be called by a CGI script actioned by a form, which allows review and approval of the links. Currently this is carried out manually with postgreSQL. A possible change to this process will be the inclusion of a field that allows visitors to suggest a category. At present visitors must choose from the existing categories.

If you find this script useful, please free to take a copy. If you want to provide some feedback you can send an e-mail.

Click here to view the source of the mlink script.