Разработка баз данных в Delphi

создании и уничтожении данного объекта, но его методы и свойства могут быть полезны в некоторых случаях. В этом компоненте содержится информация обо всех базах данных, с которыми работает программа. Ее можно найти в свойстве DataBases. Со свойством KeepConnections данного объекта мы уже знакомы. Это свойство определяет, нужно ли сохранять соединение с базой, если в программе нет ни одной открытой таблицы из этой базы. NetDir - директория, в которой лежит общий сетевой файл PDOXUSRS.NET, необходимый BDE. PrivateDir - директория для хранения временных файлов.

С помощью методов объекта Session можно получить информацию о настройках BDE, например, список всех псевдонимов, драйверов баз данных или список всех таблиц в базе.

Еще одно важное назначение объекта Session - доступ с его помощью к таблицам Paradox, защищенным паролем. Прежде, чем открыть такую таблицу, требуется выполнить метод AddPassword :


Session.AddPassword(‘my_pass’);


Удалить пароль можно с помощью метода RemovePassword или RemoveAllPasswords.


Указание сетевого протокола при соединении с БД

В случае с InterBase можно в явном виде указать, какой сетевой протокол используется при соединении с базой данных. Эта установка выполняется либо в утилите конфигурации BDE, либо в программе - нужно изменить параметр “SERVER NAME”, который содержит полный путь к файлу с базой данных.

Итак:

Протокол Параметр SERVER NAME

TCP/IP IB_SERVER:PATHDATABASE.GDB ( nt:c:ibbase.gdb ) ( unix:/ib/base.gdb )

IPX/SPX IB_SERVER:PATHDATABASE.GDB ( nw@sys:ibbase.gdb )

NetBEUI \IB_SERVERPATHDATABASE.GDB ( \ntc:ibbase.gdb )


8

Урок 8 : Управление соединением с базой данных



Создание баз данных в Delphi


Chapter 23

Урок 9: Управление транзакциями


Содержание урока 9:


Обзор 2

SQL-выражения для управления транзакциями 2

Запуск транзакции 2

Завершение транзакции 4

Управление транзакциями в Delphi 4

Обзор

Все операции, выполняемые с данными на SQL сервере, происходят в контексте транзакций. Транзакция - это групповая операция, т.е. набор действий с базой данных; самым существенным для этих действий является правило либо все, либо ни чего. Если во время выполнения данного набора действий, на каком-то этапе невозможно произвести очередное действие, то нужно выполнить возврат базы данных к начальному состоянию (произвести откат транзакции). Таким образом (при правильном планировании транзакций), обеспечивается целостность базы данных. В данном уроке объясняется, как начинать, управлять и завершать транзакции с помощью SQL выражений. А так же рассматривается вопрос об использовании транзакций в приложениях, созданных в Delphi. Вся приведенная информация касается InterBase.

SQL-выражения для управления транзакциями

Для управления транзакциями имеется три выражения:


SET TRANSACTION - Начинает транзакцию и определяет ее поведение.


COMMIT - Сохраняет изменения, внесенные транзакцией, в базе данных и завершает транзакцию.


ROLLBACK - Отменяет изменения, внесенные транзакцией, и завершает транзакцию.


Запуск транзакции

Выполнять транзакции можно, например, из Windows Interactive SQL, из программы, из сохраненной процедуры или триггера. В общем виде, синтаксис команды SQL для запуска транзакции:


SET TRANSACTION [Access mode] [Lock Resolution]

[Isolation Level] [Table Reservation]


Значения, принимаемые по-умолчанию:

выражение

SET TRANSACTION

равносильно выражению

SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL SNAPSHOT


Access Mode - определяет тип доступа к данным. Может принимать два значения:

  • READ ONLY - указывает, что транзакция может только читать данные и не может модифицировать их.

  • READ WRITE - указывает, что транзакция может читать и модифицировать данные. Это значение принимается по умолчанию.


Пример:


SET TRANSACTION READ WRITE


Isolation Level - определяет порядок взаимодействия данной транзакции с другими в данной базе. Может принимать значения:

  • SNAPSHOT - значение по умолчанию. Внутри транзакции будут доступны данные в том состоянии, в котором они находились на момент начала транзакции. Если по ходу дела в базе данных появились изменения, внесенные другими завершенными транзакциями, то данная транзакция их не увидит. При попытке модифицировать такие записи возникнет сообщение о конфликте.

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

  • READ COMMITTED - позволяет транзакции видеть текущее состояние базы.


Конфликты, связанные с блокировкой записей происходят в двух случаях:

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

  • Транзакция пытается модифицировать таблицу, которая заблокирована другой транзакцией типа SNAPSHOT TABLE STABILITY.


Lock Resolution - определяет ход событий при обнаружении конфликта блокировки. Может принимать два значения:

  • WAIT - значение по умолчанию. Ожидает разблокировки требуемой записи. После этого пытается продолжить работу.

  • NO WAIT - немедленно возвращает ошибку блокировки записи.


Table Reservation - позволяет транзакции получить гарантированный доступ необходимого уровня к указанным таблицам. Существует четыре уровня доступа:

  • PROTECTED READ - запрещает обновление таблицы другими транзакциями, но позволяет им выбирать данные из таблицы.

  • PROTECTED WRITE - запрещает обновление таблицы другими транзакциями, читать данные из таблицы могут только транзакции типа SNAPSHOT или READ COMMITTED.

  • SHARED READ - самый либеральный уровень. Читать могут все, модифицировать - транзакции READ WRITE.

  • SHARED WRITE - транзакции SNAPSHOT или READ COMMITTED READ WRITE могут модифицировать таблицу, остальные - только выбирать данные.


Завершение транзакции

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

  • COMMIT - сохраняет внесенные транзакцией изменения в базу данных. Это означает, что транзакция завершена успешно.

  • ROLLBACK - откат транзакции. Транзакция завершается и никаких изменений в базу данных не вносится. Данная операция выполняется при возникновении ошибки при выполнении операции (например, при невозможности обновить запись).


Управление транзакциями в Delphi

Прежде всего, транзакции в Delphi бывают явные и неявные.

Явная транзакция - это транзакция, начатая и завершенная с помощью методов объекта DataBase: StartTransaction, Commit, RollBack. После начала явной транзакции, все изменения, вносимые в данные относятся к этой транзакции.

Другого способа начать явную транзакцию, нежели с использованием DataBase, нет. (Точнее говоря, такая возможность есть, но это потребует обращения к функциям API InterBase. Однако, это уже достаточно низкоуровневое программирование.) Следовательно, в рамках одного соединения нельзя начать две транзакции.

Неявная транзакция стартует при модификации данных, если в данный момент нет явной транзакции. Неявная транзакция возникает, например, при выполнении метода Post для объектов Table и Query. То есть, если Вы отредактировали запись, в DBGrid и переходите на другую запись, то это влечет за собой выполнение Post, что, в свою очередь, приводит к началу неявной транзакции, обновлению данных внутри транзакции и ее завершению. Важно отметить, что неявная транзакция, начатая с помощью методов Post, Delete, Insert, Append и т.д. заканчивается автоматически.

Для модификации данных может использоваться и PassThrough SQL - SQL-выражение, выполняемое с помощью метода ExecSQL класса TQuery. Выполнение модификации через PassThrough SQL также приводит к старту неявной транзакции. Дальнейшее поведение транзакции, начатой таким путем, определяется значением параметра SQLPASSTHRU MODE для псевдонима базы данных (или тот-же параметр в св-ве Params объекта DataBase). Этот параметр может принимать три значения:

  • SHARED AUTOCOMMIT - слово SHARED указывает на то, что оба вида транзакций(через Passthrough SQL и через методы TTable и TQuery) разделяют одно и то же соединение к базе данных. Слово AUTOCOMMIT указывает на то, что неявная транзакция, начатая через Passthrough SQL, завершается после выполнения действия по модификации данных (автоматически выполняется COMMIT).

  • SHARED NOAUTOCOMMIT - отличается от предыдущего тем, что неявная транзакция, начатая через Passthrough SQL, не завершается после выполнения, ее нужно явно завершить, выполнив SQL-выражение “COMMIT”.

  • NOT SHARED - транзакции разных типов работают через разные соединения с базой. Данное значение параметра подразумевает также NOAUTOCOMMIT. То есть все неявные PassthroughSQL-транзакции нужно завершать явно - выполняя SQL-выражение “COMMIT” для Passtrough SQL.


Рассмотрим возможные сценарии поведения транзакций при разных значениях параметра.

В первом случае, если нет в данный момент начатой транзакции, то попытка модификация данных методами TTable или TQuery, как и выполнение через Passtrough SQL какой-либо операции приведет к старту неявной транзакции. После выполнения, такая транзакция будет автоматически завершена (если не возникло ошибки по ходу транзакции). Если уже имеется начатая явно (метод StartTransaction объекта DataBase) транзакция, то изменения будут проходить в ее контексте. Все транзакции используют одно и то-же соединение.

Во втором случае все происходит, как в первом. Отличие в том, что неявная PassthroughSQL-транзакция не завершается, пока не будет выполнена команда “COMMIT”.

В третьем случае, при выполнении команды Passthrough SQL, будет установлено еще одно соединение, начата неявная транзакция и выполнены действия по модификации данных. Транзакция не будет завершена, пока не будет выполнена команда “COMMIT”. Наличие транзакции, начатой явно с помощью DataBase никак не отразится на ходе выполнения PassthroughSQL-транзакции. Пока PassthroughSQL-транзакция не завершится, изменения, внесенные ей, не будут видны в объектах Table и Query, работающих через другое соединение. PassthroughSQL-транзакции можно рассматривать в некотором смысле, как транзакции из другого приложения.


Взаимодействие транзакций данной программы с транзакциями из других приложений определяется свойством TransIsolation объекта DataBase. Для InterBase имеет смысл два значения: tiReadCommitted и tiRepeatableRead. Выполнение метода StartTransaction в этих двух случаях равносильно выполнению SQL-выражений, соответственно:


SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL READ COMMITTED

и

SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL SNAPSHOT

6

Урок 9 : Управление транзакциями



Создание баз данных в Delphi


Урок 10: Основы языка SQL

Содержание урока 10:


Обзор 2

Состав языка SQL 2

Реляционные операции. Команды языка манипулирования данными 4

Команда SELECT 10

Простейшие конструкции команды SELECT 10

Список полей 10

Все поля 11

Все поля в произвольном порядке 11

Блобы 11

Вычисления 12

Литералы 12

Конкатенация 13

Использование квалификатора AS 13

Работа с датами 14

Агрегатные функции 15

Предложение FROM команды SELECT 16

Ограничения на число выводимых строк 16

Операции сравнения 16

BETWEEN 18

IN 20

LIKE 21

CONTAINING 22

IS NULL 22

Логические операторы 23

Преобразование типов (CAST) 25

Изменение порядка выводимых строк (ORDER BY) 25

Упорядочивание с использованием имен столбцов 26

Упорядочивание с использованием номеров столбцов 28

Устранение дублирования (модификатор DISTINCT) 29

Соединение (JOIN) 30

Внутренние соединения 31

Самосоединения 34

Внешние соединения 35

Обзор

SQL (обычно произносимый как "СИКВЭЛ" или “ЭСКЮЭЛЬ”) символизирует собой Структурированный Язык Запросов. Это - язык, который дает Вам возможность создавать и работать в реляционных базах данных, являющихся наборами связанной информации, сохраняемой в таблицах.


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

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

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


Стандарт SQL определяется ANSI (Американским Национальным Институтом Стандартов) и в данное время также принимается ISO (Международной Организацией по Стандартизации). Однако, большинство коммерческих программ баз данных расширяют SQL без уведомления ANSI, добавляя различные особенности в этот язык, которые, как они считают, будут весьма полезны. Иногда они несколько нарушают стандарт языка, хотя хорошие идеи имеют тенденцию развиваться и вскоре становиться стандартами "рынка" сами по себе в силу полезности своих качеств.

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

Точное описание особенностей языка приводится в документации на СУБД, которую Вы используете. SQL системы InterBase 4.0 соответствует стандарту ANSI-92 и частично стандарту ANSI-III.


Состав языка SQL

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

Поэтому, в язык SQL в качестве составных частей входят:

  • язык манипулирования данными (Data Manipulation Language, DML)

  • язык определения данных (Data Definition Language, DDL)

  • язык управления данными (Data Control Language, DCL).


Подчеркнем, что это не отдельные языки, а различные команды одного языка. Такое деление проведено только лишь с точки зрения различного функционального назначения этих команд.


Язык манипулирования данными используется, как это следует из его названия, для манипулирования данными в таблицах баз данных. Он состоит из 4 основных команд:


SELECT (выбрать)

INSERT (вставить)

UPDATE (обновить)

DELETE (удалить).


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


CREATE DATABASE (создать базу данных)

CREATE TABLE (создать таблицу)

CREATE VIEW (создать виртуальную таблицу)

CREATE INDEX (создать индекс)

CREATE TRIGGER (создать триггер)

CREATE PROCEDURE (создать сохраненную процедуру)

ALTER DATABASE (модифицировать базу данных)

ALTER TABLE (модифицировать таблицу)

ALTER VIEW (модифицировать виртуальную таблицу)

ALTER INDEX (модифицировать индекс)

ALTER TRIGGER (модифицировать триггер)

ALTER PROCEDURE (модифицировать сохраненную процедуру)

DROP DATABASE (удалить базу данных)

DROP TABLE (удалить таблицу)

DROP VIEW (удалить виртуальную таблицу)

DROP INDEX (удалить индекс)

DROP TRIGGER (удалить триггер)

DROP PROCEDURE (удалить сохраненную процедуру).


Язык управления данными используется для управления правами доступа к данным и выполнением процедур в многопользовательской среде. Более точно его можно назвать “язык управления доступом”. Он состоит из двух основных команд:


GRANT (дать права)

REVOKE (забрать права).


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

  • интерактивный SQL

  • встроенный SQL.

Интерактивный SQL используется в специальных утилитах (типа WISQL или DBD), позволяющих в интерактивном режиме вводить запросы с использованием команд SQL, посылать их для выполнения на сервер и получать результаты в предназначенном для этого окне. Встроенный SQL используется в прикладных программах, позволяя им посылать запросы к серверу и обрабатывать полученные результаты, в том числе комбинируя set-ориентированный и record-ориентированный подходы.

Мы не будем приводить точный синтаксис команд SQL, вместо этого мы рассмотрим их на многочисленных примерах, что намного более важно для понимания SQL, чем точный синтаксис, который можно посмотреть в документации на Вашу СУБД.


Итак, начнем с рассмотрения команд языка манипулирования данными.

Реляционные операции. Команды языка манипулирования данными

Наиболее важной командой языка манипулирования данными является команда SELECT. За кажущейся простотой ее синтаксиса скрывается огромное богатство возможностей. Нам важно научиться использовать это богатство!

На данном уроке предполагается, если не оговорено противное, что все команды языка SQL вводятся интерактивным способом. В качестве информационной основы для примеров мы будем использовать базу данных “Служащие предприятия” (employee.gdb), входящую в поставку Delphi и находящуюся (по умолчанию) в поддиректории IBLOCALEXAMPLES.

Рис. 1: Структура базы данных EMPLOYEE


На рис.1 приведена схема базы данных EMPLOYEE для Local InterBase, нарисованная с помощью CASE-средства S Designor (см. доп. урок). На схеме показаны таблицы базы данных и взаимосвязи, а также обозначены первичные ключи и их связи с внешними ключами. Многие из примеров, особенно в конце урока, являются весьма сложными. Однако, не следует на этом основании делать вывод, что так сложен сам язык SQL. Дело, скорее, в том, что обычные (стандартные) операции настолько просты в SQL, что примеры таких операций оказываются довольно неинтересными и не иллюстрируют полной мощности этого языка. Но в целях системности мы пройдем по всем возможностям SQL: от самых простых - до чрезвычайно сложных.

Начнем с базовых операций реляционных баз данных. Таковыми являются:

  • выборка (Restriction)

  • проекция (Projection)

  • соединение (Join)

  • объединение (Union).


Операция выборки позволяет получить все строки (записи) либо часть строк одной таблицы.


SELECT * FROM country Получить все строки
таблицы Country


COUNTRY CURRENCY

=============== ==========

USA Dollar

England Pound

Canada CdnDlr

Switzerland SFranc

Japan Yen

Italy Lira

France FFranc

Germany D-Mark

Australia ADollar

Hong Kong HKDollar

Netherlands Guilder

Belgium BFranc

Austria Schilling

Fiji FDollar


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


SELECT * FROM country

WHERE currency = “Dollar” Получить подмножество строк таблицы Country, удовлетворяющее условию Currency = “Dollar”


Результат последней операции выглядит следующим образом:


COUNTRY CURRENCY

=============== ==========

USA Dollar


Операция проекции позволяет выделить подмножество столбцов таблицы. Например:


SELECT currency FROM country Получить список
денежных единиц


CURRENCY

==========

Dollar

Pound

CdnDlr

SFranc

Yen

Lira

FFranc

D-Mark

ADollar

HKDollar

Guilder

BFranc

Schilling

FDollar


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


SELECT currency FROM country

WHERE country = “Japan” Найти денежную
единицу Японии


CURRENCY

==========

Yen


SELECT first_name, last_name

FROM employee

WHERE first_name = "Roger" Получить фамилии
работников,
которых зовут “Roger”

FIRST_NAME LAST_NAME

=============== ====================

Roger De Souza

Roger Reeves


Эти примеры иллюстрируют общую форму команды SELECT в языке SQL (для одной таблицы):


SELECT (выбрать) специфицированные поля

FROM (из) специфицированной таблицы

WHERE (где) некоторое специфицированное условие является истинным


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


SELECT first_name, last_name, proj_name

FROM employee, project

WHERE emp_no = team_leader Получить список
руководителей проектов


FIRST_NAME LAST_NAME PROJ_NAME

============== ================= ====================

Ashok Ramanathan Video Database

Pete Fisher DigiPizza

Chris Papadopoulos AutoMap

Bruce Young MapBrowser port

Mary S. MacDonald Marketing project 3


Операция объединения позволяет объединять результаты отдельных запросов по нескольким таблицам в единую результирующую таблицу. Таким образом, предложение UNION объединяет вывод двух или более SQL-запросов в единый набор строк и столбцов.


SELECT first_name, last_name, job_country

FROM employee

WHERE job_country = "France"

UNION

SELECT contact_first, contact_last, country

FROM customer

WHERE country = "France" Получить список
работников и заказчиков,
проживающих во Франции


FIRST_NAME LAST_NAME JOB_COUNTRY

=============== ================= ===============

Jacques Glon France

Michelle Roche France


Для справки, приведем общую форму команды SELECT, учитывающую возможность соединения нескольких таблиц и объединения результатов:


SELECT [DISTINCT] список_выбираемых_элементов (полей)

FROM список_таблиц (или представлений)

[WHERE предикат]

[GROUP BY поле (или поля) [HAVING предикат]]

[UNION другое_выражение_Select]

[ORDER BY поле (или поля) или номер (номера)];

Рис. 2: Общий формат команды SELECT

Отметим, что под предикатом понимается некоторое специфицированное условие (отбора), значение которого имеет булевский тип. Квадратные скобки означают необязательность использования дополнительных конструкций команды. Точка с запятой является стандартным терминатором команды. Отметим, что в WISQL и в компоненте TQuery ставить конечный терминатор не обязательно. При этом там, где допустим один пробел между элементами, разрешено ставить любое количество пробелов и пустых строк - выполняя желаемое форматирование для большей наглядности.


Гибкость и мощь языка SQL состоит в том, что он позволяет объединить все операции реляционной алгебры в одной конструкции, “вытаскивая” таким образом любую требуемую информацию, что очень часто и происходит на практике.

Команда SELECT

Простейшие конструкции команды SELECT

Итак, начнем с рассмотрения простейших конструкций языка SQL. После такого рассмотрения мы научимся:

  • назначать поля, которые должны быть выбраны

  • назначать к выборке “все поля”

  • управлять “вертикальным” и “горизонтальным” порядком выбираемых полей

  • подставлять собственные заголовки полей в результирующей таблице

  • производить вычисления в списке выбираемых элементов

  • использовать литералы в списке выбираемых элементов

  • ограничивать число возвращаемых строк

  • формировать сложные условия поиска, используя реляционные и логические операторы

  • устранять одинаковые строки из результата.


Список выбираемых элементов может содержать следующее:

  • имена полей

  • *

  • вычисления

  • литералы

  • функции

  • агрегирующие конструкции

Список полей

SELECT first_name, last_name, phone_no

FROM phone_list получить список
имен, фамилий и служебных телефонов
всех работников предприятия


FIRST_NAME LAST_NAME PHONE_NO

============= ==================== ====================

Terri Lee (408) 555-1234

Oliver H. Bender (408) 555-1234

Mary S. MacDonald (415) 555-1234

Если Вам нужна помощь с академической работой (курсовая, контрольная, диплом, реферат и т.д.), обратитесь к нашим специалистам. Более 90000 специалистов готовы Вам помочь.
Бесплатные корректировки и доработки. Бесплатная оценка стоимости работы.

Поможем написать работу на аналогичную тему

Получить выполненную работу или консультацию специалиста по вашему учебному проекту
Нужна помощь в написании работы?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Пишем статьи РИНЦ, ВАК, Scopus. Помогаем в публикации. Правки вносим бесплатно.

Похожие рефераты: