company_banner

Вред хранимых процедур


    В чат подкаста «Цинковый прод» скинули статью о том, как некие ребята перенесли всю бизнес-логику в хранимые процедуры на языке pl/pgsql. И так как у статьи было много плюсов, то значит, есть люди, а может быть, их даже большинство, которые положительно восприняли такой рефакторинг.

    Я не буду растекаться мысью по древу, а сразу накидаю кучку минусов использования хранимых процедур.

    Минусы хранимых процедур


    Версионирование


    Если в случае с кодом на php вы можете просто переключиться в git на другую ветку и посмотреть, что получилось, то хранимые процедуры нужно еще засунуть в базу. И традиционные миграции тут плохо помогут: если записывать все изменения хранимок как новый CREATE OR REPLACE PROCEDURE, то на кодревью будет ад: всегда новый файл, который непонятно с чем сравнивать. Поэтому придется искать какие-то дополнительные инструменты или писать свой велосипед.

    Сам язык pl/pgsql


    Это устаревший процедурный язык из девяностых, который вообще никак не развивается. Никакого ООП или ФП или чего бы то ни было. Синтаксис без малейшего намека на синтаксический сахар.

    Например, переменные нужно объявлять в начале процедуры, в специальном блоке DECLARE. Так делали наши деды, в этом есть некая ностальгия по языку Pascal, но спасибо, не в 2020.

    Сравните две функции, которые делают одно и то же на php и pl/pgsql:

    CREATE OR REPLACE FUNCTION sum(x int, y int)
        RETURNS int
        LANGUAGE plpgsql
    AS $$
    DECLARE
        result int;
    BEGIN
        result := x + y;
        return result;
    END;
    $$;
    

    function sum(int $x, int $y): int
    {
        $result = $x + $y;
        return $result;
    }
    

    Примерно в 2-3 раза больше писанины.

    Кроме того, язык интерпретируемый, без JIT и т.д. (поправьте меня, если что-то изменилось в последних версиях). Т.е. все очень медленно и печально. Уж если использовать какие-то хранимки, то на чистом SQL или v8 (т.е. javascript).

    Отладка


    Поверьте, отлаживать код на php в 100500 раз проще. Ты просто поправил что-то и смотришь результат. Можно обложить echo или смотреть, что там через xdebug прямо в IDE.

    Отладка хранимых процедур — это неудобно. Это надо делать в pgadmin (включив специальное расширение). PgAdmin — это далеко не PHPstorm по удобству.

    Логирование и обработка ошибок


    Забудьте о том, чтобы красивый json c трейсом падал с stdout, а потом в graylog и в sentry. И чтобы все это автоматически происходило, выдавая пользователю ошибку 500, в случае если контроллер не поймал exception.

    В хранимках pl/pgsql вы всё будете делать вручную:

    GET DIAGNOSTICS stack = PG_CONTEXT;
    RAISE NOTICE E'--- Стек вызова ---\n%', stack;

    Сбор метрик


    Вы не можете, как в golang, просто добавить эндпоинт /metrics, который будет подсасываться Прометеусом, куда вы напихаете бизнесовые и другие метрики для мониторинга. Я просто не знаю, как тут выкрутиться с pl/pgsql.

    Масштабирование


    Выполнение хранимых процедур тратит ресурсы (например, CPU) сервера базы данных. В случае других языков вы можете вынести логику на другие ноды.

    Зависимости


    В php вы, используя пакетный менеджер composer, одним движением можете подтянуть нужную библиотеку из интернета. Точно так же как в js это будет npm, в Rust это будет cargo и т.д.

    В мире pl/pgsql нужно страдать. В этом языке просто нет менеджера зависимостей.

    Фреймворки


    В современном мире веб-приложение часто не пишут с нуля, а собирают на основе фреймворка, используя его компоненты. К примеру, на Laravel у вас из коробки есть роутинг, валидация запроса, движок шаблонов, аутентификация/авторизация, 100500 хелперов на все случаи жизни и т.д. Писать всё это вручную с нуля, на устаревшем языке — ну нет, спасибо.

    Получится много велосипедов, которые потом еще и поддерживать придется.

    Юнит-тесты


    Сложно даже представить, как удобно организовать unit-тесты в хранимках на pl/pgsql. Я ни разу не пробовал. Поделитесь пожалуйста в комментариях.

    Рефакторинг


    Несмотря на то, что существует IDE для работы с базой данных (Datagrip), для обычных языков средства рефакторинга гораздо богаче. Всевозможные линтеры, подсказки по упрощению кода и т.д.

    Маленький пример: в тех кусках кода, которые я привел в начале статьи, PHPStorm дал подсказку, что переменная $result необязательна, и можно просто сделать return $x + $y;

    В случае с plpgsql — тишина.

    Плюсы хранимых процедур


    1. Нет оверхеда на перегон промежуточных данных по пути бекенд-БД.
    2. В хранимых процедурах кешируется план запроса, что может сэкономить пару ms. т.е. как обертка над запросом иногда это имеет смысл делать (в редких случаях и не на pl/pgsql, а на голом sql), если бешеный хайлоад, а сам запрос выполняется быстро.
    3. Когда пишешь свой extension к посгресу — без хранимок не обойтись.
    4. Когда хочешь из соображений безопасности спрятать какие-то данные, дав доступ приложению только к одной-двум хранимкам (редкий кейс).

    Выводы


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

    Я бы понял, если в исходной статье часть логики переложили на SQL, это можно понять. Но зачем хранимки — это загадка.

    Буду рад, если вы считаете, что я неправ или знаете, какие-то еще ситуации, связанные с хранимыми процедурами (как плюсы, так и минусы), и напишете об этом в коменты.

    RUVDS.com
    VDS/VPS-хостинг. Скидка 10% по коду HABR

    Comments 536

      +14

      По версионированию: в любой продакшн реди туле для миграции есть repeatable migrations. В Flyway они идут с префиксом R__. И именно туда нужно писать процедуры. В Liquibase возможности еще богаче.
      Другой вопрос, что программисты зачастую об этом не знают.
      Почему те ребята не используют какой-нибудь тул, который за вас делает версионирование — я не знаю. И тоже считаю это упущением.


      https://github.com/feoktant/pagila-flyway/blob/master/src/main/resources/db/migration/R__create_functions.sql вот пример как оно выглядит. Дифы, ветки, гит.

        +4

        liquibase хорош, но он решает некоторые проблемы:


        1. не получится при пулл реквесте сранивать дифы миграций, т.к. liqubase негативно реагирует на изменение файлов старых миграцией, которые уже накатаны в БД (сравнение по хеш-сумме) по этой причине придется для изменения миграции будет создаваьб новый файл, а значит и вся процедура будет в новом файле.
        2. Также есть проблемы при переключение веток и накатам и откатом миграцией, например:
          • мы разрабатываем фичу накатили новые миграции,
          • потом нас попросили глянуть баг, мы переключились накатили другие миграции
          • вернулись к фиче и хотим миграции фичи откатить

        Не стоит думать, что разработчики не знаю об доступных инструментах, просто есть вещи, которые на уровне кода проще делать.

          +3
          1. Для того, что бы накатывал каждый раз, ченджсет нужно сделать накатываемым на изменение хэшсуммы. Кажется делался через атрибут runOnChange="true". И вы получаете ровно тот же функционал, что и в Флайвее под названием repeatable миграции. И не надо никаких новых файлов.
          2. Самое большое отличие Флайвей-Ликьюбейс — это роллбэк. Если у вас есть роллбэки, которые нужны исключительно в кейсе, который вы описали, то проблемы нет вообще.

          По этим двум пунктам — разработчики не знают о фичах инструментов, и не читают документацию. И на уровне кода перемзобретается велосипед, который уже реализован

        +6
        Сложно даже представить, как удобно организовать unit-тесты в хранимках на pl/pgsql. Я ни разу не пробовал. Поделитесь пожалуйста в комментариях.

        Этот вопрос изучен и описан, например тут:
        «The Art of PostreSQL», Dimitri Fontaine, второе издание. Глава 7: раздел 3 Unit Tests, раздел 4 Regression Tests.
        Так как у нас теперь есть TestContainers то и запускать такие тесты намного легче чем 20 лет назад. Они будут медленнее, но это находка даже для легаси систем. Были бы нормально мирации организованы
        • UFO just landed and posted this here
          +9
          В вашем примере приведена функция, которой не место в коде sql. Скорее всего она будет заменена на одну из аггрегирующих функций, или же считаться прямо в
          select x + y
            0
            да это же просто пример написания функции, конкретика не важна
              +11
              Важна — таких функций у вас не будет. А раз не будет такого кода, то и считать нечего
                +37
                Нужно делать живые примеры, чтобы сравнивать сравнимое. Проблема искусственных примеров в том, что их можно подогнать под результат.

                Например код на SQL:
                SELECT * FROM tablename


                Код на PHP:
                <?php
                $data = [];
                $fh = fopen(__DIR__ . '/data/tablename.csv');
                $headers = fgetcsv($fh);
                while(($row = fgetcsv($fh)) !== false){
                    $data[] = array_combine($headers, $row);
                }
                fclose($fh);
                return $data;


                Каждый инструмент имеет свою специфику применения и исходя из специфики можно подобрать удобный в том или ином случае искусственный пример.
                  +2
                  Собственно, такие примеры — просто технический случай риторических приемов доведения до абсурда и подмены тезиса, то есть контр-аргументация против заведомо удобных аргументов, которые легко опровергнуть.
                    –1

                    Ваш пример кода на php можно упростить до одной строчки, потому что куча php библиотек из composer это делают за тебя.


                    Алсо, ваш код для базы неверен. Если это селект внутри хранимки, то мы еще должны нпписать into somevar


                    А перед этим написать declare и прочую муть-обвязку функции (create or replace, returns, begin, end, $$)


                    Мне реально интересно, в каком случае хрпнтмка получится компактнее, чем php код.

                      +4
                      SQL:
                      CREATE PROCEDURE close_doc(p_doc_id INT, type TINYINT)
                      -- type - 1 закрытие, -1 открытие
                      BEGIN
                      	-- Нельзя закрывать ЗПС и План производства
                      	@msg := (SELECT raise_error('Документы данного типа нельзя закрывать если есть позиции с не нулевыми количествами.')
                      	FROM t_docs d
                      	INNER JOIN doc_pos dp ON d.id = dp.doc_id
                      	WHERE d.doc_type_id IN (6, 8) -- ЗПС и План производства
                      	AND IFNULL(dp.kol, 0) <> 0
                      	AND d.id = p_doc_id)
                      	;
                      	-- Изменим текущие остатки
                      	INSERT cur_stock (mat_id, org_id, org_id_ur, kol, reserve)
                      	SELECT dp.mat_id, d.org_id_addr, d.org_id_ur
                      	, IFNULL(dp.kol, 0) * -1 * t.credit_type * type kol
                      	, IFNULL(dp.kol, 0) * -1 * IF(t.credit_type = 1, 1, 0) * type reserve
                      	FROM t_docs d
                      	INNER JOIN doc_pos dp ON d.id = dp.doc_id
                      	INNER JOIN doc_types t ON d.doc_type_id = t.id
                      	WHERE d.id = p_doc_id
                      	AND t.credit_type IN (1, -1)
                      	ON DUPLICATE KEY UPDATE cur_stock.kol = cur_stock.kol + IFNULL(dp.kol, 0) * -1 * t.credit_type * type
                      	, cur_stock.reserve = cur_stock.reserve + IFNULL(dp.kol, 0) * -1 * IF(t.credit_type = 1, 1, 0) * type
                      	;
                      	DELETE cur_stock
                      	FROM t_docs d
                      	INNER JOIN doc_pos dp ON d.id = dp.doc_id
                      	INNER JOIN cur_stock ON cur_stock.mat_id = dp.mat_id AND cur_stock.org_id = d.org_id_addr AND cur_stock.org_id_ur = d.org_id_ur
                      	WHERE cur_stock.kol = 0
                      	AND cur_stock.reserve = 0
                      	AND d.id = p_doc_id;
                      END$
                      


                      php:
                      <?php
                      function close_doc($p_doc_id, $type) {
                          DB:Statement("
                              @msg := (SELECT raise_error('Документы данного типа нельзя закрывать если есть позиции с не нулевыми количествами.')
                              FROM t_docs d
                              INNER JOIN doc_pos dp ON d.id = dp.doc_id
                              WHERE d.doc_type_id IN (6, 8) -- ЗПС и План производства
                               AND IFNULL(dp.kol, 0) <> 0
                               AND d.id = :p_doc_id)
                          ", ['p_doc_id' => $p_doc_id]);
                      
                          DB:Statement("
                              INSERT cur_stock (mat_id, org_id, org_id_ur, kol, reserve)
                              SELECT dp.mat_id, d.org_id_addr, d.org_id_ur
                              , IFNULL(dp.kol, 0) * -1 * t.credit_type * :type kol
                              , IFNULL(dp.kol, 0) * -1 * IF(t.credit_type = 1, 1, 0) * :type reserve
                              FROM t_docs d
                              INNER JOIN doc_pos dp ON d.id = dp.doc_id
                              INNER JOIN doc_types t ON d.doc_type_id = t.id
                              WHERE d.id = :p_doc_id
                               AND t.credit_type IN (1, -1)
                              ON DUPLICATE KEY UPDATE cur_stock.kol = cur_stock.kol + IFNULL(dp.kol, 0) * -1 * t.credit_type * :type
                              , cur_stock.reserve = cur_stock.reserve + IFNULL(dp.kol, 0) * -1 * IF(t.credit_type = 1, 1, 0) * :type
                          ", ['p_doc_id' => $p_doc_id, 'type' => $type]);
                      
                          DB:Statement("
                              DELETE cur_stock
                              FROM t_docs d
                              INNER JOIN doc_pos dp ON d.id = dp.doc_id
                              INNER JOIN cur_stock ON cur_stock.mat_id = dp.mat_id AND cur_stock.org_id = d.org_id_addr AND cur_stock.org_id_ur = d.org_id_ur
                              WHERE cur_stock.kol = 0
                               AND cur_stock.reserve = 0
                               AND d.id = :p_doc_id
                          ", ['p_doc_id' => $p_doc_id]);
                      }
                      ?>
                      
                        +11
                        Да, хороший пример.

                        PHP тут 3 раза отправит строчку в db, которая будет распарсена, составлен план выполнения, результаты сериализованые и десеарилизованны обратно + network latency на туда сюда.

                        БД все внутри процесса, без внутренних преобразований. Еще и проверит колонки и немножко даже типы на этапе компиляции. Кое где автодополнение от опечаток сразу убережет.

                        Для PHP надо что-то типа JOOQ хотя бы, чтоб не париться со сроковыми константами
                          0

                          Во-первых не знаю что у вас за БД, у меня ничего не проверяет.
                          Во-вторых от того что 3 раза строчка будет туда-сюда отправлено RPS типового сервиса изменится примерно на 0%
                          В-третьих затрат на десериализацию тут нет ибо и ответов особых нет

                            0

                            Не знаю как в РНР, в других ЯП библиотеки позволяют передать в одном вызове несколько запросов.
                            Парсинг SQL занимает наносекунды, составление плана в случае использования prepared statement также скорее всего берётся из кеша (а если и нет, то затраты на план — величина меньшего порядка, чем собственно выполнение).
                            В итоге — да, пару десятков мс сэкономить можно, при отсутствии передачи результатов. Стоит ли это усилий? Оптимизация собственно запросов может дать прирост скорости совсем других порядков. Ну а если всё остальное уже супер оптимизировано и всё равно медленно — миллисекунды не спасут, пожалуй, стоит задуматься о horizontal scalability.

                              0
                              >затраты на план — величина меньшего порядка, чем собственно выполнение
                              Заблуждение. Построение плана сложного запроса требует анализа словаря БД, статистики (а это те же самые запросы к БД и анализ их результатов), выставления блокировки на добавление в список планов (что тормозит все остальные запросы, которые требуют хард-парсинга). Если такой запрос в итоге читает несколько блоков из кеша буферов, то задержки на парсинг будут в разы больше времени чтения.
                                0
                                Я в целом сторонник обработки на стороне БД.
                                Но справедливости ради должен сказать, что план запроса можно писать в sql запросе и базе не надо будет его создавать на ходу. Например image

                                Так же есть хорошая статья по оптимизации запросов при помощи плана, включенного в sql
                                http://www.ibase.ru/files/articles/performance/Firebird%20Optimizer%20-%20ORDER%20vs%20SORT.pdf

                                В общем суть в том, что базе можно дать план с запросом, а не заставлять ее вычислять его.

                                Но я сторонник того чтобы все оборачивать в процедуры и по большей части обрабатывать на уровне БД, а не гонять по сети и нагружать веб сервер. Процедуры — это уже скомпилированный оптимизированный элемент внутри БД, он будет быстрее работать и давать уже готовый результат.
                                  0

                                  Все равно это ерундовые расходны на сколько нибудь реальных БД

                                    +1
                                    Посмотрите, как устроены гистограммы статистики, какой объем они занимают. Для запросов, которые обрабатывают маленький объем данных, но по большому количеству таблиц со сложными where, построение запроса может потребовать вычитать кратно больше данных, чем собственно выполнение запроса.
                                    Расходы ерундовые, если разработчики позаботились о том, чтобы каждый раз не делался hard_parse.
                                    Я не знаю как в PG, а в Oracle например на идентичный текст запроса может быть одновременно множество планов выполнения, которые используются разными сессиями. Потому что планы зависят не только от текста запроса, но и еще от кучи параметров окружения сессии.
                                    То есть даже использование bind переменных не гарантирует отсутствия повторного hard-parse.
                                    И вот если разработчики об этом не подозревают или вообще используют какой-то кривой генератор SQL который каждый раз новый текст генерит, или литералы, то потом встает вопрос о горизонтальном масштабировании БД, выделении реплик и т.п.
                                    Если же с базой данных работать с умом, то можно прекрасно без этого обходиться гораздо-гораздо дольше.
                                    База данных — сердце любой крупной системы. Оптимизация в ней или в работе с ней может многократно ускорить работу, а отношение к БД как черному ящику с данными, приводит потом к появлению костылей типа самописных кешей и т.п.
                              0
                              Я совершенно не умею читать сложные запросы в базах данных, задача проследить логику в таком коде для меня почти невыполнима. Поэтому я не понимаю когда топят за подобные сложные запросы.
                              Код на пхп тут ничем не лучше, потому что он делает все совершенно тоже самое, только из пхп.

                              Впрочем волшебные константы имхо это объективная проблема этого подхода. Да и волшебная бизнес логика тоже. Никаких пояснений или чего-то что могло бы помочь понять почему считаем именно так а не иначе.

                              Вычисления между данными разных типов.
                              Вот у вас в коде type tinyint. Я уже молчу про имя 'type' ужасное само по себе, да еще и ключевое слово в куче языков. Выходит этот type можно складывать с другими типа без проверок что мухи не сложились с котлетами.

                              Допустим у меня в базе есть
                              Коты и Собаки. Я хочу быть уверенным что случайно кто-то их не перемножит, а если перемножит то значит он знает что делает.
                              В го например это реализуется тривиально
                              type Dog int
                              type Cat int

                              все, теперь компилятор мне подскажет если где-то будет попытка их сложить без явного преобразования.

                              Еще интересно что если бы это был код на бэкенде, я бы проверил разные граничные условия — ну там select where ничего не вернул, может вывести ошибку. У вас же все это просто проглотится кодом. Для пользователя это обычно «я нажал на кнопку и ничего не случилось». Не самая приятная ситуация.
                                +2
                                Я совершенно не умею читать сложные запросы в базах данных, задача проследить логику в таком коде для меня почти невыполнима. Поэтому я не понимаю когда топят за подобные сложные запросы.

                                Существуют задачи, которые только и решаются написанием сложных запросов. К слову, приведённые выше запросы ни разу не являются сложными — простые DML-ки с JOIN-ами пары таблиц. Реально сложный запрос может занимать пару страниц текста и обычно это не тупо пачка джойнов, а подзапросы, группировки и т.д. Обычно сложность запросов удачно получается ограничить за счет CTE. Заявление вида «я не умею читать сложный SQL» ничем не отличается от любого другого заявления вида «я не умею читать сложный код на %langname%» в плане обсуждаемой статьи.

                                Что касается типизации — postgresql позволяет заводить типы и можно сделать систему типов, также ограничивающие недопустимые операции, но большинство ошибок, с которыми приходилось сталкиваться, ни разу не связаны с такими проверками (да и не приходилось сталкиваться с проектами, где бы заморачивались введением типов).
                                  0
                                  Заявление вида «я не умею читать сложный SQL» ничем не отличается от любого другого заявления вида «я не умею читать сложный код на %langname%» в плане обсуждаемой статьи.

                                  И поэтому есть принципы типа KISS, требуют разбивать сложный код на простые атомарные операции и т. п.

                                    +3
                                    Увы, многоэтажный «простенький» SQL-код в котором вызывается множество функций и процедур, в свою очередь вызывающих другие функции и процедуры с многократной вложенностью, может ощутимо тормозить по сравнению со сложным запросом не имеющим многократной вложенности вызываемых функций и процедур.
                                      0

                                      Ну вот ещё один минус у SQL )

                                        +1
                                        У php нет накладных расходов на вызов процедуры или функции?
                                          0

                                          Накладные расходы, конечно же, есть — но в php функция хотя бы не мешает оптимизации, как в некоторых СУБД.

                                            0

                                            Он не тормозит ощутимо из-за них. Если, конечно, в миллионный цикл не вкладывать.

                                        0
                                        Нет, поэтому профессия программиста требует постоянного саморазвития (а также в связи с тем, что написал delhi_heir чуть выше).
                                    0
                                    CREATE PROCEDURE close_doc

                                    PHP:


                                    // type - 1 закрытие, -1 открытие
                                    function close_doc(int $p_doc_id, int $type)
                                    {
                                        // Нельзя закрывать ЗПС и План производства
                                        $document = Document::find($p_doc_id)->with(['positions', 'type'])->one();
                                        if (in_array($document->doc_type_id, [DocumentType::ZPS, DocumentType::PLAN_PROIZVODSTVA]) {
                                            $cnt = $document->getPositions()->andWhere(['!=', new DbExpression('IFNULL(kol, 0)'), 0])->count();
                                            if ($cnt > 0) {
                                                throw new BusinessLogicException('Документы данного типа нельзя закрывать если есть позиции с не нулевыми количествами.');
                                            }
                                        }
                                    
                                        // Изменим текущие остатки
                                        $curStockList = $document->curStocks;
                                        $newData = [];
                                        foreach ($document->positions as $position) {
                                            $newKol = ($position->kol ?? 0) * -1 * $type;  // hack with multiplication by business value, be careful!
                                            $curStock = ($curStockList[$position->mat_id] ?? null);
                                    
                                            $newData[] = [
                                                'org_id' => $document->org_id_addr, 'org_id_ur' => $document->org_id_ur, 'mat_id' => $position->mat_id,
                                    
                                                'kol' => ($curStock === null ? 0 : $curStock->kol) + $newKol * $document->type->credit_type,
                                                'reserve' => ($curStock === null ? 0 : $curStock->reserve) + $newKol * ($document->type->credit_type === 1 ? 1 : 0),
                                            ];
                                        }
                                        Yii::$app->db->queryBuilder->batchReplace(CurStock::tableName(), $newData)->execute();
                                    
                                        CurStock::deleteAll([
                                            'org_id' => $document->org_id_addr, 'org_id_ur' => $document->org_id_ur, 'mat_id' => array_column($document->positions, 'mat_id'),
                                            'kol' => 0, 'reserve' => 0,
                                        ]);
                                    }

                                    SQL: 32 строки, 28 строк логики
                                    PHP: 33 строки, 22 строк логики


                                    Строки логики это строки, которые остаются если убрать пустые строки и строки с закрывающими скобками.
                                    На PHP на четверть меньше кода, при этом практически нет копипасты, в том числе для связи сущностей/таблиц по внешним ключам.
                                    При этом переменные названы полными именами, в отличие от сокращений в SQL.
                                    Наглядно видны взаимосвязи между сущностями — обрабатываются позиции и стоки, связанные с конкретным документом, а в SQL надо проверить джойны и фильтры, чтобы проверить, что это действительно так.
                                    Наглядно видны условия возникновения ошибки, в отличие от хитрого SELECT raise_error(), который срабатывает только если WHERE вернул записи.
                                    Типы документа это константы с говорящими названиями, а не магические значения с пояснениями в комменте.
                                    Доработав ActiveQuery, можно было бы избавиться от задания длинного первичного ключа в deleteAll(), было бы примерно так:


                                    $document->getCurStocks()->andWhere(['kol' => 0, 'reserve' => 0])->delete();

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

                                      +1
                                      только работать правильно такой код не будет в многопользовательской системе… ну ещё он медленнее раз в 5-10

                                      П.С. ещё не нашёл что-то в ActiveRecord batchReplace… ну и такой лапшекод, по мне, просто невозможно поддерживать, тут у нас и ORM с магическими методами и QueryBuilder и тут только одному богу известно какими SQL-выражениями в итоге они станут и куски чистого SQL-я ('IFNULL(kol, 0)')
                                        0
                                        только работать правильно такой код не будет в многопользовательской системе…

                                        Нормально все будет работать. Последовательный доступ, чтобы никто не поменял позиции во время расчетов, можно обеспечить через SELECT FOR UPDATE и прочими локами. Так же как это делает база при вызове процедуры.


                                        ну ещё он медленнее раз в 5-10

                                        Если это 10 миллисекунд по сравнению с 1 миллисекунд, то в большинстве случаев это неважно. Вряд ли у одного документа миллионы позиций.


                                        ещё не нашёл что-то в ActiveRecord batchReplace

                                        Да, это я по аналогии с batchInsert написал. Его можно самому добавить или имитировать через str_replace('INSERT INTO', 'REPLACE INTO').


                                        ну и такой лапшекод, по мне, просто невозможно поддерживать

                                        Там нет лапшекода, это совершенно обычный нормально структурированный код на PHP. Ничем не отличается от любого другого нормально написанного кода. Возможно вам так кажется из-за переносов строк в разметке Хабра, скопируйте в блокнот и проверьте. Можете открыть какой-нибудь известный компонент для другого фреймворка и сравнить.


                                        тут у нас и ORM с магическими методами и QueryBuilder и тут только одному богу известно какими SQL-выражениями в итоге они станут

                                        Это вам оно неизвестно. Мне например вполне известно — примерно такими же, как в вашем коде, а где-то даже и попроще. А если сомневаетесь, есть специальный метод, чтобы посмотреть сгенерированный SQL. А в интерфейсе еще и отладочная панель со списком отправленных в базу запросов.


                                        QueryBuilder это часть ORM, они работают совместно. Я вам так тоже могу написать — у вас там и SELECT, и JOIN и WHERE, давайте уберем что-нибудь, а то слишком много всего)


                                        и куски чистого SQL-я ('IFNULL(kol, 0)')

                                        Так это недостаток вашей архитектуры, я же ваш пример рефакторил. Это у вас там количество может быть 0 и NULL. Как IFNULL кстати с индексами работает и прочей оптимизацией, не мешает планировщику оптимизировать? Ну и я вообще-то думал, что вы в курсе, что это можно без SQL написать, через OR или AND.

                                          0
                                          Последовательный доступ, чтобы никто не поменял позиции во время расчетов, можно обеспечить через SELECT FOR UPDATE и прочими локами.

                                          Вот их-то и нету, поэтому будет работать неверно и остатки будут расходиться.

                                          Если это 10 миллисекунд по сравнению с 1 миллисекунд, то в большинстве случаев это неважно. Вряд ли у одного документа миллионы позиций.

                                          БП подразумевает что закрытие документов делается группой людей в определённый интервал времени + таблица остатков довольно конкурентный объект.

                                          Да, это я по аналогии с batchInsert написал. Его можно самому добавить или имитировать через str_replace('INSERT INTO', 'REPLACE INTO').

                                          Т.е. код всё таки не дописанный.

                                          Так это недостаток вашей архитектуры, я же ваш пример рефакторил. Это у вас там количество может быть 0 и NULL. Как IFNULL кстати с индексами работает и прочей оптимизацией, не мешает планировщику оптимизировать?

                                          Это не недостаток архитектуры, поле kol может быть NULL в данной системе. IFNULL никак не мешает оптимизатору, т.к. поле не индексное
                                            0
                                            Вот их-то и нету, поэтому будет работать неверно и остатки будут расходиться.

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


                                            таблица остатков довольно конкурентный объект

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


                                            Т.е. код всё таки не дописанный.

                                            Дописанный. Допустим, у меня есть проект, где функция batchReplace уже есть, я написал ее один раз и использую везде где нужно. Или библиотеку подключил. Иначе в вашем коде надо считать исходники на C с реализацией IFNULL, INSERT, SELECT и т.д.


                                            поле kol может быть NULL в данной системе.
                                            IFNULL никак не мешает оптимизатору, т.к. поле не индексное

                                            А кешированию или чтению результатов? Просто kol можно с диска прочитать, а тут вычисления.
                                            Ситуация "Количество не указано" выглядит как смешение 2 сценариев обработки и повод для дополнительной нормализации. Но ок, может тут я не прав, и такие ситуации бывают.

                                              0
                                              Ну так добавить-то это не проблема. Можно вообще это делать централизованно за пределами этой функции. У вас этого кода тоже нет, он находится в движке БД. А тут будет находиться в движке фреймворка или в другом инфраструктурном коде.

                                              Проблема, не проблема, а вот из-за таких горе программистов потом система и разваливается. В тестах работает, для 3х человек работает, а потом опа, остатки-то не правильные, а проблему такого рода ооочень сложно локализовать, пишут процедуры пересчёта остатков, которые пользователи должны периодически запускать. Так, например, в 1С было, в 7ке точно.
                                              Да, за меня лочит данные СУБД. Как говорил Том Кайт:
                                              Если можно, сделай это с помощью одного оператора SQL.
                                              В моём коде СУБД гарантирует консистентность и транзакционность данных.
                                              Притом я так и не понял как за Вас движок фреймворка залочит данные?
                                              Напишите уж код который будет работать идентично моему.

                                              Ну так мы же лочим только строки для документа.

                                              Мда… (см. выше про горе программистов)

                                              Дописанный. Допустим, у меня есть проект, где функция batchReplace уже есть

                                              Ну дак приведите код, который будет работать как мой.

                                              Я так понимаю
                                              $document->curStocks
                                              тоже самописный метод, ОРМ такое не сгенерит, тож приведите его хотя б справочно.

                                              $document->getPositions()
                                              Это, я так понимаю, ОРМ-ный метод, хотя для того чтобы он заработал надо ещё в php модель данных описать и связи…
                                                0
                                                Проблема, не проблема, а вот из-за таких горе программистов потом система и разваливается.

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


                                                Напишите уж код который будет работать идентично моему.
                                                Ну дак приведите код, который будет работать как мой.

                                                Разговор был про компактность кода, а не про угадывание требований и их реализацию.
                                                Будет что-то вроде:


                                                Document::find($p_doc_id)->forUpdate()...

                                                Если метода forUpdate() нет в стандартном ActiveQuery, его можно самому написать. Это делается один раз.


                                                Я так понимаю $document->curStocks тоже самописный метод

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


                                                class Document
                                                {
                                                    public function getCurStocks()
                                                    {
                                                        return $this->hasMany(CurStock::class, [
                                                            'org_id' => 'org_id_addr',
                                                            'org_id_ur' => 'org_id_ur',
                                                            'mat_id' => $this->getPositions()->select('id')
                                                        ]);
                                                    }
                                                
                                                    public function getPositions()
                                                    {
                                                        return $this->hasMany(Position::class, ['doc_id' => 'id']);
                                                    }
                                                }

                                                hasMany() возвращает объект запроса, который и возвращает данные для заполнения свойства. Это пишется один раз и потом используется везде, где нужно.
                                                Вместо 'mat_id' => $this->getPositions()->select('id') наверно можно viaTable() использовать, но неохота лезть в документацию и разбираться как именно. Также наверно можно через обычный innerJoin() сделать.


                                                $document->getPositions() Это, я так понимаю, ОРМ-ный метод, хотя для того чтобы он заработал надо ещё в php модель данных описать и связи…

                                                Да, только вы связи описываете каждый раз в JOIN, а тут надо описать один раз.
                                                Модель данных в БД тоже нужно описать, это таблицы с полями и их типами.

                                                  0
                                                  Да, за меня лочит данные СУБД.

                                                  А поясните пожалуйста подробнее, что и по каким критериям лочит база и чем это отличается от выполнения тех же самых запросов в PHP-коде, которые приводили вы? В вашем PHP-коде получается тоже не лочится то что нужно?

                                                    –1
                                                    В моём php коде лочится всё что нужно и мой php-код работает идентично хранимке — консистентно, безопасно и максимально производительно.
                                                    Ваш php код не работает так как мой.

                                                      0

                                                      Ну так это, насколько я понимаю, обычный лок для транзакции, и открывая транзакцию с нужным уровнем можно добиться того же эффекта. То есть о чем я и говорил — регулируется на уровне вызывающего кода, до вызова close_doc() стартуем транзакцию с нужным уровнем изоляции, да хоть SERIALIZABLE, хотя наверно должно хватить того который по умолчанию. В самой функции ничего не меняется.


                                                      $transaction = $db->startTransaction(Transaction::SERIALIZABLE);
                                                      $service->close_doc($doc_id, $type);
                                                      $transaction->commit();

                                                      А как будет работать ваш код на PHP, если между INSERT и DELETE кто-то добавит запись с остатком равным 0? Она получается в первом запросе не учтется, а во втором удалится.


                                                      Ваш php код не работает так как мой.

                                                      Ну раз вы в третий раз повторяете, то и я повторю — разговор был о компактности кода, а не о работе окружающей его инфраструктуры. Тем более что хоть через транзакцию, хоть через forUpdate, новых строк в ней не добавляется.

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

                                                        А как будет работать ваш код на PHP, если между INSERT и DELETE кто-то добавит запись с остатком равным 0? Она получается в первом запросе не учтется, а во втором удалится.

                                                        В моём коде такого не произойдёт, до коммита эти строки будут заблокированы.

                                                        разговор был о компактности кода, а не о работе окружающей его инфраструктуры

                                                        с FOR UPDATE будет работать, но я так и не увидел финального рабочего варианта. Да и о какой компактности может идти речь, если:
                                                        — у меня всего 3 (три(!)) логических оператора в процедуре, у вас куча императивного кода, что и как перебираем, какие циклы открываем, куча переменных и логических шагов
                                                        — плюс к этому надо поддерживать какую-то странную структуру называемую «моделью», притом руками, если «модель» разошлась с БД, то никакие инструменты об этом не скажут
                                                        — на каждую выборку Вам надо писать магический метод, там где я просто напишу
                                                        [INNER | LEFT] JOIN cur_stock ON…
                                                        у Вас будут методы, которые роняют производительность системы и закидывают БД запросами
                                                        — сам императивный подход выстраивает логику программы таким образом, что нам надо описывать действия и подталкивает на ошибки консистентности и провалов производительности, что мы и видим на примере Вашего кода
                                                        SQL подход, напротив, подталкивает к написанию такого кода, который будет выполняться целостно и быстро
                                                        Алгоритм на php:
                                                        * извлечём данные об остатках (сходим в базу)
                                                        * извлечём данные из позиций документа (сходим в базу)
                                                        * откроем цикл и медленно посчитаем нужные данные
                                                        * выгрузим новые данные в БД
                                                        * упс, заблокировать данные забыли, система наелась
                                                        Алгоритм на SQL:
                                                        * хочу обновить остатки с учётом новых позиций
                                                        — Ваш код будет работать в 5-10 раз дольше. С учётом общей нагрузки системы, если каждую процедуру переписать на php таким же образом, то система оч быстро превратиться в тыкву.
                                                        Скажем 400-600 документов по 70-150 позиций, 10 операторов начинают закрывать + в этот момент блочатся остатки даже на чтение…
                                                        Мда… не взлетит, не будет работать

                                                        Ну и я так и не увидел финальный рабочий код.

                                                        Кста, у меня тут ещё от дедлоков нет защиты, по хорошему бы дописать, но код и так быстрый, поэтому было не трэба.
                                                          0
                                                          Ваш код будет работать в 5-10 раз дольше.

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

                                                            0
                                                            в реальной жизни приложуха станет тыквой

                                                            Знаю, поэтому и написал "да хоть".


                                                            Для любого кода я ес-но предполагал транзакцию, как Вы и написали

                                                            Ну то есть у вас за пределами PHP кода работают какие-то механизмы, которые обеспечивают консистентность данных. Что ж вы от меня их требуете?
                                                            Я увидел, что в приведенном коде между DB:Statement('INSERT') и DB:Statement('DELETE') согласованность не обеспечивается, и предположил, что механизмы согласованности подразумеваются по умолчанию, и мы сравниваем только выразительность логики.


                                                            с FOR UPDATE будет работать, но я так и не увидел финального рабочего варианта
                                                            Ну и я так и не увидел финальный рабочий код.

                                                            Я не знаю ни ТЗ, ни CREATE TABLE, ни какие источники в каких сценариях пишут в эти таблицы. Приводите конкретный пример того, какую ситуацию мы предотвращаем. А то я уже сделал предположения, а вам они не нравятся.


                                                            Да и о какой компактности может идти речь

                                                            SQL: 32 строки, 28 строк логики
                                                            PHP: 33 строки, 22 строк логики


                                                            всего 3 (три(!)) логических оператора в процедуре

                                                            Если вы INSERT + SELECT + JOIN + WHERE считаете за 1 логический оператор, то тогда в PHP за 1 логический оператор будем считать блок кода между фигурными скобками. Тогда у меня 1 логический оператор.
                                                            А еще можно сделать, чтобы все функции возвращали bool при успешном выполнении и результат через аргументы по ссылке, тогда их можно будет соединить через &&. Тоже один логический оператор получается.
                                                            Прекращайте уже логические манипуляции.


                                                            у вас куча императивного кода

                                                            А у вас куча декларативного. Причем больше, чем императивного в PHP.


                                                            что и как перебираем, какие циклы открываем, куча переменных и логических шагов

                                                            Перебираем те же данные, что и у вас. Цикл там один. Переменных аж целых 4 штуки, уж куча так куча, причем $curStockList можно убрать, она осталась от предыдущего варианта кода. Вот кстати еще на одну строку меньше. А $newData в вашем коде тоже присутствует в неявном виде, и при изучении кода ее надо держать в голове, называется она "результат SELECT". Да в общем-то количество неподходящих записей и документ из таблицы t_docs тоже есть в неявном виде. Это минус вашего варианта, а не плюс.


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


                                                            плюс к этому надо поддерживать какую-то странную структуру называемую «моделью»

                                                            А вам в БД таблицы поддерживать не надо, они там сами по себе появляются и изменяются?


                                                            если «модель» разошлась с БД

                                                            Так не надо лезть в продакшн БД руками помимо кода. Все изменения только через код. Так же как в вашем подходе не надо лезть в файлы БД шестнадцатиричным редактором.


                                                            на каждую выборку Вам надо писать магический метод, там где я просто напишу INNER JOIN

                                                            Это ложь. Я специально написал "Это пишется один раз и потом используется везде, где нужно". Это вы везде пишете INNER JOIN по одним и тем же полям, а здесь поля для связи описываются в однм месте. Не на каждую выборку отдельно, на все сразу, то есть используются в любых выборках, где нужен этот джойн.
                                                            В базе аналогично было бы следующее — описываем FOREIGN KEY с названием positions и потом ссылаемся на него FROM t_docs INNER JOIN doc_pos USING positions без всяких ON. Но такого пока нигде не сделали.


                                                            и закидывают БД запросами

                                                            Это тоже ложь. Методы сами не отправлют запрос в БД, они используются в запросах в вызывающем коде, и дополнительные подзапросы в них тоже сами не добавляют, если это не сделано специально вручную. Они просто описывают связь между таблицами, как FOREIGN KEY в базе.


                                                            что мы и видим на примере Вашего кода

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


                                                            SQL подход, напротив, подталкивает к написанию такого кода, который будет выполняться целостно и быстро

                                                            Нет, SQL выполняется целостно потому что в базе есть механизмы обеспечения целостности. К синтаксису SQL и компактности кода это не имеет никакого отношения.


                                                            Алгоритм на SQL:
                                                            хочу обновить остатки с учётом новых позиций

                                                            Перестаньте использовать логические манипуляции.
                                                            Алгоритм на SQL:
                                                            — Выбрать поднять ошибку. Непонятно, как это переводится на нормальный язык. Ой, оно же еще не всегда срабатывает, и вообще срабатывать не должно.
                                                            — Выбрать из таблицы t_docs, соединить с таблицей doc_pos через поля id и doc_id, соединить с таблицей doc_types через поля doc_type_id и id.
                                                            — Вставить результат в cur_stock.
                                                            — Удалить из таблицы cur_stock, соединить t_docs с таблицей doc_pos через поля id и doc_id, соединить с таблицей cur_stock через поля mat_id и mat_id, org_id и org_id, org_id_ur и org_id_ur, там где количество равно 0. Ой, там же во втором наборе полей для cur_stock не org_id, а org_id_addr.


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

                                                            Да, много у кого работают нагруженные приложения на PHP, а они и не знают, что у них система в тыкву превратилась.


                                                            Скажем 400-600 документов по 70-150 позиций, 10 операторов начинают закрывать

                                                            Это не выглядит как большая нагрузка. В интернет-магазинах на PHP тысячи товаров в базе, десятки в корзинах, и миллионы пользователей, которые это покупают. И остатки там тоже отслеживаются.

                                                              0
                                                              Михаил, я обновил код до 20 строк в соседней ветке
                                                                0
                                                                Ну то есть у вас за пределами PHP кода работают какие-то механизмы, которые обеспечивают консистентность данных. Что ж вы от меня их требуете?
                                                                Я увидел, что в приведенном коде между DB:Statement('INSERT') и DB:Statement('DELETE') согласованность не обеспечивается, и предположил, что механизмы согласованности подразумеваются по умолчанию, и мы сравниваем только выразительность логики.

                                                                Нет, нет, я говорю совсем о другом.
                                                                И Ваш и мой код обеспечивается транзакцией (стандартной, REPEATABLE_READ), но Ваш код не работает как мой.
                                                                Т.е. и Ваш и мой код выглядит так:
                                                                START TRANSACTION;
                                                                <ВАШ_КОД>
                                                                COMMIT;

                                                                START TRANSACTION;
                                                                <МОЙ_КОД>
                                                                COMMIT;

                                                                Но Ваш код не работает так как мой код.
                                                                  0
                                                                  Нет, нет, я говорю совсем о другом.
                                                                  И Ваш и мой код обеспечивается транзакцией

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


                                                                  Раз вы пример ситуации не привели, снова сделаю предположение. В разных документах могут быть одинаковые mat_id, поэтому блокировки через FOR UPDATE по документу недостаточно, надо добавлять блокировку по mat_id.


                                                                  - $document = Document::find($p_doc_id)->with(['positions', 'type'])->one();
                                                                  + $document = Document::find($p_doc_id)->forUpdate()->with(['positions', 'type'])->one();
                                                                  - $curStockList = $document->curStocks;
                                                                  + $curStockList = $document->getCurStocks()->forUpdate()->all();

                                                                  То есть в таблице cur_stocks заблокируются записи по условию [org_id, org_id_ur, mat_id = [список]). Тогда мой код должен работать так, как ваш.
                                                                  Поменяются 2 строки, новых строк не добавится.


                                                                  я обновил код до 20 строк в соседней ветке

                                                                  Зато потеряли begin/end и пару комментов. Если в моем коде так сделать, будет 17 строк.


                                                                  WHEN MATCHED AND (s.kol + d.kol = 0 AND s.reserve + d.reserve = 0) THEN DELETE

                                                                  А это выглядит как потенциальный баг, когда одно количество положительное, а другое равное ему отрицательное. У вас при вставке умножается на -1, значит это возможно.

                                                                    0
                                                                    А это выглядит как потенциальный баг

                                                                    А нет, не выглядит, это же выражение для ситуации до внесения изменений. Зато это дополнительный аргумент про сложность понимания и поддержки кода.

                                                                      0
                                                                      Наконец-то Вы нужные FOR UPDATE'ы написали.
                                                                      Вот именно про это я и говорил, когда горе программистов упоминал. А в моём случае, исключив императивный код, такой проблемы просто не возникало. Если можно, сделай это с помощью одного оператора SQL.
                                                                      Ну и сейчас хоть Ваш код и соответствует моему, но работает очень медленно и в реальности не жизнеспособен.
                                                                      Ещё BatchReplace я так и не увидел
                                                                        0
                                                                        Наконец-то Вы нужные FOR UPDATE'ы написали.

                                                                        Ну так я бы написал их раньше, если бы вы отвечали на мои вопросы. А то вы требуете у меня, чтобы я догадался, что вы имеете в виду. Это вы знаете, что у вас есть в базе, что оно моделирует, и что в каком порядке вызывается, а для меня это просто какие-то таблицы и поля.


                                                                        А в моём случае, исключив императивный код, такой проблемы просто не возникало.

                                                                        Оно не возникало потому что в базе есть отдельно реализованные механизмы блокировки, а не из-за декларативности кода. Если бы вместо SQL можно было скрипты на python писать, движок бы все равно мог отслеживать используемые таблицы и блокировать нужные записи.


                                                                        но работает очень медленно и в реальности не жизнеспособен.

                                                                        Повторю, в сотнях интернет-магазинов на PHP логика написана в коде, остатки там отслеживаются, и все это работает нормально и достаточно быстро.


                                                                        Ещё BatchReplace я так и не увидел

                                                                        Зачем он вам, это библиотечный код, только в конце будет REPLACE вместо INSERT. В таком случае с вас исходники реализации INSERT INTO, будем их тоже учитывать.

                                                                          +1
                                                                          Ну так я бы написал их раньше, если бы вы отвечали на мои вопросы. А то вы требуете у меня, чтобы я догадался, что вы имеете в виду. Это вы знаете, что у вас есть в базе, что оно моделирует, и что в каком порядке вызывается, а для меня это просто какие-то таблицы и поля.

                                                                          На какие вопросы? Какие поля, какие таблицы? Я Вам привёл код, 3 оператора SQL, Вы попытались их повторить на php, получилось, но далеко не с первого раза и производительность и качество кода оставляет желать лучшего.
                                                                            0
                                                                            На какие вопросы?

                                                                            "А поясните пожалуйста подробнее, что и по каким критериям лочит база и чем это отличается от выполнения тех же самых запросов в PHP-коде, которые приводили вы?"
                                                                            "Приводите конкретный пример того, какую ситуацию мы предотвращаем."


                                                                            но далеко не с первого раза

                                                                            С первого. Потому что изначально речь шла про компактность, а требование "покажите блокировки" вы придумали потом. При этом сначала вы изложили его в виде "сделайте чтобы он работал так же как мой", не пояснив, как именно "так же", и про блокировки мне пришлось догадываться самому, а потом не объяснили, какие блокировки вам нужны, а какие подразумеваются, что мне тоже пришлось угадывать.


                                                                            производительность и качество кода оставляет желать лучшего.

                                                                            Производительность в 10 миллисекунд меня устраивает, даже если в вашем варианте 1 миллисекунда. Причем я даже не уверен, что будет сильно большая разница, так как за счет одинаковых селектов будет лучше работать кеширование самой базы. Условно, менеджер сначала открыл страницу с документом, там отобразились его позиции, потом нажал кнопку "закрыть документ", и тот же запрос выполнился для получения позиций при обработке. Но это надо замерять на практике.


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

                                                                              +1
                                                                              С первого раза Вы написали забагованый код (ну можно назвать его «код для однопользовательской системы»).

                                                                              Ну и не будет Ваш код работать 10мс, в реальных условиях
                                                                                0

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

                                                                                  0
                                                                                  поэтому Ваш код не работал так как мой в многопользовательской системе. Транзакцию я всегда всегда предполагаю от начала запроса.
                                                                                    0

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

                                                                                      –1
                                                                                      Ну что за дилетанство, ес-но серьёзная система не может работать на автокоммитах
                                                                                      +2
                                                                                      Транзакцию я всегда всегда предполагаю от начала запроса.

                                                                                      А это иллюстрация того, что у всех разные "предположения", и их неплохо бы озвучивать, если вы хотите сравнивать поведение.

                                                                                        –3
                                                                                        Эм… как бы это не я просил Михаила повторить логику моего кода на php, это он сам вызвался, вопросов он не задавал. Ну результат мы видим…
                                                                                          0

                                                                                          Зачем я должен задавать вопросы, если они уже были заданы? Вы привели свой пример в качестве ответа на вопрос "в каком случае хранимка получится компактнее, чем php код". Лично вас никто об этом не просил, вы сами вызвались.

                                                                                            –2
                                                                                            Да, высказался и привёл 2 идентично работающих листинга, а Вы попытались повторить, не получилось. Что я должен был объяснять? Объяснять что атомарный оператор DML обеспечивает консистентность и блокировки? Документацию по БД скидывать? Или то что после INSERT ODKU, до коммита, изменённые и вставленные строки будут иметь эксклюзивную блокировку, поэтому между INSERT'ом и DELETE'ом в моём коде никто бы не мог нарушить целостность данных. Всё это есть в документации, в концепциях СУБД. Мой код без сайд эффектов, и SQL, и php, никто извне никакие строки не блокирует. Это очевидно после прочтения листинга.
                                                                                              +2
                                                                                              Что я должен был объяснять?

                                                                                              Вы не должны были ничего объяснять, вы должны были не требовать показать блокировки, так как вопрос был про компактность. Сами начали спорить про компактность, а от меня почему-то блокировки потребовали. Вы подразумевали работающие транзакции, а я подразумевал работающие транзакции и блокировки. Почему вам можно что-то подразумевать, а мне нельзя?


                                                                                              Мой код без сайд эффектов, и SQL, и php

                                                                                              В вашем коде на PHP нет вызова START TRANSACTION, поэтому правильность поведения между INSERT и DELETE не гарантируется, так же как и между SELECT raise_error и SELECT данных для INSERT. Вы ее просто подразумевали. В вашем коде нет гарантии консистентности данных между INSERT и DELETE, в моем еще и между SELECT и INSERT.


                                                                                              а Вы попытались повторить, не получилось

                                                                                              Получилось. Вы хотели показать, что код на хранимках компактнее, а оказалось, что мой код на PHP компактнее вашего на SQL.

                                                                                                –1
                                                                                                даже если брать без транзакций, то у Вас должны были быть фор апдейты чтобы код работал так как мой.
                                                                                                Ну и далее по пунктам:
                                                                                                1. ваш код не компактнее
                                                                                                2. + ещё должен быть какой-то самописный код batchReplace
                                                                                                3. почему то в свой код Вы не включили getPositions, getCurstock
                                                                                                4. декларация моделей
                                                                                                5. Ваш код работает существенно медленнее моего поэтому его невозможно применить в реальных условиях

                                                                                                Ну в принципе да, если забить на целостность данных, на производительность, на простоту поддержки, забыть про то, чтобы код работал надо поддерживать модели, на каждый джоин и каждую выборку надо написать свой магический метод, то да Ваш код можно назвать более «компактным»
                                                                                                  0
                                                                                                  Ваш код работает существенно медленнее моего поэтому его невозможно применить в реальных условиях

                                                                                                  Это утверждение нуждается в доказательстве.


                                                                                                  Ну в принципе да, если забить на [...] на простоту поддержки

                                                                                                  Есть мнение, что код на PHP как раз проще в поддержке для того, кто его написал, чем ваш код на SQL.

                                                                                                    0
                                                                                                    Это утверждение нуждается в доказательстве.

                                                                                                    Подготовьте рабочий php код. Если готовы, то я готов подготовить тестовые данные.
                                                                                                      –2

                                                                                                      Неа. Вы утверждение сделали, вам и доказывать.

                                                                                                        0
                                                                                                        Ну дак код-то дайте рабочий! С которым я смогу это доказать.
                                                                                                          –2

                                                                                                          Подождите, вы же уже сделали утверждение, хотя кода у вас нет. Ну вот уже и доказывайте.

                                                                                                            0
                                                                                                            Моё утверждение верное, не рабочий код не работает вообще, поэтому мой код как минимум в 10 раз быстрее.
                                                                                                            Какой-то у Вас не конструктив, а упражнения в софистике… ))
                                                                                                              0
                                                                                                              Какой-то у Вас не конструктив, а упражнения в софистике…

                                                                                                              Я просто считаю утверждения "Ваш код работает существенно медленнее моего поэтому его невозможно применить в реальных условиях" без конкретных цифр — неконструктивными.

                                                                                                                0
                                                                                                                Экспертное мнение. Я уже показывал Михаилу как его код работает в 3-4 раза медленнее в подобном кейсе, логика в SQL, и логика в php. Могу ещё раз показать, но если реально хотите, то дайте мне рабочий код, а не упражняйтесь в софистике.
                                                                                                                  +2
                                                                                                                  Экспертное мнение.

                                                                                                                  Ну вот мое "экспертное мнение" состоит в том, что при выносе логики из SQL скорость падает, конечно, но не настолько, чтобы было "невозможно применить в реальных условиях". В конце концов, у меня тут под боком энтерпрайз-приложение, тысячи реальных установок, логика на application-сервере, и работает.


                                                                                                                  Дело же не в том, что медленнее. Дело в том, насколько медленнее.


                                                                                                                  Насколько я понял, мнение michael_vostrikov приблизительно такое же.

                                                                                                                    0

                                                                                                                    Да, все верно.

                                                                                                                      0
                                                                                                                      А мой опыт говорит о том, что если то же самое реализовать на SQL, то система в целом:
                                                                                                                      — существенно более производительна, а значит
                                                                                                                      — гораздо дольше времени не требует горизонтального масштабирования, следовательно
                                                                                                                      — требует меньше ресурсов на администрирование
                                                                                                                      — содержит меньше кода
                                                                                                                      — содержит меньше ошибок (пример выше, забыли написать FOR UPDATE, данные поплыли)
                                                                                                                      — ну и как следствие, проще разрабатывать и поддерживать, требуется меньше разработчиков
                                                                                                                      В общем использование фич СУБД, существенно упрощает разработку, нежели пытаться изобретать велосипеды и писать всё это вручную.

                                                                                                                      michael_vostrikov, Михаил, ну добейте тогда уж код до состояния, чтобы мы могли сравнить производительность
                                                                                                                        +2
                                                                                                                        А мой опыт говорит о том, что если то же самое реализовать на SQL, то система в целом:

                                                                                                                        А, простите, какой у вас опыт разработки крупных систем вне SQL?


                                                                                                                        Потому что мой опыт прямо противоречит вашему, и что мы будем с этим делать?

                                                                                                                          0
                                                                                                                          переписывал несколько систем на SQL, т.к. системы не справлялись с нагрузкой и были очень сложны в поддержке. Последний опыт — удалось сократить затраты на аренду серверов с $35к/мес до $2к/мес, ну и кодовая база сократилась на порядок.

                                                                                                                          и что мы будем с этим делать?

                                                                                                                          Ну мы с Михаилом договорились рассмотреть на каком-нть примере. В принципе этот пример, о котором сейчас дискутируем вполне подходит. В него ещё включить, то что мы с ним уже рассматривали: получение списка товаров с ценами + добавить в него еще немного реальных условий
                                                                                                                            +4
                                                                                                                            переписывал несколько систем на SQL, т.к. системы не справлялись с нагрузкой и были очень сложны в поддержке

                                                                                                                            Ну вот я переписывал несколько систем с SQL, потому что стоимость поддержки и количество ошибок были избыточными. При переходе на application стоимость поддержки (и развития) уменьшилась, а производительность не упала.

                                                                                                      0
                                                                                                      то у Вас должны были быть фор апдейты чтобы код работал так как мой

                                                                                                      Вот когда я понял, что вы подразумеваете под "так как мой", я их написал. А то транзакции вы подразумеваете, а другие механизмы нет, поди разберись.


                                                                                                      ваш код не компактнее

                                                                                                      Компактнее. Там меньше строк кода, цифры я приводил.


                                                                                                      ещё должен быть какой-то самописный код batchReplace

                                                                                                      А у вас должен быть реализован INSERT INTO. Он у вас уже есть в базе? А у меня уже есть batchReplace в приложении, я просто подключил нужную библиотеку.


                                                                                                      почему то в свой код Вы не включили getPositions, getCurstock

                                                                                                      Я уже несколько раз повторил почему — потому что они описываются один раз и используются в любых джойнах этих таблиц. Вы вот почему-то CREATE TABLE и FOREIGN KEY не включили.


                                                                                                      декларация моделей

                                                                                                      А у вас декларация таблиц.


                                                                                                      Ваш код работает существенно медленнее моего поэтому его невозможно применить в реальных условиях

                                                                                                      Недоказанное утверждение про "существенно" и "невозможно". Аналогичный код работает во многих приложениях. Вы говорили про 10 одновременно работающих операторов, это довольно мало. Возможно у вас это серьезная нагрузка потому что база занята выполнением логики. Тут вот пишут про несколько сотен запросов на страницу для Drupal, тут для Magento получилось уменьшить с 2000 до 270+, это популярные продукты, которые много где используются. Правда там наверно селекты в основном.


                                                                                                      если забить на целостность данных

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


                                                                                                      чтобы код работал надо поддерживать модели

                                                                                                      А чтобы ваш код работал, надо поддерживать таблицы. Почему таблицы поддерживать можно, а модели нельзя?


                                                                                                      на каждый джоин и каждую выборку надо написать свой магический метод

                                                                                                      Я не знаю, почему вы так решили, но если вам нравится верить в эти выдумки, дело ваше. Фактов это не меняет. Как я уже писал в другом комментарии, на любой FOREIGN KEY магический метод пишется один раз, а не на каждый джойн и выборку.

                                                                                                        0
                                                                                                        А у вас декларация таблиц.

                                                                                                        А у Вас таблицы сами появляются?

                                                                                                        Вы вот почему-то CREATE TABLE и FOREIGN KEY не включили.

                                                                                                        Потому что CREATE TABLE и FOREIGN KEY в Вашем и в моём коде будут идентичные.

                                                                                                        Почему таблицы поддерживать можно, а модели нельзя?

                                                                                                        Вам надо поддерживать и модели, и таблицы. Ещё и руками следить за соответствием моделей и таблиц.
                                                                                                          +2
                                                                                                          А у Вас таблицы сами появляются?

                                                                                                          Таблицы вполне могут создаваться из моделей. EF CodeFirst, вот это всё.


                                                                                                          Потому что CREATE TABLE и FOREIGN KEY в Вашем и в моём коде будут идентичные.

                                                                                                          Не обязательно.


                                                                                                          Вам надо поддерживать и модели, и таблицы. Ещё и руками следить за соответствием моделей и таблиц.

                                                                                                          Еще раз: code-first development. Структура описывается в коде, из кода генерится БД, из кода генерятся запросы. Никто БД руками не трогает.

                                                                                                            +1
                                                                                                            А у Вас таблицы сами появляются?

                                                                                                            Таблицы в базе задаются и изменяются через механизм миграций, миграция это код на PHP.


                                                                                                            Потому что CREATE TABLE и FOREIGN KEY в Вашем и в моём коде будут идентичные.

                                                                                                            Вы свой код написали до того, как я написал свой. При этом по каким-то причинам не включили туда объявления таблиц и внешних ключей. Вот я не включил объявление классов моделей AR и связей между ними в свой код по тем же причинам.


                                                                                                            Вам надо поддерживать и модели, и таблицы. Ещё и руками следить за соответствием моделей и таблиц.

                                                                                                            Прекращайте уже делать недоказанные утверждения, если не знаете, как разрабатываются приложения с логикой в коде) Изменения делаются через механизм миграций, руками в базу никто не лазит. Они же гарантируют идентичность dev, test и prod базы.


                                                                                                            А еще, представьте себе, в случае SPA на фронтенде в JavaScript снова эти же классы описаны — пользователь, заказ и т.д. И на это тоже есть обоснованные причины.

                                                                                                              0
                                                                                                              Прекращайте уже делать недоказанные утверждения, если не знаете, как разрабатываются приложения с логикой в коде) Изменения делаются через механизм миграций, руками в базу никто не лазит. Они же гарантируют идентичность dev, test и prod базы.

                                                                                                              Я предполагал следующее: и у меня и у Вас механизм миграций создаёт таблицы в БД. А модели у Вас сами обновляются после миграций?

                                                                                                              Давайте уже финальный код допилите, я могу тестовые данные подготовить. Мы ж с Вами хотели попробовать что-то подобное сделать, вот давайте это пример и добьём до конца.
                                                                                                                0
                                                                                                                Я предполагал следующее: и у меня и у Вас механизм миграций создаёт таблицы в БД. А модели у Вас сами обновляются после миграций?

                                                                                                                Вы, простите за прямой вопрос, так и не поняли, что миграции получаются из моделей?

                                                                                                                  0
                                                                                                                  нет, тот код который приводил Михаил был на Yii, там вроде нет такого. В Ларавеле с которым работаю, тоже такого нет. Пишем миграцию, правим модель.
                                                                                                                    0

                                                                                                                    Сочувствую вам.

                                                                                                                  0

                                                                                                                  В Yii надо поменять модель и написать миграцию. Вернее, список полей модели берется из БД, поэтому "поменять модель" означает обычно "обновить коммент с PHPDoc, чтобы PHPStorm нужные подсказки генерировал". В Symfony кажется схема генерируется из описания модели. В любом случае руками следить не надо, запустил yii migrate up, и все отсутствующие миграции накатились.


                                                                                                                  Мы ж с Вами хотели попробовать что-то подобное сделать

                                                                                                                  Давайте, через пару дней выложу.

                                                                                                                    +1
                                                                                                                    > Symfony кажется сИ, если ничего не путаю, Yii3 будет её поддерживать из коробки как одну из опцийхема генерируется из описания модели.

                                                                                                                    Точнее так работает Doctrine, которая является ORM по умолчанию в Symfony. Но её можно подключать и к Laravel, например. И, если ничего не путаю, то Yii3 будет е' поддерживать из коробки как одну из опций.
                                                                                                                      0
                                                                                                                      А пример можете привести как это работает?
                                                                                                                      Например, у меня есть 2 миграции
                                                                                                                      Миграция 1:
                                                                                                                      CREATE TABLE users (
                                                                                                                          id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
                                                                                                                          , name VARCHAR(191) NOT NULL
                                                                                                                      )

                                                                                                                      Миграция 2:
                                                                                                                      ALTER TABLE users
                                                                                                                      ADD email VARCHAR(191) UNIQUE


                                                                                                                      Что было в ОРМ модели в момент первой миграции и как надо изменить ОРМ модель чтобы получилась вторая миграци? И как мне ролбекнуть 2ю миграцию?
                                                                                                                        0
                                                                                                                        class InitialMigration : Migration {
                                                                                                                          public void Up(MigrationBuilder migrationBuilder) {
                                                                                                                            migrationBuilder.AddColumn<int>(
                                                                                                                            name: "id",
                                                                                                                            table: "users")
                                                                                                                            .HasDatabaseGeneratedOption(
                                                                                                                                DatabaseGeneratedOption.Identity);
                                                                                                                        
                                                                                                                            migrationBuilder.AddColumn<string>(
                                                                                                                            name: "name",
                                                                                                                            table: "users",
                                                                                                                            sqlType: "VARCHAR(191)");
                                                                                                                          }
                                                                                                                        }
                                                                                                                        
                                                                                                                        class FirstMigration : Migration {
                                                                                                                          public void Up(MigrationBuilder migrationBuilder) {
                                                                                                                            migrationBuilder.AddColumn<string>(
                                                                                                                            name: "email",
                                                                                                                            table: "users",
                                                                                                                            sqlType: "VARCHAR(191)",
                                                                                                                            unique: true);
                                                                                                                          }
                                                                                                                          public void Down(MigrationBuilder migrationBuilder) {
                                                                                                                            migrationBuilder.DropColumn<string>("users", "email")
                                                                                                                          }
                                                                                                                        }

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


                                                                                                                        Как работать с миграциями дальше очевидно, если нет то вот небольшая дока: https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/applying?tabs=dotnet-core-cli#command-line-tools

                                                                                                                          0
                                                                                                                          Что такое миграции и как они работают я знаю. Т.е. я сам пишу SQL для накатывания миграции и откатывания. Я так понял в Доктрине есть какая-то магия которая сама напишет миграцию при добавлении поля в Модели ОРМ
                                                                                                                            0

                                                                                                                            Было


                                                                                                                            /**
                                                                                                                             * @ORM/Entity
                                                                                                                             * @ORM/Table("users")
                                                                                                                             */
                                                                                                                            class User
                                                                                                                            {
                                                                                                                              /**
                                                                                                                               * @ORM/Id
                                                                                                                               * @ORM/Column(type="integer")
                                                                                                                               * @ORM/GeneratedValue
                                                                                                                               */
                                                                                                                              private int $id;
                                                                                                                              /**
                                                                                                                               * @ORM/Column(type="string", length="191")
                                                                                                                               */
                                                                                                                              private string $name;
                                                                                                                            }

                                                                                                                            стало


                                                                                                                            /**
                                                                                                                             * @ORM/Entity
                                                                                                                             * @ORM/Table("users")
                                                                                                                             */
                                                                                                                            class User
                                                                                                                            {
                                                                                                                              /**
                                                                                                                               * @ORM/Id
                                                                                                                               * @ORM/Column(type="integer")
                                                                                                                               * @ORM/GeneratedValue
                                                                                                                               */
                                                                                                                              private int $id;
                                                                                                                              /**
                                                                                                                               * @ORM/Column(type="string", length="191")
                                                                                                                               */
                                                                                                                              private string $name;
                                                                                                                              /**
                                                                                                                               * @ORM/Column(type="string", length="191", unique="true", nullable="true")
                                                                                                                               */
                                                                                                                              private string $email;
                                                                                                                            }

                                                                                                                            Дальше можно запустить просто обновление схемы БД (не очень подходит для продакшена) — вычислит дифф и запустит ALTER TABLE, а можно запустить генерацию миграции, которая завернёт этот ALTER TABLE в PHP-код в up методе, а также сгенерирует обратный в down


                                                                                                                            Маппинг можно задавать аннотациями, как в примере, можно в XML, YAML и просто PHP-кодом "собрать".


                                                                                                                            В принципе Doctrine2 можно считать портом на PHP Hibernate из Java

                                                                                                                              +1
                                                                                                                              т.е. а если мне надо будет расширить поле до 255, то я правлю камент
                                                                                                                              * ORM/Column(type=«string», length=«255», unique=«true», nullable=«true»)
                                                                                                                              и мне сгенериться
                                                                                                                              ALTER TABLE users MODIFY email VARCHAR(255)
                                                                                                                              -- MIGRATION_ROLLBACK
                                                                                                                              ALTER TABLE users MODIFY email VARCHAR(191)
                                                                                                                              

                                                                                                                              ?
                                                                                                                              страшно как-то в каментах прогать… если конечно можно в XML, YAML, PHP то лучше…

                                                                                                                              А составные и/или DESC индексы как в модели декларируются?

                                                                                                                              а если что-нть посложнее надо, то тоже в модели делается?
                                                                                                                              ALTER TABLE users
                                                                                                                              ADD phone VARCHAR(30)
                                                                                                                              , ADD phone_clean VARCHAR(11) GENERATED ALWAYS AS (REGEXP_REPLACE(phone, '[^0-9]', '')) VIRTUAL
                                                                                                                              , ADD CONSTRAINT phone_chk CHECK (phone_clean IS NULL OR CHAR_LENGTH(phone_clean) = 11)
                                                                                                                              , ADD UNIQUE KEY users_phone_clean_uk (phone_clean)
                                                                                                                              

                                                                                                                              Или такое уже не по фэншую создавать в базе? И поле phone_clean создаётся и поддерживается руками?

                                                                                                                              А, например, гео-поля и индексы как?
                                                                                                                              ALTER TABLE geo_objects
                                                                                                                              ADD geo_point POINT GENERATED ALWAYS AS (ST_SRID(POINT(lng, lat)), 4326)) STORED NOT NULL SRID 4326
                                                                                                                              , ADD SPATIAL INDEX (geo_point)
                                                                                                                              

                                                                                                                                +1

                                                                                                                                Да, сгенерятся ALTER TABLE users MODIFY email


                                                                                                                                Индексы всякие как параметры @Table задаются, например


                                                                                                                                @Table(name="ecommerce_products",indexes={@Index(name="search_idx", columns={"name", "email"}, options={"where": "(((id IS NOT NULL) AND (name IS NULL)) AND (email IS NULL))"})})


                                                                                                                                CHECK к @Column


                                                                                                                                Генерированные поля из коробки только по именованным sequence из сложного.


                                                                                                                                Геополей из коробки вроде нет, но делаются кастомные достаточно легко: https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/cookbook/advanced-field-value-conversion-using-custom-mapping-types.html#the-mapping-type

                                                                                                                                  –2
                                                                                                                                  Вам не кажется, что всё это дико напоминает велосипед с квадратными колёсами, который чтобы ехал надо ещё и уметь толкать:
                                                                                                                                  1. уродливый синтаксис, который нужно знать
                                                                                                                                  2. не предоставляет всех возможностей SQL
                                                                                                                                  3. некоторый возможности предоставляет с дополнительным шаманством
                                                                                                                                  4. PsyHaSTe ниже написал, что иногда в миграции надо ещё и руками залезать, подправлять
                                                                                                                                  5. в итоге всё равно получаем SQL, который в БД выполняется
                                                                                                                                  6. после добавления поля в модель ещё миграцию не забыть сгенерировать
                                                                                                                                  7. а в итоге экономии то никакой и нет
                                                                                                                                  Я пишу:
                                                                                                                                  ALTER TABLE users ADD email VARCHAR(191) UNIQUE
                                                                                                                                  -- MIGRATION_ROLLBACK
                                                                                                                                  ALTER TABLE users DROP email
                                                                                                                                  

                                                                                                                                  Вы пишите:
                                                                                                                                  /**
                                                                                                                                   * @ORM/Column(type="string", length="191", unique="true", nullable="true")
                                                                                                                                   */
                                                                                                                                  private string $email;
                                                                                                                                  

                                                                                                                                  Притом по кол-ву символов (которые так любит Михаил считать))) в SQL даже меньше.
                                                                                                                                    0

                                                                                                                                    Вот только ваши миграции не дают возможности увидеть итоговую схему БД, а модель в ORM — даёт.

                                                                                                                                      0
                                                                                                                                      Что простите?
                                                                                                                                      SHOW CREATE TABLE users
                                                                                                                                        +3

                                                                                                                                        Для того чтобы так сделать — нужна живая СУБД, со всеми миграциями. А она не всегда есть, например при изучении кода через веб-интерфейс гитхаба её нет.

                                                                                                                                          0
                                                                                                                                          никто не запрещает выгрузить все таблицы в виде файлов и справочно иметь с кодом, при миграции можно обновлять
                                                                                                                                      +4
                                                                                                                                      Вам не кажется, что всё это дико напоминает велосипед с квадратными колёсами, который чтобы ехал надо ещё и уметь толкать:

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

                                                                                                                                        0
                                                                                                                                        «Наша» задача — работать с СУБД из удобного нам языка программирования

                                                                                                                                        Но ведь все равно приходится из другого языка отправлять SQL-запросы!
                                                                                                                                        Или у вас библиотека позволяющая не знать SQL?
                                                                                                                                          0
                                                                                                                                          Или у вас библиотека позволяющая не знать SQL?

                                                                                                                                          Именно. У нас есть инструмент, позволяющий для подавляющего большинства случаев не знать SQL.

                                                                                                                                            0
                                                                                                                                            А можно пример?
                                                                                                                                            А то, тут в примерах, практически везде внутри другого языка программирования используют вставки на SQL.
                                                                                                                                              0
                                                                                                                                              _orders
                                                                                                                                              .Open()
                                                                                                                                              .CreatedByUser(_userId)
                                                                                                                                              .Where(o => o.Lines.Count() < 5
                                                                                                                                                && o.Lines.Sum(l => l.Amount) > 1000))
                                                                                                                                              .OrderByDesc(o => o.Date)
                                                                                                                                              
                                                                                                                                            0

                                                                                                                                            Да, есть у нас такая библиотека, даже несколько: Linq2SQL, Linq2DB, Entity Framework, EF Core...

                                                                                                                                              0
                                                                                                                                              Можно пару-тройку примеров, того как в этих библиотеках выглядит аналог джойна?
                                                                                                                                              И как там сделать триггер?
                                                                                                                                                +1
                                                                                                                                                Можно пару-тройку примеров, того как в этих библиотеках выглядит аналог джойна?

                                                                                                                                                Джойнов там стараются избегать, потому что джойн обычно представляется коллекций связанных объектов. Так, в примере выше o.Lines — это строки заказа, то, что вы в РСУБД вы бы выражали через Orders JOIN OrderLines. Но если очень надо, то


                                                                                                                                                orders.Join(customers, o => o.CustomerId, c => c.Id)

                                                                                                                                                И как там сделать триггер?

                                                                                                                                                Никак. Триггеры — это логика на стороне БД, то есть то, чего мы явно хотим избежать.


                                                                                                                                                  0
                                                                                                                                                  Джойнов там стараются избегать, потому что джойн обычно представляется коллекций связанных объектов

                                                                                                                                                  Представим, что:
                                                                                                                                                  — у нас есть легаси: джойн plsql
                                                                                                                                                  — и есть легаси: триггер plsql
                                                                                                                                                  — а мы хотим всё это переписать на php или java-script и с использованием библиотек не требующих знания SQL
                                                                                                                                                  как это сделать?
                                                                                                                                                  Речь не о проектировании всей базы с нуля с новой архитектурой, а о том, чтобы переписать по частям всю живую базу.
                                                                                                                                                    +1
                                                                                                                                                    как это сделать?

                                                                                                                                                    Легаси-джойны вас не волнуют, потому что вы все равно можете моделировать отношения в ORM. Если джойн слишком сложный (настолько сложный, что отношения не справляются), придется написать хелперный метод, который опирается на базовый джойн фреймворка, который я тоже показал выше.


                                                                                                                                                    Легаси-триггеры вы не описываете никак. Вы просто их используете вплоть до того момента, пока не готовы их убить и переписать как часть application-сервера.

                                                                                                                                                      0

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


                                                                                                                                                      Впрочем, это не означает что ORM в такой ситуации совсем бесполезна.


                                                                                                                                                      у нас есть легаси: джойн plsql

                                                                                                                                                      При переписывании на ORM он может либо исчезнуть, либо так и остаться джойном.


                                                                                                                                                      и есть легаси: триггер plsql

                                                                                                                                                      Он так и останется в базе пока его не заменят на что-то другое. ORM же не обязательно про него знать чтобы он работал.

                                                                                                                                                        0

                                                                                                                                                        С джойнами тут на любителя. Я лично активно стараюсь указать ORM, чтобы она сгенерировала JOIN там где нужно по логике, а не "джойнить" руками.


                                                                                                                                                        А аналоги триггеров — событийная модель или хуки, в том числе ORM часто их поддерживают"из коробки"

                                                                                                                                                          –2
                                                                                                                                                          у нас есть легаси: джойн plsql
                                                                                                                                                          и есть легаси: триггер plsql
                                                                                                                                                          а мы хотим всё это переписать на php
                                                                                                                                                          как это сделать?

                                                                                                                                                          Вместо JOIN в ORM будут 2 запроса, основной и с WHERE id IN. Если джойн нужен для сортировки, то можно и джойн сделать.


                                                                                                                                                          MainEntity::find()->with('relationName')->all();
                                                                                                                                                          MainEntity::find()->join('relationName')->all();

                                                                                                                                                          Поля для ON берутся из описания связи relationName.


                                                                                                                                                          Триггер в PHP будет вызовом обычной функции из другой функции.


                                                                                                                                                          function registerUser()
                                                                                                                                                          {
                                                                                                                                                              $user = new User();
                                                                                                                                                              $user->load($data);
                                                                                                                                                              $user->save();
                                                                                                                                                              $this->sendRegistrationEmail();  // действие после INSERT
                                                                                                                                                          }
                                                                                                                                                        0

                                                                                                                                                        Аналог джойна тут в комментариях уже несколько раз приводили.


                                                                                                                                                        Что же до триггеров — то они не нужны если не ставить СУБД в центр архитектуры.

                                                                                                                                                    –2
                                                                                                                                                    Взамен куча оверхэда, ошибки, малопроизводительный и трудноподдерживаемый код. Вон Михаил выше пытался 3 оператора SQL написать на php, видели что получилось…
                                                                                                                                                    Смесь из ORM, QueryBuilder'а, SQL'я
                                                                                                                                                      +3
                                                                                                                                                      Взамен куча оверхэда, ошибки, малопроизводительный и трудноподдерживаемый код.

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

                                                                                                                                                        0
                                                                                                                                                        производительность остается в допустимых рамках

                                                                                                                                                        Насчёт удобства разработки я — верю!.. Но на скорость разве вообще нет накладных расходов на дополнительные слои абстракции?
                                                                                                                                                          +2

                                                                                                                                                          Есть. Именно поэтому я не написал "производительность не меняется", а написал "остается в допустимых рамках".

                                                                                                                                                          –1
                                                                                                                                                          Количество ошибок уменьшается, поддержка упрощается, производительность остается в допустимых рамках.

                                                                                                                                                          примеры показывают обратное
                                                                                                                                                            +3
                                                                                                                                                            примеры показывают обратное

                                                                                                                                                            Да, есть примеры, которые показывают обратное. Плохих разработок вообще много. Но есть и примеры того, как это работает, так что нужная цель достигнута.

                                                                                                                                                          0
                                                                                                                                                          Вон Михаил выше пытался 3 оператора SQL написать на php, видели что получилось…

                                                                                                                                                          SQL: 28 строк логики
                                                                                                                                                          PHP: 22 строк логики


                                                                                                                                                          И оператор у меня один — блок кода между фигурными скобками.

                                                                                                                                                            –1
                                                                                                                                                            SQL: 28 строк логики
                                                                                                                                                            PHP: 22 строк логики
                                                                                                                                                            И оператор у меня один — блок кода между фигурными скобками.

                                                                                                                                                            я говорю про производительность, простоту поддержки, консистентность данных, ну и про кол-во кода, Вы как-то упустили из него магические методы и прочее.

                                                                                                                                                            Допилите уже, пожалуйста, финальный код, будет что сравнить. Оформите в виде REST API. Мне видимо тоже надо вызовы на каком-нть ЯП сделать и результат в JSON'е отдавать, чтобы сопоставимый код был, который делает одно и тоже. Я бы сваял на Node, оч понравилась она мне в последнее время.

                                                                                                                                                            Тогда такое предложение, делаем 3 вещи:
                                                                                                                                                            1. закрытие документа, то которые выше
                                                                                                                                                            2. отображение списка товара с ценами, остатками. То которое уже делали, на нашей прошлой дискуссии, только его чутка усложнить надо, приблизив к реальности
                                                                                                                                                            3. загрузку прайс-листа от поставщика, пишется просто, но тоже наглядно покажет огромное преимущество SQL над императивной обработкой.
                                                                                                                                                            Примеры реализации задач самые, что ни на есть, живые.
                                                                                                                                                            Вы разрабатываете на PHP+MySQL8, я на MySQL8+Node
                                                                                                                                                            Финальный код Ваш и мой будет отличным материалом для статьи и объективной всесторонней оценки.

                                                                                                                                                            Если Вы берётесь, то я бы сформулировал требования, чёткое ТЗ, подготовил бы и описал структуру данных, наваял скрипты для тестовых данных.
                                                                                                                                                            Понимаю что, для того чтобы написать красивый код, который пойдёт на суд общественности, нужно время, да ещё и свободное время, поэтому чётких сроков не будем ставить, а раз в несколько дней связываться и интересоваться прогрессом. Но думаю за пару недель можно закончить.

                                                                                                                                                            Главное понять, берётесь Вы или нет?

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

                                                                                                                                                              Интересно как вы будете сравнивать простоту поддержки кода в рамках искусственного "баттла".

                                                                                                                                                                0
                                                                                                                                                                Интересно как вы будете сравнивать простоту поддержки кода в рамках искусственного «баттла».

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

                                                                                                                                                                Ну и кол-во кода будет одним из объективных критериев простоты поддержки.
                                                                                                                                                                  0
                                                                                                                                                                  Ну и кол-во кода будет одним из объективных критериев простоты поддержки.

                                                                                                                                                                  Основной критерий простоты поддержки — отсутствие копипасты.

                                                                                                                                                                0
                                                                                                                                                                ну и про кол-во кода, Вы как-то упустили из него магические методы и прочее

                                                                                                                                                                Я уже несколько раз написал, почему я их не привел — потому что они пишутся один раз на любое количество функций бизнес-логики, поэтому некорретно считать их вместе с кодом одной функции.


                                                                                                                                                                Тогда такое предложение, делаем 3 вещи

                                                                                                                                                                Я думал, мы хотели сначала сравнить производительность закрытия документов через базу и через приложение. Я уже код почти дописал.


                                                                                                                                                                Если Вы берётесь, то я бы сформулировал требования, чёткое ТЗ
                                                                                                                                                                Главное понять, берётесь Вы или нет?

                                                                                                                                                                Давайте попробуем.

                                                                                                                                                                  0
                                                                                                                                                                  Я думал, мы хотели сначала сравнить производительность закрытия документов через базу и через приложение. Я уже код почти дописал.

                                                                                                                                                                  Да, давайте конечно сначала сравним то что уже будет написано. Если будет готовое приложение, то Вам добавить туда функционал будет уже не сложно.

                                                                                                                                                                  Давайте попробуем.

                                                                                                                                                                  Отлично!
                                                                                                                                                                    0

                                                                                                                                                                    Написал внизу отдельным комментарием.

                                                                                                                                                                  0
                                                                                                                                                                  простоту поддержки,

                                                                                                                                                                  Ну вот и мы вам про простоту поддержки. Для меня код на C# намного более поддерживаем, чем код на любой вариации SQL.

                                                                                                                                                    0

                                                                                                                                                    Ну вот этот код который я показал — его не руками пишут, он гененируется. Добавил поля в модель, сказал "фас", он сгенерировал вот эти Up/Down самостоятельно. Тут весь процесс описан по шагам: https://docs.microsoft.com/en-us/ef/ef6/modeling/code-first/migrations/


                                                                                                                                                    Иногда возомжно её надо руками подправить чтобы например индексы создать или тот же SqlType проставить нестандартный, но в целом оно генерённое.

                                                                                                                                0
                                                                                                                                Да, высказался и привёл 2 идентично работающих листинга

                                                                                                                                Неа, не идентичных. Например, поведение транзакций не определено, а, значит, не идентично.


                                                                                                                                Мой код без сайд эффектов,

                                                                                                                                Да нет, ваш код только и состоит, что из побочных эффектов, как ожидаемых (запись в БД), так и неожиданных (блокировки).

                                                                                                                                  +2
                                                                                                                                  Неа, не идентичных. Например, поведение транзакций не определено, а, значит, не идентично.

                                                                                                                                  Ещё раз повторяю: оба листигна моего кода работаю идентично в равных условиях, хоть с автокоммитом, хоть завёрнутым в транзакцию. Автокоммит не обеспечит нужной целостности, но оба листинга будут работать одинаково.
                                                                                                                                  Код Михаила, который он привёл в первый раз, без FOR UPDATE, не работает идентично моему коду, хоть с автокоммитом, хоть завёрнутым в транзакцию.

                                                                                                                                  Да нет, ваш код только и состоит, что из побочных эффектов, как ожидаемых (запись в БД), так и неожиданных (блокировки).

                                                                                                                                  Мда… Изучайте документацию СУБД, там явно описано как работают блокировки при DML операциях, как обеспечивается целостность.
                                                                                                                                    –2
                                                                                                                                    Ещё раз повторяю: оба листигна моего кода работаю идентично в равных условиях, хоть с автокоммитом, хоть завёрнутым в транзакцию.

                                                                                                                                    … вот только эти "равные условия" никем не гарантированы. Но не суть, на самом деле.


                                                                                                                                    Изучайте документацию СУБД, там явно описано как работают блокировки при DML операциях, как обеспечивается целостность.

                                                                                                                                    То, что побочные эффекты явно описаны, не означает, что они перестали быть побочными эффектами.

                                                                                                                                      0
                                                                                                                                      Это не побочные эффекты, это явные механизмы СУБД, которые обеспечивают целостность данных. Почитайте что такое ACID
                                                                                                                                        +2
                                                                                                                                        Это не побочные эффекты, это явные механизмы СУБД, которые обеспечивают целостность данных.

                                                                                                                                        От того, что это явные механизмы, они побочными эффектами быть не перестают.


                                                                                                                                        Почитайте что такое ACID

                                                                                                                                        Почитайте, что такое побочные эффекты.

                                                                                +2

                                                                                Так, для сравнения миров. Написано на C# с использованием linq2db. Все типобезопасно, те же три запроса только вставку переписал через MERGE и добавил тразакцию. Я бы с удовольствием посмотрел на код написанный спомощью JOOQ


                                                                                void CloseDoc(IDataContext db, int docId, short type)
                                                                                {
                                                                                    using var tran = db.BeginTransaction();
                                                                                
                                                                                    var documentQuery = db.GetTable<Document>().Where(d => d.id == docId);
                                                                                
                                                                                    var validationQuery = documentQuery
                                                                                        .Where(d => d.doc_type_id.In(6, 8) && d.DocPosition.kol ?? 0 != 0);
                                                                                    if (validationQuery.Any())
                                                                                      throw new Exception("Документы данного типа нельзя закрывать если есть позиции с не нулевыми количествами.");
                                                                                
                                                                                    var sourceQuery = from d in documentQuery
                                                                                      where d.DocType.credit_type.In(1, -1)
                                                                                      select new CurrentStock
                                                                                      {
                                                                                         mat_id = d.DocPosition.mat_id,
                                                                                         org_id = d.org_id_addr,
                                                                                         org_id_ur = d.org_id_ur,
                                                                                         kol = - (d.DocPosition.kol ?? 0) * d.DocType.credit_type * type,
                                                                                         reserve = - (d.DocPosition.kol ?? 0) * (d.DocType.credit_type == 1 ? 1 : 0) * type
                                                                                      };
                                                                                
                                                                                    db.GetTable<CurrentStock>()
                                                                                      .Merge()
                                                                                      .Using(sourceQuery)
                                                                                      .OnTargetKey()
                                                                                      .InsertWhenNotMatched()
                                                                                      .UpdateWhenMatched((t, s) => new CurrentStock
                                                                                      {
                                                                                          kol = t.kol + s.kol,
                                                                                          reserve = t.reserve + s.reserve
                                                                                      })
                                                                                      .Merge();
                                                                                
                                                                                    var deleteQuery = 
                                                                                       from cs in db.GetTable<CurrentStock>()
                                                                                       from d in documentQuery.InnerJoin(d => cs.mat_id == dp.mat_id && cs.org_id == d.org_id_addr && cs.org_id_ur == d.org_id_ur)
                                                                                       where cs.kol == 0 && cs.reserve == 0
                                                                                       select cs;
                                                                                
                                                                                    deleteQuery.Delete();
                                                                                
                                                                                    tran.Commit();
                                                                                }
                                                                                  0
                                                                                  Интересный код.

                                                                                  только вставку переписал через MERGE

                                                                                  А что делает MERGE?

                                                                                  А какой SQL код получается в итоге?

                                                                                  П.С. безотносительно всего: мне читается такой код ооочень тяжело…
                                                                                      0
                                                                                      SQL'ный то MERGE знаю, думал это хитрость какая-то ОРМ'а

                                                                                      MERGE'то ещё и DELETE умеет делать, поэтому тут вообще одним оператором можно обойтись и без уродливого ОРМ'а.
                                                                                      Будет что-то типа:
                                                                                      MERGE INTO cur_stock s
                                                                                      USING (
                                                                                          SELECT dp.mat_id, d.org_id_addr, d.org_id_ur
                                                                                          , IFNULL(dp.kol, 0) * -1 * t.credit_type * type kol
                                                                                          , IFNULL(dp.kol, 0) * -1 * IF(t.credit_type = 1, 1, 0) * type reserve
                                                                                          , CASE
                                                                                             WHEN d.doc_type_id IN (6, 8) -- ЗПС и План производства
                                                                                              AND IFNULL(dp.kol, 0) <> 0
                                                                                             THEN raise_error('Документы данного типа нельзя закрывать если есть позиции с не нулевыми количествами.')
                                                                                            END check_data
                                                                                          FROM t_docs d
                                                                                          INNER JOIN doc_pos dp ON d.id = dp.doc_id
                                                                                          INNER JOIN doc_types t ON d.doc_type_id = t.id
                                                                                          WHERE d.id = p_doc_id
                                                                                           AND t.credit_type IN (1, -1)
                                                                                      ) d ON (d.mat_id = s.mat_id AND d.org_id_addr = s.org_id_addr AND d.org_id_ur = s.org_id_addr)
                                                                                      WHEN NOT MATCHED INSERT (mat_id, org_id, org_id_ur, kol, reserve)
                                                                                       VALUES (mat_id, org_id, org_id_ur, kol, reserve)
                                                                                      WHEN MATCHED AND (s.kol + d.kol = 0 AND s.reserve + d.reserve = 0) THEN DELETE
                                                                                      WHEN MATCHED UPDATE SET s.kol = s.kol + d.kol, s.reserve = s.reserve + d.reserve
                                                                                      

                                                                                      Изящно, компактно, красиво, предельно эффективно. Чтение нужных данных всего 1 раз производится.

                                                                                      П.С. кстати, сюда же и проверку данных можно засунуть!!!
                                                                                      Сейчас подправлю запрос… Вот сейчас получилось просто шедеврально, вся логика в 20 строк кода и в один проход данных!
                                                                                        0

                                                                                        Могу и так написать, кода будет еще меньше.

                                                                                          +3

                                                                                          … а вот теперь попробуйте переиспользовать часть логики из этого кода. Например, чтобы везде, где кто-то пытается закрыть документ, выполнялась проверка WHEN d.doc_type_id IN (6, 8) AND IFNULL(dp.kol, 0) <> 0 THEN raise

                                                                                            0
                                                                                            дак это и так процедура которая закрывает документ, она вызывается везде, где кто-то пытается закрыть документ.
                                                                                            Точнее это функция, которая запускает такой код в триггере на таблицу t_docs
                                                                                                SELECT close_doc(d.new_id, IF(d.new_closed = 1 AND d.old_closed IS NULL, 1, -1))
                                                                                                INTO msg
                                                                                                FROM t_docs_tmp_trg d
                                                                                                WHERE d.time = 'A'
                                                                                                 AND d.type = 'U'
                                                                                                 AND (d.new_closed = 1 AND d.old_closed IS NULL
                                                                                                  OR d.new_closed IS NULL AND d.old_closed = 1
                                                                                                 )
                                                                                                ;
                                                                                            
                                                                                              +2

                                                                                              И как вы гарантируете, что какой-то разработчик просто не решит сам проставить статус "закрыто" или удалить строчку, минуя вашу процедуру?

                                                                                                0
                                                                                                я же пишу что процедура вызывается в триггере на таблицу t_docs, указанный выше код вызывается в триггере
                                                                                                  0

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

                                                                                                    0
                                                                                                    именно
                                                                                                      +3

                                                                                                      А, вы это считаете "предельно эффективно". Ок, вопросов не имею.

                                                                                                        0
                                                                                                        Для batch обработки БЛ тоже имеется способ, притом супер эффективный, довольно красивый с минимумом оверхэда. Если действительно интересно, то могу рассказать…
                                                                                                          +1

                                                                                                          Нет, в таких терминах не интересно.

                                                                                                            –1

                                                                                                            Брезгуйте? )

                                                                                                              0

                                                                                                              Нет, просто смысла не вижу.

                                                                                                                –1
                                                                                                                ))))
                                                                                                  0

                                                                                                  "какой-то разработчик просто не решит сам проставить статус" — а у разработчика нет прав в БД на update/delete

                                                                                                    0

                                                                                                    У разработчика, который разрабатывает код, который работает с БД, нет прав написать код, который проставляет статус?

                                                                                                      0

                                                                                                      Для вас разработчик и клиент одна роль в БД?
                                                                                                      Но в общем случаю да.
                                                                                                      Грант execute не подразумевает update, delete, а права разработчика в БД не подразумевает права dbowner, не говоря уже supervisor

                                                                                                        +1
                                                                                                        Для вас разработчик и клиент одна роль в БД?

                                                                                                        А при чем тут клиент-то? Я об этапе разработки говорю.


                                                                                                        права разработчика в БД не подразумевает права dbowner

                                                                                                        … и как же он разработку ведет?

                                                                                                          –2

                                                                                                          Вы не имеете опыта DB разработки совсем?
                                                                                                          Давать разработчику права dbowner и тем более supervisor совсем необязательно и даже нельзя, если разработчиков более одного

                                                                                                            0

                                                                                                            Имею. Удивительным образом, на локальных БД, в которых я веду разработку, у меня права dbo (и sa на весь сервер, чего уж).

                                                                                                              0

                                                                                                              Вы создаёте базу и её объекты от имени dbo. От его же имени выдаёте нужным ролям нужные разрешения на эти объекты.


                                                                                                              А когда вы разрабатываете клиент к этой базе, использующий созданные объекты базы, вы делаете это с connection string, соответствующей роли пользователя.


                                                                                                              Если клиент имеет админский доступ, значит вы либо используете подход code-first (когда база создаётся из клиента), либо что-то делаете неправильно. Локальность базы ничего не меняет в этом смысле.

                                                                                                                0

                                                                                                                Ну вот и что мне мешает создать в БД процедуру, которая обновит статус документа? И выдать роли пользователя на нее права?

                                                                                                                  0

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


                                                                                                                  Точно так же ничего не мешает локальному администратору отформатировать диск без бекапа. Он просто не должен этого делать, потому что это неправильно, и предполагается, что он и не станет.


                                                                                                                  Все остальные не могут создать в БД процедуру, которая обновит статус документа.

                                                                                                                    0
                                                                                                                    Вам как разработчику базы — совершенно ничего.

                                                                                                                    Ну вот это мне и не нравится. За что мне нравятся языки с контролем области видимости — так это как раз за то, что они уменьшают вероятость таких ошибок.

                                                                                                    +1

                                                                                                    Это всё равно, что спросить — а что, если предприимчивый девелопер найдёт в vTable вашего класса указатель на private-метод и вызовет его напрямую, в обход публичного интерфейса вашего класса?


                                                                                                    Разумеется, в этом случае всё сломается, но так и должно быть.


                                                                                                    У разработчика этой базы данных есть возможность поступить так. Он не должен так делать, но если сделает, то это он нарушил контракт.


                                                                                                    А у разработчика клиента к этой базе такой возможности быть не должно.


                                                                                                    В контексте базы данных это решается выдачей нужных разрешений и невыдачей ненужных.


                                                                                                    Всё это, разумеется, применимо к ситуации, когда база содержит логику, а не является безмозглым хранилищем для среднего слоя.

                                                                                                      0
                                                                                                      Это всё равно, что спросить — а что, если предприимчивый девелопер найдёт в vTable вашего класса указатель на private-метод и вызовет его напрямую, в обход публичного интерфейса вашего класса?

                                                                                                      Нет, не то же самое. Уровень необходимого вмешательства другой.


                                                                                                      Он не должен так делать, но если сделает, то это он нарушил контракт.

                                                                                                      А откуда он это знает? Где зафиксирован этот контракт?

                                                                                                        –1
                                                                                                        А откуда он это знает? Где зафиксирован этот контракт?

                                                                                                        Так ведь вы его, как разработчик базы, сами и создаёте!
                                                                                                        И предполагается, что создаёте вы его таким, чтобы он позволял всё нужное и не позволял всё ненужное.


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

                                                                                                          0
                                                                                                          Так ведь вы его, как разработчик базы, сами и создаёте!
                                                                                                          [...]
                                                                                                          Вы же спрашиваете, что будет, если вы, единственный создатель и властитель контракта, его же будете нарушать.

                                                                                                          В том-то и дело, что не единственный. Разработчиков больше одного.


                                                                                                          И предполагается, что создаёте вы его таким, чтобы он позволял всё нужное и не позволял всё ненужное.

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

                                                                                                            +1

                                                                                                            Да, это так :(


                                                                                                            Помогают:


                                                                                                            • Документация
                                                                                                            • Общая атмосфера "мы пишем базу с логикой", где первой мыслью каждого участника должно быть смотреть в документацию, что вызывать для удаления
                                                                                                            • Инструменты проверки зависимостей между хранимыми объектами и анализа использования инструкций sql

                                                                                                            Стопроцентного решения нет.

                                                                                                              +2

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

                                                                                                        +1
                                                                                                        Мне наиболее близок следующий подход:
                                                                                                        — не юзаем хранимки для изменения данных
                                                                                                        — естественный контракт для модификации данных это всем известные методы INSERT/UPDATE/DELETE на таблицу
                                                                                                        — обязательные параметры и типы параметров у нас описываются в CREATE TABLE
                                                                                                        — простые валидейшены по-возможности описываем CHECK'ами
                                                                                                        — бизнес логику и сложные проверки выносим в процедуру которая вызывается в триггере
                                                                                                        — процедура работает через временную таблицу как описано у меня статье
                                                                                                        — бизнес логику и проверки в процедуре описываем декларативно
                                                                                              0
                                                                                              А что делает MERGE?
                                                                                              А какой SQL код получается в итоге?

                                                                                              Была бы база я бы выложил. MREGE синтаксис можно почитать в документации


                                                                                              П.С. безотносительно всего: мне читается такой код ооочень тяжело…

                                                                                              Это с непривычки, нужно понимать что SELECT всегда в конце — иначе типы не вывести. Ну и синтаксис современного C#, не знаю как у вас с ним.


                                                                                              Если присмотритесть я почти не делал JOIN — все сделает библиотека через ассоциации между таблицами, их еще называют навигациями по свойствам.
                                                                                              Также я один раз задал фильтр по docId и больше этим не страдаю. Так уменьшается количество возможных багов (условие по JOIN неправильное, забыли фильтр наложить). Декомпозиция запросов в полный рост — то чего не хватает SQL.


                                                                                              Запчасти запросов я разложил по переменым, хотя можно было в некоторых местах и не делать этого. Все для того чтобы спокойно можно было каждый запрос проверить в дебаге.


                                                                                              К чему я веду — если иметь првильный тул, действительно можно обойтись без хранимок с минимумом оверхеда и максимальным mainteinability.


                                                                                              P.S.
                                                                                              Кстати, данный код будет работать на всех базах поддерживающих MERGE. Бибилиотека за вас учтет особенности синтаксиса нужной базы данных.

                                                                                                –1
                                                                                                Это с непривычки

                                                                                                нет. SQL тем и ценен, что он близок к человеческому языку, можно пару лет не прикасаться и не заглядывая в справочники написать запрос.
                                                                                                про прочитать я и не говорю — минимального знания английского достаточно чтобы прочитать 90% запросов на разных диалектах SQL (про машиногенерируемые запросы на несколько килобайт я не говорю).


                                                                                                P. S. «но сатана недолго ждал реванша», пропихнули в стандарт всякие '$.floor[*].apt[*] ? (@.area > 40 && @.area < 90)' для работы с json

                                                                                                  +1
                                                                                                  нет. SQL тем и ценен, что он близок к человеческому языку, можно пару лет не прикасаться и не заглядывая в справочники написать запрос.

                                                                                                  Это пока вам не придется писать оконные функции, Merge.


                                                                                                  Ну в общем, ваше дело, а я прекрасно читаю LINQ и не хочу тратить время на написание SQL, склейку строк, нюансы синтаксиса. Рефакторить LINQ одно удовольствие, а вот SQL — тут без ста грам DataGrip, еще тот квест.


                                                                                                  Вот пример, где кастомное решение рвет чистый SQL как тузик грелку:
                                                                                                  Есть 1000 записей вида (int Id1, int Id2) в памяти приложения. Нужно выбрать из таблицы TableX все записи у которых совпадают эти два поля.
                                                                                                  Вот решение с использованием ORM, удачи в написании SQL:


                                                                                                  var items = // список элементов
                                                                                                  using var tempTable = db.CreateTampTable("#Items", items); 
                                                                                                  // записи влетают в базу за секунду
                                                                                                  
                                                                                                  var query = 
                                                                                                    from x in db.GetTable<TableX>()
                                                                                                    from t in tempTable.InnerJoin(t => t.Id1 == x.tId1 && t.Id2 == x.Id2)
                                                                                                    select x;
                                                                                                    –1
                                                                                                    SELECT x
                                                                                                    FROM TableX x
                                                                                                    INNER JOIN Items t ON t.Id1 == x.tId1 AND t.Id2 == x.Id2
                                                                                                    
                                                                                                      0

                                                                                                      А откуда у вас таблица Items взялась? Её в БД нет, она только в памяти клиента...

                                                                                                        0
                                                                                                        Предположил что код
                                                                                                        using var tempTable = db.CreateTampTable("#Items", items);

                                                                                                        её создаёт

                                                                                                        Видимо неверно предположил. А какие SQL команды будут выполнены на сервере в приведённом Dansoid коде?
                                                                                                          0

                                                                                                          Да, этот код её создаёт (только она называется #Items, видимо для совместимости с MS SQL Server).


                                                                                                          Вот только если вы отказываетесь от ORM — у вас не будет этого кода.

                                                                                                            0
                                                                                                            т.е. Вы считаете что мне кто-то может запретить юзать из ОРМ-ной библиотеки только этот функционал?

                                                                                                            На php в своё время писал подобную процедуру самостоятельно, никаких сложностей.

                                                                                                            А в MySQL 8 c JSON_TABLE сейчас даже не надо создавать временные таблицы для подобного
                                                                                                            let items = [{id1: 1, id2: 2}, {id1: 3, id2: 4}];
                                                                                                            let [res] = db.query(`
                                                                                                            SELECT x.*
                                                                                                            FROM JSON_TABLE(?, '$[*]'
                                                                                                            COLUMNS (id1 INT PATH '$.id1', id2 INT PATH '$.id2')) i
                                                                                                            INNER JOIN TableX x ON i.id1 == x.tId1 AND i.id2 == x.Id2
                                                                                                            `, [items]);
                                                                                                            
                                                                                                              –2

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

                                                                                              0

                                                                                              Точно так же будет выглядеть на JOOQ и практически любом ОРМ

                                                                                                0

                                                                                                Показать можете? Я смотрел на JOOQ, мило и только. Как там с переиспользванием запросв?

                                                                                                  0

                                                                                                  Да мне кажется дока вполне полноценно описывает возможности. Переиспользовать конечно можно, любые билдеры запросов всегда чистые, а значит композирутся как угодно. Ну взять тот же раст:


                                                                                                  let items = Integrations::table.filter(dsl::CompanyId.eq("ff00-abcd"));
                                                                                                  let total_count = items.count();
                                                                                                  let top10 = items.take(10).load();

                                                                                                  Наличие отстуствие всяких специализированных merge/оконных функций/этц это уже к фичастости фреймворка. Где-то есть, где-то нет. В linq2db например есть, но зато там есть другие проблемы.

                                                                                                    0
                                                                                                    Да мне кажется дока вполне полноценно описывает возможности.

                                                                                                    Покажите мне query decomposition на JOOQ, я задолбался искать


                                                                                                    В linq2db например есть, но зато там есть другие проблемы.

                                                                                                    Хотелось бы о них услышать

                                                                                                      0
                                                                                                      Покажите мне query decomposition на JOOQ, я задолбался искать

                                                                                                      Там все на мутабельных билдерах, но в целом это не мешает, достаточно вметсо переменной использовать функцию возвращающую билдер.


                                                                                                      Хотелось бы о них услышать

                                                                                                      Ну в частности я не смог заскаффолдить свою базу. Там какая-то хитрая ерунда на .tt шаблонах (в 2020 году-то), которая ни на винде, ни на тем более рабочей убунту станции не заработала. Плюс да, были проблемы, начиная от того что драйве не экспейкпил имена табликек (например табличку Users в постгре нужно писать исключительно как "Users" в кавычках, иначе он её не увидит) и заканчивая тем что на кажое поле каждой модели нужно было атрибут вешать.


                                                                                                      LinqToDB.Linq.LinqException: Member 'Cars.CompanyId' is not a table column.

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

                                                                                              +6
                                                                                              CREATE PROCEDURE close_doc(p_doc_id INT, type TINYINT)
                                                                                              -- type - 1 закрытие, -1 открытие

                                                                                              Будем честными, для меня достаточно этого комментария, чтобы избегать этой хранимой процедуры, пока есть такая возможность.

                                                                                              0
                                                                                              Часть вашего комментария про упрощение мне напомнила статью 7-летней давности.
                                                                                              habr.com/ru/post/203048

                                                                                              Как показывает практика: при желании — можно упростить до 0 строк кода. А ещё можно убедить бизнес в том, что им это вообще не нужно и вообще ничего не делать — так тоже бывает.
                                                                                                +2
                                                                                                А мне вспомнилось, что быстрая сортировка занимает намного больше строк, чем простейшая сортировка в лоб, но работает быстрее.
                                                                                                  0

                                                                                                  И поэтому её написание оправдано только если скорость важна.

                                                                                                    +1

                                                                                                    Ассемблер тоже занимает больше строк и работает быстрее. Будем на нем всё писать, или всё же есть какие-то другие критерии, кроме скорости?

                                                                                          +27
                                                                                          Если вы что-то не умеете готовить не значит, что это плохо.
                                                                                          Основная проблема логики в базе, очень мало людей умеют ее готовить.
                                                                                          Основной плюс логики в базе, работа с множествами там где они лежат с использованием механизмов оптимизации СУБД. Плюс небольшое преимущество в транзациях. Ну и потребление ресурсов.
                                                                                          Основные минусы, менее развиты инструментарий и сложность с поиском людей которые умеют и готовы этим заниматься.

                                                                                          Да, отсутствие фрэймворков, это скорее плюс СУБД. Потому как сейчас уже есть целый пласт «фрэймворк разработчиков», которые helloword без двух-трех фрэйморков и гига памяти написать не могут, а на выходе сайты визитки на 20МБ.
                                                                                            +14
                                                                                            Как по мне, уж лучше пускай студент Вася возьмёт фреймворк TurboSQL 9000 и слепит что-то на нём, чем синьор Алексей Георгиевич, который 20 лет сидит на проекте, пишет что-то подобное, и не видит в этом проблем:

                                                                                            select @result = isnull(CAST(@res as nvarchar(max)),'')
                                                                                            if CHARINDEX ('<status>Error</status>', @result) > 0 or CHARINDEX ('<status>OK</status>', @result) = 0 ...
                                                                                              +9
                                                                                              Фрагмент с CHARINDEX легко переписать, если уж очень захочется, а от TurboSQL 9000 уже никак не избавишься. Так что нет, не лучше.
                                                                                                +3

                                                                                                Зато можно постоянно переезжать на новые фреймворки. В итоге всегда есть работа и потребность в разработчиках. Сейчас их даже в пиццериях по 250 человек.

                                                                                              +1
                                                                                              Если вы что-то не умеете готовить не значит, что это плохо.

                                                                                              Так говорят только те программисты, которые не готовы отвечать за проект в целом. Которые не думают о том, как с этим проектом будут работать другие люди, через 3-5-10 лет.

                                                                                              Если кто-то «умеет готовить» Perl — это не значит, что новый большой проект в 2020 году нужно писать на нем

                                                                                              сложность с поиском людей которые умеют и готовы этим заниматься.

                                                                                              Представляете, но это реально сложность. Есть проекты которые переписывали с нуля, не смотря на то, что они отлично работали, только потому, что найти программистов, которые бы их поддерживали было невозможно.
                                                                                                +3
                                                                                                Ну так я и пишу, что проблемы с sql не технические. Автор же статьи утверждает обратное.
                                                                                                Я не говорю, что нужно брать проект и писать его на SQL.
                                                                                                Это действительно не лучшая идея именно в следствии сложности с персоналом. Вам будет реально не просто набрать людей, обучить их и ввести в проект, поскольку для того же ООП есть хоть какие-то распространенные шаблоны и практики, для SQL с этим все значительно хуже.
                                                                                                Но с другой стороны, найти толковых разработчиков на любой другой стэк тоже ОЧЕНЬ не просто.
                                                                                                  0
                                                                                                  найти толковых разработчиков на любой другой стэк тоже ОЧЕНЬ не просто

                                                                                                  Найти хороших специалистов сложно, да. Но, есть области, где их найти еще раз в десять сложнее.
                                                                                              +22

                                                                                              О плюсах.


                                                                                              Удобный полный доступ ко всем функциям используемого диалекта SQL, а не только к общему подмножеству, используемому ORM-фреймворком.
                                                                                              Как результат — возрастающее желание, которому трудно сопротивляться, перетащить в базу ещё больше логики, потому что очень изящно всё с sum() over(partition by) получается.


                                                                                              Возможность тонкой настройки конкурентности и избежания дедлоков, хинтами и перестановкой операторов.
                                                                                              Как результат — все блокировки пессимистичные (гарантия успеха финального update), и при этом никто никому не мешает.


                                                                                              Избежание ORM-сгенерированных запросов, которые выполняются ужасно долго, хотя если переписать вручную чуть иначе — мгновенно.


                                                                                              Возможность быстрого написания тонких клиентов на любых фреймворках к этой базе-приложению. Одновременно нескольких, для клиентов из разных областей — один на ASP.NET MVC Core, один в MS Excel, один в MS Access, один на powershell. При правильной инкапсуляции изменение функционала базы-приложения часто не требует никаких изменений в этом зоопарке клиентов вообще.

                                                                                                +5
                                                                                                Речь же не об ORM vs чистый SQL. Речь о хранимках
                                                                                                  +2

                                                                                                  В хранимках чистый SQL.


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

                                                                                                    +2

                                                                                                    Почему неудобно? Есть инструменты вроде jOOQ которые генерируют проверяемые в compile-time запросы на SQL.

                                                                                                      +1
                                                                                                      В хранимках чистый SQL.

                                                                                                      Постгрес позволяет использовать внутри хранимых почти любой кот: перл, питон, tlc… И некоторые "любители" этим пользуются

                                                                                                    +6
                                                                                                    Расскажите подробнее, как и что именно хранимки позволят вам настроить настолько тонко, что вы избежите дедлоков?
                                                                                                    И почему пессимистичность блокировок — это гарантия успеха финального апдейта?
                                                                                                      +10

                                                                                                      Полностью избежать дедлоков нельзя. Их можно сильно уменьшить, если обращаться к данным в одном порядке и использовать updlock для тех данных, которые в итоге предполагается обновить.
                                                                                                      Использовать один и тот же порядок доступа к данным можно и из ORM, но там а) нет хинтов, и б) если вдруг запрос странслируется недостаточно оптимально, можно получить эскалацию блокировки. Например, при запросе множества "1 родитель + его дети" ORM, чтобы избежать ситуации n+1, может запросить всю структуру одним запросом, но с вложенным (select count(*)) для собственного удобства парсинга результата, что плохо для блокировки. Или таки скатывается в n+1, что тоже не фонтан.


                                                                                                      Пессимистичность блокировок — гарантия успеха в смысле их противопоставления оптимистичным блокировкам. ORM в основном работают по оптимистичным блокировкам — при чтении из базы ничего не блокируется, а при последующем обновлении обновление выполняется только если соответствующие поля не были изменены с момента запроса (т.е. к условию update .. where id = 42 автоматически дописывается and field1 = 'asd' and field2 is null). Если значения полей оказываются иные, база возвращает "0 rows affected", и ORM транслирует это в исключение "Оптимизм не оправдался, обновите свою картину мира и попробуйте снова". Механизм проверки, конечно, можно отключить, но это отложенное стреляние в ногу.
                                                                                                      Разумеется, всё это верно для областей, где, собственно, важно быть уверенным, что обновление вносится именно в ту версию объекта, которая была прочитана из базы (складской учёт, денежные транзакции). В других областях может быть совершенно безвредно переписать объект его новейшей версией безотносительно оригинального состояния (редактирование постов на форуме).

                                                                                                        0
                                                                                                        при чтении из базы ничего не блокируется

                                                                                                        Зависит от текущего transaction isolation level + самой структуры запроса — SELECT FOR UPDATE очень даже блокирует на чтение. То же самое с подзапросами.


                                                                                                        В других областях может быть совершенно безвредно переписать объект

                                                                                                        Шеф, тут пришло несколько тредов на запись с клиента и мы всё проэтосамили