Updates to DDL Builder, for improved date parsing

7 files changed, 25 insertions, 202 deletions

diff --git a/src/main/webapp/javascripts/libs/ddl_builder/ddl_builder.js b/src/main/webapp/javascripts/libs/ddl_builder/ddl_builder.js
index a2797dc..dbdc321 100644
--- a/src/main/webapp/javascripts/libs/ddl_builder/ddl_builder.js
+++ b/src/main/webapp/javascripts/libs/ddl_builder/ddl_builder.js
@@ -72,7 +72,7 @@ define(
 		this.compiledTemplate = Handlebars.compile(this.ddlTemplate);
 		this.setup(args);
 		return this;
-	}
+	};
 	
 	ddl_builder.prototype.setup = function (settings) {
 		for (var opt in settings)
@@ -87,7 +87,7 @@ define(
 			this.definition.tableName = settings.tableName;
 		
 		return this;
-	}
+	};
 	
 	ddl_builder.prototype.setupForDBType = function (type,separator) {
 		
@@ -158,7 +158,7 @@ define(
 
 		}
 		return this;
-	}
+	};
 	
 	ddl_builder.prototype.populateDBTypes = function () {
 		for (var i=0;i<this.definition.columns.length;i++)
@@ -244,15 +244,22 @@ define(
 		
 		}
 		return {status: true, separator: found_separator, column_count: column_count};
-	}
+	};
 	
 	ddl_builder.prototype.parse = function (raw) {
 
-		
+		/* 
+		 * brokenDateChecker is used to eliminate strings that for some reason pass Chrome's standard of a 'valid' date, and
+		 * yet are not worth considering as such. Chrome will take garbage like 'ABC 1' as an input to `new Date()`, returning
+		 * January 1st 2001 for some reason.  This regex will allow a lot of fuzzy input formats to still pass, but only really 
+		 * keep meaningful entries. This isn't a problem for Firefox or Safari. I haven't checked IE.
+		 */ 
+		var brokenDateChecker = /^(?!Jan)(?!Feb)(?!Mar)(?!Apr)(?!May)(?!Jun)(?!Jul)(?!Aug)(?!Sep)(?!Oct)(?!Nov)(?!Dec)[A-Za-z\ \-\_]+\d+\s*$/,
+			result = {}, lines = [], elements = [], tmpRow = [], i = 0, j = 0, value = "";
 
 		if (!this.valueSeparator.length)
 		{	
-			var result = this.guessValueSeparator(raw);
+			result = this.guessValueSeparator(raw);
 			if (!result.status)
 				return "ERROR! " + result.message;
 			else
@@ -262,11 +269,11 @@ define(
 			}
 		}
 		
-		var lines = raw.split("\n");
+		lines = raw.split("\n");
 		
-		for (var i=0;i<lines.length;i++)
+		for (i=0;i<lines.length;i++)
 		{
-			var elements = $.trim(lines[i]).split(this.valueSeparator);
+			elements = $.trim(lines[i]).split(this.valueSeparator);
 
 			if ($.trim(lines[i]).length && 
 					(
@@ -280,9 +287,9 @@ define(
 			{
 				if (! this.definition.columns.length)
 				{	
-					for (var j = 0; j < elements.length; j++)
+					for (j = 0; j < elements.length; j++)
 					{	
-							var value = $.trim(elements[j]);
+							value = $.trim(elements[j]);
 							if (value.length)
 								this.definition.columns.push({"name": value});
 							else
@@ -291,20 +298,19 @@ define(
 				}
 				else
 				{
-				
-					var tmpRow = [];
-					for (var j = 0; j < elements.length; j++)
+					tmpRow = [];
+					for (j = 0; j < elements.length; j++)
 					{
 						if (this.definition.columns[j] !== false)
 						{
-							var value = $.trim(elements[j]).replace(/'/g, "''");
+							value = $.trim(elements[j]).replace(/'/g, "''");
 	
 							// if the current field is not a number, or if we have previously decided that this one of the non-numeric field types...
 							if (isNaN(value) || this.definition.columns[j].type == 'dateType' || this.definition.columns[j].type == 'charType')
 							{
 								
 								// if we haven't previously decided that this is a character field, and it can be cast as a date, then declare it a date 
-								if (this.definition.columns[j].type != 'charType' && !isNaN(Date.parse(value)) ) 
+								if (this.definition.columns[j].type != 'charType' && !(isNaN(Date.parse(value)) || value.match(brokenDateChecker)) ) 
 									this.definition.columns[j].type = "dateType";
 								else
 									this.definition.columns[j].type = "charType";
@@ -335,7 +341,7 @@ define(
 		this.populateDBTypes();
 		this.populateWrappers();
 		return this.render();		
-	}
+	};
 
 	/* HandlebarsJS-using code below */
 	
@@ -362,7 +368,7 @@ define(
 			return new Handlebars.SafeString("'" + this.v.replace(/'/g, "''") + "'");
 		
 		if (colType == 'dateType')
-			return new Handlebars.SafeString("'" + dateFormat(this.v, root.dateFormatMask) + "'");
+			return new Handlebars.SafeString("'" + dateFormat("UTC:" + this.v, root.dateFormatMask) + "'");
 		
 		return this.v;
 	});
@@ -374,7 +380,7 @@ define(
 	
 	ddl_builder.prototype.render = function () {
 		return this.compiledTemplate($.extend(this.definition, {"separator": this.statement_separator}));		
-	}
+	};
 	
 	return ddl_builder;
 	
diff --git a/src/main/webapp/javascripts/libs/ddl_builder/qunit/columnTypes.js b/src/main/webapp/javascripts/libs/ddl_builder/qunit/columnTypes.js
deleted file mode 100644
index 6ad3301..0000000
--- a/src/main/webapp/javascripts/libs/ddl_builder/qunit/columnTypes.js
+++ /dev/null
@@ -1,8 +0,0 @@
-define(["jQuery","QUnit", "DDLBuilder/ddl_builder"], function ($,QUnit,DDLBuilder) {
-	
-	return function (id,types) {
-			var ddl_builder = new DDLBuilder({ddlTemplate: "{{#each_with_index columns}}{{#if index}},{{/if}}{{db_type}}{{/each_with_index}}}"});
-			QUnit.equal(ddl_builder.parse($("#" + id).html()), types, "Column types");
-		};
-
-});
diff --git a/src/main/webapp/javascripts/libs/ddl_builder/qunit/fixture.html b/src/main/webapp/javascripts/libs/ddl_builder/qunit/fixture.html
deleted file mode 100644
index d2d5d40..0000000
--- a/src/main/webapp/javascripts/libs/ddl_builder/qunit/fixture.html
+++ /dev/null
@@ -1,106 +0,0 @@
-<div id="ddlInputText">
-	<span id="simplestFormattedCSV" types="int,int" valueSeparator="," headers="a,b" recordCount="1">
-		a,b
-		1,2
-	</span>
-	<span id="twoColumnFixedWidth" types="int,varchar(5)" valueSeparator="/\s\s+/" headers="Period,Result" recordCount="13">
-		Period   Result
-		1        Green
-		1        Blue
-		1        Blue
-		1        Red
-		1        Blue
-		1        Blue
-		1        Blue
-		2        Green 
-		2        Green 
-		2        Green
-		2        Blue
-		2        Red
-		2        Red
-	</span>
-	<span id="fixedWidthDates" types="datetime,datetime,int,int,varchar(7)" valueSeparator="/\s\s+/" headers="date_due,date_paid,amount_due,amount_paid,category_type" recordCount="4">
-		date_due        date_paid         amount_due     amount_paid    category_type
-		2012-08-12      2012-08-12        500            450            Income
-		2012-08-13      2012-08-17        200            300            Expense
-		2012-09-15      2012-09-13        300            300            Income
-		2012-09-17      2012-09-16        100            100            Income
-	</span>
-	<span id="centeredPipes" types="varchar(1),int" valueSeparator="|" headers="L,N" recordCount="9">
-		   L    |    N
-		-------------------
-		   A    |    1
-		   A    |    3
-		   A    |    5
-		   B    |    5
-		   B    |    7
-		   B    |    9
-		   C    |    1
-		   C    |    2
-		   C    |    3
-	</span>
-	<span id="ASCII_bordered" types="int,int,datetime,int" valueSeparator="|" headers="IdPayment,Costs_IdCost,Date,Amount" recordCount="3">
-		+-----------+--------------+----------+--------+
-		| IdPayment | Costs_IdCost |   Date   | Amount |
-		+-----------+--------------+----------+--------+
-		|     1     |      2       |2012/09/10|  1000  |
-		+-----------+--------------+----------+--------+
-		|     2     |      2       |2012/09/20|  3000  |
-		+-----------+--------------+----------+--------+
-		|     3     |      2       |2012/10/01|  5000  |
-		+-----------+--------------+----------+--------+
-	</span>
-	<span id="fixedWidthWithSpaces" types="varchar(6),varchar(13)" valueSeparator="/\s\s+/" headers="Cul 1,Cul 2" recordCount="8">
-		Cul 1   Cul 2  
-		=====================
-		A10000  Test 
-		A10001  Test 123 
-		A20000  Test 1
-		A20001  Test 999 
-		A30000  Test 2  
-		A30002  Test 5555 
-		A40000  Test 3   
-		A40006  Test 84384848
-	</span>
-	<span id="pipedColumns" types="varchar(8),int,numeric" valueSeparator="|" headers="Scoreband,TotalNoOfPeople,AvgScore" recordCount="4">
-		Scoreband| TotalNoOfPeople | AvgScore
-		--------------------------------
-		-5 to 0  | 2               | -2
-		0 to 5   | 3               |  2
-		5 to 10  | 2               |  8
-		10 to 15 | 3               | 13.3
-	</span>
-	<span id="variableSpaceDelimited" types="varchar(3),varchar(4),varchar(5)" valueSeparator="/\s\s+/" headers="c1,c2,c3" recordCount="4">
-	c1   c2   c3
-	1A2  cat  black
-	1G2  dog  red
-	B11  frog green
-	1G2  girl  red
-	</span>
-	<span id="bracketHeaders" types="int,varchar(3),datetime" valueSeparator="/\s\s+/" headers="Serial Number,LID,Last Updated Date" recordCount="12">
-[Serial Number]  [LID]   [Last Updated Date]
---------------------------------------    
-123456            AAA     2012-09-24
-123456            AAA     2012-09-23
-123456            AAA     2012-09-22
-123456            AAA     2012-09-21
-123456            BBB     2012-09-20
-123456            BBB     2012-09-19
-123456            AAA     2012-09-18
-123456            AAA     2012-09-17
-123456            AAA     2012-09-16
-234567            BBB     2012-09-24
-234567            BBB     2012-09-23
-234567            AAA     2012-09-22	
-	</span>
-	<span id="singleSpacedColumnNames" types="int,int,int,int,int" valueSeparator="/\s+/" headers="customdata,check,service,loc,value" recordCount="6">
-customdata check service loc value
-  101        0    0        4 4
-  101        0    0        3   3
-  101        5     4       4    3
-  102        0     0       1     2
-  102        4     4       3     3
-  103        0     0       4     4
-  	</span>
-	
-</div>
diff --git a/src/main/webapp/javascripts/libs/ddl_builder/qunit/guessValueSeparators.js b/src/main/webapp/javascripts/libs/ddl_builder/qunit/guessValueSeparators.js
deleted file mode 100644
index c118837..0000000
--- a/src/main/webapp/javascripts/libs/ddl_builder/qunit/guessValueSeparators.js
+++ /dev/null
@@ -1,15 +0,0 @@
-define(["jQuery","QUnit", "DDLBuilder/ddl_builder"], function ($,QUnit,DDLBuilder) {
-	
-	return function(id,sep) {
-		
-		var ddl_builder = new DDLBuilder(),
-			result = ddl_builder.guessValueSeparator($("#" + id).html());
-		
-		if (result.separator)
-			QUnit.equal(ddl_builder.guessValueSeparator($("#" + id).html()).separator.toString(), sep.toString(), "Guessing Value Separators");
-		else
-			QUnit.ok(false, "Guessing value separators failed with message:" + result.message);
-	};
-
-
-});
diff --git a/src/main/webapp/javascripts/libs/ddl_builder/qunit/headerNames.js b/src/main/webapp/javascripts/libs/ddl_builder/qunit/headerNames.js
deleted file mode 100644
index d8107ad..0000000
--- a/src/main/webapp/javascripts/libs/ddl_builder/qunit/headerNames.js
+++ /dev/null
@@ -1,8 +0,0 @@
-define(["jQuery","QUnit", "DDLBuilder/ddl_builder"], function ($,QUnit,DDLBuilder) {
-
-	return function (id,headers) {
-		var ddl_builder = new DDLBuilder({ddlTemplate: "{{#each_with_index columns}}{{#if index}},{{/if}}{{name}}{{/each_with_index}}}"});
-		QUnit.equal(ddl_builder.parse($("#" + id).html()), headers, "Finding header names");
-	};
-	
-});
diff --git a/src/main/webapp/javascripts/libs/ddl_builder/qunit/main.js b/src/main/webapp/javascripts/libs/ddl_builder/qunit/main.js
deleted file mode 100644
index aba2ec9..0000000
--- a/src/main/webapp/javascripts/libs/ddl_builder/qunit/main.js
+++ /dev/null
@@ -1,28 +0,0 @@
-define([
-	"jQuery",
-	"QUnit",
-	"text!./fixture.html",
-	"./columnTypes",
-	"./guessValueSeparators",
-	"./headerNames",
-	"./recordCount"
-	], 
-
-	function ($,QUnit,fixtureContent,
-			columnTypes,guessValueSeparators,
-			headerNames,recordCount) {
-		
-		$("#qunit-fixture").append(fixtureContent);
-		
-		$("#qunit-fixture #ddlInputText span").each(function () {
-			var $this = $(this);
-			QUnit.test("Parsing " + $this.attr('id'), function () {
-				columnTypes($this.attr('id'), $this.attr('types'));
-				guessValueSeparators($this.attr('id'), $this.attr('valueSeparator'));
-				headerNames($this.attr('id'), $this.attr('headers'));
-				recordCount($this.attr('id'), $this.attr('recordCount'));
-			});
-		});
-		
-	}
-)
\ No newline at end of file
diff --git a/src/main/webapp/javascripts/libs/ddl_builder/qunit/recordCount.js b/src/main/webapp/javascripts/libs/ddl_builder/qunit/recordCount.js
deleted file mode 100644
index 0ec59fd..0000000
--- a/src/main/webapp/javascripts/libs/ddl_builder/qunit/recordCount.js
+++ /dev/null
@@ -1,18 +0,0 @@
-define(["jQuery","QUnit", "DDLBuilder/ddl_builder"], function ($,QUnit,DDLBuilder) {
-	
-	return function (id,count) {
-			var ddl_builder = new DDLBuilder({ddlTemplate: "[{{#each_with_index data}}{{#if index}},{{/if}}}{{index}}{{/each_with_index}}}]"});
-			var result = ddl_builder.parse($("#" + id).html()),
-				parsedResult = false;
-			
-			try {
-				parsedResult = $.parseJSON(result); 
-			} catch (err){}
-			
-			if (parsedResult)
-				QUnit.equal(parsedResult.length, count, "Getting Record Count");
-			else
-				QUnit.ok(false, "Getting Record Count failed: Unable to parse result to JSON array ("+ result +")");
-		};
-
-});