ميزات SQLite التي ربما فاتتك

إذا كنت تستخدم SQLite ، ولكنك لا تتابع تطوره ، فربما تكون بعض الأشياء التي تجعل الكود أسهل ، وذهب الاستعلامات بشكل أسرع ، دون أن يلاحظها أحد. تحت الخفض ، حاولت سرد أهمها.

رموز جزئية (فهارس جزئية)

عند إنشاء فهرس ، يمكنك تحديد شرط لصف ليتم تضمينه في الفهرس ، على سبيل المثال ، أحد الأعمدة ليس فارغًا ، بينما الآخر يساوي القيمة المحددة.

create index idx_partial on tab1(a, b) where a is not null and b = 5;
select * from tab1 where a is not null and b = 5; --> search table tab1 using index

فهارس التعبير (فهارس التعبيرات)

إذا تم استخدام تعبير غالبًا في استعلامات جدول ، فيمكنك إنشاء فهرس عليه. ومع ذلك ، يجب أن يؤخذ في الاعتبار أنه في حين أن المحسن ليس مرنًا للغاية ، فإن تبديل الأعمدة في التعبير سيؤدي إلى التخلي عن استخدام الفهرس.

create index idx_expression on tab1(a + b);
select * from tab1 where a + b > 10; --> search table tab1 using index ...
select * from tab1 where b + a > 10; --> scan table

عمود محسوب (أعمدة مُنشأة)

إذا كانت بيانات العمود هي نتيجة تقييم تعبير في أعمدة أخرى ، يمكنك إنشاء عمود افتراضي. هناك نوعان: VIRTUAL (يتم حسابه في كل مرة يتم فيها قراءة الجدول ولا يشغل مساحة) و STORED (يتم حسابه عند كتابة البيانات على الجدول ويشغل مساحة). بالطبع ، لا يمكنك كتابة البيانات مباشرة إلى هذه الأعمدة.

create table tab1 (
	a integer primary key,
	b int,
	c text,
	d int generated always as (a * abs(b)) virtual,
	e text generated always as (substr(c, b, b + 1)) stored
);

مؤشر R-Tree

الفهرس مخصص للبحث السريع في مجموعة من القيم / تداخل الكائنات ، أي المهام النموذجية للأنظمة الجغرافية ، عندما يتم تحديد الكائنات المستطيلة من خلال موضعها وحجمها ويكون مطلوبًا للعثور على جميع الكائنات التي تتقاطع مع الكائن الحالي. يتم تنفيذ هذا الفهرس كجدول افتراضي (انظر أدناه) وهذا فهرس في جوهره فقط. لدعم فهرس R-Tree ، تحتاج إلى إنشاء SQLite بعلامة SQLITE_ENABLE_RTREE(لم يتم تحديدها افتراضيًا).

create virtual table idx_rtree using rtree (
	id,              -- 
	minx, maxx,      --   c x 
	miny, maxy,      --   c y 
	data             --    
);  

insert into idx_rtree values (1, -80.7749, -80.7747, 35.3776, 35.3778); 
insert into idx_rtree values (2, -81.0, -79.6, 35.0, 36.2);

select id from idx_rtree 
where minx >= -81.08 and maxx <= -80.58 and miny >= 35.00  and maxy <= 35.44;

إعادة تسمية عمود

يدعم SQLite التغييرات في بنية الجداول بشكل سيئ ، لذلك ، بعد إنشاء جدول ، لا يمكنك تغيير قيد أو إسقاط عمود. منذ الإصدار 3.25.0 ، يمكنك إعادة تسمية عمود ، ولكن لا يمكنك تغيير نوعه.

alter table tbl1 rename column a to b;

بالنسبة للعمليات الأخرى ، يُقترح أيضًا كل شيء لإنشاء جدول بالهيكل المطلوب ، ونقل البيانات هناك ، وحذف القديم وإعادة تسمية الجديد.

أضف سطرًا ، أو التحديث (Upsert)

باستخدام فئة on conflictعامل التشغيل insert، يمكنك إضافة صف جديد ، وإذا كان لديك بالفعل صف بنفس القيمة بالمفتاح ، فقم بتحديثه.

create table vocabulary (word text primary key, count int default 1);
insert into vocabulary (word) values ('jovial') 
  on conflict (word) do update set count = count + 1;

التحديث من البيان

إذا كان الصف بحاجة إلى التحديث استنادًا إلى بيانات من جدول آخر ، فحينئذٍ كان عليك استخدام استعلام فرعي لكل عمود أو with. منذ الإصدار 3.33.0 ، تم updateتمديد عامل التشغيل بكلمة رئيسية fromويمكنك الآن القيام بذلك

update inventory
   set quantity = quantity - daily.amt
  from (select sum(quantity) as amt, itemid from sales group by 2) as daily
 where inventory.itemid = daily.itemid;

استعلامات CTE ، فئة مع (تعبير جدول عام)

withيمكن استخدام الفصل كتمثيل مؤقت لطلب. في الإصدار 3.34.0 ، تم الإعلان عن إمكانية استخدامه withبالداخل with.

with tab2 as (select * from tab1 where a > 10), 
  tab3 as (select * from tab2 inner join ...)
select * from tab3;

مع إضافة كلمة أساسية recursive، withيمكن استخدامها للاستعلامات حيث تريد العمل على البيانات ذات الصلة.

--  
with recursive cnt(x) as (
  values(1) union all select x + 1 from cnt where x < 1000
)
select x from cnt;

--         
create table tab1 (id, parent_id);
insert into tab1 values 
  (1, null), (10, 1), (11, 1), (12, 10), (13, 10),
  (2, null), (20, 2), (21, 2), (22, 20), (23, 21);

--    
with recursive tc (id) as (
	select id from tab1 where id = 10	
	union 
	select tab1.id from tab1, tc where tab1.parent_id = tc.id
)

--      
with recursive tc (id, parent_id) as (
	select id, parent_id from tab1 where id in (12, 21)
	union 
	select tc.parent_id, tab1.parent_id 
	from tab1, tc where tab1.id = tc.parent_id
)
select distinct id from tc where parent_id is null order by 1;

--    , .   
create table org(name text primary key, boss text references org);
insert into org values ('Alice', null), 
  ('Bob', 'Alice'), ('Cindy', 'Alice'), ('Dave', 'Bob'), 
  ('Emma', 'Bob'), ('Fred', 'Cindy'), ('Gail', 'Cindy');

with recursive
  under_alice (name, level) as (
    values('Alice', 0)
    union all
    select org.name, under_alice.level + 1
      from org join under_alice on org.boss = under_alice.name
     order by 2
  )
select substr('..........', 1, level * 3) || name from under_alice;

وظيفة النافذة (وظائف النافذة)

منذ الإصدار 3.25.0 ، أصبحت وظائف النافذة ، التي تسمى أحيانًا الوظائف التحليلية ، متاحة في SQLite ، مما يتيح لك إجراء العمليات الحسابية على جزء من البيانات (نافذة).

--    
create table tab1 (x integer primary key, y text);
insert into tab1 values (1, 'aaa'), (2, 'ccc'), (3, 'bbb');
select x, y, row_number() over (order by y) as row_number from tab1 order by x;

--     
create table tab1 (a integer primary key, b, c);
insert into tab1 values (1, 'A', 'one'),
  (2, 'B', 'two'), (3, 'C', 'three'), (4, 'D', 'one'), 
  (5, 'E', 'two'), (6, 'F', 'three'), (7, 'G', 'one');

--        
select a, b, group_concat(b, '.') over (order by a rows between 1 preceding and 1 following) as prev_curr_next from tab1;

--    (,   c)       
select c, a, b, group_concat(b, '.') over (partition by c order by a range between current row and unbounded following) as curr_end from tab1 order by c, a;

--      
select c, a, b, group_concat(b, '.') filter (where c <> 'two') over (order by a) as exceptTwo from t1 order by a;

أدوات سكليتي

بالإضافة إلى sqlite3 CLI ، تتوفر أداتان إضافيتان . الأول - sqldiff ، يسمح لك بمقارنة قواعد البيانات (أو جدول منفصل) ليس فقط بالهيكل ، ولكن أيضًا بالبيانات. الثانية ، sqlite3_analizer ، تستخدم لعرض معلومات حول كيفية استخدام المساحة بشكل فعال بواسطة الجداول والفهارس في ملف قاعدة البيانات. يمكن الحصول على معلومات مماثلة من الجدول الظاهري dbstat (يتطلب علامة SQLITE_ENABLE_DBSTAT_VTABعند تجميع SQLite).



اعتبارًا من 3.22.0 ، يحتوي sqlite3 CLI على أمر (تجريبي) .expert يمكنه إخبارك بالفهرس الذي يجب إضافته للاستعلام الذي يتم إدخاله.

إنشاء فراغ في النسخ الاحتياطي

منذ الإصدار 3.27.0 ، تم vacuumتمديد الأمر بكلمة أساسية intoتسمح لك بإنشاء نسخة من قاعدة البيانات دون إيقافها مباشرة من SQL. إنه بديل بسيط لـ Backup API .

vacuum into 'D:/backup/' || strftime('%Y-%M-%d', 'now') || '.sqlite';

وظيفة Printf

الوظيفة مماثلة للدالة C. في هذه الحالة، NULLيتم تفسير -values باعتبارها سلسلة فارغة ل %sو 0للنائب رقم.

select 'a' || ' 123 ' || null; --> null
select printf('%s %i %s', 'a', 123, null); --> 123 a
select printf('%s %i %i', 'a', 123, null); --> 123 a 0

الوقت و التاريخ

في SQLite DateوTime . على الرغم من أنه من الممكن إنشاء جدول بأعمدة من هذه الأنواع ، إلا أنه سيكون مماثلاً لإنشاء أعمدة دون تحديد نوع ، وبالتالي يتم تخزين البيانات في هذه الأعمدة كنص. يعد هذا مناسبًا عند عرض البيانات ، لكن له عددًا من العيوب: البحث غير الفعال ، إذا لم يكن هناك فهرس ، فإن البيانات تشغل مساحة كبيرة ، ولا توجد منطقة زمنية. لتجنب ذلك ، يمكنك تخزين البيانات كوقت unix ، أي عدد الثواني منذ منتصف ليل 1/1/1970.

select strftime('%Y-%M-%d %H:%m', 'now'); --> UTC 
select strftime('%Y-%M-%d %H:%m', 'now', 'localtime'); -->  
select strftime('%s', 'now'); --  Unix- 
select strftime('%s', 'now', '+2 day'); -->  unix-   
--  unix-     - 21-11-2020 15:25:14
select strftime('%d-%m-%Y %H:%M:%S', 1605961514, 'unixepoch', 'localtime')

جسون

منذ الإصدار 3.9.0 ، يمكنك العمل مع json في SQLite (يلزم وجود علامة SQLITE_ENABLE_JSON1وقت الترجمة أو ملحق محمل). يتم تخزين بيانات Json كنص. نتيجة الوظائف هي أيضًا نص.

select json_array(1, 2, 3); --> [1,2,3] ()
select json_array_length(json_array(1, 2, 3)); --> 3
select json_array_length('[1,2,3]'); --> 3
select json_object('a', json_array(2, 5), 'b', 10); --> {"a":[2,5],"b":10} ()
select json_extract('{"a":[2,5],"b":10}', '$.a[0]');  --> 2
select json_insert('{"a":[2,5]}', '$.c', 10); --> {"a":[2,5],"c":10} ()
select value from json_each(json_array(2, 5)); --> 2  2, 5
select json_group_array(value) from json_each(json_array(2, 5)); --> [2,5] ()

البحث عن نص كامل

مثل json ، يتطلب البحث عن النص الكامل وضع علامة SQLITE_ENABLE_FTS5عند تجميع أو تحميل ملحق. للعمل مع البحث ، يتم أولاً إنشاء جدول افتراضي بحقول مفهرسة ، ثم يتم تحميل البيانات هناك باستخدام الجدول المعتاد insert. يجب أن يؤخذ في الاعتبار أن الامتداد من أجل تشغيله ينشئ جداول إضافية ويستخدم الجدول الافتراضي الذي تم إنشاؤه بياناتها.

create virtual table emails using fts5(sender, body);
SELECT * FROM emails WHERE emails = 'fts5'; -- sender  body  fts5

ملحقات

يمكن إضافة قدرات سكليتي من خلال وحدات قابلة للتحميل. سبق ذكر بعضها أعلاه - json1 و fts .



يمكن استخدام الامتدادات لإضافة وظائف معرّفة من قبل المستخدم (ليس فقط الوظائف العددية ، مثل ، على سبيل المثال crc32، ولكن أيضًا التجميع أو حتى الإطارات ) والجداول الافتراضية. الجداول الافتراضية هي جداول موجودة في قاعدة البيانات ، ولكن تتم معالجة بياناتها بواسطة الامتداد ، بينما ، اعتمادًا على التنفيذ ، يتطلب بعضها إنشاء

create virtual table temp.tab1 using csv(filename='thefile.csv');
select * from tab1;

يمكن استخدام الآخرين ، الذين يطلق عليهم اسم الجدول ، على الفور.

select value from generate_series(5, 100, 5);

...

بعض الجداول الافتراضية مذكورة هنا .



يمكن أن يقوم ملحق واحد بتنفيذ كل من الوظائف والجداول الافتراضية. على سبيل المثال ، يحتوي json1 على 13 عددًا و 2 دالة مجمعة وجدولين افتراضيين json_eachو json_tree. لكتابة وظيفتك الخاصة ، تحتاج فقط إلى معرفة أساسية بـ C وتحليل كود الامتداد من مستودع SQLite . يعد تنفيذ الجداول الافتراضية الخاصة بك أكثر تعقيدًا قليلاً (من الواضح أن هذا هو سبب وجود القليل منها). هنا يمكن أن نوصي قليلا كتاب عفا عليها الزمن عن طريق سكليتي التي كتبها جاي A. Kreibich ، المقال من قبل مايكل أوينز ، قالب من مستودع، و generate_series كود وظائف ذات قيم الجدول.



بالإضافة إلى ذلك ، يمكن أن تنفذ الإضافات أشياء خاصة بنظام التشغيل ، مثل نظام الملفات ، لتوفير إمكانية النقل. يمكن العثور على التفاصيل هنا .

منوعات

• استخدم '(علامة اقتباس مفردة) لثوابت السلسلة و "(علامة اقتباس مزدوجة) لأسماء الأعمدة والجدول.
• للحصول على معلومات حول الجدول tab1 يمكنك استخدام
  
  
  
  

  --  main 
  select * from pragma_table_info('tab1');
  --  temp    (attach) 
  select * from pragma_table_info('tab1') where schema = 'temp'

• تمتلك SQLite منتدى رسميًا خاصًا بها ، حيث يشارك منشئ SQLite - ريتشارد هيب ، وحيث يمكنك نشر تقرير خطأ.
  
  
• محررات سكليتي: SQLite Studio ، DB Browser لـ SQLite و (ads!) Sqlite-gui (Windows فقط).