📱 ⚪️ 👨🏽‍🔬 将联邦通信局编号计划导出到关系数据库 😫 🎻 🐀

联邦通讯局会定期更新可公开使用的编号方案。如果使用此计划来确定拨号计划中的地区或订户提供者，则您最有可能对该信息的相关性感兴趣。乍一看，编写下载，处理并将数据发送到数据库的应用程序并没有什么复杂的事情，但是当您开始实现它时，不可避免地会遇到陷阱，我现在将讨论这些陷阱。

拨号计划由csv格式的四个表格文件组成：

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

URL有时会更改。

所有表的结构都相同：

/ DEF;;;;;

在“ 区域”字段中，某些提供程序具有额外的分号：

 ; | ;

为避免应用程序读取表时出错，您需要禁用检查期望的字段数或将不正确的记录替换为正确的记录。

我们将数据写入具有以下结构的表中：

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

表中的大多数区域可以通过出现子字符串来明确标识。例如，萨拉托夫地区的所有条目都包含子字符串Sarat。

对于无法明确确定的区域，需要以下附加逻辑。

范例1：

亚马尔·涅涅茨自治区
涅涅茨自治区

Yamalo-Nenets自治区属于秋明州地区，Nenets自治区属于阿尔汉格尔斯克州。确定该区域的数字代码的问题是子串Nenets AO完全包含在Yamalo-Nenets AO中。这意味着两个区域将对应于包含Nenets自治区的子字符串的条目。

要解决此问题，您需要添加检查以检查是否缺少子字符串（不包括子字符串）。换句话说，Yamalo-Nenets自治Okrug将由Yamalo的加入决定，而Nenets自治Okrug将由Nenets的加入和Yamalo的缺席决定。

范例2：

走。克拉斯诺戈尔斯克
莱昂托尔
帕萨

身份不明区域的示例。Rossvyaz表中有许多这样的记录。Finder有助于找出GO 克拉斯诺戈尔斯克 -莫斯科地区，良托尔 -汉特-曼西民族自治区，以及Partz结算 -莫尔多瓦共和国。解决方案很简单，将所需的子字符串转换为字符串数组，然后我们将在循环中检查它的出现。独占子字符串也将转换为数组。

范例3：

西伯利亚联邦区，远东联邦区
克拉斯诺亚尔斯克地区，哈卡斯共和国，莫斯科，圣彼得堡
俄罗斯联邦

在该示例中，不可能唯一地标识区域。您可以选择列表中人口最多的记录，或为这些记录分配特殊代码。实际上，对应于多个区域的所有记录都是号码8-80 [0-9]，这是不收取通话费用的联邦号码。我为此类记录分配的代码范围在200到210之间。我认为它们对我没有用。

可以在此处找到应用程序代码。
linux，macos和Windows平台的可执行文件位于bin目录中（我没有在Windows平台上测试该应用程序）。Config.yml

配置文件必须与可执行文件位于同一目录中。如果愿意，可以在应用程序中实现标志支持，以在命令行参数中指示配置的路径，欢迎请求拉取。

配置文件

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

data_source 字段包含Rossvyaz表文件的路径。如果路径以http开头，则应用程序将使用内置的Web客户端加载表，否则它将尝试在文件系统中查找表。

注意注释掉的行。这些是存储在存储库中的Rossvyaz表文件的路径。如果没有最快的Internet连接，每次启动应用程序都会再次下载这些文件。对于更快的应用程序，请取消注释这些行并注释掉超链接。您可能需要比您想象的更频繁地运行该应用程序，稍后我将使用一个示例解释原因。

字段例外和区域包含具有更正和区域说明的文件路径。稍后再介绍他们。db

部分描述了用于连接到MySQL DBMS的参数。要使用另一个DBMS，需要在代码中替换驱动程序，并在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)

但是，在配置中添加DBMS类型字段并在应用程序中添加对这些类型的支持并不复杂，再次欢迎拉取请求。exceptions.yml

文件包含修复程序列表。在Rossvyaz的表文件中找到的，冒号之前的exceptions.yml中所有子字符串匹配项都将由冒号之后的子字符串替换。regions.yml 文件包含区域的数字代码列表，这些区域具有事件数组，并且不包括用于确定特定区域的子字符串。如果对于任何记录都找不到任何区域的匹配项，或者有两个或多个匹配项，那么该记录将进入数据库，但是region字段将包含值0（未定义区域）。

区域描述示例

78:
  name: -
  contain:
    - 
    - 
    - .. 
  not_contain:
    -

启动应用程序之前，请使用db.name中指定的名称创建一个数据库，并确保db.user中指定的用户具有读写权限。

因此，我们已完成所有设置，该启动该应用程序了。

请注意，在启动过程中，将清除配置文件db.table字段中指定的表，请小心。

./def2sql

如果没有错误和警告，您将看到类似的输出

correct records amount: 372324
inserted 372324 records

否则，您将看到带有错误和警告的报告。

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 \",   *  |  *  )"
    ]
}

该报告包含几个部分。

unknown_regions-与区域不对应的记录列表，或者它们的数量不止一个；
wrong_records -在Rossvyaz酒店表无效条目列表;
警告 -应用程序运行时发生的警告列表；

在启动最新版本的region.yml大约一个月后，我在应用程序启动期间收到的最后一个破坏者下的报告。

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:
    -  ,  , . , . -

要摆脱警告并获取所有记录的区域代码，您需要编辑region.yml。

区域的差异版本

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

沃伊拉

correct records amount: 372324
inserted 372324 records

作为蛋糕上的樱桃，我提供了Asterisk Dialplan宏，以通过订户号确定区域：

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

出于相同的目的，您可以使用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(

但是，任何支持对MySQL进行SELECT查询的工具都适用。

享受您的管理。

将联邦通信局编号计划导出到关系数据库

More articles: