Golang database-sql.Open类(方法)实例源码

// CreateAndConnect connects and creates the requested DB (dropping
// if exists) then returns a new connection to the created DB.
func CreateAndConnect(pgURL url.URL, name string) (*gosql.DB, error) {
	{
		pgURL.Path = ""
		db, err := gosql.Open("postgres", pgURL.String())
		if err != nil {
			return nil, err
		}
		defer db.Close()

		if _, err := db.Exec(fmt.Sprintf("DROP DATABASE IF EXISTS %s", name)); err != nil {
			return nil, err
		}

		if _, err := db.Exec(fmt.Sprintf(`CREATE DATABASE %s`, name)); err != nil {
			return nil, err
		}
	}

	pgURL.Path = name

	db, err := gosql.Open("postgres", pgURL.String())
	if err != nil {
		return nil, err
	}
	return db, nil
}

func GetUser(username string) (u User, err error) {
	db, err := sql.Open("mysql", DBLoginString)
	checkErr(err)
	defer db.Close()
	stmt, err := db.Prepare("SELECT * FROM userinfo WHERE username=?")
	checkErr(err)
	rows, err := stmt.Query(username)
	if !rows.Next() {
		err = errors.New("Not found")
		return
	}
	var disposable string
	err = rows.Scan(&u.Email, &disposable, &u.Created, &u.Username,
		&u.Score)
	if err != nil {
		return
	}
	db.Close()
	scores, uids := GetSuccesfulAttempts(u.Email)
	db, _ = sql.Open("mysql", DBLoginString)
	u.Finished = make(map[string]Challenge_link)
	for i := 0; i < len(uids); i++ {
		stmt, err = db.Prepare("SELECT Title FROM challenges WHERE " +
			"UID=?")
		rows, err = stmt.Query(uids[i])
		checkErr(err)
		rows.Next()
		var title string
		rows.Scan(&title)
		u.Finished[title] = Challenge_link{Title: title, Score: scores[i],
			UID: uids[i]}
	}
	return
}

// NewDB reinitializes the mysql database and returns a connection to the db.
func (c *SQLContainer) NewDB() (*sql.DB, error) {
	// if a connection is open, close it
	if c.conn != nil {
		c.conn.Close()
		c.conn = nil
	}
	db, err := sql.Open("mysql", c.rootStr)
	if err != nil {
		return nil, fmt.Errorf("can't connect to database: %v", err)
	}
	tx, err := db.Begin()
	if err != nil {
		return nil, fmt.Errorf("could not begin tx: %v", err)
	}
	defer tx.Rollback()
	if err = DropTables(tx); err != nil {
		return nil, fmt.Errorf("could not drop tables: %v", err)
	}
	if err = InitTables(tx, "mysql"); err != nil {
		return nil, fmt.Errorf("could not drop tables: %v", err)
	}
	db.Close()
	conn, err := sql.Open("mysql", c.connStr)
	if err != nil {
		return nil, err
	}
	c.conn = conn
	return conn, nil
}

func makeTestDB(t testing.TB, ddls ...string) *DB {
	const dbName = "squalor_test"

	// We need to create an initial database connection for the purpose
	// of creating our testing database.
	tmpDB, err := sql.Open("mysql", makeTestDSN(""))
	if err != nil {
		t.Fatal(err)
	}
	defer tmpDB.Close()
	tmpDB.SetMaxOpenConns(1)

	if _, err := tmpDB.Exec("SET sql_notes=0"); err != nil {
		t.Fatal(err)
	}
	if _, err := tmpDB.Exec("DROP DATABASE IF EXISTS " + dbName); err != nil {
		t.Fatal(err)
	}
	if _, err := tmpDB.Exec("CREATE DATABASE IF NOT EXISTS " + dbName); err != nil {
		t.Fatal(err)
	}

	// Now create our actual database connection.
	db, err := sql.Open("mysql", makeTestDSN(dbName))
	if err != nil {
		t.Fatal(err)
	}
	for _, ddl := range ddls {
		if _, err := db.Exec(ddl); err != nil {
			t.Fatal(err)
		}
	}
	return NewDB(db)
}

func openDB(db_url_s string) (db *sql.DB) {
	db_url, err := url.Parse(db_url_s)
	if err != nil {
		log.Fatalln("Failed to parse", db_url_s)
	}

	if db_url.Scheme == "sqlite" {
		db, err = sql.Open("sqlite3", "triton-s3.db")
		if err != nil {
			log.Fatalln("Failed to open db", err)
		}

		// sqlite doesn't so much like concurrency
		db.SetMaxOpenConns(1)
		return
	} else if db_url.Scheme == "postgres" {
		db, err = sql.Open("postgres", db_url_s)
		if err != nil {
			log.Fatalln("Failed to open db", err)
		}
		return
	} else {
		log.Fatalln("Unknown db scheme", db_url.Scheme)
		return
	}
}

func TestLegacyAuthWireCrypt(t *testing.T) {
	var n int
	conn, err := sql.Open("firebirdsql_createdb", "sysdba:[email protected]:3050/tmp/go_test_connect.fdb")
	if err != nil {
		t.Fatalf("Error connecting: %v", err)
	}

	conn, err = sql.Open("firebirdsql", "sysdba:[email protected]:3050/tmp/go_test_connect.fdb?auth_plugin_anme=Legacy_Auth")
	if err != nil {
		t.Fatalf("Error connecting: %v", err)
	}
	err = conn.QueryRow("SELECT Count(*) FROM rdb$relations").Scan(&n)

	conn, err = sql.Open("firebirdsql", "sysdba:[email protected]:3050/tmp/go_test_connect.fdb?wire_crypt=false")
	if err != nil {
		t.Fatalf("Error connecting: %v", err)
	}
	err = conn.QueryRow("SELECT Count(*) FROM rdb$relations").Scan(&n)

	conn, err = sql.Open("firebirdsql", "sysdba:[email protected]:3050/tmp/go_test_connect.fdb?auth_plugin_name=Legacy_Auth&wire_auth=true")
	if err != nil {
		t.Fatalf("Error connecting: %v", err)
	}
	err = conn.QueryRow("SELECT Count(*) FROM rdb$relations").Scan(&n)
	conn.Close()

	conn, err = sql.Open("firebirdsql", "sysdba:[email protected]:3050/tmp/go_test_connect.fdb?auth_plugin_name=Legacy_Auth&wire_auth=false")
	if err != nil {
		t.Fatalf("Error connecting: %v", err)
	}
	err = conn.QueryRow("SELECT Count(*) FROM rdb$relations").Scan(&n)
	conn.Close()
}

func TestShouldRunWithinTransaction(t *testing.T) {
	t.Parallel()
	var count int
	db, err := sql.Open("txdb", "one")
	if err != nil {
		t.Fatalf("failed to open a mysql connection, have you run 'make test'? err: %s", err)
	}

	_, err = db.Exec(`INSERT INTO users(username, email) VALUES("txdb", "[email protected]")`)
	if err != nil {
		t.Fatalf("failed to insert an user: %s", err)
	}
	err = db.QueryRow("SELECT COUNT(id) FROM users").Scan(&count)
	if err != nil {
		t.Fatalf("failed to count users: %s", err)
	}
	if count != 4 {
		t.Fatalf("expected 4 users to be in database, but got %d", count)
	}
	db.Close()

	db, err = sql.Open("txdb", "two")
	if err != nil {
		t.Fatalf("failed to reopen a mysql connection: %s", err)
	}

	err = db.QueryRow("SELECT COUNT(id) FROM users").Scan(&count)
	if err != nil {
		t.Fatalf("failed to count users: %s", err)
	}
	if count != 3 {
		t.Fatalf("expected 3 users to be in database, but got %d", count)
	}
	db.Close()
}

func ConnectDB() *sql.DB {
	if config.Environment == "production" {
		dbinfo := fmt.Sprintf("user=%s password=%s dbname=%s host=%s sslmode=disable",
			config.DBUser, config.DBPassword, config.DBName, config.DBHost)
		db, err := sql.Open("postgres", dbinfo)
		if err != nil {
			log.Fatal("opening issue [e]: %s", err)
		}

		// sqlStmt, _ := ioutil.ReadFile("./schema_postgres.sql")
		// if err != nil {
		// 	log.Fatal(" [e] : %s", err)
		// }

		// _, err = db.Exec(string(sqlStmt))
		// if err != nil {
		// 	log.Printf("Buidling tables[e] : %s", err)
		// }

		return db
	} else {
		databasefile = "./tmp/feeds.db"
		//connect to database, create it if it doesn't exist.
		// equivalent to Python's `if os.path.exists(filename)`
		if _, err := os.Stat(databasefile); err == nil {
			db, err := sql.Open("sqlite3", databasefile)
			if err != nil {
				log.Fatal(os.Stderr, "[e] : %s", err)
			}
			return db
		}
		return CreateDB(databasefile)
	}
}

func main() {

	//db access for releases
	db, err := sql.Open("sqlite3", "./release.db")
	if err != nil {
		panic(err.Error())
	}
	reldb := &gorp.DbMap{Db: db, Dialect: gorp.SqliteDialect{}}
	reldb.AddTableWithName(town.Release{}, "release").SetKeys(false, "Checksum").ColMap("Checksum").SetUnique(true).SetNotNull(true)
	reldb.CreateTablesIfNotExists()

	//db for logs
	//different database cause of locks with high log frequency
	dblog, err := sql.Open("sqlite3", "./logs.db")
	dbmap := &gorp.DbMap{Db: dblog, Dialect: gorp.SqliteDialect{}}
	dbmap.AddTableWithName(mydb.Log{}, "log").SetKeys(true, "Uid")
	dbmap.CreateTablesIfNotExists()
	logdb := mydb.DBLog{DB: dbmap}
	if err != nil {
		panic(err.Error())
	}

	logging.SetOutput(logdb)

	//read config file
	c, _ := config.ReadDefault("default.ini")

	//webserver
	serv := &webserv.Server{Config: c, RelDB: reldb, LogDB: dbmap}
	serv.Init()

}

func (m *MySQLRunner) Run(signals <-chan os.Signal, ready chan<- struct{}) error {
	defer GinkgoRecover()

	var err error
	m.db, err = sql.Open("mysql", "diego:[email protected]/")
	Expect(err).NotTo(HaveOccurred())
	Expect(m.db.Ping()).NotTo(HaveOccurred())

	m.db.Exec(fmt.Sprintf("DROP DATABASE %s", m.sqlDBName))
	_, err = m.db.Exec(fmt.Sprintf("CREATE DATABASE %s", m.sqlDBName))
	Expect(err).NotTo(HaveOccurred())

	m.db, err = sql.Open("mysql", fmt.Sprintf("diego:[email protected]/%s", m.sqlDBName))
	Expect(err).NotTo(HaveOccurred())
	Expect(m.db.Ping()).NotTo(HaveOccurred())

	close(ready)

	<-signals

	_, err = m.db.Exec(fmt.Sprintf("DROP DATABASE %s", m.sqlDBName))
	Expect(err).NotTo(HaveOccurred())
	Expect(m.db.Close()).To(Succeed())
	m.db = nil

	return nil
}

// Open postgresql data store.
// dropDatabase only for testing use
func Open(connectionURL string, databaseName string, dropDatabase bool) (data.Store, error) {
	constr := util.ReplaceEnv(connectionURL)
	var db, err = sql.Open("postgres", constr)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	if dropDatabase {
		_, err = db.Exec(fmt.Sprintf("DROP DATABASE IF EXISTS %s", databaseName))
		if err != nil {
			debug.Error("unable to drop database: %v", err)
		}
	}

	_, err = db.Exec(fmt.Sprintf("CREATE DATABASE %s", databaseName))
	if err != nil {
		debug.Error("unable to create database: %v", err)
	}

	db, err = sql.Open("postgres", constr+" dbname="+databaseName)
	if err != nil {
		return nil, err
	}
	var store = &store{
		db:   db,
		name: databaseName,
	}
	return store, nil
}

func TestReturning(t *testing.T) {
	conn, _ := sql.Open("firebirdsql_createdb", "SYSDBA:[email protected]:3050/tmp/go_test_returning.fdb")
	defer conn.Close()

	conn.Exec(`
        CREATE TABLE test_returning (
            f1 integer NOT NULL,
            f2 integer default 2,
            f3 varchar(20) default 'abc')`)
	conn.Close()

	conn, _ = sql.Open("firebirdsql", "SYSDBA:[email protected]:3050/tmp/go_test_returning.fdb")

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

		rows, err := conn.Query("INSERT INTO test_returning (f1) values (1) returning f2, f3")
		if err != nil {
			t.Fatalf("Error Insert returning : %v", err)
		}
		var f2 int
		var f3 string
		rows.Next()
		rows.Scan(&f2, &f3)
		if f2 != 2 || f3 != "abc" {
			t.Fatalf("Bad value insert returning: %v,%v", f2, f3)
		}
	}

}

func main() {
	flag.Parse()
	flag.Usage()

	db, err := sql.Open(*flgDB, *flgDBlink)
	if err != nil {
		fmt.Println(err)
		return
	}
	defer db.Close()
	fmt.Println(*flgDB, *flgDBlink, "connected db")
	db2, _ := sql.Open(*flgDB, *flgDBlink)
	db3, _ := sql.Open(*flgDB, *flgDBlink)

	conn, err := net.Dial("tcp", *flgMc)
	if err != nil {
		fmt.Println(err)
		return
	}
	defer conn.Close()
	fmt.Println(*flgMc, "connected mc")

	go ReqProc(db)
	go QuoteProc(db2)
	go KlineProc(db3)
	go Send(conn)
	go Register(conn)
	go Recv(conn)

	select {}
}

func init() {
	var err error

	switch os.Getenv("DB") {
	case "mysql":
		dbType = "mysql"
		log.Print("Using mysql")
		adapter = Mariadb{}
		db, err = sql.Open("mysql", "gomigrate:[email protected]/gomigrate")
	case "sqlite3":
		dbType = "sqlite3"
		log.Print("Using sqlite3")
		adapter = Sqlite3{}
		db, err = sql.Open("sqlite3", "file::memory:?cache=shared")
	default:
		dbType = "pg"
		log.Print("Using postgres")
		adapter = Postgres{}
		db, err = sql.Open("postgres", "host=localhost dbname=gomigrate sslmode=disable")
	}

	if err != nil {
		panic(err)
	}
}

func main() {
	readCommandLineArgs()

	var err error
	s := fmt.Sprintf("%s:@/%s?charset=utf8&parseTime=True", App.DBUser, App.DBDir)
	App.dbdir, err = sql.Open("mysql", s)
	if nil != err {
		fmt.Printf("sql.Open for database=%s, dbuser=%s: Error = %v\n", App.DBDir, App.DBUser, err)
	}
	defer App.dbdir.Close()
	err = App.dbdir.Ping()
	if nil != err {
		fmt.Printf("App.DBDir.Ping for database=%s, dbuser=%s: Error = %v\n", App.DBDir, App.DBUser, err)
	}

	s = fmt.Sprintf("%s:@/%s?charset=utf8&parseTime=True", App.DBUser, App.DBRR)
	App.dbrr, err = sql.Open("mysql", s)
	if nil != err {
		fmt.Printf("sql.Open for database=%s, dbuser=%s: Error = %v\n", App.DBRR, App.DBUser, err)
	}
	defer App.dbrr.Close()
	err = App.dbrr.Ping()
	if nil != err {
		fmt.Printf("App.DBRR.Ping for database=%s, dbuser=%s: Error = %v\n", App.DBRR, App.DBUser, err)
	}
	buildPreparedStatements()
	initLists()
	initJFmt()
	loadDefaultCashAccts()

	//  func Date(year int, month Month, day, hour, min, sec, nsec int, loc *Location) Time
	start := time.Date(2015, time.November, 1, 0, 0, 0, 0, time.UTC)
	stop := time.Date(2015, time.December, 1, 0, 0, 0, 0, time.UTC)
	ReportAll(start, stop, App.Report)
}

// GetConnection get a database connection to a given database name, uses environment variables to
// get the hostname, user, password, and port numbers returns a database connection
func GetConnection(database string) (*sql.DB, error) {
	var dbHost, dbUser, dbPort, dbPassword string
	dbHost = os.Getenv("DB_HOST")
	dbUser = os.Getenv("DB_USER")
	dbPort = os.Getenv("DB_PORT")
	dbPassword = os.Getenv("DB_PASSWORD")

	if dbHost == "" || dbUser == "" || dbPort == "" {
		logit.Error.Println("DB_HOST [" + dbHost + "]")
		logit.Error.Println("DB_USER [" + dbUser + "]")
		logit.Error.Println("DB_PORT [" + dbPort + "]")
		logit.Error.Println("error in getting required env vars")
		panic("could not get required env vars")
	}

	var dbConn *sql.DB
	var err error
	if dbPassword != "" {
		//logit.Info.Println("connecting to database " + database + " host=" + dbHost + " user=" + dbUser + " port=" + dbPort + " password=" + dbPassword)
		dbConn, err = sql.Open("postgres", "sslmode=disable user="+dbUser+" host="+dbHost+" port="+dbPort+" dbname="+database+" password="+dbPassword)
	} else {
		//logit.Info.Println("connecting to database " + database + " host=" + dbHost + " user=" + dbUser + " port=" + dbPort)
		dbConn, err = sql.Open("postgres", "sslmode=disable user="+dbUser+" host="+dbHost+" port="+dbPort+" dbname="+database)
	}
	if err != nil {
		logit.Error.Println(err.Error())
	}
	return dbConn, err
}

// InitDatabase creates/opens and initializes the sqlite3 database
func InitDatabase() (db *sql.DB, err error) {
	// check for db env variables
	// fall back to config file

	driver := os.Getenv("VROPS_DB_DRIVER")
	if driver == "" {
		config, _ := NewConfig()
		driver = config.DB.Driver
	}

	filename := os.Getenv("VROPS_DB_FILENAME")
	if filename == "" {
		config, _ := NewConfig()
		filename = config.DB.Filename
	}

	log.Printf("sql driver: %s", driver)
	log.Printf("sql filename: %s", filename)

	if _, err = os.Stat(filename); err == nil {
		db, err = sql.Open(driver, filename)
	} else {
		db, err = sql.Open(driver, filename)
		if err == nil {
			_, err = db.Exec(createDbSQL)
		}
	}

	return
}

func DatabaseConnect(c conf.Database) (*sql.DB, error) {
	var db *sql.DB
	var err error

	if c.Type == "mysql" {
		if c.Password == "" {
			db, err = sql.Open("mysql", c.Username+"@"+c.Protocol+"("+c.Address+":"+strconv.Itoa(c.Port)+")/")
		} else {
			db, err = sql.Open("mysql", c.Username+":"+c.Password+"@"+c.Protocol+"("+c.Address+":"+strconv.Itoa(c.Port)+")/")
		}
	}

	if err != nil {
		slog.Errorf("%v: %v: %v: %v", "Database", c.Type, "connect open error", err)
		return nil, fmt.Errorf("%v: %v", "connect open error", err)
	}

	// check if can connect to database, if fail, check again every minute until connected
	for {
		err = db.Ping()
		if err == nil {
			break
		}
		slog.Errorf("%v: %v: %v: %v", "Database", c.Type, "connect ping error", err)
		next := time.After(time.Minute)
		select {
		case <-next:
		case <-ContinuousCollectorVars.quit:
			db.Close()
			return nil, fmt.Errorf("connect while quitting")
		}
	}

	return db, nil
}

func (p *PostgresRunner) Run(signals <-chan os.Signal, ready chan<- struct{}) error {
	defer GinkgoRecover()

	var err error
	p.db, err = sql.Open("postgres", "postgres://diego:[email protected]")
	Expect(err).NotTo(HaveOccurred())
	Expect(p.db.Ping()).NotTo(HaveOccurred())

	p.db.Exec(fmt.Sprintf("DROP DATABASE %s", p.sqlDBName))
	_, err = p.db.Exec(fmt.Sprintf("CREATE DATABASE %s", p.sqlDBName))
	Expect(err).NotTo(HaveOccurred())

	p.db, err = sql.Open("postgres", fmt.Sprintf("postgres://diego:[email protected]/%s", p.sqlDBName))
	Expect(err).NotTo(HaveOccurred())
	Expect(p.db.Ping()).NotTo(HaveOccurred())

	close(ready)

	<-signals

	// We need to close the connection to the database we want to drop before dropping it.
	p.db.Close()
	p.db, err = sql.Open("postgres", "postgres://diego:[email protected]")
	Expect(err).NotTo(HaveOccurred())

	_, err = p.db.Exec(fmt.Sprintf("DROP DATABASE %s", p.sqlDBName))
	Expect(err).NotTo(HaveOccurred())
	Expect(p.db.Close()).To(Succeed())

	return nil
}

func runTests(t *testing.T, dsn string, tests ...func(dbt *DBTest)) {
	if !available {
		t.Skipf("MySQL server not running on %s", netAddr)
	}

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		t.Fatalf("error connecting: %s", err.Error())
	}
	defer db.Close()

	db.Exec("DROP TABLE IF EXISTS test")

	dsn2 := dsn + "&interpolateParams=true"
	var db2 *sql.DB
	if _, err := ParseDSN(dsn2); err != errInvalidDSNUnsafeCollation {
		db2, err = sql.Open("mysql", dsn2)
		if err != nil {
			t.Fatalf("error connecting: %s", err.Error())
		}
		defer db2.Close()
	}

	dbt := &DBTest{t, db}
	dbt2 := &DBTest{t, db2}
	for _, test := range tests {
		test(dbt)
		dbt.db.Exec("DROP TABLE IF EXISTS test")
		if db2 != nil {
			test(dbt2)
			dbt2.db.Exec("DROP TABLE IF EXISTS test")
		}
	}
}