// GetRaw runs the provided postgres query and args against this sqlquery's db.
func (q SqlQuery) GetRaw(ctx context.Context, query string, args []interface{}, dest interface{}) error {
db := sqlx.NewDb(q.DB, "postgres")
log.WithField(ctx, "sql", query).Info("query sql")
log.WithField(ctx, "args", args).Debug("query args")
return db.Get(dest, query, args...)
}
func (d *DB) Open() error {
conf := pgx.ConnPoolConfig{
ConnConfig: pgx.ConnConfig{
Host: d.config.Host,
Port: uint16(d.config.Port),
User: d.config.Username,
Password: d.config.Password,
Database: d.config.Database,
Logger: d.config.Logger,
},
MaxConnections: d.config.MaxConnections,
}
p, err := pgx.NewConnPool(conf)
if err != nil {
return err
}
db, err := stdlib.OpenFromConnPool(p)
if err != nil {
return err
}
dbx := sqlx.NewDb(db, "pgx")
d.queryer = queryer{impl: dbx}
return nil
}
func init() {
var err error
db, err = sql.Open("sqlite3", ":memory:")
if err != nil {
panic(err)
}
db.Exec("DROP TABLE users;")
dbx = sqlx.NewDb(db, "sqlite3")
ddl := []string{CreateUserStmt}
for _, stmt := range ddl {
_, err = db.Exec(stmt)
if err != nil {
panic(err)
}
}
for i := 0; i < 100; i++ {
user := &User{}
user.Name = randomdata.FullName(randomdata.RandomGender)
user.Email = randomdata.Email()
user.Pass = "******"
user.Created = time.Now().Unix()
user.Updated = time.Now().Unix()
err := InsertUser(db, InsertUserStmt, user)
if err != nil {
panic(err)
}
}
}
func server(config config, db *sql.DB) *echo.Echo {
dbx := sqlx.NewDb(db, "postgres")
exec := echo.New()
if !config.Production {
exec.Debug()
}
exec.Use(mw.Logger())
exec.Use(mw.Recover())
exec.Use(mw.Gzip())
exec.Get("/status", func(c *echo.Context) error {
return c.NoContent(http.StatusOK)
})
leadHandler := lead.NewHandler(datastores.NewLead(dbx))
exec.Post("/lead", leadHandler.Create)
exec.Get("/lead/:hashCode", leadHandler.CountByInvites)
assetHandler := http.FileServer(rice.MustFindBox("static").HTTPBox())
exec.Get("/", func(c *echo.Context) error {
assetHandler.ServeHTTP(c.Response().Writer(), c.Request())
return nil
})
exec.Get("/static/*", func(c *echo.Context) error {
http.StripPrefix("/static/", assetHandler).
ServeHTTP(c.Response().Writer(), c.Request())
return nil
})
return exec
}
//Connect connec to the db
func (db *DB) Connect(sqlType, conn string, maxOpenConn int) (err error) {
db.sqlType = sqlType
db.connectionString = conn
rawDB, err := sql.Open(db.sqlType, db.connectionString)
if err != nil {
return err
}
rawDB.SetMaxOpenConns(maxOpenConn)
rawDB.SetMaxIdleConns(maxOpenConn)
db.DB = sqlx.NewDb(rawDB, db.sqlType)
if db.sqlType == "sqlite3" {
db.DB.Exec("PRAGMA foreign_keys = ON;")
}
for i := 0; i < retryDB; i++ {
err = db.DB.Ping()
if err == nil {
return nil
}
time.Sleep(retryDBWait * time.Second)
log.Info("Retrying db connection... (%s)", err)
}
return fmt.Errorf("Failed to connect db")
}
func TestMain(m *testing.M) {
var err error
test.db, err = sql.Open("sqlite3", ":memory:")
panicIfErr(err)
_, err = test.db.Exec("CREATE TABLE users(a, b UNIQUE)")
panicIfErr(err)
_, err = test.db.Exec("INSERT INTO users VALUES(1, 2)")
panicIfErr(err)
test.dbx = sqlx.NewDb(test.db, "sqlite3")
test.dbgorm, err = gorm.Open("sqlite3", ":memory:")
panicIfErr(err)
user := &User{"1", "2"}
test.dbgorm.CreateTable(user)
test.dbgorm.Create(user)
err = orm.RunSyncdb("default", true, false)
panicIfErr(err)
test.dbbeegoorm = orm.NewOrm()
beeuser := &BeeUser{A: "1", B: "2"}
test.dbbeegoorm.Insert(beeuser)
os.Exit(m.Run())
}
// NewTestDB connects to the default DBMS, creates a new database using testdb,
// and loads the application's schema.
func NewTestDB() (*testdb.TestDB, *sqlx.DB, error) {
stmts, err := readSQL("../schema.sql")
if err != nil {
return nil, nil, err
}
tdb, err := testdb.Open(DriverName, DefaultDataSource+" dbname=postgres")
if err != nil {
return nil, nil, err
}
db, err := sql.Open(DriverName, DefaultDataSource+" dbname="+tdb.Name())
if err != nil {
tdb.Close()
return nil, nil, err
}
for _, stmt := range stmts {
if _, err := db.Exec(stmt); err != nil {
db.Close()
tdb.Close()
return nil, nil, fmt.Errorf("%#v\nExecuting: %s", err, stmt)
}
}
dbx := sqlx.NewDb(db, DriverName)
dbx.MapperFunc(MapperFunc())
return tdb, dbx, nil
}
func TestCatalogueServiceTags(t *testing.T) {
logger = log.NewLogfmtLogger(os.Stderr)
db, mock, err := sqlmock.New()
if err != nil {
t.Fatalf("an error '%s' was not expected when opening stub database connection", err)
}
defer db.Close()
sqlxDB := sqlx.NewDb(db, "sqlmock")
var cols []string = []string{"name"}
mock.ExpectQuery("SELECT name FROM tag").WillReturnRows(sqlmock.NewRows(cols).
AddRow(tags[0]).
AddRow(tags[1]).
AddRow(tags[2]))
s := NewCatalogueService(sqlxDB, logger)
have, err := s.Tags()
if err != nil {
t.Errorf("Tags(): %v", err)
}
if !reflect.DeepEqual(tags, have) {
t.Errorf("Tags(): want %v, have %v", tags, have)
}
}
func Server(db *sql.DB) *echo.Echo {
dbx := sqlx.NewDb(db, "postgres")
e := echo.New()
e.Post("/books/:name", createBook(dbx))
e.Get("/books", listBooks(dbx))
return e
}
// NewDbMap returns a new DbMap using the db connection and dialect.
func NewDbMap(db *sql.DB, dialect Dialect) *DbMap {
return &DbMap{
Db: db,
Dialect: dialect,
Dbx: sqlx.NewDb(db, dialect.DriverName()),
mapper: reflectx.NewMapperFunc("db", sqlx.NameMapper),
}
}
// Setup the database to be shared across the app.
func (c *DatabaseConfig) InitDB() error {
vdb, err := c.NewVersionedDB()
if err != nil {
return err
}
DB = sqlx.NewDb(vdb.DB, database.DEFAULT_DRIVER)
return nil
}
func NewDB(dsn string) *DB {
if dsn == "" {
dsn = "testing:testing@tcp(localhost:3306)/testing?charset=utf8&parseTime=True"
}
sqlxDb := sqlx.NewDb(connect("mysql", dsn), "mysql")
return &DB{*sqlxDb, newStmtCache()}
}
// NewDatastore returns a new Datastore
func NewDatastore(db *sql.DB) datastore.Datastore {
dbx := sqlx.NewDb(db, "postgres")
return struct {
*Cepstore
}{
NewCepstore(dbx),
}
}
func (q SqlQuery) Get(ctx context.Context, sql sq.SelectBuilder, dest interface{}) error {
db := sqlx.NewDb(q.DB, "postgres")
sql = sql.PlaceholderFormat(sq.Dollar)
query, args, err := sql.ToSql()
if err != nil {
return err
}
return db.Get(dest, query, args...)
}
// SelectRaw runs the provided postgres query and args against this sqlquery's db.
func (q SqlQuery) SelectRaw(ctx context.Context, query string, args []interface{}, dest interface{}) error {
db := sqlx.NewDb(q.DB, "postgres")
log.WithField(ctx, "sql", query).Info("query sql")
log.WithField(ctx, "args", args).Debug("query args")
err := db.Select(dest, query, args...)
if err != nil {
err = errors.Wrap(err, 1)
}
return err
}
func init() {
var err error
dbNative, err = sql.Open("postgres", os.Getenv("DATABASE_URL"))
if err != nil {
panic(err)
}
dbSqlx = sqlx.NewDb(dbNative, "postgres")
fmt.Printf("Connected to database at %s", os.Getenv("DATABASE_URL"))
}
// NewDB instantiates a Connection for a given database/sql connection
func NewDB(db *sql.DB, driverName string) *DB {
database := sqlx.NewDb(db, driverName)
conn := &DB{database, &Queryable{database}}
if driverName == "postgres" {
pgMustNotAllowEscapeSequence(conn)
if dat.Strict {
conn.SQL("SET client_min_messages to 'DEBUG';")
}
} else {
panic("Unsupported driver: " + driverName)
}
return conn
}
func (q SqlQuery) Select(ctx context.Context, sql sq.SelectBuilder, dest interface{}) error {
db := sqlx.NewDb(q.DB, "postgres")
sql = sql.PlaceholderFormat(sq.Dollar)
query, args, err := sql.ToSql()
if err != nil {
return err
}
log.WithField(ctx, "sql", query).Info("Executing query")
return db.Select(dest, query, args...)
}
func TestCatalogueServiceGet(t *testing.T) {
logger = log.NewLogfmtLogger(os.Stderr)
db, mock, err := sqlmock.New()
if err != nil {
t.Fatalf("an error '%s' was not expected when opening stub database connection", err)
}
defer db.Close()
sqlxDB := sqlx.NewDb(db, "sqlmock")
var cols []string = []string{"id", "name", "description", "price", "count", "image_url_1", "image_url_2", "tag_name"}
// (Error) Test Cases 1
mock.ExpectQuery("SELECT *").WillReturnRows(sqlmock.NewRows(cols))
// Test Case 2
mock.ExpectQuery("SELECT *").WillReturnRows(sqlmock.NewRows(cols).
AddRow(s3.ID, s3.Name, s3.Description, s3.Price, s3.Count, s3.ImageURL[0], s3.ImageURL[1], strings.Join(s3.Tags, ",")))
s := NewCatalogueService(sqlxDB, logger)
{
// Error case
for _, id := range []string{
"0",
} {
want := ErrNotFound
if _, have := s.Get(id); want != have {
t.Errorf("Get(%s): want %v, have %v", id, want, have)
}
}
}
{
// Success case
for id, want := range map[string]Sock{
"3": s3,
} {
have, err := s.Get(id)
if err != nil {
t.Errorf("Get(%s): %v", id, err)
continue
}
if !reflect.DeepEqual(want, have) {
t.Errorf("Get(%s): want %s, have %s", id, want.ID, have.ID)
continue
}
}
}
}
// NewSQLiteDB returns a NewtonDB instance that is backed by an SQLiteDB stored
// in a file.
func NewSQLiteDB(dbPath string) (NewtonDB, error) {
if dbPath == "" {
return nil, errors.New("dbPath is empty")
}
sdb := &SQLiteNewtonDB{}
db, err := sql.Open("sqlite3", dbPath)
if err != nil {
return nil, err
}
sdb.db = sqlx.NewDb(db, "sqlite3")
err = updateSQLiteDBVersion(sdb)
if err != nil {
return nil, err
}
return sdb, nil
}
func TestGetAll(t *testing.T) {
db, mock, err := sqlmock.New()
defer db.Close()
sqlxDB := sqlx.NewDb(db, "sqlmock")
rows := sqlmock.NewRows([]string{"key", "value"}).
AddRow("hello", "world").
AddRow("foo", "bar")
mock.ExpectQuery("SELECT KEY, VALUE FROM KEYVAL").WillReturnRows(rows)
keyValDAL := &KeyValDAL{sqlxDB}
keyValData, err := keyValDAL.GetAll()
if err != nil {
t.Error(err)
}
fmt.Print(keyValData[0].Value)
}
func Connect(driver, conn string) (*sqlx.DB, error) {
migrator := migrate.Migrator{driver}
migration.DefaultGetVersion = migrator.GetVersion
migration.DefaultSetVersion = migrator.SetVersion
migrations := []migration.Migrator{
migrator.Setup,
migrator.CreateDefaultPerson,
}
db, err := migration.Open(driver, conn, migrations)
if err != nil {
return nil, err
}
if err := db.Ping(); err != nil {
return nil, err
}
return sqlx.NewDb(db, driver), nil
}
func RunYarpNarp() {
var zusagen Zusagen
dbx := sqlx.NewDb(db, *driver)
if err := sqlx.Select(dbx, &zusagen, "SELECT nick, kommt, kommentar, registered FROM zusagen"); err != nil {
fmt.Fprintln(os.Stderr, "Datenbankfehler:", err)
return
}
sort.Sort(zusagen)
width, trunc := TermWidth()
width -= zusagen.minWidth()
w := tabwriter.NewWriter(os.Stdout, 0, 4, 1, ' ', tabwriter.DiscardEmptyColumns)
fmt.Fprintf(w, "Nick\tKommt\tLetzte Änderung\t%s\n", maybeTruncate("Kommentar", width, trunc))
for _, z := range zusagen {
fmt.Fprintf(w, "%s\t%v\t%s\t%s\n", z.Nick, formatBool(z.Kommt), z.Registered.In(time.Local).Format("2006-01-02 15:04:05"), maybeTruncate(z.Kommentar, width, trunc))
}
w.Flush()
}
func TestCatalogueServiceCount(t *testing.T) {
logger = log.NewLogfmtLogger(os.Stderr)
db, mock, err := sqlmock.New()
if err != nil {
t.Fatalf("an error '%s' was not expected when opening stub database connection", err)
}
defer db.Close()
sqlxDB := sqlx.NewDb(db, "sqlmock")
var cols []string = []string{"count"}
mock.ExpectPrepare("SELECT *").ExpectQuery().WillReturnRows(sqlmock.NewRows(cols).AddRow(5))
mock.ExpectPrepare("SELECT *").ExpectQuery().WillReturnRows(sqlmock.NewRows(cols).AddRow(4))
mock.ExpectPrepare("SELECT *").ExpectQuery().WillReturnRows(sqlmock.NewRows(cols).AddRow(1))
s := NewCatalogueService(sqlxDB, logger)
for _, testcase := range []struct {
tags []string
want int
}{
{[]string{}, 5},
{[]string{"prime"}, 4},
{[]string{"even", "prime"}, 1},
} {
have, err := s.Count(testcase.tags)
if err != nil {
t.Errorf(
"Count(%v): returned error %s",
testcase.tags, err.Error(),
err.Error(),
)
}
if want := testcase.want; want != have {
t.Errorf("Count(%v): want %d, have %d", testcase.tags, want, have)
}
}
}
// WithDB return context with given database instance bind to it. Use DB(ctx)
// to get database back.
func WithDB(ctx context.Context, dbc *sql.DB) context.Context {
dbx := sqlx.NewDb(dbc, "postgres")
x := &sqlxdb{dbx: dbx}
return context.WithValue(ctx, "storage.sq:db", x)
}
// NewDbMap returns a new DbMap using the db connection and dialect
func NewDbMap(db *sql.DB, dialect Dialect) *DbMap {
return &DbMap{Db: db, Dialect: dialect, Dbx: sqlx.NewDb(db, dialect.DriverName())}
}
func TestAsn(t *testing.T) {
t.Log("Testing Asn...")
// setup mock database
db, mock, err := sqlmock.New()
xdb := sqlx.NewDb(db, "postgres")
assert.Nil(t, err, "error opening stub database")
defer db.Close()
// define the columns
columns := []string{"id", "asn", "cachegroup"}
// getAsns unit test
rows := sqlmock.NewRows(columns).
AddRow(1, 33099, 1).
AddRow(2, 33093, 2)
queryStr := "^select (.+) from asn$"
mock.ExpectQuery(queryStr).WillReturnRows(rows)
returned, err := getAsns(xdb)
require.Equal(t, 2, len(returned.([]Asn)), "returned array should len 2")
assert.Equal(t, int64(1), returned.([]Asn)[0].Id, "id should match")
assert.Equal(t, int64(33099), returned.([]Asn)[0].Asn, "asn should match")
assert.Equal(t, int64(2), returned.([]Asn)[1].Id, "id should match")
assert.Equal(t, int64(33093), returned.([]Asn)[1].Asn, "asn should match")
// error case
mock.ExpectQuery(queryStr).WillReturnError(fmt.Errorf("Testing Database Error"))
_, err = getAsns(xdb)
assert.NotNil(t, err, "Database error should be passed in error")
// getAsnById unit test
rowTwo := sqlmock.NewRows(columns).
AddRow(2, 33093, 2)
queryStr = "^select (.+) from asn where id=\\$1$"
mock.ExpectPrepare(queryStr)
mock.ExpectQuery(queryStr).WithArgs(2).WillReturnRows(rowTwo)
returned, err = getAsnById(2, xdb)
require.Equal(t, 1, len(returned.([]Asn)), "returned array should len 1")
assert.Equal(t, int64(2), returned.([]Asn)[0].Id, "id should match")
assert.Equal(t, int64(33093), returned.([]Asn)[0].Asn, "asn should match")
// error cases
mock.ExpectPrepare(queryStr)
mock.ExpectQuery(queryStr).WillReturnError(fmt.Errorf("Testing Database Error"))
_, err = getAsnById(2, xdb)
assert.NotNil(t, err, "Database error should be passed in error")
// postAsn unit test - using struct and encdoding/json
mock.ExpectExec("^INSERT INTO asn.*").WithArgs(333333, 5).WillReturnResult(sqlmock.NewResult(0, 1))
cg := CachegroupLink{ID: 5}
links := AsnLinks{CachegroupLink: cg}
newAsn := Asn{Asn: 333333, Links: links}
jsonTxt, err := json.MarshalIndent(newAsn, "", " ")
assert.Nil(t, err, "Error Marshalling")
if testing.Verbose() {
t.Log("Insert:\n", string(jsonTxt))
}
_, err = postAsn([]byte(jsonTxt), xdb)
// error cases
mock.ExpectExec("^INSERT INTO asn.*").WillReturnError(fmt.Errorf("Testing Database Error"))
_, err = postAsn([]byte(jsonTxt), xdb)
assert.NotNil(t, err, "Database error should be passed in error")
_, err = postAsn([]byte("fooo"), xdb)
assert.NotNil(t, err, "JSON error should be passed in error")
// putAsn unit test - using string json
mock.ExpectExec("^UPDATE asn.*").WithArgs(181818, 5, time.Now(), 1).WillReturnResult(sqlmock.NewResult(0, 1))
_, err = putAsn(1, []byte(`{ "asn":181818, "_links": { "cachegroup": { "id": 5 }}}`), xdb)
// error cases
mock.ExpectExec("^UPDATE asn.*").WillReturnError(fmt.Errorf("Testing Database Error"))
_, err = putAsn(1, []byte(`{ "asn":181818, "_links": { "cachegroup": { "id": 5 }}}`), xdb)
assert.NotNil(t, err, "Database error should be passed in error")
_, err = putAsn(1, []byte("{ quote missing herefoo\":181818, \"bar\": { \"cachegroup\": { \"id\": 5 }}}"), xdb)
assert.NotNil(t, err, "JSON error should be passed in error")
// delAsn unit test
mock.ExpectExec("^DELETE FROM asn.*").WithArgs(6).WillReturnResult(sqlmock.NewResult(0, 1))
_, err = delAsn(6, xdb)
// error case
mock.ExpectExec("^DELETE FROM asn.*").WillReturnError(fmt.Errorf("Testing Database Error"))
_, err = delAsn(6, xdb)
assert.NotNil(t, err, "Database error should be passed in error")
// Make sure all the right queries were seen
if err := mock.ExpectationsWereMet(); err != nil {
t.Errorf("there were unfulfilled expections: %s", err)
}
}
// Wrap wraps a bare *sql.DB (from the database/sql stdlib package) in a
// *db.Repo instance.
func Wrap(base *sql.DB, dialect string) *Repo {
return &Repo{DB: sqlx.NewDb(base, dialect)}
}
// New returns a new PostgreSQL storage.
func New(db *sql.DB) *Storage {
return &Storage{db: sqlx.NewDb(db, "postgres")}
}
func TestCatalogueServiceList(t *testing.T) {
logger = log.NewLogfmtLogger(os.Stderr)
db, mock, err := sqlmock.New()
if err != nil {
t.Fatalf("an error '%s' was not expected when opening stub database connection", err)
}
defer db.Close()
sqlxDB := sqlx.NewDb(db, "sqlmock")
var cols []string = []string{"id", "name", "description", "price", "count", "image_url_1", "image_url_2", "tag_name"}
// Test Case 1
mock.ExpectQuery("SELECT *").WillReturnRows(sqlmock.NewRows(cols).
AddRow(s1.ID, s1.Name, s1.Description, s1.Price, s1.Count, s1.ImageURL[0], s1.ImageURL[1], strings.Join(s1.Tags, ",")).
AddRow(s2.ID, s2.Name, s2.Description, s2.Price, s2.Count, s2.ImageURL[0], s2.ImageURL[1], strings.Join(s2.Tags, ",")).
AddRow(s3.ID, s3.Name, s3.Description, s3.Price, s3.Count, s3.ImageURL[0], s3.ImageURL[1], strings.Join(s3.Tags, ",")).
AddRow(s4.ID, s4.Name, s4.Description, s4.Price, s4.Count, s4.ImageURL[0], s4.ImageURL[1], strings.Join(s4.Tags, ",")).
AddRow(s5.ID, s5.Name, s5.Description, s5.Price, s5.Count, s5.ImageURL[0], s5.ImageURL[1], strings.Join(s5.Tags, ",")))
// Test Case 2
mock.ExpectQuery("SELECT *").WillReturnRows(sqlmock.NewRows(cols).
AddRow(s4.ID, s4.Name, s4.Description, s4.Price, s4.Count, s4.ImageURL[0], s4.ImageURL[1], strings.Join(s4.Tags, ",")).
AddRow(s1.ID, s1.Name, s1.Description, s1.Price, s1.Count, s1.ImageURL[0], s1.ImageURL[1], strings.Join(s1.Tags, ",")).
AddRow(s2.ID, s2.Name, s2.Description, s2.Price, s2.Count, s2.ImageURL[0], s2.ImageURL[1], strings.Join(s2.Tags, ",")))
// // Test Case 3
mock.ExpectQuery("SELECT *").WillReturnRows(sqlmock.NewRows(cols).
AddRow(s1.ID, s1.Name, s1.Description, s1.Price, s1.Count, s1.ImageURL[0], s1.ImageURL[1], strings.Join(s1.Tags, ",")).
AddRow(s3.ID, s3.Name, s3.Description, s3.Price, s3.Count, s3.ImageURL[0], s3.ImageURL[1], strings.Join(s3.Tags, ",")).
AddRow(s5.ID, s5.Name, s5.Description, s5.Price, s5.Count, s5.ImageURL[0], s5.ImageURL[1], strings.Join(s5.Tags, ",")))
s := NewCatalogueService(sqlxDB, logger)
for _, testcase := range []struct {
tags []string
order string
pageNum int
pageSize int
want []Sock
}{
{
tags: []string{},
order: "",
pageNum: 1,
pageSize: 5,
want: []Sock{s1, s2, s3, s4, s5},
},
{
tags: []string{},
order: "tag",
pageNum: 1,
pageSize: 3,
want: []Sock{s4, s1, s2},
},
{
tags: []string{"odd"},
order: "id",
pageNum: 2,
pageSize: 2,
want: []Sock{s5},
},
} {
have, err := s.List(testcase.tags, testcase.order, testcase.pageNum, testcase.pageSize)
if err != nil {
t.Errorf(
"List(%v, %s, %d, %d): returned error %s",
testcase.tags, testcase.order, testcase.pageNum, testcase.pageSize,
err.Error(),
)
}
if want := testcase.want; !reflect.DeepEqual(want, have) {
t.Errorf(
"List(%v, %s, %d, %d): want %v, have %v",
testcase.tags, testcase.order, testcase.pageNum, testcase.pageSize,
want, have,
)
}
}
}
