// 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://*****:*****@%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://*****:*****@localhost/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("root@%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()
}
func main() {
DB, Err := sqlx.Open(db_driver, db_source)
CheckErr(Err)
if DB.Ping() != nil {
fmt.Println("Cannot connect to Database")
} else {
fmt.Println("Connected to database")
}
//menyalakan web server
r := gin.Default()
r.Use(AddDB(DB))
//load template
r.LoadHTMLGlob("./template/*")
r.GET("/testinput", func(c *gin.Context) {
c.HTML(http.StatusOK, "testinput.tmpl", gin.H{})
})
r.POST("/coba", regisDevice)
r.GET("/out", outDevice)
//tx.MustExec("INSERT INTO device_list (device_name, host, port, token, description) VALUES ($1, $2, $3, $4, $5)", "arduino", "29", "33", "3", "tes masuk")
r.Run(":8080") // listen and serve on 0.0.0.0:8080
}
// OpenDB opens the database in the given directory; if required, the database
// will be initialized.
func OpenDB(dir string) (err error) {
log.SetLevelByString("error")
dir = filepath.Join(dir, "tidb")
_, err = os.Stat(dir)
init := os.IsNotExist(err)
name := "tidb"
db, err = sqlx.Open("tidb", "goleveldb://"+dir+"/"+name)
if err != nil {
return fmt.Errorf("db open failed: %s", err)
}
if err := db.Ping(); err != nil {
return fmt.Errorf("db ping failed: %s", err)
}
if init {
// b, err := ioutil.ReadFile(filepath.Join("data", "schema.sql"))
// if err != nil {
// return fmt.Errorf("read schema file failed: %s", err)
// }
// if _, err = db.Exec(string(b)); err != nil {
if _, err = db.Exec(schemasql); err != nil {
return fmt.Errorf("exec schema failed: %s", err)
}
if err = insertCards(); err != nil {
return fmt.Errorf("insert cards failed: %s", err)
}
}
return nil
}
func main() {
var err error
loadConfig()
//conenct to api databases
db, err = sqlx.Open("postgres", cfg.DB.connString+" sslmode=disable")
if err != nil {
log.Println("error while connecting to database:", err)
panic(err)
}
db.SetMaxIdleConns(cfg.PoolMin)
db.SetMaxOpenConns(cfg.PoolMax)
r := mux.NewRouter()
r.HandleFunc("/query", query).Methods("POST")
// start HTTP server
fmt.Println("starting server on port:", cfg.Port)
if err := http.ListenAndServe(fmt.Sprintf("localhost:%d", cfg.Port), r); err != nil {
log.Fatal("error while starting http server:", err)
}
}
func (s *Server) Start() {
config := osin.NewServerConfig()
config.ErrorStatusCode = 401
url := fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=disable",
os.Getenv("DB_USER"),
os.Getenv("DB_PASS"),
os.Getenv("DB_HOST"),
os.Getenv("DB_NAME"),
)
db, err := sqlx.Open("postgres", url)
if err != nil {
log.Fatalln(err.Error())
}
storage := postgres.New(db.DB)
s.server = osin.NewServer(config, storage)
wsContainer := restful.NewContainer()
r := UserResource{}
r.Register(wsContainer, db)
ws := new(restful.WebService)
ws.Route(ws.POST("/authorize").
Consumes("application/x-www-form-urlencoded").
To(s.authorize))
wsContainer.Add(ws)
address := fmt.Sprintf("%s:%s", s.Host, s.Port)
log.Printf("Listening on %s", address)
log.Fatalln(http.ListenAndServe(address, wsContainer))
}
// connect to the Db
func init() {
var err error
Db, err = sqlx.Open("postgres", "user=gwp dbname=gwp password=gwp sslmode=disable")
if err != nil {
panic(err)
}
}
//Connect to mysqldb using the info/credentail stored in the env.
func Connect(e env.VARS) (c Connector, err error) {
db, err := sqlx.Open("mysql", fmt.Sprintf("%s:%s@tcp(%v:%v)/%s", e.DbUser, e.DbPwd, e.DbAddres, e.DbPort, e.DbName))
if err != nil {
return c, err
}
c.DB = db
db.SetMaxIdleConns(e.DbMaxIdleConns)
db.SetMaxOpenConns(e.DbMaxOpenConns)
// prepare the queries
offer, err := db.Prepare(offerGetFieldQuery)
if err != nil {
return c, err
}
c.prepOfferQuery = offer
app, err := db.Prepare(appGetFieldQuery)
if err != nil {
return c, err
}
c.prepAppQuery = app
user, err := db.Prepare(userGetFieldQuery)
if err != nil {
return c, err
}
c.prepUserQuery = user
catalog, err := db.Prepare(catalogGetFieldQuery)
if err != nil {
return c, err
}
c.prepCatalogQuery = catalog
return c, nil
}
func GetHero() ([]modal.Modal, error) {
var db *sqlx.DB
var err error
var heroes []modal.Modal
if db, err = sqlx.Open("mysql", user+":"+password+"@/Dota?parseTime=true"); err != nil {
return heroes, err
}
defer db.Close()
rows, err := db.Queryx("select * from Hero")
if err != nil {
return heroes, err
}
for rows.Next() {
var hero modal.Hero
err = rows.StructScan(&hero)
if err != nil {
return heroes, err
}
heroes = append(heroes, hero)
}
return heroes, nil
}
func SetupDB(t *testing.T) *sqlx.DB {
// open the db
db, err := sqlx.Open("postgres", dbURL)
if err != nil {
t.Fatal(err)
}
// run pending migrations
// assumes path for migrations is data/migrations
t.Log("Starting database migrations...")
pwd, _ := os.Getwd()
errs, ok := migrate.UpSync(dbURL, filepath.Join(pwd, "/../data/migrations"))
if !ok {
for _, err := range errs {
t.Error(err)
t.Fatal("SetupDB: migrations failed")
}
}
t.Log("Completed database migrations")
// truncate tables
t.Log("Truncating tables...")
tables := []string{}
q := "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name <> 'schema_migrations';"
if err := db.Select(&tables, q); err != nil {
t.Fatal(err)
}
if _, err := db.Exec(fmt.Sprintf("TRUNCATE %s RESTART IDENTITY;", strings.Join(tables, ", "))); err != nil {
t.Fatal(err)
}
t.Log("Completed truncating tables")
return db
}
func initDB(f func(db *sqlx.DB)) {
os.Remove("./foo.db")
db, _ := sqlx.Open("sqlite3", "./foo.db")
db.MustExec(schema)
f(db)
os.Remove("./foo.db")
}
func connectMySQL() {
var db *sqlx.DB
c, err := dockertest.ConnectToMySQL(15, time.Second, func(url string) bool {
var err error
db, err = sqlx.Open("mysql", url)
if err != nil {
log.Printf("Got error in mysql connector: %s", err)
return false
}
return db.Ping() == nil
})
if err != nil {
log.Fatalf("Could not connect to database: %s", err)
}
containers = append(containers, c)
s := NewSQLManager(db, nil)
if err = s.CreateSchemas(); err != nil {
log.Fatalf("Could not create mysql schema: %v", err)
}
managers["mysql"] = s
}
func (p *sqliteSchema) buildSchema() error {
var err error
p.db, err = sqlx.Open("sqlite3", p.URL)
if err != nil {
return err
}
defer p.db.Close()
res, err := p.db.Queryx("SELECT name FROM sqlite_master WHERE type='table';")
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
}
if table.Name != "sqlite_sequence" {
err = p.buildTableData(table)
if err != nil {
return err
}
}
}
return nil
}
func New() (*Client, error) {
str, err := connection.BuildString(command.Opts)
if command.Opts.Debug && str != "" {
fmt.Println("Creating a new client for:", str)
}
if err != nil {
return nil, err
}
db, err := sqlx.Open("postgres", str)
if err != nil {
return nil, err
}
client := Client{
db: db,
ConnectionString: str,
History: history.New(),
}
return &client, nil
}
func initArrangement() (*sqlx.DB, *Repo) {
db, err := sqlx.Open("sqlite3", ":memory:")
if err != nil {
panic(err)
}
file := initSQLFiles()
defer os.Remove(file.Name())
repo, err := LoadFromFile(file.Name())
if err != nil {
panic(err)
}
sql, err := repo.LookupSQL("create-users-table")
if err != nil {
panic(err)
}
db.MustExec(sql)
return db, repo
}
func main() {
beego.BeeLogger.Info("!!!!!!")
db, err := sqlx.Open("mysql", "root:asharov@tcp(db:3306)/blog3?charset=utf8")
if err != nil {
panic(err)
}
components.App.Db = db
//websocket server
wampServer := wamp.NewServer()
beego.BeeLogger.Info("Running WS server wait for client requests")
go wampServer.ListenAndServe()
//run upgrade server, for upgrade http to ws
addr := ":8081"
upgradeServer := &http.Server{
Addr: addr,
Handler: wampServer,
}
beego.BeeLogger.Info("Running Upgrade server on %s", addr)
go upgradeServer.ListenAndServe()
//run usual web app
beego.Run()
}
func NewMysqlDataProvider(config map[string]interface{}) (MysqlDataEndPoint, error) {
db, err := sql.Open("mysql", config["connectionString"].(string))
if err != nil {
return MysqlDataEndPoint{}, err
}
return MysqlDataEndPoint{db: db, tableName: config["table"].(string)}, nil
}
