Object Explorer в MS SQL Server, как он должен быть

Давно собирался написать эту статью, о том каким должен быть Object Explorer в SQL Server.

В движок системы добавляются все новые и новые фишки, улучшается поддержка администрирования, скорость работы и так далее. Даже появляются сниппеты кода, а так же автодополнение в базовой версии, казалось бы, чего волноваться? Однако при постоянной работе с SQL Server, и когда хоткеи ReSharper’a уже хочется привнести в каждую программу, замечаешь насколько неудобная работа с объектами базы.

Все хорошо и красиво, когда в базе находится наверно не более 20 таблиц, которые можно объять одним взглядом, однако если таблиц от 50 и больше, а так же требуется работать с несколькими окружениями – работа превращается в ад. Очень легко выполнить не тот код, работая не с тем соединением. Да и просто поиск таблиц вызывает затруднения, так как обычно помнишь примерное название, а не дословное. Впрочем подобная проблема касается любых объектов в SQL Server.

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

01

Что это за база? Какой это сервер? Сколько тут еще таблиц?

Подробнее

SQL Tips & Tricks

Сегодня в программе несколько интересных наблюдений и заметок по поводу SQL Server. Советы и заметки ниже применимы к любым актуальным версиям SQL Server (таковыми считаются все версии от 2005 и новее). Итак:

  • Получение изменившихся данных без триггеров
  • Select vs Set для установления переменных
  • Забытое дополнение для TOP

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

Получение изменений без триггеров

Действительно большинство пользователей MS SQL Server думают, что триггеры это единственное место где можно использовать виртуальные таблицы Inserted и Deleted, с помощью которых можно получить данные об измененных данных в результате выполнения запросов с Insert, Update или Delete. Но, как вы уже наверно догадались, это не так.

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

Подробнее

Безопасные пароли с SqlCredential

Наткнулся на описание новой фичи, которая входит в .Net 4.5, которая должна упростить работу с безопасным хранением паролей к базе данных. Раньше это был, на мой взгляд, какой-то не очень удобный способ с файлами конфигурации, какими-то специальными преобразованиями и чем-то еще. Сейчас же появилась возможность создания безопасных данных о подключении к базе данных, которые не утекут при снятии дампа памяти целенаправленно или случайно в результате аварийной остановки программы, да мало ли какие случаи бывают. Честно сказать, в моей практике доступ к БД в основном осуществлялся с помощью Active Directory, что существенно упрощало работу. Однако такое тоже было не всегда и меня внутренне грыз червячок сомнений о несколько легкомысленном отношении к строкам соединений к БД. Сейчас появился достаточно простой и легкий способ защитить данные о соединении с базой с помощью класса SqlCredential. Теперь стало возможно задать логин\пароль с помощью свойства Credential у класса SqlConnection.

Лучше всего сразу это показать на примере, мне кажется, это будет лучше тысячи слов =)

Подробнее

GUID в роли быстрого первичного ключа для разных БД

Эта статья раскрывает подходы по использованию GUID в роли первичного ключа или кластерного индекса при этом описываются способы обхода основных неудобств связанных с GUID, на основании COMB модели для последовательных значений GUID разработанных в статье Джимми Нильсона. Большинство реализаций являются специфичными для MS SQL Server, в то время как основная идея похоже используется во многих библиотеках и фреймворках(включая NHibernate). В статье предпринимается попытка использовать общий гибкий подход для возможности использования COMB в других популярных базах данных: Oracle, PostgreSQL, MySQL. Так же мы рассмотрим некоторые особенности .net в свете наших задач.

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

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

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

Одной из альтернатив становится использование GUID в роли первичного ключа. GUID — это 128-битное значение, которое поддерживает разумно-гарантированную уникальность независимо от времени и пространства. Стандарт для создания GUID описан в документе RFC 4122, но большинство современных алгоритмов по созданию GUID используют либо очень большое случайное число, либо формируют значение исходя из некоторой случайной информации комбинированной с чем-то локальным (ранее такое формирование было у MS, они создавали GUID на основе MAC адреса, но в последствии это было не безопасно и так же нарушало privacy).

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

Так в чем же проблема?

Подробнее

Фасад доступа к данным — Оптимизация, Заключение

Построение запросов

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

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

Linq2Sql позволяет гибко работать с запросами в стиле fluent interface переводя их в sql запросы. Было бы упущением не воспользоваться этой возможностью в полной мере. Особенно учитывая то, что мы уже работаем с логическими таблицами L2S, которые реализуют интерфейс IQueryable. Да, когда мы в адаптере чтения обращаемся к контексту с методом GetTable<TData>(), мы получаем объект, который можно донастраивать с помощью методов Where(), разбирая пользовательские спецификации. Собственно в этом и состоит идея.

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

Модифицировали запрос, если надо, и отдали его обратно. Отлично, теперь рассмотрим как это может быть реализовано.

Подробнее

Фасад доступа к данным — Адаптер записи, Трансляторы

Адаптеры записи

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

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

IWriteAdapter

В интерфейс вынесены метод и свойство необходимые для работы в классе фасада, а именно Save() и Sequence.

Теперь можно переходить к рассмотрению «мяса», основной работы по сохранению данных.

Подробнее

Фасад доступа к данным — Фасад и Адаптер чтения

Фасад

Теория

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

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

Про Unit of Work (UoW) рассказано было ранее, паттерн Specification так же уже рассматривался, так что на этих типах данных останавливаться не будем.

Общий принцип работы такой:

  • Получаем фасад;
  • Вызываем метод получения доменных данных, в который передаем ограничивающие условия, которым должны соответствовать элементы;
  • Что-то делаем с данными, работаем с UoW;
  • Вызываем метод сохранения UoW.

Фасад доступа к данным — Подготовка

На основе LINQ to SQL

Сложность по шкале Microsoft 300-400

Сразу признаюсь, что я люблю Linq2Sql и не люблю Entity Framework. Люблю компактные API и не люблю развесистые классы с кучей методов служебных выставленных наружу. Может по этим причинам, а может быть по каким-то еще, но я использую схему доступа к данным, которую собираюсь описать ниже, уже несколько лет. По большей части меня она радует и выполняет свои задачи хорошо для большинства заданий возникающих на работе.

Вводная часть

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

Шаблон Facade (Фасад) — Шаблон проектирования, позволяющий скрыть сложность системы путем сведения всех возможных внешних вызовов к одному объекту, делегирующему их соответствующим объектам системы.

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

Итогом работы будет являться использование фасада в таком духе:

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

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

Подробнее

Целые vs GUID vs Естественные и Суррогатные ключи

 

В мире баз данных есть несколько аргументов, которые провоцируют очень сложный выбор правильного первичного ключа из вариантов представленных в заголовке. Что будет лучше, выбрать естественный или суррогатный ключ? Если мы выбираем суррогатный ключ то, что выбрать: целочисленный ряд или же GUID? В прошлом мне пришлось с жаром отстаивать  все точки зрения, так что могу рассмотреть плюсы и минусы каждого подхода.

Естественный vs Суррогатный ключи

Джо Целко (Joe Celko) утверждает что мы должны использовать естественные ключи, созданные из атрибутов записи, а не заниматься присвоением произвольных значений записям в таблицах. Я согласен с ним. Но! В то же время мы должны стараться хранить ключи легкими/маленькими, вместо того, чтобы создавать массивные составные ключи, чьи значения в свою очередь должны будут храниться в других таблицах для возможности соединения таблиц, поддержания их целостности. Как я выбираю какой способ использовать? Легко, я использую оба.

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

Подробнее

Dapper – micro-ORM — II

Продвинутые возможности

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

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

С помощью Dapper можно осуществлять пакетную вставку данных передав  список данных. Т.е. у нас есть список людей для внесения в базу данных, и с помощью единственной команды можно это провернуть. Лучше сразу на примере показать:

Создаем несколько экземпляров класса Person, и передаем список в качестве параметра в команду Execute. Потом можно визуально проверить результат.

В качестве параметра для пакетной обработки может выступать любая коллекция, которая реализует интерфейс IEnumerable<T>.

Упс!

Ничего же не менялось в коде, и все переменные указаны верно, в чем же дело?

Подробнее