MS SQL Server - дело тонкое... (comp.soft.winsoft.sqlhelpyouself) : Рассылка : Subscribe.Ru

Отправляет email-рассылки с помощью сервиса Sendsay

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "Вопросы и ответы по MS SQL Server" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

← Июнь 2002 →
	1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

Автор

Сергей

Статистика

17.597 подписчиков
-20 за неделю

← Все выпуски →

MS SQL Server - дело тонкое...

Информационный Канал Subscribe.Ru

#101<< #102

СОДЕРЖАНИЕ

1. СОВЕТЫ

1.1. XML в MS SQL Server 2000 и технологиях доступа к данным (продолжение)

1.1.1. UpdateGrams

2. ПОЛЕЗНОСТИ

3. ССЫЛКИ НА СТАТЬИ

3.1. Новые технические статьи Microsoft

4. ФОРУМ SQL.RU

4.1. Самые популярные темы недели

4.2. Вопросы остались без ответа

СОВЕТЫ

XML в MS SQL Server 2000 и технологиях доступа к данным (продолжение)
По материалам конференции "Корпоративные базы данных 2002"

Автор: Алексей Шуленин

01. Введение
02. Сохранить как XML
03. Получить как поток XML
04. XML на стороне сервера. FOR XML
05. SQLXML-классы в .NET
06. FOR XML на стороне клиента
07. XML-представление наборов данных в ADO .NET
08. Прямые XPath-запросы к объектам SQL Server
09. Аннотированные схемы
10. XML-шаблоны как разновидность хранимых процедур

11. UpdateGrams

До сих пор под XML-взаимодействием с SQL Server понималось, в основном, чтение данных с сервера в XML-формате с помощью SELECT ... FOR XML или XPath. Возникает вопрос: можно ли их модифицировать в рамках XML-представления. Про возможность модификации косвенно упоминалось пару раз: когда мы говорили про возможности ADO.Net (п.7) и про шаблоны (п.10). Первый способ предусматривает связь с сервером через DataAdapter и работу с DataSet в рамках его XMLной ипостаси. Второй можно реализовать, поместив запросы на обновление в секцию <sql:query> шаблона:


<Root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:header>
     <sql:param name="Имя"></sql:param>
     <sql:param name="Фамилия"></sql:param>
  </sql:header>
  <sql:query>
    UPDATE Customers SET ContactName = 'Maria Anders' WHERE CustomerID = 'ALFKI'
    INSERT Employees (FirstName, LastName) Values (@Имя, @Фамилия)
  </sql:query>
</Root>

Его выполнение:


static void Execute_UpdateTemplate_SQLXML()
{
 ...
 cmd.CommandText = "..\\Templates\\XMLTemplate3.xml";
 cmd.CommandType = SqlXmlCommandType.TemplateFile;
 SqlXmlParameter prm = cmd.CreateParameter();
 prm.Name = "@Имя"; prm.Value = "ааа";
 prm = cmd.CreateParameter();
 prm.Name = "@Фамилия"; prm.Value = "ббб";
 cmd.ExecuteNonQuery();
}

равнозначно тому, как если бы эти запросы были выполнены обычным путем. Возникает вопрос: можно ли модифицировать данные непосредственно на сервере (не кэшируя их предварительно в DataSet) и работая с ними, как с XML, а не через реляционные операторы. Такой способ предоставляет UpdateGrams.
Пример файла UpdateGrams, с которым мы будем работать, приведен на рис.8.


<?xml version="1.0" encoding="utf-8" ?>
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">

<updg:header>
 <updg:param name="Дата"/>
 <updg:param name="Стоимость" />
</updg:header>

<updg:sync mapping-schema="..\Schemas\SQLSchema1.xsd">

 <updg:before>
  <Клиент Имя="Ana Trujillo" updg:id="x" />
  <Клиент Имя="Antonio Moreno" updg:id="y" />
 </updg:before>
 <updg:after>
  <Клиент updg:id="y"
  Фирма="Рога&Копыта"
  Имя="Дон Педро" />
 </updg:after>

 <updg:before>
  <Клиент CustomerID="ALFKI" />
 </updg:before>
 <updg:after>
  <Клиент CustomerID="ALFKI">
   <Заказы>
    <Заказ>
     <Дата>
      $Дата
     </Дата>
     <Стоимость>
      cast(@Стоимость as money)
     </Стоимость>
    </Заказ>
   </Заказы>
  </Клиент>
  </updg:after>

</updg:sync>

<updg:sync mapping-schema="..\Schemas\SQLSchema2.xsd">

 <updg:after>
  <Сотрудник updg:at-identity="x"
  Имя="Альбус"
  Фамилия="Дамблдор" >
   <Сотрудник Имя="Минерва"
   Фамилия="МакГонагалл"
   ReportsTo="x" />
  </Сотрудник>

 </updg:after>

</updg:sync>

</ROOT>

Поскольку я постарался напихать в него по максимуму показательных вещей, разберем данный updategrams по частям. Начнем с того, что это XML-файл, структура которого довольно близка к уже рассмотренным нами шаблонам. В </updg:sync> может задаваться аннотированная схема, которая отображает реляционную схему на XSD и благодаря которой мы можем работать с информацией в БД как с XML. Если схема не указана, предполагается отображение по умолчанию: каждая запись таблицы - элемент, поля в ней - атрибуты. По большому счету updategrams состоит из секций трех типов: <updg:header> - в ней передаются возможные параметры; <updg:before> и <updg:after>. Если запись фигурирует только в <updg:before>, она удаляется; если только в <updg:after> - вставляется; если и там, и там - обновляется. Рассмотрим, например, ситуацию, когда весь updategrams состоит только из


<updg:sync mapping-schema="SQLSchema1.xsd">
     <updg:before>
  <Клиент Имя="Ana Trujillo" />
</updg:before>
 <updg:after>
 </updg:after>
</updg:sync>

В процессе его выполнения на SQL Server происходит следующее:

SET XACT_ABORT ON BEGIN TRAN DECLARE @eip INT, @r__ int, @e__ int SET @eip = 0 DELETE Customers WHERE ( ContactName=N'Ana Trujillo' ) ; SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT IF (@e__ != 0 OR @r__ != 1) SET @eip = 1 IF (@r__ > 1) RAISERROR ( N'SQLOLEDB Error Description: Ambiguous delete, unique identifier required Transaction aborted ', 16, 1) ELSE IF (@r__ < 1) RAISERROR ( N'SQLOLEDB Error Description: Empty delete, no deletable rows found Transaction aborted ', 16, 1) IF (@eip != 0) ROLLBACK ELSE COMMIT SET XACT_ABORT OFF

Из этого сразу видно несколько важных вещей. Первое: каждая секция <updg:sync> открывает отдельную транзакцию (см. BEGIN TRAN). Второе: описание модифицируемого элемента (<Клиент Имя="Ana Trujillo" />) в <updg:before> и/или <updg:after> должно соответствовать одной записи, неважно, идет ли маппирование по дефолту или через аннотированную схему. Если записей, отвечающих эквивалентному условию WHERE не находится (@r__ < 1) или больше одной (@r__ > 1), будет сгенерирована ошибка (RAISERROR) и транзакция откатится (ROLLBACK).
Поскольку в каждой секции <updg:before> и <updg:after> может находиться несколько записей, то необходимо как-то сопоставить их друг другу в случае обновления. Например, при парсинге этого шаблона


 <updg:before>
  <Клиент Имя="Ana Trujillo" updg:id="x" />
  <Клиент Имя="Antonio Moreno" updg:id="y" />
 </updg:before>
 <updg:after>
  <Клиент updg:id="y"
  Фирма="Рога&Копыта"
  Имя="Дон Педро" />
 </updg:after>

SqlXml должен понимать, что клиента по имени Ana Trujillo мы хотим просто удалить, а клиента по имени Antonio Moreno обновляем, поэтому строка <Клиент updg:id="y" Фирма="Рога&Копыта" Имя="Дон Педро" /> в <updg:after> соответствует именно ему. Это можно сделать двумя способами. Первый - пометить их одним и тем же updg:id. Второй способ - однозначно идентифицировать записи при помощи первичного ключа таблицы. Для этого нужно а) определить ключ в аннотированной схеме (вспоминайте аннотацию ms:key-fields) и б) явно сослаться на него в <updg:before>/<updg:after> (скажем, <Сотрудник ID_Сотрудника="..." />).

Следующая часть


 <updg:before>
  <Клиент CustomerID="ALFKI" />
 </updg:before>
 <updg:after>
  <Клиент CustomerID="ALFKI">
   <Заказы>
    <Заказ>
     <Дата>
      $Дата
     </Дата>
     <Стоимость>
      cast(@Стоимость as money)
     </Стоимость>
    </Заказ>
   </Заказы>
  </Клиент>
  </updg:after>

производит обновление и вставку одновременно. В заказы, сделанные клиентом с идентификатором ALFKI добавляется еще один заказ. При этом SQL Server сам распознает, что новую запись в таблице Orders нужно подчинить данному клиенту в таблице Customers и автоматически устанавливает для нее CustomerID в "ALFKI".


exec sp_executesql N'...
INSERT Orders (OrderDate, Freight, CustomerID)
VALUES (@Дата, cast(@Стоимость as money), N''ALFKI'') ... ',
        N'@Дата nvarchar(19),@Стоимость sql_variant',
        N'08.04.2002 18:41:34', 100

Это происходит благодаря тому, что поле CustomerID указано в качестве связующего в <ms:relationship> схемы SQLSchema1.xsd. Обратите внимание, что несмотря на то, что в аннотирующей схеме ему явно не соответствует никакой элемент/атрибут, на него можно ссылаться в updategrams-файле. Для автоматического подчинения родительскую запись в <updg:before>/<updg:after> необходимо идентифицировать именно по CustomerID, которое значится как parent-key в схеме (parent-key="CustomerID"). Определение записи по другим атрибутам, пусть даже однозначно ее идентифицирующим (например, <Клиент Имя="Maria Anders" Фирма="Alfreds Futterkiste" ...>), к такому эффекту не приводит. Следующая часть updategrams-файла:


<updg:sync mapping-schema="..\Schemas\SQLSchema2.xsd">

 <updg:after>
  <Сотрудник updg:at-identity="x"
  Имя="Альбус"
  Фамилия="Дамблдор" >
   <Сотрудник Имя="Минерва"
   Фамилия="МакГонагалл"
   ReportsTo="x" />
  </Сотрудник>

 </updg:after>

</updg:sync>

открывает другую транзакцию и работает с другой аннотирующей схемой, которая, как вы помните, превращает parent-child таблицу в XML-иерархию. Здесь демонстрируется не автоматическое, а "ручное" подчинение. Мы вставляем одновременно две записи. Чтобы подчинить вторую первой, нужно значение поля ReportsTo для второй установить в первичный ключ первой. Положение осложняется тем, что первичный ключ - это identity, и его значение априори неизвестно. Выйти из положения позволяет аннотация updg:at-identity. Кстати, здесь мы снова обращаемся к полю (ReportsTo), которое нигде в схеме не засвечено, а используется только в описании отношения (<ms:relationship>).
Дата и стоимость заказа передаются в виде параметров. Если параметр подставляется standalone, можно использовать XPath-обозначение ($Дата), если же над ним по ходу выполняются какие-то SQL-преобразования - то SQLное (@Стоимость).
Я не стал дальше усложнять пример, но хотел бы отметить еще две полезных аннотации. <updg:nullvalue> позволяет оговорить SQLный NULL:


<updg:sync mapping-schema="SQLSchema2.xsd" updg:nullvalue="Пусто">
 <updg:after>
  <Сотрудник updg:at-identity="x"
  Имя="Альбус"
  Фамилия="Дамблдор" >
   <Сотрудник Имя="Минерва"
   Фамилия="МакГонагалл"
   ReportsTo="Пусто" />
  </Сотрудник>

 </updg:after>

</updg:sync>

В этом случае второй сотрудник не будет подчинен первому, т.к. несмотря на то, что в XML эта запись вложена, при занесении ее в таблицу ей не будет назначено никакого руководителя (ReportsTo=NULL).
Аннотация ms:inverse применяется не в UpdateGrams, а при описании аннотированной схемы. Мы не рассматривали ее в п.9, потому что она имеет смысл только тогда, когда та используется затем в updategrams. Дело в том, что SQL Server считает, что иерархия "родитель-потомок" в XML соответствует ограничению первичный - внешний для ключей связанных таблиц. Рассмотрим updategrams вида


<updg:sync mapping-schema="..\Schemas\SQLSchema2.xsd">
 <updg:before>
  <Клиент CustomerID="ALFKI">
   <Заказы>
    <Заказ />
   </Заказы>
  </Клиент>
 </updg:before>
</updg:sync>

Сразу ясно, что этот пример вызовет ошибку, поскольку данный клиент сделал более одного заказа, а условием UpdateGrams является однозначная идентификация записи. Но дело не в этом. На сервере будут выполнены действия в следующем порядке: DELETE Orders WHERE CustomerID = N'ALFKI'; DELETE Customers WHERE CustomerID = N'ALFKI'. (Использование атрибутов, не входящих в определение <ms:relationship>, - напр., <Клиент Имя="Maria Anders"> приведет к полному DELETE Orders). Из этой последовательности видно, что SqlXml сначала пытается произвести обновления / удаления в дочерней таблице, а уже потом из родительской, чтобы по возможности не противоречить referential constraints. В жизни бывают ситуации, когда схема может задавать вложенность элементов, противоположную направлению действия ограничения primary key/foreign key в таблицах. Тогда SqlXml, предполагая, что вложенный элемент соответствует внешнему ключу, полезет на самом деле в первичную таблицу, получит от SQL Server по рукам и откатит транзакцию. Чтобы побороть такую ситуацию, в <ms:relationship> нужно поставить ms:inverse="true".
Полный список аннотаций, как всегда, можно найти в документации к SQLXML 3.0. Вызов UpdateGrams из приложения происходит аналогично вызову шаблона через файл (см. Скрипт 12) или Stream. При его тестировании в БД Northwind предварительно нужно превратить связь Orders -> [Order Details] из строгой в каскадную. Поскольку [Order Details] не участвует в updategrams, ее FK будет препятствовать обновлению Orders (в отличие от Customer -> Orders, где это учитывается автоматически за счет указания в relationship в аннотированной схеме).

... cmd.CommandText = "..\\Templates\\UpdateGrams1.xml"; cmd.CommandType = SqlXmlCommandType.TemplateFile; SqlXmlParameter prm = cmd.CreateParameter(); prm.Name = "@Дата"; prm.Value = DateTime.Now.ToString(); prm = cmd.CreateParameter(); prm.Name = "@Стоимость"; prm.Value = 100; cmd.ExecuteNonQuery();

Скрипт 12

ПРОДОЛЖЕНИЕ СЛЕДУЕТ

[Содержание]

ПОЛЕЗНОСТИ

FAQ по MS SQL Server на английском языке http://www.windows2000faq.com/Articles/Index.cfm?DepartmentID=800

[Содержание]

ССЫЛКИ НА СТАТЬИ

Новые технические статьи Microsoft