Use libvlr in libmsc (large refactoring)

Original libvlr code is by Harald Welte <laforge@gnumonks.org>,
polished and tweaked by Neels Hofmeyr <nhofmeyr@sysmocom.de>.

This is a long series of trial-and-error development collapsed in one patch.
This may be split in smaller commits if reviewers prefer that. If we can keep
it as one, we have saved ourselves the additional separation work.

SMS:

The SQL based lookup of SMS for attached subscribers no longer works since the
SQL database no longer has the subscriber data. Replace with a round-robin on
the SMS recipient MSISDNs paired with a VLR subscriber RAM lookup whether the
subscriber is currently attached.

If there are many SMS for not-attached subscribers in the SMS database, this
will become inefficient: a DB hit returns a pending SMS, the RAM lookup will
reveal that the subscriber is not attached, after which the DB is hit for the
next SMS. It would become more efficient e.g. by having an MSISDN based hash
list for the VLR subscribers and by marking non-attached SMS recipients in the
SMS database so that they can be excluded with the SQL query already.

There is a sanity limit to do at most 100 db hits per attempt to find a pending
SMS. So if there are more than 100 stored SMS waiting for their recipients to
actually attach to the MSC, it may take more than one SMS queue trigger to
deliver SMS for subscribers that are actually attached.

This is not very beautiful, but is merely intended to carry us over to a time
when we have a proper separate SMSC entity.

Introduce gsm_subscriber_connection ref-counting in libmsc.

Remove/Disable VTY and CTRL commands to create subscribers, which is now a task
of the OsmoHLR. Adjust the python tests accordingly.

Remove VTY cmd subscriber-keep-in-ram.

Use OSMO_GSUP_PORT = 4222 instead of 2222. See
I4222e21686c823985be8ff1f16b1182be8ad6175.

So far use the LAC from conn->bts, will be replaced by conn->lac in
Id3705236350d5f69e447046b0a764bbabc3d493c.

Related: OS#1592 OS#1974
Change-Id: I639544a6cdda77a3aafc4e3446a55393f60e4050

1 files changed, 62 insertions, 963 deletions

diff --git a/src/libmsc/db.c b/src/libmsc/db.c
index 5fe2a3c6b..28e978213 100644
--- a/src/libmsc/db.c
+++ b/src/libmsc/db.c
@@ -34,6 +34,7 @@
 #include <openbsc/gsm_04_11.h>
 #include <openbsc/db.h>
 #include <openbsc/debug.h>
+#include <openbsc/vlr.h>
 
 #include <osmocom/gsm/protocol/gsm_23_003.h>
 #include <osmocom/core/talloc.h>
@@ -43,9 +44,6 @@
 
 #include <openssl/rand.h>
 
-/* Semi-Private-Interface (SPI) for the subscriber code */
-void subscr_direct_free(struct gsm_subscriber *subscr);
-
 static char *db_basename = NULL;
 static char *db_dirname = NULL;
 static dbi_conn conn;
@@ -227,23 +225,33 @@ static struct gsm_sms *sms_from_result_v3(dbi_result result)
 {
 	struct gsm_sms *sms = sms_alloc();
 	long long unsigned int sender_id;
-	struct gsm_subscriber *sender;
 	const char *text, *daddr;
 	const unsigned char *user_data;
 	char buf[32];
+	char *quoted;
+	dbi_result result2;
+	const char *extension;
 
 	if (!sms)
 		return NULL;
 
 	sms->id = dbi_result_get_ulonglong(result, "id");
 
+	/* find extension by id, assuming that the subscriber still exists in
+	 * the db */
 	sender_id = dbi_result_get_ulonglong(result, "sender_id");
 	snprintf(buf, sizeof(buf), "%llu", sender_id);
-	sender = db_get_subscriber(GSM_SUBSCRIBER_ID, buf);
-	OSMO_ASSERT(sender);
-	osmo_strlcpy(sms->src.addr, sender->extension, sizeof(sms->src.addr));
-	subscr_direct_free(sender);
-	sender = NULL;
+
+	dbi_conn_quote_string_copy(conn, buf, &quoted);
+	result2 = dbi_conn_queryf(conn,
+				  "SELECT extension FROM Subscriber "
+				  "WHERE id = %s ", quoted);
+	free(quoted);
+	extension = dbi_result_get_string(result2, "extension");
+	if (extension)
+		osmo_strlcpy(sms->src.addr, extension, sizeof(sms->src.addr));
+	dbi_result_free(result2);
+	/* got the extension */
 
 	sms->reply_path_req = dbi_result_get_ulonglong(result, "reply_path_req");
 	sms->status_rep_req = dbi_result_get_ulonglong(result, "status_rep_req");
@@ -518,913 +526,6 @@ int db_fini(void)
 	return 0;
 }
 
-struct gsm_subscriber *db_create_subscriber(const char *imsi, uint64_t smin,
-					    uint64_t smax, bool alloc_exten)
-{
-	dbi_result result;
-	struct gsm_subscriber *subscr;
-
-	/* Is this subscriber known in the db? */
-	subscr = db_get_subscriber(GSM_SUBSCRIBER_IMSI, imsi);
-	if (subscr) {
-		subscr_put(subscr);
-		return NULL;
-	}
-
-	subscr = subscr_alloc();
-	if (!subscr)
-		return NULL;
-	subscr->flags |= GSM_SUBSCRIBER_FIRST_CONTACT;
-	result = dbi_conn_queryf(conn,
-		"INSERT INTO Subscriber "
-		"(imsi, created, updated) "
-		"VALUES "
-		"(%s, datetime('now'), datetime('now')) ",
-		imsi
-	);
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR, "Failed to create Subscriber by IMSI.\n");
-		subscr_put(subscr);
-		return NULL;
-	}
-	subscr->id = dbi_conn_sequence_last(conn, NULL);
-	osmo_strlcpy(subscr->imsi, imsi, sizeof(subscr->imsi));
-	dbi_result_free(result);
-	LOGP(DDB, LOGL_INFO, "New Subscriber: ID %llu, IMSI %s\n", subscr->id, subscr->imsi);
-	if (alloc_exten)
-		db_subscriber_alloc_exten(subscr, smin, smax);
-	return subscr;
-}
-
-osmo_static_assert(sizeof(unsigned char) == sizeof(struct gsm48_classmark1), classmark1_size);
-
-static int get_equipment_by_subscr(struct gsm_subscriber *subscr)
-{
-	dbi_result result;
-	const char *string;
-	unsigned char cm1;
-	const unsigned char *cm2, *cm3;
-	struct gsm_equipment *equip = &subscr->equipment;
-
-	result = dbi_conn_queryf(conn,
-		"SELECT Equipment.* "
-			"FROM Equipment JOIN EquipmentWatch ON "
-				"EquipmentWatch.equipment_id=Equipment.id "
-			"WHERE EquipmentWatch.subscriber_id = %llu "
-			"ORDER BY EquipmentWatch.updated DESC", subscr->id);
-	if (!result)
-		return -EIO;
-
-	if (!dbi_result_next_row(result)) {
-		dbi_result_free(result);
-		return -ENOENT;
-	}
-
-	equip->id = dbi_result_get_ulonglong(result, "id");
-
-	string = dbi_result_get_string(result, "imei");
-	if (string)
-		osmo_strlcpy(equip->imei, string, sizeof(equip->imei));
-
-	string = dbi_result_get_string(result, "classmark1");
-	if (string) {
-		cm1 = atoi(string) & 0xff;
-		memcpy(&equip->classmark1, &cm1, sizeof(equip->classmark1));
-	}
-
-	equip->classmark2_len = dbi_result_get_field_length(result, "classmark2");
-	cm2 = dbi_result_get_binary(result, "classmark2");
-	if (equip->classmark2_len > sizeof(equip->classmark2))
-		equip->classmark2_len = sizeof(equip->classmark2);
-	if (cm2)
-		memcpy(equip->classmark2, cm2, equip->classmark2_len);
-
-	equip->classmark3_len = dbi_result_get_field_length(result, "classmark3");
-	cm3 = dbi_result_get_binary(result, "classmark3");
-	if (equip->classmark3_len > sizeof(equip->classmark3))
-		equip->classmark3_len = sizeof(equip->classmark3);
-	if (cm3)
-		memcpy(equip->classmark3, cm3, equip->classmark3_len);
-
-	dbi_result_free(result);
-
-	return 0;
-}
-
-int db_get_authinfo_for_subscr(struct gsm_auth_info *ainfo,
-                               struct gsm_subscriber *subscr)
-{
-	dbi_result result;
-	const unsigned char *a3a8_ki;
-
-	result = dbi_conn_queryf(conn,
-			"SELECT * FROM AuthKeys WHERE subscriber_id=%llu",
-			 subscr->id);
-	if (!result)
-		return -EIO;
-
-	if (!dbi_result_next_row(result)) {
-		dbi_result_free(result);
-		return -ENOENT;
-	}
-
-	ainfo->auth_algo = dbi_result_get_ulonglong(result, "algorithm_id");
-	ainfo->a3a8_ki_len = dbi_result_get_field_length(result, "a3a8_ki");
-	a3a8_ki = dbi_result_get_binary(result, "a3a8_ki");
-	if (ainfo->a3a8_ki_len > sizeof(ainfo->a3a8_ki))
-		ainfo->a3a8_ki_len = sizeof(ainfo->a3a8_ki);
-	memcpy(ainfo->a3a8_ki, a3a8_ki, ainfo->a3a8_ki_len);
-
-	dbi_result_free(result);
-
-	return 0;
-}
-
-int db_sync_authinfo_for_subscr(struct gsm_auth_info *ainfo,
-                                struct gsm_subscriber *subscr)
-{
-	dbi_result result;
-	struct gsm_auth_info ainfo_old;
-	int rc, upd;
-	unsigned char *ki_str;
-
-	/* Deletion ? */
-	if (ainfo == NULL) {
-		result = dbi_conn_queryf(conn,
-			"DELETE FROM AuthKeys WHERE subscriber_id=%llu",
-			subscr->id);
-
-		if (!result)
-			return -EIO;
-
-		dbi_result_free(result);
-
-		return 0;
-	}
-
-	/* Check if already existing */
-	rc = db_get_authinfo_for_subscr(&ainfo_old, subscr);
-	if (rc && rc != -ENOENT)
-		return rc;
-	upd = rc ? 0 : 1;
-
-	/* Update / Insert */
-	dbi_conn_quote_binary_copy(conn,
-		ainfo->a3a8_ki, ainfo->a3a8_ki_len, &ki_str);
-
-	if (!upd) {
-		result = dbi_conn_queryf(conn,
-				"INSERT INTO AuthKeys "
-				"(subscriber_id, algorithm_id, a3a8_ki) "
-				"VALUES (%llu, %u, %s)",
-				subscr->id, ainfo->auth_algo, ki_str);
-	} else {
-		result = dbi_conn_queryf(conn,
-				"UPDATE AuthKeys "
-				"SET algorithm_id=%u, a3a8_ki=%s "
-				"WHERE subscriber_id=%llu",
-				ainfo->auth_algo, ki_str, subscr->id);
-	}
-
-	free(ki_str);
-
-	if (!result)
-		return -EIO;
-
-	dbi_result_free(result);
-
-	return 0;
-}
-
-int db_get_lastauthtuple_for_subscr(struct gsm_auth_tuple *atuple,
-                                    struct gsm_subscriber *subscr)
-{
-	dbi_result result;
-	int len;
-	const unsigned char *blob;
-
-	result = dbi_conn_queryf(conn,
-			"SELECT * FROM AuthLastTuples WHERE subscriber_id=%llu",
-			subscr->id);
-	if (!result)
-		return -EIO;
-
-	if (!dbi_result_next_row(result)) {
-		dbi_result_free(result);
-		return -ENOENT;
-	}
-
-	memset(atuple, 0, sizeof(*atuple));
-
-	atuple->use_count = dbi_result_get_ulonglong(result, "use_count");
-	atuple->key_seq = dbi_result_get_ulonglong(result, "key_seq");
-
-	len = dbi_result_get_field_length(result, "rand");
-	if (len != sizeof(atuple->vec.rand))
-		goto err_size;
-
-	blob = dbi_result_get_binary(result, "rand");
-	memcpy(atuple->vec.rand, blob, len);
-
-	len = dbi_result_get_field_length(result, "sres");
-	if (len != sizeof(atuple->vec.sres))
-		goto err_size;
-
-	blob = dbi_result_get_binary(result, "sres");
-	memcpy(atuple->vec.sres, blob, len);
-
-	len = dbi_result_get_field_length(result, "kc");
-	if (len != sizeof(atuple->vec.kc))
-		goto err_size;
-
-	blob = dbi_result_get_binary(result, "kc");
-	memcpy(atuple->vec.kc, blob, len);
-
-	dbi_result_free(result);
-
-	return 0;
-
-err_size:
-	dbi_result_free(result);
-	return -EIO;
-}
-
-int db_sync_lastauthtuple_for_subscr(struct gsm_auth_tuple *atuple,
-                                     struct gsm_subscriber *subscr)
-{
-	dbi_result result;
-	int rc, upd;
-	struct gsm_auth_tuple atuple_old;
-	unsigned char *rand_str, *sres_str, *kc_str;
-
-	/* Deletion ? */
-	if (atuple == NULL) {
-		result = dbi_conn_queryf(conn,
-			"DELETE FROM AuthLastTuples WHERE subscriber_id=%llu",
-			subscr->id);
-
-		if (!result)
-			return -EIO;
-
-		dbi_result_free(result);
-
-		return 0;
-	}
-
-	/* Check if already existing */
-	rc = db_get_lastauthtuple_for_subscr(&atuple_old, subscr);
-	if (rc && rc != -ENOENT)
-		return rc;
-	upd = rc ? 0 : 1;
-
-	/* Update / Insert */
-	dbi_conn_quote_binary_copy(conn,
-		atuple->vec.rand, sizeof(atuple->vec.rand), &rand_str);
-	dbi_conn_quote_binary_copy(conn,
-		atuple->vec.sres, sizeof(atuple->vec.sres), &sres_str);
-	dbi_conn_quote_binary_copy(conn,
-		atuple->vec.kc, sizeof(atuple->vec.kc), &kc_str);
-
-	if (!upd) {
-		result = dbi_conn_queryf(conn,
-				"INSERT INTO AuthLastTuples "
-				"(subscriber_id, issued, use_count, "
-				 "key_seq, rand, sres, kc) "
-				"VALUES (%llu, datetime('now'), %u, "
-				 "%u, %s, %s, %s ) ",
-				subscr->id, atuple->use_count, atuple->key_seq,
-				rand_str, sres_str, kc_str);
-	} else {
-		char *issued = atuple->key_seq == atuple_old.key_seq ?
-					"issued" : "datetime('now')";
-		result = dbi_conn_queryf(conn,
-				"UPDATE AuthLastTuples "
-				"SET issued=%s, use_count=%u, "
-				 "key_seq=%u, rand=%s, sres=%s, kc=%s "
-				"WHERE subscriber_id = %llu",
-				issued, atuple->use_count, atuple->key_seq,
-				rand_str, sres_str, kc_str, subscr->id);
-	}
-
-	free(rand_str);
-	free(sres_str);
-	free(kc_str);
-
-	if (!result)
-		return -EIO;
-
-	dbi_result_free(result);
-
-	return 0;
-}
-
-static void db_set_from_query(struct gsm_subscriber *subscr, dbi_conn result)
-{
-	const char *string;
-	string = dbi_result_get_string(result, "imsi");
-	if (string)
-		osmo_strlcpy(subscr->imsi, string, sizeof(subscr->imsi));
-
-	string = dbi_result_get_string(result, "tmsi");
-	if (string)
-		subscr->tmsi = tmsi_from_string(string);
-
-	string = dbi_result_get_string(result, "name");
-	if (string)
-		osmo_strlcpy(subscr->name, string, sizeof(subscr->name));
-
-	string = dbi_result_get_string(result, "extension");
-	if (string)
-		osmo_strlcpy(subscr->extension, string, sizeof(subscr->extension));
-
-	subscr->lac = dbi_result_get_ulonglong(result, "lac");
-
-	if (!dbi_result_field_is_null(result, "expire_lu"))
-		subscr->expire_lu = dbi_result_get_datetime(result, "expire_lu");
-	else
-		subscr->expire_lu = GSM_SUBSCRIBER_NO_EXPIRATION;
-
-	subscr->authorized = dbi_result_get_ulonglong(result, "authorized");
-
-}
-
-#define BASE_QUERY "SELECT * FROM Subscriber "
-struct gsm_subscriber *db_get_subscriber(enum gsm_subscriber_field field,
-					 const char *id)
-{
-	dbi_result result;
-	char *quoted;
-	struct gsm_subscriber *subscr;
-
-	switch (field) {
-	case GSM_SUBSCRIBER_IMSI:
-		dbi_conn_quote_string_copy(conn, id, &quoted);
-		result = dbi_conn_queryf(conn,
-			BASE_QUERY
-			"WHERE imsi = %s ",
-			quoted
-		);
-		free(quoted);
-		break;
-	case GSM_SUBSCRIBER_TMSI:
-		dbi_conn_quote_string_copy(conn, id, &quoted);
-		result = dbi_conn_queryf(conn,
-			BASE_QUERY
-			"WHERE tmsi = %s ",
-			quoted
-		);
-		free(quoted);
-		break;
-	case GSM_SUBSCRIBER_EXTENSION:
-		dbi_conn_quote_string_copy(conn, id, &quoted);
-		result = dbi_conn_queryf(conn,
-			BASE_QUERY
-			"WHERE extension = %s ",
-			quoted
-		);
-		free(quoted);
-		break;
-	case GSM_SUBSCRIBER_ID:
-		dbi_conn_quote_string_copy(conn, id, &quoted);
-		result = dbi_conn_queryf(conn,
-			BASE_QUERY
-			"WHERE id = %s ", quoted);
-		free(quoted);
-		break;
-	default:
-		LOGP(DDB, LOGL_NOTICE, "Unknown query selector for Subscriber.\n");
-		return NULL;
-	}
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR, "Failed to query Subscriber.\n");
-		return NULL;
-	}
-	if (!dbi_result_next_row(result)) {
-		DEBUGP(DDB, "Failed to find the Subscriber. '%u' '%s'\n",
-			field, id);
-		dbi_result_free(result);
-		return NULL;
-	}
-
-	subscr = subscr_alloc();
-	subscr->id = dbi_result_get_ulonglong(result, "id");
-
-	db_set_from_query(subscr, result);
-	DEBUGP(DDB, "Found Subscriber: ID %llu, IMSI %s, NAME '%s', TMSI %x, EXTEN '%s', LAC %hu, AUTH %u\n",
-		subscr->id, subscr->imsi, subscr->name, subscr->tmsi, subscr->extension,
-		subscr->lac, subscr->authorized);
-	dbi_result_free(result);
-
-	get_equipment_by_subscr(subscr);
-
-	return subscr;
-}
-
-int db_subscriber_update(struct gsm_subscriber *subscr)
-{
-	char buf[32];
-	dbi_result result;
-
-	/* Copy the id to a string as queryf with %llu is failing */
-	sprintf(buf, "%llu", subscr->id);
-	result = dbi_conn_queryf(conn,
-			BASE_QUERY
-			"WHERE id = %s", buf);
-
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR, "Failed to query Subscriber: %llu\n", subscr->id);
-		return -EIO;
-	}
-	if (!dbi_result_next_row(result)) {
-		DEBUGP(DDB, "Failed to find the Subscriber. %llu\n",
-			subscr->id);
-		dbi_result_free(result);
-		return -EIO;
-	}
-
-	db_set_from_query(subscr, result);
-	dbi_result_free(result);
-	get_equipment_by_subscr(subscr);
-
-	return 0;
-}
-
-int db_sync_subscriber(struct gsm_subscriber *subscriber)
-{
-	dbi_result result;
-	char tmsi[14];
-	char *q_tmsi, *q_name, *q_extension;
-
-	dbi_conn_quote_string_copy(conn, 
-				   subscriber->name, &q_name);
-	if (subscriber->extension[0] != '\0')
-		dbi_conn_quote_string_copy(conn,
-					   subscriber->extension, &q_extension);
-	else
-		q_extension = strdup("NULL");
-	
-	if (subscriber->tmsi != GSM_RESERVED_TMSI) {
-		sprintf(tmsi, "%u", subscriber->tmsi);
-		dbi_conn_quote_string_copy(conn,
-				   tmsi,
-				   &q_tmsi);
-	} else
-		q_tmsi = strdup("NULL");
-
-	if (subscriber->expire_lu == GSM_SUBSCRIBER_NO_EXPIRATION) {
-		result = dbi_conn_queryf(conn,
-			"UPDATE Subscriber "
-			"SET updated = datetime('now'), "
-			"name = %s, "
-			"extension = %s, "
-			"authorized = %i, "
-			"tmsi = %s, "
-			"lac = %i, "
-			"expire_lu = NULL "
-			"WHERE imsi = %s ",
-			q_name,
-			q_extension,
-			subscriber->authorized,
-			q_tmsi,
-			subscriber->lac,
-			subscriber->imsi);
-	} else {
-		result = dbi_conn_queryf(conn,
-			"UPDATE Subscriber "
-			"SET updated = datetime('now'), "
-			"name = %s, "
-			"extension = %s, "
-			"authorized = %i, "
-			"tmsi = %s, "
-			"lac = %i, "
-			"expire_lu = datetime(%i, 'unixepoch') "
-			"WHERE imsi = %s ",
-			q_name,
-			q_extension,
-			subscriber->authorized,
-			q_tmsi,
-			subscriber->lac,
-			(int) subscriber->expire_lu,
-			subscriber->imsi);
-	}
-
-	free(q_tmsi);
-	free(q_name);
-	free(q_extension);
-
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR, "Failed to update Subscriber (by IMSI).\n");
-		return 1;
-	}
-
-	dbi_result_free(result);
-
-	return 0;
-}
-
-int db_subscriber_delete(struct gsm_subscriber *subscr)
-{
-	dbi_result result;
-
-	result = dbi_conn_queryf(conn,
-			"DELETE FROM AuthKeys WHERE subscriber_id=%llu",
-			subscr->id);
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR,
-			"Failed to delete Authkeys for %llu\n", subscr->id);
-		return -1;
-	}
-	dbi_result_free(result);
-
-	result = dbi_conn_queryf(conn,
-			"DELETE FROM AuthLastTuples WHERE subscriber_id=%llu",
-			subscr->id);
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR,
-			"Failed to delete AuthLastTuples for %llu\n", subscr->id);
-		return -1;
-	}
-	dbi_result_free(result);
-
-	result = dbi_conn_queryf(conn,
-			"DELETE FROM AuthToken WHERE subscriber_id=%llu",
-			subscr->id);
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR,
-			"Failed to delete AuthToken for %llu\n", subscr->id);
-		return -1;
-	}
-	dbi_result_free(result);
-
-	result = dbi_conn_queryf(conn,
-			"DELETE FROM EquipmentWatch WHERE subscriber_id=%llu",
-			subscr->id);
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR,
-			"Failed to delete EquipmentWatch for %llu\n", subscr->id);
-		return -1;
-	}
-	dbi_result_free(result);
-
-	if (subscr->extension[0] != '\0') {
-		result = dbi_conn_queryf(conn,
-			    "DELETE FROM SMS WHERE src_addr=%s OR dest_addr=%s",
-					 subscr->extension, subscr->extension);
-		if (!result) {
-			LOGP(DDB, LOGL_ERROR,
-			     "Failed to delete SMS for %llu\n", subscr->id);
-			return -1;
-		}
-		dbi_result_free(result);
-	}
-
-	result = dbi_conn_queryf(conn,
-			"DELETE FROM VLR WHERE subscriber_id=%llu",
-			subscr->id);
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR,
-			"Failed to delete VLR for %llu\n", subscr->id);
-		return -1;
-	}
-	dbi_result_free(result);
-
-	result = dbi_conn_queryf(conn,
-			"DELETE FROM ApduBlobs WHERE subscriber_id=%llu",
-			subscr->id);
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR,
-			"Failed to delete ApduBlobs for %llu\n", subscr->id);
-		return -1;
-	}
-	dbi_result_free(result);
-
-	result = dbi_conn_queryf(conn,
-			"DELETE FROM Subscriber WHERE id=%llu",
-			subscr->id);
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR,
-			"Failed to delete Subscriber for %llu\n", subscr->id);
-		return -1;
-	}
-	dbi_result_free(result);
-
-	return 0;
-}
-
-/**
- * List all the authorized and non-expired subscribers. The callback will
- * be called one by one. The subscr argument is not fully initialize and
- * subscr_get/subscr_put must not be called. The passed in pointer will be
- * deleted after the callback by the database call.
- */
-int db_subscriber_list_active(void (*cb)(struct gsm_subscriber*,void*), void *closure)
-{
-	dbi_result result;
-
-	result = dbi_conn_query(conn,
-		       "SELECT * from Subscriber WHERE LAC != 0 AND authorized = 1");
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR, "Failed to list active subscribers\n");
-		return -1;
-	}
-
-	while (dbi_result_next_row(result)) {
-		struct gsm_subscriber *subscr;
-
-		subscr = subscr_alloc();
-		subscr->id = dbi_result_get_ulonglong(result, "id");
-		db_set_from_query(subscr, result);
-		cb(subscr, closure);
-		OSMO_ASSERT(subscr->use_count == 1);
-		llist_del(&subscr->entry);
-		talloc_free(subscr);
-	}
-
-	dbi_result_free(result);
-	return 0;
-}
-
-int db_sync_equipment(struct gsm_equipment *equip)
-{
-	dbi_result result;
-	unsigned char *cm2, *cm3;
-	char *q_imei;
-	uint8_t classmark1;
-
-	memcpy(&classmark1, &equip->classmark1, sizeof(classmark1));
-	DEBUGP(DDB, "Sync Equipment IMEI=%s, classmark1=%02x",
-		equip->imei, classmark1);
-	if (equip->classmark2_len)
-		DEBUGPC(DDB, ", classmark2=%s",
-			osmo_hexdump(equip->classmark2, equip->classmark2_len));
-	if (equip->classmark3_len)
-		DEBUGPC(DDB, ", classmark3=%s",
-			osmo_hexdump(equip->classmark3, equip->classmark3_len));
-	DEBUGPC(DDB, "\n");
-
-	dbi_conn_quote_binary_copy(conn, equip->classmark2,
-				   equip->classmark2_len, &cm2);
-	dbi_conn_quote_binary_copy(conn, equip->classmark3,
-				   equip->classmark3_len, &cm3);
-	dbi_conn_quote_string_copy(conn, equip->imei, &q_imei);
-
-	result = dbi_conn_queryf(conn,
-		"UPDATE Equipment SET "
-			"updated = datetime('now'), "
-			"classmark1 = %u, "
-			"classmark2 = %s, "
-			"classmark3 = %s "
-		"WHERE imei = %s ",
-		classmark1, cm2, cm3, q_imei);
-
-	free(cm2);
-	free(cm3);
-	free(q_imei);
-
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR, "Failed to update Equipment\n");
-		return -EIO;
-	}
-
-	dbi_result_free(result);
-	return 0;
-}
-
-int db_subscriber_expire(void *priv, void (*callback)(void *priv, long long unsigned int id))
-{
-	dbi_result result;
-
-	result = dbi_conn_query(conn,
-			"SELECT id "
-			"FROM Subscriber "
-			"WHERE lac != 0 AND "
-				"( expire_lu is NOT NULL "
-				"AND expire_lu < datetime('now') ) "
-			"LIMIT 1");
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR, "Failed to get expired subscribers\n");
-		return -EIO;
-	}
-
-	while (dbi_result_next_row(result))
-		callback(priv, dbi_result_get_ulonglong(result, "id"));
-
-	dbi_result_free(result);
-	return 0;
-}
-
-int db_subscriber_alloc_tmsi(struct gsm_subscriber *subscriber)
-{
-	dbi_result result = NULL;
-	char tmsi[14];
-	char *tmsi_quoted;
-
-	for (;;) {
-		if (RAND_bytes((uint8_t *) &subscriber->tmsi, sizeof(subscriber->tmsi)) != 1) {
-			LOGP(DDB, LOGL_ERROR, "RAND_bytes failed\n");
-			return 1;
-		}
-		if (subscriber->tmsi == GSM_RESERVED_TMSI)
-			continue;
-
-		sprintf(tmsi, "%u", subscriber->tmsi);
-		dbi_conn_quote_string_copy(conn, tmsi, &tmsi_quoted);
-		result = dbi_conn_queryf(conn,
-			"SELECT * FROM Subscriber "
-			"WHERE tmsi = %s ",
-			tmsi_quoted);
-
-		free(tmsi_quoted);
-
-		if (!result) {
-			LOGP(DDB, LOGL_ERROR, "Failed to query Subscriber "
-				"while allocating new TMSI.\n");
-			return 1;
-		}
-		if (dbi_result_get_numrows(result)) {
-			dbi_result_free(result);
-			continue;
-		}
-		if (!dbi_result_next_row(result)) {
-			dbi_result_free(result);
-			DEBUGP(DDB, "Allocated TMSI %u for IMSI %s.\n",
-				subscriber->tmsi, subscriber->imsi);
-			return db_sync_subscriber(subscriber);
-		}
-		dbi_result_free(result);
-	}
-	return 0;
-}
-
-int db_subscriber_alloc_exten(struct gsm_subscriber *subscriber, uint64_t smin,
-			      uint64_t smax)
-{
-	dbi_result result = NULL;
-	uint64_t try;
-
-	for (;;) {
-		try = (rand() % (smax - smin + 1) + smin);
-		result = dbi_conn_queryf(conn,
-			"SELECT * FROM Subscriber "
-			"WHERE extension = %"PRIu64,
-			try
-		);
-		if (!result) {
-			LOGP(DDB, LOGL_ERROR, "Failed to query Subscriber "
-				"while allocating new extension.\n");
-			return 1;
-		}
-		if (dbi_result_get_numrows(result)){
-			dbi_result_free(result);
-			continue;
-		}
-		if (!dbi_result_next_row(result)) {
-			dbi_result_free(result);
-			break;
-		}
-		dbi_result_free(result);
-	}
-	sprintf(subscriber->extension, "%"PRIu64, try);
-	DEBUGP(DDB, "Allocated extension %"PRIu64 " for IMSI %s.\n", try, subscriber->imsi);
-	return db_sync_subscriber(subscriber);
-}
-/*
- * try to allocate a new unique token for this subscriber and return it
- * via a parameter. if the subscriber already has a token, return
- * an error.
- */
-
-int db_subscriber_alloc_token(struct gsm_subscriber *subscriber, uint32_t *token)
-{
-	dbi_result result;
-	uint32_t try;
-
-	for (;;) {
-		if (RAND_bytes((uint8_t *) &try, sizeof(try)) != 1) {
-			LOGP(DDB, LOGL_ERROR, "RAND_bytes failed\n");
-			return 1;
-		}
-		if (!try) /* 0 is an invalid token */
-			continue;
-		result = dbi_conn_queryf(conn,
-			"SELECT * FROM AuthToken "
-			"WHERE subscriber_id = %llu OR token = \"%08X\" ",
-			subscriber->id, try);
-		if (!result) {
-			LOGP(DDB, LOGL_ERROR, "Failed to query AuthToken "
-				"while allocating new token.\n");
-			return 1;
-		}
-		if (dbi_result_get_numrows(result)) {
-			dbi_result_free(result);
-			continue;
-		}
-		if (!dbi_result_next_row(result)) {
-			dbi_result_free(result);
-			break;
-		}
-		dbi_result_free(result);
-	}
-	result = dbi_conn_queryf(conn,
-		"INSERT INTO AuthToken "
-		"(subscriber_id, created, token) "
-		"VALUES "
-		"(%llu, datetime('now'), \"%08X\") ",
-		subscriber->id, try);
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR, "Failed to create token %08X for "
-			"IMSI %s.\n", try, subscriber->imsi);
-		return 1;
-	}
-	dbi_result_free(result);
-	*token = try;
-	DEBUGP(DDB, "Allocated token %08X for IMSI %s.\n", try, subscriber->imsi);
-
-	return 0;
-}
-
-int db_subscriber_assoc_imei(struct gsm_subscriber *subscriber, char imei[GSM23003_IMEISV_NUM_DIGITS])
-{
-	unsigned long long equipment_id, watch_id;
-	dbi_result result;
-
-	osmo_strlcpy(subscriber->equipment.imei, imei, sizeof(subscriber->equipment.imei));
-
-	result = dbi_conn_queryf(conn,
-		"INSERT OR IGNORE INTO Equipment "
-		"(imei, created, updated) "
-		"VALUES "
-		"(%s, datetime('now'), datetime('now')) ",
-		imei);
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR, "Failed to create Equipment by IMEI.\n");
-		return 1;
-	}
-
-	equipment_id = 0;
-	if (dbi_result_get_numrows_affected(result)) {
-		equipment_id = dbi_conn_sequence_last(conn, NULL);
-	}
-	dbi_result_free(result);
-
-	if (equipment_id)
-		DEBUGP(DDB, "New Equipment: ID %llu, IMEI %s\n", equipment_id, imei);
-	else {
-		result = dbi_conn_queryf(conn,
-			"SELECT id FROM Equipment "
-			"WHERE imei = %s ",
-			imei
-		);
-		if (!result) {
-			LOGP(DDB, LOGL_ERROR, "Failed to query Equipment by IMEI.\n");
-			return 1;
-		}
-		if (!dbi_result_next_row(result)) {
-			LOGP(DDB, LOGL_ERROR, "Failed to find the Equipment.\n");
-			dbi_result_free(result);
-			return 1;
-		}
-		equipment_id = dbi_result_get_ulonglong(result, "id");
-		dbi_result_free(result);
-	}
-
-	result = dbi_conn_queryf(conn,
-		"INSERT OR IGNORE INTO EquipmentWatch "
-		"(subscriber_id, equipment_id, created, updated) "
-		"VALUES "
-		"(%llu, %llu, datetime('now'), datetime('now')) ",
-		subscriber->id, equipment_id);
-	if (!result) {
-		LOGP(DDB, LOGL_ERROR, "Failed to create EquipmentWatch.\n");
-		return 1;
-	}
-
-	watch_id = 0;
-	if (dbi_result_get_numrows_affected(result))
-		watch_id = dbi_conn_sequence_last(conn, NULL);
-
-	dbi_result_free(result);
-	if (watch_id)
-		DEBUGP(DDB, "New EquipmentWatch: ID %llu, IMSI %s, IMEI %s\n",
-			equipment_id, subscriber->imsi, imei);
-	else {
-		result = dbi_conn_queryf(conn,
-			"UPDATE EquipmentWatch "
-			"SET updated = datetime('now') "
-			"WHERE subscriber_id = %llu AND equipment_id = %llu ",
-			subscriber->id, equipment_id);
-		if (!result) {
-			LOGP(DDB, LOGL_ERROR, "Failed to update EquipmentWatch.\n");
-			return 1;
-		}
-		dbi_result_free(result);
-		DEBUGP(DDB, "Updated EquipmentWatch: ID %llu, IMSI %s, IMEI %s\n",
-			equipment_id, subscriber->imsi, imei);
-	}
-
-	return 0;
-}
-
 /* store an [unsent] SMS to the database */
 int db_sms_store(struct gsm_sms *sms)
 {
@@ -1500,7 +601,7 @@ static struct gsm_sms *sms_from_result(struct gsm_network *net, dbi_result resul
 	daddr = dbi_result_get_string(result, "dest_addr");
 	if (daddr)
 		osmo_strlcpy(sms->dst.addr, daddr, sizeof(sms->dst.addr));
-	sms->receiver = subscr_get_by_extension(net->subscr_group, sms->dst.addr);
+	sms->receiver = vlr_subscr_find_by_msisdn(net->vlr, sms->dst.addr);
 
 	sms->src.npi = dbi_result_get_ulonglong(result, "src_npi");
 	sms->src.ton = dbi_result_get_ulonglong(result, "src_ton");
@@ -1542,20 +643,21 @@ struct gsm_sms *db_sms_get(struct gsm_network *net, unsigned long long id)
 	return sms;
 }
 
-/* retrieve the next unsent SMS with ID >= min_id */
-struct gsm_sms *db_sms_get_unsent(struct gsm_network *net, unsigned long long min_id)
+struct gsm_sms *db_sms_get_next_unsent(struct gsm_network *net,
+				       unsigned long long min_sms_id,
+				       unsigned int max_failed)
 {
 	dbi_result result;
 	struct gsm_sms *sms;
 
 	result = dbi_conn_queryf(conn,
-		"SELECT SMS.* "
-			"FROM SMS JOIN Subscriber ON "
-				"SMS.dest_addr = Subscriber.extension "
-			"WHERE SMS.id >= %llu AND SMS.sent IS NULL "
-				"AND Subscriber.lac > 0 "
-			"ORDER BY SMS.id LIMIT 1",
-		min_id);
+		"SELECT * FROM SMS"
+		" WHERE sent IS NULL"
+		" AND id >= %llu"
+		" AND deliver_attempts <= %u"
+		" ORDER BY id LIMIT 1",
+		min_sms_id, max_failed);
+
 	if (!result)
 		return NULL;
 
@@ -1571,21 +673,24 @@ struct gsm_sms *db_sms_get_unsent(struct gsm_network *net, unsigned long long mi
 	return sms;
 }
 
-struct gsm_sms *db_sms_get_unsent_by_subscr(struct gsm_network *net,
-					    unsigned long long min_subscr_id,
-					    unsigned int failed)
+/* retrieve the next unsent SMS for a given subscriber */
+struct gsm_sms *db_sms_get_unsent_for_subscr(struct vlr_subscr *vsub,
+					     unsigned int max_failed)
 {
+	struct gsm_network *net = vsub->vlr->user_ctx;
 	dbi_result result;
 	struct gsm_sms *sms;
 
+	if (!vsub->lu_complete)
+		return NULL;
+
 	result = dbi_conn_queryf(conn,
-		"SELECT SMS.* "
-			"FROM SMS JOIN Subscriber ON "
-				"SMS.dest_addr = Subscriber.extension "
-			"WHERE Subscriber.id >= %llu AND SMS.sent IS NULL "
-				"AND Subscriber.lac > 0 AND SMS.deliver_attempts < %u "
-			"ORDER BY Subscriber.id, SMS.id LIMIT 1",
-		min_subscr_id, failed);
+		"SELECT * FROM SMS"
+		" WHERE sent IS NULL"
+		" AND dest_addr=%s"
+		" AND deliver_attempts <= %u"
+		" ORDER BY id LIMIT 1",
+		vsub->msisdn, max_failed);
 	if (!result)
 		return NULL;
 
@@ -1601,20 +706,20 @@ struct gsm_sms *db_sms_get_unsent_by_subscr(struct gsm_network *net,
 	return sms;
 }
 
-/* retrieve the next unsent SMS for a given subscriber */
-struct gsm_sms *db_sms_get_unsent_for_subscr(struct gsm_subscriber *subscr)
+struct gsm_sms *db_sms_get_next_unsent_rr_msisdn(struct gsm_network *net,
+						 const char *last_msisdn,
+						 unsigned int max_failed)
 {
 	dbi_result result;
 	struct gsm_sms *sms;
 
 	result = dbi_conn_queryf(conn,
-		"SELECT SMS.* "
-			"FROM SMS JOIN Subscriber ON "
-				"SMS.dest_addr = Subscriber.extension "
-			"WHERE Subscriber.id = %llu AND SMS.sent IS NULL "
-				"AND Subscriber.lac > 0 "
-			"ORDER BY SMS.id LIMIT 1",
-		subscr->id);
+		"SELECT * FROM SMS"
+		" WHERE sent IS NULL"
+		" AND dest_addr > '%s'"
+		" AND deliver_attempts <= %u"
+		" ORDER BY dest_addr, id LIMIT 1",
+		last_msisdn, max_failed);
 	if (!result)
 		return NULL;
 
@@ -1623,7 +728,7 @@ struct gsm_sms *db_sms_get_unsent_for_subscr(struct gsm_subscriber *subscr)
 		return NULL;
 	}
 
-	sms = sms_from_result(subscr->group->net, result);
+	sms = sms_from_result(net, result);
 
 	dbi_result_free(result);
 
@@ -1667,26 +772,20 @@ int db_sms_inc_deliver_attempts(struct gsm_sms *sms)
 	return 0;
 }
 
-int db_apdu_blob_store(struct gsm_subscriber *subscr,
-			uint8_t apdu_id_flags, uint8_t len,
-			uint8_t *apdu)
+/* Drop all pending SMS to or from the given extension */
+int db_sms_delete_by_msisdn(const char *msisdn)
 {
 	dbi_result result;
-	unsigned char *q_apdu;
-
-	dbi_conn_quote_binary_copy(conn, apdu, len, &q_apdu);
-
+	if (!msisdn || !*msisdn)
+		return 0;
 	result = dbi_conn_queryf(conn,
-		"INSERT INTO ApduBlobs "
-		"(created,subscriber_id,apdu_id_flags,apdu) VALUES "
-		"(datetime('now'),%llu,%u,%s)",
-		subscr->id, apdu_id_flags, q_apdu);
-
-	free(q_apdu);
-
-	if (!result)
-		return -EIO;
-
+		    "DELETE FROM SMS WHERE src_addr=%s OR dest_addr=%s",
+		    msisdn, msisdn);
+	if (!result) {
+		LOGP(DDB, LOGL_ERROR,
+		     "Failed to delete SMS for %s\n", msisdn);
+		return -1;
+	}
 	dbi_result_free(result);
 	return 0;
 }