Get best of the week

Export des Nummerierungsplans der Federal Communications Agency in eine relationale Datenbank

Das Bundesamt für Kommunikation aktualisiert regelmäßig den öffentlich zugänglichen Nummerierungsplan . Wenn Sie diesen Plan verwenden, um die Region oder den Abonnentenanbieter in Ihrem Wählplan zu bestimmen, sind Sie höchstwahrscheinlich an der Relevanz dieser Informationen interessiert. Auf den ersten Blick ist das Schreiben einer Anwendung, die Daten herunterlädt, verarbeitet und an die Datenbank sendet, nicht kompliziert. Wenn Sie jedoch mit der Implementierung beginnen, werden Sie unweigerlich auf Fallstricke stoßen, über die ich jetzt sprechen werde.

Der

Wählplan besteht aus vier tabellarischen Dateien im CSV-Format: rossvyaz.ru/data/ABC-3xx.csv
rossvyaz.ru/data/ABC-4xx.csv
rossvyaz.ru/data/ABC-8xx.csv
rossvyaz.ru/data/ DEF-9xx.csv-

URLs ändern sich manchmal.

Die Struktur aller Tabellen ist gleich:

/ DEF;;;;;

Im Feld Region haben einige Anbieter zusätzliche Semikolons:

 ; | ;

Um Fehler beim Lesen der Tabelle durch Ihre Anwendung zu vermeiden, müssen Sie die Überprüfung der erwarteten Anzahl von Feldern deaktivieren oder den falschen Datensatz durch den richtigen ersetzen.

Wir werden Daten in eine Tabelle mit der folgenden Struktur schreiben:

first BIGINT PRIMARY KEY NOT NULL  //  
last BIGINT UNIQUE NOT NULL        //  
provider TEXT                      // 
source_region TEXT                 //        
region INT NOT NULL                //

Die meisten Bereiche in den Tabellen können durch das Auftreten eines Teilstrings eindeutig identifiziert werden. Beispielsweise enthalten alle Einträge für die Region Saratov die Teilzeichenfolge Sarat .

Für jene Regionen, die nicht eindeutig bestimmt werden können, ist zusätzliche Logik erforderlich, siehe unten.

Beispiel 1:

  • Autonomer Okrug von Yamal-Nenzen
  • Nenzen Autonomous Okrug

Der autonome Okrug von Yamalo-Nenzen gehört zur Region Tjumen, und der autonome Okrug von Nenzen gehört zum Archangelsk. Das Problem bei der Bestimmung des numerischen Codes der Region besteht darin, dass der Teilstring Nenets AO vollständig im Yamalo-Nenets AO enthalten ist . Dies bedeutet, dass zwei Regionen Einträgen entsprechen, die den Teilstring des autonomen Bereichs Nenzen enthalten .

Um dieses Problem zu lösen, müssen Sie eine Prüfung auf das Fehlen eines Teilstrings hinzufügen (ausgenommen Teilstring). Mit anderen Worten, der autonome Yamalo-Nenets-Okrug wird durch den Eintritt von Yamalo und der autonome Nenets-Okrug durch den Eintritt von Nenets und das Fehlen von Yamalo bestimmt .

Beispiel 2:

  • gehen. Krasnogorsk
  • Lyantor
  • Partza

Ein Beispiel für nicht identifizierte Regionen. Es gibt viele solcher Aufzeichnungen in den Tabellen von Rossvyaz. Finder hilft herauszufinden, dass GO Krasnogorsk - Region Moskau, Lyantor - der autonome Khanty-Mansi-Okrug und die Siedlung Partz - die Republik Mordowien. Die Lösung ist einfach, der gewünschte Teilstring wird in ein Array von Zeichenfolgen konvertiert, und wir werden das Auftreten in einer Schleife überprüfen. Der exklusive Teilstring wird ebenfalls in ein Array konvertiert.

Beispiel 3:

  • Sibirischer Bundesdistrikt, Fernöstlicher Bundesdistrikt
  • Krasnojarsker Territorium, Republik Khakassia, Moskau, St. Petersburg
  • russische Föderation

In diesem Beispiel ist es nicht möglich, eine Region eindeutig zu identifizieren. Sie können den am dichtesten besetzten der aufgelisteten auswählen oder solchen Datensätzen einen speziellen Code zuweisen. Tatsächlich sind alle Datensätze, die mehreren Regionen entsprechen, die Nummern 8-80 [0-9], Bundesnummern, für die keine Gebühren erhoben werden. Ich habe solchen Datensätzen Codes im Bereich von 200 bis 210 zugewiesen. Ich glaube nicht, dass sie mir jemals nützlich sein werden.

Den Anwendungscode finden Sie hier .
Ausführbare Dateien für Linux-, MacOS- und Windows-Plattformen befinden sich im Verzeichnis bin (ich habe die Anwendung auf der Windows-Plattform nicht getestet).

Konfigurationsdatei Config.ymlmuss sich im selben Verzeichnis wie die ausführbare Datei befinden. Wenn Sie möchten, können Sie die Flag-Unterstützung in der Anwendung implementieren, um den Pfad zur Konfiguration in den Befehlszeilenargumenten anzugeben. Pull-Anforderungen sind willkommen.

Konfigurationsdatei
data_source:
  - https://rossvyaz.ru/data/ABC-3xx.csv
  - https://rossvyaz.ru/data/ABC-4xx.csv
  - https://rossvyaz.ru/data/ABC-8xx.csv
  - https://rossvyaz.ru/data/DEF-9xx.csv
#  - ../service/testdata/ABC-3xx.csv
#  - ../service/testdata/ABC-4xx.csv
#  - ../service/testdata/ABC-8xx.csv
#  - ../service/testdata/DEF-9xx.csv
exceptions: exceptions.yml
regions: regions.yml
db:
  host: localhost
  name: asterisk
  table: codes
  user: asterisk
  password: asterisk


Das Feld data_source enthält die Pfade zu den Rossvyaz-Tabellendateien. Wenn der Pfad mit http beginnt , lädt die Anwendung die Tabelle mithilfe des integrierten Webclients. Andernfalls wird versucht, die Tabelle im Dateisystem zu finden.

Achten Sie auf die auskommentierten Zeilen. Dies sind die Pfade zu den im Repository gespeicherten Rossvyaz-Tabellendateien. Wenn Sie nicht über die schnellste Internetverbindung verfügen, werden diese Dateien bei jedem Anwendungsstart erneut heruntergeladen. Kommentieren Sie diese Zeilen für eine schnellere Anwendung aus und kommentieren Sie die Hyperlinks aus. Möglicherweise müssen Sie die Anwendung öfter ausführen, als Sie denken. Später werde ich anhand eines Beispiels erläutern, warum.

Felder Ausnahmen und Regionenenthalten Pfade zu Dateien mit Korrekturen und eine Beschreibung der Regionen. Über sie etwas später.

Der Abschnitt db beschreibt die Parameter für die Verbindung zum MySQL-DBMS. Um ein anderes DBMS zu verwenden, müssen Sie den Treiber im Code und die Verbindungszeichenfolgenvorlage im Konstruktor der Servicestruktur ersetzen

db, err := dbr.Open("mysql",
		fmt.Sprintf("%s:%s@tcp(%s)/%s", c.DB.User, c.DB.Password, c.DB.Host, c.DB.Name),
		nil)

Es ist jedoch nicht kompliziert, der Konfiguration ein DBMS-Typfeld hinzuzufügen und der Anwendung Unterstützung für diese Typen hinzuzufügen. Pull-Anfragen sind wieder willkommen.

Die Datei exception.yml enthält eine Liste der Korrekturen. Alle Übereinstimmungen von Teilzeichenfolgen in Ausnahmen.yml vor dem Doppelpunkt, die in den Tabellendateien von Rossvyaz enthalten sind, werden durch Teilzeichenfolgen nach dem Doppelpunkt ersetzt.

Die Datei region.yml enthält eine Liste numerischer Codes von Regionen mit Arrays von Vorkommen und ohne Teilzeichenfolgen, die zum Bestimmen einer bestimmten Region verwendet werden. Wenn kein Datensatz für jeden Datensatz oder gibt es zwei oder mehr Spiele, dann ist dieser Datensatz in die Datenbank gehen gefunden wird, aber die Region Feld den Wert 0 (Region nicht definiert) enthalten.

Beispiel für eine Regionsbeschreibung
78:
  name: -
  contain:
    - 
    - 
    - .. 
  not_contain:
    -


Erstellen Sie vor dem Starten der Anwendung eine Datenbank mit dem in db.name angegebenen Namen und stellen Sie sicher, dass der in db.user angegebene Benutzer über Lese- und Schreibberechtigungen verfügt.

Nachdem wir alles eingerichtet haben, ist es Zeit, die Anwendung zu starten.

Bitte beachten Sie, dass beim Start die im Feld db.table der Konfigurationsdatei angegebene Tabelle gelöscht wird. Seien Sie vorsichtig.

./def2sql

Wenn keine Fehler und Warnungen vorliegen, wird eine ähnliche Ausgabe angezeigt
correct records amount: 372324
inserted 372324 records


Andernfalls wird ein Bericht mit Fehlern und Warnungen angezeigt.
correct records amount: 372324
inserted 372324 records
{
    "unknown_regions": [
        {
            "First": 3424333950,
            "Last": 3424333999,
            "Range": 50,
            "Provider": " \"\"",
            "SourceRegion": ".. ",
            "Region": 0
        },
        {
            "First": 3424820000,
            "Last": 3424820049,
            "Range": 50,
            "Provider": " \"\"",
            "SourceRegion": ".. ",
            "Region": 0
        },
        {
            "First": 3425425000,
            "Last": 3425425049,
            "Range": 50,
            "Provider": " \"\"",
            "SourceRegion": ".. ",
            "Region": 0
        },
        {
            "First": 3425620000,
            "Last": 3425620049,
            "Range": 50,
            "Provider": " \"\"",
            "SourceRegion": ".. ",
            "Region": 0
        },
        {
            "First": 3426050000,
            "Last": 3426050050,
            "Range": 51,
            "Provider": " \"\"",
            "SourceRegion": ".. ",
            "Region": 0
        },
        {
            "First": 3427399950,
            "Last": 3427399999,
            "Range": 50,
            "Provider": " \"\"",
            "SourceRegion": ".. ",
            "Region": 0
        },
        {
            "First": 4217523500,
            "Last": 4217523999,
            "Range": 500,
            "Provider": " \" \"\"",
            "SourceRegion": " ",
            "Region": 0
        },
        {
            "First": 4217526000,
            "Last": 4217526999,
            "Range": 1000,
            "Provider": " \" \"\"",
            "SourceRegion": " ",
            "Region": 0
        },
        {
            "First": 8003550000,
            "Last": 8003559999,
            "Range": 10000,
            "Provider": " \" \" ( 2460087999)",
            "SourceRegion": "  *   * .  * . -",
            "Region": 0
        },
        {
            "First": 8003810000,
            "Last": 8003819999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 8013810000,
            "Last": 8013819999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 8023810000,
            "Last": 8023819999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 8031010000,
            "Last": 8031019999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 8033550000,
            "Last": 8033559999,
            "Range": 10000,
            "Provider": " \" \" ( 2460087999)",
            "SourceRegion": "  *   * .  * . -",
            "Region": 0
        },
        {
            "First": 8033810000,
            "Last": 8033819999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 8041010000,
            "Last": 8041019999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 8043810000,
            "Last": 8043819999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 8051010000,
            "Last": 8051019999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 8053810000,
            "Last": 8053819999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 8063810000,
            "Last": 8063819999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 8073810000,
            "Last": 8073819999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 8083810000,
            "Last": 8083819999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 8091010000,
            "Last": 8091019999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 8093550000,
            "Last": 8093559999,
            "Range": 10000,
            "Provider": " \" \" ( 2460087999)",
            "SourceRegion": "  *   * .  * . -",
            "Region": 0
        },
        {
            "First": 8093810000,
            "Last": 8093819999,
            "Range": 10000,
            "Provider": " \"\"",
            "SourceRegion": "   *   ",
            "Region": 0
        },
        {
            "First": 9512780000,
            "Last": 9512789999,
            "Range": 10000,
            "Provider": " \"2 \"",
            "SourceRegion": "  *  |  *  ",
            "Region": 0
        },
        {
            "First": 9963000000,
            "Last": 9963029999,
            "Range": 30000,
            "Provider": " \"2 \"",
            "SourceRegion": "  *  |  *  ",
            "Region": 0
        }
    ],
    "warnings": [
        "couldn't find region for record (3424333950-3424333999;  \"\", .. )",
        "couldn't find region for record (3424820000-3424820049;  \"\", .. )",
        "couldn't find region for record (3425425000-3425425049;  \"\", .. )",
        "couldn't find region for record (3425620000-3425620049;  \"\", .. )",
        "couldn't find region for record (3426050000-3426050050;  \"\", .. )",
        "couldn't find region for record (3427399950-3427399999;  \"\", .. )",
        "couldn't find region for record (4217523500-4217523999;  \" \"\",  )",
        "couldn't find region for record (4217526000-4217526999;  \" \"\",  )",
        "couldn't find region for record (8003550000-8003559999;  \" \" ( 2460087999),   *   * .  * . -)",
initial commit
        "couldn't find region for record (8003810000-8003819999;  \"\",    *   )",
        "couldn't find region for record (8013810000-8013819999;  \"\",    *   )",
        "couldn't find region for record (8023810000-8023819999;  \"\",    *   )",
        "couldn't find region for record (8031010000-8031019999;  \"\",    *   )",
        "couldn't find region for record (8033550000-8033559999;  \" \" ( 2460087999),   *   * .  * . -)",
        "couldn't find region for record (8033810000-8033819999;  \"\",    *   )",
        "couldn't find region for record (8041010000-8041019999;  \"\",    *   )",
        "couldn't find region for record (8043810000-8043819999;  \"\",    *   )",
        "couldn't find region for record (8051010000-8051019999;  \"\",    *   )",
        "couldn't find region for record (8053810000-8053819999;  \"\",    *   )",
        "couldn't find region for record (8063810000-8063819999;  \"\",    *   )",
        "couldn't find region for record (8073810000-8073819999;  \"\",    *   )",
        "couldn't find region for record (8083810000-8083819999;  \"\",    *   )",
        "couldn't find region for record (8091010000-8091019999;  \"\",    *   )",
        "couldn't find region for record (8093550000-8093559999;  \" \" ( 2460087999),   *   * .  * . -)",
        "couldn't find region for record (8093810000-8093819999;  \"\",    *   )",
        "couldn't find region for record (9512780000-9512789999;  \"2 \",   *  |  *  )",
        "couldn't find region for record (9963000000-9963029999;  \"2 \",   *  |  *  )"
    ]
}


Der Bericht enthält mehrere Abschnitte.

unknown_regions - Eine Liste von Datensätzen, die nicht Regionen entsprechen oder deren Anzahl mehr als eins beträgt.
false_records - Liste ungültiger Einträge in der Rossvyaz-Tabelle;
Warnungen - Eine Liste von Warnungen, die während der Ausführung der Anwendung aufgetreten sind.

Der Bericht unter dem letzten Spoiler, den ich beim Start der Anwendung etwa einen Monat nach der letzten Ausgabe von region.yml erhalten habe .

Veraltete Version von region.yml
1:
  name:   ()
  contain:
    - 
2:
  name:  
  contain:
    - 
    -   
3:
  name:  
  contain:
    - 
4:
  name:  
  contain:
    -  
5:
  name:  
  contain:
    - 
6:
  name:  
  contain:
    - 
7:
  name: - 
  contain:
    - -
8:
  name:  
  contain:
    - 
9:
  name: - 
  contain:
    - 
10:
  name:  
  contain:
    - 
    - .. 
11:
  name:  
  contain:
    - 
12:
  name:   
  contain:
    - 
13:
  name:  
  contain:
    - 
    - . 
14:
  name:   ()
  contain:
    - 
    - 
15:
  name:    - 
  contain:
    - 
16:
  name:   ()
  contain:
    - 
    - 
    - 
17:
  name:  
  contain:
    - 
18:
  name:  
  contain:
    - 
    - . 
19:
  name:  
  contain:
    - 
  not_contain:
    - 
20:
  name:  
  contain:
    -  
    - 
    -  
21:
  name:   - 
  contain:
    -  
    - 
22:
  name:  
  contain:
    -  
23:
  name:  
  contain:
    - 
24:
  name:  
  contain:
    -  
  not_contain:
    - 
25:
  name:  
  contain:
    -  
26:
  name:  
  contain:
    -  
27:
  name:  
  contain:
    - 
28:
  name:  
  contain:
    - 
29:
  name:  
  contain:
    - 
    - 
  not_contain:
    - 
    - 
30:
  name:  
  contain:
    - 
31:
  name:  
  contain:
    - 
32:
  name:  
  contain:
    - 
33:
  name:  
  contain:
    - 
34:
  name:  
  contain:
    - 
35:
  name:  
  contain:
    - 
36:
  name:  
  contain:
    - 
    - .. 
37:
  name:  
  contain:
    - 
    - .. 
    - .. 
38:
  name:  
  contain:
    - 
39:
  name:  
  contain:
    - 
40:
  name:  
  contain:
    - 
41:
  name:  
  contain:
    - 
42:
  name:  
  contain:
    - 
43:
  name:  
  contain:
    - 
44:
  name:  
  contain:
    - 
45:
  name:  
  contain:
    - 
46:
  name:  
  contain:
    - 
#47:
#  name:  
#  contain:
#    - 
48:
  name:  
  contain:
    - 
49:
  name:  
  contain:
    - 
#50:
#  name:  
#  contain:
#    - 
51:
  name:  
  contain:
    - 
52:
  name:  
  contain:
    - 
53:
  name:  
  contain:
    - 
54:
  name:  
  contain:
    - 
    - . 
55:
  name:  
  contain:
    - 
    - . 
56:
  name:  
  contain:
    - 
57:
  name:  
  contain:
    - 
58:
  name:  
  contain:
    - 
59:
  name:  
  contain:
    - 
    - .. 
    -  -
    - - 
60:
  name:  
  contain:
    - 
61:
  name:  
  contain:
    - 
62:
  name:  
  contain:
    - 
63:
  name:  
  contain:
    - 
64:
  name:  
  contain:
    - 
65:
  name:  
  contain:
    - 
66:
  name:  
  contain:
    - 
67:
  name:  
  contain:
    - 
68:
  name:  
  contain:
    - 
69:
  name:  
  contain:
    - 
70:
  name:  
  contain:
    - 
71:
  name:  
  contain:
    - 
72:
  name:  
  contain:
    - 
73:
  name:  
  contain:
    - 
74:
  name:  
  contain:
    - 
    - .. 
    - .. 
75:
  name:  
  contain:
    - 
76:
  name:  
  contain:
    - 
77:
  name: . 
  contain:
    - 
    - 
    - 
    - 
    - .. 
    - .. 
    - .. 
    - .. 
    - .. 
    -    
    - .. 
    - .. 
    - .. 
    -  . 
    - . 
  not_contain:
    - 
78:
  name: -
  contain:
    - 
    - 
    - .. 
  not_contain:
    - 
79:
  name:   
  contain:
    - 
#83:
#  name:   
#  contain:
#    - 
86:
  name: -   - 
  contain:
    - 
    - 
    - 
    - 
    - 
    - . 
  not_contain:
    - 
87:
  name:   
  contain:
    - 
89:
  name: -  
  contain:
    - -
91:
  name:  
  contain:
    -  
    - 
    - 
#92:
#  name: 
#  contain:
#    - 
99:
  name:  ,     
  contain:
    - 

200:
  name:  
  contain:
    - 

201:
  name:   
  contain:
    -   
  not_contain:
    -   
202:
  name:   
  contain:
    -   
  not_contain:
    -   
203:
  name:   
  contain:
    -   
204:
  name:   
  contain:
    -   
  not_contain:
    -   
205:
  name:   
  contain:
    -   
  not_contain:
    -   
206:
  name: -  
  contain:
    - -  
207:
  name:   ,   
  contain:
    -   ,   
208:
  name:   ,   
  contain:
    -   ,   
209:
  name:  ,  , . , . -
  contain:
    -  ,  , . , . -


Um Warnungen zu entfernen und Regionscodes für alle Datensätze abzurufen , müssen Sie region.yml bearbeiten .

Diff-Versionen von region.yml
10d9
<     -  
245d243
<     -  
265,270d262
<     - .. 
<     - .. 
<     - .. 
<     - .. 
<     - .. 
<     - .. 
452d443
<     -    *   
457d447
<     -    *   
461,462c451
<     -  ,  , . , . -
<     -   *   * .  * . -
\ No newline at end of file
---
>     -  ,  , . , . -
\ No newline at end of file


Voila
correct records amount: 372324
inserted 372324 records


Als Kirsche auf dem Kuchen biete ich das Asterisk-Wählplan-Makro an, um die Region anhand der Teilnehmernummer zu bestimmen:

[macro-get-region]
        exten => s,1,MYSQL(Connect conn localhost user password dbname)
        exten => s,n,MYSQL(Query result ${conn} SELECT region FROM codes WHERE ${ARG1} BETWEEN first AND last LIMIT 1)
        exten => s,n,MYSQL(Fetch region ${result} region_num)
        exten => s,n,MYSQL(Clear ${result})
        exten => s,n,MYSQL(Disconnect ${conn})

Für die gleichen Zwecke können Sie das Lua-Skript verwenden:
local driver = require("luasql.mysql")
local env = assert (driver.mysql())
local con = assert (env:connect("dbname", "user", "password"))
local cur = assert (con:execute(string.format("select region from codes where %s between first and last limit 1", arg[1])))

row = cur:fetch ({}, "a")
if row ~= nil then
    print(row.region)
else
    print(0)
end

cur:close()
con:close()
env:close(

Es ist jedoch jedes Tool geeignet, das SELECT-Abfragen an MySQL unterstützt.

Viel Spaß bei Ihrer Verwaltung.

More articles:


All Articles