SQLite-UPSERT*नहीं*INSERT या स्थानांतरित करें




(10)

अगर कोई कॉर्डोवा में SQLite के लिए अपना समाधान पढ़ना चाहता है, तो मुझे उपरोक्त @ डेविड उत्तर के लिए यह जेनेरिक जेएस विधि धन्यवाद मिला।

function    addOrUpdateRecords(tableName, values, callback) {
get_columnNames(tableName, function (data) {
    var columnNames = data;
    myDb.transaction(function (transaction) {
        var query_update = "";
        var query_insert = "";
        var update_string = "UPDATE " + tableName + " SET ";
        var insert_string = "INSERT INTO " + tableName + " SELECT ";
        myDb.transaction(function (transaction) {
            // Data from the array [[data1, ... datan],[()],[()]...]:
            $.each(values, function (index1, value1) {
                var sel_str = "";
                var upd_str = "";
                var remoteid = "";
                $.each(value1, function (index2, value2) {
                    if (index2 == 0) remoteid = value2;
                    upd_str = upd_str + columnNames[index2] + "='" + value2 + "', ";
                    sel_str = sel_str + "'" + value2 + "', ";
                });
                sel_str = sel_str.substr(0, sel_str.length - 2);
                sel_str = sel_str + " WHERE NOT EXISTS(SELECT changes() AS change FROM "+tableName+" WHERE change <> 0);";
                upd_str = upd_str.substr(0, upd_str.length - 2);
                upd_str = upd_str + " WHERE remoteid = '" + remoteid + "';";                    
                query_update = update_string + upd_str;
                query_insert = insert_string + sel_str;  
                // Start transaction:
                transaction.executeSql(query_update);
                transaction.executeSql(query_insert);                    
            });
        }, function (error) {
            callback("Error: " + error);
        }, function () {
            callback("Success");
        });
    });
});
}

तो, पहले इस फ़ंक्शन के साथ कॉलम नाम चुनें:

function get_columnNames(tableName, callback) {
myDb.transaction(function (transaction) {
    var query_exec = "SELECT name, sql FROM sqlite_master WHERE type='table' AND name ='" + tableName + "'";
    transaction.executeSql(query_exec, [], function (tx, results) {
        var columnParts = results.rows.item(0).sql.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').split(','); ///// RegEx
        var columnNames = [];
        for (i in columnParts) {
            if (typeof columnParts[i] === 'string')
                columnNames.push(columnParts[i].split(" ")[0]);
        };
        callback(columnNames);
    });
});
}

फिर प्रोग्रामेटिक रूप से लेनदेन का निर्माण करें।

"मान" एक सरणी है जिसे आपको पहले बनाना चाहिए और यह उन पंक्तियों का प्रतिनिधित्व करता है जिन्हें आप तालिका में डालना या अपडेट करना चाहते हैं।

"रिमोटिड" आईडी है जिसे मैंने संदर्भ के रूप में उपयोग किया है, क्योंकि मैं अपने रिमोट सर्वर से समन्वयित कर रहा हूं।

SQLite कॉर्डोवा प्लगइन के उपयोग के लिए, कृपया आधिकारिक link देखें

http://en.wikipedia.org/wiki/Upsert

SQL सर्वर पर संग्रहीत प्रोसेस अद्यतन डालें

क्या SQLite में ऐसा करने का कोई चालाक तरीका है जिसे मैंने नहीं सोचा है?

मूल रूप से मैं रिकॉर्ड में मौजूद होने पर चार में से तीन कॉलम अपडेट करना चाहता हूं, यदि यह अस्तित्व में नहीं है तो मैं चौथे कॉलम के लिए डिफ़ॉल्ट (एनयूएल) मान के साथ रिकॉर्ड दर्ज करना चाहता हूं।

आईडी एक प्राथमिक कुंजी है इसलिए यूपीएसईआरटी के लिए केवल एक रिकॉर्ड होगा।

(मैं यह निर्धारित करने के लिए चयन के ऊपरी हिस्से से बचने की कोशिश कर रहा हूं कि मुझे अद्यतन करने या स्पष्ट रूप से INSERT की आवश्यकता है)

सुझाव?

मैं तालिका की रचना के लिए SQLite साइट पर Syntax की पुष्टि नहीं कर सकता। मैंने इसका परीक्षण करने के लिए डेमो नहीं बनाया है, लेकिन ऐसा लगता है कि यह समर्थित नहीं है ..

अगर ऐसा होता, तो मेरे पास तीन कॉलम होते हैं, इसलिए यह वास्तव में ऐसा दिखाई देगा:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    Blob1 BLOB ON CONFLICT REPLACE, 
    Blob2 BLOB ON CONFLICT REPLACE, 
    Blob3 BLOB 
);

लेकिन पहले दो ब्लब्स एक संघर्ष नहीं करेंगे, केवल आईडी होगा तो मैं ब्लॉब 1 और ब्लॉब 2 को प्रतिस्थापित नहीं किया जाएगा (वांछित के रूप में)

डेटा बाध्यकारी करते समय SQLite में अद्यतन एक पूर्ण लेनदेन है, जिसका अर्थ है कि प्रत्येक भेजी गई पंक्ति को अद्यतन करने के लिए आवश्यक है: INSERT के विपरीत बयान तैयार / चरण / अंतिम विवरण जो रीसेट फ़ंक्शन के उपयोग की अनुमति देता है

एक कथन वस्तु का जीवन इस तरह कुछ जाता है:

  1. Sqlite3_prepare_v2 () का उपयोग कर ऑब्जेक्ट बनाएं
  2. Sqlite3_bind_ इंटरफेस का उपयोग कर पैरामीटर होस्ट करने के लिए मानों को बाध्य करें।
  3. Sqlite3_step () को कॉल करके SQL चलाएं
  4. Sqlite3_reset () का उपयोग करके कथन को रीसेट करें, फिर चरण 2 पर वापस जाएं और दोहराएं।
  5. Sqlite3_finalize () का उपयोग कर कथन ऑब्जेक्ट को नष्ट करें।

अपडेट मैं अनुमान लगा रहा हूं आईएनएसईआरटी की तुलना में धीमी है, लेकिन यह प्राथमिक कुंजी का उपयोग करके SELECT से तुलना कैसे करता है?

शायद मुझे चौथे कॉलम (ब्लोब 3) को पढ़ने के लिए चयन का उपयोग करना चाहिए और फिर पहले 4 कॉलम के लिए नए डेटा के साथ मूल 4 वें कॉलम को मिलाकर एक नया रिकॉर्ड लिखने के लिए REPLACE का उपयोग करना चाहिए?


इस धागे को पढ़ने के बाद और निराश हो गया कि यह "यूपीएसईआरटी" आईएनजी के लिए आसान नहीं था, मैंने आगे की जांच की ...

आप वास्तव में SQLITE में इसे सीधे और आसानी से कर सकते हैं।

उपयोग करने के बजाय: INSERT INTO

उपयोग करें: अंदर INSERT OR REPLACE INTO

यह वही करता है जो आप करना चाहते हैं!


मुझे एहसास है कि यह एक पुराना धागा है लेकिन मैं देर से sqlite3 में काम कर रहा हूं और इस विधि के साथ आया हूं जो गतिशील रूप से पैरामीटरयुक्त क्वेरी उत्पन्न करने की मेरी आवश्यकताओं को बेहतर बनाता है:

insert or ignore into <table>(<primaryKey>, <column1>, <column2>, ...) values(<primaryKeyValue>, <value1>, <value2>, ...); 
update <table> set <column1>=<value1>, <column2>=<value2>, ... where changes()=0 and <primaryKey>=<primaryKeyValue>; 

यह अभी भी 2 प्रश्न हैं जहां अपडेट पर क्लॉज है लेकिन यह चाल चल रहा है। मेरे पास यह भी मेरे दृष्टिकोण में है कि एसकलाइट अपडेट स्टेटमेंट को पूरी तरह अनुकूलित कर सकता है अगर कॉल करने के लिए कॉल () शून्य से अधिक है। चाहे वह वास्तव में करता है या नहीं, यह मेरे ज्ञान से परे है, लेकिन एक आदमी सपना देख सकता है? ;)

बोनस प्वाइंट्स के लिए आप इस लाइन को जोड़ सकते हैं जो आपको पंक्ति की आईडी देता है चाहे वह एक नई डाली गई पंक्ति या मौजूदा पंक्ति हो।

select case changes() WHEN 0 THEN last_insert_rowid() else <primaryKeyValue> end;

मुझे पता है कि सबसे अच्छा तरीका एक अद्यतन करने के बाद, एक डालने के बाद है। "चयन का ओवरहेड" जरूरी है, लेकिन यह एक भयानक बोझ नहीं है क्योंकि आप प्राथमिक कुंजी पर खोज कर रहे हैं, जो तेज़ है।

आप जो भी चाहते हैं उसे करने के लिए आपको अपनी तालिका और फ़ील्ड नामों के साथ नीचे दिए गए बयानों को संशोधित करने में सक्षम होना चाहिए।

--first, update any matches
UPDATE DESTINATION_TABLE DT
SET
  MY_FIELD1 = (
              SELECT MY_FIELD1
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
 ,MY_FIELD2 = (
              SELECT MY_FIELD2
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
WHERE EXISTS(
            SELECT ST2.PRIMARY_KEY
            FROM
              SOURCE_TABLE ST2
             ,DESTINATION_TABLE DT2
            WHERE ST2.PRIMARY_KEY = DT2.PRIMARY_KEY
            );

--second, insert any non-matches
INSERT INTO DESTINATION_TABLE(
  MY_FIELD1
 ,MY_FIELD2
)
SELECT
  ST.MY_FIELD1
 ,NULL AS MY_FIELD2  --insert NULL into this field
FROM
  SOURCE_TABLE ST
WHERE NOT EXISTS(
                SELECT DT2.PRIMARY_KEY
                FROM DESTINATION_TABLE DT2
                WHERE DT2.PRIMARY_KEY = ST.PRIMARY_KEY
                );

यदि आप आमतौर पर अपडेट कर रहे हैं तो मैं ..

  1. एक लेनदेन शुरू करो
  2. अद्यतन करो
  3. पंक्ति गणना की जांच करें
  4. यदि यह 0 है तो सम्मिलित करें
  5. कमिट

यदि आप आम तौर पर आवेषण कर रहे होते हैं तो मैं करता हूं

  1. एक लेनदेन शुरू करो
  2. एक डालने का प्रयास करें
  3. प्राथमिक कुंजी उल्लंघन त्रुटि के लिए जाँच करें
  4. अगर हमें कोई त्रुटि मिलती है तो अपडेट करें
  5. कमिट

इस तरह आप चयन से बचें और आप स्क्लाइट पर लेनदेन से ध्वनि कर रहे हैं।


यह विधि इस प्रश्न के उत्तर में कुछ अन्य विधियों को रीमिक्स करती है और सीटीई (सामान्य तालिका अभिव्यक्तियों) के उपयोग को शामिल करती है। मैं क्वेरी पेश करूँगा और समझाऊंगा कि मैंने ऐसा क्यों किया जो मैंने किया था।

यदि कोई कर्मचारी 300 है तो मैं कर्मचारी 300 से डेविस के लिए अंतिम नाम बदलना चाहता हूं। अन्यथा, मैं एक नया कर्मचारी जोड़ूंगा।

तालिका का नाम: कर्मचारी कॉलम: आईडी, first_name, last_name

क्वेरी है:

INSERT OR REPLACE INTO employees (employee_id, first_name, last_name)
WITH registered_employees AS ( --CTE for checking if the row exists or not
    SELECT --this is needed to ensure that the null row comes second
        *
    FROM (
        SELECT --an existing row
            *
        FROM
            employees
        WHERE
            employee_id = '300'

        UNION

        SELECT --a dummy row if the original cannot be found
            NULL AS employee_id,
            NULL AS first_name,
            NULL AS last_name
    )
    ORDER BY
        employee_id IS NULL --we want nulls to be last
    LIMIT 1 --we only want one row from this statement
)
SELECT --this is where you provide defaults for what you would like to insert
    registered_employees.employee_id, --if this is null the SQLite default will be used
    COALESCE(registered_employees.first_name, 'SALLY'),
    'DAVIS'
FROM
    registered_employees
;

असल में, मैंने डिफ़ॉल्ट मान निर्धारित करने के लिए चयनित कथन का उपयोग करने की संख्या को कम करने के लिए सीटीई का उपयोग किया था। चूंकि यह एक सीटीई है, इसलिए हम केवल तालिका से इच्छित कॉलम का चयन करते हैं और INSERT कथन इसका उपयोग करता है।

अब आप तय कर सकते हैं कि आप कोल्स के फ़ंक्शन में, मूल्यों के साथ क्या होना चाहिए, नल को प्रतिस्थापित करके आप किस डिफ़ॉल्ट का उपयोग करना चाहते हैं।


सम्मिलित या स्थान "यूपीएसईआरटी" के बराबर नहीं है

मान लें कि मेरे पास फ़ील्ड आईडी, नाम और भूमिका के साथ कर्मचारी है:

INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")

बूम, आपने कर्मचारी संख्या 1 का नाम खो दिया है। SQLite ने इसे डिफ़ॉल्ट मान के साथ बदल दिया है।

यूपीएसईआरटी की अपेक्षित आउटपुट भूमिका को बदलने और नाम रखने के लिए होगी।


एरिक बी का जवाब ठीक है अगर आप मौजूदा पंक्ति से केवल एक या शायद दो स्तंभों को संरक्षित करना चाहते हैं। यदि आप बहुत सारे कॉलम को संरक्षित करना चाहते हैं, तो यह बहुत बोझिल हो जाता है।

यहां एक दृष्टिकोण है जो किसी भी तरफ कॉलम की किसी भी मात्रा में अच्छी तरह से स्केल करेगा। इसे स्पष्ट करने के लिए मैं निम्नलिखित स्कीमा मानूंगा:

 CREATE TABLE page (
     id      INTEGER PRIMARY KEY,
     name    TEXT UNIQUE,
     title   TEXT,
     content TEXT,
     author  INTEGER NOT NULL REFERENCES user (id),
     ts      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

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

मैं निम्नलिखित निर्माण के साथ एक सच्चे UPSERT प्राप्त करता हूं:

 WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) )
 INSERT OR REPLACE INTO page (id, name, title, content, author)
 SELECT old.id, new.name, new.title, old.content, new.author
 FROM new LEFT JOIN page AS old ON new.name = old.name;

इस क्वेरी का सटीक रूप थोड़ा भिन्न हो सकता है। नई मानों में मौजूदा पंक्ति में शामिल होने के लिए कुंजी बाएं बाहरी जुड़ने के साथ INSERT SELECT का उपयोग है।

यहां, यदि कोई पंक्ति पहले मौजूद नहीं थी, तो old.id होगा और SQLite तब स्वचालित रूप से एक आईडी असाइन करेगा, लेकिन यदि पहले से ही ऐसी पंक्ति थी, old.id का वास्तविक मान होगा और इसका पुन: उपयोग किया जाएगा। जो वही है जो मैं चाहता था।

वास्तव में यह बहुत लचीला है। ध्यान दें कि ts कॉलम सभी तरफ से पूरी तरह गायब है - क्योंकि इसमें एक DEFAULT मूल्य है, SQLite किसी भी मामले में सही काम करेगा, इसलिए मुझे इसकी देखभाल करने की ज़रूरत नहीं है।

आप new और old दोनों पक्षों पर एक कॉलम भी शामिल कर सकते हैं और फिर बाहरी SELECT में COALESCE(new.content, old.content) का उपयोग करने के लिए कहें, "यदि कोई था तो नई सामग्री डालें, अन्यथा पुरानी सामग्री रखें" - उदाहरण के लिए यदि आप एक निश्चित क्वेरी का उपयोग कर रहे हैं और प्लेसहोल्डर्स के साथ नए मान बाध्य कर रहे हैं।


share पर विस्तार से आप एक डमी 'सिंगलटन' तालिका (एक ही पंक्ति के साथ अपनी खुद की सृजन की एक तालिका) से चयन कर सकते हैं। यह कुछ नकल से बचाता है।

मैंने MySQL और SQLite में पोर्टेबल उदाहरण भी रखा है और एक उदाहरण के रूप में एक 'date_added' कॉलम का उपयोग किया है कि आप केवल पहली बार कॉलम कैसे सेट कर सकते हैं।

 REPLACE INTO page (
   id,
   name,
   title,
   content,
   author,
   date_added)
 SELECT
   old.id,
   "about",
   "About this site",
   old.content,
   42,
   IFNULL(old.date_added,"21/05/2013")
 FROM singleton
 LEFT JOIN page AS old ON old.name = "about";

एरिस्टोटल पागाल्टज़िस और एरिक बी के उत्तर से कोलेसेज के विचार के बाद, यहां केवल कुछ कॉलम अपडेट करने या पूर्ण पंक्ति डालने का एक अपरिवर्तनीय विकल्प है यदि यह अस्तित्व में नहीं है।

इस मामले में, कल्पना करें कि शीर्षक और सामग्री को अपडेट किया जाना चाहिए, जब मौजूदा नहीं हो तो अन्य पुराने मानों को रखें और नाम न मिलने पर आपूर्ति किए गए हों:

नोट id को इंसर्ट होने पर मजबूर होना पड़ता है क्योंकि इसे ऑटोइनक्रिकमेंट माना जाता है। यदि यह केवल एक जेनरेट की गई प्राथमिक कुंजी है तो COALESCE का भी उपयोग किया जा सकता है ( अरिस्टोटल पागाल्टज़िस टिप्पणी देखें)।

WITH new (id, name, title, content, author)
     AS ( VALUES(100, 'about', 'About this site', 'Whatever new content here', 42) )
INSERT OR REPLACE INTO page (id, name, title, content, author)
SELECT
     old.id, COALESCE(old.name, new.name),
     new.title, new.content,
     COALESCE(old.author, new.author)
FROM new LEFT JOIN page AS old ON new.name = old.name;

तो सामान्य नियम होगा, यदि आप पुराने मान रखना चाहते हैं, तो COALESCE उपयोग करें, जब आप मानों को अपडेट करना चाहते हैं, तो new.fieldname उपयोग new.fieldname





upsert