SQLite में पहले STRICT टेबल का उपयोग करें

 (evanhahn.com)
1 पॉइंट द्वारा GN⁺ 3 시간 전 | 1 टिप्पणियां | WhatsApp पर शेयर करें
  • टेबल definition के अंत में STRICT जोड़ने से integer column में मनमाना text जाने जैसी type errors को जल्दी रोका जा सकता है, जिससे data integrity बेहतर होती है
  • insert और update के समय type check करते हुए भी '123' जैसी बिना नुकसान के convert हो सकने वाली values की अनुमति देता है, और column types INT, INTEGER, REAL, TEXT, BLOB, ANY तक सीमित रहते हैं
  • जिन columns में कई types रखने हों, उनके लिए ANY specify किया जा सकता है, जिससे एक ही table में strict validation और flexible storage दोनों लागू किए जा सकते हैं
  • मौजूदा table को सीधे STRICT में नहीं बदला जा सकता, इसलिए नई table बनाकर data copy करना जरूरी है; गलत मौजूदा data को साफ करना या type convert करना होगा
  • STRICT tables सिर्फ SQLite 3.37.0 या उससे ऊपर में supported हैं, और theoretical checking cost होती है, लेकिन unofficial experiments में performance या file size में कोई स्पष्ट अंतर नहीं दिखा

STRICT table बनाना

  • SQLite की STRICT tables दूसरे SQL engines की तरह अधिक strict type checking लागू करती हैं
  • CREATE TABLE definition के आखिर में STRICT लगाना होता है
CREATE TABLE people (name TEXT) STRICT;

insert और update की type checking

  • सामान्य SQLite tables में INTEGER column में भी 'garbage' जैसा text store किया जा सकता है, लेकिन STRICT table type mismatch को error मानती है
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage');
-- 정상 처리됨

CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage');
-- 오류: cannot store TEXT value in INTEGER column
  • यही validation UPDATE पर भी लागू होती है, इसलिए save करने के बाद value बदलते समय भी गलत type रोका जाता है
  • हालांकि, अगर value बिना loss के convert हो सकती है तो STRICT table में भी अनुमति मिलती है
    • string '123' पूरी तरह integer 123 में convert हो सकती है, इसलिए नीचे दिए दोनों inserts समान रूप से process होते हैं
INSERT INTO people_strict (age) VALUES ('123');
INSERT INTO people_strict (age) VALUES (123);

table definition चरण में type restrictions

  • सामान्य tables में SQLite द्वारा support न किए जाने वाले type names भी column declaration में इस्तेमाल किए जा सकते हैं
    • GARBAGE, DATETIME, JSON, UUID, BLOBB सभी स्वीकार किए जाते हैं
    • ऐसी declarations typo हो सकती हैं या SQLite द्वारा support किए गए data types को लेकर गलतफहमी से आ सकती हैं
  • STRICT table में unsupported type name इस्तेमाल करने पर creation चरण में error आता है
CREATE TABLE tbl (name GARBAGE) STRICT;
CREATE TABLE tbl (name DATETIME) STRICT;
CREATE TABLE tbl (name JSON) STRICT;
CREATE TABLE tbl (name UUID) STRICT;
CREATE TABLE tbl (name BLOBB) STRICT;
  • allowed column types सिर्फ INT, INTEGER, REAL, TEXT, BLOB, ANY हैं
  • हर column में type specify करना जरूरी है, इसलिए CREATE TABLE tbl (name) की तरह type omit नहीं किया जा सकता

ANY से flexibility बनाए रखें

  • अगर ऐसे data को store करना हो जिसका type fixed नहीं है, तो ANY column इस्तेमाल किया जा सकता है
  • STRICT table के अंदर भी ANY column में integer, text, real number, BLOB आदि सभी types store किए जा सकते हैं
CREATE TABLE tbl (value ANY) STRICT;

INSERT INTO tbl (value) VALUES (123);
INSERT INTO tbl (value) VALUES ('text');
INSERT INTO tbl (value) VALUES (12.34);
INSERT INTO tbl (value) VALUES (X'8647');

मौजूदा tables का migration

  • मौजूदा non-strict table को ALTER से सीधे STRICT table में बदलने का कोई तरीका नहीं है, इसलिए शुरुआत से ही strict बनाना आसान है
  • मौजूदा table को convert करने के लिए नई STRICT table बनाएं → data copy करें → पुरानी table replace करें प्रक्रिया अपनानी होगी
CREATE TABLE new_people (name TEXT) STRICT;
INSERT INTO new_people SELECT * FROM people;
DROP TABLE people;
ALTER TABLE new_people RENAME TO people;
  • अगर मौजूदा data के integer column में text पड़ा है जैसे गलत types हैं, तो copy process में error आ सकता है
    • migration से पहले data साफ करना या CAST से type conversion करना पड़ सकता है
  • केवल नई tables पर STRICT लागू करने की policy भी संभव है, लेकिन हर table की validation strength अलग हो जाएगी, जिससे सभी tables के loose होने की तुलना में behavior predict करना ज्यादा मुश्किल हो सकता है

जब flexible typing उपयुक्त हो

  • SQLite developers ने flexible typing के फायदे अलग से संकलित किए हैं और ऐसे cases बताए हैं जहां default behavior उपयोगी है
  • non-strict tables इन uses के लिए उचित हो सकती हैं
    • कई types को जस का तस रखने वाला pure key-value store
    • अलग-अलग types वाली miscellaneous properties रखने की जगह
    • messy CSV को सीधे import करते समय invalid values तक को बिना खोए preserve करना हो
  • लेकिन unexpected types subtle bugs पैदा कर सकते हैं, इसलिए सामान्य tables में उन्हें चुपचाप allow करने के बजाय तुरंत error देना बेहतर है
  • SQLite source में non-strict tables को legacy कहने वाला comment भी है, लेकिन इसे official documentation से अधिक भरोसेमंद आधार मानना मुश्किल है

version compatibility

  • STRICT tables को नवंबर 2021 में release हुए SQLite 3.37.0 में introduce किया गया था
  • पुराने SQLite versions में STRICT tables इस्तेमाल नहीं की जा सकतीं
  • पुराने versions STRICT table वाली database भी read नहीं कर सकते
    • latest SQLite में STRICT table बनाने के बाद उसी database को SQLite 3.36.0 से खोलने पर error आता है

performance और storage space

  • STRICT tables insert और update के समय data type की extra checking करती हैं, इसलिए theoretically वे धीमी हो सकती हैं
  • 100 columns वाली table में कई million rows insert करने वाले unofficial experiment में कई computers पर कोई स्पष्ट performance difference नहीं दिखा
  • disk पर database file size भी समान था, लेकिन यह rigorous benchmark नहीं था, इसलिए कुछ differences न पकड़े गए हों यह संभव है
  • column affinity के अनुरूप न होने वाली value गलती से store होने से रोककर performance उल्टा बेहतर हो सकती है, लेकिन इसे अलग से verify नहीं किया गया

अपनाते समय सोचने योग्य बातें

  • STRICT tables सभी data problems हल नहीं करतीं, लेकिन type-related mistakes कम करती हैं और data integrity मजबूत करती हैं
  • अधिकतर मामलों में table definition में सिर्फ STRICT जोड़ना होता है, इसलिए इसे लागू करना आसान है
  • सामान्य tables में strict type validation से मिलने वाले फायदे migration burden, पुराने versions से incompatibility और सीमित flexibility से बड़े हैं

संबंधित पढ़ाई

1 टिप्पणियां

 
GN⁺ 3 시간 전
Hacker News की राय

  • SQLite में किसी table को ALTER करके strict mode में बदलने की सुविधा नहीं है, इसलिए non-strict table का data strict table में copy करना पड़ता है—इसी से प्रेरित होकर sqlite-utils 4.1 में दोनों दिशाओं में conversion की सुविधा जोड़ी गई है
    CLI में इसे uvx sqlite-utils transform data.db mytable --strict से, और Python में db.table("mytable").transform(strict=True) से इस्तेमाल किया जा सकता है
    release notes https://sqlite-utils.datasette.io/en/stable/changelog.html#v..., Python API docs https://sqlite-utils.datasette.io/en/stable/python-api.html#..., और CLI docs https://sqlite-utils.datasette.io/en/stable/cli.html#transfo... पर देखे जा सकते हैं

  • https://sqlite.org/flextypegood.html यह समझाता है कि flexible typing default क्यों है और आगे भी शायद क्यों नहीं बदलेगा, लेकिन यह वास्तविक अनुभव से मेल नहीं खाता
    इसमें कहा गया है कि अगर ग्राहक का नाम integer type वाले Customer.creditScore में डाल दिया जाए, तो उसे आसानी से खोजकर ठीक किया जा सकता है; इसके उलट, corrupt हुई rows को recover करना मुश्किल होता है और data पूरी तरह गायब भी हो सकता है
    इस तर्क से भी बिल्कुल सहमत होना मुश्किल है कि strict type checking केवल आसानी से मिलने वाली errors को रोकती है और उल्टे bug detection और fixing को कठिन बना देती है

    • भरोसेमंद database configuration से मिलने वाली safety और trust ही data bugs को ठीक करना कठिन बना देती है—यह नजरिया समझना मुश्किल है
      Postgres में आमतौर पर जितनी संभव हों उतनी checks और safeguards जोड़ी जाती हैं, जिससे बाद में उन errors को ढूंढते फिरने की जरूरत नहीं पड़ती जो शुरू में ही नहीं होनी चाहिए थीं
    • MongoDB में भी ऐसा ही तर्क था कि उसमें कुछ भी store किया जा सकता है, लेकिन ज्यादातर लोगों ने असली इस्तेमाल के बाद समझा कि यह आम तौर पर खराब गुण है
      यह सख्त engineering principles से ज्यादा उस दौर और लेखक के मजबूत विश्वासों का परिणाम लगता है जिसमें SQLite बना था; काफी आलोचना के बाद भी किसी भी आधार पर अपनी position बनाए रखने जैसा impression देता है
      Postgres के JSON या HSTORE जैसे types से मनचाही flexibility दी जा सकती है, लेकिन unlimited typing को default के रूप में मजबूर करने के बजाय जरूरत पड़ने पर चुनने देना लगभग हमेशा बेहतर होता है

  • अच्छा होगा अगर STRICT default हो
    इस बात को छोड़ दें तो SQLite developer ने शानदार tool बनाया है और वे कमाल के व्यक्ति हैं

    • foreign keys तक default रूप से enabled नहीं होतीं, इसलिए PRAGMA foreign_keys = ON; इस्तेमाल करना पड़ता है
      बड़ी समस्या यह है कि strict tables के लिए ऐसा कोई equivalent pragma नहीं है, इसलिए हर CREATE TABLE में non-standard STRICT लगाना पड़ता है
      global STRICT pragma पर भी विचार हुआ था, लेकिन implement नहीं किया गया; संबंधित material https://sqlite.org/foreignkeys.html और https://sqlite.org/forum/forumpost/1b9d073a37ca5998 पर देखा जा सकता है
    • SQLite backward compatibility को महत्व देता है, इसलिए defaults शायद ही बदलता है
      मकसद यह है कि SQLite 3.53 के लिए बना software 3.54 पर upgrade होने के बाद CREATE TABLE अचानक strict tables बनाने लगे, errors दे और सब कुछ टूट जाए—ऐसी स्थिति से बचा जाए
    • foreign key constraints भी compatibility के कारण default रूप से disabled रहती हैं; ऐसी ही problem है
      कभी-कभी लगता है कि क्या एक समय SQLite सिर्फ foreign key syntax support करता था और असली functionality implement नहीं की थी
    • साधारण strings के बजाय proper date और timestamp types भी चाहिए
    • database को गलती से गलत type डालने की अनुमति देने की कोई वजह नहीं है, और SQLite की type safety philosophy निराश करती है
      हजारों devices पर deployed code boolean column में strings '1' और '0' store कर रहा था, जिन्हें साफ करना पड़ा—यह बिल्कुल मजेदार नहीं था
      timestamp type भी नहीं है, इसलिए उन्हें text columns में store करना पड़ता है, और standard date-time functions तक ISO format yyyy-mm-ddTHH:MM:SSZ के बजाय yyyy-mm-dd HH:MM:SS बनाते हैं, जिसके बारे में implicit assumption करना पड़ता है कि वह UTC है
      SQLite सचमुच शानदार project है, लेकिन कुछ design decisions उलझन में डालते हैं

  • enterprise SQL माहौल से आए व्यक्ति के तौर पर, सिर्फ इस वजह से SQLite को गंभीरता से नहीं लिया कि इसमें field types default रूप से enforce नहीं होते, और जब यह smartphone app metadata का आधार बना तब भी हैरानी हुई
    यह उस पुरानी networking कहानी की याद दिलाता है, जहाँ low latency और simplicity के कारण UDP चुना गया और फिर application में ही TCP की reliability features का ज़्यादातर हिस्सा दोबारा बनाया गया

    • खुद implement करने पर आप इसे TCP से अलग तरीके से बना सकते हैं और कभी-कभी बड़ा फायदा पा सकते हैं; QUIC और HTTP/3 इसके उदाहरण हैं
      लेकिन non-strict SQLite के ऊपर type checking खुद implement करने से वैसी ही ताकत मिलती नहीं दिखती
    • foreign keys 2009 से पहले बिल्कुल supported नहीं थीं और आज भी default रूप से enabled नहीं हैं: https://sqlite.org/foreignkeys.html
    • कभी-कभी ऐसे approach को असली business में सफल भी बनाया जा सकता है: https://aeron.io/
    • अगर type checking पूरी तरह supported नहीं है, तो SQLite को गंभीरता से न लेने वाला रवैया समझ में आता है, लेकिन ज़्यादातर databases भी perfect default settings नहीं देते
      PostgreSQL के defaults भी low-spec systems को target करते हैं, इसलिए optimal performance के लिए configuration चाहिए
      साथ ही TCP stream-based है, इसलिए कई applications में HOL blocking की समस्या होती है; अगर आपने UDP के ऊपर reliability layer खुद बनाई हो, तो काफी काम के बाद भी TCP से बेहतर नतीजा मिल सकता है
    • अगर आपको Jeep या Bronco में रुचि है, तो आप शायद कोई general car reviewer नहीं ढूँढेंगे
      वे उन्हें noisy और खराब handling वाला बताते हैं, क्योंकि वे ऐसे product को, जो शुरू से अलग use case के लिए बना है, ऐसे देखते हैं मानो सिर्फ उनका अपना use case ही मायने रखता हो

  • strict tables की कमी यह है कि Date जैसे कुछ types इस्तेमाल नहीं किए जा सकते, लेकिन फिर भी उन्हें default होना चाहिए
    अगर कई applications database share कर रही हैं, तो declared types पर भरोसा किया जा सकना चाहिए; अगर एक application numeric column में string store कर दे, तो बाकी सब बिगड़ जाते हैं
    दूसरी ओर SQLite का मुख्य use case, यानी embedded database, आम तौर पर सिर्फ एक application इस्तेमाल करती है, इसलिए नया database बनाकर copy करने के बजाय schema evolve कर पाने का फायदा भी है
    ऐसे में application code को पता होता है कि हर column से क्या expect करना है, mixed types सहित

    • SQLite में INTEGER, TEXT, BLOB, REAL, NUMERIC के सिर्फ 5 data types हैं: https://sqlite.org/datatype3.html
    • SQLite में date data type नहीं है, और किसी arbitrary SELECT query पर EXPLAIN call करके औपचारिक रूप से specified type name हासिल करने का भी कोई तरीका नहीं है
      इसलिए DATE या DATETIME जैसे नाम लिखने पर भी उसे infer तक नहीं किया जा सकता
    • Date कोई वास्तविक type नहीं है; उससे बस numeric affinity वाला column बनता है

  • strict mode column type notation को सीमित कर देता है, जिससे ज्यादा meaningful names इस्तेमाल नहीं हो पाते, और database types व application types को map करते समय code उन names का उपयोग नहीं कर पाता; इसलिए यह उल्टे application layer के अधिक strict type system में बाधा डालता है
    ज़्यादा जानकारी https://hn.algolia.com/?query=chrismorgan+strict+sqlite&type... पर है, और अगर आप Rust की sqlx जैसी crate से database handle करते हैं, तो strict mode से बचना बेहतर लगता है

  • databases और RPC में मैं strict types चाहता हूँ, लेकिन SQLite का use case थोड़ा अलग है; सीधे इस्तेमाल करने पर शायद https://sqlite.org/flextypegood.html बेहतर समझ में आए
    उदाहरण के लिए, SQLite के लिए न लिखे गए arbitrary scripts भी संयोग से काम कर जाएँ—यह लक्ष्य ऐसा feature है जिसे दूसरे DBMS आम तौर पर consider नहीं करते

    • उस document के pros/cons वाले हिस्से में मुख्य तर्क छूट गया है
      flexible typing का मुख्य फायदा यह है कि schema को आसानी से evolve किया जा सकता है
      embedded database की requirements बदलने पर हर बार नया database बनाकर data migrate करने के बजाय schema को in-place बदला जा सकता है, और अगर सिर्फ एक application पढ़-लिख रही है, तो integer column में string जुड़ जाने पर हैरानी की बात नहीं
      इसके उलट, अगर कई applications अपने-अपने schedule पर update होते हुए वही database share कर रही हैं, तो schema एक contract है, इसलिए एक गलत type भी दूसरे application को तोड़ सकता है
      अगर एक application द्वारा बनाई गई table का इस्तेमाल कोई दूसरी application करती है, तो सहमत data types का सख्ती से पालन होना चाहिए
    • अधिकतर मामलों में compile time पर जांचा जाता है कि code type structure का पालन करता है या नहीं, और साथ में develop हो रहा SQLite schema static analysis के लिए जरूरी type information देता है
      runtime पर फिर से check करने का कोई वास्तविक फायदा नहीं; static analysis ने जिस code को string डालते हुए verify किया है, वह अचानक mutate होकर integer नहीं डाल देगा
      SQLite, Postgres से अलग है, जहाँ कई applications data share करती हैं और third parties पर भरोसा करना पड़ता है
      Postgres जैसे environment में runtime validation जरूरी है, लेकिन SQLite मूल रूप से एक application और एक database के लिए design किया गया है, इसलिए compile time पर evaluate किए जा सकने वाले अपने code पर ही भरोसा करना काफी है
      कई applications द्वारा एक file share करने जैसी atypical situation में strict tables enable किए जा सकते हैं

  • dynamic data types simple key-value store जैसे use cases के लिए ठीक हो सकते हैं, लेकिन सवाल यह है कि INTEGER का बिना error के 'hello world' स्वीकार करना और ऐसी value डालने के लिए NONSTRICT जैसे keyword या ANY type को explicitly लिखना—इनमें से कौन सा principle of least surprise के ज्यादा करीब है
    ज़्यादातर SQLite users शायद expect नहीं करेंगे कि पहला संभव है

    • शायद इसलिए कि SQLite type-less database बनना चाहता था, फिर भी syntax को standard SQL जैसा दिखाना चाहता था
      यह उस explanation से भी मेल खाता है कि दूसरे DBMS के लिए लिखा code SQLite में भी संयोग से काम कर जाए
      फिर भी, जब user ने type छोड़ा भी नहीं बल्कि INTEGER explicitly लिखा हो, तब text डाल सकना बहुत surprising है

  • 08123… जैसी value से शुरू होने वाला UUID octal के रूप में parse हो गया था, जिससे उसका कुछ हिस्सा गलती से number में convert हो गया
    यह confusing और annoying था, लेकिन STRICT apply करके और पूरी table फिर से बनाकर हल कर लिया

    • वह SQLite नहीं, बल्कि language driver के जरूरत से ज्यादा मददगार होने का नतीजा था
      SQLite में octal type नहीं है

  • CREATE TABLE ... STRICT WITHOUT ROWID को default के रूप में इस्तेमाल करता हूँ, और इसे अलग तरह से बनाने की कोई खास वजह समझ नहीं आती