در ادامهی مباحث پشتیبانی از XML در SQL Server، به کارآیی فیلدهای XML ایی و نحوهی ایندکس گذاری بر روی آنها خواهیم پرداخت. این مساله در تولید برنامههایی سریع و مقیاس پذیر، بسیار حائز اهمیت است.
در SQL Server، کوئریهای انجام شده بر روی فیلدهای XML، توسط همان پردازشگر کوئریهای رابطهای متداول آن، خوانده و اجرا خواهند شد و امکان تعریف یک XQuery خارج از یک عبارت SQL و یا T-SQL وجود ندارد. متدهای XQuery بسیار شبیه به system defined functions بوده و Query Plan یکپارچهای را با سایر قسمتهای رابطهای یک عبارت SQL دارند.
مفهوم Node table
دادههای XML ایی برای اینکه توسط SQL Server قابل استفاده باشند، به صورت درونی تبدیل به یک node table میشوند. به این معنا که نودهای یک سند XML، به یک جدول رابطهای به صورت خودکار تجزیه میشوند. این جدول درونی در صورت بکارگیری XML Indexes در جدول سیستمی sys.internal_tables قابل مشاهده خواهد بود. SQL Server برای انجام اینکار از یک XmlReader خاص خودش استفاده میکند. در مورد XMLهای ایندکس نشده، این تجزیه در زمان اجرا صورت میگیرد؛ پس از اینکه Query Plan آن تشکیل شد.
بررسی Query Plan فیلدهای XML ایی
جهت فراهم کردن مقدمات آزمایش، ابتدا جدول xmlInvoice را با یک فیلد XML ایی untyped درنظر بگیرید:
سپس 6 ردیف را به آن اضافه میکنیم:
همچنین برای مقایسه، دقیقا جدول مشابهی را اینبار با یک XML Schema مشخص ایجاد میکنیم.
سپس مجددا همان 6 رکورد قبلی را در این جدول جدید نیز insert خواهیم کرد.
در این جدول دوم، حالت پیش فرض content قبلی، به document تغییر کردهاست. با توجه به اینکه میدانیم اسناد ما چه فرمتی دارند و بیش از یک root element نخواهیم داشت، انتخاب document سبب خواهد شد تا Query Plan بهتری حاصل شود.
در ادامه برای مشاهدهی بهتر نتایج، کش Query Plan و اطلاعات آماری جدول xmlInvoice را حذف و به روز میکنیم:
به علاوه در management studio بهتر است از منوی Query، گزینهی Include actual execution plan را نیز انتخاب کنید (یا فشردن دکمههای Ctrl+M) تا پس از اجرای کوئری، بتوان Query Plan نهایی را نیز مشاهده نمود. برای خواندن یک Query Plan عموما از بالا به پایین و از راست به چپ باید عمل کرد. در آن نهایتا باید به عدد estimated subtree cost کوئری، دقت داشت.
کوئریهایی را که در این قسمت بررسی خواهیم کرد، در ادامه ملاحظه میکنید. بار اول این کوئریها را بر روی xmlInvoice و بار دوم، بر روی نگارش دوم دارای اسکیمای آن اجرا خواهیم کرد:
کوئری 1
همانطور که عنوان شد، از منوی Query گزینهی Include actual execution plan را نیز انتخاب کنید (یا فشردن دکمههای Ctrl+M) تا پس از اجرای کوئری، بتوان Query Plan نهایی را نیز مشاهده کرد.
در کوئری 1، با استفاده از متد exist به دنبال رکوردهایی هستیم که دارای ویژگی InvoiceId مساوی 1003 هستند. پس از اجرای کوئری، تصویر Query Plan آن به شکل زیر خواهد بود:
برای خواندن این تصویر، از بالا به پایین و چپ به راست باید عمل شود. هزینهی انجام کوئری را نیز با نگه داشتن کرسر ماوس بر روی select نهایی سمت چپ تصویر میتوان مشاهده کرد. البته باید درنظر داشت که این اعداد از دیدگاه Query Processor مفهوم پیدا میکنند. پردازشگر کوئری، بر اساس اطلاعاتی که در اختیار دارد، سعی میکند بهترین روش پردازش کوئری دریافتی را پیدا کند. برای اندازه گیری کارآیی، باید اندازه گیری زمان اجرای کوئری، مستقلا انجام شود.
در این کوئری، مطابق تصویر اول، ابتدا قسمت SQL آن (چپ بالای تصویر) پردازش میشود و سپس قسمت XML آن. قسمت XQuery این عبارت در دو قسمت سمت چپ، پایین تصویر مشخص شدهاند. Table valued functionها جاهایی هستند که node table ابتدای بحث جاری در آنها ساخته میشوند. در اینجا دو مرحلهی تولید Table valued functionها مشاهده میشود. اگر به جمع درصدهای آنها دقت کنید، هزینهی این دو قسمت، 98 درصد کل Query plan است.
سؤال: چرا دو مرحلهی تولید Table valued functionها در اینجا قابل مشاهده است؟ یک مرحلهی آن مربوط است به انتخاب نود Invoice و مرحلهی دوم مربوط است به فیلتر داخل [] ذکر شد برای یافتن ویژگیهای مساوی 1003.
در اینجا و در کوئریهای بعدی، هر Query Plan ایی که تعداد مراحل تولید Table valued function کمتری داشته باشد، بهینهتر است.
کوئری 5
اگر کوئری پلن شماره 5 را بررسی کنیم، به 3 مرحله تولید Table valued functionها خواهیم رسید. یک XML Reader برای خارج از [] (اصطلاحا به آن predicate گفته میشود) و دو مورد برای داخل [] تشکیل شدهاست؛ یکی برای انتخاب نود متنی و دیگری برای تساوی.
کوئری 7
اگر کوئری پلن شماره 7 را بررسی کنیم، به 3 مرحله تولید Table valued functionها خواهیم رسید که بسیار شبیه است به مورد 5. بنابراین در اینجا عمق بررسی و سلسله مراتب اهمیتی ندارد.
کوئری 9
کوئری 9 دقیقا معادل است با کوئری 1 نوشته شده؛ با این تفاوت که از روش FLOWR استفاده کردهاست. نکتهی جالب آن، وجود تنها یک XML reader در Query plan آن است که باید آنرا بخاطر داشت.
کوئری 2
کوئری 3
کوئری 4
کوئری 6
کوئری 8
اگر به این 5 کوئری یاد شده دقت کنید، از یک دات به معنای self استفاده کردهاند (یعنی پردازش بیشتری را انجام نده و از همین نود جاری برای پردازش نهایی استفاده کن). با توجه به بکارگیری متد exist، معنای کوئریهای یک و دو، یکیاست. اما در کوئری شماره 2، تنها یک XML Reader در Query plan نهایی وجود دارد (همانند عبارت FLOWR کوئری شماره 9).
یک نکته: اگر میخواهید بدانید بین کوئریهای 1 و 2 کدامیک بهتر عمل میکنند، از بین تمام کوئریهای موجود، دو کوئری یاد شده را انتخاب کرده و سپس با فرض روش بودن نمایش Query plan، هر دو کوئری را با هم اجرا کنید.
در این حالت، کوئری پلنهای هر دو کوئری را با هم یکجا میتوان مشاهده کرد؛ به علاوهی هزینهی نسبی آنها را در کل عملیات صورت گرفته. در حالت استفاده از دات و وجود تنها یک XML Reader، این هزینه تنها 6 درصد است، در مقابل هزینهی 94 درصدی کوئری شماره یک.
بنابراین از دیدگاه پردازشگر کوئریهای SQL Server، کوئری شماره 2، بسیار بهتر است از کوئری شماره 1.
در کوئریهای 3 و 4، شماره نود مدنظر را دقیقا مشخص کردهایم. این مورد در حالت سوم تفاوت محسوسی را از لحاظ کارآیی ایجاد نمیکند و حتی کارآیی را به علت اضافه کردن یک XML Reader دیگر برای پردازش عدد نود وارد شده، کاهش میدهد. اما کوئری 4 که عدد اولین نود را خارج از پرانتز قرار دادهاست، تنها در کل یک XML Reader را به همراه خواهد داشت.
سؤال: بین کوئریهای 2، 3 و 4 کدامیک بهینهتر است؟
بله. اگر هر سه کوئری را با هم انتخاب کرده و اجرا کنیم، میتوان در قسمت کوئری پلنها، هزینهی هر کدام را نسبت به کل مشاهده کرد. در این حالت کوئری 4 بهتر است از کوئری 2 و تنها یک درصد هزینهی کل را تشکیل میدهد.
کوئری 10
کوئری 10 اندکی متفاوت است نسبت به کوئریهای دیگر. در اینجا بجای متد exist از متد value استفاده شدهاست. یعنی ابتدا صریحا مقدار ویژگی InvoiceId استخراج شده و با 1003 مقایسه میشود.
اگر کوئری پلن آنرا با کوئری 4 که بهترین کوئری سری exist است مقایسه کنیم، کوئری 10، هزینهی 70 درصدی کل عملیات را به خود اختصاص خواهد داد، در مقابل 30 درصد هزینهی کوئری 4. بنابراین در این موارد، استفاده از متد exist بسیار بهینهتر است از متد value.
استفاده از Schema collection و تاثیر آن بر کارآیی
تمام مراحلی را که در اینجا ملاحظه کردید، صرفا با تغییر نام xmlInvoice به xmlInvoice2، تکرار کنید. xmlInvoice2 دارای ساختاری مشخص است، به همراه ذکر صریح document حین تعریف ستون XML ایی آن.
تمام پاسخهایی را که دریافت خواهید کرد با حالت بدون Schema collection یکی است.
برای مقایسه بهتر، یکبار نیز سعی کنید کوئری 1 جدول xmlInvoice را با کوئری 1 جدول xmlInvoice2 با هم در طی یک اجرا مقایسه کنید، تا بهتر بتوان Query plan نسبی آنها را بررسی کرد.
پس از این بررسی و مقایسه، به این نتیجه خواهید رسید که تفاوت محسوسی در اینجا و بین این دو حالت، قابل ملاحظه نیست. در SQL Server از Schema collection بیشتر برای اعتبارسنجی ورودیها استفاده میشود تا بهبود کارآیی کوئریها.
بنابراین به صورت خلاصه
- متد exist را به value ترجیح دهید.
- اصطلاحا ordinal (همان مشخص کردن نود 1 در اینجا) را در آخر قرار دهید (نه در بین نودها).
- مراحل اجرایی را با معرفی دات (استفاده از نود جاری) تا حد ممکن کاهش دهید.
و ... کوئری 4 در این سری، بهترین کارآیی را ارائه میدهد.
در SQL Server، کوئریهای انجام شده بر روی فیلدهای XML، توسط همان پردازشگر کوئریهای رابطهای متداول آن، خوانده و اجرا خواهند شد و امکان تعریف یک XQuery خارج از یک عبارت SQL و یا T-SQL وجود ندارد. متدهای XQuery بسیار شبیه به system defined functions بوده و Query Plan یکپارچهای را با سایر قسمتهای رابطهای یک عبارت SQL دارند.
مفهوم Node table
دادههای XML ایی برای اینکه توسط SQL Server قابل استفاده باشند، به صورت درونی تبدیل به یک node table میشوند. به این معنا که نودهای یک سند XML، به یک جدول رابطهای به صورت خودکار تجزیه میشوند. این جدول درونی در صورت بکارگیری XML Indexes در جدول سیستمی sys.internal_tables قابل مشاهده خواهد بود. SQL Server برای انجام اینکار از یک XmlReader خاص خودش استفاده میکند. در مورد XMLهای ایندکس نشده، این تجزیه در زمان اجرا صورت میگیرد؛ پس از اینکه Query Plan آن تشکیل شد.
بررسی Query Plan فیلدهای XML ایی
جهت فراهم کردن مقدمات آزمایش، ابتدا جدول xmlInvoice را با یک فیلد XML ایی untyped درنظر بگیرید:
CREATE TABLE xmlInvoice ( invoiceId INT IDENTITY PRIMARY KEY, invoice XML )
INSERT INTO xmlInvoice VALUES(' <Invoice InvoiceId="1000" dept="hardware"> <CustomerName>Vahid</CustomerName> <LineItems> <LineItem><Description>Gear</Description><Price>9.5</Price></LineItem> </LineItems> </Invoice> ') INSERT INTO xmlInvoice VALUES(' <Invoice InvoiceId="1002" dept="garden"> <CustomerName>Mehdi</CustomerName> <LineItems> <LineItem><Description>Shovel</Description><Price>19.2</Price></LineItem> </LineItems> </Invoice> ') INSERT INTO xmlInvoice VALUES(' <Invoice InvoiceId="1003" dept="garden"> <CustomerName>Mohsen</CustomerName> <LineItems> <LineItem><Description>Trellis</Description><Price>8.5</Price></LineItem> </LineItems> </Invoice> ') INSERT INTO xmlInvoice VALUES(' <Invoice InvoiceId="1004" dept="hardware"> <CustomerName>Hamid</CustomerName> <LineItems> <LineItem><Description>Pen</Description><Price>1.5</Price></LineItem> </LineItems> </Invoice> ') INSERT INTO xmlInvoice VALUES(' <Invoice InvoiceId="1005" dept="IT"> <CustomerName>Ali</CustomerName> <LineItems> <LineItem><Description>Book</Description><Price>3.2</Price></LineItem> </LineItems> </Invoice> ') INSERT INTO xmlInvoice VALUES(' <Invoice InvoiceId="1006" dept="hardware"> <CustomerName>Reza</CustomerName> <LineItems> <LineItem><Description>M.Board</Description><Price>19.5</Price></LineItem> </LineItems> </Invoice> ')
CREATE XML SCHEMA COLLECTION invoice_xsd AS ' <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="Invoice"> <xs:complexType> <xs:sequence> <xs:element name="CustomerName" type="xs:string" /> <xs:element name="LineItems"> <xs:complexType> <xs:sequence> <xs:element name="LineItem"> <xs:complexType> <xs:sequence> <xs:element name="Description" type="xs:string" /> <xs:element name="Price" type="xs:decimal" /> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> <xs:attribute name="InvoiceId" type="xs:unsignedShort" use="required" /> <xs:attribute name="dept" type="xs:string" use="required" /> </xs:complexType> </xs:element> </xs:schema>' Go CREATE TABLE xmlInvoice2 ( invoiceId INT IDENTITY PRIMARY KEY, invoice XML(document invoice_xsd) ) Go
در این جدول دوم، حالت پیش فرض content قبلی، به document تغییر کردهاست. با توجه به اینکه میدانیم اسناد ما چه فرمتی دارند و بیش از یک root element نخواهیم داشت، انتخاب document سبب خواهد شد تا Query Plan بهتری حاصل شود.
در ادامه برای مشاهدهی بهتر نتایج، کش Query Plan و اطلاعات آماری جدول xmlInvoice را حذف و به روز میکنیم:
UPDATE STATISTICS xmlInvoice DBCC FREEPROCCACHE
کوئریهایی را که در این قسمت بررسی خواهیم کرد، در ادامه ملاحظه میکنید. بار اول این کوئریها را بر روی xmlInvoice و بار دوم، بر روی نگارش دوم دارای اسکیمای آن اجرا خواهیم کرد:
-- query 1 SELECT * FROM xmlInvoice WHERE invoice.exist('/Invoice[@InvoiceId = "1003"]') = 1 -- query 2 SELECT * FROM xmlInvoice WHERE invoice.exist('/Invoice/@InvoiceId[. = "1003"]') = 1 -- query 3 SELECT * FROM xmlInvoice WHERE invoice.exist('/Invoice[1]/@InvoiceId[. = "1003"]') = 1 -- query 4 SELECT * FROM xmlInvoice WHERE invoice.exist('(/Invoice/@InvoiceId)[1][. = "1003"]') = 1 -- query 5 SELECT * FROM xmlInvoice WHERE invoice.exist('/Invoice[CustomerName = "Vahid"]') = 1 -- query 6 SELECT * FROM xmlInvoice WHERE invoice.exist('/Invoice/CustomerName [.= "Vahid"]') = 1 -- query 7 SELECT * FROM xmlInvoice WHERE invoice.exist('/Invoice/LineItems/LineItem[Description = "Trellis"]') = 1 -- query 8 SELECT * FROM xmlInvoice WHERE invoice.exist('/Invoice/LineItems/LineItem/Description [.= "Trellis"]') = 1 -- query 9 SELECT * FROM xmlInvoice WHERE invoice.exist(' for $x in /Invoice/@InvoiceId where $x = 1003 return $x ') = 1 -- query 10 SELECT * FROM xmlInvoice WHERE invoice.value('(/Invoice/@InvoiceId)[1]', 'VARCHAR(10)') = '1003' -- یکبار هم با جدول شماره 2 که اسکیما دارد تمام این موارد تکرار شود UPDATE STATISTICS xmlInvoice DBCC FREEPROCCACHE GO
کوئری 1
همانطور که عنوان شد، از منوی Query گزینهی Include actual execution plan را نیز انتخاب کنید (یا فشردن دکمههای Ctrl+M) تا پس از اجرای کوئری، بتوان Query Plan نهایی را نیز مشاهده کرد.
در کوئری 1، با استفاده از متد exist به دنبال رکوردهایی هستیم که دارای ویژگی InvoiceId مساوی 1003 هستند. پس از اجرای کوئری، تصویر Query Plan آن به شکل زیر خواهد بود:
برای خواندن این تصویر، از بالا به پایین و چپ به راست باید عمل شود. هزینهی انجام کوئری را نیز با نگه داشتن کرسر ماوس بر روی select نهایی سمت چپ تصویر میتوان مشاهده کرد. البته باید درنظر داشت که این اعداد از دیدگاه Query Processor مفهوم پیدا میکنند. پردازشگر کوئری، بر اساس اطلاعاتی که در اختیار دارد، سعی میکند بهترین روش پردازش کوئری دریافتی را پیدا کند. برای اندازه گیری کارآیی، باید اندازه گیری زمان اجرای کوئری، مستقلا انجام شود.
در این کوئری، مطابق تصویر اول، ابتدا قسمت SQL آن (چپ بالای تصویر) پردازش میشود و سپس قسمت XML آن. قسمت XQuery این عبارت در دو قسمت سمت چپ، پایین تصویر مشخص شدهاند. Table valued functionها جاهایی هستند که node table ابتدای بحث جاری در آنها ساخته میشوند. در اینجا دو مرحلهی تولید Table valued functionها مشاهده میشود. اگر به جمع درصدهای آنها دقت کنید، هزینهی این دو قسمت، 98 درصد کل Query plan است.
سؤال: چرا دو مرحلهی تولید Table valued functionها در اینجا قابل مشاهده است؟ یک مرحلهی آن مربوط است به انتخاب نود Invoice و مرحلهی دوم مربوط است به فیلتر داخل [] ذکر شد برای یافتن ویژگیهای مساوی 1003.
در اینجا و در کوئریهای بعدی، هر Query Plan ایی که تعداد مراحل تولید Table valued function کمتری داشته باشد، بهینهتر است.
کوئری 5
اگر کوئری پلن شماره 5 را بررسی کنیم، به 3 مرحله تولید Table valued functionها خواهیم رسید. یک XML Reader برای خارج از [] (اصطلاحا به آن predicate گفته میشود) و دو مورد برای داخل [] تشکیل شدهاست؛ یکی برای انتخاب نود متنی و دیگری برای تساوی.
کوئری 7
اگر کوئری پلن شماره 7 را بررسی کنیم، به 3 مرحله تولید Table valued functionها خواهیم رسید که بسیار شبیه است به مورد 5. بنابراین در اینجا عمق بررسی و سلسله مراتب اهمیتی ندارد.
کوئری 9
کوئری 9 دقیقا معادل است با کوئری 1 نوشته شده؛ با این تفاوت که از روش FLOWR استفاده کردهاست. نکتهی جالب آن، وجود تنها یک XML reader در Query plan آن است که باید آنرا بخاطر داشت.
کوئری 2
کوئری 3
کوئری 4
کوئری 6
کوئری 8
اگر به این 5 کوئری یاد شده دقت کنید، از یک دات به معنای self استفاده کردهاند (یعنی پردازش بیشتری را انجام نده و از همین نود جاری برای پردازش نهایی استفاده کن). با توجه به بکارگیری متد exist، معنای کوئریهای یک و دو، یکیاست. اما در کوئری شماره 2، تنها یک XML Reader در Query plan نهایی وجود دارد (همانند عبارت FLOWR کوئری شماره 9).
یک نکته: اگر میخواهید بدانید بین کوئریهای 1 و 2 کدامیک بهتر عمل میکنند، از بین تمام کوئریهای موجود، دو کوئری یاد شده را انتخاب کرده و سپس با فرض روش بودن نمایش Query plan، هر دو کوئری را با هم اجرا کنید.
در این حالت، کوئری پلنهای هر دو کوئری را با هم یکجا میتوان مشاهده کرد؛ به علاوهی هزینهی نسبی آنها را در کل عملیات صورت گرفته. در حالت استفاده از دات و وجود تنها یک XML Reader، این هزینه تنها 6 درصد است، در مقابل هزینهی 94 درصدی کوئری شماره یک.
بنابراین از دیدگاه پردازشگر کوئریهای SQL Server، کوئری شماره 2، بسیار بهتر است از کوئری شماره 1.
در کوئریهای 3 و 4، شماره نود مدنظر را دقیقا مشخص کردهایم. این مورد در حالت سوم تفاوت محسوسی را از لحاظ کارآیی ایجاد نمیکند و حتی کارآیی را به علت اضافه کردن یک XML Reader دیگر برای پردازش عدد نود وارد شده، کاهش میدهد. اما کوئری 4 که عدد اولین نود را خارج از پرانتز قرار دادهاست، تنها در کل یک XML Reader را به همراه خواهد داشت.
سؤال: بین کوئریهای 2، 3 و 4 کدامیک بهینهتر است؟
بله. اگر هر سه کوئری را با هم انتخاب کرده و اجرا کنیم، میتوان در قسمت کوئری پلنها، هزینهی هر کدام را نسبت به کل مشاهده کرد. در این حالت کوئری 4 بهتر است از کوئری 2 و تنها یک درصد هزینهی کل را تشکیل میدهد.
کوئری 10
کوئری 10 اندکی متفاوت است نسبت به کوئریهای دیگر. در اینجا بجای متد exist از متد value استفاده شدهاست. یعنی ابتدا صریحا مقدار ویژگی InvoiceId استخراج شده و با 1003 مقایسه میشود.
اگر کوئری پلن آنرا با کوئری 4 که بهترین کوئری سری exist است مقایسه کنیم، کوئری 10، هزینهی 70 درصدی کل عملیات را به خود اختصاص خواهد داد، در مقابل 30 درصد هزینهی کوئری 4. بنابراین در این موارد، استفاده از متد exist بسیار بهینهتر است از متد value.
استفاده از Schema collection و تاثیر آن بر کارآیی
تمام مراحلی را که در اینجا ملاحظه کردید، صرفا با تغییر نام xmlInvoice به xmlInvoice2، تکرار کنید. xmlInvoice2 دارای ساختاری مشخص است، به همراه ذکر صریح document حین تعریف ستون XML ایی آن.
تمام پاسخهایی را که دریافت خواهید کرد با حالت بدون Schema collection یکی است.
برای مقایسه بهتر، یکبار نیز سعی کنید کوئری 1 جدول xmlInvoice را با کوئری 1 جدول xmlInvoice2 با هم در طی یک اجرا مقایسه کنید، تا بهتر بتوان Query plan نسبی آنها را بررسی کرد.
پس از این بررسی و مقایسه، به این نتیجه خواهید رسید که تفاوت محسوسی در اینجا و بین این دو حالت، قابل ملاحظه نیست. در SQL Server از Schema collection بیشتر برای اعتبارسنجی ورودیها استفاده میشود تا بهبود کارآیی کوئریها.
بنابراین به صورت خلاصه
- متد exist را به value ترجیح دهید.
- اصطلاحا ordinal (همان مشخص کردن نود 1 در اینجا) را در آخر قرار دهید (نه در بین نودها).
- مراحل اجرایی را با معرفی دات (استفاده از نود جاری) تا حد ممکن کاهش دهید.
و ... کوئری 4 در این سری، بهترین کارآیی را ارائه میدهد.
ASP.NET Core به همراه زیر ساختیاست جهت خارج کردن امکانات Caching درون حافظهای آن از سرور جاری و انتقال آن به سرورهای دیگر جهت کاهش بار سرور و برنامه. این کش توزیع شده را میتوان به عنوان زیرساختی برای مدیریت سشنها، مدیریت اطلاعات کش و همچنین مدیریت کوکیهای حجیم ASP.NET Core Identity نیز بکار گرفت. برای مثال بجای ارسال یک کوکی حجیم بالای 5 کیلوبایت به کلاینت، فقط ID رمزنگاری شدهی آنرا ارسال کرد و اصل کوکی را در داخل دیتابیس ذخیره و بازیابی نمود. این مساله هم مقیاس پذیری برنامه را افزایش خواهد داد و هم امنیت آنرا با عدم ارسال اصل محتوای کوکی به سمت کلاینتها و یا ذخیره سازی اطلاعات سشنها در بانک اطلاعاتی، مشکلات راه اندازی مجدد برنامه را به طور کامل برطرف میکنند و در این حالت بازیابی Application pool و یا کرش برنامه و یا ری استارت شدن کل سرور، سبب از بین رفتن سشنهای کاربران نخواهند شد. بنابراین آشنایی با نحوهی راه اندازی این امکانات، حداقل از بعد امنیتی بسیار مفید هستند؛ حتی اگر سرور ذخیره کنندهی این اطلاعات، همان سرور و بانک اطلاعاتی اصلی برنامه باشند.
پیشنیازهای کار با کش توزیع شدهی مبتنی بر SQL Server
برای کار با کش توزیع شدهی با قابلیت ذخیره سازی در یک بانک اطلاعاتی SQL Server، نیاز است دو بستهی ذیل را به فایل project.json برنامه اضافه کرد:
وابستگی که در قسمت dependencies ذکر شدهاست، کلاسهای اصلی کار با کش توزیع شده را به برنامه اضافه میکند. ذکر وابستگی قسمت tools، اختیاری است و کار آن، ایجاد جدول مورد نیاز برای ذخیره سازی اطلاعات، در یک بانک اطلاعاتی SQL Server میباشد.
ایجاد جدول ذخیره سازی اطلاعات کش توزیع شده به کمک ابزار sql-cache
پس از افزودن و بازیابی ارجاعات فوق، با استفاده از خط فرمان، به پوشهی جاری برنامه وارد شده و دستور ذیل را صادر کنید:
توضیحات:
- در اینجا میتوان هر نوع رشتهی اتصالی معتبری را به انواع و اقسام بانکهای SQL Server ذکر کرد. برای نمونه در مثال فوق این رشتهی اتصالی به یک بانک اطلاعاتی از پیش ایجاد شدهی LocalDB اشاره میکند. نام دلخواه این بانک اطلاعاتی در اینجا sql_cache ذکر گردیده و نام دلخواه جدولی که قرار است این اطلاعات را ثبت کند AppSqlCache تنظیم شدهاست و dbo، نام اسکیمای جدول است:
در اینجا تصویر ساختار جدولی را که توسط ابزار dotnet sql-cache ایجاد شدهاست، مشاهده میکنید. اگر خواستید این جدول را خودتان دستی ایجاد کنید، یک چنین کوئری را باید بر روی دیتابیس مدنظرتان اجرا نمائید:
ایجاد جدول ذخیره سازی اطلاعات کش توزیع شده به کمک ابزار Migrations در EF Core
زیر ساخت کش توزیع شدهی مبتنی بر SQL Server هیچگونه وابستگی به EF Core ندارد و تمام اجزای آن توسط Async ADO.NET نوشته شدهاند. اما اگر خواستید قسمت ایجاد جدول مورد نیاز آنرا به ابزار Migrations در EF Core واگذار کنید، روش کار به صورت زیر است:
- ابتدا یک کلاس دلخواه جدید را با محتوای ذیل ایجاد کنید:
- سپس تنظیمات ایجاد جدول متناظر با آن را به نحو ذیل تنظیم نمائید:
به این ترتیب این جدول جدید به صورت خودکار در کنار سایر جداول برنامه ایجاد خواهند شد.
البته این مورد به شرطی است که بخواهید از یک دیتابیس، هم برای برنامه و هم برای ذخیره سازی اطلاعات کش استفاده کنید.
معرفی تنظیمات رشتهی اتصالی و نام جدول ذخیره سازی اطلاعات کش به برنامه
پس از ایجاد جدول مورد نیاز جهت ذخیره سازی اطلاعات کش، اکنون نیاز است این اطلاعات را به برنامه معرفی کرد. برای این منظور به کلاس آغازین برنامه مراجعه کرده و متد الحاقی AddDistributedSqlServerCache را بر روی مجموعهی سرویسهای موجود فراخوانی کنید؛ تا سرویسهای این کش توزیع شده نیز به برنامه معرفی شوند:
باتوجه به توزیع شده بودن این کش، هیچ الزامی ندارد که ConnectionString ذکر شدهی در اینجا با رشتهی اتصالی مورد استفادهی توسط EF Core یکی باشد (هرچند مشکلی هم ندارد).
آزمایش کش توزیع شدهی تنظیمی با فعال سازی سشنها
سشنها را همانند نکات ذکر شدهی در مطلب «ارتقاء به ASP.NET Core 1.0 - قسمت 16 - کار با Sessions» فعال کنید و سپس مقداری را در آن بنویسید:
اکنون از جدول AppSqlCache کوئری بگیرید:
همانطور که مشاهده میکنید، سیستم سشن اینبار بجای حافظه، به صورت خودکار از جدول بانک اطلاعاتی SQL Server تنظیم شده، برای ذخیره سازی اطلاعات خود استفاده کردهاست.
کار با کش توزیع شده از طریق برنامه نویسی
همانطور که در مقدمهی بحث نیز عنوان شد، استفادهی از زیر ساخت کش توزیع شده منحصر به استفادهی از آن جهت ذخیره سازی اطلاعات سشنها نیست و از آن میتوان جهت انواع و اقسام سناریوهای مختلف مورد نیاز استفاده کرد. در این حالت روش دسترسی به این زیر ساخت، از طریق اینترفیس IDistributedCache است. زمانیکه متد AddDistributedSqlServerCache را فراخوانی میکنیم، در حقیقت کار ثبت یک چنین سرویسی به صورت خودکار انجام خواهد شد:
به عبارتی کلاس SqlServerCache به صورت singleton به مجموعهی سرویسهای برنامه اضافه شدهاست و برای دسترسی به آن تنها کافی است اینترفیس IDistributedCache را به کنترلرها و یا سرویسهای برنامه تزریق و از امکانات آن استفاده کنیم.
در اینجا یک نمونه از این تزریق وابستگی و سپس استفادهی از متدهای Set و Get اینترفیس IDistributedCache را مشاهده میکنید:
در ابتدای بحث که ساختار جدول ذخیره سازی اطلاعات کش را بررسی کردیم، فیلد value آن یک چنین نوعی را دارد:
که در سمت کدهای دات نتی، به شکل آرایهای از بایتها قابل بیان است.
به همین جهت متد Set آن مقدار مدنظر را به صورت آرایهای از بایتها قبول میکند.
در این حالت اگر برنامه را اجرا و مسیر http://localhost:7742/CacheTest/SetCacheData را فراخوانی کنیم، اطلاعات ذخیره شدهی با کلید Test را میتوان در بانک اطلاعاتی مشاهده کرد:
Tag helper مخصوص کش توزیع شده
در ASP.NET Core، میتوان از یک Tag Helper جدید به نام distributed-cache برای کش سمت سرور توزیع شدهی محتوای قسمتی از یک View به نحو ذیل استفاده کرد:
که اطلاعات آن در بانک اطلاعاتی به نحو ذیل ذخیره میشود:
در اینجا name به صورت هش شده به صورت کلید کش مورد استفاده قرار میگیرد. سپس محتوای تگ distributed-cache رندر شده، تبدیل به آرایهای از بایتها گردیده و در بانک اطلاعاتی ذخیره میگردد.
ذکر name در اینجا اجباری است و باید دقت داشت که چون به عنوان کلید بازیابی کش مورد استفاده قرار خواهد گرفت، نباید به اشتباه در قسمتهای دیگر برنامه با همین نام وارد شود. در غیر اینصورت دو قسمتی که name یکسانی داشته باشند، یک محتوا را نمایش خواهند داد.
پیشنیازهای کار با کش توزیع شدهی مبتنی بر SQL Server
برای کار با کش توزیع شدهی با قابلیت ذخیره سازی در یک بانک اطلاعاتی SQL Server، نیاز است دو بستهی ذیل را به فایل project.json برنامه اضافه کرد:
{ "dependencies": { "Microsoft.Extensions.Caching.SqlServer": "1.1.0" }, "tools": { "Microsoft.Extensions.Caching.SqlConfig.Tools": "1.1.0-preview4-final" } }
ایجاد جدول ذخیره سازی اطلاعات کش توزیع شده به کمک ابزار sql-cache
پس از افزودن و بازیابی ارجاعات فوق، با استفاده از خط فرمان، به پوشهی جاری برنامه وارد شده و دستور ذیل را صادر کنید:
dotnet sql-cache create "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=sql_cache;Integrated Security=True;" "dbo" "AppSqlCache"
- در اینجا میتوان هر نوع رشتهی اتصالی معتبری را به انواع و اقسام بانکهای SQL Server ذکر کرد. برای نمونه در مثال فوق این رشتهی اتصالی به یک بانک اطلاعاتی از پیش ایجاد شدهی LocalDB اشاره میکند. نام دلخواه این بانک اطلاعاتی در اینجا sql_cache ذکر گردیده و نام دلخواه جدولی که قرار است این اطلاعات را ثبت کند AppSqlCache تنظیم شدهاست و dbo، نام اسکیمای جدول است:
در اینجا تصویر ساختار جدولی را که توسط ابزار dotnet sql-cache ایجاد شدهاست، مشاهده میکنید. اگر خواستید این جدول را خودتان دستی ایجاد کنید، یک چنین کوئری را باید بر روی دیتابیس مدنظرتان اجرا نمائید:
CREATE TABLE AppSqlCache ( Id NVARCHAR (449) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, Value VARBINARY (MAX) NOT NULL, ExpiresAtTime DATETIMEOFFSET NOT NULL, SlidingExpirationInSeconds BIGINT NULL, AbsoluteExpiration DATETIMEOFFSET NULL, CONSTRAINT pk_Id PRIMARY KEY (Id) ); CREATE NONCLUSTERED INDEX Index_ExpiresAtTime ON AppSqlCache(ExpiresAtTime);
ایجاد جدول ذخیره سازی اطلاعات کش توزیع شده به کمک ابزار Migrations در EF Core
زیر ساخت کش توزیع شدهی مبتنی بر SQL Server هیچگونه وابستگی به EF Core ندارد و تمام اجزای آن توسط Async ADO.NET نوشته شدهاند. اما اگر خواستید قسمت ایجاد جدول مورد نیاز آنرا به ابزار Migrations در EF Core واگذار کنید، روش کار به صورت زیر است:
- ابتدا یک کلاس دلخواه جدید را با محتوای ذیل ایجاد کنید:
public class AppSqlCache { public string Id { get; set; } public byte[] Value { get; set; } public DateTimeOffset ExpiresAtTime { get; set; } public long? SlidingExpirationInSeconds { get; set; } public DateTimeOffset? AbsoluteExpiration { get; set; } }
public class MyDBDataContext : DbContext { public virtual DbSet<AppSqlCache> AppSqlCache { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<AppSqlCache>(entity => { entity.ToTable(name: "AppSqlCache", schema: "dbo"); entity.HasIndex(e => e.ExpiresAtTime).HasName("Index_ExpiresAtTime"); entity.Property(e => e.Id).HasMaxLength(449); entity.Property(e => e.Value).IsRequired(); }); } }
البته این مورد به شرطی است که بخواهید از یک دیتابیس، هم برای برنامه و هم برای ذخیره سازی اطلاعات کش استفاده کنید.
معرفی تنظیمات رشتهی اتصالی و نام جدول ذخیره سازی اطلاعات کش به برنامه
پس از ایجاد جدول مورد نیاز جهت ذخیره سازی اطلاعات کش، اکنون نیاز است این اطلاعات را به برنامه معرفی کرد. برای این منظور به کلاس آغازین برنامه مراجعه کرده و متد الحاقی AddDistributedSqlServerCache را بر روی مجموعهی سرویسهای موجود فراخوانی کنید؛ تا سرویسهای این کش توزیع شده نیز به برنامه معرفی شوند:
public void ConfigureServices(IServiceCollection services) { services.AddDistributedSqlServerCache(options => { options.ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=sql_cache;Integrated Security=True;"; options.SchemaName = "dbo"; options.TableName = "AppSqlCache"; });
آزمایش کش توزیع شدهی تنظیمی با فعال سازی سشنها
سشنها را همانند نکات ذکر شدهی در مطلب «ارتقاء به ASP.NET Core 1.0 - قسمت 16 - کار با Sessions» فعال کنید و سپس مقداری را در آن بنویسید:
public IActionResult Index() { HttpContext.Session.SetString("User", "VahidN"); return Json(true); } public IActionResult About() { var userContent = HttpContext.Session.GetString("User"); return Json(userContent); }
همانطور که مشاهده میکنید، سیستم سشن اینبار بجای حافظه، به صورت خودکار از جدول بانک اطلاعاتی SQL Server تنظیم شده، برای ذخیره سازی اطلاعات خود استفاده کردهاست.
کار با کش توزیع شده از طریق برنامه نویسی
همانطور که در مقدمهی بحث نیز عنوان شد، استفادهی از زیر ساخت کش توزیع شده منحصر به استفادهی از آن جهت ذخیره سازی اطلاعات سشنها نیست و از آن میتوان جهت انواع و اقسام سناریوهای مختلف مورد نیاز استفاده کرد. در این حالت روش دسترسی به این زیر ساخت، از طریق اینترفیس IDistributedCache است. زمانیکه متد AddDistributedSqlServerCache را فراخوانی میکنیم، در حقیقت کار ثبت یک چنین سرویسی به صورت خودکار انجام خواهد شد:
services.Add(ServiceDescriptor.Singleton<IDistributedCache, SqlServerCache>());
در اینجا یک نمونه از این تزریق وابستگی و سپس استفادهی از متدهای Set و Get اینترفیس IDistributedCache را مشاهده میکنید:
using System; using System.Text; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Caching.Distributed; namespace Core1RtmEmptyTest.Controllers { public class CacheTestController : Controller { readonly IDistributedCache _cache; public CacheTestController(IDistributedCache cache) { _cache = cache; } public IActionResult SetCacheData() { var time = DateTime.Now.ToLocalTime().ToString(); var cacheOptions = new DistributedCacheEntryOptions { AbsoluteExpiration = DateTime.Now.AddYears(1) }; _cache.Set("Time", Encoding.UTF8.GetBytes(time), cacheOptions); return View(); } public IActionResult GetCacheData() { var time = Encoding.UTF8.GetString(_cache.Get("Time")); ViewBag.data = time; return View(); } public bool RemoveCacheData() { _cache.Remove("Time"); return true; } } }
Value VARBINARY (MAX) NOT NULL,
public byte[] Value { get; set; }
در این حالت اگر برنامه را اجرا و مسیر http://localhost:7742/CacheTest/SetCacheData را فراخوانی کنیم، اطلاعات ذخیره شدهی با کلید Test را میتوان در بانک اطلاعاتی مشاهده کرد:
Tag helper مخصوص کش توزیع شده
در ASP.NET Core، میتوان از یک Tag Helper جدید به نام distributed-cache برای کش سمت سرور توزیع شدهی محتوای قسمتی از یک View به نحو ذیل استفاده کرد:
<distributed-cache name="MyCacheItem2" expires-sliding="TimeSpan.FromMinutes(30)"> <p>From distributed-cache</p> @DateTime.Now.ToString() </distributed-cache>
در اینجا name به صورت هش شده به صورت کلید کش مورد استفاده قرار میگیرد. سپس محتوای تگ distributed-cache رندر شده، تبدیل به آرایهای از بایتها گردیده و در بانک اطلاعاتی ذخیره میگردد.
ذکر name در اینجا اجباری است و باید دقت داشت که چون به عنوان کلید بازیابی کش مورد استفاده قرار خواهد گرفت، نباید به اشتباه در قسمتهای دیگر برنامه با همین نام وارد شود. در غیر اینصورت دو قسمتی که name یکسانی داشته باشند، یک محتوا را نمایش خواهند داد.
پشتیبانی SQL Server از Spatial data
از SQL Server 2008 به بعد، نوع داده جدیدی به نام geography به نوعهای قابل تعریف ستونها اضافه شدهاست. در این نوع ستونها میتوان طول و عرض جغرافیایی یک نقطه را ذخیره کرد و سپس به کمک توابع توکاری از آنها کوئری گرفت.
در اینجا نمونهای از نحوهی تعریف و همچنین مقدار دهی این نوع ستونها را مشاهده میکنید:
متد geography::STGeoFromText یک SQL CLR function است. این متد در مثال فوق، مختصات یک نقطه را دریافت کردهاست. همچنین نیاز دارد بداند که این نقطه توسط چه نوع سیستم مختصاتی ارائه میشود. عدد 4326 در اینجا یک SRID یا Spatial Reference System Identifier استاندارد است. برای نمونه اطلاعات ارائه شده توسط Google و یا Bing توسط این استاندارد ارائه میشوند.
در اینجا متدهای توکار دیگری مانند geography::STDistance برای یافتن فاصله مستقیم بین نقاط نیز ارائه شدهاند. خروجی آن بر حسب متر است.
پشتیبانی از Spatial Data در Entity framework
پشتیبانی از نوع مخصوص geography، در EF 5 توسط نوع دادهای DbGeography ارائه شد. این نوع دادهای immutable است. به این معنا که پس از نمونه سازی، دیگر مقدار آن قابل تغییر نیست.
در اینجا برای نمونه مدلی را مشاهده میکنید که از نوع دادهای DbGeography استفاده میکند:
به همراه یک Context، تا کلاس GeoLocation در معرض دید EF قرار گیرد:
برای مقدار دهی خاصیت Location از نوع DbGeography میتوان از متد ذیل استفاده کرد که بسیار شبیه به متد geography::STGeoFromText عمل میکند:
تهیه منبع دادهی جغرافیایی
برای تدارک یک مثال واقعی جغرافیایی، نیاز به اطلاعاتی دقیق داریم. این نوع اطلاعات عموما توسط یک سری فایل مخصوص به نام Shapefiles که حاوی اطلاعات برداری جغرافیایی هستند ارائه میشوند. برای نمونه اطلاعات جغرافیایی به روز ایران را از آدرس ذیل میتوانید دریافت کنید:
http://download.geofabrik.de/asia/iran.html
http://download.geofabrik.de/asia/iran-latest.shp.zip
پس از دریافت این فایل، به تعدادی فایل با پسوندهای shp، shx و dbf خواهیم رسید.
فایلهای shp بیانگر فرمت اشکال ذخیره شده هستند. فایلهای shx یک سری ایندکس بوده و فایلهای dbf از نوع بانک اطلاعاتی dBase IV میباشند.
همچنین اگر فایلهای prj را باز کنید، یک چنین اطلاعاتی در آن موجودند:
نکتهی مهمی که در اینجا باید مدنظر داشت، استاندارد GCS_WGS_1984 آن است. این استاندارد معادل است با استاندارد EPSG 4326. عدد 4326 آن جهت ثبت این اطلاعات در یک بانک اطلاعاتی SQL Server حائز اهمیت است (پارامتر coordinateSystemId در متد createPoint) و ممکن است از هر فایلی به فایل دیگر متفاوت باشد.
خواندن فایلهای shp در دات نت
پس از دریافت فایلهای shp و بانکهای اطلاعاتی مرتبط با اطلاعات جغرافیایی ایران، اکنون نوبت به پردازش این فایلهای مخصوص با فرمت بانک اطلاعاتی فاکس پرو مانند، رسیدهاست. برای این منظور میتوان از پروژهی سورس باز ذیل استفاده کرد:
این پروژه در خواندن فایلهای shp بدون نقص عمل میکند اما توانایی خواندن نامهای فارسی وارد شده در این نوع بانکهای اطلاعاتی را ندارد. برای رفع این مشکل، سورس آن را از Codeplex دریافت کنید. سپس فایل Shapefile.cs را گشوده و ابتدای خاصیت Current آنرا به نحو ذیل تغییر دهید:
در اینجا فقط سطر استفاده از Encoding خاصی با شماره 720 و تبدیل آن به UTF8 اضافه شدهاست. پس از آن بدون مشکل میتوان برچسبهای فارسی را از فایلهای dBase IV این نوع بانکهای اطلاعاتی استخراج کرد (اصلاح شدهی آن در فایل پیوست مطلب موجود است).
در کدهای فوق به کمک کتابخانهی C# Esri Shapefile Reader، اطلاعات نقاط بانک اطلاعاتی shape files را خوانده و به صورت لیستهایی از MapPoint بازگشت میدهیم. نکتهی مهم آن، Metadata است که از هر فایلی به فایل دیگر میتوان متفاوت باشد. به همین جهت این اطلاعات را به شکل ویژگیهای key/value در این نوع بانکهای اطلاعاتی ذخیره میکنند.
افزودن اطلاعات جغرافیایی به بانک اطلاعاتی SQL Server به کمک Entity framework
فایل places.shp را در مجموعه فایلهایی که در ابتدای بحث عنوان شدند، میتوانید مشاهده کنید. قصد داریم اطلاعات نقاط آنرا به مدل GeoLocation انتساب داده و سپس ذخیره کنیم:
تعریف متد createPoint را که بر اساس X و Y نقاط، معادل قابل پذیرش آنرا جهت SQL Server تهیه میکند، در ابتدای بحث مشاهده کردید.
در فایلهای مرتبط با places.shp، متادیتا name، معادل نام شهرهای ایران است و type آن بیانگر شهر، روستا و امثال آن میباشد.
پس از اینکه اطلاعات مکانهای ایران، در SQL Server ذخیره شدند، نمایش بصری آنها را در management studio نیز میتوان مشاهده کرد:
کوئری گرفتن از اطلاعات جغرافیایی
فرض کنید میخواهیم مکانهایی را با فاصله کمتر از 5 کیلومتر از تهران پیدا کنیم:
همانطور که پیشتر نیز عنوان شد، متد Distance بر اساس متر کار میکند. به همین جهت برای تعریف 5 کیلومتر به نحو فوق عمل شدهاست. همچنین نحوهی مرتب سازی اطلاعات نیز بر اساس فاصله از یک مکان مشخص صورت گرفتهاست.
و یا اگر بخواهیم دقیقا بر اساس مختصات یک نقطه، مکانی را بیابیم، میتوان از متد SpatialEquals استفاده کرد:
کدهای کامل این مثال را از اینجا میتوانید دریافت کنید:
EFGeoTests.zip
از SQL Server 2008 به بعد، نوع داده جدیدی به نام geography به نوعهای قابل تعریف ستونها اضافه شدهاست. در این نوع ستونها میتوان طول و عرض جغرافیایی یک نقطه را ذخیره کرد و سپس به کمک توابع توکاری از آنها کوئری گرفت.
در اینجا نمونهای از نحوهی تعریف و همچنین مقدار دهی این نوع ستونها را مشاهده میکنید:
CREATE TABLE [Geo]( [id] [int] IDENTITY(1,1) NOT NULL, [Location] [geography] NULL ) insert into Geo( Location , long, lat ) values ( geography::STGeomFromText ('POINT(-121.527200 45.712113)', 4326))
در اینجا متدهای توکار دیگری مانند geography::STDistance برای یافتن فاصله مستقیم بین نقاط نیز ارائه شدهاند. خروجی آن بر حسب متر است.
پشتیبانی از Spatial Data در Entity framework
پشتیبانی از نوع مخصوص geography، در EF 5 توسط نوع دادهای DbGeography ارائه شد. این نوع دادهای immutable است. به این معنا که پس از نمونه سازی، دیگر مقدار آن قابل تغییر نیست.
در اینجا برای نمونه مدلی را مشاهده میکنید که از نوع دادهای DbGeography استفاده میکند:
using System.Data.Entity.Spatial; namespace EFGeoTests.Models { public class GeoLocation { public int Id { get; set; } public DbGeography Location { get; set; } public string Name { get; set; } public string Type { get; set; } public override string ToString() { return string.Format("Name:{0}, Location:{1}", Name, Location); } } }
using System; using System.Data.Entity; using EFGeoTests.Models; namespace EFGeoTests.Config { public class MyContext : DbContext { public DbSet<GeoLocation> GeoLocations { get; set; } public MyContext() : base("Connection1") { this.Database.Log = sql => Console.Write(sql); } } }
private static DbGeography createPoint(double longitude, double latitude, int coordinateSystemId = 4326) { var text = string.Format(CultureInfo.InvariantCulture.NumberFormat,"POINT({0} {1})", longitude, latitude); return DbGeography.PointFromText(text, coordinateSystemId); }
تهیه منبع دادهی جغرافیایی
برای تدارک یک مثال واقعی جغرافیایی، نیاز به اطلاعاتی دقیق داریم. این نوع اطلاعات عموما توسط یک سری فایل مخصوص به نام Shapefiles که حاوی اطلاعات برداری جغرافیایی هستند ارائه میشوند. برای نمونه اطلاعات جغرافیایی به روز ایران را از آدرس ذیل میتوانید دریافت کنید:
http://download.geofabrik.de/asia/iran.html
http://download.geofabrik.de/asia/iran-latest.shp.zip
پس از دریافت این فایل، به تعدادی فایل با پسوندهای shp، shx و dbf خواهیم رسید.
فایلهای shp بیانگر فرمت اشکال ذخیره شده هستند. فایلهای shx یک سری ایندکس بوده و فایلهای dbf از نوع بانک اطلاعاتی dBase IV میباشند.
همچنین اگر فایلهای prj را باز کنید، یک چنین اطلاعاتی در آن موجودند:
GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]
خواندن فایلهای shp در دات نت
پس از دریافت فایلهای shp و بانکهای اطلاعاتی مرتبط با اطلاعات جغرافیایی ایران، اکنون نوبت به پردازش این فایلهای مخصوص با فرمت بانک اطلاعاتی فاکس پرو مانند، رسیدهاست. برای این منظور میتوان از پروژهی سورس باز ذیل استفاده کرد:
این پروژه در خواندن فایلهای shp بدون نقص عمل میکند اما توانایی خواندن نامهای فارسی وارد شده در این نوع بانکهای اطلاعاتی را ندارد. برای رفع این مشکل، سورس آن را از Codeplex دریافت کنید. سپس فایل Shapefile.cs را گشوده و ابتدای خاصیت Current آنرا به نحو ذیل تغییر دهید:
/// <summary> /// Gets the current shape in the collection /// </summary> public Shape Current { get { if (_disposed) throw new ObjectDisposedException("Shapefile"); if (!_opened) throw new InvalidOperationException("Shapefile not open."); // get the metadata StringDictionary metadata = null; if (!RawMetadataOnly) { metadata = new StringDictionary(); for (int i = 0; i < _dbReader.FieldCount; i++) { string value = _dbReader.GetValue(i).ToString(); if (_dbReader.GetDataTypeName(i) == "DBTYPE_WVARCHAR") { // برای نمایش متون فارسی نیاز است value = Encoding.UTF8.GetString(Encoding.GetEncoding(720).GetBytes(value)); } metadata.Add(_dbReader.GetName(i), value); } }
using System.Collections.Generic; using System.Linq; using Catfood.Shapefile; namespace EFGeoTests { public class MapPoint { public Dictionary<string, string> Metadata { set; get; } public double X { set; get; } public double Y { set; get; } } public static class ShapeReader { public static IList<MapPoint> ReadShapeFile(string path) { var results = new List<MapPoint>(); using (var shapefile = new Shapefile(path)) { foreach (var shape in shapefile) { if (shape.Type != ShapeType.Point) continue; var shapePoint = shape as ShapePoint; if (shapePoint == null) continue; var metadataNames = shape.GetMetadataNames(); if(!metadataNames.Any()) continue; var metadata = new Dictionary<string, string>(); foreach (var metadataName in metadataNames) { metadata.Add(metadataName,shape.GetMetadata(metadataName)); } results.Add(new MapPoint { Metadata = metadata, X = shapePoint.Point.X, Y = shapePoint.Point.Y }); } } return results; } } }
افزودن اطلاعات جغرافیایی به بانک اطلاعاتی SQL Server به کمک Entity framework
فایل places.shp را در مجموعه فایلهایی که در ابتدای بحث عنوان شدند، میتوانید مشاهده کنید. قصد داریم اطلاعات نقاط آنرا به مدل GeoLocation انتساب داده و سپس ذخیره کنیم:
var points = ShapeReader.ReadShapeFile("IranShapeFiles\\places.shp"); using (var context = new MyContext()) { context.Configuration.AutoDetectChangesEnabled = false; context.Configuration.ProxyCreationEnabled = false; context.Configuration.ValidateOnSaveEnabled = false; if (context.GeoLocations.Any()) return; foreach (var point in points) { context.GeoLocations.Add(new GeoLocation { Name = point.Metadata["name"], Type = point.Metadata["type"], Location = createPoint(point.X, point.Y) }); } context.SaveChanges(); }
در فایلهای مرتبط با places.shp، متادیتا name، معادل نام شهرهای ایران است و type آن بیانگر شهر، روستا و امثال آن میباشد.
پس از اینکه اطلاعات مکانهای ایران، در SQL Server ذخیره شدند، نمایش بصری آنها را در management studio نیز میتوان مشاهده کرد:
کوئری گرفتن از اطلاعات جغرافیایی
فرض کنید میخواهیم مکانهایی را با فاصله کمتر از 5 کیلومتر از تهران پیدا کنیم:
var tehran = createPoint(51.4179604, 35.6884243); using (var context = new MyContext()) { // find any locations within 5 kilometers ordered by distance var locations = context.GeoLocations .Where(loc => loc.Location.Distance(tehran) < 5000) .OrderBy(loc => loc.Location.Distance(tehran)) .ToList(); foreach (var location in locations) { Console.WriteLine(location.Name); } }
و یا اگر بخواهیم دقیقا بر اساس مختصات یک نقطه، مکانی را بیابیم، میتوان از متد SpatialEquals استفاده کرد:
var tehran = createPoint(51.4179604, 35.6884243); using (var context = new MyContext()) { // find any locations within 5 kilometers ordered by distance var tehranLocation = context.GeoLocations.FirstOrDefault(loc => loc.Location.SpatialEquals(tehran)); if (tehranLocation != null) { Console.WriteLine(tehranLocation.Type); } }
کدهای کامل این مثال را از اینجا میتوانید دریافت کنید:
EFGeoTests.zip
اشتراکها