SQL — Урок 7

Базы данных SQL — Урок 7

Помечено: ,

  • В этой теме 0 ответов, 1 участник, последнее обновление 1 год, 7 месяцев назад сделано Васильев Владимир Сергеевич.
Просмотр 0 веток ответов
  • Автор
    Сообщения
    • #5202
      @admin
      StudLance.ru

      Транзакции, конкурирующие запросы

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

      • атомарность — выполнение или не выполнение всех DML команд входящих в тразакцию;
      • целостность БД — завершение транзакции не должно нарушать целостность БД;
      • изоляция — можно отображать либо исходные данные, которые были до начала транзакции, либо новые данные после выполнения транзакции;
      • сохранность данных — если пользователю пришло подтверждение выполнения транзакции, то его изменения не будут отменены по каким-либо причинам.

      В стандарте предусмотрены следующие команды управления транзакциями:

      • START TRANSACTION — явное начало транзакции. Команда не поддерживается в Oracle. В MySQL и PostgreSQL можно использовать синоним begin (не путать с блоковым оператором begin, после которого нет разделителя). Если начало транзакции явно не указано, то PostgreSQL считает каждую DML команду отдельной транзакцией. В Oracle транзакции следуют одна за другой. То есть первая DML команда открывает транзакцию, следующие команды становятся частью этой транзакции, пока не будет вызвана команда commit или rollback. Если режим автоподтверждения (autocommit) включен, то MySQL работает также как PostgreSQL, иначе как Oracle;
      • COMMIT — завершить транзакцию, применяя все сделанные изменения;
      • ROLLBACK — завершить транзакцию, отменяя все сделанные изменения. Если точка отката не указана, то отменяется вся текущая транзакция;
      • SAVEPOINT — сохранить точку отката;
      • RELEASE SAVEPOINT — уничтожить точку отката, что позволяет освободить часть ресурсов до завершения транзакции;
      • SET TRANSACTION — устанавливает характеристики текущей транзакции. Если транзакция не начата явно, то эта команда игнорируется в PostgreSQL.

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

      BEGIN
         ...
         SAVEPOINT pt1; -- сохранили точку отката
         ...
      EXCEPTION
         WHEN VALUE_ERROR  THEN  -- неправильное значение 
            -- SAVEPOINT можно опустить
            ROLLBACK TO SAVEPOINT pt1;
         WHEN OTHERS THEN  -- другие исключения
         ...
      END;
      

      Команда select … for update, блокирует записи таблицы от изменений другими пользователями.
      При этом нельзя использовать другие конструкции как distinct, group by.

      -- блокируем запись для обновления
      SELECT * FROM tbl1
          WHERE id=2
          FOR UPDATE;	
      
      -- обновляем записи через UPDATE
      ...
      
      -- завершаем транзакцию, блокировка снимается	
      COMMIT; 
      

      Для блокировки нескольких таблиц в различных режимах используется команда lock table. Блокировка снимается при завершении текущей транзакции.

      -- блокируем таблицу в режиме SHARE
      -- запросы данных разрешены
      -- изменение таблицы запрещено
      -- блокировать таблицу в режиме EXCLUSIVE запрещено
      LOCK TABLE tbl2 
         IN SHARE MODE;
      
      -- блокируем таблицу в режиме EXCLUSIVE
      -- и не ждем, если другой пользователь уже заблокировал таблицу
      LOCK TABLE tbl1
         IN EXCLUSIVE MODE 
         NOWAIT; 
      

      В MySQL поддержка транзакций зависит от используемого способа хранения таблиц. Так для таблиц на движке InnoDB возможны транзакции, а для MyISAM нет. В последнем случае отсутствие одновременного обновления одних и тех же данных разными пользователями гарантируется самой СУБД. Невозможность отката, означает, необходимость самостоятельной предварительной проверки на ошибки перед внесением изменений. Такой подход увеличивает скорость работы от трех до пяти раз за счет уменьшения использования памяти, дискового пространства и процессора.

      Схема SQL

      Объект схема позволяет логически сгруппировать множество других объектов. Доступ к объектам схемы осуществляется через точку. По стандарту в команду создания схемы позволяется вкладывать дополнительные команды создающие различные объекты схемы и раздающие права на эти объекты. А также владельцем всех объектов схемы должен являться владелец схемы.

      PostgreSQL

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

      Во-вторых, в текущей версии СУБД порядок вложенных команд создания объектов схемы может иметь значение. В качестве вложенных команд могут быть команды создания таблиц, представлений, индексов, последовательностей, триггеров и команды раздачи привилегий. Если перед именем объекта имя схемы не указывается, то используется схема public.

      -- создание схемы с объектами
      -- владельцем схемы и объектов объявляется 
      -- пользователь root
      CREATE SCHEMA newchema AUTHORIZATION root
        create table tblname (
          id integer, 
          str varchar(45),      
          constraint pk_tblname primary key (id)
          )
        create view v_tblname 
          as select * from tblname
        grant select on tblname to public
        grant select on tblname to public
      ;
      
      -- переименование схемы
      ALTER SCHEMA newchema RENAME TO newnama2;
      
      -- смена владельца схемы
      ALTER SCHEMA root OWNER TO anyuser;
      
      -- уничтожение схемы если в ней нет объектов
      DROP SCHEMA IF EXISTS newchema; 
      
      -- уничтожение схемы и всех ее объектов
      DROP SCHEMA IF EXISTS newchema CASCADE; 
      

      Oracle

      В Oracle для каждого пользователя автоматически создается одноименная схема с полным доступом. А команда CREATE SCHEME служит лишь для наполнения схемы множеством таблиц, представлений и правами доступами к ним другим пользователям. Ниже приведен пример, как пользователь root за одну команду создает одну таблицу, одно представление и открывает к ним общий доступ.

      CREATE SCHEMA AUTHORIZATION root
        CREATE TABLE tblname (
          id integer, 
          str varchar(45),  
          dt date,
          constraint pk_tblname primary key (id)
          )
        CREATE VIEW v_tblname 
          as select * from tblname
        GRANT select ON tblname TO public
        GRANT select ON tblname TO public
      ; 
      

      MySQL

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

      -- создание схемы
      CREATE  SCHEMA IF NOT EXISTS newchema;
      
      -- делаем схему newchema
      -- схемой по умолчанию 
      USE newchema;
      
      -- уничтожение схемы
      DROP SCHEMA IF EXISTS newchema;
      

      Управление аккаунтами SQL

      пользователи

      Аккаунт или учетная запись характеризуется пользователем и правами доступа (привилегиями) назначенных пользователю. Обычно аккаунты защищены паролем.

      -- создание пользователя
      -- Oracle
      CREATE USER username
        IDENTIFIED BY psw
      
      -- MySQL
      CREATE USER username
        IDENTIFIED BY 'pswd';
      
      -- PostgreSQL  
      CREATE USER username
        PASSWORD 'pswd';  
        
      -- уничтожения пользователя
      DROP USER username;  
      

      права доступа

      Права доступа обеспечивают уровень безопасности как для сокрытия данных от посторонних лиц, так и безопасность всей структуры БД от не преднамеренного уничтожения объектов. Команда добавления привелегий GRANT имеет три составляющие: имя привелегии, объект, на который даются права, и кому даются права — пользователь или роль.

      Чтобы задать все доступные права на указанный объект, в стандарте предусмотрена конструкция
      ALL PRIVILEGES. Конструкция WITH GRANT OPTION дает разрешение пользователю
      самому назначать права. Чтобы сделать некоторые права на указанные объекты общедоступными
      в Oracle и PostgreSQL в качестве имени пользователя нужно указать public.

      -- разрешаем пользователю username
      -- вставлять, обновлять, вставлять и удалять записи
      -- из таблицы tblname в текущей схеме
      GRANT select, update, insert, delete 
         ON tblname 
         TO username;
      
      -- разрешаем пользователю username 
      -- выполнять процедуру proc и функцию func
      
      -- Oracle
      GRANT execute ON proc TO username;
      GRANT execute ON func TO username;
      
      -- MySQL
      GRANT execute ON PROCEDURE proc TO username;
      GRANT execute ON FUNCTION func TO username;
      
      -- PostgreSQL, если у процедуры или у функции
      -- есть аргументы, то их надо указать 
      GRANT EXECUTE
          ON FUNCTION proc()
          TO username;
      
      -- ---------------------
      GRANT ALL PRIVILEGES 
         ON tblname 
         TO username
         WITH GRANT OPTION;
      
      -- Oracle, PostgreSQL
      GRANT select 
         ON tblname 
         TO public;
      

      права доступа MySQL

      В MySQL схемы и пользователи независимы, поэтому при назначении прав необходимо указывать конкретную схему (имя_схемы.*) или распространять право на все схемы сразу (*.*). Последний вариант в MySQL называется глобальным уровнем назначением прав.

      По сравнению с Oracle список прав не так велик из-за отсутствия некоторых типов объектов и объединения использования нескольких команд в одну привилегию. Ниже приведен список основных прав:

      • ALL PRIVILEGES — все права на указанный объект за исключением назначения прав на этот объект;
      • CREATE — право на создание таблицы;
      • ALTER — право на изменение таблицы;
      • DROP — право на уничтожение таблицы;
      • LOCK TABLES — право на блокировку таблицы;
      • CREATE TEMPORARY TABLES — право на создание временных таблиц;
      • CREATE ROUTINE — право на создание хранимых процедур и функций;
      • ALTER ROUTINE — право на изменение или уничтожение хранимых процедур и функций;
      • CREATE VIEW — право на создание представления;
      • TRIGGER — право на создание и уничтожение триггеров;
      • INDEX — права на создание и уничтожение индексов;
      • EXECUTE — право на выполнения хранимых процедур и функций;
      • EVENT — право на создание событий;
      • CREATE USER — права на создание, уничтожение, переименование пользователя и снятия все прав. Назначается только на глобальном уровне;
      • SELECT — право на выборку;
      • DELETE — право на удаление;
      • INSERT — право на вставку;
      • UPDATE — право на обновление;
      • FILE — право на использование команд SELECT … INTO OUTFILE и LOAD DATA INFILE;
      • PROCESS — право на просмотр всех процессов командой SHOW PROCESSLIST;
      • SHOW DATABASES — право на просмотр списка схем;
      • SHOW VIEW — право на просмотр списка представлений;
      • SHUTDOWN — право на закрытие.

      -- право на создание таблицы в схеме test
      grant create on test.* to username;
      -- право на уничтожение таблицы в схеме test
      grant create on test.* to username;
      
      -- право на создание, уничтожение, переименование
      -- пользователя и снятие привилегий
      GRANT create user ON *.* TO username 
      -- с правом назначать привилегии
         WITH GRANT OPTION; 
      

      права доступа PostgreSQL

      В PostgreSQL права задаются только на существующие объекты следующих типов: table (таблица), view (представление), sequence (последовательность), database (база данных), function (функция), procedural language (процедурный язык), schema (схема) и tablespace (табличное пространство). За исключение таблиц перед именами объектов обязательно указывается их тип. Ниже приведен список привилегий:

      • ALL PRIVILEGES — все привилегии на указанный объект;
      • CREATE — право на создание. Для базы данных это создание
        схем внутри ее. Для схем это создание новых объектов внутри ее. Для
        табличного пространства это создание таблиц, индексов и временных файлов
        внутри него;
      • REFERENCES — право на создание внешний ключей;
      • TRIGGER — право создания триггера на указанную таблицу;
      • CONNECT — право на соединение с указанной базой данных;
      • TEMPORARY, TEMP — право на создание временных таблиц;
      • EXECUTE — право на выполнение функций;
      • USAGE — право на использование указанного языка для
        написания функций. Для схемы это доступ к объектам внутри ее. Для
        последовательности это право использовать функции currval и nextval;
      • SELECT — право на выборку;
      • DELETE — право на удаление;
      • INSERT — право на вставку;
      • UPDATE — право на обновление.

      -- все права на указанные базы данных
      GRANT ALL PRIVILEGES 
          ON DATABASE dbname1, dbname2
          TO username 
          WITH GRANT OPTION;
      
      -- право на выбор из двух представлений
      GRANT select
          ON VIEW view_name1, view_name2
          TO public;
      

      права доступа Oracle

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

      Ключевое слово ANY указывает, что привилегия распространяется на все схемы, иначе только на текущую (из которой вызывается команда GRANT).

      -- право на создание таблицы 
      GRANT create table TO username; 
      
      -- право на создание любых таблиц
      GRANT create any table TO username; 
      
      -- право на создание триггера 
      GRANT create trigger TO username;
      
      -- право на уничтожение любой последовательности
      GRANT drop any sequence TO username;
      
      -- право на изменение любой таблицы
      GRANT alter any table TO username;
      
      -- право на соединение к БД
      GRANT create session TO username;  
      

      удаление прав доступа

      Удаление прав делается командой REVOKE.

      -- удаление права выборки из таблицы
      -- tblname пользователю username
      REVOKE select ON tblname FROM username;
      
      -- PostgreSQL
      REVOKE GRANT OPTION FOR ALL PRIVILEGES 
          ON DATABASE dbname1, dbname2
          FROM username;
          
      -- MySQL
      REVOKE ALL PRIVILEGES, GRANT OPTION FROM username;
      

      роли

      Для удобства управления привилегиями используются роли. Им можно назначать права так же, как и пользователям или использовать вместо имени привилегии в командах
      GRANT и REVOKE.

      роли PostgreSQL

      В PostgreSQL роль и пользователь являются одним типом объекта. Различие в командах CREATE USER и CREATE ROLE, заключается в том, что в первом случае по умолчанию определяется имя с возможностью соединения (параметр LOGIN). Во втором случае соединение запрещено (параметр NOLOGIN). Группы пользователей, существовавшие в предыдущих версиях, теперь также слились с ролями. Ниже приведен список параметров определяющих роль:

      • SUPERUSER, NOSUPERUSER — определяют является ли новая роль суперпользователем —
        пользователем не имеющего ограничений внутри базы;
      • CREATEDB, NOCREATEDB — может ли новая роль создавать базы данных;
      • CREATEROLE, NOCREATEROLE — может ли новая роль создавать новые роли;
      • LOGIN, NOLOGIN — может ли новая роль использоваться для соединения к БД;
      • CONNECTION LIMIT n — если роли доступно соединение к БД, то n указывает предел этих
        соединений. По умолчанию значение -1, означающее отсутствие предела;
      • PASSWORD psw — определяет пароль для соединения;
      • ENCRYPTED, UNENCRYPTED — шифровать или нет пароль в системном каталоге;
      • VALID UNTIL ‘временная отметка’ — определяет действие пароля до указанной
        временной отметки;
      • IN ROLE lst — определяет список ролей lst, добавляемых к новой;
      • ROLE lst — определяет список ролей, которые станут членами новой группы;
      • ADMIN lst — аналогично предыдущему плюс указанные роли сами могут добавлять другие роли в эту группу.

      -- создание новой роли
      CREATE ROLE rolename WITH  
         SUPERUSER 
         CREATEDB 
         NOLOGIN;
      
      -- добавление права роли
      GRANT select ON tblname TO rolename;
      
      -- добавление роли пользователю
      GRANT rolename TO username;
      
      -- удаляем все права на таблицу
      -- нельзя уничтожить роль, если она используется
      -- для доступа к объекту
      REVOKE ALL PRIVILEGES ON tblname FROM  rolename;
      
      -- уничтожение роли
      DROP ROLE IF EXISTS rolename;
      

      роли Oracle

      Синтаксис создания ролей в Oracle также схож с синтаксисом создания пользователей.

      -- создание роли
      CREATE ROLE rolename;
      
      -- роль с паролем
      CREATE ROLE rolename
         IDENTIFIED BY pswd;
      
      -- добавление права роли
      GRANT select ON tblname TO rolename;
      
      -- добавление роли пользователю
      GRANT rolename TO username;
      
      -- уничтожение роли
      DROP ROLE rolename;
      

      роли MySQL

      MySQL пока не поддерживает ролей.

      Файловый вывод/ввод

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

      -- таблица цветов
      create table colors(
      id integer,
      cname varchar(45), 
      constraint pk_colors primary key (id)
      );
      
      insert into colors values(1,'красный');
      insert into colors values(2,'синий');
      insert into colors values(3,'зеленый');
      insert into colors values(4,'белый');
      insert into colors values(5,'черный');
      commit;
      

      Oracle

      В Oracle вывод в файл реализуется с помощью команды SQL plus spool. Она служит как для начала вывода в файл, так и для остановки. По умолчанию расширение файла lst.

      -- открываем вывод в файл
      spool c:\\myfile; 
      
      -- выборка
      select t.id || ' ' || t.cname from colors t;
      
      -- закрываем вывод в файл
      spool off; 
      

      Так как это не SQL команда, то ее нельзя использовать внутри PL/SQL блока, но можно поместить блок между этими командами. По этой же причине, чтобы выполнить этот пример в PL/SQL developer, нужно открыть Command window.

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

      -- открываем вывод в файл
      spool c:\myfile; 
      
      -- выборка
      select t.id || ' ' || t.cname from colors t;
      
      -- закрываем вывод файл и распечатываем
      spool out; 
      

      MySQL

      В MySQL данная задача реализована аналогично сохранению выборки в списке переменных в
      команде select.

      select *
      into outfile 'c:/myfile.lst' -- указываем файл
      fields terminated by ',' -- разделитель полей
      enclosed by '"' -- заключить каждое поле между "
      lines terminated by '\\n' -- разделитель строки
      from colors;
      

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

      Команда LOAD DATA INFILE является обратной и позволяет загрузить данные из файла в таблицу.

      Например пусть есть файл myfile.lst, со следующим содержимым в обычной для Windows русской кодировке.

      "50", "светло-коричневый"
      "51", "темно-коричневый"

      Тогда загрузить данные можно следующей командой.

      load data  infile 'c:/myfile.lst' 
        ignore -- пропускаем если данные уже в таблице
        into table colors
        character set cp1251 -- кодировка исходного файла
        fields
        terminated by ',' -- разделитель полей
        enclosed by '"' -- поле заключено между "
        lines terminated by '\\n'; -- разделитель строки
      

      Если таблица colors использует другую кодировку, например utf-8, данные преобразуются корректно.

      PostgreSQL

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

      -- простой вывод данных в файл
      copy (select * from colors) to 'c:/myfile.lst'; 
      
      -- предположим, база настроена на кодировку utf-8,
      -- тогда и строки сохранятся в файл в utf-8
      copy 
        (
        select '"50", "светло-коричневый"'
          union
        select '"51", "темно-коричневый"'
        ) to 'c:/myfile.lst';
      
      -- а теперь загрузим данные в таблицу
      copy colors (id, cname) from 'c:/myfile.lst'
        with delimiter ',' -- разделитель полей
        null 'null' -- чем заменять пустые значения
        cvs -- CSV настройки
        quote '"' -- поле заключено между "
        escape '\\\\' ; -- ESC символ, для пропуска "
      

      Информация о базе данных

      В стандарте SQL определена особая схема information_schema, содержащая информацию о базе данных. Например, таблица tables хранит значения различных параметров всех таблиц, как их имена. Само число параметров зависит от СУБД.

      -- PostgreSQL, MySQL
      select * from information_schema.tables;
      

      В старых версиях Oracle пока не реализована эта возможность. А использование системных таблиц или пакета dbms_metadata по ряду причин не так удобны с точки зрения разработки инструментов управления БД.

      В MySQL имеется специальная команда для получения такого рода информации — show.
      В PostgreSQL тоже есть одноименная команда, но она служит для других целей.

      -- список таблиц в схеме test
      SHOW tables from test;
      
      -- посмотреть SQL код создания таблицы tblname
      SHOW CREATE TABLE tblname;
      
      -- подобным образом просматриваются другие
      -- типы объектов, 
      -- однако некоторые команды у меня не заработали,
      -- как просмотр кода функции 
      SHOW FUNCTION CODE myfunc;
      

      StudLance.ru

Просмотр 0 веток ответов
  • Для ответа в этой теме необходимо авторизоваться.
×