A course to build the SQL layer of a distributed database.

Related tags

Database tinysql
Overview

TinySQL

TinySQL is a course designed to teach you how to implement a distributed relational database in Go. TinySQL is also the name of the simplifed version of TiDB.

Prerequisites

Experience with Go is required. If not, it is recommended to learn A Tour of Go first.

Course Overview

The detailed information you can get in the directory courses.

There's a material list. It lists plenty of materials for you to study how a database system works. We picks some of the topics in it and prepare homework for you to have a better understand about it.

This course will take you from idea to implementation, with the essential topics of distributed relational database covered.

The course is organized into three parts:

  1. Gives a simple interpretation of SQL and relational algebra in preparation for the following course.

  2. Explains the life of a read-only SQL, which includes parsing, execution, and the optimization of SQL plans.

  3. Focuses on SQLs (including DML and DDL) that change the state of the database: how they get implemented and how to deal with the interaction of them and read-only statements.

Other courses in this series

This course only focuses on the SQL layer of a distributed database system. If you are also interested in KV layer, see TinyKV.

Deploy

Once you finish the project. You can deploy the binary and use MySQL client to connect the server.

Build

make

Run & Play

Use ./bin/tidb-server to deploy the server and use mysql -h127.0.0.1 -P4000 -uroot to connect the server.

Also you can deploy the cluster together with the tinykv project.

Use make command in tinykv project to build binary tinyscheduler-server, tinykv-server.

Then put the binaries into a single dir and run the following commands:

mkdir -p data
./tinyscheduler-server
./tinykv-server -path=data
./tidb-server --store=tikv --path="127.0.0.1:2379"

Contributing

Contributions are welcomed and greatly appreciated. See CONTRIBUTING.md for details on submitting patches and the contribution workflow.

License

TinySQL is under the Apache 2.0 license. See the LICENSE file for details.

Comments
  • Enhencement:tablecodec/tablecodec.go/EncodeIndexSeekKey

    Enhencement:tablecodec/tablecodec.go/EncodeIndexSeekKey

    This PR improve EncodeIndexSeekKey with allocating enough space for the key:

    • currently the key was allocated with some space at first, which is not enough for the total things
    • that works ok, but it means there will have another allocating and copy when append encodedValue
    • so i change the size of space allocated at first to avoid reallocating and finally improve performance
    opened by benoxo 4
  • parser: add missing test.sh&gitignore

    parser: add missing test.sh&gitignore

    test.sh missing in parser package, if we execute make test would return:

    sh test.sh
    sh: test.sh: No such file or directory
    make: *** [test] Error 127
    
    opened by hidehalo 3
  • ddl: Project 3 Go test fail

    ddl: Project 3 Go test fail

    What did I do

    go test in ddl folder

    Expect

    Assert fauilre or Success

    See Instead

    # github.com/pingcap/tidb/planner/core [github.com/pingcap/tidb/ddl.test]
    ../planner/core/rule_join_reorder_dp.go:17:2: imported and not used: "math/bits"
    FAIL    github.com/pingcap/tidb/ddl [build failed]
    
    opened by BowenXiao1999 3
  • Makefile error causes the target test-proj4-1,test-proj4-2 fails

    Makefile error causes the target test-proj4-1,test-proj4-2 fails

    [email protected] tinysql-course % make test-proj4-1 pwd /Users/roger/Downloads/pichunying/tinysql-course cd planner/core &&
    go test -check.f TestPredicatePushDown && \

    /bin/sh: -c: line 1: syntax error: unexpected end of file make: *** [test-proj4-1] Error 2

    opened by yanguwan 2
  • Fix TestLockTTL

    Fix TestLockTTL

    fix #122

    1. Add elapsedUnit for ease of correcting calculation of TTL in TestLockTTL without effect on other implementations
    2. Convert x and y to float64 ahead of subtraction in ttlEquals
    opened by czt1999 2
  • Add MySQL protocol layer code

    Add MySQL protocol layer code

    This closes #88

    This PR contains the simple MySQL protocol layer code. These codes refer to the implementation of TiDB, but are simplified and only include the necessary implementation. It should be noted that the current implementation only supports the "mysql_native_password" auth plugin.

    How to use

    compile and run

    make
    ./bin/tinysql-server
    

    use MySQL-client

    mysql --host 127.0.0.1 --port 4000 -u root
    

    TODO

    • [ ] https://github.com/tidb-incubator/tinysql/issues/94
    • [ ] https://github.com/tidb-incubator/tinysql/issues/96
    • [ ] https://github.com/tidb-incubator/tinysql/issues/95
    opened by rebelice 2
  • Update proj1-part2-README-zh_CN.md: add example about how to run single test

    Update proj1-part2-README-zh_CN.md: add example about how to run single test

    I think we need to add example about how to run single test in proj1-part2-README-zh_CN.md, because this is an important information to help beginners to debug tinysql on single test at the beginning of tinysql.

    opened by HuGanghui 2
  • Possibly mismatched zipfFactor and avgError in statistics/cmsketch_test.go

    Possibly mismatched zipfFactor and avgError in statistics/cmsketch_test.go

    Hi!

    While struggling with TestCMSketch, I found an interesting fact that values generated with lower zipfFactor are more dispersive (thus lead to more collision) but with lower expected avgErr. This can be inferred by the definition of Zipf distribution.

    I have passed case 2 (zipfFactor = 2) and case 3 (zipfFactor = 3), and the avgErrs are all 0. I am sure that hashing results for different rows are independent and I have tried many groups. However, these different groups of hasher all failed in case 1 (zipfFactor = 1.1) with avgErr 10, which really upsets me :)

    The numbers of distinct values generated in terms of different zipfFactor are as follows:

    zipfFactor: 1.1  len(lMap): 23965
    zipfFactor: 2  len(lMap): 420
    zipfFactor: 3  len(lMap): 58
    

    It seems that lower zipfFactor should be coupled with higher avgErr. And the incorrect arrErr 10 I got in case 1 looks reasonable giving width = 2048. Is that wrong? I really need some advice.

    Thanks!

    opened by czt1999 1
  • Non-issue: allow user to custom

    Non-issue: allow user to custom "go" command in makefile

    TinySQL require "go" version 1.13.

    I can't install [email protected] via "Homebrew" on my laptop, cauz it's abandoned at upstream.

    Since that, I must download [email protected] manually from "go" website.

    The point is, a lots user work on go version > 1.13, they must have multiple go versions executable command.

    We should allow user to custom "go" command in "Makefile".

    eg:

    # which [email protected]
    [email protected] version
    go version go1.13.15 darwin/amd64
    
    # which [email protected]
    go version
    go version go1.18.3 darwin/amd64
    
    # env variable `GOCMD` use `[email protected]` replace to `[email protected]`
    [email protected] make test-proj1
    cd tablecodec && \
    	[email protected] test
    
    # no specific `GOCMD` is fine, we would use default command "go"
     make test-proj1
     cd tablecodec && \
    	go test
    
    opened by hidehalo 1
  • fix default unsigned integers judgment

    fix default unsigned integers judgment

    What problem does this PR solve?

    • I am using Apple M1 (GOARCH="arm64"; GOOS="darwin"; GOVERSION="go1.18.3") to test the proj6 but failed to pass the only case TestLockTTL.
    • The testLockSuite function ttlEquals should be set true when the arch is arm64, otherwise, the code will not be able to pass the test locally, although it works well on the Autograding machine.

    What is changed and how it works?

    I have written a demo as follows.

    func main() {
        fmt.Printf("runtime.GOARCH: %s\n", runtime.GOARCH)
        var x, y uint64 = 73, 1040
        fmt.Printf("x: %d, y: %d\n", x, y)
        fmt.Printf("int(math.Abs(float64(x-y))): %d\n", int(math.Abs(float64(x-y))))
        fmt.Printf("int(-math.Abs(float64(x-y))): %d\n", int(-math.Abs(float64(x-y))))
    }
    

    It will get different results depending on whether the system is arm64 or amd64.

    // in GOARCH="amd64" GOOS="linux"
    runtime.GOARCH: amd64
    x: 73, y: 1040
    int(-math.Abs(float64(x-y))): -9223372036854775808
    int(math.Abs(float64(x-y))): -9223372036854775808
    
    // in GOARCH="arm64" GOOS="darwin"
    runtime.GOARCH: arm64
    x: 73, y: 1040
    int(-math.Abs(float64(x-y))): -9223372036854775808
    // c.Assert(int(math.Abs(float64(x-y))), LessEqual, 2) will failed
    int(math.Abs(float64(x-y))): 9223372036854775807
    

    In order to avoid unnecessary misunderstandings among students using the MacBook with Apple silicon, I suggest adding a condition runtime.GOARCH == "arm64" to the judgment.

    opened by Chasing1020 0
  • Typo in proj1-2 code comments

    Typo in proj1-2 code comments

    In tableCodec.go, line 146, the comment is suggesting to use errInvalidRecordKey.GenWithStack whereas the function is decoding index prefix key. Might be better to consider changing this into using errInvalidIndexKey.GenWithStack.

    func DecodeIndexKeyPrefix(key kv.Key) (tableID int64, indexID int64, indexValues []byte, err error) {
    	...
    	 *   3. errInvalidRecordKey.GenWithStack is a useful function to generate invalid record key errors.
    	 ...
    }
    

    To

    func DecodeIndexKeyPrefix(key kv.Key) (tableID int64, indexID int64, indexValues []byte, err error) {
    	...
    	 *   3. errInvalidIndexKey.GenWithStack is a useful function to generate invalid record key errors.
    	 ...
    }
    
    opened by ccjeff 0
  • Fix `lock_test.go/TestLockTTL`

    Fix `lock_test.go/TestLockTTL`

    In lock_test.go, settings of lock TTL are shrunk together to speed up tests:

    func init() {
        // Speed up tests.
        defaultLockTTL = 3
        maxLockTTL = 120
        ttlFactor = 6
        oracleUpdateInterval = 2
    }
    

    However, the remaining code still applies millisecond while computing elapsed time (ex.2pc.go/txnLockTTL), which will add a relatively large bias on the basic formula ttl = ttlFactor * sqrt(sizeInMiB).

    In addition, ttlEquals ignores case that x < y, where float(x - y) will result in a wrong answer given x and y of uint type.

    opened by czt1999 0
Owner
TiDB Incubator
TiDB Incubator
TiDB Incubator
CockroachDB - the open source, cloud-native distributed SQL database.

CockroachDB is a cloud-native SQL database for building global, scalable cloud services that survive disasters. What is CockroachDB? Docs Quickstart C

CockroachDB 26.3k Jan 2, 2023
LBADD: An experimental, distributed SQL database

LBADD Let's build a distributed database. LBADD is an experimental distributed SQL database, written in Go. The goal of this project is to build a dat

Tom Arrell 379 Nov 29, 2022
Yeqllo 23 Nov 1, 2022
Beerus-DB: a database operation framework, currently only supports Mysql, Use [go-sql-driver/mysql] to do database connection and basic operations

Beerus-DB · Beerus-DB is a database operation framework, currently only supports Mysql, Use [go-sql-driver/mysql] to do database connection and basic

Beerus 7 Oct 29, 2022
A decentralized, trusted, high performance, SQL database with blockchain features

中文简介 CovenantSQL(CQL) is a Byzantine Fault Tolerant relational database built on SQLite: ServerLess: Free, High Availabile, Auto Sync Database Service

CovenantSQL 1.4k Jan 3, 2023
☄ The golang convenient converter supports Database to Struct, SQL to Struct, and JSON to Struct.

Gormat - Cross platform gopher tool The golang convenient converter supports Database to Struct, SQL to Struct, and JSON to Struct. 中文说明 Features Data

永林 289 Dec 20, 2022
Dolt is a SQL database that you can fork, clone, branch, merge, push and pull just like a git repository.

Dolt is a SQL database that you can fork, clone, branch, merge, push and pull just like a git repository. Connect to Dolt just like any MySQL database to run queries or update the data using SQL commands. Use the command line interface to import CSV files, commit your changes, push them to a remote, or merge your teammate's changes.

DoltHub 13.8k Dec 31, 2022
DonutDB: A SQL database implemented on DynamoDB and SQLite

DonutDB: A SQL database implemented on DynamoDB and SQLite

Peter Sanford 128 Dec 21, 2022
This is a simple Golang application that executes SQL commands to clean up a mirror node's database.

This is a simple Golang application that executes SQL commands to clean up a mirror node's database.

Tom 1 Jan 24, 2022
Distributed reliable key-value store for the most critical data of a distributed system

etcd Note: The master branch may be in an unstable or even broken state during development. Please use releases instead of the master branch in order

etcd-io 42.3k Jan 9, 2023
The lightweight, distributed relational database built on SQLite.

rqlite is a lightweight, distributed relational database, which uses SQLite as its storage engine. Forming a cluster is very straightforward, it grace

rqlite 12.9k Jan 5, 2023
TiDB is an open source distributed HTAP database compatible with the MySQL protocol

Slack Channel Twitter: @PingCAP Reddit Mailing list: lists.tidb.io For support, please contact PingCAP What is TiDB? TiDB ("Ti" stands for Titanium) i

PingCAP 33.1k Jan 9, 2023
TalariaDB is a distributed, highly available, and low latency time-series database for Presto

TalariaDB is a distributed, highly available, and low latency time-series database that stores real-time data. It's built on top of Badger DB.

Grab 104 Nov 16, 2022
Redwood is a highly-configurable, distributed, realtime database that manages a state tree shared among many peers

Redwood is a highly-configurable, distributed, realtime database that manages a state tree shared among many peers. Imagine something like a Redux store, but distributed across all users of an application, that offers offline editing and is resilient to poor connectivity.

Redwood 740 Jan 8, 2023
decentralized,distributed,peer-to-peer database.

P2PDB 中文 | English 简介 P2PDB(p2p数据库),是一个去中心化、分布式、点对点数据库、P2PDB使用IPFS为其数据存储和IPFS Pubsub自动与对等方同步数据。P2PDB期望打造一个去中心化的分布式数据库,使P2PDB 成为去中心化应用程序 (dApps)、区块链应用程

Rock 16 Jan 1, 2023
Couchbase - distributed NoSQL cloud database

couchbase Couchbase is distributed NoSQL cloud database. create Scope CREATE SCO

Md Abu. Raihan 2 Feb 16, 2022
Owl is a db manager platform,committed to standardizing the data, index in the database and operations to the database, to avoid risks and failures.

Owl is a db manager platform,committed to standardizing the data, index in the database and operations to the database, to avoid risks and failures. capabilities which owl provides include Process approval、sql Audit、sql execute and execute as crontab、data backup and recover .

null 34 Nov 9, 2022
This is a simple graph database in SQLite, inspired by "SQLite as a document database".

About This is a simple graph database in SQLite, inspired by "SQLite as a document database". Structure The schema consists of just two structures: No

Denis Papathanasiou 1.2k Jan 3, 2023
Hard Disk Database based on a former database

Hard Disk Database based on a former database

null 0 Nov 1, 2021