Get best of the week

Exportation du plan de numérotation de l'Agence fédérale des communications vers une base de données relationnelle

La Federal Communications Agency met régulièrement à jour le plan de numérotation accessible au public . Si vous utilisez ce plan pour déterminer la région ou le fournisseur d'abonnés dans votre plan de numérotation, alors vous êtes très probablement intéressé par la pertinence de ces informations. À première vue, il n'y a rien de difficile à écrire une application qui télécharge, traite et envoie des données à la base de données, cependant, lorsque vous commencerez l'implémentation, vous rencontrerez inévitablement des pièges, dont je parlerai maintenant.

Le plan de numérotation se compose de quatre fichiers tabulaires au format csv:

rossvyaz.ru/data/ABC-3xx.csv
rossvyaz.ru/data/ABC-4xx.csv
rossvyaz.ru/data/ABC-8xx.csv
rossvyaz.ru/data/ Les

URL DEF-9xx.csv changent parfois.

La structure de toutes les tables est la même:

/ DEF;;;;;

Dans le champ Région , certains fournisseurs ont des points-virgules supplémentaires:

 ; | ;

Pour éviter une erreur lors de la lecture du tableau par votre application, vous devez désactiver la vérification du nombre attendu de champs ou remplacer l'enregistrement incorrect par le bon.

Nous allons écrire des données dans une table avec la structure suivante:

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

La plupart des régions des tableaux peuvent être identifiées sans ambiguïté par l'occurrence d'une sous-chaîne. Par exemple, toutes les entrées de la région Saratov contiennent la sous-chaîne Sarat .

Pour les régions qui ne peuvent être déterminées sans ambiguïté, une logique supplémentaire est nécessaire, à ce sujet ci-dessous.

Exemple 1:

  • Okrug autonome de Yamal-Nenets
  • Nenets Autonomous Okrug

L'Okrug autonome de Yamalo-Nenets fait partie de la région de Tioumen et l'Okrug autonome de Nenets fait partie de l'Arkhangelsk. Le problème de la détermination du code numérique de la région est que la sous-chaîne Nenets AO est entièrement incluse dans le Yamalo-Nenets AO . Cela signifie que deux régions correspondront à des entrées contenant la sous-chaîne de la zone autonome Nenets .

Pour résoudre ce problème, vous devez ajouter une vérification de l'absence d'une sous-chaîne (à l'exclusion de la sous-chaîne). En d'autres termes, l'Okrug autonome Yamalo-Nenets sera déterminé par l'entrée de Yamalo , et l'Okrug autonome Nenets par l'entrée de Nenets et l'absence de Yamalo .

Exemple 2:

  • aller. Krasnogorsk
  • Lyantor
  • Partza

Un exemple de régions non identifiées. Il existe de nombreux enregistrements de ce type dans les tableaux de Rossvyaz. Finder aide à découvrir que GO Krasnogorsk - région de Moscou, Lyantor - l'Okrug autonome de Khanty-Mansi et la colonie de Partz - la République de Mordovie. La solution est simple, la sous-chaîne souhaitée est convertie en un tableau de chaînes, et nous vérifierons l'occurrence dans une boucle. La sous-chaîne exclusive est également convertie en tableau.

Exemple 3:

  • District fédéral sibérien, District fédéral extrême-oriental
  • Territoire de Krasnoïarsk, République de Khakassie, Moscou, Saint-Pétersbourg
  • La fédération Russe

Dans cet exemple, il n'est pas possible d'identifier de manière unique une région. Vous pouvez choisir le plus peuplé des répertoriés ou attribuer un code spécial à ces enregistrements. En fait, tous les enregistrements qui correspondent à plusieurs régions sont les numéros 8-80 [0-9], numéros fédéraux pour lesquels les appels ne sont pas facturés. J'ai attribué des codes à ces enregistrements dans une plage de 200 à 210. Je ne pense pas qu'ils me seront jamais utiles.

Le code d'application se trouve ici .
Les fichiers exécutables pour les plates-formes Linux, MacOS et Windows se trouvent dans le répertoire bin (je n'ai pas testé l'application sur la plate-forme Windows). Fichier de

configuration Config.ymldoit se trouver dans le même répertoire que le fichier exécutable. Si vous le souhaitez, vous pouvez implémenter la prise en charge des indicateurs dans l'application pour indiquer le chemin d'accès à la configuration dans les arguments de la ligne de commande, les demandes d'extraction sont les bienvenues.

Fichier de configuration
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


Le champ data_source contient les chemins d'accès aux fichiers de la table Rossvyaz. Si le chemin commence par http , l'application chargera la table à l'aide du client Web intégré, sinon elle essaiera de trouver la table dans le système de fichiers.

Faites attention aux lignes commentées. Ce sont les chemins d'accès aux fichiers de table Rossvyaz stockés dans le référentiel. Si vous ne disposez pas de la connexion Internet la plus rapide, chaque lancement d'application téléchargera à nouveau ces fichiers. Pour une application plus rapide, décommentez ces lignes et commentez les hyperliens. Vous devrez peut-être exécuter l'application plus souvent que vous ne le pensez, je vous expliquerai plus tard pourquoi utiliser un exemple.

Champs exceptions et régionscontiennent des chemins d'accès aux fichiers avec corrections et une description des régions. À leur sujet un peu plus tard.

La section db décrit les paramètres de connexion au SGBD MySQL. Pour utiliser un autre SGBD, vous devez remplacer le pilote dans le code et le modèle de chaîne de connexion dans le constructeur de la structure de service

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)

Cependant, il n'y a rien de compliqué à ajouter un champ de type SGBD à la configuration et à ajouter la prise en charge de ces types à l'application, les pull-requests sont à nouveau les bienvenues.

Le fichier exceptions.yml contient une liste de correctifs. Toutes les correspondances de sous-chaînes dans exceptions.yml avant les deux-points trouvés dans les fichiers de table Rossvyaz seront remplacées par des sous-chaînes après les deux-points.

Le fichier regions.yml contient une liste de codes numériques de régions avec des tableaux d'occurrences et excluant les sous-chaînes utilisées pour déterminer une région spécifique. Si aucun enregistrement n'est trouvé pour un enregistrement ou s'il y a deux correspondances ou plus, cet enregistrement ira à la base de données, mais le champ région contiendra la valeur 0 (la région n'est pas définie).

Exemple de description de région
78:
  name: -
  contain:
    - 
    - 
    - .. 
  not_contain:
    -


Avant de démarrer l'application, créez une base de données avec le nom spécifié dans db.name et assurez-vous que l'utilisateur spécifié dans db.user dispose des autorisations de lecture et d'écriture.

Donc, nous avons tout configuré, il est temps de lancer l'application.

Veuillez noter qu'au démarrage, la table spécifiée dans le champ db.table du fichier de configuration sera effacée, soyez prudent.

./def2sql

S'il n'y a pas d'erreurs et d'avertissements, vous verrez une sortie similaire
correct records amount: 372324
inserted 372324 records


Sinon, vous verrez un rapport avec des erreurs et des avertissements.
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 \",   *  |  *  )"
    ]
}


Le rapport contient plusieurs sections.

unknown_regions - une liste d'enregistrements qui ne correspondent pas à des régions ou dont le nombre est supérieur à un;
false_records - liste des entrées invalides dans la table Rossvyaz;
avertissements - une liste des avertissements qui se sont produits pendant l'exécution de l'application;

Le rapport sous le dernier spoiler que j'ai reçu lors du lancement de l'application environ un mois après les dernières éditions de regions.yml .

Version obsolète de regions.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:
    -  ,  , . , . -


Pour vous débarrasser des avertissements et obtenir les codes de région pour tous les enregistrements, vous devez modifier regions.yml .

versions différentes de regions.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


Cerise sur le gâteau, je propose la macro de plan de numérotation Asterisk pour déterminer la région par numéro d'abonné:

[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})

Aux mêmes fins, vous pouvez utiliser le script Lua:
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(

Cependant, tout outil qui prend en charge les requêtes SELECT vers MySQL convient.

Profitez de votre administration.

More articles:


All Articles