remove support for SQL dialects that rely on LastInsertId()

While researching for Upsert(), I noticed that both SQLite and MariaDB
support INSERT with RETURNING clause, which is objectively better than
LastInsertId() and cuts out a lot of useless crap from the codebase
(esp. from monomorphization-relevant methods).

The only tangible downside is that this drops support specifically for
MySQL, but you know what? Fuck Oracle.

1 files changed, 50 insertions, 26 deletions

diff --git a/plan_test.go b/plan_test.go
index 08c7252..b3eeac5 100644
--- a/plan_test.go
+++ b/plan_test.go
@@ -69,15 +69,15 @@ func TestQueryConstructionBasic(t *testing.T) {
 		PrimaryKeyColumnNames: []string{"ID"},
 	}
 
-	t.Run("MysqlDialect", func(t *testing.T) {
-		plan, err := buildPlan(reflect.TypeFor[record](), MysqlDialect(), opts)
+	t.Run("MariaDBDialect", func(t *testing.T) {
+		plan, err := buildPlan(reflect.TypeFor[record](), MariaDBDialect(), opts)
 		if err != nil {
 			t.Error(err)
 		}
 		assert.Equal(t, plan.Select.Query, "SELECT `ID`, `Description`, `CreatedAt` FROM `basic_records` WHERE ")
 		assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil)
 		assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}})
-		assert.Equal(t, plan.Insert.Query, "INSERT INTO `basic_records` (`Description`, `CreatedAt`) VALUES (?, ?)")
+		assert.Equal(t, plan.Insert.Query, "INSERT INTO `basic_records` (`Description`, `CreatedAt`) VALUES (?, ?) RETURNING `ID`")
 		assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{1}, {2}})
 		assert.DeepEqual(t, plan.Insert.ScanIndexes, [][]int{{0}})
 		assert.Equal(t, plan.Upsert.Query, "")
@@ -121,7 +121,7 @@ func TestQueryConstructionBasic(t *testing.T) {
 		assert.Equal(t, plan.Select.Query, `SELECT "ID", "Description", "CreatedAt" FROM "basic_records" WHERE `)
 		assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil)
 		assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}})
-		assert.Equal(t, plan.Insert.Query, `INSERT INTO "basic_records" ("Description", "CreatedAt") VALUES (?, ?)`)
+		assert.Equal(t, plan.Insert.Query, `INSERT INTO "basic_records" ("Description", "CreatedAt") VALUES (?, ?) RETURNING "ID"`)
 		assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{1}, {2}})
 		assert.DeepEqual(t, plan.Insert.ScanIndexes, [][]int{{0}})
 		assert.Equal(t, plan.Upsert.Query, "")
@@ -146,8 +146,8 @@ func TestQueryConstructionWithOnlyPrimaryKey(t *testing.T) {
 		PrimaryKeyColumnNames: []string{"foo_id", "bar_id"},
 	}
 
-	t.Run("MysqlDialect", func(t *testing.T) {
-		plan, err := buildPlan(reflect.TypeFor[relation](), MysqlDialect(), opts)
+	t.Run("MariaDBDialect", func(t *testing.T) {
+		plan, err := buildPlan(reflect.TypeFor[relation](), MariaDBDialect(), opts)
 		if err != nil {
 			t.Error(err)
 		}
@@ -222,8 +222,8 @@ func TestQueryConstructionWithoutPrimaryKey(t *testing.T) {
 		TableName: "foo_bar_relations",
 	}
 
-	t.Run("MysqlDialect", func(t *testing.T) {
-		plan, err := buildPlan(reflect.TypeFor[relation](), MysqlDialect(), opts)
+	t.Run("MariaDBDialect", func(t *testing.T) {
+		plan, err := buildPlan(reflect.TypeFor[relation](), MariaDBDialect(), opts)
 		if err != nil {
 			t.Error(err)
 		}
@@ -321,7 +321,7 @@ func TestQueryConstructionImpossble(t *testing.T) {
 		}
 	}
 
-	t.Run("MysqlDialect", testWith(MysqlDialect()))
+	t.Run("MariaDBDialect", testWith(MariaDBDialect()))
 	t.Run("PostgresDialect", testWith(PostgresDialect()))
 	t.Run("SqliteDialect", testWith(SqliteDialect()))
 }
@@ -337,8 +337,8 @@ func TestQueryConstructionWithMultiplePrimaryKeyColumns(t *testing.T) {
 		PrimaryKeyColumnNames: []string{"group_id", "name"},
 	}
 
-	t.Run("MysqlDialect", func(t *testing.T) {
-		plan, err := buildPlan(reflect.TypeFor[record](), MysqlDialect(), opts)
+	t.Run("MariaDBDialect", func(t *testing.T) {
+		plan, err := buildPlan(reflect.TypeFor[record](), MariaDBDialect(), opts)
 		if err != nil {
 			t.Error(err)
 		}
@@ -415,9 +415,26 @@ func TestQueryConstructionWithMultipleAutoColumns(t *testing.T) {
 		PrimaryKeyColumnNames: []string{"id"},
 	}
 
-	t.Run("MysqlDialect", func(t *testing.T) {
-		_, err := NewStore[record](MysqlDialect())
-		assert.Equal(t, err.Error(), `cannot use type oblast.record for queries: multiple columns are marked as auto-filled (id, created_at), but this SQL dialect only supports at most one per table`)
+	t.Run("MariaDBDialect", func(t *testing.T) {
+		plan, err := buildPlan(reflect.TypeFor[record](), MariaDBDialect(), opts)
+		if err != nil {
+			t.Error(err)
+		}
+		assert.Equal(t, plan.Select.Query, "SELECT `id`, `name`, `created_at` FROM `autogenerated_records` WHERE ")
+		assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}})
+		assert.Equal(t, plan.Insert.Query, "INSERT INTO `autogenerated_records` (`name`) VALUES (?) RETURNING `id`, `created_at`")
+		assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{1}})
+		assert.DeepEqual(t, plan.Insert.ScanIndexes, [][]int{{0}, {2}})
+		assert.Equal(t, plan.Upsert.Query, "")
+		assert.DeepEqual(t, plan.Upsert.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Upsert.ScanIndexes, nil)
+		assert.Equal(t, plan.Update.Query, "UPDATE `autogenerated_records` SET `name` = ?, `created_at` = ? WHERE `id` = ?")
+		assert.DeepEqual(t, plan.Update.ArgumentIndexes, [][]int{{1}, {2}, {0}})
+		assert.DeepEqual(t, plan.Update.ScanIndexes, nil)
+		assert.Equal(t, plan.Delete.Query, "DELETE FROM `autogenerated_records` WHERE `id` = ?")
+		assert.DeepEqual(t, plan.Delete.ArgumentIndexes, [][]int{{0}})
+		assert.DeepEqual(t, plan.Delete.ScanIndexes, nil)
 	})
 
 	t.Run("PostgresDialect", func(t *testing.T) {
@@ -443,8 +460,25 @@ func TestQueryConstructionWithMultipleAutoColumns(t *testing.T) {
 	})
 
 	t.Run("SqliteDialect", func(t *testing.T) {
-		_, err := NewStore[record](SqliteDialect())
-		assert.Equal(t, err.Error(), `cannot use type oblast.record for queries: multiple columns are marked as auto-filled (id, created_at), but this SQL dialect only supports at most one per table`)
+		plan, err := buildPlan(reflect.TypeFor[record](), SqliteDialect(), opts)
+		if err != nil {
+			t.Error(err)
+		}
+		assert.Equal(t, plan.Select.Query, `SELECT "id", "name", "created_at" FROM "autogenerated_records" WHERE `)
+		assert.DeepEqual(t, plan.Select.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Select.ScanIndexes, [][]int{{0}, {1}, {2}})
+		assert.Equal(t, plan.Insert.Query, `INSERT INTO "autogenerated_records" ("name") VALUES (?) RETURNING "id", "created_at"`)
+		assert.DeepEqual(t, plan.Insert.ArgumentIndexes, [][]int{{1}})
+		assert.DeepEqual(t, plan.Insert.ScanIndexes, [][]int{{0}, {2}})
+		assert.Equal(t, plan.Upsert.Query, "")
+		assert.DeepEqual(t, plan.Upsert.ArgumentIndexes, nil)
+		assert.DeepEqual(t, plan.Upsert.ScanIndexes, nil)
+		assert.Equal(t, plan.Update.Query, `UPDATE "autogenerated_records" SET "name" = ?, "created_at" = ? WHERE "id" = ?`)
+		assert.DeepEqual(t, plan.Update.ArgumentIndexes, [][]int{{1}, {2}, {0}})
+		assert.DeepEqual(t, plan.Update.ScanIndexes, nil)
+		assert.Equal(t, plan.Delete.Query, `DELETE FROM "autogenerated_records" WHERE "id" = ?`)
+		assert.DeepEqual(t, plan.Delete.ArgumentIndexes, [][]int{{0}})
+		assert.DeepEqual(t, plan.Delete.ScanIndexes, nil)
 	})
 }
 
@@ -495,16 +529,6 @@ func TestPlanErrorCases(t *testing.T) {
 	assert.Equal(t, err.Error(), `cannot use type oblast.recordWithUnknownPK for queries: `+
 		"no field has tag `db:\"record_id\"`, but a field of this name was declared in the primary key")
 
-	type recordWithNonintegerAutoKey struct {
-		CreatedAt time.Time `db:"created_at,auto"`
-		Name      string    `db:"name"`
-	}
-	_, err = NewStore[recordWithNonintegerAutoKey](SqliteDialect(),
-		TableNameIs("records"),
-	)
-	assert.Equal(t, err.Error(), `cannot use type oblast.recordWithNonintegerAutoKey for queries: `+
-		`column "created_at" is marked as auto-filled, but this SQL dialect only supports auto-filling struct fields with integer types`)
-
 	type recordWithWeirdTagOption struct {
 		ID          int64  `db:",auto"`
 		Name        string `db:",unique"`