At first I thought it should be a challenge, though I did not expect it to be too tough, since we were already using a custom db wrapper. Well it turned out to be well too heavy a nightmare. And there was situations where I even considered retiring from life. I pity those who are paying thousands of hard earned money to buy such filthy crap.
- No limit x,y to handle server side pagination.. and that is handled using row_count, and conditional subquery
- Cant’ alter a table to add an auto incrementing primary key, bull shit..
- Concat and Group Concat, convoluted and making people write more code, wasting more clock cycles and computing power
- Coaxing the camel to pass through a needle hole is easier than cooking up a create table statement programatically
- General concepts have been screwed up such that if one accidentally learns, then he is doomed and caught in the web. Just like the spider catches its prey
I got frustrated at the implementations that I thought it would be better to forget stored procedures altogether, since I had to maintain both versions of the software. In fact I am maintaining three, one each in MySQL, SQLite3 and MS-SQL, so felt it such that implementing the procedures at the php level was better for me. And this could be achieved by intercepting the query call in my wrapper through a preg_match and invoke the appropriate wrapper method. The technique was implemented so as not to affect most of the coding which was already done in MySQL.
The query parser would manipulate the queries with ‘ limit x,y ‘ as ‘select top y [rest of query]’ in case x=0, and use the row_count method other wise. We were already using a dozen of the functions, so these were rewritten for the MS-SQL crap. It was where we were generating a load of JSON using concat and group_concat that we got the real nightmare. The concat could be replaced in query while running with a neatly crafted find and replace array through preg_replace. But for group_concat, we simply had to move it out, so those areas were identified in the whole code base, and dual methods are added. This makes sure that when the system uses MySQL, the more efficient concat, group_concat will be used for the dynamic bulid of JSON, where as with MS-SQL, the JSON will be built using php iteration through all the tables.
My advice to all aspiring young ones, dont take the challenge to convert or migrate applications from one system to another, rewrite the whole thing, get a team to maintain the different versions, and the crappy corporates.. find a way to release yourself from the clutches of a resource sucking money machine, or atleast stay away from middle men who will make you sign one sided agreements and jail you in the constraints.