Welcome Back blog!

After losing my blog domain a few years back, I finally have this back up again – on a new domain, of course…

Funny thing is that mathewfrank.com never used to be available.

Bare with me while this blog gets setup properly – this won’t keep the default theme for long…


Posted in Uncategorized | Comments Off

A Cool Blog From a Cool Lady

Okay it’s been like almost a year since I posted last… Pretty slack since I used to post every day, but you know how it is.

Life’s highlights recently have been getting the flu, missing a big swing dancing festival, and dropping back on my dragon boat training. Still smiling, and getting my training back on schedule now, though.

If the above was a bit de-motivating, I could tell you that I plan to help Ice Dragons break the distance record a single boat/team over 24 hours, or you could just check out this new blog, from a friend of mine:
Persistence of Forward Motion

Be sure and tell her Mathew sent ya!


Posted in default | Comments Off

Hoops to jump through if using Oracle 10g (instead of Postgres 6+)

I’ve been using PostgreSQL for years, and I’ve often made use of cacheing of functions.

Last year I discovered – working on an Oracle 10g environment, that any sort of caching of functinon results does not exist. (It does for 11, though) This totally blew me away, and lead to the following soluction. PostgreSQL people may enjoy reading what they can happily avoid…

The solution utilises oracle Packages. These are assembles of functions and procedures that stay in memory for a time after first being called. The packages can contain member private variables – and these are what we use to cache the results.

The result of doing this calculation in a cached function was a halving of run-time.

FUNCTION keyword_text_clean(i_rawtext IN VARCHAR2 ) RETURN VARCHAR2;
END etl_utils;

create or replace PACKAGE BODY SCHEMA.etl_utils IS
TYPE assoc_array_str_type IS TABLE OF VARCHAR2(200) INDEX BY VARCHAR2(200);
TYPE array_num_type IS TABLE OF number INDEX BY pls_integer;
TYPE assoc_array_nums_type IS TABLE OF array_num_type INDEX BY VARCHAR2(200);

assoc_array_str assoc_array_str_type;

assoc_array_nums assoc_array_nums_type;


FUNCTION keyword_text_clean(i_rawtext IN VARCHAR2 ) RETURN VARCHAR2
l_out VARCHAR2(200);
l_out_upper VARCHAR2(200);
l_key VARCHAR2(200);
l_found BOOLEAN := TRUE; — set to TRUE to enable caching
l_key := trim(i_rawtext);
l_out := assoc_array_str( l_key );
l_found := FALSE;

–sometimes short text comes back NULL erroneously
–this means we can’t rely on looking up the keys that should resovle to null
–pain but makes only a small difference

l_found := FALSE;

–do calculation for value and store in array-cache.
IF NOT l_found THEN

l_out := …..

assoc_array_str( l_key ) := l_out;
RETURN l_out;


Posted in postgresql | Comments Off

in response to: Need Help Reducing View Calculations

I'm posting this via trackback because of a strange posting problem: (layout is strange and missing the email field? Yes I am using a crud browser from here… that could be it)

I have to agree with Hernan.

The simplest saving I can see is an extra column to save on the "run time" calculation. This does not have to be dangerous (ie potentially different to the date column you have already)Just setup a trigger for whenever the existing date column gets updated(/inserted) to update the extra column.

Another alternative is to use a materialised view. On Postgres this is a manual process involving setting up a view and rules, and ig you want it always up to date, triggers again on the source table to update the materialised view whenever the data changes.

This latter idea would be the fastest – faster than your original view by quite a margin, in fact. You may then be able to get rid of some (all?) indexes you have already on the existing table – because they would no longer be needed.

One thing I think would be handy that I have on my list of "when I get time" is to write some reasonably automatic code to do materialised views without the manual coding (ala Oracle). People may use these things more if they were a bit more simple to do.

Link to Need Help Reducing View Calculations page: http://www.justatheory.com/computers/databases/postgresql/reducing_view_calculations.html

Posted in postgresql | Comments Off

Data Warehouse / Business Intelligence – PostgreSQL or Oracle

I’ve just started a new contract for a Federal department where an old grants management system is being replaced with a PostgreSQL/Java based version. The sister project (sub project really) is a data warehouse. The choices were Oracle or PostgreSQL (PostgreSQL was what attracted me to the contract actually)

The argument that I have been unable to win in putting together a warehouse on PostgreSQL comes down to tool maturity – ie the risk involved in something “not proven” so it is almost certainly going to be built using Oracle Warehouse Builder and a BI tool tbd.

I have a long history with databases, and experience with PostgreSQL that dates to the beginning of version 7, however I’ve never been involved in formal datawarehousing or ETL – so I can’t speak with the authority I would prefer ;-)

From my research (mostly involving asking Liam at Fujitsu) Bizgres which seems to be still at the “not finished” stage, and Jasper. I have experience in Jasper products from some time back – they looked very good then – so I can’t wait to try out JasperETL.

In any case I am likely to do a parallel run of the work with OWB using Jasper/Postgres to see how they go in my own time. I would welcome any comments on all this – particularly if they can be backed up with real-world datawarehousing projects.

For those wondering about this blog, family illness has kept me from this for some time, unfortunately… something that is now coming to an end in a good way.


Mathew Frank (http://frakkle.com)

Posted in postgresql | Comments Off

Finally a Keyword Competition System as I Would Build It

With thanks to Rebecca @ seomoz for finding it – this system looks really good for people wanting more than just classic “keyword research” and really want focused business information on what is happening in the market.

I highly recommend you check it out – the free vesion is very good. Well done guys!


For a more complete pulling about of the service, look here: http://feeds.feedburner.com/~r/seomoz/~3/64378835/blogdetail.php

Mathew Frank

Posted in default | Comments Off

PHP mime_content_type() Alternative avoiding PECL and PHPCompat

Okay – its been a long time between posts (that’s code for very busy). The following is something I came accross while installing a membership system on somebody elses server. Aside from old PHP4, there were missing libraries… this is the result.

If mime_content_type is unavailable, and so is the PECL FileInfo library and so is the shell (in which case you are pretty annoyed by now) here is an alternative that falls back on the file extension – instead of ‘magic’.

I took the mime list basically and used that for the extension check. note that unlike ‘magic’ file detection this will get fooled by a bad extension. So a PDF file called file.zip will be reported as an ‘application/zip’ file.

Like the PHP Compat library, this falls back to ‘application/octet-stream’

if (!function_exists('mime_content_type')) {
function mime_content_type($filename) {
$idx = strtolower(end( explode( '.', $filename )) );
$mimet = array( 'ai' =>'application/postscript',
'aif' =>'audio/x-aiff',
'aifc' =>'audio/x-aiff',
'aiff' =>'audio/x-aiff',
'asc' =>'text/plain',
'atom' =>'application/atom+xml',
'avi' =>'video/x-msvideo',
'bcpio' =>'application/x-bcpio',
'bmp' =>'image/bmp',
'cdf' =>'application/x-netcdf',
'cgm' =>'image/cgm',
'cpio' =>'application/x-cpio',
'cpt' =>'application/mac-compactpro',
'crl' =>'application/x-pkcs7-crl',
'crt' =>'application/x-x509-ca-cert',
'csh' =>'application/x-csh',
'css' =>'text/css',
'dcr' =>'application/x-director',
'dir' =>'application/x-director',
'djv' =>'image/vnd.djvu',
'djvu' =>'image/vnd.djvu',
'doc' =>'application/msword',
'dtd' =>'application/xml-dtd',
'dvi' =>'application/x-dvi',
'dxr' =>'application/x-director',
'eps' =>'application/postscript',
'etx' =>'text/x-setext',
'ez' =>'application/andrew-inset',
'gif' =>'image/gif',
'gram' =>'application/srgs',
'grxml' =>'application/srgs+xml',
'gtar' =>'application/x-gtar',
'hdf' =>'application/x-hdf',
'hqx' =>'application/mac-binhex40',
'html' =>'text/html',
'html' =>'text/html',
'ice' =>'x-conference/x-cooltalk',
'ico' =>'image/x-icon',
'ics' =>'text/calendar',
'ief' =>'image/ief',
'ifb' =>'text/calendar',
'iges' =>'model/iges',
'igs' =>'model/iges',
'jpe' =>'image/jpeg',
'jpeg' =>'image/jpeg',
'jpg' =>'image/jpeg',
'js' =>'application/x-javascript',
'kar' =>'audio/midi',
'latex' =>'application/x-latex',
'm3u' =>'audio/x-mpegurl',
'man' =>'application/x-troff-man',
'mathml' =>'application/mathml+xml',
'me' =>'application/x-troff-me',
'mesh' =>'model/mesh',
'mid' =>'audio/midi',
'midi' =>'audio/midi',
'mif' =>'application/vnd.mif',
'mov' =>'video/quicktime',
'movie' =>'video/x-sgi-movie',
'mp2' =>'audio/mpeg',
'mp3' =>'audio/mpeg',
'mpe' =>'video/mpeg',
'mpeg' =>'video/mpeg',
'mpg' =>'video/mpeg',
'mpga' =>'audio/mpeg',
'ms' =>'application/x-troff-ms',
'msh' =>'model/mesh',
'mxu m4u' =>'video/vnd.mpegurl',
'nc' =>'application/x-netcdf',
'oda' =>'application/oda',
'ogg' =>'application/ogg',
'pbm' =>'image/x-portable-bitmap',
'pdb' =>'chemical/x-pdb',
'pdf' =>'application/pdf',
'pgm' =>'image/x-portable-graymap',
'pgn' =>'application/x-chess-pgn',
'php' =>'application/x-httpd-php',
'php4' =>'application/x-httpd-php',
'php3' =>'application/x-httpd-php',
'phtml' =>'application/x-httpd-php',
'phps' =>'application/x-httpd-php-source',
'png' =>'image/png',
'pnm' =>'image/x-portable-anymap',
'ppm' =>'image/x-portable-pixmap',
'ppt' =>'application/vnd.ms-powerpoint',
'ps' =>'application/postscript',
'qt' =>'video/quicktime',
'ra' =>'audio/x-pn-realaudio',
'ram' =>'audio/x-pn-realaudio',
'ras' =>'image/x-cmu-raster',
'rdf' =>'application/rdf+xml',
'rgb' =>'image/x-rgb',
'rm' =>'application/vnd.rn-realmedia',
'roff' =>'application/x-troff',
'rtf' =>'text/rtf',
'rtx' =>'text/richtext',
'sgm' =>'text/sgml',
'sgml' =>'text/sgml',
'sh' =>'application/x-sh',
'shar' =>'application/x-shar',
'shtml' =>'text/html',
'silo' =>'model/mesh',
'sit' =>'application/x-stuffit',
'skd' =>'application/x-koan',
'skm' =>'application/x-koan',
'skp' =>'application/x-koan',
'skt' =>'application/x-koan',
'smi' =>'application/smil',
'smil' =>'application/smil',
'snd' =>'audio/basic',
'spl' =>'application/x-futuresplash',
'src' =>'application/x-wais-source',
'sv4cpio' =>'application/x-sv4cpio',
'sv4crc' =>'application/x-sv4crc',
'svg' =>'image/svg+xml',
'swf' =>'application/x-shockwave-flash',
't' =>'application/x-troff',
'tar' =>'application/x-tar',
'tcl' =>'application/x-tcl',
'tex' =>'application/x-tex',
'texi' =>'application/x-texinfo',
'texinfo' =>'application/x-texinfo',
'tgz' =>'application/x-tar',
'tif' =>'image/tiff',
'tiff' =>'image/tiff',
'tr' =>'application/x-troff',
'tsv' =>'text/tab-separated-values',
'txt' =>'text/plain',
'ustar' =>'application/x-ustar',
'vcd' =>'application/x-cdlink',
'vrml' =>'model/vrml',
'vxml' =>'application/voicexml+xml',
'wav' =>'audio/x-wav',
'wbmp' =>'image/vnd.wap.wbmp',
'wbxml' =>'application/vnd.wap.wbxml',
'wml' =>'text/vnd.wap.wml',
'wmlc' =>'application/vnd.wap.wmlc',
'wmlc' =>'application/vnd.wap.wmlc',
'wmls' =>'text/vnd.wap.wmlscript',
'wmlsc' =>'application/vnd.wap.wmlscriptc',
'wmlsc' =>'application/vnd.wap.wmlscriptc',
'wrl' =>'model/vrml',
'xbm' =>'image/x-xbitmap',
'xht' =>'application/xhtml+xml',
'xhtml' =>'application/xhtml+xml',
'xls' =>'application/vnd.ms-excel',
'xml xsl' =>'application/xml',
'xpm' =>'image/x-xpixmap',
'xslt' =>'application/xslt+xml',
'xul' =>'application/vnd.mozilla.xul+xml',
'xwd' =>'image/x-xwindowdump',
'xyz' =>'chemical/x-xyz',
'zip' =>'application/zip'

if (isset( $mimet[$idx] )) {
return $mimet[$idx];
} else {
return 'application/octet-stream';
Posted in default | Comments Off

Internet Marketers Only Social Bookmarking Service

Having been reading the Buttefly Marketing book (get it 40% off standard price here) I’ve setup an experiment using a domain I’ve had kicking around for ages.

I’ve combined the features of delicious, digg, and technorati into a
social tagging system for internet marketers only. It sort of
stumped me that such a thing has not been done until now, really.

In any case I hope this system is able to grow into a useful resource
as more people start putting their bookmarks into it. I’d love
for you to check it out.


technical details: I started with code from scuttle that
had been modified with a digg voting system, and inserted technorati
using the pear library. It’s written in PHP.


Posted in default | Comments Off

Save 40% on Mike Filsaime’s Butterfly Marketing Manuscript (take 2)

For those of you who tried yesterday to look at this, my
apologies. My blog software stuffed things up… all fixed now ;-)

This is the only place on the net you will see that can get Mike Filsaime’s
$98 Butterfly Marketing book with $40 off.

That’s amost half price!

Get is now before my server crashes again (just kidding) ;-)

Put your details in the form below, and I will send you instructions on
just how you can claim your cash back.

As a bonus, I’ll also give
you a free copy of the first chaptor of the book itself.

First Name: Email:

Link to the butterfly marketing manuscript



Tags: , , , , , ,

Posted in default | Comments Off

40 Percent Off Mike Filsaime’s Butterfly Marketing Manuscript

All around the net you will be seeing how to get Mike Filsaime’s
Butterfly Marketing book for $97. But only here will you see how
you can get this great book for 40% off.

That’s amost half price!

Put your details in the form below, and I will send you instructions on
just how you can claim your cash back.

As a bonus, I’ll also give
you a free copy of the first chaptor of the book itself.

First Name: Email:

Link to the butterfly marketing manuscript



Tags: , , , , , ,

Posted in default | Comments Off