Get best of the week

Exportando o Plano de Numeração da Agência Federal de Comunicações para um Banco de Dados Relacional

A Agência Federal de Comunicações atualiza regularmente o plano de numeração disponível ao público . Se você usar esse plano para determinar a região ou o provedor de assinantes em seu plano de discagem, provavelmente estará interessado na relevância dessas informações. À primeira vista, não há nada complicado ao escrever um aplicativo que baixa, processa e envia dados para o banco de dados; no entanto, quando você inicia a implementação, inevitavelmente se depara com armadilhas, das quais falarei agora.

O plano de discagem consiste em quatro arquivos tabulares no formato csv:

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

vezes, os URLs DEF-9xx.csv mudam.

A estrutura de todas as tabelas é a mesma:

/ DEF;;;;;

No campo Região , alguns provedores possuem ponto e vírgula extra:

 ; | ;

Para evitar um erro ao ler a tabela pelo seu aplicativo, é necessário desativar a verificação do número esperado de campos ou substituir o registro incorreto pelo correto.

Vamos gravar dados em uma tabela com a seguinte estrutura:

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

A maioria das regiões nas tabelas pode ser identificada sem ambiguidade pela ocorrência de uma substring. Por exemplo, todas as entradas para a região de Saratov contêm a substring Sarat .

Para as regiões que não podem ser determinadas sem ambiguidade, é necessária lógica adicional, sobre isso abaixo.

Exemplo 1:

  • Okrug autônomo de Yamal-Nenets
  • Okenug autônomo de Nenets

O Okrug Autônomo de Yamalo-Nenets faz parte da Região de Tyumen e o Okrug Autônomo de Nenets faz parte de Arkhangelsk. O problema de determinar o código numérico da região é que a substring Nenets AO está totalmente incluída no Yamalo-Nenets AO . Isso significa que duas regiões corresponderão às entradas que contêm a substring da área autônoma de Nenets .

Para resolver esse problema, você precisa adicionar uma verificação da ausência de uma substring (excluindo substring). Em outras palavras, o Okrug Autônomo de Yamalo-Nenets será determinado pela entrada de Yamalo , e o Okrug Autônomo de Nenets pela entrada de Nenets e pela ausência de Yamalo .

Exemplo 2:

  • vai. Krasnogorsk
  • Lyantor
  • Partza

Um exemplo de regiões não identificadas. Existem muitos desses registros nas tabelas de Rossvyaz. O Finder ajuda a descobrir que o GO Krasnogorsk - região de Moscou, Lyantor - o Okrug autônomo de Khanty-Mansi e o assentamento de Partz - a República da Mordóvia. A solução é simples, a substring desejada é convertida em uma matriz de strings e verificaremos a ocorrência em um loop. A substring exclusiva também é convertida em uma matriz.

Exemplo 3:

  • Distrito Federal da Sibéria, Distrito Federal do Extremo Oriente
  • Território de Krasnoyarsk, Republic of Khakassia, Moscow, São Petersburgo
  • A Federação Russa

Neste exemplo, não é possível identificar exclusivamente uma região. Você pode escolher o mais preenchido dos listados ou atribuir um código especial a esses registros. De fato, todos os registros que correspondem a várias regiões são os números 8-80 [0-9], números federais pelos quais as chamadas não são cobradas. Atribuai códigos a esses registros no intervalo de 200 a 210. Não acho que eles jamais serão úteis para mim.

O código do aplicativo pode ser encontrado aqui .
Arquivos executáveis ​​para plataformas linux, macos e windows estão localizados no diretório bin (não testei o aplicativo na plataforma windows). Arquivo de

configuração Config.ymldeve estar no mesmo diretório que o arquivo executável. Se desejar, você pode implementar o suporte a sinalizadores no aplicativo para indicar o caminho para a configuração nos argumentos da linha de comando; solicitações de recebimento são bem-vindas.

Arquivo de configuração
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


O campo data_source contém os caminhos para os arquivos de tabela Rossvyaz. Se o caminho começar com http , o aplicativo carregará a tabela usando o Web client incorporado, caso contrário, tentará encontrar a tabela no sistema de arquivos.

Preste atenção às linhas comentadas. Esses são os caminhos para os arquivos de tabela Rossvyaz armazenados no repositório. Se você não tiver a conexão mais rápida com a Internet, cada inicialização do aplicativo fará o download desses arquivos novamente. Para uma aplicação mais rápida, remova o comentário dessas linhas e comente os hiperlinks. Pode ser necessário executar o aplicativo com mais frequência do que você pensa, depois explicarei por que usar um exemplo. Exceções de

campos e regiõescontém caminhos para arquivos com correções e uma descrição das regiões. Sobre eles um pouco mais tarde.

A seção db descreve os parâmetros para conectar-se ao MySQL DBMS. Para usar outro DBMS, você precisa substituir o driver no código e o modelo de cadeia de conexão no construtor da estrutura de Serviço

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)

No entanto, não há nada complicado ao adicionar um campo do tipo DBMS à configuração e ao adicionar suporte para esses tipos ao aplicativo, solicitações de recebimento são bem-vindas novamente.

O arquivo exceptions.yml contém uma lista de correções. Todas as correspondências de substrings em exceptions.yml antes dos dois pontos encontrados nos arquivos da tabela Rossvyaz serão substituídas por substrings após os dois pontos.

O arquivo region.yml contém uma lista de códigos numéricos de regiões com matrizes de ocorrências e excluindo substrings usados ​​para determinar uma região específica. Se nenhum registro for encontrado para nenhum registro ou houver duas ou mais correspondências, esse registro irá para o banco de dados, mas o campo região conterá o valor 0 (a região não está definida).

Exemplo de descrição da região
78:
  name: -
  contain:
    - 
    - 
    - .. 
  not_contain:
    -


Antes de iniciar o aplicativo, crie um banco de dados com o nome especificado em db.name e verifique se o usuário especificado em db.user possui permissões de leitura e gravação.

Então, nós configuramos tudo, é hora de iniciar o aplicativo.

Observe que durante a inicialização, a tabela especificada no campo db.table do arquivo de configuração será limpa, tenha cuidado.

./def2sql

Se não houver erros e avisos, você verá uma saída semelhante
correct records amount: 372324
inserted 372324 records


Caso contrário, você verá um relatório com erros e avisos.
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 \",   *  |  *  )"
    ]
}


O relatório contém várias seções.

unknown_regions - uma lista de registros que não correspondem às regiões ou seu número é mais de um;
wrong_records - lista de entradas inválidas na tabela Rossvyaz;
avisos - uma lista de avisos que ocorreram enquanto o aplicativo estava em execução;

O relatório do último spoiler que recebi durante o lançamento do aplicativo cerca de um mês após as últimas edições do region.yml .

Versão desatualizada de 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:
    -  ,  , . , . -


Para se livrar de avisos e obter códigos de região para todos os registros, você precisa editar o region.yml .

versões diff de 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


Como uma cereja no bolo, ofereço a macro de plano de discagem do Asterisk para determinar a região pelo número de assinante:

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

Para os mesmos fins, você pode usar o 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(

No entanto, qualquer ferramenta que suporte consultas SELECT ao MySQL é adequada.

Aproveite sua administração.

More articles:


All Articles