github.com/rjgonzale/pop/v5@v5.1.3-dev/dialect_sqlite.go (about) 1 // +build sqlite 2 3 package pop 4 5 import ( 6 "fmt" 7 "io" 8 "net/url" 9 "os" 10 "os/exec" 11 "path/filepath" 12 "strings" 13 "sync" 14 "time" 15 16 "github.com/gobuffalo/fizz" 17 "github.com/gobuffalo/fizz/translators" 18 _ "github.com/mattn/go-sqlite3" // Load SQLite3 CGo driver 19 "github.com/pkg/errors" 20 21 "github.com/gobuffalo/pop/v5/columns" 22 "github.com/gobuffalo/pop/v5/internal/defaults" 23 "github.com/gobuffalo/pop/v5/logging" 24 ) 25 26 const nameSQLite3 = "sqlite3" 27 28 func init() { 29 AvailableDialects = append(AvailableDialects, nameSQLite3) 30 dialectSynonyms["sqlite"] = nameSQLite3 31 urlParser[nameSQLite3] = urlParserSQLite3 32 newConnection[nameSQLite3] = newSQLite 33 finalizer[nameSQLite3] = finalizerSQLite 34 } 35 36 var _ dialect = &sqlite{} 37 38 type sqlite struct { 39 commonDialect 40 gil *sync.Mutex 41 smGil *sync.Mutex 42 } 43 44 func (m *sqlite) Name() string { 45 return nameSQLite3 46 } 47 48 func (m *sqlite) DefaultDriver() string { 49 return nameSQLite3 50 } 51 52 func (m *sqlite) Details() *ConnectionDetails { 53 return m.ConnectionDetails 54 } 55 56 func (m *sqlite) URL() string { 57 c := m.ConnectionDetails 58 return c.Database + "?" + c.OptionsString("") 59 } 60 61 func (m *sqlite) MigrationURL() string { 62 return m.ConnectionDetails.URL 63 } 64 65 func (m *sqlite) Create(s store, model *Model, cols columns.Columns) error { 66 return m.locker(m.smGil, func() error { 67 keyType := model.PrimaryKeyType() 68 switch keyType { 69 case "int", "int64": 70 var id int64 71 w := cols.Writeable() 72 var query string 73 if len(w.Cols) > 0 { 74 query = fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s)", m.Quote(model.TableName()), w.QuotedString(m), w.SymbolizedString()) 75 } else { 76 query = fmt.Sprintf("INSERT INTO %s DEFAULT VALUES", m.Quote(model.TableName())) 77 } 78 log(logging.SQL, query) 79 res, err := s.NamedExec(query, model.Value) 80 if err != nil { 81 return err 82 } 83 id, err = res.LastInsertId() 84 if err == nil { 85 model.setID(id) 86 } 87 if err != nil { 88 return err 89 } 90 return nil 91 } 92 return errors.Wrap(genericCreate(s, model, cols, m), "sqlite create") 93 }) 94 } 95 96 func (m *sqlite) Update(s store, model *Model, cols columns.Columns) error { 97 return m.locker(m.smGil, func() error { 98 return errors.Wrap(genericUpdate(s, model, cols, m), "sqlite update") 99 }) 100 } 101 102 func (m *sqlite) Destroy(s store, model *Model) error { 103 return m.locker(m.smGil, func() error { 104 return errors.Wrap(genericDestroy(s, model, m), "sqlite destroy") 105 }) 106 } 107 108 func (m *sqlite) SelectOne(s store, model *Model, query Query) error { 109 return m.locker(m.smGil, func() error { 110 return errors.Wrap(genericSelectOne(s, model, query), "sqlite select one") 111 }) 112 } 113 114 func (m *sqlite) SelectMany(s store, models *Model, query Query) error { 115 return m.locker(m.smGil, func() error { 116 return errors.Wrap(genericSelectMany(s, models, query), "sqlite select many") 117 }) 118 } 119 120 func (m *sqlite) Lock(fn func() error) error { 121 return m.locker(m.gil, fn) 122 } 123 124 func (m *sqlite) locker(l *sync.Mutex, fn func() error) error { 125 if defaults.String(m.Details().Options["lock"], "true") == "true" { 126 defer l.Unlock() 127 l.Lock() 128 } 129 err := fn() 130 attempts := 0 131 for err != nil && err.Error() == "database is locked" && attempts <= m.Details().RetryLimit() { 132 time.Sleep(m.Details().RetrySleep()) 133 err = fn() 134 attempts++ 135 } 136 return err 137 } 138 139 func (m *sqlite) CreateDB() error { 140 _, err := os.Stat(m.ConnectionDetails.Database) 141 if err == nil { 142 return errors.Errorf("could not create SQLite database '%s'; database exists", m.ConnectionDetails.Database) 143 } 144 dir := filepath.Dir(m.ConnectionDetails.Database) 145 err = os.MkdirAll(dir, 0766) 146 if err != nil { 147 return errors.Wrapf(err, "could not create SQLite database '%s'", m.ConnectionDetails.Database) 148 } 149 _, err = os.Create(m.ConnectionDetails.Database) 150 if err != nil { 151 return errors.Wrapf(err, "could not create SQLite database '%s'", m.ConnectionDetails.Database) 152 } 153 154 log(logging.Info, "created database '%s'", m.ConnectionDetails.Database) 155 return nil 156 } 157 158 func (m *sqlite) DropDB() error { 159 err := os.Remove(m.ConnectionDetails.Database) 160 if err != nil { 161 return errors.Wrapf(err, "could not drop SQLite database %s", m.ConnectionDetails.Database) 162 } 163 log(logging.Info, "dropped database '%s'", m.ConnectionDetails.Database) 164 return nil 165 } 166 167 func (m *sqlite) TranslateSQL(sql string) string { 168 return sql 169 } 170 171 func (m *sqlite) FizzTranslator() fizz.Translator { 172 return translators.NewSQLite(m.Details().Database) 173 } 174 175 func (m *sqlite) DumpSchema(w io.Writer) error { 176 cmd := exec.Command("sqlite3", m.Details().Database, ".schema") 177 return genericDumpSchema(m.Details(), cmd, w) 178 } 179 180 func (m *sqlite) LoadSchema(r io.Reader) error { 181 cmd := exec.Command("sqlite3", m.ConnectionDetails.Database) 182 in, err := cmd.StdinPipe() 183 if err != nil { 184 return err 185 } 186 go func() { 187 defer in.Close() 188 io.Copy(in, r) 189 }() 190 log(logging.SQL, strings.Join(cmd.Args, " ")) 191 err = cmd.Start() 192 if err != nil { 193 return err 194 } 195 196 err = cmd.Wait() 197 if err != nil { 198 return err 199 } 200 201 log(logging.Info, "loaded schema for %s", m.Details().Database) 202 return nil 203 } 204 205 func (m *sqlite) TruncateAll(tx *Connection) error { 206 const tableNames = `SELECT name FROM sqlite_master WHERE type = "table"` 207 names := []struct { 208 Name string `db:"name"` 209 }{} 210 211 err := tx.RawQuery(tableNames).All(&names) 212 if err != nil { 213 return err 214 } 215 if len(names) == 0 { 216 return nil 217 } 218 stmts := []string{} 219 for _, n := range names { 220 stmts = append(stmts, fmt.Sprintf("DELETE FROM %s", m.Quote(n.Name))) 221 } 222 return tx.RawQuery(strings.Join(stmts, "; ")).Exec() 223 } 224 225 func newSQLite(deets *ConnectionDetails) (dialect, error) { 226 deets.URL = fmt.Sprintf("sqlite3://%s", deets.Database) 227 cd := &sqlite{ 228 gil: &sync.Mutex{}, 229 smGil: &sync.Mutex{}, 230 commonDialect: commonDialect{ConnectionDetails: deets}, 231 } 232 233 return cd, nil 234 } 235 236 func urlParserSQLite3(cd *ConnectionDetails) error { 237 db := strings.TrimPrefix(cd.URL, "sqlite://") 238 db = strings.TrimPrefix(db, "sqlite3://") 239 240 dbparts := strings.Split(db, "?") 241 cd.Database = dbparts[0] 242 243 if len(dbparts) != 2 { 244 return nil 245 } 246 247 q, err := url.ParseQuery(dbparts[1]) 248 if err != nil { 249 return errors.Wrapf(err, "unable to parse sqlite query") 250 } 251 252 if cd.Options == nil { // prevent panic 253 cd.Options = make(map[string]string) 254 } 255 for k := range q { 256 cd.Options[k] = q.Get(k) 257 } 258 259 return nil 260 } 261 262 func finalizerSQLite(cd *ConnectionDetails) { 263 defs := map[string]string{ 264 "_busy_timeout": "5000", 265 } 266 forced := map[string]string{ 267 "_fk": "true", 268 } 269 if cd.Options == nil { // prevent panic 270 cd.Options = make(map[string]string) 271 } 272 273 for k, v := range defs { 274 cd.Options[k] = defaults.String(cd.Options[k], v) 275 } 276 277 for k, v := range forced { 278 // respect user specified options but print warning! 279 cd.Options[k] = defaults.String(cd.Options[k], v) 280 if cd.Options[k] != v { // when user-defined option exists 281 log(logging.Warn, "IMPORTANT! '%s: %s' option is required to work properly but your current setting is '%v: %v'.", k, v, k, cd.Options[k]) 282 log(logging.Warn, "It is highly recommended to remove '%v: %v' option from your config!", k, cd.Options[k]) 283 } // or override with `cd.Options[k] = v`? 284 if cd.URL != "" && !strings.Contains(cd.URL, k+"="+v) { 285 log(logging.Warn, "IMPORTANT! '%s=%s' option is required to work properly. Please add it to the database URL in the config!", k, v) 286 } // or fix user specified url? 287 } 288 }