Настройка типов столбцов. Прямое использование Pass-Through технологии через функцию SQLEXEC()
Вы увидели некоторые соглашения по конфигурации, которые использует этот подход. В частности вы видели, как настроить сущностную модель с помощью аннотаций данных, выраженных через атрибуты C#, и с помощью Fluent API. В этой и последующих статьях мы более подробно рассмотрим эти способы настройки модели данных.
Настройка типов столбцов
Ранее, при рассмотрении примера использования Code-First, вы уже видели использование некоторых атрибутов метаданных, которые позволяли настраивать тип данных столбца в таблице базы данных и применять к нему ограничения (например, указав поддерживает ли он NULL-значения). Далее мы рассмотрим эти атрибуты более подробно.
Ограничение длины
В таблице ниже показаны соглашения об ограничении длины столбцов, их реализация в виде аннотаций и в Fluent API:
Ограничение длины можно наложить на строки или массивы байт. Согласно соглашениям, Code-First использует только ограничение максимальной длины, это означает, что SQL Server устанавливает тип данных для строк и массивов байт как NVARCHAR(n) и VARBINARY(n), где n – это длина, указанная в ограничении. По умолчанию, если к свойствам модели не использовалось ограничение по длине, Code-First установит максимально возможную длину столбцов – NVARCHAR(max) и VARBINARY(max).
Как показано в таблице, используя аннотации данных вы можете настроить минимальную длину для свойства модели с помощью атрибута MinLength – это ограничение никак не повлияет на таблицу базы данных. (Как описывалось ранее, атрибуты метаданных могут использоваться не только в Entity Framework, а, например, также в проверки достоверности модели ASP.NET.) Именно поэтому в Fluent API отсутствует метод HasMinLength(), т.к. этот API-интерфейс является частью Entity Framework и отвечает за настройку соглашений, связанных только с Code-First.
Стоит обратить внимание, что указать максимальную и минимальную длину поля можно в одном атрибуте StringLength, используя именованные параметры этого атрибута. В следующем примере показано использование ограничения по длине с помощью аннотаций (здесь мы используем пример модели, который создали в статье “Использование Code-First” ранее):
Using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
namespace CodeFirst
{
public class Customer
{
public int CustomerId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public int Age { get; set; }
public byte Photo { get; set; }
// Ссылка на заказы
public virtual List
А в следующем коде аналогичная настройка производится с помощью Fluent API (напомню, что для использования этого API-интерфейса необходимо переопределить метод настройки конфигурации OnModelCreating() в классе контекста, которым в нашем примере является класс SampleContext):
Явное указание типа столбца
Как описывалось ранее, Entity Framework автоматически отображает типы данных модели на SQL-совместимые типы данных. Code-First позволяет управлять этим процессом, для того чтобы явно указать тип данных для столбца, как показано в примере ниже:
Public class Customer
{
public int CustomerId { get; set; }
// ...
public byte Photo { get; set; }
// ...
}
// то же самое с помощью Fluent API
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity
Поддержка NULL-значений для столбца
Соглашение Entity Framework о поддержке значений NULL в столбце таблицы гласит о том, что все типы.NET, поддерживающие значение null (объекты), отображаются на SQL-типы с явным указанием инструкции NULL, и наоборот, типы.NET, не поддерживающие значение null (структуры) отображаются на SQL-типы с явным указанием инструкции NOT NULL.
Для того, чтобы явно указать, что тип данных не должен поддерживать значения NULL, нужно использовать атрибут Required в модели данных или использовать метод IsRequired() объекта конфигурации в Fluent API. Для того, чтобы наоборот явно указать, что тип данных должен поддерживать значения NULL, нужно использовать коллекцию Nullable
В примере ниже показано использование этих настроек, для конфигурирования информации об обнуляемых или не обнуляемых типах в таблице базы данных:
Public class Customer
{
public int CustomerId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
// Это поле может иметь значение NULL,
// т.к. мы явно указали тип int?
public int? Age { get; set; }
// Аналогично предыдущему свойству
public Nullable
Обратите внимание, что в Fluent API можно настроить только поддержку NOT NULL для ссылочных типов данных, и нельзя настроить поддержку NULL для типов значений, т.к. поддержка NULL для них указывается явно, при объявлении типа свойства в классе модели.
Установка первичных ключей
Entity Framework требует, чтобы каждый класс сущностной модели имел уникальный ключ (т.к. для каждой таблицы в реляционной базе данных должен использоваться первичный ключ). Этот ключ используется в объекте контекста, для отслеживания изменений в объектах модели. Code-First делает некоторые предположения, при поиске ключа в таблице. Например, когда мы сгенерировали базу данных для классов сущностей Customer и Order ранее, при рассмотрении подхода Code-First, то Entity Framework пометил поля CustomerId и OrderId в таблицах, как первичные ключи и задал для них поддержку не обнуляемых типов:
Также EF автоматически добавил поддержку автоинкремента в эти поля (напомню, что в T-SQL это делается с помощью инструкции IDENTITY). Чаще всего, первичные ключи в базе данных имеют тип INT или GUID, хотя любой примитивный тип может быть использован в качестве первичного ключа. Первичный ключ в базе данных может состоять из нескольких столбцов таблицы, аналогично, ключ сущностной модели EF может быть составлен из нескольких свойств модели. Позже вы увидите как настроить составные ключи.
Явная установка первичных ключей
В случае двух наших классов Customer и Order беспокоиться об явном указании первичного ключа не стоит, т.к. мы используем свойства CustomerId и OrderId, которые соответствуют соглашению об именовании ключей - “ + Id”. Давайте рассмотрим пример, в котором нужно явно указать первичный ключ. Добавьте в файл модели следующий простой класс:
Public class Project { public Guid Identifier { get; set; } public DateTime StartDate { get; set; } public DateTime EndDate { get; set; } public decimal Cost { get; set; } }
Нашей целью является указать, что свойство Identifier в этом классе является первичным ключом таблицы. Как видите имя этого свойства не соответствует соглашению Entity Framework об именовании первичных ключей.
Добавьте в класс контекста SampleContext описание новой таблицы:
Public class SampleContext: DbContext
{
// ...
public DbSet
Теперь, если вы запустите наше приложение и вставите данные нового заказчика, то в классе DbModelBuilder будет сгенерировано исключение, в связи с тем, что он не может корректно построить сущностную модель данных. (Напомню, что наше приложение является простым сайтом ASP.NET в котором реализована возможность вставить нового заказчика.)
Это исключение вызвано тем, что Entity Framework не нашел в таблице Project свойство с именем Id или ProjectId, и Code-First не может понять, какое поле нужно использовать в качестве первичного ключа таблицы. Эту проблему можно исправить, используя аннотации данных или Fluent API, как показано в примере ниже:
Public class Project
{
public Guid Identifier { get; set; }
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }
public decimal Cost { get; set; }
}
// то же самое с помощью Fluent API
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity
Обратите внимание, что при использовании Fluent API метод HasKey() указывается после вызова метода Entity
Настройка автоинкремента для первичных ключей
Как видно из таблицы, следуя соглашениям, Entity Framework указывает автоинкремент для свойств имеющих тип int. В созданной ранее таблице Project для первичного ключа указывается тип Guid, вследствие чего, EF не использует счетчик для этого поля, при создании таблицы в базе данных. Это показано на рисунке:
Давайте добавим в наш проект новую веб-форму, которую назовем DatabaseGenerated.aspx. В обработчике Page_Load добавьте следующий код, в котором мы добавляем новые данные в таблицу Project. В данном случае эти данные будут добавляться всякий раз, когда мы открываем страницу нашей веб-формы в браузере.
Using System;
using System.Data.Entity;
using CodeFirst;
namespace ProfessorWeb.EntityFramework
{
public partial class DatabaseGenerated: System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
// Эта настройка нужна для того, чтобы база данных автоматически
// удалялась и заново создавалась при изменении структуры модели
// (чтобы было удобно тестировать примеры)
Database.SetInitializer(new DropCreateDatabaseIfModelChanges
Запустите проект и откройте веб-форму DatabaseGenerated.aspx. В результате в таблицу Project добавится новая запись:
Ни базе данных, ни Entity Framework не известно, что мы хотели бы создавать новый идентификатор Guid для каждой новой записи, поэтому будет автоматически создан идентификатор, содержащий одни нули. Если вы обновите страницу в браузере (фактически в данном случае код попытается вставить новую запись в таблицу), то Entity Framework вернет исключение SqlException, которое возникает из-за того, что мы пытаемся вставить запись имеющую идентификатор, который уже существует в таблице, т.е. в данном случае срабатывает ограничение первичного ключа – он должен быть уникальным для каждой новой записи.
В результате, чтобы решить данную проблему, нам бы потребовалось генерировать уникальный идентификатор Guid в коде. Таблицы, использующие автоинкремент для первичных ключей лишены такой дополнительной работы, т.к. для каждой новой вставляемой записи, счетчик создает новое значение для первичного ключа, извлекая значение первичного ключа для последней записи и прибавляя к нему 1 (если использовалась конструкция IDENTITY(1,1)).
Чтобы решить эту проблему для ключей, имеющих тип отличный от int, нужно использовать атрибут метаданных DatabaseGenerated, в конструкторе которого указывается перечисление DatabaseGeneratedOption , имеющее три возможных значения:
NoneБаза данных не создает никакого уникального значения для первичного ключа. Фактически, с помощью этой опции можно отключить автоматическое добавление автоинкремента к первичным ключам типа int.
IdentityПри вставке значений в таблицу база данных создаст уникальное значение для первичного ключа.
ComputedАналогично Identity, за тем лишь исключением, что первичный ключ будет генерироваться не только при вставке записей в таблицу, но и при их обновлении.
Измените класс модели таким образом, чтобы указать базе данных возможность создавать уникальный первичный ключ:
Public class Project { public Guid Identifier { get; set; } public DateTime StartDate { get; set; } public DateTime EndDate { get; set; } public decimal Cost { get; set; } }
Запустите пример проекта и несколько раз обновите страницу, чтобы вставить несколько записей в таблицу и убедиться, что исключение больше не генерируется. На рисунке ниже показаны данные, добавленные в таблицу:
Обратите внимание на автоматически сгенерированные идентификаторы для проектов. Того же эффекта можно добиться, используя метод HasDatabaseGeneratedOption() в Fluent API:
Protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity
Работа со сложными типами данных
Entity Framework поддерживает возможность использования сложных типов, начиная с первой версии. Фактически сложный тип в.NET является классом, ссылку на который можно использовать в классе модели. Сложный тип не имеет ключа и может использоваться в нескольких объектах модели. Давайте рассмотрим пример следующей модели:
Public class User { public int UserId { get; set; } public int SocialNumber { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string StreetAddress { get; set; } public string City { get; set; } public string ZipCode { get; set; } }
В этом классе адрес проживания пользователя можно выделить в отдельный класс и сослаться на него:
Public class User { public int UserId { get; set; } public int SocialNumber { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public Address Address { get; set; } } public class Address { public int AddressId { get; set; } public string StreetAddress { get; set; } public string City { get; set; } public string ZipCode { get; set; } }
По соглашениями Entity Framework разберет эту модель – как две отдельные таблицы. Но нашей целью является создание сложного типа из класса Address. Традиционный способ для создания сложного типа из класса Address представляет удаление идентификатора AddressId:
Public class Address { // public int AddressId { get; set; } public string StreetAddress { get; set; } public string City { get; set; } public string ZipCode { get; set; } }
В дополнение к правилу, что сложный тип не должен иметь ключ, Code-First накладывает два других правила, которые должны быть выполнены для обнаружения сложного типа. Во-первых, сложный тип должен содержать только простые свойства. Во-вторых, в классе, который использует этот тип, не разрешается указывать тип коллекции для свойства сложного типа. Другими словами, если вы хотите использовать сложный тип Address в классе User, то свойство, имеющее этот тип, не должно быть помечено как List
или использовать другую коллекцию.Как показано на рисунке ниже, после запуска приложения Code-First распознает сложный тип и создает специальные поля в таблице User (не забудьте добавить объявление User в классе контекста):
Обратите внимание, как поля, описывающие адрес пользователя, названы: ИмяСложногоТипа_ИмяСвойства. Это является соглашением Entity Framework по именованию сложных типов.
Настройка сложных типов в обход соглашениям Code-First
Что делать, если ваш класс, описывающий сложный тип, не следует соглашениям Entity Framework, например, вы хотите использовать в классе Address поле AddressId? Если сейчас мы добавим это поле в класс Address и запустим проект, то вместо одной таблицы User и сложного типа Address, Entity Framework создаст две таблицы, связанные друг с другом внешним ключом. Чтобы исправить эту проблему, вы можете явно указать атрибут ComplexType в классе модели или использовать метод ComplexType() класса DbModelBuilder в Fluent API:
Public class Address { public int AddressId { get; set; } public string StreetAddress { get; set; } public string City { get; set; } public string ZipCode { get; set; } } // то же самое с помощью Fluent API protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.ComplexType
(); }Выше было сказано, что класс, описывающий сложный тип, должен иметь только простые свойства (т.е. не ссылающиеся на другие объекты). Это соглашение можно преодолеть, используя все те же средства. Ниже показан пример, в котором был добавлен новый сложный тип UserInfo, ссылающийся на другой тип FullName:
Public class User { public int UserId { get; set; } public UserInfo UserInfo { get; set; } public Address Address { get; set; } } public class UserInfo { public int SocialNumber { get; set; } // Не является простым свойством public FullName FullName { get; set; } } public class FullName { public string FirstName { get; set; } public string LastName { get; set; } } public class Address { public int AddressId { get; set; } public string StreetAddress { get; set; } public string City { get; set; } public string ZipCode { get; set; } }
Благодаря указанию Code-First на то, что UserInfo является сложным типом с помощью атрибута ComplexType, мы преодолели ограничение, накладываемое на сложные типы при использовании соглашения по умолчанию.
Стоит отметить, что Code-First позволяет настраивать сложные типы, также, как и обычные таблицы с использованием Fluent API или аннотаций. Ниже показан пример, для настройки сложного типа Address:
Public class Address { public int AddressId { get; set; } public string StreetAddress { get; set; } public string City { get; set; } public string ZipCode { get; set; } } // то же самое с помощью Fluent API protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.ComplexType
().Property(a => a.StreetAddress) .HasMaxLength(100); }На рисунке ниже показана структура таблицы User. Здесь вы можете увидеть, как EF именует свойства сложных типов с ссылками внутри и то, что EF накладывает ограничение на поле StreetAddress:
Описание других настроек
В этом разделе мы кратко рассмотрим все оставшиеся настройки столбцов таблицы, которые используются довольно редко в силу своих специфических особенностей.
Столбцы типа Timestamp
Тип данных TIMESTAMP в T-SQL указывает столбец, определяемый как VARBINARY(8) или BINARY(8), в зависимости от свойства столбца принимать значения NULL. Для каждой базы данных система содержит счетчик, значение которого увеличивается всякий раз, когда вставляется или обновляется любая строка, содержащая ячейку типа TIMESTAMP, и присваивает этой ячейке данное значение. Таким образом, с помощью ячеек типа TIMESTAMP можно определить относительное время последнего изменения соответствующих строк таблицы. (ROWVERSION является синонимом TIMESTAMP.)
Само по себе значение, сохраняемое в столбце типа TIMESTAMP, не представляет никакой важности. Этот столбец обычно используется для определения, изменилась ли определенная строка таблицы со времени последнего обращения к ней. Это позволяет решать вопросы параллельного доступа к таблице базы данных, позволяя блокировать другие потоки, если текущий поток изменил значения в строке.
В Code-First для указания на то, что столбец должен иметь тип TIMESTAMP должен использоваться одноименный атрибут Timestamp в аннотациях или метод IsRowVersion() в Fluent API, как показано в примере ниже:
Public byte RowVersion { get; set; }
// то же самое с помощью Fluent API
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity
Менее распространенным способом, для обеспечения безопасности при работе с параллельными потоками, является указание проверки параллелизма для каждого столбца. Такой способ может еще использоваться в СУБД, не поддерживающих типы Timestamp/Rowversion. При работе таким образом, поток не проверяет, изменилась ли запись в таблице, а просто блокирует доступ к ней для других потоков, пока сам не завершит процесс записи. Для указания столбцов, которые должны пройти проверку на параллелизм, используется атрибут ConcurrencyCheck в аннотациях, либо метод IsConcurrencyToken() в Fluent API.
Изменение кодировки строк с Unicode на ASCII
По умолчанию Entity Framework преобразует все строковые типы данных модели, такие как string или char, в строковые типы данных SQL, использующие двухбайтовую кодировку Unicode – NVARCHAR или NCHAR. Вы можете изменить это поведение, и явно указать EF использовать однобайтовую кодировку ASCII – соответственно будут использоваться типы VARCHAR и CHAR. Для этого нужно использовать метод IsUnicode() с переданным ему логическим параметром false в Fluent API. Аннотации не предоставляют возможность настройки кодировки строк.
Указание точности для типа Decimal
Для указания точности для типа Decimal (количество цифр в числе) и масштаба (количество цифр справа от десятичной точки в числе) можно использовать метод HasPrecision() с передачей ему двух параметров, который используется в Fluent API. Аннотации данных в Code-First не предлагают альтернативы этому методу. По умолчанию Entity Framework устанавливает точность 18, а масштаб 2 для типов Decimal.
В примере ниже показано использование этого метода, для свойства Cost таблицы Project, которую мы создали ранее, при рассмотрении первичных ключей:
Protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity
Указывает, что новый столбец является столбцом идентификаторов. При добавлении в таблицу новой строки ядро СУБД формирует для этого столбца уникальное последовательное значение. Столбцы идентификаторов обычно используются с ограничением PRIMARY KEY для поддержания уникальности идентификаторов строк в таблице. Свойство IDENTITY может быть присвоено столбцам типов tinyint, smallint, int, bigint, decimal(p,0) или numeric(p,0). Для каждой таблицы можно создать только один столбец идентификаторов. Значения по умолчанию не могут использоваться в столбце идентификаторов. Необходимо указать как начальное значение, так и приращение, или же не указывать ничего. Если ни одно из значений не указано, значением по умолчанию является (1,1).
Синтаксис:
[ IDENTITY [ (seed, increment) ]
s eed - значение, используемое для самой первой строки, загружаемой в таблицу.
i ncrement - з начение приращения, добавляемое к значению идентификатора предыдущей загруженной строки.
[ Табельный номер ] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY
Вычисляемые поля.
Синтаксис:
<имя_столбца> AS <выражение> ]
Выражение, определяющее значение вычисляемого столбца. Вычисляемый столбец представляет собой виртуальный столбец, физически не хранящийся в таблице, если для него не установлен признак PERSISTED. Значение столбца вычисляется на основе выражения, использующего другие столбцы той же таблицы.
Например, определение вычисляемого столбца может быть следующим:
Стоимость_товара AS Цена_за_штуку * Количество .
Выражение может быть именем невычисляемого столбца, константой, функцией, переменной или любой их комбинацией, соединенной одним или несколькими операторами. Выражение не может быть вложенным запросом или содержать псевдонимы типов данных.
Вычисляемые столбцы могут использоваться в списках выбора, предложениях WHERE, ORDER BY и в любых других местах, в которых могут использоваться обычные выражения, за исключением следующих случаев.
Вычисляемый столбец нельзя использовать ни в качестве определения ограничения DEFAULT или FOREIGN KEY, ни вместе с определением ограничения NOT NULL. Однако вычисляемый столбец может использоваться в качестве ключевого столбца индекса или части какого-либо ограничения PRIMARY KEY или UNIQUE, если значение этого вычисляемого столбца определяется детерминистическим (предсказуемым) выражением и тип данных результата разрешен в столбцах индекса.
Например, если таблица содержит целочисленные столбцы a и b , вычисляемый столбец a+b может быть включен в индекс, а вычисляемый столбец a+DATEPART(dd, GETDATE()) - не может, так как его значение может изменяться при последующих вызовах.
Вычисляемый столбец не может быть целевым столбцом инструкций INSERT или UPDATE.
СУБД автоматически определяет для вычисляемых столбцов допустимость значений NULL на основе использованных выражений. Считается, что результат большинства выражений допускает значение NULL, даже если используются только столбцы, для которых значение NULL запрещено, так как при возможном переполнении или потере точности может формироваться значение NULL. Для выяснения возможности вычисляемого столбца таблицы принимать значение NULL используйте функцию COLUMNPROPERTY со свойством AllowsNull . Добиться того, чтобы выражение не допускало значения NULL, можно, указав ISNULL с константой check_expression , где константа представляет собой ненулевое значение, заменяющее любое значение NULL. Для вычисляемых столбцов, основанных на выражениях, содержащих определяемые пользователем типы среды CLR, требуется разрешение REFERENCES на тип.
Указывает, что компонент SQL Server будет физически хранить вычисляемые значения в таблице и обновлять их при изменении любого столбца, от которого зависит вычисляемый столбец. Указание PERSISTED для вычисляемого столбца позволяет создать индекс по вычисляемому столбцу, который будет детерминистическим, но неточным.
При создании таблицы , кроме рассмотренных приемов, можно указать необязательное ключевое слово CONSTRAINT, чтобы присвоить ограничению имя, уникальное в пределах базы данных.
CREATE Т ABLE Заказы (
ID_Заказ INT NOT NULL ,
Блюдо INT NOT NULL,
Количество _ порций INT NOT NULL CHECK( Количество _ порций >0 ) ,
Дата DATE NOT NULL ,
PRIMARY KEY( ID_Заказ , Блюдо , Дата ),
CONSTRAINT FOREIGN_KEY
FOREIGN KEY ( Блюдо , Дата ) REFERENCES Меню ( Блюдо , Дата )) ;
Затем с поименованным ограничением можно работать, как с объектом БД. Если имя ограничения не задано, ядро СУДБ создаст его автоматически, выбрав имя по определенным в системе правилам.
Пример скрипта, построенного системой для создания таблицы Справочник_вид_блюда
CREATE TABLE .[Справочник_вид_блюда](
IDENTITY(1,1) NOT NULL,
[Вид] (20) NOT NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
Что такое IDENTITY
IDENTITY - это не тип данных. Это некоторое дополнительное свойство, ограничение, накладываемое на целочисленные типы данных в MS SQL сервере. Т.е. это свойство может быть применено к полям следующего типа: tinyint , smallint , int , bigint , decimal(p,0) , или numeric(p,0)
Аналогом этого свойства в FoxPro является тип данных Integer-AutoIncrement. Только не надо считать, что Integer-AutoIncrement это и есть поле со свойством Identity. Вовсе нет. Это именно аналог. В основном они похожи, но имеют ряд отличий. В данной статье речь пойдет о свойстве IDENTITY в MS SQL сервере.
Поля со свойством IDENTITY обладают следующими особенностями:
- В одной таблице допустимо существование только одного поля со свойством IDENTITY
- Поле со свойством IDENTITY нельзя редактировать. Они имеет свойство "только для чтения".
- Значение полю со свойством IDENTITY присваивается автоматически в момент создания новой записи.
Есть еще некоторые особенности, но они уже являются следствием перечисленных особенностей
Новое значение - это последнее использованное значение плюс некоторая фиксированная величина. Обратите внимание, новое значение опирается не на максимальное значение в существующих записях, а на последнее использованное значение. Это значит, что записи с последним использованным значением физически может не существовать, тем не менее, это значение будет использовано.
Другими словами, в последовательности значений поля со свойством IDENTITY вполне допустимы "дыры". Список значение вовсе не непрерывный
Как правило, в качестве шага приращения указывают 1, но это может быть и любое целое число. В том числе и отрицательное.
В связи с такими особенностями полей со свойством IDENTITY такие поля часто используют в качестве первичных ключей. Другими словами, в качестве полей, по значению которых всегда можно однозначно идентифицировать запись таблицы.
Следует иметь в виду, что свойство IDENTITY никак не контролирует уникальность данных. Например, если изначально поле имело тип INTEGER, и в него были введены ряд значений. А затем была изменена структура таблицы, и на данное поле было наложено свойство IDENTITY, то новые записи вполне могут иметь те же данные, что уже были введены ранее в эту таблицу. Поэтому, если поле со свойством IDENTITY используется в качестве первичного ключа, то на это поле следует наложить дополнительное ограничение по уникальности.
Недостаток использования полей со свойством IDENTITY как первичного ключа
Однако, несмотря на явные достоинства использования полей со свойством IDENTITY в качестве первичного ключа, они имеют и серьезный недостаток.
Значение полей со свойством IDENTITY невозможно узнать до того, как запись будет физически создана.
Ну и что? Какие проблемы-то? Создадим запись и узнаем ее новое значение.
Проблема заключается в том, что для того, чтобы узнать значение поля какой-либо записи эту запись надо сначала найти. А поиск записи как раз и осуществляется по значению первичного ключа. Того самого, значение которого необходимо определить. Замкнутый круг: чтобы прочитать значение это значение надо знать!
Структура хранения данных в MS SQL сервере принципиально отличается от структуры хранения данных в DBF-файлах. В нем нет таких понятий как "физический номер записи", "следующая запись", "последняя запись" и т.п. Т.е. невозможно перейти к "последней записи" чтобы прочитать значение ее первичного ключа.
Более того, хотя новое значение поля со свойством IDENTITY всегда больше любого из существующих значений (если шаг приращения - положительное число), но определить это новое значение, просто вычислив максимальное из существующих значений, тоже нельзя. Нет, само максимальное значение, разумеется, будет получено. Просто нет никакой гарантии, что полученное значение - это значение именно той записи, которая и была создана.
Дело тут в том, что, как правило, MS SQL сервер используется в многопользовательских приложениях. Это значит, что сразу несколько пользователей, одновременно, могут создавать новые записи. Получается, что один пользователь создал новую запись, затем начал вычислять максимальное значение и в этот момент другой пользователь также создал новую запись. В результате, первый пользователь в качестве максимального значения получит значение записи созданной вторым пользователем.
Так что же, отказаться от использования полей со свойством IDENTITY в качестве первичного ключа? Вовсе нет. Все-таки существуют способы определения значения поля со свойством IDENTITY у новой записи.
Как определить значение поля со свойством IDENTITY в новой записи
Собственно, есть три принципиальные стратегии определения значения поля со свойством IDENTITY в новой, только что созданной, записи
Теперь рассмотрим более подробно достоинства и недостатки каждой стратегии
Значение, возвращаемое системной переменной @@IDENTITY
В MS SQL сервере есть ряд системных переменных, значение которых изменяется автоматически при наступлении определенных событий. В частности, значение системной переменной @@IDENTITY автоматически устанавливается равным значению поля со свойством IDENTITY последней созданной записи в текущем соединении. Т.е. создание новых записей в другом соединении (другим пользователем) никак не повлияет на ее значение в данном соединении.
Ну, так вот оно, решение. Просто после создания новой записи читаем значение системной переменной @@IDENTITY и имеем искомое значение.
В целом, верно. Проблема только в том, что системная переменная @@IDENTITY меняет свое значение при создании записи в любой таблице.
На практике, это означает, что если на таблицу установлен триггер на вставку, в теле которого дается команда INSERT на создание записи в другой таблице, которая, в свою очередь, также имеет поле со свойством IDENTITY, то системная переменная @@IDENTITY получит значение поля из этой второй таблицы.
Другими словами, опираться на значение системной переменной @@IDENTITY можно, но, помня о том, что эта переменная не привязана к значению поля одной таблицы.
Значение, возвращаемое функцией SCOPE_IDENTITY()
В версии MS SQL 2000 была введена системная функция SCOPE_IDENTITY(). Эта функция также возвращает значение поля со свойством IDENTITY последней созданной записи, но созданной в пределах текущего SCOPE.
Адекватно перевести термин SCOPE на русский язык достаточно сложно. Но, приблизительно, можно сказать так: SCOPE - это одна процедура или функция. Другими словами, SCOPE_IDENTITY() вернет значение поля со свойством IDENTITY последней записи созданной в пределах той процедуры, где эта функция была вызвана.
Триггер - это уже другой SCOPE (другая функция), поэтому он никак не повлияет на значение возвращаемое SCOPE_IDENTITY().
Даже если два пользователя одновременно вызвали одну и ту же процедуру, но каждый вызвал процедуру в своем SCOPE. Т.е. опять-таки нет конфликта.
К недостаткам этой функции относится то, что ее следует вызывать в пределах того SCOPE, где и была создана новая запись интересующей нас таблицы. А это не всегда возможно.
Другими словами, для корректного использования SCOPE_IDENTITY() необходимо всегда следить за областью действия SCOPE. Зачастую, создавая специальные процедуры.
Нахождение новой записи по значению других полей
Если помните, то основная проблема с определением значения поля со свойством IDENTITY заключается в том, что данное поле используется в качестве первичного ключа. Т.е. по его значению как раз и находят нужную запись.
Однако, зачастую, таблицы имеют поле или набор полей, по которым также можно однозначно определить запись. Например, если речь идет о справочнике, то, разумеется, справочник имеет поле "Название". Также очевидно, что это поле должно быть уникально в пределах справочника. Иначе просто теряется смысл использования самого справочника. Зачем вводить в справочник записи с одинаковым значением?
Почему же не использовать в качестве первичного ключа это самое "Название"? Зачем вообще нужно поле со свойством IDENTITY? Это тема отдельного разговора. Вкратце, "Название" - это для пользователя (внешние данные), а IDENTITY - это для обеспечения ссылочной целостности базы данных (внутренние данные).
Значение поля со свойством IDENTITY в новой записи не известно. Но значение поля "Название" в этой новой записи вполне известно. Пользователь сам же его и ввел! Значит, после создания новой записи можно найти эту новую запись по значению поля "Название" и прочитать значение поля со свойством IDENTITY.
Проблема только в том, что далеко не всегда существует такое поле или набор полей для однозначной идентификации записи. Это, кстати, одна из причин ввода, так называемых, суррогатных ключей. Тех самых полей со свойством IDENTITY.
Если, тем не менее, Вы решите использовать эту стратегию для поиска новой записи, то обязательно наложите на поле "Название" (или выбранный Вами набор полей) ограничение по уникальности. Т.е., чтобы случайно не оказалось двух записей с одинаковым значением в этом поле.
Как работать с полями со свойством IDENTITY в FoxPro
С теоретической частью закончили, теперь "попробуем со всем этим добром взлететь". Т.е. определимся, как же воспользоваться всеми этими знаниями в FoxPro. Еще раз уточним задачу, которую необходимо решить.
Добавляется запись в таблицу MS SQL сервера имеющую поле со свойством IDENTITY. Необходимо сразу после создания новой записи получить значение поля со свойством IDENTITY на стороне FoxPro.
У FoxPro есть три принципиальные возможности организации работы с MS SQL сервером
- Использование Remote View
- Использование Cursor Adapter
Тут следует остановиться на том, какое именно событие собственно создает запись на MS SQL сервер. Ну, с Pass-Trough все ясно. Это собственно прямая команда серверу создать новую запись. А вот с Remote View и Cursor Adapter несколько иначе.
Результатом работы как Remote View, так и Cursor Adapter является курсор. Т.е. некая временная таблица, физически расположенная на машине клиента. По умолчанию, этот курсор автоматически открывается в режиме оптимистической буферизации строк (3) и может быть переключен только в режим оптимистической буферизации таблиц (5). Переключиться в режим пессимистической буферизации или отключить буферизацию совсем для этого курсора невозможно
Следовательно, новая запись сначала физически будет создана именно на клиентской машине в этом самом курсоре. Точнее, в буфере этого курсора. Физическое создание записи на MS SQL сервере произойдет только после сброса буфера.
Для строковой буферизации сброс буфера может произойти автоматически при выполнении одного из следующих действий:
- Переход (или попытка перехода) на другую запись
- Закрытие курсора
- Переключение в режим табличной буферизации
- По команде TableUpdate()
Для табличной буферизации сброс буфера может произойти только по команде TableUpdate() и никак иначе.
Никакие другие действия и операции ни с Remote View, ни с Cursor Adapter не приведут к созданию новой записи на MS SQL сервере. Если при выполнении какой-либо операции оказалось, что на MS SQL сервере создалась новая запись, это означает, что курсор находился в режиме строковой буферизации, и произошло одно из событий вызвавших автоматический сброс буфера.
Например, такое может произойти по команде Requery() для Remote View. Но это вовсе не означает, что команда Requery() сбрасывает буфер. Вовсе нет. Просто одним из условий выполнения команды Requery() является закрытие ранее существовавшего курсора. А вот это событие как раз и вызовет автоматический сброс буфера, если курсор находится в режиме строковой буферизации.
Чтобы избежать подобных недоразумений, переключайте курсор в режим табличной буферизации (5). В этом случае Вы всегда сможете контролировать процесс сброса буфера.
Однако следует понимать, что даже если Вы установите режим табличной буферизации, измените несколько записей в Remote View или Cursor Adapter, а потом дадите команду TableUpdate() все равно сброс буфера будет происходить по одной записи за раз. Т.е. на сервер будет послана не одна команда, например, на модификацию, а набор команд по модификации каждой записи в отдельности.
Применительно к операциям создания новой записи из этого следует вывод, что во всех событиях объекта Cursor Adapter всегда происходит вставка только одной записи за раз.
Прямое использование Pass-Through технологии через функцию SQLEXEC()
При таком способе работы программист как бы напрямую работает с MS SQL сервером. Сам формирует все команды, отсылаемые на сервер, получает результат, и сам же его обрабатывает. В этом случае не составляет никакого труда послать дополнительный запрос серверу на значение функции SCOPE_IDENTITY
LOCAL lcNewValue, lnResut lcNewValue = "Новое значение" lnResut = SQLExec(m.lnConnectHandle,"INSERT INTO MyTab (Field1) VALUES (?m.lcNewValue)") IF m.lnResut>0 SQLExec(m.lnConnectHandle,"SELECT NewIdent=SCOPE_IDENTITY()","NewIdent") ?NewIdent.NewIdent ELSE LOCAL laError(1) =AERROR(laError) * Анализ массива laError для уточнения причины ошибки ENDIF
В данном примере m.lnConnectHandle - это число, номер соединения с MS SQL сервером, которое настраивается раньше. MyTab - это таблица, которая имеет поле со свойством IDENTITY.
После выполнения второго запроса в результирующем курсоре NewIdent в поле NewIdent первой записи и получим искомое значение. В данном синтаксисе и команда вставки и вызов функции SCOPE_IDENTITY() происходят в одном SCOPE. Поэтому, получаем нужное значение.
Использование Remote View
Remote View - это некая "надстройка" над технологией Pass-Through. По сути, при создании новой записи выполняется та же команда INSERT INTO. Однако проблема в том, что даже если прочитать номер соединения, в котором работает Remote View, а потом выполнить запрос для определения значения, возвращаемого SCOPE_IDENTITY(), то получим NULL, поскольку в этом случае команда вставки и SCOPE_IDENTITY() выполняются в разных SCOPE. Следовательно, остается только два способа определения значения поля со свойством IDENTITY.
* Определение значения системной переменной @@IDENTITY LOCAL lnConnectHandle lnConnectHandle = CursorGetProp("ConnectHandle","MyRemoteView") SQLExec(m.lnConnectHandle,"SELECT NewIdent=@@IDENTITY","NewIdent") ?NewIdent.NewIdent * Определение по значению другого поля LOCAL lnConnectHandle, lcNickName lnConnectHandle = CursorGetProp("ConnectHandle","MyRemoteView") lcNickName = MyRemoteView.NickName SQLExec(m.lnConnectHandle,"SELECT TabId FROM MyTab WHERE NickName=?lcNickName","NewIdent") ?NewIdent.TabId
В обоих примерах MyRemoteView - это имя Вашего Remote View. NickName - это имя поля в Remote View по значению которого выполняется поиск новой записи, а TabID - это то самое поле со свойством IDENTITY значение которого и надо определить
Предполагается, что создание новой записи уже произошло. Либо явно была дана команда TableUpdate(), либо Remote View находится в режиме строковой буферизации, и была попытка перехода на другую запись.
А почему во втором случае не было использовано, казалось бы, более очевидное решение поиска после Requery()? Что-то вроде
REQUERY("MyRemoteView") SELECT MyRemoteView LOCATE FOR NickName = "Новое значение" ?MyRemoteView.TabID
На это есть несколько причин.
Во-первых, Requery() предполагает выполнение запроса заново. Т.е. предполагается, что произошел сброс буфера всех записей Remote View. Но ведь это может быть не так. Например, сбрасывают буфер по одной записи за раз в цикле.
Во-вторых, обычно Remote View содержит в себе дополнительные условия отбора записей, и только что созданная запись может вообще не попасть в условия отбора. Т.е. после Requery() запись, хотя и будет физически создана на сервере, но в самом Remote View отображена не будет.
Использование Cursor Adapter
Объект Cursor Adapter был введен в FoxPro, начиная с версии Visual FoxPro 8. Он призван облегчить работу с удаленными данными при выполнении некоторых стандартных операций. В частности, операции создания новой записи. Через Cursor Adapter можно реализовать все три варианта получения значения поля со свойством IDENTITY в новой записи.
Значение, возвращаемое системной переменной @@IDENTITY
После того, как будет выполнен сброс буфера и произойдет физическая создание новой записи на MS SQL сервере, в объекте Cursor Adapter сработает событие AfterInsert. Вот в нем как раз и надо выполнить дополнительный запрос на сервер и прочитать значение системной переменной @@IDENTITY
PROCEDURE AfterInsert LPARAMETERS cFldState, lForce, cInsertCmd, lResult IF lResult=.T. && вставка произошла успешно. Надо получить значение ID LOCAL currentArea currentArea=SELECT() TRY IF 1=SQLEXEC(this.DataSource,"SELECT NewIdent=@@IDENTITY","NewIdent") REPLACE TabId WITH NewIdent.NewIdent IN (This.Alias) USE IN IDRes ELSE LOCAL laError(1) =AERROR(laError) * Анализ массива laError для уточнения причины ошибки ENDIF FINALLY SELECT (currentArea) ENDTRY ENDIF ENDPROC
Думаю, код достаточно понятный и не нуждается в пояснениях. После успешного создания новой записи на сервере при помощи SQLExec() для того же самого соединения определили значение @@IDENTITY и записали это значение в текущую запись курсора в ключевое поле.
В версии Visual FoxPro 9 в объект Cursor Adapter было добавлено свойство InsertCmdRefreshCmd . Это команда, которая посылается на сервер только после успешной вставки новой записи. Т.е. здесь нет необходимости убеждаться в самом факте создания новой записи.
В паре с другим новым свойством InsertCmdRefreshFieldList можно выполнить обновление, опираясь на значение системной переменной @@IDENTITY проще. Для этого всего лишь надо сделать следующие настройки объекта CursorAdapter
CursorAdapter.InsertCmdRefreshCmd = "SELECT @@IDENTITY" CursorAdapter.InsertCmdRefreshFieldList = "TabId"
Здесь TabId - это не имя поля со свойством IDENTITY в таблице собственно на MS SQL сервере, а имя поля курсора, полученного на стороне клиента в котором и отображается содержимое поля со свойством IDENTITY. Как правило, эти имена совпадают, но, в общем случае, могут и отличаться. Более подробно о свойстве InsertCmdRefreshFieldList читайте ниже. В разделе, посвященном нахождению новой записи по значению других полей.
Значение, возвращаемое функцией SCOPE_IDENTITY()
Здесь все несколько сложнее. Дело в том, что действовать, так же как и в случае определения системной переменной @@IDENTITY нельзя. SQLExec() и Cursor Adapter работают в разных SCOPE. Поэтому при подобном подходе SCOPE_IDENTITY() всегда будет возвращать NULL. Чтобы преодолеть это противоречие используют специальные процедуры.
Для начала, надо создать на самом MS SQL сервере вот такую хранимую процедуру
CREATE PROCEDURE Get_ValueInt @ValueIn Int, @ValueOut Int OUTPUT AS SET NOCOUNT ON SELECT @ValueOut=@ValueIn
Как видите, цель данной процедуры просто присвоить значение входного параметра выходному параметру. Теперь можно использовать эту процедуру для определения значения SCOPE_IDENTITY() в Cursor Adapter. Для этого в событии BeforeInsert делается такая подмена
PROCEDURE BeforeInsert LPARAMETERS cFldState, lForce, cInsertCmd cInsertCmd = cInsertCmd + ; "; DECLARE @id int" + ; "; SELECT @id=SCOPE_IDENTITY()" + ; "; EXEC Get_ValueInt @id, [email protected]" ENDPROC
Только следует иметь в виду, что в данной подмене MyTab - это уже не имя таблицы на MS SQL сервере, а имя курсора создаваемого у клиента. Точнее, то имя, которое записано в свойстве Alias собственно Cursor Adapter. Соответственно, "TabId" - это имя поля курсора, созданного у клиента и содержащего значение поля со свойством IDENTITY
В данном случае, по сути, формируется динамическая хранимая процедура. Т.е. не просто одна команда INSERT, а последовательность команд. Команды отделяются друг от друга символом точки с запятой, хотя это вовсе не обязательно. Достаточно отделять команды друг от друга простым пробелом.
Нахождение новой записи по значению других полей
Если у Вас Visual FoxPro 8, то Вам остается использовать метод аналогичный методу, примененному для поиска значения системной переменной @@IDENTITY. Т.е. в методе AfterInsert объекта Cursor Adapter выполнить дополнительный запрос через SQLExec()
Начиная с версии Visual FoxPro 9, в объекте Cursor Adapter появились дополнительные свойства, позволяющие автоматизировать эту процедуру без написания дополнительного кода
InsertCmdRefreshFieldList
- список полей, значение которых будет обновлено после Insert
InsertCmdRefreshKeyFieldList
- поле, или набор НЕ ключевых полей также однозначно идентифицирующих запись, по которым и будет осуществлен поиск новой записи
Следует отметить, что в этих свойствах указываются не имена полей таблицы собственно MS SQL сервера, а соответствующие им имена полей курсора, полученного на стороне клиента. Применительно к тому же примеру это будет примерно так:
InsertCmdRefreshFieldList = "TabID" InsertCmdRefreshKeyFieldList = "NickName"
Другими словами, опираясь на значение поля NickName, будет найдена запись в таблице на MS SQL сервере и прочитано значение поля TabID, после чего это значение будет записано в новую запись на стороне клиента.
Более подробно как обсуждение данной темы, так и примеры использования Вы можете посмотреть здесь
Владимир Максимов
Последнее обновление: 01.05.06