sqle

package module
v1.0.1 Latest Latest
Warning

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

 Go to latest Published: Jul 31, 2024 License: Apache-2.0 Imports: 26 Imported by: 0

README

A MySQL compatible database engine written in pure Go

go-mysql-server is a data-source agnostic SQL engine and server which runs queries on data sources you provide, using the MySQL dialect and wire protocol. A simple in-memory database implementation is included, and you can query any data source you want by implementing your own backend.

Dolt, a SQL database with Git-style versioning, is the main production database implementation of this package. Check out that project for reference a implementation. Or, hop into the Dolt discord here if you want to talk to the core developers behind go-mysql-server and Dolt.

Compatibility

With the exception of specific limitations (see below), go-mysql-server is a drop-in replacement for MySQL. Any client library, tool, query, SQL syntax, SQL function, etc. that works with MySQL should also work with go-mysql-server. If you find a gap in functionality, please file an issue.

For full MySQL compatibility documentation, see the Dolt docs on this topic.

Scope of this project

  • SQL server and engine to query your data sources.
  • In-memory database backend implementation suitable for use in tests.
  • Interfaces you can use to implement new backends to query your own data sources.
  • With a few caveats and using a full database implementation, a drop-in MySQL database replacement.

go-mysql-server has two primary uses case:

  1. Stand-in for MySQL in a golang test environment, using the built-in memory database implementation.

  2. Providing access to arbitrary data sources with SQL queries by implementing a handful of interfaces. The most complete real-world implementation is Dolt.

Installation

Add go-mysql-server as a dependency to your project. In the directory with the go.mod file, run:

go get github.com/dolthub/go-mysql-server@latest

Using the in-memory test server

The in-memory test server can replace a real MySQL server in tests. Start the server using the code in the _example directory, also reproduced below.

package main

import (
	"context"
	"fmt"
	"time"

	"github.com/dolthub/vitess/go/vt/proto/query"

	sqle "github.com/dolthub/go-mysql-server"
	"github.com/dolthub/go-mysql-server/memory"
	"github.com/dolthub/go-mysql-server/server"
	"github.com/dolthub/go-mysql-server/sql"
	"github.com/dolthub/go-mysql-server/sql/types"
)

// This is an example of how to implement a MySQL server.
// After running the example, you may connect to it using the following:
//
// > mysql --host=localhost --port=3306 --user=root mydb --execute="SELECT * FROM mytable;"
// +----------+-------------------+-------------------------------+----------------------------+
// | name     | email             | phone_numbers                 | created_at                 |
// +----------+-------------------+-------------------------------+----------------------------+
// | Jane Deo | janedeo@gmail.com | ["556-565-566","777-777-777"] | 2022-11-01 12:00:00.000001 |
// | Jane Doe | jane@doe.com      | []                            | 2022-11-01 12:00:00.000001 |
// | John Doe | john@doe.com      | ["555-555-555"]               | 2022-11-01 12:00:00.000001 |
// | John Doe | johnalt@doe.com   | []                            | 2022-11-01 12:00:00.000001 |
// +----------+-------------------+-------------------------------+----------------------------+
//
// The included MySQL client is used in this example, however any MySQL-compatible client will work.

var (
	dbName    = "mydb"
	tableName = "mytable"
	address   = "localhost"
	port      = 3306
)

func main() {
	pro := createTestDatabase()
	engine := sqle.NewDefault(pro)

	session := memory.NewSession(sql.NewBaseSession(), pro)
	ctx := sql.NewContext(context.Background(), sql.WithSession(session))
	ctx.SetCurrentDatabase("test")

	// This variable may be found in the "users_example.go" file. Please refer to that file for a walkthrough on how to
	// set up the "mysql" database to allow user creation and user checking when establishing connections. This is set
	// to false for this example, but feel free to play around with it and see how it works.
	if enableUsers {
		if err := enableUserAccounts(ctx, engine); err != nil {
			panic(err)
		}
	}

	config := server.Config{
		Protocol: "tcp",
		Address:  fmt.Sprintf("%s:%d", address, port),
	}
	s, err := server.NewServer(config, engine, memory.NewSessionBuilder(pro), nil)
	if err != nil {
		panic(err)
	}
	if err = s.Start(); err != nil {
		panic(err)
	}
}

func createTestDatabase() *memory.DbProvider {
	db := memory.NewDatabase(dbName)
	db.BaseDatabase.EnablePrimaryKeyIndexes()

	pro := memory.NewDBProvider(db)
	session := memory.NewSession(sql.NewBaseSession(), pro)
	ctx := sql.NewContext(context.Background(), sql.WithSession(session))

	table := memory.NewTable(db, tableName, sql.NewPrimaryKeySchema(sql.Schema{
		{Name: "name", Type: types.Text, Nullable: false, Source: tableName, PrimaryKey: true},
		{Name: "email", Type: types.Text, Nullable: false, Source: tableName, PrimaryKey: true},
		{Name: "phone_numbers", Type: types.JSON, Nullable: false, Source: tableName},
		{Name: "created_at", Type: types.MustCreateDatetimeType(query.Type_DATETIME, 6), Nullable: false, Source: tableName},
	}), db.GetForeignKeyCollection())
	db.AddTable(tableName, table)

	creationTime := time.Unix(0, 1667304000000001000).UTC()
	_ = table.Insert(ctx, sql.NewRow("Jane Deo", "janedeo@gmail.com", types.MustJSON(`["556-565-566", "777-777-777"]`), creationTime))
	_ = table.Insert(ctx, sql.NewRow("Jane Doe", "jane@doe.com", types.MustJSON(`[]`), creationTime))
	_ = table.Insert(ctx, sql.NewRow("John Doe", "john@doe.com", types.MustJSON(`["555-555-555"]`), creationTime))
	_ = table.Insert(ctx, sql.NewRow("John Doe", "johnalt@doe.com", types.MustJSON(`[]`), creationTime))

	return pro
}

This example populates the database by creating memory.Database and memory.Table objects via golang code, but you can also populate it by issuing CREATE DATABASE, CREATE TABLE, etc. statements to the server once it's running.

Once the server is running, connect with any MySQL client, including the golang MySQL connector and the mysql shell.

> mysql --host=localhost --port=3306 --user=root mydb --execute="SELECT * FROM mytable;"
+----------+-------------------+-------------------------------+----------------------------+
| name     | email             | phone_numbers                 | created_at                 |
+----------+-------------------+-------------------------------+----------------------------+
| Jane Deo | janedeo@gmail.com | ["556-565-566","777-777-777"] | 2022-11-01 12:00:00.000001 |
| Jane Doe | jane@doe.com      | []                            | 2022-11-01 12:00:00.000001 |
| John Doe | john@doe.com      | ["555-555-555"]               | 2022-11-01 12:00:00.000001 |
| John Doe | johnalt@doe.com   | []                            | 2022-11-01 12:00:00.000001 |
+----------+-------------------+-------------------------------+----------------------------+

Limitations of the in-memory database implementation

The in-memory database implementation included with this package is intended for use in tests. It has specific limitations that we know of:

Custom backend implementations

You can create your own backend to query your own data sources by implementing some interfaces. For detailed instructions, see the backend guide.

Technical documentation for contributors and backend developers

  • Architecture is an overview of the various packages of the project and how they fit together.
  • Contribution guide for new contributors, including instructions for how to get your PR merged.

Powered by go-mysql-server

Are you building a database backend using go-mysql-server? We would like to hear from you and include you in this list.

Security Policy

go-mysql-server's security policy is maintained in this repository. Please follow the disclosure instructions there. Please do not initially report security issues in this repository's public GitHub issues.

Acknowledgements

go-mysql-server was originally developed by the {source-d} organzation, and this repository was originally forked from src-d. We want to thank the entire {source-d} development team for their work on this project, especially Miguel Molina (@erizocosmico) and Juanjo Álvarez Martinez (@juanjux).

License

Apache License 2.0, see LICENSE

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var ExperimentalGMS bool

Functions

This section is empty.

Types

type ColumnWithRawDefault 

type ColumnWithRawDefault struct {
	SqlColumn *sql.Column
	Default   string
}

type Config 

type Config struct {
	// VersionPostfix to display with the `VERSION()` UDF.
	VersionPostfix string
	// IsReadOnly sets the engine to disallow modification queries.
	IsReadOnly     bool
	IsServerLocked bool
	// IncludeRootAccount adds the root account (with no password) to the list of accounts, and also enables
	// authentication.
	IncludeRootAccount bool
	// TemporaryUsers adds any users that should be included when the engine is created. By default, authentication is
	// disabled, and including any users here will enable authentication. All users in this list will have full access.
	// This field is only temporary, and will be removed as development on users and authentication continues.
	TemporaryUsers []TemporaryUser
}

Config for the Engine.

type Engine 

type Engine struct {
	Analyzer          *analyzer.Analyzer
	LS                *sql.LockSubsystem
	ProcessList       sql.ProcessList
	MemoryManager     *sql.MemoryManager
	BackgroundThreads *sql.BackgroundThreads
	ReadOnly          atomic.Bool
	IsServerLocked    bool
	PreparedDataCache *PreparedDataCache

	Version        sql.AnalyzerVersion
	EventScheduler *eventscheduler.EventScheduler
	// contains filtered or unexported fields
}

Engine is a SQL engine.

func New 

func New(a *analyzer.Analyzer, cfg *Config) *Engine

New creates a new Engine with custom configuration. To create an Engine with the default settings use `NewDefault`. Should call Engine.Close() to finalize dependency lifecycles.

func NewDefault 

func NewDefault(pro sql.DatabaseProvider) *Engine

NewDefault creates a new default Engine.

func (*Engine) AnalyzeQuery 

func (e *Engine) AnalyzeQuery(
	ctx *sql.Context,
	query string,
) (sql.Node, error)

AnalyzeQuery analyzes a query and returns its sql.Node

func (*Engine) BoundQueryPlan 

func (e *Engine) BoundQueryPlan(ctx *sql.Context, query string, parsed sqlparser.Statement, bindings map[string]*querypb.BindVariable) (sql.Node, error)

BoundQueryPlan returns query plan for the given statement with the given bindings applied

func (*Engine) Close 

func (e *Engine) Close() error

func (*Engine) CloseSession 

func (e *Engine) CloseSession(connID uint32)

CloseSession deletes session specific prepared statement data

func (*Engine) EngineAnalyzer 

func (e *Engine) EngineAnalyzer() *analyzer.Analyzer

func (*Engine) EnginePreparedDataCache 

func (e *Engine) EnginePreparedDataCache() *PreparedDataCache

func (*Engine) InitializeEventScheduler 

func (e *Engine) InitializeEventScheduler(ctxGetterFunc func() (*sql.Context, func() error, error), status eventscheduler.SchedulerStatus, period int) error

InitializeEventScheduler initializes the EventScheduler for the engine with the given sql.Context getter function, |ctxGetterFunc, the EventScheduler |status|, and the |period| for the event scheduler to check for events to execute. If |period| is less than 1, then it is ignored and the default period (30s currently) is used. This function also initializes the EventScheduler of the analyzer of this engine.

func (*Engine) IsReadOnly 

func (e *Engine) IsReadOnly() bool

func (*Engine) PrepQueryPlanForExecution 

func (e *Engine) PrepQueryPlanForExecution(ctx *sql.Context, query string, plan sql.Node) (sql.Schema, sql.RowIter, error)

PrepQueryPlanForExecution prepares a query plan for execution and returns the result schema with a row iterator to begin spooling results

func (*Engine) PrepareParsedQuery 

func (e *Engine) PrepareParsedQuery(
	ctx *sql.Context,
	statementKey, query string,
	stmt sqlparser.Statement,
) (sql.Node, error)

PrepareParsedQuery returns a partially analyzed query for the parsed statement provided

func (*Engine) PrepareQuery 

func (e *Engine) PrepareQuery(
	ctx *sql.Context,
	query string,
) (sql.Node, error)

PrepareQuery returns a partially analyzed query

func (*Engine) Query 

func (e *Engine) Query(ctx *sql.Context, query string) (sql.Schema, sql.RowIter, error)

Query executes a query.

func (*Engine) QueryWithBindings 

func (e *Engine) QueryWithBindings(ctx *sql.Context, query string, parsed sqlparser.Statement, bindings map[string]*querypb.BindVariable) (sql.Schema, sql.RowIter, error)

QueryWithBindings executes the query given with the bindings provided. If parsed is non-nil, it will be used instead of parsing the query from text.

func (*Engine) WithBackgroundThreads 

func (e *Engine) WithBackgroundThreads(b *sql.BackgroundThreads) *Engine

type PreparedDataCache 

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

PreparedDataCache manages all the prepared data for every session for every query for an engine. There are two types of caching supported: 1. Prepared statements for MySQL, which are stored as sqlparser.Statements 2. Prepared statements for Postgres, which are stored as sql.Nodes TODO: move this into the session

func NewPreparedDataCache 

func NewPreparedDataCache() *PreparedDataCache

func (*PreparedDataCache) CacheStmt 

func (p *PreparedDataCache) CacheStmt(sessId uint32, query string, stmt sqlparser.Statement)

CacheStmt saves the parsed statement and associates a ctx.SessionId and query to it

func (*PreparedDataCache) CachedStatementsForSession 

func (p *PreparedDataCache) CachedStatementsForSession(sessId uint32) map[string]sqlparser.Statement

CachedStatementsForSession returns all the prepared queries for a particular session

func (*PreparedDataCache) DeleteSessionData 

func (p *PreparedDataCache) DeleteSessionData(sessId uint32)

DeleteSessionData clears a session along with all prepared queries for that session

func (*PreparedDataCache) GetCachedStmt 

func (p *PreparedDataCache) GetCachedStmt(sessId uint32, query string) (sqlparser.Statement, bool)

GetCachedStmt retrieves the prepared statement associated with the ctx.SessionId and query. Returns nil, false if the query does not exist

func (*PreparedDataCache) UncacheStmt 

func (p *PreparedDataCache) UncacheStmt(sessId uint32, query string)

UncacheStmt removes the prepared node associated with a ctx.SessionId and query to it

type ProcessList 

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

ProcessList is a structure that keeps track of all the processes and their status.

func NewProcessList 

func NewProcessList() *ProcessList

NewProcessList creates a new process list.

func (*ProcessList) AddConnection 

func (pl *ProcessList) AddConnection(id uint32, addr string)

func (*ProcessList) AddPartitionProgress 

func (pl *ProcessList) AddPartitionProgress(pid uint64, tableName, partitionName string, total int64)

AddPartitionProgress adds a new item to track progress from to the process with the given pid. If the pid or the table does not exist, it will do nothing.

func (*ProcessList) AddTableProgress 

func (pl *ProcessList) AddTableProgress(pid uint64, name string, total int64)

AddTableProgress adds a new item to track progress from to the process with the given pid. If the pid does not exist, it will do nothing.

func (*ProcessList) BeginQuery 

func (pl *ProcessList) BeginQuery(
	ctx *sql.Context,
	query string,
) (*sql.Context, error)

func (*ProcessList) ConnectionReady 

func (pl *ProcessList) ConnectionReady(sess sql.Session)

func (*ProcessList) EndQuery 

func (pl *ProcessList) EndQuery(ctx *sql.Context)

func (*ProcessList) Kill 

func (pl *ProcessList) Kill(connID uint32)

Kill terminates all queries for a given connection id.

func (*ProcessList) Processes 

func (pl *ProcessList) Processes() []sql.Process

Processes returns the list of current running processes.

func (*ProcessList) RemoveConnection 

func (pl *ProcessList) RemoveConnection(connID uint32)

func (*ProcessList) RemovePartitionProgress 

func (pl *ProcessList) RemovePartitionProgress(pid uint64, tableName, partitionName string)

RemovePartitionProgress removes an existing item tracking progress from the process with the given pid, if it exists.

func (*ProcessList) RemoveTableProgress 

func (pl *ProcessList) RemoveTableProgress(pid uint64, name string)

RemoveTableProgress removes an existing item tracking progress from the process with the given pid, if it exists.

func (*ProcessList) UpdatePartitionProgress 

func (pl *ProcessList) UpdatePartitionProgress(pid uint64, tableName, partitionName string, delta int64)

UpdatePartitionProgress updates the progress of the table partition with the given name for the process with the given pid.

func (*ProcessList) UpdateTableProgress 

func (pl *ProcessList) UpdateTableProgress(pid uint64, name string, delta int64)

UpdateTableProgress updates the progress of the table with the given name for the process with the given pid.

type TemporaryUser 

type TemporaryUser struct {
	Username string
	Password string
}

TemporaryUser is a user that will be added to the engine. This is for temporary use while the remaining features are implemented. Replaces the old "auth.New..." functions for adding a user.

Source Files

View all Source files

Directories

Path Synopsis
Package driver implements a driver for Go's database/sql support.
 Package driver implements a driver for Go's database/sql support.
_example
mysqlshim
plangen/cmd/plangen
queries
scriptgen/cmd/scriptgen
scriptgen/setup
sqllogictest
sqllogictest/check
sqllogictest/convert
sqllogictest/harness
sqllogictest/utils
internal
cmap
regex
similartext
sockstate
strings
time
Package time contains low-level utility functions for working with time.Time values and timezones.
 Package time contains low-level utility functions for working with time.Time values and timezones.
optgen
cmd/optgen
cmd/support
golden
sql
analyzer
analyzer/analyzererrors
binlogreplication
encodings
encodings/generate
expression
expression/function
expression/function/aggregation
expression/function/aggregation/window
expression/function/json
expression/function/spatial
fulltext
in_mem_table
information_schema
memo
mysql_db
mysql_db/serial
plan
planbuilder
planbuilder/dateparse
rowexec
stats
transform
types
values
variables

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.