Golang sqlx.Open函数代码示例

// TODO (ajafri): we perform a file read on each one of these calls so use it sparingly or change the pattern
func createNewDB() (*sqlx.DB, error) {
	var version string = "dbVersion"

	b, err := ioutil.ReadFile("bridgdDBVersion.txt")
	if err != nil {
		b = []byte("") // simulate a version mismatch if reading the file throws an error
	}

	version = string(b)
	_, err = os.Stat(dbFile)

	if version != dbVersion || os.IsNotExist(err) {
		log.Println("Version difference detected - recreating database")

		os.Remove(dbFile) // clear the db if it is not versioned to the current version

		// let's create a new db and instantiate the structure
		db, err := sqlx.Open("sqlite3", "./rooms.db")
		if err != nil {
			return db, err
		}

		/* TABLE CREATION */
		_, err = db.Exec(schema)
		if err != nil {
			log.Printf("%q: %s\n", err, schema)
			return db, err
		}

		room := Room{Id: 1, Name: "no-yolo-zone"}
		// TODO (ajafri): testing code to initialize table with data. Take out.
		_, err = db.NamedExec("insert into rooms(id, name) values(:id, :name)", room)
		if err != nil {
			log.Printf("%q: %s\n", err)
			return db, err
		}

		// TODO (ajafri): testing code to initialize table with data. Take out.
		song := Song{VideoData: "{ \"id\": { \"kind\": \"youtube#video\", \"videoId\": \"IDKMKBmpwrg\" }, \"snippet\": { \"title\": \"Tinashe - Player (Audio) ft. Chris Brown\", \"description\": \"\\\"Player\\\" feat. Chris Brown from Tinashe's forthcoming new album, Joyride. Apple Music: http://smarturl.it/PlayerCBa?IQid=yt Spotify: http://smarturl.it/PlayerCBs?\", \"thumbnails\": { \"default\": { \"url\": \"https://i.ytimg.com/vi/IDKMKBmpwrg/default.jpg\" } }, \"channelTitle\": \"TinasheOfficialVEVO\" } }"}

		_, err = db.NamedExec("insert into songs(songId, roomId, videoData) values(:songId, :id, :videoData)", map[string]interface{}{"songId": "IDKMKBmpwrg", "id": room.Id, "videoData": song.VideoData})
		if err != nil {
			log.Printf("%q: %s\n", err)
			return db, err
		}

		err = ioutil.WriteFile("bridgdDBVersion.txt", []byte(dbVersion), 0644)
		if err != nil {
			return db, err
		}
	}

	return sqlx.Open("sqlite3", "./rooms.db")
}

func Open(url string) (db *sqlx.DB, err error) {
	switch {
	case strings.HasPrefix(url, "mysql://"):
		db, err = sqlx.Open(`mysql`, url[len("mysql://"):])
	case strings.HasPrefix(url, "postgres://"):
		db, err = sqlx.Open(`postgres`, url)
	case strings.HasPrefix(url, "mssql://"):
		db, err = sqlx.Open("mssql", splitMssql(url))
	default:
		db, err = nil, ErrProtocol
	}
	return db, err
}

func setupDB() error {
	var err error

	dbDir = filepath.Join(dataDir, "db")
	dbFile = filepath.Join(dbDir, "db.sql")
	err = os.MkdirAll(dbDir, 0700)
	if err != nil {
		return err
	}

	db, err = sqlx.Open("sqlite3", dbFile)
	if err != nil {
		return err
	}

	_, err = db.Exec(messagesSchema)
	if err != nil {
		return err
	}

	_, err = db.Exec(sessionsSchema)
	if err != nil {
		return err
	}

	_, err = db.Exec(groupsSchema)
	if err != nil {
		return err
	}

	migrations()

	return loadMessagesFromDB()
}

func waitingForStartup(ipAddress string) (string, error) {

	connect, err := sqlx.Open("postgres", fmt.Sprintf("postgres://postgres:[email protected]%s/postgres?sslmode=disable", ipAddress))

	if err != nil {

		return "", err
	}

	defer connect.Close()

	var serverVersion string

	for i := 0; i < 30; i++ {

		time.Sleep(time.Second * 2)

		if err := connect.Get(&serverVersion, "SHOW server_version"); err == nil {

			return serverVersion, nil
		}
	}

	return "", fmt.Errorf("connect timeout: %s", ipAddress)
}

// NewDB constructor for database connection.
func NewDB(driver, info string) (*DB, error) {
	db, err := sqlx.Open(driver, info)
	if err != nil {
		return &DB{}, err
	}
	return &DB{db}, nil
}

func main() {
	db, err := sqlx.Open("postgres", "postgres://postgres:[email protected]/godos_development?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}

	tx := db.MustBegin()
	now := time.Now()
	t := Todo{
		Subject:     "Mow Lawn!",
		Description: "Yuck!",
		CreatedAt:   now,
		UpdatedAt:   now,
	}
	tx.Exec("INSERT INTO todos (subject, description, created_at, updated_at) VALUES ($1, $2, $3, $4)", t.Subject, t.Description, t.CreatedAt, t.UpdatedAt)
	// // demonstrate the transaction:
	// tx.Exec("INSERT INTO todos (subject, description, created_at, updated_at) VALUES ($1, $2, $3, $4)", t.Subject, t.Description, t.CreatedAt, t.UpdatedAt)
	// tx.Exec("INSERT INTO todos (subject, description, created_at, updated_at) VALUES ($1, $2, $3, $4)", nil, t.Description, t.CreatedAt, t.UpdatedAt)
	tx.Commit()

	todos := []Todo{}
	db.Select(&todos, "select * from todos")

	for _, todo := range todos {
		log.Printf("Subject is %s", todo.Subject)
	}
}

func GetLeague() ([]modal.Modal, error) {
	var db *sqlx.DB
	var err error
	var leagues []modal.Modal

	if db, err = sqlx.Open("mysql", user+":"+password+"@/Dota?parseTime=true"); err != nil {
		return leagues, err
	}
	defer db.Close()

	// find league where deleted is 0000-00-00 00:00:00
	rows, err := db.Queryx("select * from League where deleted = ?", deletedTime)
	if err != nil {
		return leagues, err
	}

	for rows.Next() {
		var league modal.League

		err = rows.StructScan(&league)
		if err != nil {
			return leagues, err
		}

		leagues = append(leagues, league)
	}
	return leagues, nil
}

func GetTeam() ([]modal.Modal, error) {
	var db *sqlx.DB
	var err error
	var teams []modal.Modal

	if db, err = sqlx.Open("mysql", user+":"+password+"@/Dota?parseTime=true"); err != nil {
		return teams, err
	}
	defer db.Close()

	rows, err := db.Queryx("select * from Team")
	if err != nil {
		return teams, err
	}

	for rows.Next() {
		var team modal.Team

		err = rows.StructScan(&team)
		if err != nil {
			return teams, err
		}

		teams = append(teams, team)
	}
	return teams, nil
}

func dbconnect() {
	// Connect to database
	var err error
	db, err = sqlx.Open("sqlite3", *database)
	if err != nil {
		log.Fatal("Error connecting to database: %s\n", err)
	}

	// Ping the database
	err = db.Ping()
	if err != nil {
		log.Fatal("Error pinging database: %s\n", err)
	}

	// Setup migrations
	migrations := &migrate.FileMigrationSource{
		Dir: "app/database/migrations",
	}

	// Run migrations
	n, err := migrate.Exec(db.DB, "sqlite3", migrations, migrate.Up)
	if err != nil {
		log.Fatal("Error running database migrations: %s\n", err)
	} else {
		if n == 0 {
			log.Println("Nothing to migrate")
		} else {
			log.Println("Applied %d migrations", n)
		}
	}
}

func (s *testDriverSuite) SetUpSuite(c *C) {
	dsn := fmt.Sprintf("[email protected]%s:3306?test", *testHost)

	var err error
	s.db, err = sqlx.Open("mysql", dsn)
	c.Assert(err, IsNil)
}

func main() {
	log.Println("Main")
	db, _ = sqlx.Open("postgres", "dbname=terry host=localhost user=terry sslmode=disable")
	log.Println(db)

	now := time.Now()

	//numberOfWorker := 1
	//w := Writer{100000, db}
	//w.Run()

	numberOfWorker := 3
	var wg sync.WaitGroup
	wg.Add(numberOfWorker)
	for i := 0; i < numberOfWorker; i++ {
		go func(i int, conn *sqlx.DB, wg *sync.WaitGroup) {
			w := Writer{100000, conn}
			w.Run()
			log.Println("Writer", i, "finished")
			wg.Done()
		}(i, db, &wg)

	}

	wg.Wait()

	log.Println(numberOfWorker, "Writer", "took", time.Now().Sub(now))

}

func (p *mysqlSchema) buildSchema() error {
	var err error
	p.db, err = sqlx.Open("mysql", p.URL)
	if err != nil {
		return err
	}
	defer p.db.Close()

	res, err := p.db.Queryx(fmt.Sprintf("select TABLE_NAME as name from information_schema.TABLES where TABLE_SCHEMA = '%s'", p.Name))
	if err != nil {
		return err
	}
	for res.Next() {
		table := &fizz.Table{
			Columns: []fizz.Column{},
			Indexes: []fizz.Index{},
		}
		err = res.StructScan(table)
		if err != nil {
			return err
		}
		err = p.buildTableData(table)
		if err != nil {
			return err
		}

	}
	return nil
}

func GetAbility() ([]modal.Modal, error) {
	var db *sqlx.DB
	var err error
	var abilities []modal.Modal

	if db, err = sqlx.Open("mysql", user+":"+password+"@/Dota?parseTime=true"); err != nil {
		return abilities, err
	}
	defer db.Close()

	rows, err := db.Queryx("select * from Ability")
	if err != nil {
		return abilities, err
	}

	for rows.Next() {
		var ability modal.Ability

		err = rows.StructScan(&ability)
		if err != nil {
			return abilities, err
		}

		abilities = append(abilities, ability)
	}
	return abilities, nil
}

func TestQueryVariable(t *testing.T) {

	db, err := sqlx.Open("sqlite3", "./test_db.db")
	if err != nil {
		t.Fatal(err)
	}

	const q = `{
  Products(ProductID: 3) {
    ProductName,
    UnitsInStock,
    CategoryId,
    Categories(CategoryID: $CategoryID) {
       CategoryName
    }
  }
}`
	if ast, err := parse.NewQuery([]byte(q)); err != nil {
		t.Error(err.Error())
	} else {
		//		log.Println(prettyprint.AsJSON(d(ast, db, t)))
		d(ast, db, t)
	}

	defer db.Close()

}

func TestQueryComplex(t *testing.T) {

	db, err := sqlx.Open("sqlite3", "./test_db.db")
	if err != nil {
		t.Fatal(err)
	}

	const q = `{
  Products(ProductID: 9) {
    ProductName,
    UnitsInStock,
    ProductID,
    OrderDetails(ProductID: $ProductID) {
       OrderID,
       Orders(OrderID: $OrderID) {
         EmployeeID,
         Employees(EmployeeID: $EmployeeID) {
            FirstName
         }
       }
    }
  }
}`
	if ast, err := parse.NewQuery([]byte(q)); err != nil {
		t.Error(err.Error())
	} else {
		log.Println(prettyprint.AsJSON(d(ast, db, t)))
		//d(ast, db, t)
	}

	defer db.Close()

}