با Sqlite شوخی نکنیم!

فرقی نداره که شما توسعه‌دهنده هستید، مهندس دِوآپسید، آنالیست داده یا هرچیز دیگه‌ای، Sqlite یه ابزار عالی برای شما به حساب میاد! اما چرا؟ اینجا مینویسم.

یکی از کارهایی که ما توی شرکتمون انجام میدیم، میمیک‌کردن بخشی از رفتارهای کاربرها و آنالیز اونهاست نسبت به خود نرم‌افزار. برای اینکارما نیاز به یه پایگاه‌داده داریم. از اونجایی که محیط تستمون کوچیکه، استفاده از ابزاری مثل داکر جز پیچیده‌تر کردن پروسه‌ی کار، چیز دیگه‌ای برامون نداره. یکی از انتخاب‌های ما، برای کوچکتر نگه‌داشتن فضای تست، استفاده از پایگاه‌داده‌ی Sqlite هست.

حالا تو این مدت به نتایجی رسیدم، که با کمک گرفتن از چند مطلب دیگه، براتون از مزایا و قدرت‌های این پایگاه‌داده بگم. بیاین به چندتا واقعیت در موردش بپردازیم:

1. تمام سیستم‌عامل‌ها ازش پشتیبانی میکنن،
2. سرورلِس یا Serverless هست و نیازی به جایی برای هاستش نداره،
3. میتونه مستقیم توی برنامه تعبیه یا embed بشه،
4. یه فایل بیشتر نیست!

کنسول و REPL

برای مهندسین داده، خود CLI این پایگاه‌داده قدرت‌های خیلی زیادی داره، مثلا میشه با یه خط، یه فایل CSV رو توی پایگاه‌داده import کرد:

> .import --csv city.csv city
> select count(*) from city;
1117

که میتونه همین خروجی رو خیلی شیک توی یه جدول ASCII نشون بده:

select
  century || ' century' as dates,
  count(*) as city_count
from history
group by century
order by century desc;

┌────────────┬────────────┐
│   dates    │ city_count │
├────────────┼────────────┤
│ 21 century │ 1          │
│ 20 century │ 263        │
│ 19 century │ 189        │
│ 18 century │ 191        │
│ 17 century │ 137        │
│ ...        │ ...        │
└────────────┴────────────┘

که میشه این دیتا رو مستقیما به JSON هم تبدیل کرد:

.mode json
.output city.json
select city, country from city limit 3;

[
    { "city": "Amsterdam", "country": "Nederlands" },
    { "city": "Berlin", "country": "Germany" },
    { "city": "Helsinki", "country": "Finland" }
]

حالا حتی میشه از JSON هم به جدولاش برگردوند!

پشتیبانی از JSON

یکی از ویژگی‌های خیلی خوب Sqlite، پشتیبانیش از JSON به حساب میاد. مثلا میشه مستقیما از یه فایل .json محتواش رو خوند، و اون رو به شکل یه جدول تولید کرد. به تابع readfile تو خط ۷ نگاه کنید:

select
    json_extract(value, '$.iso.code') as code,
    json_extract(value, '$.iso.number') as num,
    json_extract(value, '$.name') as name,
    json_extract(value, '$.units.major.name') as unit
from
    json_each(readfile('currency.json'))
;

┌──────┬─────┬─────────────────┬──────────┐
│ code │ num │      name       │   unit   │
├──────┼─────┼─────────────────┼──────────┤
│ ARS  │ 032 │ Argentine peso  | peso     │
│ CHF  │ 756 │ Swiss Franc     │ franc    │
│ EUR  │ 978 │ Euro            │ euro     │
│ GBP  │ 826 │ British Pound   │ pound    │
│ INR  │ 356 │ Indian Rupee    │ rupee    │
│ JPY  │ 392 │ Japanese yen    │ yen      │
│ MAD  │ 504 │ Moroccan Dirham │ dirham   │
│ RUR  │ 643 │ Russian Rouble  │ rouble   │
│ SOS  │ 706 │ Somali Shilling │ shilling │
│ USD  │ 840 │ US Dollar       │ dollar   │
└──────┴─────┴─────────────────┴──────────┘

توابع آماری و ریاضیاتی

خود Sqlite توابع مورد نیاز برای محاسبات آماری رو داره، گرچه لازمه براشون یه ماژول بارگزاری بشه:

.load sqlite3-stats

select
  count(*) as book_count,
  cast(avg(num_pages) as integer) as mean,
  cast(median(num_pages) as integer) as median,
  mode(num_pages) as mode,
  percentile_90(num_pages) as p90,
  percentile_95(num_pages) as p95,
  percentile_99(num_pages) as p99
from books;

┌────────────┬──────┬────────┬──────┬─────┬─────┬──────┐
│ book_count │ mean │ median │ mode │ p90 │ p95 │ p99  │
├────────────┼──────┼────────┼──────┼─────┼─────┼──────┤
│ 1483       │ 349  │ 295    │ 256  │ 640 │ 817 │ 1199 │
└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘

با وجود اینکه Sqlite خیلی از ویژگی‌های دیتابیس‌های دیگه رو نداره، ولی میشه خیلی از اونها رو با استفاده از Extensionهای خود دیتابیس لود کرد. فقط یه ایراد بزرگ اینه که به مرور زمان میتونن خیلی زیاد و بهم‌ریخته بشن.

بامزه‌تر وقتیه که میشه با Sqlite نمودارهای ساده رو ترسیم کرد:

with slots as (
  select
    num_pages/100 as slot,
    count(*) as book_count
  from books
  group by slot
),
max as (
  select max(book_count) as value
  from slots
)
select
  slot,
  book_count,
  printf('%.' || (book_count * 30 / max.value) || 'c', '*') as bar
from slots, max
order by slot;

┌──────┬────────────┬────────────────────────────────┐
│ slot │ book_count │              bar               │
├──────┼────────────┼────────────────────────────────┤
│ 0    │ 116        │ *********                      │
│ 1    │ 254        │ ********************           │
│ 2    │ 376        │ ****************************** │
│ 3    │ 285        │ **********************         │
│ 4    │ 184        │ **************                 │
│ 5    │ 90         │ *******                        │
│ 6    │ 54         │ ****                           │
│ 7    │ 41         │ ***                            │
│ 8    │ 31         │ **                             │
│ 9    │ 15         │ *                              │
│ 10   │ 11         │ *                              │
│ 11   │ 12         │ *                              │
│ 12   │ 2          │ *                              │
└──────┴────────────┴────────────────────────────────┘

پرفورمنس

اس‌کیو‌لایت میتونه خیلی خوب تا چند میلیون رکورد رو بررسی کنه. مثلا یه دستور INSERTساده رو سیستم من چیزی تا حدود ۲۵۰هزار رکورد در ثانیه رو انجام داده، و اگه بهش یه فایل csvرو بدیم چه بسا سریع‌تر هم عمل کنه:

.load sqlite3-vsv

create virtual table temp.blocks_csv using vsv(
    filename="ipblocks.csv",
    schema="create table x(network text, geoname_id integer, registered_country_geoname_id integer, represented_country_geoname_id integer, is_anonymous_proxy integer, is_satellite_provider integer, postal_code text, latitude real, longitude real, accuracy_radius integer)",
    columns=10,
    header=on,
    nulls=on
);

.timer on
insert into blocks
select * from blocks_csv;

Run Time: real 5.176 user 4.716420 sys 0.403866

select count(*) from blocks;
3386629

Run Time: real 0.095 user 0.021972 sys 0.063716

برای وب

یه موضوعی هم هست، که غالبا فکر میکنن (برنامه‌نویس‌ها) که اس‌کیو‌لایت برای وب مناسب نیست، چون مثلا نمیتونه چند عمل همزمان یا Concurrent رو انجام بده. که خب این ایده اشتباهه! اس‌کیو‌لایت یه حالتی رو داره به اسم write-ahead log mode که میتونه امکان داشتن چندین reader که concurrent عمل میکنن رو برای شما بسازه. (گرچه فقط امکان یک دونه writer هست).

به نظر من، اس‌کیو‌لایت برای سایت‌های کوچک میتونه خیلی مناسب باشه.

جست‌وجو، گراف و داکیومنت

اس‌کیو‌لایت از Partial Indexingها هم پشتیبانی میکنه. برای همین میشه روش ستون‌های ساخته شده رو ایندکس کرد و حتی کل دیتابیس رو به یه داکیومنت-دیتابیس تبدیل کرد!

create table currency(
  body text,
  code text as (json_extract(body, '$.code')),
  name text as (json_extract(body, '$.name'))
);

create index currency_code_idx on currency(code);

insert into currency
select value
from json_each(readfile('currency.sample.json'));

explain query plan
select name from currency where code = 'EUR';

QUERY PLAN
`--SEARCH TABLE currency USING INDEX currency_code_idx (code=?)

و حتی میشه روش Full-text search انجام داد!

create virtual table books_fts
using fts5(title, author, publisher);

insert into books_fts
select title, author, publisher from books;

select
  author,
  substr(title, 1, 30) as title,
  substr(publisher, 1, 10) as publisher
from books_fts
where books_fts match 'ann'
limit 5;

┌─────────────────────┬────────────────────────────────┬────────────┐
│       author        │             title              │ publisher  │
├─────────────────────┼────────────────────────────────┼────────────┤
│ Ruby Ann Boxcar     │ Ruby Ann's Down Home Trailer P │ Citadel    │
│ Ruby Ann Boxcar     │ Ruby Ann's Down Home Trailer P │ Citadel    │
│ Lynne Ann DeSpelder │ The Last Dance: Encountering D │ McGraw-Hil │
│ Daniel Defoe        │ Robinson Crusoe                │ Ann Arbor  │
│ Ann Thwaite         │ Waiting for the Party: The Lif │ David R. G │
└─────────────────────┴────────────────────────────────┴────────────┘

مموری دیتابیس

اس‌کیو‌لایت میتونه حتی روی مموری ذخیره بشه:

db = sqlite3.connect(":memory:")

یا حتی چندتا کانکشن:

db = sqlite3.connect("file::memory:?cache=shared")

امیدوارم این مطلب یکم دیدتون رو نسبت به اس‌کیو‌لایت عوض کرده باشه.

من خودم فَن این دیتابیس هستم و گاهن ازش استفاده میکنم. گرچه نه تو اپ‌های با مقیاس بزرگ، اما در کل تجربه‌ی خوبی رو داشتم باهاش.

موفق باشید.