14130607387

Committed 28 Mar 2025 01:59PM UTC coverage: 69.03% (-0.006%) from 69.036%

Build # 14130607387

Build Type

push

github

Committed by

web-flow

Commit Message

Merge pull request #9647 from bhandras/sqldb-migration-base-version

sqldb: establish a base DB version even if it's not yet tracked

Run Details

48 of 75 new or added lines in 3 files covered. (64.0%)

77 existing lines in 18 files now uncovered.

133408 of 193262 relevant lines covered (69.03%)

22159.27 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

87.04

/sqldb/sqlite.go

//go:build !js && !(windows && (arm || 386)) && !(linux && (ppc64 || mips || mipsle || mips64))

package sqldb

import (
        "context"
        "database/sql"
        "fmt"
        "net/url"
        "path/filepath"
        "testing"

        sqlite_migrate "github.com/golang-migrate/migrate/v4/database/sqlite"
        "github.com/lightningnetwork/lnd/sqldb/sqlc"
        "github.com/stretchr/testify/require"
        _ "modernc.org/sqlite" // Register relevant drivers.
)

const (
        // sqliteOptionPrefix is the string prefix sqlite uses to set various
        // options. This is used in the following format:
        //   * sqliteOptionPrefix || option_name = option_value.
        sqliteOptionPrefix = "_pragma"

        // sqliteTxLockImmediate is a dsn option used to ensure that write
        // transactions are started immediately.
        sqliteTxLockImmediate = "_txlock=immediate"
)

var (
        // sqliteSchemaReplacements maps schema strings to their SQLite
        // compatible replacements. Currently, no replacements are needed as our
        // SQL schema definition files are designed for SQLite compatibility.
        sqliteSchemaReplacements = map[string]string{}

        // Make sure SqliteStore implements the MigrationExecutor interface.
        _ MigrationExecutor = (*SqliteStore)(nil)

        // Make sure SqliteStore implements the DB interface.
        _ DB = (*SqliteStore)(nil)
)

// SqliteStore is a database store implementation that uses a sqlite backend.
type SqliteStore struct {
        cfg *SqliteConfig

        *BaseDB
}

// NewSqliteStore attempts to open a new sqlite database based on the passed
// config.
func NewSqliteStore(cfg *SqliteConfig, dbPath string) (*SqliteStore, error) {
        // The set of pragma options are accepted using query options. For now
        // we only want to ensure that foreign key constraints are properly
        // enforced.
        pragmaOptions := []struct {
                name  string
                value string
        }{
                {
                        name:  "foreign_keys",
                        value: "on",
                },
                {
                        name:  "journal_mode",
                        value: "WAL",
                },
                {
                        name:  "busy_timeout",
                        value: "5000",
                },
                {
                        // With the WAL mode, this ensures that we also do an
                        // extra WAL sync after each transaction. The normal
                        // sync mode skips this and gives better performance,
                        // but risks durability.
                        name:  "synchronous",
                        value: "full",
                },
                {
                        // This is used to ensure proper durability for users
                        // running on Mac OS. It uses the correct fsync system
                        // call to ensure items are fully flushed to disk.
                        name:  "fullfsync",
                        value: "true",
                },
        }
        sqliteOptions := make(url.Values)
        for _, option := range pragmaOptions {
                sqliteOptions.Add(
                        sqliteOptionPrefix,
                        fmt.Sprintf("%v=%v", option.name, option.value),
                )
        }

        // Construct the DSN which is just the database file name, appended
        // with the series of pragma options as a query URL string. For more
        // details on the formatting here, see the modernc.org/sqlite docs:
        // https://pkg.go.dev/modernc.org/sqlite#Driver.Open.
        dsn := fmt.Sprintf(
                "%v?%v&%v", dbPath, sqliteOptions.Encode(),
                sqliteTxLockImmediate,
        )
        db, err := sql.Open("sqlite", dsn)
        if err != nil {
                return nil, err
        }

        // Create the migration tracker table before starting migrations to
        // ensure it can be used to track migration progress. Note that a
        // corresponding SQLC migration also creates this table, making this
        // operation a no-op in that context. Its purpose is to ensure
        // compatibility with SQLC query generation.
        migrationTrackerSQL := `
        CREATE TABLE IF NOT EXISTS migration_tracker (
                version INTEGER UNIQUE NOT NULL,
                migration_time TIMESTAMP NOT NULL
        );`

        _, err = db.Exec(migrationTrackerSQL)
        if err != nil {
                return nil, fmt.Errorf("error creating migration tracker: %w",
                        err)
        }

        db.SetMaxOpenConns(defaultMaxConns)
        db.SetMaxIdleConns(defaultMaxConns)
        db.SetConnMaxLifetime(connIdleLifetime)
        queries := sqlc.New(db)

        s := &SqliteStore{
                cfg: cfg,
                BaseDB: &BaseDB{
                        DB:      db,
                        Queries: queries,
                },
        }

        return s, nil
}

// GetBaseDB returns the underlying BaseDB instance for the SQLite store.
// It is a trivial helper method to comply with the sqldb.DB interface.
func (s *SqliteStore) GetBaseDB() *BaseDB {
        return s.BaseDB
}

// ApplyAllMigrations applies both the SQLC and custom in-code migrations to the
// SQLite database.
func (s *SqliteStore) ApplyAllMigrations(ctx context.Context,
        migrations []MigrationConfig) error {

        // Execute migrations unless configured to skip them.
        if s.cfg.SkipMigrations {
                return nil
        }

        return ApplyMigrations(ctx, s.BaseDB, s, migrations)
}

func errSqliteMigration(err error) error {
        return fmt.Errorf("error creating sqlite migration: %w", err)
}

// ExecuteMigrations runs migrations for the sqlite database, depending on the
// target given, either all migrations or up to a given version.
func (s *SqliteStore) ExecuteMigrations(target MigrationTarget) error {
        driver, err := sqlite_migrate.WithInstance(
                s.DB, &sqlite_migrate.Config{},
        )
        if err != nil {
                return errSqliteMigration(err)
        }

        // Populate the database with our set of schemas based on our embedded
        // in-memory file system.
        sqliteFS := newReplacerFS(sqlSchemas, sqliteSchemaReplacements)
        return applyMigrations(
                sqliteFS, driver, "sqlc/migrations", "sqlite", target,
        )
}

// GetSchemaVersion returns the current schema version of the SQLite database.
func (s *SqliteStore) GetSchemaVersion() (int, bool, error) {
        driver, err := sqlite_migrate.WithInstance(
                s.DB, &sqlite_migrate.Config{},
        )
        if err != nil {
                return 0, false, errSqliteMigration(err)
        }

        version, dirty, err := driver.Version()
        if err != nil {
                return 0, dirty, err
        }

        return version, dirty, nil
}

// SetSchemaVersion sets the schema version of the SQLite database.
//
// NOTE: This alters the internal database schema tracker. USE WITH CAUTION!!!
func (s *SqliteStore) SetSchemaVersion(version int, dirty bool) error {
        driver, err := sqlite_migrate.WithInstance(
                s.DB, &sqlite_migrate.Config{},
        )
        if err != nil {
                return errSqliteMigration(err)
        }

        return driver.SetVersion(version, dirty)
}

// NewTestSqliteDB is a helper function that creates an SQLite database for
// testing.
func NewTestSqliteDB(t *testing.T) *SqliteStore {
        t.Helper()

        t.Logf("Creating new SQLite DB for testing")

        // TODO(roasbeef): if we pass :memory: for the file name, then we get
        // an in mem version to speed up tests
        dbFileName := filepath.Join(t.TempDir(), "tmp.db")
        sqlDB, err := NewSqliteStore(&SqliteConfig{
                SkipMigrations: false,
        }, dbFileName)
        require.NoError(t, err)

        require.NoError(t, sqlDB.ApplyAllMigrations(
                context.Background(), GetMigrations()),
        )

        t.Cleanup(func() {
                require.NoError(t, sqlDB.DB.Close())
        })

        return sqlDB
}

// NewTestSqliteDBWithVersion is a helper function that creates an SQLite
// database for testing and migrates it to the given version.
func NewTestSqliteDBWithVersion(t *testing.T, version uint) *SqliteStore {
        t.Helper()

        t.Logf("Creating new SQLite DB for testing, migrating to version %d",
                version)

        // TODO(roasbeef): if we pass :memory: for the file name, then we get
        // an in mem version to speed up tests
        dbFileName := filepath.Join(t.TempDir(), "tmp.db")
        sqlDB, err := NewSqliteStore(&SqliteConfig{
                SkipMigrations: true,
        }, dbFileName)
        require.NoError(t, err)

        err = sqlDB.ExecuteMigrations(TargetVersion(version))
        require.NoError(t, err)

        t.Cleanup(func() {
                require.NoError(t, sqlDB.DB.Close())
        })

        return sqlDB
}

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	// SqliteStore is a database store implementation that uses a sqlite backend.
44	type SqliteStore struct {
45	cfg *SqliteConfig
46
47	*BaseDB
48	}
49
50	// NewSqliteStore attempts to open a new sqlite database based on the passed
51	// config.
52	func NewSqliteStore(cfg SqliteConfig, dbPath string) (SqliteStore, error) {	280✔
53	// The set of pragma options are accepted using query options. For now	280✔
54	// we only want to ensure that foreign key constraints are properly	280✔
55	// enforced.	280✔
56	pragmaOptions := []struct {	280✔
57	name string	280✔
58	value string	280✔
59	}{	280✔
60	{	280✔
61	name: "foreign_keys",	280✔
62	value: "on",	280✔
63	},	280✔
64	{	280✔
65	name: "journal_mode",	280✔
66	value: "WAL",	280✔
67	},	280✔
68	{	280✔
69	name: "busy_timeout",	280✔
70	value: "5000",	280✔
71	},	280✔
72	{	280✔
73	// With the WAL mode, this ensures that we also do an	280✔
74	// extra WAL sync after each transaction. The normal	280✔
75	// sync mode skips this and gives better performance,	280✔
76	// but risks durability.	280✔
77	name: "synchronous",	280✔
78	value: "full",	280✔
79	},	280✔
80	{	280✔
81	// This is used to ensure proper durability for users	280✔
82	// running on Mac OS. It uses the correct fsync system	280✔
83	// call to ensure items are fully flushed to disk.	280✔
84	name: "fullfsync",	280✔
85	value: "true",	280✔
86	},	280✔
87	}	280✔
88	sqliteOptions := make(url.Values)	280✔
89	for _, option := range pragmaOptions {	1,680✔
90	sqliteOptions.Add(	1,400✔
91	sqliteOptionPrefix,	1,400✔
92	fmt.Sprintf("%v=%v", option.name, option.value),	1,400✔
93	)	1,400✔
94	}	1,400✔
95
96	// Construct the DSN which is just the database file name, appended
97	// with the series of pragma options as a query URL string. For more
98	// details on the formatting here, see the modernc.org/sqlite docs:
99	// https://pkg.go.dev/modernc.org/sqlite#Driver.Open.
100	dsn := fmt.Sprintf(	280✔
101	"%v?%v&%v", dbPath, sqliteOptions.Encode(),	280✔
102	sqliteTxLockImmediate,	280✔
103	)	280✔
104	db, err := sql.Open("sqlite", dsn)	280✔
105	if err != nil {	280✔
106	return nil, err	×
107	}	×
108
109	// Create the migration tracker table before starting migrations to
110	// ensure it can be used to track migration progress. Note that a
111	// corresponding SQLC migration also creates this table, making this
112	// operation a no-op in that context. Its purpose is to ensure
113	// compatibility with SQLC query generation.
114	migrationTrackerSQL := `	280✔
115	CREATE TABLE IF NOT EXISTS migration_tracker (	280✔
116	version INTEGER UNIQUE NOT NULL,	280✔
117	migration_time TIMESTAMP NOT NULL	280✔
118	);`	280✔
119		280✔
120	_, err = db.Exec(migrationTrackerSQL)	280✔
121	if err != nil {	280✔
122	return nil, fmt.Errorf("error creating migration tracker: %w",	×
123	err)	×
124	}	×
125
126	db.SetMaxOpenConns(defaultMaxConns)	280✔
127	db.SetMaxIdleConns(defaultMaxConns)	280✔
128	db.SetConnMaxLifetime(connIdleLifetime)	280✔
129	queries := sqlc.New(db)	280✔
130		280✔
131	s := &SqliteStore{	280✔
132	cfg: cfg,	280✔
133	BaseDB: &BaseDB{	280✔
134	DB: db,	280✔
135	Queries: queries,	280✔
136	},	280✔
137	}	280✔
138		280✔
139	return s, nil	280✔
140	}
141
142	// GetBaseDB returns the underlying BaseDB instance for the SQLite store.
143	// It is a trivial helper method to comply with the sqldb.DB interface.
144	func (s SqliteStore) GetBaseDB() BaseDB {	×
145	return s.BaseDB	×
146	}	×
147
148	// ApplyAllMigrations applies both the SQLC and custom in-code migrations to the
149	// SQLite database.
150	func (s *SqliteStore) ApplyAllMigrations(ctx context.Context,
151	migrations []MigrationConfig) error {	273✔
152		273✔
153	// Execute migrations unless configured to skip them.	273✔
154	if s.cfg.SkipMigrations {	273✔
155	return nil	×
156	}	×
157
158	return ApplyMigrations(ctx, s.BaseDB, s, migrations)	273✔
159	}
160
NEW 161	func errSqliteMigration(err error) error {	×
NEW 162	return fmt.Errorf("error creating sqlite migration: %w", err)	×
NEW 163	}	×
164
165	// ExecuteMigrations runs migrations for the sqlite database, depending on the
166	// target given, either all migrations or up to a given version.
167	func (s *SqliteStore) ExecuteMigrations(target MigrationTarget) error {	1,834✔
168	driver, err := sqlite_migrate.WithInstance(	1,834✔
169	s.DB, &sqlite_migrate.Config{},	1,834✔
170	)	1,834✔
171	if err != nil {	1,834✔
NEW 172	return errSqliteMigration(err)	×
173	}	×
174
175	// Populate the database with our set of schemas based on our embedded
176	// in-memory file system.
177	sqliteFS := newReplacerFS(sqlSchemas, sqliteSchemaReplacements)	1,834✔
178	return applyMigrations(	1,834✔
179	sqliteFS, driver, "sqlc/migrations", "sqlite", target,	1,834✔
180	)	1,834✔
181	}
182
183	// GetSchemaVersion returns the current schema version of the SQLite database.
184	func (s *SqliteStore) GetSchemaVersion() (int, bool, error) {	272✔
185	driver, err := sqlite_migrate.WithInstance(	272✔
186	s.DB, &sqlite_migrate.Config{},	272✔
187	)	272✔
188	if err != nil {	272✔
NEW 189	return 0, false, errSqliteMigration(err)	×
NEW 190	}	×
191
192	version, dirty, err := driver.Version()	272✔
193	if err != nil {	272✔
NEW 194	return 0, dirty, err	×
NEW 195	}	×
196
197	return version, dirty, nil	272✔
198	}
199
200	// SetSchemaVersion sets the schema version of the SQLite database.
201	//
202	// NOTE: This alters the internal database schema tracker. USE WITH CAUTION!!!
203	func (s *SqliteStore) SetSchemaVersion(version int, dirty bool) error {	5✔
204	driver, err := sqlite_migrate.WithInstance(	5✔
205	s.DB, &sqlite_migrate.Config{},	5✔
206	)	5✔
207	if err != nil {	5✔
NEW 208	return errSqliteMigration(err)	×
NEW 209	}	×
210
211	return driver.SetVersion(version, dirty)	5✔
212	}
213
214	// NewTestSqliteDB is a helper function that creates an SQLite database for
215	// testing.
216	func NewTestSqliteDB(t testing.T) SqliteStore {	256✔
217	t.Helper()	256✔
218		256✔
219	t.Logf("Creating new SQLite DB for testing")	256✔
220		256✔
221	// TODO(roasbeef): if we pass :memory: for the file name, then we get	256✔
222	// an in mem version to speed up tests	256✔
223	dbFileName := filepath.Join(t.TempDir(), "tmp.db")	256✔
224	sqlDB, err := NewSqliteStore(&SqliteConfig{	256✔
225	SkipMigrations: false,	256✔
226	}, dbFileName)	256✔
227	require.NoError(t, err)	256✔
228		256✔
229	require.NoError(t, sqlDB.ApplyAllMigrations(	256✔
230	context.Background(), GetMigrations()),	256✔
231	)	256✔
232		256✔
233	t.Cleanup(func() {	512✔
234	require.NoError(t, sqlDB.DB.Close())	256✔
235	})	256✔
236
237	return sqlDB	256✔
238	}
239
240	// NewTestSqliteDBWithVersion is a helper function that creates an SQLite
241	// database for testing and migrates it to the given version.
242	func NewTestSqliteDBWithVersion(t testing.T, version uint) SqliteStore {	1✔
243	t.Helper()	1✔
244		1✔
245	t.Logf("Creating new SQLite DB for testing, migrating to version %d",	1✔
246	version)	1✔
247		1✔
248	// TODO(roasbeef): if we pass :memory: for the file name, then we get	1✔
249	// an in mem version to speed up tests	1✔
250	dbFileName := filepath.Join(t.TempDir(), "tmp.db")	1✔
251	sqlDB, err := NewSqliteStore(&SqliteConfig{	1✔
252	SkipMigrations: true,	1✔
253	}, dbFileName)	1✔
254	require.NoError(t, err)	1✔
255		1✔
256	err = sqlDB.ExecuteMigrations(TargetVersion(version))	1✔
257	require.NoError(t, err)	1✔
258		1✔
259	t.Cleanup(func() {	2✔
260	require.NoError(t, sqlDB.DB.Close())	1✔
261	})	1✔
262
263	return sqlDB	1✔
264	}

lightningnetwork / lnd / 14130607387

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous