sqlite

package module
v0.0.0-...-df040c9 Latest Latest
Warning

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

 Go to latest Published: Mar 27, 2023 License: MIT Imports: 8 Imported by: 0

README

sqlite-toy

简介

sqlite-toy 是一个以研究为目的的，基于内存的，完全原生实现的，有限支持 SQL 查询的关系型数据库。

主要的目标是为了向数据库爱好者展示一个关系型数据库的基本原理和关键设计。因此，为了便于理解，采取了很多取巧但不是很严谨的设计，代码量控制在 2000 行以内。

特性列表

纯 Golang 实现，不依赖任何第三方包。

存储引擎

基于 B+Tree 的数据检索结构。

SQL Parser
  1. Tokenizer 基于 text/scanner 实现。
  2. 支持简单的 SELECT、INSERT、UPDATE、DELETE、CREARE TABLE 语法。
    1. SELECT、UPDATE、DELETE 支持数值类型的 WHERE。
    2. 支持 LIMIT，但暂不支持 ORDER BY。
  3. 距离实现 SQL-2011 标准有十万八千里远。
执行计划 Planner

基于火山模型（Volcano Model）的 Select 实现。

实现的局限

  1. 有限支持 SQL 语法。
  2. 以研究为目的，没有严格的单元测试。
  3. Tokenizer 由于是基于 Golang 语言本身的一个取巧实现，对于一些字符串里的特殊字符支持会出现问题，可以通过加 " 解决。

使用

func main() {
	db := sqlite.NewDB()
	err := db.Exec(`
	CREATE TABLE user (
		email      VARCHAR(255)   NOT NULL  DEFAULT "default@gmail.com",
		username   VARCHAR(16)    NOT NULL,
		id         INTEGER        NOT NULL,
		PRIMARY KEY (id)
	);`)
	if err != nil {
		log.Fatalln(err)
	}

	for i := 1; i != 30; i++ {
		sql := fmt.Sprintf(`INSERT INTO user (id, username, email) VALUES (%d, "userName-%d", "User-%d@gmail.com")`, i, i, i)
		if err = db.Exec(sql); err != nil {
			log.Fatalln(err)
		}
	}

	result, err := db.Query(`SELECT email, id, username FROM user WHERE id > 3 LIMIT 10`)
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println(result)

	err = db.Exec(`UPDATE user SET username = "newName222", email = "NewEmail111" WHERE username = "userName-27";`)
	if err != nil {
		log.Fatalln(err)
	}

	err = db.Exec(`DELETE FROM user WHERE username = "newName222" AND email = "NewEmail111";`)
	if err != nil {
		log.Fatalln(err)
	}

	err = db.Exec(`DELETE FROM user WHERE id < 25;`)
	if err != nil {
		log.Fatalln(err)
	}

	result, err = db.Query(`SELECT email, id, username FROM user WHERE id > 26`)
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println(result)
}

Documentation

Index

Constants

View Source 
const (
	UNSUPPORTED = "N/A"
	SELECT      = "SELECT"
	INSERT      = "INSERT"
	UPDATE      = "UPDATE"
	DELETE      = "DELETE"

	CREATE = "CREATE"
	TABLE  = "TABLE"

	FROM   = "FROM"
	WHERE  = "WHERE"
	LIMIT  = "LIMIT"
	INTO   = "INTO"
	VALUES = "VALUES"
	Set    = "SET"

	ASTERISK = "*"
	NULL     = "NULL"
	DEFAULT  = "DEFAULT"
	PRIMARY  = "PRIMARY"
	KEY      = "KEY"

	NOT = "not"
	AND = "and"
	OR  = "or"
)

SQL type tokens

Variables

View Source 
var (
	IsNotInteger         = fmt.Errorf("is not inetger")
	IsSignedIntegerError = fmt.Errorf("is not signed integer")
	IsNotString          = fmt.Errorf("is not string")
	IsNotBoolError       = fmt.Errorf("is not bool")
	HasNoPrimaryKeyError = fmt.Errorf("has no primary key")
	NotEmptyError        = fmt.Errorf("not empty")
	VarCharTooLongError  = fmt.Errorf("varchar too long")
	OptionLimitError     = fmt.Errorf("option limit error")

	DuplicateKeyError = fmt.Errorf("duplicate key")
	HasNotColumnError = fmt.Errorf("has no such column")
	TableError        = fmt.Errorf("has no such table")
	SyntaxError       = fmt.Errorf("syntax error")
)

Functions

func BoolFormatter 

func BoolFormatter(data string) interface{}

func Compose 

func Compose(fns ...func(data string) error) func(data string) error

func IntegerFormatter 

func IntegerFormatter(data string) interface{}

func IsBool 

func IsBool(data string) error

func IsInteger 

func IsInteger(data string) error

func IsSignedInteger 

func IsSignedInteger(data string) error

func IsString 

func IsString(data string) error

func NotEmpty 

func NotEmpty(data string) error

func OptionLimit 

func OptionLimit[T int | string](data T, options []T) error

func StringFormatter 

func StringFormatter(data string) interface{}

func TrimQuotes 

func TrimQuotes(data string) string

func VarcharTooLong 

func VarcharTooLong(data string, maxLen int) error

Types

type BPItem 

type BPItem struct {
	Key int64
	Val interface{}
}

type BPNode 

type BPNode struct {
	ID     int
	MaxKey int64 // 子树的最大关键字

	// non-leaf node only
	Children []*BPNode // 结点的子树

	// leaf node only
	Items []*BPItem // 叶子结点的数据记录
	Next  *BPNode   // 叶子结点中指向下一个叶子结点，用于实现叶子结点链表
}

func (*BPNode) IsLeaf 

func (node *BPNode) IsLeaf() bool

type BPTree 

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

func NewBPTree 

func NewBPTree(width int, getNewID func() int) *BPTree

func (*BPTree) Get 

func (t *BPTree) Get(key int64) interface{}

func (*BPTree) GetAllItems 

func (t *BPTree) GetAllItems() chan *BPItem

func (*BPTree) GetData 

func (t *BPTree) GetData() map[int64]interface{}

func (*BPTree) GetFarLeftLeaf 

func (t *BPTree) GetFarLeftLeaf() *BPNode

func (*BPTree) Remove 

func (t *BPTree) Remove(key int64)

func (*BPTree) Set 

func (t *BPTree) Set(key int64, value interface{}) (update bool)

type ConstraintError 

type ConstraintError struct {
	Table  string
	Row    []string
	Column string
	Err    error
}

type CreateTableAST 

type CreateTableAST struct {
	Table      string
	PrimaryKey string
	Columns    []string
	Type       []string
	NotNull    []bool
	Default    []string
}

type DB 

type DB struct {
	Tables map[string]*Table
}

func NewDB 

func NewDB() *DB

func (*DB) AddTable 

func (db *DB) AddTable(table *Table)

func (*DB) CreateTable 

func (db *DB) CreateTable(parser *Parser, sql string) error

func (*DB) Delete 

func (db *DB) Delete(parser *Parser, sql string) error

func (*DB) Exec 

func (db *DB) Exec(sql string) error

func (*DB) GetTable 

func (db *DB) GetTable(tableName string) *Table

func (*DB) Insert 

func (db *DB) Insert(parser *Parser, sql string) error

func (*DB) NewTable 

func (db *DB) NewTable(ast *CreateTableAST) (*Table, error)

func (*DB) Query 

func (db *DB) Query(sql string) ([]*BPItem, error)

func (*DB) Update 

func (db *DB) Update(parser *Parser, sql string) error

type DeleteAST 

type DeleteAST struct {
	Table string
	Where []string
	Limit int64
}

type InsertAST 

type InsertAST struct {
	Table   string
	Columns []string
	Values  [][]string
}

type Parser 

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

func (*Parser) GetSQLType 

func (p *Parser) GetSQLType(sql string) StatementType

func (*Parser) ParseCreateTable 

func (p *Parser) ParseCreateTable(sql string) (ast *CreateTableAST, err error)

func (*Parser) ParseDelete 

func (p *Parser) ParseDelete(sql string) (ast *DeleteAST, err error)

func (*Parser) ParseInsert 

func (p *Parser) ParseInsert(insert string) (ast *InsertAST, err error)

ParseInsert can parse a simple INSERT statement, eg. 

 	INSERT INTO table_name VALUES (value1, value2, …)
	or
	INSERT INTO table_name(column1, column2, …) VALUES (value1, value2, …)

func (*Parser) ParseSelect 

func (p *Parser) ParseSelect(sql string) (ast *SelectAST, err error)

ParseSelect is a simple select statement parser. It's just a demo of SELECT statement parser skeleton. Currently, the most complex SQL supported here is something like: 

SELECT * FROM foo WHERE id < 3 LIMIT 1;

Even SQL-92 standard is far more complex. For a production ready SQL parser, see: https://github.com/auxten/postgresql-parser

func (*Parser) ParseUpdate 

func (p *Parser) ParseUpdate(sql string) (ast *UpdateAST, err error)

func (*Parser) ScanSet 

func (p *Parser) ScanSet(s *scanner.Scanner) ([]string, []string, string, error)

func (*Parser) ScanTable 

func (p *Parser) ScanTable(s *scanner.Scanner) (
	PrimaryKey string, Columns []string, Type []string, NotNull []bool, Default []string, err error)

func (*Parser) ScanWhere 

func (p *Parser) ScanWhere(s *scanner.Scanner) ([]string, string, error)

func (*Parser) ScanWhereAndLimit 

func (p *Parser) ScanWhereAndLimit(s *scanner.Scanner, lastToken string) (where []string, limit int64, err error)

type Plan 

type Plan struct {
	UnFilteredPipe chan *BPItem
	FilteredPipe   chan *BPItem
	LimitedPipe    chan *BPItem
	ErrorsPipe     chan error
	Stop           chan struct{}
	// contains filtered or unexported fields
}

func NewPlan 

func NewPlan(table *Table) (p *Plan)

func (*Plan) Delete 

func (p *Plan) Delete(ast *DeleteAST) error

func (*Plan) Insert 

func (p *Plan) Insert(dataset map[int64][]interface{}) error

func (*Plan) Select 

func (p *Plan) Select(ast *SelectAST) (ret []*BPItem, err error)

func (*Plan) Update 

func (p *Plan) Update(ast *UpdateAST) error

type SelectAST 

type SelectAST struct {
	Table    string
	Projects []string
	Where    []string
	Limit    int64
}

type StatementType 

type StatementType string

type Table 

type Table struct {
	Name         string
	PrimaryKey   string
	Columns      []string
	Constraint   map[string]func(data string) error
	Formatter    map[string]func(data string) interface{}
	DefaultValue []interface{}
	Indies       map[string]*BPTree // multi indies, maybe
}

Table get table from .frm file

func (*Table) CheckDeleteConstraint 

func (t *Table) CheckDeleteConstraint(ast *DeleteAST) *ConstraintError

func (*Table) CheckInsertConstraint 

func (t *Table) CheckInsertConstraint(ast *InsertAST) *ConstraintError

func (*Table) CheckLimit 

func (t *Table) CheckLimit(limit int64) *ConstraintError

func (*Table) CheckSelectConstraint 

func (t *Table) CheckSelectConstraint(ast *SelectAST) *ConstraintError

func (*Table) CheckTable 

func (t *Table) CheckTable(table string) *ConstraintError

func (*Table) CheckUpdateConstraint 

func (t *Table) CheckUpdateConstraint(ast *UpdateAST) *ConstraintError

func (*Table) CheckWhere 

func (t *Table) CheckWhere(where []string) *ConstraintError

func (*Table) FilterCols 

func (t *Table) FilterCols(item *BPItem, cols []string) *BPItem

func (*Table) Format 

func (t *Table) Format(ast *InsertAST) map[int64][]interface{}

map[primaryKeyValue]rowData NOTE: 简单实现,限死prmaryKey必须是数字类型

func (*Table) GetClusterIndex 

func (t *Table) GetClusterIndex() *BPTree

type UpdateAST 

type UpdateAST struct {
	Table    string
	Columns  []string
	NewValue []string
	Where    []string
	Limit    int64
}

Source Files

View all Source files

Directories

Path Synopsis

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.