Генерация и регистрация таблиц в rawdata на уровне 0

[dimakarov]

Хочу обсудить принцип формирования таблиц на уровне 0 в схеме rawdata.
Для удобства в конец сообщения я добавил описание из нашего гугл-документа по структуре Леда.

Каждая новая работа регистрируется с каталоге библиографии и получает уникальный номер bib : common.bib (id).
По аналогии с Леда его можно и нужно использовать при занесении таблиц на уровень 0.
При этом одна библиография может содержать более одной таблицы (в случае данных из CDS они все описываются в файле ReadMe).
В принципе, текущая схема наименования таких таблиц выглядит достаточно универсальной: rawdata.<prefix><bib>_<cds-table-name>.

Вопрос: нужно ли нам создать специальный список таких таблиц?
К примеру, такую

CREATE TABLE rawdata.tables (
, table_name text PRIMARY KEY
, bib integer REFERENCES common.bib (id)
, description text NOT NULL
);

С одной стороны, особой жизненной необходимости в этом нет, т.к. всегда можно найти соответствующие имена по шаблону в системных таблицах, а описание таблицы можно и нужно делать с помощью механизма метаданных.
С другой стороны, наличие такой таблицы существенно облегчает жизнь при обработке вновь поступивших данных и последующем доступе к ним.
Еще один важный момент, это сделает возможным использование стандартных механизмов ограничений по внешнему ключу при описании наборов данных (dataset).
Пример:
CREATE TABLE geometry.dataset (
id serial PRIMARY KEY
, datatype text REFERENCES common.datatype (id ) ON DELETE restrict ON UPDATE cascade
, bib integer NOT NULL REFERENCES common.bib ( id ) ON DELETE restrict ON UPDATE cascade
, srctab text REFERENCES rawdata.tables ( table_name ) ON DELETE restrict ON UPDATE cascade
) ;
В данный момент, создание такого внешнего ключа невозможно, т.к. Postgres запрещает ссылаться на системные таблицы.

Текущая схема хранения таблиц исходных данных в HyperLeda

Создается уникальный библиографический номер iref
Для каждой таблицы, описанной в ReadMe-файле создается ее копия в БД с именем
hl<iref>_<tablename>
Структура таблицы полностью дублирует исходную со следующими модификациями:

• Создаются поля ra, dec с координатами объектов на эпоху J2000.0
• Добавляются служебные поля:

1. hl_seq - номер записи в оригинальной таблице
2. hl_pgc - кроссидентификация с Leda
3. hl_idmod - код идентификации
4. hl_time - время переноса данных на уровень 1

• Также могут добавляться вспомогательные поля, к примеру hl_name для формирования стандартного имени
• Каждая таблица сопровождается метаданными из ReadMe-файла, записанными в таблицу с именем
  metahl<iref>_<tablename>
  Структура:
  field - имя поля
  name - параметр
  value - значение
  Пример:

hl=# \d hl51124_table1
                                         Table "public.hl51124_table1"
  Column  |            Type             | Collation | Nullable |                    Default
----------+-----------------------------+-----------+----------+------------------------------------------------
 hl_seq   | integer                     |           | not null | nextval('hl51124_table1_hl_seq_seq'::regclass)
 RAh      | smallint                    |           |          |
 RAm      | smallint                    |           |          |
 RAs      | real                        |           |          |
 DE-      | character varying(1)        |           |          |
 DEd      | smallint                    |           |          |
 DEm      | smallint                    |           |          |
 DEs      | real                        |           |          |
 Rmag     | real                        |           |          |
 f_Rmag   | character varying(1)        |           |          |
 z        | real                        |           |          |
 e_z      | real                        |           |          |
 ztype    | character varying(6)        |           |          |
 ra       | double precision            |           |          |
 dec      | double precision            |           |          |
 hl_coord | character varying           |           |          |
 hl_name  | character varying           |           |          |
 hl_pgc   | integer                     |           |          |
 hl_idmod | smallint                    |           |          |
 hl_line  | integer                     |           |          |
 hl_time  | timestamp without time zone |           |          |
Indexes:
    "idx_hl51124_table1_hl_seq" btree (hl_seq)
    "idx_hl51124_table1_ra" btree (ra)
    "idx_hl51124_table1_radec" btree ("dec", ra)
    "indexhlpgchl51124_table1" btree (hl_pgc)

hl=# SELECT * FROM metahl51124_table1 LIMIT 10;
 field |    name     |                              value
-------+-------------+------------------------------------------------------------------
       | cds_cat     | J/ApJ/646/133
       | bibcode     | 2006ApJ...646..133M
       | reference   | Astrophys. J., 646, 133-142 (2006)
       | author      | Mulchaey J.S., Lubin L.M., Fassnacht C., Rosati P., Jeltema T.E.
 RAh   | description | Hour of Right Ascension (J2000)
 RAh   | datatype    | int
 RAh   | width       | 2
 RAh   | unit        | h
 RAm   | description | Minute of Right Ascension (J2000)
 RAm   | datatype    | int
(10 rows)

[dimakarov]

Подумал, что в этой таблице будет удобно хранить текущий статус работы с ней. Что-то типа:
initiated
downloaded
xidetified
processed

[Kraysent]

Из чата:

@Kraysent
Задумался тут насчёт хранения таблиц на нулевом уровне: для перемещения с нулевого на первый нам помимо самих данных из таблицы (названия, две координаты, их ошибки, и поля, которые мы собственно собираемся перенести, т.е., например, красное смещение) нужны ещё и, очевидно, единицы измерения и, в перспективе, систематические ошибки, связанные с вносимой таблицей. Как хранить систематику, я пока не знаю, и насколько я понимаю, первый запуск мы планируем без неё.

Тогда нам как-то нужно хранить единицы измерения; сейчас они никак не хранится, мы лишь загружаем данные, переводим типы из изначальных в типы постгреса, и делаем INSERT INTO всех столбцов.

Предложение следующее: сделать две новые таблицы. Первая - регистр таблиц нулевого уровня. Пока я вижу в нем три поля: id, источник (FOREIGN KEY на таблицу библиографии) и название таблицы. Т.е. с какой библиографией связана та или иная таблица, соотношение - одна библиография ко многим таблицам. Вторая таблица - регистр столбцов с их метаданными. Поля в моей голове следующие - id столбца, id таблицы, в которой этот столбец содержится (FOREIGN KEY на регистр таблиц), название столбца из оригинальной таблицы, единицы измерения из оригинального источника. Можно ещё добавить описание, но я не знаю, нужно ли - отображаться это вроде нигде не будет, и его можно класть в COMMENT к соответствующим столбцам. Тогда при добавлении новой таблицы из какого-то каталога в эти два регистра нужно будет вставлять соответствующие данные.

Консистентности, обеспеченной СУБД, как мы обсуждали, не будет, потому что ссылаться на системные таблицы нельзя, но это как будто и не очень страшно - просто сами будем вставлять туда при создании таблицы на нулевом уровне.

Тогда при переносе с 0 на 1 уровень мы спросим у пользователя, какие столбцы он хочет использовать для кросс-идентификации, а какие - для внесения данных по результатам этой кросс-идентификации (и в какой каталог). Он ответит, мы поджоиним в эти два регистра (SELECT columns.name, columns.unit FROM tables JOIN columns ON columns.table_id = tables.id WHERE tables.name = "название таблицы", что-то такое) и вытащим оттуда единицы измерения колонок, переведём их в нужные нам единицы измерения и отправим на кросс-идентификацию. Точно так же и переведём единицы измерения для столбцов, которые мы просто собираемся внести в каталог (красное смещение, например).

@dimakarov
Нам нужны метаданные по таблицам - это факт.

Я сейчас реализовал механизм метаданных на основе комментариев постгреса. Плюс - это поддерживается на уровне БД. Минус - управлять эти приходится вручную. Комментарий - это просто строка с описанием. Я туда записываю метаданные в виде JSON. Одним из параметров JSON могут быть единицы измерения.

Можно создать специальную таблицу содержащую список всех колонок всех наших таблиц, что фактически дублирует системные таблицы постгреса. С ними, наверное, чуть проще работать чем с комментариями, но полноценную целостность обеспечить вряд ли удастся.

[Kraysent]

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

Целостность можно достичь договоренностью не редактировать таблицы на нулевом уровне вручную:). Если это всё же нужно будет, можно сделать метод сетевого API, который будет редактировать то, что нужно отредактировать.

[dimakarov]

IMHO, тут есть несколько нюансов.

1. нам нужны метаданные не только по rawdata, но и по остальным таблицам в БД. Соответственно, такая таблица должна быть общей для всей БД.
2. При этом ее функционал будет разным для таблиц каталогов и rawdata. В rawdata нужно отслеживать текущий статус работы и, наверное, журнал обращений: кто и когда что делал

Посмотри, плс, текущую реализацию meta. Она решает все нужные вопросы.
Если комментарий не является JSON - он преобразуется в JSON на лету с одним полем description
Обращение с JSON близко к стандартам, принятым в БД.
Есть пара функций для записи JSON в качестве комментария для схем, таблиц и колонок.

Как правило, я пишу комментарии ко всем создаваемым таблицам и колонкам. Правда, они сделаны "классическим" способом постгреса, т.е. в виде простой строки. На данный момент, JSON-комментарии я сделал только для нескольких таблиц и я добавлял только UCD коды. Обращение к ним выглядит следующим образом (специально добавил обращение к несуществующему параметру units, чтобы продемонстрировать, что он будет возвращать просто NULL):

dim_test=> SELECT table_name, column_name, param->'ucd' AS ucd, param->'units' AS units, param->'description' AS description FROM meta.column_info WHERE param->'ucd' IS NOT NULL; 

 table_name |    column_name    |        ucd         | units |                                                      description                                                       
------------+-------------------+--------------------+-------+------------------------------------------------------------------------------------------------------------------------
 notes      | pgc               | "meta.id"          |       | "PGC number of the object"
 notes      | bib               | "meta.bib"         |       | "Bibliography reference"
 notes      | note              | "meta.note"        |       | "Important comments on the object"
 notes      | modification_time | "time.creation"    |       | "Timestamp when the record was added to the database"
 ucd        | id                | "meta.id;meta.ucd" |       | "UCD"
 ucd        | description       | "meta.note"        |       | "Important comments on the object"
 data       | pgc               | "meta.id"          |       | "PGC number of the object"
 data       | design            | "meta.id"          |       | "Unique designation the object. It must follow the IAU recommendations: https://cdsweb.u-strasbg.fr/Dic/iau-spec.html"
 data       | bib               | "meta.bib"         |       | "Bibliography reference"
 data       | modification_time | "time.creation"    |       | "Timestamp when the record was added to the database"
(10 строк)

Естественно, можно получать JSON-структуру напрямую, без формирования колонок.

dim_test=> SELECT table_name, column_name, param FROM meta.column_info WHERE param->'ucd' IS NOT NULL;
 table_name |    column_name    |                                                                            param                                                                             
------------+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
 notes      | pgc               | {"description" : "PGC number of the object" , "ucd" : "meta.id"}
 notes      | bib               | {"description" : "Bibliography reference" , "ucd" : "meta.bib"}
 notes      | note              | {"description" : "Important comments on the object" , "ucd" : "meta.note"}
 notes      | modification_time | {"description" : "Timestamp when the record was added to the database" , "ucd" : "time.creation"}
 ucd        | id                | {"description" : "UCD" , "ucd" : "meta.id;meta.ucd"}
 ucd        | description       | {"description" : "Important comments on the object" , "ucd" : "meta.note"}
 data       | pgc               | {"description" : "PGC number of the object" , "ucd" : "meta.id"}
 data       | design            | {"description" : "Unique designation the object. It must follow the IAU recommendations: https://cdsweb.u-strasbg.fr/Dic/iau-spec.html" , "ucd" : "meta.id"}
 data       | bib               | {"description" : "Bibliography reference" , "ucd" : "meta.bib"}
 data       | modification_time | {"description" : "Timestamp when the record was added to the database" , "ucd" : "time.creation"}
(10 строк)

Запись параметров осуществляется через функции setparams

dim_test=> SELECT setparams( 'icrs', 'data', 'id', json_build_object( 'description','Position ID','ucd','meta.id' ) ) ;
 setparams 
-----------
 
(1 строка)

dim_test=> SELECT schema_name, table_name, column_name, param FROM meta.column_info WHERE schema_name='icrs' and table_name='data' and column_name='id';
 schema_name | table_name | column_name |                       param                        
-------------+------------+-------------+----------------------------------------------------
 icrs        | data       | id          | {"description" : "Position ID", "ucd" : "meta.id"}
(1 строка)

[Kraysent]

Уточняющий вопрос: а зачем нам нужны метаданные по не-rawdata таблицам? Насколько я понимаю, с ними связаны какие-то метаданные, но что мы с ними планируем делать? Они как-то должны отображаться пользователю?

[dimakarov]

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

[Kraysent]

Ага, посмотрел, в целом действительно решает нужные задачи. Я правильно понимаю, что всё, что нужно будет сделать - это при создании таблицы нулевого уровня в комментарий к её столбцам записать JSON с полями description, unit и прочими метаданными и они сразу станут доступны через VIEW в meta схеме?

Кроме того, консистентность в dataset-таблицах мы будем руками обеспечивать без помощи СУБД?