SQL в Visual FoxPro 6.0 (основные понятия) SQL в Visual FoxPro ® 6.0 (основные понятия) Дроздов Михаил Компания «ИВС Софт» My Page: My Page: ICS Page:

Что такое SQL SQL (Structured Query Language) - язык структурированных запросов: является универсальным языком для создания, модификаций и управления данными в реляционных базах данных. Историческая справка: –предложен в 1970 И.Ф. Коддом (E.F.Codd) в исследовательской лаборатории IBM – начало 80-х Oracle Relational Technology создаёт СУБД на основе SQL – в 1989 уже существует более 25 SQL-подобных СУБД –в 1983 выходит ANSI-стандарт ( American National Standards Institute ) SQL, а в 1992 расширенный стандарт (SQL2 или SQL- 92). Все промышленные версии стараются поддерживать ANSI- SQL, но не обеспечиваю его полностью, однако, каждая из них имеет свои расширения, стремясь покрыть требуемые функциональные возможности.

Вся информация представлена в виде таблиц. Поддерживать логическую структуру данных независимо от их физического представления. Использовать язык высокого уровня для работы со структурой данных, получения данных и их изменения. Обеспечивать теоретико-множественные операции над данными, такие как объединение, пересечение и дополнение. Поддерживать виртуальные таблицы, как альтернативный способ получения данных. Позволять различать неопределённые (пропущенные) данные. Обеспечивать механизмы для –поддержки реляционной целостности, –авторизованного доступа, –транзакционности изменений, –восстановления данных. Общие требования к реляционной СУБД

Данные в реляционной СУБД Отношение (relation), таблица (table), файл (file) Вся информация представлена в виде таблиц: Атрибут (attribute), столбец (column), поле (field) Кортеж (tuple), строка (row), запись (record) Ячейка (cell), адрес (addres), значение атрибута (alltibute value)

В реляционных СУБД обычно существует два типа таблиц: –пользовательские таблицы (user tables) –системные таблицы (system tables). В VFP системные таблицы в виде одной единственной таблицы- файла с расширением dbc. Обычно также системные таблицы объединяются в системную базу данных (в MS SQL Server например, это база Master) Системные таблицы (известные также под названием системный каталог [system catalog]) содержат описания баз(ы) данных. Доступ к этим таблицам обычно открыт и осуществляется либо через системные процедуры-функции либо непосредственно. Независимость данных в реляционных СУБД обеспечивается на двух уровнях: –Физическая независимость - означает независимость от способа физического хранения данных. –Логическая независимость - означает, что правильное функционирование не зависит от изменений, вносимых в структуру пользовательских данных. Структура СУБД

Язык программирования Единый язык высокого уровня, позволяющий: описывать данные (data definition) манипулировать данными (data manipulation) вести администрирование данных (data administration) Любая операция осуществляется посредством: операторов (statement) команд (command) Манипулирование с данными различается на выборку данных (data retrieval) или запросы к данным (data queries) модификация данных (data modification) Язык SQL является непроцедурным языком (nonprocedural language)

CREATE DATABASE [DatabaseName | ?] Создание объектов базы данных CREAE TABLE | DBF TableName1 [NAME LongTableName] [FREE] (FieldName1 FieldType [(nFieldWidth [, nPrecision])] [NULL | NOT NULL] [CHECK lExpression1 [ERROR cMessageText1]] [DEFAULT eExpression1] [PRIMARY KEY | UNIQUE] [REFERENCES TableName2 [TAG TagName1]] [NOCPTRANS] [, FieldName2...] [, PRIMARY KEY eExpression2 TAG TagName2 |, UNIQUE eExpression3 TAG TagName3] [, FOREIGN KEY eExpression4 TAG TagName4 [NODUP] REFERENCES TableName3 [TAG TagName5]] [, CHECK lExpression2 [ERROR cMessageText2]]) | FROM ARRAY ArrayName CREATE SQL VIEW [ViewName ] [REMOTE] [CONNECTION ConnectionName [SHARE] | CONNECTION DataSourceName] [AS SQLSELECTStatement] CREATE CURSOR alias_name (fname1 type [(precision [, scale]) [NULL | NOT NULL] [CHECK lExpression [ERROR cMessageText]] [DEFAULT eExpression] [UNIQUE] [NOCPTRANS]] [, fname2...]) | FROM ARRAY ArrayName INDEX ON eExpression TO IDXFileName | TAG TagName [OF CDXFileName] [FOR lExpression] [COMPACT] [ASCENDING | DESCENDING] [UNIQUE | CANDIDATE] [ADDITIVE] CREATE CONNECTION [ConnectionName | ?] [DATASOURCE cDataSourceName] [USERID cUserID] [PASSWORD cPassWord] [DATABASE cDatabaseName] | CONNSTRING cConnectionString] CREATE TRIGGER ON TableName FOR DELETE | INSERT | UPDATE AS lExpression

ALTER TABLE TableName1 ADD | ALTER [COLUMN] FieldName1 FieldType [(nFieldWidth [, nPrecision])] [NULL | NOT NULL] [CHECK lExpression1 [ERROR cMessageText1]] [DEFAULT eExpression1] [PRIMARY KEY | UNIQUE] [REFERENCES TableName2 [TAG TagName1]] [NOCPTRANS] [NOVALIDATE] -or- ALTER TABLE TableName1 [DROP [COLUMN] FieldName3] [SET CHECK lExpression3 [ERROR cMessageText3]] [DROP CHECK] [ADD PRIMARY KEY eExpression3 TAG TagName2 [FOR lExpression4]] [DROP PRIMARY KEY] [ADD UNIQUE eExpression4 [TAG TagName3 [FOR lExpression5]]] [DROP UNIQUE TAG TagName4] [ADD FOREIGN KEY [eExpression5] TAG TagName4 [FOR lExpression6] REFERENCES TableName2 [TAG TagName5]] [DROP FOREIGN KEY TAG TagName6 [SAVE]] [RENAME COLUMN FieldName4 TO FieldName5] [NOVALIDATE] -or- ALTER TABLE TableName1 ALTER [COLUMN] FieldName2 [NULL | NOT NULL] [SET DEFAULT eExpression2] [SET CHECK lExpression2 [ERROR cMessageText2]] [DROP DEFAULT] [DROP CHECK] [NOVALIDATE] Изменения объектов базы данных MODIFY PROCEDURE ADD TABLE TableName | ? [NAME LongTableName] COMPILE DATABASE DatabaseName VALIDATE DATABASE [RECOVER] [NOCONSOLE] [TO PRINTER [PROMPT] | TO FILE FileName] Gendbc.prg

Удаления объектов базы данных DELETE CONNECTION ConnectionName DELETE DATABASE DatabaseName | ? [DELETETABLES] [RECYCLE] DELETE TAG TagName1 [OF CDXFileName1] [, TagName2 [OF CDXFileName2]]... -or- DELETE TAG ALL [OF CDXFileName] DELETE VIEW ViewName -or- DROP VIEW ViewName DELETE TRIGGER ON TableName FOR DELETE | INSERT | UPDATE DROP TABLE TableName | FileName | ? [RECYCLE]

Добавление изменение и удаление данных из таблиц INSERT INTO dbf_name [(fname1 [, fname2,...])] VALUES (eExpression1 [, eExpression2,...]) -or- INSERT INTO dbf_name FROM ARRAY ArrayName | FROM MEMVAR UPDATE [DatabaseName1!]TableName1 SET Column_Name1 = eExpression1 [, Column_Name2 = eExpression2...] WHERE FilterCondition1 [AND | OR FilterCondition2...]] DELETE FROM [DatabaseName!]TableName [WHERE FilterCondition1 [AND | OR FilterCondition2...]] RECALL [Scope] [FOR lExpression1] [WHILE lExpression2] [NOOPTIMIZE] (VFP specific) DELETE [Scope] [FOR lExpression1] [WHILE lExpression2] [IN nWorkArea | cTableAlias] [NOOPTIMIZE]

Выборка данных SELECT список_полей FROM список_таблиц WHERE условия_на_записи GROUP BY способ_группировки HAVING условия_на_группы ORDER BY способ_сортировки INTO новая_таблица

SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]] [Alias.] Select_Item [AS Column_Name] [, [Alias.] Select_Item [AS Column_Name]...] FROM [FORCE] [DatabaseName!]Table [[AS] Local_Alias] [[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN DatabaseName!]Table [[AS] Local_Alias] [ON JoinCondition …] [[INTO Destination] | [TO FILE FileName [ADDITIVE] | TO PRINTER [PROMPT] | TO SCREEN]] [PREFERENCE PreferenceName] [NOCONSOLE] [PLAIN] [NOWAIT] [WHERE JoinCondition [AND JoinCondition...] [AND | OR FilterCondition [AND | OR FilterCondition...]]] [GROUP BY GroupColumn [, GroupColumn...]] [HAVING FilterCondition] [UNION [ALL] SELECTCommand] [ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC]...]] Destination: ARRAY ArrayName CURSOR CursorName [NOFILTER] DBF | TABLE TableName [DATABASE DatabaseName [NAME LongTableName]] Команда SQL Select синтаксис Order_Item: field

Допустимые форматы Допустимые форматы FilterCondition Команда SQL Select Select_Item: поле таблицы строкой () вычисляемым выражением (ydt_sales*price), которое может включать UDF

Агрегирующие функции: AVG(Select_Item) среднее значение COUNT(Select_Item) количество выбранных записей для поля COUNT(*) количество выбранных записей в запросе MIN(Select_Item) наименьшее значений поля MAX(Select_Item) наибольшее значение поля SUM(Select_Item) сумма для числового поля Нужно иметь ввиду: Системная глобальная переменная _TALLY содержит количество записей выбранных в последнем запросе Значение SET FILTER не влияет на запрос Подзапрос не может быть вложенным, т.е. снова содержать в себе подзапрос. Названия полей должны быть уникальны в списке полей Использование функций: DELETED( ), EOF( ), FOUND( ), RECCOUNT( ) и RECNO( ) может привести к непредвиденным результатам по причине неопределённости рабочих областей для таблиц, участвующих в запросе Предложение WHERE накладывает ограничения на строки в то время как HAVING на группы. WHERE не может применяться к агрегирующим функциям, в то время как HAVING может. Команда SQL Select

Коррелированные и некоррелированные подзапросы: Некоррелированный: Select pub_name from publishers where pub_id in (select pub_id from titles where type = business) Коррелированный: Select pub_name from publishers p where business in (select type from titles where pub_id = p.pub_id) Внутренний запрос «работает» независимо, передавая результаты во внешний. Внутренний запрос «работает» каждый раз только получив результаты от внешнего.

Технология основана на оптимизации доступа к данным с помощью использования стандартных VFP-индексов (idx,cdx) и способна резко повысить производительность операций обработки данных за счёт использования оперативной памяти компьютера. Способна уменьшить время выполнения многотабличных запросов если даже индекс создаётся как временный (только на время выполнения запроса). Кратко: оптимизации подвержены команды, содержащие предложение FOR и когда используемые условные выражения представлены в терминах определяющих выражений индексов. В SQL командах для успешной оптимизации не требуется предварительного открытия ни таблиц ни индексов. SQL самостоятельно решает вопрос о необходимости тех или иных индексов и, если они не существуют, создаёт их временными самостоятельно. Потенциально оптимизируемые команды, содержащие предложение FOR: Технология Rushmore AVERAGE BLANK BROWSE CALCULATE CHANGE COPY TO COPY TO ARRAY COUNT DELETE DISPLAY EDIT EXPORT TO INDEX JOIN WITH LABEL LIST LOCATE RECALL REPLACE REPLACE FROM ARRAY REPORT SCAN SET DELETED SET FILTER SORT TO SUM TOTAL TO SELECT UPDATE DELETE и SQL-команды:

В предложениях FOR наличие –ALL и REST - используют, –NEXT и RECORD -отключает оптимизацию. Поскольку ALL-режим по умолчанию, то отсутствие указанных выше параметров подразумевает оптимизацию. Оптимизации отключается для: –индексов при наличии любого фильтра –индекса типа UNIQUE все другие типы индексов оптимизируемы. Для повышения производительность отключайте (SET ORDER TO 0) активные индексы у таблиц (включенные индексы вынуждают выполнять «двойную работу»). Выражения типа: NOT DELETED() или DISCOUNT > 10 не оптимизируются, хотя оптимизируется например, DELETED() [ INDEX ON DELETED() TAG DEL ] Если WHERE(FOR) содержит несколько условий соединённых AND или OR, то оптимизации подвергается каждое подвыражения,...и если нашлось хотя бы одно не оптимизируемое подвыражение при наличии оптимизируемых, то говорят о частичной оптимизации. Оптимизация не выполняется: –если в FOR невозможно выполнить хотя бы частичную оптимизацию. –в WHILE-выражениях. –нет достаточно оперативной памяти. Технология Rushmore

Функция SYS(3054) позволяет контролировать режим оптимизации: None, Partial, Full Определяя допустима ли оптимизации в FOR-выражениях VFP тратит некоторое время, … и если Вам известно, что выражения не оптимизируемы, то можно «сэкономить» это время, отключив оптимизацию посредством параметра NOOPTIMIZE или команды SET OPTIMIZE OFF Оптимизация производится над базовым выражением eIndex relOp eExp -или- eExpr relOp eIndex где –eIndex - точное совпадение с индексным выражением –relOp - операция сравнения:, =, =, , #, ==, или !=, возможно использование также функций: ISNULL(), BETWEEN(), или INLIST() (или SQL эквивалентов, таких как IS NULL, и т.д.) Однако, функции ISBLANK() и EMPTY() не оптимизируются. –eExp - любое выражение, включая переменные и поля таблиц Если созданы индексы: firstname, custno, UPPER(lastname), and hiredate, каждое из нижеследующих выражений оптимизируемы: firstname = "Fred" custno >= 1000 UPPER(lastname) = "SMITH" hiredate < {^ } Выражение может содержать переменные или функции, вычисляемые до конечных выражений, например, нижеследующая последовательность команд приводит к оптимизации: STORE "WASHINGTON AVENUE" TO cVar ADDR = cVar ADDR = SUBSTR(cVar,8,3) Технология Rushmore

Доступ к внешним данным через ODBC Позволяет: Осуществлять соединения с различными ODBC-совместимыми источниками данных. Выполнять SQL-команды и операторы работы с данными. Осуществлять контроль над возможными ошибками. Закрывать соединения и завершать сеанс работы с данными

Доступ к внешним данным через ODBC (схема обмена данными) (схема обмена данными) Приложение ODBC драйвер приёмника ODBC драйвер источника Источник данных Процесс ODBC

Доступ к внешним данным через ODBC (регистрация источника данных в ODBC Администраторе)

Доступ к внешним данным через ODBC (регистрация источника данных в ODBC Администраторе)

Доступ к внешним данным через ODBC (регистрация соединения в VFP-базе данных)

Доступ к внешним данным через ODBC (создание удалённых представлений в VFP-базе данных)

Доступ к внешним данным через ODBC (открытие данных удалённых представлений в VFP)

Доступ к внешним данным через ODBC Доступ к внешним данным через ODBC (п-тры соединения) Data Processing (Параметры обмена данными) Asynchronous execution (Асинхронное выполнение) - Работа с данными в фоновом режиме. Если не включен, то выполнение приложения будет приостановлено вплоть до завершения операций обмена всех данных, вовлечённых в процесс обмена. Display warning (Отображение предупреждений) - Определяет, будут ли выводится диалоги предупреждений при возникновении ошибок. Если Вы программно обрабатываете все возможные ошибки соединения, то выключите этот режим. Bath processing (Пакетная обработка) - Определяет, будет ли обмен данными осуществляться единым блоком данных. Если этот режим установлен, то Ваше приложение не получит ни одной записи, пока сервер не завершит выполнение запроса. Automatic transaction (Автоматические транзакции) - Определяет, должны ли изменения данных оформляться как транзакция. Если Вы не управляете транзакциями в ручном режиме (используя команды T-SQL), то установите этот режим. Packet size (Размер пакета) - Управляет размером блока пересылаемых данных. Данный режим влияет на производительность процесса обмена данными. Timeout Intervals (Параметры интервалов ожидания) Connections (sec) (Соединение) - Время в секундах, по истечении которого VFP сообщит об ошибки соединения, в случае неудачи. Idle (min) (Время простоя) - Время в минутах, по истечению которого будет разорвано соединение. Если соединение находится в ручном режиме, оно не будет разорвано до окончания транзакции. Query (sec) (Запрос к данным) - Время в секундах ожидания данных запроса. Wait time (ms) (Время ожидания) - Время в миллисекундах для проверки завершения выполнения инструкции.

Доступ к внешним данным через ODBC Доступ к внешним данным через ODBC (список функций) Выполнение инструкций SQL Pass Through обеспечивается следующими функциями: SQLCANCEL() SQLCOLUMNS() SQLCOMMIT() SQLCONNECT() SQLDISCONNECT() SQLEXEC() SQLGETPROP() SQLMORERESULTS() SQLPREPARE() SQLROLLBACK() SQLSETPROP() SQLSTRINGCONNECT() SQLTABLES() А также: DBGETPROP () DBSETPROP() CURSORGETPROP() CURSORSETPROP()

Доступ к внешним данным через ODBC (различия между Remote View и SQL Pass-trough)

Доступ к внешним данным через ODBC (преимущества и недостатки Remote View и SQL Pass-trough) SQL pass-through технология имеет следующие преимущества над Remote Views: Вы можете использовать особенности функциональных возможностей сервера данных, таких как сохраненных процедур и встроенных функций сервера. Вы можете использовать SQL расширения Вашего конкретного сервера данных, также как и особенности языка описания данных, администрирования сервера, и команд контроля безопасности. Вы имеете большие возможности контроля выполнения SQL- инструкций для операций изменения, удаления, и добавления. Вы имеете возможность управления внешними транзакциями Напоминание: в настоящее время Visual FoxPro может обрабатывать SQL запросы, которые возвращают больше чем един набор результатов. Более подробной см., «Processing Multiple Result Sets»

Доступ к внешним данным через ODBC (преимущества и недостатки Remote View и SQL Pass-trough) В свою очередь, SQL pass-through также имеет свои недостатки: По умолчанию, SQL pass-through запрос всегда возвращает неизменяемый набор данных (snapshot), в виде курсора, служащего только для просмотра данных. Вы можете сделать курсор изменяемым, с помощью функции CURSORSETPROP(), позволяющей переустанавливать ряд свойств курсора. Изменяемые Remote View, напротив, обычно не требует, чтобы Вы меняли какие-либо свойства пред тем, чтобы в дальнейшем курсор был редактируемый, поскольку набор его свойств, отвечающий за редактирование, постоянно хранится в базе данных Вам необходимо ввести код SQL команды либо непосредственно в окно Команд либо в программу, без помощи среды Проектировщика запросов. Вам необходимо создать и контролировать соединением с источником данных. Так или иначе, используете ли Вы remote views или SQL pass-through, Вы можете создать как запросы, так и изменяемый remote views. Во многих приложениях обычно используются как те, так и другие.

Блокировка данных Перед выполнение ряда команд автоблокировка выполняется автоматически: ALTER TABLE - таблица APPEND - заголовок таблицы APPEND BLANK - заголовок таблицы APPEND FROM - заголовок таблицы APPEND MEMO - текущая запись BLANK - текущая запись BROWSE, CHANGE, EDIT - текущая запись и все записи связанные с редактируемой из других таблиц CURSORSETPROP() - зависит от параметров DELETE - текущая запись, с предложением FOR вся таблица GATHER - текущая запись INSERT - таблица INSERT SQL - заголовок таблицы MODIFY MEMO - текущая запись RECALL - текущая запись, с предложением FOR вся таблица REPLACE - текущая запись и все записи в других таблицах, поля которых указанны в команде. При указании диапазона вся таблица. TABLEUPDATE() - зависит от типа буферизации UPDATE SQL - таблица

Блокировка данных Режимы: SET EXCLUSIVE - таблица выключает/отключает монопольный режим работы с данными SET REFRESH TO - определяет интенсивность обновления данных SET REPROCESS TO - определяет время/количество попыток блокировки данных, если первая попытка блокировки была неудачной SET MULTILOCS - определяет возможность блокировки нескольких записей SET LOCK - определяет возможность блокировки таблиц для команд: AVERAGE CALCULATE COPY TO COPY TO ARRAY COUNT DISPLAY (with a scope) INDEX JOIN (both files) LABEL LIST REPORT SORT SUM TOTAL Функции: RLOCK() [LOCK()] - осуществляет попытку блокировки записи(ей) FLOCK() - осуществляет попытку блокировки таблицы ISRLOCKED() - проверяет блокировку записи(ей) ISFLOCKED() - проверяет блокировку таблицы SYS(2011) - определяет статус блокировки записи таблицы Снятие блокировки: UNLOCK [RECORD nRecordNumber] [IN nWorkArea | cTableAlias] [ALL] ( снятие должно производится из того программного модуля, в котором производилась блокировка )

Блокировка данных Функции работы с блокированными записями: CURVAL() -возвращает текущее значение поля таблицы GETFLDSTATE() - определяет статус изменения поля(ей) записи таблицы GETNEXTMODIFIED() - определяет номер следующей изменённой записи в буферизованном курсоре OLDVAL() - определяет прежнее значение поля (до изменения) если изменение выполнено, но данные не обновлялись SETFLDSTATE() - изменяет статус изменения полей записи TABLEREVERT() - отменяет изменения, выполненные в буфере, но ещё не сохранённые TABLEUPDATE() - пытается выполнить изменения, выполненные в буфере курсора.

Транзакции Функции и команды не поддерживающие транзакции: APPEND PROCEDURES COPY INDEXES CREATE SQL VIEW DELETE DATABASE MODIFY DATABASE CLEAR ALL COPY PROCEDURES CREATE TRIGGER DELETE TRIGGER MODIFY PROCEDURE CLOSE ALL CREATE CONNECTION CREATE VIEW DELETE VIEW MODIFY VIEW CLOSE DATABASES CREATE DATABASE DELETE CONNECTION MODIFY CONNECTION REQUERY() Функции и команды, которые не могут быть выполнены над таблицей вовлечённой в транзакции: ALTER TABLE CREATE TABLE CURSORSETPROP() INDEX REINDEX MODIFY STRUCTURE DELETE TAG TABLEREVERT() INSERT PACK ZAP

Транзакции Функции и команды, обеспечивающие поддержку транзакций: BEGIN TRANSACTION- открывает блок транзакций END TRANSACTION - закрывает блок транзакций сохраняя все изменения текущего блока ROLLBACK - отменяет все изменения в текущем блоке транзакций TXNLEVEL() - определяет уровень вложенности транзакций (0 - нет транзакций [0:5]) Нужно иметь ввиду, что транзакции VFP поддерживает только локальных данных, помещённых в VFP базу данных. Для обеспечения транзакций внешних данных, следует использовать средства сервера данных. Вовлечённые в транзакции данные не доступны даже на чтение, пока транзакция не завершится. Поэтому, следует стремиться свести к минимуму время выполнения транзакции