دیتابیس، schema، role و search_path؛ encoding و collation فارسی

ساختن دیتابیس به روش درست

بیشتر دردسرهای فارسی از همان لحظه‌ی CREATE DATABASE شروع می‌شود. encoding باید UTF8 باشد و collation (قاعده‌ی مقایسه و مرتب‌سازی) آگاهانه انتخاب شود؛ هر دو بعداً قابل تغییر نیستند مگر با dump و restore.

CREATE ROLE carpet_owner LOGIN PASSWORD 'change-me';

CREATE DATABASE carpet
  OWNER carpet_owner
  ENCODING 'UTF8'
  LOCALE_PROVIDER icu
  ICU_LOCALE 'fa-IR'
  TEMPLATE template0;

\c carpet
CREATE SCHEMA app AUTHORIZATION carpet_owner;
ALTER ROLE carpet_owner IN DATABASE carpet SET search_path = app, public;

TEMPLATE template0 لازم است چون template1 ممکن است encoding یا locale دیگری داشته باشد. هر دیتابیس جدید در واقع کپی template1 است؛ هرچه (اکستنشن، جدول) در template1 بگذارید، در دیتابیس‌های بعدی هم خواهد بود.

collation: libc، ICU و builtin

با collation نوع C، حروف بر اساس کد یونیکد مرتب می‌شوند؛ «پ»، «چ»، «ژ» و «گ» که کدشان بعد از حروف عربی است، بعد از «و» می‌نشینند و «پرویز» بعد از «وحید» می‌آید. با ICU و fa-IR ترتیب الفبای فارسی رعایت می‌شود. می‌توانید دیتابیس را C نگه دارید و فقط جایی که لازم است collation بدهید:

SELECT name FROM customers ORDER BY name COLLATE "fa-IR-x-icu";
CREATE INDEX ON customers (name COLLATE "fa-IR-x-icu");

مسئله‌ی «ی» و «ک»

«ي» عربی (U+064A) و «ی» فارسی (U+06CC) و همین‌طور «ك» و «ک» از نظر دیتابیس دو کاراکتر متفاوت‌اند؛ هیچ collationی مشکل را کامل حل نمی‌کند. راه درست، یکسان‌سازی در ورود داده است (در برنامه یا با trigger) و یک CHECK برای اطمینان:

ALTER TABLE customers
  ADD CONSTRAINT name_persian_chars CHECK (name !~ '[يك]');
UPDATE customers SET name = translate(name, 'يك', 'یک') WHERE name ~ '[يك]';

schema و search_path

schema فضای نام است: app.orders و report.orders دو جدول مجزایند. وقتی نام را بدون schema می‌نویسید، پستگرس آن را در schemaهای search_path به ترتیب جست‌وجو می‌کند. پیش‌فرض "$user", public است؛ یعنی اگر schemaی هم‌نام کاربر وجود داشته باشد، اول آن‌جا را می‌گردد.

نکته‌هایی که کمتر کسی می‌داند

• از نسخه‌ی 15 کاربران عادی دیگر حق CREATE در schemaی public را ندارند؛ خطای permission denied for schema public پس از ارتقا از همین است. راه تمیز: یک schemaی اختصاصی برای برنامه بسازید.
• بعد از ارتقای سیستم‌عامل با تغییر نسخه‌ی glibc (معروف‌ترینش glibc 2.28 در دبیان 10) ایندکس‌های متنی با collation libc باید REINDEX شوند؛ نمای pg_database ستون datcollversion دارد و پستگرس هنگام عدم تطابق هشدار می‌دهد.
• collation غیرقطعی (deterministic = false) می‌تواند مقایسه‌ی بدون حساسیت به حروف بزرگ و کوچک بسازد، اما LIKE روی آن تا نسخه‌ی 18 اصلاً پشتیبانی نمی‌شد و ایندکس B-tree آن برای جست‌وجوی پیشوندی به کار نمی‌آید؛ با احتیاط استفاده کنید.
• SET search_path در یک تابع SECURITY DEFINER حیاتی است؛ بدون آن کاربر می‌تواند با ساختن تابع هم‌نام در schemaی خودش کد شما را ربوده و با مجوز صاحب تابع اجرا کند.
• ICU_LOCALE با پسوندها قابل تنظیم است؛ مثلاً fa-IR-u-kn-true اعداد داخل رشته را عددی مرتب می‌کند تا «فرش 9» قبل از «فرش 10» بیاید.