Beispiel #1
0
func TestExecGroupBy(t *testing.T) {
	// TODO:  this test is bad, it occasionally fails
	sqlText := `
		select 
	        user_id, count(user_id), avg(price)
	    FROM orders
	    GROUP BY user_id
	`
	ctx := td.TestContext(sqlText)
	job, err := exec.BuildSqlJob(ctx)
	assert.Tf(t, err == nil, "no error %v", err)

	msgs := make([]schema.Message, 0)
	resultWriter := exec.NewResultBuffer(ctx, &msgs)
	job.RootTask.Add(resultWriter)

	err = job.Setup()
	assert.T(t, err == nil)
	err = job.Run()
	time.Sleep(time.Millisecond * 10)
	assert.Tf(t, err == nil, "no error %v", err)
	assert.Tf(t, len(msgs) == 2, "should have grouped orders into 2 users %v", len(msgs))
	u.Debugf("msg: %#v", msgs[0])
	row := msgs[0].(*datasource.SqlDriverMessageMap).Values()
	u.Debugf("row: %#v", row)
	assert.Tf(t, len(row) == 3, "expects 3 cols but got %v", len(row))
	assert.T(t, row[0] == "9Ip1aKbeZe2njCDM", "%#v", row)
	// I really don't like this float behavior?
	assert.Tf(t, int(row[1].(int64)) == 2, "expected 2 orders for %v", row)
	assert.Tf(t, int(row[2].(float64)) == 30, "expected avg=30 for price %v", row)

	sqlText = `
		select 
	        avg(len(email))
	    FROM users
	    GROUP BY "-"
	`
	ctx = td.TestContext(sqlText)
	job, err = exec.BuildSqlJob(ctx)
	assert.Tf(t, err == nil, "no error %v", err)

	msgs = make([]schema.Message, 0)
	resultWriter = exec.NewResultBuffer(ctx, &msgs)
	job.RootTask.Add(resultWriter)

	err = job.Setup()
	assert.T(t, err == nil)
	err = job.Run()
	time.Sleep(time.Millisecond * 10)
	assert.Tf(t, err == nil, "no error %v", err)
	assert.Tf(t, len(msgs) == 1, "should have grouped orders into 1 record %v", len(msgs))
	u.Debugf("msg: %#v", msgs[0])
	row = msgs[0].(*datasource.SqlDriverMessageMap).Values()
	u.Debugf("row: %#v", row)
	assert.Tf(t, len(row) == 1, "expects 1 cols but got %v", len(row))
	assert.Tf(t, int(row[0].(float64)) == 13, "expected avg(len(email))=15 for %v", int(row[0].(float64)))
}
Beispiel #2
0
func TestExecHaving(t *testing.T) {
	sqlText := `
		select 
	        user_id, count(user_id) AS order_ct
	    FROM orders
	    GROUP BY user_id
	    HAVING order_ct > 1
	`
	ctx := td.TestContext(sqlText)
	job, err := exec.BuildSqlJob(ctx)
	assert.Tf(t, err == nil, "no error %v", err)

	msgs := make([]schema.Message, 0)
	resultWriter := exec.NewResultBuffer(ctx, &msgs)
	job.RootTask.Add(resultWriter)

	err = job.Setup()
	assert.T(t, err == nil)
	err = job.Run()
	time.Sleep(time.Millisecond * 10)
	assert.Tf(t, err == nil, "no error %v", err)
	assert.Tf(t, len(msgs) == 1, "should have filtered HAVING orders into 1 users %v", len(msgs))
	u.Debugf("msg: %#v", msgs[0])
	row := msgs[0].(*datasource.SqlDriverMessageMap).Values()
	u.Debugf("row: %#v", row)
	assert.Tf(t, len(row) == 2, "expects 2 cols but got %v", len(row))
	assert.T(t, row[0] == "9Ip1aKbeZe2njCDM")
	// I really don't like this float behavior?
	assert.Tf(t, int(row[1].(int64)) == 2, "expected 2 orders for %v", row)
}
Beispiel #3
0
func TestExecSelectWhere(t *testing.T) {
	sqlText := `
		select 
	        user_id, email, referral_count * 2, 5, yy(reg_date) > 10
	    FROM users
	    WHERE yy(reg_date) > 10 
	`
	ctx := td.TestContext(sqlText)
	job, err := exec.BuildSqlJob(ctx)
	assert.Tf(t, err == nil, "no error %v", err)

	msgs := make([]schema.Message, 0)
	resultWriter := exec.NewResultBuffer(ctx, &msgs)
	job.RootTask.Add(resultWriter)

	err = job.Setup()
	assert.T(t, err == nil)
	err = job.Run()
	time.Sleep(time.Millisecond * 10)
	assert.Tf(t, err == nil, "no error %v", err)
	assert.Tf(t, len(msgs) == 1, "should have filtered out 2 messages %v", len(msgs))
	//u.Debugf("msg: %#v", msgs[0])
	row := msgs[0].(*datasource.SqlDriverMessageMap).Values()
	//u.Debugf("row: %#v", row)
	assert.Tf(t, len(row) == 5, "expects 5 cols but got %v", len(row))
	assert.T(t, row[0] == "9Ip1aKbeZe2njCDM")
	// I really don't like this float behavior?
	assert.Tf(t, int(row[2].(float64)) == 164, "expected %v == 164  T:%T", row[2], row[2])
	assert.Tf(t, row[3] == int64(5), "wanted 5 got %v  T:%T", row[3], row[3])
	assert.T(t, row[4] == true)
}
Beispiel #4
0
func ExecSpec(t *testing.T, q *QuerySpec) {
	ctx := td.TestContext(q.sql)
	job, err := exec.BuildSqlJob(ctx)
	if !q.haserr {
		assert.Tf(t, err == nil, "expected no error but got %v for %s", err, q.sql)
	} else {
		assert.Tf(t, err != nil, "expected error but got %v for %s", err, q.sql)
		return
	}

	msgs := make([]schema.Message, 0)
	resultWriter := exec.NewResultBuffer(ctx, &msgs)
	job.RootTask.Add(resultWriter)

	err = job.Setup()
	if !q.haserr {

	}
	assert.T(t, err == nil)
	err = job.Run()
	//time.Sleep(time.Millisecond * 1)
	assert.Tf(t, err == nil, "got err=%v for sql=%s", err, q.sql)
	assert.Tf(t, len(msgs) == q.rowct, "expected %d rows but got %v for %s", q.rowct, len(msgs), q.sql)
	for rowi, msg := range msgs {
		row := msg.(*datasource.SqlDriverMessageMap).Values()
		expect := q.expect[rowi]
		//u.Debugf("msg?  %#v", msg)
		assert.Tf(t, len(row) == len(expect), "expects %d cols but got %v for sql=%s", len(expect), len(row), q.sql)
		for i, v := range row {
			assert.Equalf(t, expect[i], v, "Comparing values, col:%d expected %v:%T got %v:%T for sql=%s",
				i, expect[i], expect[i], v, v, q.sql)
		}
	}
}
Beispiel #5
0
func TestExecSqlSet(t *testing.T) {
	sqlText := `
		set @myvarname = "var value"
	`
	ctx := td.TestContext(sqlText)
	job, err := exec.BuildSqlJob(ctx)
	assert.Tf(t, err == nil, "no error %v", err)

	msgs := make([]schema.Message, 0)
	resultWriter := exec.NewResultBuffer(ctx, &msgs)
	job.RootTask.Add(resultWriter)

	err = job.Setup()
	assert.T(t, err == nil)
	err = job.Run()
	time.Sleep(time.Millisecond * 10)
	assert.Tf(t, err == nil, "no error %v", err)
	assert.Tf(t, len(msgs) == 0, "should not have messages %v", len(msgs))
	//u.Debugf("msg: %#v", msgs[0])

	ctx2 := td.TestContext(`SELECT 3, @myvarname;`)
	ctx2.Session = ctx.Session
	job, err = exec.BuildSqlJob(ctx2)
	assert.Tf(t, err == nil, "no error %v", err)

	msgs = make([]schema.Message, 0)
	resultWriter = exec.NewResultBuffer(ctx2, &msgs)
	job.RootTask.Add(resultWriter)

	err = job.Setup()
	assert.T(t, err == nil)
	err = job.Run()
	time.Sleep(time.Millisecond * 10)
	assert.Tf(t, err == nil, "no error %v", err)
	assert.Tf(t, len(msgs) == 1, "should have 1 messages %v", len(msgs))
	msg := msgs[0].Body().(*datasource.SqlDriverMessageMap)

	assert.Tf(t, msg.Vals[0] == int64(3), "Has 3?")
	assert.Tf(t, msg.Vals[1] == "var value", "Has variable value? %v", msg.Vals[1])

	setcmds := []string{
		`set sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'`,
		`set NAMES utf8`,
		`set CHARACTER SET utf8`,
	}

	msgs = make([]schema.Message, 0)
	for _, setCmd := range setcmds {
		ctx2 := td.TestContext(setCmd)
		ctx2.Session = ctx.Session
		job, err = exec.BuildSqlJob(ctx2)
		assert.Tf(t, err == nil, "no error %v", err)

		resultWriter = exec.NewResultBuffer(ctx2, &msgs)
		job.RootTask.Add(resultWriter)

		err = job.Setup()
		assert.T(t, err == nil)
		err = job.Run()
		assert.Tf(t, err == nil, "no error %v", err)
	}
}
Beispiel #6
0
func TestExecInsert(t *testing.T) {

	//mockSchema, _ = registry.Schema("mockcsv")

	// By "Loading" table we force it to exist in this non DDL mock store
	mockcsv.LoadTable("user_event", "id,user_id,event,date\n1,abcabcabc,signup,\"2012-12-24T17:29:39.738Z\"")

	//u.Infof("%p schema", mockSchema)
	td.TestContext("select * from user_event")

	db, err := datasource.OpenConn("mockcsv", "user_event")
	assert.Tf(t, err == nil, "%v", err)
	dbTable, ok := db.(*mockcsv.MockCsvTable)
	assert.Tf(t, ok, "Should be type StaticDataSource but was T %T", db)
	assert.Tf(t, dbTable.Length() == 1, "Should have inserted 1 but was %v", dbTable.Length())

	sqlText := `
		INSERT into user_event (id, user_id, event, date)
		VALUES
			(uuid(), "9Ip1aKbeZe2njCDM", "logon", now())
	`
	ctx := td.TestContext(sqlText)
	job, err := exec.BuildSqlJob(ctx)
	assert.Tf(t, err == nil, "%v", err)

	msgs := make([]schema.Message, 0)
	resultWriter := exec.NewResultBuffer(ctx, &msgs)
	job.RootTask.Add(resultWriter)

	err = job.Setup()
	assert.T(t, err == nil)
	//u.Infof("running tasks?  %v", len(job.RootTask.Children()))
	err = job.Run()
	assert.T(t, err == nil)
	db2, err := datasource.OpenConn("mockcsv", "user_event")
	assert.Tf(t, err == nil, "%v", err)
	dbTable2, ok := db2.(*mockcsv.MockCsvTable)
	assert.Tf(t, ok, "Should be type StaticDataSource but was T %T", db2)
	//u.Infof("db:  %#v", dbTable2)
	assert.Tf(t, dbTable2.Length() == 2, "Should have inserted 2 but was %v", dbTable2.Length())

	// Now lets query it, we are going to use QLBridge Driver
	sqlText = `
		select id, user_id, event, date
	    FROM user_event
	    WHERE user_id = "9Ip1aKbeZe2njCDM"
	`
	sqlDb, err := sql.Open("qlbridge", "mockcsv")
	assert.Tf(t, err == nil, "no error: %v", err)
	assert.Tf(t, sqlDb != nil, "has conn: %v", sqlDb)
	defer func() { sqlDb.Close() }()

	rows, err := sqlDb.Query(sqlText)
	assert.Tf(t, err == nil, "error: %v", err)
	defer rows.Close()
	assert.Tf(t, rows != nil, "has results: %v", rows)
	cols, err := rows.Columns()
	assert.Tf(t, err == nil, "no error: %v", err)
	assert.Tf(t, len(cols) == 4, "4 cols: %v", cols)
	events := make([]*UserEvent, 0)
	for rows.Next() {
		var ue UserEvent
		err = rows.Scan(&ue.Id, &ue.UserId, &ue.Event, &ue.Date)
		assert.Tf(t, err == nil, "no error: %v", err)
		//u.Debugf("events=%+v", ue)
		events = append(events, &ue)
	}
	assert.Tf(t, rows.Err() == nil, "no error: %v", err)
	assert.Tf(t, len(events) == 1, "has 1 event row: %+v", events)

	ue1 := events[0]
	assert.T(t, ue1.Event == "logon")
	assert.T(t, ue1.UserId == "9Ip1aKbeZe2njCDM")

	sqlText = `
		INSERT into user_event (id, user_id, event, date)
		VALUES
			(uuid(), "9Ip1aKbeZe2njCDM", "logon", now())
			, (uuid(), "9Ip1aKbeZe2njCDM", "click", now())
			, (uuid(), "abcd", "logon", now())
			, (uuid(), "abcd", "click", now())
	`
	result, err := sqlDb.Exec(sqlText)
	assert.Tf(t, err == nil, "error: %v", err)
	assert.Tf(t, result != nil, "has results: %v", result)
	insertedCt, err := result.RowsAffected()
	assert.Tf(t, err == nil, "no error: %v", err)
	assert.Tf(t, insertedCt == 4, "should have inserted 4 but was %v", insertedCt)
	assert.Tf(t, dbTable.Length() == 6, "should have 6 rows now")
	// TODO:  this doesn't work
	// row := sqlDb.QueryRow("SELECT count(*) from user_event")
	// assert.Tf(t, err == nil, "count(*) shouldnt error: %v", err)
	// var rowCt int
	// row.Scan(&rowCt)
	// assert.Tf(t, rowCt == 6, "has rowct=6: %v", rowCt)
}