SQLite फ़ाइलें समझें: संरचना, उपयोग के मामले और मुख्य सीमाएं

एक SQLite फ़ाइल एक सिंगल डिस्क फ़ाइल होती है जिसमें पूरा रिलेशनल डेटाबेस — टेबल, इंडेक्स और स्कीमा एक साथ होते हैं। SQLite का अपना दस्तावेज़ इसे "मुख्य डेटाबेस फ़ाइल" कहता है और नोट करता है कि "एक SQLite डेटाबेस की पूरी स्थिति आमतौर पर" इसी में समाहित होती है। इस वाक्य में "आमतौर पर" शब्द वास्तव में बहुत महत्वपूर्ण भूमिका निभा रहा है।

यदि आपको कभी कोई .db, .sqlite, या .sqlite3 फ़ाइल सौंपी गई है और आपने सोचा है कि क्या आपको पूरा डेटा मिला है, तो यह वह फ़ॉर्मेट है जिसे ठीक से समझना ज़रूरी है।

वास्तव में एक SQLite फ़ाइल क्या है

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

इसके दो परिणाम होते हैं। पहला, डेटाबेस एक सिंगल आर्टिफ़ैक्ट के रूप में यात्रा करता है, यही वजह है कि बहुत सारे एप्लिकेशन अपने डेटा को इसी तरह शिप करते हैं। दूसरा, फ़ॉर्मेट को बेहद स्थिर होना चाहिए, क्योंकि ये फ़ाइलें उन्हें लिखने वाले सॉफ़्टवेयर की तुलना में अधिक समय तक जीवित रहती हैं। SQLite अपनी मुख्य विशेषताओं में "स्थिर, स्थायी फ़ाइल फ़ॉर्मेट" को सूचीबद्ध करता है। यह यह भी बताता है कि कोड पब्लिक डोमेन में है, "किसी भी उद्देश्य के लिए उपयोग करने के लिए स्वतंत्र है, चाहे वह व्यावसायिक हो या निजी।"

इसके पैमाने को कम आंकना आसान है। SQLite का अपना 'अबाउट' पेज कहता है कि यह "दुनिया में सबसे व्यापक रूप से तैनात किया जाने वाला डेटाबेस है जिसके एप्लिकेशन हमारी गिनती से भी अधिक हैं।"

फ़ाइल के अंदर क्या है

100-बाइट का हेडर

पहले बाइट्स फ़ॉर्मेट की पहचान करते हैं। ऑफ़सेट 0 पर, फ़ाइल में 16-बाइट की हेडर स्ट्रिंग होती है: SQLite format 3\000। यही वह सिग्नेचर है जिससे टूल फ़ाइल को पहचानते हैं, चाहे उसका एक्सटेंशन कुछ भी हो।

अगला फ़ील्ड दिखने से कहीं अधिक मायने रखता है। ऑफ़सेट 16 पर एक 2-बाइट का इंटीजर होता है जिसमें "बाइट्स में डेटाबेस पेज का आकार" होता है। दस्तावेज़ में कहा गया है कि यह "512 और 32768 के बीच दो की घात होना चाहिए, या 65536 के पेज आकार का प्रतिनिधित्व करने वाला मान 1 होना चाहिए।" हेडर में सभी मल्टीबाइट फ़ील्ड सबसे महत्वपूर्ण बाइट को पहले रखते हुए संग्रहीत किए जाते हैं।

ऑफ़सेट 18 और 19 पर दो और बाइट्स आते हैं: फ़ाइल फ़ॉर्मेट का राइट वर्शन और रीड वर्शन। दस्तावेज़ में उल्लेख है कि यह मान "लेगेसी के लिए 1; WAL के लिए 2" है।

पेज, रो नहीं

हेडर के नीचे फ़ाइल निश्चित आकार के पेजों का एक स्टैक होती है। स्पेसिफिकेशन स्पष्ट है: "मुख्य डेटाबेस फ़ाइल में एक या अधिक पेज होते हैं। एक पेज का आकार 512 और 65536 के बीच दो की घात होता है। एक ही डेटाबेस के भीतर सभी पेज समान आकार के होते हैं।"

पेजों को 1 से नंबर दिया जाता है, और अधिकतम पेज संख्या 4,294,967,294 है। टेबल और इंडेक्स उन पेजों के भीतर B-tree संरचनाओं के रूप में रहते हैं, यही वजह है कि एक टेक्स्ट एडिटर आपको बहुत कम दिखाता है।

साइडकार फ़ाइलें जिनका कोई उल्लेख नहीं करता

यह वह हिस्सा है जो लोगों को उलझन में डाल देता है। दस्तावेज़ कहता है कि पूरी स्थिति "आमतौर पर" एक फ़ाइल में होती है। फिर यह अपवाद का नाम लेता है। एक ट्रांज़ैक्शन के दौरान, SQLite "एक दूसरी फ़ाइल में अतिरिक्त जानकारी संग्रहीत करता है जिसे 'rollback journal' कहा जाता है।" WAL मोड में वह दूसरी फ़ाइल एक write-ahead log होती है।

इसलिए जब एप्लिकेशन लिखने के बीच में हो, तब ली गई कॉपी में वह कमिटेड डेटा गायब हो सकता है जो अभी भी साइडकार में मौजूद है। यदि कोई सहकर्मी आपको केवल एक .db फ़ाइल भेजता है और कुछ नहीं, और संख्याएँ थोड़ी पुरानी लगती हैं, तो सबसे पहले इसी चीज़ की जाँच करनी चाहिए।

SQLite फ़ाइल को कैसे खोलें

इसके तीन तरीके हैं, और सही तरीका इस बात पर निर्भर करता है कि आप आगे क्या करना चाहते हैं।

इसे एक व्यूअर के साथ पढ़ें। डेस्कटॉप और ब्राउज़र-आधारित SQLite व्यूअर फ़ाइल को खोलते हैं, टेबल को सूचीबद्ध करते हैं, और आपको रो पर क्लिक करने की अनुमति देते हैं। "आखिर इसमें क्या है" इसका उत्तर पाने का यह सबसे तेज़ तरीका है, और आमतौर पर पहली नज़र के लिए यह पर्याप्त होता है।

कमांड लाइन या लाइब्रेरी के साथ इसे क्वेरी करें। sqlite3 शेल और Python, Node और अधिकांश अन्य भाषाओं में मानक लाइब्रेरी बाइंडिंग सीधे इस फ़ॉर्मेट को पढ़ते हैं। यह वह तरीका है जब आप पहले से ही स्कीमा जानते हैं और एक विशिष्ट संख्या चाहते हैं।

एक टेबल को एक्सपोर्ट करें और कहीं और उसका विश्लेषण करें। एक टेबल को CSV में डंप करें और इसे अपनी टीम द्वारा पहले से उपयोग किए जाने वाले किसी भी टूल में ले जाएं। आप टेबल के बीच के संबंधों को खो देते हैं, जो कि एकमात्र ऐसी चीज़ थी जिसे यह फ़ॉर्मेट सुरक्षित रख रहा था। जहाँ तक संभव हो, रॉ टेबल के बजाय जॉइन किए गए परिणाम को एक्सपोर्ट करें।

कोई टूल यह क्यों कह सकता है कि फ़ाइल एक डेटाबेस नहीं है

स्पेसिफिकेशन इसे स्पष्ट करता है। प्रत्येक वैध फ़ाइल 16-बाइट की हेडर स्ट्रिंग, SQLite format 3\000 के साथ शुरू होती है। एक रीडर जो फ़ाइल खोलता है और ऑफ़सेट 0 पर उस सिग्नेचर को नहीं पाता है, उसे SQLite डेटाबेस नहीं सौंपा गया है।

अधिकांश मामलों में तीन सामान्य कारण होते हैं। फ़ाइल अधूरी ट्रांसफर हुई, इसलिए हेडर तो है लेकिन बाकी हिस्सा कट गया है। फ़ाइल किसी एप्लिकेशन द्वारा एन्क्रिप्टेड या रैप की गई है, इसलिए पहले बाइट्स कुछ और हैं। या एक्सटेंशन भ्रामक है, और जो आपको वास्तव में मिला वह एक साधारण एक्सपोर्ट था जिसे किसी ने मदद करने के लिए रीनेम कर दिया था।

एक SQLite फ़ाइल कितनी बड़ी हो सकती है

आमतौर पर इस सवाल से जितना अंदाज़ा लगाया जाता है, उससे कहीं बड़ी। SQLite का लिमिट्स पेज बताता है कि डेटाबेस फ़ाइल का अधिकतम आकार 4,294,967,294 पेज है। 65,536 बाइट्स के अधिकतम पेज आकार पर, यह लगभग 281 टेराबाइट का अधिकतम डेटाबेस आकार है।

यह पेज उस आंकड़े के बारे में बेहद ईमानदार है। यह नोट करता है कि ऊपरी सीमा "अपरीक्षित है क्योंकि डेवलपर्स के पास इस सीमा तक पहुँचने में सक्षम हार्डवेयर की सुविधा नहीं है।"

रो की संख्या भी इसी सीमा से बंधी है। सैद्धांतिक अधिकतम सीमा एक टेबल में 2^64 रो है। दस्तावेज़ बताता है कि यह सीमा "अप्राप्य है क्योंकि 281 टेराबाइट का अधिकतम डेटाबेस आकार पहले ही पूरा हो जाएगा।"

व्यावहारिक काम के लिए उपयोगी सीख किसी सीमा के विपरीत है। यदि कोई आपको एक .db फ़ाइल सौंपता है और चेतावनी देता है कि यह बड़ी है, तो यह फ़ॉर्मेट लगभग निश्चित रूप से आपको नहीं रोकेगा। फ़ाइल बनाते समय चुना गया पेज आकार, और क्या इसमें इंडेक्स शामिल हैं, किसी भी प्रलेखित सीमा की तुलना में आपके अनुभव को कहीं अधिक प्रभावित करेगा।

आपको SQLite फ़ाइलें कहाँ मिलती हैं

• एप्लिकेशन एक्सपोर्ट। डेस्कटॉप और मोबाइल ऐप अक्सर इतिहास, सेटिंग्स और मैसेज लॉग को एक SQLite फ़ाइल में संग्रहीत करते हैं जिसे आप कॉपी कर सकते हैं।
• एनालिटिक्स हैंडऑफ़। इंजीनियर डेटाबेस एक्सेस देने के बजाय एक फ़ाइल के रूप में स्नैपशॉट शिप करते हैं।
• डिवाइस और टेलीमेट्री। एम्बेडेड सिस्टम स्थानीय रूप से लिखते हैं क्योंकि बात करने के लिए कोई सर्वर नहीं होता है।
• अभिलेखागार। फ़ॉर्मेट की दीर्घकालिक स्थिरता इसे उन डेटासेट के लिए एक आम विकल्प बनाती है जिन्हें वर्षों तक पठनीय रहना चाहिए।
• ब्राउज़र और टूलिंग इंटरनल्स। कई स्थानीय टूल इस तरह से स्थिति को बनाए रखते हैं, यही वजह है कि यह एक्सटेंशन सपोर्ट टिकटों में दिखाई देता है।

WAL और जर्नल फ़ाइलें किस लिए होती हैं

हो सकता है कि आपने एक .db फ़ाइल कॉपी की हो और उसके बगल में एक -wal या -journal फ़ाइल पाई हो। ये वे साइडकार हैं जिनका स्पेसिफिकेशन वर्णन करता है, और इन्हें डिलीट करने से लोग डेटा खो देते हैं।

रोलबैक जर्नल पुराना तंत्र है। किसी पेज को बदलने से पहले, SQLite उस पेज के मूल वर्शन को जर्नल में लिखता है। यदि राइट बाधित होता है, तो मूल को वापस रखा जा सकता है, यही वह चीज़ है जो क्रैश होने पर भी ट्रांज़ैक्शन को सुरक्षित रखती है।

राइट-अहेड लॉग इस व्यवस्था को उलट देता है। बदलाव पहले लॉग में जाते हैं और मुख्य फ़ाइल बाद में अपडेट की जाती है। हेडर यह फ़्लैग करता है कि डेटाबेस किस मोड में है। ऑफ़सेट 18 पर फ़ाइल फ़ॉर्मेट का राइट वर्शन "लेगेसी के लिए 1; WAL के लिए 2" है।

व्यावहारिक नियम सीधे पूरी स्थिति वाले वाक्य से आता है। मान लीजिए कि डेटाबेस WAL मोड में है और कोई आपको केवल मुख्य फ़ाइल सौंपता है। सबसे हाल के कमिटेड बदलाव अभी भी उस लॉग में हो सकते हैं जो आपको नहीं मिला।

सो जब आपको कोई डेटाबेस फ़ाइल दी जाए, तो दो प्रश्न पूछें। क्या कॉपी लेते समय एप्लिकेशन ठीक से बंद किया गया था, और क्या इसके साथ कुछ और भी आया था? दोनों उत्तर आमतौर पर हाँ होते हैं, और जिस एक बार वे नहीं होते हैं, वही वह समय होता है जब संख्याएँ प्रोडक्शन डेटा से चुपचाप मेल नहीं खाती हैं।

SQLite फ़ाइल बनाम CSV बनाम Parquet

	SQLite फ़ाइल	CSV	Parquet
आकार (Shape)	कई टेबल, एक फ़ाइल	एक टेबल, एक फ़ाइल	एक टेबल, एक फ़ाइल या फ़ोल्डर
प्रकार (Types)	डेटा के साथ संग्रहीत	रीडर द्वारा अनुमानित	डेटा के साथ संग्रहीत
संबंध (Relationships)	कुंजियों और इंडेक्स के माध्यम से सुरक्षित	खो जाते हैं	खो जाते हैं
मानव-पठनीय	नहीं	हाँ	नहीं
क्वेरी करने के लिए लिखे गए	हाँ, SQL के साथ	नहीं	हाँ, एनालिटिक्स इंजन द्वारा
सामान्य विफलता	जर्नल या WAL साइडकार का गायब होना	प्रकार और डिलीमीटर का अनुमान लगाना	टूलचेन सपोर्ट

यदि आप इन फ़ॉर्मेट के साथ नियमित रूप से काम करते हैं, तो Parquet फ़ाइलों और TSV फ़ाइलों पर हमारे व्याख्यात्मक लेख इन दोनों के लिए समान विषयों को कवर करते हैं।

टीमें इस फ़ॉर्मेट को क्यों चुनती हैं

चलाने के लिए कुछ नहीं। क्योंकि SQLite में "कोई अलग सर्वर प्रोसेस नहीं होती है," इसलिए हैंडऑफ़ एक प्रोविज़निंग टिकट के बजाय केवल एक फ़ाइल कॉपी है।

प्रकार सुरक्षित रहते हैं। एक डेट कॉलम डेट के रूप में ही पहुँचता है। जिसने भी किसी CSV रीडर को एक आइडेंटिफ़ायर को साइंटिफिक नोटेशन में बदलते देखा है, वह इसके मूल्य को समझता है।

संबंध भी सुरक्षित रहते हैं। कई संबंधित टेबल एक ही आर्टिफ़ैक्ट में एक साथ रहते हैं, इसलिए वे जॉइन्स जो डेटा को सार्थक बनाते हैं, अभी भी उपलब्ध रहते हैं।

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

जानने योग्य सीमाएँ

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

पेज का आकार निर्माण के समय तय होता है। डेटाबेस का प्रत्येक पेज समान आकार का होता, और वह आकार हेडर में दर्ज होता है। आप इसे एक बार चुनते हैं।

फिर से वही साइडकार नियम। कोई भी कॉपी, बैकअप या अपलोड रूटीन जो केवल मुख्य फ़ाइल को लेता है, वह जर्नल या राइट-अहेड लॉग में मौजूद चीज़ों को छोड़ सकता है।

अपारदर्शिता। एक SQLite फ़ाइल को उस तरह से सरसरी तौर पर नहीं देखा जा सकता जैसे CSV को देखा जा सकता है। इसे पढ़ने के लिए एक टूल की आवश्यकता होती, और यही वह बाधा है जो बहुत सारे विश्लेषणों को रोक देती है।

SQLite फ़ाइल से उत्तर कैसे प्राप्त करें

पारंपरिक तरीका एक क्लाइंट इंस्टॉल करना, फ़ाइल खोलना, स्कीमा सीखना और SQL लिखना शुरू करना है। यह तब ठीक है जब आप पहले से ही टेबल जानते हैं। लेकिन यह तब धीमा होता है जब आपको आज सुबह ही फ़ाइल सौंपी गई हो और दोपहर में मीटिंग हो।

छोटा तरीका सीधे सवाल पूछना है। Powerdrill Bloom आपको अपनी प्राकृतिक भाषा में डेटा के साथ काम करने की अनुमति देता है और इसके स्रोत के साथ उत्तर देता है। होमपेज वादा करता है कि "हर संख्या पेज, रो और उसके पीछे के आंकड़े के साथ वापस आती है।" वहाँ से वही वर्कस्पेस चार्ट, शीट या एक छोटा डेक तैयार कर सकता है।

यदि यह आपका नियमित वर्कफ़्लो है, तो दो संबंधित पेजों के बारे में जानना उपयोगी है। Chat with Database संरचित डेटा में संवादात्मक मार्ग को कवर करता है, और Text to SQL उस मामले को कवर करता है जहाँ आप स्वयं क्वेरी चाहते हैं। यदि आपका हैंडऑफ़ इसके बजाय एक फ़्लैट एक्सपोर्ट के रूप में आता है, तो CSV AI assistant पेज उस मार्ग को कवर करता है।

एक और बात जो हेडर आपको बताता है

चूंकि पेज का आकार एक निश्चित ऑफ़सेट पर रहता है, इसलिए आप किसी फ़ाइल को ठीक से खोलने से पहले उसके बारे में कुछ उपयोगी जान सकते हैं। 4,096-बाइट पेज के साथ बनाया गया डेटाबेस 65,536-बाइट पेजों के साथ बनाए गए डेटाबेस से अलग व्यवहार करता है। वह विकल्प एक बार चुना गया था, जब फ़ाइल बनाई गई थी।

यह कोई ऐसी संख्या नहीं है जिसे आप बाद में आसानी से बदल सकें। यह किसी सेटिंग के बजाय स्कीमा निर्णय की तरह ही एक महत्वपूर्ण निर्णय है।

निष्कर्ष

एक SQLite फ़ाइल एक ही आर्टिफ़ैक्ट में पूरा डेटाबेस होती है। इसमें 16-बाइट का सिग्नेचर, ऑफ़सेट 16 पर दर्ज पेज का आकार, और आपके टेबल और इंडेक्स ले जाने वाले निश्चित आकार के पेजों का एक स्टैक होता है। यह आसानी से पोर्टेबल है, अपने प्रकारों को सुरक्षित रखता है, और वर्षों तक पठनीय रहता है।

उस एक चेतावनी को याद रखें जिसके बारे में स्पेसिफिकेशन सावधान करता है। पूरी स्थिति आमतौर पर उसी फ़ाइल में होती है। एक ट्रांज़ैक्शन के दौरान, इसका एक हिस्सा इसके साथ रोलबैक जर्नल या राइट-अहेड लॉग में रहता है। कॉपी पर भरोसा करने से पहले साइडकार की जाँच करें।

जब आपके पास फ़ाइल हो और आपको स्कीमा के बजाय उत्तर की आवश्यकता हो, तो Powerdrill Bloom आज़माएं और सीधे डेटा से अपना प्रश्न पूछें।

अक्सर पूछे जाने वाले प्रश्न

.db, .sqlite, और .sqlite3 में क्या अंतर है?

संरचनात्मक रूप से कोई अंतर नहीं है। तीनों एक ही फ़ॉर्मेट के लिए पारंपरिक एक्सटेंशन हैं, और वास्तविक पहचानकर्ता फ़ाइल की शुरुआत में 16-बाइट की हेडर स्ट्रिंग SQLite format 3\000 है।

मुझे कैसे पता चलेगा कि SQLite फ़ाइल किस पेज आकार का उपयोग करती है?

यह हेडर में दर्ज होता है। ऑफ़सेट 16 पर एक 2-बाइट का इंटीजर बाइट्स में पेज का आकार रखता है। यह 512 और 32768 के बीच दो की घात होना चाहिए, या 65536 का प्रतिनिधित्व करने वाला मान 1 होना चाहिए।

क्या एक SQLite फ़ाइल संपूर्ण डेटाबेस है?

आमतौर पर, लेकिन हमेशा नहीं। दस्तावेज़ में कहा गया है कि एक ट्रांज़ैक्शन के दौरान SQLite अतिरिक्त जानकारी को रोलबैक जर्नल में रखता है। WAL मोड में वह जानकारी इसके बजाय राइट-अहेड लॉग में जाती है।

क्या मैं Excel में SQLite फ़ाइल खोल सकता हूँ?

सीधे नहीं, क्योंकि फ़ाइल टेक्स्ट की पंक्तियों के बजाय B-tree पेजों को संग्रहीत करती है। सामान्य तरीका पहले एक टेबल को CSV में एक्सपोर्ट करना है, या ऐसे टूल का उपयोग करना है जो डेटाबेस फ़ॉर्मेट को पढ़ता है और परिणाम देता है।

क्या SQLite व्यावसायिक रूप से उपयोग करने के लिए मुफ़्त है?

हाँ। SQLite बताता है कि इसका कोड पब्लिक डोमेन में है और "किसी भी उद्देश्य के लिए उपयोग करने के लिए मुफ़्त है, चाहे वह व्यावसायिक हो या निजी।"