PostgreSQL: محرك البحث النصي الكامل في PostgreSQL

SELECT * FROM ts_debug('english', 'The red shoes are beautiful');

 alias | descriptor | token  |  dictionaries  | dictionary | lexemes 
-------+------------+--------+----------------+------------+---------
 ascii | word       | The    | {english_stem}  | english_stem| {}
 ascii | word       | red    | {english_stem}  | english_stem| {red}
 ascii | word       | shoes  | {english_stem}  | english_stem| {shoe}
 ascii | word       | are    | {english_stem}  | english_stem| {}
 ascii | word       | beautiful | {english_stem}| english_stem| {beauti}

SELECT to_tsvector('english', 'The red shoes are beautiful shoes');

'beauti':5 'red':2 'shoe':3,6

SELECT to_tsquery('english', 'red & shoe');
-- 'red' & 'shoe'

SELECT to_tsquery('english', 'red | blue');
-- 'red' | 'blue'

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT plainto_tsquery('english', 'red shoes');
-- 'red' & 'shoe'  (يطابق المستندات التي تحتوي على كلا المصطلحين)

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT phraseto_tsquery('english', 'red shoes');
-- 'red' <-> 'shoe'  (يطابق المستندات حيث 'red' قبل 'shoe' مباشرة)

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT websearch_to_tsquery('english', '"red shoes" -broken OR new');
-- 'red' <-> 'shoe' & !'broken' | 'new'

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE TABLE catalog (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  search_vector tsvector
);

INSERT INTO catalog (name, description, search_vector) VALUES
  ('Red Running Shoes', 'Lightweight red shoes for running', 
   to_tsvector('english', 'Lightweight red shoes for running')),
  ('Blue Casual Shoes', 'Comfortable blue shoes for daily wear',
   to_tsvector('english', 'Comfortable blue shoes for daily wear')),
  ('Red Dress', 'Elegant red dress for special occasions',
   to_tsvector('english', 'Elegant red dress for special occasions'));

-- البحث عن "red shoes"
SELECT name, description
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

INSERT 0 1

SELECT name,
       ts_rank(search_vector, plainto_tsquery('english', 'red shoes')) AS rank
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes')
ORDER BY rank DESC;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

ALTER TABLE catalog ADD COLUMN title_vector tsvector;

UPDATE catalog SET title_vector = to_tsvector('english', name);

-- تعيين أوزان: title=D(افتراضي), body=A(الأعلى)
SELECT name,
       ts_rank(
         setweight(title_vector, 'A') || setweight(search_vector, 'B'),
         plainto_tsquery('english', 'red shoes')
       ) AS rank
FROM catalog
WHERE setweight(title_vector, 'A') || setweight(search_vector, 'B')
      @@ plainto_tsquery('english', 'red shoes')
ORDER BY rank DESC;

UPDATE 3

SELECT name,
       ts_headline('english', description, plainto_tsquery('english', 'red shoes')) AS highlighted
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

 name              | highlighted
-------------------+-------------------------------------------------
 Red Running Shoes | Lightweight <b>red</b> <b>shoes</b> for running

SELECT ts_headline(
  'english',
  description,
  plainto_tsquery('english', 'red shoes'),
  'StartSel=<em>,StopSel=</em>,MaxWords=10,MinWords=5'
) AS highlighted
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE INDEX idx_catalog_desc_fts
ON catalog USING gin (to_tsvector('english', description));

-- يجب أن يستخدم الاستعلام نفس التكوين
SELECT name FROM catalog
WHERE to_tsvector('english', description) @@ plainto_tsquery('english', 'red shoes');

CREATE TABLE

CREATE INDEX idx_catalog_search_fts ON catalog USING gin (search_vector);

-- الاستعلام يستخدم العمود المخزن
SELECT name FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

CREATE TABLE

CREATE FUNCTION catalog_search_vector_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_catalog_search
BEFORE INSERT OR UPDATE OF name, description ON catalog
FOR EACH ROW EXECUTE FUNCTION catalog_search_vector_update();

INSERT 0 1

SELECT to_tsvector('simple', 'The red shoes');
-- 'red':2 'shoes':3 'the':1  (يحتفظ بـ 'the'، لا تجذيع)

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- تجزئة النص الصيني مسبقًا بمسافات في طبقة التطبيق
INSERT INTO catalog (name, description, search_vector)
VALUES (
  'Red Running Shoes',
  'light red running shoes for jogging',
  to_tsvector('simple', 'light red running shoes for jogging')
);

-- بحث باستعلام مجزأ
SELECT name FROM catalog
WHERE search_vector @@ to_tsquery('simple', 'red & shoes');

INSERT 0 1

CREATE TABLE articles (
  article_id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT,
  fts_en tsvector,
  fts_zh tsvector
);

-- تكوين english للمحتوى الإنجليزي، simple للصيني
UPDATE articles SET
  fts_en = to_tsvector('english', COALESCE(title, '') || ' ' || COALESCE(content, '')),
  fts_zh = to_tsvector('simple', COALESCE(title, '') || ' ' || COALESCE(content, ''));

-- بحث بالإنجليزية
SELECT title FROM articles
WHERE fts_en @@ plainto_tsquery('english', 'database optimization');

-- بحث بالصينية (مجزأة في التطبيق)
SELECT title FROM articles
WHERE fts_zh @@ to_tsquery('simple', 'data & optimization');

UPDATE 3

SELECT word, ndoc, nentry
FROM ts_stat(
  'SELECT search_vector FROM catalog'
)
ORDER BY nentry DESC
LIMIT 10;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT cfgname FROM pg_ts_config;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- إنشاء بيانات اختبار
INSERT INTO catalog (name, description, search_vector)
SELECT
  'Product ' || i,
  'High quality product number ' || i || ' with great features and amazing design',
  to_tsvector('english', 'High quality product number ' || i || ' with great features and amazing design')
FROM generate_series(1, 100000) AS i;

-- استعلام LIKE (بطيء، لا دعم فهرس لحرف البدل البادئ)
EXPLAIN ANALYZE
SELECT name FROM catalog WHERE description LIKE '%great features%';

-- بحث نصي كامل مع فهرس GIN (سريع)
CREATE INDEX idx_catalog_fts ON catalog USING gin (search_vector);
EXPLAIN ANALYZE
SELECT name FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'great features');

INSERT 0 1

-- الخطوة 1: إنشاء جدول منتجات مع tsvector محسوب مسبقًا
CREATE TABLE shop_products (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  description TEXT,
  price NUMERIC(10,2) NOT NULL,
  search_doc tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', COALESCE(name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(category, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(description, '')), 'C')
  ) STORED
);

-- الخطوة 2: إدراج بيانات نموذجية
INSERT INTO shop_products (name, category, description, price) VALUES
  ('Red Running Shoes', 'Shoes', 'Lightweight red running shoes with cushioned sole', 89.99),
  ('Blue Casual Shoes', 'Shoes', 'Comfortable blue shoes for everyday casual wear', 59.99),
  ('Red Leather Dress Shoes', 'Shoes', 'Premium red leather dress shoes for formal events', 149.99),
  ('Red Silk Dress', 'Dresses', 'Elegant red silk dress for special occasions', 199.99),
  ('Running Watch', 'Accessories', 'GPS running watch with heart rate monitor', 249.99),
  ('Running Shorts', 'Clothing', 'Breathable running shorts with pockets', 39.99);

-- الخطوة 3: إنشاء فهرس GIN على العمود المولد
CREATE INDEX idx_shop_products_search ON shop_products USING gin (search_doc);

-- الخطوة 4: دالة بحث مع ترتيب وتمييز وتصفح
CREATE FUNCTION search_products(
  p_query TEXT,
  p_limit INT DEFAULT 20,
  p_offset INT DEFAULT 0
) RETURNS TABLE (
  product_id INT,
  name TEXT,
  category TEXT,
  price NUMERIC,
  rank REAL,
  headline TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    s.product_id,
    s.name,
    s.category,
    s.price,
    ts_rank(s.search_doc, websearch_to_tsquery('english', p_query)) AS rank,
    ts_headline(
      'english',
      COALESCE(s.name, '') || '. ' || COALESCE(s.description, ''),
      websearch_to_tsquery('english', p_query),
      'StartSel=<mark>,StopSel=</mark>,MaxWords=20,MinWords=5'
    ) AS headline
  FROM shop_products s
  WHERE s.search_doc @@ websearch_to_tsquery('english', p_query)
  ORDER BY rank DESC
  LIMIT p_limit
  OFFSET p_offset;
END;
$$ LANGUAGE plpgsql;

-- الخطوة 5: اختبار البحث
SELECT * FROM search_products('red shoes');
SELECT * FROM search_products('"running shoes"');
SELECT * FROM search_products('running -shoes');

-- الخطوة 6: بحث مع تصفية الفئة
SELECT name, price,
       ts_rank(search_doc, plainto_tsquery('english', 'red')) AS rank
FROM shop_products
WHERE search_doc @@ plainto_tsquery('english', 'red')
  AND category = 'Shoes'
ORDER BY rank DESC;