Sunday, 13 January 2013

OpenCart efficiency - logging viewed products

OpenCart, straight out of the box, helpfully records products viewed.

/catalog/model/catalog/product.php called from catalog/controller/product/product.php

public function updateViewed($product_id) { $this->db->query("UPDATE " . DB_PREFIX . "product SET viewed = (viewed + 1) WHERE product_id = '" . (int)$product_id . "'"); }

This simple update statement can slow the system down while users fight for the table. To help your site's performance out, add a low priority:

public function updateViewed($product_id) { $this->db->query("UPDATE LOW_PRIORITY " . DB_PREFIX . "product SET viewed = (viewed + 1) WHERE product_id = '" . (int)$product_id . "'"); } 

And for the kitchen sink approach, defrag and reindex the database. If you feel like helping everyone out, do that for the all the databases. To do so, run this at the command line:

mysqlcheck -u -p -a -o --all-databases;

Monday, 5 November 2012

November's coding horror

Well it's early, but I'm struggling to envisage another coding horror being able to beat this one.


November's coding horror

Wednesday, 8 August 2012

Major SQL efficiency tip - drag and drop table column list in SSMS

Most MSSQL DBAs know the object explorer in Microsoft SQL Management Studio has a heap of tricks that go mostly under used, and a new one to me until just a few days ago is the ability to drag all of the column names straight into the query editor.

To say 1,000 words in just one image, here you go:

Click the table columns folder, hold the click and drag to where you want your column list and release.

Friday, 8 June 2012

Proud to be British - free British Manufacturer images

The Jubilee. The Euros. The Olympics. I have been totally swept away with patriotism - a fantastic shot of adrenalin to fight off the recession depression that threatens to consume all.

Recent documentaries continue to highlight why we should be proud of our Manufacturers too, so on the off-chance that "flying the flag" helps - you're welcome to these:

Tuesday, 17 April 2012

Measuring query speed versus cost in T-SQL

Every DBA spends considerable time constructing queries and managing their server but do as many spend time optimising their individual queries?

Finding time

For those of you keen to reduce your running time, a great tool to help is the execution plan.

Working on two queries within one editor provides you the ability to compare the cost on the server which 9 times out 10, produces a faster query too.

Cost means Cost

But cost means cost - not speed.

To see the actual execution time, stamp: set statistics time on (set back to off if you don't want it on anymore) at the start of your query, then under messages you'll be shown both the CPU time and elapsed time.

Now it's up you to balance server cost versus speed. What will you choose?

Monday, 16 April 2012

There was a problem creating the addondomain

Generally the set up of Add-On domains is pretty straightforward but you can bump into cPanel's security, e.g.,
"There was a problem creating the addondomain.
Error from park wrapper: Sorry, cannot determine nameserver IPs. Please make sure that the domain is registered with a valid domain registrar".
Presumably you configured the registar settings correctly so it's now just about tweaking your security settings temporarily:

FIXING IT

Pay a visit to the Tweak Settings section (Main >> Server Configuration >> Tweak Settings) and allow two actions disabled by default:
  1. allow remote domain domains (allow creation of parked/addon domains that resolve to other servers (e.g. domain transfers) This can be a major security problem. If you must have it enabled, be sure not to allow users to park common Internet domains),
  2. allow unregistered domains (Allow creation of parked/addon domains that are not registered.).

Now I suggest you create that Add-On domain then revert these settings so you're back working with a nice secure server again - now with your new domain to play with too!

Friday, 9 March 2012

OpenCart google_base infinite loop risk

If you're using OpenCart, you'll likely be using Google Base too - a wise bet to improve your reach.

While OpenCart is a superbly well thought out and well developed product, like with every piece of programming there are limitations or opportunities for improvement. With the google_base file there is a risk of hitting a never ending loop, some missing escaping and a few attributes missing.

To start you off, this is the remedy for the biggest problem - the possibility for a loop.

File: /catalog/controller/feed/google_base.php

Line 1: protected function getPath($parent_id, $current_path = '') {
Line 2: $category_info = $this->model_catalog_category->getCategory($parent_id);
Line 3:
Line 4: if($parent_id == $current_path)//avoid looping
Line 5: return $current_path;
Line 6:
Line 7: if ($category_info) {
Line 8: if (!$current_path) {
Line 9: $new_path = $category_info['category_id'];
Line 10: } else {
Line 11: $new_path = $category_info['category_id'] . '_' . $current_path;
Line 12: }
Line 13:
Line 14: $path = $this->getPath($category_info['parent_id'], $new_path);
Line 15:
Line 16: if ($path) {
Line 17: return $path;
Line 18: } else {
Line 19: return $new_path;
Line 20: }
Line 21: }
Line 22: }


Simply add the two lines entered to your google_base  file to avoid the risk of an infinite loop.

Saturday, 3 March 2012

Oh dear, another coding horror:

Today, I very fortunately stumbled across some code responsible for reassembling an object following a log in.

What this author hadn't considered was: what if the user had already start creating the object before they attempted a retrieve...

(actual object namings have been altered)

Line 1: if(Session["foo"] == null) {
Line 2:     Session["foo"] = actual_obj_value;
Line 3: }


Amazing eh! But luckily I found it, so maybe an iceberg avoided... or perhaps several icebergs hit but ignorance is bliss after all.

Thursday, 5 January 2012

Conditionally add (which replaces) headers via Spam Filter Rules with VPOP3

VPOP3 is a Windows based email server that provides a plethora of nifty features to help manage email for one or many domains.

Today, I exploited another of VPOP3's helpful features in a way I hadn't before anticipated.

The problem: How to stamp a display name to all outbound email based on the sender domain

The solution: Conditionally add (which replaces) headers via Spam Filter Rules

The spamrules_userlocal.txt in the local VPOP3 folder is the home for customised spam rules - which are protected from future VPOP3 upgrades.

Spam rules are particularly flexible with various statements supported - see the VPOP3 wiki site for lots of help on this. (http://wiki.pscs.co.uk/reference%3Bspam_filter_rules)

With these tools the solution becomes straightforward:

IfMatch from /@domain1/
AddHeader "From: Company1 <${Mailfrom}>"
AddHeader "Organization: Company1"
EndIf
IfMatch from /@domain2/
AddHeader "From: Company2 <${Mailfrom}>"
AddHeader "Organization: Company2"
EndIf

Tuesday, 13 December 2011

CSS selectors

The past weeks have thrown more of my time back into the world of front end web development and away from all that good SQL and server side development - a small shame, but I do love front end development too, especially so since jQuery arrived.

While I'm hit by the usual enjoyment and satisfaction of working with aesthetics the usual frustration at my sieve of a memory with CSS selectors has also came back with a bump. So, in the "if I write it down I might remember it" mode, here goes a recap of my three favourites to forget for me (and maybe you).

Specify a declaration on the combination of element type and class:
        DIV.CLASS

Specify elements (such as DIVs) which are the direct children of an element with a particular id:
        #ID >  DIV

Specify elements that are a child (or grandchild or.. ) of an element with a particular id:
        #ID DIV

Thursday, 24 November 2011

Finally, the battle with PHPMailer and Goldmine Web Import is over

Goldmine integration can be approached several ways with the most simple being the built-in web import facility.

By marking up an email in a particular way, a goldmine user upon opening said email will execute the web import (which is essentially some simple instructions and some data to be used for inserting or updating fields).

Before I go on, yes, you're right, doesn't this sound a nice thing for hackers to exploit... I'll give you some tips on this in another post, one thing at a time!

Anyway, if you find yourself in a similar position to me, where you just need to make this work, read on:

The Goldmine manual tells us by simply adding Content-Type: application/x-gm-impdata to our email headers, Goldmine will initiate a webimport on reading (i.e., a user opening the email).

This instruction is 100% correct, however, it is imperative that the unknown mime type's addition to the email headers is placed at the right point, not doing so will result in the email importing successfully but later reading of said email will render the lovely unknown mime type: application/x-gm-impdata.

Tip

Make sure when you add your goldmine content type you do so as the last line of the html boundary header, when written in PHP, resulting in something similar to:
$result .= $this->TextLine("--" . $boundary);
$result .= sprintf("Content-Type: %s; charset = \"%s\"", $contentType, $charSet);
$result .= $this->LE;
$result .= $this->HeaderLine("Content-Transfer-Encoding", $encoding);
       
//gm-mime-type
if($contentType == "text/html"){
  $result .= $this->HeaderLine("Content-Disposition", "inline");
  $result .= $this->HeaderLine("Content-Type", "application/x-gm-impdata");
}
//gm-mime-type-end
       
$result .= $this->LE;

This will mean you provide Goldmine with a suitably written HTML block which contains your web import instruction and leaves you with the plain text part to write whatever you'd like users to read once the email is filed to history.


Hope it helps, @dpitt

Saturday, 19 November 2011

A bit of twavatar (twitter avatar) design with Paint.Net

During the last few weeks I have been swept away with a sudden obsession with graphic editing software - of course only the legally free ones!

My latest production is a new twavatar (twitter avatar).

I set off aiming to produce a jigsaw puzzle but having done so I am a little unsure whether it doesn't quite work - mainly due to poor design of the individual pieces, which I can't seem to crack with Paint.Net, I may have another go via the Gimp.

So... #alittlehelp

In the meantime, could you let me know which you prefer. (Please ignore the ugly chap on the picture, it's the only face I have :) )

Image A - without a jigsaw effect



Image B - with a jigsaw effect


UPDATE:

I just can't resist... here's the latest twavatar:


A half cartoon, with a hint of some of the languages I'm strong with in the background and an xml tag "coder" running up the right. My favourite so far - who knows, I might just leave this one in place for a while...