sqle

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

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

 Go to latest Published: Jun 29, 2018 License: Apache-2.0 Imports: 5 Imported by: 0

README

go-mysql-server

Build Status codecov GoDoc

go-mysql-server is a SQL engine which parses standard SQL (based on MySQL syntax), resolves and optimizes queries. It provides simple interfaces to allow custom tabular data source implementations.

go-mysql-server also provides a server implementation compatible with the MySQL wire protocol. That means it is compatible with MySQL ODBC, JDBC, or the default MySQL client shell interface.

Scope of this project

These are the goals of go-mysql-server:

  • Be a generic extensible SQL engine that performs queries on your data sources.
  • Provide interfaces so you can implement your own custom data sources without providing any (except for the mem data source that is used for testing purposes).
  • Have a runnable server you can use on your specific implementation.
  • Parse and optimize queries while still allow specific implementations to add their own analysis steps and optimizations.
  • Provide some common index driver implementations so the user does not have to bring their own index implementation, and still be able to do so if they need to.

What are not the goals of go-mysql-server:

  • Be a drop-in MySQL database replacement.
  • Be an application/server you can use directly.
  • Provide any kind of backend implementation (other than the mem one used for testing) such as json, csv, yaml, ... That's for clients to implement and use.

What's the use case of go-mysql-server?

Having data in another format that you want as tabular data to query using SQL, such as git. As an example of this, we have gitbase.

Installation

The import path for the package is gopkg.in/src-d/go-mysql-server.v0.

To install it, run:

go get gopkg.in/src-d/go-mysql-server.v0

Documentation

SQL syntax

We are continuously adding more functionality to go-mysql-server. We support a subset of what is supported in MySQL, to see what is currently included check the SUPPORTED file.

Custom functions

  • IS_BINARY(blob): returns whether a BLOB is a binary file or not.
  • SUBSTRING(str,pos), SUBSTRING(str,pos,len): return a substring from the provided string.
  • Date and Timestamp functions: YEAR(date), MONTH(date), DAY(date), HOUR(date), MINUTE(date), SECOND(date), DAYOFYEAR(date).
  • ARRAY_LENGTH(json): If the json representation is an array, this function returns its size.
  • SPLIT(str,sep): receives a string and a delimiter and returns the parts of the string splitted by the delimiter as a JSON array of strings.

Example

go-mysql-server contains a SQL engine and server implementation. So, if you want to start a server, first instantiate the engine and pass your sql.Database implementation.

It will be in charge of handling all the logic to retrieve the data from your source. Here you can see an example using the in-memory database implementation:

...

func main() {
    driver := sqle.New()
    driver.AddDatabase(createTestDatabase())

    auth := mysql.NewAuthServerStatic()
    auth.Entries["user"] = []*mysql.AuthServerStaticEntry{{
        Password: "pass",
    }}

    config := server.Config{
        Protocol: "tcp",
        Address:  "localhost:3306",
        Auth:     auth,
    }

    s, err := server.NewDefaultServer(config, driver)
    if err != nil {
        panic(err)
    }

    s.Start()
}

func createTestDatabase() *mem.Database {
    const (
        dbName    = "test"
        tableName = "mytable"
    )

    db := mem.NewDatabase(dbName)
    table := mem.NewTable(tableName, sql.Schema{
        {Name: "name", Type: sql.Text, Nullable: false, Source: tableName},
        {Name: "email", Type: sql.Text, Nullable: false, Source: tableName},
        {Name: "phone_numbers", Type: sql.JSON, Nullable: false, Source: tableName},
        {Name: "created_at", Type: sql.Timestamp, Nullable: false, Source: tableName},
    })

    db.AddTable(tableName, table)
    table.Insert(sql.NewRow("John Doe", "john@doe.com", []string{"555-555-555"}, time.Now()))
    table.Insert(sql.NewRow("John Doe", "johnalt@doe.com", []string{}, time.Now()))
    table.Insert(sql.NewRow("Jane Doe", "jane@doe.com", []string{}, time.Now()))
    table.Insert(sql.NewRow("Evil Bob", "evilbob@gmail.com", []string{"555-666-555", "666-666-666"}, time.Now()))
    return db
}

...

Then, you can connect to the server with any MySQL client:

> mysql --host=127.0.0.1 --port=3306 -u user -ppass db -e "SELECT * FROM mytable"
+----------+-------------------+-------------------------------+---------------------+
| name     | email             | phone_numbers                 | created_at          |
+----------+-------------------+-------------------------------+---------------------+
| John Doe | john@doe.com      | ["555-555-555"]               | 2018-04-18 10:42:58 |
| John Doe | johnalt@doe.com   | []                            | 2018-04-18 10:42:58 |
| Jane Doe | jane@doe.com      | []                            | 2018-04-18 10:42:58 |
| Evil Bob | evilbob@gmail.com | ["555-666-555","666-666-666"] | 2018-04-18 10:42:58 |
+----------+-------------------+-------------------------------+---------------------+

See the complete example here.

Queries examples
SELECT count(name) FROM mytable
+---------------------+
| COUNT(mytable.name) |
+---------------------+
|                   4 |
+---------------------+

SELECT name,year(created_at) FROM mytable
+----------+--------------------------+
| name     | YEAR(mytable.created_at) |
+----------+--------------------------+
| John Doe |                     2018 |
| John Doe |                     2018 |
| Jane Doe |                     2018 |
| Evil Bob |                     2018 |
+----------+--------------------------+

SELECT email FROM mytable WHERE name = 'Evil Bob'
+-------------------+
| email             |
+-------------------+
| evilbob@gmail.com |
+-------------------+

Custom data source implementation

To be able to create your own data source implementation you need to implement the following interfaces:

  • sql.Database interface. This interface will provide tables from your data source.

    • If your database implementation supports adding more tables, you might want to add support for sql.Alterable interface

  • sql.Table interface. It will be in charge of transforming any kind of data into an iterator of Rows. Depending on how much you want to optimize the queries, you also can implement other interfaces on your tables:

    • sql.PushdownProjectionTable interface will provide a way to get only the columns needed for the executed query.
    • sql.PushdownProjectionAndFiltersTable interface will provide the same functionality described before, but also will push down the filters used in the executed query. It allows to filter data in advance, and speed up queries.
    • sql.Indexable add index capabilities to your table. By implementing this interface you can create and use indexes on this table.
    • sql.Inserter can be implemented if your data source tables allow insertions.

  • If you need some custom tree modifications, you can also implement your own analyzer.Rules.

You can see a really simple data source implementation on our mem package.

Powered by go-mysql-server

License

Apache License 2.0, see LICENSE

Documentation

Overview

Example 
package main

import (
	"fmt"
	"io"

	"gopkg.in/src-d/go-mysql-server.v0"
	"gopkg.in/src-d/go-mysql-server.v0/mem"
	"gopkg.in/src-d/go-mysql-server.v0/sql"
)

func main() {
	e := sqle.NewDefault()
	ctx := sql.NewEmptyContext()

	// Create a test memory database and register it to the default engine.
	e.AddDatabase(createTestDatabase())

	_, r, err := e.Query(ctx, `SELECT name, count(*) FROM mytable
	WHERE name = 'John Doe'
	GROUP BY name`)
	checkIfError(err)

	// Iterate results and print them.
	for {
		row, err := r.Next()
		if err == io.EOF {
			break
		}
		checkIfError(err)

		name := row[0]
		count := row[1]

		fmt.Println(name, count)
	}

}

func checkIfError(err error) {
	if err != nil {
		panic(err)
	}
}

func createTestDatabase() sql.Database {
	db := mem.NewDatabase("test")
	table := mem.NewTable("mytable", sql.Schema{
		{Name: "name", Type: sql.Text, Source: "mytable"},
		{Name: "email", Type: sql.Text, Source: "mytable"},
	})
	db.AddTable("mytable", table)
	table.Insert(sql.NewRow("John Doe", "john@doe.com"))
	table.Insert(sql.NewRow("John Doe", "johnalt@doe.com"))
	table.Insert(sql.NewRow("Jane Doe", "jane@doe.com"))
	table.Insert(sql.NewRow("Evil Bob", "evilbob@gmail.com"))

	return db
}

Output:

John Doe 2

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

This section is empty.

Types

type Engine 

type Engine struct {
	Catalog  *sql.Catalog
	Analyzer *analyzer.Analyzer
}

Engine is a SQL engine.

func New 

func New(c *sql.Catalog, a *analyzer.Analyzer) *Engine

New creates a new Engine

func NewDefault 

func NewDefault() *Engine

NewDefault creates a new default Engine.

func (*Engine) AddDatabase 

func (e *Engine) AddDatabase(db sql.Database)

AddDatabase adds the given database to the catalog.

func (*Engine) Init 

func (e *Engine) Init() error

Init performs all the initialization requirements for the engine to work.

func (*Engine) Query 

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

Query executes a query without attaching to any context.

Source Files

View all Source files

Directories

Path Synopsis
_scripts
go-vitess/etc
Package vitess is an automatic filter-branch, of the great Vitess project.
 Package vitess is an automatic filter-branch, of the great Vitess project.
sql
analyzer
expression
expression/function
expression/function/aggregation
index
index/pilosa
parse
plan

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.