JSON, full-text search আর বিশেষ ডেটা টাইপ

১ · Relational ডেটাবেসে semi-structured ডেটা কেন

একটি অনলাইন দোকানের কথা ভাবুন, যেখানে মোবাইল ফোন, বই, পাঞ্জাবি আর শাড়ি সবই বিক্রি হয়। সব পণ্যের কিছু তথ্য এক — নাম, দাম, স্টক, ক্যাটাগরি। কিন্তু বাকিটা একেবারে আলাদা। ফোনের আছে RAM, storage আর ক্যামেরা। বইয়ের আছে পৃষ্ঠাসংখ্যা আর ভাষা। শাড়ির আছে কাপড়, দৈর্ঘ্য আর কোথায় বোনা। পাওয়ার ব্যাংকের আছে mAh. কাল যদি দোকানে ওষুধ আসে, আসবে মেয়াদের তারিখ আর ডোজ।

এই ধরনের তথ্যকে বলে semi-structured data — ডেটার ভেতরেই ঘরের নাম লেখা থাকে, কিন্তু সব রেকর্ডের ঘর এক হওয়ার বাধ্যবাধকতা নেই (পর্ব ৪-এ প্রথম দেখেছেন)। প্রশ্ন হলো, relational টেবিলে একে কোথায় রাখবেন? প্রচলিত তিনটি পথ আছে, আর প্রতিটির দাম আছে।

আজ প্রায় সব বড় relational DBMS-এ JSON রাখার ব্যবস্থা আছে — PostgreSQL, MySQL, SQL Server, Oracle, SQLite — আর SQL:2016 মানদণ্ডে JSON ফাংশন যোগ হয়েছে।

এই পর্বের এডিটরগুলো চলবে কোর্সের সাধারণ db-shop ডেটাবেসে — customers (customer_id, name, city, phone, joined_on), categories (category_id, name), products (product_id, name, category_id, price, stock), orders (order_id, customer_id, order_date, status) আর order_items (order_id, product_id, quantity, unit_price). এর সাথে যোগ করা আছে একটি নতুন টেবিল:

product_attributes (
  product_id INTEGER PRIMARY KEY REFERENCES products(product_id),
  attrs      TEXT NOT NULL CHECK (json_valid(attrs))   -- প্রতিটি পণ্যের JSON
)

আটটি পণ্যের attrs আট রকম। Mobile Phone (101)-এর RAM, storage, ক্যামেরা (ভেতরে আরেকটি object), dual SIM আর রঙের তালিকা; Headphone (102)-এর wireless আর ব্যাটারির ঘণ্টা; Power Bank (103)-এর mAh আর port-এর তালিকা; দুটি বইয়ের (104, 105) পৃষ্ঠা আর ভাষা; Panjabi (106)-র কাপড়, মাপের তালিকা আর রং; Saree (107)-র কাপড়, দৈর্ঘ্য (মিটার), উৎস আর রং; আর Smart Watch (110)-এর RAM, storage, ব্যাটারি আর পানিরোধী কি না। চাল আর তেলের (108, 109) কোনো সারি নেই — সব পণ্যের বাড়তি গুণ থাকতেই হবে, এমন নয়। প্রতিটি এডিটরের নিচের “নমুনা ডেটাবেস” বাক্সে পুরো ডেটা দেখতে পাবেন।

২ · SQLite-এ JSON পড়া — json_extract, -> আর ->>

SQLite-এ আলাদা কোনো JSON টাইপ নেই। JSON থাকে সাধারণ TEXT কলামে, আর ফাংশনগুলো সেই লেখাকে পার্স করে ভেতরে ঢোকে। কোন মান চাই, তা বলা হয় একটি JSON path দিয়ে: $ মানে পুরো ডকুমেন্ট, .camera মানে camera নামের ঘর, [0] মানে array-র প্রথম মান (সূচি 0 থেকে), আর [#-1] মানে শেষ মান।

মান বের করার তিনটি উপায়: পুরোনো ফাংশন json_extract(doc, path), আর SQLite ৩.৩৮ (২০২২) থেকে দুটি অপারেটর — -> আর ->>. এই পাতার ইঞ্জিন SQLite 3.49, তাই দুটিই চলে। অপারেটর দুটির পার্থক্য ছোট শোনায়, কিন্তু এটিই এই অংশের সবচেয়ে গুরুত্বপূর্ণ কথা:

• doc -> path ফেরত দেয় JSON লেখা — string হলে উদ্ধৃতিচিহ্নসহ ("cotton"), সংখ্যা হলে সংখ্যার লেখা ('8', TEXT হিসেবে)।
• doc ->> path ফেরত দেয় SQL মান — TEXT, INTEGER, REAL বা NULL, উদ্ধৃতিচিহ্ন ছাড়া। হিসাব আর তুলনার জন্য এটিই চাই। ফলাফল json_extract-এর প্রায় সমান।

SELECT p.name,
       a.attrs -> '$.ram_gb'   AS ram_json,
       a.attrs ->> '$.fabric'  AS fabric,
       a.attrs -> '$.fabric'   AS fabric_json,
       json_extract(a.attrs, '$.camera.rear_mp[0]') AS rear_mp
FROM products p
JOIN product_attributes a ON a.product_id = p.product_id
WHERE p.category_id IN (1, 3)      -- Electronics আর Clothing
ORDER BY p.product_id;

name         │ ram_json │ fabric │ fabric_json │ rear_mp
Mobile Phone │ 8        │ NULL   │ NULL        │ 50
Headphone    │ NULL     │ NULL   │ NULL        │ NULL
Power Bank   │ NULL     │ NULL   │ NULL        │ NULL
Panjabi      │ NULL     │ cotton │ "cotton"    │ NULL
Saree        │ NULL     │ silk   │ "silk"      │ NULL
Smart Watch  │ 1        │ NULL   │ NULL        │ NULL

একটি path ধাপে ধাপে কীভাবে ডকুমেন্টের গাছে নামে, আর প্রতিটি ধাপে দুই অপারেটর কী দেয় — Mobile Phone (101)-এর ডকুমেন্টে দেখুন:

এবার সেই পার্থক্যের ফাঁদ। প্রশ্ন: “কোন কোন পণ্যের RAM অন্তত 4 GB?” একই কোয়েরি দুবার লেখা হলো — প্রথমবার -> দিয়ে, দ্বিতীয়বার ->> দিয়ে:

-- ভুল: -> দেয় TEXT '8' আর '1'
SELECT p.name, a.attrs -> '$.ram_gb' AS ram
FROM products p JOIN product_attributes a USING (product_id)
WHERE a.attrs -> '$.ram_gb' >= 4;

-- ঠিক: ->> দেয় INTEGER 8 আর 1
SELECT p.name, a.attrs ->> '$.ram_gb' AS ram
FROM products p JOIN product_attributes a USING (product_id)
WHERE a.attrs ->> '$.ram_gb' >= 4;

-- প্রথম কোয়েরি               -- দ্বিতীয় কোয়েরি
name         │ ram            name         │ ram
Mobile Phone │ 8              Mobile Phone │ 8
Smart Watch  │ 1

৩ · JSON মেলে দেওয়া, বদলানো আর বানানো

json_each আর json_tree — ডকুমেন্ট থেকে সারি

অনেক সময় প্রশ্নটা একটি মান নিয়ে নয়, ডকুমেন্টের ভেতরের সবকিছু নিয়ে — “কোন কোন গুণ কতগুলো পণ্যে আছে?” বা “এই ফোনের সব মান একটি তালিকায় দেখাও”। এর জন্য SQLite-এর দুটি table-valued function — FROM-এ টেবিলের মতো বসে, আর প্রতিটি মানের জন্য একটি সারি দেয়:

• json_each(doc) — শুধু প্রথম স্তরের ঘর (বা array-র মান)। কলাম: key, value, type, atom, fullkey, path ইত্যাদি।
• json_tree(doc) — পুরো গাছ, প্রতিটি স্তরে নেমে (DSA-র ভাষায় depth-first traversal). একই কলাম, সাথে কোন সারি কার সন্তান তা বোঝার parent.

-- কোন গুণ অন্তত দুটি পণ্যে আছে?
SELECT j.key, COUNT(*) AS products, group_concat(a.product_id) AS ids
FROM product_attributes a, json_each(a.attrs) j
GROUP BY j.key
HAVING COUNT(*) >= 2
ORDER BY products DESC, j.key;

-- ফোনের ডকুমেন্টের সব পাতা (object/array বাদে)
SELECT t.fullkey, t.type, t.atom
FROM product_attributes a, json_tree(a.attrs) t
WHERE a.product_id = 101 AND t.type NOT IN ('object', 'array');

key           │ products │ ids
battery_hours │ 2        │ 102,110
color         │ 2        │ 106,107
colors        │ 2        │ 101,102
fabric        │ 2        │ 106,107
language      │ 2        │ 104,105
pages         │ 2        │ 104,105
ram_gb        │ 2        │ 101,110
storage_gb    │ 2        │ 101,110

fullkey                │ type    │ atom
$."ram_gb"             │ integer │ 8
$."storage_gb"         │ integer │ 128
$.camera."rear_mp"[0]  │ integer │ 50
$.camera."rear_mp"[1]  │ integer │ 2
$.camera."front_mp"    │ integer │ 8
$."dual_sim"           │ true    │ 1
$.colors[0]            │ text    │ black
$.colors[1]            │ text    │ blue

বদলানো, বানানো আর বৈধতা যাচাই

ডকুমেন্টের একটি ঘর বদলাতে পুরো JSON লেখা অ্যাপে এনে, বদলে, আবার পাঠানোর দরকার নেই। SQLite-এর চারটি ফাংশন নতুন একটি ডকুমেন্ট ফেরত দেয় (মূলটি বদলায় না — তাই UPDATE … SET attrs = … দিয়ে বসাতে হয়):

UPDATE product_attributes
SET attrs = json_set(attrs, '$.ram_gb', 12, '$.warranty_months', 12)
WHERE product_id = 101;
UPDATE product_attributes
SET attrs = json_insert(attrs, '$.ram_gb', 99, '$.charger', 'included')
WHERE product_id = 101;
UPDATE product_attributes
SET attrs = json_remove(attrs, '$.dual_sim', '$.colors[0]')
WHERE product_id = 101;
SELECT attrs FROM product_attributes WHERE product_id = 101;

{"ram_gb":12,"storage_gb":128,"camera":{"rear_mp":[50,2],"front_mp":8},
 "colors":["blue"],"warranty_months":12,"charger":"included"}

উল্টো দিকের কাজও সমান জরুরি: সাধারণ টেবিল থেকে JSON বানানো। ধরুন মোবাইল অ্যাপের API চায় প্রতিটি অর্ডার একটি JSON হিসেবে — গ্রাহকের নাম, অবস্থা আর জিনিসের তালিকা। json_object একটি object বানায়, আর দুটি aggregate function — json_group_array আর json_group_object — GROUP BY-এর প্রতিটি দলের সারিগুলোকে একটি JSON array বা object-এ গুছিয়ে দেয়:

SELECT o.order_id,
       json_object('customer', c.name, 'status', o.status,
         'items', json_group_array(
            json_object('product', p.name, 'qty', oi.quantity))) AS doc
FROM orders o
JOIN customers c    ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
WHERE o.order_id IN (1001, 1003)
GROUP BY o.order_id;

SELECT json_group_object(name, stock) AS stock_map
FROM products WHERE category_id = 1;

order_id │ doc
1001     │ {"customer":"Rahim Uddin","status":"delivered","items":[{"product":"Mobile Phone","qty":1},{"product":"Headphone","qty":2}]}
1003     │ {"customer":"Rahim Uddin","status":"shipped","items":[{"product":"Rice 5kg","qty":3},{"product":"Mustard Oil 1L","qty":2}]}

stock_map
{"Mobile Phone":12,"Headphone":40,"Power Bank":0,"Smart Watch":5}

শেষ প্রশ্ন: কেউ যদি ভাঙা JSON লিখে দেয়? json_valid(x) বলে লেখাটি বৈধ JSON কি না (1 বা 0), আর json(x) বৈধ JSON-কে ফাঁকা জায়গা ছাড়া ছোট (minified) রূপে দেয়। আমাদের টেবিলে CHECK (json_valid(attrs)) আছে — একটি CHECK constraint. নিচের শেষ বাক্যটিতে ইচ্ছা করে একটি বাড়তি কমা রাখা হয়েছে:

SELECT json_valid('{"ram_gb": 8}')  AS ok,
       json_valid('{ram_gb: 8}')    AS bad,
       json('{ "a" : [1, 2] }')     AS minified;

-- শেষে বাড়তি কমা — JSON-এ এটি নিষেধ
INSERT INTO product_attributes VALUES (108, '{"weight_kg": 5,}');

৪ · JSON-এ index — SQLite, PostgreSQL আর MySQL

এখন পর্যন্ত প্রতিটি JSON কোয়েরিতে SQLite প্রতিটি সারির ডকুমেন্ট পার্স করেছে — একটি full table scan. আটটি সারিতে টের পাওয়া যায় না, কিন্তু দশ লাখ পণ্যে “৮ GB RAM-এর ফোন” খুঁজতে দশ লাখ JSON পার্স করা মানে প্রতিটি খোঁজে কয়েক সেকেন্ড। সমাধান সাধারণ index-এর মতোই, শুধু কলামের বদলে একটি expression-এর ওপর — expression index (পর্ব ১৭-এ প্রথম দেখেছেন)। অথবা JSON-এর মানটিকে একটি generated column বানিয়ে তার ওপর সাধারণ index.

CREATE INDEX idx_attr_ram
  ON product_attributes (json_extract(attrs, '$.ram_gb'));
EXPLAIN QUERY PLAN SELECT product_id FROM product_attributes
  WHERE json_extract(attrs, '$.ram_gb') >= 4;
EXPLAIN QUERY PLAN SELECT product_id FROM product_attributes
  WHERE attrs ->> '$.ram_gb' >= 4;

ALTER TABLE product_attributes ADD COLUMN ram_gb INTEGER
  GENERATED ALWAYS AS (attrs ->> '$.ram_gb') VIRTUAL;
CREATE INDEX idx_ram_col ON product_attributes (ram_gb);
EXPLAIN QUERY PLAN SELECT product_id, ram_gb FROM product_attributes
  WHERE ram_gb >= 4;
SELECT product_id, ram_gb FROM product_attributes WHERE ram_gb >= 4;

SEARCH product_attributes USING COVERING INDEX idx_attr_ram (<expr>>?)
SCAN product_attributes
SEARCH product_attributes USING INDEX idx_ram_col (ram_gb>?)
product_id │ ram_gb
101        │ 8

খরচের হিসাবটা স্পষ্ট করে নিই। n সারির টেবিলে index ছাড়া প্রতিটি খোঁজে n-টি ডকুমেন্ট পার্স হয় — প্রতিটি ডকুমেন্টের দৈর্ঘ্য L হলে মোট কাজ মোটামুটি n × L-এর সমানুপাতিক। B-tree index (পর্ব ৪০) থাকলে মিলে যাওয়া প্রথম মানে পৌঁছাতে লাগে O(log n) ধাপ, তারপর প্রতিটি মিলে যাওয়া সারির জন্য একটি করে ধাপ — মোট O(log n + k), যেখানে k = ফলের সারির সংখ্যা। বিনিময়ে প্রতিটি INSERT/UPDATE-এ expression-টি একবার হিসাব করে index হালনাগাদ করতে হয়।

অন্য দুই প্রধান সিস্টেমে একই চিন্তা, ভিন্ন হাতিয়ার (এই কোডগুলো এখানে চলবে না — শুধু পড়ার জন্য):

CREATE TABLE product_attributes (
  product_id INT PRIMARY KEY REFERENCES products(product_id),
  attrs      JSONB NOT NULL            -- বৈধতা টাইপ নিজেই যাচাই করে
);
SELECT product_id,
       attrs -> 'camera' -> 'rear_mp' -> 0 AS rear_json,  -- jsonb
       attrs ->> 'fabric'                  AS fabric      -- text
FROM product_attributes;

SELECT product_id FROM product_attributes
WHERE attrs @> '{"fabric": "silk"}';   -- containment
SELECT product_id FROM product_attributes
WHERE attrs ? 'ram_gb';                 -- ঘরটি আছে কি না

-- @>, ?, ?|, ?& দ্রুত করে (jsonb_path_ops: ছোট, শুধু @> ও path)
CREATE INDEX idx_attrs_gin ON product_attributes USING GIN (attrs);

-- সংখ্যার তুলনা GIN দিয়ে হয় না: B-tree expression index
CREATE INDEX idx_ram ON product_attributes (((attrs ->> 'ram_gb')::int));
SELECT product_id FROM product_attributes
WHERE (attrs ->> 'ram_gb')::int >= 4;

-- SQL/JSON path (PostgreSQL 12 থেকে): 10 MP-এর বড় ক্যামেরাগুলো
SELECT product_id, jsonb_path_query(attrs, '$.camera.rear_mp[*] ? (@ > 10)')
FROM product_attributes;

CREATE TABLE product_attributes (
  product_id INT PRIMARY KEY,
  attrs      JSON NOT NULL,
  ram_gb     INT GENERATED ALWAYS AS (CAST(attrs ->> '$.ram_gb' AS UNSIGNED)) VIRTUAL,
  INDEX idx_ram (ram_gb),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);
SELECT product_id,
       JSON_EXTRACT(attrs, '$.camera.rear_mp[0]') AS rear_mp,
       attrs ->> '$.fabric'                       AS fabric
FROM product_attributes
WHERE ram_gb >= 4;                     -- idx_ram কাজে লাগে

-- ৮.০.১৩+: generated column ছাড়াই functional index
CREATE INDEX idx_ram2 ON product_attributes
  ((CAST(attrs ->> '$.ram_gb' AS UNSIGNED)));
-- ৮.০.১৭+: array-র জন্য multi-valued index, খোঁজা MEMBER OF দিয়ে
SELECT product_id FROM product_attributes
WHERE 'blue' MEMBER OF (attrs -> '$.colors');

৫ · JSON কখন ঠিক, আর কখন ডিজাইনের দুর্গন্ধ

প্রোগ্রামাররা “code smell” বলেন এমন লক্ষণকে, যা নিজে ভুল নয় কিন্তু গভীর সমস্যার ইঙ্গিত দেয়। JSON কলামের সবচেয়ে বড় smell: আপনি প্রতিনিয়ত JSON-এর ভেতরে খুঁজছেন, ফিল্টার করছেন, JOIN করছেন। তখন বুঝতে হবে তথ্যটি আসলে কাঠামোবদ্ধ — তার জায়গা সাধারণ কলাম বা আলাদা টেবিল (normalization, পর্ব ৩৬)। নিচের stepper-এ একই চারটি প্রশ্ন দিয়ে ছয়টি গুণের জায়গা ঠিক করা হলো:

৬ · Full-text search কেন — LIKE '%শব্দ%'-এর সীমা আর inverted index

দোকানের সার্চ বক্সে একজন ক্রেতা লিখলেন “phone”. সবচেয়ে সহজ SQL হলো WHERE name LIKE '%phone%'. দেখা যাক এটি কী করে — নামের ওপর একটি index বানিয়ে, তারপর খুঁজে, তারপর plan দেখে:

CREATE INDEX idx_products_name ON products (name);

SELECT product_id, name FROM products WHERE name LIKE '%phone%';

EXPLAIN QUERY PLAN
SELECT product_id FROM products WHERE name LIKE '%phone%';
EXPLAIN QUERY PLAN
SELECT product_id FROM products WHERE name GLOB 'Mobile*';

product_id │ name
102        │ Headphone
101        │ Mobile Phone

SCAN products USING COVERING INDEX idx_products_name
SEARCH products USING COVERING INDEX idx_products_name (name>? AND name<?)

আরও আছে: “charging” লিখলে “charges” মেলে না (শব্দের মূল বোঝে না), আর n সারি ও গড় দৈর্ঘ্য L হলে প্রতিটি খোঁজে মোটামুটি n × L অক্ষর পড়তে হয়।

সমাধানটি বহু শতাব্দী পুরোনো। বইয়ের শেষে যে নির্ঘণ্ট (index) থাকে — “ডেটাবেস: পৃষ্ঠা ১২, ৪৫, ৮০” — সেটিই inverted index. স্বাভাবিক দিকটা হলো “এই ডকুমেন্টে কোন কোন শব্দ”; উল্টো (inverted) দিকটা হলো “এই শব্দ কোন কোন ডকুমেন্টে”। প্রতিটি শব্দের ডকুমেন্ট-তালিকাকে বলে posting list. লেখাকে শব্দে ভাঙার কাজটি করে tokenizer. নিচে চারটি ছোট রিভিউ (ছোট করে লেখা, ছোট হাতের অক্ষরে, যতিচিহ্ন বাদ দিয়ে) থেকে ধাপে ধাপে index বানানো আর খোঁজা দেখুন:

d1: Battery lasts two days. Camera is good.
d2: Fast phone, good camera.
d3: Battery became weak.
d4: Battery drains fast.

বাস্তব full-text engine এর সাথে আরও তিনটি কাজ করে। Normalization — ছোট হাতের অক্ষর, accent চিহ্ন সরানো (café → cafe). Stemming — শব্দকে মূলে নামানো: charges, charging → charg (Porter-এর ১৯৮০ সালের অ্যালগরিদম ইংরেজির জন্য সবচেয়ে পরিচিত)। Stop word বাদ — the, is, a-র মতো অতি সাধারণ শব্দ, যা প্রায় সব ডকুমেন্টে আছে (PostgreSQL-এর english কনফিগারেশন এগুলো বাদ দেয়; SQLite FTS ডিফল্টে দেয় না)। তারপর relevance ranking — কোন ডকুমেন্ট বেশি মানানসই, তা হিসাব করে সাজানো।

৭ · SQLite FTS, PostgreSQL tsvector আর MySQL FULLTEXT

SQLite-এ full-text search আসে একটি virtual table হিসেবে — দেখতে টেবিল, কিন্তু ভেতরে আসলে inverted index. কয়েকটি সংস্করণের (FTS1 থেকে FTS5) মধ্যে আজ প্রচলিত FTS4 আর FTS5 (FTS5 এসেছে SQLite ৩.৯, ২০১৫ সালে)।

এডিটরে db-shop-এর সাথে একটি নতুন সাধারণ টেবিল আছে — reviews (review_id, product_id, body) — সাতটি ইংরেজি রিভিউ: ১ ও ২ Mobile Phone-এর, ৩ Headphone-এর, ৪ Saree-র, ৫ Panjabi-র, ৬ Power Bank-এর, ৭ Smart Watch-এর। এডিটর থেকে একটি FTS4 টেবিল বানিয়ে রিভিউগুলো তাতে ঢোকানো হচ্ছে। tokenize=porter মানে ইংরেজি Porter stemmer চালু।

CREATE VIRTUAL TABLE review_fts USING fts4(body, tokenize=porter);
INSERT INTO review_fts (docid, body) SELECT review_id, body FROM reviews;

SELECT docid, body FROM review_fts WHERE review_fts MATCH 'battery';
SELECT docid FROM review_fts WHERE review_fts MATCH 'battery camera';
SELECT docid, body FROM review_fts WHERE review_fts MATCH 'charging';
SELECT docid, snippet(review_fts, '[', ']', '…', -1, 6) AS hit
FROM review_fts WHERE review_fts MATCH 'batter* OR camera';

docid │ body
1     │ Battery lasts two days. Camera is good in daylight.
3     │ Great sound. The battery became weak after six months.
7     │ Battery drains fast when GPS is on.

docid
1

docid │ body
6     │ Charges my phone three times. Heavy but reliable.

docid │ hit
1     │ [Battery] lasts two days. [Camera] is…
2     │ …and a good [camera], but the…
3     │ Great sound. The [battery] became weak…
7     │ [Battery] drains fast when GPS is…

লক্ষ করুন, ফলগুলো docid-এর ক্রমে এসেছে, প্রাসঙ্গিকতার ক্রমে নয়। FTS4-এ কোনো তৈরি ranking ফাংশন নেই (matchinfo() দিয়ে নিজে লিখতে হয়)। FTS5-এ আছে bm25() — তথ্য-অনুসন্ধানের (information retrieval) একটি সুপরিচিত সূত্র, যা TF-IDF-এর চিন্তা থেকে এসেছে: শব্দটি ডকুমেন্টে যত বেশিবার (term frequency), আর পুরো সংগ্রহে যত বিরল (inverse document frequency), স্কোর তত ভালো; ছোট ডকুমেন্টে একবার থাকা বড় ডকুমেন্টে একবার থাকার চেয়ে বেশি অর্থবহ। নিচের Python প্রোগ্রামটি FTS5 চালায় (এটি অনলাইন কম্পাইলারে CPython-এর sqlite3-এ চলে, এই পাতার sql.js-এ নয়); এতে একটি “স্প্যাম” রিভিউ (৮) রাখা হয়েছে, যেখানে battery শব্দটি চারবার:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE VIRTUAL TABLE review_fts USING fts5(body, tokenize='porter')")
db.executemany("INSERT INTO review_fts (rowid, body) VALUES (?, ?)", [
    (1, "Battery lasts two days. Camera is good in daylight."), (2, "Fast phone and a good camera, but the charger gets hot."),
    (3, "Great sound. The battery became weak after six months."), (4, "Beautiful silk saree, the colour is exactly as shown."),
    (5, "Soft cotton panjabi, comfortable for summer."), (6, "Charges my phone three times. Heavy but reliable."),
    (7, "Battery drains fast when GPS is on."), (8, "Battery battery battery! Best battery ever."),
    (9, "Rice was clean and the packet was sealed."), (10, "Delivery was quick, the book pages are thick."),
])
sql = """SELECT rowid, round(bm25(review_fts), 3) AS score,
                highlight(review_fts, 0, '[', ']') AS hit
         FROM review_fts WHERE review_fts MATCH ?
         ORDER BY bm25(review_fts)"""   # ছোট (বেশি ঋণাত্মক) = বেশি প্রাসঙ্গিক
for q in ("battery", "battery OR camera"):
    print("query:", q)
    for rowid, score, hit in db.execute(sql, (q,)):
        print(f"  {rowid:>2} | {score:>6} | {hit}")

query: battery
   8 | -0.652 | [Battery] [battery] [battery]! Best [battery] ever.
   7 | -0.389 | [Battery] drains fast when GPS is on.
   1 | -0.352 | [Battery] lasts two days. Camera is good in daylight.
   3 | -0.352 | Great sound. The [battery] became weak after six months.
query: battery OR camera
   1 | -1.522 | [Battery] lasts two days. [Camera] is good in daylight.
   2 | -1.067 | Fast phone and a good [camera], but the charger gets hot.
   8 | -0.652 | [Battery] [battery] [battery]! Best [battery] ever.
   7 | -0.389 | [Battery] drains fast when GPS is on.
   3 | -0.352 | Great sound. The [battery] became weak after six months.

অন্য দুই সিস্টেমে full-text search টেবিলের ভেতরেই থাকে, আলাদা virtual table লাগে না (চলবে না, শুধু পড়ার জন্য):

SELECT to_tsvector('english', 'Charges my phone three times');
-- 'charg':1 'phone':3 'three':4 'time':5     (my = stop word, বাদ)

-- PostgreSQL 12+: লেখা বদলালে tsv নিজে থেকে হালনাগাদ হয়
ALTER TABLE reviews ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', body)) STORED;
CREATE INDEX idx_reviews_tsv ON reviews USING GIN (tsv);

SELECT review_id,
       ts_rank(tsv, q) AS rank,
       ts_headline('english', body, q) AS hit
FROM reviews, websearch_to_tsquery('english', 'battery -fast') AS q
WHERE tsv @@ q
ORDER BY rank DESC;

ALTER TABLE reviews ADD FULLTEXT INDEX ft_body (body);

-- natural language mode: স্কোরসহ
SELECT review_id, MATCH(body) AGAINST ('battery camera') AS score
FROM reviews
WHERE MATCH(body) AGAINST ('battery camera')
ORDER BY score DESC;

-- boolean mode: + থাকতেই হবে, - থাকা চলবে না, * উপসর্গ
SELECT review_id FROM reviews
WHERE MATCH(body) AGAINST ('+batter* -fast' IN BOOLEAN MODE);

৮ · বাংলা লেখা খোঁজার চ্যালেঞ্জ

ইংরেজির জন্য তৈরি সার্চ বাংলায় প্রায়ই অদ্ভুত আচরণ করে। তিনটি বড় সমস্যা, আর প্রথম দুটি নিচের এডিটরেই দেখা যাবে।

১. একই অক্ষর, দুই রকম Unicode. “ড়” লেখা যায় একটি code point দিয়ে (U+09DC), আবার “ড” + নুকতা “়” (U+09A1 U+09BC) — দুটি code point দিয়েও। পর্দায় হুবহু এক, কম্পিউটারের কাছে আলাদা। কোন কিবোর্ড বা কোন অ্যাপ দিয়ে লেখা হয়েছে, তার ওপর নির্ভর করে কোন রূপ আসবে। এর সমাধান Unicode normalization — লেখা জমা রাখার আগে সবকিছু একটি নির্দিষ্ট রূপে (সাধারণত NFC) বদলে নেওয়া। Unicode-এর নিয়ম অনুযায়ী NFC-তে U+09DC ভেঙে দুই code point-এর রূপ হয়ে যায় (ড়, ঢ়, য় — এই তিনটি এমন “composition exclusion”).

২. Tokenizer বাংলা বোঝে না। SQLite FTS4-এর ডিফল্ট simple tokenizer ইংরেজি ছাড়া সব অক্ষরকে শব্দের অংশ ধরে — বাংলার দাঁড়ি “।”-কেও! আর unicode61 tokenizer (Unicode ৬.১-এর অক্ষর-শ্রেণি অনুযায়ী) কার-চিহ্ন (া, ি, ু…) আর নুকতাকে শব্দ-বিভাজক ধরে, ফলে “শাড়ির” ভেঙে হয় শ, ড, র — তিনটি অর্থহীন টুকরো।

SELECT char(2524) = char(2465, 2492) AS same_letter,
       length(char(2524)) AS len1, length(char(2465, 2492)) AS len2;

CREATE VIRTUAL TABLE bn_simple USING fts4(t);
CREATE VIRTUAL TABLE bn_uni USING fts4(t, tokenize=unicode61);
INSERT INTO bn_simple (docid, t) VALUES (1, 'লাল শাড়ি'),
  (2, 'শাড়ির দাম কত'), (3, 'নীল শাড়ি।'),
  (4, 'সবুজ শা' || char(2524) || 'ি');    -- একক code point ড়
INSERT INTO bn_uni (docid, t) SELECT docid, t FROM bn_simple;

SELECT docid, t FROM bn_simple WHERE t MATCH 'শাড়ি';
SELECT docid, t FROM bn_uni    WHERE t MATCH 'শাড়ি';

same_letter │ len1 │ len2
0           │ 1    │ 2

docid │ t                 (simple tokenizer)
1     │ লাল শাড়ি

docid │ t                 (unicode61 tokenizer)
1     │ লাল শাড়ি
2     │ শাড়ির দাম কত
3     │ নীল শাড়ি।

FTS5-এর unicode61 tokenizer-এ categories নামের একটি বিকল্প আছে, যা দিয়ে বলে দেওয়া যায় কোন Unicode শ্রেণির অক্ষর শব্দের অংশ। কার-চিহ্ন আর নুকতা যে শ্রেণির (M* — mark), সেটি যোগ করলে বাংলা শব্দ আস্ত থাকে (এই পাতায় চলবে না; Python sqlite3 দিয়ে যাচাই করা):

CREATE VIRTUAL TABLE bn5 USING fts5(t,
  tokenize = "unicode61 categories 'L* N* Co M*'");
INSERT INTO bn5 (rowid, t) VALUES (1, 'লাল শাড়ি'), (2, 'শাড়ির দাম কত'), (3, 'নীল শাড়ি।');
SELECT rowid FROM bn5 WHERE bn5 MATCH 'শাড়ি';    -- 1, 3   (দাঁড়ি আলাদা হলো)
SELECT rowid FROM bn5 WHERE bn5 MATCH 'শাড়ি*';   -- 1, 2, 3 (উপসর্গ খোঁজ)
-- token তালিকা: কত, দাম, নীল, লাল, শাড়ি, শাড়ির

৩. বিভক্তি আর stemming. বাংলায় একই শব্দের বহু রূপ: শাড়ি, শাড়ির, শাড়িতে, শাড়িগুলো, শাড়িটা। ইংরেজির Porter stemmer বাংলায় কিছুই করে না, আর বেশিরভাগ ডেটাবেস ইঞ্জিনে বাংলার তৈরি stemmer থাকে না। বাস্তবে যে কৌশলগুলো ব্যবহার হয়: উপসর্গ খোঁজ (শাড়ি*), যা শব্দের শেষে যুক্ত বিভক্তি ধরে ফেলে; trigram বা n-gram index — শব্দকে তিন অক্ষরের টুকরোয় ভেঙে আংশিক মিল (SQLite FTS5-এ trigram tokenizer, PostgreSQL-এ pg_trgm extension); আর বড় সার্চ engine-এর ভাষাভিত্তিক analyzer — Lucene-ভিত্তিক কিছু engine-এর ডকুমেন্টেশনে বাংলার analyzer-এর উল্লেখ পাওয়া যায়, তবে ব্যবহারের আগে নিজের ডেটায় মান যাচাই করুন।

৯ · অন্যান্য বিশেষ টাইপ — array, enum, UUID, range আর geospatial

JSON আর full-text ছাড়াও আধুনিক DBMS-এ কিছু বিশেষ টাইপ আছে, যা নির্দিষ্ট সমস্যা খুব পরিচ্ছন্নভাবে মেটায়। এগুলোর বেশিরভাগ PostgreSQL-এ সবচেয়ে সমৃদ্ধ; SQLite-এ নেই, তাই নিচের কোড চালানো যাবে না।

Array

PostgreSQL-এ যেকোনো টাইপের array কলাম হয়: tags TEXT[]. ছোট তালিকার জন্য সুবিধাজনক — যেমন পণ্যের ট্যাগ। তবে মানগুলো অন্য টেবিলের দিকে নির্দেশ করলে বা তাদের নিজের গুণ থাকলে আলাদা টেবিলই ঠিক (অংশ ৫-এর colors).

ALTER TABLE products ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';
UPDATE products SET tags = ARRAY['eid', 'cotton'] WHERE product_id = 106;
SELECT name FROM products WHERE 'eid' = ANY (tags);          -- কোনো একটি মান
SELECT name FROM products WHERE tags @> ARRAY['eid'];        -- containment, GIN নিতে পারে
CREATE INDEX idx_products_tags ON products USING GIN (tags);

Enum

Enum হলো নির্দিষ্ট কয়েকটি মানের তালিকা। db-shop-এ orders.status-এর জন্য আমরা CHECK (status IN ('pending', …)) ব্যবহার করেছি — SQLite-এ এটিই পথ। PostgreSQL-এ CREATE TYPE … AS ENUM আর MySQL-এ কলামের টাইপ ENUM(…). নতুন মান যোগ সহজ, কিন্তু মান মোছা কঠিন; মানগুলো প্রায়ই বদলালে বা তাদের নিজস্ব তথ্য (বাংলা নাম) থাকলে lookup টেবিল আর foreign key ভালো।

CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered', 'cancelled');
ALTER TABLE orders ALTER COLUMN status TYPE order_status USING status::order_status;
ALTER TYPE order_status ADD VALUE 'returned' AFTER 'delivered';
SELECT order_id FROM orders WHERE status > 'shipped';   -- ক্রম ঘোষণার ক্রম অনুযায়ী

UUID

UUID একটি ১২৮-বিটের আইডি, যা কোনো কেন্দ্রীয় কাউন্টার ছাড়াই কার্যত অনন্য — মোবাইল অ্যাপ অফলাইনে অর্ডার বানিয়ে পরে পাঠালেও আইডি মিলে যাওয়ার ভয় নেই। অসুবিধা: আকারে বড় (১৬ byte), আর এলোমেলো version 4 B-tree-তে এলোমেলো জায়গায় ঢোকে; সময়-ক্রমিক version 7 (RFC 9562, ২০২৪) এর সমাধান — বিস্তারিত ভাবনার প্রশ্ন ৫-এ।

CREATE TABLE app_orders (
  order_uuid UUID PRIMARY KEY DEFAULT gen_random_uuid(),   -- v4, PostgreSQL 13+
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- PostgreSQL 18 থেকে সময়-ক্রমিক: DEFAULT uuidv7()
-- MySQL: UUID() দেয় version 1; জায়গা বাঁচাতে UUID_TO_BIN(UUID(), 1) দিয়ে BINARY(16)
-- SQLite: নিজস্ব টাইপ নেই — TEXT বা 16-byte BLOB হিসেবে রাখুন

Range আর exclusion constraint

অতিথিশালার রুম বুকিংয়ের কথা ভাবুন। একই রুম একই রাতে দুজনকে দেওয়া চলবে না। শুরু আর শেষের তারিখ দুটি কলামে রাখলে “ওভারল্যাপ নেই” নিয়মটি সাধারণ UNIQUE বা CHECK দিয়ে বলা যায় না; অ্যাপে যাচাই করলে দুজন একই মুহূর্তে বুক করলে দুটিই ঢুকে যেতে পারে (race condition, মডিউল ৭)। PostgreSQL-এর range type (daterange, tstzrange, int4range) একটি সময়সীমাকে একটি মান বানায়, আর exclusion constraint বলে: “একই রুমের দুটি সারির সময়সীমা পরস্পর ওভারল্যাপ (&&) করতে পারবে না” — DBMS নিজে রক্ষা করে, যত অনুরোধই একসাথে আসুক।

CREATE EXTENSION IF NOT EXISTS btree_gist;     -- INT-এর = কে GiST-এ আনতে
CREATE TABLE room_bookings (
  room_id INT NOT NULL,
  guest   TEXT NOT NULL,
  stay    DATERANGE NOT NULL,                 -- '[2025-12-20,2025-12-23)'
  EXCLUDE USING gist (room_id WITH =, stay WITH &&)
);
INSERT INTO room_bookings VALUES (7, 'Rahim', '[2025-12-20,2025-12-23)');
INSERT INTO room_bookings VALUES (7, 'Karima', '[2025-12-22,2025-12-24)');
-- ERROR: conflicting key value violates exclusion constraint ...
INSERT INTO room_bookings VALUES (7, 'Karima', '[2025-12-23,2025-12-24)');  -- ঠিক আছে

[ ) মানে শুরুর দিন অন্তর্ভুক্ত, শেষের দিন নয় — পর্ব ১৬-এর half-open interval. তাই ২৩ তারিখে চেক-আউট আর একই দিনে চেক-ইন সংঘর্ষ নয়।

Geospatial

ডেলিভারি অ্যাপের প্রশ্ন: “গুদাম থেকে ৩ কিলোমিটারের মধ্যে কোন কোন অর্ডার?” অক্ষাংশ-দ্রাঘিমাংশ দুটি REAL কলামে রেখে নিজে দূরত্বের সূত্র লিখলে ভুল আর ধীর — পৃথিবী গোল, আর সাধারণ B-tree দুই মাত্রার কাছাকাছি খোঁজা বোঝে না। Geospatial extension — PostgreSQL-এ PostGIS, SQLite-এ SpatiaLite, MySQL-এ নিজস্ব spatial টাইপ — বিন্দু, রেখা আর এলাকার টাইপ, দূরত্ব-ফাংশন আর R-tree/GiST-জাতীয় spatial index দেয়।

CREATE EXTENSION IF NOT EXISTS postgis;
ALTER TABLE orders ADD COLUMN drop_point geography(Point, 4326);
-- ST_MakePoint(দ্রাঘিমাংশ, অক্ষাংশ) — ক্রম খেয়াল করুন: আগে x = longitude
UPDATE orders SET drop_point = ST_MakePoint(90.41, 23.81)::geography WHERE order_id = 1001;
CREATE INDEX idx_orders_drop ON orders USING GIST (drop_point);
SELECT order_id FROM orders
WHERE ST_DWithin(drop_point, ST_MakePoint(90.39, 23.75)::geography, 3000);  -- মিটার

টাকা — DECIMAL-এর পুনরাবৃত্তি

পর্ব ১৩-এ দেখেছেন, টাকার হিসাবে floating-point (REAL, DOUBLE) বিপজ্জনক: 0.1 + 0.2 = 0.3 SQLite-এ 0 (মিথ্যা) দেয়, কারণ 0.1 binary-তে হুবহু রাখা যায় না। db-shop সরলতার জন্য price REAL ব্যবহার করে, কিন্তু আসল দোকানে PostgreSQL/MySQL-এ NUMERIC(12,2) বা DECIMAL, আর SQLite-এ পয়সায় INTEGER (৳১২৫.৫০ = 12550) রাখাই নিরাপদ। PostgreSQL-এর money টাইপ server-এর locale সেটিংয়ের ওপর নির্ভর করে, তাই অনেকে এটি এড়িয়ে চলার পরামর্শ দেন।

১০ · AI-এর যুগে JSON আর সার্চ কোয়েরি

AI টুলকে “ফোনের RAM দিয়ে ফিল্টার করো” বা “সার্চ বক্স বানাও” বললে সে প্রায় সবসময় চলার মতো কোড দেয়। কিন্তু এই পর্বের প্রায় প্রতিটি ফাঁদে সে নিয়মিত পা দেয়:

AI-এর লেখা JSON বা সার্চ কোয়েরি হাতে পেলে — যাচাইয়ের চেকলিস্ট

• প্রতিটি JSON path-এর ঘরের নাম আসল ডকুমেন্টের সাথে মিলিয়েছি (json_each দিয়ে key-গুলোর তালিকা দেখে)
• তুলনা আর হিসাবে ->> (বা PostgreSQL-এ cast) আছে; একটি “ভুল টাইপের” সারি দিয়ে পরীক্ষা করেছি
• সারির সংখ্যা হাতে গুনে মিলিয়েছি — ঘর না থাকা (NULL) সারি বাদ পড়ছে কি না, সেটা জেনে-বুঝে
• EXPLAIN QUERY PLAN-এ SCAN নয়, SEARCH দেখেছি; expression index থাকলে কোয়েরির লেখা হুবহু মিলিয়েছি
• সার্চে শব্দের সীমা (phone বনাম headphone), বড়-ছোট হাতের অক্ষর, আর বাংলা হলে দুই Unicode রূপ দিয়ে পরীক্ষা করেছি