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.