SQL *

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

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

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

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

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

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

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

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

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

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

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

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

Недавно наткнулся на простое и эффективное решение одной элементарной даже не проблемы, а неудобства и решил поделиться. Суть вот в чем:

Как и любой практикующий разработчик SQL или администратор БД, я сохраняю скрипты для решения повторяющихся задач, чтобы в будущем уже иметь подготовленный инструмент для быстрого выполнения. С помощью DBArtisan можно автоматически записывать все операторы SQL, которые я выполнял в течение сессии и потом использовать некоторые из них для создания и сохранения таких скриптов.  В среде DBArtisan я могу поместить в главное меню пункты для вызова наиболее часто используемых скриптов или одновременно выполнить скрипт на нескольких серверах.

Естественно, многие из таких повторяющихся задач требуют различных специализированных «кусков кода», в зависимости от решаемой задачи  или БД. Оказалось, что часто быстрее и проще в поддержке не создавать множество однотипных, «почти» совпадающих скриптов SQL или версий, а применять «блочные комментарии» для временного выключения/включения нужного фрагмента SQL и вручную управлять ими в ISQL редакторе.

Для собственного удовольствия у меня на личном компьютере крутится робот для Википедии (аккаунт1, аккаунт2, исходный код). Бот держит локальный кеш версий страниц Википедии — чтобы не ходить каждый раз на удалённый сервер за ними, а также набор специфичных данных, которые собирались последние пару лет и очень важны для работы бота. Данные собираются в базу данных под управлением Apache Derby, и, вместе с кешем, БД занимает около 50 Гб.

И вот, в один прекрасный выходной день, когда бот обрабатывал данные в 8 потоков на 4-х CPU, Abbyy Finereader распознавал 14-ый том русского биографического словаря под редакцией А. А. Половцева, а противники делали свой ход в Civilization Age of Kings… возник он — синий экран смерти. Давненько не виделись, подумал я, перезагружая компьютер. С причиной ладно — скорее всего проблемы с видеоадаптером на аппаратной почве. Вот только когда компьютер загрузился и я попробовал запустить бота ещё раз, возникло это:

ERROR XSDG2: Invalid checksum on Page Page

А прошлый бэкап, как обычно, датирован мартом месяцем…

Июль уже подходит к концу, а на «хабре» до сих пор никто не вспомнил о CTP релизе MSSQL Server 2014.
Релизу будущего года присвоен позывной «Hekaton». Его появление было заявлено еще на конференции PASS 2012 года и тогда это было только название in-memory движка OLTP. Теперь это название целого релиза с встроенной поддержкой и оптимизацией обработки транзакций непосредственно в оперативной памяти.

Итак, дорогие хабровчане, хочу представить на ваше обозрение инструкцию, как нам пришлось поднимать OLAP-сервер в нашей компании. Шаг за шагом мы пройдем по пути, который был нами проделан, начиная с установки и настройки Pentaho и заканчивая подготовкой таблиц данных и публикацией olap-куба на сервере. Естественно, многое здесь может быть сумбурным/неточным/неоптимальным, но когда нам понадобилось поднять сервер и посмотреть, сможет ли Pentaho заменить нашу самописную статистику, у нас не было и такого…

Вступление

Некоторый интерес сообщества к моей первой статье, заставил меня усиленно поработать над orm. Мне еще не все в нем нравится (где-то код не оптимизирован; где-то реализация не такая, как я хотел; не хватает проверок и возможно стабильности), но он выполняет все необходимые в текущей момент мне функции. И так, встречайте: UcaOrm!

Рассмотрим ситуацию: имеется статистическая таблица с колонками-идентификаторами и колонками-счётчиками. Требуется просуммировать счётчики по некоторому подмножеству. При этом нас не интересует, каким образом мы выбираем интересующее нас множество — про индексы и партицирование написано множество книг и статей. Будем считать, что все данные уже выбраны самым оптимальным способом и изучим, как быстрее суммировать.

Это не первое место, которое надо оптимизировать, если запрос тормозит, скорее последнее. Изложенные ниже идеи осмысленно применять когда план выполнения (explain) уже с виду идеальный и комар в нём носа не подточит, но хочется «выжать» ещё немного.

Сразу оговорюсь, это не обучающий пост и не провозглашение новой парадигмы )), скорее решение, к которому я пришел, и хочется его обсудить в широкой и честной дискуссии.
Теперь к сути, представьте, что есть некая ORM, написанная на PHP, в которой описана модель Posts, имеющая связи многие-ко-многим через промежуточные таблицы с другими моделями: Comments, Tags, Categories. Вопрос в том, каким способом лучше поднимать связанные данные, всё сразу или с отложенной загрузкой?

Вступление

Идея написать свое приложение под Android пришла мне на пятый день отдыха в солнечном Таиланде. Не буду вдаваться в подробности что именно натолкнуло меня на неё, как и что я задумал за приложение (просто статья не об этом). Однако идея крепко укоренилась и на шестой день пребывания, воспользовавшись бесплатным интернетом в отеле, на ноутбук, взятый только ради просмотра фильмов и скидывания фотографий с фотоаппарата, я закачал MySql.
Начал я, как вы наверное уже догадались, с реляционный модели.
Работа шла трудно, но через пару месяцев с моделью я закончил и окунулся в дебри разработки под Android. До этого, под мобильные платформы я писал только на .Net Compact Framework, но так как с Java был знаком не понаслышке, накидать простенькую форму с кнопками труда не составило. Объектная модель, ожидаемо, трудностей не вызвала вообще и я, радостно предвкушая как сейчас мои тестовые данные улетят куда-то в недра устройства, открыл раздел Data Storage на сайте Android Developers. Раздел Using Databases нельзя назвать исчерпывающим, однако все необходимы ссылки на API он содержит, и я принялся писать своего наследника от SQLiteOpenHelper. После пары удачных проб, разбалованный Entity Framework’ом, я понял, что и тут бы было неплохо использовать какой-нибудь orm, так как сущностей у меня набралось больше десятка. Вбив в Великом и Ужасном «android orm», первую же ссылку я получил на эту статью, и несколько полезных на StackOverflow. Набрав в общей сложности три orm’а, я приступил к экспериментам.

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

Ощутите переполняющее чувство радости того, что вы вставляете свою собственную строку в таблицу «rows»!

При разумном выборе SQL-запросов вы вскоре получите трех- или даже четырехзначное число в одном из полей своей строки! На этот случай знайте: в игре идет самая настоящая война. В новых запросах вы сможете использовать эти числа для вычитания из строк других игроков — при этом номера в вашей собственной строке станут еще выше!

«Caché предоставляет программистам свободу в выборе способа хранения и доступа к данным через объекты, SQL, либо путем прямого доступа к многомерным структурам данных. Независимо от способа доступа, все данные в базах данных Caché хранятся в многомерных массивах.»
Технологический справочник Caché

«Глобалы (глобальные хранимые переменные) – абстракция B-tree структур, используемых в MUMPS для хранения больших объемов данных.»
A Universal NoSQL Engine, Using a Tried and Tested Technology

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

В PHPStorm есть встроенная возможность подключения автодополнения имен таблиц и полей в редакторе кода, однако не все об этом знают.



Если у вас код отображается примерно так, то прошу под кат.

Цитаты из википедии (1NF):

Каждое пересечение строки и столбца содержит ровно одно значение из соответствующего домена (и больше ничего).

Одно и то же значение может быть атомарным или неатомарным в зависимости от смысла этого значения. Например, значение «4286» является

• атомарным, если его смысл — «пин-код кредитной карты» (при разбиении на части или переупорядочивании смысл теряется)
• неатомарным, если его смысл — «набор цифр» (при разбиении на части или переупорядочивании смысл не теряется)

В данной статье будут рассмотрены стандартные способы ускорения SQL-запросов по следующим типам полей: строка, дата, простой список (в формате $LB), коллекции-cписки и коллекции-массивы.

SQL инъекции, подделка межсайтовых запросов, поврежденный XML… Страшные, страшные вещи, от которых мы все бы хотели защититься, да вот только знать бы почему это все происходит. Эта статья объясняет фундаментальное понятие, стоящее за всем этим: строки и обработка строк внутри строк.

Связь многие ко многим и MS SQL Server Analysis Services

Данный пост я хочу посвятить такой, как выяснилось, несложной проблеме, как обработка OLAP-кубом связей многие-ко-многим с помощью MS SQL Server Analysis Services.

Эта статья — частичный перевод одной интересной статьи с sqlite.org, в которой подробно рассматривается реализация транзакций в SQLite. На самом деле я очень редко работаю с SQLite, но тем не менее мне очень понравилось это чтиво. Поэтому если хотите просто развить кругозор — будет интересно почитать. Первые две секции не включены в перевод, так как там нет ничего интересного, да и мне лень их набивать (пост и так огромный).

3.0 Однофайловый коммит

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

3.1 Начальное состояние

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

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