LXF78:PHP
|
|
|
Часть 3. Живите легко! Сократите объемы SQL-кода и повысьте производительность. Пол Хадсон представляет себе последствия...
Хотя структура вашей БД, скорее всего, меняется не каждые пять минут, существует множество способов подать одни и те же данные. В сложной системе доступ ко всей базе данных (и записи в нее) может быть предоставлен только администратору. Простым смертным приходится довольствоваться лишь чтением какой-либо таблицы или даже ее части.
Если вы не можете найти реальный пример, подумайте об отделе кадров. Его сотрудники должны легко находить ваше имя, должность и так далее. Но ваш домашний телефон или реквизиты банковского счета – это не их дело. Такая информация является личной, и доступ к ней предоставляется только старшему персоналу. Можно также представить себе еще один уровень доступа, которым обладают все сотрудники компании, позволяющий, например, узнать имя, должность и внутренний номер телефона.
Так что, даже если в вашей таблице всего одна строка данных, вы имеете три разных способа их подачи: полный доступ, ограниченный доступ и малый доступ. MySQL 5.0 предоставляет средства для программирования этих разграничений в самой базе данных, так что каждый пользователь будет иметь доступ лишь к одному представлению.
- Представление начинается
Думайте о представлении как о фиксированном SQL-запросе, который определяет, что может увидеть пользователь в таблице. В этом учебнике мы будем работать с таблицей employees (сотрудники):
CREATE TABLE employees (ID INT NOT NULL AUTO_ INCREMENT PRIMARY KEY, FirstName VARCHAR(100), LastName VARCHAR(100), Age INT, SSN VARCHAR(30), IBAN VARCHAR(100));
В базе данных хранится имя и возраст человека (вполне безобидная информация), номер социального страхования (SSN) и International Bank Account Number (IBAN) – определенно приватные данные. Мы хотим, чтобы другие сотрудники видели только имя и возраст. Отделу кадров понадобится и SSN, а бухгалтерии – еще и IBAN. Чтобы сделать это, нам потребуются три представления (view): ‘restricted’ (ограниченный), ‘HR’ (отдел кадров) и ‘accounts’ (бухгалтерия).
Представления создаются при помощи оператора CREATE VIEW, за которым следует стандартный запрос SELECT:
CREATE VIEW restricted AS SELECT FirstName, LastName, Age FROM employees;
Представления выглядят как виртуальные таблицы: они не занимаются реальным копированием данных из employees и не появляются в выводе команды SHOW TABLES. Чтобы увидеть представления, необходимо выполнить SHOW FULL TABLES. Ее вывод будет разделен на две колонки. Строка BASE TABLE обозначает стандартную таблицу MySQL, VIEW соответствует представлению.
Однако в смысле выполнения запросов представления ведут себя как самые обычные таблицы. Например, чтобы прочитать все строки таблицы employees от имени «ограниченного» пользователя, выполните: SELECT * FROM restricted;. Вы также можете добавить некоторые условия, например:
SELECT * FROM restricted WHERE Age > 40; SELECT * FROM restricted LIMIT 3; SELECT MAX(Age) FROM restricted;
Последняя строка представляет особенный интерес, поскольку в ней мы «фильтруем фильтр»: представление restricted является подмножеством employees, а мы выделяем из него одну-единственную колонку. По сути, это эквивалентно:
SELECT MAX(Age) FROM (SELECT * FROM employees);
Но что мешает пользователю прочитать данные из employees, а не из restricted? Пока – ничего. Пока...
- Ограниченное представление
MySQL весьма гибок в отношении пользовательских прав на доступ к таблицам. Эта же гибкость сохраняется и для представлений, а значит, мы можем позволить людям читать только одно представление, или же читать одно, а писать – другое. Прежде всего, нам потребуется пользовательская учетная запись, так что подключитесь к MySQL как root и наберите:
USE MySQL INSERT INTO user (Host, User, Password, ssl_cipher, x509_issuer, x509_subject) VALUES (‘localhost’, ‘peon’, PASSWORD(‘p30n’), ‘’, ‘’, ‘’);
В зависимости от версии MySQL и режима работы (strict или не-strict), вам может потребоваться поиграть с ssl_cipher. В любом случае, теперь у нас есть новый пользователь, которому нужно предоставить право на чтение представления:
GRANT SELECT ON your_db_name.restricted TO peon@localhost; FLUSH PRIVILEGES;
В итоге пользователь peon получит доступ только к таблице или представлению ‘restricted’ в БД your_db_name. Теперь отсоединитесь от MySQL и вновь зайдите как новый пользователь. Попробуйте выполнить следующее:
SELECT * FROM employees;
Вы получите сообщение об ошибке: «SELECT command denied to user ‘peon’@’localhost’ for table ‘employees’». На самом деле, выполнив SHOW TABLES, вы увидите только представление ‘restricted’ – MySQL делает вид, что никакой таблицы employees не существует. Чтобы предоставить пользователю peon чуть больше прав, опять зайдите как root и выполните:
GRANT SELECT,INSERT,DELETE,UPDATE ON test.restricted TO peon@localhost; FLUSH PRIVILEGES;
Это позволит пользователю манипулировать данными таблицы, не боясь навредить ей самой. Производные Производные колонки (derived columns) позволяют хранить в вашем представлении синтетические данные. Обычная колонка просто читает значение поля таблицы и помещает его в представление, тогда как производная колонка читает некоторое поле, выполняет преобразование и лишь затем помещает данные в представление. Умно!
Один из моих любимых принципов БД – атомарность. Я предпочитаю хранить все поля по отдельности, независимо друг от друга, и вычислять нужные величины «на лету» по мере надобности. Например, если меня интересует число страниц, которые пользователь посетил в местной интранет-сети, я сохраню запись о каждой странице, а также сопутствующую информацию (дату, время, ...) в отдельности, а затем запущу запрос, который посчитает число строк – это и будет количество страниц, посещенных данным пользователем.
Вашему боссу это может не понравиться – ведь он-то хочет видеть в базе данных одно число, а не отдельные запросы! С представлениями и производными колонками мы можем легко синтезировать эти данные и предоставить боссу на блюдечке.
- Вычисляемые колонки
В качестве иллюстрации, рассмотрим следующую таблицу для несущес- твующего файла журнала:
CREATE TABLE page_requests (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, UserID INT, PageID INT, RequestTime INT);
После занесения в нее кое-каких данных, можно создать «боссово представление» следующим образом:
CREATE VIEW total_requests AS SELECT UserID, Count(*) AS RequestCount FROM page_requests GROUP BY UserID;
Здесь мы создаем производную колонку, основываясь на числе запросов, которые сделал пользователь. Вы не можете обновить ее или удалить какую-либо строку на основе ее данных, поскольку на самом деле это не колонка, а вычисление, которое MySQL производит по ходу работы.
Это – самый распространенный способ использования производных колонок. Например, отдел кадров может не интересовать, когда именно человек был в отпуске, но его сотрудникам важно знать, сколько у него осталось неиспользованных дней. Производные колонки также можно использовать для замещения значений колонки нужными нам величинами. Например, мы использовали представления, чтобы ограничить доступ к SSN и IBAN. Возможно, лучшим решением было бы поместить в этих колонках служебное сообщение, указывающее, к кому следует обратиться за теми или иными сведениями. Вот соответствующий код:
CREATE VIEW restricted_contact AS SELECT FirstName, LastName, Age, «Обратитесь к Иванову из отдела кадров» AS SSN, «Обратитесь к Петрову из бухгалтерии» AS IBAN FROM employees;
- Вид сверху
Представления – прекрасное средство для обеспечения безопасности, но они могут также оказаться полезными при работе со сложными SQL-запросами, оперирующими с несколькими источниками данных. Вся «соль» в том, что пользователь может работать с представлением как с таблицей, свободно выбирая колонки и способ упорядочения данных. Длинные SQL-запросы долго пересылаются, обрабатываются и служат прекрасной почвой для разведения различных ошибок. Представления позволяют инкапсулировать эти сложные запросы в простые конструкции SELECT, которыми пользователь может распоряжаться по своему разумению.
Допустим, у нас есть две таблицы: одна – для сотрудников и вторая – для записей о просмотренных ими страницах. Мы можем объединить их при помощи LEFT JOIN:
SELECT employees.*, count(page_requests.ID) AS PageRequests FROM employees LEFT JOIN page_requests ON employees.ID = page_requests.UserID GROUP BY page_requests.UserID;
Это даст нам всю информацию о человеке, включая число посещенных страниц. Правда, запрос получился не такой уж короткий, и многие наверняка споткнутся, дойдя до LEFT JOIN. Используя представления, вы можете сократить его до SELECT * FROM employees_complete и забыть об этих проблемах.
Все, что мы видели до сих пор, позволяет сократить время разработки и снизить число ошибок в коде, однако, представления обладают еще двумя преимуществами: во-первых, они позволяют создать одну большую «супертаблицу» и подпредставления (subview), которые выбирают данные из обширного источника. Во-вторых, представления – это, по сути, функции, которые можно изменить в любое время.
Как я уже неоднократно говорил, в большинстве случаев представление действует как таблица. Подпредставление – это представление, созданное на базе другого представления. Вы можете создать «супер-представление», которое объединяет четыре таблицы, а затем ввести десяток подпредставлений, которые выбирают некоторые колонки, не заставляя вас писать сложные запросы. Синтаксис определения подпредставления ничем не отличается от создания представления на базе обычной таблицы, так что обойдемся без примера. Следуя моему совету, будьте предельно бдительны: легко создать суперпредставление, работающее значительное время, и подпредставления, которым нужна лишь ничтожная часть его данных. Это очень неэкономный подход к использованию ресурсов, так что создавайте подпредставление только если оно содержит столько же таблиц, что и суперпредставление.
Наконец, если рассматривать представления как функции, вы выходите на совершенно новый уровень контроля доступа к данным. Если для получения необходимой информации используются только представления, вы можете изменить их в любое время и добавить новую колонку или подправить алгоритм. Все пользователи увидят результат нового запроса – без какой-либо правки кода.
Таким образом, представления сокращают время разработки, создают меньше ошибок, добавляют гибкости и помогают убираться по воскресеньям. Ну, насчет уборки я, конечно, загнул, но согласитесь: представления – это круто, и нужно быть сумасшедшим, чтобы не использовать их в своей работе.
...Советы
• Если какие-то поля в вашей таблице вычисляются триггером, зачем позволять пользователю устанавливать их значения напрямую? Создайте представления, исключите их из него и пусть триггер делает свое дело.
• Кстати о триггерах – вы не можете использовать их с представлениями, поскольку в них на самом деле не хранятся данные. Устанавливайте триггер на родительскую таблицу.
• И таблицы, и представления принадлежат базе данных, а значит, вы не можете давать им одинаковые имена.
• Чтобы изменить запрос, соответствующий представлению, наберите ту же конструкцию, что и для его создания, но замените CREATE VIEW на CREATE OR REPLACE VIEW.
• Чтобы удалить представление, наберите: DROP VIEW your_view_name;
