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.

Issues
  • 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
  • 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
  • Tracking issue for improvement course documentation and code comments

    Tracking issue for improvement course documentation and code comments

    This issue aims to address the lack of course documentation and code comments for TinySQL. We will start from this step to improve the user experience.

    The documentation layout

    ## Project
    ### Introduction
    
    ### Topic
    #### Knowledge topic
    #### Related code
    
    ### Exercises
    ### References
    

    Chinese version

    ## Project
    ### 简介
    
    ### XX主题
    #### 相关知识
    #### 相关代码
    
    ### 练习
    ### 引用
    

    The code comments layout

    • We'd better use /* Project 1: Your Code Here */ to identify where the user needs to fill in.
    • We'd better add detailed comments to help users, such as:
      func MyFunc(a int, b int) (int, error) {
      /*  What does `MyFunc` need to do?
       *  explain parameters:
       *      a int: ....;
       *      b int: ....;
       *   explain return value:
       *     int: ....;
       *     error: ....;
       *   `MyFunc` may need to follow the steps:
       *       1. xxxx;
       *       2. xxxx;
       *       .....
       *    Some hints that might be useful:
       *       - you may need some structs
       *       - you may need to add some members for struct A
       *       - you need to implement 'FuncA' first
       *       - Be aware of concurrency issues
       *       .....
       */ 
      }
      

    TODO

    • [x] #113
    • [x] #115
    • [ ] #111
    • [x] #119
    • [ ] Project 5
    opened by rebelice 1
  • A line in Proj6 may corrupt Proj3 randomly

    A line in Proj6 may corrupt Proj3 randomly


    Env setup Go: 1.16 Current progress: proj3 in progress, proj1&2 fin


    When I test my code for as the the last section in this page: https://github.com/tidb-incubator/tinysql/blob/course/courses/proj3-README-zh_CN.md

    I hit this line sometimes: https://github.com/tidb-incubator/tinysql/blob/1bdb9a3bc6ba158424a7da352d14d2b72d39ebaf/store/tikv/snapshot.go#L150

    After I commented it out, those tests are able to pass.

    Can someone please check if that's a real issue or a bug introduced by my change?

    opened by johnhaxx7 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 25.3k Aug 7, 2022
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 377 Aug 4, 2022
Yeqllo 22 Nov 26, 2021
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 6 Mar 5, 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 Aug 2, 2022
☄ 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

永林 276 Jul 19, 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 12.8k Aug 9, 2022
DonutDB: A SQL database implemented on DynamoDB and SQLite

DonutDB: A SQL database implemented on DynamoDB and SQLite

Peter Sanford 100 Jul 7, 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 40.8k Aug 9, 2022
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 10.8k Aug 7, 2022
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 32k Aug 1, 2022
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 98 Jun 29, 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 646 Jul 31, 2022
decentralized,distributed,peer-to-peer database.

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

Rock 6 Aug 8, 2022
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 35 Jun 17, 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.1k Aug 1, 2022
Hard Disk Database based on a former database

Hard Disk Database based on a former database

null 0 Nov 1, 2021