myCCQL - A database management system

[NihilisticPuffin]

myCCQL

myCCQL is a database management system for ComputerCraft. It supports a custom binary format for persistent storage and an SQL-like query language (CCQL) with common operations such as 'CREATE TABLE', 'INSERT', 'SELECT', and 'DELETE'.

Install

Code Select Expand

wget https://gist.githubusercontent.com/NihilisticPuffin/bf17c985f0ebb925a4bbf34ad6001208/raw/837f6e493ee1e48fbf6c15ee085ea951d4d080ee/database.lua
Features

• Tables 
  Define tables with typed columns (u8, u16, u32, s8, s16, s32, str).

• Data Types 
• Unsigned integers: u8, u16, u32 
• Signed integers: s8, s16, s32 
• Strings: str 
• Null: Null (special null object)

• Query Language
• CREATE TABLE name (column type, ...)
• INSERT INTO name VALUES (...)
• INSERT INTO name (columns...) VALUES (...)
• SELECT column(s) FROM name [WHERE column=value [AND ...]]
• DELETE FROM name [WHERE column=value [AND ...]]

• NulL Handling
• NullL is a special object that can be inserted into any column
• Stored using a unique type ID 0x11

Binary Format Overview

• File starts with magic number: 0xCCDB
• Table count as u16
• Tables
• Name (string)
• Column count as u16 + definitions (name + type)
• Row count as u16 + values
 
• Each value is preceded by a type byte:
• 0x01 - u8, 0x02 - u16, 0x03 - u32
• 0x04 - s8, 0x05 - s16, 0x06 - s32
• 0x10 - string (null-terminated)
• 0x11 - null

Example Usage

Code Select Expand

local Database = require "database"

local function print_result(result)
    for _, row in ipairs(result) do
        local out = {}
        for k, v in pairs(row) do
            table.insert(out, tostring(k) .. ":" .. tostring(v))
        end
        print(table.concat(out, ", "))
    end
end

local db = Database.new()
-- db:load("database.db") -- Load from file
-- Create a table
db:execute("CREATE TABLE users (id u32, name str, age u8)")

-- Insert rows
db:execute("INSERT INTO users VALUES (1, 'Alice', 19)")
db:execute("INSERT INTO users VALUES (2, 'Bob', 25)")
db:execute("INSERT INTO users VALUES (3, 'Frank', 23)")

-- Delete a row
db:execute("DELETE FROM users WHERE name='Bob'")

-- Query remaining rows
local result = db:execute("SELECT * FROM users")
print_result(result)

-- Save to disk
db:save("database.db")


Planned Features

• Complete rewrite of query language parser
• Automatically save changes on query
• Statements
• UPDATE ex: UPDATE users SET name='Bobby', age=23 WHERE id=1;
 
• Operators
• IN ex: SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
• BETWEEN ex: SELECT * FROM Products WHERE Price BETWEEN 50 AND 60;
 
• Functions
• NOW() returns current time as unix epoch
 
• Column type checking during insertions.
• Support for OR operator in WHERE clause