Rooted

With the last weeks of my 2 year phone contract coming to and end and my phone long out of warranty I started looking at removing the bloatware from my phone.  The HTC Wildfire S I had was a good budget phone when it was released and I was really impressed when I first got it.  I had plenty of room for new apps, it was fast and the apps on the original image were really useful.  However, as time went by a number of the apps either stopped their services, had large updates or just simply stopped working.  There were also a couple I never used but I couldn't stop from running (stocks app) which would use unnecessary data usage.  After about a year the phone started running out of internal memory, even with most apps partially stored on the SD card.  I was forever installing/uninstalling and wiping user data keep enough space.  The other problem was that some apps required about 25MB on the internal storage to be free even to run efficiently. 

My first stop was to look at removing the apps but soon realised that to do so would mean a new image.  Being the first time I had attempted re imaging a phone I went to look at the big players first and to gauge the most popular custom Roms.  The two main players were AOKP and Cyanogen mod.  Both these groups have unofficial ports for the Wildfire S but I chose to go with the Cyanogen mod purely on popularity (Baa Baa). 

After finding a tutorial on XDA developers net I started to attempt to unlock the bootloader which is needed to install the Clockwork mod recovery image.  Unlocking was fairly simple and after getting a token from the website I flashed to the boot image and hey presto the bootloader was unlocked.  Once the bootloader was unlocked I installed the clockwork mod recovery tool and did a backup of the stock image (very important before proceeding with any custom Roms).  I also did a backup of the SD card and then formatted it for reasons I will explain shortly.

Once the Recovery program is installed its a fairly simple operation to then install items from the SD card.  First of all I installed the superuser application to gain root access to the phone.  This was a simple case of copying the superuser zip onto the root of the SD card  (which has to be formatted) and using the recovery application to install it.  After a quick factory reset of the phone I rebooted and we are all done, bootloader unlocked and phone rooted.

Next step is to install the custom ROM and off we go.  First I copied the Cyanogen Mod 9.1 and the google apps installer onto the SD card.  Then boot into recovery (power + volume down) and install both zips from the SD card. After they are both installed I reboot the phone and waited for the mod to configure itself.  Now, the first boot can take time so it was pretty scary waiting to see if it had installed correctly or whether the phone had just hung and my phone was bricked.  Eventually the mod booted up and a wizard takes you through the setup.  The phone runs really smooth with ICS and there is no lag on any of the screens.  The only obvious problem is that the video function on the camera does not work but to be honest I only ever use it in camera mode so wasn't a big deal. 

So far so good.  However, after a couple of app installs the phones lack of internal memory soon starts to hinder things and performance starts to suffer.  After reading some posts on the forums of similarly disgruntled Wildfire S owners I came across INT2EXT scripts.  Now these are little scripts you can install to extend the internal memory onto the SD card and the best thing is that it is seamless.  I went for INT2EXT4+ which requires an ext4 partition on the SD card which I put on using mini tool.  After the partition is made I place the INT2EXT4+ zip onto the root of the SD card and install the script.  A quick factory reset and wipe of the cache and I reboot the phone.  I have only given the partition 500MB because the script can only address 500MB for every 100MB of internal storage but it seems enough.  Once rebooted the phone reports 500MB of internal storage with 1.5GB on the card.  I reinstall my apps and everything runs just as smooth.  Very impressive.  The SD card is class 4 so a faster card is recommended if you need to run some disk access heavy apps but most of the apps I have been running all run fairly quickly.

Overall, it was a worth while activity as it has resurrected a phone that in all honesty I thought was worthless.  The custom ROM is stable and the extra storage is an absolute luxury.  Its probably not a task to take lightly and some degree of skill is needed (in order to keep the flapping to a minimum) but otherwise the benefits outweigh the risk of bricking your phone, well certainly phones you were looking to replace anyway.  I would recommend having an external micro SD to USB reader or some other way of reading the micro SD card without using the phone as this makes things a lot easier.  The other key is to backup everything and don't even attempt it if the phone is under warranty.  I would also look around and read everything you can before you start.  Choosing an actively developed ROM is also a must as problems can be answered on the forums a lot quicker.

SQL Server and CTE

I recently posted a function to perform string replacement for T-SQL which we were using with a cursor to run through a some look up table and replace any occurences of the lookup text in the destination with the corresponding replacement text. After initial testing we were finding that the cursor was really killing performance so we needed a way to replace the cursor.  All roads led to CTEs (Common Table Expression)  but apart from a small function to create a list of people from a tree structure (recursive CTE), that a colleague had written, I had not seen many CTEs in action.

I had a few examples of recursive CTEs from around the web to build from and so I could easily get a CTE to recusively look through a table of lookups and replace a destination string with the corresponding string. The added complication was that there was actually several lookup tables combined into one lookup table and  first I had to link the look up table to an id in the destination table.  An example of the two tables are shown below:

ItemID	Lookup	Replacement
1	MX	MN
1	MY	CY
1	SB	DG
2	HG	TY
2	HD	OK

Table 1: The lookup table

ItemID	String	Result
1	MX+MY+SB
2	HD/HG

  Table 2: The string table with column for resultant string with replacements
The desired result is to have MN+CY+DG in row 1 column 3 and OK/TY in row 2 column 3.  The first idea was to just let the lookup navigate all the way through table 2 but this may have replaced strings in the result that were not linked to the item (i.e. If row2 of table 2 contained SB) and would be really inefficent as in the real system these tables could potentially contain 1000s of rows.

I Ain't Afraid of No Ghost

I am often "copying" databases from different dev boxes and live boxes by backing up and restoring. However this causes problems with user accounts.

"Ghost" users are created when a database is backed up and restored to a different machine which uses local SQL user accounts. Even if the local SQL user account is recreated, because the underlying ID doesn't match, when the user tries to access the database the message will say the User does not exist. However when you try and add the user you will be told the user already existings. This causes access issues which at first glance seem illogical.

To fix this run the following query against the database:

use <database name>

go 

sp_change_users_login 'auto_fix', '<local login name>'

Conditional Joins

I recently came across a problem where I needed to join a table based on whether the a row in another column contained a flag.  I needed to retrive a date field from either a sale table or order table depending on status of the order.  I needed to join the tables as there was certain other data I need to take into account. 

Unfortunately SQL doesn;t support a case join statement so I went off to serch for a solution. Jeffs blog revealed a good solution involving left joining both tables then using COALESCE to get the relevant date.  I use a where statement on the status table so that I only get the data from the order table when the status is not sold and from the sale table when the item has been sold.

select
  C.CustomerName, coalesce(s.date,o.date) as ActivityDate
from
  Customers C
left outer join 
  Sales S on ...
left outer join
  Orders O on ...
where
  O.Dateis not null OR S.Date is not null

The solution worked well and, as in Jeffs blog, I could have use CASE instead of the COALESCE but I went with COALESCE for neatness.

Arrrgh, I've Deleted my Default Database

I have a local instance of SQL server 2005 on my desktop which hosts a number of databases that I have worked on over the years.  I decided I would have a bit of a clean up and deleted a number of the databases.  Unfortunately, I hadn't updated my main login and deleted the default database.  In SQL server this means that when a login has no default database the login will not be given access to the server object so I could no longer login. 

I couldn't remember my sa password so it was off to google to see what I could do.  Fortunately I found a command to open SSMS using a different DB (master). 

sqlwb -S <login name> -d master -E

Once into SSMS I could then alter the default database of the login to master.

EXEC sp_defaultdb <login name>, 'master'

This altered the login default database and I checked by running

SELECT * FROM master.dbo.syslogins

There it was, default db changed.  Whilst I was there I updated a number of logins that had deleted databases as their master and everything was hunky dory.  I also changed the sa password to something I would remember so that I didn't caught out again.

500 Mile Challenge

At the start of the year I was looking to give myself a small running challenge to motivate myself into increasing my running both in number of runs and length of run. After chatting to some friends I decided to go for 365 miles in a year which was achievable but also allowed for some time off due to holidays and any possible injuries.

I decided to mention it to the guys I regularly go running with (both better runners than myself) and they decided it was a good idea but 365 was a bit too easy. The 365 mile challenge was replaced with the 400 mile challenge. We decided to log this on Endomondo so we could track each others progress but found that someone had already posted a 500 mile challenge. This led to the 365 mile challenge becoming the 500 mile challenge.

It worked out at roughly 9 miles a week which is a mile more than what I usually do after completing two 4 mile runs at lunchtimes. It was good motivation so we upped our normal run to 5 miles so that we would be completing 10 miles a week on average. So far I am just about on track but I had to clock ~70 miles this month to make up for the short fall I had in January due to the birth of my son.

I also bought some new trainers for some extra motivation and finally ditched my 10 year old asics that had about 1000 miles on the clock. I went for the asics fuji es trail shoes as most of the running I do is on tracks in the forest. The shoes are really comfortable and have the usual asics excellent fit and are a lot lighter than my old shoes. 

For trail shoes they are great with good grip but the only gripe I have is that they should really be a lot more waterproof to be true trail shoes. 

T-SQL Replace String

On a recent project I was tasked with writing a simple string replace function for T-SQL.  On the surface it seemed a fairly straightforward task using either PatIndex or charindex.  However, some of the specific requirements proved problematic.  There could be multiple replaces of the same text throughout the string, the replacement text may be the same as the text to find and the word may contain numbers and underscores. 

Using patindex in a loop seemed the best way forward to deal with the multiple replacements and the numbers and underscore characters.  The other problem was that I had to step through the original text to do the replacements so that if a text was replaced with the same text then the function wouldn't get stuck.  I achieved this by using sub string to get the remaining text after each replacement.

@OrigString varchar(8000), 
@LookFor varchar(1000), 
@ReplaceWith varchar(1000))

returns varchar(8000)
BEGIN
DECLARE @findIndex int
DECLARE @lengthLookFor int
DECLARE @lengthReplace int
DECLARE @totalLength int
DECLARE @tempString as varchar(8000)
DECLARE @counter int

SET @OrigString = '(' + @OrigString + ')'

SET @lengthLookFor = LEN(@LookFor)
SET @lengthReplace = LEN(@ReplaceWith)
SET @totalLength = LEN(@OrigString)
SET @tempString = @OrigString
SET @counter = 0

DECLARE @stuffindex int
SET @stuffindex = 0

DECLARE @substring int
SET @substring = 0

WHILE PATINDEX('%' + @LookFor +'[^a-z0-9]%', @tempString) != 0
BEGIN
 SET @findIndex = PATINDEX('%[^a-z0-9]' + @LookFor +'[^a-z0-9]%', @tempString) + 1
--SELECT @findIndex as 'findIndex'

 set @stuffindex =  @findIndex + @substring 
 if(@counter >0)
  set @stuffindex = @stuffindex - 1

 --SELECT @stuffindex as stuffindex

 SET @OrigString = STUFF(@OrigString, @stuffindex , @lengthLookFor, @ReplaceWith)
--SELECT @OrigString as 'outputstring'

SET @substring = @stuffindex + @lengthReplace
--SELECT @substring as substring

SET @tempString = SUBSTRING(@OrigString,@substring,@totalLength - @findIndex)
--SELECT @tempString as 'tempstring'

SET @counter = @counter + 1


END

SET @OrigString = SUBSTRING( @OrigString, 2, LEN(@OrigString)- 2)