"time"
"github.com/stretchr/testify/assert"
"github.com/stretchr/testify/require"
sql "github.com/aodin/sol"
"github.com/aodin/sol/dialect"
"github.com/aodin/sol/types"
)
// The sql dialect must implement the dialect interface
var _ dialect.Dialect = &Sqlite3{}
var things = sql.Table("things",
sql.Column("name", types.Varchar()),
sql.Column("created_at", types.Timestamp()), // TODO auto-timestamp?
)
type thing struct {
Name string
CreatedAt *time.Time `db:",omitempty"`
}
// Connect to an in-memory sqlite3 instance and execute some statements.
func TestSqlite3(t *testing.T) {
conn, err := sql.Open("sqlite3", ":memory:")
require.Nil(t, err, `Failed to connect to in-memory sqlite3 instance`)
defer conn.Close()
require.Nil(t,
conn.Query(things.Create()),
package sol
import (
"time"
"github.com/aodin/sol/types"
)
// Valid schemas should not panic
var users = Table("users",
Column("id", types.Integer()),
Column("email", types.Varchar().Limit(256).NotNull()), // TODO unique
Column("name", types.Varchar().Limit(32).NotNull()),
Column("password", types.Varchar()),
Column("created_at", types.Timestamp()),
PrimaryKey("id"),
Unique("email"),
)
var contacts = Table("contacts",
Column("id", types.Integer()),
Column("user_id", types.Integer()), // TODO FK
Column("key", types.Varchar()),
Column("value", types.Varchar()),
PrimaryKey("id"),
Unique("user_id", "key"),
)
type user struct {
ID uint64 `db:",omitempty"`
Email string
// IntegrationTest runs a large, neutral dialect test
func IntegrationTest(t *testing.T, conn *DB, ddlCommit bool) {
// Perform all tests in a transaction
// TODO What features should be tested outside of a transaction?
// CREATE TABLE is performed outside of the transaction because any
// change to the DDL in MySQL is a implicit commit
// Other databases: http://stackoverflow.com/a/4736346
testusers := Table("testusers",
Column("id", types.Integer()),
Column("email", types.Varchar().Limit(255).NotNull()),
Column("is_admin", types.Boolean().NotNull()),
Column("created_at", types.Timestamp()),
PrimaryKey("id"),
Unique("email"),
)
type testuser struct {
ID int64
Email string
IsAdmin bool
CreatedAt time.Time
}
tx, err := conn.Begin()
if err != nil {
t.Fatalf("Creating a new transaction should not error: %s", err)
}
defer tx.Rollback()
if ddlCommit {
if err = conn.Query(testusers.Create().IfNotExists()); err != nil {
t.Fatalf("CREATE TABLE should not error: %s", err)
}
} else {
if err = tx.Query(testusers.Create().IfNotExists()); err != nil {
t.Fatalf("CREATE TABLE should not error: %s", err)
}
}
// INSERT by struct
// Truncate the time.Time field to avoid significant digit errors
admin := testuser{
ID: 1,
Email: "*****@*****.**",
IsAdmin: true,
CreatedAt: time.Now().UTC().Truncate(time.Second),
}
if err = tx.Query(testusers.Insert().Values(admin)); err != nil {
t.Fatalf("INSERT by struct should not fail %s", err)
}
// SELECT
var selected testuser
if err = tx.Query(
testusers.Select().Where(testusers.C("id").Equals(admin.ID)),
&selected,
); err != nil {
t.Fatalf("SELECT should not fail: %s", err)
}
// TODO test with direct comparison: selected == admin
// For now, test each field since DATETIME handling is terribly
// inconsistent across databases
if selected.ID != admin.ID {
t.Errorf(
"Unequal testusers id: have %d, want %d",
selected.ID, admin.ID,
)
}
if selected.Email != admin.Email {
t.Errorf(
"Unequal testusers email: have %s, want %s",
selected.Email, admin.Email,
)
}
if selected.IsAdmin != admin.IsAdmin {
t.Errorf(
"Unequal testusers is_admin: have %t, want %t",
selected.IsAdmin, admin.IsAdmin,
)
}
if !selected.CreatedAt.Equal(admin.CreatedAt) {
t.Errorf(
"Unequal testusers created_at: have %v, want %v",
selected.CreatedAt, admin.CreatedAt,
)
}
// UPDATE
if err = tx.Query(
testusers.Update().Values(
Values{"is_admin": false},
).Where(testusers.C("id").Equals(admin.ID)),
); err != nil {
t.Fatalf("UPDATE should not fail: %s", err)
}
var updated testuser
if err = tx.Query(testusers.Select().Limit(1), &updated); err != nil {
t.Fatalf("SELECT should not fail: %s", err)
}
selected.IsAdmin = false
if updated != selected {
t.Errorf(
"Unequal testusers: have %+v, want %+v",
updated, selected,
)
}
// INSERT by values
client := Values{
"id": 2,
"email": "*****@*****.**",
"is_admin": false,
"created_at": time.Now().UTC().Truncate(time.Second),
}
if err = tx.Query(testusers.Insert().Values(client)); err != nil {
t.Fatalf("INSERT by values should not fail %s", err)
}
var list []testuser
if err = tx.Query(
testusers.Select().OrderBy(testusers.C("id").Desc()),
&list,
); err != nil {
t.Fatalf("SELECT with ORDER BY should not fail: %s", err)
}
if len(list) != 2 {
t.Fatalf("Unexpected length of list: want 2, have %d", len(list))
}
// The client should be first
if list[0].Email != "*****@*****.**" {
t.Errorf(
"Unexpected email: want [email protected], have %s",
list[0].Email,
)
}
var count int64
if err = tx.Query(Select(Count(testusers.C("id"))), &count); err != nil {
t.Fatalf("SELECT with COUNT should not fail: %s", err)
}
if count != 2 {
t.Errorf("Unexpected COUNT: want 2, have %d", count)
}
// DELETE
if err = tx.Query(
testusers.Delete().Where(testusers.C("email").Equals(admin.Email)),
); err != nil {
t.Fatalf("DELETE should not fail: %s", err)
}
// DROP TABLE
// TODO Since this is a DDL, this will likely commit in MySQL
if err = tx.Query(testusers.Drop()); err != nil {
t.Fatalf("DROP TABLE should not fail %s", err)
}
// Test a recover
func() {
defer func() {
if panicked := recover(); panicked == nil {
t.Errorf("Connection failed to panic on error")
}
}()
conn.Must().Query(testusers.Select(), list)
}()
}