README
¶
Package SQLittle provides pure Go, read-only, access to SQLite (version 3) database files.
What
SQLittle reads SQLite3 tables and indexes. It iterates over tables, and can search efficiently using indexes. SQLittle will deal with all SQLite storage quirks, but otherwise it doesn't try to be smart; if you want to use an index you have to give the name of the index.
There is no support for SQL, and if you want to do the most efficient joins possible you'll have to use the low level code.
Based on https://sqlite.org/fileformat2.html and some SQLite source code reading.
Why
This whole thing is mostly for fun. The normal SQLite libraries are perfectly great, and there is no real need for this. However, since this library is pure Go cross-compilation is much easier. Given the constraints a valid use-case would for example be storing app configuration in read-only sqlite files.
Docs
https://godoc.org/github.com/alicebob/sqlittle for the go doc and examples.
See LOWLEVEL.md about the low level reader. See CODE.md for an overview how the code is structured.
Features
Things SQLittle can do:
- table scan in row order; table scan in index order; simple searches with use of (partial) indexes
- works on both rowid and non-rowid (`WITHOUT ROWID`) tables
- files can be used concurrently with sqlite (compatible locks)
- behaves nicely on corrupted database files (no panics)
- detects corrupt journal files
- hides all SQLite low level storage details
- DESC indexes are handled automatically
- Collate functions are used automatically
- indexes with expression (either in columns or as a `WHERE`) are (partially) supported
- Scan() to most Go datatypes, including `time.Time`
- works on Linux, Mac OS, and Windows
- has an experimental database/sql driver, see below
Things SQLittle should do:
- add a helper to find indexes. That would be especially useful for the `sqlite_autoindex_...` indexes
- optimize loading when all requested columns are available in the index
- expose the locking so you can do bigger read transactions
Things SQLittle can not do:
- read-only
- only supports UTF8 strings
- no joins
- WAL files are not supported
- indexes are used for sorting, but there is no on-the-fly sorting
Locks
SQLittle has a read-lock on the file during the whole execution of the select-like functions. It's safe to update the database using SQLite while the file is opened in SQLittle.
Status
The current level of abstraction is likely the final one (that is: deal with reading single tables; don't even try joins or SQL or query planning), but the API might still change.
Examples
Basic SELECT
Code:
{
db, err := sqlittle.Open("./testdata/music.sqlite")
if err != nil {
panic(err)
}
defer db.Close()
db.Select("tracks", func(r sqlittle.Row) {
var (
name string
length int
)
_ = r.Scan(&name, &length)
fmt.Printf("%s: %d seconds\n", name, length)
}, "name", "length")
}
Output:
Drive My Car: 145 seconds
Norwegian Wood: 121 seconds
You Wont See Me: 198 seconds
Come Together: 259 seconds
Something: 182 seconds
Maxwells Silver Hammer: 207 seconds
SELECT by primary key
Code:
{
db, err := sqlittle.Open("./testdata/music.sqlite")
if err != nil {
panic(err)
}
defer db.Close()
db.PKSelect("tracks", sqlittle.Key{4}, func(r sqlittle.Row) {
name, _ := r.ScanString()
fmt.Printf("%s\n", name)
}, "name")
}
Output:
Come Together
Driver
The ./driver/ package implements an experimental driver for database/sql. It currently supports very basic SELECT statements only. Issues, PRs, and feature requests are (as always) welcome.
Idea
The driver exposes sqlittle as a database/sql driver. It uses the main sqlittle package to implement a basic query executor, which can be used as any other Go database driver.
It will never support full SQL, but certain things we can do without implementing an actual database system. For example ORDER BY could be supported, as long as you have an index which we can use for it. LIMIT should be easy. But GROUP BY needs an actual database system, so that's out.
Supported SQL
These statements are supported. Anything else is not:
SELECT * FROM yourtableSELECT col1, col2 FROM yourtable
example
import (
"database/sql"
_ "github.com/alicebob/sqlittle/driver"
)
func Albums() {
c, err := sql.Open("sqlittle", "../testdata/music.sqlite")
rows, err := c.Query(`SELECT * FROM albums`)
// normal rows.Next() stuff
// Be sure to check rows.Err()!
}
&c.
make fuzz uses go-fuzz
See sqlite2go for another approach to pure Go SQLite
Documentation
¶
Overview ¶
Package SQLittle provides pure Go, read-only, access to SQLite (version 3) database files.
What ¶
SQLittle reads SQLite3 tables and indexes. It iterates over tables, and can search efficiently using indexes. SQLittle will deal with all SQLite storage quirks, but otherwise it doesn't try to be smart; if you want to use an index you have to give the name of the index.
There is no support for SQL, and if you want to do the most efficient joins possible you'll have to use the low level code.
Based on https://sqlite.org/fileformat2.html and some SQLite source code reading.
Why ¶
This whole thing is mostly for fun. The normal SQLite libraries are perfectly great, and there is no real need for this. However, since this library is pure Go cross-compilation is much easier. Given the constraints a valid use-case would for example be storing app configuration in read-only sqlite files.
Docs ¶
https://godoc.org/github.com/alicebob/sqlittle for the go doc and examples.
See [LOWLEVEL.md](LOWLEVEL.md) about the low level reader. See [CODE.md](CODE.md) for an overview how the code is structured.
Features ¶
Things SQLittle can do:
- table scan in row order; table scan in index order; simple searches with use of (partial) indexes
- works on both rowid and non-rowid (`WITHOUT ROWID`) tables
- files can be used concurrently with sqlite (compatible locks)
- behaves nicely on corrupted database files (no panics)
- detects corrupt journal files
- hides all SQLite low level storage details
- DESC indexes are handled automatically
- Collate functions are used automatically
- indexes with expression (either in columns or as a `WHERE`) are (partially) supported
- Scan() to most Go datatypes, including `time.Time`
- Works on Linux, Mac OS, and Windows
Things SQLittle should do:
- add a helper to find indexes. That would be especially useful for the `sqlite_autoindex_...` indexes
- optimize loading when all requested columns are available in the index
- expose the locking so you can do bigger read transactions
Things SQLittle can not do:
- read-only
- only supports UTF8 strings
- no joins
- WAL files are not supported
- indexes are used for sorting, but there is no on-the-fly sorting
Locks ¶
SQLittle has a read-lock on the file during the whole execution of the select-like functions. It's safe to update the database using SQLite while the file is opened in SQLittle.
Status ¶
The current level of abstraction is likely the final one (that is: deal with reading single tables; don't even try joins or SQL or query planning), but the API might still change.
Index ¶
- type DB
- func (db *DB) Close() error
- func (db *DB) Columns(table string) ([]string, error)
- func (db *DB) IndexedSelect(table, index string, cb RowCB, columns ...string) error
- func (db *DB) IndexedSelectEq(table, index string, key Key, cb RowCB, columns ...string) error
- func (db *DB) PKSelect(table string, key Key, cb RowCB, columns ...string) error
- func (db *DB) Select(table string, cb RowCB, columns ...string) error
- func (db *DB) SelectDone(table string, cb RowDoneCB, columns ...string) error
- func (db *DB) SelectRowid(table string, rowid int64, columns ...string) (Row, error)
- type Key
- type Row
- type RowCB
- type RowDoneCB
Examples ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
This section is empty.
Types ¶
type DB ¶
type DB struct {
// contains filtered or unexported fields
}
func (*DB) IndexedSelect ¶
Select all rows from the given table via the index. The order will be the index order (every `DESC` field will iterate in descending order).
`columns` are the name of the columns you want, their values always come from the data table. Index columns can have expressions, but that doesn't do anything (except maybe change the order).
If the index has a WHERE expression only the rows matching that expression will be matched.
Example ¶
SELECT in index order
package main
import (
"fmt"
"github.com/alicebob/sqlittle"
)
func main() {
db, err := sqlittle.Open("./testdata/music.sqlite")
if err != nil {
panic(err)
}
defer db.Close()
db.IndexedSelect(
"tracks",
"tracks_length",
func(r sqlittle.Row) {
var (
name string
length int
)
_ = r.Scan(&name, &length)
fmt.Printf("%s: %d seconds\n", name, length)
},
"name",
"length",
)
}
Output: Norwegian Wood: 121 seconds Drive My Car: 145 seconds Something: 182 seconds You Wont See Me: 198 seconds Maxwells Silver Hammer: 207 seconds Come Together: 259 seconds
func (*DB) IndexedSelectEq ¶
Select all rows matching key from the given table via the index. The order will be the index order (every `DESC` field will iterate in descending order). Any collate function defined in the schema will be applied automatically.
`key` is compared against the index columns. `key` can have fewer columns than the index, in which case only the given columns need to compare equal. If the index column is an expression then `key` is compared against the value stored in the index.
For example, given a table:
1: "aap", 1 2: "aap", 13 3: "noot", 12
matches:
Key{"aap", 1} will match rows 1
Key{"aap"} will match rows 1 and 2
Key{"noot", 1} will not match any row
Key{} will match every row
If the index has a WHERE expression only the rows matching that expression will be matched.
Example ¶
package main
import (
"fmt"
"github.com/alicebob/sqlittle"
)
func main() {
db, err := sqlittle.Open("./testdata/music.sqlite")
if err != nil {
panic(err)
}
defer db.Close()
db.IndexedSelectEq(
"tracks",
"tracks_length",
sqlittle.Key{198},
func(r sqlittle.Row) {
var (
name string
length int
)
_ = r.Scan(&name, &length)
fmt.Printf("%s: %d seconds\n", name, length)
},
"name",
"length",
)
}
Output: You Wont See Me: 198 seconds
func (*DB) PKSelect ¶
Select rows via a Primary Key lookup.
`key` is compared against the columns of the primary key. `key` can have fewer columns than the primary key has, in which case only the given columns need to compare equal (see `IndexedSelectEq` for an example). Any collate function defined in the schema will be applied automatically.
PKSelect is especially efficient for non-rowid tables (`WITHOUT ROWID`), and for rowid tables which have a single 'integer primary key' column.
Example ¶
SELECT a primary key
package main
import (
"fmt"
"github.com/alicebob/sqlittle"
)
func main() {
db, err := sqlittle.Open("./testdata/music.sqlite")
if err != nil {
panic(err)
}
defer db.Close()
db.PKSelect(
"tracks",
sqlittle.Key{4},
func(r sqlittle.Row) {
name, _ := r.ScanString()
fmt.Printf("%s\n", name)
},
"name",
)
}
Output: Come Together
func (*DB) Select ¶
Select the columns from every row from the given table. Order is the rowid order for rowid tables, and the ordered primary key for non-rowid tables (`WITHOUT ROWID`).
For rowid tables the special values "rowid", "oid", and "_rowid_" will load the rowid (unless there is a column with that name).
Example ¶
Basic SELECT
package main
import (
"fmt"
"github.com/alicebob/sqlittle"
)
func main() {
db, err := sqlittle.Open("./testdata/music.sqlite")
if err != nil {
panic(err)
}
defer db.Close()
db.Select(
"tracks",
func(r sqlittle.Row) {
var (
name string
length int
)
_ = r.Scan(&name, &length)
fmt.Printf("%s: %d seconds\n", name, length)
},
"name",
"length",
)
}
Output: Drive My Car: 145 seconds Norwegian Wood: 121 seconds You Wont See Me: 198 seconds Come Together: 259 seconds Something: 182 seconds Maxwells Silver Hammer: 207 seconds
func (*DB) SelectDone ¶ added in v1.5.0
SelectDone is the same as Select(), but the callback can stop the iterating. Useful for implementing things like `LIMIT`.
type Key ¶
type Key []interface{}
Key is used to find a record.
It accepts most Go datatypes, but they will be converted to the set SQLite supports: nil, int64, float64, string, []byte
type Row ¶
A row with values as stored in the database. Use Row.Scan() to process these values.
Values are allowed to point to bytes in the database and hence are only valid during a DB transaction.
func (Row) Scan ¶
Scan converts a row with database values to the Go values you want. Supported Go types:
- bool
- float64
- int
- int32
- int64
- string
- []byte
- time.Time
- nil (skips the column)
Conversions are usually stricter than in SQLite:
- string to number does not accept trailing letters such as in "123test"
- string to bool needs to convert to a number cleanly
- numbers are stored as either int64 or float64, and are converted with the normal Go conversions.
Values are a copy of the database bytes; they stay valid even after closing the database.
func (Row) ScanString ¶
ScanString is a shortcut for row.Scan(&string)
func (Row) ScanStringString ¶
ScanStringString is a shortcut for row.Scan(&string, &string)
func (Row) ScanStrings ¶
ScanStrings is a shortcut to scan all columns as string
Since everything can be converted to strings nothing can possibly go wrong.
Source Files
¶
Directories
¶
| Path | Synopsis |
|---|---|
|
Package sqlittle/db provides low level access to SQLite (version 3) database files.
|
Package sqlittle/db provides low level access to SQLite (version 3) database files. |
|
Package sql is a parser for the subset of SQL needed for SQLite's `CREATE TABLE` and `CREATE INDEX` statements.
|
Package sql is a parser for the subset of SQL needed for SQLite's `CREATE TABLE` and `CREATE INDEX` statements. |