Working around WITH not being available in older versions of sqlite.

For one of my iPhone apps I need to rename route points to match their order as they are inserted or deleted, I started with an easy WITH version:

[NSString stringWithFormat:@"WITH wcte (id, wname) AS (SELECT w.id, '%@ ' ||  (SELECT COUNT(*) + 1 FROM waypoint WHERE vY1 < w.vY1) as wname FROM waypoint w)  UPDATE waypoint SET \"name\" = (SELECT wname FROM wcte WHERE id = waypoint.id) WHERE \"name\" LIKE '%@ %%' OR \"name\" is null OR \"name\" = ''", LSSTRING(@"Point"), LSSTRING(@"Point")]

And ran into the problem when testing on iOS8.1, obviously its version of sqlite didn't support WITH at that time.

So here is the workaround solution for older sqlite versions:

[NSString stringWithFormat:@"UPDATE waypoint SET \"name\" = (SELECT '%@ ' || (SELECT COUNT(*) + 1 FROM waypoint w WHERE w.vY1 < w1.vY1) FROM waypoint w1 WHERE w1.id = waypoint.id) WHERE \"name\" LIKE '%@ %%' OR \"name\" is null OR \"name\" = ''", LSSTRING(@"Point"), LSSTRING(@"Point")]

Not that nicely looking, but I'm still committed to support iOS8 for a few more months. If you are puzzled by that LSSTRING part - that's just my macro for the LocalizableString as I only want to rename these points that are named automatically and surely I want to name them in the localized manner. Punto it is in Spanish (I hope) :). vY1 is a cryptic name for the order column :).

Would not be publishing at all, but sqlite syntax sometime is surprising in what it can or can't do, so I thought I might save time to someone.

If you are into hiking, fishing, cycling or classic skiing here is the app link, it's free: https://itunes.apple.com/us/app/id1120906807