آشنایی با NHibernate - قسمت ششم
برای مثال در قسمت هشتم یک سیستم many-to-many مثال زده شده است به همراه کوئریهایی از نوع Linq . اینجا فقط تعریف کلاسهایی که بیانگر روابط many-to-many باشند مهم است؛ نحوهی نگاشت خودکار آنها به دیتابیس کار Fluent NHibernate است. (از این نوع مثالها در هر قسمت پیاده سازی شده)
جزئیات ریز نحوهی نگاشت خودکار با مطالعهی سورس کتابخانه NHibernate و مشتقات آن قابل درک است (برای علاقمندان).
ضمنا فرقی نمیکند از Linq قسمت پنجم استفاده کنید یا هر روش موجود دیگری برای کوئری گرفتن (زمانیکه Linq هست و نگارشهای جدید آن برای NHibernate پیشرفت زیادی داشته، چرا روشهای دیگر؟).
نسخه نهایی Blazor WebAssembly 3.2.0 تا اواسط ماه May 2020 (ماه جاری) منتشر میشود
The Blazor WebAssembly Release Candidate is here! This release contains all of the features and improvements that we expect to release for the upcoming Blazor WebAssembly release. There are no more breaking changes planned at this point.
The April release of the Azure Data Studio. Included in this release is:
- Added a new Welcome page.
- Added new markdown toolbar for Notebooks.
- Announcing KQL Magic support in Notebooks.
- Charting in SQL Notebooks can now be saved.
- Announcing improvements to Azure Data Studio dashboards.
- Support for Always Encrypted.
- Bug fixes.
The key highlights to cover this month include:
- Announcing SQL Server 2019 support
- New notebook features
- Announcing PowerShell notebooks
- Announcing collapsible code cells
- Performance improvements in notebooks
- Announcing Jupyter Books
- General availability of Schema Compare and SQL Server Dacpac extensions
- Announcing Visual Studio IntelliCode extension
- Bug fixes
Rider EAP 17 منتشر شد .
What’s in this build? Significant performance improvements were made, shared projects are now supported, code inspection severity can be configured, settings for our unit test runner can be edited, various updates to the NuGet tool window UI, and more. Check out the list of fixes in this build if you feel like digging into details.
مثال 12: محاسبه کنید در سال 2012 و به ازای هر ماه مجزای آن، چه تعداد slots رزرو شدهاند؛ قسمت دوم.
این مثال را در قسمت قبل (مثال 6 آن) نیز بررسی کردیم. در اینجا میخواهیم در گزارش نهایی تولید شده، پس از اتمام ردیفهای یک ماه به ازای یک امکان خاص، جمع کل آن نیز درج شود و همچنین در پایان تمام ردیفها، جمع کل نهایی ذکر شود؛ چیزی شبیه به تصویر زیر که در آن 910، جمع کل slots ماه 8 است و 9191، جمع کل سال.
روش پیشنهادی حل این مساله استفاده از مفهومی به نام «GROUP BY ROLLUP» است:
SELECT facid, DATEPART(month, [StartTime]) AS month, sum(slots) AS slots FROM bookings WHERE starttime >= '2012-01-01' AND starttime < '2013-01-01' GROUP BY ROLLUP(facid, DATEPART(month, [StartTime])) ORDER BY facid, month;
ابتدا جمع slots را گروه بندی شده بر اساس هر ماه سال محاسبه میکنیم. این قسمت توسط LINQ to Entities قابل انجام است؛ همان مثال 6 قسمت قبل است.
سپس این اطلاعات که اکنون در سمت کلاینت (یعنی برنامهی ما) در حافظه موجود هستند، نیاز دارند به ازای هر گروه، یک جمع کل (sub total) و به ازای کل سال نیز یک جمع کل (grand total یا total) پیدا کنند.
ROLLUP(facid, month) اطلاعات تجمعی سلسه مراتبی پارامترهای ارسالی به آن را تولید میکند. یعنی (facid, month), (facid) و (). پیاده سازی LINQ to Objects این تابع را در اینجا میتوانید مشاهده کنید: Utils\GroupingExtensions.cs
بنابراین راه حل این مساله به صورت زیر خواهد بود:
var date1 = new DateTime(2012, 01, 01); var date2 = new DateTime(2013, 01, 01); var facilities = context.Bookings .Where(booking => booking.StartTime >= date1 && booking.StartTime < date2) .GroupBy(booking => new { booking.FacId, booking.StartTime.Month }) .Select(group => new { group.Key.FacId, group.Key.Month, TotalSlots = group.Sum(booking => booking.Slots) }) .OrderBy(result => result.FacId) .ThenBy(result => result.Month) .ToList() //This is new .GroupByWithRollup( item => item.FacId, item => item.Month, (primaryGrouping, secondaryGrouping) => new { FacId = primaryGrouping.Key, Month = secondaryGrouping.Key, TotalSlots = secondaryGrouping.Sum(item => item.TotalSlots) }, item => new { FacId = item.Key, Month = -1, TotalSlots = item.SubTotal(subItem => subItem.TotalSlots) }, items => new { FacId = -1, Month = -1, TotalSlots = items.GrandTotal(subItem => subItem.TotalSlots) });
در اینجا سلولهایی که اطلاعاتی ندارند، با منهای یک مشخص شدهاند؛ در گزارش اصلی با null مقدار دهی شده بودند.
مثال 13: به ازای نام هر کدام از امکانات موجود، جمع کل تعداد ساعات رزرو شدهی آنها را محاسبه کنید.
هر slot تنها نیم ساعت است و گزارش نهایی باید به همراه ستونهای facid, name, Total Hours باشد؛ مرتب شده بر اساس facid.
var items = context.Bookings .GroupBy(booking => new { booking.FacId, booking.Facility.Name }) .Select(group => new { group.Key.FacId, group.Key.Name, TotalHours = group.Sum(booking => booking.Slots) / 2M }) .OrderBy(result => result.FacId) .ToList();
مثال 14: گزارشی را از اولین رزرو کاربران پس از September 1st 2012، تهیه کنید.
این گزارش باید به همراه ستونهای surname, firstname, memid, starttime باشد؛ مرتب شده بر اساس memid.
var date1 = new DateTime(2012, 09, 01); var items = context.Bookings .Where(booking => booking.StartTime >= date1) .GroupBy(booking => new { booking.Member.Surname, booking.Member.FirstName, booking.Member.MemId }) .Select(group => new { group.Key.Surname, group.Key.FirstName, group.Key.MemId, StartTime = group.Min(booking => booking.StartTime) }) .OrderBy(result => result.MemId) .ToList();
مثال 15: گزارشی را از کاربران تهیه کنید که هر ردیف آن، به همراه تعداد کل کاربران باشد.
این گزارش باید به همراه ستونهای count, firstname, surname باشد؛ مرتب شده بر اساس joindate.
var members = context.Members .OrderBy(member => member.JoinDate) .Select(member => new { Count = context.Members.Count(), member.FirstName, member.Surname }) .ToList();
SELECT COUNT(*) FROM [Members] AS [m]; SELECT [m].[FirstName], [m].[Surname], @__Count_0 AS [Count] FROM [Members] AS [m] ORDER BY [m].[JoinDate];
باید بخاطر داشت که ID کاربران پشت سرهم نیست و همچنین این گزارش باید به همراه ستونهای row_number, firstname, surname باشد؛ مرتب شده بر اساس joindate.
هدف اصلی از این مثال، کار با مفهوم window functionها و تابع row_number است:
SELECT row_number() OVER (ORDER BY joindate) AS row_number, firstname, surname FROM members ORDER BY joindate;
var members = context.Members .OrderBy(member => member.JoinDate) .Select(member => new { member.FirstName, member.Surname }) .ToList() /* SELECT [m].[FirstName], [m].[Surname] FROM [Members] AS [m] ORDER BY [m].[JoinDate] */ // Now using LINQ to Objects .Select((member, index) => new { RowNumber = index + 1, member.FirstName, member.Surname }) .ToList();
مثال 17: کدامیک از امکانات موجود، بیشترین slots رزرو شده را دارد؟ قسمت دوم.
این مورد همان مثال 11 قسمت قبل است که پاسخ آنرا یافتیم (و از تکرار مجدد آن صرفنظر میکنیم) و هدف اصلی آن رسیدن به کوئری window function دار زیر است که تنها از طریق اجرای یک raw sql در EF-Core قابل اجرا است:
SELECT facid, total FROM (SELECT facid, sum(slots) AS total, rank() OVER (ORDER BY sum(slots) DESC) AS rank FROM bookings GROUP BY facid) AS ranked WHERE rank = 1;
مثال 18: به کاربران بر اساس تعداد ساعات رزرو آنها، امتیاز دهی (رتبه بندی) کنید.
این گزارش باید به همراه ستونهای firstname, surname, hours, rank باشد؛ مرتب شده بر اساس rank, surname.
هدف اصلی از این مثال، رسیدن به کوئری rank دار زیر است:
SELECT mems.firstname, mems.surname, ((sum(bks.slots) + 10) / 20) * 10 AS hours, rank() OVER (ORDER BY ((sum(bks.slots) + 10) / 20) * 10 DESC) AS rank FROM bookings AS bks INNER JOIN members AS mems ON bks.memid = mems.memid GROUP BY mems.firstname, mems.surname ORDER BY rank, mems.surname, mems.firstname;
var itemsQuery = context.Bookings .GroupBy(booking => new { booking.Member.FirstName, booking.Member.Surname }) .Select(group => new { group.Key.FirstName, group.Key.Surname, Hours = (group.Sum(booking => booking.Slots) + 10) / 20 * 10 }) .OrderByDescending(result => result.Hours) .ThenBy(result => result.Surname) .ThenBy(result => result.FirstName); var rankedItems = itemsQuery.Select(thisItem => new { thisItem.FirstName, thisItem.Surname, thisItem.Hours, Rank = itemsQuery.Count(mainItem => mainItem.Hours > thisItem.Hours) + 1 }) .ToList();
با این خروجی SQL نهایی:
مثال 19: سه امکانی را لیست کنید که بالاترین میزان فروش را داشتهاند.
این گزارش باید به همراه ستونهای name, rank باشد؛ مرتب شده بر اساس rank.
روش محاسبهی این گزارش با مثال قبلی یکی است (البته اینبار رتبه بندی بر اساس TotalRevenue است) و فقط در انتهای آن یک Where(result => result.Rank <= 3) را بیشتر دارد:
var facilitiesQuery = context.Bookings.Select(booking => new { booking.Facility.Name, Revenue = booking.MemId == 0 ? booking.Slots * booking.Facility.GuestCost : booking.Slots * booking.Facility.MemberCost }) .GroupBy(b => b.Name) .Select(group => new { Name = group.Key, TotalRevenue = group.Sum(b => b.Revenue) }) .OrderBy(result => result.TotalRevenue); var rankedFacilities = facilitiesQuery.Select(thisItem => new { thisItem.Name, thisItem.TotalRevenue, Rank = facilitiesQuery.Count(mainItem => mainItem.TotalRevenue > thisItem.TotalRevenue) + 1 }) .Where(result => result.Rank <= 3) .OrderBy(result => result.Rank) .ToList();
مثال 20: امکانات موجود را بر اساس میزان فروشی که دارند به گروههایی با تعداد مساوی high, average, low تقسیم بندی کنید.
این گزارش باید به همراه ستونهای name, revenue باشد؛ مرتب شده بر اساس revenue, name.
هدف اصلی از این گزارش کار با تابع ntile است که اطلاعات را بر اساس پارامتر ارسالی به آن تاجای ممکن به گروههای مساوی تقسیم میکند:
SELECT name, CASE WHEN class = 1 THEN 'high' WHEN class = 2 THEN 'average' ELSE 'low' END AS revenue FROM (SELECT facs.name AS name, ntile(3) OVER (ORDER BY sum(CASE WHEN memid = 0 THEN slots * facs.guestcost ELSE slots * membercost END) DESC) AS class FROM bookings AS bks INNER JOIN facilities AS facs ON bks.facid = facs.facid GROUP BY facs.name) AS subq ORDER BY class, name;
var facilities = context.Bookings.Select(booking => new { booking.Facility.Name, Revenue = booking.MemId == 0 ? booking.Slots * booking.Facility.GuestCost : booking.Slots * booking.Facility.MemberCost }) .GroupBy(b => b.Name) .Select(group => new { Name = group.Key, TotalRevenue = group.Sum(b => b.Revenue) }) .OrderByDescending(result => result.TotalRevenue) .ToList();
SELECT [f].[Name], SUM(CASE WHEN [b].[MemId] = 0 THEN CAST ([b].[Slots] AS DECIMAL (18, 6)) * [f].[GuestCost] ELSE CAST ([b].[Slots] AS DECIMAL (18, 6)) * [f].[MemberCost] END) AS [TotalRevenue] FROM [Bookings] AS [b] INNER JOIN [Facilities] AS [f] ON [b].[FacId] = [f].[FacId] GROUP BY [f].[Name] ORDER BY SUM(CASE WHEN [b].[MemId] = 0 THEN CAST ([b].[Slots] AS DECIMAL (18, 6)) * [f].[GuestCost] ELSE CAST ([b].[Slots] AS DECIMAL (18, 6)) * [f].[MemberCost] END) DESC;
var n = 3; var tiledFacilities = facilities.Select((item, index) => new { Item = item, Index = (index / n) + 1 }) .GroupBy(x => x.Index) .Select(g => g.Select(z => new { z.Item.Name, z.Item.TotalRevenue, Tile = g.Key, GroupName = g.Key == 1 ? "High" : (g.Key == 2 ? "Average" : "Low") }) .OrderBy(x => x.GroupName) .ThenBy(x => x.Name) ) .ToList(); var flatTiledFacilities = tiledFacilities.SelectMany(group => group) .Select(tile => new { tile.Name, Revenue = tile.GroupName }) .ToList();
مثال 21: چندماه طول میکشد تا هر کدام از امکانات موجود بر اساس فروشی که دارند، هزینهی مالکیت ابتدایی خود را کسب کنند.
این گزارش باید به همراه ستونهای name, months باشد؛ مرتب شده بر اساس name.
var facilities = context.Bookings.Select(booking => new { booking.Facility.Name, booking.Facility.InitialOutlay, booking.Facility.MonthlyMaintenance, Revenue = booking.MemId == 0 ? booking.Slots * booking.Facility.GuestCost : booking.Slots * booking.Facility.MemberCost }) .GroupBy(b => new { b.Name, b.InitialOutlay, b.MonthlyMaintenance }) .Select(group => new { group.Key.Name, RepayTime = group.Key.InitialOutlay / ((group.Sum(b => b.Revenue) / 3) - group.Key.MonthlyMaintenance) }) .OrderBy(result => result.Name) .ToList();
مثال 22: گزارش میانگین متحرک فروش کل هر کدام از روزهای August 2012 را برای یک بازهی 15 روزهی قبل، محاسبه کنید.
این گزارش باید به همراه ستونهای date, revenue باشد؛ مرتب شده بر اساس date. در این گزارش روزهای ماه 8 میلادی ردیف شده و به ازای هر ردیف، میانگین فروش 15 روز قبل از آن تاریخ، نمایش داده میشود. به همین جهت به آن میانگین متحرک نیز میگویند.
هدف اصلی از این گزارش، استفاده از توابع avg(revdata.rev) over است. اما چون نمیتوان از آنها در LINQ to Entities استفاده کرد، از روش دیگری که شامل جوین یک جدول با خودش است، استفاده میکنیم:
var startDate = new DateTime(2012, 08, 1); var endDate = new DateTime(2012, 08, 31); var period = 14; var dailyRevenueQuery = context.Bookings .Select(booking => new { StartDate = booking.StartTime.Date, // How to group by date (or TruncateTime) in EF-Core Revenue = booking.MemId == 0 ? booking.Slots * booking.Facility.GuestCost : booking.Slots * booking.Facility.MemberCost }) .GroupBy(b => b.StartDate) .Select(group => new { Date = group.Key, TotalRevenue = group.Sum(b => b.Revenue) });
اکنون که میزان کل فروش روزها را داریم، میخواهیم میانگین فروش 15 روز قبل شروع شدهی از از ابتدای ماه 8، تا انتهای آنرا محاسبه کنیم. برای اینکار نیاز است کوئری فوق را یکبار دیگر با خودش جوین کنیم تا از یک سر آن تاریخ هر روز و از طرف دیگر، میانگین 15 روز قبل، تولید شود:
var movingAvgs = dailyRevenueQuery .Select(dr1 => new { dr1.Date, MovingAvg = dailyRevenueQuery .Where(dr2 => dr2.Date <= dr1.Date && dr2.Date >= dr1.Date.AddDays(-period)) .Average(dr2 => dr2.TotalRevenue) }) .Where(result => result.Date >= startDate && result.Date <= endDate) .OrderBy(result => result.Date) .ToList();
کدهای کامل این قسمت را در اینجا میتوانید مشاهده کنید.
ایا معمار نرم افزار هستید؟
....
Becoming a software architect isn't something that simply happens overnight or with a promotion. It's a role , not a rank . It's an evolutionary process where you'll gradually gain the experience and confidence that you need to undertake the role.
استفاده از XQuery - قسمت اول
XQuery پیاده سازی شده در SQL Server با استانداردهای XQuery 1.0 و XPath 2.0 سازگار است. XQuery برای کار با نودهای مختلف یک سند XML، از XPath استفاده میکند. همچنین باید دقت داشت که این زبان به بزرگی و کوچکی حروف حساس است. در آن تمام واژههای کلیدی lowercase هستند و تمام متغیرها با علامت $ شروع میشوند.
ورودی و خروجی در XQuery
استاندارد XQuery از یک سری توابع ورودی مانند doc برای کار با یک سند و collection برای پردازش چندین سند کمک میگیرد. SQL Server از هیچکدام از این توابع پشتیبانی نمیکند. در اینجا از XQuery، به کمک متدهای نوع دادهای XML استفاده خواهد شد. این متدها شامل موارد ذیل هستند:
- query : یک xml را به عنوان ورودی گرفته و نهایتا یک خروجی XML دیگر را بر میگرداند.
- exist : خروجی bit دارد؛ true یا false.
- value : یک خروجی SQL Type را ارائه میدهد.
- nodes : خروجی جدولی دارد.
- modify : برای تغییر اطلاعات بکار میرود.
این موارد را در طی مثالهایی بررسی خواهیم کرد. بنابراین در ادامه نیاز است یک سند XML را که در طی مثالهای این قسمت مورد استفاده قرار خواهد گرفت، به شرح ذیل مدنظر داشته باشیم:
DECLARE @data XML SET @data = '<people> <person> <name> <givenName>name1</givenName> <familyName>lname1</familyName> </name> <age>33</age> <height>short</height> </person> <person> <name> <givenName>name2</givenName> <familyName>lname2</familyName> </name> <age>40</age> <height>short</height> </person> <person> <name> <givenName>name3</givenName> <familyName>lname3</familyName> </name> <age>30</age> <height>medium</height> </person> </people>'
همانطور که در قسمت قبل نیز ذکر شد، اگر اطلاعات شما در یک فایل XML قرار دارند، نحوهی خواندن آن به شکل یک فیلد XML با کمک openrowset مطابق دستورات زیر خواهد بود:
declare @data xml set @data = (select * from openrowset(bulk 'c:\path\data.xml', single_blob) as x)
بررسی متد query
متد query یک XQuery متنی را دریافت کرده، آنرا بر روی XML ورودی اجرا نموده و سپس یک خروجی XML دیگر را ارائه خواهد داد.
اگر به کتابهای استاندارد XQuery مراجعه کنید، به یک چنین کوئریهایی خواهید رسید:
for $p in doc("data.xml")/people/person where $p/age > 30 return $p/name/givenName/text()
SELECT @data.query(' for $p in /people/person where $p/age > 30 return $p/name/givenName/text() ')
بررسی متد value
در ادامه متد value را بررسی خواهیم کرد. در اینجا قصد داریم مقدار سن اولین شخص را نمایش دهیم:
SELECT @data.value('/people/person/age', 'int')
اگر کوئری فوق را اجرا کنیم با خطای ذیل مواجه خواهیم شد:
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
برای رفع این مشکل و اشاره به اولین شخص، میتوان از روش ذیل استفاده کرد:
SELECT @data.value('(/people/person/age)[1]', 'int')
تولید schema برای سند XML بحث جاری
با استفاده از برنامه Infer.exe مایکروسافت به سادگی میتوان برای یک سند XML، فایل Schema ایجاد کرد. این برنامه را از اینجا میتوانید دریافت کنید. پس از آن، اگر فرض کنیم اطلاعات سند XML مثال فوق در فایلی به نام people.xml ذخیره شدهاست، میتوان schema آنرا توسط دستور ذیل تولید کرد:
Infer.exe people.xml -o schema.xsd
که نهایتا چنین شکلی را خواهد داشت:
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="people"> <xs:complexType> <xs:sequence> <xs:element maxOccurs="unbounded" name="person"> <xs:complexType> <xs:sequence> <xs:element name="name"> <xs:complexType> <xs:sequence> <xs:element name="givenName" type="xs:string" /> <xs:element name="familyName" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="age" type="xs:unsignedByte" /> <xs:element name="height" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>
این خروجی را که اکنون به صورت یک فایل xsd، در کنار فایل xml معرفی شده به آن میتوان یافت، با استفاده از openrowset قابل بارگذاری است:
declare @schema xml set @schema = (select * from openrowset(bulk 'c:\path\schema_1.xsd', single_blob) as x)
سپس از این متغیر برای تعریف یک اسکیما کالکشن جدید استفاده خواهیم کرد:
CREATE XML SCHEMA COLLECTION poeple_xsd AS @schema
DECLARE @data XML(poeple_xsd) SET @data = 'مانند قبل با همان محتوایی که در ابتدای بحث عنوان شد'
SELECT @data.value('/people/person[1]/age', 'int')
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xs:unsignedByte *'
مشکل کوئری نوشته در اینجا این است که زمانیکه نوع XML تعریف میشود، پیش فرض آن content است. یعنی در این حالت چندین root elemnt مجاز هستند. بنابراین person 1 درخواستی، میتواند چندین خروجی داشته باشد که در متد value مجاز نیست. این متد، پیش از اجرای کوئری، توسط parser تعیین اعتبار میشود و الزاما نیازی نیست تا حتما اجرا شده و سپس مشخص شود که چندین خروجی حاصل آن است.
اینبار تنها کاری که باید برای رفع مشکل گزارش شده انجام شود، تغییر content پیش فرض به document است:
DECLARE @data XML(document poeple_xsd)
sequences در XQuery
Sequences بسیار شبیه به آرایهای از آیتمها هستند و منظور مجموعهای از نودها یا مقادیر آنها است. برای مثال به ورودی کوئریهای XQuery به شکل توالی از یک سند و به خروجی آنها همانند توالی صفر تا چند نود نگاه کنید.
DECLARE @x XML SET @x='' SELECT @x.query( ' 1,2 (: 1,2 :) ')
همچنین باید دقت داشت که این توالی خطی تفسیر میشود.
DECLARE @x XML SET @x='' SELECT @x.query( ' for $x in (1,2,3) for $y in (4,5) return ($x,$y) ')
به علاوه در SQL Server امکان تعریف Heterogeneous sequences وجود ندارد؛ به عبارتی توالی بین مقادیر و نودها مجاز نیست. برای مثال اگر کوئری زیر را اجرا کنید:
DECLARE @x XML SET @x='' SELECT @x.query( ' 1, <node/> ')
XQuery [query()]: Heterogeneous sequences are not allowed: found 'xs:integer' and 'element(node,xdt:untyped)'