9 مورد از بهترین ابزارهای GUI برای مدیریت بانک اطلاعاتی MongoDB در سال 2019
در مورد طراحی Self Referencing Entities پیشتر مطلبی را در این سایت مطالعه کرده‌اید .
یک مثال دیگر آن میتواند نظرات چند سطحی در یک سایت باشند. نحوه تعریف آن با مطالبی که در قسمت هشتم عنوان شود تفاوتی نمی‌کند؛ اما ... زمانیکه نوبت به نمایش آن فرا می‌رسد، چند نکته اضافی را باید درنظر گرفت. ابتدا مثال کامل زیر را در نظر بگیرید:
در مثال فوق کلاس نظرات به صورت خود ارجاع دهنده (خاصیت Reply به همین کلاس اشاره می‌کند) تعریف شده است تا کاربران بتوانند تا هر چند سطح لازم، به یک نظر خاص، پاسخ دهند.
در اینجا یک چنین جدولی با اطلاعاتی که ملاحظه می‌کنید تشکیل خواهند شد:
یک نظر ارائه شده و سپس دو نظر تو در توی دیگر برای این نظر ثبت شده است.
اولین نکته اضافهتری که نسبت به قسمت هشتم قابل ملاحظه است، تعریف خاصیت جدید Children به نحو زیر می‌باشد:
این خاصیت تاثیری در نحوه تشکیل جدول ندارد. علت تعریف آن به توانمندی EF در پرکردن خودکار آن بر می‌گردد.
اگر به کوئری نوشته شده در متد RunTests دقت کنید، ابتدا یک ToList نوشته شده است. این مورد سبب میشود که تمام رکوردهای مرتبط دریافت شوند. مثلا در اینجا سه رکورد دریافت میشود. سپس برای اینکه حالت درختی آن حفظ شود، در مرحله بعد ریشه‌ها فیلتر می‌شوند (مواردی که reply آنها null است). سپس این مورد تبدیل به list خواهد شد. اینبار اگر خروجی را بررسی کنیم، به ظاهر فقط یک رکورد است اما ... به نحو زیبایی توسط EF به شکل یک ساختار درختی، بدون نیاز به کدنویسی خاصی، منظم شده است:
سؤال:
برای نمایش این اطلاعات درختی و تو در تو در یک برنامه وب چکار باید کرد؟
تا اینجا که توانستیم اطلاعات را به نحو صحیحی توسط EF مرتب کنیم، برای نمایش آنها در یک برنامه ASP.NET MVC می‌توان از یک TreeViewHelper سورس باز استفاده کرد.
البته کد آن در اصل برای استفاده از EF Code first طراحی نشده و نیاز به اندکی تغییر به نحو زیر دارد تا با EF هماهنگ شود (متد ToList و Count موجود در سورس اصلی آن باید به نحو زیر حذف و اصلاح شوند):
یک مثال دیگر آن میتواند نظرات چند سطحی در یک سایت باشند. نحوه تعریف آن با مطالبی که در قسمت هشتم عنوان شود تفاوتی نمی‌کند؛ اما ... زمانیکه نوبت به نمایش آن فرا می‌رسد، چند نکته اضافی را باید درنظر گرفت. ابتدا مثال کامل زیر را در نظر بگیرید:
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;
namespace EFGeneral
{
public class BlogComment
{
public int Id { set; get; }
[MaxLength]
public string Body { set; get; }
public virtual BlogComment Reply { set; get; }
public int? ReplyId { get; set; }
public ICollection<BlogComment> Children { get; set; }
}
public class MyContext : DbContext
{
public DbSet<BlogComment> BlogComments { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// Self Referencing Entity
modelBuilder.Entity<BlogComment>()
.HasOptional(x => x.Reply)
.WithMany(x => x.Children)
.HasForeignKey(x => x.ReplyId)
.WillCascadeOnDelete(false);
base.OnModelCreating(modelBuilder);
}
}
public class Configuration : DbMigrationsConfiguration<MyContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = true;
AutomaticMigrationDataLossAllowed = true;
}
protected override void Seed(MyContext context)
{
var comment1 = new BlogComment { Body = "نظر من این است که" };
var comment12 = new BlogComment { Body = "پاسخی به نظر اول", Reply = comment1 };
var comment121 = new BlogComment { Body = "پاسخی به پاسخ به نظر اول", Reply = comment12 };
context.BlogComments.Add(comment121);
base.Seed(context);
}
}
public static class Test
{
public static void RunTests()
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, Configuration>());
using (var ctx = new MyContext())
{
var list = ctx.BlogComments
//.where ...
.ToList() // fills the childs list too
.Where(x => x.Reply == null) // for TreeViewHelper
.ToList();
if (list.Any())
{
}
}
}
}
}
در مثال فوق کلاس نظرات به صورت خود ارجاع دهنده (خاصیت Reply به همین کلاس اشاره می‌کند) تعریف شده است تا کاربران بتوانند تا هر چند سطح لازم، به یک نظر خاص، پاسخ دهند.
در اینجا یک چنین جدولی با اطلاعاتی که ملاحظه می‌کنید تشکیل خواهند شد:
یک نظر ارائه شده و سپس دو نظر تو در توی دیگر برای این نظر ثبت شده است.
اولین نکته اضافهتری که نسبت به قسمت هشتم قابل ملاحظه است، تعریف خاصیت جدید Children به نحو زیر می‌باشد:
public class BlogComment
{
// ...
public ICollection<BlogComment> Children { get; set; }
}
اگر به کوئری نوشته شده در متد RunTests دقت کنید، ابتدا یک ToList نوشته شده است. این مورد سبب میشود که تمام رکوردهای مرتبط دریافت شوند. مثلا در اینجا سه رکورد دریافت میشود. سپس برای اینکه حالت درختی آن حفظ شود، در مرحله بعد ریشه‌ها فیلتر می‌شوند (مواردی که reply آنها null است). سپس این مورد تبدیل به list خواهد شد. اینبار اگر خروجی را بررسی کنیم، به ظاهر فقط یک رکورد است اما ... به نحو زیبایی توسط EF به شکل یک ساختار درختی، بدون نیاز به کدنویسی خاصی، منظم شده است:
سؤال:
برای نمایش این اطلاعات درختی و تو در تو در یک برنامه وب چکار باید کرد؟
تا اینجا که توانستیم اطلاعات را به نحو صحیحی توسط EF مرتب کنیم، برای نمایش آنها در یک برنامه ASP.NET MVC می‌توان از یک TreeViewHelper سورس باز استفاده کرد.
البته کد آن در اصل برای استفاده از EF Code first طراحی نشده و نیاز به اندکی تغییر به نحو زیر دارد تا با EF هماهنگ شود (متد ToList و Count موجود در سورس اصلی آن باید به نحو زیر حذف و اصلاح شوند):
private void AppendChildren(TagBuilder parentTag, T parentItem, Func<T, IEnumerable<T>> childrenProperty)
{
var children = childrenProperty(parentItem);
if (children == null || !children.Any())
{
return;
}
//...
سلام و عرض ادب
اگر کلیت مشکل فارسی و عربی را بررسی کنیم به گمانم به 3 سطح زیر میرسیم
1- تبدیل در ویندوز
2- تبدیل در UI
3- تبدیل در بانک اطلاعاتی
در تایید فرمایش شما ، شخصا چون صدها کاربر با میلیونها رکورد پراکند در سطح کشور دارم که همگی از هر دو ویندوز XP و 7 میخواهند استفاده کنند و هزینه تبدیل اطلاعات قبلی به فارسی بسیار بالاست ، ترجیح میدهم ادامه ورود اطلاعاتم روی عربی باشد .
اما در سطح بانک اطلاعاتی بدلیل کنترلهای فراوان و احتمال خطا زیاد موافق نیستم (اگر نیاز است میتوانم توضیح بیشتری بدهم )
در سطح UI هم همانگونه که فرمودید مشکلات عدیده ایی وجود دارد
در سطح ویندوز بهترین راه حل است ..اما نمیدانم چگونه میتوانم انرا پیاده کنم . اگر بتوانید راهنمایی بفرمایید من در مورد پیاده سازی و یا سرمایه گذاری روی پیاده سازی مشکلی ندارم
مرسی
وکیلی
Javan_Soft@Yahoo.com
اگر کلیت مشکل فارسی و عربی را بررسی کنیم به گمانم به 3 سطح زیر میرسیم
1- تبدیل در ویندوز
2- تبدیل در UI
3- تبدیل در بانک اطلاعاتی
در تایید فرمایش شما ، شخصا چون صدها کاربر با میلیونها رکورد پراکند در سطح کشور دارم که همگی از هر دو ویندوز XP و 7 میخواهند استفاده کنند و هزینه تبدیل اطلاعات قبلی به فارسی بسیار بالاست ، ترجیح میدهم ادامه ورود اطلاعاتم روی عربی باشد .
اما در سطح بانک اطلاعاتی بدلیل کنترلهای فراوان و احتمال خطا زیاد موافق نیستم (اگر نیاز است میتوانم توضیح بیشتری بدهم )
در سطح UI هم همانگونه که فرمودید مشکلات عدیده ایی وجود دارد
در سطح ویندوز بهترین راه حل است ..اما نمیدانم چگونه میتوانم انرا پیاده کنم . اگر بتوانید راهنمایی بفرمایید من در مورد پیاده سازی و یا سرمایه گذاری روی پیاده سازی مشکلی ندارم
مرسی
وکیلی
Javan_Soft@Yahoo.com
با آمدن ORMها به دنیای برنامه نویسی، کار برنامه نویسی نسبت به قبل ساده‌تر و راحت‌تر شد. عدم استفاده کوئری‌های دستی، پشتیبانی از چند دیتابیس و از همه مهمتر و اصلی‌ترین هدف این ابزار "تنها درگیری با اشیا و مدل شیء گرایی" کار را پیش از پیش آسان‌تر نمود.
در این بین به راحتی می‌توان چندین نمونه از این ORMها را نام برد مثل IBatis , Hibernate ,Nhibernate و EF که از معروفترین آنها هستند.
من در حال حاضر قصد شروع یک پروژه اندرویدی را دارم و دوست دارم بجای استفاده‌ی از Sqlitehelper، از یک ORM مناسب بهره ببرم که چند سوال برای من پیش می‌آید. آیا ORM ای برای آن تهیه شده است؟ اگر آری چندتا و کدامیک از آنها بهتر هستند؟ شاید در اولین مورد کتابخانه‌ی Hibernate جاوا را نام ببرید؛ ولی توجه به این نکته ضروری است که ما در مورد پلتفرم موبایل و محدودیت‌های آن صحبت می‌کنیم. یک کتابخانه همانند Hibernate مطمئنا برای یک برنامه اندروید چه از نظر حجم نهایی برنامه و چه از نظر حجم بزرگش در اجرا، مشکل زا خواهد بود و وجود وابستگی‌های متعدد و دارا بودن بسیاری از قابلیت‌هایی که اصلا در بانک‌های اطلاعاتی موبایل قابل اجرا نیست، باعث میشود این فریمورک انتخاب خوبی برای یک برنامه اندروید نباشد.
معیارهای انتخاب یک فریم ورک مناسب برای موبایل:
OrmLight
این فریمورک مختص اندروید طراحی نشده ولی سبک بودن آن موجب شده‌است که بسیاری از برنامه نویسان از آن در برنامه‌های اندرویدی استفاده کنند. این فریم ورک جهت اتصالات JDBC و Spring و اندروید طراحی شده است.
نحوه معرفی جداول در این فریمورک به صورت زیر است:
در این بین به راحتی می‌توان چندین نمونه از این ORMها را نام برد مثل IBatis , Hibernate ,Nhibernate و EF که از معروفترین آنها هستند.
من در حال حاضر قصد شروع یک پروژه اندرویدی را دارم و دوست دارم بجای استفاده‌ی از Sqlitehelper، از یک ORM مناسب بهره ببرم که چند سوال برای من پیش می‌آید. آیا ORM ای برای آن تهیه شده است؟ اگر آری چندتا و کدامیک از آنها بهتر هستند؟ شاید در اولین مورد کتابخانه‌ی Hibernate جاوا را نام ببرید؛ ولی توجه به این نکته ضروری است که ما در مورد پلتفرم موبایل و محدودیت‌های آن صحبت می‌کنیم. یک کتابخانه همانند Hibernate مطمئنا برای یک برنامه اندروید چه از نظر حجم نهایی برنامه و چه از نظر حجم بزرگش در اجرا، مشکل زا خواهد بود و وجود وابستگی‌های متعدد و دارا بودن بسیاری از قابلیت‌هایی که اصلا در بانک‌های اطلاعاتی موبایل قابل اجرا نیست، باعث میشود این فریمورک انتخاب خوبی برای یک برنامه اندروید نباشد.
معیارهای انتخاب یک فریم ورک مناسب برای موبایل:
- سبک بودن: مهمترین مورد سبک بودن آن است؛ چه از لحاظ اجرای برنامه و چه از لحاظ حجم نهایی برنامه
- سریع بودن: مطمئنا ORMهای طراحی شده‌ی موجود، از سرعت خیلی بدی برخوردار نخواهند بود؛ اگر سر زبان هم افتاده باشند. ولی باز هم انتخاب سریع بودن یک ORM، مورد علاقه‌ی بسیاری از ماهاست.
- یادگیری آسان و کانفیگ راحت تر.
OrmLight
این فریمورک مختص اندروید طراحی نشده ولی سبک بودن آن موجب شده‌است که بسیاری از برنامه نویسان از آن در برنامه‌های اندرویدی استفاده کنند. این فریم ورک جهت اتصالات JDBC و Spring و اندروید طراحی شده است.
نحوه معرفی جداول در این فریمورک به صورت زیر است:
@DatabaseTable(tableName = "users")
public class User {
@DatabaseField(id = true)
private String username;
@DatabaseField
private String password;
public User() {
// ORMLite needs a no-arg constructor
}
public User(String username, String password) {
this.username = username;
this.password = password;
}
// Implementing getter and setter methods
public String getUserame() {
return this.username;
}
public void setName(String username) {
this.username = username;
}
public String getPassword() {
return this.password;
}
public void setPassword(String password) {
this.password = password;
}
}
سورس این فریمورک را می‌توان در گیت هاب یافت و مستندات آن در این آدرس قرار دارند.
SugarORM
این فریمورک مختص اندروید طراحی شده است. یادگیری آن بسیار آسان است و به راحتی به یاد می‌ماند. همچنین جداول مورد نیاز را به طور خودکار خواهد ساخت. روابط یک به یک و یک به چند را پشتیبانی می‌کند و عملیات CURD را با سه متد Save,Delete و Find که البته FindById هم جزء آن است، پیاده سازی می‌کند.
برای استفاده از این فریمورک نیاز است ابتدا متادیتا‌های زیر را به فایل manifest اضافه کنید:
SugarORM
این فریمورک مختص اندروید طراحی شده است. یادگیری آن بسیار آسان است و به راحتی به یاد می‌ماند. همچنین جداول مورد نیاز را به طور خودکار خواهد ساخت. روابط یک به یک و یک به چند را پشتیبانی می‌کند و عملیات CURD را با سه متد Save,Delete و Find که البته FindById هم جزء آن است، پیاده سازی می‌کند.
برای استفاده از این فریمورک نیاز است ابتدا متادیتا‌های زیر را به فایل manifest اضافه کنید:
<meta-data android:name="DATABASE" android:value="my_database.db" /> <meta-data android:name="VERSION" android:value="1" /> <meta-data android:name="QUERY_LOG" android:value="true" /> <meta-data android:name="DOMAIN_PACKAGE_NAME" android:value="com.my-domain" />
برای تبدیل یک کلاس به جدول هم از کلاس این فریم ورک ارث بری می‌کنیم:
public class User extends SugarRecord<User> {
String username;
String password;
int age;
@Ignore
String bio; //this will be ignored by SugarORM
public User() { }
public User(String username, String password,int age){
this.username = username;
this.password = password;
this.age = age;
}
} باقی عملیات آن از قبیل اضافه کردن یک رکورد جدید یا حذف رکورد(ها) به صورت زیر است:
User johndoe = new User(getContext(),"john.doe","secret",19);
johndoe.save(); //ذخیره کاربر جدید در دیتابیس
//حذف تمامی کاربرانی که سنشان 19 سال است
List<User> nineteens = User.find(User.class,"age = ?",new int[]{19});
foreach(user in nineteens) {
user.delete();
}
GreenDAO
موقعیکه بحث کارآیی و سرعت پیش می‌آید نام GreenDAO هست که می‌درخشد. طبق گفته‌ی سایت رسمی آن این فریمورک میتواند در ثانیه چند هزار موجودیت را اضافه و به روزرسانی و بارگیری نماید. این لیست حاوی برنامه‌هایی است که از این فریمورک استفاده می‌کنند. جدول زیر مقایسه‌ای است بین این کتابخانه و OrmLight که نشان میدهد 4.5 برابر سریعتر از OrmLight عمل می‌کند.
موقعیکه بحث کارآیی و سرعت پیش می‌آید نام GreenDAO هست که می‌درخشد. طبق گفته‌ی سایت رسمی آن این فریمورک میتواند در ثانیه چند هزار موجودیت را اضافه و به روزرسانی و بارگیری نماید. این لیست حاوی برنامه‌هایی است که از این فریمورک استفاده می‌کنند. جدول زیر مقایسه‌ای است بین این کتابخانه و OrmLight که نشان میدهد 4.5 برابر سریعتر از OrmLight عمل می‌کند.
غیر از این‌ها در زمینه‌ی حجم هم حرف‌هایی برای گفتن دارد. حجم این کتابخانه کمتر از 100 کیلوبایت است که در اندازه‌ی APK اثر چندانی نخواهد داشت.
آموزش راه اندازی آن در اندروید استادیو، سورس آن در گیت هاب و مستندات رسمی آن.
Active Android
این کتابخانه از دو طریق فایل JAR و به شیوه maven قابل استفاده است که می‌توانید روش استفاده‌ی از آن را در این لینک ببینید و سورس اصلی آن هم در این آدرس قرار دارد. بعد از اینکه کتابخانه را به پروژه اضافه کردید، دو متادیتای زیر را که به ترتیب نام دیتابیس و ورژن آن هستند، به manifest اضافه کنید:
آموزش راه اندازی آن در اندروید استادیو، سورس آن در گیت هاب و مستندات رسمی آن.
Active Android
این کتابخانه از دو طریق فایل JAR و به شیوه maven قابل استفاده است که می‌توانید روش استفاده‌ی از آن را در این لینک ببینید و سورس اصلی آن هم در این آدرس قرار دارد. بعد از اینکه کتابخانه را به پروژه اضافه کردید، دو متادیتای زیر را که به ترتیب نام دیتابیس و ورژن آن هستند، به manifest اضافه کنید:
<meta-data android:name="AA_DB_NAME" android:value="my_database.db" /> <meta-data android:name="AA_DB_VERSION" android:value="1" />
public class MyActivity extends Activity {
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
ActiveAndroid.initialize(this);
//ادامه برنامه
}
} @Table(name = "User")
public class User extends Model {
@Column(name = "username")
public String username;
@Column(name = "password")
public String password;
public User() {
super();
}
public User(String username,String password) {
super();
this.username = username;
this.password = password;
}
} ORMDroid
از آن دست کتابخانه‌هایی است که سادگی و کم حجم بودن شعار آنان است و سعی دارند تا حد ممکن همه چیز را خودکار کرده و کمترین کانفیگ را نیاز داشته باشد. حجم فعلی آن حدود 20 کیلوبایت بوده و نمی‌خواهند از 30 کیلوبایت تجاوز کند.
برای استفاده‌ی از آن ابتدا دو خط زیر را جهت معرفی تنظیمات به manifest اضافه کنید:
<meta-data android:name="ormdroid.database.name" android:value="your_database_name" /> <meta-data android:name="ormdroid.database.visibility" android:value="PRIVATE||WORLD_READABLE||WORLD_WRITEABLE" />
ORMDroidApplication.initialize(someContext);
public class Person extends Entity {
public int id;
public String name;
public String telephone;
}
//====================
Person p = Entity.query(Person.class).where("id=1").execute();
p.telephone = "555-1234";
p.save();
// یا
Person person = Entity.query(Person.class).where(eql("id", id)).execute();
p.telephone = "555-1234";
p.save();
کد بالا دقیقا یادآوری به EF هست ولی حیف که از Linq پشتیبانی نمی‌شود.
سورس آن در گیت هاب
در اینجا سعی کردیم تعدادی از کتابخانه‌های محبوب را معرفی کنیم ولی تعداد آن به همین جا ختم نمی‌شود. ORMهای دیگری نظیر AndRom و سایر ORM هایی که در این لیست معرفی شده اند وجود دارند.
نکته نهایی اینکه خوب میشود دوستانی که از این ORMهای مختص اندروید استفاده کرده اند؛ نظراتشان را در مورد آنها بیان کنند و مزایا و معایب آنها را بیان کنند.
سورس آن در گیت هاب
در اینجا سعی کردیم تعدادی از کتابخانه‌های محبوب را معرفی کنیم ولی تعداد آن به همین جا ختم نمی‌شود. ORMهای دیگری نظیر AndRom و سایر ORM هایی که در این لیست معرفی شده اند وجود دارند.
نکته نهایی اینکه خوب میشود دوستانی که از این ORMهای مختص اندروید استفاده کرده اند؛ نظراتشان را در مورد آنها بیان کنند و مزایا و معایب آنها را بیان کنند.
با سلام و احترام
در انتهای بحث فرمودید «نکته جالبی که در اینجا وجود دارد، عدم نیاز به join نویسی برای دریافت اطلاعات وابسته به یک شیء است». با توجه به این مطلب من دقیقا متوجه نشدم در مسئله زیر چه باید کرد:
هر answer و question توسط By مشخص میشود توسط چه کسی ارسال شده (مثلاً : "users/Vahid":"By")، و در سند users اطلاعات کامل هر کاربر مثل نام کاریری، ایمیل، آدرس و شماره تماس و... در زمان ثبت نام ذخیره شده است. سوال: اگر لازم باشد به ازای هر سوال یا جواب ایمیل و شماره تماس فرستنده را هم به کاربر نهایی نشان دهیم، باید در زمان ارسال سوال یا جواب ابتدا کاربر را با استفاده از By در users یافته و ایمیل و شماره تماس وی را خوانده و در سند آن سوال یا جواب ذخیره کنیم؛ یا باید هر زمان که سوال یا جوابی ارسال شد فقط نام کاربری را ثبت کنیم و در زمان نمایش سوال‌ها و جواب‌ها به کاربر مشحصات فرستنده را از users خوانده و همراه با خصوصیات دیگر به کاربر نشان دهیم، که این مورد آخر همان بانک‌های اطلاعاتی رابطه ای است.
سلام
یک پروژه با کتابخانه MediatR ایجاد کردم و برای عملیات CRUD به ازای تمام Entityها Handlerهای مورد نیاز را پیاده سازی کردم. برخی از این Handlerها در زمان اجرای Bussiness مربوط به خودش، نیاز داره تا اطلاعاتی را در یک جدول دیگر در بانک اطلاعاتی ذخیره کنه ولی افزودن اطلاعات جدید در جدول دوم، مستلزم لحاظ نمودن Business مربوط به Handler دوم است. به تمامی Handlerها IEntityRepository مربوط به Entity که در Domain تعریف شده است تزریق می شود. در لایه Infrastructure نیز تمام متدهای موجود در IEntityRepository تحت نام EntityRepository پیاده سازی شده اند.
سوال:
- آیا با توجه به سناریوی بالا، امکان تزریق MediatR.IMediator در Handler اول وجود دارد (از حیث Best Practice بودن)؟
- اگر آیتم یک مناسب نباشد آیا باید یک لایه دیگری به پروژه افزوده شود تا امکان استفاده از Handlerهای مشترک وجود داشته باشد؟
- در یکی از منابع اینترنتی به آیتم شماره یک عنوان Anti Pattern داده بودند. آیا درست است؟
تشکر
فرض کنید در حال توسعه‌ی یک برنامه‌ی Blazor WASM هاست شده هستید و می‌خواهید که نیازی نباشد تا به ازای هر صفحه‌ای که به برنامه اضافه می‌کنید، یکبار منوی آن‌را به روز رسانی کنید و نمایش منو به صورت خودکار توسط برنامه صورت گیرد. همچنین در این حالت نیاز است در قسمت مدیریتی برنامه، بتوان به صورت پویا، به ازای هر کاربری، مشخص کرد که به کدامیک از صفحات برنامه دسترسی دارد و یا خیر و به علاوه اگر به صفحاتی دسترسی ندارد، مشخصات این صفحه، در منوی پویا برنامه ظاهر نشود و همچنین با تایپ آدرس آن در نوار آدرس مرورگر نیز قابل دسترسی نباشد. امن سازی پویای سمت کلاینت، یک قسمت از پروژه‌است؛ قسمت دیگر چنین پروژه‌ای، لیست کردن اکشن متدهای API سمت سرور پروژه و انتساب دسترسی‌های پویایی به این اکشن متدها، به کاربران مختلف برنامه‌است.
دریافت کدهای کامل این پروژه
کدهای کامل پروژه‌ای که نیازمندی‌های فوق را پیاده سازی می‌کند، در اینجا می‌توانید مشاهده و دریافت کنید. در این مطلب از قرار دادن مستقیم این کدها صرفنظر شده و سعی خواهد شد بجای آن، نقشه‌ی ذهنی درک کدهای آن توضیح داده شود.
پیشنیازها
در پروژه‌ی فوق برای شروع به کار، از اطلاعات مطرح شده‌ی در سلسله مطالب زیر استفاده شده‌است:
- «اعتبارسنجی مبتنی بر JWT در ASP.NET Core 2.0 بدون استفاده از سیستم Identity»
- «مدیریت مرکزی شماره نگارش‌های بسته‌های NuGet در پروژه‌های NET Core.»
- «کاهش تعداد بار تعریف usingها در C# 10.0 و NET 6.0.»
- «روش یافتن لیست تمام کنترلرها و اکشن متدهای یک برنامه‌ی ASP.NET Core»
نیاز به علامتگذاری صفحات امن شده‌ی سمت کلاینت، جهت نمایش خودکار آنها
صفحات امن سازی شده‌ی سمت کلاینت، با ویژگی Authorize مشخص می‌شوند. بنابراین قید آن الزامی است، تا صرفا جهت کاربران اعتبارسنجی شده، قابل دسترسی شوند. در اینجا می‌توان یک نمونه‌ی سفارشی سازی شده‌ی ویژگی Authorize را به نام ProtectedPageAttribute نیز مورد استفاده قرار داد. این ویژگی از AuthorizeAttribute ارث‌بری کرده و دقیقا مانند آن عمل می‌کند؛ اما این اضافات را نیز به همراه دارد:
- به همراه یک Policy از پیش تعیین شده به نام CustomPolicies.DynamicClientPermission است تا توسط قسمت‌های بررسی سطوح دسترسی پویا و همچنین منوساز برنامه، یافت شده و مورد استفاده قرار گیرد.
- به همراه خواص اضافهتری مانند GroupName و Title نیز هست. GroupName نام سرتیتر منوی dropdown نمایش داده شده‌ی در منوی اصلی برنامه‌است و Title همان عنوان صفحه که در این منو نمایش داده میشود. اگر صفحه‌ی محافظت شده‌ای به همراه GroupName نباشد، یعنی باید به صورت یک آیتم اصلی نمایش داده شود. همچنین در اینجا یک سری Order هم درنظر گرفته شده‌اند تا بتوان ترتیب نمایش صفحات را نیز به دلخواه تغییر داد.
نمونه‌ای از استفاده‌ی از ویژگی فوق را در مسیر src\Client\Pages\Feature1 می‌توانید مشاهده کنید که خلاصه‌ی آن به صورت زیر است:
ویژگی ProtectedPage را معادل یک ویژگی Authorize سفارشی، به همراه چند خاصیت بیشتر، جهت منوساز پویای برنامه درنظر بگیرید.
نیاز به لیست کردن صفحات علامتگذاری شده‌ی با ویژگی ProtectedPage
پس از اینکه صفحات مختلف برنامه را توسط ویژگی ProtectedPage علامتگذاری کردیم، اکنون نوبت به لیست کردن پویای آنها است. اینکار توسط سرویس ProtectedPagesProvider صورت می‌گیرد. این سرویس با استفاده از Reflection، ابتدا تمام IComponentها یا همان کامپوننت‌های تعریف شده‌ی در برنامه را از اسمبلی جاری استخراج می‌کند. بنابراین اگر نیاز دارید که این جستجو در چندین اسمبلی صورت گیرد، فقط کافی است ابتدای این کدها را تغییر دهید. پس از یافت شدن IComponent ها، فقط آن‌هایی که دارای RouteAttribute هستند، پردازش می‌شوند؛ یعنی کامپوننت‌هایی که به همراه مسیریابی هستند. پس از آن بررسی میشود که آیا این کامپوننت دارای ProtectedPageAttribute هست یا خیر؟ اگر بله، این کامپوننت در لیست نهایی درج خواهد شد.
نیاز به یک منوساز پویا جهت نمایش خودکار صفحات امن سازی شده‌ی با ویژگی ProtectedPage
اکنون که لیست صفحات امن سازی شده‌ی توسط ویژگی ProtectedPage را در اختیار داریم، می‌توانیم آنها را توسط کامپوننت سفارشی NavBarDynamicMenus به صورت خودکار نمایش دهیم. این کامپوننت لیست صفحات را توسط کامپوننت NavBarDropdownMenu نمایش میدهد.
تهیه‌ی جداول و سرویس‌های ثبت دسترسی‌های پویای سمت کلاینت
جداول و فیلدهای مورد استفاده‌ی در این پروژه را در تصویر فوق ملاحظه می‌کنید که در پوشه‌ی src\Server\Entities نیز قابل دسترسی هستند. در این برنامه نیاز به ذخیره سازی اطلاعات نقش‌های کاربران مانند نقش Admin، ذخیره سازی سطوح دسترسی پویای سمت کلاینت و همچنین سمت سرور است. بنابراین بجای اینکه به ازای هر کدام، یک جدول جداگانه را تعریف کنیم، می‌توان از همان طراحی ASP.NET Core Identity مایکروسافت با استفاده از جدول UserClaimها ایده گرفت. یعنی هر کدام از این موارد، یک Claim خواهند شد:
در اینجا نقش‌ها با Claim استانداردی به نام http://schemas.microsoft.com/ws/2008/06/identity/claims/role که توسط خود مایکروسافت نامگذاری شده و سیستم‌های اعتبارسنجی آن بر همین اساس کار می‌کنند، قابل مشاهده‌است. همچنین دو Claim سفارشی دیگر ::DynamicClientPermission:: برای ذخیره سازی اطلاعات صفحات محافظت شده‌ی سمت کلاینت و ::DynamicServerPermission:: جهت ذخیره سازی اطلاعات اکشن متدهای محافظت شده‌ی سمت سرور نیز تعریف شده‌اند. رابطه‌ای این اطلاعات با جدول کاربران، many-to-many است.
به این ترتیب است که مشخص میشود کدام کاربر، به چه claimهایی دسترسی دارد.
برای کار با این جداول، سه سرویس UsersService، UserClaimsService و UserTokensService پیش بینی شده‌اند. UserTokens اطلاعات توکن‌های صادر شده‌ی توسط برنامه را ذخیره می‌کند و توسط آن می‌توان logout سمت سرور را پیاده سازی کرد؛ از این جهت که JWTها متکی به خود هستند و تا زمانیکه منقضی نشوند، در سمت سرور پردازش خواهند شد، نیاز است بتوان به نحوی اگر کاربری غیرفعال شد، از آن ثانیه به بعد، توکن‌های او در سمت سرور پردازش نشوند که به این نکات در مطلب «اعتبارسنجی مبتنی بر JWT در ASP.NET Core 2.0 بدون استفاده از سیستم Identity» پیشتر پرداخته شده‌است.
اطلاعات این سرویس‌ها توسط اکشن متدهای UsersAccountManagerController، در اختیار برنامه‌ی کلاینت قرار می‌گیرند.
نیاز به قسمت مدیریتی ثبت دسترسی‌های پویای سمت کلاینت و سرور
قبل از اینکه بتوان قسمت‌های مختلف کامپوننت NavBarDynamicMenus را توضیح داد، نیاز است ابتدا یک قسمت مدیریتی را جهت استفاده‌ی از لیست ProtectedPageها نیز تهیه کرد:
در این برنامه، کامپوننت src\Client\Pages\Identity\UsersManager.razor کار لیست کردن کاربران، که اطلاعات آن‌را از کنترلر UsersAccountManagerController دریافت می‌کند، انجام میدهد. در مقابل نام هر کاربر، دو دکمه‌ی ثبت اطلاعات پویای دسترسی‌های سمت کلاینت و سمت سرور وجود دارد. سمت کلاینت آن توسط کامپوننت UserClientSidePermissions.razor مدیریت میشود و سمت سرور آن توسط UserServerSidePermissions.razor.
کامپوننت UserClientSidePermissions.razor، همان لیست صفحات محافظت شده‌ی توسط ویژگی ProtectedPage را به صورت گروه بندی شده و به همراه یک سری chekmark، ارائه میدهد. اگر در اینجا صفحه‌ای انتخاب شد، اطلاعات آن به سمت سرور ارسال میشود تا توسط Claim ای به نام ::DynamicClientPermission:: به کاربر انتخابی انتساب داده شود.
شبیه به همین عملکرد در مورد دسترسی سمت سرور نیز برقرار است. UserServerSidePermissions.razor، لیست اکشن متدهای محافظت شده را از کنترلر DynamicPermissionsManagerController دریافت کرده و نمایش میدهد. این اطلاعات توسط سرویس ApiActionsDiscoveryService جمع آوری میشود. همچنین این اکشن متدهای ویژه نیز باید با ویژگی Authorize(Policy = CustomPolicies.DynamicServerPermission) مزین شده باشند که نمونه مثال آنها را در مسیر src\Server\Controllers\Tests می‌توانید مشاهده کنید. اگر در سمت کلاینت و قسمت مدیریتی آن، اکشن متدی جهت کاربر خاصی انتخاب شد، اطلاعات آن ذیل Claimای به نام ::DynamicServerPermission:: به کاربر انتخابی انتساب داده میشود.
بازگشت اطلاعات پویای دسترسی‌های سمت کلاینت از API
تا اینجا کامپوننت‌های امن سازی شده‌ی سمت کلاینت و اکشن متدهای امن سازی شده‌ی سمت سرور را توسط صفحات مدیریتی برنامه، به کاربران مدنظر خود انتساب دادیم و توسط سرویس‌های سمت سرور، اطلاعات آنها را در بانک اطلاعاتی ذخیره کردیم. اکنون نوبت به استفاده‌ی از claims تعریف شده و مرتبط با هر کاربر است. پس از یک لاگین موفقیت آمیز توسط UsersAccountManagerController، سه توکن به سمت کاربر ارسال می‌شوند:
- توکن دسترسی: اطلاعات اعتبارسنجی کاربر به همراه نام و نقش‌های او در این توکن وجود دارند.
- توکن به روز رسانی: هدف از آن، دریافت یک توکن دسترسی جدید، بدون نیاز به لاگین مجدد است. به این ترتیب کاربر مدام نیاز به لاگین مجدد نخواهد داشت و تا زمانیکه refresh token منقضی نشده‌است، برنامه میتواند از آن جهت دریافت یک access token جدید استفاده کند.
- توکن سطوح دسترسی پویای سمت کلاینت: در اینجا لیست ::DynamicClientPermission::ها به صورت یک توکن مجزا به سمت کاربر ارسال میشود. این اطلاعات به توکن دسترسی اضافه نشده‌اند تا بی‌جهت حجم آن اضافه نشود؛ از این جهت که نیازی نیست تا به ازای هر درخواست HTTP به سمت سرور، این لیست حجیم claims پویای سمت کلاینت نیز به سمت سرور ارسال شود. چون سمت سرور از claims دیگری به نام ::DynamicServerPermission:: استفاده می‌کند.
اگر دقت کنید، هم refresh-token و هم DynamicPermissions هر دو به صورت JWT ارسال شده‌اند. می‌شد هر دو را به صورت plain و ساده نیز ارسال کرد. اما مزیت refresh token ارسال شده‌ی به صورت JWT، انجام اعتبارسنجی خودکار سمت سرور اطلاعات آن است که دستکاری سمت کلاینت آن‌را مشکل می‌کند.
این سه توکن توسط سرویس BearerTokensStore، در برنامه‌ی سمت کلاینت ذخیره و بازیابی می‌شوند. توکن دسترسی یا همان access token، توسط ClientHttpInterceptorService به صورت خودکار به تمام درخواست‌های ارسالی توسط برنامه الصاق خواهد شد.
مدیریت خودکار اجرای Refresh Token در برنامه‌های Blazor WASM
دریافت refresh token از سمت سرور تنها قسمتی از مدیریت دریافت مجدد یک access token معتبر است. قسمت مهم آن شامل دو مرحله‌ی زیر است:
الف) اگر خطاهای سمت سرور 401 و یا 403 رخ دادند، ممکن است نیاز به refresh token باشد؛ چون احتمالا یا کاربر جاری به این منبع دسترسی ندارد و یا access token دریافتی که طول عمر آن کمتر از refresh token است، منقضی شده و دیگر قابل استفاده نیست.
ب) پیش از منقضی شدن access token، بهتر است با استفاده از refresh token، یک access token جدید را دریافت کرد تا حالت الف رخ ندهد.
- برای مدیریت حالت الف، یک Policy ویژه‌ی Polly طراحی شده‌است که آن‌را در کلاس ClientRefreshTokenRetryPolicy مشاهده می‌کنید. در این Policy ویژه، هرگاه خطاهای 401 و یا 403 رخ دهند، با استفاده از سرویس جدید IClientRefreshTokenService، کار به روز رسانی توکن انجام خواهد شد. این Policy در کلاس program برنامه ثبت شده‌است. مزیت کار با Policyهای Polly، عدم نیاز به try/catch نوشتن‌های تکراری، در هر جائیکه از سرویس‌های HttpClient استفاده میشود، می‌باشد.
- برای مدیریت حالت ب، حتما نیاز به یک تایمر سمت کلاینت است که چند ثانیه پیش از منقضی شدن access token دریافتی پس از لاگین، کار دریافت access token جدیدی را به کمک refresh token موجود، انجام دهد. پیاده سازی این تایمر را در کلاس ClientRefreshTokenTimer مشاهده می‌کنید که محل فراخوانی و راه اندازی آن یا پس از لاگین موفق در سمت کلاینت و یا با ریفرش صفحه (فشرده شدن دکمه‌ی F5) و در کلاس آغازین ClientAuthenticationStateProvider می‌باشد.
نیاز به پیاده سازی Security Trimming سمت کلاینت
از داخل DynamicPermissions دریافتی پس از لاگین، لیست claimهای دسترسی پویای سمت کلاینت کاربر لاگین شده استخراج میشود. بنابراین مرحله‌ی بعد، استخراج، پردازش و اعمال این سطوح دسترسی پویای دریافت شده‌ی از سرور است.
سرویس BearerTokensStore، کار ذخیره سازی توکن‌های دریافتی پس از لاگین را انجام میدهد و سپس با استفاده از سرویس DynamicClientPermissionsProvider، توکن سوم دریافت شده که مرتبط با لیست claims دسترسی کاربر جاری است را پردازش کرده و تبدیل به یک لیست قابل استفاده می‌کنیم تا توسط آن بتوان زمانیکه قرار است آیتم‌های منوها را به صورت پویا نمایش داد، مشخص کنیم که کاربر، به کدامیک دسترسی دارد و به کدامیک خیر. عدم نمایش قسمتی از صفحه که کاربر به آن دسترسی ندارد را security trimming گویند. برای نمونه کامپوننت ویژه‌ی SecurityTrim.razor، با استفاده از نقش‌ها و claims یک کاربر، میتواند تعیین کند که آیا قسمت محصور شده‌ی صفحه توسط آن قابل نمایش به کاربر است یا خیر. این کامپوننت از متدهای کمکی AuthenticationStateExtensions که کار با user claims دریافتی از طریق JWTها را ساده می‌کنند، استفاده می‌کند. یک نمونه از کاربرد کامپوننت SecurityTrim را در فایل src\Client\Shared\MainLayout.razor می‌توانید مشاهده کنید که توسط آن لینک Users Manager، فقط به کاربران دارای نقش Admin نمایش داده میشود.
نحوه‌ی مدیریت security trimming منوی پویای برنامه، اندکی متفاوت است. DynamicClientPermissionsProvider لیست claims متعلق به کاربر را بازگشت میدهد. این لیست پس از لاگین موفقیت آمیز دریافت شده‌است. سپس لیست کلی صفحاتی را که در ابتدای برنامه استخراج کردیم، در طی حلقه‌ای از سرویس ClientSecurityTrimmingService عبور می‌دهیم. یعنی مسیر صفحه و همچنین دسترسی‌های پویای کاربر، مشخص هستند. در این بین هر مسیری که در لیست claims پویای کاربر نبود، در لیست آیتم‌های منوی پویای برنامه، نمایش داده نمی‌شود.
نیاز به قطع دسترسی به مسیرهایی در سمت کلاینت که کاربر به صورت پویا به آنها دسترسی ندارد
با استفاده از ClientSecurityTrimmingService، در حلقه‌ای که آیتم‌های منوی سایت را نمایش میدهد، موارد غیرمرتبط با کاربر جاری را حذف کردیم و نمایش ندادیم. اما این حذف، به این معنا نیست که اگر این آدرس‌ها را به صورت مستقیم در مرورگر وارد کند، به آنها دسترسی نخواهد داشت. برای رفع این مشکل، نیاز به پیاده سازی یک سیاست دسترسی پویای سمت کلاینت است. روش ثبت این سیاست را در کلاس DynamicClientPermissionsPolicyExtensions مشاهده می‌کنید. کلید آن همان CustomPolicies.DynamicClientPermission که در حین تعریف ProtectedPageAttribute به عنوان مقدار Policy پیش‌فرض مقدار دهی شد. یعنی هرگاه ویژگی ProtectedPage به صفحه‌ای اعمال شد، از این سیاست دسترسی استفاده می‌کند که پردازشگر آن DynamicClientPermissionsAuthorizationHandler است. این هندلر نیز از ClientSecurityTrimmingService استفاده می‌کند. در هندلر context.User جاری مشخص است. این کاربر را به متد تعیین دسترسی مسیر جاری به سرویس ClientSecurityTrimming ارسال می‌کنیم تا مشخص شود که آیا به مسیر درخواستی دسترسی دارد یا خیر؟
نیاز به قطع دسترسی به منابعی در سمت سرور که کاربر به صورت پویا به آنها دسترسی ندارد
شبیه به ClientSecurityTrimmingService سمت کلاینت را در سمت سرور نیز داریم؛ به نام ServerSecurityTrimmingService که کار آن، پردازش claimهایی از نوع ::DynamicServerPermission:: است که در صفحه‌ی مدیریتی مرتبطی در سمت کلاینت، به هر کاربر قابل انتساب است. هندلر سیاست دسترسی پویایی که از آن استفاده می‌کند نیز DynamicServerPermissionsAuthorizationHandler می‌باشد. این سیاست دسترسی پویا با کلید CustomPolicies.DynamicServerPermission در کلاس ConfigureServicesExtensions تعریف شده‌است. به همین جهت هر اکشن متدی که Policy آن با این کلید مقدار دهی شده باشد، از هندلر پویای فوق جهت تعیین دسترسی پویا عبور خواهد کرد. منطق پیاده سازی شده‌ی در اینجا، بسیار شبیه به مطلب «سفارشی سازی ASP.NET Core Identity - قسمت پنجم - سیاست‌های دسترسی پویا» است؛ اما بدون استفاده‌ی از ASP.NET Core Identity.
روش اجرای برنامه
چون این برنامه از نوع Blazor WASM هاست شده‌است، نیاز است تا برنامه‌ی Server آن‌را در ابتدا اجرا کنید. با اجرای آن، بانک اطلاعاتی SQLite برنامه به صورت خودکار توسط EF-Core ساخته شده و مقدار دهی اولیه میشود. لیست کاربران پیش‌فرض آن‌را در اینجا می‌توانید مشاهده کنید. ابتدا با کاربر ادمین وارد شده و سطوح دسترسی سایر کاربران را تغییر دهید. سپس بجای آنها وارد سیستم شده و تغییرات منوها و سطوح دسترسی پویا را بررسی کنید.
دریافت کدهای کامل این پروژه
کدهای کامل پروژه‌ای که نیازمندی‌های فوق را پیاده سازی می‌کند، در اینجا می‌توانید مشاهده و دریافت کنید. در این مطلب از قرار دادن مستقیم این کدها صرفنظر شده و سعی خواهد شد بجای آن، نقشه‌ی ذهنی درک کدهای آن توضیح داده شود.
پیشنیازها
در پروژه‌ی فوق برای شروع به کار، از اطلاعات مطرح شده‌ی در سلسله مطالب زیر استفاده شده‌است:
- «اعتبارسنجی مبتنی بر JWT در ASP.NET Core 2.0 بدون استفاده از سیستم Identity»
- پیاده سازی اعتبارسنجی کاربران در برنامه‌های Blazor WASM؛ قسمت‌های 31 تا 33 .
- «غنی سازی کامپایلر C# 9.0 با افزونه‌ها»- «مدیریت مرکزی شماره نگارش‌های بسته‌های NuGet در پروژه‌های NET Core.»
- «کاهش تعداد بار تعریف usingها در C# 10.0 و NET 6.0.»
- «روش یافتن لیست تمام کنترلرها و اکشن متدهای یک برنامه‌ی ASP.NET Core»
نیاز به علامتگذاری صفحات امن شده‌ی سمت کلاینت، جهت نمایش خودکار آنها
صفحات امن سازی شده‌ی سمت کلاینت، با ویژگی Authorize مشخص می‌شوند. بنابراین قید آن الزامی است، تا صرفا جهت کاربران اعتبارسنجی شده، قابل دسترسی شوند. در اینجا می‌توان یک نمونه‌ی سفارشی سازی شده‌ی ویژگی Authorize را به نام ProtectedPageAttribute نیز مورد استفاده قرار داد. این ویژگی از AuthorizeAttribute ارث‌بری کرده و دقیقا مانند آن عمل می‌کند؛ اما این اضافات را نیز به همراه دارد:
- به همراه یک Policy از پیش تعیین شده به نام CustomPolicies.DynamicClientPermission است تا توسط قسمت‌های بررسی سطوح دسترسی پویا و همچنین منوساز برنامه، یافت شده و مورد استفاده قرار گیرد.
- به همراه خواص اضافهتری مانند GroupName و Title نیز هست. GroupName نام سرتیتر منوی dropdown نمایش داده شده‌ی در منوی اصلی برنامه‌است و Title همان عنوان صفحه که در این منو نمایش داده میشود. اگر صفحه‌ی محافظت شده‌ای به همراه GroupName نباشد، یعنی باید به صورت یک آیتم اصلی نمایش داده شود. همچنین در اینجا یک سری Order هم درنظر گرفته شده‌اند تا بتوان ترتیب نمایش صفحات را نیز به دلخواه تغییر داد.
نمونه‌ای از استفاده‌ی از ویژگی فوق را در مسیر src\Client\Pages\Feature1 می‌توانید مشاهده کنید که خلاصه‌ی آن به صورت زیر است:
@attribute [ProtectedPage(GroupName = "Feature 1", Title = "Page 1", GlyphIcon = "bi bi-dot", GroupOrder = 1, ItemOrder = 1)]
ویژگی ProtectedPage را معادل یک ویژگی Authorize سفارشی، به همراه چند خاصیت بیشتر، جهت منوساز پویای برنامه درنظر بگیرید.
نیاز به لیست کردن صفحات علامتگذاری شده‌ی با ویژگی ProtectedPage
پس از اینکه صفحات مختلف برنامه را توسط ویژگی ProtectedPage علامتگذاری کردیم، اکنون نوبت به لیست کردن پویای آنها است. اینکار توسط سرویس ProtectedPagesProvider صورت می‌گیرد. این سرویس با استفاده از Reflection، ابتدا تمام IComponentها یا همان کامپوننت‌های تعریف شده‌ی در برنامه را از اسمبلی جاری استخراج می‌کند. بنابراین اگر نیاز دارید که این جستجو در چندین اسمبلی صورت گیرد، فقط کافی است ابتدای این کدها را تغییر دهید. پس از یافت شدن IComponent ها، فقط آن‌هایی که دارای RouteAttribute هستند، پردازش می‌شوند؛ یعنی کامپوننت‌هایی که به همراه مسیریابی هستند. پس از آن بررسی میشود که آیا این کامپوننت دارای ProtectedPageAttribute هست یا خیر؟ اگر بله، این کامپوننت در لیست نهایی درج خواهد شد.
نیاز به یک منوساز پویا جهت نمایش خودکار صفحات امن سازی شده‌ی با ویژگی ProtectedPage
اکنون که لیست صفحات امن سازی شده‌ی توسط ویژگی ProtectedPage را در اختیار داریم، می‌توانیم آنها را توسط کامپوننت سفارشی NavBarDynamicMenus به صورت خودکار نمایش دهیم. این کامپوننت لیست صفحات را توسط کامپوننت NavBarDropdownMenu نمایش میدهد.
تهیه‌ی جداول و سرویس‌های ثبت دسترسی‌های پویای سمت کلاینت
جداول و فیلدهای مورد استفاده‌ی در این پروژه را در تصویر فوق ملاحظه می‌کنید که در پوشه‌ی src\Server\Entities نیز قابل دسترسی هستند. در این برنامه نیاز به ذخیره سازی اطلاعات نقش‌های کاربران مانند نقش Admin، ذخیره سازی سطوح دسترسی پویای سمت کلاینت و همچنین سمت سرور است. بنابراین بجای اینکه به ازای هر کدام، یک جدول جداگانه را تعریف کنیم، می‌توان از همان طراحی ASP.NET Core Identity مایکروسافت با استفاده از جدول UserClaimها ایده گرفت. یعنی هر کدام از این موارد، یک Claim خواهند شد:
در اینجا نقش‌ها با Claim استانداردی به نام http://schemas.microsoft.com/ws/2008/06/identity/claims/role که توسط خود مایکروسافت نامگذاری شده و سیستم‌های اعتبارسنجی آن بر همین اساس کار می‌کنند، قابل مشاهده‌است. همچنین دو Claim سفارشی دیگر ::DynamicClientPermission:: برای ذخیره سازی اطلاعات صفحات محافظت شده‌ی سمت کلاینت و ::DynamicServerPermission:: جهت ذخیره سازی اطلاعات اکشن متدهای محافظت شده‌ی سمت سرور نیز تعریف شده‌اند. رابطه‌ای این اطلاعات با جدول کاربران، many-to-many است.
به این ترتیب است که مشخص میشود کدام کاربر، به چه claimهایی دسترسی دارد.
برای کار با این جداول، سه سرویس UsersService، UserClaimsService و UserTokensService پیش بینی شده‌اند. UserTokens اطلاعات توکن‌های صادر شده‌ی توسط برنامه را ذخیره می‌کند و توسط آن می‌توان logout سمت سرور را پیاده سازی کرد؛ از این جهت که JWTها متکی به خود هستند و تا زمانیکه منقضی نشوند، در سمت سرور پردازش خواهند شد، نیاز است بتوان به نحوی اگر کاربری غیرفعال شد، از آن ثانیه به بعد، توکن‌های او در سمت سرور پردازش نشوند که به این نکات در مطلب «اعتبارسنجی مبتنی بر JWT در ASP.NET Core 2.0 بدون استفاده از سیستم Identity» پیشتر پرداخته شده‌است.
اطلاعات این سرویس‌ها توسط اکشن متدهای UsersAccountManagerController، در اختیار برنامه‌ی کلاینت قرار می‌گیرند.
نیاز به قسمت مدیریتی ثبت دسترسی‌های پویای سمت کلاینت و سرور
قبل از اینکه بتوان قسمت‌های مختلف کامپوننت NavBarDynamicMenus را توضیح داد، نیاز است ابتدا یک قسمت مدیریتی را جهت استفاده‌ی از لیست ProtectedPageها نیز تهیه کرد:
در این برنامه، کامپوننت src\Client\Pages\Identity\UsersManager.razor کار لیست کردن کاربران، که اطلاعات آن‌را از کنترلر UsersAccountManagerController دریافت می‌کند، انجام میدهد. در مقابل نام هر کاربر، دو دکمه‌ی ثبت اطلاعات پویای دسترسی‌های سمت کلاینت و سمت سرور وجود دارد. سمت کلاینت آن توسط کامپوننت UserClientSidePermissions.razor مدیریت میشود و سمت سرور آن توسط UserServerSidePermissions.razor.
کامپوننت UserClientSidePermissions.razor، همان لیست صفحات محافظت شده‌ی توسط ویژگی ProtectedPage را به صورت گروه بندی شده و به همراه یک سری chekmark، ارائه میدهد. اگر در اینجا صفحه‌ای انتخاب شد، اطلاعات آن به سمت سرور ارسال میشود تا توسط Claim ای به نام ::DynamicClientPermission:: به کاربر انتخابی انتساب داده شود.
شبیه به همین عملکرد در مورد دسترسی سمت سرور نیز برقرار است. UserServerSidePermissions.razor، لیست اکشن متدهای محافظت شده را از کنترلر DynamicPermissionsManagerController دریافت کرده و نمایش میدهد. این اطلاعات توسط سرویس ApiActionsDiscoveryService جمع آوری میشود. همچنین این اکشن متدهای ویژه نیز باید با ویژگی Authorize(Policy = CustomPolicies.DynamicServerPermission) مزین شده باشند که نمونه مثال آنها را در مسیر src\Server\Controllers\Tests می‌توانید مشاهده کنید. اگر در سمت کلاینت و قسمت مدیریتی آن، اکشن متدی جهت کاربر خاصی انتخاب شد، اطلاعات آن ذیل Claimای به نام ::DynamicServerPermission:: به کاربر انتخابی انتساب داده میشود.
بازگشت اطلاعات پویای دسترسی‌های سمت کلاینت از API
تا اینجا کامپوننت‌های امن سازی شده‌ی سمت کلاینت و اکشن متدهای امن سازی شده‌ی سمت سرور را توسط صفحات مدیریتی برنامه، به کاربران مدنظر خود انتساب دادیم و توسط سرویس‌های سمت سرور، اطلاعات آنها را در بانک اطلاعاتی ذخیره کردیم. اکنون نوبت به استفاده‌ی از claims تعریف شده و مرتبط با هر کاربر است. پس از یک لاگین موفقیت آمیز توسط UsersAccountManagerController، سه توکن به سمت کاربر ارسال می‌شوند:
- توکن دسترسی: اطلاعات اعتبارسنجی کاربر به همراه نام و نقش‌های او در این توکن وجود دارند.
- توکن به روز رسانی: هدف از آن، دریافت یک توکن دسترسی جدید، بدون نیاز به لاگین مجدد است. به این ترتیب کاربر مدام نیاز به لاگین مجدد نخواهد داشت و تا زمانیکه refresh token منقضی نشده‌است، برنامه میتواند از آن جهت دریافت یک access token جدید استفاده کند.
- توکن سطوح دسترسی پویای سمت کلاینت: در اینجا لیست ::DynamicClientPermission::ها به صورت یک توکن مجزا به سمت کاربر ارسال میشود. این اطلاعات به توکن دسترسی اضافه نشده‌اند تا بی‌جهت حجم آن اضافه نشود؛ از این جهت که نیازی نیست تا به ازای هر درخواست HTTP به سمت سرور، این لیست حجیم claims پویای سمت کلاینت نیز به سمت سرور ارسال شود. چون سمت سرور از claims دیگری به نام ::DynamicServerPermission:: استفاده می‌کند.
اگر دقت کنید، هم refresh-token و هم DynamicPermissions هر دو به صورت JWT ارسال شده‌اند. می‌شد هر دو را به صورت plain و ساده نیز ارسال کرد. اما مزیت refresh token ارسال شده‌ی به صورت JWT، انجام اعتبارسنجی خودکار سمت سرور اطلاعات آن است که دستکاری سمت کلاینت آن‌را مشکل می‌کند.
این سه توکن توسط سرویس BearerTokensStore، در برنامه‌ی سمت کلاینت ذخیره و بازیابی می‌شوند. توکن دسترسی یا همان access token، توسط ClientHttpInterceptorService به صورت خودکار به تمام درخواست‌های ارسالی توسط برنامه الصاق خواهد شد.
مدیریت خودکار اجرای Refresh Token در برنامه‌های Blazor WASM
دریافت refresh token از سمت سرور تنها قسمتی از مدیریت دریافت مجدد یک access token معتبر است. قسمت مهم آن شامل دو مرحله‌ی زیر است:
الف) اگر خطاهای سمت سرور 401 و یا 403 رخ دادند، ممکن است نیاز به refresh token باشد؛ چون احتمالا یا کاربر جاری به این منبع دسترسی ندارد و یا access token دریافتی که طول عمر آن کمتر از refresh token است، منقضی شده و دیگر قابل استفاده نیست.
ب) پیش از منقضی شدن access token، بهتر است با استفاده از refresh token، یک access token جدید را دریافت کرد تا حالت الف رخ ندهد.
- برای مدیریت حالت الف، یک Policy ویژه‌ی Polly طراحی شده‌است که آن‌را در کلاس ClientRefreshTokenRetryPolicy مشاهده می‌کنید. در این Policy ویژه، هرگاه خطاهای 401 و یا 403 رخ دهند، با استفاده از سرویس جدید IClientRefreshTokenService، کار به روز رسانی توکن انجام خواهد شد. این Policy در کلاس program برنامه ثبت شده‌است. مزیت کار با Policyهای Polly، عدم نیاز به try/catch نوشتن‌های تکراری، در هر جائیکه از سرویس‌های HttpClient استفاده میشود، می‌باشد.
- برای مدیریت حالت ب، حتما نیاز به یک تایمر سمت کلاینت است که چند ثانیه پیش از منقضی شدن access token دریافتی پس از لاگین، کار دریافت access token جدیدی را به کمک refresh token موجود، انجام دهد. پیاده سازی این تایمر را در کلاس ClientRefreshTokenTimer مشاهده می‌کنید که محل فراخوانی و راه اندازی آن یا پس از لاگین موفق در سمت کلاینت و یا با ریفرش صفحه (فشرده شدن دکمه‌ی F5) و در کلاس آغازین ClientAuthenticationStateProvider می‌باشد.
نیاز به پیاده سازی Security Trimming سمت کلاینت
از داخل DynamicPermissions دریافتی پس از لاگین، لیست claimهای دسترسی پویای سمت کلاینت کاربر لاگین شده استخراج میشود. بنابراین مرحله‌ی بعد، استخراج، پردازش و اعمال این سطوح دسترسی پویای دریافت شده‌ی از سرور است.
سرویس BearerTokensStore، کار ذخیره سازی توکن‌های دریافتی پس از لاگین را انجام میدهد و سپس با استفاده از سرویس DynamicClientPermissionsProvider، توکن سوم دریافت شده که مرتبط با لیست claims دسترسی کاربر جاری است را پردازش کرده و تبدیل به یک لیست قابل استفاده می‌کنیم تا توسط آن بتوان زمانیکه قرار است آیتم‌های منوها را به صورت پویا نمایش داد، مشخص کنیم که کاربر، به کدامیک دسترسی دارد و به کدامیک خیر. عدم نمایش قسمتی از صفحه که کاربر به آن دسترسی ندارد را security trimming گویند. برای نمونه کامپوننت ویژه‌ی SecurityTrim.razor، با استفاده از نقش‌ها و claims یک کاربر، میتواند تعیین کند که آیا قسمت محصور شده‌ی صفحه توسط آن قابل نمایش به کاربر است یا خیر. این کامپوننت از متدهای کمکی AuthenticationStateExtensions که کار با user claims دریافتی از طریق JWTها را ساده می‌کنند، استفاده می‌کند. یک نمونه از کاربرد کامپوننت SecurityTrim را در فایل src\Client\Shared\MainLayout.razor می‌توانید مشاهده کنید که توسط آن لینک Users Manager، فقط به کاربران دارای نقش Admin نمایش داده میشود.
نحوه‌ی مدیریت security trimming منوی پویای برنامه، اندکی متفاوت است. DynamicClientPermissionsProvider لیست claims متعلق به کاربر را بازگشت میدهد. این لیست پس از لاگین موفقیت آمیز دریافت شده‌است. سپس لیست کلی صفحاتی را که در ابتدای برنامه استخراج کردیم، در طی حلقه‌ای از سرویس ClientSecurityTrimmingService عبور می‌دهیم. یعنی مسیر صفحه و همچنین دسترسی‌های پویای کاربر، مشخص هستند. در این بین هر مسیری که در لیست claims پویای کاربر نبود، در لیست آیتم‌های منوی پویای برنامه، نمایش داده نمی‌شود.
نیاز به قطع دسترسی به مسیرهایی در سمت کلاینت که کاربر به صورت پویا به آنها دسترسی ندارد
با استفاده از ClientSecurityTrimmingService، در حلقه‌ای که آیتم‌های منوی سایت را نمایش میدهد، موارد غیرمرتبط با کاربر جاری را حذف کردیم و نمایش ندادیم. اما این حذف، به این معنا نیست که اگر این آدرس‌ها را به صورت مستقیم در مرورگر وارد کند، به آنها دسترسی نخواهد داشت. برای رفع این مشکل، نیاز به پیاده سازی یک سیاست دسترسی پویای سمت کلاینت است. روش ثبت این سیاست را در کلاس DynamicClientPermissionsPolicyExtensions مشاهده می‌کنید. کلید آن همان CustomPolicies.DynamicClientPermission که در حین تعریف ProtectedPageAttribute به عنوان مقدار Policy پیش‌فرض مقدار دهی شد. یعنی هرگاه ویژگی ProtectedPage به صفحه‌ای اعمال شد، از این سیاست دسترسی استفاده می‌کند که پردازشگر آن DynamicClientPermissionsAuthorizationHandler است. این هندلر نیز از ClientSecurityTrimmingService استفاده می‌کند. در هندلر context.User جاری مشخص است. این کاربر را به متد تعیین دسترسی مسیر جاری به سرویس ClientSecurityTrimming ارسال می‌کنیم تا مشخص شود که آیا به مسیر درخواستی دسترسی دارد یا خیر؟
نیاز به قطع دسترسی به منابعی در سمت سرور که کاربر به صورت پویا به آنها دسترسی ندارد
شبیه به ClientSecurityTrimmingService سمت کلاینت را در سمت سرور نیز داریم؛ به نام ServerSecurityTrimmingService که کار آن، پردازش claimهایی از نوع ::DynamicServerPermission:: است که در صفحه‌ی مدیریتی مرتبطی در سمت کلاینت، به هر کاربر قابل انتساب است. هندلر سیاست دسترسی پویایی که از آن استفاده می‌کند نیز DynamicServerPermissionsAuthorizationHandler می‌باشد. این سیاست دسترسی پویا با کلید CustomPolicies.DynamicServerPermission در کلاس ConfigureServicesExtensions تعریف شده‌است. به همین جهت هر اکشن متدی که Policy آن با این کلید مقدار دهی شده باشد، از هندلر پویای فوق جهت تعیین دسترسی پویا عبور خواهد کرد. منطق پیاده سازی شده‌ی در اینجا، بسیار شبیه به مطلب «سفارشی سازی ASP.NET Core Identity - قسمت پنجم - سیاست‌های دسترسی پویا» است؛ اما بدون استفاده‌ی از ASP.NET Core Identity.
روش اجرای برنامه
چون این برنامه از نوع Blazor WASM هاست شده‌است، نیاز است تا برنامه‌ی Server آن‌را در ابتدا اجرا کنید. با اجرای آن، بانک اطلاعاتی SQLite برنامه به صورت خودکار توسط EF-Core ساخته شده و مقدار دهی اولیه میشود. لیست کاربران پیش‌فرض آن‌را در اینجا می‌توانید مشاهده کنید. ابتدا با کاربر ادمین وارد شده و سطوح دسترسی سایر کاربران را تغییر دهید. سپس بجای آنها وارد سیستم شده و تغییرات منوها و سطوح دسترسی پویا را بررسی کنید.
مقدمه
در اکثر موارد در یک Landscape عملیاتی، چنانچه به تجمیع و انتقال داده‌ها از بانک‌های اطلاعاتی مختلف نیاز باشد، از SSIS Package اختصار (SQL Server Integration Service) استفاده میشود و معمولاً با تعریف یک Job در سطح SQL Server به اجرای Package در زمانهای مشخص می‌پردازند. چنانچه در موقعیتی لازم باشد که از طریق برنامه کاربردی توسعه یافته، به اجرای Package مبادرت ورزیده شود و البته نخواهیم Job تعریف شده را از طریق کد برنامه، اجرا کنیم و در واقع این امکان را داشته باشیم که همانند یک رویه ذخیره شده تعریف شده در سطح بانک اطلاعاتی به اجرای عمل فوق بپردازیم، یک راه حل میتواند تعریف یک CLR Stored Procedures باشد. در این مقاله به بررسی این موضوع پرداخته میشود، در ابتدا لازم است به بیان تئوری موضوع پرداخته شود (قسمت‌های 1 الی 5) در ادامه به ذکر پیاده سازی روش پیشنهادی پرداخته میشود.
1- اجرای Integration Service Package
جهت اجرای یک Package از ابزارهای زیر می‌توان استفاده کرد:• command-line ابزار خط فرمان dtexec.exeتوجه: همچنین یک Package را در زمان طراحی در Business Intelligence Development Studio) BIDS) می‌توان اجرا نمود.
• ابزار اجرائی پکیج dtexecui.exe
• استفاده از SQL Server Agent job
2- استفاده از dtexec جهت اجرای Package
با استفاده از ابزار dtexec می‌توان Packageهای ذخیره شده در فایل سیستم، یک SQL Instance و یا Packageهای ذخیره شده در Integration Service را اجرا نمود.توجه: در سیستم عامل‌های 64 بیتی، ابزار dtexec موجود در Integration Service با نسخه 64 بیتی نصب میشود. چنانچه بایست Packageهای معینی را در حالت 32 بیتی اجرا کنید، لازم است ابزار dtexec نسخه 32 بیتی نصب شود. ابزار dtexec دستیابی به تمامی ویژگی‌های پیکربندی و اجرای Package از قبیل اتصالات، مشخصات(Properties)، متغیرها، logging و شاخص‌های پردازشی را فراهم می‌کند.
توجه: زمانی که از نسخه‌ی ابزار dtexec که با SQL Server 2008 ارائه شده استفاده می‌کنید برای اجرای یک SSIS Package نسخه 2005، Integration Service به صورت موقت Package را به نسخه 2008 ارتقا میدهد، اما نمی‌توان از ابزار dtexec برای ذخیره این تغییرات استفاده کرد.
2-1- ملاحظات نصب dtexec روی سیستم‌های 64 بیتی
به صورت پیش فرض، یک سیستم عامل 64 بیتی که هر دو نسخه 64 بیتی و 32 بیتی ابزار خط فرمان Integration Service را دارد، نسخه 32 بیتی نصب شده را در خط فرمان اجرا خواهد کرد. نسخه 32 بیتی بدین دلیل اجرا میشود که در متغیر محیطی (Path (Path environment variable مسیر directory نسخه 32 بیتی قرار گرفته است.به طور معمول:
(<drive>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn)
توجه: اگر از SQL Server Agent برای اجرای Package استفاده می‌کنید، SQL Server Agent به طور خودکار از ابزار نسخه 64 بیتی استفاده می‌کند. SQL Server Agent از Registry و نه از متغیر محیطی Path استفاده می‌کند. برای اطمینان از اینکه نسخه 64 بیتی این ابزار را در خط فرمان اجرا می‌کنید، directory را به directory ای تغییر دهید که شامل نسخه 64 بیتی این ابزار است(<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn) و ابزار را از این مسیر اجرا کنید و یا برای همیشه مسیر قرار گرفته در متغیر محیطی path را با مسیری که نسخه 64 بیتی قرار دارد، جایگزین کنید.2-2- تفسیر کدهای خروجی
هنگامی که یک Package اجرا میشود، dtexec یک کد خروجی (Return Code) بر می‌گرداند:| مقدار | توصیف |
| 0 | Package با موفقیت اجرا شده است. |
| 1 | Package با خطا مواجه شده است. |
| 3 | Package در حال اجرا توسط کاربر لغو شده است. |
| 4 | Package پیدا نشده است. |
| 5 | Package بارگذاری نشده است. |
| 6 | ابزار با یک خطای نحوی یا خطای معنایی در خط فرمان برخورد کرده است. |
2-3- قوانین نحوی dtexec
تمامی گزینه‌ها (Options) باید با یک علامت Slash (/) و یا Minus (-) شروع شوند.یک آرگومان باید در یک quotation mark محصور شود چنانچه شامل یک فاصله خالی باشد.
گزینه‌ها و آرگومان‌ها بجز رمزعبور حساس به حروف کوچک و بزرگ نیستند.
2-3-1- Syntax
dtexec /option [value] [/option [value]]…
2-3-2- Parameters
نکته: در Integration Service، ابزار خط فرمان dtsrun که برایData Transformation Service) DTS)های نسخه SQL Server 2000 استفاده می‌شد، با ابزار خط فرمان dtexec جایگزین شده است.• تعدادی از گزینه‌های خط فرمان dtsrun به طور مستقیم در dtexec معادل دارند برای مثال نام Server و نام Package.
• تعدادی از گزینه‌های dtsrun به طور مستقیم در dtexec معادل ندارند.
• تعدادی گزینه‌های خط فرمان جدید dtsexec وجود دارد که در ویژگی‌های جدید Integration Service پشتیبانی میشود.
2-3-3- مثال
1) به منظور اجرای یک SSIS Package که در SQL Server ذخیره شده است، با استفاده از Windows Authentication :dtexec /sq <Package Name> /ser <Server Name>
2) به منظور اجرای یک SSIS Package که در پوشه File System در SSIS Package Store ذخیره شده است :
dtexec /dts “\File System\<Package File Name>”
3) به منظور اجرای یک SSIS Package که در سیستم فایل ذخیره شده است و مشخص کردن گزینه logging:
dtexec /f “c:\<Package File Name>” /l “DTS.LogProviderTextFile; <Log File Name>”
4) به منظور اجرای یک SSIS Package که در SQL Server ذخیره شده با استفاده از SQL Server Authentication برای نمونه(user:ssis;pwd:ssis@ssis)و رمز (Package(123:
dtexec /server “<Server Name>” /sql “<Package Name>” / user “ssis” /Password “ssis@ssis” /De “123”
3- تنظیمات سطح حفاظتی یک Package
به منظور حفاظت از داده‌ها در Packageهای Integration Service می‌توانید یک سطح حفاظتی (protection level) را تنظیم کنید که به حفاظت از داده‌های صرفاً حساس یا تمامی داده‌های یک Package کمک نماید. به علاوه می‌توانید این داده‌ها را با یک Password یا یک User Key رمزگذاری نمائید یا به رمزگذاری داده‌ها در بانک اطلاعاتی اعتماد کنید. همچنین سطح حفاظتی که برای یک Package استفاده می‌کنید، الزاماً ایستا (static) نیست و در طول چرخه حیات یک Package میتواند تغییر کند. اغلب سطح حفاظتی در طول توسعه یا به محض (deploy) استقرار Package تنظیم میشود.توجه: علاوه بر سطوح حفاظتی که توصیف شد، Packageها در بانک اطلاعاتی msdb ذخیره می‌شوند که همچنین می‌توانند توسط نقش‌های ثابت در سطح بانک اطلاعاتی (fixed database-level roles) حفاظت شوند. Integration Service شامل 3 نقش ثابت بانک اطلاعاتی برای نسبت دادن مجوزها به Package است که عبارتند از db_ssisadmin ،db_ssisltduser و db_ssisoperator
3-1- درک سطوح حفاظتی
در یک Package اطلاعات زیر به عنوان حساس تعریف می‌شوند:• بخش password در یک connection string. گرچه، اگر گزینه ای را که همه چیز را رمزگذاری کند، انتخاب کنید تمامی connection string حساس در نظر گرفته میشود.
• گره‌های task-generated XML که برچسب (tagged) هایی حساس هستند.
• هر متغییری که به عنوان حساس نشان گذاری شود.
3-1-1- Do not save sensitive
هنگامی که Package ذخیره میشود از ذخیره مقادیر ویژگی‌های حساس در Package جلوگیری می‌کند. این سطح حفاظتی رمزگذاری نمی‌کند اما در عوض از ذخیره شدن ویژگی هایی که حساس نشان گذاری شده اند به همراه Package جلوگیری می‌کند.3-1-2- Encrypt all with password
به منظور رمزگذاری تمامی Package از یک Password استفاده میشود. Package توسط Password ای رمزگذاری میشود که کاربر هنگامی که Package را ایجاد یا Export می‌کند، ارائه میدهد. به منظور باز کردن Package در SSIS Designer یا اجرای Package توسط ابزار خط فرمان dtexec کاربر بایست رمز Package را ارائه نماید. بدون رمز کاربر قادر به دستیابی و اجرای Package نیست.3-1-3- Encrypt all with user key
به منظور رمزگذاری تمامی Package از یک کلید که مبتنی بر Profile کاربر جاری می‌باشد، استفاده میشود. تنها کاربری که Package را ایجاد یا Export می‌کند، میتواند Package را در SSIS Designer باز کند و یا Package را توسط ابزار خط فرمان dtexec اجرا کند.3-1-4- Encrypt sensitive with password
به منظور رمزگذاری تنها مقادیر ویژگی‌های حساس در Package از یک Password استفاده میشود. برای رمزگذاری از DPAPI استفاده میشود. داده‌های حساس به عنوان بخشی از Package ذخیره می‌شوند اما آن داده‌ها با استفاده از Password رمزگذاری می‌شوند. به منظور باز نمودن Package در SSIS Designer کاربر باید رمز Package را ارائه دهد. اگر رمز ارائه نشود، Package بدون داده‌های حساس باز میشود و کاربر باید مقادیر جدیدی برای داده‌های حساس فراهم کند. اگر کاربر سعی نماید Package را بدون ارائه رمز اجرا کند، اجرای Package با خطا مواجه میشود.3-1-5- Encrypt sensitive with user key
به منظور رمزگذاری تنها مقادیر ویژگی‌های حساس در Package از یک کلید که مبتنی بر Profile کاربر جاری می‌باشد، استفاده میشود. تنها کاربری که از همان Profile استفاده می‌کند، Package را میتواند بارگذاری (load) کند. اگر کاربر متفاوتی Package را باز نماید، اطلاعات حساس با مقادیر پوچی جایگزین میشود و کاربر باید مقادیر جدیدی برای داده‌های حساس فراهم کند. اگر کاربر سعی نماید Package را بدون ارائه رمز اجرا کند، اجرای Package با خطا مواجه میشود. برای رمزگذاری از DPAPI استفاده میشود.3-1-6- (Rely on server storage for encryption (ServerStorage
با استفاده از نقش‌های بانک اطلاعاتی، SQL Server تمامی Package را حفاظت می‌کند. این گزینه تنها زمانی پشتیبانی میشود که Package در بانک اطلاعاتی msdb ذخیره شده است.4- استفاده از نقش‌های Integration Service
برای کنترل کردن دستیابی به Package، SSIS شامل 3 نقش ثابت در سطح بانک اطلاعاتی است. نقش‌ها می‌توانند تنها روی Package هایی که در بانک اطلاعاتی msdb ذخیره شده اند، بکار روند. با استفاده از SSMS می‌توانید نقش‌ها را به Packageها نسبت دهید، این انتساب نقش‌ها در بانک اطلاعاتی msdb ذخیره میشود.| Write action | Read action | Role |
| Import packages Delete own packages Delete all packages Change own package roles Change all package roles * به نکته رجوع شود | Enumerate own packages Enumerate all packages View own packages View all packages Execute own packages Execute all packages Export own packages Export all packages Execute all packages in SQL Server Agent | db_ssisadmin or sysadmin |
| Import packages Delete own packages Change own package roles | Enumerate own packages Enumerate all packages View own packages Execute own packages Export own packages | db_ssisltduser |
| None | Enumerate all packages View all packages Execute all packages Export all packages Execute all packages in SQL Server Agent | db_ssisoperator |
| Stop all currently running packages | View execution details of all running packages | Windows administrators |
همچنین جدول sysssispackages در بانک اطلاعاتی msdb شامل Package هایی است که در SQL Server ذخیره می‌شوند. این جدول شامل ستون هایی که اطلاعاتی درباره نقش هایی که به Packageها نسبت داده شده است، می‌باشد.
به صورت پیش فرض، مجوزهای نقش‌های ثابت بانک اطلاعاتی db_ssisadmin و db_ssisoperator و شناسه منحصر به فرد کاربری (unique security identifier) که Package را ایجاد کرده برای خواندن Package بکار می‌رود، و مجوزهای نقش db_ssisadmin و شناسه منحصر به فرد کاربری که Package را ایجاد کرده برای نوشتن Package به کار می‌رود. یک User باید عضو نقش db_ssisadmin و db_ssisltduser یا db_ssisoperator برای داشتن دسترسی خواندن Package باشد. یک User باید عضو نقش db_ssisadmin برای داشتن دسترسی نوشتن Package باشد.
5- اتصال به صورت Remote به Integration Service
زمانی که یک کاربر بدون داشتن دسترسی کافی تلاش کند به یک Integration Service به صورت Remote متصل شود، با پیغام خطای "Access is denied" مواجه میشود. برای اجتناب از این پیغام خطا می‌توان تضمین کرد که کاربر مجوز مورد نیاز DCOM را دارد.به منظور پیکربندی کردن دسترسی کاربر به صورت Remote به سرویس Integration مراحل زیر را دنبال کنید:
- Component Service را باز نمایید ( در Run عبارت dcomcnfg را تایپ کنید).مجوز دسترسی Lunch به منظور شروع و خاتمه سرویس، اعطا یا رد میشود و مجوز دسترسیActivation به منظور متصل شدن به سرویس، اعطا (grant) یا رد (deny) میشود.
- گره Component Service را باز کنید، گره Computer و سپس My Computer را باز نمایید و روی DCOM Config کلیک نمایید.
- گره DCOM Config را باز کنید و از لیست برنامه هایی که می‌توانند پیکربندی شوند MsDtsServer را انتخاب کنید.
- روی Properties برنامه MsDtsServer رفته و قسمت Security را انتخاب کنید.
- در قسمت Lunch and Activation Permissions، مورد Customize را انتخاب و سپس روی Edit کلیک نمایید تا پنجره Lunch Permission باز شود.
- در پنجره Lunch Permission، کاربران را اضافه و یا حذف کنید و مجوزهای مناسب را به کاربران یا گروه‌های مناسب نسبت دهید. مجوزهای موجود عبارتند از Local Lunch، Remote Lunch، Local Activation و Remote Activation .
- در قسمت Access Permission مراحل فوق را به منظور نسبت دادن مجوزهای مناسب به کاربران یا گروه‌های مناسب انجام دهید.
- سرویس Integration را Restart کنید.
6- پیاده سازی
در ابتدا به ایجاد یک CLR Stored Procedures پرداخته میشود نام اسمبلی ساخته شده به این نام RunningPackage.dll می‌باشد و حاوی کد زیر است:Partial Public Class StoredProcedures
'------------------------------------------------
'exec dbo.Spc_NtDtexec 'Package','ssis','ssis@ssis','1234512345'
'------------------------------------------------
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Spc_NtDtexec(ByVal PackageName As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal Decrypt As String)
Dim p As New System.Diagnostics.Process()
p.StartInfo.FileName = "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"
p.StartInfo.RedirectStandardOutput = True
p.StartInfo.Arguments = "/sql " & PackageName & " /User " & UserName & " /Password " & Password & " /De " & Decrypt
p.StartInfo.UseShellExecute = False
p.Start()
p.WaitForExit()
Dim output As String
output = p.StandardOutput.ReadToEnd()
Microsoft.SqlServer.Server.SqlContext.Pipe.Send(output)
End Sub
End Class در قدم بعدی نیاز به Register کردن dll ساخته شده در سطح بانک اطلاعاتی SQL Server است، این گام‌ها پس از اتصال به SQL Server Management Studio به شرح زیر است:
1- فعال کردن CLR در سرویس SQL Server
SP_CONFIGURE 'clr enabled',1 GO RECONFIGURE
2- فعال کردن ویژگی TRUSTWORTHY در بانک اطلاعاتی مورد نظر
ALTER DATABASE <Database Name> SET TRUSTWORTHY ON GO RECONFIGURE
3- ایجاد Assembly و Stored Procedure در بانک اطلاعاتی مورد نظر
Assembly ساخته شده با نام RunningPacakge.dll در ریشه :C کپی شود. بعد از ثبت نمودن این Assembly لزومی به وجود آن نمی‌باشد.
USE <Database Name> GO CREATE ASSEMBLY [RunningPackage] AUTHORIZATION [dbo] FROM 'C:\RunningPackage.dll' WITH PERMISSION_SET = UNSAFE Go CREATE PROCEDURE [dbo].[Spc_NtDtexec] @PackageName [nvarchar](50), @UserName [nvarchar](50), @Password [nvarchar](50), @Decrypt [nvarchar](50) WITH EXECUTE AS CALLER AS EXTERNAL NAME [RunningPackage].[RunningPackage.StoredProcedures].[Spc_NtDtexec] GO
در برنامه کاربردی تان کافی است متدی به شکل زیر ایجاد و با توجه به نیازتان در برنامه به فراخوانی آن و اجرای Package بپردازید.
Private Sub ExecutePackage()
Dim oSqlConnection As SqlClient.SqlConnection
Dim oSqlCommand As SqlClient.SqlCommand
Dim strCnt As String = String.Empty
strCnt = "Data Source=" & txtServer.Text & ";User ID=" & txtUsername.Text & ";Password=" & txtPassword.Text & ";Initial Catalog=" & cmbDatabaseName.SelectedValue.ToString() & ";"
Try
oSqlConnection = New SqlClient.SqlConnection(strCnt)
oSqlCommand = New SqlClient.SqlCommand
With oSqlCommand
.Connection = oSqlConnection
.CommandType = System.Data.CommandType.StoredProcedure
.CommandText = "dbo.Spc_NtDtexec"
.Parameters.Clear()
.Parameters.Add("@PackageName", System.Data.SqlDbType.VarChar, 50)
.Parameters.Add("@UserName", System.Data.SqlDbType.VarChar, 50)
.Parameters.Add("@Password", System.Data.SqlDbType.VarChar, 50)
.Parameters.Add("@Decrypt", System.Data.SqlDbType.VarChar, 50)
.Parameters("@PackageName").Value = txtPackageName.Text.Trim()
.Parameters("@UserName").Value = txtUsername.Text.Trim()
.Parameters("@Password").Value = txtPassword.Text.Trim()
.Parameters("@Decrypt").Value = txtDecrypt.Text.Trim()
End With
If (oSqlCommand.Connection.State <> System.Data.ConnectionState.Open) Then
oSqlCommand.Connection.Open()
oSqlCommand.ExecuteNonQuery()
System.Windows.Forms.MessageBox.Show("Success")
End If
If (oSqlCommand.Connection.State = System.Data.ConnectionState.Open) Then
oSqlCommand.Connection.Close()
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Error")
End Try
End Sub 'ExecutePackage 
Window Functionها برای اولین بار در نسخه SQL Server 2005 ارائه گردیدند، و در ورژن‌های جدیدتر SQL Server، به تعداد این فانکشنها افزوده شده است.
تعریف Window Function :
معمولا از این نوع فانکشنها روی مجموعه ای از ROWهای یک جدول، در جهت اعمال عملیاتهای محاسباتی ،ارزیابی داده ها، رتبه بندی و غیرو... استفاده می‌گردد، به بیان ساده‌تر بوسیله Window Functionها می‌توان، ROWهای یک جدول را گروه بندی نمود. و روی گروه‌ها از توابع جمعی (Aggregate Functions ) استفاده کرد. این نوع فانکشنها از قابلیت و انعطاف پذیری زیادی برخوردار می‌باشند، و بوسیله آنها می‌توان نتایج (خروجی) بسیار مفیدی از Query ها، بدست آورد، معمولا از این نوع فانکشنها در Data Mining (داده کاوی) و گزارشگیری‌ها استفاده می‌گردد. و آگاهی و روش استفاده از Window Functionها برای برنامه نویسان و DBA ها، میتواند بسیار مفید باشد.
مفهوم Window Function مطابق استاندارد ISO و ANSI می‌باشد، و دیتابیس هایی همچون Oracle،DB2،Sybase از آن پشتیبانی می‌نمایند.برای اطلاعات بیشتر می‌توانید به سایت‌های زیر مراجعه کنید:
کلمه "Window" در Window Function، به مجموعه ROW هایی اشاره می‌کند، که محاسبات و ارزیابی و غیرو... روی آنها اعمال می‌گردد.
Window Functionها برای ارائه قابلیت‌های خود، از Over Clause استفاده می‌کنند. اگر مقاله آشنایی با Row_Number،Rank،Dense_Rank،NTILE را مطالعه کرده باشید، می‌توان هریک از آنها را یک Window Function دانست.
برای شروع، به بررسی Over Clause می‌پردازیم، و Syntax آن به شرح ذیل می‌باشد:
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
<PARTITION BY clause> ::=
PARTITION BY value_expression , ... [ n ]
<ORDER BY clause> ::=
ORDER BY order_by_expression
[ COLLATE collation_name ]
[ ASC | DESC ]
[ ,...n ]
<ROW or RANGE clause> ::=
{ ROWS | RANGE } <window frame extent>
<window frame extent> ::=
{ <window frame preceding>
| <window frame between>
}
<window frame between> ::=
BETWEEN <window frame bound> AND <window frame bound>
<window frame bound> ::=
{ <window frame preceding>
| <window frame following>
}
<window frame preceding> ::=
{
UNBOUNDED PRECEDING
| <unsigned_value_specification> PRECEDING
| CURRENT ROW
}
<window frame following> ::=
{
UNBOUNDED FOLLOWING
| <unsigned_value_specification> FOLLOWING
| CURRENT ROW
}
<unsigned value specification> ::=
{ <unsigned integer literal> }
OVER دارای سه آرگومان اختیاری است که هر کدام را به تفصیل بررسی می‌کنیم:
1- PARTITION BY clause : بوسیله این پارامتر می‌توانیم Rowهای یک جدول را گروه بندی نماییم. این پارامتر یک value_expression می پذیرد. یک Value_expression میتواند نام یک ستون ، یک Scalar Subquery ، Scalar Function و غیرو باشد.
2- ORDER BY clause : از نامش مشخص است و برای Sort استفاده میشود، و ویژگی‌های Order By در آن اعمال می‌گردد. به جز Offset.
3- ROW or RANGE clause :این پارامتر بیشتر برای محدود نمودن Row در یک Partition (گروه) مورد استفاده قرار می‌گیرد، به عنوان مثال نقطه شروع و پایان را می‌توان بوسیله پارامتر فوق تعیین نمود.
Row و Range نسبت به هم یک تفاوت عمده دارند،و آن این است که، اگر از ROW Clause استفاده نمایید، ارتباط ROWهای قبلی یا بعدی، نسبت به Row جاری،بصورت فیزیکی (physical association ) سنجیده میشود، بطوریکه با استفاده از Range Clause ارتباط سطرهای قبلی و بعدی، نسبت به سطر جاری بصورت منطقی (logical association ) در نظر گرفته میشود. ممکن است درک این مطلب کمی سخت باشد، در ادامه با مثالهایی که بررسی می‌نماییم، براحتی تفاوت این دو را متوجه می‌شوید.
Row یا Range در قالب‌های متفاوتی مقدار می‌پذیرند، که هر کدام را بررسی می‌کنیم:
UNBOUNDED PRECEDING : بیانگر اولین سطر Partition می‌باشد. UNBOUNDED PRECEDING فقط نقطه شروع را مشخص می‌نماید.
UNBOUNDED FOLLOWING : بیانگر آخرین سطر Partition می‌باشد. UNBOUNDED FOLLOWING فقط نقطه پایانی را مشخص می‌نماید.
CURRENT ROW : اولین سطر جاری یا آخرین سطر جاری را مشخص می‌نماید.
n PRECEDING یا unsigned value specification> PRECEDING> : تعداد سطرهای قبل از سطر جاری را تعیین می‌کند، n یا <unsigned value specification>تعداد سطرهای قبل از سطر جاری را تعیین می‌نماید. از n PRECEDING نمی توان برای Range استفاده نمود.
n FOLLOWING یا unsigned value specification> FOLLOWING> : تعداد سطرهای بعد از سطر جاری را تعیین می‌کند، n یا<unsigned value specification> تعداد سطر های بعد از سطر جاری را تعیین می‌نماید. از n FOLLOWING نمی توان برای Range استفاده نمود.
<BETWEEN <window frame bound > AND <window frame bound : از چارچوب فوق برای Range و Row می‌توان استفاده نمود، و نقطه آغازین و نقطه پایانی توسط قالب فوق تعیین می‌گردد. نکته قابل توجه آن است که نقطه پایانی نمی‌تواند، کوچکتر از نقطه آغازین گردد.
در ادامه برای درک هرچه بیشتر تعاریف بیان شده، چندین مثال می‌زنیم و هر کدام را بررسی می‌نماییم:
در ابتدا Script زیر را اجرا نمایید، که شامل جدولی به نام Revenue (سود،درآمد) و درج چند درکورد در آن:
CREATE TABLE REVENUE ( [DepartmentID] int, [Revenue] int, [Year] int ); insert into REVENUE values (1,10030,1998),(2,20000,1998),(3,40000,1998), (1,20000,1999),(2,60000,1999),(3,50000,1999), (1,40000,2000),(2,40000,2000),(3,60000,2000), (1,30000,2001),(2,30000,2001),(3,70000,2001)
مثال اول : می‌خواهیم براساس فیلد DepartmentID جدول Revenue را Partition بندی نماییم و از توابع جمعی AVG و SUM روی فیلد درآمد(Revenue) استفاده کنیم.
ابتدا Script زیر را اجرا می‌کنیم:
select *, avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue, sum(Revenue) OVER (PARTITION by DepartmentID) as TotalRevenue from REVENUE order by departmentID, year;
خروجی بصورت زیر خواهد بود:
مطابق شکل، جدول براساس فیلد DepartmentID به سه Partition تقسیم شده است، و عملیات میانگین و جمع روی فیلد Revenue انجام شده است و عملیات Sort روی هرگروه بطور مستقل انجام گرفته است. چنین کاری را نمی‌توانستیم بوسیله Group By انجام دهیم.
مثال دوم : نحوه استفاده از ROWS PRECEDING،در این مثال قصد داریم عملیات جمع را روی فیلدRevenue انجام دهیم. بطوریکه جمع هر مقدار برابر است با سه مقدار قبلی + مقدار جاری:
لطفا رکوردهای زیر را به جدول فوق درج نمایید:
insert into REVENUE values(1,90000,2002),(2,20000,2002),(3,80000,2002), (1,10300,2003),(2,1000,2003), (3,90000,2003), (1,10000,2004),(2,10000,2004),(3,10000,2004), (1,20000,2005),(2,20000,2005),(3,20000,2005), (1,40000,2006),(2,30000,2006),(3,30000,2006), (1,70000,2007),(2,40000,2007),(3,40000,2007), (1,50000,2008),(2,50000,2008),(3,50000,2008), (1,20000,2009),(2,60000,2009),(3,60000,2009), (1,30000,2010),(2,70000,2010),(3,70000,2010), (1,80000,2011),(2,80000,2011),(3,80000,2011), (1,10000,2012),(2,90000,2012),(3,90000,2012)
سپس Script زیر را اجرا می‌نماییم:
select Year, DepartmentID, Revenue,
sum(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR]
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as Prev3
From REVENUE order by departmentID, year;
خروجی :
در Script بالا، جدول را براساس فیلد DepartmentID گروه بندی می‌کنیم، که سه گروه ایجاد میشود، هر گروه را بطور مستقل، روی فیلد Year بصورت صعودی مرتب می‌نماییم. حال برای آنکه بتوانیم سیاست جمع، روی فیلد Revenue، را پیاده سازی نماییم ، قطعه کد زیر را در Script بالا اضافه کردیم.
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as Prev3
برای شرح چگونگی استفاده از PRECEDING،فقط به شرح گروه اول بسنده می‌کنیم. مقدار جمع فیلد Revenue سطر اول، که قبل از آن سطری وجود ندارد، برابر است با مقدار خود، یعنی 10030، مقدار جمع فیلد Revenue سطر دوم برابر است با حاصل جمع مقدار فیلدRevenue سطر اول و دوم ، یعنی 30030 . این روند تا سطر چهار ادامه دارد، اما برای بدست آوردن مقدار جمع فیلدRevenue سطر پنجم، مقدار جمع فیلد Revenue سطر دوم،سوم،چهارم و پنجم در نظر گرفته میشود، و مقدار فیلدRevenue سطر اول در حاصل جمع در نظر گرفته نمی‌شود،بنابراین مقدار جمع فیلد Revenue سطر پنجم برابر است با 180000. در صورت مسئله گفته بودیم، مقدار جمع فیلد Revenue هر سطر جاری برابر است با حاصل جمع مقدارسطر جاری و مقادیر سه سطر ماقبل خود.
مثال سوم: نحوه استفاده از ROWS FOLLOWING، این مثال عکس مثال دوم است، یعنی حاصل جمع مقدار فیلد Revenue هر سطر برابر است با حاصل جمع سطر جاری با سه سطر بعد از خود. بنابراین Script زیر را اجرا نمایید:
select Year, DepartmentID, Revenue,
sum(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR]
ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) as Next3
From REVENUE order by departmentID, year;
خروجی :
مطابق شکل مقدار جمع فیلد اول برابراست با حاصل جمع مقدار سطر جاری و سه سطر بعد از آن.
نکته ای که در مثالهای دوم و سوم،می بایست به آن توجه نمود، این است که در زمان استفاده از Row یا Range ، استفاده از Order by در Partition الزامی است، در غیر این صورت با خطا مواجه می‌شوید.
نحوه استفاده از UNBOUNDED PRECEDING ، این امکان در T-SQL Server 2012 افزوده شده است.
مثال چهار: در این مثال می‌خواهیم کمترین سود بدست آمده در چند سال را بدست آوریم:
ابتدا Script زیر را اجرا نمایید:
select Year, DepartmentID, Revenue,
min(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR]
ROWS UNBOUNDED PRECEDING) as MinRevenueToDate
From REVENUE order by departmentID, year;
خروجی:
طبق تعریف UNBOUNDED PRECEDING اولین سطر هر Partition را مشخص می‌نماید، و چون از PRECEDING استفاده کرده ایم، بنابراین مقایسه همیشه بین سطر جاری و سطرهای قبل از آن انجام می‌پذیرد. بنابراین خواهیم داشت، کمترین مقدار فیلد Revenue در سطر اول، برابر با مقدار خود می‌باشد، چون هیچ سطری ماقبل از آن وجود ندارد. در سطر دوم مقایسه کمترین مقدار، بین 20000 و 10030 انجام می‌گیرد، که برابر است با 10030، در سطر سوم، مقایسه بین مقادیر سطر اول،دوم و سطر سوم صورت می‌گیرد، یعنی کمترین مقدار بین 40000،20000 و 10030، بنابراین کمترین مقدار سطر سوم برابر است با 10030.
به بیان ساده‌تر برای بدست آوردن کمترین مقدار هر سطر، مقدار سطر جاری با مقادیر همه سطرهای ماقبل خود مقایسه می‌گردد.
برای بدست آوردن کمترین مقدار در سطر ششم، مقایسه بین مقادیر سطرهای اول،دوم،سوم،چهارم،پنجم و ششم صورت می‌گیرد که عدد 10000 بدست می‌آید و الی آخر...
نکنه: اگر در Over Clause شرط Order by را اعمال نماییم، اما از Row یا Range استفاده نکنیم، SQL Server بصورت پیش فرض از قالب زیر استفاده می‌نماید:
RANGE UNBOUNDED PRECEDING AND CURRENT ROW
برای روشن‌تر شدن مطلب فوق مثالی می‌زنیم:
ابتدا Script زیر را اجرا نمایید، که شامل ایجاد یک جدول و درج چند رکورد در آن می‌باشد:
CREATE TABLE Employees (
EmployeeId INT IDENTITY PRIMARY KEY,
Name VARCHAR(50),
HireDate DATE NOT NULL,
Salary INT NOT NULL
)
GO
INSERT INTO Employees (Name, HireDate, Salary)
VALUES
('Alice', '2011-01-01', 20000),
('Brent', '2011-01-15', 19000),
('Carlos', '2011-02-01', 22000),
('Donna', '2011-03-01', 25000),
('Evan', '2011-04-01', 18500)
GO
سپس Script زیر را اجرا نمایید:
SELECT
Name,
Salary,
AVG(Salary) OVER(ORDER BY HireDate) AS avgSalary
FROM Employees
GO
خروجی :
حال اگر Script زیر را نیز اجرا نمایید، خروجی آن مطابق شکل بالا خواهد بود:
SELECT
Name,
Salary,
AVG(Salary) OVER(ORDER BY HireDate
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS avgSalary
FROM Employees
GO
توضیح درباره Script بالا، در این روش برای بدست آوردن میانگین هر سطر، مقدار سطر جاری با مقادیر سطرهای ماقبل خود جمع و تقسیم بر تعداد سطر میشود.
سطر دوم 20000 + 19000 تقسیم بر دو برابر است با 19500
میانگین سطر پنجم، حاصل جمع فیلد Salary همه مقادیر سطرها تقسیم بر 5
*** اگر بخواهید بوسیله Over Clause ، میانگین همه سطرها یکسان باشد می‌توانید از Script زیر استفاده نمایید:
SELECT
Name,
Salary,
AVG(Salary) OVER(ORDER BY HireDate
RANGE
BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS avgSalary
FROM Employees
GO
خروجی :
منظور از ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING یعنی در محاسبه میانگین برای هر سطر تمامی مقادیر سطرهای دیگر در نظر گرفته شود.
پایان بخش اول
امیدوارم مفید واقع شده باشد.
تصویر فوق، یکی از تصویرهایی است که شاید از طریق ایمیل‌هایی تحت عنوان "فقط در ایران!" به دست شما هم رسیده باشد. تصور کاربر نهایی (که این ایمیل را با تعجب ارسال کرده) این است که در اینجا به او گفته شده مثلا "مرتضی" را جستجو نکنید و امثال آن. چون برای او تفاوتی بین ی و ی وجود ندارد. همچنین بکار بردن "اقلامی" هم کمی غلط انداز است و بیشتر ذهن را به سمت کلمه سوق میدهد تا حرف.
در ادامه‌ی بحث آلرژی مزمن به وجود انواع "ی" و "ک" در بانک اطلاعاتی (+ و + و +)، اینبار قصد داریم این اطلاعات را به NHibernate بسط دهیم. شاید یک روش اعمال یک دست سازی "ی" و "ک" این باشد که در کل برنامه هر جایی که قرار است update یا insert ایی صورت گیرد، خواص رشته‌ای را یافته و تغییر دهیم. این روش "کار می‌کنه" ولی ایده آل نیست؛ چون حجم کار تکراری در برنامه زیاد خواهد شد و نگهداری آن هم مشکل میشود. همچنین امکان فراموش کردن اعمال آن هم وجود دارد.
در NHibernate یک سری EventListener وجود دارند که کارشان گوش فرا دادن به یک سری رخدادها مانند مثلا update یا insert است. این رخدادها می‌توانند پیش یا پس از هرگونه ثبت یا ویرایشی در برنامه صادر شوند. بنابراین بهترین جایی که جهت اعمال این نوع ممیزی (Auditing) بدون بالا بردن حجم برنامه یا اضافه کردن بیش از حد یک سری کد تکراری در حین کار با NHibernate می‌توان یافت، روال‌های مدیریت کننده‌ی همین EventListener ها هستند.
کلاس YeKeAuditorEventListener نهایی با پیاده سازی IPreInsertEventListener و IPreUpdateEventListenerبه شکل زیر خواهد بود:
using NHibernate.Event;
namespace NHYeKeAuditor
{
public class YeKeAuditorEventListener : IPreInsertEventListener, IPreUpdateEventListener
{
// Represents a pre-insert event, which occurs just prior to performing the
// insert of an entity into the database.
public bool OnPreInsert(PreInsertEvent preInsertEvent)
{
var entity = preInsertEvent.Entity;
CorrectYeKe.ApplyCorrectYeKe(entity);
return false;
}
// Represents a pre-update event, which occurs just prior to performing the
// update of an entity in the database.
public bool OnPreUpdate(PreUpdateEvent preUpdateEvent)
{
var entity = preUpdateEvent.Entity;
CorrectYeKe.ApplyCorrectYeKe(entity);
return false;
}
}
}
تا اینجا فقط تعریف YeKeAuditorEventListener انجام شده است. اما NHibernate چگونه از وجود آن مطلع خواهد شد؟
برای تزریق کلاس YeKeAuditorEventListener به تنظیمات برنامه باید به شکل زیر عمل کرد:
using System;
using System.Linq;
using FluentNHibernate.Cfg;
using NHibernate.Cfg;
namespace NHYeKeAuditor
{
public static class MappingsConfiguration
{
public static FluentConfiguration InjectYeKeAuditorEventListener(this FluentConfiguration fc)
{
return fc.ExposeConfiguration(configListeners());
}
private static Action<Configuration> configListeners()
{
return
c =>
{
var listener = new YeKeAuditorEventListener();
c.EventListeners.PreInsertEventListeners =
c.EventListeners.PreInsertEventListeners
.Concat(new[] { listener })
.ToArray();
c.EventListeners.PreUpdateEventListeners =
c.EventListeners.PreUpdateEventListeners
.Concat(new[] { listener })
.ToArray();
};
}
}
}
کدهای NHYeKeAuditor را از اینجا می‌توانید دریافت کنید.
