Hi all,
I have recently made my first attempt at using Google Gears and performing replication to the local Sqlite database after many weeks of trial and error everything seemed to be sweet as candy.
I have accomplished this using approximately 10 worker threads each of which intermittently poll a webservice and update the database as required. Each update and insertion is being performed synchronously inside the worker threads.
All was well and good in the world of my “little replication engine that could” until one day came along a monstrous surprise when I started performing lookups on the database from the UI thread.
Suddenly a monster known as “DATABASE IS LOCKED” exception started intermittently appearing (which was a shock given none of the threads ever encountered the same problem even when running in parallel). Well since that point in time the tears have continued to flow and the frustration continued to build as I have been unable to come up with a “sane” way of performing the required steps.
Basically my current solution to the problem boils down to creating a worker thread to perform all database activities asynchronously but as you can imagine (particularly given javascripts horrendous syntax and language constraints) the solution can only be described as FUGLY.
So basically the whole point of this question is to see if anyone has dealt with any similar problems and if so how did they solve them? I am also interested in hearing ANY AND ALL suggestions about how one could solve this problem. I don’t need someone to lay it down for me step by step but anything in the right direction would be a godsend...
Please donate your time and save a lowly software developer from a depressing and agonising death... :-)
PS: Please find below an example of one of my original synchronous workers. Please note: this is one of my first JavaScript programs so I have undoubtedly done some ugly things with the syntax and would be happy to hear any general suggestions or mistakes I may have made in the code.
PSS: Hopefully u guys and girls will be kind enough to excuse my poor attempts at adding some humour to this post.
var CategoryReplicationWorker = new function () {
//Public
this.ReplicationCycleDelay = 5000;
this.TableName = "Category";
this.PagingValue = 30; //The amount of items to pull from the webservice in one go
//Public
//Private
var _wp = google.gears.workerPool;
var _timer = google.gears.factory.create('beta.timer');
var _db = google.gears.factory.create('beta.database');
var _DatabaseName = 'MyDatabase';
var _timerID;
var _senderID;
var _Username;
var _Password;
var _LastReplication;
//end private
var MainEngineActionsEnum = {
"Start": 0,
"Stop": 1
};
this.Start = function () {
_wp.onmessage = function (a, b, message) {
_senderID = message.sender;
var Action = message.body.Action;
_Username = message.body.Username;
_Password = message.body.Password;
switch (Action) {
case MainEngineActionsEnum.Start:
CategoryReplicationWorker.BeginReplication();
break;
case MainEngineActionsEnum.Stop:
CategoryReplicationWorker.StopReplication();
break;
}
};
this.BeginReplication = function () {
_timerID = _timer.setTimeout(CategoryReplicationWorker.PerformReplicationCycle, CategoryReplicationWorker.ReplicationCycleDelay);
};
this.StopReplication = function () {
_timer.clearTimeout(_timerID);
};
this.PerformReplicationCycle = function () {
//Step 1. Perform lookup on replication data
_LastReplication = CategoryReplicationWorker.ReplicationDataLayer.GetReplicationByTableName(CategoryReplicationWorker.TableName);
//Step 2. Begin category replication web call
CategoryReplicationWorker.BeginCategoryReplication(_Username, _Password, _LastReplication);
};
this.BeginCategoryReplication = function (Username, Password, Replication) {
var FromRow = Replication.NextReplicationRow ? Replication.NextReplicationRow : 1;
var ToRow = FromRow + (this.PagingValue - 1); //PagingValue - 1 because NextReplicationRow is auto incremented
var LastReplicated = Replication.LastReplicated ? Replication.LastReplicated : "01/01/1990";
CategoryWebServiceProxy.GetCategoryData(Username, Password, FromRow, ToRow, LastReplicated, CategoryReplicationWorker.OnCategoryReplicationSuccess, CategoryReplicationWorker.OnCategoryReplicationFailed);
};
this.OnCategoryReplicationSuccess = function (body) {
var myBaseObject = new Object;
var myInvokedResult = new Object();
eval("myBaseObject = " + body);
eval("myInvokedResult = {" + myBaseObject.d + "}");
if (myInvokedResult.RESULT) {
//Commit all category items
var res = CategoryReplicationWorker.CategoryDataLayer.CommitList(myInvokedResult.RESULT);
_LastReplication.NextReplicationRow = CategoryReplicationWorker.CalcNextReplicationRow(_LastReplication.NextReplicationRow);
} else {
//If NextReplicationRow is NOT null then replication cycle has completed so we set date. If IS NULL then simply no data and we ignore cycle.
_LastReplication.LastReplicated = _LastReplication.NextReplicationRow ? myInvokedResult.TIMESTAMP : _LastReplication.LastReplicated;
_LastReplication.NextReplicationRow = null;
}
CategoryReplicationWorker.ReplicationDataLayer.Update(_LastReplication.ReplicationID, _LastReplication.TableName, _LastReplication.LastReplicated, _LastReplication.NextReplicationRow);
CategoryReplicationWorker.BeginReplication(); //Restart replication
};
this.OnCategoryReplicationFailed = function (body) {
CategoryReplicationWorker.SendMessage(body);
CategoryReplicationWorker.BeginReplication(); //Restart replication
};
this.CalcNextReplicationRow = function (CurrentNextReplicationRow) {
if (CurrentNextReplicationRow) {
return CurrentNextReplicationRow + this.PagingValue;
} else {
return this.PagingValue + 1;
}
};
};
this.SendMessage = function (Message) {
_wp.sendMessage(Message, _senderID);
};
this.CategoryDataLayer = new function () {
this.CommitList = function (CategoryList) {
var res = 0;
for (var i = 0; i < CategoryList.length; i++) {
var curCategoryItem = CategoryList[i];
var categoryItem = this.GetCategoryByCategoryID(curCategoryItem.CategoryID);
if (categoryItem) {
res = res + this.Update(curCategoryItem.CategoryID, curCategoryItem.UserID, curCategoryItem.ParentCategoryID, curCategoryItem.Description);
} else {
res = res + this.Insert(curCategoryItem.CategoryID, curCategoryItem.UserID, curCategoryItem.ParentCategoryID, curCategoryItem.Description);
}
}
return res;
};
this.Insert = function (CategoryID, UserID, ParentCategoryID, Description) {
var SqlInsertCategoryCommand = 'INSERT INTO Category VALUES (?, ?, ?, ?)';
_db.open(_DatabaseName);
_db.execute(SqlInsertCategoryCommand, [CategoryID, UserID, this.IsNullGuid(ParentCategoryID), Description]);
var res = _db.rowsAffected;
_db.close();
return res;
};
this.IsNullGuid = function (GUID) {
if (GUID == "00000000-0000-0000-0000-000000000000") {
return null;
} else {
return GUID;
}
};
this.GetCategoryByCategoryID = function (CategoryID) {
var SqlSelectCategoryCommand = 'SELECT * FROM Category WHERE CategoryID = ?';
_db.open(_DatabaseName);
var rs = _db.execute(SqlSelectCategoryCommand, [CategoryID]);
if (rs.isValidRow()) {
var res = new Category;
res.Fill(rs);
}
rs.close();
_db.close();
return res ? res : null;
};
this.Update = function (CategoryID, UserID, ParentCategoryID, Description) {
var SqlUpdateCategoryCommand = 'UPDATE Category SET UserID = ?, ParentCategoryID = ?, Description = ? WHERE CategoryID = ?';
_db.open(_DatabaseName);
_db.execute(SqlUpdateCategoryCommand, [UserID, this.IsNullGuid(ParentCategoryID), Description, CategoryID]);
var res = _db.rowsAffected;
_db.close();
return res;
};
this.Delete = function (CategoryID) {
var SqlDeleteCategoryCommand = 'DELETE FROM Category WHERE CategoryID = ?';
_db.open(_DatabaseName);
_db.execute(SqlDeleteCategoryCommand, [CategoryID]);
var res = _db.rowsAffected;
_db.close();
return res;
};
};
this.ReplicationDataLayer = new function () {
this.GetReplicationByTableName = function (TableName) {
var SqlSelectReplicationCommand = 'SELECT * FROM Replication WHERE TableName = ?';
_db.open(_DatabaseName);
var rs = _db.execute(SqlSelectReplicationCommand, [TableName]);
if (rs.isValidRow()) {
var res = new Replication;
res.Fill(rs);
}
rs.close();
_db.close();
return res ? res : null;
};
this.GetNextReplicationTable = function () {
var SqlSelectReplicationCommand = 'SELECT * FROM Replication ORDER BY LastReplicated ASC LIMIT 1';
_db.open(_DatabaseName);
var rs = _db.execute(SqlSelectReplicationCommand);
if (rs.isValidRow()) {
var res = new Replication;
res.Fill(rs);
}
rs.close();
_db.close();
return res ? res : null;
};
this.Insert = function (TableName, LastReplicated, NextReplicationRow) {
var SqlInsertReplicationCommand = 'INSERT INTO Replication VALUES (?, ?, ?, ?)';
_db.open(_DatabaseName);
_db.execute(SqlInsertReplicationCommand, [null, TableName, LastReplicated, NextReplicationRow]);
var res = _db.rowsAffected;
_db.close();
return res;
};
this.Update = function (ReplicationID, TableName, LastReplicated, NextReplicationRow) {
var SqlUpdateReplicationCommand = 'UPDATE Replication SET TableName = ?, LastReplicated = ?, NextReplicationRow = ? WHERE ReplicationID = ?';
_db.open(_DatabaseName);
_db.execute(SqlUpdateReplicationCommand, [TableName, LastReplicated, NextReplicationRow, ReplicationID]);
var res = _db.rowsAffected;
_db.close();
return res;
};
};
//-------------------------------Replication----------------------------------\\
function Replication(ReplicationID, TableName, LastReplicated, NextReplicationRow) {
this.ReplicationID = ReplicationID;
this.TableName = TableName;
this.LastReplicated = LastReplicated;
this.NextReplicationRow = NextReplicationRow;
this.Fill = function (rs) {
this.ReplicationID = rs.fieldByName("ReplicationID");
this.TableName = rs.fieldByName("TableName");
this.LastReplicated = rs.fieldByName("LastReplicated");
this.NextReplicationRow = rs.fieldByName("NextReplicationRow");
};
};
//------------------------------End Replication-------------------------------\\
var CategoryWebServiceProxy = new function () {
function ServiceParameter(Desc, Val) {
this.Description = Desc;
this.Value = Val;
};
var ServiceParameterParser = new function () {
this.Parse = function (ServiceParameterList) {
var tmpString = "?";
for (var i = 0; i < ServiceParameterList.length; i++) {
tmpString = tmpString + ServiceParameterList[i].Description + "=%22" + ServiceParameterList[i].Value + "%22" + "&";
}
tmpString = tmpString.substring(0, tmpString.length - 1); //Trim last &
return tmpString;
};
}
var WebServiceProxy = new function () {
//Enums
this.RequestMethod = { GET: "GET", POST: "POST" };
this.ReadyState = { Uninitialized: 0, Open: 1, Sent: 2, Interactive: 3, Complete: 4 };
this.Status = { Aborted: 0, OK: 200, BadRequest: 400, Unauthorized: 401 };
//End enums
var _TimeOut = 10000;
var _Threaded = true;
var _client;
var _TimerID;
var _gearsTimer;
var _OnSuccessEvent;
var _OnFailedEvent;
this.BeginRequest = function (Method, WebServicePath, ServiceParameterList, OnSuccess, OnFailed) {
var URL = WebServicePath + ServiceParameterParser.Parse(ServiceParameterList);
_OnSuccessEvent = OnSuccess;
_OnFailedEvent = OnFailed;
_client = this.CreateXMLHttpObject();
_client.onreadystatechange = this.OnReadyStateChanged;
_client.open(Method, URL, true);
_client.setRequestHeader("Content-Type", "application/json");
_client.send(null);
_TimerID = this.StartTimerObject(this.AbortRequest, _TimeOut);
};
this.AbortRequest = function (OnFailed) {
_client.abort();
//Gears HTTP request object doesnt call the readystate change event on abort. So we call it now.
if (_Threaded) {
WebServiceProxy.RaiseFailedEvent("Request aborted or timeout occured.");
}
};
this.RaiseSuccessEvent = function (body) {
WebServiceProxy.StopTimerObject(_TimerID);
_OnSuccessEvent(body);
};
this.RaiseFailedEvent = function (body) {
WebServiceProxy.StopTimerObject(_TimerID);
_OnFailedEvent(body);
};
this.OnReadyStateChanged = function () {
if (_client.readyState == WebServiceProxy.ReadyState.Complete) {
switch (_client.status) {
case WebServiceProxy.Status.OK:
WebServiceProxy.RaiseSuccessEvent(_client.responseText);
break;
case WebServiceProxy.Status.Aborted:
WebServiceProxy.RaiseFailedEvent("Request aborted or timeout occured.");
break;
default:
WebServiceProxy.RaiseFailedEvent("Non OK status result. Status Code: " + _client.status);
break;
}
}
};
this.StartTimerObject = function (funct, msecDelay) {
if (_Threaded) {
_gearsTimer = google.gears.factory.create('beta.timer');
return _gearsTimer.setTimeout(funct, msecDelay);
} else {
return setTimeout(funct, msecDelay);
}
};
this.StopTimerObject = function (timerID) {
if (_Threaded) {
_gearsTimer.clearTimeout(timerID);
} else {
clearTimeout(timerID);
}
};
this.CreateXMLHttpObject = function () {
if (_Threaded) {
return google.gears.factory.create('beta.httprequest');
} else {
return window.XMLHttpRequest ? new XMLHttpRequest() : new ActiveXObject("Microsoft.XMLHTTP");
}
};
}
//Above is the WebProxy - all generic shit
//Now we expose a simple WebServiceAPI
//Webservice URLs.
this.URL_GetCategoryData = "/ReplicationService.asmx/GetCategoryDataJSON";
this.URL_HelloWorld = "/ReplicationService.asmx/HelloWorld";
//End Webservice URLs.
this.GetCategoryData = function (Username, Password, FromRow, ToRow, LastUpdated, OnSuccess, OnFailed) {
var ServiceParameterList = new Array();
ServiceParameterList[0] = new ServiceParameter("Username", Username);
ServiceParameterList[1] = new ServiceParameter("Password", Password);
ServiceParameterList[2] = new ServiceParameter("FromRow", FromRow);
ServiceParameterList[3] = new ServiceParameter("ToRow", ToRow);
ServiceParameterList[4] = new ServiceParameter("LastUpdated", LastUpdated);
WebServiceProxy.BeginRequest(WebServiceProxy.RequestMethod.GET, this.URL_GetCategoryData, ServiceParameterList, OnSuccess, OnFailed);
};
};
//---------------------------------Category-----------------------------------\\
function Category(CategoryID, ParentCategoryID, Description) {
this.CategoryID = CategoryID
this.ParentCategoryID = ParentCategoryID
this.Description = Description
this.Fill = function (rs) {
this.CategoryID = rs.fieldByName("CategoryID");
this.ParentCategoryID = rs.fieldByName("ParentCategoryID");
this.Description = rs.fieldByName("Description");
}
}
//-------------------------------End Category---------------------------------\\
this.Start();
}