Вопрос про observer для реляционных баз данных

Есть задача.

Допустим есть две программы: «Админ» и «Клиент».

«Админ»:

Позволяет проводить любые запросы на изменения данных (INSERT, UPDATE, DELETE)
Изменения схемы базы данных — out of scope данного вопроса
Можно считать это очень тонкая command-line обертка над INSERT/UPDATE/DELETE
Условно: любой консольный клиент к базе

«Клиент»:

Отображает результат произвольно сложного SELECT на экране в виде read-only таблицы
Реагирует на изменения данных, вносимые «Админом» (добавляет-изменяет-удаляет строки)
Ограничен в правах доступа (не может менять данные, не имеет доступа к базе за пределами таблиц в SELECT и т.п. principle of least power)
Запрос для отображения сохраняется в конфигурации к данному инстансу «Клиента»
Условно: табло прибытий-отправлений поездов на вокзале

Вопрос: как именно реализовать такое? Ограничений на СУБД нет. Думаю над вариантами либо скриптовать СУБД (триггерами, хранимками и т.д.), либо связка «Сервер» + СУБД + кастомный протокол (либо парсинг SQL) между «Сервер»-«Админ» и «Сервер»-«Клиент».

Решал ли кто-нибудь такое в общем случае?

Теми: база даних, дані

Ctrl + Enter

Yegor Chumakov founder в startup 28.10.2021 12:52

Если это постгрес, то создаём там репликационный слот и слушаем ивенты. Когда видим что что-то поменяли в таблице — рефрешим клиента.

Відповісти

Підтримати

Roman Gorodishcher Principal Engineer в Twilio 28.10.2021 02:00

Чем не подходит решение в лоб: читать из базы в цикле с подходящим интервалом (раз в минуту например)?

—-
Если по каким-то причинам простое решение не годится, посмотри на Debezium. Оно апдейты данных в кафку посылает. Дальше в простейшем случае — пересчитываешь свой запрос на каждый апдейт.

Уставший архитектор 28.10.2021 10:50

Скучно

Если по каким-то причинам простое решение не годится, посмотри на Debezium. Оно апдейты данных в кафку посылает. Дальше в простейшем случае — пересчитываешь свой запрос на каждый апдейт.

Смотрю

Roman Gorodishcher

Dmitry Astapov камлатель на ocaml в Jane Street 28.10.2021 17:20

С большими таблицами и большими объёмами изменений у него не все хорошо, beware

Уставший архитектор

Дмитро 27.10.2021 18:40

слушать апдейты через общую задачу через какие-то коннекторы к базам можно высокоуровневые — иногда базы сами предоставляют cdc с пушем из базы (ms sql — sql dependency) или низкоуровневые, что слушают транзакционные логи по типу этого github.com/...sql-binlog-connector-java, остается вопрос маппинга ивентов через SELECT query на подключенный tcp клиент, тут наверное парсинг имен обьектов и матчинг только. может какие-то из решений умеют слушать и сорсить события базы могут прямо по переданному query, но это надо изучать.
github.com/airbnb/SpinalTap
github.com/Yelp/mysql_streamer
github.com/zendesk/maxwell

Алексей Алексей 27.10.2021 18:12

Якщо правильно зрозумів умови, то в загальному випадку — це Event Sourcing клієнту з read-моделлю.
Якщо процесинг простий — можно взагалі нічого не вигадувати та наробити тригерів.
Якщо складний — оновлювати дати в змінених записах (тригерами, якщо нема доступа до write-коду) та процесити їх клієнтом.
Або повноцінні меседжи + обробка в клієнті.
Деякі бази вміють навіть самі в меседжинг
www.postgresql.org/docs/9.4/sql-notify.html

А ще можна навіть слухати replication івенти бази.
github.com/davyam/pgEasyReplication
jdbc.postgresql.org/...ion/head/replication.html

Якщо Oracle та обробка підійде під вимоги Fast Refresh On Commit, то в них є MaterializedViews.

Можу ще десяток варіантів навести.
Але що буде краще у даному випадку — складно сказати по наданим вимогам.
Не вказаний ані стек, ані read/write навантаження, ані можливість доступу до коду клієнта/сервера.

Dimon Dimon 27.10.2021 18:28

Любое решение делать это средствами субд видится полным трэшем.

Алексей Алексей

Алексей Алексей 27.10.2021 18:32

Якщо все вирішується одним скриптом для MaterializedView замість купи коду та інфраструктури, то трешем як раз виглядає останнє.

Dimon Dimon

Andrey Rogovsky Допомагаю знайти першу роботу в IT для Трейні та Джунів на topizda.to 27.10.2021 17:10

Добавлю условий — 10М клиентов, 1M админов, 100k апдейтов в сутки, дискретность триггера — минута, задерживать данные нельзя, терять тоже. Агрегирование в минуту — допустимо.

Вот теперь можете решать.

Gena Kartashevskyy Platform Engineer в MyHeritage 27.10.2021 16:17

Любой OOTB Change Data Capture?

Уставший архитектор 27.10.2021 17:44

Там идут изменения на все таблицы или только на те, на которые есть права?

Gena Kartashevskyy

Gena Kartashevskyy Platform Engineer в MyHeritage 27.10.2021 17:57

Зависит от реализации. У того же Debezium очень гибко. Например, MySQL
debezium.io/...ysql-connector-properties
table.include.list — The connector does not capture changes in any table not included in table.include.list

HorAV 27.10.2021 15:48

newbedev.com/...-table-changes-by-using-c

Dimon Dimon 27.10.2021 16:08

Миллионы лет опыта и интуиция советуют не связываться с этим. Но классно, соглашусь — костыльнуть разок где-то сгодиться.

HorAV

Уставший архитектор 27.10.2021 16:10

Интересно, спасибо

DNA Exp 27.10.2021 16:53

Читай список ограничений. Как я писал, самый надежный способ, это слой бизнесс логики.
Где ты контролируешь все свои сущности которые меняются, а значит можешь оповестить через сокеты всех кому надо.

The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.
The statement may not use the asterisk (*) or table_name.* syntax to specify columns.
The statement may not use unnamed columns or duplicate column names.
The statement must reference a base table.
The statement must not reference tables with computed columns.
The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.
A projected column in the SELECT statement that is used as a simple expression must not appear more than once.
The statement must not include PIVOT or UNPIVOT operators.
The statement must not include the UNION, INTERSECT, or EXCEPT operators.
The statement must not reference a view.
The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.
The statement must not reference server global variables (@@variable_name).
The statement must not reference derived tables, temporary tables, or table variables.
The statement must not reference tables or views from other databases or servers.
The statement must not contain subqueries, outer joins, or self-joins.
The statement must not reference the large object types: text, ntext, and image.
The statement must not use the CONTAINS or FREETEXT full-text predicates.
The statement must not use rowset functions, including OPENROWSET and OPENQUERY.
The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.
The statement must not use any nondeterministic functions, including ranking and windowing functions.
The statement must not contain user-defined aggregates.
The statement must not reference system tables or views, including catalog views and dynamic management views.
The statement must not include FOR BROWSE information.
The statement must not reference a queue.
The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).
The statement can not specify READPAST locking hint.
The statement must not reference any Service Broker QUEUE.
The statement must not reference synonyms.
The statement must not have comparison or expression based on double/real data types.
The statement must not use the TOP expression.

Dimon Dimon 27.10.2021 18:31

Как неожиданно

DNA Exp

DNA Exp 27.10.2021 15:41

Моя новая субд такое может делать из каропки. На юае мгновенно отображать изменения в субд асинхронно. (синк клиентской и серверной субд) Но я ее еще не дописал. Жди.

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

Уставший архитектор 27.10.2021 16:04

это создать кеш прослойку между бизнесс логикой и субд.

Просто по факту получается туда нужно вставить парсер SQL, чтобы выдергивать инфу о том, какие поля меняются

DNA Exp 27.10.2021 16:15

Какой парсер ? У тебя что, запросы от админки идут мимо бизнесс логики в приложении ?

Уставший архитектор 27.10.2021 17:20

Во-первых CRUD, во-вторых хранимые процедуры. Потому что хочется универсальное решение

Dimon Dimon 27.10.2021 15:35

Я нифига не понял, но попробуй сокеты.

Вопрос про observer для реляционных баз данных

Схожі топіки

22 коментарі

Підписатись на коментарі

Новини