ایجاد کاتالوگهای Full text search و ایندکسهای آن
همانطور که در قسمت قبل نیز عنوان شد، فیلترهای FTS آفیس، علاوه بر اینکه امکان جستجوی پیشرفته FTS را بر روی کلیه فایلهای مجموعه آفیس میسر میکنند، امکان جستجوی FTS را بر روی خواص ویژه اضافی آنها، مانند نام نویسنده، واژههای کلیدی، تاریخ ایجاد و امثال آن نیز به همراه دارند.
اینکه چه خاصیتی را بتوان جستجو کرد نیز بستگی به نوع فیلتر نصب شده دارد. برای تعریف خواص قابل جستجوی یک سند، باید یک SEARCH PROPERTY LIST را ایجاد کرد:
CREATE SEARCH PROPERTY LIST WordSearchPropertyList; GO ALTER SEARCH PROPERTY LIST WordSearchPropertyList ADD 'Authors' WITH (PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 4, PROPERTY_DESCRIPTION = 'System.Authors - authors of a given item.'); GO
بهبود کیفیت جستجو توسط Stop lists و Stop words
به یک سری از کلمات و حروف، اصطلاحا noise words گفته میشود. برای مثال در زبان انگلیسی حروف و کلماتی مانند a، is، the و and به صورت خودکار از FTS حذف میشوند؛ چون جستجوی آنها بیحاصل است. به اینها stop words نیز میگویند.
با استفاده از کوئری ذیل میتوان لیست stop words تعریف شده در بانک اطلاعاتی جاری را مشاهده کرد:
-- Check the Stopwords list SELECT w.stoplist_id, l.name, w.stopword, w.language FROM sys.fulltext_stopwords AS w INNER JOIN sys.fulltext_stoplists AS l ON w.stoplist_id = l.stoplist_id;
-- Stopwords list CREATE FULLTEXT STOPLIST SQLStopList; GO -- Add a stopword ALTER FULLTEXT STOPLIST SQLStopList ADD 'SQL' LANGUAGE 'English'; GO
کاتالوگهای Full Text Search
ایندکسهای ویژهی FTS، در مکانهایی به نام Full Text Catalogs ذخیره میشوند. این کاتالوگها صرفا یک شیء مجازی بوده و تنها برای تعریف ظرفی دربرگیرندهی ایندکسهای FTS تعریف میشوند. در نگارشهای پیش از 2012 اس کیوال سرور، این کاتالوگها اشیایی فیزیکی بودند؛ اما اکنون تبدیل به اشیایی مجازی شدهاند.
حالت کلی تعریف یک fulltext catalog به نحو ذیل است:
create fulltext catalog catalog_name on filegroup filegroup_name in path 'rootpath' with some_options as default authoriztion owner_name accent_sensivity = {on|off}
به صورت پیش فرض حساسیت به لهجه یا accent_sensivity خاموش است. اگر روشن شود، باید کل ایندکس مجددا بازسازی شود.
ایجاد ایندکسهای Full Text
پس از ایجاد یک fulltext catalog، اکنون نوبت به تعریف ایندکسهایی فیزیکی هستند که داخل این کاتالوگها ذخیره خواهند شد:
-- Full-text catalog CREATE FULLTEXT CATALOG DocumentsFtCatalog; GO -- Full-text index CREATE FULLTEXT INDEX ON dbo.Documents ( docexcerpt Language 1033, doccontent TYPE COLUMN doctype Language 1033 STATISTICAL_SEMANTICS ) KEY INDEX PK_Documents ON DocumentsFtCatalog WITH STOPLIST = SQLStopList, SEARCH PROPERTY LIST = WordSearchPropertyList, CHANGE_TRACKING AUTO; GO
CHANGE_TRACKING AUTO به این معنا است که SQL Server به صورت خودکار کار به روز رسانی این ایندکس را با تغییرات رکوردها انجام خواهد داد.
ذکر STATISTICAL_SEMANTICS، منحصر به SQL Server 2012 بوده و کار آن تشخیص واژههای کلیدی و ایجاد ایندکسهای یافتن اسناد مشابه است. برای استفاده از آن حتما نیاز است مطابق توضیحات قسمت قبل، Semantic Language Database پیشتر نصب شده باشد.
توسط STOPLIST، لیست واژههایی که قرار نیست ایندکس شوند را معرفی خواهیم کرد. SQLStopList را در ابتدای بحث ایجاد کردیم.
Language 1033 به معنای استفاده از زبان US English است.
نحوهی استفاده از SEARCH PROPERTY LIST ایی که پیشتر تعریف کردیم را نیز در اینجا ملاحظه میکنید.
مثالی برای ایجاد ایندکسهای FTS
برای اینکه ربط منطقی نکات عنوان شده را بهتر بتوانید بررسی و آزمایش کنید، مثال ذیل را درنظر بگیرید.
ابتدا جدول Documents را برای ذخیره سازی تعدادی سند، ایجاد میکنیم:
CREATE TABLE dbo.Documents ( id INT IDENTITY(1,1) NOT NULL, title NVARCHAR(100) NOT NULL, doctype NCHAR(4) NOT NULL, docexcerpt NVARCHAR(1000) NOT NULL, doccontent VARBINARY(MAX) NOT NULL, CONSTRAINT PK_Documents PRIMARY KEY CLUSTERED(id) );
سپس اطلاعاتی را در این جدول ثبت میکنیم:
-- Insert data -- First row INSERT INTO dbo.Documents (title, doctype, docexcerpt, doccontent) SELECT N'Columnstore Indices and Batch Processing', N'docx', N'You should use a columnstore index on your fact tables, putting all columns of a fact table in a columnstore index. In addition to fact tables, very large dimensions could benefit from columnstore indices as well. Do not use columnstore indices for small dimensions. ', bulkcolumn FROM OPENROWSET (BULK 'C:\Users\Vahid\Desktop\Updates\fts_docs\ColumnstoreIndicesAndBatchProcessing.docx', SINGLE_BLOB) AS doc; -- Second row INSERT INTO dbo.Documents (title, doctype, docexcerpt, doccontent) SELECT N'Introduction to Data Mining', N'docx', N'Using Data Mining is becoming more a necessity for every company and not an advantage of some rare companies anymore. ', bulkcolumn FROM OPENROWSET (BULK 'C:\Users\Vahid\Desktop\Updates\fts_docs\IntroductionToDataMining.docx', SINGLE_BLOB) AS doc; -- Third row INSERT INTO dbo.Documents (title, doctype, docexcerpt, doccontent) SELECT N'Why Is Bleeding Edge a Different Conference', N'docx', N'During high level presentations attendees encounter many questions. For the third year, we are continuing with the breakfast Q&A session. It is very popular, and for two years now, we could not accommodate enough time for all questions and discussions! ', bulkcolumn FROM OPENROWSET (BULK 'C:\Users\Vahid\Desktop\Updates\fts_docs\WhyIsBleedingEdgeADifferentConference.docx', SINGLE_BLOB) AS doc; -- Fourth row INSERT INTO dbo.Documents (title, doctype, docexcerpt, doccontent) SELECT N'Additivity of Measures', N'docx', N'Additivity of measures is not exactly a data warehouse design problem. However, you have to realize which aggregate functions you will use in reports for which measure, and which aggregate functions you will use when aggregating over which dimension.', bulkcolumn FROM OPENROWSET (BULK 'C:\Users\Vahid\Desktop\Updates\fts_docs\AdditivityOfMeasures.docx', SINGLE_BLOB) AS doc; GO
fts_docs.zip
در ادامه میخواهیم قادر باشیم تا بر روی متادیتای نویسندهی این اسناد نیز جستجوی کامل FTS را انجام دهیم. به همین جهت SEARCH PROPERTY LIST آنرا نیز ایجاد خواهیم کرد:
-- Search property list CREATE SEARCH PROPERTY LIST WordSearchPropertyList; GO ALTER SEARCH PROPERTY LIST WordSearchPropertyList ADD 'Authors' WITH (PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 4, PROPERTY_DESCRIPTION = 'System.Authors - authors of a given item.'); GO
-- Stopwords list CREATE FULLTEXT STOPLIST SQLStopList; GO -- Add a stopword ALTER FULLTEXT STOPLIST SQLStopList ADD 'SQL' LANGUAGE 'English'; GO
اکنون زمان ایجاد یک کاتالوگ FTS است:
-- Full-text catalog CREATE FULLTEXT CATALOG DocumentsFtCatalog; GO
و در آخر ایندکس FTS ایی را که پیشتر در مورد آن بحث کردیم، ایجاد خواهیم کرد:
-- Full-text index CREATE FULLTEXT INDEX ON dbo.Documents ( docexcerpt Language 1033, doccontent TYPE COLUMN doctype Language 1033 STATISTICAL_SEMANTICS ) KEY INDEX PK_Documents ON DocumentsFtCatalog WITH STOPLIST = SQLStopList, SEARCH PROPERTY LIST = WordSearchPropertyList, CHANGE_TRACKING AUTO; GO
در این تصویر محل یافتن اجزای مختلف Full text search را در management studio مشاهده میکنید.
یک نکتهی تکمیلی
برای زبان فارسی نیز یک سری stop words وجود دارند. لیست آنها را از اینجا میتوانید دریافت کنید:
stopwords.sql
متاسفانه زبان فارسی جزو زبانهای پشتیبانی شده توسط FTS در SQL Server نیست (نه به این معنا که نمیتوان با آن کار کرد؛ به این معنا که برای مثال دستورات صرفی زبان را ندارد) و به همین جهت از زبان انگلیسی در اینجا استفاده شدهاست.
تهیه کوئری بر روی ایندکسهای Full Text Search
SELECT * FROM News LEFT OUTER JOIN CONTAINSTABLE(news, title, N'ISABOUT("آموزش or دیجیتال" weight(0.9))') AS CT_title ON News.ID = CT_title.[KEY]
در مطالب قبلی به اختصار در مورد dynamic management views که از SQL server 2005 به بعد ارائه شدهاند مثالهایی کاربردی ارائه گشتند. یکی دیگر از قابلیتهای فوق العاده مهم این DMV ها، پیشنهاد ایجاد ایندکس بر روی جداول است. این پیشنهادات بر اساس آمارهای جمع آوری شده توسط موتور بهینه ساز اجرای کوئریها در اس کیوال سرور به شما ارائه خواهند شد. برای مثال کوئری زیر را در management studio اجر نمائید:
USE master;
SELECT d.database_id,
d.object_id,
d.index_handle,
d.equality_columns,
d.inequality_columns,
d.included_columns,
d.statement AS fully_qualified_object,
gs.*
FROM sys.dm_db_missing_index_groups g
JOIN sys.dm_db_missing_index_group_stats gs
ON gs.group_handle = g.index_group_handle
JOIN sys.dm_db_missing_index_details d
ON g.index_handle = d.index_handle
خروجی حاصل لیستی است که بر اساس تفاسیر موتور بهینه ساز اجرای کوئریها بدست آمده است. equality_columns بر اساس حالتهایی مانند table.column = constant_value پیش بینی شدهاست. inequality_columns بر اساس حالتهایی مانند table.column > constant_value و included_columns برای حالتهایی است که میخواهیم ایندکس ایجاد شده محدودیت اندازه 900 بایت را نداشته باشد، یا نوع دادهای مورد استفاده برای مثال nvrachar max و امثال آن باشد (text و ntext مجاز نیست) و مواردی از این دست.
fully_qualified_object هم مشخص میکند که این ایندکس دقیقا باید بر روی چه دیتابیس و جدولی ایجاد شود.
تذکر: این آمارهای جمعآوری شده پس از هر بار ریاستارت سرور، صفر خواهند شد.
اکنون این سؤال مطرح میشود که چگونه از این اطلاعات استفاده کنیم؟
دقیقا بر اساس EQUALITY_COLUMNS ، INEQUALITY_COLUMNS و INCLUDED_COLUMNS گزارش فوق، میتوان به صورت زیر عمل کرد:
CREATE NONCLUSTERED INDEX <unique index name>
ON <FULL_TABLE_NAME> (<EQUALITY_COLUMNS>,<INEQUALITY_COLUMNS>) -- exclude INEQUALITY_COLUMNS if NULL
INCLUDE (<INCLUDED_COLUMNS>); -- exclude INCLUDED_COLUMNS if NULL
SELECT mig.index_group_handle,
mid.index_handle,
migs.avg_total_user_cost AS AvgTotalUserCostThatCouldbeReduced,
migs.avg_user_impact AS AvgPercentageBenefit,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle)
+ '_' + CONVERT (varchar, mid.index_handle)
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE
WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns
IS NOT NULL THEN ','
ELSE ''
END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS
create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
مزایای ایجاد ایندکسهای صحیح بر اساس نیازهای واقعی کاری:
- سریعتر شدن اجرای کوئریهای جستجو در تعداد رکوردهای بالا
- مرتب سازی سریعتر نتایج (sorting)
- کوئریهایی که بر اساس عبارت GROUP BY ایجاد شدهاند، سریعتر اجرا خواهند شد
An extremely simple and responsive jQuery plugin that allows you to
create multi-level drop down menu with subtle effects (sliding, fading,
scale up, or scale down). Each sub-menu in this menu will be shown in
its own context, making the “parent” level disappear, good for saving
space for menus which have a lot of content
سری کار با Web API
My road for the series is as follows,
RESTful Day #1: Enterprise level application architecture with Web APIs using Entity Framework, Generic Repository pattern and Unit of Work.
RESTful Day #2: Inversion of control using dependency injection in Web APIs using Unity Container and Bootstrapper.
RESTful Day #3: Resolve dependency of dependencies using Inversion of Control and dependency injection in Asp.net Web APIs with Unity Container and Managed Extensibility Framework (MEF).
RESTful Day #4: Custom URL Re-Writing/Routing using Attribute Routes in MVC 4 Web APIs.
RESTful Day #5: Basic Authentication and Token based custom Authorization in Web APIs using Action Filters.
RESTful Day #6: Request logging and Exception handing/logging in Web APIs using Action Filters, Exception Filters and nLog.
RESTful Day #7: Unit testing ASP.NET Web APIs controllers using nUnit.
RESTful Day #8: Extending OData support in ASP.NET Web APIs.
<script type="text/ng-template" id="menu"> {{item.caption}} -- <b>{{level}}</b> <ul ng-if="item.child.length > 0"> <li id="{{item.caption}}" ng-finish-render="doWork()" ng-repeat="item in item.child" ng-include="'menu'" ng-init="level = level + 1"></li> </ul> </script>
<div class="well"> <ul> <li id="{{item.caption}}" ng-repeat="item in data" ng-include="'menu'" ng-init="level = level + 1" ng-finish-render="doWork()"></li> </ul> </div>
$scope.doWork = function(){ console.log($('#item2_1')); } $scope.level = 0; $scope.data = [ { caption:'root', child:[ { caption:'item1', child:[{ caption:'item1_1', child:[{ caption:'item1_1_1' }] },{ caption:'item1_1' }] }, { caption:'item2', child:[{ caption:'item2_1', child:[{ caption:'item2_1_1' }] }] } ] } ];
update products set Name = "Test" Where Id = 1
update products with (nolock,updlock) set Name = "Test" where Id = 1
public class UpdateRowLockHintDbCommandInterceptor : IDbCommandInterceptor { public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext) { if (command.CommandType != CommandType.Text) return; // (1) if (!(command is SqlCommand)) return; // (2) SqlCommand sqlCommand = (SqlCommand)command; String commandText = sqlCommand.CommandText; String updateCommandRegularExpression = "(update) "; Boolean isUpdateCommand = Regex.IsMatch(commandText, updateCommandRegularExpression, RegexOptions.IgnoreCase | RegexOptions.Multiline); // You may use better regular expression pattern here. if (isUpdateCommand) { Boolean isSnapshotIsolationTransaction = sqlCommand.Transaction != null && sqlCommand.Transaction.IsolationLevel == IsolationLevel.Snapshot; String tableHintToAdd = isSnapshotIsolationTransaction ? " with (rowlock , updlock) set " : " with (rowlock) set "; commandText = Regex.Replace(commandText, "^(set) ", (match) => { return tableHintToAdd; }, RegexOptions.IgnoreCase | RegexOptions.Multiline); command.CommandText = commandText; } }
یافتن تداخلات Collations در SQL Server
DECLARE @defaultCollation NVARCHAR(1000) SET @defaultCollation = CAST( DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS NVARCHAR(1000) ) select 'ALTER TABLE ' + QUOTENAME(C.TABLE_SCHEMA) +'.'+ QUOTENAME(C.Table_Name) +' ALTER COLUMN ' +' [' +Column_Name+'] ' + DATA_TYPE+'('+cast(character_maximum_length as varchar(10))+')' +' COLLATE Persian_100_CI_AS ' +(case IS_NULLABLE when 'YES' then 'NULL' else 'NOT NULL' end )+';' FROM Information_Schema.Columns C INNER JOIN Information_Schema.Tables T ON C.Table_Name = T.Table_Name WHERE T.Table_Type = 'Base Table' AND RTRIM(LTRIM(Collation_Name)) <> RTRIM(LTRIM(@defaultCollation)) --AND DATA_TYPE='nvarchar' AND character_maximum_length>0 ORDER BY C.Table_Name, C.Column_Name