[PATCH 2/2] [db] Fix queries for unsent SMS

[Sylvain Munaut]

From: Sylvain Munaut <tnt at 246tNt.com>

- Need to use sms.id for the ORDER BY since 'subscriber' also has 'id'
- Need to add the join clause between 'SMS' and 'subscriber'
- Add a LIMIT 1 (probably no impact for the db size we're dealing with
here, but with large DB and mysql/postgresql this can help the planner)
- (fix a wrong comment in passing ...)

Signed-off-by: Sylvain Munaut <tnt at 246tNt.com>
---
 openbsc/src/db.c |    8 +++++---
 1 files changed, 5 insertions(+), 3 deletions(-)

diff --git a/openbsc/src/db.c b/openbsc/src/db.c
index 5dfefb5..ebfe923 100644
--- a/openbsc/src/db.c
+++ b/openbsc/src/db.c
@@ -769,8 +769,9 @@ struct gsm_sms *db_sms_get_unsent(struct gsm_network *net, int min_id)
 	result = dbi_conn_queryf(conn,
 		"SELECT * FROM SMS,Subscriber "
 		"WHERE sms.id >= %llu AND sms.sent is NULL "
+			"AND sms.receiver_id = subscriber.id " 
 			"AND subscriber.lac > 0 "
-		"ORDER BY id",
+		"ORDER BY sms.id LIMIT 1",
 		min_id);
 	if (!result)
 		return NULL;
@@ -787,7 +788,7 @@ struct gsm_sms *db_sms_get_unsent(struct gsm_network *net, int min_id)
 	return sms;
 }
 
-/* retrieve the next unsent SMS with ID >= min_id */
+/* retrieve the next unsent SMS for a given subscriber */
 struct gsm_sms *db_sms_get_unsent_for_subscr(struct gsm_subscriber *subscr)
 {
 	dbi_result result;
@@ -796,8 +797,9 @@ struct gsm_sms *db_sms_get_unsent_for_subscr(struct gsm_subscriber *subscr)
 	result = dbi_conn_queryf(conn,
 		"SELECT * FROM SMS,Subscriber "
 		"WHERE sms.receiver_id = %llu AND sms.sent is NULL "
+			"AND sms.receiver_id = subscriber.id " 
 			"AND subscriber.lac > 0 "
-		"ORDER BY id",
+		"ORDER BY sms.id LIMIT 1",
 		subscr->id);
 	if (!result)
 		return NULL;
-- 
1.6.5.1