SQLite-UPSERT*لا*INSERT أو REPLACE




(10)

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

إدراج تحديث تخزين proc على SQL Server

هل هناك طريقة ذكية للقيام بذلك في SQLite أنني لم أفكر؟

أساسا أريد تحديث ثلاثة من أصل أربعة أعمدة إذا كان السجل موجودا ، إذا لم يكن موجودا أريد أن أذكر السجل مع القيمة الافتراضية (NUL) للعمود الرابع.

المعرّف هو مفتاح أساسي لذا لن يكون هناك سوى سجل واحد على UPSERT.

(أحاول تجنب الحمل في SELECT من أجل تحديد ما إذا كنت بحاجة للتحديث أو INSERT بوضوح)

اقتراحات؟

لا يمكنني تأكيد ذلك بناء الجملة على موقع SQLite لـ TABLE CREATE. لم أقم ببناء عرض تجريبي لاختباره ، ولكن يبدو أنه غير مدعوم ..

إذا كان الأمر كذلك ، فلدي ثلاثة أعمدة بحيث تبدو في الواقع:

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

لكن النقطتين الأوليتين لن تتسبب في حدوث صراع ، فقط المعرّف لن يُستعاض عن So I asusme Blob1 و Blob2 (حسب الرغبة)

UPDATEs في SQLite عندما تكون بيانات الربط عبارة عن معاملة كاملة ، مما يعني أن كل صف تم إرساله يتطلب تحديثه: تحضير / ربط / خطوة / إنهاء العبارات بخلاف INSERT التي تسمح باستخدام وظيفة إعادة التعيين

حياة كائن بيان يذهب شيء مثل هذا:

  1. إنشاء الكائن باستخدام sqlite3_prepare_v2 ()
  2. ربط القيم إلى معلمات المضيف باستخدام واجهات sqlite3_bind_.
  3. قم بتشغيل SQL عن طريق استدعاء sqlite3_step ()
  4. إعادة تعيين العبارة باستخدام sqlite3_reset () ثم العودة إلى الخطوة 2 وكرر.
  5. تدمير كائن بيان باستخدام sqlite3_finalize ().

UPDATE أظن أن التخمين بطيء مقارنة بـ INSERT ، ولكن كيف يمكن مقارنته بـ SELECT باستخدام المفتاح الأساسي؟

ربما يجب علي استخدام التحديد لقراءة العمود الرابع (Blob3) ثم استخدام REPLACE لكتابة سجل جديد يمزج العمود الأصلي الرابع مع البيانات الجديدة للأعمدة الثلاثة الأولى؟


INSERT أو REPLACE لا يساوي "UPSERT".

لنفترض أن لدي الموظف جدولًا به حقول الهوية والاسم والدور:

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

Boom ، لقد فقدت اسم الموظف رقم 1. قام SQLite باستبداله بقيمة افتراضية.

سيكون الناتج المتوقع من UPSERT لتغيير الدور وللحفاظ على الاسم.


أظن أن هذا قد يكون ما تبحث عنه: حول بند CONFLICT .

إذا قمت بتعريف الجدول الخاص بك مثل هذا:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    field1 TEXT 
); 

الآن ، إذا قمت بإجراء INSERT بمعرف موجود بالفعل ، يقوم SQLite تلقائيًا بتحديث UPDATE بدلاً من INSERT.

هته ...


أنا أدرك أن هذا خيط قديم ولكنني كنت أعمل في 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 مع جملة حيث التحديث ولكن يبدو أن تفعل الخدعة. أنا أيضا لدي هذه الرؤية في رأسي أن sqlite يمكن تحسين بيان التحديث تماما إذا كانت الدعوة إلى التغييرات () أكبر من الصفر. سواء كان ذلك في الواقع أم لا هو أبعد من علمي ، ولكن الرجل يمكن أن يحلم لا يمكن هو؟ ؛)

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

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

إذا أراد أحدهم قراءة الحل الخاص بـ SQLite في Cordova ، فقد حصلت على طريقة js العامة هذه بفضلdavid answer أعلاه.

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);
    });
});
}

ثم بناء المعاملات برمجيا.

"القيم" هي مصفوفة يجب إنشاؤها قبلها وهي تمثل الصفوف التي تريد إدراجها أو تحديثها في الجدول.

"remoteid" هو المعرّف الذي استخدمته كمرجع ، بما أنني أقوم بمزامنته مع الخادم البعيد.

لاستخدام ملحق SQLite Cordova ، يرجى الرجوع إلى link الرسمي


بافتراض وجود 3 أعمدة في الجدول .. المعرف ، NAME ، ROLE

BAD: سيؤدي هذا إلى إدراج أو استبدال كل الأعمدة بقيم جديدة للمعرّف = 1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD: سيؤدي هذا إلى إدراج أو استبدال 2 من الأعمدة ... سيتم تعيين عمود NAME على NULL أو القيمة الافتراضية:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD: سيقوم بتحديث 2 من الأعمدة. عند وجود المعرّف = 1 ، لن يتأثر NAME. عندما لا يوجد ID = 1 ، سيكون الاسم الافتراضي (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

سيقوم هذا بتحديث 2 من الأعمدة. عند وجود معرف = 1 ، لن يتأثر ROLE. عندما لا يوجد ID = 1 ، سيتم تعيين الدور إلى "Benchwarmer" بدلاً من القيمة الافتراضية.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

بالتوسع على 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";

بعد قراءة هذا الموضوع وخيبة أمل أنه لم يكن من السهل فقط لهذا "UPSERT" جي ، بحثت كذلك ...

يمكنك فعل هذا مباشرة وبسهولة في SQLITE.

بدلا من استخدام: INSERT INTO

الاستعمال: INSERT OR REPLACE INTO

هذا يفعل بالضبط ما تريد أن تفعل!


تقوم هذه الطريقة بإعادة خلط بعض الطرق الأخرى من الإجابة في هذا السؤال وتضمين استخدام CTE (تعبيرات جدول شائعة). سوف أعرض الاستعلام ثم اشرح لماذا فعلت ما فعلت.

أرغب في تغيير الاسم الأخير للموظف 300 إلى DAVIS إذا كان هناك موظف 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
;

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

يمكنك الآن تحديد الإعدادات الافتراضية التي تريد استخدامها عن طريق استبدال القيم الخالية ، في الدالة COALESCE بما يجب أن تكون عليه القيم.


الإجابة إريك ب هو موافق إذا كنت ترغب في الحفاظ على عمود واحد فقط أو ربما اثنين من الصف الموجود. إذا كنت ترغب في الحفاظ على الكثير من الأعمدة ، فإنه يصبح سريعًا جدًا.

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

 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 فقط للمفاتيح الخارجية ، وبالتالي فإن النقطة هي أن SQLite لاختيار قيمة المعرف نفسه عند إدراج صف جديد. ولكن عند تحديث صف موجود استنادًا إلى 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 NULL وسيقوم SQLite بتعيين معرف تلقائيًا ، ولكن إذا كان هناك بالفعل هذا الصف ، فسيكون لدى old.id قيمة فعلية وسيتم إعادة استخدام هذا. وهو بالضبط ما أردت.

في الواقع هذا هو مرن جدا. لاحظ كيف أن عمود ts مفقود تمامًا من جميع الجوانب - لأنه يحتوي على قيمة DEFAULT ، سيقوم SQLite فقط بعمل الشيء الصحيح في أي حال ، لذلك لا يجب أن أعتني به بنفسي.

يمكنك أيضًا تضمين عمود في كل من الجانبين new old ثم استخدام COALESCE(new.content, old.content) في SELECT الخارجي ليقول "أدخل المحتوى الجديد إذا كان هناك أي محتوى ، أو احتفظ بالمحتوى القديم" - على سبيل المثال ، إذا كنت تستخدم استعلامًا ثابتًا وتربط القيم الجديدة بالعناصر النائبة.


قد يكون غير صالح لـ SQLITE (لا يعمل على الأقل في android و WebSQL)

أعرف أني متأخر على الحفلة لكن ....

UPDATE employee SET role = 'code_monkey', name='fred' WHERE id = 1;
INSERT INTO employee(id, role, name) values (1, 'code monkey, 'fred') WHERE changes() = 0;

لذلك يحاول تحديث ، إذا كان السجل هناك ثم التغييرات () == 1 بحيث لا يتم عمل الإدراج.

بدلا من ذلك:

طريقة أخرى مختلفة تماماً للقيام بذلك: في التطبيق الخاص بي أقوم بتعيين صفري في الذاكرة لتكون long.MaxValue عند إنشاء الصف في الذاكرة. (لن يتم استخدام MaxValue كمعرف ، فلن تعيش طويلا بما فيه الكفاية ....) إذا لم يكن rowID بهذه القيمة ، فيجب أن يكون بالفعل في قاعدة البيانات لذا يحتاج إلى UPDATE إذا كان MaxValue فإنه يحتاج إلى إدراج. هذا مفيد فقط إذا كان بإمكانك تتبع الصفوف في تطبيقك.





upsert