Fatworm 2012

User Tools

Site Tools

Trace: • spec
spec

This is an old revision of the document!

Table of Contents

Specification

Introduction

This page describes the specification of Fatworm project

Feature Requirements

Baseline

  1. Data Types
    1. Primitive INT, FLOAT, BOOLEAN
    2. Time TIMESTAMP, DATETIME
    3. String CHAR(), VARCHAR()
    4. Other DECIMAL(,)
  2. Components
    1. Storate
      • Buffering
    2. Heap File
    3. SQL Parser and Engine
      1. Create / Drop databases
      2. Create / Drop tables
      3. Insert / Delete / Update tuples in table
      4. Basic SELECT on single or multiple table(s)
      5. More powerful SELECT
        • DISTINCT
        • Alias of tables
        • [NOT] EXISTS
        • ORDER BY can accept more than one column
        • GROUP BY / HAVING clauses
  3. Functionality
    1. JDBC Interface
    2. Standalone Driver

Advance

  1. Components
    1. B+ Tree
      1. Basic B+ Tree Index support
      2. Balanced deleting
      3. Duplicated key support
      4. VARCHAR index
    2. More powerful SQL
      1. Create / Drop indexes (only involved with single column)
      2. Powerful SELECT
        1. Subquery as Scalar Operand
        2. Subquery as Relation Operand (ANY, ALL, IN)
        3. Subquery in FROM clause, see Syntax
      3. More complex INSERT: Insert subquery
    3. Database manager
      1. More than one database in system
      2. Concurrency control (locks on table)
      3. Concurrency control (locks on pages)
  2. Functionality:
    1. Support transaction
    2. Logger and roll back
  3. Supplementary Specifi cation
    1. Syntax
      1. STRING & DATE
        1. Any CHAR, VARCHAR, DATETIME or TIMESTAMP starts and ends with a '.
      2. AUTO_INCREMENT
        1. One table can have one column to be AUTO_INCREMENT, and this column MUST BE the PRIMARY KEY.
        2. AUTO INCREMENT column MUST BE INT.
        3. When inserting a row, value in AUTO_INCREMENT eld CAN be a value given. If no value were given, the value should be (MAX(this column)+1).If the value were not given and no row were in table, the value should be 1.
        4. Of couse, AUTO_INCREMENT column CAN BE updated.
      3. INSERT
        1. If user used INSERT INTO table name VALUES (…), ALL elds should be speci ed.
        2. The Default value will be used, only when user uses INSERT INTO table name(…) VALUES (…).
      4. TIMESTAMP
        1. When inserting or updateing, if the value of this eld were not given,it means current time.

Some Clari cation

  1. It's free for your team to decide which programming language to use, but the test case is totally based on the JDBC interface, so we strongly recommend Java as your programming language.
  2. You should code very thing by yourself. However, taking advantage of some code-generation tool like JFLEX or JavaCup is allowed and encouraged.
  3. It's NOT required to do arithmetic operation on DECIMAL column. In addition, you can set arbitrary restrict on CHAR(), DECIMAL() and VARCHAR(), as long as it's reasonable. For example, the length of VARCHAR can't exceed 4000. You should also write these restrict in document.
  4. You are allowed to keep metadata of table / database (or something like that) in memory (outside bu er), since they are not very big. What you have to load into bu er are records (in heap le) and B+ trees.

Syntax of FwSQL

Note: the syntax of FwSQL(Fatworm-SQL)is a simplified version of MySQL. Please go to http://dev.mysql.com/doc/ for more details.

- Create or Drop Database

  • CREATE DATABASE db-name
  • USE db-name
  • DROP DATABASE db-name

- Create or Drop table

  • CREATE TABLE tbl-name (create-definition, …)
  • DROP TABLE tbl-name [,tbl-name]
  • create-definition ::= column-definition | PRIMARY KEY (col-name)
  • column-definition ::= col-name data-type [[NOT]NULL] [DEFAULT const-value][AUTO_INCREMENT]
  • data-type ::= INT | FLOAT | CHAR(M) | DATETIME | BOOLEAN | DECIMAL(M[,D]) | TIMESTAMP | VARCHAR(M)

-Insert

  • INSERT INTO tbl-name VALUES (value, …)
  • INSERT INTO tbl-name(col-name,…)VALUES(value,…)
  • INSERT INTO tbl-name(subquery)

-Delete

  • DELETE FROM tbl-name [WHERE where-condition]

-Update

  • UPDATE tbl-name SET col-name1=value [,col-name2=value…][WHERE where-condition]

- Create or Drop index

  • CREATE [UNIQUE] INDEX index-name ON tbl-name (col-name)
  • DROP INDEX index-name ON tbl-name

- Select

  • SELECT [DISTINCT] select-expr,…
    [FROM tbl-ref[,tbl-ref]…]
    [WHERE where-condition]
    [GROUP BY col-name]
    [HAVING having-condition]
    [ORDER BY col-name[ ASC | DESC ],…]
  • select-expr ::=value [AS alias]|*
  • func ::= AVG | COUNT | MIN | MAX | SUM
  • col-name ::= [tbl-name.] col-name
  • tbl-ref ::=tbl-name[AS alias]
    |(subquery) AS alias
  • where-condition::=bool-expr
  • bool-expr ::=value cop value
    | bool-expr AND bool-expr
    | bool-expr OR bool-expr
    | [NOT] EXISTS (subquery)
    | value cop ANY (subquery)
    | value IN (subquery)
    | value cop ALL (subquery)
    | (bool-expr)
  • cop ::= < | > | = | ⇐ | >= | <>
  • value ::= (value)|col-name|const-value|value aop value
    | (subquery with only one elements(or NULL)in the result table)
    | func(col-name)
  • aop ::= + | - | * | / | %
  • const-value ::= integer | 'string' | float | 'YYYY-MM-DD HH:MM:SS' | TRUE | FALSE | NULL | DEFAULT
  • having-condition ::= bool-expr

Misc

Version 4.1

Date Created 2012-2-25

Authors

spec.1331392014.txt.gz · Last modified: 2012/03/10 15:06 by wuyw
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki