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

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

Было решено генерировать HTML со стороны сервера базы данных и через Database Mail формировать рассылку путем выполнения команды sp_send_dbmail.

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

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

Вы, возможно, помните как несколько лет назад стремительно стали набирать популярность NoSQL-базы данных (MongoDB, DynamoDB и другие). Многие пророчили смерть классических реляционных баз данных, торжество новых парадигм и всеобщее счастье в мире. И вы, возможно, в курсе того, как в последний год (или около того) наблюдается откат этой эйфории — выходят статьи типа «Broken by Design: MongoDB Fault Tolerance» и Why You Should Never Use MongoDB. Народ на Хабре на Тостере интересуется — «А почему же Монгу критикуют?», на что получает ответы «перерекламировали», «серебрянной пули нет», «надо выбирать базу данных по задачам».

Все 3 очевидных варианта — «Использовать реляционную БД», «Использовать NoSQL-БД», «Выбирать БД по задачам проекта» мне не нравятся по причине, высказанной в заголовке статьи.

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

А постигшие список модулей Node.js могут убедиться в том, что создатели этих модулей духовно ближе не к дзэн-буддистам, а к байхуистам — к поклонникам движения «Байхуа юньдун» (百花运动), провозглашённого Мао Цзэдуном в 1957 году по мотивам классического китайского стихотворения «пусть расцветают сто цветов, пусть соперничают сто школ», начинающегося словами «бай хуа» («百花», «сто цветов»). Иными словами, модули для Node.js предоставляют, как правило, несколько способов сделать одно и то же, и из них потребитель выбирает тот способ, который более всех пригоден ему.

Но почему не существует такого одного способа, который был бы пригоден для всех?

Ответ на этот вопрос я предлагаю рассмотреть на примере употребления базы данных SQLite.

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

Msg 8117, Level 16, State 1, Line 1
Operand data type char is invalid for sum operator.

Хотя для решения подобного рода задач, для MySQL была добавлена функция GROUP_CONCAT, а в Oracle – LISTAGG. В свою же очередь, SQL Server такого встроенного функционала пока не имеет.

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

Сегодня все большую популярность завоевывают In-Memory BI решения. Кубы уже не в моде, их структура морально устарела, и хотя они довольно прилично масштабируются, требования к скорости работы современных BI систем значительно возросли. Тем не менее, многие компании до сих пор успешно используют аналитику, построенную на одном из OLAP-серверов (Microsoft, Oracle, Cognos, и др.). Мне, например, очень нравится Microsoft SQL Server Analysis Service, и я хотел бы рассказать, как в нем можно использовать немного необычную для аналитики функцию – обратную запись данных в источник (Write Back).

Синтаксис конструкции INSERT может показаться весьма тривиальным, поскольку стандарт T-SQL рассматривал ключевое слово VALUES лишь в контексте вставки данных – INSERT INTO … VALUES ….

С выходом SQL Server 2008 существенно расширился синтаксис T-SQL, благодаря чему стало возможным использовать многострочную конструкцию VALUES, при этом не только в контексте вставки.

В данном топике будет рассмотрена сравнительная эффективность использования конструкции VALUES в различных типовых ситуациях. Чтобы дать объективную оценку полученных результатов, для каждого примера, будет рассмотрен его план выполнения.

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

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

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

С такого рода проблемой я столкнулся, когда применял конструкцию UNPIVOT для преобразования столбцов в строки.

Путем небольшого сравнительного анализа, для UNPIVOT была найдена более эффективная альтернатива.

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

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

Проблематика

Итак, вы – пользователь, имеющий право на чтение в некой СУБД. Вероятно, перед вами стоит набор типовых подзадач:

• Разобраться со структурой данных
• Найти в ней нужные сущности
• Найти в них нужные поля
• Найти связи между сущностями
• Найти интересующие значения
• Отобрать набор значений
• Выбрать нужные данные
• Убедиться, что это действительно ТЕ САМЫЕ данные, которые вы искали
• Сохранить результаты
• Подготовить из них отчеты

Наконец, весьма вероятно, что эти задачи вам надо решать регулярно.

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

Широко известна фраза: «Повторение – мать учения». Возможно, это звучит банально, но на втором году работы, я смог в полной мере прочувствовать смысл этой фразы.

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

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

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

Мы работаем над DWH в телекоммуникациях, поэтому пример, который я рассматриваю, называется «Абонент». Принцип универсален и это мог быть «Клиент» или «Пациент» — в зависимости от отрасли. Я надеюсь методику найдут полезной разработчики DWH из разных отраслей.

Если Вы не понимаете, что такое DWH, измерения и факты, я рекомендую прочитать книгу Ральфа Кимбалла «Dimensional Modeling». Речь идёт о базе данных для аналитики и консолидированной отчетности предприятия, конкретно о формировании и актуализации измерений — таблиц, которые хранят атрибуты (поля) для отбора (WHERE) в будущих запросах.

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

Дополнение к циклу переведенных статей.
Статьи: 1-3, 4-6, 7-9, 10-13, 14-15

Информация в статье относится к 5-й части руководства.

В комментариях один из пользователей небеспричинно упрекнул в отсутствии информации о каскадном удалении данных. Восполняю пробел. У автора статей нет информации на эту тему, поэтому я написал небольшую статью об этом. Она достаточно логично впишется в указанный цикл.
Для начала, чтобы не было путаницы, стоит сказать, что речь не столько и не только о каскадном удалении данных, а о теме ссылочной целостности и внешних ключах, частью которой и является каскадное удаление данных.

Введение.

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

Продолжение.
Предыдущие части: 1-3, 4-6, 7-9, 10-13
Продолжение. Каскадное удаление данных.

14. Другой пример: база данных интернет-магазина.

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

Система интернет-магазина.

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

• Отображение товаров
• Классификация товаров
• Регистрация клиентов
• Добавление товаров в корзину покупок
• Отображение содержимого корзины покупок
• Оформление заказов посетителями
• И т.д.

Определяем сущности и отношения.

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

Продолжение.
Предыдущие части: 1-3, 4-6, 7-9

10. Нормализация баз данных

Указания для правильного проектирования реляционных баз данных изложены в реляционной модели данных. Они собраны в 5 групп, которые называются нормальными формами. Первая нормальная форма представляет самый низкий уровень нормализации баз данных. Пятый уровень представляет высший уровень нормализации.

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

Продолжение.
Предыдущие части: 1-3, 4-6

7. Связь один-ко-многим.

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

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

(Технически лучше говорить о женщине и ее детях вместо матери и ее детях потому, что, в контексте связи один-ко-многим, мать может иметь 0, 1 или множество потомков, но мать с 0 детей не может считаться матерью. Но давайте закроем на это глаза, хорошо?)

Когда одна запись в таблице А может быть связана с 0, 1 или множеством записей в таблице B, вы имеете дело со связью один-ко-многим. В реляционной модели данных связь один-ко-многим использует две таблицы.


Схематическое представление связи один-ко-многим. Запись в таблице А имеет 0, 1 или множество ассоциированных ей записей в таблице B.

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

4. ТАБЛИЦЫ И ПЕРВИЧНЫЕ КЛЮЧИ

Как вы уже знаете из прошлых частей, данные хранятся в таблицах, которые содержат строки или по-другому записи. Ранее я приводил пример таблицы, содержащей информацию об уроках. Давайте снова на нее взглянем.



В таблице имеются 6 уроков. Все 6 – разные, но для каждого урока значения одинаковых полей хранятся в таблице, а именно: tutorial_id (идентификатор урока), title (заголовок)и category (категория). Tutorial_id – первичный ключ таблицы уроков. Первичный ключ – это значение, которое уникально для каждой записи в таблице.
В таблице клиентов ниже customer_id – первичный ключ. В данном случае первичный ключ – также уникальное значение (число) для каждой записи.

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

Другие части: 4-6, 7-9, 10-13, 14-15.

Руководство по проектированию баз данных.

1. Вступление.

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

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

Японский кроссворд — NP-полная задача, как и задача коммивояжёра, укладки рюкзака и др. Когда ее решает человек, следует последовательно определять гарантированно заполненные и пустые ячейки. Одну за другой вычеркивать колонки и строки, пока не сложится весь рисунок. Как же возможно запрограммировать решение подобной задачи на языке, который официально даже не является языком программирования, не содержит циклов и переменных? SQL — язык запросов, его главная задача — выбирать строки. Вот мы и будем генерировать множество всех возможных перестановок и, словно скульптор, отсекать все лишнее.

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

Не найдя на хабре статьи, объединяющей в удобном для чтения виде информацию о методах доступа к данным, используемых СУБД Oracle, я решил совершить «пробу пера» и написать эту статью.

Общая информация

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