Software Engineer Encyclopedia
0%
Domain 04 · Database Engineering & Data Management · Pillar

SQLite من الصفر: Embedded Database وSQL الأساسي

SQLite Fundamentals — Embedded Database and Core SQL

يعني إيه SQLite، الفرق بينها وبين SQL Server وPostgreSQL، type affinity، constraints، joins، aggregation، views وtriggers بأمثلة اتنفّذت فعلاً.

  • Intermediate
  • ~11 min read
  • 16 sections
  • 8 code examples
  • 2 diagrams
  • 6 questions
  • Priority L2 · Essential
  • SQLite 3.x (الأمثلة اتنفذت على SQLite 3.53.1 عبر Python sqlite3)

المتطلبات قبل ما تبدأ

C# وأساسيات ASP.NET Core. معرفة سابقة بـ SQL مفيدة لكن مش شرط.

01

هنطلع من الدرس بإيه؟

Measurable outcomes
Basics

بعد الدرس ده هتقدر تنشئ database في ملف واحد، تصمّم جداول بـ constraints صح، تكتب queries فيها joins و aggregation، وتشرح ليه SQLite مش «SQL Server صغيرة» لكنها محرك مختلف التصميم.

  • تشرح يعني إيه embedded database وإزاي بتختلف عن client/server.
  • تفرّق بين type affinity و Strict Tables وتتجنب مفاجآت الأنواع.
  • تفعّل PRAGMA foreign_keys = ON وتفهم ليه هي مش شغالة افتراضيًا.
  • تكتب CREATE/INSERT/UPDATE/DELETE/SELECT و JOIN و GROUP BY و VIEW و TRIGGER.

المتطلبات: فهم أساسي لمعنى table و row و column (راجع SQL Server — الجزء الأول). مش محتاج تثبّت حاجة: الأمثلة تحت بتشتغل بـ Python ومكتبة sqlite3 الجاهزة.

02

ابدأ بمثال شغال

A working first run
Practical

الكود ده بيعمل database في الذاكرة، ينشئ جدول، يضيف صفوف ويقرأها. الـ output تحت اتسجل من تشغيل حقيقي وقت بناء الموسوعة.

Pythonfirst-sqlite.pyExecuted ✓
import sqlite3

con = sqlite3.connect(":memory:")          # database كاملة جوه RAM، من غير سيرفر
con.execute("CREATE TABLE product (id INTEGER PRIMARY KEY, name TEXT NOT NULL, qty INTEGER NOT NULL)")
con.executemany("INSERT INTO product (name, qty) VALUES (?, ?)", [("Keyboard", 10), ("Mouse", 25)])
con.commit()

for row in con.execute("SELECT id, name, qty FROM product ORDER BY id"):
    print(row)
print("SQLite version:", sqlite3.sqlite_version)
ConsoleOutput — captured from a real run
(1, 'Keyboard', 10)
(2, 'Mouse', 25)
SQLite version: 3.53.1

لاحظ إنك ما شغّلتش service ولا عملت connection string فيه host و password. الـ «database» هي مكتبة جوه الـ process نفسه. لو استبدلت ":memory:" باسم ملف زي "shop.db" هتتحفظ البيانات في ملف واحد على الـ disk.

03

Mental model: مكتبة مش سيرفر

Embedded versus client/server
Basics

تخيّل إن SQL Server زي مطعم: فيه مطبخ منفصل (server process)، وانت بتبعت طلب (network) وبيرجعلك الأكل. SQLite زي مطبخ في بيتك: بتطبخ بنفسك وبتستخدم نفس الأدوات، مفيش وسيط. ده بيخليها سريعة جدًا في الاستخدام المحلي، لكن مفيش حد تاني «بيدير» الدخول المتزامن بالشكل اللي سيرفر كبير بيعمله.

المحورSQLiteSQL Server / PostgreSQL
التشغيلمكتبة داخل التطبيق (in-process)Server process منفصل عبر الشبكة
التخزينملف واحد (+ ملفات مؤقتة مثل WAL)ملفات داخل مدير السيرفر
Users وصلاحياتمفيش users؛ الصلاحية = صلاحية الملف في نظام التشغيلRoles و GRANT
Concurrencyقراءات كتير متوازية، كاتب واحد في نفس الوقتكتّاب كتير بتحكم row-level
الأنسب لـموبايل، Desktop، أدوات CLI، Tests، تطبيقات صغيرة/متوسطة، local-firstأنظمة multi-user عالية الكتابة
Application processSQLite libraryAppServerfunction call → filenetwork protocol
التطبيق بيتكلم مع SQLite كمكتبة، ومع SQL Server عبر الشبكة
04

Type affinity: SQLite مرنة في الأنواع

Dynamic typing and STRICT tables
Deep dive

ده أهم اختلاف مفاجئ. في SQL Server العمود INT ما يقبلش نص. في SQLite الافتراضي إن النوع المكتوب للعمود هو affinity (تفضيل) مش قيد صارم: القيمة هي اللي ليها نوع. SQLite بتحاول تحوّل القيمة للـ affinity، ولو ما قدرتش بتخزنها زي ما هي.

Pythonaffinity-demo.pyExecuted ✓
import sqlite3
con = sqlite3.connect(":memory:")

# جدول عادي: النوع affinity فقط
con.execute("CREATE TABLE loose (n INTEGER)")
con.executemany("INSERT INTO loose VALUES (?)", [(42,), ("42",), ("abc",), (3.0,), (3.5,)])
print("loose:", con.execute("SELECT n, typeof(n) FROM loose").fetchall())

# STRICT: النوع بيتفرض
con.execute("CREATE TABLE tight (n INTEGER) STRICT")
con.execute("INSERT INTO tight VALUES (42)")
try:
    con.execute("INSERT INTO tight VALUES ('abc')")
except sqlite3.Error as e:
    print("strict rejected:", type(e).__name__, "-", e)
ConsoleOutput — captured from a real run
loose: [(42, 'integer'), (42, 'integer'), ('abc', 'text'), (3, 'integer'), (3.5, 'real')]
strict rejected: IntegrityError - cannot store TEXT value in INTEGER column tight.n

الـ "42" اتحولت لـ integer، والـ 3.0 برضه، لكن "abc" فضلت نص داخل عمود INTEGER. الجدول STRICT (متاح من SQLite 3.37) رفض القيمة الغلط. قاعدة عملية: في الجداول الجديدة استخدم STRICT لما تقدر، واكتب CHECK constraints للقواعد المهمة. مفيش نوع DATE حقيقي: التواريخ بتتخزن كـ TEXT بصيغة ISO-8601 أو INTEGER (Unix time) أو REAL، وأنت اللي بتختار وتلتزم.

خطأ شائعافتراض إن VARCHAR(50) بيحدد الطول. في SQLite الرقم ده بيتجاهل. لو عايز حد أقصى استخدم CHECK (length(name) <= 50).
05

Constraints و Foreign Keys (وفخ الـ PRAGMA)

Constraints that actually protect data
Deep dive

الـ constraints هي خط الدفاع الأخير عن صحة البيانات، مش validation في الكود بس. الأنواع: PRIMARY KEY، NOT NULL، UNIQUE، CHECK، FOREIGN KEY، و DEFAULT. الفخ الكبير: Foreign keys مش مفعّلة افتراضيًا في SQLite لأسباب توافق قديمة، وبتتفعّل لكل connection بـ PRAGMA foreign_keys = ON.

Pythonconstraints-demo.pyExecuted ✓
import sqlite3

SCHEMA = """
CREATE TABLE category (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE) STRICT;
CREATE TABLE product (
  id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL CHECK (length(name) BETWEEN 1 AND 50),
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
  category_id INTEGER NOT NULL REFERENCES category(id) ON DELETE RESTRICT
) STRICT;
INSERT INTO category (name) VALUES ('Accessories');
"""

def make(fk):
    con = sqlite3.connect(":memory:")
    con.execute(f"PRAGMA foreign_keys = {'ON' if fk else 'OFF'}")   # لازم قبل أي transaction
    con.executescript(SCHEMA)
    return con

def attempt(con, label, sql):
    try:
        con.execute(sql); print("OK   ", label)
    except sqlite3.IntegrityError as e:
        print("FAIL ", label, "->", e)

ORPHAN = "INSERT INTO product (sku,name,price_cents,category_id) VALUES ('A1','Mouse',500,999)"
for fk in (False, True):
    con = make(fk)
    print(f"--- foreign_keys = {con.execute('PRAGMA foreign_keys').fetchone()[0]}")
    attempt(con, "orphan row (category 999 مش موجودة)", ORPHAN)
    attempt(con, "valid row", "INSERT INTO product (sku,name,price_cents,category_id) VALUES ('A2','Cable',100,1)")
    attempt(con, "duplicate sku", "INSERT INTO product (sku,name,price_cents,category_id) VALUES ('A2','Other',100,1)")
    attempt(con, "negative price", "INSERT INTO product (sku,name,price_cents,category_id) VALUES ('A3','X',-1,1)")
    attempt(con, "delete a category that is in use", "DELETE FROM category WHERE id = 1")

# فخ: PRAGMA foreign_keys بيتجاهل لو اتنفذ جوه transaction
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t (x INTEGER)")
con.execute("INSERT INTO t VALUES (1)")            # بدأ transaction ضمنية
con.execute("PRAGMA foreign_keys = ON")
print("--- PRAGMA inside a transaction -> foreign_keys =", con.execute("PRAGMA foreign_keys").fetchone()[0])
ConsoleOutput — captured from a real run
--- foreign_keys = 0
OK    orphan row (category 999 مش موجودة)
OK    valid row
FAIL  duplicate sku -> UNIQUE constraint failed: product.sku
FAIL  negative price -> CHECK constraint failed: price_cents >= 0
OK    delete a category that is in use
--- foreign_keys = 1
FAIL  orphan row (category 999 مش موجودة) -> FOREIGN KEY constraint failed
OK    valid row
FAIL  duplicate sku -> UNIQUE constraint failed: product.sku
FAIL  negative price -> CHECK constraint failed: price_cents >= 0
FAIL  delete a category that is in use -> FOREIGN KEY constraint failed
--- PRAGMA inside a transaction -> foreign_keys = 0

لاحظ: مع foreign_keys = 0 الصف اليتيم (category 999) دخل والحذف المحظور اتنفّذ، ومع 1 الاتنين اترفضوا. وآخر سطر أثبت فخ حقيقي: PRAGMA foreign_keys لا يعمل جوه transaction (بيفضل 0 بدون أي خطأ)، فلازم تنفّذه فور فتح الـ connection قبل أي statement. الأموال بنخزنها price_cents INTEGER مش REAL عشان نتجنب أخطاء الـ floating point. حسب توثيق Microsoft.Data.Sqlite المكتبة بتفعّل الـ foreign keys على الـ connection (Version Verification Required: راجع الصفحة الرسمية لإصدارك)، لكن في أي أداة تانية (CLI، Python) لازم تفعّلها بنفسك.

06

SQL: DML و Joins و Aggregation و Views و Triggers

Core SQL end to end
Practical

نبني مثال مخزن صغير ونمشي على الأوامر الأساسية. الأمثلة الجاية بتستخدم نفس الـ schema، وبتوضح كل مفهوم بنتيجة حقيقية.

Pythonsql-core.pyExecuted ✓
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON")
con.executescript("""
CREATE TABLE category (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE) STRICT;
CREATE TABLE product (
  id INTEGER PRIMARY KEY, sku TEXT NOT NULL UNIQUE, name TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
  qty INTEGER NOT NULL DEFAULT 0 CHECK (qty >= 0),
  category_id INTEGER REFERENCES category(id)
) STRICT;
INSERT INTO category (name) VALUES ('Accessories'), ('Displays'), ('Empty');
INSERT INTO product (sku,name,price_cents,qty,category_id) VALUES
 ('KB-1','Keyboard',2500,10,1), ('MS-1','Mouse',1200,25,1),
 ('MN-1','Monitor 24',15000,4,2), ('MN-2','Monitor 27',22000,0,2),
 ('NO-1','Uncategorised cable',300,100,NULL);
""")
def show(title, sql, params=()):
    print("--", title)
    for r in con.execute(sql, params): print("  ", r)

show("filter + sort", "SELECT name, qty FROM product WHERE qty > 0 AND price_cents < 20000 ORDER BY qty DESC")
show("INNER JOIN (يسقط الصف اللي من غير category)", "SELECT p.name, c.name FROM product p JOIN category c ON c.id = p.category_id ORDER BY p.id")
show("LEFT JOIN (يحافظ على الصف)", "SELECT p.name, c.name FROM product p LEFT JOIN category c ON c.id = p.category_id ORDER BY p.id")
show("aggregate + GROUP BY + HAVING", "SELECT c.name, COUNT(*) AS items, SUM(p.price_cents * p.qty) AS stock_value FROM category c JOIN product p ON p.category_id = c.id GROUP BY c.id HAVING SUM(p.qty) > 0 ORDER BY stock_value DESC")
show("subquery", "SELECT name FROM product WHERE price_cents > (SELECT AVG(price_cents) FROM product)")

con.execute("CREATE VIEW low_stock AS SELECT sku, name, qty FROM product WHERE qty < 5")
show("view", "SELECT * FROM low_stock ORDER BY sku")

con.executescript("""
CREATE TABLE audit (id INTEGER PRIMARY KEY, sku TEXT, old_qty INTEGER, new_qty INTEGER) STRICT;
CREATE TRIGGER trg_qty AFTER UPDATE OF qty ON product
BEGIN INSERT INTO audit (sku, old_qty, new_qty) VALUES (OLD.sku, OLD.qty, NEW.qty); END;
""")
cur = con.execute("UPDATE product SET qty = qty - 3 WHERE sku = 'KB-1'")
print("rows updated:", cur.rowcount)
show("trigger wrote audit", "SELECT sku, old_qty, new_qty FROM audit")
cur = con.execute("DELETE FROM product WHERE qty = 0")
print("rows deleted:", cur.rowcount)
ConsoleOutput — captured from a real run
-- filter + sort
   ('Uncategorised cable', 100)
   ('Mouse', 25)
   ('Keyboard', 10)
   ('Monitor 24', 4)
-- INNER JOIN (يسقط الصف اللي من غير category)
   ('Keyboard', 'Accessories')
   ('Mouse', 'Accessories')
   ('Monitor 24', 'Displays')
   ('Monitor 27', 'Displays')
-- LEFT JOIN (يحافظ على الصف)
   ('Keyboard', 'Accessories')
   ('Mouse', 'Accessories')
   ('Monitor 24', 'Displays')
   ('Monitor 27', 'Displays')
   ('Uncategorised cable', None)
-- aggregate + GROUP BY + HAVING
   ('Displays', 2, 60000)
   ('Accessories', 2, 55000)
-- subquery
   ('Monitor 24',)
   ('Monitor 27',)
-- view
   ('MN-1', 'Monitor 24', 4)
   ('MN-2', 'Monitor 27', 0)
rows updated: 1
-- trigger wrote audit
   ('KB-1', 10, 7)
rows deleted: 1

اقرأ الـ output بالترتيب: الـ INNER JOIN رجّع 4 صفوف، والـ LEFT JOIN رجّع 5 لأنه حافظ على «Uncategorised cable». الـ GROUP BY جمع حسب category، والـ HAVING فلتر المجموعات (بعد التجميع) بعكس WHERE اللي بيفلتر الصفوف قبل التجميع. الـ trigger اشتغل تلقائيًا وسجّل التغيير. الـ ? placeholders (parameters) هي الطريقة الصح لتمرير القيم؛ ما تبنيش SQL بـ string concatenation أبدًا (SQL injection).

07

تتبّع التنفيذ: إيه اللي بيحصل في ملف الـ database؟

Execution tracing
Deep dive

لما تكتب INSERT في وضع الـ default (rollback journal): SQLite تفتح transaction ضمنية، تكتب الصفحات القديمة في ملف journal، تعدّل ملف الـ database، وبعد ما تتأكد إن كله على الـ disk تمسح الـ journal، وده اللحظة اللي بيحصل فيها commit فعليًا. لو الجهاز وقع في النص، عند الفتح التالي SQLite بترجّع من الـ journal. ده سبب إن كل INSERT منفرد بدون transaction صريحة بطيء (كل واحد = fsync). التفاصيل والـ WAL mode في الدرس التالي.

أما الـ executemany الأولى فبتتنفذ جوه transaction واحدة لحد commit(). فرق الأداء بين 10,000 insert في transaction واحدة وبين 10,000 transaction ممكن يبقى بأوامر من الحجم، وهنقيسه فعليًا في الدرس التالي بدل ما نفترضه.

08

إمتى تستخدم SQLite وإمتى لأ؟

When to use it, when not to
Deep dive

التوثيق الرسمي نفسه بيوضح إن SQLite مناسبة لـ application file format، التطبيقات المضمنة، الأجهزة، وتخزين بيانات متوسطة الحجم، والـ testing. مش مناسبة لما: (1) عندك تطبيق client/server بكتّاب كتير ومتزامنين، (2) الـ database لازم توصلها عدة أجهزة عبر network filesystem، (3) محتاج roles وصلاحيات تفصيلية على مستوى الـ database، أو (4) البيانات ضخمة جدًا. مش معناها إنها «لعبة»: بتخدم تطبيقات حقيقية كتير لو احتياجاتك داخل حدودها.

09

جوه ملف SQLite: pages و B-trees و rowid

How SQLite stores data
Deep dive

ملف SQLite عبارة عن pages بحجم ثابت (افتراضيًا 4096 byte). كل جدول عادي هو B-tree مرتب بالـ rowid (رقم 64-bit)، وكل index هو B-tree تاني مرتب بقيم الأعمدة وبيشاور على الـ rowid. فهم ده بيفسّر الأداء كله: البحث بالـ rowid = نزول شجرة واحدة، والبحث بـ index = شجرتين (index ثم الجدول) إلا لو الـ index «covering».

Table root pagerowid 1..500rowid 501..1000leaf pages: full rowsIndex root (email)a…m → rowidsn…z → rowidslookup rowid in table tree
Table B-tree keyed by rowid, plus an index B-tree on email that points back to rowids
  • INTEGER PRIMARY KEY = alias للـ rowid نفسه (مفيش index إضافي) — أسرع شكل للـ primary key.
  • WITHOUT ROWID tables بتخزن الصف مرتب بالـ primary key مباشرة — مفيدة لـ keys مركبة أو نصية بتتقري كتير.
  • Covering index: لو كل الأعمدة المطلوبة موجودة في الـ index، SQLite ما بينزلش للجدول خالص («USING COVERING INDEX» في الـ plan).
SQLsee it for yourself
CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT, city TEXT);
CREATE INDEX ix_customers_city_name ON customers(city, name);

EXPLAIN QUERY PLAN SELECT * FROM customers WHERE id = 42;
-- SEARCH customers USING INTEGER PRIMARY KEY (rowid=?)
EXPLAIN QUERY PLAN SELECT name FROM customers WHERE city = 'Cairo';
-- SEARCH customers USING COVERING INDEX ix_customers_city_name (city=?)
EXPLAIN QUERY PLAN SELECT * FROM customers WHERE name LIKE '%ali%';
-- SCAN customers   <- leading wildcard cannot use a B-tree; consider FTS5
10

FTS5 و JSON: قدرات SQLite اللي ناس كتير ما تعرفهاش

Full-text search and JSON
Practical
SQLFTS5: real full-text search inside a file
CREATE VIRTUAL TABLE notes_fts USING fts5(title, body, content='notes', content_rowid='id', tokenize='unicode61 remove_diacritics 2');
-- keep it in sync with triggers (or rebuild): INSERT INTO notes_fts(notes_fts) VALUES('rebuild');
SELECT n.id, n.title, bm25(notes_fts) AS score
FROM notes_fts JOIN notes n ON n.id = notes_fts.rowid
WHERE notes_fts MATCH 'invoice NEAR(overdue, 5)'
ORDER BY score                                   -- bm25(): lower is better in SQLite's implementation
LIMIT 10;
SQLJSON functions
CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT CHECK (json_valid(payload)));
SELECT json_extract(payload, '$.customer.id') AS customer, count(*)
FROM events WHERE payload ->> '$.type' = 'order.created' GROUP BY 1;
-- index an expression that is queried a lot
CREATE INDEX ix_events_type ON events(payload ->> '$.type');

FTS5 بيستخدم inverted index وBM25 — نفس الأفكار اللي في Elasticsearch/OpenSearch بس على مستوى ملف. ده بيخليه مثالي لبحث offline في تطبيق موبايل/desktop، أو كـ «keyword half» لـ hybrid search صغير جنب vector index. ملاحظة العربي: الـ tokenizer الافتراضي ما بيعملش stemming للعربي؛ normalization (الألف، التاء المربوطة، التشكيل) قبل الإدخال وعند البحث بتفرق كتير.

11

SQLite من ‎.NET من غير EF: Microsoft.Data.Sqlite

ADO.NET-style access
Practical
C#parameterised commands, one transaction for a batch
await using var conn = new SqliteConnection("Data Source=app.db;Foreign Keys=True");
await conn.OpenAsync();
await using var tx = (SqliteTransaction)await conn.BeginTransactionAsync();
var cmd = conn.CreateCommand();
cmd.Transaction = tx;
cmd.CommandText = "INSERT INTO customers(email, name, city) VALUES ($email, $name, $city)";
var email = cmd.Parameters.Add("$email", SqliteType.Text);
var name = cmd.Parameters.Add("$name", SqliteType.Text);
var city = cmd.Parameters.Add("$city", SqliteType.Text);
foreach (var c in incoming)                         // 10k rows: ~100x faster inside one transaction than autocommit
{
    email.Value = c.Email; name.Value = c.Name; city.Value = (object?)c.City ?? DBNull.Value;
    await cmd.ExecuteNonQueryAsync();
}
await tx.CommitAsync();

ليه الفرق ضخم؟ في autocommit كل INSERT = transaction = fsync للقرص. في transaction واحدة، fsync مرة واحدة في الآخر. ده التفسير العملي لدرس Transactions و WAL.

12

تمرين محلول إضافي

Extra worked exercise
Practical
بحث بـ LIKE و wildcard في الأول على 300 ألف ملاحظة بطيء في تطبيق desktop. الحل؟

الحل: الـ leading wildcard بيمنع استخدام B-tree index فبيعمل full scan. استخدم FTS5 virtual table مربوط بالجدول (external content) مع triggers للمزامنة، وابحث بـ MATCH مع ترتيب bm25(). للعربي: normalize النص قبل الإدخال وقبل البحث بنفس الدالة. قِس قبل وبعد بـ EXPLAIN QUERY PLAN ووقت الاستعلام.

13

أخطاء شائعة

Common mistakes
Pitfalls
  • نسيان PRAGMA foreign_keys = ON (الـ constraints موجودة بس مش بتتفرض).
  • تخزين الأموال كـ REAL (استخدم integer cents أو decimal كنص بحذر).
  • بناء SQL بدمج strings بدل parameters.
  • افتراض إن الأنواع صارمة من غير STRICT.
  • فتح connection كتير بدون إغلاقها، أو مشاركة connection بين threads من غير فهم قواعد المكتبة.
  • وضع ملف database على network share أو مجلد sync (Dropbox/OneDrive) يشتغل عليه أكتر من جهاز: ده بيخرّب الملف.
14

خمس تمارين محلولة

Practice with worked solutions
Practical
1 (Easy): ليه بقت قيمة 'abc' جوه عمود INTEGER؟ وإزاي تمنعها؟

الحل: العمود INTEGER ليه affinity فقط، فلما التحويل فشل خزّنت SQLite النص كما هو. الحل الأول: CREATE TABLE ... STRICT. الحل الثاني: CHECK (typeof(n) = 'integer'). التفكير: الأول أنظف لأنه بيحمي كل الأعمدة، والتاني مفيد لو الجدول قديم.

2 (Easy): اكتب query ترجّع أسماء المنتجات اللي سعرها أكبر من المتوسط

الحل: SELECT name FROM product WHERE price_cents > (SELECT AVG(price_cents) FROM product);. الـ subquery بتتحسب مرة واحدة (non-correlated) وبتتقارن بكل صف. نفس النتيجة ظهرت في المثال.

3 (Intermediate): ليه INNER JOIN رجّع 4 صفوف واللي LEFT JOIN رجّع 5؟

الحل: منتج «Uncategorised cable» قيمة category_id فيه NULL، والـ NULL ما بيساويش أي id، فالـ INNER JOIN بيسقطه. الـ LEFT JOIN بيحافظ على كل صفوف الجدول الشمال ويحط NULL في الأعمدة اللي ملهاش match.

4 (Intermediate): اكتب trigger يمنع تقليل qty تحت صفر برسالة خطأ واضحة (بدل CHECK)

الحل: CREATE TRIGGER trg_nonneg BEFORE UPDATE OF qty ON product WHEN NEW.qty < 0 BEGIN SELECT RAISE(ABORT, 'qty cannot be negative'); END;. التفكير: الـ CHECK أبسط وأفضل في الغالب؛ الـ trigger مفيد لما تحتاج رسالة مخصصة أو قاعدة بتقارن بجدول تاني. جرّبه في مختبرك قبل الاعتماد عليه.

5 (Challenging): أنت اكتشفت إن جدول product فيه category_id = 999 مش موجود. اشرح السبب واكتب query تكتشف كل الحالات

الحل: السبب غالبًا إن الـ FK كانت OFF وقت الإدخال (أو الجدول اتملى بأداة تانية). الاكتشاف: SELECT p.* FROM product p LEFT JOIN category c ON c.id = p.category_id WHERE p.category_id IS NOT NULL AND c.id IS NULL;، أو PRAGMA foreign_key_check; اللي بترجّع كل المخالفات. بعد التنظيف فعّل الـ PRAGMA على كل connection.

15

Mini Project: دفتر عناوين بـ SQLite

Mini project
Practical

ابنِ سكربت Python (أو برنامج console) فيه جدولين contact و phone بعلاقة one-to-many، STRICT، و CHECK على صيغة الرقم، و view اسمه contact_summary بيرجّع الاسم وعدد الأرقام، و trigger بيسجل أي حذف في جدول audit. معيار النجاح: (1) إدخال رقم لجهة غير موجودة بيفشل، (2) حذف جهة بيمسح أرقامها (ON DELETE CASCADE)، (3) كل الاستعلامات بـ parameters، (4) فيه اختبارات (unittest) لكل قاعدة.

16

أسئلة إنترفيو

Interview questions
Basics
إيه معنى إن SQLite embedded database؟
بتشتغل كمكتبة جوه نفس process التطبيق بدون server منفصل، وبتخزن البيانات في ملف واحد. مفيش network protocol ولا users؛ الأمان بيعتمد على صلاحيات الملف والتطبيق.
يعني إيه type affinity؟
هو تفضيل نوع للعمود: SQLite بتحاول تحوّل القيم للنوع المفضل لكن ممكن تخزن القيمة بنوعها الأصلي لو التحويل مش ممكن. جداول STRICT بتفرض الأنواع.
ليه ممكن Foreign Key ما تتفرضش في SQLite؟
لأن PRAGMA foreign_keys افتراضيًا OFF لكل connection (للتوافق مع الإصدارات القديمة) ولازم تتفعّل صراحة.
إيه الفرق بين WHERE و HAVING؟
WHERE بيفلتر الصفوف قبل التجميع، و HAVING بيفلتر المجموعات بعد الـ aggregation.
إمتى SQLite اختيار سيئ؟
لما فيه كتّاب كتير متزامنين على نفس الـ database، أو لما الملف لازم يتشارك عبر network filesystem، أو محتاج صلاحيات ومستخدمين على مستوى الـ database.
(أصعب) ليه تخزين الأموال كـ REAL خطر؟
لأن REAL هو binary floating point، فبعض القيم العشرية (زي 0.1) مش بتتمثل بدقة وبتتراكم أخطاء التقريب. الأفضل integer للوحدة الصغرى (cents) أو decimal مع قواعد تقريب واضحة.
↗

المراجع والقراءة الإضافية

References & further reading

الدرس بيشرح الفكرة نفسها؛ الروابط دي للتحقق والتعمق والرجوع لتفاصيل الـ API. (محتاجة إنترنت.)

خلصت الدرس وحليت الأسئلة؟ علّم عليه عشان يتحسب في تقدّمك.

محتويات الدرس