آیین سعیدی، یه برنامه‌نویس و بلاگر

برگردید به صفحه‌ی اصلی

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

March 25, 20212 دقیقه مطالعهبرای باتجربه‌ترهای علاقه‌مند به مهندسی نرم‌افزار
  • #database
  • #sqlite
  • #statistics
  • #پایگاه داده

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

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

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

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

کنسول و REPL

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

1> .import --csv city.csv city
2> select count(*) from city;
31117

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

1select
2 century || ' century' as dates,
3 count(*) as city_count
4from history
5group by century
6order by century desc;
1┌────────────┬────────────┐
2│ dates │ city_count │
3├────────────┼────────────┤
4│ 21 century │ 1 │
5│ 20 century │ 263 │
6│ 19 century │ 189 │
7│ 18 century │ 191 │
8│ 17 century │ 137 │
9│ ... │ ... │
10└────────────┴────────────┘

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

1.mode json
2.output city.json
3select city, country from city limit 3;
1[
2 { "city": "Amsterdam", "country": "Nederlands" },
3 { "city": "Berlin", "country": "Germany" },
4 { "city": "Helsinki", "country": "Finland" }
5]

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

پشتیبانی از JSON

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

1select
2 json_extract(value, '$.iso.code') as code,
3 json_extract(value, '$.iso.number') as num,
4 json_extract(value, '$.name') as name,
5 json_extract(value, '$.units.major.name') as unit
6from
7 json_each(readfile('currency.json'))
8;
1┌──────┬─────┬─────────────────┬──────────┐
2│ code │ num │ name │ unit │
3├──────┼─────┼─────────────────┼──────────┤
4│ ARS │ 032 │ Argentine peso | peso │
5│ CHF │ 756 │ Swiss Franc │ franc │
6│ EUR │ 978 │ Euro │ euro │
7│ GBP │ 826 │ British Pound │ pound │
8│ INR │ 356 │ Indian Rupee │ rupee │
9│ JPY │ 392 │ Japanese yen │ yen │
10│ MAD │ 504 │ Moroccan Dirham │ dirham │
11│ RUR │ 643 │ Russian Rouble │ rouble │
12│ SOS │ 706 │ Somali Shilling │ shilling │
13│ USD │ 840 │ US Dollar │ dollar │
14└──────┴─────┴─────────────────┴──────────┘

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

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

1.load sqlite3-stats
2
3select
4 count(*) as book_count,
5 cast(avg(num_pages) as integer) as mean,
6 cast(median(num_pages) as integer) as median,
7 mode(num_pages) as mode,
8 percentile_90(num_pages) as p90,
9 percentile_95(num_pages) as p95,
10 percentile_99(num_pages) as p99
11from books;
1┌────────────┬──────┬────────┬──────┬─────┬─────┬──────┐
2│ book_count │ mean │ median │ mode │ p90 │ p95 │ p99 │
3├────────────┼──────┼────────┼──────┼─────┼─────┼──────┤
4│ 1483 │ 349 │ 295 │ 256 │ 640 │ 817 │ 1199 │
5└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘

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

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

1with slots as (
2 select
3 num_pages/100 as slot,
4 count(*) as book_count
5 from books
6 group by slot
7),
8max as (
9 select max(book_count) as value
10 from slots
11)
12select
13 slot,
14 book_count,
15 printf('%.' || (book_count * 30 / max.value) || 'c', '*') as bar
16from slots, max
17order by slot;
1┌──────┬────────────┬────────────────────────────────┐
2│ slot │ book_count │ bar │
3├──────┼────────────┼────────────────────────────────┤
4│ 0 │ 116 │ ********* │
5│ 1 │ 254 │ ******************** │
6│ 2 │ 376 │ ****************************** │
7│ 3 │ 285 │ ********************** │
8│ 4 │ 184 │ ************** │
9│ 5 │ 90 │ ******* │
10│ 6 │ 54 │ **** │
11│ 7 │ 41 │ *** │
12│ 8 │ 31 │ ** │
13│ 9 │ 15 │ * │
14│ 10 │ 11 │ * │
15│ 11 │ 12 │ * │
16│ 12 │ 2 │ * │
17└──────┴────────────┴────────────────────────────────┘

پرفورمنس

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

1.load sqlite3-vsv
2
3create virtual table temp.blocks_csv using vsv(
4 filename="ipblocks.csv",
5 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)",
6 columns=10,
7 header=on,
8 nulls=on
9);
1.timer on
2insert into blocks
3select * from blocks_csv;
4
5Run Time: real 5.176 user 4.716420 sys 0.403866
1select count(*) from blocks;
23386629
3
4Run Time: real 0.095 user 0.021972 sys 0.063716

برای وب

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

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

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

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

1create table currency(
2 body text,
3 code text as (json_extract(body, '$.code')),
4 name text as (json_extract(body, '$.name'))
5);
6
7create index currency_code_idx on currency(code);
8
9insert into currency
10select value
11from json_each(readfile('currency.sample.json'));
1explain query plan
2select name from currency where code = 'EUR';
3
4QUERY PLAN
5`--SEARCH TABLE currency USING INDEX currency_code_idx (code=?)

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

1create virtual table books_fts
2using fts5(title, author, publisher);
3
4insert into books_fts
5select title, author, publisher from books;
6
7select
8 author,
9 substr(title, 1, 30) as title,
10 substr(publisher, 1, 10) as publisher
11from books_fts
12where books_fts match 'ann'
13limit 5;
1┌─────────────────────┬────────────────────────────────┬────────────┐
2│ author │ title │ publisher │
3├─────────────────────┼────────────────────────────────┼────────────┤
4│ Ruby Ann Boxcar │ Ruby Ann's Down Home Trailer P │ Citadel │
5│ Ruby Ann Boxcar │ Ruby Ann's Down Home Trailer P │ Citadel │
6│ Lynne Ann DeSpelder │ The Last Dance: Encountering D │ McGraw-Hil │
7│ Daniel Defoe │ Robinson Crusoe │ Ann Arbor │
8│ Ann Thwaite │ Waiting for the Party: The Lif │ David R. G │
9└─────────────────────┴────────────────────────────────┴────────────┘

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

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

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

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

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

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

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

موفق باشید.

میخوای همیشه بروز باشی؟

کافیه خیلی ساده ایمیلت رو اینجا بدی و من به محض انتشار یه پست جدید بهت خبر میدم. راستی، ایمیل رو با کسی به اشتراک نمیذارم و قرار نیست اسپم دریافت کنی.

کپی‌رایت 2021، حقوق معنوی محفوظ هست، ولی میتونید با ذکر منبع مطالب رو منتشر کنید.
Link to $https://twitter.com/aientechLink to $https://you.aien.me/joinLink to $https://www.instagram.com/aientech/Link to $https://github.com/AienTech