ComputerCraft Database v1.01

--Tests if the database 'dbName' exists.
--Returns true if it does, else it returns false
function dbExists(dbName)
    if (fs.isDir("Databases\\" .. dbName)) then
        return true --Database exists
    else
        return false --Database doesn't exist
    end
end

--Tests if the table 'tableName' exists in the database 'db'
--Returns true if it does, else it returns false
function tableExists(db, tableName)
    if dbExists(db) then
        if (fs.isDir("Databases\\" .. db .. "\\" .. tableName)) then
            return true
        else
            return false
        end
    else
        print("Invalid Database")
        return false
    end
end

--This function behaves like the CREATE DATABASE operator in MySQL.
--Ex: MySQL: CREATE DATABASE `exampleDB`
--in this API: db.createDatabase("exampleDB")
function createDatabase(dbName)
    if (not(fs.isDir("Databases"))) then
        fs.makeDir("Databases")
    end
    if (not(dbExists(dbName))) then
        fs.makeDir("Databases\\" .. dbName)
    else
        print("Database already existing!")
    end
end

--This function behaves like the DROP DATABASE operator in MySQL.
--Ex: MySQL: DROP DATABASE `exampleDB`
--in this API: db.deleteDatabase("exampleDB")
function deleteDatabase(dbName)
    if (dbExists(dbName)) then
        fs.delete("Databases\\" .. dbName)
    end
end

--With this function you can create a new table.
--You dont need to specify any columns like in MySQL.
--The keys are set completely flexible, so you can create a key
--named "foo" for one row, and for the other you can create a key
--named "bar".
function createTable(db, tableName)
    if (dbExists(db)) then
        if (tableExists(db, tableName)) then
            print("Table already exists!")
        else
            fs.makeDir("Databases\\" .. db .. "\\" .. tableName)
        end
    else
        print("Invalid Database!")
    end
end

--This function behaves like the RENAME TABLE operator in MySQL.
--Ex: MySQL: RENAME TABLE `exampleTable` TO `newName`
--in this API: db.renameTable("DatabaseName", "exampleTable", "newName")
function renameTable(db, oldName, newName)
    if (dbExists(db)) then
        if (tableExists(db, oldName)) then
            basePath = "Databases\\" .. db .. "\\"
            fs.move(basePath .. oldName, basePath .. newName)
        else
            print("Invalid Table")
        end
    else
        print("Invalid Database!")
    end
end

--This function behaves like the DROP TABLE operator in MySQL.
--Ex: MySQL: DROP TABLE `exampleTable`
--in this API: db.deleteTable("DatabaseName", "exampleTable")
function deleteTable(db, tableName)
    if (dbExists(db)) then
        if (tableExists(db, oldName)) then
            path = "Databases\\" .. db .. "\\" .. tableName
            fs.delete(path)
        else
            print("Invalid Table")
        end
    else
        print("Invalid Database!")
    end
end

--This function is a mix of:
--MYSQL: SELECT * FROM `exampleTable` WHERE 1
--and PHP: mysql_num_rows()
function getTotalEntries(db, tableName)
    if (dbExists(db)) then
        if (tableExists(db, tableName)) then
            path = "Databases\\" .. db .. "\\" .. tableName
            ls = fs.list(path)
            totalEntries = 0
            for _ in pairs(ls) do totalEntries = totalEntries + 1 end
            return totalEntries
        else
            print("Invalid Table!")
            return nil
        end
    else
        print("Invalid Database!")
        return nil
    end
end

--This function behaves like the UPDATE/INSERT operator in MySQL.
--Ex: MySQL: INSERT INTO `exampleTable` (`id`, `foo`) VALUES ('1', 'bar')
--in this API: db.set("DatabaseName", "exampleTable", db.getNextFreeID(), "foo", "bar")
--MySQL: UPDATE `exampleTable` SET `foo` = 'updatedValue' WHERE `id` = '1'
--in this API: db.set("DatabaseName", "exampleTable", 1, "foo", "updatedValue")
--NOTE: It doesn't matter if you hand over the ID as number or as string
function set(db, tableName, id, key, value)
    if (dbExists(db)) then
        if (tableExists(db, tableName)) then
            if (not(fs.isDir("Databases\\" .. db .. "\\" .. tableName .. "\\" .. id))) then
                fs.makeDir("Databases\\" .. db .. "\\" .. tableName .. "\\" .. id)
            end
            path = "Databases\\" .. db .. "\\" .. tableName .. "\\" .. id .. "\\" .. key
            keyFile = fs.open(path, "w")
            keyFile.write(value)
            keyFile.close()
        else
            print("Invalid Table!")
        end
    else
        print("Invalid Database!")
    end
end

--This function behaves like the SELECT operator in MySQL.
--Ex: MySQL: SELECT `foo` FROM `exampleTable` WHERE `id` = '1'
--in this API: db.get("DatabaseName", "exampleTable", 1, "foo")
--NOTE: It doesn't matter if you hand over the ID as number or as string
function get(db, tableName, id, key)
    if (dbExists(db)) then
        if (tableExists(db, tableName)) then
            path = "Databases\\" .. db .. "\\" .. tableName .. "\\" .. id .. "\\" .. key
            if (fs.exists(path)) then
                keyFile = fs.open(path, "r")
                value = keyFile.readAll()
                keyFile.close()
                return value
            else
                return nil
            end
        else
            print("Invalid Table!")
        end
    else
        print("Invalid Database!")
    end
end

--This function behaves like the DELETE operator in MySQL.
--Ex: MySQL: DELETE FROM `exampleTable` WHERE `id` = '1'
--in this API: db.delete("DatabaseName", "exampleTable", 1)
function delete(db, tableName, id)
    if (dbExists(db)) then
        if (tableExists(db, tableName)) then
            path = "Databases\\" .. db .. "\\" .. tableName .. "\\" .. id
            if (fs.exists(path)) then
                fs.delete(path)
            end
        else
            print("Invalid Table!")
        end
    else
        print("Invalid Database!")
    end
end

--This function is similar to the WHERE operator in MySQL.
--It returns all the id's, where the value of the key specified
--is the same as the value you gave as a parameter.
--Ex: MySQL: SELECT `id` FROM `exampleTable` WHERE `foo` = 'bar'
--in this API: db.getIdsByValue("DatabaseName", "exampleTable", "foo", "bar")
function getIdsByValue(db, tableName, key, value)
    if (dbExists(db)) then
        if (tableExists(db, tableName)) then
            path="Databases\\" .. db .. "\\" .. tableName .. "\\"
            ls = fs.list(path)
            index = 0
            ids = {}
            for _, file in ipairs(ls) do
                origValue = get(db, tableName, file, key)
                if (origValue == value) then
                    index = index + 1
                    ids[index] = file
                end
            end
            return ids
        else
            print("Invalid Table!")
        end
    else
        print("Invalid Database!")
    end
end

--Gets the next not used ID
--Behaves like manual auto-increment
function getNextFreeID(db, tableName)
    if (dbExists(db)) then
        if (tableExists(db, tableName)) then
            totalEntries = getTotalEntries(db, tableName)
            path = "Databases\\" .. db .. "\\" .. tableName
            ls = fs.list(path)
            nextFreeID = tonumber(ls[totalEntries]) + 1
            return nextFreeID
        else
            print("Invalid Table!")
            return nil
        end
    else
        print("Invalid Database!")
        return nil
    end
end

function splitString(aString)
    --Split the string (query) into single words
    --and put them into a table
    local index = 0
    local splitString = {}
    for word in string.gmatch(aString, "[%a%d]+") do
        index = index + 1
        splitString[index] = word
    end
    return splitString
end

function query(db, query)
    --Get the first word, so the program knows, what to do
    --Possible: SELECT | UPDATE | INSERT | DELETE | CREATE | RENAME | DROP
    --IMPORTANT NOTE: You cant use parameters with spaces yet.
    --So something like UPDATE exampleTable SET foo = 'Hello World' isn't possible yet.
    --I will try to understand the gmatch-Patterns, so i can improve that.

    split = splitString(query)

    if (split[1] == "SELECT") then return sqlSELECT(db, query)
    elseif (split[1] == "UPDATE") then sqlUPDATE(db, query)
    elseif (split[1] == "INSERT") then return sqlINSERT(db, query)
    elseif (split[1] == "DELETE") then sqlDELETE(db, query)
    elseif (split[1] == "CREATE") then sqlCREATE(db, query)
    elseif (split[1] == "RENAME") then sqlRENAME(db, query)
    elseif (split[1] == "DROP") then sqlDROP(db, query)
    else
        print("Invalid SQL Query! Possible Operators (need to be the first word):")
        print("SELECT | UPDATE | INSERT | DELETE | CREATE | DROP")
    end
end

--db.query("TestDB", "UPDATE TestTable1 SET key = 'Value' WHERE id = '2'")

function sqlSELECT(db, query)
    --Standard SELECT query should look like this:
    --SELECT foo FROM exampleTable WHERE id = 1
    --So we need the words 2 (key) | 4 (table) | 6 (id or key) | 8 (searchValue)

    split = splitString(query)

    key = split[2]
    tableName = split[4]

    if (split[6] == "id") then
        --since we have got a single id we can just use the get-function
        return get(db, tableName, split[6], key)
    else
        --get the lists of IDs where key=value
        ids = getIdsByValue(db, tableName, split[6], split[8])

        --get the value of the key for every id found
        index = 0
        results = {}
        for _, id in ipairs(ids) do
            index = index + 1
            results[index] = get(db, tableName, id, key)
        end

        --results[0] is the total count of results found
        results[0] = index
        return results
    end
end

function sqlUPDATE(db, query)
    --Standard UPDATE query should look like this (only accepting a single value to be updated due to Lua
    --string parsing limitations, but this value can be updated in multiple rows):
    --UPDATE exampleTable SET foo = newValue WHERE id = 1
    --So we need the words 2 (table) | 4 (key) | 5 (newValue) | 7 (id or key) | 8 (searchValue)

    split = splitString(query)

    tableName = split[2]
    key = split[4]
    newValue = split[5]

    if (split[7] == "id") then
        --since we have got a single id we can just use the set-function
        set(db, tableName, split[8], key, newValue)
    else
        --get the lists of IDs where key=value
        ids = getIdsByValue(db, tableName, split[7], split[8])

        --set the value of the key for every id found
        index = 0
        results = {}
        for _, id in ipairs(ids) do
            index = index + 1
            set(db, tableName, id, key, newValue)
        end
    end
end

function sqlINSERT(db, query)
    --This function behaves a bit different as the MySQL model.
    --Instead of giving multiple keys and values you can just use this query:
    --INSERT INTO exampleTable
    --It will return the ID of the row that has just been inserted, so you can use UPDATE after.
    --If you want to create a row with one key and value, you can simply use the UPDATE query with given id after WHERE like this:
    --UPDATE exampleTable SET foo = newValue WHERE id = '5'
    --If a row with the id '5' isn't existing, the program will automatically create it and create the key with the given value.
    --Therefore the function db.getNextFreeID() can be useful, this is the only advantage this function has over the UPDATE function,
    --because this function directly looks for the next free ID, so you dont have to parameterize the ID.
    --This function is limited due to Lua string parsing limitations and will maybe get fixed in future versions

    split = splitString(query)

    tableName = split[3]
    nextFreeID = getNextFreeID(db, tableName)

    fs.makeDir("Databases\\" .. db .. "\\" .. tableName .. "\\" .. nextFreeID)

    return nextFreeID

end



function sqlDELETE(db, query)
    --Standard DELETE query should look like this:
    --DELETE FROM exampleTable WHERE id = '1'
    --So we need the words 3 (table) | 5 (id or key) | 6 (searchValue)

    split = splitString(query)

    tableName = split[3]

    if (split[5] == "id") then
        --since we have got a single id we can just use the delete-function
        delete(db, tableName, split[6])
    else
        --get the lists of IDs where key=value
        ids = getIdsByValue(db, tableName, split[5], split[6])

        --delete the row for every id found
        index = 0
        results = {}
        for _, id in ipairs(ids) do
            index = index + 1
            delete(db, tableName, id)
        end
    end
end

function sqlCREATE(db, query)
    --Standard CREATE query should look like this:
    --CREATE DATABASE dbName
    --or
    --CREATE TABLE tableName
    --So we need the words 2 (operator) | 3 (name)
    --NOTE: If you create a database, its not necessary to parameterize the db, you can just hand over nil

    split = splitString(query)

    operator = split[2]
    name = split[3]

    if (operator == "DATABASE") then createDatabase(name)
    elseif (operator == "TABLE") then createTable(db, name)
    else
        print("Invalid second operator! Possible second operators:")
        print("DATABASE | TABLE")
    end
end

function sqlRENAME(db, query)
    --Standard RENAME query should look like this:
    --RENAME TABLE oldName TO newName
    --So we need the words 3 (oldName) | 5 (newName)
    --NOTE: In MySQL it it possible to rename multiple DB's at once, but in this API you
    --can only rename one at the same time due to Lua string parsing limitations

    split = splitString(query)

    oldName = split[3]
    newName = split[5]

    renameTable(db, oldName, newName)
end

function sqlDROP(db, query)
    --Standard DROP query should look like this:
    --DROP DATABASE dbName
    --or
    --DROP TABLE tableName
    --So we need the words 2 (operator) | 3 (name)

    split = splitString(query)

    operator = split[2]
    name = split[3]

    if (operator == "DATABASE") then deleteDatabase(name)
    elseif (operator == "TABLE") then deleteTable(db, name)
    else
        print("Invalid second operator! Possible second operators:")
        print("DATABASE | TABLE")
    end
end