preface

Shortly after I joined the new company, the operation girl made a request. She sorted out an Excel with 400 data records, which required two steps: batch audit and batch merger. The background interface can be operated, but there is no batch function. Let me write a script for that. In order to make a good impression on my new colleagues, I gladly accepted the request. For the sudden demand, can only take out their own years and product operations continue to fight, accumulated skills to deal with, commonly known as see recruit.

Organize things to do

First of all, you need to figure out what you need to do and how to do it. The operation was given an Excel spreadsheet. It is arranged inside the 400 data format with mapping relationship like:

fieldName newValue oldValue
Kkk 111 2222

The first thing is that there is a record table to be audited, let’s call it check_info table. This table stores 400 data to be audited introduced by crawlers, corresponding to the 400 data in Excel, as follows:

fieldName value Review the status
kkk 111 To audit

These 400 pieces of data need to be approved in batches. The interface has no batch function. When a record is successfully reviewed, a new table is generated, and a new table is added to the existing table. This table is called field_detail, and looks like this:

fieldName Value Key map_key
kkk 111 1 Null
Kkk 222 2 Null

The 222 column is the data corresponding to oldValue, and the 111 column is the new data approved above.

The second thing to do is to execute the following statement:

update field_detail set map_key = 1 where fieldName='kkk' and key=2;

It’s simple, but it also doesn’t have a batch interface, and it does more than just execute this statement, it also does a lot of things like cache clearing and updates. So you can only write scripts to batch call a single interface. The matter has been described. Now it’s time to start.

Provide solutions

The interface for a single audit is a GET request, such as: /check_info/check? Id =&value= XXX, id=&value= XXX, id=&value= XXX, id=&value= XXX, id=&value= XXX Because we audit by ID, we must find the ID of the 400 records in the Excel table that correspond to the check_info table. This will convert the Excel records into a select statement, and then connect the 400 with a union. So we can find the 400 ids. Note that you need to pass the value to the background in the parameters here, because the definition of this interface can be modified during the review of the redefined value, but here we only need to pass the original value. Find it, put it in a JSON array, and then use your identity to log in to the background system. Press F12 to open the Chrome debugging tool and create a new snippet under the Sources TAB. You can write js code there. Here you can write a for loop and ajax calls each of the 400 pieces of data to the corresponding interface. Success continues to call the next one, failure breaks the loop. The code will be posted below. So the first thing is done. The second key thing is to find the key of oldValue and newValue. A single interface parameter has three parameters: fieldName, oldKey, and newKey. Again, use SQL processing. The idea is to combine the new and old records into a single record, fieldName, oldKey, newKey, and then union the 400 records together. The query results are exported in JSON format. The interfaces are also called one by one in a manner similar to the first step.

The detailed process

First of all, according to the operation provided by excel to find out the id to be audited, this use of Excel CONCATENATE function is very good. Like:

=CONCATENATE("select id,value from check_info where fieldName= B2 and value= C2 union")

B2 means row 2, column 2, C2 means row 2, column 3. Finally, by piecing together the SQL, the id and value of the record to be audited can be retrieved from the online library. Fortunately, it is only 400, because the DMS limits the number of queries to no more than 500. Export the queried data in JSON format.

Start writing a batch request script:

Fortunately, all 400 items show successful execution. Of course, because of the direct operation of the online environment, do not start batch run, get a few try is very necessary. Then you check the results, but unfortunately something went wrong. The result should be 400 pieces of data:

select * from field_detail where fieldName=kkk and value = 111 union 
...

But the result is only 395. Why are there 5 missing? Using some SQL, I found the missing five items. Sent to operations. Ask her if there is something wrong with these data. Because I was confident that my operation was ok. There must be something wrong with the data provided by the operation. Operation sister is not bad, quickly found out the rule of these several. The contents of value contain a + sign. Actually, these pieces of data are already in the field_detail, but the + sign is now a space. I knew immediately what the problem was. The request parameters are processed by my encodeURI. Actually, I saw this at my last company. Is not enough attention to their own, “second palace”. Operation sister readily said that her own change is ok. Just five. But at this point my heart is a little remorse. It’s wrong to step on the same pit twice. So let me explain why the plus sign becomes a space.

The RFC-2396 standard defines the + sign as a reserved character in URIs. EncodeURI does not encode reserved characters. Such as:

EncodeURI (" country + home "); //%E5%9B%BD+%E5%AE%B6

The reserved character plus means space. So passing in the back end is going to be whitespace. There are two ways to solve this problem. One is to put the value in the payload. Change it to a POST request, which is certainly not possible in the current scenario. The second option is to use encodeURIComponent, which encodes reserved characters, such as:

EncodeURIComponent (" country + home "); //%E5%9B%BD%2B%E5%AE%B6

The plus sign becomes %2B. It will also be decoded as a + sign on the back end.

About percent encoding explain in detail please refer to: http://www.lmwlove.com/ac/id1030

So the first step is muddled through, and the next step needs to be a batch merger. First of all, we need to pass in the parameters of SQL query processing, processing SQL like:

select `fieldName`,(select `key` from field_detail where fieldName = 'kkk' and value = '111') as newKey,`key` as oldKey from field_detail where  fieldName = 'kkk' and value = '222' union
...

Batch merge script, such as:

Function sleep(ms) {return new Promise(resolve => setTimeout(resolve, ms)); } const ajaxGet = (url)=> New Promise(function(resolve,reject){var XHR = new XMLHttpRequest(); xhr.open('GET', url); xhr.send(); xhr.onload = function() { if(xhr.status == 200) { resolve(xhr); }else{reject(" call failed "); }}; }); async function doMerge(){ var mergeParamArray = [{"fieldName":"kkk","newKey":123,"oldKey":456}]; For (var I =0; i< mergeParamArray.length; Var mergeParam = mergeParamArray[I]; var url = "/field_detail/merge? field="+mergeParam.fieldName+"&oldKey="+mergeParam.oldKey+"&newKey="+mergeParam.newKey; await ajaxGet(url); console.log("merge field ",JSON.stringify(mergeParam)," success"); await sleep(1000); } } doMerge();

I’m glad 400 of them worked. The operation girl expressed her gratitude.

Summary and reflection

The above case can only be a temporary solution, the real scientific way is to add functionality. Meet daily operational requirements. The whole process down, maybe it is their own do a lot of similar things, there is almost no card. However, some areas are still not good enough, such as the percentage sign coding problem above, and the whole process and operation confirmation is not well done. In theory, such requirements need to be implemented by themselves, and operation confirmation step. If you are not careful in your operational confirmation, you need to pass on key information to her. Indicate that you are done when an operation is executed, what the effect will be. It is also worth considering that the interface is called in bulk, and there is no guarantee that one call will fail and the entire execution will be interrupted. Record the current progress. Find out which record is wrong, so that the next recovery execution. It is also critical to assume that a problem has been discovered during execution and that the task needs to be interrupted in order to stop the loss. Consider forcing the current TAB to close. This will also terminate the js for the loop. In short, be bold, but be careful.