VR-007.3 - доработка функционала для поддержки MySQL

[Booroondook Zubiler]

Не дождавшись от разработчика внятного ответа по доработкам, слепил кое-что самостоятельно.

Сначала пошел по неправильному пути - пытался переписать имеющиеся скрипты *.php под MySQL - причем, для отправки данных на внешний MySQL- сервер. В принципе, система заработала, но потом я подумал - а что будет, если вдруг разорвется сетевое подключение, и сервер БД станет недоступным? Тогда пропадут данные. Также возникло неудобство, связанное с тем, чтоб таблицы на MySQL-сервере должны быть созданы заранее, до включения в работу VR-007.3.

И я решил пойти другим путём, а именно: данные по-прежнему накапливаются и хранятся в локальных SQLite-базах, но после каждого INSERT'а дополнительно запись передается на MySQL-сервер.

Для этого потребовалось:

1) Установить на роутер (через админку OpenWRT пакеты:

libmariadbclient

mariadb-client

2) На MySQL-сервере создать таблицы migdate, energy_daily и energy_y по образу и подобию одноименных таблиц SQLite, штатно используемых роутером (рекомендую для rowid использовать INT,  для остальных полей CHAR(20), а не TEXT). Триггеры создавать не надо. Также при необходимости создать пользователя с соответствующими правами и возможностью подключения к серверу извне.

3) На роутере в папке /usr/sbin (или любой другой, входящей в PATH) создать три скрипта:

sqlite2mysql_migdate.sh

#####

#!/bin/ash

sqlite3 -csv /www/pages/mydb.db "select * from migdate where rowid = (select max (rowid) from migdate);" > /tmp/migdate.csv

if [ -f /tmp/migdate.csv ]; then 

mysql -h <server> <database> -u<user> -p<password> -e "LOAD DATA LOCAL INFILE '/tmp/migdate.csv' INTO TABLE <database>.migdate FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

fi

#####

sqlite2mysql_energy_daily.sh 

#####

#!/bin/ash

sqlite3 -csv /www/pages/energy.db "select * from energy_daily where rowid = (select max (rowid) from energy_daily);" > /tmp/energy_daily.csv

if [ -f /tmp/energy_daily.csv ]; then 

mysql -h <server> <database> -u<user> -p<password>  -e "LOAD DATA LOCAL INFILE '/tmp/energy_daily.csv' INTO TABLE <database>.energy_daily FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

fi

#####

sqlite2mysql_energy_y.sh 

#####

#!/bin/ash

sqlite3 -csv /www/pages/energy_y.db "select * from energy_y where rowid = (select max (rowid) from energy_y);" > /tmp/energy_y.csv

if [ -f /tmp/energy_y.csv ]; then 

mysql -h <server> <database> -u<user> -p<password>   -e "DELETE FROM <database>.energy_y;"

mysql -h 192.168.0.25 mercury -umercury -pmercury -e "LOAD DATA LOCAL INFILE '/tmp/energy_y.csv' INTO TABLE <database>.energy_y FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

fi

#####

4) На роутере в скрипте /www/pages/loop.php находим строки, содержащие SQL-запросы, содержащие текст... 

("INSERT INTO 

... и под каждой такой строкой добавляем вызов скрипта, соответствующего таблице, используемой в запросе.

Например, под строкой...

$db3->saveEnergyD("INSERT INTO energy_daily (rowid, time, E11,E12,E13,E14,E21,E22,E23,E24,E31,E32,E33,E34,E41,E42,E43,E44) VALUES (NULL, '$date_yest_str', '$en_Ap_t1_yest','$en_Am_t1_yest','$en_Rp_t1_yest','$en_Rm_t1_yest','$en_Ap_t2_yest','$en_Am_t2_yest','$en_Rp_t2_yest','$en_Rm_t2_yest','$en_Ap_t3_yest','$en_Am_t3_yest','$en_Rp_t3_yest','$en_Rm_t3_yest','$en_Ap_t4_yest','$en_Am_t4_yest','$en_Rp_t4_yest','$en_Rm_t4_yest')");

дописываем строку:

$myVar= shell_exec('/usr/sbin/sqlite2mysql_energy_daily.sh');

Таких "добавок" нужно сделать общим счетом три штуки - по одной для каждой из трех таблиц.

Вот, собственно говоря, и всё.

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

P.S. Отдельно скажу о триггерах. По оригинальной задумке разработчика количество записей в таблице migdate  ограничивается числом 1440 - 'это соответствует одним суткам работы при условии, что счетчик опрашивается каждую минуту, и все опросы оказываются удачными. Триггер работает так - при появлении 1441-й записи удаляется самая старая. Конечная цель такого ограничения - избежать "раздутия" базы данных SQLite до размера, заполняющего все место на файловой системе роутера.

То есть, это ограничение вполне оправдано. Но для MySQL- сервера, работающего обычно на "большом" компьютере и оснащенном дисками размером в несколько терабайт, такое ограничение не имеетс смысла. Поэтому я не стал создавать в MySQL-таблице migdate триггер, копирующий по функционалу оригинальный.

Что же касается триггера в таблице energy_daily, то там смысл немного другой - там по такому же (как в migdate) алгоритму выдерживается ограничение на 31 запись. Другими словами, сохраняются записи только за последний месяц (не календарный, а просто за 3 день назад от текущей даты). Пока у меня на MySQL такого триггера нет - но и записей накопилось всего 12, т.к. я приступил к эксплуатации VR007.3 всего пару недель назад. Посмотрим, что будет дальше.

P.P.S. Зачем все это нужно? Да все очень просто - разработчик дал нам красивый интерфейс для просмотра данных (он действительно красивый - без шуток), но не дал никакого инструментария по выгрузке данных, помещению их куда-нибудь для обработки и т.д., и т.п. Когда я ему написал об этом, он мне ответил знаете, что? Он сказал: выгружайте данные на Narodmon.ru, а там есть экспорт в Excel и CSV. Я не буду комментировать эту рекомендацию.

Зато теперь я могу подключиться к MySQL-базе хоть через Excel, хоть через MS Access, хоть еще через какую-нибудь оболочку для работы с БД. могу также сам написать программу для обработки данных и так далее.

Надеюсь, эта информация будет кому-нибудь полезна.

[Юрий Макаров]

да, выгрузки данных очень не хватает....  у меня 17 учетов меркурий 230 и выгружать на narodmon это ужас. 

вторник, 1 декабря 2020 г. в 17:00:50 UTC+3, booro...@gmail.com: