too tired to blog

I’ve been working 16 hour days for the past few weeks and it’s just making me too darn tired to blog. I have a big blog post in my head, but my fingers just won’t type the words. So to keep it simple, just go check out Paul’s new ISV. In short I like the idea. Everyone has been spending so much time saying we don’t need anymore CMS’s for that past few years that I think now we actually do.

Also my wife says my posts are full of poor grammar and sentence fragments lately. Hopefully you can all forgive me!

postgresql follow up

Robert has a nice little followup to my “adding PostgreSQL support to HelpSpot” post. He seems to be a PostgreSQL “guy”, which is a good thing for me to know 🙂

To follow up on a few items he mentions:

  1. Yes, I think the fulltext search is very much glossed over in the PostgreSQL world. I don’t know how tSearch compares to MySQL’s but what I do know is that MySQL’s is supper easy to setup. That’s huge for anyone who needs to throw up a simple search. It’s also important for people like me who want to build an application that has a very easy installer, that doesn’t require sending people off to the command line, etc.

  2. Good to know that there aren’t alot of people offering help desk software on PostgreSQL, now you know where to send people who need it!

  3. I believe the page I was looking at about tSearch was the one you linked to. It must have just been taken down. It was last updated Dec 2003, but I figured the updates might just have been taken over as part of the main dev. If that’s the case, then all the more reason it should be integrated in.

helpspot gets postgresql support

Well what can I say, I’m a sucker for customer feedback. Actually, PostgreSQL support has been in my plans from the beginning, but I always envisioned it as more of a V2 feature. This was probably stupid on my part as I’m pretty aware of the database “camps” out there, but I was hoping to get by. However, after several emails from potential customers asking about alternative database support (thanks Bob. Tim, and Mike!) I decided it was better to do the porting now and get it over with. I wouldn’t feel comfortable adding support for a new database without a full beta cycle on it and that won’t come again until V2 so it seemed best to do it now, hence the reason I’ve been very silent here the past few days.

First off, this would not even have been possible if I wasn’t already using the super fantastic ADODB abstraction library. It’s just amazing how much functionality is in that package. However, there were still a few bumps, which I’ll point out here since there’s not much out there about this stuff. At least, I found very few weblog entries or articles which discussed all of the issues someone might face when porting a MySQL codebase to PostgreSQL. Hopefully this helps someone in the future.

1. Automatic case folding
Ah, this was a big one. HelpSpot’s database was written in my own offshoot of Hungarian Notation, meaning the names of tables and fields are camel case with a leading indicator as to the data type of the field. Not a big deal I thought. Having developed for years on MySQL and MS SQL Server this always worked fine. Ah my friends not in the PostgreSQL world.

See the issue is that I always write my queries something like this:
SELECT xProduct, sThing, sOtherThing
FROM HS_ThingTable
WHERE HS_ThingTable.xBlah = 8

No problem under normal conditions. However in PostgreSQL all tables and fields are case folded to lowercase automatically unless surrounded by quotes! So the above query becomes:
SELECT xproduct, sthing, sotherthing
FROM hs_thingtable
WHERE hs_thingtable.xblah = 8

So PostgreSQL ends up looking for table hs_thingtable when it needs to be looking for HS_ThingTable hence it can’t find it and the query fails. To force PostgreSQL to obey you need to make the query look like this:
SELECT “xProduct”, “sThing”,” sOtherThing”
FROM “HS_ThingTable”
WHERE “HS_ThingTable”.”xBlah” = 8

Not a big deal you say, just put the quotes in. Ah, that works great for PostgreSQL and MS SQL Server since the quotes are part of the SQL standard, but those crazy guys over at MySQL decided that they would rather use `back ticks` instead so when you try the query with quotes it fails in MySQL 🙁

So the other alternative is to make everything lowercase, but I refused to do that because I find it to be much less readable. Also it would be a truly huge amount of work, because internally I reference the recordsets by associative array so there’s things like this all over the place: $thingy[‘sThing’] I’m not about to go and change tens of thousands of references a few weeks before the beta.

Luckily ADODB to the rescue! Using the very cool fnExecute variable I was able to define a function which runs only if PostgreSQL is the current databse type. In that case I do some lightweight query rewriting to add in the necessary quotes. It works perfect and after some quick checks to make sure performance wasn’t hurt I was satisfied with the solution and able to both keep my camel case and continue the port.

On a side note, MySQL since sometime in V4 does have an option to set the quote character back to ANSI standard, but since I want to support pre 4 databases that wasn’t an option. Also, I believe it’s something that must be configured in MySQL which also wasn’t acceptable to me, since it’s sure to be the type of thing which causes many people headaches during installation.

aside 1: PostgreSQL documentation is terrible, I mean really bad on their site. The search is a total joke. Hence I wouldn’t be surprised if someone posts here that all I had to do was X and PostgreSQL would honor the camel case. If so I’d love to know the trick.

aside 2: Damn I love having transactions!!! That MySQL gets away without them is amazing, it actually costs me a alot of time that the MyISAM table type doesn’t support them because I have to do more checks to try and maintain at least some consistency in case of a failure between inserts/updates/deletes. Yes I know about the InnoDB type, no I won’t use it. To much overhead for the customer to know about plus the loss of fulltext searching. This is another nice thing about ADODB. I can put all the ADODB transaction code in there and it works for supported databases and is ignored in DB’s that don’t have transcations like MySQL.

2. Last Insert ID
You insert into one table, get the primary key ID of that inserted row and use that in another query on a related table. Happens all the time. A little bit of a pain on PostgreSQL. The good news is that ADODB has a undocumented Postgres function called pg_insert_id which handles it, the only thing is you have to pass it the table name and field name so it can look at the sequence ID and return that value so you’ll need to change all your Insert_ID references to the other function when using PostgreSQL.

3. Fulltext search
Full text search won’t be supported in HelpSpot V1. Unlike the other platforms the fulltext engine is an addon and must be built. As such, it functions very differently than the internal fulltext support of MySQL and SQL Server. I gave it a quick go, but could see that it was going to be a challenge so this will have to wait. I also didn’t really like that the open source project that built the engine seems to have had it’s last update in 2003. Perhaps the Postgres people are working on a built in solution????

4. Empty quotes for numeric field
This was the last big hurdle. In several spots of code the INSERT SQL generated for numeric fields simply passed an empty string. MySQL handles this by inserting the tables default value for that field, PostgreSQL throws an error. So I went through and fixed that. It’s actually much better this way from a security standpoint anyway and I really should have been doing this all along in every spot.

Whew, so overall it really wasn’t too bad. About a day or so of work and I think the changes I made actually helped HelpSpot become a better product. I now also have great coverage by being able to support 3 great databases at least one of which should be acceptable in most IT shops.

giving up on the isv dream

Jay talks about calling it quits but then steps back from the ledge.

My advice is that if you’re really serious about doing it then the best thing you can do is focus on getting in a financial place to leave your day job. If you’re building a small app then maybe this advice isn’t for you, but any major size project is going to take all of your time to get right and doing it between work, wife, kids along with trying to stay on top of technology plus having any time for yourself is simply impossible. You’d be much better off focusing your energy on how you can save money, what corners you can cut, what side gigs you can take in order to get to a financial place to devote yourself entirely to the business.

why dont geeks get rss

Everyone is always trying to replace RSS. I don’t think it’s perfect, but it works and it’s been widely adopted so why can’ t we just run with it? Why not use the namespace support if you want to extend it? Why must geeks invent new “better” versions? Why must they do it in such a way as to confuse the average person who may have some understanding? Case in point:


If you are going to try and replace a de-facto standard like RSS 2.0 then why not at least try and make it simple? Instead, RSS 3 spec states (seemingly proudly) that it encompasses 4 wholly different parts: RSS 3 Lite, RSS 3 Full, RCDL and RRDL

Come on man! People get RSS because it’s simple. Trying to replace it with one spec that describes 4 totally different things isn’t going to work.

And must you go and name it version 3 as if it supersedes version 2 when it’s unlikely to ever do so? All that does is confuse people. At least give it a new name. Calling it RSS is just a blatant ploy to get some press on slashdot.

blog about the process

I thought I’d publish this email just for fun. I’ve been meaning to post something about this, but why rewrite it when I can just copy and paste! It’s republished with Humberto’s very gracious permission. I’m looking forward to following his product. Another reader of this blog going forth into the ISV world!

On Aug 19, 2005, at 2:51 PM, Humberto Oliveira wrote:


First of all I would like to say that I really enjoy your blog and your expertise in marketing your product. Like yourself, I am starting a micro ISV venture with a friend of mine and I was hoping you could give me some tips about using a blog to advertise my product. Did you already have the name and maybe a rough spec of your product when you started blogging? What is the best moment to begin blogging? If I start to early isn’t it going to sound like vaporware?

Here in Brazil we don’t have as much people using blogs as a marketing tool and really believe in the power of this tool. Our product is a mass mailer web application, and our target customers are small companies and freelancers webdesigners.


Humberto Oliveira


Hi Humberto,

Thanks for the email. You may want to check out an article I wrote that goes through some of this:

As for your questions, I started blogging a few months before I actually started production of HelpSpot. During that time I mostly just got my feet wet and talked about technology and so on. Then I think it was about February when I really got started spec’ing my business and product and I started talking about it on the blog.

I don’t think it’s really ever too early to start. The thing I’ve found is that you need to focus on the ‘business’ your starting and not entirely on the product. If all your talking about is a product that’s 6 months away, that’s boring. If, however, you talk about the process you’re going through and talk about the product in terms of the process then that’s interesting. You’ll be able to build your reader base that way and it’s great to have a reader base before the product is done because your readers can give you some great ideas. I know mine have.

Try and be as transparent as you can be, that’s how people learn from you and it’s also what gives them enough knowledge about your organization to be able to effectively help you when you ask. This is really hard, because in most businesses (all I’ve worked in) they want to keep all the knowledge inside. If you’re doing it right then you should have at least one post every few weeks where you just stare at it and wonder if you should click the publish button.

In general, I would just stay away from a blog where every post is just directly about the product. Readers aren’t as interested in your product as much as interested in you and the process you’re going through.


google sanctions theft of content

As I mentioned in a post the other day I contacted Google about a search engine spammer who is stealing my posts and using them to wrap around Google ads. There’s no attribution back to me and it is an obviously automated spammer. Here’s there response:

“Hi Ian,

Thank you for your note. Google aggregates and organizes information
published on the web; we don’t control the content of these webpages. If
you’re concerned about the content on a particular page in our search
results, we suggest that you directly address the webmaster of the page in
question. To learn how to contact the webmaster of a site, please visit For more
information about our Terms of Service, please visit

The Google Team”

I find this unacceptable. I would even prefer a “hey we looked at it and think it’s OK”, but this is obviously a canned response. Most likely nobody even looked at the site because the response seems to be suggesting my problem is with something I found in a search. My biggest problem with it is that it’s basically sanctioning the theft of my content. Saying we don’t control it so there’s nothing we can do is insane when the only reason these people are stealing the content is to make money from Google Ads.

So Google how about turning off the Ads? How about when a copyright holder comes to you with an issue you at least have a human look at it? You’ve got $3.1 Billion in current assets, how about hiring a $10/hour human to review your emails?

helpspot beta announcement

I sent this email out to the mailing list a few minutes ago. Republished here for your RSS reading pleasure:

We thought you should be among the first to know …..

HelpSpot is finally here!

After over a year of planning and development, we’re now ready
to pull back the curtain to unveil HelpSpot.

Beginning Monday, September 12, a beta version of HelpSpot will
be available for those interested in being among the first to
review and provide feedback. The beta trial period will run
September 12 – October 14. Below are the requirements to run
this beta version, but the most important requirement is a
willingness to provide us your feedback!

In order to run HelpSpot you will need:
* PHP 4.3+ or 5 (runs great with Register Globals OFF)
* MySQL 3.23.X, MySQL 4.0.X, or Microsoft SQL Server 2000
* Apache or IIS
* Windows XP/2003 Server, Linux/Unix, or Mac OSX

As a thank you to the folks that actively participate during
the beta trial period, we’re offering a 50% license discount
should you choose to continue using HelpSpot beyond the beta.

Interested in participating?
Getting involved is simple. As a member of this mailing list
you are already in the loop. Simply wait for the email we will
be sending on September 12 that contains all of the necessary
download and installation information.

Please feel free to send any questions on this beta trail period
to [email protected]


Ian Landsman
President, UserScape