مرتب سازی صحیح حروف فارسی در بانک اطلاعاتی SQLite

اندازه‌ی قلم متن

تخمین مدت زمان مطالعه‌ی مطلب: سه دقیقه

فرض کنید لیست حروف الفبای فارسی را در یک بانک اطلاعاتی SQLite ذخیره کرده‌اید:

var connection = new SqliteConnection("Data Source=:memory:");
connection.Open();

var createCommand = connection.CreateCommand();
createCommand.CommandText =
            @"
                CREATE TABLE persian_letter (
                    value TEXT
                );

                INSERT INTO persian_letter
                VALUES ('ا'),('ب'),('پ'),('ت'),('ث'),('ج'),('چ'),('ح'),('خ'),('د'),('ذ'),('ر'),('ز'),('ژ'),('س'),('ش'),
                       ('ص'),('ض'),('ط'),('ظ'),('ع'),('غ'),('ف'),('ق'),('ک'),('گ'),('ل'),('م'),('ن'),('و'),('ه'),('ی');
            ";
createCommand.ExecuteNonQuery();

اگر از این لیست کوئری گرفته و آن‌ها‌را مرتب کنیم:

var queryCommand = connection.CreateCommand();
queryCommand.CommandText =
            @"
                SELECT value
                FROM persian_letter
                order by value
            ";
var reader = queryCommand.ExecuteReader();
var sortedDbItems = new List<string>();
while (reader.Read())
{
    sortedDbItems.Add($"{reader["value"]}");
}

یک چنین خروجی حاصل می‌شود:

همانطور که ملاحظه می‌کنید، مرتب سازی حروف فارسی در اینجا به صورت پیش‌فرض کار نمی‌کند. علت اینجا است که روش پیش‌فرض مرتب سازی حروف در SQLite، بر اساس کد اسکی حروف است و فقط در مورد حروف ASCII از A تا Z درست کار می‌کند.

امکان تعریف Collation سفارشی در SQLite

در بانک‌های اطلاعاتی، قابلیتی که مستقیما بر روی نحوه‌ی جستجو و همچنین مرتب سازی حروف تاثیر می‌گذارد، Collation نام دارد و در SQLite برخلاف بسیاری از بانک‌های اطلاعاتی دیگر، امکان تعریف Collation سفارشی نیز وجود دارد و برای این منظور باید یک function pointer را در اختیار آن قرار داد تا از آن در سمت بانک اطلاعاتی جهت مرتب سازی و جستجوی حروف استفاده کند.
خوشبختانه پروژه‌ی Microsoft.Data.Sqlite امکان تبدیل یک managed delegate دات نتی را به یک function pointer مخصوص SQLite، میسر می‌کند. به عبارتی SQLite کدهای دات نتی را در حین انجام محاسبات خود اجرا خواهد کرد و این اجرا به صورتی نیست که ابتدا کل اطلاعات، به سمت برنامه‌ی کلاینت منتقل شود و سپس در این سمت، در حافظه، عملیاتی بر روی آن صورت گیرد. کل عملیات در سمت بانک اطلاعاتی مدیریت می‌شود.
روش تعریف یک Collation جدید هم در اینجا بسیار ساده‌است:

connection.CreateCollation("PersianCollationNoCase", (x, y) => string.Compare(x, y, ignoreCase: true));

فقط کافی است بر روی اتصال باز شده، متد CreateCollation فراخوانی و نحوه‌ی مقایسه‌ی دو رشته مشخص شود. سپس این Collation نامدار، به صورت زیر در کوئری‌ها قابل استفاده خواهد بود:

SELECT value
FROM persian_letter
order by value COLLATE PersianCollationNoCase

اینبار اگر خروجی برنامه را بررسی کنیم، مشاهده خواهیم کرد که مرتب سازی حروف فارسی در SQLite به درستی کار می‌کند:

تعریف Collation سفارشی غیرحساس به «ی و ک» !

این مورد شاید یکی از آرزوهای توسعه دهندگان SQL Server باشد! اما با SQLite به سادگی زیر قابل تعریف و مدیریت است:

connection.CreateCollation("PersianCollationNoCaseYekeInsensitive",
(x, y) => string.Compare(x.ApplyCorrectYeKe(), y.ApplyCorrectYeKe(), ignoreCase: true));

متد ApplyCorrectYeKe فوق از بسته‌ی نیوگت DNTPersianUtils.Core دریافت شده و کار آن یک‌دست کردن «ی و ک» فارسی و عربی است.
در یک چنین حالتی اگر اطلاعاتی را به همراه «ی و ک» فارسی و یا عربی ثبت کنیم:

CREATE TABLE persian_letter (
value TEXT
);
INSERT INTO persian_letter
VALUES ('ی'),('ک');

جستجوی بر روی آن‌ها دیگر وابسته‌ی به مقدار «ی و ک» وارد شده نبوده و چه «ی و ک» فارسی وارد شود و چه عربی، این کوئری همواره کار می‌کند:

SELECT count()
FROM persian_letter
WHERE value = 'ى' COLLATE PersianCollationNoCaseYekeInsensitive

کدهای کامل این مثال را از اینجا می‌توانید دریافت کنید: SQLitePersianCollation.zip

مرتب سازی صحیح حروف فارسی در بانک اطلاعاتی SQLite

مطالب مشابه