🧘🏾 ⤵️ 📲 SQLite - एक महान एंबेडेड डेटाबेस (भाग 3) 🤾🏻 😩 🤹🏽

पहला भाग एक परिचय है ।
दूसरा भाग एक त्वरित शुरुआत है ।

तीसरा भाग - सूक्ष्मता और विशेषताएं।

यह हिस्सा सभी प्रकार के SQLite फीचर्स का एक हॉजपेज है। मैंने यहां (मेरी राय में) सबसे महत्वपूर्ण विषयों को एकत्र किया है, जिसके बिना SQLite निर्वाण को समझना असंभव है।

चूंकि, फिर से, बहुत सारी जानकारी है, लेख का प्रारूप इस प्रकार होगा: एक दिलचस्प विषय के लिए एक छोटा सा परिचय और घर की साइट के लिए एक लिंक, जहां विवरण हैं। साइट, अफसोस, अंग्रेजी में है।

बहुपरत अनुप्रयोगों में SQLite का उपयोग करना

SQLite को एकल-थ्रेडेड संस्करण (संकलन पैरामीटर SQLITE_THREADSAFE = 0 ) में बनाया जा सकता है।

इस प्रकार में, इसे कई धाराओं से एक साथ उपयोग नहीं किया जा सकता है, क्योंकि सिंक्रनाइज़ेशन कोड पूरी तरह से अनुपस्थित है। क्यों? उन्मत्त गति के लिए।

आप जाँच कर सकते हैं कि sqlite3_threadsafe () को कॉल करके मल्टीथ्रेडिंग है या नहीं: यदि यह 0 देता है, तो यह सिंगल-थ्रेडेड SQLite है।

डिफ़ॉल्ट रूप से, SQLite को थ्रेडिंग समर्थन (sqlite3.dll) के साथ संकलित किया गया है।

बहु-थ्रेडेड SQLite का उपयोग करने के दो तरीके हैं: क्रमबद्ध और बहु-थ्रेड।

सीरियलाइज्ड (आपको कनेक्शन खोलते समय SQLITE_OPEN_FULLMUTEX ध्वज निर्दिष्ट करना होगा)। इस मोड में, थ्रेड्स किसी भी तरह से SQLite कॉल को खींच सकते हैं, कोई प्रतिबंध नहीं। लेकिन सभी कॉल एक दूसरे को ब्लॉक करते हैं और क्रमिक रूप से सख्ती से संसाधित होते हैं।

बहु-सूत्र ( SQLITE_OPEN_NOMUTEX )। इस मोड में, आप एक साथ कई थ्रेड्स से एक ही कनेक्शन का उपयोग नहीं कर सकते हैं (लेकिन इसे अलग-अलग थ्रेड्स द्वारा एक ही समय में अलग-अलग कनेक्शन का उपयोग करने की अनुमति है)। आमतौर पर इस विधा का उपयोग किया जाता है।

और जानें

डेटा प्रारूप

SQLite डेटाबेस UTF-8 या UTF-16 में डेटा को संग्रहीत (पाठ) कर सकता है।

एपीआई कॉल के सेट में कॉल शामिल होते हैं जो UTF-8 ( sqlite3_XXX ) और कॉल जो UTF-16 ( sqlite3_XXX16 ) प्राप्त करते हैं।

यदि इंटरफ़ेस का डेटा प्रकार और कनेक्शन मेल नहीं खाता है, तो मक्खी पर रूपांतरण किया जाता है।

हमेशा यूटीएफ -8 का उपयोग करें।

यूनिकोड का समर्थन

डिफ़ॉल्ट रूप से, कोई समर्थन नहीं है। आपको sqlite3_create_collation के माध्यम से अपना कॉलेशन (तुलना विधि) बनाने की आवश्यकता है।
और www.sqlite.org/c3ref/create_function.html के माध्यम से अपने अंतर्निहित कार्यों (), ऊपरी (), निचले () को परिभाषित करें ।

इस तरह की एक परियोजना है "यूनिकोड के लिए अंतर्राष्ट्रीय घटक" , आईसीयू।

और कुछ SQLite DLL को पहले से ही बना रहे हैं ।

SQLite में ICU का उपयोग कैसे करें।

एक हब पर एक लेख आया

डेटा प्रकार और मूल्य तुलना

जैसा कि पहले ही उल्लेख किया गया है, SQLIte आपको किसी भी कॉलम में कोई भी मान लिखने की अनुमति देता है।

डेटाबेस के अंदर का मूल्य निम्नलिखित भंडारण प्रकारों में से एक हो सकता है:
शून्य,
INTEGER (1,2,3,4,6 या 8 बाइट्स लेता है),
वास्तविक (अस्थायी बिंदु संख्या, IEEE प्रारूप में 8 बाइट्स),
पाठ (डेटाबेस डेटा प्रारूप में स्ट्रिंग, आमतौर पर UTF-8),
BLOB (जैसा कि बाइनरी डेटा संग्रहीत है)।

विभिन्न प्रकारों के मूल्यों का क्रम क्रम:
- कम से कम (एक और NULL सहित);
- INTEGER और REAL किसी TEXT और BLOB से कम हैं , उनकी तुलना अंकगणित से की जाती है;
- पाठ किसी भी BLOB से कम है, उनकी तुलना उनके टकराव के आधार पर की जाती है;
- BLOBs एक दूसरे के साथ memcmp () के माध्यम से तुलना की जाती है।

SQLite कई स्थानों पर मक्खी पर निहित प्रकार के रूपांतरण करता है:
- कॉलम में मान दर्ज करते समय (कॉलम का प्रकार रूपांतरण के लिए सिफारिश सेट करता है);
- एक दूसरे के साथ मूल्यों की तुलना करते समय।

एक कॉलम में निम्नलिखित टाइपकास्ट सिफारिशें हो सकती हैं: TEXT , NUMERIC , INTEGER , REAL , NONE ।

BLOB और NULL मान हमेशा किसी भी "जैसा है" कॉलम में दर्ज किया जाता है।

TEXT कॉलम में, TEXT मान "जैसा है" दर्ज किया जाता है, INTEGER और REAL के मान पंक्तियाँ बन जाते हैं।
NUMERIC , INTEGER कॉलम में, संख्याएं "जैसा है" लिखी जाती हैं, और पंक्तियाँ नंबर बन जाती हैं अगर _mogut_ (यानी, "दोषरहित" रूपांतरण की अनुमति है)।
एक वास्तविक कॉलम के लिए, नियम INTEGER ( NUMERIC ) के समान हैं; अंतर यह है कि सभी नंबर फ्लोटिंग पॉइंट फॉर्मेट में हैं।
NONE कॉलम में, मानों को "जैसा है" दर्ज किया गया है (यह प्रकार डिफ़ॉल्ट रूप से उपयोग किया जाता है, जब तक कि अन्यथा निर्दिष्ट न हो)।

जब एक दूसरे के साथ विभिन्न प्रकारों के मूल्यों की तुलना करते हैं, तो अतिरिक्त प्रकार का रूपांतरण किया जा सकता है।

किसी संख्या की स्ट्रिंग से तुलना करते समय, यदि स्ट्रिंग को "दोषरहित" संख्या में परिवर्तित किया जा सकता है, तो यह एक संख्या बन जाती है।

और जानें

मैं यहां ध्यान देता हूं कि एक अद्वितीय सूचकांक में SQLite में किसी भी संख्या में NULL मान हो सकते हैं (Oracle सहमत हैं और MS SQL सहमत नहीं है)।

इन-मेमोरी डेटाबेस

यदि sqlite3_open () कॉल में आप फ़ाइल नाम को ": मेमोरी:" पास करते हैं , तो SQLite मेमोरी में नए (स्वच्छ) डेटाबेस से एक कनेक्शन बनाएगा।

उपयोग तर्क के अनुसार फ़ाइल में डेटाबेस से कनेक्शन के लिए यह कनेक्शन बिल्कुल अप्रभेद्य है: SQL कमांड का एक ही सेट उपलब्ध है।

~~काश, स्मृति में एक ही डेटाबेस के लिए दो कनेक्शन खोलने का कोई तरीका नहीं है।~~

UPD: पहले से ही, यह पता चला है, आप मेमोरी में एक डेटाबेस में दो कनेक्शन खोल सकते हैं।

rc = sqlite3_open("file:memdb1?mode=memory&cache=shared", &db);

 ATTACH DATABASE 'file:memdb1?mode=memory&cache=shared' AS aux1;

और जानें

एक साथ कई डेटाबेस में शामिल हो रहे हैं

डेटाबेस से कनेक्शन खोलने के लिए, sqlite3_open () कॉल का उपयोग करें।

किसी भी समय, हम SQL ATTACH DATABASE कमांड के माध्यम से एक खुले कनेक्शन के लिए 10 और डेटाबेस तक संलग्न कर सकते हैं ।

 sqlite3_open('foo.sqlite3', &db); //       "foo.sqlite3" sqlite3_exec(&db, "ATTACH 'bar.sqlite3' AS bar", ... ); //  "bar.sqlite3"

अब db1.sqlite3 फ़ाइल में सभी डेटाबेस टेबल पारदर्शी रूप से हमारे कनेक्शन में उपलब्ध हो गए हैं।

नाम संघर्षों को हल करने के लिए, आपको अनुलग्नक नाम का उपयोग करना चाहिए (मुख्य आधार को "मुख्य" कहा जाता है):

 SELECT * FROM main.my_table UNION SELECT * FROM bar.my_table

कुछ भी आपको डेटाबेस में मेमोरी में एक नया डेटाबेस संलग्न करने और कैशिंग के लिए उपयोग करने से रोकता है, आदि।

 sqlite3_open('foo.sqlite3', &db); //       "foo.sqlite3" sqlite3_exec(&db, "ATTACH ':memory:' AS mem", ... ); //

और जानें

यह एक बहुत ही उपयोगी सुविधा है। संलग्न डेटाबेस में मुख्य डेटाबेस के समान डेटा प्रारूप होना चाहिए, अन्यथा एक त्रुटि।

अस्थायी डेटाबेस

Sqlite3_open () में फ़ाइल नाम के बजाय एक खाली स्ट्रिंग पास करें और डिस्क पर फ़ाइल में एक अस्थायी डेटाबेस बनाया जाएगा। इसके अलावा, डेटाबेस से कनेक्शन बंद करने के बाद, इसे डिस्क से हटा दिया जाएगा।

PRAGMA कमांड के माध्यम से डेटाबेस की फाइन ट्यूनिंग

PRAGMA SQL कमांड का उपयोग कनेक्शन या डेटाबेस के लिए सभी प्रकार की सेटिंग्स को सेट करने के लिए किया जाता है:

  PRAGMA name; //     name PRAGMA name = value; //   name  value

कनेक्शन सेटअप (स्पष्ट रूप से) खोलने के तुरंत बाद और उपयोग से पहले किया जाना चाहिए।

सभी मापदंडों का एक पूरा विवरण यहां है ।

मैं सबसे महत्वपूर्ण चीजों पर ध्यान केन्द्रित करूंगा।

 PRAGMA page_size = bytes; //   ;   -       ,       (  4096) PRAGMA cache_size = -kibibytes; //      ,     2000   PRAGMA encoding = "UTF-8"; //   ,   UTF-8 PRAGMA foreign_keys = 1; //   foreign keys,   -  PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF; //   , .  PRAGMA synchronous = 0 | OFF | 1 | NORMAL | 2 | FULL; //   , .

लॉग इन करें और लेनदेन करें

इसलिए हम इस विषय पर आते हैं, जिसकी महारत आपको तुरंत SQLite के मास्टर के तीसरे स्तर पर ले जाती है।

SQLite डेटाबेस में डेटा की अखंडता ( ACID ) की सावधानीपूर्वक निगरानी करता है, लेनदेन के माध्यम से डेटा को संशोधित करने के लिए एक तंत्र को लागू करता है ।

लेन-देन के बारे में संक्षेप में: एक लेनदेन या तो पूरी तरह से लुढ़का हुआ है या पूरी तरह से लुढ़का हुआ है। कोई मध्यवर्ती राज्य नहीं हो सकता है।

यदि आप लेन-देन का स्पष्ट रूप से उपयोग नहीं करते हैं ( BEGIN; ... COMMIT; ) ; एक अंतर्निहित लेनदेन हमेशा बनाया जाता है । यह कमांड निष्पादित करने से पहले शुरू होता है और इसके तुरंत बाद शुरू होता है।

इसलिए, संयोग से, और SQLite के "सुस्ती" के बारे में शिकायतें। SQLite प्रति सेकंड 50 हजार रिकॉर्ड भी डाल सकता है, लेकिन यह प्रति सेकंड ~ 50 से अधिक लेनदेन रिकॉर्ड नहीं कर सकता है।

यही कारण है कि एक अंतर्निहित लेनदेन का उपयोग करके जल्दी से रिकॉर्ड डालना संभव नहीं है।

डिफ़ॉल्ट सेटिंग्स पर, SQLite डेटाबेस की अखंडता की गारंटी देता है, भले ही ऑपरेशन के दौरान बिजली बंद हो।

यह अद्भुत व्यवहार लॉगिंग (एक विशेष फ़ाइल) और डिस्क में परिवर्तन को सिंक्रनाइज़ करने के लिए सरल तंत्र द्वारा प्राप्त किया जाता है।

डेटाबेस में डेटा को संक्षिप्त रूप से अपडेट करना इस प्रकार है:

- डेटाबेस के किसी भी संशोधन से पहले, SQLite एक अलग फ़ाइल (लॉग) में डेटाबेस से संशोधित पृष्ठों को बचाता है, अर्थात, यह बस उन्हें वहां कॉपी करता है;
- यह सुनिश्चित करना कि पृष्ठों की एक प्रति बनाई गई है, SQLite डेटाबेस को बदलना शुरू करता है;
- यह सुनिश्चित करना कि डेटाबेस में सभी परिवर्तन "डिस्क पर पहुंच गए" और डेटाबेस पूर्ण हो गया है, SQLite लॉग को मिटा देता है।

लेनदेन तंत्र की परमाणुता का विस्तार से वर्णन यहाँ किया गया है ।

यदि SQLite डेटाबेस से एक कनेक्शन खोलता है और देखता है कि लॉग पहले से ही है, तो यह पता चलता है कि डेटाबेस अपूर्ण स्थिति में है और स्वचालित रूप से अंतिम लेन-देन वापस कर देता है।

यही है, विफलताओं के बाद डेटाबेस पुनर्प्राप्ति तंत्र, वास्तव में, SQLite में बनाया गया है और उपयोगकर्ता के लिए मूल काम करता है।

डिफ़ॉल्ट रूप से, लॉग DELETE मोड में है।

 PRAGMA journal_mode = DELETE

इसका मतलब यह है कि लेनदेन पूरा होने के बाद लॉग फ़ाइल को हटा दिया जाता है। इस मोड में लॉग फ़ाइल होने के तथ्य का मतलब है कि SQLite के लिए लेन-देन पूरा नहीं हुआ था, डेटाबेस को पुनर्स्थापित करने की आवश्यकता है। लॉग फ़ाइल में डेटाबेस फ़ाइल का नाम होता है जिसमें "-journal" जोड़ा जाता है।

TRUNCATE मोड में, लॉग फ़ाइल को शून्य पर काट दिया जाता है (कुछ सिस्टम पर यह फ़ाइल को हटाने की तुलना में तेज़ी से काम करता है)।

PERSIST मोड में , लॉग फ़ाइल की शुरुआत शून्य से भरी हुई है (जबकि इसका आकार नहीं बदलता है और यह बहुत अधिक स्थान ले सकता है)।

मेमोरी मोड में, लॉग फ़ाइल को मेमोरी में रखा जाता है और यह जल्दी से काम करता है, लेकिन विफलताओं के मामले में डेटाबेस रिकवरी की गारंटी नहीं देता है (डिस्क पर डेटा की कोई प्रतिलिपि नहीं है)।

या आप पूरी तरह से जर्नल को निष्क्रिय कर सकते हैं ( PRAGMA journal_mode = OFF )। इस स्थिति में, लेन-देन रोलबैक ( रोलबैक आदेश) काम करना बंद कर देता है और प्रोग्राम क्रैश होने पर डेटाबेस खराब होने की संभावना है।

स्मृति में डेटाबेस के लिए, लॉग मोड केवल मेमोरी या ऑफ़ हो सकता है।

थोड़ा पीछे चलते हैं। SQLite कैसे सुनिश्चित करता है कि डेटाबेस हमेशा पूरा होगा?

हम जानते हैं कि आधुनिक सिस्टम प्रदर्शन को बेहतर बनाने के लिए सरल कैशिंग का उपयोग करते हैं और डिस्क पर लिखने में देरी कर सकते हैं।

मान लें कि SQLite ने डेटाबेस में लिखना समाप्त कर दिया है और लेनदेन करने के तथ्य को इंगित करने के लिए लॉग फ़ाइल को मिटाना चाहता है।

यदि डेटाबेस अपडेट होने से पहले फ़ाइल मिटा दी जाती है तो क्या होगा?

यदि इस अवधि के दौरान बिजली बंद हो जाती है, तो लॉग नहीं होगा, और डेटाबेस अभी तक पूरा नहीं होगा - डेटा हानि!

संक्षेप में, सरल परिवर्तन प्रतिबद्ध तंत्र को डिस्क सिस्टम और ओएस से कुछ गारंटी पर भरोसा करना चाहिए।

PRAGMA तुल्यकालिक इस प्रभाव के लिए "व्यामोह" SQLite की डिग्री निर्धारित करता है।

ऑफ मोड (या 0) का अर्थ है: SQLite मानता है कि डेटा डिस्क पर तुरंत तय हो गया है क्योंकि इसे OS पर स्थानांतरित कर दिया गया है (अर्थात, संगत OS API कॉल करने के तुरंत बाद)।

इसका मतलब है कि एप्लिकेशन क्रैश होने की स्थिति में अखंडता की गारंटी है (जैसा कि ओएस काम करना जारी रखता है), लेकिन ओएस क्रैश या पावर आउटेज की स्थिति में नहीं।

NORMAL सिंक्रोनाइज़ेशन मोड (या 1) OS क्रैश और लगभग सभी पावर आउटेज के दौरान अखंडता सुनिश्चित करता है। एक गैर-शून्य मौका है कि अगर बिजली सबसे अधिक अपर्याप्त समय पर खो जाती है , तो आधार बिगड़ जाएगा। यह प्रदर्शन और विश्वसनीयता के मामले में एक तरह का माध्यम, समझौता मोड है।

पूर्ण मोड हमेशा और हर जगह और किसी भी दुर्घटना में अखंडता की गारंटी देता है। लेकिन यह काम करता है, ज़ाहिर है, अधिक धीरे-धीरे, क्योंकि कुछ जगहों पर अपेक्षा के ठहराव किए जाते हैं। और यह डिफ़ॉल्ट मोड है।

तो, केवल वाल जैसी पत्रिका का विषय ही नहीं रह गया।

वाल लॉग मोड

डिफ़ॉल्ट रूप से, डेटाबेस लॉग मोड हमेशा DELETE को "रिटर्न" करता है। मान लीजिए कि हमने डेटाबेस से कनेक्शन खोला और मोड को PERSIST में सेट किया। डेटा बदल दिया, कनेक्शन बंद कर दिया।

डिस्क पर एक लॉग फ़ाइल है (जिसकी शुरुआत शून्य है)।

डेटाबेस से फिर से कनेक्शन खोलें। यदि आप इस संबंध में लॉग मोड सेट नहीं करते हैं, तो यह फिर से DELETE में काम करेगा। जैसे ही हम डेटा अपडेट करते हैं, ट्रांजेक्शन कम मैकेनिज्म लॉग फाइल को मिटा देगा।

वाल लॉग मोड अलग तरह से काम करता है - यह "लगातार" है। जैसे ही हम डेटाबेस को वाल मोड में डालते हैं, यह इस मोड में रहेगा जब तक कि यह स्पष्ट रूप से दूसरे लॉग मोड में परिवर्तित नहीं हो जाता।

तो इसकी आवश्यकता क्यों है?

प्रारंभ में, SQLite को एक एम्बेडेड डेटाबेस के रूप में डिज़ाइन किया गया था। डेटा तक एक साथ पहुंच साझा करने की वास्तुकला आदिम थी: एक ही समय में कई कनेक्शन डेटाबेस पढ़ सकते हैं, लेकिन केवल एक कनेक्शन एक निश्चित समय पर लिख सकता है। यह, कम से कम, इसका मतलब है कि लेखन कनेक्शन डेटाबेस के पाठकों से "जारी" होने की प्रतीक्षा कर रहा है। जब "व्यस्त" डेटाबेस में लिखने की कोशिश की जाती है, तो एप्लिकेशन को SQLITE_BUSY त्रुटि ( SQL के साथ भ्रमित नहीं होना चाहिए!)। फ़ाइल लॉकिंग एपीआई के माध्यम से पहुंच साझा करने के लिए यह तंत्र हासिल किया गया है (जो नेटवर्क ड्राइव पर अच्छी तरह से काम नहीं करता है, इसलिए SQLite की सिफारिश नहीं की जाती है; और जानें );

वाल ( राइट-अहेड लॉगिंग ) मोड में, डेटाबेस के "रीडर" और डेटाबेस में "लेखक" अब एक-दूसरे के साथ हस्तक्षेप नहीं करते हैं, अर्थात, पढ़ते समय डेटा को संशोधित किया जा सकता है। संक्षेप में, यह बड़े और गंभीर डीबीएमएस की दिशा में एक कदम है, जिसमें सब कुछ है। यह भी तर्क दिया जाता है कि वाल में SQLite तेज है।

लेकिन इसके नुकसान भी हैं:
- ओएस से कुछ अतिरिक्त निश्तिकी की आवश्यकता होती है (यूनिक्स और विंडोज में ये निश्तिकी हैं);
- डेटाबेस कई फाइलें लेता है (फाइलें "XXX-वाल" और "XXX-shm" );
- यह बड़े लेनदेन पर अच्छा काम नहीं करता है (सशर्त रूप से, यदि लेनदेन 50 एमबी से अधिक है);
- आप रीड-ओनली मोड में ऐसा डेटाबेस नहीं खोल सकते हैं;
- एक अतिरिक्त चेकपॉइंट ऑपरेशन होता है।

वास्तव में, वाल मोड में, डेटाबेस डेटा डेटाबेस और लॉग फ़ाइल के बीच साझा किया जाता है। चेकपॉइंट ऑपरेशन डेटाबेस में डेटा स्थानांतरित करता है। डिफ़ॉल्ट रूप से, यह स्वचालित रूप से किया जाता है यदि लॉग ने 1000 डेटाबेस पृष्ठ ले लिए हैं।
यही है, तेजी से कमियां हैं और अचानक कुछ कमिट ने इस बारे में सोचा और चेकपॉइंट करना शुरू कर दिया। यदि यह व्यवहार अवांछनीय है, तो आप मैन्युअल रूप से एक चेकपॉइंट कर सकते हैं (जब सब कुछ शांत होता है), आप इसे एक अलग प्रक्रिया में कर सकते हैं।

की सीमा

इसके लघुकरण के बावजूद, वास्तव में SQLite फ़ील्ड, टेबल या डेटाबेस के आकार पर गंभीर प्रतिबंध नहीं लगाता है।

डिफ़ॉल्ट रूप से, एक BLOB या एक स्ट्रिंग मान 1 GB पर कब्जा कर सकता है और यह एक रिकॉर्ड के लिए समान आकार की सीमा है (आप इसे 2 ^ 31 - 1, SQLITE_MAX_LENGTH पैरामीटर के लिए बढ़ा सकते हैं)।

स्तंभों की संख्या: 2000 (32767, SQLITE_MAX_COLUMN तक बढ़ाई जा सकती है)।

SQL कथन का आकार: 1 MB (1073741824 बाइट्स, SQLITE_MAX_SQL_LENGTH)।

एक साथ शामिल हों: 64 टेबल।

कनेक्शन को आधार संलग्न करें: 10 (62 तक, SQLITE_MAX_ATTACHED)

डेटाबेस में अधिकतम पृष्ठ: 1073741823 (2147483646, SQLITE_MAX_PAGE_COUNT तक)।

यदि आप पृष्ठ का आकार 65,636 बाइट्स पर सेट करते हैं, तो अधिकतम डेटाबेस का आकार लगभग 14 टेराबाइट्स होगा।

तालिका में प्रविष्टियों की अधिकतम संख्या 2 ^ 64 - 1 है, लेकिन व्यवहार में, निश्चित रूप से, आकार सीमा पहले आएगी।

अधिक जानें sqlite.org/limits.html

UDP: SQLite ऑप्टिमाइज़ेशन लिंक्स: 1 2 android-1 android-2

जारी रखा जाए

SQLite - एक महान एंबेडेड डेटाबेस (भाग 3)