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