XML в MS SQL Server 2000 и технологиях доступа к данным - UpdateGrams

Written on . Posted in MS SQL Server

ОГЛАВЛЕНИЕ

Страница 11 из 15

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

форум sql server