Click here to Skip to main content
15,860,972 members
Articles / Programming Languages / Javascript

JavaScript Database Engine (JS-DBE)

Rate me:
Please Sign up or sign in to vote.
4.60/5 (4 votes)
12 Feb 2013CPOL4 min read 21.1K   477   12  
This is a simple JavaScript database engine implementation
function DataSet(url, async, callback)
{				
	load = function(url)
	{
		var dataTables = new Array();
		
		var xmlhttp = null;
		if (window.XMLHttpRequest)
		{
			xmlhttp = new XMLHttpRequest();
		}
		else
		{
			xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
		}
		if (xmlhttp)
		{
			xmlhttp.onreadystatechange = function()
			{
				if ((xmlhttp.readyState == 4) || (xmlhttp.readyState == 0))
				{
					var tables = xmlhttp.responseXML.getElementsByTagName("table");
					for (var i=0; i<tables.length; i++)
					{
						var dataTable = null;
						var getColumns = null;
						for (var j=0; j<tables[i].childNodes.length; j++)
						{
							if(tables[i].childNodes[j].nodeType == 1)
							{
								if (dataTable == null)
								{
									dataTable = new DataTable(tables[i].childNodes[j].nodeName);
									getColumns = true;
								}
								
								var dataTableRow = new Object();
								for (var k=0; k<tables[i].childNodes[j].childNodes.length; k++)
								{
									if(tables[i].childNodes[j].childNodes[k].nodeType == 1)
									{
										if (getColumns == true)
										{
											dataTable.Columns.push(tables[i].childNodes[j].childNodes[k].nodeName);
										}
										
										dataTableRow[tables[i].childNodes[j].childNodes[k].nodeName] = tables[i].childNodes[j].childNodes[k].childNodes[0].nodeValue;
									}
								}
								dataTable.Rows.push(dataTableRow);
								getColumns = false;
							}
						}
						dataTables.push(dataTable);
					}
					
					if (callback != null)
					{
						callback();
					}
				}
			}
			xmlhttp.open("GET", url, async);
			xmlhttp.send(null);
		}
		
		return dataTables;
	}
	
	this.Tables = load(url);
}

function DataTable(name, headerStyle, bodyStyle)
{
	this.Name = name;
	this.Columns = new Array();
	this.Rows = new Array();
	this.GrouppingColumn = null;
	this.HeaderStyle = headerStyle;
	this.BodyStyle = bodyStyle;
	
	this.updateTable = function(tableId)
	{
		var table = document.getElementById(tableId);
		if (table != null)
		{
			if (table.rows.length > 0)
			{
				table.rows.clear();
			}
			var header = table.insertRow(-1);
			for (var j=0; j<this.Columns.length; j++)
			{
				var th = header.insertCell(-1);
				th.innerHTML = this.Columns[j];
				if (this.HeaderStyle != null)
				{
					th.style.cssText = this.HeaderStyle;
				}
			}
			for (var i=0; i<this.Rows.length; i++)
			{
				var row = table.insertRow(-1);
				for (var j=0; j<this.Columns.length; j++)
				{
					var td = row.insertCell(-1);
					td.innerHTML = this.Rows[i][this.Columns[j]];
					if (this.BodyStyle != null)
					{
						td.style.cssText = this.BodyStyle;
					}
				}
			}
		}
	}
	
	this.updateComboBox = function(comboBoxId, columnName)
	{
		var comboBox = document.getElementById(comboBoxId);
		if ((comboBox != null) && (columnName != null))
		{
			if (comboBox.options.length > 0)
			{
				while (comboBox.options.length > 0)
				{
					comboBox.remove(0);
				}
			}
			for (var i=0; i<this.Rows.length; i++)
			{
				var option = new Option(this.Rows[i][columnName], i);
				comboBox.options.add(option);
			}
		}
	}
	
	this.joinTable = function(dataTable, primaryKey, foreignKey)
	{
		var dt = null;
		
		if ((dataTable != null) && (primaryKey != null) && (foreignKey != null))
		{
			dt = new DataTable(this.Name + dataTable.Name, this.HeaderStyle, this.BodyStyle);
			for (var k=0; k<this.Columns.length; k++)
			{
				dt.Columns.push(this.Name + "." + this.Columns[k]);
			}
			for (var k=0; k<dataTable.Columns.length; k++)
			{
				dt.Columns.push(dataTable.Name + "." + dataTable.Columns[k]);
			}
			for (var i=0; i<this.Rows.length; i++)
			{
				for (var j=0; j<dataTable.Rows.length; j++)
				{
					if (this.Rows[i][foreignKey] == dataTable.Rows[j][primaryKey])
					{
						var dataTableRow = new Object();
						for (var k=0; k<this.Columns.length; k++)
						{
							dataTableRow[this.Name + "." + this.Columns[k]] = this.Rows[i][this.Columns[k]];
						}
						for (var k=0; k<dataTable.Columns.length; k++)
						{
							dataTableRow[dataTable.Name + "." + dataTable.Columns[k]] = dataTable.Rows[j][dataTable.Columns[k]];
						}
						dt.Rows.push(dataTableRow);
					}
				}
			}
		}
		
		return dt;
	}
	
	this.sortTable = function(columnName, desc)
	{
		this.GrouppingColumn = null;
		
		if ((desc == false) || (desc == null))
		{
			if(isNaN(this.Rows[0][columnName].replace(',', '')) == true)
			{
				this.Rows.sort(function(a, b) {return (a[columnName] > b[columnName])});
			}
			else
			{
				this.Rows.sort(function(a, b) {return (new Number(a[columnName].replace(',', '')) > new Number(b[columnName].replace(',', '')))});
			}
		}
		else
		{
			if(isNaN(this.Rows[0][columnName].replace(',', '')) == true)
			{
				this.Rows.sort(function(a, b) {return (a[columnName] < b[columnName])});
			}
			else
			{
				this.Rows.sort(function(a, b) {return (new Number(a[columnName].replace(',', '')) < new Number(b[columnName].replace(',', '')))});
			}
		}
	}
	
	this.groupTable = function(columnName)
	{
		if (columnName != null)
		{
			this.GrouppingColumn = columnName;
			
			var i = 0;
			var j = 0;
			var s = 0;
			var e = 0;
			while (i < this.Rows.length-1)
			{
				j = i + 1;
				s = j;
				while (j < this.Rows.length)
				{
					if (this.Rows[j][columnName] == this.Rows[i][columnName])
					{
						j++;
					}
					else
					{
						s = j;
						e = j;
						break;
					}
				}

				j++;
				while (j < this.Rows.length)
				{
					if (this.Rows[j][columnName] != this.Rows[i][columnName])
					{
						j++;
					}
					else
					{
						e = j;
						break;
					}
				}
				
				if (s < e)
				{
					var tmp1 = this.Rows[s];
					var tmp2 = this.Rows[e];
					this.Rows.splice(e, 1, tmp1);
					this.Rows.splice(s, 1, tmp2);
					i = s;
				}
				else
				{
					i++;
				}
			}
		}
	}
	
	this.where = function(condition)
	{
		var dt = null;
		
		if (condition != null)
		{
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			for (var k=0; k<this.Columns.length; k++)
			{
				dt.Columns.push(this.Columns[k]);
			}
			
			var data = condition.split(' ');
			var columnName = data[0].replace(' ', '');
			var operation = data[1].replace(' ', '');
			var fieldValue = data[2].replace(' ', '');
			for (var i=0; i<this.Rows.length; i++)
			{
				var addRow = false;
				if(isNaN(this.Rows[i][columnName].replace(',', '')) == true)
				{
					switch (operation)
					{
						case ">":
						{
							if (this.Rows[i][columnName] > fieldValue)
							{
								addRow = true;
							}
						}
						break;
						case "<":
						{
							if (this.Rows[i][columnName] < fieldValue)
							{
								addRow = true;
							}
						}
						break;
						case "=":
						{
							if (this.Rows[i][columnName] == fieldValue)
							{
								addRow = true;
							}
						}
						break;
						case "<>":
						{
							if (this.Rows[i][columnName] != fieldValue)
							{
								addRow = true;
							}
						}
						break;
						case "<=":
						{
							if (this.Rows[i][columnName] <= fieldValue)
							{
								addRow = true;
							}
						}
						break;
						case ">=":
						{
							if (this.Rows[i][columnName] >= fieldValue)
							{
								addRow = true;
							}
						}
						break;
					}
				}
				else
				{
					var op1 = new Number(this.Rows[i][columnName].replace(',', ''));
					var op2 = new Number(fieldValue.replace(',', ''));
					switch (operation)
					{
						case ">":
						{
							if (new Number(this.Rows[i][columnName].replace(',', '')) > new Number(fieldValue.replace(',', '')))
							{
								addRow = true;
							}
						}
						break;
						case "<":
						{
							if (new Number(this.Rows[i][columnName].replace(',', '')) < new Number(fieldValue.replace(',', '')))
							{
								addRow = true;
							}
						}
						break;
						case "=":
						{
							if (op1.toString() == op2.toString())
							{
								addRow = true;
							}
						}
						break;
						case "<>":
						{
							if (new Number(this.Rows[i][columnName].replace(',', '')) != new Number(fieldValue.replace(',', '')))
							{
								addRow = true;
							}
						}
						break;
						case "<=":
						{
							if (new Number(this.Rows[i][columnName].replace(',', '')) <= new Number(fieldValue.replace(',', '')))
							{
								addRow = true;
							}
						}
						break;
						case ">=":
						{
							if (new Number(this.Rows[i][columnName].replace(',', '')) >= new Number(fieldValue.replace(',', '')))
							{
								addRow = true;
							}
						}
						break;
					}
				}
				
				if (addRow == true)
				{
					var dataTableRow = new Object();
					for (var k=0; k<this.Columns.length; k++)
					{
						dataTableRow[this.Columns[k]] = this.Rows[i][this.Columns[k]];
					}
					dt.Rows.push(dataTableRow);
				}
			}
		}
		
		return dt;
	}

	this.select = function(columnNames)
	{
		var dt = null;
		
		if (columnNames != null)
		{
			var columns = columnNames.split(',');
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			for (var k=0; k<columns.length; k++)
			{
				dt.Columns.push(columns[k].replace(' ', ''));
			}
			for (var i=0; i<this.Rows.length; i++)
			{
				var dataTableRow = new Object();
				var k = 0;
				for (var j=0; j<this.Columns.length; j++)
				{
					if (this.Columns[j] == dt.Columns[k])
					{
						dataTableRow[dt.Columns[k]] = this.Rows[i][this.Columns[j]];
						k++;
					}
				}
				dt.Rows.push(dataTableRow);
			}
		}
		
		return dt;
	}
	
	this.sum = function(columnName)
	{
		var dt = null;
		
		if ((columnName != null) && (this.GrouppingColumn != null))
		{
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			dt.Columns.push(this.GrouppingColumn);
			dt.Columns.push(columnName);
			var fieldValue = this.Rows[0][this.GrouppingColumn];
			var sumValue = new Number(this.Rows[0][columnName].replace(',', ''));
			for (var i=1; i<this.Rows.length; i++)
			{
				if (this.Rows[i][this.GrouppingColumn] == fieldValue)
				{
					sumValue += new Number(this.Rows[i][columnName].replace(',', ''));
				}
				else
				{
					var dataTableRow = new Object();
					dataTableRow[this.GrouppingColumn] = fieldValue;
					dataTableRow[columnName] = this.convertToCurrency(sumValue.toString());
					dt.Rows.push(dataTableRow);
					
					fieldValue = this.Rows[i][this.GrouppingColumn];
					sumValue = new Number(this.Rows[i][columnName].replace(',', ''));
				}
			}
			if (sumValue != 0)
			{
				var dataTableRow = new Object();
				dataTableRow[this.GrouppingColumn] = fieldValue;
				dataTableRow[columnName] = this.convertToCurrency(sumValue.toString());
				dt.Rows.push(dataTableRow);
			}
		}
		else if (columnName != null)
		{
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			dt.Columns.push(columnName);
			var sumValue = 0;
			for (var i=0; i<this.Rows.length; i++)
			{
				sumValue += new Number(this.Rows[i][columnName].replace(',', ''));
			}
			var dataTableRow = new Object();
			dataTableRow[columnName] = this.convertToCurrency(sumValue.toString());
			dt.Rows.push(dataTableRow);
		}
		
		return dt;
	}
	
	this.count = function(columnName)
	{
		var dt = null;
		
		if ((columnName != null) && (this.GrouppingColumn != null))
		{
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			dt.Columns.push(this.GrouppingColumn);
			dt.Columns.push(columnName);
			var fieldValue = this.Rows[0][this.GrouppingColumn];
			var countValue = 0;
			for (var i=0; i<this.Rows.length; i++)
			{
				if (this.Rows[i][this.GrouppingColumn] == fieldValue)
				{
					countValue++;
				}
				else
				{
					var dataTableRow = new Object();
					dataTableRow[this.GrouppingColumn] = fieldValue;
					dataTableRow[columnName] = countValue.toString();
					dt.Rows.push(dataTableRow);
					
					fieldValue = this.Rows[i][this.GrouppingColumn];
					countValue = 1;
				}
			}
			if (countValue != 0)
			{
				var dataTableRow = new Object();
				dataTableRow[this.GrouppingColumn] = fieldValue;
				dataTableRow[columnName] = countValue.toString();
				dt.Rows.push(dataTableRow);
			}
		}
		else if (columnName != null)
		{
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			dt.Columns.push(columnName);
			var countValue = 0;
			for (var i=0; i<this.Rows.length; i++)
			{
				countValue++;
			}
			var dataTableRow = new Object();
			dataTableRow[columnName] = countValue.toString();
			dt.Rows.push(dataTableRow);
		}
		
		return dt;
	}
	
	this.min = function(columnName)
	{
		var dt = null;
		
		if ((columnName != null) && (this.GrouppingColumn != null))
		{
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			dt.Columns.push(this.GrouppingColumn);
			dt.Columns.push(columnName);
			var fieldValue = this.Rows[0][this.GrouppingColumn];
			var minValue = null;
			for (var i=0; i<this.Rows.length; i++)
			{
				if (minValue == null)
				{
					minValue = new Number(this.Rows[i][columnName].replace(',', ''));
				}
				else
				{
					if (this.Rows[i][this.GrouppingColumn] == fieldValue)
					{
						if (minValue > new Number(this.Rows[i][columnName].replace(',', '')))
						{
							minValue = new Number(this.Rows[i][columnName].replace(',', ''));
						}
					}
					else
					{
						var dataTableRow = new Object();
						dataTableRow[this.GrouppingColumn] = fieldValue;
						dataTableRow[columnName] = this.convertToCurrency(minValue.toString());
						dt.Rows.push(dataTableRow);
						
						fieldValue = this.Rows[i][this.GrouppingColumn];
						minValue = new Number(this.Rows[i][columnName].replace(',', ''));
					}
				}
			}
			if (minValue != 0)
			{
				var dataTableRow = new Object();
				dataTableRow[this.GrouppingColumn] = fieldValue;
				dataTableRow[columnName] = this.convertToCurrency(minValue.toString());
				dt.Rows.push(dataTableRow);
			}
		}
		else if (columnName != null)
		{
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			dt.Columns.push(columnName);
			var minValue = null;
			for (var i=0; i<this.Rows.length; i++)
			{
				if (minValue == null)
				{
					minValue = new Number(this.Rows[i][columnName].replace(',', ''));
				}
				else
				{
					if (minValue > new Number(this.Rows[i][columnName].replace(',', '')))
					{
						minValue = new Number(this.Rows[i][columnName].replace(',', ''));
					}
				}
			}
			var dataTableRow = new Object();
			dataTableRow[columnName] = this.convertToCurrency(minValue.toString());
			dt.Rows.push(dataTableRow);
		}
		
		return dt;
	}
	
	this.max = function(columnName)
	{
		var dt = null;
		
		if ((columnName != null) && (this.GrouppingColumn != null))
		{
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			dt.Columns.push(this.GrouppingColumn);
			dt.Columns.push(columnName);
			var fieldValue = this.Rows[0][this.GrouppingColumn];
			var maxValue = null;
			for (var i=0; i<this.Rows.length; i++)
			{
				if (maxValue == null)
				{
					maxValue = new Number(this.Rows[i][columnName].replace(',', ''));
				}
				else
				{
					if (this.Rows[i][this.GrouppingColumn] == fieldValue)
					{
						if (maxValue < new Number(this.Rows[i][columnName].replace(',', '')))
						{
							maxValue = new Number(this.Rows[i][columnName].replace(',', ''));
						}
					}
					else
					{
						var dataTableRow = new Object();
						dataTableRow[this.GrouppingColumn] = fieldValue;
						dataTableRow[columnName] = this.convertToCurrency(maxValue.toString());
						dt.Rows.push(dataTableRow);
						
						fieldValue = this.Rows[i][this.GrouppingColumn];
						maxValue = new Number(this.Rows[i][columnName].replace(',', ''));
					}
				}
			}
			if (maxValue != 0)
			{
				var dataTableRow = new Object();
				dataTableRow[this.GrouppingColumn] = fieldValue;
				dataTableRow[columnName] = this.convertToCurrency(maxValue.toString());
				dt.Rows.push(dataTableRow);
			}
		}
		else if (columnName != null)
		{
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			dt.Columns.push(columnName);
			var maxValue = null;
			for (var i=0; i<this.Rows.length; i++)
			{
				if (maxValue == null)
				{
					maxValue = new Number(this.Rows[i][columnName].replace(',', ''));
				}
				else
				{
					if (maxValue < new Number(this.Rows[i][columnName].replace(',', '')))
					{
						maxValue = new Number(this.Rows[i][columnName].replace(',', ''));
					}
				}
			}
			var dataTableRow = new Object();
			dataTableRow[columnName] = this.convertToCurrency(maxValue.toString());
			dt.Rows.push(dataTableRow);
		}
		
		return dt;
	}
	
	this.avg = function(columnName)
	{
		var dt = null;
		
		if ((columnName != null) && (this.GrouppingColumn != null))
		{
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			dt.Columns.push(this.GrouppingColumn);
			dt.Columns.push(columnName);
			var fieldValue = this.Rows[0][this.GrouppingColumn];
			var sumValue = new Number(this.Rows[0][columnName].replace(',', ''));
			var countValue = 1;
			for (var i=1; i<this.Rows.length; i++)
			{
				if (this.Rows[i][this.GrouppingColumn] == fieldValue)
				{
					sumValue += new Number(this.Rows[i][columnName].replace(',', ''));
					countValue++;
				}
				else
				{
					var dataTableRow = new Object();
					dataTableRow[this.GrouppingColumn] = fieldValue;
					dataTableRow[columnName] = this.convertToCurrency((sumValue / countValue).toString());
					dt.Rows.push(dataTableRow);
					
					fieldValue = this.Rows[i][this.GrouppingColumn];
					sumValue = new Number(this.Rows[i][columnName].replace(',', ''));
					countValue = 1;
				}
			}
			if ((sumValue != 0) && (countValue != 0))
			{
				var dataTableRow = new Object();
				dataTableRow[this.GrouppingColumn] = fieldValue;
				dataTableRow[columnName] = this.convertToCurrency((sumValue / countValue).toString());
				dt.Rows.push(dataTableRow);
			}
		}
		else if (columnName != null)
		{
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			dt.Columns.push(columnName);
			var sumValue = 0;
			var countValue = 0;
			for (var i=0; i<this.Rows.length; i++)
			{
				sumValue += new Number(this.Rows[i][columnName].replace(',', ''));
				countValue++;
			}
			var dataTableRow = new Object();
			dataTableRow[columnName] = this.convertToCurrency((sumValue / countValue).toString());
			dt.Rows.push(dataTableRow);
		}
		
		return dt;
	}
	
	this.distinct = function(columnName)
	{
		var dt = null;
		
		if ((columnName != null) && (this.GrouppingColumn != null))
		{
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			dt.Columns.push(this.GrouppingColumn);
			dt.Columns.push(columnName);
			var fieldValue = this.Rows[0][this.GrouppingColumn];
			var distinctValues = new Array();
			for (var i=0; i<this.Rows.length; i++)
			{
				if (this.Rows[i][this.GrouppingColumn] == fieldValue)
				{
					var exists = false;
					for (var j=0; j<distinctValues.length; j++)
					{
						if (distinctValues[j] == this.Rows[i][columnName])
						{
							exists = true;
						}
					}
					if (exists == false)
					{
						distinctValues.push(this.Rows[i][columnName]);
					}
				}
				else
				{
					for (var k=0; k<distinctValues.length; k++)
					{
						var dataTableRow = new Object();
						dataTableRow[this.GrouppingColumn] = fieldValue;
						dataTableRow[columnName] = distinctValues[k];
						dt.Rows.push(dataTableRow);
					}
					
					fieldValue = this.Rows[i][this.GrouppingColumn];
					distinctValues = new Array();
					distinctValues.push(this.Rows[i][columnName]);
				}
			}
			if (distinctValues.length > 0)
			{
				for (var i=0; i<distinctValues.length; i++)
				{
					var dataTableRow = new Object();
					dataTableRow[this.GrouppingColumn] = fieldValue;
					dataTableRow[columnName] = distinctValues[i];
					dt.Rows.push(dataTableRow);
				}
			}
		}
		else if (columnName != null)
		{
			dt = new DataTable(this.Name, this.HeaderStyle, this.BodyStyle);
			dt.Columns.push(columnName);
			var distinctValues = new Array();
			for (var i=0; i<this.Rows.length; i++)
			{
				var exists = false;
				for (var j=0; j<distinctValues.length; j++)
				{
					if (distinctValues[j] == this.Rows[i][columnName])
					{
						exists = true;
					}
				}
				if (exists == false)
				{
					distinctValues.push(this.Rows[i][columnName]);
				}
			}
			for (var i=0; i<distinctValues.length; i++)
			{
				var dataTableRow = new Object();
				dataTableRow[columnName] = distinctValues[i];
				dt.Rows.push(dataTableRow);
			}
		}
		
		return dt;
	}
	
	this.convertToCurrency = function(value)
	{
		var currencyValue = "";
		
		var currencyValueArray = new Array();
		var values = value.split('.');
		for (var i=values[0].length-1; i>=0; i--)
		{
			if ((((values[0].length-i-1) % 3) == 0) && ((values[0].length-i-1) > 0))
			{
				currencyValueArray.push(',');
				currencyValueArray.push(values[0].charAt(i));
			}
			else
			{
				currencyValueArray.push(values[0].charAt(i));
			}
		}
		currencyValueArray.reverse();
		for (var i=0; i<currencyValueArray.length; i++)
		{
			currencyValue += currencyValueArray[i];
		}
		if (values[1] != null)
		{
			if (values[1].length > 2)
			{
				var rest = new Number("0." + values[1]);
				rest = Math.round(rest * 100);
				if (rest < 10)
				{
					currencyValue += ".0" + rest.toString();
				}
				else
				{
					currencyValue += "." + rest.toString();
				}
			}
			else
			{
				currencyValue += "." + values[1];
			}
		}
		else
		{
			currencyValue += ".00";
		}
		
		return currencyValue;
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Elektromehanika d.o.o. Nis
Serbia Serbia
He has a master degree in Computer Science at Faculty of Electronics in Nis (Serbia), and works as a C++/C# application developer for Windows platforms since 2001. He likes traveling, reading and meeting new people and cultures.

Comments and Discussions