Repository: proullon/ramsql
Pull request #29 (2018-12-12 19:59:17)

add support for comparing dates
This patch enables comparing dates using > and <.

A feature that is still missing is is using >= and <=. This is
a more fundamental change that should follow in another
patch in the future.


Patch:
diff --git a/engine/operator.go b/engine/operator.go
index 329f007..7d6a0b4 100644
--- a/engine/operator.go
+++ b/engine/operator.go
@@ -2,10 +2,10 @@ package engine
 
 import (
 	"fmt"
-	"strconv"
-
 	"github.com/proullon/ramsql/engine/log"
 	"github.com/proullon/ramsql/engine/parser"
+	"strconv"
+	"time"
 )
 
 // Operator compares 2 values and return a boolean
@@ -25,6 +25,23 @@ func NewOperator(token int, lexeme string) (Operator, error) {
 	return nil, fmt.Errorf("Operator '%s' does not exist", lexeme)
 }
 
+func convToDate(t interface{}) (time.Time, error) {
+
+	switch t := t.(type) {
+	default:
+		log.Debug("convToDate> unexpected type %T\n", t)
+		return time.Time{}, fmt.Errorf("unexpected internal type %T", t)
+	case string:
+		d, err :=parser.ParseDate(string(t))
+		if err != nil {
+			return time.Time{}, fmt.Errorf("cannot parse date %v", t)
+		}
+
+		return *d, nil
+	}
+
+}
+
 func convToFloat(t interface{}) (float64, error) {
 
 	switch t := t.(type) {
@@ -55,19 +72,36 @@ func greaterThanOperator(leftValue Value, rightValue Value) bool {
 		rvalue = rightValue.lexeme
 	}
 
+	var leftDate time.Time
+	var isDate bool
+
 	left, err = convToFloat(leftValue.v)
 	if err != nil {
-		log.Debug("GreateThanOperator> %s\n", err)
-		return false
+		leftDate, err = convToDate(leftValue.v)
+		if err != nil {
+			log.Debug("GreaterThanOperator> %s\n", err)
+			return false
+		}
+		isDate = true
 	}
 
-	right, err = convToFloat(rvalue)
+	if !isDate {
+		right, err = convToFloat(rvalue)
+		if err != nil {
+			log.Debug("GreaterThanOperator> %s\n", err)
+			return false
+		}
+
+		return left > right
+	}
+
+	rightDate, err := convToDate(rvalue)
 	if err != nil {
-		log.Debug("GreateThanOperator> %s\n", err)
+		log.Debug("GreaterThanOperator> %s\n", err)
 		return false
 	}
 
-	return left > right
+	return leftDate.After(rightDate)
 }
 
 func lessThanOperator(leftValue Value, rightValue Value) bool {
@@ -82,19 +116,36 @@ func lessThanOperator(leftValue Value, rightValue Value) bool {
 		rvalue = rightValue.lexeme
 	}
 
+	var leftDate time.Time
+	var isDate bool
+
 	left, err = convToFloat(leftValue.v)
 	if err != nil {
-		log.Debug("lessThanOperator> %s\n", err)
-		return false
+		leftDate, err = convToDate(leftValue.v)
+		if err != nil {
+			log.Debug("LessThanOperator> %s\n", err)
+			return false
+		}
+		isDate = true
+	}
+
+	if !isDate {
+		right, err = convToFloat(rvalue)
+		if err != nil {
+			log.Debug("LessThanOperator> %s\n", err)
+			return false
+		}
+
+		return left < right
 	}
 
-	right, err = convToFloat(rvalue)
+	rightDate, err := convToDate(rvalue)
 	if err != nil {
-		log.Debug("lessThanOperator> %s\n", err)
+		log.Debug("LessThanOperator> %s\n", err)
 		return false
 	}
 
-	return left < right
+	return leftDate.Before(rightDate)
 }
 
 // EqualityOperator checks if given value are equal
diff --git a/engine/parser/date.go b/engine/parser/date.go
index e42f6ad..d38f7e8 100644
--- a/engine/parser/date.go
+++ b/engine/parser/date.go
@@ -11,6 +11,8 @@ const DateLongFormat = "2006-01-02 15:04:05.999999999 -0700 MST"
 // DateShortFormat is a short format
 const DateShortFormat = "2006-Jan-02"
 
+const DateNumberFormat = "2006-01-02"
+
 // ParseDate intends to parse all SQL date format
 func ParseDate(data string) (*time.Time, error) {
 	t, err := time.Parse(DateLongFormat, data)
@@ -28,5 +30,10 @@ func ParseDate(data string) (*time.Time, error) {
 		return &t, nil
 	}
 
+	t, err = time.Parse(DateNumberFormat, data)
+	if err == nil {
+		return &t, nil
+	}
+
 	return nil, fmt.Errorf("not a date")
 }
diff --git a/go.mod b/go.mod
new file mode 100644
index 0000000..dcf9ec2
--- /dev/null
+++ b/go.mod
@@ -0,0 +1,6 @@
+module github.com/proullon/ramsql
+
+require (
+	github.com/go-gorp/gorp v2.0.0+incompatible
+	github.com/lib/pq v1.0.0
+)
diff --git a/go.sum b/go.sum
new file mode 100644
index 0000000..3d29077
--- /dev/null
+++ b/go.sum
@@ -0,0 +1,4 @@
+github.com/go-gorp/gorp v2.0.0+incompatible h1:dIQPsBtl6/H1MjVseWuWPXa7ET4p6Dve4j3Hg+UjqYw=
+github.com/go-gorp/gorp v2.0.0+incompatible/go.mod h1:7IfkAQnO7jfT/9IQ3R9wL1dFhukN6aQxzKTHnkxzA/E=
+github.com/lib/pq v1.0.0 h1:X5PMW56eZitiTeO7tKzZxFCSpbFZJtkMMooicw2us9A=
+github.com/lib/pq v1.0.0/go.mod h1:5WUZQaWbwv1U+lTReE5YruASi9Al49XbQIvNi/34Woo=


Test patch:
diff --git a/driver/driver_test.go b/driver/driver_test.go
index efcde2a..2634c19 100644
--- a/driver/driver_test.go
+++ b/driver/driver_test.go
@@ -276,6 +276,120 @@ func TestBatch(t *testing.T) {
 
 }
 
+func TestCompareDateGT(t *testing.T) {
+	log.UseTestLogger(t)
+
+	db, err := sql.Open("ramsql", "TestCompareDateGT")
+	if err != nil {
+		t.Fatalf("sql.Open : Error : %s", err)
+	}
+	defer db.Close()
+
+	_, err = db.Exec("CREATE TABLE comp (dat DATE);")
+	if err != nil {
+		t.Fatalf("Cannot create table: %s", err)
+	}
+
+	_, err = db.Exec("INSERT INTO comp (dat) VALUES ('2018-01-01')")
+	if err != nil {
+		t.Fatal("Cannot insert value")
+	}
+	_, err = db.Exec("INSERT INTO comp (dat) VALUES ('2019-01-01')")
+	if err != nil {
+		t.Fatal("Cannot insert value")
+	}
+	_, err = db.Exec("INSERT INTO comp (dat) VALUES ('2020-01-01')")
+	if err != nil {
+		t.Fatal("Cannot insert value")
+	}
+
+	query := "SELECT dat FROM comp WHERE dat > '2018-03-03'"
+
+	rows, err := db.Query(query, )
+	if err != nil {
+		t.Fatalf("sql.Query: %s", err)
+	}
+
+	var nb int
+	for rows.Next() {
+		var dat time.Time
+		if err := rows.Scan(&dat); err != nil {
+			t.Fatalf("Cannot scan row: %s", err)
+		}
+		unwantedDate, err := time.Parse("2006-01-02", "2018-01-01")
+		if err != nil {
+			t.Fatal("Cannot parse unwanted date", err)
+		}
+		if dat.Equal(unwantedDate) {
+			t.Fatalf("Unwanted row: %v", dat)
+		}
+
+		nb++
+	}
+
+	if nb != 2 {
+		t.Fatalf("Unwanted number of rows %d", nb)
+	}
+
+}
+
+func TestCompareDateLT(t *testing.T) {
+	log.UseTestLogger(t)
+
+	db, err := sql.Open("ramsql", "TestCompareDateLT")
+	if err != nil {
+		t.Fatalf("sql.Open : Error : %s", err)
+	}
+	defer db.Close()
+
+	_, err = db.Exec("CREATE TABLE comp (dat DATE);")
+	if err != nil {
+		t.Fatalf("Cannot create table: %s", err)
+	}
+
+	_, err = db.Exec("INSERT INTO comp (dat) VALUES ('2018-01-01')")
+	if err != nil {
+		t.Fatal("Cannot insert value")
+	}
+	_, err = db.Exec("INSERT INTO comp (dat) VALUES ('2019-01-01')")
+	if err != nil {
+		t.Fatal("Cannot insert value")
+	}
+	_, err = db.Exec("INSERT INTO comp (dat) VALUES ('2020-01-01')")
+	if err != nil {
+		t.Fatal("Cannot insert value")
+	}
+
+	query := "SELECT dat FROM comp WHERE dat < '2019-03-03'"
+
+	rows, err := db.Query(query, )
+	if err != nil {
+		t.Fatalf("sql.Query: %s", err)
+	}
+
+	var nb int
+	for rows.Next() {
+		var dat time.Time
+		if err := rows.Scan(&dat); err != nil {
+			t.Fatalf("Cannot scan row: %s", err)
+		}
+		unwantedDate, err := time.Parse("2006-01-02", "2020-01-01")
+		if err != nil {
+			t.Fatal("Cannot parse unwanted date", err)
+		}
+		if dat.Equal(unwantedDate) {
+			t.Fatalf("Unwanted row: %v", dat)
+		}
+
+		nb++
+	}
+
+	if nb != 2 {
+		t.Fatalf("Unwanted number of rows %d", nb)
+	}
+
+}
+
 func TestDate(t *testing.T) {
 	log.UseTestLogger(t)