node-webkit中sqlite3事务提交后事务没完成就回调的解决方案

node-webkit中,用sqlite3的库来创建本地sqlite数据库,并且同步服务器数据到本地,如果数据量大时,事务执行要很久,而回调很快返回,不是想像中的执行完事务才返回结果,在实时性要求高或后面的处理依赖事务完成才执行的情况下,会出bug,本文用循环查询来确定事务是否完成,才执行回调.

示例代码如下,在pubservices数据有两万多条时处理比较久,能明显看出来通常情况下事务处理完后回调了但数据没有全部保存进去,数据少时会有概率性看得出来.

var _=require("underscore");
var sqlite3=require("sqlite3");
var baseDB = new sqlite3.Database(yourPath+‘//database.db‘);
this.pubserviceSync = function (pubservices,callbacks){
	        try{
	        //stml存sql的临时变量
	        //readyInterval存循环查询的变量 
	        //beginPubserviceCount存查询出来数量的变量 
	        	var stmt,readyInterval,beginPubserviceCount=0;
	        	baseDB.run("BEGIN TRANSACTION");
				baseDB.run("PRAGMA synchronous=OFF");
				//删除服务号
	        	stmt = baseDB.prepare("DELETE FROM pubservice");
		        stmt.run();
				
				//增加服务号
				stmt = baseDB.prepare("insert into pubservice (pubserviceId,name,avatarId,recommend,createdAt,updatedAt) values ($pubserviceId,$name,$avatarId,$recommend,$createdAt,$updatedAt)");
				_.each(pubservices,function(pubservice){
					stmt.run({
					$pubserviceId:pubservice.pubserviceId,
					$name:pubservice.pubserviceName,
					$avatarId:pubservice.avatarId,
					$recommend:pubservice.recommend,
					$createdAt:new Date().toISOString(),
					$updatedAt:new Date().toISOString()
					})
				});
				stmt.finalize(function(){
					baseDB.run("COMMIT TRANSACTION",function(err){
						if(err){
							callbacks(err);
						}else{
							readyInterval = setInterval(function(){
		                        baseDB.get("select count(*) as count from pubservice",function(err,result){		                           
		                            if(err){
		                                clearInterval(readyInterval);
		                                readyInterval=null;
		                                if(typeof callbacks == "function")callbacks(err)
		                            }else{
		                                var getUserCount=0;
		                                if(typeof pubservices  != "undefined" &&typeof pubservices.length != "undefined"){
		                                    getUserCount=pubservices.length;
		                                }
		                                console.log("已同步服务号"+result.count+" 总数："+getUserCount+" 百分比： "+parseInt(result.count/getUserCount*100)+"%")		                               
		                                if(pubservices&&pubservices.length){       
		                                    if(result.count==beginPubserviceCount){       
		                                        clearInterval(readyInterval);
		                                        readyInterval=null;
		                                        if(typeof callbacks == "function")callbacks(null)
		                                    }else{
		                                    	beginPubserviceCount = result.count;
		                                    }
		                                }else{		                                    
		                                    clearInterval(readyInterval);
		                                    readyInterval=null;
		                                    if(typeof callbacks == "function")callbacks()
		                                }
		                            }
		                        })
	                    },400);	
						}
					});					
				});
			}catch(e){
	        	console.error("pubserviceSync error:"+e);
	        }
		};
	}