Unit test JOIN at the database level
@@ -177,6 +177,44 @@ func Test_SimpleSingleStatements(t *testing.T) {
}
}
+func Test_SimpleJoinStatements(t *testing.T) {
+ db, path := mustCreateDatabase()
+ defer db.Close()
+ defer os.Remove(path)
+
+ _, err := db.Execute([]string{"CREATE TABLE names (id INTEGER NOT NULL PRIMARY KEY, name TEXT, ssn TEXT)"}, false, false)
+ if err != nil {
+ t.Fatalf("failed to create table: %s", err.Error())
+ }
+
+ _, err = db.Execute([]string{
+ `INSERT INTO "names" VALUES(1,'bob','123-45-678')`,
+ `INSERT INTO "names" VALUES(2,'tom','111-22-333')`,
+ `INSERT INTO "names" VALUES(3,'matt','222-22-333')`,
+ }, false, false)
+ if err != nil {
+ t.Fatalf("failed to insert record: %s", err.Error())
+ }
+
+ _, err = db.Execute([]string{"CREATE TABLE staff (id INTEGER NOT NULL PRIMARY KEY, employer TEXT, ssn TEXT)"}, false, false)
+ if err != nil {
+ t.Fatalf("failed to create table: %s", err.Error())
+ }
+
+ _, err = db.Execute([]string{`INSERT INTO "staff" VALUES(1,'acme','222-22-333')`}, false, false)
+ if err != nil {
+ t.Fatalf("failed to insert record: %s", err.Error())
+ }
+
+ r, err := db.Query([]string{`SELECT names.id,name,names.ssn,employer FROM names INNER JOIN staff ON staff.ssn = names.ssn`}, false, false)
+ if err != nil {
+ t.Fatalf("failed to query table using JOIN: %s", err.Error())
+ }
+ if exp, got := `[{"columns":["id","name","ssn","employer"],"types":["integer","text","text","text"],"values":[[3,"matt","222-22-333","acme"]]}]`, asJSON(r); exp != got {
+ t.Fatalf("unexpected results for query\nexp: %s\ngot: %s", exp, got)
+ }
+}
+
func Test_SimpleSingleConcatStatements(t *testing.T) {
db, path := mustCreateDatabase()
defer db.Close()
