در نسخه نهایی پروژه شما دو پلاگین قرار دادید. پلاگینی برای اخبار و مقالات و یک پروژه هم برای مدلهای مشترک که کاربر در این قسمت قرار داره. حالا سوال من اینه که فرضا مدل Tags برای هر دو جدول اخبار و مقالات یکی هست. و ممکنه اصلا ما یک افزونه دیگری هم داشته باشیم که بخواد برچسب بگیره. برای این جور روابط باید به چه شکلی عمل کرد ؟ رابطهی بین جدول اخبار و برچسبها و رابطه بین جدول مقالات و برچستها همگی چند به چند هستند.
بازخوردهای پروژهها
ارتباطات بین جداول
با سلام و تشکر از پروژهی خوبتان که تو یاد گیری MVC خیلی کمکم میکنه
جدول UserMetaDatas چرا با جدول Users رابطهی یک به چند ( one to many ) داره؟ مگه جدول UserMetaDatas برای نگه داری مشخصات کاربر نیست؟ و هر کاربر نیز مشخصات واحدی داره مثل تاریخ تولد! به نظر باید رابطه یک به یک باشه یا مساله ای هست و من متوجه نشدم؟ بازم تشکر از شما
بازخوردهای پروژهها
راهنمایی در مورد تحلیل محصول و قیمت
سلام . تشکر بابت پروژه هایی که منتشر میکنید .
آقای ربال ، ببینید در مورد محصول و قیمت درست متوجه شدم ؟
یک رابطه یک به چند بین محصول و قیمت وجود دارد که یکی از مزیتهای این میتونه نمودار بالا پایین تغییرات قیمت محصول باشه . آیا آخرین قیمت رو شما در جدول Product ذخیره میکنید ؟ به نظر شما بهتر است در جدول Product ذخیره شود و یا در جدول Price ؟
ممنون
آقای ربال ، ببینید در مورد محصول و قیمت درست متوجه شدم ؟
یک رابطه یک به چند بین محصول و قیمت وجود دارد که یکی از مزیتهای این میتونه نمودار بالا پایین تغییرات قیمت محصول باشه . آیا آخرین قیمت رو شما در جدول Product ذخیره میکنید ؟ به نظر شما بهتر است در جدول Product ذخیره شود و یا در جدول Price ؟
ممنون
در مقاله قبلی با یکی از کتابخانههای مدیریت دیتابیس sqlite آشنا شدیم و و یاد گرفتیم که چگونه یک دیتابیس جدید را بسازیم و اطلاعات را از آن دریافت کنیم. در این مقاله قصد داریم، بیشتر در مورد دستورات این کتابخانه بدانیم و بفهمیم که چگونه باید آنها را به کار بست.
دستورات بدون خروجی:
یک سری از دستورات هستند که خروجی ندارند و رکوردی را باز نمیگردانند و برای اجرای دستوراتی چون افزودن، به روزرسانی و حذف بسیار مناسبند. اجرای این دستورات را ما به متدی به نام run میسپاریم. در دفعه قبل که از این دستور استفاده کردیم، پارامتری برای تعیین کردن نداشت؛ ولی در این مقاله، دستور با پارامتر آن را اجرا میکنیم:
ابتدا کدهای زیر را به فایل html، برای درج رکورد جدید اضافه میکنیم:
دستورات بدون خروجی:
یک سری از دستورات هستند که خروجی ندارند و رکوردی را باز نمیگردانند و برای اجرای دستوراتی چون افزودن، به روزرسانی و حذف بسیار مناسبند. اجرای این دستورات را ما به متدی به نام run میسپاریم. در دفعه قبل که از این دستور استفاده کردیم، پارامتری برای تعیین کردن نداشت؛ ولی در این مقاله، دستور با پارامتر آن را اجرا میکنیم:
ابتدا کدهای زیر را به فایل html، برای درج رکورد جدید اضافه میکنیم:
First Name:<br/> <input type="text" id="txtfname" /><br/> Last Name:<br/> <input type="text" id=txtlname /><br/> Number:<br/> <input type="tel" id="txttel" /><br/> <button id="btnsubmit">Save</button><br/>
function GetValues() { let fname=$("#txtfname").val(); let lname=$("#txtlname").val() let tel=$("#txttel").val(); let row= { fname:fname, lname:lname, number:tel }; return row; }
$("#btnsubmit").click((e)=>{ e.preventDefault(); let row=GetValues(); //save in db //get last id let statement==db.prepare("select id from numbers order by id desc limit 1"); let lastRecord=statement.getAsObject({}).id; row.id=lastRecord++; let count=db.prepare("select count(*) as count from numbers order by id desc").getAsObject({}).count; statement.free(); let insertCommand="insert into numbers values(?,?,?,?)"; db.run(insertCommand,[row.id,row.fname,row.lname,String(row.number)]) let newcount=db.prepare("select count(*) as count from numbers order by id desc").getAsObject({}).count; SaveChanges(); //show in the table if(count<newcount) { AddToTable(row); } }); });
var statement= db.prepare("SELECT * FROM NUMBERS WHERE fname=@fname AND lname=@lname"); var result = statement .getAsObject({'@fname' :'ali', '@lname' : 'yeganeh'});
statement.bind(['hossein','yeganeh']);
متد step همانند متدهای next در cursor یا read در datareader عمل میکند و با هر بار صدا زدن، یک رکورد، به سمت جلو حرکت میکند. دریافت هر رکورد جاری توسط متد get و نوع خروجی آرایه انجام میشود:
while(statement.step()) { var rec=statement.get(); }
بعد از اینکه کارمان با آن تمام شد، برای پاکسازی حافظه از متد free استفاده میکنیم. در دستورات بعد، شیء statement را مستقیما مورد استفاده قرار دادهایم و توسط آن تعداد رکوردها را دریافت کردهایم. سپس با استفاده از متد run دستور درج را دادهایم. اینبار این متد را به شکل متفاوتی استفاده کردیم و به آن پارامتری هم دادیم. نحوه ارائه پارامتر به این متد، باید به صورت آرایه و به ترتیب علامتهای ؟ باشد. نهایتا با دریافت تعداد رکوردهای جاری و مقایسه با تعداد رکوردهای سابق متوجه میشویم که آیا رکوردی اضافه شده است یا خیر؟ در صورتی که اضافه شده است، باید رکورد جدید در جدول، توسط جی کوئری نمایش داده شود و تغییرات دیتابیس هم روی دیسک سخت ذخیره شوند. چون دیتابیس مورد استفاده به صورت in-memory یعنی مقیم در حافظه مورد استفاده قرار میگیرد، باید کل دیتابیس، بر روی دیسک سخت رونویسی شود. متد SaveChanges شامل کد زیر است که حاوی کد ارسال پیام به Main Thread یا Main Process میباشد تا در دیسک سخت بنویسد:
const {ipcRenderer} = require('electron'); function SaveChanges() { ipcRenderer.send("SaveToDb"); }
const {ipcMain} = require('electron'); ipcMain.on("SaveToDb", (event, arg) => { SaveToDb(); }); function SaveToDb() { var data=db.export(); var buffer=new Buffer(data); fs.writeFileSync(dbPath,buffer); }
ویرایش رکورد
ابتدا template string سطر جدول را به شکل زیر تغییر میدهیم:
function AddToTable(row) { let tableBody=$("#people"); let rowTemplate=`<tr><td>${row.fname}</td><td>${row.lname}</td><td>${row.number}</td><td><button class= "btn btn-success btnupdate" data-id="${row.id}" >Edit</button></td></tr>`; tableBody.append(rowTemplate); }
سپس در تگ اسکریپت، در رویداد ready جی کوئری، این دستورات را اضافه میکنیم:
$("#people").on('click','.btnupdate',function(e) { e.preventDefault(); row.id=$(this).data("id"); let row=GetValues(); db.run("UPDATE NUMBERS SET FNAME=?,LNAME=?,NUMBER=? WHERE ID=?",[row.fname,row.lname,row.number,row.id]); SaveChanges(); tr=$(this).closest("tr"); let column=0; tr.find("td").each(function(index) { oldRow=$(this); switch(column) { case 0: //fname oldRow.text(row.fname); break; case 1: //lname oldRow.text(row.lname); break; case 2: //number oldRow.text(row.number); break; } column++; }); });
ابتدای id ذخیره شده در المان و مقادیر جدید را دریافت میکنیم. با استفاده از متد run کوئری به روزرسانی را به همراه پارامترها ارسال میکنیم و نتیجه را بر روی دیسک سخت ذخیره میکنیم. از اینجا به بعد نقش جی کوئری پر رنگتر میشود و به خوبی میتوانیم اهمیت آن را درک کنیم. سطر دکمه جاری را پیدا میکنیم و مقادیر جدید را ستون به ستون تغییر میدهیم.
خواندن و بازگردانی رکوردها
در مقاله قبلی با دستور each آشنا شدیم که یک متد غیرهمزمان بود و نتیجه هر رکورد را با یک callback به ما بازگشت میداد. در اصل این متد شامل 4 پارامتر است: پارامتر اول آن، کوئری ارسالی است. پارامتر دوم آن، پارامتر کوئریها ، پارامتر سوم، تابع callback که به ازای هر رکورد اجرا میشود و پارامتر چهارم، تابع done می باشد. یعنی زمانی که کلیه رکوردها بازگشت داده شدند. شکل کامل آن به این صورت است:
db.each("SELECT name,age FROM users WHERE age >= $majority", {$majority:18}, function(row){console.log(row.name)}, function(){console.log("done");} );
در این مقاله با متد دیگری به نام exec نیز آشنا میشویم که بازگردانی مقادیر در آن به صورت همزمان صورت میگیرد و توانایی آن را دارد که چندین دستور select را بازگردانی کند. به عنوان مثال دستور زیر را در نظر بگیرید:
SELECT ID FROM NUMBERS;SELECT FNAME,LNAME FROM NUMBERS
[ {columns: ['id'], values:[[1],[2],[3]]}, {columns: ['fname','lname'], values:[['ali','yeganeh'],['hossein','yeganeh'],['mohammad','yeganeh']]} ]
var records=db.exec("select * from numbers"); let values=records[0].values; let length=values.length; for(let i=0;i<length;i++) { let object=values[i]; let row={ id:object[0], fname:object[1], lname:object[2], number:object[3] }; AddToTable(row); }
با سلام
اطلاعات رو با entity خوندم توی لایه سرویس و با Automapper توی ویومدل بایندکردم و خروجی رو توسط یک اکشن به jqgrid بر میگردونم
مشکل اینجاست که زمان جستجو به مشکل میخورم
مثلا دو تا جدول استان و شهر دارم
که فیلدهاشون Id , Name هستن
این دوتا جدول رو join میکنم و داخل یه ویومدل میریزم با این اسم ها:
StateId
StateName
CityId
CityName
چون خاصیت index توی گریدویو به این شکل هست
زمانی که میخوام روی این فیلد جستجو بزنم دچار خطا میشه که میگه این فیلد رو نمیشناسه که راست هم میگه
ممنون میشم پیشنهادی برای این موضوع بدید
کد دریافت اطلاعات لایه سرویس هم اینه
اطلاعات رو با entity خوندم توی لایه سرویس و با Automapper توی ویومدل بایندکردم و خروجی رو توسط یک اکشن به jqgrid بر میگردونم
مشکل اینجاست که زمان جستجو به مشکل میخورم
مثلا دو تا جدول استان و شهر دارم
که فیلدهاشون Id , Name هستن
این دوتا جدول رو join میکنم و داخل یه ویومدل میریزم با این اسم ها:
StateId
StateName
CityId
CityName
چون خاصیت index توی گریدویو به این شکل هست
index: ' (StronglyTyped.PropertyName<StateCityViewModel>(x => x.CityName))',
ممنون میشم پیشنهادی برای این موضوع بدید
کد دریافت اطلاعات لایه سرویس هم اینه
public async Task<DataGridViewModel<StateCityViewModel>> GetDataGridSource(string orderBy, JqGridRequest request, NameValueCollection form, DateTimeType dateTimeType, int page, int pageSize) { var query = _stateCity.AsQueryable(); query = new JqGridSearch(request, form, dateTimeType).ApplyFilter(query); var resultsToSkip = page * pageSize; var dataGridModel = new DataGridViewModel<StateCityViewModel> { Records = await query.AsQueryable().OrderBy(orderBy) .Skip(() => resultsToSkip) .Take(pageSize).ProjectTo<StateCityViewModel>(null, _mappingEngine).ToListAsync(), TotalCount = await query.AsQueryable().OrderBy(orderBy).CountAsync() }; return dataGridModel; }
مطالب دورهها
متدهای توکار استفاده از نوع دادهای XML - قسمت دوم
امکان ترکیب دادههای یک بانک اطلاعاتی رابطهای و XML در SQL Server به کمک یک سری تابع کمکی خاص به نامهای sql:variable و sql:column پیش بینی شدهاست. sql:variable امکان استفاده از یک متغیر T-SQL را داخل یک XQuery میسر میسازد و توسط sql:column میتوان با یکی از ستونهای ذکر شده در قسمت select، داخل XQuery کار کرد. در ادامه به مثالهایی در این مورد خواهیم پرداخت.
ابتدا جدول xmlTest را به همراه چند رکورد ثبت شده در آن، درنظر بگیرید:
استفاده از متد sql:column
در ادامه میخواهیم مقدار ویژگی name رکوردی را که نام آن Vahid است، به همراه id آن ردیف، توسط یک XQuery بازگشت دهیم:
یک sql:column حتما نیاز به یک نام ستون دو قسمتی دارد. قسمت اول آن نام جدول است و قسمت دوم، نام ستون مورد نظر.
در مورد متد data در قسمت قبل بیشتر بحث شد و از آن برای استخراج دادهی یک ویژگی در اینجا استفاده شدهاست. عبارات داخل {} نیز پویا بوده و به همراه سایر قسمتهای ثابت return، ابتدا محاسبه و سپس بازگشت داده میشود.
اگر این کوئری را اجرا کنید، ردیف اول آن مساوی عبارت زیر خواهد بود
به همراه دو ردیف خالی دیگر در ادامه. این ردیفهای خالی به علت وجود دو رکورد دیگری است که با شرط where یاد شده تطابق ندارند.
یک روش برای حذف این ردیفهای خالی استفاده از متد exist است به شکل زیر:
در اینجا فقط ردیفی انتخاب خواهد شد که نام ویژگی آن Vahid است.
روش دوم استفاده از یک derived table و بازگشت ردیفهای غیرخالی است:
استفاده از متد sql:variable
در این مثال نحوهی بکارگیری یک متغیر T-SQL را داخل یک XQuery توسط متد sql:variable ملاحظه میکنید.
استفاده از For XML برای دریافت یکبارهی تمام ردیفهای XML
اگر کوئری معمولی ذیل را اجرا کنیم:
سه ردیف خروجی را مطابق سه رکوردی که ثبت کردیم، بازگشت میدهد.
اما اگر بخواهیم این سه ردیف را با هم ترکیب کرده و تبدیل به یک نتیجهی واحد کنیم، میتوان از For XML به نحو ذیل استفاده کرد:
بررسی متد xml.nodes
متد xml.nodes اندکی متفاوت است نسبت به تمام متدهایی که تاکنون بررسی کردیم. کار آن تجزیهی محتوای XML ایی به ستونها و سطرها میباشد. بسیار شبیه است به متد OpenXML اما کارآیی بهتری دارد.
در اینجا یک سند XML را درنظر بگیرید که از چندین نود شخص تشکیل شدهاست. اغلب آنها دارای یک name هستند. چهارمین نود، دو نام دارد و آخری بدون نام است.
در ادامه قصد داریم این اطلاعات را تبدیل به ردیفهایی کنیم که هر ردیف حاوی یک نام است. اولین سعی احتمالا استفاده از متد value خواهد بود:
این روش کار نمیکند زیرا متد value، بیش از یک مقدار را نمیتواند بازگشت دهد. البته میتوان از متد value به نحو زیر استفاده کرد:
اما حاصل آن دقیقا چیزی نیست که دنبالش هستیم؛ ما دقیقا نیاز به تمام نامها داریم و نه تنها یکی از آنها را.
سعی بعدی استفاده از متد query است:
در این حالت تمام نامها را بدست میآوریم:
اما این حاصل دو مشکل را به همراه دارد:
الف) خروجی آن XML است.
ب) تمام اینها در طی یک ردیف و یک ستون بازگشت داده میشوند.
و این خروجی نیز چیزی نیست که برای ما مفید باشد. ما به ازای هر شخص نیاز به یک ردیف جداگانه داریم. اینجا است که متد xml.nodes مفید واقع میشود:
خروجی متد xml.nodes یک table valued function است؛ یک جدول را باز میگرداند که دقیقا حاوی یک ستون میباشد. به همین جهت Alias آنرا با tab col مشخص کردهایم. tab متناظر است با جدول بازگشت داده شده و col متناظر است با تک ستون این جدول حاصل. این نامها در اینجا مهم نیستند؛ اما ذکر آنها اجباری است.
هر ردیف حاصل از این جدول بازگشت داده شده، یک اشارهگر است. به همین جهت نمیتوان آنها را مستقیما نمایش داد. هر سطر آن، به نودی که با آن مطابق XQuery وارد شده تطابق داشته است، اشاره میکند. در اینجا مطابق کوئری نوشته شده، هر ردیف به یک نود name اشاره میکند. در ادامه برای استخراج اطلاعات آن میتوان از متد text استفاده کرد.
اگر قصد داشتید، اطلاعات کامل نود ردیف جاری را مشاهده کنید میتوان از
استفاده کرد. دات در اینجا به معنای self است. دو دات (نقطه) پشت سرهم به معنای درخواست اطلاعات والد نود میباشد.
روش دیگر بدست آوردن مقدار یک نود را در کوئری ذیل مشاهده میکنید؛ value دات و data دات. خروجی value مقدار آن نود است و خروجی data مقدار آن نود با فرمت XML.
همچنین اگر بخواهیم اطلاعات تنها یک نود خاص را بدست بیاوریم، میتوان مانند کوئری ذیل عمل کرد:
در مورد کار با جداول، بجای متغیرهای T-SQL نیز روال کار به همین نحو است:
در اینجا یک جدول حاوی ستون XML ایی ایجاد شدهاست. سپس چهار ردیف در آن ثبت شدهاند. در آخر مقدار ویژگی نام این ردیفها بازگشت داده شدهاست.
نکته : استفادهی وسیع SQL Server از XML برای پردازش کارهای درونی آن
بسیاری از ابزارهایی که در نگارشهای جدید SQL Server اضافه شدهاند و یا مورد استفاده قرار میگیرند، استفادهی وسیعی از امکانات توکار XML آن دارند. مانند:
Showplan، گرافهای dead lock، گزارش پروسههای بلاک شده، اطلاعات رخدادها، SSIS Jobs، رخدادهای Trace و ...
مثال اول: کدام کوئریها در Plan cache، کارآیی پایینی داشته و table scan را انجام میدهند؟
اطلاعات Query Plan در SQL Server با فرمت XML ارائه میشود. در اینجا میخواهیم یک سری متغیر مانند Clustered Index Scan و امثال آنرا از ویژگی PhysicalOp آن کوئری بگیریم. بنابراین از متد sql:variable کمک گرفته شدهاست.
اگر علاقمند هستید که اصل این اطلاعات را با فرمت XML مشاهده کنید، کوئری نوشته شده را تا پیش از where آن یکبار مستقلا اجرا کنید. ستون آخر آن query_plan نام دارد و حاوی اطلاعات XML ایی است.
مثال دوم: استخراج اپراتورهای رابطهای (RelOp) از یک Query Plan ذخیره شده
در اینجا کار کردن با WITH XMLNAMESPACES در حین استفاده از متد xml.nodes سادهتر است؛ بجای قرار دادن فضای نام در تمام کوئریهای نوشته شده.
بررسی متد xml.modify
تا اینجا تمام کارهایی که صورت گرفت و نکاتی که بررسی شدند، به مباحث select اختصاص داشتند. اما insert، delete و یا update قسمتی از یک سند XML بررسی نشدند. برای این منظور باید از متد xml.modify استفاده کرد. از آن در عبارات update و یا set کمک گرفته شده و ورودی آن نباید نال باشد. در ادامه در طی مثالهایی این موارد را بررسی خواهیم کرد.
ابتدا فرض کنید که سند XML ما چنین شکلی را دارا است:
در ادامه قصد داریم یک نود جدید را پس از CustomerName اضافه کنیم.
اینکار را با استفاده از دستور insert، به نحو فوق میتوان انجام داد. از عبارت Set و متغیر doc مقدار دهی شده، کار شروع شده و سپس نود جدیدی پس از (after) اولین نود CustomerName موجود insert میشود. Select بعدی نتیجه را نمایش خواهد داد.
در SQL Server 2008 به بعد، امکان استفاده از متغیرهای T-SQL نیز در اینجا مجاز شدهاست:
بنابراین اگر نیاز به تعریف متغیری در اینجا داشتید از جمع زدن رشتهها استفاده نکنید. حتما نیاز است متغیر تعریف شود و گرنه باخطای ذیل متوقف خواهید شد:
افزودن ویژگیهای جدید به یک سند XML توسط متد xml.modify
اگر بخواهیم یک ویژگی (attribute) جدید را به نود خاصی اضافه کنیم میتوان به نحو ذیل عمل کرد:
که خروجی دو سطر ابتدایی آن پس از اضافه شدن ویژگی status با مقدار backorder به نحو ذیل است:
حذف نودهای یک سند XML توسط متد xml.modify
اگر بخواهیم تمام LineItemها را حذف کنیم میتوان نوشت:
با این خروجی:
به روز رسانی نودهای یک سند XML توسط متد xml.modify
اگر نیاز باشد تا مقدار یک نود را تغییر دهیم میتوان از replace value of استفاده کرد:
با خروجی ذیل که در آن نام اولین مشتری با مقدار Farid جایگزین شده است:
replace value of فقط با یک نود کار میکند و همچنین، فقط مقدار آن نود را تغییر میدهد. به همین جهت از متد text استفاده شدهاست. اگر از text استفاده نشود با خطای ذیل متوقف خواهیم شد:
به روز رسانی نودهای خالی توسط متد xml.modify
باید دقت داشت، نودهای خالی (بدون مقدار)، مانند LineItems پس از delete کلیه اعضای آن در مثال قبل، قابل replace نیستند و باید مقادیر جدید را در آنها insert کرد. یک مثال:
در این مثال اگر از replace value of برای مقدار دهی نود سوم استفاده میشد:
تغییری را پس از اعمال دستورات مشاهده نمیکردید؛ زیرا این المان ()text ایی را برای replace شدن ندارد.
ابتدا جدول xmlTest را به همراه چند رکورد ثبت شده در آن، درنظر بگیرید:
CREATE TABLE xmlTest ( id INT IDENTITY PRIMARY KEY, doc XML ) GO INSERT xmlTest VALUES('<Person name="Vahid" />') INSERT xmlTest VALUES('<Person name="Farid" />') INSERT xmlTest VALUES('<Person name="Mehdi" /><Person name="Hamid" />') GO
استفاده از متد sql:column
در ادامه میخواهیم مقدار ویژگی name رکوردی را که نام آن Vahid است، به همراه id آن ردیف، توسط یک XQuery بازگشت دهیم:
SELECT doc.query(' for $p in //Person where $p/@name="Vahid" return <li>{data($p/@name)} has id = {sql:column("xmlTest.id")}</li> ') FROM xmlTest
در مورد متد data در قسمت قبل بیشتر بحث شد و از آن برای استخراج دادهی یک ویژگی در اینجا استفاده شدهاست. عبارات داخل {} نیز پویا بوده و به همراه سایر قسمتهای ثابت return، ابتدا محاسبه و سپس بازگشت داده میشود.
اگر این کوئری را اجرا کنید، ردیف اول آن مساوی عبارت زیر خواهد بود
<li>Vahid has id = 1</li>
یک روش برای حذف این ردیفهای خالی استفاده از متد exist است به شکل زیر:
SELECT doc.query(' for $p in //Person where $p/@name="Vahid" return <li>{data($p/@name)} has id = {sql:column("xmlTest.id")}</li> ') FROM xmlTest WHERE doc.exist(' for $p in //Person where $p/@name="Vahid" return <li>{data($p/@name)} has id = {sql:column("xmlTest.id")}</li> ')=1
روش دوم استفاده از یک derived table و بازگشت ردیفهای غیرخالی است:
SELECT * FROM ( (SELECT doc.query(' for $p in //Person where $p/@name="Vahid" return <li>{data($p/@name)} has id = {sql:column("xmlTest.id")}</li> ') AS col1 FROM xmlTest) ) A WHERE CONVERT(VARCHAR(8000), col1)<>''
استفاده از متد sql:variable
DECLARE @number INT = 1 SELECT doc.query(' for $p in //Person where $p/@name="Vahid" return <li>{data($p/@name)} has number = {sql:variable("@number")}</li> ') FROM xmlTest
استفاده از For XML برای دریافت یکبارهی تمام ردیفهای XML
اگر کوئری معمولی ذیل را اجرا کنیم:
SELECT doc.query('/Person') FROM xmlTest
اما اگر بخواهیم این سه ردیف را با هم ترکیب کرده و تبدیل به یک نتیجهی واحد کنیم، میتوان از For XML به نحو ذیل استفاده کرد:
DECLARE @doc XML SET @doc = (SELECT * FROM xmlTest FOR XML AUTO, ELEMENTS) SELECT @doc.query('/xmlTest/doc/Person')
بررسی متد xml.nodes
متد xml.nodes اندکی متفاوت است نسبت به تمام متدهایی که تاکنون بررسی کردیم. کار آن تجزیهی محتوای XML ایی به ستونها و سطرها میباشد. بسیار شبیه است به متد OpenXML اما کارآیی بهتری دارد.
DECLARE @doc XML =' <people> <person><name>Vahid</name></person> <person><name id="2">Farid</name></person> <person><name>Mehdi</name></person> <person><name>Hooshang</name><name id="1">Hooshi</name></person> <person></person> </people> '
در ادامه قصد داریم این اطلاعات را تبدیل به ردیفهایی کنیم که هر ردیف حاوی یک نام است. اولین سعی احتمالا استفاده از متد value خواهد بود:
SELECT @doc.value('/people/person/name', 'varchar(50)')
SELECT @doc.value('(/people/person/name)[1]', 'varchar(50)')
سعی بعدی استفاده از متد query است:
SELECT @doc.query('/people/person/name')
<name>Vahid</name> <name id="2">Farid</name> <name>Mehdi</name> <name>Hooshang</name> <name id="1">Hooshi</name>
الف) خروجی آن XML است.
ب) تمام اینها در طی یک ردیف و یک ستون بازگشت داده میشوند.
و این خروجی نیز چیزی نیست که برای ما مفید باشد. ما به ازای هر شخص نیاز به یک ردیف جداگانه داریم. اینجا است که متد xml.nodes مفید واقع میشود:
SELECT tab.col.value('text()[1]', 'varchar(50)') AS name, tab.col.query('.'), tab.col.query('..') from @doc.nodes('/people/person/name') AS tab(col)
هر ردیف حاصل از این جدول بازگشت داده شده، یک اشارهگر است. به همین جهت نمیتوان آنها را مستقیما نمایش داد. هر سطر آن، به نودی که با آن مطابق XQuery وارد شده تطابق داشته است، اشاره میکند. در اینجا مطابق کوئری نوشته شده، هر ردیف به یک نود name اشاره میکند. در ادامه برای استخراج اطلاعات آن میتوان از متد text استفاده کرد.
اگر قصد داشتید، اطلاعات کامل نود ردیف جاری را مشاهده کنید میتوان از
tab.col.query('.'),
روش دیگر بدست آوردن مقدار یک نود را در کوئری ذیل مشاهده میکنید؛ value دات و data دات. خروجی value مقدار آن نود است و خروجی data مقدار آن نود با فرمت XML.
SELECT tab.col.value('.', 'varchar(50)') AS name, tab.col.query('data(.)'), tab.col.query('.'), tab.col.query('..') from @doc.nodes('/people/person/name') AS tab(col)
همچنین اگر بخواهیم اطلاعات تنها یک نود خاص را بدست بیاوریم، میتوان مانند کوئری ذیل عمل کرد:
SELECT tab.col.value('name[.="Farid"][1]', 'varchar(50)') AS name, tab.col.value('name[.="Farid"][1]/@id', 'varchar(50)') AS id, tab.col.query('.') from @doc.nodes('/people/person[name="Farid"]') AS tab(col)
در مورد کار با جداول، بجای متغیرهای T-SQL نیز روال کار به همین نحو است:
DECLARE @tblXML TABLE ( id INT IDENTITY PRIMARY KEY, doc XML ) INSERT @tblXML VALUES('<person name="Vahid" />') INSERT @tblXML VALUES('<person name="Farid" />') INSERT @tblXML VALUES('<person />') INSERT @tblXML VALUES(NULL) SELECT id, doc.value('(/person/@name)[1]', 'varchar(50)') AS name FROM @tblXML
نکته : استفادهی وسیع SQL Server از XML برای پردازش کارهای درونی آن
بسیاری از ابزارهایی که در نگارشهای جدید SQL Server اضافه شدهاند و یا مورد استفاده قرار میگیرند، استفادهی وسیعی از امکانات توکار XML آن دارند. مانند:
Showplan، گرافهای dead lock، گزارش پروسههای بلاک شده، اطلاعات رخدادها، SSIS Jobs، رخدادهای Trace و ...
مثال اول: کدام کوئریها در Plan cache، کارآیی پایینی داشته و table scan را انجام میدهند؟
CREATE PROCEDURE LookForPhysicalOps (@op VARCHAR(30)) AS SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.* FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql CROSS APPLY sys.dm_exec_query_plan(plan_handle) p WHERE query_plan.exist(' declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")] ') = 1 GO EXECUTE LookForPhysicalOps 'Table Scan' EXECUTE LookForPhysicalOps 'Clustered Index Scan' EXECUTE LookForPhysicalOps 'Hash Match'
اگر علاقمند هستید که اصل این اطلاعات را با فرمت XML مشاهده کنید، کوئری نوشته شده را تا پیش از where آن یکبار مستقلا اجرا کنید. ستون آخر آن query_plan نام دارد و حاوی اطلاعات XML ایی است.
مثال دوم: استخراج اپراتورهای رابطهای (RelOp) از یک Query Plan ذخیره شده
WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID, RelOp.op.value(N'@NodeId', N'int') AS OperationID, RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator, RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator, RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost, RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO, RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU, RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows, cp.plan_handle AS PlanHandle, st.TEXT AS QueryText, qp.query_plan AS QueryPlan, cp.cacheobjtype AS CacheObjectType, cp.objtype AS ObjectType FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp(op)
بررسی متد xml.modify
تا اینجا تمام کارهایی که صورت گرفت و نکاتی که بررسی شدند، به مباحث select اختصاص داشتند. اما insert، delete و یا update قسمتی از یک سند XML بررسی نشدند. برای این منظور باید از متد xml.modify استفاده کرد. از آن در عبارات update و یا set کمک گرفته شده و ورودی آن نباید نال باشد. در ادامه در طی مثالهایی این موارد را بررسی خواهیم کرد.
ابتدا فرض کنید که سند XML ما چنین شکلی را دارا است:
DECLARE @doc XML = ' <Invoice> <InvoiceId>100</InvoiceId> <CustomerName>Vahid</CustomerName> <LineItems> <LineItem> <Sku>134</Sku> <Quantity>10</Quantity> <Description>Item 1</Description> <UnitPrice>9.5</UnitPrice> </LineItem> <LineItem> <Sku>150</Sku> <Quantity>5</Quantity> <Description>Item 2</Description> <UnitPrice>1.5</UnitPrice> </LineItem> </LineItems> </Invoice> '
SET @doc.modify(' insert <InvoiceInfo><InvoiceDate>2014-02-10</InvoiceDate></InvoiceInfo> after /Invoice[1]/CustomerName[1] ') SELECT @doc
<Invoice> <InvoiceId>100</InvoiceId> <CustomerName>Vahid</CustomerName> <InvoiceInfo> <InvoiceDate>2014-02-10</InvoiceDate> </InvoiceInfo> <LineItems> ...
در SQL Server 2008 به بعد، امکان استفاده از متغیرهای T-SQL نیز در اینجا مجاز شدهاست:
SET @x.modify('insert sql:variable("@x") into /doc[1]')
The argument 1 of the XML data type method "modify" must be a string literal.
افزودن ویژگیهای جدید به یک سند XML توسط متد xml.modify
اگر بخواهیم یک ویژگی (attribute) جدید را به نود خاصی اضافه کنیم میتوان به نحو ذیل عمل کرد:
SET @doc.modify(' insert attribute status{"backorder"} into /Invoice[1] ') SELECT @doc
<Invoice status="backorder"> <InvoiceId>100</InvoiceId> ....
حذف نودهای یک سند XML توسط متد xml.modify
اگر بخواهیم تمام LineItemها را حذف کنیم میتوان نوشت:
SET @doc.modify('delete /Invoice/LineItems/LineItem') SELECT @doc
<Invoice status="backorder"> <InvoiceId>100</InvoiceId> <CustomerName>Vahid</CustomerName> <InvoiceInfo> <InvoiceDate>2014-02-10</InvoiceDate> </InvoiceInfo> <LineItems /> </Invoice>
به روز رسانی نودهای یک سند XML توسط متد xml.modify
اگر نیاز باشد تا مقدار یک نود را تغییر دهیم میتوان از replace value of استفاده کرد:
SET @doc.modify('replace value of /Invoice[1]/CustomerName[1]/text()[1] with "Farid" ') SELECT @doc
<Invoice status="backorder"> <InvoiceId>100</InvoiceId> <CustomerName>Farid</CustomerName> <InvoiceInfo> <InvoiceDate>2014-02-10</InvoiceDate> </InvoiceInfo> <LineItems /> </Invoice>
The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content.
به روز رسانی نودهای خالی توسط متد xml.modify
باید دقت داشت، نودهای خالی (بدون مقدار)، مانند LineItems پس از delete کلیه اعضای آن در مثال قبل، قابل replace نیستند و باید مقادیر جدید را در آنها insert کرد. یک مثال:
DECLARE @tblTest AS TABLE (xmlField XML) INSERT INTO @tblTest(xmlField) VALUES ( '<Sample> <Node1>Value1</Node1> <Node2>Value2</Node2> <Node3/> </Sample>' ) DECLARE @newValue VARCHAR(50) = 'NewValue' UPDATE @tblTest SET xmlField.modify( 'insert text{sql:variable("@newValue")} into (/Sample/Node3)[1] [not(text())]' ) SELECT xmlField.value('(/Sample/Node3)[1]','varchar(50)') FROM @tblTest
UPDATE @tblTest SET xmlField.modify( 'replace value of (/Sample/Node3/text())[1] with sql:variable("@newValue")' )
سلام آقای نصیری بنده یه سوال داشتم بهترین روش برای پیاده سازی رابطه n به m برای طراحی صفحه ادمین چیه؟ببنید بنده تویه قسمت ادمین سایت یک صفحه برای هر جدول طراحی میکنم که از طریق اون بتونم اطلاعات جدول رو حذف و یا بروزرسانی و یا رکورد جدید وارد کنم حالا برای طراحی صفحه ای که جداول اون رابطه n به m دارن دچار مشکل شدم .مثلا فرض بکنید که جدول پست 1000 رکورد داره و جدول tags هم 500 رکورد .حالا برای وارد کردن یک رکورد در داخل جدول رابط باید ID یک رکورد پست و همچنین ID یک رکورد tags رو تویه جدول رابط قرار بدیم بنده خودم از dropdownlist استفاده کردم برای اینکار که چون تعداد رکوردهای خیلی زیاده این روش جواب گو نیست به نظر شما بهترین روش چیه؟ اگه منظورم رو متوجه نشدید بگید توضیح بیشتری بدم.ممنون
مطالب دورهها
پشتیبانی از XML Schema در SQL Server
XML Schema چیست؟
XML Schema معرف ساختار، نوع دادهها و المانهای یک سند XML است. البته باید درنظر داشت که تعریف XML Schema کاملا اختیاری است و اگر تعریف شود مزیت اعتبارسنجی دادههای در حال ذخیره سازی در بانک اطلاعاتی را به صورت خودکار به همراه خواهد داشت. در این حالت به نوع دادهای XML دارای اسکیما، typed XML و به نوع بدون اسکیما، untyped XML گفته میشود.
به یک نوع XML، چندین اسکیمای مختلف را میتوان نسبت داد و به آن XML schema collection نیز میگویند.
XML schema collections پیش فرض و سیستمی
تعدادی XML Schema پیش فرض در SQL Server تعریف شدهاند که به آنها sys schema collections گفته میشود.
در اینجا پیشوندها و فضاهای نام sys schema collections را ملاحظه میکنید. از این اسکیماها برای تعاریف strongly typed امکانات موجود در SQL Server کمک گرفته شدهاست.
اگر علاقمند باشید تا این تعاریف را مشاهده کنید به مسیر Program Files\Microsoft SQL Server\version\Tools\Binn\schemas\sqlserver در جایی که SQL Server نصب شدهاست مراجعه نمائید. برای مثال در مسیر Tools\Binn\schemas\sqlserver\2006\11\events فایل events.xsd قابل مشاهده است و یا در مسیر Tools\Binn\schemas\sqlserver\2004\07 اسکیمای ابزارهای query processor و show plan قابل بررسی میباشد.
مهمترین آنها را در پوشه Tools\Binn\schemas\sqlserver\2004\sqltypes در فایل sqltypes.xsd میتوانید ملاحظه کنید. اگر به محتوای آن دقت کنید، قسمتی از آن به شرح ذیل است:
در اینجا نوعهای توکار char تا ntext به xsd:string نگاشت شدهاند و برای اعتبارسنجی datetime و نگاشت آن، از الگوی ذیل استفاده میشود؛ به همراه حداقل و حداکثر قابل تعریف:
ادیتور SQL Server managment studio به خوبی، گشودن، ایجاد و یا ویرایش فایلهایی با پسوند xsd را پشتیبانی میکند.
تعریف XML Schema و استفاده از آن جهت تعریف یک strongly typed XML
XML Schema مورد استفاده در SQL Server حتما باید در بانک اطلاعاتی ذخیره شود و برای خواندن آن، برای مثال از فایل سیستم استفاده نخواهد شد.
در اینجا نحوهی تعریف کلی یک XML Schema collection و سپس انتساب آنرا به یک ستون XML ملاحظه میکنید. ستون invoice که از نوع XML تعریف شده، ارجاعی را به اسکیمای تعریف شده دارد.
در ادامه نحوهی تعریف یک اسکیمای نمونه قابل مشاهده است:
در این اسکیما، یک نوع ساده به نام dim تعریف شدهاست که محدودیت آن، ورود اعداد صحیح میباشد. همچنین امکان تعریف نوعهای پیچیده نیز در اینجا وجود دارد. برای مثال نوع پچیده Point دارای دو المان X و Y از نوع dim در ادامه تعریف شدهاست. المانی که نهایتا بر این اساس در XML ظاهر خواهد شد توسط xs:element تعریف شدهاست.
اکنون برای آزمایش اسکیمای تعریف شده، جدول geo_tab را به نحو ذیل تعریف میکنیم و سپس سعی در insert دو رکورد در آن خواهیم کرد:
در اینجا اگر دقت کنید، برای تعریف نام اسکیمای مورد استفاده، واژه content نیز ذکر شدهاست. Content مقدار پیش فرض است و در آن پذیرش XML Fragments یا محتوای XML ایی با بیش از یک Root element مجاز است. حالت دیگر آن document است که تنها یک Root element را میپذیرد.
در این مثال، insert اول با موفقیت انجام خواهد شد؛ اما insert دوم با خطای ذیل متوقف میشود:
همانطور که ملاحظه میکنید، چون در insert دوم، در المان عددی Y، مقدار test وارد شدهاست و تطابقی با اسکیمای تعریف شده ندارد، insert آن مجاز نخواهد بود.
یافتن محل ذخیره سازی اطلاعات اسکیما در SQL Server
اگر علاقمند باشید تا با محل ذخیره سازی اطلاعات اسکیما، نوعهای تعریف شده و حتی محل استفاده از آنها در بانکهای اطلاعاتی مختلف موجود آشنا شوید و گزارشی از آنها تهیه کنید، میتوانید از کوئریهای ذیل استفاده نمائید:
باید دقت داشت زمانیکه یک schema در حال استفاده است (یک رکورد ثبت شده مقید به آن تعریف شده باشد)، امکان drop آن نخواهد بود. حتما باید اطلاعات و ستون مرتبط، ارجاعی را به schema نداشته باشند تا بتوان آن schema را حذف کرد.
محتوای اسکیمای ذخیره شده به شکل xsd تعریف شده، ذخیره سازی نمیشود. بلکه اطلاعات آن تجزیه شده و سپس در جداول سیستمی SQL Server ذخیره میگردند. هدف از اینکار، بالا بردن سرعت اعتبارسنجی typed XMLها است.
بنابراین بدیهی است در این حالت اطلاعاتی مانند commnets موجود در xsd تهیه شده در بانک اطلاعاتی ذخیره نمیگردند.
برای بازیابی اطلاعات اسکیمای ذخیره شده میتوان از متد xml_schema_namespace استفاده کرد:
برای تعریف و یا تغییر یک XML Schema نیاز به دسترسی مدیریتی یا dbo است (به صورت پیش فرض). همچنین برای استفاده از Schema تعریف شده، کاربر متصل به SQL Server باید دسترسی Execute و References نیز داشته باشد.
نحوهی ویرایش یک schema collection موجود
چند نکته:
- امکان alter یک schema collection وجود دارد.
- میتوان یک schema جدید را به collection موجود افزود.
- امکان افزودن (و نه تغییر) نوعهای یک schema موجود، میسر است.
- امکان drop یک اسکیما از collection موجودی وجود ندارد. باید کل collection را drop کرد و سپس آنرا تعریف نمود.
- جداولی با فیلدهای nvarchar را میتوان به فیلدهای XML تبدیل کرد و برعکس.
- امکان تغییر یک فیلد XML به حالت untyped و برعکس وجود دارد.
فرض کنید که میخواهیم اسکیمای متناظر با یک ستون XML را تغییر دهیم. ابتدا باید آن ستون XML ایی را Alter کرده و قید اسکیمای آنرا برداریم. سپس باید اسکیمای موجود را drop و مجددا ایجاد کرد. همانطور که پیشتر ذکر شد، اگر اسکیمایی در حال استفاده باشد، قابل drop نیست. در ادامه مجددا باید ستون XML ایی را تغییر داده و اسکیمای آنرا معرفی کرد.
روش دوم مدیریت این مساله، اجازه دادن به حضور بیش از یک اسکیما در مجموعه است. به عبارتی نگارشبندی اسکیما که به نحو ذیل قابل انجام است:
در اینجا به collection موجود، یک اسکیمای جدید (برای مثال نگارش دوم اسکیمای فعلی) اضافه میشود. در این حالت geocol، هر دو نوع اسکیمای موجود را پشتیبانی خواهد کرد.
نحوهی import یک فایل xsd و ذخیره آن به صورت اسکیما
اگر بخواهیم یک فایل xsd موجود را به عنوان xsd معرفی کنیم میتوان از دستورات ذیل کمک گرفت:
در اینجا به کمک openrowset فایل xsd موجود، در یک متغیر xml بارگذاری شده و سپس در دستور ایجاد یک اسکیما کالکشن جدید استفاده میشود.
از openrowset برای خواندن یک فایل xml موجود، جهت insert محتوای آن در بانک اطلاعاتی نیز میتوان استفاده کرد.
محدودیتهای XML Schema در SQL Server
تمام استاندارد XML Schema در SQL Server پشتیبانی نمیشود و همچنین این مورد از نگارشی به نگارشی دیگر نیز ممکن است تغییر یافته و بهبود یابد. برای مثال در SQL Server 2005 از xs:any پشتیبانی نمیشود اما در SQL Server 2008 این محدودیت برطرف شدهاست. همچنین مواردی مانند xs:include، xs:redefine، xs:notation، xs:key، xs:keyref و xs:unique در SQL Server پشتیبانی نمیشوند.
یک نکتهی تکمیلی
برنامهای به نام xsd.exe به همراه Visual Studio ارائه میشود که قادر است به صورت خودکار از یک فایل XML موجود، XML Schema تولید کند. اطلاعات بیشتر
XML Schema معرف ساختار، نوع دادهها و المانهای یک سند XML است. البته باید درنظر داشت که تعریف XML Schema کاملا اختیاری است و اگر تعریف شود مزیت اعتبارسنجی دادههای در حال ذخیره سازی در بانک اطلاعاتی را به صورت خودکار به همراه خواهد داشت. در این حالت به نوع دادهای XML دارای اسکیما، typed XML و به نوع بدون اسکیما، untyped XML گفته میشود.
به یک نوع XML، چندین اسکیمای مختلف را میتوان نسبت داد و به آن XML schema collection نیز میگویند.
XML schema collections پیش فرض و سیستمی
تعدادی XML Schema پیش فرض در SQL Server تعریف شدهاند که به آنها sys schema collections گفته میشود.
Prefix - Namespace xml = http://www.w3.org/XML/1998/namespace xs = http://www.w3.org/2001/XMLSchema xsi = http://www.w3.org/2001/XMLSchema-instance fn = http://www.w3.org/2004/07/xpath-functions sqltypes = http://schemas.microsoft.com/sqlserver/2004/sqltypes xdt = http://www.w3.org/2004/07/xpath-datatypes (no prefix) = urn:schemas-microsoft-com:xml-sql (no prefix) = http://schemas.microsoft.com/sqlserver/2004/SOAP
اگر علاقمند باشید تا این تعاریف را مشاهده کنید به مسیر Program Files\Microsoft SQL Server\version\Tools\Binn\schemas\sqlserver در جایی که SQL Server نصب شدهاست مراجعه نمائید. برای مثال در مسیر Tools\Binn\schemas\sqlserver\2006\11\events فایل events.xsd قابل مشاهده است و یا در مسیر Tools\Binn\schemas\sqlserver\2004\07 اسکیمای ابزارهای query processor و show plan قابل بررسی میباشد.
مهمترین آنها را در پوشه Tools\Binn\schemas\sqlserver\2004\sqltypes در فایل sqltypes.xsd میتوانید ملاحظه کنید. اگر به محتوای آن دقت کنید، قسمتی از آن به شرح ذیل است:
<xsd:simpleType name="char"> <xsd:restriction base="xsd:string"/> </xsd:simpleType> <xsd:simpleType name="nchar"> <xsd:restriction base="xsd:string"/> </xsd:simpleType> <xsd:simpleType name="varchar"> <xsd:restriction base="xsd:string"/> </xsd:simpleType> <xsd:simpleType name="nvarchar"> <xsd:restriction base="xsd:string"/> </xsd:simpleType> <xsd:simpleType name="text"> <xsd:restriction base="xsd:string"/> </xsd:simpleType> <xsd:simpleType name="ntext"> <xsd:restriction base="xsd:string"/> </xsd:simpleType>
<xsd:simpleType name="datetime"> <xsd:restriction base="xsd:dateTime"> <xsd:pattern value="((000[1-9])|(00[1-9][0-9])|(0[1-9][0-9]{2})|([1-9][0-9]{3}))-((0[1-9])|(1[012]))-((0[1-9])|([12][0-9])|(3[01]))T(([01][0-9])|(2[0-3]))(:[0-5][0-9]){2}(\.[0-9]{2}[037])?"/> <xsd:maxInclusive value="9999-12-31T23:59:59.997"/> <xsd:minInclusive value="1753-01-01T00:00:00.000"/> </xsd:restriction> </xsd:simpleType>
تعریف XML Schema و استفاده از آن جهت تعریف یک strongly typed XML
XML Schema مورد استفاده در SQL Server حتما باید در بانک اطلاعاتی ذخیره شود و برای خواندن آن، برای مثال از فایل سیستم استفاده نخواهد شد.
CREATE XML SCHEMA COLLECTION invcol AS '<xs:schema ... targetNamespace="urn:invoices"> ... </xs:schema> ' CREATE TABLE Invoices( id int IDENTITY PRIMARY KEY, invoice XML(invcol) )
در ادامه نحوهی تعریف یک اسکیمای نمونه قابل مشاهده است:
CREATE XML SCHEMA COLLECTION geocol AS '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:geo" elementFormDefault="qualified" xmlns:tns="urn:geo"> <xs:simpleType name="dim"> <xs:restriction base="xs:int" /> </xs:simpleType> <xs:complexType name="Point"> <xs:sequence> <xs:element name="X" type="tns:dim" minOccurs="0" maxOccurs="unbounded" /> <xs:element name="Y" type="tns:dim" minOccurs="0" maxOccurs="unbounded" /> </xs:sequence> </xs:complexType> <xs:element name="Point" type="tns:Point" /> </xs:schema>'
اکنون برای آزمایش اسکیمای تعریف شده، جدول geo_tab را به نحو ذیل تعریف میکنیم و سپس سعی در insert دو رکورد در آن خواهیم کرد:
declare @geo_tab table( id int identity primary key, point xml(content geocol) ) insert into @geo_tab values('<Point xmlns="urn:geo"><X>10</X><Y>20</Y></Point>') insert into @geo_tab values('<Point xmlns="urn:geo"><X>10</X><Y>test</Y></Point>')
در این مثال، insert اول با موفقیت انجام خواهد شد؛ اما insert دوم با خطای ذیل متوقف میشود:
XML Validation: Invalid simple type value: 'test'. Location: /*:Point[1]/*:Y[1]
یافتن محل ذخیره سازی اطلاعات اسکیما در SQL Server
اگر علاقمند باشید تا با محل ذخیره سازی اطلاعات اسکیما، نوعهای تعریف شده و حتی محل استفاده از آنها در بانکهای اطلاعاتی مختلف موجود آشنا شوید و گزارشی از آنها تهیه کنید، میتوانید از کوئریهای ذیل استفاده نمائید:
select * from sys.xml_schema_collections select * from sys.xml_schema_namespaces select * from sys.xml_schema_elements select * from sys.xml_schema_attributes select * from sys.xml_schema_types select * from sys.column_xml_schema_collection_usages select * from sys.parameter_xml_schema_collection_usages
محتوای اسکیمای ذخیره شده به شکل xsd تعریف شده، ذخیره سازی نمیشود. بلکه اطلاعات آن تجزیه شده و سپس در جداول سیستمی SQL Server ذخیره میگردند. هدف از اینکار، بالا بردن سرعت اعتبارسنجی typed XMLها است.
بنابراین بدیهی است در این حالت اطلاعاتی مانند commnets موجود در xsd تهیه شده در بانک اطلاعاتی ذخیره نمیگردند.
برای بازیابی اطلاعات اسکیمای ذخیره شده میتوان از متد xml_schema_namespace استفاده کرد:
declare @x xml select @x = xml_schema_namespace(N'dbo', N'geocol') print convert(varchar(max), @x)
نحوهی ویرایش یک schema collection موجود
چند نکته:
- امکان alter یک schema collection وجود دارد.
- میتوان یک schema جدید را به collection موجود افزود.
- امکان افزودن (و نه تغییر) نوعهای یک schema موجود، میسر است.
- امکان drop یک اسکیما از collection موجودی وجود ندارد. باید کل collection را drop کرد و سپس آنرا تعریف نمود.
- جداولی با فیلدهای nvarchar را میتوان به فیلدهای XML تبدیل کرد و برعکس.
- امکان تغییر یک فیلد XML به حالت untyped و برعکس وجود دارد.
فرض کنید که میخواهیم اسکیمای متناظر با یک ستون XML را تغییر دهیم. ابتدا باید آن ستون XML ایی را Alter کرده و قید اسکیمای آنرا برداریم. سپس باید اسکیمای موجود را drop و مجددا ایجاد کرد. همانطور که پیشتر ذکر شد، اگر اسکیمایی در حال استفاده باشد، قابل drop نیست. در ادامه مجددا باید ستون XML ایی را تغییر داده و اسکیمای آنرا معرفی کرد.
روش دوم مدیریت این مساله، اجازه دادن به حضور بیش از یک اسکیما در مجموعه است. به عبارتی نگارشبندی اسکیما که به نحو ذیل قابل انجام است:
alter XML SCHEMA COLLECTION geocol add @x
نحوهی import یک فایل xsd و ذخیره آن به صورت اسکیما
اگر بخواهیم یک فایل xsd موجود را به عنوان xsd معرفی کنیم میتوان از دستورات ذیل کمک گرفت:
declare @x xml set @x = (select * from openrowset(bulk 'c:\path\file.xsd', single_blob) as x) CREATE XML SCHEMA COLLECTION geocol2 AS @x
از openrowset برای خواندن یک فایل xml موجود، جهت insert محتوای آن در بانک اطلاعاتی نیز میتوان استفاده کرد.
محدودیتهای XML Schema در SQL Server
تمام استاندارد XML Schema در SQL Server پشتیبانی نمیشود و همچنین این مورد از نگارشی به نگارشی دیگر نیز ممکن است تغییر یافته و بهبود یابد. برای مثال در SQL Server 2005 از xs:any پشتیبانی نمیشود اما در SQL Server 2008 این محدودیت برطرف شدهاست. همچنین مواردی مانند xs:include، xs:redefine، xs:notation، xs:key، xs:keyref و xs:unique در SQL Server پشتیبانی نمیشوند.
یک نکتهی تکمیلی
برنامهای به نام xsd.exe به همراه Visual Studio ارائه میشود که قادر است به صورت خودکار از یک فایل XML موجود، XML Schema تولید کند. اطلاعات بیشتر
فرض کنید میخواهیم سطرهای جدول را 6 تا 6 تا سوا کنیم و به هر کدام یک عددی انتساب دهیم و هر قسم تولید شده را نیز 2 تا 2 تا سوا کنیم و بهش عدد انتساب دهیم.
به تصویر زیر توجه بفرمایید. ابتدا دادهها به دو دسته ششتایی تقسیم شدن(ستون ntl)، سپس هر کدام از این دستهها نیز به سه دسته دوتایی تقسیم شدن(ستون grp) هدف ما تولید دو ستون ntl و grp توسط query میباشد.
برای بدست آوردن مقادیر دو ستون مذکور روشهای متنوعی وجود دارد که برخی از آنها را در انیجا پوشش میدم.
قبل از هر چیزی ابتدا جدول را ایجاد و 12 سطر زیر را در آن انتشار دهید:
روش اول:
این روش، تعمیم پذیری و پویایی ندارد و برای هر سناریویی مناسب نخواهد بود. ولی از آنجایی که دیدم کوئری زیر میتواند یک نمونه از کاربرد Ntile باشه آن را مطرح کردم.
تابع ntile داخلی سطرهای جدول را به دو قسم تقسیم میکند و برای قسم اول عدد 1 و برای قسم دوم عدد 2 را در نظر میگیرد.
تابع ntile بیرونی بر اساس دو عدد 1و 2 گروه بندی انجام داده و هر گروه را به 3 قسمت تقسیم میکند. قسمت اول 1، دوم 2 و سوم 3 خواهد بود.
لازم به ذکر است که باید خارج قسمت تقسیم تعدادسطرها بر عدد ntile یک عدد صحیح باشد تا خروجی مناسب داشته باشیم. و همچنین بایستی بدانیم که تعداد سطرهای جدول چنتاست تا آن را به گونه ای تقسیم کنیم که خارج قسمت برابر شود با عدد مورد نظر ما یعنی 6.
روش دوم:
در این روش بر خلاف روش قبل که همه چیز توسط تابع بدست میآمد باید خودمان دست به کار شویم و فرمولی را بدست آوریم که نتیجه مورد نظر را تولید کند.
برای حل این مساله ابتدا باید سطرهای جدول را 6 تا 6 تا سوا کنیم و عناصر هر دسته را شماره گذاری کنیم (از 1 تا 6 بر اساس ترتیب مقدار nbr) سپس با کمک سایر فرمولها دستهها را دوتا دوتا شماره گذاری میکنیم.
به تصویر زیر توجه بفرمایید:
در کادر نارنجی رنگ همانطور که اشاره شد ما سطرهای شمارگذاری شده ای داریم که در رنج 1 تا 6 هستند. و در کادر بنفش ستون مورد نظر ما قرار دارد. ستون بنفش با کمک ستون نارنجی بدست آمده است.
اگر تقسیم صحیح را div و باقیمانده صحیح را mod بگیریم فرولهای مورد نظر به این شرح خواهد بود:
طبق کوئری زیر ستون نارنجی(rnk1/rnk2) را به دو طریق میتوان ایجاد نمود و ستون بنفش(grp1/grp2) را نیز به دو طریق میتوان ایجاد نمود.
به تصویر زیر توجه بفرمایید. ابتدا دادهها به دو دسته ششتایی تقسیم شدن(ستون ntl)، سپس هر کدام از این دستهها نیز به سه دسته دوتایی تقسیم شدن(ستون grp) هدف ما تولید دو ستون ntl و grp توسط query میباشد.
برای بدست آوردن مقادیر دو ستون مذکور روشهای متنوعی وجود دارد که برخی از آنها را در انیجا پوشش میدم.
قبل از هر چیزی ابتدا جدول را ایجاد و 12 سطر زیر را در آن انتشار دهید:
CREATE TABLE T (nbr INT NOT NULL); INSERT T VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12);
روش اول:
این روش، تعمیم پذیری و پویایی ندارد و برای هر سناریویی مناسب نخواهد بود. ولی از آنجایی که دیدم کوئری زیر میتواند یک نمونه از کاربرد Ntile باشه آن را مطرح کردم.
SELECT nbr, ntl, NTILE(3) OVER(PARTITION BY ntl ORDER BY nbr) AS grp FROM ( SELECT nbr, NTILE(2) OVER(ORDER BY nbr) ntl FROM T ) AS D;
تابع ntile بیرونی بر اساس دو عدد 1و 2 گروه بندی انجام داده و هر گروه را به 3 قسمت تقسیم میکند. قسمت اول 1، دوم 2 و سوم 3 خواهد بود.
لازم به ذکر است که باید خارج قسمت تقسیم تعدادسطرها بر عدد ntile یک عدد صحیح باشد تا خروجی مناسب داشته باشیم. و همچنین بایستی بدانیم که تعداد سطرهای جدول چنتاست تا آن را به گونه ای تقسیم کنیم که خارج قسمت برابر شود با عدد مورد نظر ما یعنی 6.
روش دوم:
در این روش بر خلاف روش قبل که همه چیز توسط تابع بدست میآمد باید خودمان دست به کار شویم و فرمولی را بدست آوریم که نتیجه مورد نظر را تولید کند.
برای حل این مساله ابتدا باید سطرهای جدول را 6 تا 6 تا سوا کنیم و عناصر هر دسته را شماره گذاری کنیم (از 1 تا 6 بر اساس ترتیب مقدار nbr) سپس با کمک سایر فرمولها دستهها را دوتا دوتا شماره گذاری میکنیم.
به تصویر زیر توجه بفرمایید:
در کادر نارنجی رنگ همانطور که اشاره شد ما سطرهای شمارگذاری شده ای داریم که در رنج 1 تا 6 هستند. و در کادر بنفش ستون مورد نظر ما قرار دارد. ستون بنفش با کمک ستون نارنجی بدست آمده است.
اگر تقسیم صحیح را div و باقیمانده صحیح را mod بگیریم فرولهای مورد نظر به این شرح خواهد بود:
طبق کوئری زیر ستون نارنجی(rnk1/rnk2) را به دو طریق میتوان ایجاد نمود و ستون بنفش(grp1/grp2) را نیز به دو طریق میتوان ایجاد نمود.
SELECT nbr, rnk1, rnk2, (rnk1 + 1) / 2 AS grp2, (rnk1 - 1) / 2 + 1 AS grp2 FROM ( SELECT nbr, ROW_NUMBER() OVER(PARTITION BY (nbr + 5) / 6 ORDER BY nbr) rnk1, (nbr - 1) % 6 + 1 AS rnk2 FROM t )d
نظرات مطالب
EF Code First #12
خیر. هستند یک سری الگوی مخزن عمومی به این شکل که در قسمت 11 سری EF نقد شدند و دارای مشکلات زیادی بوده که نیازی به تکرار آن در اینجا نیست. به علاوه دنیای واقعی با چند مورد متد ساده عمومی مدل نمیشود. عموما جمع چند عملیات هست که در قالب یک متد مشخص، خروجی یک سرویس را تشکیل میدهد. این عملیات هم میتواند مرتبط به چندین موجودیت باشد در آن واحد. تمام این موارد باید به صورت بسته بندی شده در قالب یک متد در اختیار لایههای دیگر قرار گیرد.