github.com/duskeagle/pop@v4.10.1-0.20190417200916-92f2b794aab5+incompatible/dialect_cockroach.go (about) 1 package pop 2 3 import ( 4 "context" 5 "database/sql" 6 "fmt" 7 "io" 8 "os" 9 "os/exec" 10 "path/filepath" 11 "strings" 12 "sync" 13 14 _ "github.com/cockroachdb/cockroach-go/crdb" // Load CockroachdbQL/postgres Go driver which also loads github.com/lib/pq 15 "github.com/gobuffalo/fizz" 16 "github.com/gobuffalo/fizz/translators" 17 "github.com/gobuffalo/pop/columns" 18 "github.com/gobuffalo/pop/logging" 19 "github.com/jmoiron/sqlx" 20 "github.com/markbates/going/defaults" 21 "github.com/pkg/errors" 22 ) 23 24 const nameCockroach = "cockroach" 25 const portCockroach = "26257" 26 27 func init() { 28 AvailableDialects = append(AvailableDialects, nameCockroach) 29 dialectSynonyms["cockroachdb"] = nameCockroach 30 dialectSynonyms["crdb"] = nameCockroach 31 finalizer[nameCockroach] = finalizerCockroach 32 newConnection[nameCockroach] = newCockroach 33 } 34 35 var _ dialect = &cockroach{} 36 37 // ServerInfo holds informational data about connected database server. 38 type cockroachInfo struct { 39 VersionString string `db:"version"` 40 product string `db:"-"` 41 license string `db:"-"` 42 version string `db:"-"` 43 buildInfo string `db:"-"` 44 client string `db:"-"` 45 } 46 47 type cockroach struct { 48 commonDialect 49 translateCache map[string]string 50 mu sync.Mutex 51 info cockroachInfo 52 } 53 54 func (p *cockroach) Name() string { 55 return nameCockroach 56 } 57 58 func (p *cockroach) Details() *ConnectionDetails { 59 return p.ConnectionDetails 60 } 61 62 func (p *cockroach) Create(s store, model *Model, cols columns.Columns) error { 63 keyType := model.PrimaryKeyType() 64 switch keyType { 65 case "int", "int64": 66 cols.Remove("id") 67 id := struct { 68 ID int `db:"id"` 69 }{} 70 w := cols.Writeable() 71 var query string 72 if len(w.Cols) > 0 { 73 query = fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s) returning id", model.TableName(), w.String(), w.SymbolizedString()) 74 } else { 75 query = fmt.Sprintf("INSERT INTO %s DEFAULT VALUES returning id", model.TableName()) 76 } 77 log(logging.SQL, query) 78 stmt, err := s.PrepareNamed(query) 79 if err != nil { 80 return errors.WithStack(err) 81 } 82 err = stmt.Get(&id, model.Value) 83 if err != nil { 84 if err := stmt.Close(); err != nil { 85 return errors.WithMessage(err, "failed to close statement") 86 } 87 return errors.WithStack(err) 88 } 89 model.setID(id.ID) 90 return errors.WithMessage(stmt.Close(), "failed to close statement") 91 } 92 return genericCreate(s, model, cols) 93 } 94 95 func (p *cockroach) Update(s store, model *Model, cols columns.Columns) error { 96 return genericUpdate(s, model, cols) 97 } 98 99 func (p *cockroach) Destroy(s store, model *Model) error { 100 stmt := p.TranslateSQL(fmt.Sprintf("DELETE FROM %s WHERE %s", model.TableName(), model.whereID())) 101 _, err := genericExec(s, stmt, model.ID()) 102 return errors.WithStack(err) 103 } 104 105 func (p *cockroach) SelectOne(s store, model *Model, query Query) error { 106 return genericSelectOne(s, model, query) 107 } 108 109 func (p *cockroach) SelectMany(s store, models *Model, query Query) error { 110 return genericSelectMany(s, models, query) 111 } 112 113 func (p *cockroach) CreateDB() error { 114 // createdb -h db -p 5432 -U cockroach enterprise_development 115 deets := p.ConnectionDetails 116 db, err := sql.Open(deets.Dialect, p.urlWithoutDb()) 117 if err != nil { 118 return errors.Wrapf(err, "error creating Cockroach database %s", deets.Database) 119 } 120 defer db.Close() 121 query := fmt.Sprintf("CREATE DATABASE \"%s\"", deets.Database) 122 log(logging.SQL, query) 123 124 _, err = db.Exec(query) 125 if err != nil { 126 return errors.Wrapf(err, "error creating Cockroach database %s", deets.Database) 127 } 128 129 log(logging.Info, "created database %s", deets.Database) 130 return nil 131 } 132 133 func (p *cockroach) DropDB() error { 134 deets := p.ConnectionDetails 135 db, err := sql.Open(deets.Dialect, p.urlWithoutDb()) 136 if err != nil { 137 return errors.Wrapf(err, "error dropping Cockroach database %s", deets.Database) 138 } 139 defer db.Close() 140 query := fmt.Sprintf("DROP DATABASE \"%s\" CASCADE;", deets.Database) 141 log(logging.SQL, query) 142 143 _, err = db.Exec(query) 144 if err != nil { 145 return errors.Wrapf(err, "error dropping Cockroach database %s", deets.Database) 146 } 147 148 log(logging.Info, "dropped database %s", deets.Database) 149 return nil 150 } 151 152 func (p *cockroach) URL() string { 153 c := p.ConnectionDetails 154 if c.URL != "" { 155 return c.URL 156 } 157 s := "postgres://%s:%s@%s:%s/%s?%s" 158 return fmt.Sprintf(s, c.User, c.Password, c.Host, c.Port, c.Database, c.OptionsString("")) 159 } 160 161 func (p *cockroach) urlWithoutDb() string { 162 c := p.ConnectionDetails 163 s := "postgres://%s:%s@%s:%s/?%s" 164 return fmt.Sprintf(s, c.User, c.Password, c.Host, c.Port, c.OptionsString("")) 165 } 166 167 func (p *cockroach) MigrationURL() string { 168 return p.URL() 169 } 170 171 func (p *cockroach) TranslateSQL(sql string) string { 172 defer p.mu.Unlock() 173 p.mu.Lock() 174 175 if csql, ok := p.translateCache[sql]; ok { 176 return csql 177 } 178 csql := sqlx.Rebind(sqlx.DOLLAR, sql) 179 180 p.translateCache[sql] = csql 181 return csql 182 } 183 184 func (p *cockroach) FizzTranslator() fizz.Translator { 185 return translators.NewCockroach(p.URL(), p.Details().Database) 186 } 187 188 func (p *cockroach) DumpSchema(w io.Writer) error { 189 cmd := exec.Command("cockroach", "dump", p.Details().Database, "--dump-mode=schema") 190 191 c := p.ConnectionDetails 192 if defaults.String(c.Options["sslmode"], "disable") == "disable" || strings.Contains(c.RawOptions, "sslmode=disable") { 193 cmd.Args = append(cmd.Args, "--insecure") 194 } 195 return genericDumpSchema(p.Details(), cmd, w) 196 } 197 198 func (p *cockroach) LoadSchema(r io.Reader) error { 199 return genericLoadSchema(p.ConnectionDetails, p.MigrationURL(), r) 200 } 201 202 func (p *cockroach) TruncateAll(tx *Connection) error { 203 type table struct { 204 TableName string `db:"table_name"` 205 } 206 207 tableQuery := "select table_name from information_schema.tables where table_schema = 'public' and table_type = 'BASE TABLE' and table_catalog = ?" 208 if strings.HasPrefix(p.info.version, "v1") { 209 tableQuery = "select table_name from information_schema.tables where table_schema = ?" 210 } 211 212 var tables []table 213 if err := tx.RawQuery(tableQuery, tx.Dialect.Details().Database).All(context.TODO(), &tables); err != nil { 214 return err 215 } 216 217 if len(tables) == 0 { 218 return nil 219 } 220 221 tableNames := make([]string, len(tables)) 222 for i, t := range tables { 223 tableNames[i] = t.TableName 224 //! work around for current limitation of DDL and DML at the same transaction. 225 // it should be fixed when cockroach support it or with other approach. 226 // https://www.cockroachlabs.com/docs/stable/known-limitations.html#schema-changes-within-transactions 227 if err := tx.RawQuery(fmt.Sprintf("delete from %s", t.TableName)).Exec(); err != nil { 228 return err 229 } 230 } 231 return nil 232 // TODO! 233 // return tx3.RawQuery(fmt.Sprintf("truncate %s cascade;", strings.Join(tableNames, ", "))).Exec() 234 } 235 236 func (p *cockroach) AfterOpen(c *Connection) error { 237 if err := c.RawQuery(`select version() AS "version"`).First(context.TODO(), &p.info); err != nil { 238 return err 239 } 240 if s := strings.Split(p.info.VersionString, " "); len(s) > 3 { 241 p.info.product = s[0] 242 p.info.license = s[1] 243 p.info.version = s[2] 244 p.info.buildInfo = s[3] 245 } 246 log(logging.Debug, "server: %v %v %v", p.info.product, p.info.license, p.info.version) 247 248 return nil 249 } 250 251 func newCockroach(deets *ConnectionDetails) (dialect, error) { 252 deets.Dialect = "postgres" 253 d := &cockroach{ 254 commonDialect: commonDialect{ConnectionDetails: deets}, 255 translateCache: map[string]string{}, 256 mu: sync.Mutex{}, 257 } 258 d.info.client = deets.Options["application_name"] 259 return d, nil 260 } 261 262 func finalizerCockroach(cd *ConnectionDetails) { 263 appName := filepath.Base(os.Args[0]) 264 cd.Options["application_name"] = defaults.String(cd.Options["application_name"], appName) 265 cd.Port = defaults.String(cd.Port, portCockroach) 266 }