یکی از قابلیتهای جالب SQL Server در یک شبکه محلی امکان link و اتصال آنها به یکدیگر است. به این صورت امکان کوئری گرفتن (و یا اعمال متداول SQL ایی) از دو یا چند سرور مختلف با دستورات T-SQL میسر میشود؛ به نحوی که حس یکپارچگی دیتابیسهای این سرورها را حین کوئری نوشتن خواهیم داشت.
برای مثال فرض کنید دو سرور SQL1 و SQL2 را در شبکه داریم. میخواهیم در سرور SQL1 اتصالی را به سرور SQL2 ایجاد کنیم.
USE master
EXEC sp_addlinkedserver
'SQL2',
N'SQL Server'
sp_addlinkedsrvlogin @useself='false ', @rmtsrvname = 'SQL2',
@rmtuser = 'sa',
@rmtpassword = 'pass#'
دستورات T-SQL فوق کار ثبت یک liked server جدید و اعمال مشخصات کاربری که توسط آن قرار است به سرور SQL2 دسترسی داشت، انجام میدهند.
اکنون جهت بررسی این اتصال در سرور SQL1 کوئری زیر را اجرا میکنیم:
select * from sql2.faxManager.dbo.tblErja
که نحوهی فراخوانی جدول مورد نظر باید به صورت Server.DatabaseName.dbo.TableName در آن رعایت شود.
تا اینجا همه چیز خوب است. مشکل از زمانی شروع میشود که بخواهیم تراکنشها را نیز دخالت دهیم و اصولی کار کنیم. برای مثال:
begin distributed tran
select * from sql2.faxManager.dbo.tblErja
commit tran
خطایی که در ویندوز سرور 2003 با آخرین به روز رسانیها ظاهر میشود به صورت زیر است:
OLE DB provider for linked server returned message "The partner transaction manager has disabled its support for remote/network transactions.".
به صورت پیش فرض این نوع تراکنشهای توزیع شده غیرفعال هستند مگر اینکه فعال شوند و روش حل مشکل نیز به صورت زیر میباشد:
قبل از هر کاری به کنسول سرویسهای ویندوز مراجعه کرده و از در حال اجرا بودن سرویس Distribute Transaction Coordinator اطمینان حاصل کنید.
سپس به قسمت زیر مراجعه نمائید:
نود مربوط به Component Service را گشوده و سپس بر روی My Computer کلیک راست کرده و گزینهی خواص را انتخاب کنید.
در صفحهی بازه شده به برگهی MSTDC مراجعه کرده و بر روی دکمهی Security Configuration کلیک نمائید.
اکنون تنظیمات آنرا مطابق شکل زیر تغییر دهید.
این تنظیم باید بر روی هر دو سرور SQL1 و SQL2 انجام شود.
پس از این تغییرات که شامل راه اندازی مجدد سرویس Distribute Transaction Coordinator نیز خواهد شد، مشکل خطای فوق برطرف شده و امکان استفاده از تراکنشها در linked servers نیز میسر میشود.
مشکل دیگری که به آن برخوردم خطای زیر است:
OLE DB provider for linked server returned message "Cannot start more transactions on this session.".
برای حل این مشکل یک سطر زیر را باید به ابتدای کوئری خود اضافه کرد که جزو الزامات تراکنشهای توزیع شده است و به این صورت از rollback کامل تمامی دستورات موجود فراخوانی شده T-SQL در صورت بروز کوچکترین خطایی اطمینان حاصل میکند:
SET XACT_ABORT ON
برای مطالعه بیشتر:
MSDTC Troubleshooting
اگر با SQL Server کار کرده باشید حتما با مفهوم و امکان Computed columns (فیلدهای محاسبه شده) آن آشنایی دارید. چقدر خوب میشد اگر این امکان برای سایر بانکهای اطلاعاتی که از تعریف فیلدهای محاسبه شده پشتیبانی نمیکنند، نیز مهیا میشد. زیرا یکی از اهداف مهم استفادهی صحیح از ORMs ، مستقل شدن برنامه از نوع بانک اطلاعاتی است. برای مثال امروز میخواهیم با MySQL کار کنیم، ماه بعد شاید بخواهیم یک نسخهی سبکتر مخصوص کار با SQLite را ارائه دهیم. آیا باید قسمت دسترسی به داده برنامه را از نو بازنویسی کرد؟ اینکار در NHibernate فقط با تغییر نحوهی اتصال به بانک اطلاعاتی میسر است و نه بازنویسی کل برنامه (و صد البته شرط مهم و اصلی آن هم این است که از امکانات ذاتی خود NHibernate استفاده کرده باشید. برای مثال وسوسهی استفاده از رویههای ذخیره شده را فراموش کرده و به عبارتی ORM مورد استفاده را به امکانات ویژهی یک بانک اطلاعاتی گره نزده باشید).
خوشبختانه در NHibernate امکان تعریف فیلدهای محاسباتی با کمک تعریف نگاشت خواص به صورت فرمول مهیا است. برای توضیحات بیشتر لطفا به مثال ذیل دقت بفرمائید:
در ابتدا کلاس کاربر تعریف میشود:
using System;
using NHibernate.Validator.Constraints;
namespace FormulaTests.Domain
{
public class User
{
public virtual int Id { get; set; }
[NotNull]
public virtual DateTime JoinDate { set; get; }
[NotNullNotEmpty]
[Length(450)]
public virtual string FirstName { get; set; }
[NotNullNotEmpty]
[Length(450)]
public virtual string LastName { get; set; }
[Length(900)]
public virtual string FullName { get; private set; } //از طریق تعریف فرمول مقدار دهی میگردد
public virtual int DayOfWeek { get; private set; }//از طریق تعریف فرمول مقدار دهی میگردد
}
}
using FluentNHibernate.Automapping;
using FluentNHibernate.Automapping.Alterations;
namespace FormulaTests.Domain
{
public class UserCustomMappings : IAutoMappingOverride<User>
{
public void Override(AutoMapping<User> mapping)
{
mapping.Id(u => u.Id).GeneratedBy.Identity(); //ضروری است
mapping.Map(x => x.DayOfWeek).Formula("DATEPART(dw, JoinDate) - 1");
mapping.Map(x => x.FullName).Formula("FirstName + ' ' + LastName");
}
}
}
اکنون اگر Fluent NHibernate را وادار به تولید اسکریپت متناظر با این دو کلاس کنیم حاصل به صورت زیر خواهد بود:
create table Users (
UserId INT IDENTITY NOT NULL,
JoinDate DATETIME not null,
FirstName NVARCHAR(450) not null,
LastName NVARCHAR(450) not null,
primary key (UserId)
)
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
default-access="property" auto-import="true" default-cascade="none" default-lazy="true">
<class xmlns="urn:nhibernate-mapping-2.2" mutable="true"
name="FormulaTests.Domain.User, FormulaTests, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="Users">
<id name="Id" type="System.Int32" unsaved-value="0">
<column name="UserId" />
<generator class="identity" />
</id>
<property name="DayOfWeek" formula="DATEPART(dw, JoinDate) - 1" type="System.Int32" />
<property name="FullName" formula="FirstName + ' ' + LastName" type="System.String" />
<property name="JoinDate" type="System.DateTime">
<column name="JoinDate" />
</property>
<property name="FirstName" type="System.String">
<column name="FirstName" />
</property>
<property name="LastName" type="System.String">
<column name="LastName" />
</property>
</class>
</hibernate-mapping>
var list = session.Query<User>.ToList();
foreach (var item in list)
{
Console.WriteLine("{0}:{1}", item.FullName, item.DayOfWeek);
}
select
user0_.UserId as UserId0_,
user0_.JoinDate as JoinDate0_,
user0_.FirstName as FirstName0_,
user0_.LastName as LastName0_,
DATEPART(user0_.dw, user0_.JoinDate) - 1 as formula0_, --- همان فرمول تعریف شده است
user0_.FirstName + ' ' + user0_.LastName as formula1_ ---از طریق فرمول تعریف شده حاصل گردیده است
from
Users user0_
C# 7 - Tuple return types and deconstruction
تبدیل خروجیهای dynamic قبلی
// query dapper via "dynamic" int id = ... var row = conn.QuerySingle("select Name, LastUpdated from Records where Id=@id", new {id}); string name = row.Name; // "dynamic"
// query dapper via C# 7 tuples int id = ... var row = conn.QuerySingle<(string name, DateTime lastUpdated)>( "select Name, LastUpdated from Records where Id=@id", new {id}); // use row.name and row.lastUpdated
Stored Procedure چیست ؟
Stored Procedure یا SP یا به زبان فارسی «رویههای ذخیره شده» اشیایی اجرا پذیر در بانک اطلاعاتی SQL Server هستند که شامل یک یا چندین دستور SQL میشوند. این رویهها میتوانند پارامترهای ورودی و خروجی داشته باشند؛ همچنین میتوانند لیستی از موجودیتها را نیز برگردانند و یا میتوان داخل این رویهها به زبان T-SQL برنامه نویسی کرد.
مهمترین کاربر این رویهها، ذخیره کردن دستورات Select , Insert , Update , Delete هست و یا ترکیبی از اینها .
اشکال راه حلهای پیش فرض مبتنی بر Context
برای استفاده از راه حلهای پیش فرض مبتنی بر Context، همانطور که در مقاله «استفاده از امکانات بومی بانکهای اطلاعاتی» به آن پرداخته شده، سه روش کلی برای استفاده از Stored Procedure پیشنهاد شدهاست:
- روش اول استفاده از متد fromsql است. اشکال این متد، محدودیت استفاده برای یک موجودیت برنامه است و به زبان ساده نمیتوان در کوئری پایگاه داده از join استفاده کرد.
- روش دوم استفاده از متد ExecuteSqlCommand موجود در context برنامه است . اشکال این متد void بودن آن است که باعث میشود بازگشتی از پایگاه داده حاصل نشود.
- روش سوم استفاده از متد ExecuteScalar موجود در Context برنامه است. اشکالی که به این متد گرفته میشود، Scalar بودن مقدار بازگشتی از آن است که باعث میشود نتوانیم لیستی از موجودیتها را به ViewModel مورد نظر نگاشت کنیم.
راه حل این مشکل
برای حل این مشکلات که بسیار هم مهم هستند، اول باید قطعه کد زیر را به Context برنامه اضافه نمود:
public void OpenConnection() { Database.OpenConnection(); } public DbCommand Command() { DbCommand cmd = Database.GetDbConnection().CreateCommand(); return cmd; }
void OpenConnection(); DbCommand Command();
public void ConfigureServices(IServiceCollection services) { services.AddScoped<IUnitOfWork, ApplicationDbContext>(); services.AddScoped<ISpReader, SpReader>(); }
public List<ViewModel> GetFromSp <ViewModel>(string[,] Parametr, string NameSp) where ViewModel : new()
{ _uow.OpenConnection(); DbCommand cmd = _uow.Command(); cmd.CommandText = NameSp; cmd.CommandType = CommandType.StoredProcedure; var countParametr = Parametr.GetLength(0); for (int i = 0; i < countParame tr; i++) { cmd.Parameters.Add(new SqlParameter { ParameterName = Parametr[i, 0], Value = Parametr[i, 1] }); } List<ViewModel> list = new List<ViewModel >(); using (var reader = cmd.ExecuteReader()) { if (reader != null && reader.HasRows) { var entity = typeof(ViewModel); var propDict = new Dictionary<string, PropertyInfo>(); var props = entity.GetProperties (BindingFlags.Instance | BindingFlags.Public); propDict = props.ToDictionary(p => p.Name.ToUpper(), p => p); while (reader.Read()) { ViewModel newobject = new ViewModel (); for (int index = 0; index < reader.FieldCount; index++) { if (propDict.ContainsKey(reader.GetName(index).ToUpper())) { var info = propDict[reader.GetName(index).ToUpper()]; if ((info != null) && info.CanWrite) { var val = reader.GetValue(index); info.SetValue(newobject, (val == DBNull.Value) ? null : val, null); } } } list.Add(newobject); } } return list; }
همچنین میتوان برای استفاده این متد برای رویههای بدون پارامتر ورودی، از OverLoad این متد، با حذف قطعات کد زیر:
var countParametr = Parametr.GetLength(0); for (int i = 0; i < countParametr; i++) { cmd.Parameters.Add(new SqlParameter { ParameterName = Parametr[i, 0], Value = Parametr[i, 1] }); }
روش استفاده از این متد
برای استفاده از این متد، لازم است چند نکته رعایت شوند:
1- خروجی Stored Procedure دقیقا منطبق بر ViewModel ارسالی به متد جهت تشکیل لیست باشد.
2- لیست پارامترها باید بصورت آرایه دوبعدی باشد که اندازه بعد اول، تعداد پارامترها و اندازه بعد دوم 2 باشد.
3- در ماتریسی که از این پارامترها ساخته میشود، ستون اول نام پارامتر و ستون دوم مقدار پارامتر ست میشود.
بطور مثال Stored Procedure زیر حاوی سه پارامتر است :
CREATE PROCEDURE [dbo].[isRelation]( @TableName as varchar(50), @FieldOfRelation as varchar(70), @ValueOfField as int)
public class EntityServices : IEntityService { private ISpreader _Reader; public EntityServices( ISpreader reader) { _Reader = reader; } public List<StoreProcedureResultViewModel> IsRelation(string tableName , int keyValue, string keyFieldName) { List<StoreProcedureResultViewModel> IsContact; try { string[,] Parametr = new string[3, 2]; Parametr[0, 0] = "@TableName"; Parametr[0, 1] = tableName ; Parametr[1, 0] = "@ValueOfField"; Parametr[1, 1] = keyValue.ToString().Trim(); Parametr[2, 0] = "@FieldOfRelation"; Parametr[2, 1] = keyFieldName.Trim(); IsContact = _Reader.GetSp<StoreProcedureResultViewModel>(Parametr, "IsRelation"); return IsContact; } catch (Exception ex) { } } }
بر این اساس، کلاسهای مدل دومین مساله به صورت زیر خواهند بود:
public class Project { public int Id { set; get; } public string Name { set; get; } public virtual ICollection<ProjectIssue> ProjectIssues { set; get; } } public class ProjectIssue { public int Id { set; get; } public string Body { set; get; } [ForeignKey("ProjectStatusId")] public virtual ProjectIssueStatus ProjectIssueStatus { set; get; } public int ProjectStatusId { set; get; } [ForeignKey("ProjectId")] public virtual Project Project { set; get; } public int ProjectId { set; get; } } public class ProjectIssueStatus { public int Id { set; get; } public string Name { set; get; } public virtual ICollection<ProjectIssue> ProjectIssues { set; get; } }
اگر EF Code first را وادار به تهیه جداول و روابط معادل کلاسهای فوق کنیم:
public class MyContext : DbContext { public DbSet<ProjectIssueStatus> ProjectStatus { get; set; } public DbSet<ProjectIssue> ProjectIssues { get; set; } public DbSet<Project> Projects { get; set; } } public class Configuration : DbMigrationsConfiguration<MyContext> { public Configuration() { AutomaticMigrationsEnabled = true; AutomaticMigrationDataLossAllowed = true; } protected override void Seed(MyContext context) { var project1 = new Project { Name = "پروژه جدید" }; context.Projects.Add(project1); var stat1 = new ProjectIssueStatus { Name = "درحال انجام" }; var stat2 = new ProjectIssueStatus { Name = "انجام شد" }; context.ProjectStatus.Add(stat1); context.ProjectStatus.Add(stat2); var issue1 = new ProjectIssue { Body = "تغییر قلم گزارش", ProjectIssueStatus = stat1, Project = project1 }; var issue2 = new ProjectIssue { Body = "تغییر لوگوی گزارش", ProjectIssueStatus = stat1, Project = project1 }; context.ProjectIssues.Add(issue1); context.ProjectIssues.Add(issue2); base.Seed(context); } }
سابقا برای یافتن تعداد متناظر با هر IssueStatus خیلی سریع میشد چنین کوئری را نوشت:
اما اکنون معادل آن با EF Code first چیست؟
public static class Test { public static void RunTests() { Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, Configuration>()); using (var ctx = new MyContext()) { var projectId = 1; var list = ctx.ProjectStatus.Select(x => new { Id = x.Id, Name = x.Name, Count = x.ProjectIssues.Count(p => p.ProjectId == projectId) }).ToList(); foreach (var item in list) Console.WriteLine("{0}:{1}",item.Name, item.Count); } } }
SELECT [Project1].[Id] AS [Id], [Project1].[Name] AS [Name], [Project1].[C1] AS [C1] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], ( SELECT COUNT(1) AS [A1] FROM [dbo].[ProjectIssues] AS [Extent2] WHERE ([Extent1].[Id] = [Extent2].[ProjectStatusId]) AND ([Extent2].[ProjectId] = 1 /*@p__linq__0*/) ) AS [C1] FROM [dbo].[ProjectIssueStatus] AS [Extent1] ) AS [Project1]
1- مقدمه
پارتیشن بندی در بانک اطلاعاتی SQL Server، از ویژگیهایی است که از نسخه 2005، به این محصول اضافه شده است. بکارگیری این قابلیت که با Split کردن، محتوای یک جدول و قرار دادن آنها در چندین فایل، برای جداول حجیم، به ویژه جداولی که دادههای آن حاوی مقادیر تاریخچهای است، بسیار سودمند است.سادگی در مدیریت دادهها و شاخصهای موجود یک جدول (از قبیل اندازه فضای ذخیره سازی و استراتژی جدید Back up گیری)، اجرای سریعتر کوئری هایی که روی یک محدوده از دادهها کار میکنند و سهولت در آرشیو دادههای قدیمی یک جدول، از قابلیتهایی است که استفاده از این ویژگی بوجود میآورد.
محدوده استفاده از این ویژگی روی یک بانک اطلاعاتی و در یک Instance است. بنابراین مباحث مرتبط با معماری Scalability را پوشش نمیدهد و صرفاً Solution ایی است که در یک Instance بانک اطلاعاتی استفاده میشود.
2- Data File و Filegroup
هر بانک اطلاعاتی در حالت پیش فرض، شامل یک فایل دادهای (MDF.) و یک فایل ثبت تراکنشی (LDF.) میباشد. میتوان جهت ذخیره سطرهای دادهای از فایلهای بیشتری تحت نام فایلهای ثانویه (NDF.) استفاده نمود. به همان طریق که در فایل سیستم، فایلها به پوشهها تخصیص داده میشوند، میتوان Data File را به Filegroup تخصیص داد. چنانچه چندین Data File به یک Filegroup تخصیص داده شوند، دادهها در تمامی Data Fileها به طریق Round-Robin توزیع میشوند.3- Partition Function
مطابق با مقادیر تعریف شده در بدنه دستور، محدوده دادهای (پارتیشنها) با استفاده از Partition Function ایجاد میشود. با در نظر گرفتن ستونی که به عنوان Partition Key انتخاب شده، این تابع یک Data Type را به عنوان ورودی دریافت میکند. در هنگام تعریف محدوده برای پارتیشنها، به منظور مشخص کردن محدوده هر پارتیشن از Right و Left استفاده میشود.Left نمایش دهندهی حد بالای هر محدوده است و به طور مشابه، Right برای مشخص کردن حد پائین آن محدوده استفاده میشود. به منظور درک بهتر، به شکل زیر توجه نمائید:
همانطور که مشاهده میشود، همواره نیاز به یک Filegroup اضافهتری از آنچه مورد نظرتان در تعریف تابع است، میباشد. بنابراین اگر Function دارای n مقدار باشد، به n+1 مقدار برای Filegroup نیاز است.
همچنین هیچ محدودیتی برای اولین و آخرین بازه در نظر گرفته نمیشود. بنابراین جهت محدود کردن مقادیری که در این بازهها قرار میگیرند، میتوان از Check Constraint استفاده نمود.
3-1- Right or Left
یک سوال متداول اینکه از کدام مورد استفاده شود؟ در پاسخ باید گفت، به چگونگی تعریف پارتیشن هایتان وابسته است. مطابق شکل، تنها تفاوت این دو، در نقاط مرزی هر یک از پارتیشنها میباشد. در بیشتر اوقات هنگام کار با دادههای عددی میتوان از Left استفاده نمود و بطور مشابه هنگامیکه نوع دادهها از جنس زمان است، میتوان از Right استفاده کرد.
4- Partition Schema
گام بعدی پس از ایجاد Partition Function، تعریف Partition Schema است، که به منظور قرار گرفتن هر یک از پارتیشنهای تعریف شده توسط Function در Filegroupهای مناسب آن استفاده میشود.
5- Partition Table
گام پایانی ایجاد یک جدول، استفاده از Partition Schema است، که دادهها را با توجه به رویه درون Partition Function مورد استفاده، ذخیره میکند. همانطور که میدانید هنگام ایجاد یک جدول، میتوان مکان ذخیره شدن آنرا مشخص نمود.
Create Table <name> (…) ON …
در هنگام ایجاد یک جدول، معمولاً جدول در Filegroup پیش فرض که PRIMARY است، قرار میگیرد. میتوان با نوشتن نام Partition Schema و همچنین Partition Key که پیشتر ذکر آن رفت، بعد از بخش ON، برای جدول مشخص نمائیم که دادههای آن به چه ترتیبی ذخیره شوند. ارتباط این سه به شرح زیر است:
توجه شود زمانیکه یک Primary Key Constraint به یک جدول اضافه میشود، یک Unique Clustered Index نیز همراه با آن ساخته میشود. چنانچه Primary Key شامل یک Clustered Index باشد، جدول با استفاده از این ستون (ستونهای) شاخص ذخیره خواهد شد، در حالیکه اگر Primary Key شامل یک Non Clustered Index باشد، یک ساختار ذخیره-سازی اضافی ایجاد خواهد شد که دادههای جدول در آن قرار خواهند گرفت.
6- Index & Data Alignment
به عنوان یک Best Practice هنگام ایجاد یک Partition Table به منظور پارتیشن بندی، از ساختار Aligned Index استفاده شود. بدین ترتیب که تعریف Index، شامل Partition Key (ستونی که معیاری برای پارتیشن بندی است) باشد. چنانچه این عمل انجام شود، دادههای ذخیره شده مرتبط با هر پارتیشن متناظر با همان شاخص، در فایل دادهای (NDF.) ذخیره خواهند شد. از این رو چنانچه کوئری درخواست شده از جدول روی یک محدوده باشد
Where [OrderDate] Between …
بدین ترتیب برای بهرمندی از این مزایا، استفاده از Aligned Index توصیه شده است.
7- Operations
از نیازمندیهای متداول در پارتیشنینگ میتوان به افزودن، حذف پارتیشنها و جابجایی محتوای یک پارتیشن که برای عملیات آرشیو استفاده میشود، اشاره کرد.
7-1- Split Partition
به منظور ایجاد یک محدوده جدید به پارتیشنها استفاده میشود. یک نکته مهم مادامی که عملیات انتقال دادهها به پارتیشن جدید انجام میگیرد، روی جدول یک قفل انحصاری قرار میگیرد و بدین ترتیب عملیات ممکن است زمانبر باشد.
به عنوان یک Best Practice همواره یک Partition خالی را Split نمائید و پس از آن اقدام به بارگذاری داده در آن نمائید.
به یاد داشته باشید پیش از انجام عملیات splitting روی Partition Function با تغییر در Partition Schema (و بکارگیری Next Used) مشخص نمائید چه محدودهای در این Filegroup جدید قرار خواهد گرفت.
7-2- Merge Partition
به منظور ادغام پارتیشنها استفاده میشود، چنانچه پارتیشن خالی نیست، برای عملیات ادغام مسائل Performance به علت اینکه در طول عملیات از Lock (قفل انحصاری) استفاده میشود، در نظر گرفته شود.
7-3- Switch Partition
چنانچه جدول و شاخصهای آن به صورت Aligned هستند، میتوانید از Switch in و Switch out استفاده نمائید. عملیات بدین ترتیب انجام میشود که بلافاصله محتوای یک پارتیشن یا جدول (Source) در یک پارتیشن خالی جدولی دیگر و یا یک جدول خالی (Target) قرار میگیرد. عملیات تنها روی Meta Data انجام میگیرد و هیچ داده ای منتقل نمیشود.
محدودیتهای بکارگیری به شرح زیر است:
- جدول یا پارتیشن Target باید حتماً خالی باشد.
- جداول Source و Target حتماً باید در یک Filegroup یکسان قرار داشته باشند.
- جدول Source باید حاوی Aligned Indexهای مورد نیاز Target و همچنین مطابقت در Filegroup را دارا باشد.
- چنانچه Target به عنوان یک پارتیشن است، اگر Source جدول است بایست دارای یک Check Constraint باشد در غیر این صورت چنانچه یک پارتیشن است باید محدوده آن در محدوده Target قرار گیرد.
8- بررسی یک سناریوی نمونه
در ابتدا یک بانک اطلاعاتی را به طریق زیر ایجاد میکنیم:
این بانک مطابق تصویر، شامل 3 عدد فایل گروپ (FG1، FG2 و FG3) و 3 عدد دیتا فایل (P1، P2 و P3) میباشد. Filegroup پیش فرض Primary است، که چنانچه در تعریف جداول به نام Partition Schema و Partition Key مرتبط اشاره نشود، به طور پیش فرض در Filegroup موسوم به Primary قرار میگیرد. چنانچه چک باکس Default انتخاب شود، همانطور که قابل حدس زدن است، آن Filegroup در صورت مشخص نکردن نام Filegroup در تعریف جدول، به عنوان مکان ذخیره سازی انتخاب میشود. چک باکس Read Only نیز همانطور که از نامش پیداست، چنانچه روی یک Filegroup تنظیم گردد، عملیات مربوط به Write روی دادههای آن قابل انجام نیست و برای Filegroup هایی که جنبه نگهداری آرشیو را دارند، قابل استفاده است.
چنانچه Filegroup ای را از حالت Read Only دوباره خارج کنیم، میتوان عملیات Write را دوباره برای آن انجام داد.
پس از ایجاد بانک اطلاعاتی، گام بعدی ایجاد یک Partition Function و پس از آن یک Partition Schema است. همانطور که مشاهده میکنید در Partition Function از سه مقدار استفاده شده، بنابراین در Partition Schema باید از چهار Filegroup استفاده شود، که در مثال ما از Filegroup پیش فرض که Primary است، استفاده شده است.
USE [PartitionDB] GO CREATE PARTITION FUNCTION pfOrderDateRange(DATETIME) AS RANGE LEFT FOR VALUES ('2010/12/31','2011/12/31','2012/12/31') GO CREATE PARTITION SCHEME psOrderDateRange AS PARTITION pfOrderDateRange TO (FG1,FG2,FG3,[PRIMARY]) GO
پس از طی گامهای قبل، به ایجاد یک جدول به صورت Aligned Index مبادرت ورزیده میشود.
CREATE TABLE Orders ( OrderID INT IDENTITY(1,1) NOT NULL, OrderDate DATETIME NOT NULL, OrderFreight MONEY NULL, ProductID INT NULL, CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID ASC, OrderDate ASC) ON psOrderDateRange (OrderDate) ) ON psOrderDateRange (OrderDate) GO
در ادامه برای بررسی درج اطلاعات در پارتیشن با توجه به محدوده آنها اقدام به افزودن رکوردهایی در جدول ساخته شده مینمائیم.
SET NOCOUNT ON DECLARE @OrderDate DATETIME DECLARE @X INT SET @OrderDate = '2010/01/01' SET @X = 0 WHILE @X < 300 BEGIN INSERT dbo.Orders ( OrderDate, OrderFreight, ProductID) VALUES( @OrderDate + @X, @X + 10, @X) SET @X = @X + 1 END GO SET NOCOUNT ON DECLARE @OrderDate DATETIME DECLARE @X INT SET @OrderDate = '2011/01/01' SET @X = 0 WHILE @X < 300 BEGIN INSERT dbo.Orders ( OrderDate, OrderFreight, ProductID) VALUES( @OrderDate + @X, @X + 10, @X) SET @X = @X + 1 END GO SET NOCOUNT ON DECLARE @OrderDate DATETIME DECLARE @X INT SET @OrderDate = '2012/01/01' SET @X = 0 WHILE @X < 300 BEGIN INSERT dbo.Orders ( OrderDate, OrderFreight, ProductID) VALUES( @OrderDate + @X, @X + 10, @X) SET @X = @X + 1 END GO
از طریق دستور Select زیر میتوان نحوه توزیع دادهها را در جدول مشاهده کرد.
USE [PartitionDB] GO SELECT OBJECT_NAME(i.object_id) AS OBJECT_NAME, p.partition_number, fg.NAME AS FILEGROUP_NAME, ROWS, au.total_pages, CASE boundary_value_on_right WHEN 1 THEN 'Less than' ELSE 'Less or equal than' END AS 'Comparition',VALUE FROM sys.partitions p JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id JOIN sys.partition_functions f ON f.function_id = ps.function_id LEFT JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id JOIN (SELECT container_id, SUM(total_pages) AS total_pages FROM sys.allocation_units GROUP BY container_id) AS au ON au.container_id = p.partition_id WHERE i.index_id < 2
خروجی دستور فوق به شرح زیر است:
در ادامه به ایجاد یک Filegroup جدید میپردازیم.
/* Query 2-3- Split a partition*/ -- Add FG4: ALTER DATABASE PartitionDB ADD FILEGROUP FG4 Go ALTER PARTITION SCHEME [psOrderDateRange] NEXT USED FG4 GO ALTER PARTITION FUNCTION [pfOrderDateRange]() SPLIT RANGE('2013/12/31') GO -- Add Partition 4 (P4) to FG4: GO ALTER DATABASE PartitionDB ADD FILE ( NAME = P4, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\P4.NDF' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) TO FILEGROUP [FG4] -- GO
و در ادامه به درج اطلاعاتی برای بررسی نحوه توزیع دادهها در Filegroup هایمان میپردازیم.
SET NOCOUNT ON DECLARE @OrderDate DATETIME DECLARE @X INT SET @OrderDate = '2013/01/01' SET @X = 0 WHILE @X < 300 BEGIN INSERT dbo.Orders ( OrderDate, OrderFreight, ProductID) VALUES( @OrderDate + @X, @X + 10, @X) SET @X = @X + 1 END GO SET NOCOUNT ON DECLARE @OrderDate DATETIME DECLARE @X INT SET @OrderDate = '2012/01/01' SET @X = 0 WHILE @X < 300 BEGIN INSERT dbo.Orders ( OrderDate, OrderFreight, ProductID) VALUES( @OrderDate + @X, @X + 10, @X) SET @X = @X + 1 END GO
جهت ادغام پارتیشنها به طریق زیر عمل میشود:
/* Query 2-4- Merge Partitions */ ALTER PARTITION FUNCTION [pfOrderDateRange]() MERGE RANGE('2010/12/31') Go
به منظور آرشیو نمودن اطلاعات به طریق زیر از Switch استفاده میکنیم. ابتدا یک جدول موقتی برای ذخیره رکوردهایی که قصد آرشیو آنها را داریم، ایجاد میکنیم. همانگونه که در تعریف جدول مشاهده میکنید، نام Filegroup ای که برای ساخت این جدول استفاده میشود، با Filegroup ای که قصد آرشیو اطلاعات آنرا داریم، یکسان است.
در ادامه میتوان مثلاً با ایجاد یک Temporary Table به انتقال این اطلاعات بدون توجه به Filegroup آنها پرداخت.
/* Query 2-5- Switch Partitions */ USE [PartitionDB] GO CREATE TABLE [dbo].[Orders_Temp]( [OrderID] [int] IDENTITY(1,1) NOT NULL, [OrderDate] [datetime] NOT NULL, [OrderFreight] [money] NULL, [ProductID] [int] NULL, CONSTRAINT [PK_OrdersTemp] PRIMARY KEY CLUSTERED ([OrderID] ASC,[OrderDate] ASC)ON FG2 ) ON FG2 GO USE [tempdb] GO CREATE TABLE [dbo].[Orders_Hist]( [OrderID] [int] NOT NULL, [OrderDate] [datetime] NOT NULL, [OrderFreight] [money] NULL, [ProductID] [int] NULL, CONSTRAINT [PK_OrdersTemp] PRIMARY KEY CLUSTERED ([OrderID] ASC,[OrderDate] ASC) ) GO USE [PartitionDB] GO ALTER TABLE [dbo].[Orders] SWITCH PARTITION 1 TO [dbo].[Orders_Temp] GO INSERT INTO [tempdb].[dbo].[Orders_Hist] SELECT * FROM [dbo].[Orders_Temp] GO DROP TABLE [dbo].[Orders_Temp] GO SELECT * FROM [tempdb].[dbo].[Orders_Hist]
- و فقط در نگارشهای SQL Server 2014 Enterprise, Developer, and Evaluation editions پشتیبانی میشود. برای مثال نگارش Standard این قابلیت را ندارد.
- هنگام نصب هم باید گزینهی «Database Engine Services -> install support for In-Memory OLTP engine» انتخاب شده باشد.
EF Code First #13
استفاده مستقیم از عبارات SQL در EF Code first
طراحی اکثر ORMهای موجود به نحوی است که برنامه نهایی شما را مستقل از بانک اطلاعاتی کنند و این پروایدر نهایی است که معادلهای صحیح بسیاری از توابع توکار بانک اطلاعاتی مورد استفاده را در اختیار EF قرار میدهد. برای مثال در یک بانک اطلاعاتی تابعی به نام substr تعریف شده، در بانک اطلاعاتی دیگری همین تابع substring نام دارد. اگر برنامه را به کمک کوئریهای LINQ تهیه کنیم، نهایتا پروایدر نهایی مخصوص بانک اطلاعاتی مورد استفاده است که این معادلها را در اختیار EF قرار میدهد و برنامه بدون مشکل کار خواهد کرد. اما یک سری از موارد شاید معادلی در سایر بانکهای اطلاعاتی نداشته باشند؛ برای مثال رویههای ذخیره شده یا توابع تعریف شده توسط کاربر. امکان استفاده از یک چنین تواناییهایی نیز با اجرای مستقیم عبارات SQL در EF Code first پیش بینی شده و بدیهی است در این حالت برنامه به یک بانک اطلاعاتی خاص گره خواهد خورد؛ همچنین مزیت استفاده از کوئریهای Strongly typed تحت نظر کامپایلر را نیز از دست خواهیم داد. به علاوه باید به یک سری مسایل امنیتی نیز دقت داشت که در ادامه بررسی خواهند شد.
کلاسهای مدل مثال جاری
در مثال جاری قصد داریم نحوه استفاده از رویههای ذخیره شده و توابع تعریف شده توسط کاربر مخصوص SQL Server را بررسی کنیم. در اینجا کلاسهای پزشک و بیماران او، کلاسهای مدل برنامه را تشکیل میدهند:
using System.Collections.Generic;
namespace EF_Sample08.DomainClasses
{
public class Doctor
{
public int Id { set; get; }
public string Name { set; get; }
public virtual ICollection<Patient> Patients { set; get; }
}
}
namespace EF_Sample08.DomainClasses
{
public class Patient
{
public int Id { set; get; }
public string Name { set; get; }
public virtual Doctor Doctor { set; get; }
}
}
کلاس Context برنامه به نحو زیر تعریف شده:
using System.Data.Entity;
using EF_Sample08.DomainClasses;
namespace EF_Sample08.DataLayer.Context
{
public class Sample08Context : DbContext
{
public DbSet<Doctor> Doctors { set; get; }
public DbSet<Patient> Patients { set; get; }
}
}
و اینبار کلاس DbMigrationsConfiguration تعریف شده اندکی با مثالهای قبلی متفاوت است:
using System.Data.Entity.Migrations;
using EF_Sample08.DomainClasses;
using System.Collections.Generic;
namespace EF_Sample08.DataLayer.Context
{
public class Configuration : DbMigrationsConfiguration<Sample08Context>
{
public Configuration()
{
AutomaticMigrationsEnabled = true;
AutomaticMigrationDataLossAllowed = true;
}
protected override void Seed(Sample08Context context)
{
addData(context);
addSP(context);
addFn(context);
base.Seed(context);
}
private static void addData(Sample08Context context)
{
var patient1 = new Patient { Name = "p1" };
var patient2 = new Patient { Name = "p2" };
var doctor1 = new Doctor { Name = "doc1", Patients = new List<Patient> { patient1, patient2 } };
context.Doctors.Add(doctor1);
}
private static void addFn(Sample08Context context)
{
context.Database.ExecuteSqlCommand(
@"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FindDoctorPatientsCount]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FindDoctorPatientsCount]");
context.Database.ExecuteSqlCommand(
@"CREATE FUNCTION FindDoctorPatientsCount(@Doctor_Id INT)
RETURNS INT
BEGIN
RETURN
(
SELECT COUNT(*)
FROM Patients
WHERE Doctor_Id = @Doctor_Id
);
END");
}
private static void addSP(Sample08Context context)
{
context.Database.ExecuteSqlCommand(
@"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FindDoctorsStartWith]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[FindDoctorsStartWith]
");
context.Database.ExecuteSqlCommand(
@"CREATE PROCEDURE FindDoctorsStartWith(@name NVARCHAR(400))
AS
SELECT *
FROM Doctors
WHERE [Name] LIKE @name + '%'");
}
}
}
در اینجا از متد Seed علاوه بر مقدار دهی اولیه جداول، برای تعریف یک رویه ذخیره شده به نام FindDoctorsStartWith و یک تابع سفارشی به نام FindDoctorPatientsCount نیز استفاده شده است. متد context.Database.ExecuteSqlCommand مستقیما یک عبارت SQL را بر روی بانک اطلاعاتی اجرا میکند.
در ادامه کدهای کامل برنامه نهایی را ملاحظه میکنید:
using System;
using System.Data;
using System.Data.Entity;
using System.Data.Objects.SqlClient;
using System.Data.SqlClient;
using System.Linq;
using EF_Sample08.DataLayer.Context;
using EF_Sample08.DomainClasses;
namespace EF_Sample08
{
class Program
{
static void Main(string[] args)
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion<Sample08Context, Configuration>());
using (var db = new Sample08Context())
{
runSp(db);
runFn(db);
usingSqlFunctions(db);
}
}
private static void usingSqlFunctions(Sample08Context db)
{
var doctorsWithNumericNameList = db.Doctors.Where(x => SqlFunctions.IsNumeric(x.Name) == 1).ToList();
if (doctorsWithNumericNameList.Any())
{
//do something
}
}
private static void runFn(Sample08Context db)
{
var doctorIdParameter = new SqlParameter
{
ParameterName = "@doctor_id",
Value = 1,
SqlDbType = SqlDbType.Int
};
var patientsCount = db.Database.SqlQuery<int>("select dbo.FindDoctorPatientsCount(@doctor_id)", doctorIdParameter).FirstOrDefault();
Console.WriteLine(patientsCount);
}
private static void runSp(Sample08Context db)
{
var nameParameter = new SqlParameter
{
ParameterName = "@name",
Value = "doc",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.NVarChar
};
var doctors = db.Database.SqlQuery<Doctor>("exec FindDoctorsStartWith @name", nameParameter).ToList();
if (doctors.Any())
{
foreach (var item in doctors)
{
Console.WriteLine(item.Name);
}
}
}
}
}
توضیحات
همانطور که ملاحظه میکنید، برای اجرای مستقیم یک عبارت SQL صرفنظر از اینکه یک رویه ذخیره شده است یا یک تابع و یا یک کوئری معمولی، باید از متد db.Database.SqlQuery استفاده کرد. خروجی این متد از نوع IEnumerable است و این توانایی را دارد که رکوردهای بازگشت داده شده از بانک اطلاعاتی را به خواص یک کلاس به صورت خودکار نگاشت کند.
پارامتر اول متد db.Database.SqlQuery، عبارت SQL مورد نظر است. پارامتر دوم آن باید توسط وهلههایی از کلاس SqlParameter مقدار دهی شود. به کمک SqlParameter نام پارامتر مورد استفاده، مقدار و نوع آن مشخص میگردد. همچنین Direction آن نیز برای استفاده از رویههای ذخیره شده ویژهای که دارای پارامتری از نوع out هستند درنظر گرفته شده است.
چند نکته
- در متد runSp فوق، متد الحاقی ToList را حذف کرده و برنامه را اجرا کنید. بلافاصله پیغام خطای «The SqlParameter is already contained by another SqlParameterCollection.» ظاهر خواهد شد. علت هم این است که با بکارگیری متد ToList، تمام عملیات یکبار انجام شده و نتیجه بازگشت داده میشود اما اگر به صورت مستقیم از خروجی IEnumerable آن استفاده کنیم، در حلقه foreach تعریف شده، ممکن است این فراخوانی چندبار انجام شود. به همین جهت ذکر متد ToList در اینجا ضروری است.
- عنوان شد که در اینجا باید به مسایل امنیتی دقت داشت. بدیهی است امکان نوشتن یک چنین کوئریهایی نیز وجود دارد:
db.Database.SqlQuery<Doctor>("exec FindDoctorsStartWith "+ txtName.Text, nameParameter).ToList()
در این حالت به ظاهر مشغول به استفاده از رویههای ذخیره شدهای هستیم که عنوان میشود در برابر حملات تزریق SQL در امان هستند، اما چون در کدهای ما به نحو ناصحیحی با جمع زدن رشتهها مقدار دهی شده است، برنامه و بانک اطلاعاتی دیگر در امان نخواهند بود. بنابراین در این حالت استفاده از پارامترها را نباید فراموش کرد.
زمانیکه از کوئریهای LINQ استفاده میشود تمام این مسایل توسط EF مدیریت خواهد شد. اما اگر قصد دارید مستقیما عبارات SQL را فراخوانی کنید، تامین امنیت برنامه به عهده خودتان خواهد بود.
- در متد usingSqlFunctions از SqlFunctions.IsNumeric استفاده شده است. این مورد مختص به SQL Server است و امکان استفاده از توابع توکار ویژه SQL Server را در کوئریهای LINQ برنامه فراهم میسازد. برای مثال متدالحاقی از پیش تعریف شدهای به نام IsNumeric به صورت مستقیم در دسترس نیست، اما به کمک کلاس SqlFunctions این تابع و بسیاری از توابع دیگر توکار SQL Server قابل استفاده خواهند بود.
اگر علاقمند هستید که لیست این توابع را مشاهده کنید، در ویژوال استودیو بر روی SqlFunctions کلیک راست کرده و گزینه Go to definition را انتخاب کنید.
ممنون از مطالب مفیدتون.
آیا دو دستور زیر با هم یکسان هستند یا خیر؟
range unbounded preceding
range between unbounded preceding and current row
و کوئری اولتون باید مساله running total باشه، که به سادگی توسط Over Clause حل شده.
کوئری زیر روشی بوده که قبل از نسخه 2012 برای حل اینگونه مسائل مورد استفاده قرار میگرفته
SELECT AccountId, TranDate, TranAmt, D.sumAmt AS older_method Sum(TranAmt) OVER(partition by Accountid ORDER BY TranDate RANGE UNBOUNDED PRECEDING) AS SumAmt FROM #Transactions AS T1 CROSS APPLY (SELECT SUM(T2.TranAmt) FROM #Transactions AS T2 WHERE T2.AccountId = T1.AccountId AND T2.TranDate <= T1.TranDate) AS D(SumAmt) ORDER BY T1.AccountId, T1.TranDate;