Назначение

Платформа (некоторые сводные наработки для платформы - базы данных на внешнем MS SQL Server-е и набор клиентских утилит) по:

• авиационному процессингу,
• авиационной диспетчеризации,
• авиационной телеметрии,
• авиационной телематике.

Описание

В объем данного проекта входит:

• разработка баз данных (справочные, рабочие и оперативные данные) на внешнем сервере СУБД,
• администрирование сервера СУБД,
• разработка прикладного программного обеспечения - утилит и графических оболочек (в части UX/UI) - для работы с ними,
• загрузка рабочих и оперативных данных с помощью утилит (графических диалоговых формочек и в командной строке),
• правка справочных данных с помощью графических оболочек (графических диалоговых формочек),
• первичная аналитика баз данных,
• предоставление способа взаимодействия другим проектам с помощью API-шек.

Справочные данные:

• объекты (аэропорты, аэродромы, авиабазы, вертодромы, взлетные полосы и хелипады),
• авиакомпании,
• летательные аппараты

использовались из источников:

• http://apinfo.ru
• http://openflights.org
• http://www1.ourairport.com
• http://planelist.net
• http://www.flightradar24.com
• https://www.jetphotos.com

Рабочие данные

загружаются с https://www.transtats.bts.gov/DL_SelectFields.asp?gnoyr_VQ=FGJ&QO_fu146_anzr=b0-gvzr

Оперативные данные

загружаются асинхронно:

• с сайта http://www.flightradar24.com на API-шках и по ВЭБ-хукам (надо покупать и оплачивать токен),
• с первичного оборудования.

Рабочие и оперативные данные загружаются без учета хронологии.

Структура хранилища с базами данных

• Снежинка - в центре таблицы с рабочими и оперативными данными (далее по тексту данными), с краев - таблицы со справочными данными (далее по тексту словарями). Таблица летательных аппаратов гибридная и содержит поля, которые правятся только вручную и которые заполняются в процессе загрузки данных.

Модель восстановления баз данных - Полная, так как предусматривается одновременная работа множества клиентов и в хранимых процедурах используются помеченные транзакции. Обслуживание баз данных (проверка целостности, обслуживание индексов, бэкапы) делается обычным способом - на время все действия клиентов ставим на паузу до уведомления о возобновлении работы.

Права пользователей на доступ к базам данных соответсвуют их учеткам Windows на сервере СУБД.

Инфраструктура:

• сервер СУБД,
• файловый сервер,
• терминальный сервер - работают по учеткам Windows без контроллера домена.

Работа клиентов возможна в локальной подсети или из внешней сети через рабочий стол терминального сервера по RDP.

Прикладное программное обеспечение:

• хранится на файловом сервере,
• используется только в оригинале,
• дорабатывается в процессе эксплуатации (в части CI/CD) без уведомления пользователей.

Выпуск релизов и пакетов не предусматривается. Программное обеспечение на распространяется, хранится на инфраструктуре серверов.

(первоначальное, август 2016-го года) [Рисунок 1]

Поправки:

• С tk, ttk переделали на pyQt,
• Gtk применялась в Linux-е (библиотека pyGTK на Windows сейчас пока не ставится),
• Linux может использоваться на клиентах для правки справочников (выходит из употребления на клиентах из-за запаздывания в написании и функционале драйверов),
• (*) Сайт на WEB-сервере разрабатывается отдельно и в объем данного проекта не входит.

Хранение информации в файлах различных типов и их использование [Рисунок 2]

Файлы, открываемые только в своем ПО, желательно не использовать.

Отчеты по базам данных из Management Studio

История изменений и улучшений в проекте

Версия 3 (устарела):

• были ошибки в загрузке данных (в объектах перепутали коды IATA и ICAO) - исправили, но база данных с ошибкой, оставили как есть,
• образец наработок,
• источник первичных данных и для анализа.

Версии 4, 5 (теперь не актуальны, медленные, сильно загружает процессор сервера СУБД):

• исправлены ошибки версии 3,
• версия 5 предназначена для проверки правильности транзакций загрузки данных в версии 4 - результаты сошлись, все правильно,
• добавили обход взаимоблокировок с помощью обработки исключения и нарастающей временной задержке,
• у летательных аппаратов регистрация и авиакомпании не указываются,
• авиарейсы плюсуются без даты.

Версия 6 (текущая, теперь не актуальна):

• авиарейсы вставляются по датам,
• у летательных аппаратов обновляется регистрация и авиакомпания-оператор крайнего авиарейса,
• удачно проиндексировали поля в таблицах (загрузка ускорилась в 25 ... 35 раз, простои на взаимоблокировках уменьшились).

Версия 7 (разработка пока на паузе, наработки переносятся в версию 8):

• у летательных аппаратов по принципу медленно меняющейся размерности сделана хронология регистраций (бизнес-ключ - сочетание заводских номеров, суррогатный ключ - регистрации по периодам использования соответственно),
• а также сделана хронология авиакомпании-оператора, авиакомпании-владельца, авиакомпании-арендатора, авиакомпании-лизингодателя (бизнес-ключ - тот же, суррогатные ключи - авиакомпании по периодам использования соответственно) через промежуточные справочные таблицы.

Версия 8 (в процессе разработки):

• у летательных аппаратов хронология регистраций и авиакомпаний пишется в XML-ных полях (если в базе данных у летательных аппаратов нет регистрации, используем функционал версии 7 для временного хранения недозагруженных данных, подробнее см. "Загрузка данных" ниже по тексту),
• у авиакомпаний список аэропортов-хабов сделан XML-ным полем,
• у объекта описание сделано XML-ным полем (структура разделов и подразделов),
• убрали отдельную базу данных по летательным аппаратам,
• добавили отношения, индексы, XSD-схемы, каскадные правила на удаления и обновления,
• подняли очередь и службу Service Broker (см. https://docs.microsoft.com/ru-ru/sql/database-engine/configure-windows/sql-server-service-broker?view=sql-server-ver15 , https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/monitor-database-deadlocks ), чтобы не пользоваться временными задержками для ухода от взаимоблокировок, но функционала SQL-ных скриптов внутри хранимых процедур не хватает, ищем решение как разнести сложность, пробуем разные серверные курсоры внутри хранимых процедур (см. https://docs.microsoft.com/ru-ru/sql/relational-databases/native-client-odbc-cursors/implementation/odbc-cursor-library?view=sql-server-ver15 , https://docs.microsoft.com/ru-ru/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-ver15 ),
• для экспериментов используем не тестовую базу, а только тестовые таблицы (делаем трансфер двух таблиц соответственно в две тестовые таблицы, можно перенести отдельной файловой группой на отдельный HDD).

Справочники и данные

Летательный аппарат однозначно определяется сочетанием его заводских номеров LN, MSN, SN, CN в зависимости от фирмы-изготовителя.

Авиакомпания однозначно определяется сочетанием кодов IATA и ICAO.

Регистрационный номер летательного аппарата (англ. Tail Number, далее по тексту регистрация) с течением времени может последовательно несколько раз переходить от одного летательного аппарата к другому.

В данных используется:

• регистрация,
• дата авиарейса,
• обозначение авиарейса,
• код IATA объекта вылета,
• код IATA объекта прилета,
• число ходок и остальные данные в зависимости от источника.

Считаем, что любая авиакомпания (в том числе неизвестный частный владелец IATA - пусто, ICAO - None) может быть:

• оператором,
• владельцем (арендатором),
• арендодателем,
• лизингодателем.

Считаем, что авиарейс может выполнять:

• собственно авиакомпания-владелец (нет оператора, нет аренды, нет лизинга),
• авиакомпания-оператор на летательном аппарате авиакомпании-владельца (нет аренды и нет лизинга),
• авиакомпания-оператор на летательном аппарате авиакомпании-арендатора (нет лизинга),
• авиакомпания-оператор на летательном аппарате авиакомпании-арендатора, арендодатель которой взял его в лизинг.

Загрузка данных:

• обновляет XML-ные поля авиакомпаний XML(CONTENT dbo.XSD-схема) в таблице летательных аппаратов, которые подаются на вход хранимой процедуры через XSD-схему и парсятся как SAX, используя комплектный функционал XPath & XQuery (см. http://xmlhack.ru/texts/03/xquery/what.is.xquery.html ) и спецификацию SQL/XML,
• вставляет строки в таблице объектов,
• вставляет строки в таблице маршрутов,
• вставляет или обновляет строки в таблице авиарейсов

следующим образом:

• парсим в таблице летательных аппаратов XML-ное поле регистрации (первичный XML-ный индекс),
• выбираем строку, у которой временной диапазон с указанной регистрацией соответствует дате авиарейса,
• если строки с подходящим временным периодом не найдено (это может случиться, если дата авиарейса новее, чем все временные диапазоны с указанной регистрацией), данные пишем во временную таблицу по образцу 7-й версии (см. "История изменений и улучшений в базах данных" в разделе "Версия 7"),
• формируем список соответствия регистраций и дат авиарейсов например в виде отдельной таблицы, которые надо восполнить в базе данных,
• после восполнения переносим данные из временной таблицы к остальным данным,
• если в XML-ном поле авиакомпании нет, вставляем относительно корневого тэга новый тэг с указанным аттрибутом-идентификатором авиакомпании (как внешний ключ авиакомпании) и новый подтэг с датой авиарейса (как начало нового временного диапазона),
• если в XML-ном поле авиакомпания есть, к существующим подтэгам в соответствии с хронологией добавляем новый подтэг с датой авиарейса,
• подтэги дополнительно группируем по отдельному алгоритму (см. ниже "Группировка подтэгов ..."),
• если регистрация в данных не указана, то в таблицу летательных аппаратов изменения не вносим,
• если объекта, указанного в данных нет, вставляем новый объект,
• если маршрута между объектами, указанными в данных, еще нет, вставляем новый маршрут,
• если авиарейса с параметрами, указанными в данных нет, вставляем новый авиарейс,
• если авиарейс с параметрами, указанными в данных уже есть, плюсуем количество этого авиарейса.

Группировка подтэгов с датами авиарейсов (пока в разработке):

• группируем подтэги программно при изменении,
• часть API-шек пишем на SQLAlchemy под MS SQL Server (см. https://docs.sqlalchemy.org/en/14/dialects/mssql.html ),
• во всех его строках переставляем подтэги по хронологии и отбрасываем промежуточные подтэги,
• пробуем вручную SQL-ные запросы со вставками на XPath & XQuery,
• сохраняем вставки на XPath & XQuery файлами запросов *.xq,
• вызываем вставки в запросах программно по URL,
• вызываем вручную из диалога открытия файла,
• пробуем хранимые процедуры с программными вызовами вставки,
• вставляем старую XML-ную базу летательного аппарата (не более 2 Гбайт на каждую согласно документации msdn.com) в XML-ное поле XML(CONTENT dbo.XSD-схема) его строки и парсим его как DOM (см. https://stackoverflow.com/questions/192907/xml-parsing-elementtree-vs-sax-and-dom , https://stackoverflow.com/questions/1890923/xpath-to-fetch-sql-xml-value , https://stackoverflow.com/questions/43848456/t-sql-xquery-value-of-attribute-y-where-attribute-x-is-known ) внутри скрипта на Python-е Saxon-ом (см. https://www.saxonica.com/technology/xslt-and-xquery.xml библиотеки libxml2, libxslt).

Недостаток хранимой процедуры - не возвращает в скрипты на Python-е достоверный результат своей работы: получилось, не получилось с указанием причины (см. раздел "Возврат данных с помощью кода возврата" на https://docs.microsoft.com/ru-ru/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-ver15 ). Недостаток XSD-схемы - тот же и тот, что она пропускает все или не пропускает ничего.

Собираем XML-ные поля, определяемся с их структурой

В XML-ном поле регистрации может быть только один тэг с аттрибутом-строкой, обозначающим какую-то одну регистрацию. Начало временного диапазона со следующей регистрацией считаем окончанием временного диапазона использования с предыдущей. Заполняем только ручным вводом.

В каждом XML-ном поле авиакомпании может быть только один тэг с аттрибутом-идентификатором, указывающим на какую-то одну авиакомпанию. Начало временного диапазона в следующей авиакомпании считаем окончанием временного диапазона использования в предыдущей. Аттрибуты-идентификаторы в разных XML-ных полях могут указывать на одну и ту же авиакомпанию (см. выше 2 абзаца "Считаем, что ...").

Собираем XSD-схему (устарело, как пример)

В начале XSD-схемы объявляются:

• типовые и ссылочные схемы,
• типовые, ссылочные и пользовательские пространства имен (см. https://www.w3.org/TR/xmlschema11-1 ),
• типовые, ссылочные и пользовательские типы данных XPath & XQuery.

Далее в XSD-схеме определяются элементы, каждый под своим именем (см. https://www.w3schools.com/xml/schema_simple.asp ).

Элемент генерируется из XML-ного файла внутри Management Studio или с помощью XSLT-преобразования (см. https://docs.microsoft.com/ru-ru/visualstudio/xml-tools/how-to-execute-an-xslt-transformation-from-the-xml-editor?view=vs-2022 ) и вставляется в соответствии с порядком просмотра XML-ных полей. В сложных случаях можно пользоваться Schematron-ом (см. https://www.schematron.com ). Имя корневого тэга XML-ного поля соответствует имени элемента XSD-схемы. Исходный текст XSD-схемы вставляется в SQL-ный скрипт ее привязки к базе данных (надо найти способ не вставлять исходник схемы через буфер обмена, а выбирать ее в диалоге открытия файла или дать URL до нее file:///P:/...). К базе данных можно привязать несколько XSD-схем. К каждому XML-ному полю можно привязать свою XSD-схему.

Привязываем XSD-схему к базе данных

Поправки по терминологии:

• Коллекцию схем XML в Management Studio точнее называть XSD-схемой.
• Созданием схемы в SQL-ном скрипте правильнее называть привязкой XSD-схемы, потому что она уже собрана и сохранена файлом типа *.xsd.
• DTD-схемы и XDR-схемы кратко упомянуты в msdn.com (см. https://docs.microsoft.com/ru-ru/visualstudio/xml-tools/how-to-create-an-xml-schema-from-an-xml-document?view=vs-2022 ), но уже не применяются.

Прочие наработки

XML-ные поля пропускаются через XSD-схему:

• программно на входе хранимой процедуры,
• программно при вставке или при обновлении строки,
• при редактировании XML-ного файла с привязкой к XSD-схеме внутри Management Studio,
• при ручном вводе XML-ного поля при вызове хранимой процедуры внутри Management Studio.

Так как сервер СУБД не дает обратные вызовы (вэб-хуки или программные прерывания) клиентам на повторную попытку, если запрашиваемые данные пока заняты, то уход от взаимоблокировок при загрузке данных с нескольких внешних клиентов на сервер СУБД выполняется обертыванием попытки запроса в обработку исключения внутри тела цикла с нарастающей задержкой по времени. Выход из цикла - получение результата запроса или превышение определенного максимума попыток.

Без обратных вызовов пока не получается эффективно задействовать внешний сервер СУБД. Транзакции сделаны короткими, но между ними все перезапрашивается снова, так как данные изменяются другими клиентами. Потери времени на задержках частично уменьшены путем уточнения уровня изоляции транзакции в зависимости от действия. Число перезапросов и их перенос по времени пишутся в журнал загрузки рабочих данных (см. LogReport_DBNew6.txt в папке проекта на сервере), ошибки дозаписи в него - в журнал ошибок (см. ErrorLog.txt там же). Начальное значение задержки по времени и шаг ее приращения подобран экспериментально по результатам нагрузочных тестов осенью 2019-го года и зависит от вычислительных характеристик сервера СУБД. При 2-х кратном увеличении количества клиентов задержки увеличиваются на 15 ... 20 %, а нагрузка на сервер СУБД (процессор, HDD) уменьшается на 25 ... 35 % благодаря удачно проиндексированным полям в таблицах. Траффик по локальной подсети увеличивается из-за перезапросов. Пропускная способность локальной подсети достаточная.

Каждый клиент использует непрерывное подключение с несколькими клиентскими статическими однопроходными непрокручиваемыми API-курсорами ODBC. При вызове хранимой процедуры используются серверные курсоры. Хранимые процедуры применяются мало, потому что по мере усложнения прикладного функционала выполнить его только средствами SQL сложно (бедность типов данных и синтаксиса, сложность передачи и возврата составных типов данных, пока нет способа возврата результата и причины несработки). Уровни изоляции транзакции курсоров уточнены и проверены под нагрузкой на 4-х тестовых базах данных летом и осенью 2019-го года.

Для обхода попадания на вложенную обработку исключений на клиентах:

• установить или обновить Драйвер ODBC для MS SQL (дистрибутив версии 17 и руководство см. на сервере в папке Q:\M$_Windows\SQL_Server\Driver ODBC for SQL Server),
• поднять Системный DSN в источниках данных ODBC (см. Подключение к БД через системный DSN на сервере в папке проекта ..\SQL & XML (XPath & XQuery XSD XDR XSLT)).

Контроль подключения и его восстановление при разрыве например, при:

• плохом контакте на коннекторах,
• через Wi-Fi или через мобильный Интернет не предусматривается.

Объемы доработок на клиентах

Выборочно поставить клиентам для аналитики:

• ORACLE SQL Developer (запускаемый файл sqldeveloper см. на сервере в папке Q:\Oracle\Oracle SQL Developer\Oracle SQL Developer 21.4.3\sqldeveloper-21.4.3.063.0100-x64, ключи и лицензии не требуются) и его плагин под MS SQL Server (см. на сервере в папке Q:\Oracle\Oracle SQL Developer\Plug-ins),
• PL SQL Developer (дистрибутив см. на сервере в папке Q:\M$_Windows\PL SQL Developer\PL SQL DEVELOPER 14.0.6.1988 x86 x64 2020),
• Management Studio (дистрибутив см. на сервере в папке Q:\M$_Windows\SQL_Server\SQL Server Management Studio).

Сделать графическую формочку для правки свойств летательных аппаратов и уточнить набор виджетов на ней, ссылаться на их фото на https://www.jetphotos.com (присутствуют немодерирруемые несоответствия).

Добавить на графической формочке свойтсв авиакомпаний виджеты и ссылки просмотра финансовой и юридической информации из надежной онлайн базы.

Добавить на графической формочке свойств объектов:

• поиск по названию объекта в выпадающем списке с автодополнением из уже имеющихся названий объектов в базе данных,
• добавить виджеты на вкладке ВПП (широта, долгота, абсолютная отметка, длина, ширина, покрытие полос, оснащение системой сближения и посадки и т. д.),
• ссылку по объекту на статью из WikiPedia.org,
• виждеты выбора страны, области (графства, штата, региона), города, района города из надежной онлайн базы на ее API-шках в формате XML (как простой пример см. https://htmlweb.ru/geo/api_get_data.php , надо зарегистрироваться, купить и оплачивать API-ключ согласно тарифа https://htmlweb.ru/user/tariffs.php для каждого клиента, а также см. https://www.maxmind.com/en/worldcities , http://www.geonames.org , http://netload.biz/2011/01/24/geoip3 , https://pear.php.net/manual/en/package.webservices.services-geonames.examples.php и аналогичные), значение подтэга wiki вынести отдельно гиперссылкой, чтобы открывать статью из WikiPedia.org,
• виджеты и ссылки для просмотра свойств вышеперечисленных географических объектов.

Сделать графическую формочку для правки свойств альянсов (или делать это внутри Management Studio). Добавить ссылку на каждый из WikiPedia.org.

На сайте (см. (*) выше) показать маршруты в виде их профилей на топологии с привязкой к карте на https://www.google.com/maps , опираясь на аналитику баз данных.

Остальные указания и замечания см.:

• в исходниках по тэгам todo и fixme,
• на https://github.com/tsv19su254052/LoadWorkDataAirFlightsDBNew/issues ,
• комментарии в исходниках.