RSS MAD

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

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.

Cheers,

Mathew Frank (http://frakkle.com)

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!

http://www.keycompete.com/

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'

<?php
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';
		}
	}
}
?>

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.

copywriting-etc.com

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.

tags: butterfly marketing viral marketing social marketing tag and ping  copywriting-etc.com scuttle digg del.icio.us technorati pear

interesting

Human-Anatomy Pictures

Links

Powersite Web CMS Design