I've been working on an iPhone app that is supposed to be the implementation of what is to be in my dissertation, and I hit the limitations of Sqlite on IOS. The problem is easy enough to explain. There is a list of preconditions a character/player has to match before a project can be chosen.
The preconditions a key value based, say "assertiveness, 65" (meaning the players value must be higher than 65 for this project). Now if this is the data:
preconditions player charactericstics
Id | key | value key | value
1 | A | 30 A | 45
1 | B | 54 C | 42
2 | A | 65
Then it's clear that project 1 matches.
I first thought to do this by generating a nice SQL query in objective-c and fire it off to Sqlite:
((SELECT * FROM preconditions WHERE key = A and value <= 45) UNION
(SELECT * FROM preconditions WHERE key <> A EXCEPT
SELECT * FROM preconditions WHERE key = A AND value > 45)
) INTERSECT (
(SELECT * FROM preconditions WHERE key = C and value <= 42) UNION
(SELECT * FROM preconditions WHERE key <> C EXCEPT
SELECT * FROM table WHERE key = C AND value > 42))
It's very nice to use the set operations UNION and INTERSECT, because all duplicates are removed as wel. But guess what? Sqlite does not support parentheses around the operands of the set operator. You're only allowed to use them around subqueries. So much for this query, precendence is important and I need those parentheses.
My next choice was to load the preconditions in memory (player characteristics are already in memory), loop them, and in each loop purge the projectid that is violating a current constraint. In objective-c:
// loop over player characteristics
for (NSString *keyc in characteristics) {
NSNumber *valc = [characteristics objectForKey:keyc];
//loop over preconditions
for (IdKeyValue *p in preconditions){
if ([keyc isEqualToString: p.key] && ([valc intValue] < p.value)) {
//remove all p.oid from preconditions
for (IdKeyValue *t in preconditions){
if (t.oid == p.oid) [preconditions removeObject:t];
}
}
}
}
The problem is that in the most inner loop, I'm modifying the most outer loop. It turns out that is not a particularly good idea on IOS:
Terminating app due to uncaught exception 'NSGenericException', reason: '*** Collection <__NSArrayM: 0x4b80700> was mutated while being enumerated.(
The easy, but not elegant, solution would be to just list all possible keys when creating the preconditions, and for each project insert value "0" for those key's not mentioned for that project.
The data would look like this:
preconditions player charactericstics
Id | key | value key | value
1 | A | 30 A | 45
1 | B | 54 C | 42
1 | C | 0
2 | A | 65
2 | B | 0
2 | C | 0
And the SQL query would be:
SELECT * FROM preconditions WHERE (key = A AND value <=45) INTERSECT
SELECT * FROM preconditions WHERE (key = C AND value <=42)
I think this should work, but it doesn't feel good.
