Change in osmo-hlr[master]: manual: describe subscriber import by SQL

This is merely a historical archive of years 2008-2021, before the migration to mailman3.

A maintained and still updated list archive can be found at https://lists.osmocom.org/hyperkitty/list/gerrit-log@lists.osmocom.org/.

laforge gerrit-no-reply at lists.osmocom.org
Tue Jul 21 16:12:25 UTC 2020


laforge has submitted this change. ( https://gerrit.osmocom.org/c/osmo-hlr/+/19052 )

Change subject: manual: describe subscriber import by SQL
......................................................................

manual: describe subscriber import by SQL

It seems a bad idea to cement the internal SQL structure in the user manual,
but since we currently lack a safe and portable import mechanism (like CSV
import in osmo-hlr-db-tool), this is currently valuable info to users.

Change-Id: I3246e6d5364215a71c33b5aca876deab7b6cfd70
---
M doc/manuals/chapters/running.adoc
M doc/manuals/chapters/subscribers.adoc
2 files changed, 81 insertions(+), 1 deletion(-)

Approvals:
  Jenkins Builder: Verified
  pespin: Looks good to me, but someone else must approve
  laforge: Looks good to me, approved

Objections:
  fixeria: I would prefer this is not merged as is



diff --git a/doc/manuals/chapters/running.adoc b/doc/manuals/chapters/running.adoc
index ff75c7f..1f1bcdc 100644
--- a/doc/manuals/chapters/running.adoc
+++ b/doc/manuals/chapters/running.adoc
@@ -54,7 +54,7 @@
 
 Alternatively, you may use the `osmo-hlr-db-tool`, which is installed along
 with `osmo-hlr`, to bootstrap an empty database, or to migrate subscriber data
-from an old 'OsmoNITB' database. See `osmo-hlr-db-tool --help`.
+from an old 'OsmoNITB' database. See <<db_import_nitb>>.
 
 === Multiple instances
 
diff --git a/doc/manuals/chapters/subscribers.adoc b/doc/manuals/chapters/subscribers.adoc
index ab41b0f..3bd5879 100644
--- a/doc/manuals/chapters/subscribers.adoc
+++ b/doc/manuals/chapters/subscribers.adoc
@@ -127,3 +127,83 @@
 ----
 <1> Randomly generated 5 digit MSISDN
 <2> Disabled CS and PS NAM prevent the subscriber from accessing the network
+
+
+=== Import Subscriber Data
+
+==== Scripted Import
+
+WARNING: It is not generally a good idea to depend on the HLR database's internal table structure, but in the lack of an
+automated import procedure, this example is provided as an ad-hoc method to aid automated subscriber import. This is not
+guaranteed to remain valid.
+
+NOTE: We may add CSV and other import methods to the `osmo-hlr-db-tool`, but so far that is not implemented. Contact the
+community if you are interested in such a feature being implemented.
+
+NOTE: `sqlite3` is available from your distribution packages or `sqlite.org`.
+
+Currently, probably the easiest way to automatically import subscribers to OsmoHLR is to write out a text file with SQL
+commands per subscriber, and feed that to `sqlite3`, as described below.
+
+A difficulty is to always choose subscriber IDs that are not yet in use. For an initial import, the subscriber ID may be
+incremented per subscriber record. If adding more subscribers to an existing database, it is necessary to choose
+subscriber IDs that are not yet in use. Get the highest ID in use with:
+
+----
+sqlite3 hlr.db 'select max(id) from subscriber'
+----
+
+A full SQL example of adding a single subscriber with id 23, IMSI 001010123456789, MSISDN 1234, Ki for COMP128v1, and K
+and OPC for Milenage:
+
+----
+INSERT subscriber (id, imsi, msisdn) VALUES (23, '001010123456789', '1234');
+
+INSERT INTO auc_2g (subscriber_id, algo_id_2g, ki)
+VALUES(23, 1, '0123456789abcdef0123456789abcdef');
+
+INSERT INTO auc_3g (subscriber_id, algo_id_3g, k, op, opc)
+VALUES(23, 5, '0123456789abcdef0123456789abcdef',NULL,'0123456789abcdef0123456789abcdef');
+----
+
+Table entries to `auc_2g` and/or `auc_3g` may be omitted if no such key material is required.
+
+UMTS Milenage auth (on both 2G and 3G RAN) is configured by the `auc_3g` table. `algo_id_3g` must currently always be 5
+(MILENAGE).
+
+The algorithm IDs for `algo_id_2g` and `algo_id_3g` are:
+
+.Algorithm IDs in OsmoHLR's database
+[options="header",width="50%",cols="40%,60%"]
+|===
+|`algo_id_2g` / `algo_id_3g` | Authentication Algorithm
+| 1 | COMP128v1
+| 2 | COMP128v2
+| 3 | COMP128v3
+| 4 | XOR
+| 5 | MILENAGE
+|===
+
+Create an empty HLR database with
+
+----
+osmo-hlr-db-tool -l hlr.db create
+----
+
+Repeat above SQL commands per subscriber, incrementing the subscriber ID for each block, then feed the SQL commands for
+the subscribers to be imported to the `sqlite3` command line tool:
+
+----
+sqlite3 hlr.db < subscribers.sql
+----
+
+[[db_import_nitb]]
+==== Import OsmoNITB database
+
+To upgrade from old OsmoNITB to OsmoHLR, use `osmo-hlr-db-tool`:
+
+----
+osmo-hlr-db-tool -l hlr.db import-nitb-db nitb.db
+----
+
+Be aware that the import is lossy, only the IMSI, MSISDN, nam_cs/ps and 2G auth data are set.

-- 
To view, visit https://gerrit.osmocom.org/c/osmo-hlr/+/19052
To unsubscribe, or for help writing mail filters, visit https://gerrit.osmocom.org/settings

Gerrit-Project: osmo-hlr
Gerrit-Branch: master
Gerrit-Change-Id: I3246e6d5364215a71c33b5aca876deab7b6cfd70
Gerrit-Change-Number: 19052
Gerrit-PatchSet: 1
Gerrit-Owner: neels <nhofmeyr at sysmocom.de>
Gerrit-Reviewer: Jenkins Builder
Gerrit-Reviewer: daniel <dwillmann at sysmocom.de>
Gerrit-Reviewer: dexter <pmaier at sysmocom.de>
Gerrit-Reviewer: fixeria <vyanitskiy at sysmocom.de>
Gerrit-Reviewer: laforge <laforge at osmocom.org>
Gerrit-Reviewer: neels <nhofmeyr at sysmocom.de>
Gerrit-Reviewer: pespin <pespin at sysmocom.de>
Gerrit-MessageType: merged
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osmocom.org/pipermail/gerrit-log/attachments/20200721/dd7f27f1/attachment.htm>


More information about the gerrit-log mailing list