Google App Maker批量更新数据库记录 [英] Google App Maker updating DB records in batches

问题描述

我们有一个使用Google Cloud SQL表的Google App Maker应用.我们的位置是中欧的布拉格，app.saveRecords()最少需要240ms的时间.我们已经尝试了很多方法，包括更改实例的位置，但是并不能解决问题.

We are having a Google App Maker app using Google Cloud SQL tables. Our location is Prague, Central Europe and app.saveRecords() takes incredible 240ms at minimum. We have tried many things including changing the location of the instance but it did not solve the problem.

幸运的是，解决方案是分批插入新记录.我以前的主题[

Fortunately, the solution is to insert new records in batches. This was brilliantly solved by the answer in my previous thread [ Google App Maker saving records to DB is taking 240ms per record ].

如果有人为我们提供了一个简单的代码来批量更新数据库中的记录[服务器端脚本]，我们将非常感激.这样，我们不仅可以批量插入全新的记录，而且可以快速更新已经保存在数据库中的记录数据.

We would be so grateful if anyone provided us with a simple code to update records in the DB in batches [server-side scripting]. This way, we not only could insert completely new records in batches, but we could update the data of the records already saved in the DB fast way.

假设我们有一个包含3个自定义字段的数据库:

Let's say we have a DB with 3 custom fields:

product code | product availability | price

+ 带有数据的示例数组:

+ an example array with the data:

ourData[0] = ['pc001','in stock','99'];
ourData[1] = ['pc002','out of stock','49'];
ourData[2] = ['pc003','out of stock','199'];
ourData[3] = ['pc004','in stock','149'];
ourData[4] = ['pc005','in stock','299'];

...，我们想使用键产品代码"将可用性和价格数据分配给特定的数据库行.如果在数据库中找不到产品代码，那么我们将新记录插入到数据库中.

...and we want to assign the availability and price data to the particular DB line using the key "product code". If the product code is not found in the DB, then we insert a new record to the DB.

多次单行插入/更新会使欧盟的Google App Maker用户花费太长时间，因为即使SQL实例位于欧盟，查询也确实会转到美国.批量运行此程序可能会打破当今在美国以外的Google App Maker的局限性.非常感谢您为使Google App Maker成为全球初学者的绝佳工具所提供的帮助.

Multiple single line inserts/updates take Google App Maker users in the EU too long because the query does go to the US even if the SQL instance is located in the EU. Running this in batches could break today's Google App Maker limitations outside the US. Thank you so much for any help to make Google App Maker a great tool for beginners all around the world.

推荐答案

可以通过使用适当的服务器脚本来解决此问题，我强烈建议您阅读

This issue can be solved by using the proper server scripting and I strongly encourage you to read how server scripting works in the official documentation. Pay close attention at the Querying records example.

因此，以前面的解决方案示例为基础，可以对其进行略微修改以实现您的需求.它应该是这样的:

So, taking as base the previous solution example, it can be slightly modified to achieve your needs. This is how it should look:

function saveData(){

    //get the data
    var data = getData();

    var totalRecords = data.length;
    var batchLimit = 2000;
    var totalRounds = Math.ceil(totalRecords / batchLimit);
    var round = 1;
    var roundlimit = batchLimit;
    var currentItem = 0;

    do{
        var recordsToSave = [];

        for(var i=currentItem; i<roundlimit; i++){

            var recordData = data[i];
            var productCode = recordData[0];

            //check if the record already exists
            var query = app.models.testDB.newQuery();
            query.filters.productCode._equals = productCode;
            var productRecord = query.run()[0];

            //if non existent, create a new one
            if(!productRecord){
                productRecord = app.models.testDB.newRecord();
                productRecord.productCode = productCode;
            }

            productRecord.availability = recordData[1];
            productRecord.price = recordData[2];
            recordsToSave.push(newProduct);
        }

        if(recordsToSave.length){
            app.saveRecords(recordsToSave);
        }

        currentItem += batchLimit;
        roundlimit += batchLimit;
        round++;

    } while(round <= totalRounds);

}

这篇关于Google App Maker批量更新数据库记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！