1  //go:build !js && !(windows && (arm || 386)) && !(linux && (ppc64 || mips || mipsle || mips64))
  2  
  3  package sqldb
  4  
  5  import (
  6  	"context"
  7  	"database/sql"
  8  	"fmt"
  9  	"net/url"
 10  	"path/filepath"
 11  	"testing"
 12  
 13  	sqlite_migrate "github.com/golang-migrate/migrate/v4/database/sqlite"
 14  	"github.com/lightningnetwork/lnd/sqldb/sqlc"
 15  	"github.com/stretchr/testify/require"
 16  	_ "modernc.org/sqlite" // Register relevant drivers.
 17  )
 18  
 19  const (
 20  	// sqliteOptionPrefix is the string prefix sqlite uses to set various
 21  	// options. This is used in the following format:
 22  	//   * sqliteOptionPrefix || option_name = option_value.
 23  	sqliteOptionPrefix = "_pragma"
 24  
 25  	// sqliteTxLockImmediate is a dsn option used to ensure that write
 26  	// transactions are started immediately.
 27  	sqliteTxLockImmediate = "_txlock=immediate"
 28  )
 29  
 30  var (
 31  	// sqliteSchemaReplacements maps schema strings to their SQLite
 32  	// compatible replacements. Currently, no replacements are needed as our
 33  	// SQL schema definition files are designed for SQLite compatibility.
 34  	sqliteSchemaReplacements = map[string]string{}
 35  
 36  	// Make sure SqliteStore implements the MigrationExecutor interface.
 37  	_ MigrationExecutor = (*SqliteStore)(nil)
 38  
 39  	// Make sure SqliteStore implements the DB interface.
 40  	_ DB = (*SqliteStore)(nil)
 41  )
 42  
 43  // pragmaOption holds a key-value pair for a SQLite pragma setting.
 44  type pragmaOption struct {
 45  	name  string
 46  	value string
 47  }
 48  
 49  // SqliteStore is a database store implementation that uses a sqlite backend.
 50  type SqliteStore struct {
 51  	cfg *SqliteConfig
 52  
 53  	*BaseDB
 54  }
 55  
 56  // NewSqliteStore attempts to open a new sqlite database based on the passed
 57  // config.
 58  func NewSqliteStore(cfg *SqliteConfig, dbPath string) (*SqliteStore, error) {
 59  	// The set of pragma options are accepted using query options. For now
 60  	// we only want to ensure that foreign key constraints are properly
 61  	// enforced.
 62  	pragmaOptions := []pragmaOption{
 63  		{
 64  			name:  "foreign_keys",
 65  			value: "on",
 66  		},
 67  		{
 68  			name:  "journal_mode",
 69  			value: "WAL",
 70  		},
 71  		{
 72  			name:  "busy_timeout",
 73  			value: fmt.Sprintf("%d", cfg.busyTimeoutMs()),
 74  		},
 75  		{
 76  			// With the WAL mode, this ensures that we also do an
 77  			// extra WAL sync after each transaction. The normal
 78  			// sync mode skips this and gives better performance,
 79  			// but risks durability.
 80  			name:  "synchronous",
 81  			value: "full",
 82  		},
 83  		{
 84  			// This is used to ensure proper durability for users
 85  			// running on Mac OS. It uses the correct fsync system
 86  			// call to ensure items are fully flushed to disk.
 87  			name:  "fullfsync",
 88  			value: "true",
 89  		},
 90  		{
 91  			name:  "auto_vacuum",
 92  			value: "incremental",
 93  		},
 94  	}
 95  	sqliteOptions := make(url.Values)
 96  	for _, option := range pragmaOptions {
 97  		sqliteOptions.Add(
 98  			sqliteOptionPrefix,
 99  			fmt.Sprintf("%v=%v", option.name, option.value),
100  		)
101  	}
102  
103  	// Then we add any user specified pragma options. Note that these can
104  	// be of the form: "key=value", "key(N)" or "key".
105  	for _, option := range cfg.PragmaOptions {
106  		sqliteOptions.Add(sqliteOptionPrefix, option)
107  	}
108  
109  	// Construct the DSN which is just the database file name, appended
110  	// with the series of pragma options as a query URL string. For more
111  	// details on the formatting here, see the modernc.org/sqlite docs:
112  	// https://pkg.go.dev/modernc.org/sqlite#Driver.Open.
113  	dsn := fmt.Sprintf(
114  		"%v?%v&%v", dbPath, sqliteOptions.Encode(),
115  		sqliteTxLockImmediate,
116  	)
117  	db, err := sql.Open("sqlite", dsn)
118  	if err != nil {
119  		return nil, err
120  	}
121  
122  	// Create the migration tracker table before starting migrations to
123  	// ensure it can be used to track migration progress. Note that a
124  	// corresponding SQLC migration also creates this table, making this
125  	// operation a no-op in that context. Its purpose is to ensure
126  	// compatibility with SQLC query generation.
127  	migrationTrackerSQL := `
128  	CREATE TABLE IF NOT EXISTS migration_tracker (
129  		version INTEGER UNIQUE NOT NULL,
130  		migration_time TIMESTAMP NOT NULL
131  	);`
132  
133  	_, err = db.Exec(migrationTrackerSQL)
134  	if err != nil {
135  		return nil, fmt.Errorf("error creating migration tracker: %w",
136  			err)
137  	}
138  
139  	db.SetMaxOpenConns(cfg.MaxConns())
140  	db.SetMaxIdleConns(cfg.MaxConns())
141  	db.SetConnMaxLifetime(connIdleLifetime)
142  	queries := sqlc.New(db)
143  
144  	s := &SqliteStore{
145  		cfg: cfg,
146  		BaseDB: &BaseDB{
147  			DB:      db,
148  			Queries: queries,
149  		},
150  	}
151  
152  	return s, nil
153  }
154  
155  // GetBaseDB returns the underlying BaseDB instance for the SQLite store.
156  // It is a trivial helper method to comply with the sqldb.DB interface.
157  func (s *SqliteStore) GetBaseDB() *BaseDB {
158  	return s.BaseDB
159  }
160  
161  // ApplyAllMigrations applies both the SQLC and custom in-code migrations to the
162  // SQLite database.
163  func (s *SqliteStore) ApplyAllMigrations(ctx context.Context,
164  	migrations []MigrationConfig) error {
165  
166  	// Execute migrations unless configured to skip them.
167  	if s.cfg.SkipMigrations {
168  		return nil
169  	}
170  
171  	return ApplyMigrations(ctx, s.BaseDB, s, migrations)
172  }
173  
174  func errSqliteMigration(err error) error {
175  	return fmt.Errorf("error creating sqlite migration: %w", err)
176  }
177  
178  // ExecuteMigrations runs migrations for the sqlite database, depending on the
179  // target given, either all migrations or up to a given version.
180  func (s *SqliteStore) ExecuteMigrations(target MigrationTarget) error {
181  	driver, err := sqlite_migrate.WithInstance(
182  		s.DB, &sqlite_migrate.Config{},
183  	)
184  	if err != nil {
185  		return errSqliteMigration(err)
186  	}
187  
188  	// Populate the database with our set of schemas based on our embedded
189  	// in-memory file system.
190  	sqliteFS := newReplacerFS(sqlSchemas, sqliteSchemaReplacements)
191  	return applyMigrations(
192  		sqliteFS, driver, "sqlc/migrations", "sqlite", target,
193  	)
194  }
195  
196  // GetSchemaVersion returns the current schema version of the SQLite database.
197  func (s *SqliteStore) GetSchemaVersion() (int, bool, error) {
198  	driver, err := sqlite_migrate.WithInstance(
199  		s.DB, &sqlite_migrate.Config{},
200  	)
201  	if err != nil {
202  		return 0, false, errSqliteMigration(err)
203  	}
204  
205  	version, dirty, err := driver.Version()
206  	if err != nil {
207  		return 0, dirty, err
208  	}
209  
210  	return version, dirty, nil
211  }
212  
213  // SetSchemaVersion sets the schema version of the SQLite database.
214  //
215  // NOTE: This alters the internal database schema tracker. USE WITH CAUTION!!!
216  func (s *SqliteStore) SetSchemaVersion(version int, dirty bool) error {
217  	driver, err := sqlite_migrate.WithInstance(
218  		s.DB, &sqlite_migrate.Config{},
219  	)
220  	if err != nil {
221  		return errSqliteMigration(err)
222  	}
223  
224  	return driver.SetVersion(version, dirty)
225  }
226  
227  // NewTestSqliteDB is a helper function that creates an SQLite database for
228  // testing.
229  func NewTestSqliteDB(t testing.TB) *SqliteStore {
230  	t.Helper()
231  
232  	t.Logf("Creating new SQLite DB for testing")
233  
234  	// TODO(roasbeef): if we pass :memory: for the file name, then we get
235  	// an in mem version to speed up tests
236  	dbFileName := filepath.Join(t.TempDir(), "tmp.db")
237  	sqlDB, err := NewSqliteStore(&SqliteConfig{
238  		SkipMigrations: false,
239  	}, dbFileName)
240  	require.NoError(t, err)
241  
242  	require.NoError(t, sqlDB.ApplyAllMigrations(
243  		context.Background(), GetMigrations()),
244  	)
245  
246  	t.Cleanup(func() {
247  		require.NoError(t, sqlDB.DB.Close())
248  	})
249  
250  	return sqlDB
251  }
252  
253  // NewTestSqliteDBWithVersion is a helper function that creates an SQLite
254  // database for testing and migrates it to the given version.
255  func NewTestSqliteDBWithVersion(t *testing.T, version uint) *SqliteStore {
256  	t.Helper()
257  
258  	t.Logf("Creating new SQLite DB for testing, migrating to version %d",
259  		version)
260  
261  	// TODO(roasbeef): if we pass :memory: for the file name, then we get
262  	// an in mem version to speed up tests
263  	dbFileName := filepath.Join(t.TempDir(), "tmp.db")
264  	sqlDB, err := NewSqliteStore(&SqliteConfig{
265  		SkipMigrations: true,
266  	}, dbFileName)
267  	require.NoError(t, err)
268  
269  	err = sqlDB.ExecuteMigrations(TargetVersion(version))
270  	require.NoError(t, err)
271  
272  	t.Cleanup(func() {
273  		require.NoError(t, sqlDB.DB.Close())
274  	})
275  
276  	return sqlDB
277  }