OCS-NG Inventory 2.x: нормальный доступ к новому формату ACCOUNTINFO

abstract: convenient human-readable access to ACCOUNTINFO table from OCS-NG Inventory 2.x
keywords: ACCOUNTINFO, ACCOUNTINFO_CONFIG, FIELDS_###

Я долго бился над одной совершенно дурацкой проблемой: в OCS Inventory версий 1.х таблица с дополнительной "административной" информацией была одна - ACCOUNTINFO - и имела довольно простой формат: каждый столбец таблицы представлял собой одно поле административной информации и делать выборку было не просто, а очень просто.
В версиях 2.х это изменилось: теперь все добавленные пользователем поля имеют имена вида FIELDS_XXX, где XXX - какое-то число, соответствующее порядковому номеру, каким по счёту добавлялось это поле. Описания же полей хранятся в таблице ACCOUNTINFO_CONFIG, причем прямой связи между ними нет:

CREATE TABLE `accountinfo` (
  `HARDWARE_ID` int(11) NOT NULL,
  `TAG` varchar(255) DEFAULT 'NA',
  `fields_12` varchar(255) DEFAULT NULL,
  `fields_13` varchar(255) DEFAULT NULL,
  `fields_14` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`HARDWARE_ID`),
  KEY `TAG` (`TAG`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `accountinfo_config` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME_ACCOUNTINFO` varchar(255) DEFAULT NULL,
  `TYPE` int(11) DEFAULT NULL, #способ представления поля, с ним еще надо будет разбираться
  `NAME` varchar(255) DEFAULT NULL,
  `ID_TAB` int(11) DEFAULT NULL, #на какой вкладке GUI показывать это поле
  `COMMENT` varchar(255) DEFAULT NULL,
  `SHOW_ORDER` int(11) NOT NULL, #каким по порядку его показывать
  `ACCOUNT_TYPE` varchar(255) DEFAULT NULL, #computers или snmp
  `DEFAULT_VALUE` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8; 

Для чего нужно поле NAME_ACCOUNTINFO я так и не понял: в добавленных через GUI полях оно не заполнено. Интерес представляют поля ID, NAME и отчасти COMMENT.
COMMENT содержит "человеческое" название поля, которое выводится в GUI, например "Инвентарный номер"
NAME - имя поля в более-менее приемлемом виде, например "INVNOMER", задается в GUI.
А вот ID соответствует числу, которое в ACCOUNTINFO дописано в названии полей после "fields_".
То есть, связь какая-то очевидно есть, но вот как объединить эти таблицы, чтобы не гадать каждый раз, чему соответствует очередное FIELDS_XXX?

MySQL не поддерживает косвенное обращение к столбцам таблиц по имени. То есть, нельзя написать что-то вроде:

set @colname:='fields_13';
select "accountinfo".@colname from accountinfo;

Можно, разумеется, подключиться к базе из какого-нибудь ЯВУ и формировать запрос привычными операторами, как это и сделано в самом сервере, но неужели нельзя обойтись средствами самого MySQL? Оказывается можно, пусть и обходным путём.

Коллега напомнил мне про VIEW, хранимые процедуры и про PREPARE. Ну а дальше было дело техники. После пары дней игр с различными вариантами родился вот такой красивый, думаю, код:

#creating VIEW mimicking ACCOUNTINFO structure but with more meaningful column names 

set @stmt=concat(
    'create or replace view ocsweb.accinfo as select hardware_id, tag,',
    (select group_concat(concat(' fields_', ID, ' as ', name))
        from accountinfo_config
        where id > 2 and ACCOUNT_TYPE = 'computers'),
    ' from accountinfo');

#select @stmt; #для проверки правильности формирования оператора в интерактивном режиме
prepare statement from @stmt; execute statement; deallocate prepare statement;

В нём я создаю VIEW, дублирующий структуру таблицы ACCOUNTINFO, но с "человеческими" именами. В яблочко!

Составляем переменную @stmt из трех частей.  "Голова" и "хвост" особого интереса не представляют - это рамки для упрощенного создания VIEW. В "голове" жестко предписываем обязательно выбирать поля HARDWARE_ID и TAG.
А вот средняя часть и делает основную работу:

внутренним concat-ом создаем имя поля FIELDS_XXX из текстового фрагмента и номера поля, дописываем к нему " as " и добавляем "человеческое" название из поля NAME, получая строку вида " fields_13 as MMODEL" (mmodel - название одного из полей в моей реальной БД)

group_concat формирует строку таких сочетаний, разделенных запятыми (для удобочитаемости перед "fields_" поставлен пробел, хотя можно обойтись и без него): " fields_13 as MMODEL, fields_14 as MMFGR, fields_15 as MSN".

Поскольку данные в ACCOUNTINFO могут относиться как к компьютеру, так и к snmp-устройству, выбираем только нужное - "computers. Поле TAG всегда имеет ID=2, поэтому его пропускаем, хотя можно и не пропускать, убрав ", tag" из "головы".

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

Пока что этот код нормально работает только с текстовыми полями, хотя 2.х поддерживает более тонкую настройку: поля административной информации могут быть чекбоксами, наборами радио-кнопок, выпадающими списками и т.д., см. GUI. Для таких полей в ACCOUNTINFO хранится не значение, в отличие от текстовых, а несколько иная информация, завязанная на таблицу OCSWEB.CONFIG, в которой хранятся возможные варианты значений и т.п., но с ними я пока подробно не разбирался. Как-нибудь потом