db

package
v1.1.1 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Nov 7, 2019 License: MIT Imports: 14 Imported by: 0

Documentation

Overview

Package sqlittle/db provides low level access to SQLite (version 3) database files.

See ../README.md for general information about SQLittle, and see ../LOWLEVEL.md for information about this package.

Index

Examples

Constants

View Source 
const (

	// CachePages is the number of pages to keep in memory. Default size per
	// page is 4K (1K on older databases).
	CachePages = 100
)

Variables

View Source 
var (
	// Various error messages returned when the database is corrupted
	ErrInvalidMagic    = errors.New("invalid magic number")
	ErrInvalidPageSize = errors.New("invalid page size")
	ErrReservedSpace   = errors.New("unsupported database (encrypted?)")
	ErrCorrupted       = errors.New("database corrupted")
	ErrInvalidDef      = errors.New("invalid object definition")
	ErrRecursion       = errors.New("tree is too deep")

	// Various error messages returned when the database uses features sqlittle
	// doesn't support.
	ErrIncompatible = errors.New("incompatible database version")
	ErrEncoding     = errors.New("unsupported encoding")
	// Database is in WAL journal mode, which we don't support. You need to
	// convert the database to journal mode.
	ErrWAL = errors.New("WAL journal mode is unsupported")
	// There is a stale `-journal` file present with an unfinished transaction.
	// Open the database in sqlite3 to repair the database.
	ErrHotJournal = errors.New("crashed transaction present")

	ErrNoSuchTable = errors.New("no such table")
	ErrNoSuchIndex = errors.New("no such index")
)
View Source 
var CollateFuncs = map[string]func(string, string) int{
	"binary": strings.Compare,
	"rtrim": func(a, b string) int {
		return strings.Compare(
			strings.TrimRight(a, " \t\r\n"),
			strings.TrimRight(b, " \t\r\n"),
		)
	},
	"nocase": func(a, b string) int {
		lc := func(r rune) rune {
			if r >= 'A' && r <= 'Z' {
				return rune(strings.ToLower(string(r))[0])
			}
			return r
		}
		return strings.Compare(
			strings.Map(lc, a),
			strings.Map(lc, b),
		)
	},
}

available collate functions

View Source 
var DefaultCollate = "binary"

Functions

func Equals 

func Equals(key Key, r Record) bool

func Fuzz 

func Fuzz(data []byte) int

Fuzz routine for the go-fuzz test system 

func Search(key Key, r Record) bool

True if r is eq or bigger than key

Types

type Database 

type Database struct {
	// contains filtered or unexported fields
}

func OpenFile 

func OpenFile(f string) (*Database, error)

OpenFile opens a .sqlite file. This is the main entry point. Use database.Close() when done.

func (*Database) Close 

func (db *Database) Close() error

Close the database.

func (*Database) Index 

func (db *Database) Index(name string) (*Index, error)

Index opens the named index. Will return ErrNoSuchIndex when the index isn't there (or isn't an index). Index pointer is always valid if err == nil.

Indexes work for normal and WITHOUT ROWID tables. The columns the callback gets depends on the type of table.

func (*Database) Indexes 

func (db *Database) Indexes() ([]string, error)

Indexes lists all index names.

func (*Database) Info 

func (db *Database) Info() (string, error)

Info gives some debugging info about the open database

func (*Database) NonRowidTable 

func (db *Database) NonRowidTable(name string) (*Index, error)

NonRowidTable open a `WITHOUT ROWID` table. It's implemented with an Index.

func (*Database) RLock 

func (db *Database) RLock() error

Lock database for reading. Blocks. Don't nest RLock() calls.

func (*Database) RUnlock 

func (db *Database) RUnlock() error

Unlock a read lock. Use a single RUnlock() for every RLock().

func (*Database) Schema 

func (db *Database) Schema(table string) (*Schema, error)

Schema gives the definition of a table and all associated indexes.

Example 
db, err := OpenFile("../testdata/words.sqlite")
if err != nil {
	panic(err)
}
defer db.Close()

schema, err := db.Schema("words")
if err != nil {
	panic(err)
}
fmt.Printf("columns:\n")
for _, c := range schema.Columns {
	fmt.Printf(" - %q is a %s\n", c.Column, c.Type)
}
fmt.Printf("available indexes:\n")
for _, ind := range schema.Indexes {
	fmt.Printf(" - %q (", ind.Index)
	for i, c := range ind.Columns {
		if i > 0 {
			fmt.Printf(", ")
		}
		fmt.Print(c.Column)
	}
	fmt.Print(")\n")
}

Output:

columns:
 - "word" is a varchar
 - "length" is a int
available indexes:
 - "words_index_1" (word)
 - "words_index_2" (length, word)

func (*Database) Table 

func (db *Database) Table(name string) (*Table, error)

Table opens the named table. Will return ErrNoSuchTable when the table isn't there (or isn't a table). Table pointer is always valid if err == nil. See also NonRowidTable() for `WITHOUT ROWID` tables.

func (*Database) Tables 

func (db *Database) Tables() ([]string, error)

Tables lists all table names. Also sqlite internal ones.

type Index 

type Index struct {
	// contains filtered or unexported fields
}

Index is either an index, or the table for a non-rowid table

func (*Index) Def 

func (t *Index) Def() (*sql.CreateIndexStmt, error)

Def returns the index definition.

func (*Index) Scan 

func (in *Index) Scan(cb RecordCB) error

Scan calls cb() for every row in the index. These will be called in the index order. The callback gets the record as stored in the index. For indexes on a non-WITHOUT ROWID table the last value will be the rowid (see ChompRowid()). For a WITHOUT ROWID table the columns depend on your table structure. If the callback returns true (done) the scan will be stopped.

Example 
// This code will iterate over all words in alphabetical order.
// The `words` table has: CREATE INDEX words_index_1 ON words (word)
db, err := OpenFile("../testdata/words.sqlite")
if err != nil {
	panic(err)
}
defer db.Close()

index, err := db.Index("words_index_1")
if err != nil {
	panic(err)
}
i := 0
if err := index.Scan(
	func(rec Record) bool {
		fmt.Printf("row %d: %s\n", rec[1].(int64), rec[0].(string))
		i++
		return i >= 10
	},
); err != nil {
	panic(err)
}

Output:

row 329: Adams
row 123: Ahmadinejad
row 870: Alabaman
row 685: Algonquin
row 619: Amy
row 700: Andersen
row 900: Annette's
row 423: Antipas's
row 891: Arizonan
row 945: Artaxerxes's

func (*Index) ScanEq 

func (in *Index) ScanEq(key Key, cb RecordCB) error

Scan all record matching key

Example 
// Find records matching a key.
db, err := OpenFile("../testdata/words.sqlite")
if err != nil {
	panic(err)
}
defer db.Close()

index, err := db.Index("words_index_1")
if err != nil {
	panic(err)
}
// match only wombat
if err := index.ScanEq(
	Key{KeyCol{V: "wombat"}},
	func(rec Record) bool {
		fmt.Printf("%v\n", rec) // word, rowid
		return false
	},
); err != nil {
	panic(err)
}

Output:

[wombat 159]

func (*Index) ScanMin 

func (in *Index) ScanMin(from Key, cb RecordCB) error

ScanMin calls cb() for every row in the index, starting from the first record where key is true.

If the callback returns true (done) the scan will be stopped. All comments from Index.Scan are valid here as well.

Example 
// This will iterate over all words in alphabetical order, starting from
// the first record >= the given record.
// The `words` table has: CREATE INDEX words_index_1 ON words (word)
db, err := OpenFile("../testdata/words.sqlite")
if err != nil {
	panic(err)
}
defer db.Close()

index, err := db.Index("words_index_1")
if err != nil {
	panic(err)
}
if err := index.ScanMin(
	Key{KeyCol{V: "wombat"}},
	func(rec Record) bool {
		word := rec[0].(string)
		if word >= "y" {
			return true
		}
		fmt.Printf("%s\n", word)
		return false
	},
); err != nil {
	panic(err)
}

Output:

wombat
workbook
world's
worsens
wristwatch's
writhing
wusses

func (*Index) ScanRange 

func (in *Index) ScanRange(from, to Key, cb RecordCB) error

Find all records where from(index) is true, and to(index) is false.

You'll have to compensate for DESC columns.

Example 
// Find records matching a range
db, err := OpenFile("../testdata/words.sqlite")
if err != nil {
	panic(err)
}
defer db.Close()

index, err := db.Index("words_index_1")
if err != nil {
	panic(err)
}
// scan from wombat inclusive to wusses exclusive
if err := index.ScanRange(
	Key{KeyCol{V: "wombat"}},
	Key{KeyCol{V: "wusses"}},
	func(rec Record) bool {
		fmt.Printf("%v\n", rec) // word, rowid
		return false
	},
); err != nil {
	panic(err)
}

Output:

[wombat 159]
[workbook 975]
[world's 114]
[worsens 770]
[wristwatch's 491]
[writhing 343]

type IndexColumn 

type IndexColumn struct {
	Column     string
	Expression string
	Collate    string
	SortOrder  sql.SortOrder
}

type Key 

type Key []KeyCol

type KeyCol 

type KeyCol struct {
	V       interface{}
	Collate string // either empty or names a valid CollateFuncs key
	Desc    bool
}

type Record 

type Record []interface{}

Record is a row in the database. It can only have fields of these types: nil, int64, float64, string, []byte

func ChompRowid 

func ChompRowid(rec Record) (int64, Record, error)

Removes the rowid column from an index value (that's the last value from a Record). Returns: rowid, record, error

type RecordCB 

type RecordCB func(Record) bool

RecordCB is passed to Index.Scan(), Index.ScanMin(), and Table.ScanWithoutRowid. It should return true when the scan should be stopped.

For index scans: The callback gets the raw values as stored in the index. For a normal index the last value is the rowid value (see ChompRowid()). For a WITHOUT ROWID it depends on the table which rows there are.

type Schema 

type Schema struct {
	Table        string
	WithoutRowid bool
	Columns      []TableColumn
	Indexes      []SchemaIndex
	PK           []IndexColumn // only set for non-rowid tables
	PrimaryKey   string        // only set for rowid tables: name of the index
	RowidPK      bool          // only set for rowid tables: whether we have a 'integer primary key' column, which means there is no separate index for the primary key
}

func (*Schema) Column 

func (st *Schema) Column(name string) int

Returns the index of the named column, or -1.

func (*Schema) NamedIndex 

func (st *Schema) NamedIndex(name string) *SchemaIndex

NamedIndex returns the index with the name (case insensitive)

type SchemaIndex 

type SchemaIndex struct {
	Index   string
	Columns []IndexColumn
}

func (*SchemaIndex) Column 

func (si *SchemaIndex) Column(name string) int

Returns the index of the named column, or -1.

type Table 

type Table struct {
	// contains filtered or unexported fields
}

func (*Table) Def 

func (t *Table) Def() (*sql.CreateTableStmt, error)

Def returns the table definition. Not everything SQLite supports is supported (yet). See Database.Schema() for a friendlier interface.

Example 
db, err := OpenFile("../testdata/words.sqlite")
if err != nil {
	panic(err)
}
defer db.Close()

index, err := db.Index("words_index_2")
if err != nil {
	panic(err)
}
ind, err := index.Def()
if err != nil {
	panic(err)
}
for _, c := range ind.IndexedColumns {
	fmt.Printf("indexed column: %q (sortorder %s)\n", c.Column, c.SortOrder)
}

Output:

indexed column: "length" (sortorder ASC)
indexed column: "word" (sortorder ASC)

func (*Table) Rowid 

func (t *Table) Rowid(rowid int64) (Record, error)

Rowid finds a single row by rowid. Will return nil if it isn't found. The rowid is an internal id, but if you have an `integer primary key` column that should be the same. See Table.Scan comments about the Record

Example 
db, err := OpenFile("../testdata/single.sqlite")
if err != nil {
	panic(err)
}
defer db.Close()

table, err := db.Table("hello")
if err != nil {
	panic(err)
}
row, err := table.Rowid(2)
if err != nil {
	panic(err)
}
fmt.Printf("row: %s\n", row[0].(string))

Output:

row: universe
Example (Nonrowid) 
db, err := OpenFile("../testdata/withoutrowid.sqlite")
if err != nil {
	panic(err)
}
defer db.Close()

table, err := db.NonRowidTable("words")
if err != nil {
	panic(err)
}
if err := table.ScanEq(
	Key{KeyCol{V: "awesomely"}},
	func(r Record) bool {
		fmt.Printf("row: %v\n", r)
		return false
	},
); err != nil {
	panic(err)
}

Output:

row: [awesomely 9]

func (*Table) Scan 

func (t *Table) Scan(cb TableScanCB) error

Scan calls cb() for every row in the table. Will be called in 'database order'. The record is given as sqlite stores it; this means:

  • float64 columns might be stored as int64
  • after an alter table which adds columns a row might miss the new columns
  • an "integer primary key" column will be always be nil, and the rowid is the value

If the callback returns true (done) the scan will be stopped.

Example 
db, err := OpenFile("../testdata/single.sqlite")
if err != nil {
	panic(err)
}
defer db.Close()

table, err := db.Table("hello")
if err != nil {
	panic(err)
}
if err := table.Scan(
	func(rowid int64, rec Record) bool {
		fmt.Printf("row %d: %s\n", rowid, rec[0].(string))
		return false // we want all the rows
	},
); err != nil {
	panic(err)
}

Output:

row 1: world
row 2: universe
row 3: town
Example (Nonrowid) 
db, err := OpenFile("../testdata/withoutrowid.sqlite")
if err != nil {
	panic(err)
}
defer db.Close()

table, err := db.NonRowidTable("words")
if err != nil {
	panic(err)
}
i := 10
if err := table.Scan(
	func(rec Record) bool {
		fmt.Printf("row %s\n", rec[0].(string))
		i--
		return i <= 0
	},
); err != nil {
	panic(err)
}

Output:

row Adams
row Ahmadinejad
row Alabaman
row Algonquin
row Amy
row Andersen
row Annette's
row Antipas's
row Arizonan
row Artaxerxes's

type TableColumn 

type TableColumn struct {
	Column  string
	Type    string // as given in the CREATE TABLE
	Null    bool
	Default interface{}
	Collate string
	Rowid   bool
}

type TableScanCB 

type TableScanCB func(int64, Record) bool

TableScanCB is the callback for Table.Scan(). It gets the rowid (usually an internal number), and the data of the row. It should return true when the scan should be terminated.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.