Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 46
Тоже люблю пооптимизировать запросы.
Однако, жутко не люблю логику в БД. Всегда, когда сталкивался с ней, ругался, так как это был недокументированный код, который проводил неявные операции с бд. Приходилось изучать все триггеры, процедуры и функции, документировать и составлять карту, чтобы внести ясность в логику системы.
Однако, жутко не люблю логику в БД. Всегда, когда сталкивался с ней, ругался, так как это был недокументированный код, который проводил неявные операции с бд. Приходилось изучать все триггеры, процедуры и функции, документировать и составлять карту, чтобы внести ясность в логику системы.
Добавлю, что декомпозиция логики — сама по себе плохая идея.
Когда часть бизнес-правил описана в базе, а другая в приложении, то где-нибудь когда-нибудь обязательно произойдет сбой или несоответствие. И понять полную цепочку правил очень тяжело.
Когда одни данные меняются логикой БД, а другие — логикой приложения, то может появится путаница. Еще хуже, когда одни и те же данные меняются одновременно и логикой БД, и логикой приложения. В такой ситуации на разбор полетов уходит на порядок больше времени.
Когда часть бизнес-правил описана в базе, а другая в приложении, то где-нибудь когда-нибудь обязательно произойдет сбой или несоответствие. И понять полную цепочку правил очень тяжело.
Когда одни данные меняются логикой БД, а другие — логикой приложения, то может появится путаница. Еще хуже, когда одни и те же данные меняются одновременно и логикой БД, и логикой приложения. В такой ситуации на разбор полетов уходит на порядок больше времени.
А чем такой вариант прерывания по ошибке не устраивает?
Как обычно вопрос состоит не в том, что лучше, а в том, что целесообразнее.
Всегда найдутся как плюсы, так и минусы. В случае с хранением логики в БЖ мы можем получить, например, большую производительность. Но тогда мы привязываемся к данной конкретной БД.
А за статью спасибо! Именно такого рода статьи должны быть на хабре.
Всегда найдутся как плюсы, так и минусы. В случае с хранением логики в БЖ мы можем получить, например, большую производительность. Но тогда мы привязываемся к данной конкретной БД.
А за статью спасибо! Именно такого рода статьи должны быть на хабре.
Бизнес логика в БД это конечно круто, но как быть с программистами, которые приходят в команду и не то что с MySQL, а просто с SQL на «вы»? Зато джавист он добротный, например. Не брать такого парня?
Плюс, как правило, этой логикой владеет только тот, кто ее реализовал. И, как выше написали, никому не рассказывает что это за логика, и почему именно так написана.
А как быть с тестированием этой логики? Есть какие-то инструменты для unit-тестирования Ваших процедур и функций? А что если придется менять СУБД? В общем, больше вопросов, чем ответов. По сему, я сомневаюсь, что реализация бизнес логики на уровне хранимых процедур в БД — это хорошая идея.
Плюс, как правило, этой логикой владеет только тот, кто ее реализовал. И, как выше написали, никому не рассказывает что это за логика, и почему именно так написана.
А как быть с тестированием этой логики? Есть какие-то инструменты для unit-тестирования Ваших процедур и функций? А что если придется менять СУБД? В общем, больше вопросов, чем ответов. По сему, я сомневаюсь, что реализация бизнес логики на уровне хранимых процедур в БД — это хорошая идея.
По поводу Ваших сомнений очень хорошо сказано опять же у Кайта
Вообще очень рекомендую прочитать хотя бы первую главу книги, которую я цитирую.
Oracle для профессионалов. Глава 1
Уверен что после прочтения у Вас развеются сомнения и предрассудки по поводу реализации БЛ в БД.
По поводу тестирования нет никаких проблем. В Oracle использую utPLSQL:
В MySQL тесты на php. Тестируется практически идентично, если бы логика была не в БД. Для тестирования моего примера из статьи:
Но можно и вообще сваять аналог utPLSQL и тестировать всё в БД
2 xxvy если Вы пропустили статью «TDD для хранимых процедур Oracle», то вот Вам ссылка
По поводу хорошего джависта не знающего SQL
Наиболее типичной причиной неудачи является нехватка практических знаний по используемой СУБД — элементарное непонимание основ работы используемого инструментального средства. Подход по принципу «черного ящика» требует осознанного решения: оградить разработчиков от СУБД. Их заставляют не вникать ни в какие особенности ее функционирования. Причины использования этого подхода связаны с опасениями, незнанием и неуверенностью. Разработчики слышали, что СУБД — это «сложно», язык SQL, транзакции и целостность данных — не менее «сложно». Решение: не заставлять никого делать что-либо «сложное». Будем относиться к СУБД, как к черному ящику, и найдем инструментальное средство, которое сгенерирует необходимый код. Изолируем себя несколькими промежуточными уровнями, чтобы не пришлось сталкиваться непосредственно с этой «сложной» СУБД.
[...]
Вот типичный сценарий такого рода разработки.
- Разработчики были полностью обучены графической среде разработки или соответствующему языку программирования (например, Java), использованных для создания клиентской части приложения. Во многих случаях они обучались несколько недель, если не месяцев.
- Команда разработчиков ни одного часа не изучала СУБД Oracle и не имела никакого опыта работы с ней. Многие разработчики вообще впервые сталкивались с СУБД.
- В результате разработчики столкнулись с огромными проблемами, связанными с производительностью, обеспечением целостности данных, зависанием приложений и т.д. (но пользовательский интерфейс выглядел отлично).
[...]
Странная идея о том, что разработчик приложения баз данных должен быть огражден от СУБД, чрезвычайно живуча. Многие почему-то считают, что разработчикам не следует тратить время на изучение СУБД. Неоднократно приходилось слышать: «СУБД Oracle — самая масштабируемая в мире, моим сотрудникам не нужно ее изучать, потому что СУБД со всеми проблемами справится сама». Действительно, СУБД Oracle — самая масштабируемая. Однако написать плохой код, который масштабироваться не будет, в Oracle намного проще, чем написать хороший, масштабируемый код. Можно заменить СУБД Oracle любой другой СУБД — это утверждение останется верным. Это факт: проще писать приложения с низкой производительностью, чем высокопроизводительные приложения. Иногда очень легко создать однопользовательскую систему на базе самой масштабируемой СУБД в мире, если не знать, что делаешь. СУБД — это инструмент, а неправильное применение любого инструмента может привести к катастрофе. Вы будете щипцами колоть орехи так же, как молотком? Можно, конечно, и так, но это неправильное использование инструмента, и результат вас не порадует. Аналогичные результаты будут и при игнорировании особенностей используемой СУБД.
Независимость от СУБД
Вы, наверное, уже поняли направление моей мысли. Я ссылался на другие СУБД и описывал различия реализации одних и тех же возможностей в каждой из них. Я убежден: за исключением некоторых приложений, исключительно читающих из базы данных, создать полностью независимое от СУБД и при этом масштабируемое приложение крайне сложно и даже практически невозможно, не зная особенностей работы всех СУБД.
Вообще очень рекомендую прочитать хотя бы первую главу книги, которую я цитирую.
Oracle для профессионалов. Глава 1
Уверен что после прочтения у Вас развеются сомнения и предрассудки по поводу реализации БЛ в БД.
Плюс, как правило, этой логикой владеет только тот, кто ее реализовал. И, как выше написали, никому не рассказывает что это за логика, и почему именно так написана.Очень странное утверждение. И почему это справедливо только для БЛ в БД?
По поводу тестирования нет никаких проблем. В Oracle использую utPLSQL:
- всё можно хранить в базе и, следовательно, запускать все тесты скриптом из SQLPlus
- много разных assert'ов: запросы, коллекции, таблицы, курсоры
- возможность тестить не публичные функции/процедуры пакетов
В MySQL тесты на php. Тестируется практически идентично, если бы логика была не в БД. Для тестирования моего примера из статьи:
- Подготовка: INSERT тестового документа
- INSERT позиции с NULL ценой
- SELECT цены и assert с ожидаемым результатом
- SELECT суммы документа и assert с ожидаемым результатом
- UPDATE кол-ва / цены, DELETE позиции
- SELECT суммы документа и assert с ожидаемым результатом
- Закрываем документ
- INSERT/UPDATE/DELETE позиции
- assert на SQL ошибку
- Удаляем за собой тестовый документ или делаем ROLLBACK
Но можно и вообще сваять аналог utPLSQL и тестировать всё в БД
2 xxvy если Вы пропустили статью «TDD для хранимых процедур Oracle», то вот Вам ссылка
Спасибо за ссылки, изучу.
Полностью согласен с утверждениями Тома Кайта о том, что восприятие СУБД как сложного черного ящика есть абсолютное зло. Но реальность заключается в том, что именно с такими разработчиками чаще всего приходится иметь дело. И приходится тратить время на то, чтобы научить разработчика мыслить иначе.
Я ни в коем случае не пытаюсь оградить разработчиков от СУБД, наоборот, я, при любом удобном случае, пытаюсь «окунуть с головой» разработчика в Oracle. И считаю, что хороший разработчик должен быть хорош во всем, и в СУБД, и во фреймворках, используемых на проекте, и в предметной области, в которой работает приложение. По сему, пойду изучать Oracle по приведенным ссылкам, еще раз спасибо.
Полностью согласен с утверждениями Тома Кайта о том, что восприятие СУБД как сложного черного ящика есть абсолютное зло. Но реальность заключается в том, что именно с такими разработчиками чаще всего приходится иметь дело. И приходится тратить время на то, чтобы научить разработчика мыслить иначе.
Я ни в коем случае не пытаюсь оградить разработчиков от СУБД, наоборот, я, при любом удобном случае, пытаюсь «окунуть с головой» разработчика в Oracle. И считаю, что хороший разработчик должен быть хорош во всем, и в СУБД, и во фреймворках, используемых на проекте, и в предметной области, в которой работает приложение. По сему, пойду изучать Oracle по приведенным ссылкам, еще раз спасибо.
Java developer, причем как вы пишете добротный, и не знает SQL? Звучит мягко говоря странно. Кроме того, а разве это проблема — посидеть пару вечеров и вникнуть? Когда-то я сам сталкнулся с этим. И ничего, просто нужно выйти из зоны комфорта и поднять MySQL storage proc. Есть даже замечательная книга «MySQL Stored Procedure Programming». Джавистам будет полезно из нее узнать, к примеру, как в хранимых процедурах организовать оптимистичные блокировки и т.д.
А что если придется менять СУБД?
Было бы интересно узнать, какая серьезная причина может привести к смене СУБД посреди проекта. С ходу можно придумать только одну: «У нас было множество инсталляций оракла, но человек, получавший откаты с их внедрения был вынужден покинуть нашу компанию, а с новым они не договорились. Так что теперь мы спешно ставим постгрес». А какие еще могут быть сценарии? «Мы писали-писали на .Net-стеке с MS SQL Server, а потом ВНЕЗАПНО осознали, что нам нужен LAMP», так что ли?
Пока что такое впечатление, что смена СУБД в середине проекта — это либо баловство, либо перезапуск проекта.
Мы писали-писали на .Net-стеке с MS SQL Server, а потом ВНЕЗАПНО осознали, что нам нужен LAMP
Увы, так бывает, к примеру на одной из предыдущих работ внезапно захотели хранить кредитные карты пользователей, что влечет за собой сертификацию по PCI DSS и отдельную изолированную базу данных.
Т.к. все писали на Net-стеке с MS SQL Server то и выбор был соответствующий. Но к моменту релиза внезапно! оказалось, что лицензий нет и необходимо переехать на PostgreSQL. И вот черт его знает баловство это или нет =(
Согласен, вероятность этого не велика. Просто я все чаще слышу от менеджеров вопросы, по типу «а можем наше приложение не на Oracle поставить?». А связан такой вопрос со стоимостью лицензии на СУБД от Oracle. Т.е. приложение, допустим, стоит 500 килорублей в базовой комплектации, но для его работы нужно прикупить лицензию на СУБД еще за 500 килорублей. И на фоне этого привлекательность Вашего приложения по цене уже не такая, как заявлено в рекламе. А проект Ваш с многолетней историей (читай legacy), львиная доля бизнес логики лежит в СУБД. И Вы как разработчик вынуждены сообщить менеджеру, что это не реально сделать даже за год. Соответственно, ни а каком перезапуске проекта речи не идет. Но и Вы, как компания, начинаете терять конкурентные преимущества.
Посреди проекта сменить СУБД может заставить банально очередной Федеральный Закон, запрещающий использовать зарубежное ПО, если есть отечественный аналог в реестре отечественного ПО. А софт Ваш, например документооборот, и бюджетные или государственные учреждения Ваш целевой клиент. А не будь бизнес логики в БД, глядишь и за пару месяцев можно поддержать работу приложения на другой СУБД, отличной от Oracle.
Из всего этого, у меня нарисовалась другая картинка: Вы разрабатываете приложение, которое может в качестве СУБД использовать Oracle, PostgreSQL и MSSQL Server. Как Jira, например, делает. Тогда Вам уже нужно поддерживать бизнес логику во всех поддерживаемых СУБД? Как с этим быть?
Было бы интересно узнать, какая серьезная причина может привести к смене СУБД посреди проекта.
Посреди проекта сменить СУБД может заставить банально очередной Федеральный Закон, запрещающий использовать зарубежное ПО, если есть отечественный аналог в реестре отечественного ПО. А софт Ваш, например документооборот, и бюджетные или государственные учреждения Ваш целевой клиент. А не будь бизнес логики в БД, глядишь и за пару месяцев можно поддержать работу приложения на другой СУБД, отличной от Oracle.
Из всего этого, у меня нарисовалась другая картинка: Вы разрабатываете приложение, которое может в качестве СУБД использовать Oracle, PostgreSQL и MSSQL Server. Как Jira, например, делает. Тогда Вам уже нужно поддерживать бизнес логику во всех поддерживаемых СУБД? Как с этим быть?
Если мы с самого начала понимаем, что приложение должно поддерживать широкий спектр СУБД, то тут вопросов нет, бизнес-логика в СУБД должна быть минимизирована.
Кейс «спрыгиваем с оракла» действительно распространен, но тут палка о двух концах:
— Писать сразу под N поддерживаемых СУБД банально дороже ( и в деньгах и в инженерных усилиях по разработке и тестированию ). Причем платим мы сразу, когда проект еще только начали и доходов еще нет и нужно ли это, в общем-то неизвестно.
— А вот ситуация, когда «У нас уже есть приложение под оракл, если вы хотите поддержку <другой платформы> заплатите денежку» она и клиенту в общем-то понятна и разработчика устраивает — если труд точно будет оплачен, почему бы не попереписывать логику для другой СУБД.
Итого: банально, но вопрос допустимости бизнес-логики внутри СУБД зависит от бизнес-стратегии проекта. Не забываем, однако, что вот логика данных обязана лежать рядом с данными, будь у вас хоть Oracle, хоть Firebird, хоть собственное хранилище на файлах
Кейс «спрыгиваем с оракла» действительно распространен, но тут палка о двух концах:
— Писать сразу под N поддерживаемых СУБД банально дороже ( и в деньгах и в инженерных усилиях по разработке и тестированию ). Причем платим мы сразу, когда проект еще только начали и доходов еще нет и нужно ли это, в общем-то неизвестно.
— А вот ситуация, когда «У нас уже есть приложение под оракл, если вы хотите поддержку <другой платформы> заплатите денежку» она и клиенту в общем-то понятна и разработчика устраивает — если труд точно будет оплачен, почему бы не попереписывать логику для другой СУБД.
Итого: банально, но вопрос допустимости бизнес-логики внутри СУБД зависит от бизнес-стратегии проекта. Не забываем, однако, что вот логика данных обязана лежать рядом с данными, будь у вас хоть Oracle, хоть Firebird, хоть собственное хранилище на файлах
Исходя из своего небольшого опыта переползания с Oracle на PostgreSQL, могу сказать, что пакеты, процедуры, функции, да пусть даже и триггеры (хотя мы ими почти не пользуемся) — это наименьшая часть проблемы. Больше всего крови портят мелкие гадские различия, вроде того, что в одной СУБД надо писать with recursive, а в другой просто with, ключевое слово table при обращении к табличным функциям, отличия в синтаксисе при работе с последовательностями и прочее, обойтись без которого никак не получится. Нельзя просто поменять JDBC-драйвер и подключится к другой СУБД. В Java-код, в любом случае, придётся вносить изменения. Независимость от СУБД — миф. А если это так, то глупо не пользоваться теми преимуществами, которые даёт конкретная СУБД. Хотя и с таким подходом приходилось сталкиваться (даже в случае Oracle). Что касается кадрового вопроса — программистов просто нужно учить. Если Java-программист узнает SQL чуть лучше — это его не испортит.
Томас (Том) Кайт (англ. Thomas Kyte) — американский специалист по информационным технологиям, вице-президент корпорации Oracle (работает в компании с 1993 года).
Понятно, почему им рекомендуется всё реализовывать в БД… Фиг ты потом к конкурентам перейдешь задёшево…
Понятно, почему им рекомендуется всё реализовывать в БД… Фиг ты потом к конкурентам перейдешь задёшево…
MySQL бесплатная БД, PostgreSQL тоже. Кайт же призывает разбираться в инструменте который используешь.
Не спорю про инструменты…
Но, попробуй, задёшево перейди с пусть даже и бесплатной PostgresSQL со всеми наворотами, что в ней возможны, на другую БД, да пусть и бесплатную…
Но, попробуй, задёшево перейди с пусть даже и бесплатной PostgresSQL со всеми наворотами, что в ней возможны, на другую БД, да пусть и бесплатную…
Аналогичный аргумент: попробуй переписать программу с одного языка на другой
Учитывая то, что по сути дела БД — это хранилище данных и SQL — это как бы стандарт, то при реализации БЛ в коде приложения (сервиса и т.п.), перенос будет стоить меньше, чем если бы при реализации БЛ фичами конкретной СУБД. Я бы не сказал, что при этом происходит переписывание программы на другой язык. Если только не влезать в дебри хранимок на Java или C (а также python, C# и прочее вендороБДзависимое :)…
как я понимаю, вот похожее мнение
как я понимаю, вот похожее мнение
Бизнес логика в БД имеет право на жизнь в крайне редких ситуациях и намного проще написать ее плюч, чем минусы.
+ Enterprise система с обработкой миллионов данных и применения на них ACL
+ Единое место хранения/изменения конфигураций, кода, ACL
+ Можно написать статью про то как круто реализовывать бизнес логику в БД
И теперь парочка минусов из сотни возможных:
— Сложность скейлинга. Вот уперлась БД по памяти (а такое не редкость в кровавом энтерпрайзе) и что делать? Правильно, добавлять памяти! Когда можно просто развернуть еще один AS и размазать нагрузку как вертикально так и горизонтально. Вы можете возразить и сказать, а как же кластер? Да вот в энтерпрайзе не любят postgre и mysql, а любят Оракл и DB2 где кластеризация мало того что стоит как самолет, так еще и слабо поддерживаемое, неуправляемое решение, к тому же это требует допиливания для поддержания ACID.
— Неуправляемый код (триггеры, шмиггеры — это вообще «до свидание»)
— Тестирование БД требует написания отдельного приклада (то есть, штат разработчиков БД, штат разработчик тестов для БД — беда с точки зрения финансов и количество ч/с для проекта) — да и зачем это нужно?!
— Непереносимость решения — нельзя поменять СУБД, железо и прочее никогда!
— Режим Создателя для разработчика(ов) БД — тот кто спроектировал БД и заработал ее большую часть — на нем все держится, без него система не получит развития, архитектор БД имеет полную власть над всем. Другой вопрос, что если изначально составить карту, все описать и так далее — жизнь будет проще, но все мы живем в реальном мире, где такое бывает редко и чаще, если даже и описаны ключевые моменты, то все 65535 триггеров и процедур не будут описаны никогда.
Все вышеописанное это 2-х летний опыт архитектора на проекте где бизнес логика была в БД. Не советую никому браться за подобные системы. А другие вице-президенты пусть и дальше пишут рекламные статейки-проспекты про отличную идею реализации бизнес логики в БД продвигая свой продукт.
+ Enterprise система с обработкой миллионов данных и применения на них ACL
+ Единое место хранения/изменения конфигураций, кода, ACL
+ Можно написать статью про то как круто реализовывать бизнес логику в БД
И теперь парочка минусов из сотни возможных:
— Сложность скейлинга. Вот уперлась БД по памяти (а такое не редкость в кровавом энтерпрайзе) и что делать? Правильно, добавлять памяти! Когда можно просто развернуть еще один AS и размазать нагрузку как вертикально так и горизонтально. Вы можете возразить и сказать, а как же кластер? Да вот в энтерпрайзе не любят postgre и mysql, а любят Оракл и DB2 где кластеризация мало того что стоит как самолет, так еще и слабо поддерживаемое, неуправляемое решение, к тому же это требует допиливания для поддержания ACID.
— Неуправляемый код (триггеры, шмиггеры — это вообще «до свидание»)
— Тестирование БД требует написания отдельного приклада (то есть, штат разработчиков БД, штат разработчик тестов для БД — беда с точки зрения финансов и количество ч/с для проекта) — да и зачем это нужно?!
— Непереносимость решения — нельзя поменять СУБД, железо и прочее никогда!
— Режим Создателя для разработчика(ов) БД — тот кто спроектировал БД и заработал ее большую часть — на нем все держится, без него система не получит развития, архитектор БД имеет полную власть над всем. Другой вопрос, что если изначально составить карту, все описать и так далее — жизнь будет проще, но все мы живем в реальном мире, где такое бывает редко и чаще, если даже и описаны ключевые моменты, то все 65535 триггеров и процедур не будут описаны никогда.
Все вышеописанное это 2-х летний опыт архитектора на проекте где бизнес логика была в БД. Не советую никому браться за подобные системы. А другие вице-президенты пусть и дальше пишут рекламные статейки-проспекты про отличную идею реализации бизнес логики в БД продвигая свой продукт.
Вы можете возразить и сказать, а как же кластер?
… требует допиливания для поддержания ACID.
А какая альтернатива? Ок, понятно, что Oracle-кластер это сложно и дорого, а какое решение дешевое и простое «из коробки»? Если нам нужен строго ACID-кластер на «интерпрайз-левел» технологиях, то нам определенно придется платить и мучаться, не с Ораклом, так с чем-то похожим. Вы же не своими руками кластер пишете?
Абсолютно с вами согласен. Был в такой же ситуации. В добавок, я попал в новую команду, т.к. старая ушла, оставив систему в плачевном состоянии и без документации. Так что нам скучно не было…
Моя основная позиция против бизнес логики в базе потому что:
1. Сложнее дебажить
2. Логика размазана
3. Если база начнет захлебываться, то это будет на порядки сложнее скалировать, нежели если бы это было логикой со стороны кода.
Как с точки зрения последующего мейнтенанса, так и с точки зрения скалирования это плохо. Да, возможно вы сможете написать быстрее что-то там, но потом поддерживать вам это будет стоить 10x по сравнению если бы у вас логика была просто на стороне приложения. Я это говорю основываясь на своем опыте работы как над кровавыми оракловскими энтерпрпайзами, где 70% всей логики в базе — кстати очень здорово расхлебывать им теперь перформанс проблемы так как бОльшая часть всей логики на стороне БД ;) Так и имея опыт разработки где база — просто хранилище данных, в котором проблемы как сопровождения так и перформанса решаются на порядки проще и дешевле.
Вопрос — зачем вы все еще пишите логику на стороне базы?
1. Сложнее дебажить
2. Логика размазана
3. Если база начнет захлебываться, то это будет на порядки сложнее скалировать, нежели если бы это было логикой со стороны кода.
Как с точки зрения последующего мейнтенанса, так и с точки зрения скалирования это плохо. Да, возможно вы сможете написать быстрее что-то там, но потом поддерживать вам это будет стоить 10x по сравнению если бы у вас логика была просто на стороне приложения. Я это говорю основываясь на своем опыте работы как над кровавыми оракловскими энтерпрпайзами, где 70% всей логики в базе — кстати очень здорово расхлебывать им теперь перформанс проблемы так как бОльшая часть всей логики на стороне БД ;) Так и имея опыт разработки где база — просто хранилище данных, в котором проблемы как сопровождения так и перформанса решаются на порядки проще и дешевле.
Вопрос — зачем вы все еще пишите логику на стороне базы?
В целом поддерживаю. Есть некоторые соображение правда. Для пользователей Оракла (именно его) — логика в базе имеет свои преимущества: не завязанность бинарников на ОС/железо (ну это и про другие СУБД верно), работа с большими, очень большими объемами данных все же пишется за меньше строк и работает с данными напрямую без оверхеда (как на получение и обработку, так и на интерпретацию).
Для сайтов на LAMP или вообще приложений, где БД нужна только для хранения человеко-читаемого контента, это всё не нужно. Он (контент) так и так выйдет за пределы базы, «перерабатывать» его внутри базы нет нужды.
Тоже работал с системами, где 70% это PL/SQL ;) В принципе, даже те десять лет назад оракл очень много мог по межпроцессному взаимодействию в *nix, а таскать для разных клиентов бинарники на сумеречные платформы стоило куда дороже в те времена (и Кайт был тогда царь и бох). Сейчас другие архитектурные подходы и инструменты, возможно концепции стоит и переосмыслить, да.
Для сайтов на LAMP или вообще приложений, где БД нужна только для хранения человеко-читаемого контента, это всё не нужно. Он (контент) так и так выйдет за пределы базы, «перерабатывать» его внутри базы нет нужды.
Тоже работал с системами, где 70% это PL/SQL ;) В принципе, даже те десять лет назад оракл очень много мог по межпроцессному взаимодействию в *nix, а таскать для разных клиентов бинарники на сумеречные платформы стоило куда дороже в те времена (и Кайт был тогда царь и бох). Сейчас другие архитектурные подходы и инструменты, возможно концепции стоит и переосмыслить, да.
Итоговый код триггера будет выглядеть так:
SELECT o.max_limit, o.name INTO max_limit, client_name FROM org o WHERE o.id = NEW.org_id_client; IF NEW.sum > max_limit THEN CALL raise_error(CONCAT('Сумма ... не может превышать лимит ...')); END IF;
В коде будет проще и понятнее:
if ($this->sum > $this->org->max_limit) {
throw new MaxLimitException('Сумма ... не может превышать лимит ...');
}
Результат поиска org по id может быть закеширован и использован при обращении из любых других сущностей.
Или более красивый вариант с использованием функции
SET msg := (SELECT raise_error(CONCAT('Сумма ... не может превышать лимит ...')) FROM org o WHERE o.id = NEW.org_id_client AND NEW.sum > o.max_limit );
А что тут красивого? Неочевидный код, напоминает какую-нибудь хитрую ассемблерную конструкцию. Одно сравнение относится к бизнес-логике, второе нужно для связи по ключу, а находятся они вместе в одном выражении WHERE. И «SET msg» в зависимости от них может и не случиться, хотя на первый взляд это просто присваивание.
Я создал триггеры, которые в каждом BEFORE триггере создают MEMORY TEMPORARY TABLE с одноимёнными столбцами
Аналог загрузки данных из БД в переменную. Вы придумали то, что при бизнес-логике в приложении появляется само собой.
Кода стало меньше, он весь в одном месте и он не дублируется! Такой код поддерживать очень легко.
В приложении код еще проще, и поддерживать легче.
Будет что-то типа этого
UPDATE doc_pos_tmp_trg
INNER JOIN docs ON doc_pos_tmp_trg.new_doc_id = docs.id
SET dp.new_price = get_price(dp.new_material_id, d.org_id_client)
WHERE dp.time = 'B' AND dp.type = 'I';
// function DocPos::beforeInsert()
$this->price = getPrice($this->material_id, $this->doc->org_id_client);
UPDATE docs
INNER JOIN doc_pos_tmp_trg ON docs.id IN (doc_pos_tmp_trg.new_doc_id, doc_pos_tmp_trg.old_doc_id)
SET sum = IFNULL(docs.sum, 0)
- CASE
WHEN doc_pos_tmp_trg.old_doc_id = id
THEN IFNULL(doc_pos_tmp_trg.old_kol * doc_pos_tmp_trg.old_price, 0)
ELSE 0
END
+ CASE
WHEN doc_pos_tmp_trg.new_doc_id = id
THEN IFNULL(doc_pos_tmp_trg.new_kol * doc_pos_tmp_trg.new_price, 0)
ELSE 0
END
WHERE doc_pos_tmp_trg.time = 'A';
// function DocPos::beforeInsert()
$oldAttributes = $this->oldAttributes;
$docId = ($this->doc_id ?: $oldAttributes['doc_id']);
$doc = Doc::find($docId);
if ($doc->sum === null) {
$doc->sum = 0;
}
if ($oldAttributes['doc_id'] !== null) {
$doc->sum -= $oldAttributes['kol'] * $oldAttributes['price'];
}
if ($this->doc_id !== null) {
$doc->sum += $this->kol * $this->price;
}
$doc->save();
A почему бы, если есть выбор, каждому не заниматься своим делом? БД — работа с данными. Она для этого придумана. Зачем какие-то костыли с debug, с тестами,… если есть инструменты, которые сами под это заточены?
У нас на данный момент большинство логики в БД ибо начальник базист. Вот и все «аргументы» из моего богатого опыта.
Я лично строго за бизнес логику там где ей место. В 99% это точно не ДБ. Натерпелся этой красоты… Простихоссподи. Имхо.
У нас на данный момент большинство логики в БД ибо начальник базист. Вот и все «аргументы» из моего богатого опыта.
Я лично строго за бизнес логику там где ей место. В 99% это точно не ДБ. Натерпелся этой красоты… Простихоссподи. Имхо.
В коде будет проще и понятнее:
if ($this->sum > $this->org->max_limit) { throw new MaxLimitException('Сумма ... не может превышать лимит ...'); }
Вижу тут как минимум один лишний запрос из php к mysql серверу для извлечения max_limit и client_name.
Во-вторых, а что если есть ещё Python скрипт который инсертит в позиции? На Питоне логику повторять?
Внешний php сервис писать который Питон будет дёргать?
Чувствуйте как мы обрастаем кучей кода.
По поводу почему функцией красивее — «если можно, сделай это с помощью одного оператора SQL»
Не создаются лишние переменные, нет переключения контекста, меньше логических операций, тупо меньше кода, следовательно, будет работать быстрее и поддерживать легче.
По реализации в коде.
1. $oldAttributes = $this->oldAttributes;
Видимо придётся дёргать SELECT'ом — доп. нагрузка
2. У Вас будет генериться много запросов которые гоняются между php <=> mysql. Ещё нагрузка.
3. Как бы Вы реализовали, например, сервис копирования документов за период. С логикой в коде, будет сгенерированно 100500 запросов к БД, которые просто положат сервер. Отсюда похоже и растут ноги байки, что БД с логикой сложнее скалить.
Я же просто написал бы 3-4 SQL команды вида
INSERT INTO docs ... SELECT .. FROM docs d ... WHERE d.date BETWEEN ...
INSERT INTO doc_pos ... SELECT .. FROM doc_pos .. docs ... WHERE d.date BETWEEN ...
А теперь давайте, применяем паттерн переиспользования кода, пишем CORE процедуру, используем ее в 50 других процедурах, далее, делаем перегруженные процедуры (по 2-3 на эти 50 зависящих от CORE) для тонких клиентов, API, и обратной совместимости всего этого. А теперь… меняем CORE процедуру (например, количество предикатов) и начинаем методично перекомпилировать все ~150 процедур зависящих от нее, соблюдаем порядок компилирования, пишем дополнительный код для совместимости всего прочего, пишем еще 100500 тестов на этот дополнительный код.
Вижу тут как минимум один лишний запрос из php к mysql серверу
Это будет один запрос на все время работы скрипта, а кроме того, его можно брать из кеша, а не из БД. То есть один запрос к БД на N пользовательских запросов на чтение.
Внешний php сервис писать который Питон будет дёргать?
Да, сервис называется API. Написание API не просто так стало популярным. Никакой кучи кода нет. Либо вы пишете код в БД, либо в PHP. Да, в клиентах API кода будет побольше, чем если бы они коннектились напрямую к базе, зато нет проблем с контролем доступа к таблицам, авторизацией, и прочими инфраструктурными вещами.
Не создаются лишние переменные, нет переключения контекста, меньше логических операций, тупо меньше кода
Я даже количество символов сравнил. В варианте на PHP кода меньше. То, что не создаются переменные с понятными именами, это скорее минус. Не совсем понял, что вы имели в виду насчет контекста, но то, что в рамках одного условия логические операторы служат разным целям, это тоже понятности не добавляет.
1. $oldAttributes = $this->oldAttributes;
Видимо придётся дёргать SELECT'ом — доп. нагрузка
Нет. При загрузке данных сущности из БД устанавливаются свойства в $this и те же самые в $this->oldAttributes. При обработке меняются данные в $this, а если надо, можно прочитать прежние значения из $this->oldAttributes.
У Вас будет генериться много запросов которые гоняются между php <=> mysql. Ещё нагрузка.
Селекты такие же, как и в вашем коде.
Как бы Вы реализовали, например, сервис копирования документов за период.
Не очень понятно, зачем копировать сущности, но это ладно. Скорее всего, я бы дернул их таким же селектом, обработал, и вставил новые данные в один или несколько групповых инсертов. Связанные сущности загружаются через дополнительный запрос с IN. В сложных случаях никто не мешает вызывать из кода запросы с INNER JOIN. То есть, счет запросов к базе идет на единицы или в крайнем случае на десятки, но никак не 100500. И мне кажется, это не такая частая операция, чтобы из-за нее переносить всю логику в базу.
Нет. При загрузке данных сущности из БД устанавливаются свойства в $this и те же самые в $this->oldAttributes. При обработке меняются данные в $this, а если надо, можно прочитать прежние значения из $this->oldAttributes.для PL/SQL и PL/PgSQL давно можно использовать пользовательские переменные.
зато нет проблем с контролем доступа к таблицам, авторизацией
это как раз в варианте с БЛ на сервере нет этих проблем, т.к. всё имплементировано в триггерах и VIEW, а API-интерфейсом к манипуляции этими данных являются всем знакомые команды INSERT/UPDATE/DELETE
Я даже количество символов сравнил. В варианте на PHP кода меньше. То, что не создаются переменные с понятными именами, это скорее минус.
Это я про оба своих варианта.
Не совсем понял, что вы имели в виду насчет контекста, но то, что в рамках одного условия логические операторы служат разным целям, это тоже понятности не добавляет.
Переключение контекста между SQL и SQL/PSM
При загрузке данных сущности из БД устанавливаются свойства в $this и те же самые в $this->oldAttributes. При обработке меняются данные в $this, а если надо, можно прочитать прежние значения из $this->oldAttributes.
Когда при загрузке? Если в момент загрузки страницы, то так не пойдёт, т.к. пока пользователь на странице oldAttributes могут измениться. Так что придётся считать перед изменением и ещё заблокировать строку пока вы на клиенте вычисляете остальные значения полей, чтобы в этот период никто не изменил строку.
Селекты такие же, как и в вашем коде.
Селекты-то такие же, НО у меня переключение контекста между SQL и SQL/PSM, а в Вашем случае между php и mysql, а это существенная просадка производительности.
Далее, у меня функция get_price серверная, у Вас я так понимаю, php-шная. Предположим get_price в простом варианте рассчитывается из 5 сущностей: прайс, скидка клиента общая, скидка клиента по производителю, скидка клиента по товару, курс валюты.
В итоге на вставку одной позиции документа надо поочерёдно сгенерить 5 запросов и обменяться ими php и mysql'ю.
Я уж боюсь предстваить как бы Вы реализовали selectList для выбора товара с ценой и остатком без использования логики в БД.
В моём случае это был бы примерно такой запрос:
SELECT m.name
, get_price(m.id, d.org_id_client, d.date) price
, cs.stock
FROM materials m
LEFT JOIN docs d ON d.id = @doc_id
LEFT JOIN cur_stock cs ON cs.warehouse_id = d.warehouse_id AND m.id = cs.material_id
WHERE m.name LIKE CONCAT('%', IFNULL(@term, ''), '%')
LIMIT 50
Не очень понятно, зачем копировать сущности, но это ладно.Просто пример массовой обработки данных.
Скорее всего, я бы дернул их таким же селектом, обработал, и вставил новые данные в один или несколько групповых инсертов. Связанные сущности загружаются через дополнительный запрос с IN. В сложных случаях никто не мешает вызывать из кода запросы с INNER JOIN. То есть, счет запросов к базе идет на единицы или в крайнем случае на десятки, но никак не 100500. И мне кажется, это не такая частая операция, чтобы из-за нее переносить всю логику в базу.
Возможно Вы не поняли. Вы мне описали имплементацию обработки одной(!) строки позиции, для массовой обработки Вам придётся либо мириться с провалом производительности, либо допиливать код руками. В моём случае переключением контекста между SQL и SQL/PSM можно пренебречь по сравнению с php и mysql. Поэтому такую систему будет гораздо проще масштабировать.
это как раз в варианте с БЛ на сервере нет этих проблем
И поэтому вы написали целую статью, как сделать безопасность на уроне строк?) В приложении в простом случае это делается тривиально
if ($entity->user_id == $currentUser->id), в сложном через RBAC if ($user->can('viewEntity', ['entity' => $entity]))Когда при загрузке? Если в момент загрузки страницы, то так не пойдёт, т.к. пока пользователь на странице oldAttributes могут измениться. Так что придётся считать перед изменением и ещё заблокировать строку пока вы на клиенте вычисляете остальные значения полей, чтобы в этот период никто не изменил строку.
В момент загрузки данных сущности при обработке запроса. Да, это обеспечивается блокировками и транзакциями, они для того и придуманы, это не очень сложно сделать. MySQL делает примерно то же самое, чтобы обеспечить атомарность и последовательность запросов.
а это существенная просадка производительности
А вы проверяли? Давайте проверим. Насколько я понимаю, у вас есть возможность сделать тестовую базу с данными и триггерами. Допустим, вы бы могли выложить такую базу на github, и привести пару примеров, на которых можно проверить производительность. А я бы попробовал написать приложение, которое делает то же самое. Можно будет проверить разницу и решить, стоит ли она того, чтобы переносить бизнес-логику в базу.
прайс, скидка клиента общая, скидка клиента по производителю, скидка клиента по товару, курс валюты. В итоге на вставку одной позиции документа надо поочерёдно сгенерить 5 запросов и обменяться ими php и mysql'ю.
Редко меняющиеся сущности, для них как раз можно использовать кеширование и не дергать базу по пустякам.
Я уж боюсь представить как бы Вы реализовали selectList для выбора товара с ценой и остатком без использования логики в БД.
Из запроса не очень понятно, по каким полям связаны docs и остальные 2 сущности, текущий документ джойнится ко всем записям. Допустим, мы смотрим страницу документа и нам надо вывести выпадающий список материалов с ценой. Я бы сделал примерно так:
$term = 'Material';
$query = Materials::find();
$query->joinWith('curStocks');
$query->where(['warehouse_id' => $document->warehouse_id]);
$query->andWhere(['like', 'name', $term]);
$query->limit(50);
$materials = $query->all();
// foreach по результатам будет в обоих вариантах, независимо от местонахождения бизнес-логики
$data = [];
foreach ($materials as $material) {
$price = getPrice($material, $document);
$data[$material->id] = $material->name . ' - ' . $price;
}
renderSelectList($data);
Вы мне описали имплементацию обработки одной(!) строки позиции
Мне показалось, что слова «в один или несколько групповых инсертов» намекают на множественное число записей) Я привел пример для массовой обработки. Насколько он будет менее производительным, надо проверять на практике. У вас тоже на каждую строку будет дергаться триггер с несколькими селектами. Дело не в переключении контекстов, процессы обычно работают параллельно на разных ядрах, задержки в основном связаны не с процессором, а с вводом-выводом — сеть и диск. Кстати, база будет доступна для чтения/записи в вашем варианте, и можно ли это контролировать?
Попробовал прописать БЛ в программе для примера с get_price. На довольно простой логике просадка производительности в 2-4 раза. Завтра вечером постараюсь выложить тестовый пример. Надеюсь это Вас убедит. Тем не менее, мне все равно приятно, что Вы искренне пытаетесь разобраться и оппонируйте аргументируя свою позицию.
Сори за небольшую задержку. Вот пример, ваял на скорую руку.
1. Настроить config.php
2. Запустить generate.php — создаст и заполнит базу
3. Запустить selectlist.html
1. Настроить config.php
2. Запустить generate.php — создаст и заполнит базу
3. Запустить selectlist.html
config.php
<?php
class Config {
public $db_host = '127.0.0.1';
public $db_username = 'username';
public $db_password = 'password';
public $db_name = 'habr';
}
generate.php
<?php
require_once 'config.php';
$config = new Config();
$db = new PDO("mysql:host=" . $config->db_host.";dbname=".$config->db_name, $config->db_username, $config->db_password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$db->exec('SET names utf8');
$db->exec('SET storage_engine=innoDB');
$db->exec("SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY'");
//$cn = isset($_REQUEST['cn'])?$_REQUEST['cn'] * 1:10000;
$material_cn = 10000;
$client_cn = 1000;
$db->exec("SET FOREIGN_KEY_CHECKS=0");
// Документы
$db->exec("DROP TABLE IF EXISTS `docs`");
$db->exec("CREATE TABLE `docs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`org_id_addr` int(11) NOT NULL, -- Склад
`org_id_client` int(11) NOT NULL,
PRIMARY KEY (`id`)
)");
$db->exec("INSERT `docs` (`id`, `date`, `org_id_addr`, `org_id_client`) VALUES (20515, '2016-10-12', 1, 500)");
// Материалы
$db->exec("DROP TABLE IF EXISTS `materials`");
$db->exec("CREATE TABLE `materials` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`manufact_id` int(11) NOT NULL, -- Производитель
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
)");
$q = $db->prepare("INSERT materials (name, manufact_id) VALUES (CONCAT('Материал товар №', LPAD(:name, 5, '0')), :manufact_id)");
for ($i = 1; $i <= $material_cn; $i++) {
$q->execute(['name' => $i, 'manufact_id' => (($i % 10) + 1)]);
}
// Остатки
$db->exec("DROP TABLE IF EXISTS `cur_stock`");
$db->exec("CREATE TABLE `cur_stock` (
`mat_id` int(11) NOT NULL,
`org_id` int(11) NOT NULL, -- Склад
`kol` decimal(10,3) NOT NULL,
PRIMARY KEY (`org_id`,`mat_id`),
KEY `mat_id` (`mat_id`),
CONSTRAINT `cur_stock_ibfk_1` FOREIGN KEY (`mat_id`) REFERENCES `materials` (`id`)
)");
//echo PHP_EOL.'<br>INSERT INTO cur_stock = '.
$db->exec("INSERT INTO cur_stock (mat_id, org_id, kol)
SELECT m.id mat_id, o.org_id, FLOOR(RAND() * 500) kol
FROM
(SELECT id FROM materials ORDER BY RAND() LIMIT ".floor($material_cn / 1.5).") m
, (SELECT 1 org_id UNION ALL SELECT 2 UNION ALL SELECT 3) o");
// Прайсы клиентов
$db->exec("DROP TABLE IF EXISTS `client_price`");
$db->exec("CREATE TABLE `client_price` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`org_id` int(11) NOT NULL, -- Клиент
`price_type_id` int(11) NOT NULL, -- Тип прайса
`org_id_manufact` int(11) DEFAULT NULL, -- Производитель
`mat_id` int(11) DEFAULT NULL, -- Материал
`discount` decimal(5,2) DEFAULT NULL, -- Скидка
PRIMARY KEY (`id`),
UNIQUE KEY `org_id` (`org_id`,`price_type_id`,`org_id_manufact`,`mat_id`,`discount`),
KEY `price_type_id` (`price_type_id`),
KEY `mat_id` (`mat_id`),
KEY `org_id_manufact` (`org_id_manufact`),
CONSTRAINT `client_price_ibfk_1` FOREIGN KEY (`mat_id`) REFERENCES `materials` (`id`)
)");
// Общие прайсы
$q = $db->prepare("INSERT client_price (org_id, price_type_id) VALUES (:org_id, :price_type_id)");
for ($i = 1; $i <= $client_cn; $i++) {
$q->execute(['org_id' => $i, 'price_type_id' => (($i % 2) + 1)]);
}
// Скидки по производителю
$db->exec("INSERT INTO client_price (org_id, price_type_id, org_id_manufact)
SELECT o.org_id
, FLOOR(1 + RAND() * 3) price_type_id
, m.manufact_id
FROM
(SELECT DISTINCT manufact_id FROM materials) m
, (SELECT DISTINCT org_id FROM client_price ORDER BY CASE WHEN org_id = 500 THEN 1 ELSE 2 END, RAND() LIMIT ".floor($client_cn / 10).") o
ORDER BY RAND()
LIMIT ".floor($client_cn / 2));
// Скидки по материалам
$db->exec("INSERT INTO client_price (org_id, price_type_id, mat_id)
SELECT o.org_id
, FLOOR(1 + RAND() * 3) price_type_id
, m.id
FROM
(SELECT id FROM materials ORDER BY RAND() LIMIT ".floor($material_cn / 10).") m
, (SELECT DISTINCT org_id FROM client_price ORDER BY CASE WHEN org_id = 500 THEN 1 ELSE 2 END, RAND() LIMIT ".floor($client_cn / 10).") o
ORDER BY RAND()
LIMIT ".(floor($client_cn / 10) * floor($material_cn / 10) / 10));
// Курсы валют
$db->exec("DROP TABLE IF EXISTS `exchange_rate`");
$db->exec("CREATE TABLE IF NOT EXISTS `exchange_rate` (
`currency_id` int(11) NOT NULL,
`date` date NOT NULL,
`value` decimal(29,15) NOT NULL,
PRIMARY KEY (`currency_id`,`date`)
)");
$q = $db->prepare("INSERT exchange_rate (currency_id, date, value) VALUES (:currency_id, DATE(NOW()) - INTERVAL :i DAY, :value)");
for ($i = 100; $i >= 0; $i--) {
$q->execute(['currency_id' => 2, 'i' => ($i + 1), 'value' => 100 - ($i / 100)]);
}
// Прайсы
$db->exec("DROP TABLE IF EXISTS prices");
$db->exec("CREATE TABLE `prices` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`price_type_id` int(11) NOT NULL,
`tmc_id` int(11) NOT NULL,
`date` date NOT NULL,
`price` decimal(29,15) NOT NULL,
`currency_id` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `tmc_id` (`tmc_id`,`price_type_id`,`date`),
KEY `price_type_id` (`price_type_id`),
CONSTRAINT `prices_ibfk_2` FOREIGN KEY (`tmc_id`) REFERENCES `materials` (`id`)
)");
$db->exec("INSERT INTO prices (price_type_id, tmc_id, date, price, currency_id)
SELECT pt.price_type_id, m.id
, d.date
, FLOOR(RAND() * 1000) price
, FLOOR(1 + RAND() * 2) currency_id
FROM materials m
, (SELECT DATE(NOW()) - INTERVAL dd.id MONTH AS date FROM materials dd ORDER BY id LIMIT 10) d -- Даты
, (SELECT 1 price_type_id UNION ALL SELECT 2 UNION ALL SELECT 3) pt -- Типы прайсов
");
// Функция get_price
$db->exec("DROP FUNCTION IF EXISTS get_client_price");
$db->exec("CREATE FUNCTION get_client_price(v_org_id INT, v_mat_id INT, v_date DATE)
RETURNS decimal(29,15) READS SQL DATA
BEGIN
/* версия 00002 */
DECLARE v_price_date date;
DECLARE v_price decimal(29,15);
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN
RETURN NULL;
END;
SET @client_price_type_id := NULL;
SET @client_price_discount := NULL;
SET @client_price_date := NULL;
-- Определяем тип прайса
SELECT price_type_id, discount
INTO @client_price_type_id, @client_price_discount
FROM (
SELECT cp.price_type_id, cp.discount, 10 rule
FROM client_price cp
WHERE cp.org_id = v_org_id
AND cp.mat_id = v_mat_id
AND cp.org_id_manufact IS NULL
UNION ALL
SELECT cp.price_type_id, cp.discount, 50 rule
FROM client_price cp
INNER JOIN materials m ON cp.org_id_manufact = m.manufact_id
WHERE cp.org_id = v_org_id
AND m.id = v_mat_id
AND cp.mat_id IS NULL
UNION ALL
SELECT cp.price_type_id, cp.discount, 100 rule
FROM client_price cp
WHERE cp.org_id = v_org_id
AND cp.mat_id IS NULL
AND cp.org_id_manufact IS NULL
) t
ORDER BY rule
LIMIT 1;
-- Определяем цену
SELECT p.price
* CASE WHEN p.currency_id = 1 THEN 1
ELSE (SELECT value FROM exchange_rate r WHERE p.currency_id = r.currency_id AND r.date <= v_date ORDER BY r.date DESC LIMIT 1)
END
* (100 - IFNULL(@client_price_discount, 0)) / 100 price
, p.date
INTO v_price, @client_price_date
FROM prices p
WHERE p.price_type_id = @client_price_type_id
AND p.tmc_id = v_mat_id
AND p.date <= v_date
ORDER BY p.date DESC
LIMIT 1;
RETURN v_price;
END;
");
$db->exec("SET FOREIGN_KEY_CHECKS=1");
echo sprintf("%.6f", microtime(true) - $_SERVER["REQUEST_TIME_FLOAT"]);
get_db_list.php
<?php
require_once 'config.php';
$config = new Config();
$db = new PDO("mysql:host=" . $config->db_host.";dbname=".$config->db_name, $config->db_username, $config->db_password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$db->exec('SET names utf8');
$db->exec('SET storage_engine=innoDB');
$db->exec("SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY'");
$term = isset($_REQUEST['term'])?$_REQUEST['term']:'';
$doc_id = isset($_REQUEST['doc_id'])?$_REQUEST['doc_id']:null;
$page = @$_REQUEST['page']*1?$_REQUEST['page']:1;
//$per_page = 1000;
$per_page = @$_REQUEST['page']*1?$_REQUEST['per_page']:50;
$sql = "SELECT SQL_NO_CACHE CONCAT(m.name
, ' - ', IFNULL(TRIM(get_client_price(d.org_id_client, m.id, d.date)) + 0, '')
, ' - ', IFNULL(cs.kol, '')
) name
FROM materials m
LEFT JOIN docs d ON d.id = :doc_id
LEFT JOIN cur_stock cs ON cs.org_id = d.org_id_addr AND m.id = cs.mat_id
WHERE m.name LIKE CONCAT('%', IFNULL(:term, ''), '%')
AND NOW() = NOW()
ORDER BY m.name
";
$sql .= PHP_EOL.'LIMIT '.(($page - 1) * $per_page).', '.$per_page;
$q = $db->prepare($sql);
$q->execute(['doc_id' => $doc_id, 'term' => $term]);
$q->setFetchMode(PDO::FETCH_ASSOC);
$data = $q->fetchAll();
$return = [];
$return['total_count'] = ($page - 1) * $per_page + (sizeof($data) < $per_page?sizeof($data):($per_page+1));
$return['items'] = $data;
$return['time'] = sprintf("%.6f", microtime(true) - $_SERVER["REQUEST_TIME_FLOAT"]);
echo json_encode($return);
get_app_list.php
<?php
require_once 'config.php';
$config = new Config();
$db = new PDO("mysql:host=" . $config->db_host.";dbname=".$config->db_name, $config->db_username, $config->db_password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$db->exec('SET names utf8');
$db->exec('SET storage_engine=innoDB');
$db->exec("SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY'");
$term = isset($_REQUEST['term'])?$_REQUEST['term']:'';
$doc_id = isset($_REQUEST['doc_id'])?$_REQUEST['doc_id']:null;
$page = @$_REQUEST['page']*1?$_REQUEST['page']:1;
//$per_page = 1000;
$per_page = @$_REQUEST['page']*1?$_REQUEST['per_page']:50;
// Построением sql запросов будет заниматься ОРМ, но для простоты пишу итоговые запросы который сгенерит ОРМ
// Документ
$sql = "SELECT SQL_NO_CACHE * FROM docs WHERE id = :doc_id AND NOW() = NOW()";
$q = $db->prepare($sql);
$q->execute(['doc_id' => $doc_id]);
$doc = $q->fetch();
// Материалы
$sql = "SELECT SQL_NO_CACHE m.id, m.name
, cs.kol
FROM materials m
LEFT JOIN cur_stock cs ON cs.org_id = :org_id AND m.id = cs.mat_id
WHERE m.name LIKE CONCAT('%', :term, '%')
AND NOW() = NOW()";
$sql .= PHP_EOL.'LIMIT '.(($page - 1) * $per_page).', '.$per_page;
$q = $db->prepare($sql);
$q->execute(['org_id' => $doc['org_id_addr'], 'term' => $term]);
$materials = $q->fetchAll();
function get_client_price($mat, $doc){
global $db;
// Определяем скидку по клиенту
// Скидка по товару
$sql = "SELECT SQL_NO_CACHE cp.price_type_id, cp.discount
FROM client_price cp
WHERE cp.org_id = :org_id
AND cp.mat_id = :mat_id
AND cp.org_id_manufact IS NULL
AND NOW() = NOW()";
$q = $db->prepare($sql);
$q->execute(['org_id' => $doc['org_id_client'], 'mat_id' => $mat['id']]);
$r = $q->fetch();
if ($r === false) {
// Скидка по производителю
$sql = "SELECT SQL_NO_CACHE cp.price_type_id, cp.discount
FROM client_price cp
INNER JOIN materials m ON cp.org_id_manufact = m.manufact_id
WHERE cp.org_id = :org_id
AND m.id = :mat_id
AND cp.mat_id IS NULL
AND NOW() = NOW()";
$q = $db->prepare($sql);
$q->execute(['org_id' => $doc['org_id_client'], 'mat_id' => $mat['id']]);
$r = $q->fetch();
if ($r === false) {
// Общая скидка
$sql = "SELECT SQL_NO_CACHE cp.price_type_id, cp.discount
FROM client_price cp
WHERE cp.org_id = :org_id
AND cp.mat_id IS NULL
AND cp.org_id_manufact IS NULL
AND NOW() = NOW()";
$q = $db->prepare($sql);
$q->execute(['org_id' => $doc['org_id_client']]);
$r = $q->fetch();
if ($r === false) {
$r = ['price_type_id' => null, 'discount' => 0];
}
}
}
$client_price_type_id = $r['price_type_id'];
$client_price_discount = $r['discount'];
//exit($doc['org_id_client'].' - '.$client_price_type_id.' - '.$client_price_discount);
// Определяем цену
$sql = "SELECT SQL_NO_CACHE p.price, p.date, p.currency_id
FROM prices p
WHERE p.price_type_id = :client_price_type_id
AND p.tmc_id = :mat_id
AND p.date <= :date
AND NOW() = NOW()
ORDER BY p.date DESC
LIMIT 1";
$q = $db->prepare($sql);
$q->execute(['client_price_type_id' => $client_price_type_id, 'mat_id' => $mat['id'], 'date' => $doc['date']]);
$r = $q->fetch();
if ($r === false) {
return null;
}
$price = (100 - ($client_price_discount?$client_price_discount:0)) * $r['price'] / 100;
// Если Валюта не Рубль, то пересчитаем по курсу на дату
if ($r['currency_id'] != 1) {
$sql = "SELECT SQL_NO_CACHE value FROM exchange_rate r WHERE :currency_id = r.currency_id AND r.date <= :date AND NOW() = NOW() ORDER BY r.date DESC LIMIT 100";
$q = $db->prepare($sql);
$q->execute(['currency_id' => $r['currency_id'], 'date' => $doc['date']]);
//$r = $q->fetch(); $value = $r['value'];
$value = $q->fetchColumn();
$price = $price * $value;
}
return $price;
}
$data = [];
foreach ($materials as $material) {
$price = get_client_price($material, $doc);
$data[] = ['name' => $material['name'].' - '.$price.' - '.$material['kol']];
}
$return = [];
$return['total_count'] = ($page - 1) * $per_page + (sizeof($data) < $per_page?sizeof($data):($per_page+1));
$return['items'] = $data;
$return['time'] = sprintf("%.6f", microtime(true) - $_SERVER["REQUEST_TIME_FLOAT"]);
echo json_encode($return);
selectlist.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title></title>
<link href="//cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/css/select2.min.css" rel="stylesheet" />
<script src="//cdnjs.cloudflare.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
</head>
<body>
<div>Per page <input type="number" value="500" id="per_page"></div>
<div>Номер документа <input type="number" value="20515" id="doc_id"></div>
<div>Список из app <select id="sl_app" style="width: 400px;"></select> <span id="time_sl_app"></span></div>
<div>Список из DB <select id="sl_db" style="width: 400px;"></select> <span id="time_sl_db"></span></div>
<script>
$(function(){
$('#sl_db').select2({allowClear: true
, placeholder: "Выберите значение"
, language: 'ru'
, ajax: {url: "get_db_list.php"
, dataType: 'json'
, delay: 250
, data: function (params) {
return {
term: params.term
, page: params.page
, doc_id: $('#doc_id').val()
, per_page: $('#per_page').val()
};
}
, processResults: function (data, params) {
params.page = params.page || 1;
$('#time_sl_db').text(data.time);
return {
results: data.items
, pagination: {
more: (params.page * 50) < data.total_count
}
};
}
}
, templateResult: function(repo) {
if (repo.loading) return repo.text;
return repo.name;
}
, templateSelection: function(repo) {
return repo.name || repo.text;
}
, escapeMarkup: function(markup) {
return markup;
}
});
$('#sl_app').select2({allowClear: true
, placeholder: "Выберите значение"
, language: 'ru'
, ajax: {url: "get_app_list.php"
, dataType: 'json'
, delay: 250
, data: function (params) {
return {
term: params.term
, page: params.page
, doc_id: $('#doc_id').val()
, per_page: $('#per_page').val()
};
}
, processResults: function (data, params) {
params.page = params.page || 1;
$('#time_sl_app').text(data.time);
return {
results: data.items
, pagination: {
more: (params.page * 50) < data.total_count
}
};
}
}
, templateResult: function(repo) {
if (repo.loading) return repo.text;
return repo.name;
}
, templateSelection: function(repo) {
return repo.name || repo.text;
}
, escapeMarkup: function(markup) {
return markup;
}
});
})
</script>
</body>
<script src="//cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/js/select2.full.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/js/i18n/ru.js"></script>
</html>
Тоже извиняюсь за задержку.
Вижу, вы уже сами сделали алгоритм и в БД и в приложении. Так как тут нас интересует скорость отдачи контента, а не читаемость, то пожалуй тогда не буду переписывать код на ORM. Просто покажу, как можно ускорить показ результатов — добавить внешнюю систему кэширования Memcached. Понятно, что в базе тоже можно настроить кэширование, но кэшироваться будут результаты отдельных запросов, а в случае с get_client_price() можно запоминать готовый результат всех расчетов.
Итак, вначале на моей машине были такие показатели:
Per page: 500
Номер документа: 20515
Список из app: 0.96 — 1.02
Список из DB: 0.34 — 0.36
После добавления кэширования:
Список из app: 1.16 — 1.20 (первый вызов)
Список из app: 0.04 — 0.08 (последующие вызовы)
Это потребовало добавления нескольких строчек без особого изменения остального кода (коммит на github). Там кеширование на 10 секунд, в зависимости от характера изменений в таблицах можно сделать на большее время, или постоянно с инвалидацией кеша при внесении изменений.
Размещение логики в коде дает больше возможностей, как по описанию этой логики, так и по управлению данными.
Вижу, вы уже сами сделали алгоритм и в БД и в приложении. Так как тут нас интересует скорость отдачи контента, а не читаемость, то пожалуй тогда не буду переписывать код на ORM. Просто покажу, как можно ускорить показ результатов — добавить внешнюю систему кэширования Memcached. Понятно, что в базе тоже можно настроить кэширование, но кэшироваться будут результаты отдельных запросов, а в случае с get_client_price() можно запоминать готовый результат всех расчетов.
Итак, вначале на моей машине были такие показатели:
Per page: 500
Номер документа: 20515
Список из app: 0.96 — 1.02
Список из DB: 0.34 — 0.36
После добавления кэширования:
Список из app: 1.16 — 1.20 (первый вызов)
Список из app: 0.04 — 0.08 (последующие вызовы)
Это потребовало добавления нескольких строчек без особого изменения остального кода (коммит на github). Там кеширование на 10 секунд, в зависимости от характера изменений в таблицах можно сделать на большее время, или постоянно с инвалидацией кеша при внесении изменений.
Размещение логики в коде дает больше возможностей, как по описанию этой логики, так и по управлению данными.
Вы же понимаете что любое кеширование и денормализация это усложнение логики, которую надо поддерживать и отслеживать + можно нарываться на очень неприятные ошибки, которые ни юнит тестами не покроешь, ни в дебагере не поймаешь. Например, при добавлении зависимой сущности забыли закодить сбрасывание кеша.
Если уж применять кеширование, то тут эталон для меня это реализация в Oracle через MATERIALIZED_VIEW + QUERY_REWRITE.
1. Код кеширования находится отдельно от кода модели данных и никак с ним не связан
2. При изменении модели данных максимальный ущерб это запрос не будет использовать кеш, но мы не получим несогласованных данных.
Но я не хотел обсуждать кеширование, мой посыл в другом: разрабатывая приложение в БД Вы имеете серьёзную фору по производительности, поэтому Вам гораздо реже потребуются различные костыли в виде кеширования и т.д. Соответственно код будет более лаконичный и простой, кодить проще, поддерживать проще и дешевле. Приложение более масштабируемое.
Кстати, в коде получения данных для БЛ в приложении я забыл начать транзакцию, а это залочит на запись ВСЕ таблицы из которых мы читаем данные. Не сделав это мы можем получить несогласованные данные
Если уж применять кеширование, то тут эталон для меня это реализация в Oracle через MATERIALIZED_VIEW + QUERY_REWRITE.
1. Код кеширования находится отдельно от кода модели данных и никак с ним не связан
2. При изменении модели данных максимальный ущерб это запрос не будет использовать кеш, но мы не получим несогласованных данных.
Но я не хотел обсуждать кеширование, мой посыл в другом: разрабатывая приложение в БД Вы имеете серьёзную фору по производительности, поэтому Вам гораздо реже потребуются различные костыли в виде кеширования и т.д. Соответственно код будет более лаконичный и простой, кодить проще, поддерживать проще и дешевле. Приложение более масштабируемое.
Кстати, в коде получения данных для БЛ в приложении я забыл начать транзакцию, а это залочит на запись ВСЕ таблицы из которых мы читаем данные. Не сделав это мы можем получить несогласованные данные
БЛ в БД только на стандартных SQL, предоставляемых БД — сущее зло.
Как насчет плагинов (MySQL/MariaDB) или модулей (Postgres)?
Как насчет плагинов (MySQL/MariaDB) или модулей (Postgres)?
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Реализация бизнес-логики в MySQL