by R.I. Pienaar | Jun 2, 2007 | Code
I’ve been doing a whole lot of programming recently and even getting into doing some MySQL stored procedure and trigger programming. I got a copy of the excellent book MySQL Stored Procedure Programming and can recommend it to anyone keen to get information on the subject.
Usually when dealing with errors in stored procedures or triggers you define a handler for the MySQL error code and either continue – and presumably do something to handle the exception – or exit with an error. When doing an UPDATE with a WHERE clause that does not match any data though no error gets thrown, it just doesn’t do anything.
So I tried to come across some samples of how to get the affected row count but came up short – there are very few online resources that I found about MySQL stored procedures in general. So here is a solution for a simple trigger that updates a table when new data arrives in another.
DELIMITER $$
CREATE TRIGGER trg_update_latest_on_email_stats
AFTER INSERT ON email_stats
FOR each row
BEGIN
DECLARE l_rows INT DEFAULT 0;
UPDATE server_stats SET last_email_time = NEW.time
WHERE server_name = NEW.server_name;
/* how many rows did we affect? */
SELECT ROW_COUNT() INTO l_rows;
/* If we didn't update any rows, then insert new data */
IF (l_rows = 0) THEN
INSERT INTO server_stats (server_name, last_email_time)
VALUES (NEW.server_name, NEW.time);
END IF;
END $$
That’s it, pretty simple stuff.
Data comes in, the trigger fires but if there is no data there nothing happens, so it inserts some data and future updates will pass.
I could have used the REPLACE function for simpler code, but my solution should be faster which is key when using trigggers.
by R.I. Pienaar | Apr 8, 2007 | Front Page
This is part 3 of my series of posts on the general usability of Google Apps for Domains as a hosting service for email. This one really speaks for itself so I’ll keep it short.
It’s a bit like those joke Windows error messages that does the rounds.
by R.I. Pienaar | Apr 2, 2007 | Front Page
This is the 2nd in a series of posts about Google Apps for Domains, I’ve been evaluating it for a while. Today I’ll focus a bit on the XML feeds that Google Calendar outputs as a means of integrating it into other applications.
It basically provides 2 types of XML – a private and a public one. The public one only works if you share the cal publicly and the other is for your own use to pull into apps of your own. Each calendar should have a private one.
This sounds very interesting, so what’s the problem? Well there are some inconsistencies. On my two domains, the one has private URLs on the main calendar the other doesn’t have the option at all. So this means my primary calendar doesn’t have a XML feed unless I share it publicly.
GCal also has a feature that mails you a short daily agenda, much like what you see if you hit the Agenda button. Problem is the agenda mail function ONLY reads from your primary calendar, it does not include events from any subscribed calendars etc. This means that one of the biggest selling points of Google for Domains is crippled, if you share calendars you can’t use them even in the rudimentary tools provided.
I can’t use my primary private calendar with XML feeds, and I can only get events mailed from the primary calendar. Why is this a problem? I want to use the shared calendars with people I work with, that’s one of the major reasons to use Apps for Domains.
On my iCal calendars I had a RSS feed that I’d pull into some perl code to generate agendas. I resigned myself to the fact that I’d need to write one myself again now for GCal and wanted to use the XML feeds. I can’t pull events into my primary calendar – it doesn’t have private feeds – so I had to create a new cal and use that – completely invalidating the in UI agenda function. I also can’t delete the primary function, the option is grey’d out.
Back to the feeds, by default the data you’ll get in a feed looks like this for recurring events:
<?xml version='1.0' encoding='UTF-8'?>
<entry xmlns='http://www.w3.org/2005/Atom'
xmlns:gd='http://schemas.google.com/g/2005'
xmlns:gCal='http://schemas.google.com/gCal/2005'>
<id>removed</id>
<published>2006-09-04T18:45:41.000Z</published>
<updated>2007-03-15T23:50:18.000Z</updated>
<category scheme='http://schemas.google.com/g/2005#kind'
term='http://schemas.google.com/g/2005#event'></category>
<title type='text'>Cleaners</title>
<summary type='html'>Recurring Event<br>
First start: 2006-09-01 11:30:00 BST <br>
Duration: 3600 <br>
Who: Shared Calendar <br>
Event Status: tentative</summary>
<content type='text'>Recurring Event<br>
First start: 2006-09-01 11:30:00 BST <br>
Duration: 3600 <br>
Who: Shared Calendar <br>
Event Status: tentative</content>
<link rel='alternate' type='text/html' href='removed' title='alternate'>
</link>
<link rel='self' type='application/atom+xml' href='removed'>
</link>
<author>
<name>R.I. Pienaar</name>
<email>rip@removed</email>
</author>
<gCal:sendEventNotifications value='true'></gCal:sendEventNotifications>
</entry>
Have a good look at that, this is the default feed output, it shows you details for a recurring event – when it was first created and how long it runs for. There is not enough information here for a human to actually figure out when this event is for, they do not give you any information about the schedule, this event may recur once a year, once a day, once a month you just don’t have any idea. You’ll see later that they have all the ability already to make this useful and user friendly, they just didn’t bother.
The devs in the Google Support Groups say the default feed is for ease of reading by a human and not for machines, for machines you need to use the full feed query format . This lets you do all sorts of good queries for feeds, date ranges and all sorts of things.
Not only is the default feed not machine friendly, no human can figure out what a recurring event is, when it runs etc, as a data feed the default GCal feed is completely useless to everyone.
Using the full feed I now have a daily mail that I have to schedule on my own server to produce a daily agenda, something like this:
Things to do TODAY - Monday, 02 April 2007
No events.
Things to do TOMORROW - Tuesday, 03 April 2007
Test Event
Things to do next 7 days:
Friday Cleaners
Friday Good Friday
Saturday Rebecca Birthday Party
Monday Easter Monday
I had to write this myself using perl and had to go and do this using the full feeds, interestingly enough the full feed has options to expand the recurring events into a single entry, so for instance my Cleaners entry in the agenda above is the same recurring event from the XML earlier. So clearly their backend has the ability to turn the nasty useless recurring event format into something totally human readable and useful, they just chose not to make the default feeds a useful feature.
The calendar has a lot of other questionable features and limitations, it really is a load of compromises, bugs and half baked features cobbled together in something that does not really work as a proper shared calendar environment like a paying customer would expect. And these limitations is not limited to the free versions, they are exactly the same in the paying version. I’d say anyone serious about shared calendar apps or about 3rd party integration should really give GCal and Apps for Domains a miss.
by R.I. Pienaar | Mar 30, 2007 | Front Page
A few of my clients have indicated an interest in moving their mail to Google Apps for Domains, for those living under rocks it’s basically GMail hosted on your own domain. It combines Google Docs (Spreadsheet, Word processor), Calendar, Google Talk and a few other services into one.
So I setup a a test domain a while ago to do a proper evaluation but not against their pay version using the free service. The idea is if the free version is passable but not fatally flawed then the paid version should be fine.
It’s been painful to say the least. First I mistakenly set up a domain that I shouldn’t have since it was already in use and I couldn’t migrate it, my mistake. I figured I’ll be nice and let them know of this and get them to shut it down – a useful exercise since if you do decide to move to them and later want to move away, what you don’t want is your domain in limbo on the Google servers with possible mail routing from inside Google to it and not your real domain.
So first I try and change the primary domain name, can’t, you can add other domains, but it’s impossible to change the primary domain. So I contact them and ask them to shut it down. They contacted me saying no problem, all I need is to send them the PIN code I can get inside the control panel. The thing with these PINs are they keep changing often, so an ex staff member for example can’t claim to be working for you since the PIN won’t be valid anymore, nice feature.
Problem is it takes them longer to respond to support requests than PINs are valid for. So the process goes as follows:
- Ask to close domain
- Get asked for the PIN, supply the PIN
- Support people checks your PIN, it’s not valid anymore because they took too long to check it.
- Get asked for PIN, supply the PIN
- Go back to 3
That’s just pathetic. Combine this with shoddy level of service, there has been several well published extended downtimes in the first month alone and other weird outages. Yesterday for example I couldn’t access any attachments since some automated bot decided I did something wrong and returned a “Lockdown in sector 6!” error, amazingly descriptive that is, needless to say I never heard from support on this issue.
There are other deep rooted issues as well like with the feeds out of calendars etc, but more on that later. I’ll make a few posts here over the next while with my experiences.
by R.I. Pienaar | Feb 27, 2007 | Front Page
I’ve been giving Ubuntu another go on a Desktop. Last time I tried it my intention was to see if it really was a viable switching target from OS X and it turns out for that specific task It sux, this time I was more looking for a desktop fit for a Unix knowledgeable person so have had more tolerance for some stupid things like not being able to play popular content out the box.
Here is a random list of things that was worth noting to me:
- I got Beryl and XGL going on my little Acer 12″ laptop, it’s really nice but unfortunately I can’t get it going on my desktops because they have ATI cards. I will be buying some Intel based cards for these.
- Laptop support in general is really good, it hibernates and everything works. Getting the Acer buttons for things like enabling Wifi was a right pain, I had to tinker with some kernel modules etc.
- Wifi support has a long way to go, the UI for configuring keys, choosing networks and types is totally useless and I hope it’s something on their list to fix real soon
- GAIM has taken a turn for the worst, you should try Kopete for a good IM client
- Changing the behavior of something as simple as the Backspace key in Firefox can really spoil your day
- They push out a silly amount of updates and like with OS X they’re pretty big, I’m really glad I am not on a modem in the 3rd world using Ubuntu or OS X.
-
Easyubuntu is pretty good, Ubuntu needs to take this and put together a paid for package, I’d pay for it.
-
Crossover Office is really good, I’ve been running MS Office 2003 using it and been very happy with the results, MS Word is fast, stable and full featured, blows OpenOffice away, currently on the Demo but will buy soon.
-
Wine isn’t too bad either, it runs Digiguide without a problem, this was one of the big things that kept me on Windows.
- I do sometimes boot into Windows, I do this via VMWare for simple stuff, I’m booting the the physical NTFS partition inside VMWare and its ok, but my AMD 2600 is pretty crap for this, soon I’ll buy a nice Core 2 DUO machine for this and it will solve my speed problems.
So it seems I’ll be moving off a Windows desktop now, I might still get a 2nd Mac instead of the Ubuntu machine that will be the perfect setup for me – 2 x iMac – but till then this will do well enough.
